6.2.3 其他题目
查询平均工资最低的部门id
MySQL中聚合函数不能嵌套使用
方式一：
# 查询平均工资最低的部门id SELECT department_id, AVG(salary) "avg_sal" FROM employees GROUP BY department_id HAVING avg_sal = ( # 查询出所有部门中的最低工资 SELECT MIN(avg_sal) # 在from中使用子查询，需要为子查询取别名 FROM ( # 查询所有部门的平均工资 SELECT AVG(salary) "avg_sal" FROM employees GROUP BY department_id ) t_dep_avg_sal );
方式二：
# 查询平均工资最低的部门id SELECT department_id, AVG(salary) "avg_sal" FROM employees GROUP BY department_id # 筛选出平均工资小于等于 所有部门平均工资 的部门 # 即筛选出平均工资最低的部门id HAVING avg_sal <= ALL ( # 查询所有部门的平均工资 SELECT AVG(salary) "avg_sal" FROM employees GROUP BY department_id );
6.3 空值问题
注意内查询返回的null值
SELECT last_name FROM employees WHERE employee_id NOT IN ( SELECT manager_id FROM employees );
SELECT manager_id FROM employees;
由于子查询中有null值的返回，null参与外层查询的NOT IN运算，会返回null，所以每条记录与null运算都返回null，所以查询不出记录。
排除空值，即可返回正确结果。
# 查询不为管理者的员工的姓名 SELECT last_name FROM employees WHERE employee_id NOT IN ( SELECT manager_id FROM employees WHERE manager_id IS NOT NULL );
7. 相关子查询
如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为 关联子查询 。
7.1 相关子查询执行流程
相关子查询按照一行接一行的顺序执行，主查询的每一行都会执行一次子查询。
7.2 相关子查询示例
查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
方法一：
# 查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id SELECT last_name, salary, department_id FROM employees e1 WHERE salary > ( SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e1.department_id );
方法二：
不相关子查询
# 查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id SELECT e1.last_name, e1.salary, e1.department_id FROM employees e1, ( SELECT e2.department_id, AVG(e2.salary) "avg_sal" FROM employees e2 GROUP BY e2.department_id ) dep_avg_sal WHERE e1.department_id = dep_avg_sal.department_id AND e1.salary > dep_avg_sal.avg_sal;
7.3 在ORDER BY 中使用相关子查询
查询员工的id,salary,按照department_name 排序
# 查询员工的id,salary,按照department_name 排序 SELECT employee_id, salary FROM employees e ORDER BY ( SELECT department_name FROM departments d WHERE d.department_id = e.department_id );
7.4 在WHERE中使用相关子查询
若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_id
# 若employees表中employee_id 与job_history表中employee_id相同 的数目不小于2， # 输出这些相同id的员工的employee_id,last_name和其job_id SELECT employee_id, last_name, job_id FROM employees e WHERE 2 <= ( SELECT COUNT(*) FROM job_history j WHERE e.employee_id = j.employee_id );
7.5 EXISTS 与 NOT EXISTS关键字
关联子查询通常也会和 EXISTS 操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
- 如果在子查询中不存在满足条件的行：
- 条件返回 FALSE
- 继续在子查询中查找
- 如果在子查询中存在满足条件的行：
- 不在子查询中继续查找
- 条件返回 TRUE
NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。
7.5.1 题目1
查询公司管理者的employee_id，last_name，job_id，department_id信息
# 方式一：自连接 SELECT DISTINCT m.employee_id, m.last_name, m.job_id, m.department_id FROM employees e, employees m WHERE e.manager_id = m.employee_id;
# 方式二：子查询 SELECT employee_id, last_name, job_id, department_id FROM employees WHERE employee_id IN ( SELECT DISTINCT manager_id FROM employees WHERE manager_id IS NOT NULL );
# 方式三：使用EXISTS SELECT employee_id, last_name, job_id, department_id FROM employees e1 # 判断子查询中是否有记录存在 # 外层查询的当前行与子查询中使用的表二进行一一对比 # 当外层的employee_id = 表二的manager_id 即子查询中存在记录 # 此时不在进行查询返回true # 否则继续查询直到查询完整个表，返回false WHERE EXISTS( SELECT * FROM employees e2 WHERE e1.employee_id = e2.manager_id );
7.5.2 题目2
查询departments表中，不存在于employees表中的部门的department_id和department_name
# 查询departments表中，不存在于employees表中的部门的department_id和department_name SELECT department_id, department_name FROM departments d # 每行数据进入子查询与子查询中的表的每行进行匹配 # 当当前行与子查询表中的每行都匹配不成功时，即子查询没有一行数据 # NOT EXISTS返回true WHERE NOT EXISTS( SELECT * FROM employees e WHERE e.department_id = d.department_id );
8. 子查询可以声明的位置
在SELECT中，除了GROUP BY 和 LIMIT之外，其他位置都可以声明子查询！
SELECT的完整结构
#sql99语法： SELECT ...., ...., ....(存在聚合函数) FROM ... (LEFT / RIGHT)JOIN ....ON 多表的连接条件 (LEFT / RIGHT)JOIN ... ON .... WHERE 不包含聚合函数的过滤条件 GROUP BY ..., .... HAVING 包含聚合函数的过滤条件 ORDER BY ...., ...(ASC / DESC ) LIMIT ..., ....
