PS：本文所用数据表可参照前文： mysql基础复习——连接查询

子查询

出现在其他语句中的select语句称为子查询或者内查询 外部的查询语句成为主查询或外查询 分类： 子查询出现的位置 select后：仅支持标量子查询； from后：支持表子查询； where或having后：标量子查询（单行）、列子查询（多行）、行子查询； exists后：表子查询； 按结果集的行列数区分： 标量子查询（结果集只有一行一列）； 列子查询（结果集有一列多行）； 行子查询（结果集有一行多列）； 表子查询（结果集一般为多行多列）；

一、where或having后

1、标量子查询（结果集只有一行一列）； 2、列子查询（结果集有一列多行）； 3、行子查询（结果集有一行多列）；

特点： ①子查询放在小括号内 ②子查询一般放在条件的右侧 ③标量子查询一般搭配多行操作符使用 ④列子查询，搭配多行操作符

标量子查询

# 案例1：谁的工资比Abel高？ SELECT e.last_name, e.salary FROM employees AS e WHERE e.`salary`>( SELECT e.`salary` FROM employees AS e WHERE e.`last_name` = 'Abel' );

# 案例2：返回job_id与141号相同，salary比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：返回公司工资最少的员工的last_name，job_id和salary SELECT last_name, job_id, salary FROM employees WHERE salary = ( SELECT MIN(salary) FROM employees );

# 案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资 # 在分组结果的基础上进行筛选所以用having SELECT department_id, MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary) > ( SELECT MIN(salary) FROM employees WHERE department_id = 50 );

列子查询

# 案例1：返回location_id是1400或1700的部门中所有员工姓名 select last_name from employees where department_id in( SELECT department_id FROM departments WHERE location_id IN(1400,1700) );

# 案例2：返回其他工种中比job_id为'IT_PROG'工种任一工资低的员工的员工号、姓名、job_id以及salary select employee_id, last_name,job_id, salary from employees where salary < any( select distinct salary from employees where job_id = 'IT_PROG' ) and job_id <> 'IT_PROG'; # any()函数可以换为max()函数

# 案例3：返回其他工种中比job_id为'IT_PROG'工种中所有员工工资都低的员工的员工号、姓名、job_id以及salary select employee_id,last_name,job_id,salary from employees where salary < all( SELECT salary FROM employees WHERE job_id = 'IT_PROG' ); # all()函数可以换为min()函数

行子查询

# 案例：查询员工id最小并且工资最高的员工信息 # 一般方法 SELECT * FROM employees WHERE employee_id = ( SELECT MIN(employee_id) FROM employees ) AND salary = ( SELECT MAX(salary) FROM employees ); # 行子查询 SELECT * FROM employees WHERE (employee_id, salary) = ( SELECT MIN(employee_id), MAX(salary) FROM employees ); # PS：行子查询中，多个条件表达式所用符号必须一致，如都是等于或都是大于或小于

二、在select后的子查询

# 案例1：查询每个部门的员工个数 # select后子查询 SELECT d.*, ( SELECT COUNT(*) FROM employees AS e WHERE d.`department_id`=e.`department_id` ) AS 'count' FROM departments AS d; # 外连接查询 SELECT d.*,COUNT(e.`employee_id`) AS 'count' FROM departments AS d LEFT OUTER JOIN employees AS e ON d.`department_id`=e.`department_id` GROUP BY d.`department_id`

# 案例2：查询各部门平均工资的部门id，平均工资以及工资等级 SELECT salarys.*,grade_level FROM ( SELECT e.`department_id`,AVG(salary) AS avg_salary FROM employees AS e GROUP BY e.`department_id` ) AS salarys INNER JOIN job_grades AS job ON salarys.`avg_salary` BETWEEN job.`lowest_sal` AND job.`highest_sal`;

三、exists后

# 案例1：查询有员工存在的部门 SELECT department_name FROM departments d WHERE EXISTS( SELECT employee_id FROM employees e WHERE e.`department_id`=d.`department_id` );

子查询案例

# 案例1：查询和Zlotkey相同部门的员工姓名和工资 # 案例2：查询工资比公司平均工资高的员工的员工号、姓名和工资 # 案例3：查询各部门中工资比本部门平均工资高的员工的员工号、姓名和工资 # 案例4：查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名 # 案例5：查询在部门的location_id为1700的部门工作的员工的员工号 # 案例6：查询管理者是king 的员工姓名和工资 # 案例7：查询工资最高的员工姓名，要求first_name和last_name显示为一列，列名为‘姓.名’ # 案例1：查询和Zlotkey相同部门的员工姓名和工资 SELECT last_name, salary FROM employees AS e WHERE e.`department_id` = ( SELECT department_id FROM employees AS e2 WHERE e2.`last_name` = 'Zlotkey' );

# 案例2：查询工资比公司平均工资高的员工的员工号、姓名和工资 SELECT employee_id, last_name, salary FROM employees AS e WHERE e.`salary`>( SELECT AVG(salary) FROM employees );

# 案例3：查询各部门中工资比本部门平均工资高的员工的员工号、姓名和工资 SELECT employee_id, last_name, salary FROM employees AS e WHERE e.`salary` > ( SELECT AVG(salary) FROM employees AS e2 GROUP BY e2.`department_id` HAVING e.`department_id` = e2.`department_id` ); # 解法2 SELECT employee_id, last_name, salary FROM employees AS e inner join ( SELECT AVG(salary) as avgsal, department_id FROM employees AS e2 GROUP BY e2.`department_id` ) as avg_salary on e.`department_id` = avg_salary.department_id where e.`salary` > avg_salary.avgsal;

# 案例4：查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名 SELECT employee_id, last_name FROM employees AS e WHERE e.`department_id` IN( SELECT department_id FROM employees AS e2 WHERE e2.`last_name` LIKE '%u%' );

# 案例5：查询在部门的location_id为1700的部门工作的员工的员工号 SELECT employee_id FROM employees AS e WHERE e.`department_id` IN( SELECT department_id FROM departments AS d WHERE d.`location_id` = 1700 );

# 案例6：查询管理者是K_ing 的员工姓名和工资 select last_name, salary from employees as e where e.`manager_id` in ( select employee_id from employees as e2 where e2.`last_name`='K_ing' );

# 案例7：查询工资最高的员工姓名，要求first_name和last_name显示为一列，列名为‘姓.名’ SELECT CONCAT(first_name,'.',last_name) AS '姓.名' FROM employees AS e WHERE e.`salary` = ( SELECT MAX(salary) FROM employees );

# 案例8：查询工资最低的员工信息：last_name，salary SELECT last_name, salary FROM employees AS e WHERE e.`salary` = ( SELECT MIN(salary) FROM employees );

# 案例9：查询平均工资最低的部门信息 # 方式一：嵌套查询，较麻烦 SELECT * FROM departments AS d WHERE d.`department_id`= ( SELECT department_id FROM employees AS e GROUP BY e.`department_id` HAVING AVG(e.`salary`)=( SELECT MIN(d_avg.d_avgs) FROM ( SELECT AVG(salary) AS d_avgs FROM employees GROUP BY department_id ) AS d_avg ) ); # 方式二：简洁明了 SELECT * FROM departments AS d WHERE d.`department_id` = ( SELECT department_id FROM employees AS e GROUP BY e.`department_id` ORDER BY AVG(salary) ASC LIMIT 1 );

# 案例10：查询平均工资最低的部门信息和该部门的平均工资 SELECT d.*,AVG(e.`salary`) AS 平均工资 FROM departments AS d INNER JOIN employees AS e ON d.`department_id` = e.`department_id` GROUP BY d.`department_id` HAVING d.`department_id`=( SELECT department_id FROM employees AS e GROUP BY e.`department_id` ORDER BY AVG(salary) ASC LIMIT 1 );

# 案例11：查询平均工资最高的job信息 SELECT * FROM jobs WHERE jobs.`job_id`=( SELECT job_id FROM employees AS e GROUP BY e.`job_id` ORDER BY AVG(salary) DESC LIMIT 1 );

# 案例12：查询平均工资高于公司平均工资的部门有哪些 SELECT department_id,AVG(salary) FROM employees AS e GROUP BY department_id HAVING AVG(salary) > ( SELECT AVG(salary) FROM employees )

# 案例13：查询出公司中所有manager的详细信息 SELECT * FROM employees AS e WHERE e.`employee_id` IN( SELECT DISTINCT manager_id FROM employees );

# 案例14：各个部门中最高工资中最低的那个部门的最低工资是多少 select min(salary),e.`department_id` from employees as e where e.`department_id` = ( SELECT e.`department_id` FROM employees AS e GROUP BY e.`department_id` ORDER BY MAX(salary) ASC LIMIT 1 ) group by e.`employee_id`

# 案例15：查询平均工资最高的部门的manager的详细信息：last_name、department_id、email、salary SELECT last_name, department_id, email, salary FROM employees AS e WHERE e.`employee_id` IN( SELECT manager_id FROM employees AS e WHERE e.`department_id`=( SELECT e.`department_id` FROM employees AS e GROUP BY e.`department_id` ORDER BY MAX(salary) DESC LIMIT 1 ) );