Oracle数据库基础SQL语句(三)—高级查询(子查询)
找出比SCOTT工资高的员工信息
问题分析:
SELECT *
from EMP
WHERE SAL>(SELECT SAL
FROM EMP
WHERE ENAME='SCOTT');
子查询需要注意的十个问题
1. 子查询语法中的小括号
必需要写小括号,不写就会报错
2. 子查询的书写风格
该换行的换行,该缩进的缩进
3. Oracle可以使用子查询的位置:where select having from
- WHERE:
SELECT *
from EMP
WHERE SAL>(SELECT SAL
FROM EMP
WHERE ENAME='SCOTT');
- SELECT
注:在select中使用子查询时,子查询只能是单行查询(即返回一条数据)
SELECT ENAME,JOB,mgr,(SELECT JOB FROM EMP WHERE EMPNO='7839') 第四列
FROM EMP;
- HAVING
SELECT DEPTNO,AVG(SAL)
from EMP
GROUP BY DEPTNO
HAVING AVG(SAL)>(SELECT MAX(SAL) //此处不能用where,因为where中不能包含组函数
FROM EMP
WHERE DEPTNO=30);
- FROM
把子查询的结果看成一张表,在Oracle数据库中很常用
SELECT *
FROM (SELECT DEPTNO,sal FROM EMP);
4. 不可以使用子查询的位置:GROUP BY
语法规定,不可以用,group by处不能出现子查询语句
5. from后面的子查询
from后面跟的一般是一张表名,而子查询的结果也可以看成一张表
SELECT *
FROM (SELECT DEPTNO,sal,sal*12 年薪 from EMP);
6. 主查询和子查询不是同一张表
实例:查询部门名称是“sales”的员工信息
SELECT *
from EMP
WHERE DEPTNO=(SELECT DEPTNO
from dept
where DNAME='SALES');
思考:此处涉及到了多张表,那可不可以用多表查询来实现呢?
SELECT e.*
from emp e,DEPT d
WHERE e.DEPTNO=d.DEPTNO and d.DNAME='SALES'//最少有n-1个连接条件,此处就用了两个
思考:既然都可以实现,那哪个比较好呢?
理论上来说多表好,只是理论上,因为子查询有两个from,但多表查询涉及到笛卡尔集的问题,所以具体情况,具体分析
7. 一般子查询中不使用排序,打包在Top-N分析问题中,必须实用排序
思考:什么是Top-N问题?
示例:找到工资表中工资最高的前三位
首先先介绍下Oracle中的*伪列:*rownum
SELECT rownum,ENAME,EMPNO,SAL from EMP;
有了行号我们的问题就变得简单了起来,只需要子查询的结果进行降序排序即可
注:行号永远按照默认的顺序生成(下面这条语句虽然进行了排序,但行号并没有改变)
为什么默认排序,涉及到Oracle数据库的默认表问题
SELECT rownum,ENAME,EMPNO,SAL from EMP ORDER BY SAL DESC;
我们工资的降序排序是没有问题的,子查询登场,让行号默认成此排序就好了
SELECT rownum,ENAME,EMPNO,SAL from (SELECT *
from EMP
ORDER BY SAL DESC)
WHERE ROWNUM <=3;
注:rownum只能使用<,<=不能使用>,>=(Oracle的分页查询文中涉及)
8. 一般先执行子查询,在执行主查询,但相关子查询例外
什么是相关子查询?
示例:找到员工表中薪水大于本部门平均薪资的员工
select ENAME,EMPNO,SAL,(SELECT AVG(SAL)from EMP WHERE DEPTNO = e.DEPTNO) as avgsal
//此处的子查询查的就是员工部门的平均工资,所以复制过来起个别名得到这一列即可
from EMP e
//设置参数e 传递给这上下两个子查询
where SAL>(SELECT AVG(SAL)from EMP WHERE DEPTNO = e.DEPTNO);
9. 单行子查询只能使用单行操作符,多行子查询只能使用多行操作符
单行子查询:子查询只返回一条记录
多行子查询:两行或多行数据
SELECT *
from EMP
//此处都是单行操作符,进行是单行子查询
WHERE JOB=(SELECT JOB from EMP where EMPNO=7566) AND
SAL>(SELECT SAL from EMP WHERE EMPNO=7782);
下面的语句中=是但行操作符,而子查询的是每个部门的最低工资,不止一条数据,所以爆错
多行操作符IN
示例:查询部门是SALES和ACCOUNTING的员工信息
SELECT *
FROM EMP
WHERE DEPTNO in (SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES'or DNAME = 'ACCOUNTING');
思考:此处是否可以用多表查询来做呢?答案是可以的
SELECT *
from emp e,DEPT d
WHERE e.DEPTNO=d.DEPTNO and(d.DNAME = 'SALES' OR d.DNAME = 'ACCOUNTING');
多行操作符ANY
示例:查询工资比30号部门任意一个员工高的员工信息
SELECT *
from emp
WHERE sal>any(SELECT SAL FROM EMP WHERE DEPTNO=30);
思考:大于任意一个值,不就是大于最大值吗,可以把多行查询变成单行子查询
SELECT *
from emp
WHERE sal>(SELECT MIN(SAL) FROM EMP WHERE DEPTNO=30);
多行操作符ALL
示例:查询工资比30号部门所有员工高的员工信息(跟上边不一样,注意仔细观看)
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);
注:在实际问题中,最大还是最小值,具体问题具体分析
10. 子查询的空值问题
多行子查询的空值问题
判断空值不能用等号,所以a!=null永远为假
正确写法,加个不为空的条件
SELECT *
from EMP
WHERE EMPNO not in (SELECT MGR FROM EMP WHERE mgr is not NULL);