MySQL数据库DML——子查询
子查询简介
子查询是一种查询中嵌套查询的语句
案例:查询底薪超过公司平均底薪的员工信息
SELECT empno,ename,sal
FROM t_emp
WHERE sal>=(SELECT AVG(sal) FROM t_emp);
注: 在正常情况下,WHERE子句中是不能出现AVG等聚合函数的,但是我们可以应用子查询的方式先将聚合函数查询出来,再插入到WHERE子句中。
- 上述这种子查询不推荐写,而更加推荐使用表连接的方式进行相关查询。
子查询的分类
子查询可以写在三个地方:WHERE子句、FROM子句、SELECT子句,但是只有FROM子句查询是最可取的。
WHERE子查询
- 这种子查询最简单,最容易理解,但是却是效率很低的子查询
案例:查询底薪超过公司平均底薪的员工信息
SELECT empno,ename,sal
FROM t_emp
WHERE sal>=(SELECT AVG(sal) FROM t_emp);
注: 比较每条记录都要重新执行(SELECT AVG(sal) FROM t_emp)这条子查询,因此效率很低。
FROM子查询
- 这种子查询只会执行一次,所以查询效率很高
案例:查询底薪超过公司平均底薪的员工信息
SELECT e.empno,e.ename,e.sal,t.avg
FROM t_emp e JOIN
(SELECT deptno,AVG(sal) AS avg FROM t_emp GROUP BY deptno) t
ON e.deptno=t.deptno AND e.sal>=t.avg;
SELECT子查询
- 这种子查询每输出一条记录的时候都要执行一次,查询效率很低,不建议使用
案例:查看每个员工的部门信息
SELECT
e.empno,
e.ename,
(SELECT dname FROM t_dept WHERE deptno=e.deptno)
FROM t_emp e;
单行子查询和多行子查询
- 单行子查询的结果集只有一条记录,多行子查询结果集有多行记录
- 多行子查询只能出现在WHERE子句和FROM子句中,不能出现在SELECT子句中
多行子查询案例
案例:使用子查询查找FORD和MARTIN两个人的同事
SELECT ename FROM t_emp
WHERE deptno IN
(SELECT deptno FROM t_emp WHERE ename IN("FORD","MARTIN"))
AND ename NOT IN("FORD","MARTIN");
WHERE子句中的多行子查询
- WHERE子句中,可以使用IN、ALL、ANY、EXISTS关键字来处理多行表达式结果集的条件判断
案例:查询比FORD和MARTIN底薪都高的员工信息
SELECT ename FROM t_emp
WHERE sal>=ALL
(SELECT sal FROM t_emp WHERE ename IN("FORD","MARTIN"))
AND ename NOT IN("FORD","MARTIN");
__注:__如果将ALL替换为ANY,则结果为比FORD或者MARTIN任何一个人工作高的员工都会显示。
EXISTS关键字
- EXISTS关键字是把原来在子查询之外的条件判断,写到了子查询的里面。
使用方法:
SELECT ...... FROM 表名 WHERE [NOT] EXISTS (子查询);
案例:查询工资等级是3级或者4级的员工信息
SELECT empno,ename,sal
FROM t_emp
WHERE EXISTS(
SELECT * FROM t_salgrade
WHERE sal BETWEEN losal AND hisal
AND grade IN(3,4)
);