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