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;