Oracle数据库知识梳理---续(子查询)

6.子查询

子查询的本质 多个select语句的嵌套

主查询 select sal from emp where ename = 'SCOTT'; 子查询 select * from emp where sal > 3000; 将两步并为一步走 select * from emp where sal > (select sal from emp where ename = 'SCOTT');

子查询知识体系搭建 1.合理的书写风格 2.子查询外面的()不能省略 3.子查询和主查询可以是不同的表,只要子查询返回的结果,主查询可以使用即可 4.可以放置子查询的位置 elect ———可以放置,只能存放单行子查询,不能是多行子查询 from ————–可以放置 where ————可以放置 group by —–不能 having ———-可以 order by ———不能

查询部门名称是SALES的员工信息 select * from emp where dmptno = (select deptno from dept where dname = 'SALES'); select e.* from emp e,dept d where e.deptno = d.deptno and d.ename = '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 查询员工的姓名和薪水 select * from (select ename, sal from emp); ENAME SAL ----------- ------ SMITH 800 ALLEN 1600 WARD 1250 JONES 2975 MARTIN 1250 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 TURNER 1500 ADAMS 1100 JAMES 950 FORD 3000 MILLER 1300 查询部门名称SALES和ACCOUNTING的员工信息 select * from emp where deptno in (select deptno from dept d where dname = 'SALES' or 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 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7839 KING PRESIDENT 17-11月-81 5000 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7900 JAMES CLERK 7698 03-12月-81 950 30 7934 MILLER CLERK 7782 23-1月 -82 1300 10

ANY 和集合中的任意一个值比较 ALL 和集合中的所有值比较

单行操作符对应单行子查询，多行操作符对应多行子查询。 select * from emp where sal > all(select sal from emp where deptno = 30) select * from emp where sal > (select max(sal) from emp where deptno = 30) 效果相同... 查询不是经理的员工信息 select* from emp where deptno in (select mgr from emp ); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 7902 FORD ANALYST 7566 03-12月-81 3000 20 select * from emp where empno not in (select mgr from emp where mgr is not null) EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 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 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7900 JAMES CLERK 7698 03-12月-81 950 30 7934 MILLER CLERK 7782 23-1月 -82 1300

集合运算 讲集合运算,实质上是讲集合运算的操作符

查询部门号是10 和 20的员工信息 方法1 select * from emp where deptno in (10, 20); 方法2 select * from emp where deptno=10 or deptno=20; 方法3 select * from emp where deptno = 10 union select * from emp where deptno = 20;