Oracle 查询(SELECT)语句（二）

Ø  简介

在前面的 Oracle 查询 SELECT 语句（一） 中介绍了 SELECT 常用的一些基本查询语法，接下来再来看 SELECT 更深入的一些查询功能和技巧，包括以下内容：

1.   All 与 Any 运算符

2.   集合操作符(UNION、UNION ALL、INTERSECT、MINUS)

3.   WITH AS 的用法

4.   CONNECT BY 子句的使用

 

1.   All 与 Any 运算符

1)   All 运算符，表示满足给出列表中的所有值。通常用于以下场景：

1.   查出大于30号部门所有员工最高工资的员工姓名、工资

--使用ALL

SELECT ename, sal FROM emp WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 30);

--使用MAX

SELECT ename, sal FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);

 

2)   Any 运算符，表示满足给出列表中的任意值。通常用于以下场景：

1.   查出大于30号部门任意员工的工资的员工姓名、工资

--使用ALL

SELECT ename, sal FROM emp WHERE sal > ANY(SELECT sal FROM emp WHERE deptno = 30);

--使用MIN

SELECT ename, sal FROM emp WHERE sal > (SELECT MIN(sal) FROM emp WHERE deptno = 30);

 

n  提示

通过以上示例，可以看到通常情况下，ALL 和 ANY 都可以使用 MAX 和 MIN 去取代，所以这两个运算符一般情况用的不多。分析如下：

比较运算符	All	Any	Max	Min
> 大于	取最大值	取最小值	取最大值	取最小值
< 小于	取最小值	取最小值	取最大值	取最小值

 

2.   集合操作符(UNION、UNION ALL、INTERSECT、MINUS)

在 Oracle 中对多个集合操作，主要使用 UNION、UNION ALL、INTERSECT、MINUS 这四个操作符来完成，解决取多个集合的并集、交集和差集。

1)   UNION、UNION ALL

UNION 和 UNION ALL 用于取两个结果集的并集，举例说明：

Ø  UNION，取出并集并去除重复行，例如：

SELECT * FROM emp WHERE empno <= 7500

UNION

SELECT * FROM emp WHERE empno < 7600;

说明：

1.   两个查询的结果集中，都有两条记录小于7500，UNION 在合并时进行去重了，所以相同的记录只会显示一行。

2.   UNION 逻辑上可以合并任意个结果集，所以只需要在后面接着写 UNION 即可。

 

Ø  UNION ALL，取出并集不会去除重复行，例如：

SELECT * FROM emp WHERE empno <= 7500

UNION ALL

SELECT * FROM emp WHERE empno < 7600;

可见，两个结果集中具有两条相同的记录，UNION ALL 并没有去重。

 

2)   INTERSECT

INTERSECT 用于取两个结果集中相交的记录，即取得交集记录。还是以刚才的数据为例：

SELECT * FROM emp WHERE empno <= 7500

INTERSECT

SELECT * FROM emp WHERE empno < 7600;

在两个查询的结果集中，存在两个完全相同的记录，这就是交集。

 

3)   MINUS

MINUS 中文意思是减去，表示第一个结果集减去第二个结果集，取出它们的差集。任然以刚才的数据为例：

SELECT * FROM emp WHERE empno <= 7600

MINUS

SELECT * FROM emp WHERE empno < 7500;

取得两条记录，这两条记录是在第一个结果集中存在，在第二个结果集中不存在的记录，则为差集。

 

3.   WITH AS 的用法

使用 WITH AS 语句可以为一个子查询语句块定义一个名称，使用这个子查询名称可以在查询语句的很多地方引用这个子查询。Oracle 数据库像对待内联视图或临时表一样对待它，从而起到一定的优化作用。with 子句是9i中新增的语法。

语法：

WITH <alias_name1> AS (subquery_sql_statement)

    [,<alias_name2> AS (subquery_sql_statement)]

    [...,<alias_name3> AS (subquery_sql_statement)]

SELECT <column_name_list> FROM <alias>;

 

n  WITH AS 的优点：

1)   增强 SQL 的可读性，比如对 with 子查询取一个有意义的名字等；

2)   with 子查询只执行一次，将结果存储在用户临时表空间中，可以被引用多次，增强性能。

 

n  示例

1)   查询部门总薪水大于所有部门平均薪水的部门

分析（三步骤）：

1.   获取每个部门总薪水；

2.   再计算出所有部门的平均薪水；

3.   最后获取部门总薪水比较部门平均薪水；

SQL 如下：

WITH dept_total AS

 (SELECT t1.dname, SUM(t2.sal) totalsal

    FROM dept t1, emp t2

   WHERE t1.deptno = t2.deptno

   GROUP BY t1.dname)

SELECT t.dname, t.totalsal, (SELECT AVG(totalsal) FROM dept_total) avgsal

  FROM dept_total t

 WHERE t.totalsal > (SELECT AVG(totalsal) FROM dept_total);

 

2)   查询部门总薪水大于所有部门平均薪水的部门（进一步优化）

可以看到之前求平均薪资时，执行了两次 SELECT 语句，其实我们还可以进一步优化，即再定义一个子查询，SQL 如下：

WITH dept_total AS

 (SELECT t1.dname, SUM(t2.sal) totalsal

    FROM dept t1, emp t2

   WHERE t1.deptno = t2.deptno

   GROUP BY t1.dname),

dept_avg AS

 (SELECT SUM(totalsal) / COUNT(*) avgsal FROM dept_total) --这里模拟复杂处理

SELECT t.dname, t.totalsal, (SELECT avgsal FROM dept_avg) avgsal

  FROM dept_total t

 WHERE t.totalsal > (SELECT avgsal FROM dept_avg);

 

3)   使用 WITH 子句插入数据

INSERT INTO dept (deptno, dname, loc)

WITH temp AS(

     --这里模拟数据来源

     SELECT 50 col1, '财务部' col2, '上海' col3 FROM dual UNION

     SELECT 60 col1, '运营部' col2, '北京' col3 FROM dual UNION

     SELECT 70 col1, '研发部' col2, '武汉' col3 FROM dual

)

SELECT * FROM temp;

 

查询结果：SELECT * FROM dept;

 

4)   使用 WITH 子句更新数据

UPDATE dept t1 SET loc = (

       WITH t2 AS (SELECT * FROM dept)

       SELECT concat(loc, '市') col1 FROM t2 WHERE t1.deptno = t2.deptno

);     --这里只看语法，不看逻辑

 

查询结果：SELECT * FROM dept;

 

5)   使用 WITH 子句删除数据

DELETE FROM dept t1 WHERE EXISTS(

       WITH t2 AS (SELECT deptno, ascii(substr(dname, 1, 1)) col1 FROM dept),

       t3 AS (SELECT * FROM t2 WHERE NOT(col1 >= 65 AND col1 <= 90) AND NOT(col1 >= 97 AND col1 <= 122))

       SELECT 1 FROM t3 WHERE t1.deptno = t3.deptno

);

说明：执行以上语句，将会删除部门名称不以字母开头的部门（忽略意义，只看语法）

 

查询结果：SELECT * FROM dept;

 

6)   对输出列定义别名

WITH temp(empno2, name2, sal2) AS

(

     SELECT empno, ename, sal FROM emp

)

SELECT * FROM temp;

注意：别名的数量必须与子查询输出的列数量相同。

 

n  注意事项：

1)   定义了 with 子查询，可以在之后的 SELECT 中不被使用到，并不会报错，例如：

WITH temp_emp AS

 (SELECT * FROM emp WHERE sal > 3000)

SELECT * FROM emp WHERE sal > 3000;

提示：有些文章中提到以上情况下会报错，那可能是 11g 以下版本，经测试在 11g 版本中是不报错的。

2)   在 with 中定义多个子查询时，后面的子查询语句可以引用前面的子查询的别名；

3)   with 子句通常用于对某一 SELECT 结果集需要进行多次处理时，可以减少查询次数和提高可读性。

 

4.   CONNECT BY 子句的使用

connect by 子句通常用于数据表存在父子、上下级等层级关系的场景中，可以进行层级查询（递归查询）。

语法：

START WITH condition CONNECT BY [NOCYCLE] condition [AND condition]...

START WITH: 指定起始节点的条件；

CONNECT BY: 指定节点之间的关联关系；

PRIOR: 层级关联限定符，可用于显示层级中指定的字段；

NOCYCLE: 是否终止循环依赖，如存在循环依赖时，不指定该参数会报错。指定该参数，循环依赖只会显示一行；

connect_by_iscycle: 是否为循环行，1表示是，0表示否，必须指定 NOCYCLE 参数后才能使用该参数；

connect_by_isleaf: 是否为叶子节点，0表示是，0表示否；

level: 表示层级的级数，值越小表示层级越高，第一层为1，之后开始递增。

 

n  在 scott 用户中提供了一个 emp（员工表），数据如下：

SELECT * FROM emp ORDER BY mgr DESC;

数据层次结构如下：

可以看到，该表包含4级别，这是我花了半个小时才整理好的，眼睛都看花了。由此可见，如果人为去找层次结构的数据，是一件多么辛苦的事情。所以，这就是 connect by 子句的强大所在。

好了，废话不多说。现在搞清楚层次结构后，下面开始各种查询：

 

1)   查出员工编号[7839]下的所有下属

SELECT level, empno, ename, mgr FROM emp

START WITH empno = 7839

CONNECT BY PRIOR empno = mgr    --表示根据当前 empno 的值，去寻找 mgr 为该值的记录

ORDER BY level;

说明：根据当前记录向下递归

 

2)   查出员工编号[7876]的所有领导

SELECT level, empno, ename, mgr FROM emp

START WITH empno = 7876

CONNECT BY PRIOR mgr = empno    --表示根据当前 mgr 的值，去寻找 empno 为该值的记录

ORDER BY level;

说明：根据当前记录向上递归

 

3)   查出员工编号[4902]的领导，以及与领导同级员工

WITH Temp AS (

  SELECT level AS lv, empno, ename, mgr, PRIOR ename FROM emp

  START WITH mgr IS NULL

  CONNECT BY mgr = PRIOR empno

)

SELECT * FROM Temp t1, Temp t2 WHERE t1.empno = 7902 AND t2.lv = (t1.lv - 1) --这里借助了笛卡尔积

AND t2.empno <> t1.mgr; --是否包含父节点

 

4)   查出员工编号[7566]的族兄节点

WITH Temp AS (

  SELECT level AS lv, empno, ename, mgr, PRIOR ename FROM emp

  START WITH mgr IS NULL

  CONNECT BY mgr = PRIOR empno

)

SELECT t2.* FROM Temp t1, Temp t2 WHERE t1.empno = 7566

AND t2.lv = t1.lv AND t2.empno <> 7566; --这里借助了笛卡尔积

 

5)   使用 level  伪列，格式化输出

SELECT (lpad(' ', level * 2) || ename) ename, empno, mgr, level FROM emp

START WITH empno = 7839

CONNECT BY mgr = PRIOR empno;

 

6)   使用 connect_by_root 查看跟节点

SELECT level AS lv, (connect_by_root ename) root_ename, empno, ename, mgr, PRIOR ename FROM emp

START WITH mgr IS NULL

CONNECT BY mgr = PRIOR empno;

 

7)   使用 connect_by_isleaf 查看是否为叶子节点

SELECT level AS lv, empno, ename, mgr, connect_by_isleaf AS isleaf FROM emp

START WITH mgr IS NULL

CONNECT BY mgr = PRIOR empno;

 

8)   使用 connect_by_iscycle 查看是否循环依赖（因为 empno 是主键，不可能重复，就不演示了）

SELECT level AS lv, empno, ename, mgr, connect_by_iscycle as cycle FROM emp

START WITH mgr IS NULL

CONNECT BY NOCYCLE mgr = PRIOR empno;