SQL> --什么是集合运算?
SQL> --查询10号部门和20号部门的员工
SQL> --第一种
SQL> select *
2 from emp
3 where deptno=10 or deptno=20;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择8行。
SQL> --第二种
SQL> select *
2 from emp
3 where deptno in (10,20);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择8行。
SQL> --第三种:集合运算
SQL> -- select * from emp where deptno=10;
SQL> -- select * from emp where deptno=20;
SQL> -- 集合的三种运算:并，交，差
SQL> select * from emp where deptno=10
2 union
3 select * from emp where deptno=20;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择8行。
SQL> --关于并集，讨论性能
SQL> --group by的增强
SQL> select deptno,job,sum(sal)
2 from emp
3 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> break on deptno skip 2
SQL> /
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
MANAGER 2450
PRESIDENT 5000
8750
20 CLERK 1900
ANALYST 6000
MANAGER 2975
10875
DEPTNO JOB SUM(SAL)
---------- --------- ----------
30 CLERK 950
MANAGER 2850
SALESMAN 5600
9400
29025
已选择13行。
SQL> host cls
SQL> select deptno,job,sum(sal) from emp group by deptno,job
2 union
3 select deptno,sum(sal) from emp group by deptno
4 union
5 select sum(sal) from emp;
select deptno,sum(sal) from emp group by deptno
*
第 3 行出现错误:
ORA-01789: 查询块具有不正确的结果列数
SQL> --使用集合操作的 注意事项:
SQL> --1. 参与运算的集合必须列数相同
SQL> --2. 运算后的结果的表头为第一个集合的表头
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
4 union
5 select to_number(null),to_char(null),sum(sal) from emp;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
MANAGER 2450
PRESIDENT 5000
8750
20 ANALYST 6000
CLERK 1900
MANAGER 2975
10875
DEPTNO JOB SUM(SAL)
---------- --------- ----------
30 CLERK 950
MANAGER 2850
SALESMAN 5600
9400
29025
已选择13行。
SQL> --打开sql运行时间开关
SQL> set timing on
SQL> /
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
MANAGER 2450
PRESIDENT 5000
8750
20 ANALYST 6000
CLERK 1900
MANAGER 2975
10875
DEPTNO JOB SUM(SAL)
---------- --------- ----------
30 CLERK 950
MANAGER 2850
SALESMAN 5600
9400
29025
已选择13行。
已用时间: 00: 00: 00.56
SQL> select deptno,job,sum(sal)
2 from emp
3 group by rollup(deptno,job);
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
MANAGER 2450
PRESIDENT 5000
8750
20 CLERK 1900
ANALYST 6000
MANAGER 2975
10875
DEPTNO JOB SUM(SAL)
---------- --------- ----------
30 CLERK 950
MANAGER 2850
SALESMAN 5600
9400
29025
已选择13行。
已用时间: 00: 00: 00.50
SQL> set timing off
SQL> break on null
SQL> host cls
SQL> --交集:
SQL> -- 查询工资属于1000~2000这个区间和1500~2500这个区间
SQL> select * from emp where sal between 1000 and 2000
2 intersect
3 select * from emp where sal between 1500 and 2500;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
SQL> --差集：
SQL> --查询工资属于1000~2000,但不属于1500~2500
SQL> ed
已写入 file afiedt.buf
1 select * from emp where sal between 1000 and 2000
2 minus
3* select * from emp where sal between 1500 and 2500
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
SQL> --1.列数相同
SQL> --2. 采用第一个集合的表头
SQL> spool off
