MySQL 子查询

# 子查询是一种查询中嵌套查询的语句
# 但非常不推荐，所有子查询的效率会非常低，如果一定要用只用from子查询

# 查询底薪高于平均底薪的员工
# WHERE子查询（不推荐）
SELECT empno,ename,sal
FROM t_emp
WHERE sal>= (SELECT AVG(sal) FROM t_emp); # 生成了一个值再比较

# SELECT子查询（不推荐）
SELECT e.empno,e.ename,
(SELECT dname FROM t_dept WHERE deptno=e.deptno) as "部门名称"
FROM t_emp e;

# 如果一定要用子查询，推荐使用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;


# 如何用子查询找FORD和MARTIN两个人的同事
SELECT ename
FROM t_emp
WHERE deptno IN  # 如果t_emp表中deptno字段在子集合中，则就是我们要找的名单
(SELECT deptno FROM t_emp WHERE ename IN ("FORD","MARTIN"))  #  子查询：先找出ford和martin的部门编号
AND ename NOT IN ("FORD","MARTIN") # 显示的名单不能出现Ford和Martin

# 查询比ford和Martin底薪都高的员工信息
SELECT ename,sal
FROM t_emp
WHERE sal >= ALL  # ALL表示所有子集的结果
(SELECT sal
FROM t_emp WHERE ename IN ("FORD","MARTIN"))
AND ename NOT IN ("FORD","MARTIN")


SELECT ename,sal
FROM t_emp
WHERE sal >= ANY  # ANY代表子集的任何一个结果
(SELECT sal
FROM t_emp WHERE ename IN ("FORD","MARTIN"))
AND ename NOT IN ("FORD","MARTIN")

# 查询工资等级是3级或4级的员工信息
SELECT ename,sal
FROM t_emp
WHERE EXISTS(
SELECT * 
FROM t_salgrade
WHERE sal BETWEEN losal and hisal
AND grade IN(3,4)
);