MySQL入门教程（15）MySQL中的子查询

一、MySQL子查询作用
子查询就是嵌套进行多次查询的一个过程，最外层的查询也可以称为主查询，其嵌套的查询都可以叫做子查询。可以理解为子查询的结果是一个值，获取到这个值后进行后续查询，比如要查询企业中哪些员工的工资比张三高，那就需要先查询张三的工资。

· 子查询主要解决AVG等聚合函数不能写在WHERE条件中的问题

· 通常要对聚合函数进行嵌套的话就可以考虑使用子查询（Oracle中聚合函数可以嵌套，如min(avg()）

· 单行子查询是指子查询的查询结果只有1条数据，对于单行子查询可以使用的操作符有=、!=、>、<、>=、<=

· 多行子查询是指子查询的查询结果大于1条数据，对于多行子查询可以使用的操作符有IN（等于列表中的任意一个）、ANY（需要和单行操作符一起使用，和子查询返回的其中一个值进行比较）、ALL（需要和单行操作符一起使用，和子查询返回的其中所有值进行比较）。如果主查询中使用了像=、!=这样的关联条件就会返回subquery return more than 1row的错误

· 如果子查询并没有返回数据，那么整个查询结果为空而不报错

· 子查询对于性能消耗比较明显，执行子查询时会将内层语句的查询结果存放在内存临时表中，主查询再通过临时表进行查询，最后才是删除临时表。这些操作会消耗过多的CPU和IO资源，在进行复杂查询时一定要注意，尽量用JOIN代替子查询

二、MySQL子查询示例

1、查询工资大于149号员工的其他员工信息

SELECT employee_id,last_name,salary FROM employees
WHERE salary > (
SELECT salary FROM employees WHERE employee_id = 149
);

#查询工资大于张三的员工
SELECT last_name,salary FROM employees WHERE salary > ( SELECT salary FROM employees WHERE last_name='zhangsan');

2、查询工号与141号员工相同、工资比143号员工多的其他员工的姓名、job_id和工资

SELECT last_name,job_id,salary FROM employees 
WHERE job_id =
(
    SELECT job_id FROM employees WHERE employee_id = 141
)
AND salary > 
(
    SELECT salary FROM employees WHERE employee_id = 143
)

3、查询最低工资大于110号部门中最低工资的部门ID和该部门最低工资（在HAVING中使用子查询）

SELECT department_id,MIN(salary) FROM employees 
WHERE department_id IS NOT NULL 
GROUP BY department_id
HAVING MIN(salary) > (
  SELECT MIN(salary) FROM employees WHERE department_id = 110
)

4、查询结果显示employee_id，last_name和location三个字段，其中location字段的值需要进行判断，如果员工department_id和location_id为1800的department_id相同则location显示CANADA，否则显示USA（在CASE中使用子查询）

SELECT employee_id,last_name,CASE department_id WHEN (SELECT department_id FROM departments WHERE location_id=1800)
                                                ELSE 'USA' END "location"
FROM employees;

5、查询除IT部门外其他所有部门中工资比IT部门所有人都要低的员工信息（多行子查询）

SELECT employee_id,last_name,job_ib,salary FROM employees WHERE job_id <> 'IT'
AND salary < ALL (
  SELECT salary FROM employees WHERE job_id = 'IT'
)

6、查询平均工资最低的部门ID（把子查询的结果作为一张表，解决聚合函数嵌套问题）

SELECT MIN(avg_salary) FROM ( 
  SELECT AVG(salary) avg_salary 
  FROM employees 
  GROUP BY department_id                             
) t_dept_avg_salary      #必须为子查询起一个表别名