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;