SQL>---------******---集合运算----------******-------
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
2 union
3 select * from emp where deptno=20;
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400
29025
已选择13行。
SQL> /*
SQL> --注意的问题
SQL> --1. 参与运算的各个集合必须列数相同 且类型一致(重点)
SQL> -- 2. 采用第一个集合的表头作为最后的表头
SQL> --3. 如果排序，必须 在每个集合后使用相同order by
SQL> --4. 括号
SQL> */
SQL> select deptno,job,sum(sal) from emp group by deptno,job
2 union
3 select deptno,to_char(null),sum(sal) from emp group by deptno --为了使列数类型相同，添加null值
4 union
5 select to_number(null),to_char(null),sum(sal) from emp;
SQL> break on deptno skip 2
SQL> break on null
SQL> host cls
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
DEPTNO JOB SUM(SAL)
---------- --------- ----------
30 9400
29025
已选择13行。
SQL> set timing on
SQL> --优化5: 尽量不要使用集合运算
SQL> set timing off
SQL> --交集
SQL> select ename,sal from emp
2 where sal between 700 and 1300
3 INTERSECT
4 select ename,sal from emp
5 where sal between 1201 and 1400;
SQL> --差集（减法）
SQL> select ename,sal from emp
2 where sal between 700 and 1300
3 minus
4 select ename,sal from emp
5 where sal between 1201 and 1400;
-----------*****子查询*****--------------
SQL> /*
SQL> 注意的问题（不重要）
SQL> 1. 括号
SQL> 2. 合理的书写风格
SQL> 3. 可以主查询的where select from having后面放置子查询
SQL> 4. 不可以在主查询的group by后面放置子查询
SQL> 5. 强调from后面的子查询
SQL> 6. 主查询和子查询可以不是同一张表，只要子查询返回的结果 主查询可以使用即可
SQL> 7. 一般不在子查询使用order by，但在Top-N分析问题中 必须使用order by
SQL> 8. 一般先执行子查询，再执行主查询；但相关子查询除外
SQL> 9. 单行子查询只能使用单行操作符 多行子查询只能使用多行操作符
SQL> 10. 子查询中null
SQL> */
SQL> -- 3. 可以主查询的where select from having后面放置子查询
SQL> select ename,sal,(select job from emp where empno=7839) 一列
2 from emp;
SQL> --6. 主查询和子查询可以不是同一张表，只要子查询返回的结果 主查询可以使用即可
SQL> --查询部门名称为SALES的员工信息
SQL> select *
2 from emp
3 where deptno=(select deptno
4 from dept
5 where dname='SALES');
SQL> select e.*
2 from emp e,dept d
3 where e.deptno=d.deptno and d.dname='SALES';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30
已选择6行。
SQL> --优化5:理论上，尽量使用多表查询
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> select e.*
2 from emp e,dept d
3 where e.deptno=d.deptno and (d.dname='SALES' or d.dname='ACCOUNTING');
SQL> --any: 和集合中 任意一个值比较
SQL> --查询工资比30号部门任意一个员工高的员工信息
SQL> select *
2 from emp
3 where sal > any (select sal from emp where deptno=30);
SQL> ed
已写入 file afiedt.buf
1 select *
2 from emp
3* where sal > (select min(sal) from emp where deptno=30)
SQL> /
SQL> --all:和集合的所有值比较
SQL> --查询工资比30号部门所有员工高的员工信息
SQL> select *
2 from emp
3 where sal > all (select sal from emp where deptno=30);
SQL> ed
已写入 file afiedt.buf
1 select *
2 from emp
3* where sal > (select max(sal) from emp where deptno=30)
SQL> /
SQL> --多行子查询中null
SQL> --查询没有下属的员工信息
SQL> --1、先查询有下属的员工
SQL> select *
2 from emp
3 where empno in (select mgr from emp);
SQL> --2、再查询没有下属的员工
SQL> ed
已写入 file afiedt.buf
1 select *
2 from emp
3* where empno not in (select mgr from emp)
SQL> /
未选定行
--解释：因为not in集合中有null值，KING没有mgr，因此是空值，not in null 返回false
SQL> ed
已写入 file afiedt.buf
1 select *
2 from emp
3* where empno not in (select mgr from emp where mgr is not null)
SQL> /
SQL> spool off
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
