MySQL入门教程(15)MySQL中的子查询

Tanglu MySQL 2022-12-15 89 0

一、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      #必须为子查询起一个表别名


评论