目录
定义
SELECT中的子查询
FROM中的子查询
HAVING中的子查询
INSERT中的子查询
UPDATE中的子查询
DELETE语句中的子查询
多行子查询
IN
ALL
ANY
规则
定义
子查询(Sub Query)又叫(Inner Query),嵌套查询(Nested Query),是嵌套在其他SQL查询的WEHERE子句中的查询,用于为主查询返回其所需要的数据,或对检索数据进行进一步限制。
子查询可以应用在FROM、WHERE、HAVING子句中,也可在SELECT、INSERT、UPDATE、DELETE语句中,和=、<、>、>=、<=、IN、BETWEEN等运算符号一起使用
SELECT中的子查询
查看emp表
SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------- ----- ------------------- ----- ----- ------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 |
查询emp表中谁的工资比BLAKE多
SQL> select * from emp where sal>( select * from emp where ename='BLAKE' ); select * from emp where sal>(select * from emp where ename='BLAKE') * ERROR at line 1: ORA-00913: too many values |
报错了
子查询的SELECT子句中只能有一个列(如果主查询中有多个列用于与子查询选中的列相比较时除外)
SQL> select * from emp where sal>( select sal from emp where ename='BLAKE' ); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------- ----- ------------------- ----- ----- ------- 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 |
在圆括号中查询得到一个唯一结果,把它作为外层查询的一个参数,因此子查询只能在圆括号中
查询和MARTIN在同一部门且工资比他多的员工信息
SQL> select * from emp where deptno=( select deptno from emp where ename='MARTIN' ) and sal>( select sal from emp where ename='MARTIN' ); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------- ----- ------------------- ----- ----- ------- 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 |
这也属于单行子查询
查询那个员工的工资最多(不用考虑并列最多的问题,因为最大值只有一种)
SQL> select * from emp where sal=(select max(sal) from emp); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------- ----- ------------------- ----- ----- ------- 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 |
这依然数据单行子查询
那如果子查询中没有返回数据时呢
查询比KING奖金多的员工信息
SQL> select * from emp where comm>(select comm from emp where ename='KING'); no rows selected |
怎么没有信息?
当子查询没有得到结果时,其返回值为 null,外层的查询中的 where 条件 comm>null,则 where 不成立,则没有数据返回 comm 存在 null 值,那么在比较两个 null 值结果依然是 null,所以还是没 有数据返回
那么怎么才能查出来呢,用nvl将空值转换成0即可
SQL> select * from emp where comm>(select nvl(comm,0)from emp where ename='KING'); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------- ----- ------------------- ----- ----- ------- 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 |
看来只有做销售才能有奖金。
FROM中的子查询
查询NEW YOEK
部门工作的员工信息和个数
SQL> select * from ( select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno ) where loc='NEW YORK'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC ------ ------ --------- ----- ------------------- ----- ---- ---------- -------- 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 ACCOUNTING NEW YORK 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 ACCOUNTING NEW YORK SQL> select count(*) from ( select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno ) where loc='NEW YORK'; COUNT(*) ---------- 3 |
HAVING中的子查询
查询比30号部门最低工资还低工资低的部门编号
SQL> select deptno from emp group by deptno having min(sal)>( select min(sal) from emp where deptno=30 ); DEPTNO ------- 10 |
查询那个部门的平均工资最高
SQL> select dname from dept where deptno=( select deptno from emp group by deptno having avg(sal)=( select max(avg(sal)) from emp group by deptno ) ); DNAME ---------- ACCOUNTING |
INSERT中的子查询
insert中的子查询,用于将子查询的数据插入到其他表中,子查询的数据可以被任何字符、日期或者函数所修饰
创建表tb_ins01定义参照emp数据不要
SQL> create table tb_ins01 as select * from emp where 1=2; Table created. SQL> select * from tb_ins01; no rows selected SQL> desc tb_ins01; Name Null? Type ----------------------- -------- ---------------- EMPNO NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) |
将emp中在NEW YORK工作的员工信息复制到tb_ins01中
SQL> insert into tb_ins01 select empno,ename,job,mgr,hiredate,sal,comm,deptno from ( select empno,ename,job,mgr,hiredate,sal,comm,e.deptno,loc from emp e,dept d where e.deptno=d.deptno ) where loc='NEW YORK'; 3 rows created. SQL> select * from tb_ins01; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ------ --------- ----- ------------------- ----- ---- ------- 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 |
UPDATE中的子查询
创建emp表的复制tb_upd01
将emp表中1982年之前出生的员工工资增加1.2倍更新到 tb_upd01中
SQL> create table tb_upd01 as select * from emp; Table created. SQL> select * from tb_upd01; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ------ --------- ----- ------------------- ----- ---- ------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 14 rows selected. SQL> update tb_upd01 set sal=sal*1.2 where empno in (select empno from emp where hiredate<to_date('1982-01-01')); 11 rows updated. SQL> select * from tb_upd01; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------- ----- ------------------- ----- ----- ------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 960 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1920 300 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1500 500 30 7566 JONES MANAGER 7839 1981-04-02 00:00:00 3570 20 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1500 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 3420 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2940 10 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7839 KING PRESIDENT 1981-11-17 00:00:00 6000 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1800 0 30 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 1140 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3600 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 14 rows selected. |
DELETE语句中的子查询
将emp表中1982年之前出生的员工在tb_upd01表中删除(涨完工资就开除!!)
SQL> delete from tb_upd01 where empno in (select empno from emp where hiredate<to_date('1982-01-01')); 11 rows deleted. SQL> select * from tb_upd01; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------- ----- ------------------- ----- ----- ------- 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 |
多行子查询
其实刚才的例子里已经有了,就是子查询返回的值有多个
查看每个部门工资最少的员工信息
SQL> select * from emp where sal=(select min(sal) from emp group by deptno); select * from emp where sal=(select min(sal) from emp group by deptno) * ERROR at line 1: ORA-01427: single-row subquery returns more than one row |
为什么会报错,因为子查询中的返回值有多个无法与主查询一一匹配
因此对于多行子查询需要应用多行运算符(IN、ALL、ANY)
IN:表示外层条件数据集合中的任意成员
ANY:表示外层条件只满足子查询的任意一个值即可
ALL:表示外层条件必须满足子查询的全部值
IN
SQL> select * from emp where sal in (select min(sal) from emp group by deptno); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------- ----- ------------------- ----- ----- ------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 |
ALL
查询工资比MANAGER工资还多并且工作岗位不是MANAGER的员工信息
SQL> select * from emp where sal >all(select sal from emp where job='MANAGER'); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------- ----- ------------------- ----- ----- ------- 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10< |
sal < all() 表示sal小于其中的最小值
sal > all() 表示sal大于其中的最大值
ANY
查询比某个部门的最高工资还多的员工信息
SQL> select * from emp where sal>any(select max(sal) from emp group by deptno); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------- ----- ------------------- ----- ----- ------- 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 |
规则
子查询必须在圆括号中
子查询不能使用ORDER BY,但是可以使用GROUP BY起来起到相同的作用
返回多行数据的子查询只能同多值操作符号一同使用,如IN
子查询的SELECT列表中不能包含任何对BLOB、ARRAY、CLOB、NCLOB值的引用
子查询不能直接用在集合函数中
BETWEEN不能和子查询一同使用,但是可以用在子查询中