Oracle学习记录——3.Oracle的查询

待我称王封你为后i 2023-06-14 10:49 47阅读 0赞

### 文章目录 ###

*  1.查询
 *   *  Scott用户的表结构
     *  注意事项
     *  简单查询
     *  限定（条件）查询
 *  2.排序
 *   *  升序降序排列
     *  使用非选择列表排序
     *  使用表达式或者别名排序
     *  使用多列排序
 *  3.单行函数
 *   *  字符函数
     *  数字函数
     *  日期函数
     *  转换函数
     *  通用函数

# 1.查询 #

以下以scott用户中的四张表作为基础来进行查询。

## Scott用户的表结构 ##

1.  雇员表（emp）

雇员表中记录的是一个个雇员的基本信息

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMTUxNjU5_size_16_color_FFFFFF_t_70]

1.  部门表（dept）

表示一个个部门的具体信息

![在这里插入图片描述][20191119172225982.png]  
3. 工资登记表（SALGRADE）

一个公司工资是有等级制度，那么用此表表示一个工资的等级

![在这里插入图片描述][20191119172242290.png]  
4. 工资表（BONUS）

表示的是一个雇员的工资及奖金

![在这里插入图片描述][20191119172307374.png]

## 注意事项 ##

1.  Oracle不支持在指定字段的时候，用括号括起来的字段
2.  DISTINCT可以去除重复，但是注意位置是在SELECT的后跟。一定要记住，别名上的内容不要使用” ‘ ”括起来。而只有在 SELECT子句中出现的内容使用。
3.  对于别名，尽量建议不要使用中文，只要是程序的开发，都要回避中文
4.  Oracle的数据内容区分大小写
5.  Oracle中字符串的索引以1开始，如果输入0会被自动转换成1

## 简单查询 ##

简单查询指的是查询出一张表中的所有的数据，简单查询的语法如下：

SELECT [DISTINCT] *| 字段 [别名] [,字段 [别名]]
     FROM 表名称 [别名]

类似于SQL中的简单查询，这里不再详述，只介绍一些Oracle中的特殊用法。

在进行简单查询的操作之中，也可以使用各个数学的四则运算符。

例：要求显示每一个雇员的姓名、基本年薪，而且由于公司的福利很高，每个月都有 200 元的饭食补贴以及 100 元交通补贴，这个时候的SQL语句如下

1.  括号的优先

select ename,(sal + 300) * 12 from emp;

但是这个时候显示列上出现了一个“SAL\*12”，这个肯定是显示的查询列，但是这个列 名称不方便浏览，所以此时可以起一个别名。

1.  列起别名

select ename,(sal + 300) * 12 as Year from emp;

1.  链接查询的字段

在简单查询之中，也可以使用“||”链接查询的字段

例如：要求现在的数据按照如下的格式显示

雇员编号是：7369 的雇员姓名是：SMITH，基本工资是：800，职位是：CLERK!

select '雇员编号是：'||empno||'的雇员姓名是：'||ename||'，基本工资是：'||sal||'，职位 是：'||job||'!' 雇员信息 from emp;

## 限定（条件）查询 ##

在之前的简单查询之中，是将所有的记录显示，但是现在可以对显示的记录进行过滤操作，而这就属于限定查询的工作，限定查询就是在之前语法的基础上增加了一个WHERE子句，用于指定限定的条件，此时格式如下：

SELECT [DISTINCT] *| 字段 [别名] [,字段 [别名]] 
     FROM 表名称 [别名] 
     [WHERE 条件(s)];

在 WHERE 子句之后可以增加多个条件，最常见的条件就是基本的关系运算：>、>=、<、 <=、!=(<>)、BETWEEN…AND、LIKE、IN、IS NULL 、AND、OR、NOT。

1.  **关系运算**

**AND用法**：

要求查询出基本工资高于1500，低于3000的所有雇员信息

select * from emp where sal > 1500 and sal < 3000

若满足多条件的查询，可以类比为if语句中的多条件表达式

**OR用法**

查询出职位是办事员或者是销售人员的全部信息

select * from emp where job = 'CLERK' or job = 'SALESMAN'

多个条件，或的关系，和AND做区分，也就是不需要全部同时成立。可以从or开始拆分为多条语句最终做结果聚合

**AND和OR的混合用法**

查询出职位是办事员或者是销售人员的全部信息，并且要求这些雇员的工资大于 1200

select * from emp where (job='CLERK' or job='SALESMAN') and sal > 1200

**！=的用法**

查询所有不是办事员的雇员信息

select * from emp where job != 'CLERK'

类似于反查

1.  **范围判断**

**BETWEEN…AND…**

“BETWEEN 最小值 AND 最大值”，表示的是一个范围间的判断过程。

例：要求查询出基本工资在 1500-3000 的雇员信息。

SELECT * from emp where sal between 1500 and 3000;

现在也可以对 BETWEEN…AND…操作求反

SELECT * FROM emp where sal not between 1500 and 3000；

**注意：取反操作时，not的位置**

可是”BETWEEN…AND…”操作符不光只是针对于数字有用，对于日期也同样有用，但是需要使用to\_date进行转化

例：要求查询出在 1981 年雇佣的全部雇员信息，时间范围：

select * from emp where hiredate between to_date('1981-01-01','yyyy-mm-dd') and to_date('1981-12-31','yyyy-mm-dd');

**注意：日期格式，也可以自己转换成自己想要的格式。**

1.  **判断是否为空**

**IS (NOT) NULL**

使用此语法可以判断某一个字段上的内容是否为”null”，但是 null 和数字 0 以及空字符串 是两个概念。

例： 查询出所有领取奖金的雇员信息

Select * from emp where comm is not null;

**NVL函数**

如果NVL第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

例：查询年薪及奖金

select sal*12+comm from emp; // 查出来没有奖金的会导致年薪也没有了
    select sal*12+nvl(comm,0) from emp; // 使用nvl用指定的值来替换为空的值

1.  **指定范围的判断**

**IN操作符**

IN 操作符表示的是一个查询的范围。

例：查询出雇员号是 7369、7566、7799 的雇员信息

select * from emp where empno in (7369,7566,7788);

**NOT IN操作符**

使用 NOT IN，则表示不在指定范围中

如果现在使用了 IN 操作符，查询的范围之中存在了 NULL,不影响查询；

例：

Select *from emp where empno in(7369,7566,null);

如果现在使用的是 NOT IN 操作符，如果查询的范围之中有了 NULL，则不会有任何的查询结果返回；

例：

Select * from emp where empno not in(7369,7566,null);

对于这个限制现在先作为特点记住，以后会讲解为什么 NOT IN 之中不能出现 NULL，而且记住了，**如果NOT IN出现了null则表示的不是查询全部的信息**。

需要说明的是，使用 IN 操作符不仅可以用在数字上，还可以用在字符串上，**但是数字和字符串不能混用，因为数据类型只可能指定为其中一种**

1.  **模糊查询**

LIKE 子句的功能是提供了模糊查找的操作，例如：某些程序上出现的搜索操作，都属于LIKE 子句的实现，但是必须提醒的是，搜索引擎上的查询可不是 LIKE，但是要想使用 LIKE 子句则必须认识两个匹配符号：

*  匹配单个字符： \_ ; ->1 个
 *  匹配任意多个字符：% ; ->0 个、1 个、多个

例：要求查询雇员姓名中以字母A开头的全部雇员信息

Select *from emp where ename like ‘A%’;

要求查询出雇员姓名中第二个字母是 A 的全部雇员信息

Select * from emp where ename like ‘_A%’;

要求查询出雇员姓名中带有字母A的雇员

Select * from emp where ename like ‘%A%’;

LIKE现在也可以使用 NOT 操作，对操作进行求反功能

LIKE也可以在日期上使用

在开发之中，数据库的模糊查询肯定使用 LIKE 子句，但是在使用 LIKE 子句的时候有一个最大的注意点：如果在模糊查询上不设置任何的查询的关键字(%%)的话，则表示查询全部 记录：

这个特点可以帮助用户节约很多的代码，所以一定要记住。

1.  **运算符的优先级规则**

和算数运算符一样，比较运算符和逻辑运算符同样有优先级的区分，在整个 WHERE 子句中优先级的排列从高到低分别是：比较操作符>NOT>AND>OR

# 2.排序 #

当数据返回查询结果之后，所有的数据默认情况下是按照雇员编号排序的，当然，现在 也可以使用”ORDER BY”子句指定所需要的排序的操作列，此时 SQL 语法格式如下：

SELECT [DISTINCT] *| 字段 [别名] [,字段 [别名]] 
     FROM 表名称 [别名] 
     [WHERE 条件(s)]; 
     [ORDER BY 字段 [ASC|DESC] [,字段 [ASC|DESC],...]];

”ORDER BY”子句是写在所有的 SQL 语句最后的内容，而且对于排序有以下几点说明：

*  排序的时候可以指定多个排序的字段；
 *  支持使用数字和字符串排序
 *  排序的方式有两种：
    
     *  升序（ASC）:默认，不写也是升序；
     *  降序（DESC）:用户需要指定，由大到小排序；

## 升序降序排列 ##

例：查询所有雇员的信息，要求按照工资排序

Select * from emp order by sal;

Select * from emp order by sal asc;

进行降序排列

Select * from emp order by sal desc;

显示所有的销售人员的工资，按升序排列。

Select * from emp where job= ’SALESMAN’ order by sal;

## 使用非选择列表排序 ##

以上例子中都是按照在 SELECT 列表中出现的列，作为排序的依据，但是 SELECT 列表中也可以不出现排序的列。

例如：

Select empno,ename from emp order by sal;

## 使用表达式或者别名排序 ##

在 SELECT 子句中可以出现算术表达式，比如计算员工的年薪，按照年薪排序：

select ename from emp order by sal*12;

也可以将表达式列或者表达式起别名，利用别名排序：

select ename as name from emp order by name;

## 使用多列排序 ##

当执行操作时，不仅可以按照单列进行排序，还可以根据多列排序。当以多列作为排序

标准时，首先按照第一列进行排序，如果第一列数据相同，再以第二列排序，以此类推。

要求查出所有的雇员信息，按照工资由高到低排序，如果工资相同，则按照雇佣日期由早到晚排序

select * from emp order by sal,hiredate desc;

需要注意的是：多列排序时，不管正序还是倒序，每个列需要单独设置排列方式。

对于排序操作，一般只在需要的地方上使用。而且一定要记住，ORDER BY 子句是写在所有的 SQL 语句的最后的部分。

注：clear screen 清屏

# 3.单行函数 #

虽然各个数据库都是支持 SQL 语句的，但是每一个数据库也有它自己所支持的操作函 数，这些就是单行函数，而如果想要进行数据库开发的话，除了会使用 SQL 之外，就是要多学习函数。

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-25tEqDWV-1574146237526)(C:\\Users\\Administrator\\AppData\\Roaming\\Typora\\typora-user-images\\image-20191119140548717.png)\]][img-25tEqDWV-1574146237526_C_Users_Administrator_AppData_Roaming_Typora_typora-user-images_image-20191119140548717.png]

单行函数主要常用的函数有以下五类：字符函数、数字函数、日期函数、转换函数、通用函数。

## 字符函数 ##

字符函数的功能主要是进行字符串数据的操作，下面给出几个常用的字符函数：

Oracle 之中有一点比较麻烦，即使要验证字符串，也必须编写完整的 SQL 语句，所以 Oracle 数据库之中为了用户的查询方便，所以专门提供了一个”dual”的虚拟表。该表没有任何内容，只是为了方便验证函数。

除此之外，还有几个不常用的函数：

1.  CONCAT(char1,char2)：返回两个字符串连接后的结果，这个功能类似于使用连接操作 符"||"
2.  INSTR(char1,char2\[, n \[,m\]\])：用于取得子串在源字符串中的位置，也就是在 char1 中搜 索 char2,从 n 的位置开始搜索，如果没有指定 n，就从第一个字符开始搜索。m 用于指定子 串的第 m 次出现的次数，如果不指定取值为 1。如果在 char1 中没有找到子串，则返回 0.
3.

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MtYgyT8W-1574146237527)(C:\\Users\\Administrator\\AppData\\Roaming\\Typora\\typora-user-images\\image-20191119142459650.png)\]][img-MtYgyT8W-1574146237527_C_Users_Administrator_AppData_Roaming_Typora_typora-user-images_image-20191119142459650.png]

![在这里插入图片描述][20191119172504637.png]

## 数字函数 ##

常用的数字函数一共有三个：

1.  ROUND(数字|列 \[,保留小数的位数\])：四舍五入的操作；
2.  TRUNC(数字|列 \[,保留小数的位数\])：舍弃指定位置的内容；
3.  MOD(数字 1，数字 2)：取模，取余数；

一些其他的数字函数如下：

1.  floor(n) 返回小于或是等于 n 的最大整数
2.  ceil(n) 返回大于或是等于 n 的最小整数
3.  abs(n)： 返回数字 n 的绝对值
4.  acos(n)： 返回数字的反余弦值
5.  asin(n)： 返回数字的反正弦值
6.  atan(n)： 返回数字的反正切值
7.  cos(n)： 返回数字的余弦值
8.  exp(n)： 返回 e 的 n 次幂
9.  log(m,n)： 返回对数值
10. power(m,n)： 返回 m 的 n 次幂

## 日期函数 ##

如果现在要想进行日期的操作，则首先有一个必须要解决的问题，就是如何取得当前的日期，这个当前日期可用”SYSDATE”取得，代码如下

Select sysdate from dual;

除了以上的当前日期之外，在日期当中也可以进行若干计算：

1.  日期+数字 = 日期，表示若干天之后的日期；
2.  日期-数字 = 日期，表示若干天之前的日期；
3.  日期-日期 = 数字，表示的是两个日期间的天数，但是肯定是大日期-小日期;

而且很多的编程语言之中，也都会提出一种概念，日期可以通过数字表示出来。

除了以上三个公式之外，也提供了如下的四个操作函数：

1.  LAST\_DAY(日期)：求出指定日期**月**的最后一天；
2.  NEXT\_DAY(日期，星期数)：求出下一个指定星期 X 的日期；
3.  ADD\_MONTHS(日期，月份的数量)：求出若个月之后的日期，从下个月开始加；
4.  MONTHS\_BETWEEN(日期 1（大日期），日期 2（小日期）)：求出两个日期之间所经历的月份；

## 转换函数 ##

现在已经接触到了 Oracle 数据库之中的三种数据：数字（NUMBER）、字符串（VARCHAR2)、 日期（DATE）,转换函数的主要功能是完成这几个数据间的互相转换操作，一共有三种转换函数：

1.  TO\_CHAR(字符串|列，格式字符串):将日期或者数字变为字符串显示；
2.  TO\_DATE(字符串，格式字符串):将字符串变为 DATE 型数据显示；
3.  TO\_NUMBER(字符串)：将字符串变为数字显示；在一定程度上可以不写，可以实际直接使用数字字符串进行数学运算，依旧是可行的

## 通用函数 ##

通用函数主要有两个：NVL()、DECODE()，这两个函数就是 Oracle 自己的特色函数

1.  **NVL()**

NVL(expr1,expr2)：

如果 expr1 是 null，则用 expr2 值代替。

1.  **NVL2()**

NVL2（expr1,expr2,expr3）;

如果 expr1 不是 null，用 expr2 值代替，否则 null 就用 expr3 代

替。

1.  **DECODE()**

DECODE()函数非常类似于程序中的 if…else 语句，唯一不同的是 DECODE()函数判断的是数值，而不是逻辑条件。

实际作用就是将查询结果进行指定的替换。

这种判断肯定是逐行进行判断，所以这个时候就必须采用 DECODE()函数，此函数的语法格式：

DECODE(数值|列，判断值 1，显示值 1，判断值 2，显示值 2，判断值 3，显示值 3，…) ;

DECODE()函数是整个 Oracle 之中最具有特点的函数，一定要将其掌握。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMTUxNjU5_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191119172215364.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMTUxNjU5,size_16,color_FFFFFF,t_70
[20191119172225982.png]: https://img-blog.csdnimg.cn/20191119172225982.png
[20191119172242290.png]: https://img-blog.csdnimg.cn/20191119172242290.png
[20191119172307374.png]: https://img-blog.csdnimg.cn/20191119172307374.png
[img-25tEqDWV-1574146237526_C_Users_Administrator_AppData_Roaming_Typora_typora-user-images_image-20191119140548717.png]: https://img-blog.csdnimg.cn/20191119172419124.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMTUxNjU5,size_16,color_FFFFFF,t_70
[img-MtYgyT8W-1574146237527_C_Users_Administrator_AppData_Roaming_Typora_typora-user-images_image-20191119142459650.png]: https://img-blog.csdnimg.cn/20191119172436189.png
[20191119172504637.png]: https://img-blog.csdnimg.cn/20191119172504637.png