oracle16c,oracle--子查询和集合运算

子查询和集合运算

一、子查询

语法：

select select_list

from table

where expr operator

(select select_list from table);

子查询(内查询)在主查询之前一次执行完成。

子查询的结果被主查询使用(外查询)。

注意：

子查询要包含在括号内。

将子查询放在条件的右侧。

单行操作符对应单行子查询，多行操作符对应多行子查询。

例：

SQL> --查询工资比SCOTT高的员工信息

SQL> --1. SCOTT的工资

SQL> select sal from emp where ename='SCOTT';

SAL

----------

3000

SQL> --2. 比3000高的员工

SQL> set linesize 200

SQL> select * from emp where sal > 3000;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- -------------- ---------- ---------- ----------

7839 KING PRESIDENT 17-11月-81 5000 10

SQL> --子查询所要解决的问题： 不能一步求解

SQL> select *

2 from emp

3 where sal > (select sal

4 from emp

5 where ename='SCOTT');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- -------------- ---------- ---------- ----------

7839 KING PRESIDENT 17-11月-81 5000 10

注意的问题：

1. 括号

2. 合理的书写风格

3. 可以在where select having from后面 都可以使用子查询

4. 不可以在group by后面使用子查询

5. 强调from后面的子查询

6. 主查询和子查询可以不是同一张表；只要子查询返回的结果 主查询可以使用 即可

7. 一般不在子查询中排序；但在top-n分析问题中，必须对子查询排序

8. 一般先执行子查询，再执行主查询；但相关子查询例外

9. 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符

单行子查询：只返回一行，使用单行比较操作符

多行子查询：返回多行，使用多行比较操作符

10.子查询中的null

3.可以在where select having from后面 都可以使用子查询：

SQL> select empno,ename,sal,(select job from emp where empno=7839) 第四列

2 from emp;

5.强调from后面的子查询

SQL> --查询员工信息：员工号 姓名 月薪

SQL> select *

2 from (select empno,ename,sal from emp);

--查询员工信息：员工号 姓名 月薪 年薪

select * from (select empno,ename,sal,sal*12 annsal from emp)

思考：

select empno,ename,sal,sal*12 annsal from emp;

select * from (select empno,ename,sal,sal*12 annsal from emp);

以上两条语句性能一样

6.主查询和子查询可以不是同一张表；只要子查询返回的结果 主查询可以使用即可

查询部门名称是SALES的员工：

方式一：使用子查询

select * from emp where deptno=(select deptno from dept where dname='sales');

方式二：使用多表查询

select e.* from emp e,dept d where e.deptno=d.deptno and d.dname='sales';

SQL 优化：尽量使用多表查询

单行比较操作符：

单行比较操作符.PNG

执行单行子查询：

select ename,job,sal from emp

where job =

(select job from emp where empno = 7566)

and sal >

(select sal from emp where empno = 7782);

在子查询中使用组函数

select ename,job,sal from emp where sal =

(select min(sal)

from emp);

子查询中的having子句

首先执行子查询

向主查询中的having子句返回结果

select deptno,min(sal)

from emp

group by deptno

having min(sal) >

(select min(sal)

from emp

where deptno = 10);

多行比较操作符

多行比较操作符.PNG

SQL> --in 在集合中

SQL> --查询部门名称是SALES和ACCOUNTING的员工

SQL> select *

2 from emp

3 where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING');

SQL> --any: 和集合中的任意一个值比较

SQL> --查询工资比30号部门任意一个员工高的员工信息

SQL> select *

2 from emp

3 where sal > any (select sal from emp where deptno=30);

等价于：

select * from emp where sal > (select min(sal) from emp where deptno=30)

SQL> --all 和集合中的所有值比较

SQL> --查询工资比30号部门所有员工高的员工信息

SQL> select *

2 from emp

3 where sal > all (select sal from emp where deptno=30);

等价于：

select * from emp where sal > (select max(sal) from emp where deptno=30)

多行子查询中的null：

SQL> --not in (10,20,null)

SQL> --查询不是老板的员工

SQL> --查询是老板的员工

SQL> select *

2 from emp

3 where empno in (select mgr from emp);

SQL> select *

2 from emp

3 where empno not in (select mgr from emp where mgr is not null);

二、集合运算

集合运算符：

集合运算符.PNG

union:返回两个集合去掉重复元素后的所有记录

union all:返回两个集合的所有记录，包括重复的

intersect:运算符返回同时属于两个集合的记录

minus:返回属于第一个集合，但不属于第二个集合的记录

例：

SQL> /*

SQL> 查询10和20号部门的员工

SQL> 1. select * from emp where deptno in (10,20);

SQL> 2. select * from emp where deptno=10 or deptno=20;

SQL> 3. 集合运算

SQL> select * from emp where deptno=10

SQL> 加上

SQL> select * from emp where deptno=20

SQL> */

SQL> select * from emp where deptno=10

2 union

3 select * from emp where deptno=20;

SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);

注意的问题：

select语句中的参数类型和个数要一致

可以使用括号改变集合执行的顺序

如果有order by子句，必须放到最后一句查询语句后

集合运算采用第一个语句的表头作为表头

SQL> select deptno,job,sum(sal) from emp group by deptno,job

2 union

3 select deptno,to_char(null),sum(sal) from emp

4 union

5 select to_number(null),to_char(null),sum(sal) from emp;

SQL 优化 5. 尽量不要使用集合运算