【Oracle】的子查询和常用函数详解

1.子查询
子查询在 SELECT、UPDATE、DELETE 语句内部可以出现 SELECT 语句。内部的 SELECT 语句结果可以作为外部语句中条件子句的一部分，也可以作为外部查询的临时表。子查询的类型有：

• 单行子查询：不向外部返回结果，或者只返回一行结果。
• 多行子查询：向外部返回零行、一行或者多行结果。

1）查询出销售部（SALES）下面的员工姓名，工作，工资。
案例分析：该问题可以用联接查询实现，由于所需的结果信息都在 Emp 表中，可以先从 Dept 表中查询出销售部对应的部门号，然后根据当前部门号再到 Emp 表中查询出符合该部门的员工记录即可。从销售表中查询出的结果可以作为 Emp 表中查询的条件，SQL 语句实现如下

需要注意：如果内部查询不返回任何记录，则外部条件中字段 DEPTNO 与 NULL 比较永远为假，也就是说外部查询不返还任何结果；在单行子查询中外部查询可以使用=、>、<、>=、<=、<>等比较运算符；内部查询返回的结果必须与外部查询条件中的字段（DEPTNO）匹配；如果内部查询返回多行结果则出现错误。

2）查询出 Emp 表中比任意一个销售员(“SALESMAN”)工资低的员工姓名、工作、工资。
案例分析：销售员在 Emp 表中有很多条记录，每个人工资不相等，如果返回“比任意员工的工资还低”的条件，返回比“最高工资还低”即可。如果用子查询做，子查询中就会返回多条记录。用普通的关系符（>、<等）运行就会出错。这时候需要用关键字 ANY。ANY 放在比较运算符后面，表示“任意”的意思。

3）查询出比所有销售员(“SALESMAN”)的工资都高的员工姓名，工作，工资。
案例分析：ANY 可以表示任意的，但本案例中要求比所有销售员工资都高，那么就要使用另外一个
关键字 ALL。ALL 与关系操作符一起使用，表示与子查询中所有元素比较。

对于子查询还可以使用 IN 和 NOT IN 操作符进行操作。

2.Oracle 中的伪列
在 Oracle 的表的使用过程中，实际表中还有一些附加的列，称为伪列。伪列就像表中的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作。

1） ROWID
表中的每一行在数据文件中都有一个物理地址，ROWID 伪列返回的就是该行的物理地址。使用 ROWID 可以快速的定位表中的某一行。ROWID 值可以唯一的标识表中的一行。由于 ROWID 返回的是该行的物理地址，因此使用 ROWID 可以显示行是如何存储的。

2）ROWNUM
在查询的结果集中，ROWNUM 为结果集中每一行标识一个行号，第一行返回 1，第二行返回 2，以此类推。通过 ROWNUM 伪列可以限制查询结果集中返回的行数。
注意：ROWNUM 与 ROWID 不同，ROWID 是插入记录时生成，ROWNUM 是查询数据时生成。ROWID 标识的是行的物理地址。ROWNUM 标识的是查询结果中的行的次序。

–查询出员工表中前 5 名员工的姓名，工作，工资。

–查询出工资最高的前 5 名员工的姓名、工作和工资。
案例分析：“工资最高的前 5 名”需要先降序排序，再取前 5 名，但是生成 ROWNUM 操作比排序要早，排序时已经连同 ROWNUM 一起排序了，需要对排序的结果重新做二次查询，产生新的 ROWNUM 才能作为查询的条件依据。

T 是子查询的别名，这里的 ROWNUM 是第二次查询后的 ROWNUM。

–查询出表 EMP 中第 5 条到第 10 条之间的记录。

内部查询中得到 ROWNUM 并且用别名 r 记录，供外层条件使用；内部查询的 ROWNUM，与外出的 ROWNUM 列是平等的两列；使用的 r 是内层产生的 ROWNUM，在外层看来，内层查询的 ROWNUM 是正常的一列。

3.Oracle 函数
Oracle SQL 提供了用于执行特定操作的专用函数。这些函数大大增强了 SQL 语言的功能。函数可以接受零个或者多个输入参数，并返回一个输出结果。Oracle 数据库中主要使用两种类型的函数：

• 单行函数：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结果，比如：MOD(x,y)返回 x 除以 y 的余数（x 和 y 可以是两个整数，也可以是表中的整数列）。常用的单行函数有：
   字符函数：对字符串操作。
   数字函数：对数字进行计算，返回一个数字。
   转换函数：可以将一种数据类型转换为另外一种数据类型。
   日期函数：对日期和时间进行处理。
• 聚合函数：聚合函数同时可以对多行数据进行操作，并返回一个结果。比如 SUM(x)返回结果集中 x 列的总合。

1）字符函数
字符函数接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式。下表列出了常用的字符函数。

2）数字函数
数字函数接受数字参数，参数可以来自表中的一列，也可以是一个数字表达式。


1. ROUND(X[,Y])，四舍五入。
在缺省 y 时，默认 y=0；比如：ROUND(3.56)=4。
y 是正整数，就是四舍五入到小数点后 y 位。ROUND(5.654,2)=5.65。
y 是负整数，四舍五入到小数点左边|y|位。ROUND(351.654,-2)=400。
2. TRUNC(x[,y])，直接截取，不四舍五入。
在缺省 y 时，默认 y=0；比如：TRUNC (3.56)=3。
y 是正整数，就是四舍五入到小数点后 y 位。TRUNC (5.654,2)=5.65。
y 是负整数，四舍五入到小数点左边|y|位。TRUNC (351.654,-2)=300。

3）日期函数
日期函数对日期进行运算。常用的日期函数有：

–ADD_MONTHS(d,n)，在某一个日期 d 上，加上指定的月数 n，返回计算后的新日期。d 表示日期，n 表示要加的月数。

–LAST_DAY(d)，返回指定日期当月的最后一天。

–ROUND(d[,fmt])，返回一个以 fmt 为格式的四舍五入日期值，d 是日期，fmt 是格式
模型。默认 fmt 为 DDD，即月中的某一天。
 如果 fmt 为“YEAR”则舍入到某年的 1 月 1 日，即前半年舍去，后半年作为下一年。
 如果 fmt 为“MONTH”则舍入到某月的 1 日，即前月舍去，后半月作为下一月。
 默认为“DDD”，即月中的某一天，最靠近的天，前半天舍去，后半天作为第二天。
 如果 fmt 为“DAY”则舍入到最近的周的周日，即上半周舍去，下半周作为下一周周日。

与 ROUND 对应的函数时 TRUNC(d[,fmt])对日期的操作，TRUNC 与 ROUND 非常相似，只是不对日期进行舍入，直接截取到对应格式的第一天。

–EXTRACT(fmt FROM d)，提取日期中的特定部分。
fmt 为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。其中 YEAR、MONTH、DAY可以为 DATE 类型匹配，也可以与 TIMESTAMP 类型匹配；但是 HOUR、MINUTE、SECOND 必须与 TIMESTAMP 类型匹配。HOUR 匹配的结果中没有加上时区，因此在中国运行的结果小 8 小时。

4）转换函数
转换函数将值从一种数据类型转换为另外一种数据类型。常用的转换函数有：

–TO_CHAR(d|n[,fmt])
把日期和数字转换为制定格式的字符串。fmt 是格式化字符串。

在格式化字符串中，使用双引号对非格式化字符进行引用。针对数字的格式化，格式化字符有：

–TO_DATE(x [,fmt])
把一个字符串以 fmt 格式转换为一个日期类型。

–TO_NUMBER(x[,fmt])
把一个字符串以 fmt 格式转换为一个数字。

5）其他单行函数

–NVL(x,value)
如果 x 为空，返回 value，否则返回 x。

–NVL2(x,value1,value2)
如果 x 非空，返回 value1，否则返回 value2

6）聚合函数
聚合函数同时对一组数据进行操作，返回一行结果，比如计算一组数据的总和，平均值等。

–求本月所有员工的基本工资总和。

–求不同部门的平均工资。

4.总结

• Oracle 常用函数有字符相关的函数、数字相关的函数、日期相关的函数、转换函数等。
• EXTRACT 函数能够获取日期中的某个字段的值。
• TO_CHAR 函数能够把数字和日期转换成固定的字符串格式。TO_DATE函数能够把固定格式的字符串转换为日期类型。
• 子查询中有返回单行的子查询和返回多行的子查询。
• Oracle 中存在 ROWID、ROWNUM 等伪列。