子查询:
跟一个不确定的条件去比,使用子查询
使用子查询
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);
• 括号内的查询叫做子查询,也叫内部查询,先
于主查询执行。
• 子查询的结果被主查询(外部查询)使用
• expr operator包括比较运算符。
–单行运算符:>、=、>=、<、<>、<=
–多行运算符: IN、ANY、ALL
使用子查询
• 子查询可以嵌于以下SQL子句中:
–WHERE子句
–HAVING子句
–FROM子句
使用子查询
• 子查询使用指导
–子查询要用括号括起来
–将子查询放在比较运算符的右边
–对于单行子查询要使用单行运算符
–对于多行子查询要使用多行运算符
单行子查询:一行一列
多行子查询:多行(0,1,。。)一列
多行子查询
–子查询返回记录的条数 可以是一条或多条。
–和多行子查询进行比较时,需要使用多行操作符,多
行操作符包括:
•IN
•ANY
•ALL
–IN操作符和以前介绍的功能一致,判断是否与子查询
的任意一个返回值相同。
IN: IN使用
SELECT empno,ename
FROM emp
WHERE sal = (应该换成IN)
(SELECT MIN(sal)
FROM emp
GROUP BY deptno);
–返回结果
ERROR at line 4:
ORA-01427: single-row subquery returns more than
one row
ANY
–ANY:表示和子查询的任意一行结果进行比较,有一个满足
条件即可。
•< ANY:表示小于子查询结果集中的任意一个,即小于最
大值就可以。
•> ANY:表示大于子查询结果集中的任意一个,即大于最
小值就可以。
•= ANY:表示等于子查询结果中的任意一个,即等于谁都
可以,相当于IN。
ALL
ALL的使用
–ALL:表示和子查询的所有行结果进行比较,每一行必须都
满足条件。
•< ALL:表示小于子查询结果集中的所有行,即小于最小
值。
•> ALL:表示大于子查询结果集中的所有行,即大于最大
值。
•= ALL :表示等于子查询结果集中的所有行,即等于所有
值,通常无意义。
练习2
• 1.查询入职日期比10部门任意一个员工晚的员工姓名、入职日期,不包括10部门员工
-------1
SELECT ename,hiredate
FROM emp
WHERE hiredate >
ANY (SELECT hiredate FROM emp WHERE deptno=10)
AND deptno<>10
• 2.查询入职日期比10部门所有员工晚的员工姓名、入职日期,不包括10部门员工
SELECT ename,hiredate
FROM emp
WHERE hiredate >
ALL (SELECT hiredate FROM emp WHERE deptno=10)
AND deptno<>10
• 3.查询职位和10部门任意一个员工职位相同的员 工姓名,职位,不包括10部门员工
SELECT ename,job
FROM emp
WHERE job=
ANY (SELECT job FROM emp WHERE deptno=10)
AND deptno<>10
--------------------------
----------------------------------
------------------------------------
--------查询每个部门的最低工资的员工的信息
SELECT * FROM emp
WHERE (sal,deptno) IN
(SELECT MIN(sal),deptno FROM emp GROUP BY deptno)-----(要一一对应)
----------
查询比自己部门平均工资高的员工姓名,工资,部门编号,部门平均工资
思路:假设有一个表,保存部门的平均工资和部门编号(嵌套子查询)
SELECT ename,sal, emp.deptno,avgsal
FROM emp,(
SELECT deptno,AVG(sal) avgsal
FROM emp
GROUP BY deptno) xb
WHERE emp.deptno=xb.deptno
AND sal>avgsal
多列子查询:(多行,多列)
练习3
• 1.查询职位及经理和10部门任意一个员工职位及经理相同的员工姓名,职位,不包括10部门员工
SELECT job,mgr FROM emp
WHERE job IN(
SELECT job FROM emp WHERE deptno=10)
AND mgr IN(
SELECT mgr FROM emp WHERE deptno=10)
AND deptno<>10
或者
SELECT mgr,job FROM emp
WHERE (mgr,job) =ANY (
SELECT mgr,job FROM emp WHERE deptno=10)
AND deptno<>10
• 2.查询职位及经理和10部门任意一个员工职位或经理相同的员工姓名,职位,不包括10部门员工
SELECT job,mgr FROM emp
WHERE (job IN(
SELECT job FROM emp WHERE deptno=10)
OR mgr IN(
SELECT mgr FROM emp WHERE deptno=10))
AND deptno<>10
子查询中的空值 :
查询不是经理的员工姓名。
SQL> SELECT ename
2 FROM emp
3 WHERE empno NOT IN
4 (SELECT mgr
5 FROM emp);
no rows selected.
子查询返回的结果中含有空值
上面的SQL语句试图查找出没有下属的雇员,逻辑上,这个SQL语句应该会
返回8条记录,但是却一条也没返回,why?
因为子查询的结果中有一条空值,这条空值导致主查询没有记录返回。这
是因为所有的条件和空值比较结果都是空值。因此无论什么时候只要空值
有可能成为子查询结果集合中的一部分,就不能使用NOT IN 运算符。
改正:
SELECT ename FROM emp WHERE empno NOT IN(
SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL)
在 FROM 子句中使用子查询
• 查询比自己部门平均工资高的员工姓名,工资,
部门编号,部门平均工资
SQL> SELECT a.ename, a.sal, a.deptno, b.salavg
2 FROM emp a, (SELECT deptno, avg(sal) salavg
3 FROM emp
4 GROUP BY deptno) b
5 WHERE a.deptno = b.deptno
6 AND a.sal > b.salavg;
ENAME SAL DEPTNO SALAVG
---------- --------- --------- ----------
KING 5000 10 2600
JONES 2975 20 2335
SCOTT 3000 20 2335
...
6 rows selected.
练习4
• 1.查询比自己职位平均工资高的员工姓名、职位,部门名称,职位平均工资
SELECT ename,e.job,dname ,avgsal
FROM emp e,dept d,(
SELECT job,AVG(sal) avgsal
FROM emp
GROUP BY job
)xb
WHERE e.deptno =d.deptno
AND e.job= xb.job
AND e.sal>xb.avgsal
• 2.查询职位和经理同员工SCOTT或BLAKE完全相同的员工姓名、职位,不包括SCOOT和BLAKE本人。
SELECT e.ename,e.job
FROM emp e,
(SELECT job,mgr FROM emp WHERE ename IN('SCOTT','BLAKE') ) xinbiao
WHERE e.job =xinbiao.job
AND e.mgr =xinbiao.mgr
AND e.ename NOT IN('SCOTT','BLAKE')
或者
SELECT e.ename,e.job
FROM emp e
WHERE (e.job,e.mgr) IN (SELECT job,mgr FROM emp WHERE ename IN('SCOTT','BLAKE') )
AND e.ename NOT IN('SCOTT','BLAKE')
• 3.查询不是经理的员工姓名。
SELECT ename
FROM emp
WHERE empno NOT IN (SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL)
ROWNUM
• ROWNUM
–ROWNUM是一个伪列,伪列是使用上类似于表中的列,而
实际并没有存储在表中的特殊列;
–ROWNUM的功能是在每次查询时,返回结果集的顺序号,
这个顺序号是在记录输出时才一步一步产生的,第一行
显示为1,第二行为2,以此类推。
• ROWNUM
–ROWNUM使用的注意点:
• 1.如下SQL语句,SELECT * FROM EMP WHERE ROWNUM>2;查询
不到任何记录,因为ROWNUM是在记录输出时才生成,且总是
从1开始,所以输出的第一条记录不满足>2的条件,被过滤掉,
第二条的ROWNUM又成了1,又不满足〉2的条件,又被过滤掉,
依此类推,所以永远没有满足条件的记录,返回为空。所以
对于ROWNUM只能执行<、<=运算,不能执行>、>=或一个区间
运算Between..And等
•2.ROWNUM和ORDER BY一起使用时,因为ROWNUM在记录输出时
生成,而ORDER BY子句在最后执行,所以当两者一起使用时,
需要注意ROWNUM实际是已经被排了序的ROWNUM。
TOP-N查询
• TOP-N查询
–Top-N查询主要是实现表中按照某个列排序,输出最大或
最小的N条记录功能。
•Top-N分析语法:
SELECT [列名], ROWNUM
FROM (SELECT [列名]
FROM 表名
ORDER BY Top-N操作的列 ASC|DESC)
WHERE ROWNUM <= N;
•ASC:查询最小的N条记录
•DESC:查询最大的N条记录
练习5
• 1.查询入职日期最早的前5名员工姓名,入职日 期。
SELECT ROWNUM,hiredate
FROM (SELECT ename,hiredate FROM emp
ORDER BY hiredate ASC)
WHERE ROWNUM <=5
• 2.查询工作在CHICAGO并且入职日期最早的前2名 员工姓名,入职日期。
SELECT ROWNUM ,hiredate
FROM (SELECT ename,hiredate
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND loc='CHICAGO'
ORDER BY hiredate ASC)
WHERE ROWNUM <=2
分页
• 分页查询
–在Oracle中,利用ROWNUM的特性,可以实现数据库端的分
页查询,查询语法为:
–1.当未指定需要按照某列排序,语法为:
SELECT b.*
FROM (SELECT ROWNUM rn,[ 1, 2,.... n]
列名 列名 列名
FROM 1,[ 2,... n]
表名 表名 表名
WHERE [ AND ] ROWNUM <= * ) b
条件表达式 目标页数每页记录数
WHERE rn > (目标页数-1)*每页记录数
或
SELECT b.*
FROM (SELECT ROWNUM rn,[ 1, 2,.... n]
列名 列名 列名
FROM 1,[ 2,... n]
表名 表名 表名
[WHERE 条件表达式]) b
WHERE rn <= * and rn > ( -1)*
目标页数每页记录数 目标页数 每页记录数
思考:哪种方式效率高? (第一种)
练习6
• 1.按照每页显示5条记录,分别查询第1页,第2 页,第3页信息,要求显示员工姓名、入职日期 、部门名称。
SELECT b.*
FROM (SELECT ROWNUM rn,ename,hiredate,dname
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND
ROWNUM <=1*5)b
WHERE rn>(1-1)*5
分页
• 分页查询
–2.当指定需要按照某列排序时,语法为:
SELECT *
FROM (SELECT ROWNUM rn, b.*
FROM (SELECT 列名1 [,列名2,....列名n]
FROM 1,[ 2,... n]
表名 表名 表名
[WHERE 子句]
ORDER BY 要排序的列 ASC|DESC ) b
WHERE ROWNUM <= *
目标页数每页记录数
)
WHERE rn > (目标页数-1)*每页记录数 ;
或:
SELECT *
FROM (SELECT ROWNUM rn, b.*
FROM (SELECT 列名1 [,列名2,....列名n]
FROM 1,[ 2,... n]
表名 表名 表名
[WHERE 子句]
ORDER BY 要排序的列 ASC|DESC ) b
)
WHERE rn <= * and rn > ( -1)* ;
目标页数每页记录数 目标页数 每页记录数
练习7
• 1.按照每页显示5条记录,分别查询工资最高的第1页,第2页,第3页信息,要求显示员工姓名 、入职日期、部门名称、工资。
第一页:
SELECT *
FROM
(SELECT ROWNUM rn,b.*
FROM (SELECT ename,hiredate,dname,sal
FROM emp e,dept d
WHERE e.deptno=d.deptno
ORDER BY hiredate DESC) b
WHERE ROWNUM <=1*5)
WHERE rn >(1-1)*5
第二页:
SELECT *
FROM
(SELECT ROWNUM rn,b.*
FROM (SELECT ename,hiredate,dname,sal
FROM emp e,dept d
WHERE e.deptno=d.deptno
ORDER BY hiredate DESC) b
WHERE ROWNUM <=2*5)
WHERE rn >(2-1)*5
第三页:
SELECT *
FROM
(SELECT ROWNUM rn,b.*
FROM (SELECT ename,hiredate,dname,sal
FROM emp e,dept d
WHERE e.deptno=d.deptno
ORDER BY hiredate DESC) b
WHERE ROWNUM <=3*5)
WHERE rn >(3-1)*5