子查询
子查询
语法:
SELECT A FROM B WHERE C GROUP BY D HAVING E ORDER BY F;
- 单行单列
SELECT EMPNO FROM EMP WHERE SAL = 800;
→
- 单行多列
SELECT EMPNO,ENAME,JOB FROM EMP WHERE SAL= 800;
→
- 多行单列
SELECT EMPNO FROM EMP;
→
- 多行多列
SELECT EMPNO,ENAME,JOB FROM EMP;
→
SELECT 子查询
总结:
- 多列不能作为SELECT子查询
- 多行不能作为SELECT子查询
FROM子查询
总结:
- FROM 子查询后面可以跟所有的子查询
WHERE 子查询
应用:
查询 SMITH所在部门的部门平均薪资
→
SELECT AVG(SAL) FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME='SMITH');
多行多列的WHERE子查询:
SELECT * FROM EMP WHERE (EMPNO,ENAME,SAL) IN (SELECT EMPNO,ENAME,SAL FROM EMP);--这里的括号都不能省略不写昂~
总结:
- WHERE 子查询全适用,只是适用于不同的条件
GROUP BY子查询
总结:
该子查询不可用
HAVING 子查询
总结:
HAVING 子查询全适用,只是适用于不同的条件
ORDER BY 子查询
总结:
ORDER BY 子查询只适用于单行单列子查询
注意:若子查询中含有函数,需得给函数列起个别名,才能在外层查询语句中被引用
应用:
查询部门平均薪资
→
SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO;
再对部门平均薪资查询其平均值
→
SELECT AVG(AVG(SAL)) FROM (SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO);--报错,SAL无效字符
因为AVG(AVG(SAL))
中的SAL
在表SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO
中不存在,所以我们可以给它起个别名,变成
→
SELECT AVG(AVG(SAL)) FROM (SELECT AVG(SAL) "SAL" FROM EMP GROUP BY DEPTNO);
总结:什么时候可能会用到子查询呢?
1.当一步不能查询出准确数据时
2.当给定的条件不明确且是随某列变化时
3.当题目的条件是一列随着另一列的值变化时
相关子查询:
子查询语句的执行受主查询语句的影响,子查询语句不能独立运行
EXISTS+子查询:
一行一行地,当子查询语句有结果返回时,执行主查询语句
NOT EXISTS+子查询:
一行一行地,当子查询语句返回空值时,主查询语句返回空值
不相关子查询
子查询语句的执行不受主查询语句的影响,子查询语句能独立运行
IN+子查询:
符合子查询语句中结果,即返回查询结果
语法:
SELECT COLUMN1,COLUMN2... FROM TABLE_NAME WHERE [NOT] EXISTS(查询语句)
应用:
- 相关非相关的相互转化
SELECT * FROM EMP A
WHERE EXISTS (
SELECT DEPTNO FROM EMP
WHERE SAL>2000
AND DEPTNO<>10
AND A.DEPTNO = DEPTNO--这一行非常重要
);
←
查询工资大于2000并且工作部门不为20号的人员的全部信息
注意这里不是直接执行
SELECT * FROM EMP A
而是一行一行地执行SELECT * FROM EMP A
所以最后的结果不是单纯的SELECT * FROM EMP A
的结果,
而是过滤出了SELECT * FROM EMP A
中
AND DEPTNO<>10 AND A.DEPTNO = DEPTNO
的结果,所以最后的结果是10条
而不是SELECT * FROM EMP
的14条
- 想要用EXISTS语句实现过滤需要考虑以下几点:
1.要明确比较的列
2.把比较的列引入子查询中
3.在子查询中将引入的比较列和子查询中列联系起来
4.将两个条件联系以后,根据EXISTS独有的规则决定该条数据是否返回到结果集中
应用:查询工资全部大于2000的部门的员工信息
→
SELECT * FROM EMP WHERE DEPTNO IN(SELECT DISTINCT DEPTNO FROM EMP WHERE SAL >2000 MINUS SELECT DISTINCT DEPTNO FROM EMP WHERE SAL IS NULL OR SAL <=2000);
↑↓
老师补充答案:
SELECT * FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING MIN(SAL)>2000);
相关与不相关子查询的相互转化的重要习题:
--1.查询在纽约工作的员工的编号,姓名,以及职位
--不相关子查询:
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC IN ('NEW YORK'));--0.017s
--相关子查询:
SELECT EMPNO, ENAME, JOB
FROM EMP "E"
WHERE EXISTS (SELECT DEPTNO
FROM DEPT
WHERE LOC IN ('NEW YORK')
AND E.DEPTNO = DEPTNO);--0.013s
--2.查询工资大于公司平均工资的员工的姓名及职位
--相关子查询:
SELECT ENAME, JOB, SAL, round((SELECT AVG(SAL) FROM EMP), 2) "公司平均工资"
FROM EMP
WHERE SAL > (SELECT AVG(SAL) FROM EMP);--0.021s
--不相关子查询:
SELECT ENAME, JOB, SAL, round((SELECT AVG(SAL) FROM EMP), 2) "公司平均工资"
FROM EMP "E"
WHERE EXISTS (SELECT AVG(SAL) FROM EMP HAVING E.SAL>AVG(SAL));--0.016s
--3.查询工资大于该部门平均薪资的员工信息
--相关子查询:
SELECT E.*,
ROUND((SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING DEPTNO = E.DEPTNO),2) "其部门平均工资"
FROM EMP "E"
WHERE SAL >
(SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING E.DEPTNO = DEPTNO);--0.025s
--不相关子查询:
SELECT E.*,
ROUND((SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING DEPTNO = E.DEPTNO),2) "其部门平均工资"
FROM EMP "E"
WHERE EXISTS
(SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING E.DEPTNO = DEPTNO AND E.SAL>AVG(SAL));--0.023s
--4.査询emp表的全部信息及各部门的平均工资,并将平均薪资展示在emp表后以部门平均薪资显示
SELECT E.*,
ROUND((SELECT AVG(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING DEPTNO = E.DEPTNO),
2) "部门平均薪资"
FROM EMP "E";--0.033s
习题:
1.查询工资大于10号部门平均工资的员工信息
→
SELECT * FROM EMP WHERE SAL>(SELECT AVG(SAL) FROM EMP WHERE DEPTNO=10);
2.查询工资比20号部门所有人工资都高的员工信息
→
SELECT * FROM EMP WHERE SAL>(SELECT MAX(SAL) FROM EMP WHERE DEPTNO=20 );
↑↓
- 老师补充答案:
SELECT * FROM EMP WHERE SAL> ALL(SELECT SAL FROM EMP WHERE DEPTNO =20);
3.查询工资最低的5名员工信息
→
SELECT E.*,ROWNUM FROM (SELECT * FROM EMP ORDER BY SAL ASC) "E" WHERE ROWNUM<=5;
4.査询哪个部门的平均薪资高于30号部门平均薪资,显示出其部门编号和人数
→
SELECT DEPTNO,COUNT(ENAME) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL)>(SELECT AVG(SAL) FROM EMP WHERE DEPTNO IN(30));
5.显示部门内最低工资比20部门最低工资要高的部门的部门编号及部门内的最低工资
→
SELECT DEPTNO,MIN(SAL) FROM EMP GROUP BY DEPTNO HAVING MIN(SAL)>(SELECT MIN(SAL) FROM EMP WHERE DEPTNO IN(20));
6.査询比CLERK岗位所有人的收入都少的员工的编号、姓名、岗位和薪资
→
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE SAL<(SELECT MIN(SAL) FROM EMP WHERE JOB='CLERK');
** 7.査询比三个部门平均工资都高的员工编号,员工姓名,岗位和薪资
→
SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE SAL > ALL ( SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO );
- ↑错因总结:聚合函数不能对子查询聚合,用ALL关键字代替
8.显示部门名称为 RESEARCH的员工的姓名和工资
→
SELECT ENAME,SAL FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE DNAME='RESEARCH');
9.找出工资高于SMITH的员工姓名,工资
→
SELECT ENAME,SAL FROM EMP WHERE SAL>(SELECT SAL FROM EMP WHERE ENAME='SMITH');
10.求公司最高工资的姓名和其工资
SELECT ENAME,SAL FROM EMP WHERE SAL=(SELECT MAX(SAL) FROM EMP);
11.求每个部门最高工资员工的姓名,工资,部门编号
→
12.查找与 SMITH或 JAMES同部门同岗位的其他员工的详细信息
→
13.查询部门名称中带有字母E的部门下所有员工信息(使用 EXISTS)
→
SELECT * FROM EMP A WHERE EXISTS (SELECT DEPTNO FROM DEPT WHERE DNAME LIKE '%E%' AND DEPTNO =A.DEPTNO);
14.查询工资小于1000的员工所在的部门的部门名称和工作地点
→
SELECT DNAME,LOC FROM DEPT WHERE DEPTNO IN(SELECT DEPTNO FROM EMP WHERE SAL<1000);
15.查询平均工资不小于2000的部门信息(使用 not exists)
→
SELECT * FROM DEPT WHERE DEPTNO IN (SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING AVG(SAL)>=2000);
16.查询出销售部(SALES)下面的员工姓名,工作,工资
→
SELECT ENAME,JOB,SAL FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE DNAME IN('SALES'));
17.查询出EMP表中比所有销售员(“SALESMAN”)工资低的员工姓名、工作、工资
→
SELECT ENAME,JOB,SAL FROM EMP WHERE SAL<(SELECT MIN(SAL) FROM EMP WHERE JOB IN('SALESMAN'));
*** 18.查询工资降序排在第4到第8名的员工
→
SELECT * FROM (SELECT EMP.,ROWNUM R FROM EMP ORDER BY EMP.SAL) E WHERE E.R BETWEEN 4 AND 8;
↑↓
SELECT E.,ROWNUM FROM (SELECT * FROM EMP ORDER BY SAL DESC) E WHERE ROWNUM<9
MINUS
SELECT E.*,ROWNUM FROM (SELECT * FROM EMP ORDER BY SAL DESC) E WHERE ROWNUM<4;
- 错误原因:什么时候必须要起别名?
1.当牵扯到rownum和多个表时,大概率要起别名
19.查询没有员工的部门的部门名称和工作地点
→
SELECT DNAME,LOC FROM DEPT WHERE NOT EXISTS(SELECT EMPNO FROM EMP WHERE EMP.DEPTNO=DEPT.DEPTNO);
↑↓
SELECT DNAME,LOC FROM DEPT WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP );
20.求入职时间相同的员工
→
SELECT * FROM EMP WHERE HIREDATE IN (SELECT HIREDATE FROM EMP GROUP BY HIREDATE HAVING COUNT(*) >=2 );
21.显示比工资最高的员工参加工作时间晚的员工姓名和参加工作时间
→
SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE>(SELECT HIREDATE FROM EMP WHERE SAL=(SELECT MAX(SAL) FROM EMP ));
习题:
1.查询部门编号与(工资大于所有以S开头的员工的工资的员工)的部门编号相等的员工信息
→
SELECT * FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM EMP WHERE SAL>ALL(SELECT SAL FROM EMP WHERE ENAME LIKE 'S%'));
2.查询和部门平均薪资相等的员工信息
→
SELECT * FROM EMP WHERE (DEPTNO,SAL) IN(SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO );
3.查询和部门最高薪资相等的员工信息
→
SELECT * FROM EMP WHERE SAL IN(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);
4.查询部门中和该部门最高薪资相等的员工信息
→
SELECT * FROM EMP WHERE SAL IN(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);
5.查询最高薪资在2000到3000之间的部门的部门信息
→
SELECT * FROM DEPT WHERE DEPTNO IN(SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING MAX(SAL) BETWEEN 2000 AND 3000);
- 6.统计各个部门未达到整体平均薪资水平的员工人数(暂不考虑部门全部大于平均薪资)
→
SELECT DEPTNO, COUNT(EMPNO)
FROM EMP
WHERE EMPNO IN
(SELECT EMPNO FROM EMP WHERE SAL > (SELECT AVG(SAL) FROM EMP))
GROUP BY DEPTNO;
↑↑总结:WHERE筛选的是针对FROM后表的数据, HAVING是针对 GROUP BY分组之后的数据进行筛选↑↑
↑↓
SELECT DEPTNO,
SUM(CASE
WHEN SAL > (SELECT AVG(SAL) FROM EMP) THEN
'1'
ELSE
'0'
END)
FROM EMP
GROUP BY DEPTNO;
7.A:查询薪资最高的员工信息 B:查询薪资最低的员工信息
→
SELECT * FROM EMP WHERE SAL=(SELECT MAX(SAL) FROM EMP);
→
SELECT * FROM EMP WHERE SAL=(SELECT MIN(SAL) FROM EMP);
8.查询工资等级为5的员工的EMPNO,ENAME,SAL,JOB 列信息
→
SELECT EMPNO,ENAME,SAL,JOB FROM EMP WHERE SAL BETWEEN (SELECT LOSAL FROM SALGRADE WHERE GRADE=5) AND (SELECT HISAL FROM SALGRADE WHERE GRADE= 5);
9.查询在DALLAS和NEW YORK工作的员工的基本信息
→
SELECT * FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC IN('DALLAS','NEW YORK'));
10.查询工资等级为4且不在CHICAGO工作的员工基本信息
→
SELECT *
FROM EMP
WHERE SAL IN
(SELECT SAL
FROM SALGRADE
WHERE SAL BETWEEN (SELECT LOSAL FROM SALGRADE WHERE GRADE = 4) AND
(SELECT HISAL FROM SALGRADE WHERE GRADE = 4)
AND DEPTNO <> (SELECT DEPTNO FROM DEPT WHERE LOC IN ('CHICAGO')));
11.假设公司成立于1976,查询五年以后入职的员工信息(不能手动加成1981,然后比较)(一年按照365天计算)
→
SELECT * FROM EMP WHERE HIREDATE >(SELECT TO_DATE('1976/01/01','YYYY/MM/DD')+5*365 FROM DUAL );
12.查询比ALLEN入职晚两天的员工信息
→
SELECT * FROM EMP WHERE HIREDATE IN(SELECT HIREDATE+2 FROM EMP WHERE ENAME='ALLEN');
13.查询比ALLEN入职晚两天的员工所在部门的平均薪资以及最高薪资
→
SELECT AVG(SAL), MAX(SAL)
FROM EMP
WHERE DEPTNO IN
(SELECT DEPTNO
FROM EMP
WHERE EMPNO IN
(SELECT EMPNO
FROM EMP
WHERE HIREDATE =
(SELECT HIREDATE + 2 FROM EMP WHERE ENAME IN ('ALLEN'))));
** 14.查询和20号部门的员工人数相等的部门的员工信息
→
SELECT *
FROM EMP
WHERE DEPTNO IN
(SELECT DEPTNO
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*) = (SELECT COUNT(*) FROM EMP GROUP BY DEPTNO HAVING DEPTNO =20));
15.查询平均薪资大于30号部门的且员工人数不等于4的部门的员工信息
→
SELECT *
FROM EMP
WHERE DEPTNO IN
(SELECT DEPTNO
FROM EMP
GROUP BY DEPTNO
HAVING 4 <> COUNT(*)
AND AVG(SAL)>(SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 30));
16.统计薪资大于30号部门平均薪资的员工人数
→
SELECT COUNT(*) FROM EMP WHERE SAL>(SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING DEPTNO IN(30));
17.查询工资大于2000的员工所在的部门的员工信息,并且部门的员工人数和20号部门相同
→
SELECT *
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE SAL > 2000)
AND DEPTNO IN (SELECT DEPTNO
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*) IN (SELECT COUNT(*)
FROM EMP
GROUP BY DEPTNO
HAVING DEPTNO IN(20)));
附加:假设EMP表中增加一列:离职时间 其字段名为 LEAVEDATE 数据类型为 DATE
限制:每天各部门入职,离职的员工人数不等
1)查询每天每个部门入职的人数
2)查询每天每个部门离职的人数
3)查询每天每个部门的入职人数与离职人数差,若其差值为正,则标记+ ;若其差值为负,则标记为-