oracle实验20：子查询
子查询
语法：
SELECT 查询列
FROM表名
WHERE 列名操作符
(SELECT查询列
FROM表名);
-括号内的查询叫做子查询（Subquery）或者内部查询（Inner Query），
-外面的查询叫做主查询（Main query）或外部查询（Outer query）。
实验20：子查询
谁的工资最多？
1.查最大工资值
SQL> select max(sal) from emp;
MAX(SAL)
----------
5000
2.找到最大工资值的人
SQL> select ename from emp where sal=5000;
ENAME
----------
KING
将两个语句写在一起
SQL> select ename from emp where sal=(select max(sal) from emp);
ENAME
----------
KING
简单子查询
- 先于主查询执行；
- 主查询调用了子查询的结果；
- 注意列的个数和类型要匹配；
- 子查询返回多行要用多行关系运算操作；
- 子查询需要写在括号中；
- 子查询需要写在运算符的右端；
- 子查询可以写在WHERE，HAVING，FROM子句中；
- 子查询中通常不写ORDER BY子句。
单行子查询
子查询返回的记录有且只有一条。单行子查询要求使用单行操作符。
即：<、>、>=、<=、=、<>
查询工资总和高于10号部门工资总和的部门
SQL> select deptno,sum(sal)
from emp
group by deptno
having sum(sal)>
(select sum(sal) from emp where deptno=10);
DEPTNO SUM(SAL)
---------- ----------
30 9400
多行多列子查询
多行子查询，子查询返回记录的条数可以是一条或多条。多行子查询需要使用多行操作符。常用的多行操作符包括：
–IN
–ANY
–ALL
IN操作符 判断是否与子查询的任意一个返回值相同。返回的结果可以是一条或多条。
查询每个部门的最大工资
SQL> select deptno,ename,sal from emp
where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);
DEPTNO ENAME SAL
---------- ---------- ----------
30 BLAKE 2850
20 FORD 3000
10 KING 5000
ANY 表示任意的。
SQL> select ename,sal from emp where sal<any(1000,2000);
ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
MARTIN 1250
TURNER 1500
JAMES 950
MILLER 1300
已选择7行。
小于2000就可以。
ALL 表示所有的。
SQL> select ename,sal from emp where sal<all(1000,2000);
ENAME SAL
---------- ----------
SMITH 800
JAMES 950
必须小于1000。
小于any，小于最大值；大于any，大于最小值。
小于all，小于最小值；大于all，大于最大值。
SQL> select avg(sal) from emp group by deptno;
AVG(SAL)
----------
1567
2259.33333
2916.66667
SQL> select ename,sal,deptno from emp
where sal<any(select avg(sal) from emp group by deptno);
ENAME SAL DEPTNO
---------- ---------- ----------
SMITH 801 20
JAMES 950 30
WARD 1250 30
MARTIN 1250 30
MILLER 1300 10
TURNER 1501 30
ALLEN 1601 30
CLARK 2450 10
BLAKE 2850 30
已选择9行。
SQL> select ename,sal,deptno from emp
where sal>any(select avg(sal) from emp group by deptno);
ENAME SAL DEPTNO
---------- ---------- ----------
KING 5000 10
FORD 3002 20
JONES 2975 20
BLAKE 2850 30
CLARK 2450 10
ALLEN 1601 30
已选择6行。
SQL> select ename,sal,deptno from emp
where sal<all(select avg(sal) from emp group by deptno)
ENAME SAL DEPTNO
---------- ---------- ----------
SMITH 801 20
WARD 1250 30
MARTIN 1250 30
TURNER 1501 30
JAMES 950 30
MILLER 1300 10
已选择6行。
FROM语句中子查询
找出比本部门工资高的员工
SQL> select ename,e.deptno,sal,asal from emp e,
(select deptno,avg(sal) asal from emp group by deptno) a
where e.deptno=a.deptno and sal>asal;
ENAME DEPTNO SAL ASAL
---------- ---------- ---------- ----------
ALLEN 30 1600 1566.66667
JONES 20 2975 2258.33333
BLAKE 30 2850 1566.66667
KING 10 5000 2916.66667
FORD 20 3000 2258.33333
a为视图，使用别名asal是因为表达式不能当列的名称，别名的本质是非法的合法化。
子查询中空值问题
SQL> SELECT a.ename, a.sal FROM emp a WHERE a.empno NOT IN(SELECT b.mgr FROM emp b);
未选定行
出现这种情况的原因有两个：
-子查询中返回值中包含有空值；
-NOT IN操作符对空值不忽略。
NOT IN操作符相当于<> ALL，即除了列表值的所有值，就包括了空值NULL，结果即为空。
相互关联的子查询
相关子查询中，内部查询需引用外部查询的列，进行交互判断。相关子查询的执行方式是一行行操作。外部查询每执行一行操作，内部查询都要执行一次。
SQL> select ename,sal,deptno
from emp o
where sal>(select avg(sal) from emp where deptno=o.deptno);
ENAME SAL DEPTNO
---------- ---------- ----------
ALLEN 1600 30
JONES 2975 20
BLAKE 2850 30
KING 5000 10
FORD 3000 20
先运行主查询，得到第一行，将DEPTNO传入到子查询，由于查询求出avg(sal)，再判定主查询的行是否符合查询的条件。
执行计划是将子查询看作视图的关联，这叫做sql的自动改写。
EXISTS和NOT EXISTS操作符
相关子查询还可使用EXISTS和NOT EXISTS操作符。
EXISTS判断存在与否。具体操作如下：
–子查询中如果有记录找到，子查询语句不会继续执行，返回值为TRUE；
–子查询中如果到表的末尾也没有记录找到，返回值为FALSE。
EXISTS子查询并没有确切记录返回，只判断是否有记录。而且只要找到相关记录，子查询就不需要再执行，然后再进行下面的操作。这样大大提高了语句的执行效率。
NOT EXISTS正好相反，判断子查询是否没有返回值。
-如果没有返回值，表达式为真，
-如果找到一条返回值，则为假。
NOT EXISTS操作符因为运算方法与NOT IN不同，只会返回TRUE或FALSE，不会返回空值，所以不需要考虑子查询去除空值的问题。
SQL> select ename,empno,mgr from emp o where exists(select 1 from emp where mgr=o.empno);
ENAME EMPNO MGR
---------- ---------- ----------
JONES 7566 7839
BLAKE 7698 7839
CLARK 7782 7839
KING 7839
FORD 7902 7566
练习
1.列出没有下级的员工。
答案： select ename,empno,mgr from emp o where not exists(select 1 from emp where mgr=o.empno)；
2.每个部门工资最少的员工。
答案： select deptno, ename,sal
from emp
where (deptno,sal) in (select deptno,min(sal) from emp group by deptno);
3.所有比平均工资高的员工。
返回目录 http://www.cnblogs.com/downpour/p/3155689.html
