oracle 之 sql 子查询
子查询包括：单行子查询，多行子查询，多列子查询，关联子查询，嵌套子查询
1）单行子查询：
就是在子查询中的结果只能是1行或者是没有结果
eg：
SCOTT@orcl#select empno,ename,job,sal from emp where empno=(select empno from emp where empno=7000);
EMPNO ENAME JOB SAL
---------- -------------------- ------------------ ----------
7000 MILLER CLERK 2000
SCOTT@orcl#select empno,ename,job,sal from emp where empno=(select * from emp where empno>7000);
select empno,ename,job,sal from emp where empno=(select * from emp where empno>7000)
*
第 1 行出现错误:
ORA-00913: 值过多
SCOTT@orcl# 单行子查询可以使用比较运算符，而且可以和having进行使用
eg：查看部门平均工资低于部门组平均工资最大值的部门号和平均工资
SCOTT@orcl#select deptno,avg(sal) from emp group by deptno having avg(sal)<(select max(avg(sal)) from emp group by deptno);
DEPTNO AVG(SAL)
---------- ----------
30 1566.66667
20 2375
SCOTT@orcl#另外子查询可以应用与from语句中
eg：高于部门平均工资的雇员信息
SCOTT@orcl#r
1 select * from emp ,(select deptno, avg(sal) vg from emp group by deptno) de
2* where emp.deptno=de.deptno and sal>de.vg
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO VG
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 30 1566.66667
7566 JONES MANAGER 7839 02-4月 -81 2975 20 20 2375
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 30 1566.66667
7788 SCOTT ANALYST 7566 19-4月 -87 4000 20 20 2375
7839 KING PRESIDENT 17-11月-81 5000 10 10 3150
7902 FORD ANALYST 7566 03-12月-81 3000 20 20 2375
已选择6行。
SCOTT@orcl#
注意：子查询语句中不能出现order by字句
2）多行子查询
多行子查询就是查询中存在in，any，all词组进行的查询，注意：any表示任意一个数值就满足，all表示符合zichaxun的全部。但是any和all必须和比较运算符联合使用。
eg：
SCOTT@orcl#r
1* select empno,ename,sal from emp where sal in (1600,2975,5000)
EMPNO ENAME SAL
---------- -------------------- ----------
7499 ALLEN 1600
7566 JONES 2975
7839 KING 5000
SCOTT@orcl#
SCOTT@orcl#select empno,ename,sal from emp where sal>any(1600,2975,5000);
EMPNO ENAME SAL
---------- -------------------- ----------
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 4000
7839 KING 5000
7902 FORD 3000
7000 MILLER 2000
已选择7行。
SCOTT@orcl#select empno,ename,sal from emp where sal>all(1600,2975,5000);
未选定行
SCOTT@orcl#select max(sal) from emp;
MAX(SAL)
----------
5000
SCOTT@orcl#select empno,ename,sal from emp where sal>all(1600,2956,2900);
EMPNO ENAME SAL
---------- -------------------- ----------
7566 JONES 2975
7788 SCOTT 4000
7839 KING 5000
7902 FORD 3000
SCOTT@orcl#
3）多列子查询
eg：
查看每个部门中在该部门最低的工资雇员信息
SCOTT@orcl#select empno,ename,sal from emp where (deptno,sal) in (select deptno,min(sal) from emp group by deptno);
EMPNO ENAME SAL
---------- -------------------- ----------
7900 JAMES 950
7369 SMITH 800
7000 MILLER 2000
SCOTT@orcl#4）编写关联子查询
关联子查询会应用外部desql语句中的一列或是多列。
eg：
SCOTT@orcl#select empno,deptno,ename,sal from emp outer where sal>
2 (select avg(sal) from emp inner where inner.deptno=outer.deptno);
EMPNO DEPTNO ENAME SAL
---------- ---------- -------------------- ----------
7499 30 ALLEN 1600
7566 20 JONES 2975
7698 30 BLAKE 2850
7788 20 SCOTT 4000
7839 10 KING 5000
7902 20 FORD 3000
已选择6行。
SCOTT@orcl#
SCOTT@orcl#select * from emp where exists (select loc from dept where emp.deptno=dept.deptno and dept.loc='NEW YORK');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7839 KING PRESIDENT 17-11月-81 5000 10
7000 MILLER CLERK 2000 23-1月 -82 2000 10
SCOTT@orcl#
SCOTT@orcl#select * from emp where not exists (select loc from dept where emp.deptno=dept.deptno and dept.loc='NEW YORK');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-12月-81 950 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7788 SCOTT ANALYST 7566 19-4月 -87 4000 20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7369 SMITH CLERK 7902 17-12月-80 800 20
已选择11行。
SCOTT@orcl#
5）嵌套子查询
eg：
SCOTT@orcl#select deptno,avg(sal) from emp group by deptno having avg(sal)>
2 (select max(avg(sal)) from emp
3 where deptno in
4 (select deptno from dept where deptno>10)
5 group by deptno);
DEPTNO AVG(SAL)
---------- ----------
10 3150
SCOTT@orcl#6）子查询中的update和delete语句：
eg：
SCOTT@orcl#update emp set (ename,sal)=(select ename,sal from emp where sal=(select max(sal) from emp));
已更新14行。
SCOTT@orcl#select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7369 KING CLERK 7902 17-12月-80 5000 20
7499 KING SALESMAN 7698 20-2月 -81 5000 300 30
7521 KING SALESMAN 7698 22-2月 -81 5000 500 30
7566 KING MANAGER 7839 02-4月 -81 5000 20
7654 KING SALESMAN 7698 28-9月 -81 5000 1400 30
7698 KING MANAGER 7839 01-5月 -81 5000 30
7782 KING MANAGER 7839 09-6月 -81 5000 10
7788 KING ANALYST 7566 19-4月 -87 5000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 KING SALESMAN 7698 08-9月 -81 5000 0 30
7876 KING CLERK 7788 23-5月 -87 5000 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7900 KING CLERK 7698 03-12月-81 5000 30
7902 KING ANALYST 7566 03-12月-81 5000 20
7000 KING CLERK 2000 23-1月 -82 5000 10
已选择14行。
SCOTT@orcl#
SCOTT@orcl#delete from emp where empno=(select max(empno) from emp);
已删除 1 行。
SCOTT@orcl#r
1* delete from emp where empno=(select max(empno) from emp)
已删除 1 行。
SCOTT@orcl#select * from emp;
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
7566 JONES MANAGER 7839 02-4月 -81 2975 20
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
7788 SCOTT ANALYST 7566 19-4月 -87 4000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7000 MILLER CLERK 2000 23-1月 -82 2000 10
已选择12行。
SCOTT@orcl#
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
