SQL> --查询工资比SCOTT高的员工信息
SQL> --1. SCOTT的工资
SQL> select sal from emp where ename='SCOTT';
SAL
----------
3000
SQL> --2. 查询比3000高的员工
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 * from emp where sal > (select sal from emp where ename='SCOTT');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10
SQL> /*
SQL> 注意的问题：1
SQL> 1、括号
SQL> 2、合理的书写风格
SQL> 3、可以在主查询的where select having from 后面使用子查询
SQL> 4、不可以在group by使用子查询
SQL> 5、强调from后面的子查询
SQL> 6、主查询和子查询可以不是同一张表；只有子查询返回的结果 主查询可以使用 即可
SQL> 7、一般不在子查询中排序；但在top-n分析问题中 必须对子查询排序
SQL> 8、一般先执行子查询，再执行主查询；但相关子查询例外
SQL> 9、单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
SQL> 10、子查询中的null
SQL> */
SQL> --3、可以在主查询的where select having from 后面使用子查询
SQL> select empno,ename,sal,(select job from emp where empno=7839) 第四列
from emp;
EMPNO ENAME SAL 第四列
---------- ---------- ---------- ---------
7369 SMITH 800 PRESIDENT
7499 ALLEN 1600 PRESIDENT
SQL> --4、强调from后面的子查询
SQL> --查询员工信息：员工号 姓名 月薪 年薪
1 select * from (select empno,ename,sal,sal*12 annsal from emp)
EMPNO ENAME SAL ANNSAL
---------- ---------- ---------- ----------
7369 SMITH 800 9600
7499 ALLEN 1600 19200
SQL> --6、主查询和子查询可以不是同一张表；只有子查询返回的结果 主查询可以使用 即可
SQL> --查询部门名称是SALES的员工
SQL> select * from emp where deptno=(select deptno from dept where 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
SQL> select e.* from emp e,dept d 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
SQL> --SQL优化 3、尽量使用多表查询
SQL> --in 在集合中
SQL> --查询部门名称是SALES和ACCOUNTING的员工
SQL> select * from emp where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING');
SQL> select e.* from emp e,dept d where e.deptno=d.deptno and (d.dname='SALES' or d.dname='ACCOUNTING');
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
SQL> --any: 和集合中的任意一个值比较-有一个满足条件就ok
SQL> --查询工资比30号部门任意一个员工高的员工信息
SQL> select * from emp where sal > any (select sal from emp where deptno=30);
SQL> select * from emp where sal > (select min(sal) from emp where deptno=30)
助解：大于最小值,小于最大值
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
SQL> --all:和集合中的所有值比较
SQL> --查询工资比30号部门所有员工高的员工信息
SQL> select * from emp where sal > all (select sal from emp where deptno=30);
SQL>select * from emp where sal > (select max(sal) from emp where deptno=30)
助解：小于最小值,大于最大值
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
总结：any 就是匹配集合中的任意一个就满足条件了；而 all 要跟集合中的每一个作比较，每一个都满足条件才为真。
SQL> --多行子查询中的null--子查询查询出来的某行有为null情况下
SQL> select * from emp where empno not in (select mgr from emp);
未选定行
SQL> --查询不是老板的员工信息
SQL> select * from emp where empno in (select mgr from emp)
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7902 FORD ANALYST 7566 03-12月-81 3000 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7839 KING PRESIDENT 17-11月-81 5000 10
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
SQL> select * from emp where empno not in (select mgr from emp where mgr is not null);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7369 SMITH CLERK 7902 17-12月-80 800 20
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30