MySQL复习记录【四】：子查询

老实说，个人感觉子查询挺容易弄混淆的，也可能是我个人做的题还不够多，不过问题不大，加油！

概念

含义：出现在其它语句中的select语句，称为子查询或者内查询
相应地，外部的查询语句，称为外查询或者主查询

分类：

按照子查询出现的位置：

• select 后面：【仅仅支持标量子查询】

• from 后面：【表子查询】

• where或者having后面：【重点】
  标量子查询（单行）√
  列子查询（多行）√
  行子查询

• exists后面（相关子查询）：【表子查询】

按照结果集的行列数不同：

• 标量子查询（结果集只有一行一列）
• 列子查询（结果集只有一列多行）
• 行子查询（结果集有一行多列）
• 表子查询（结果集一般为多行多列）

一、where或者having后面

特点：
（1）子查询放在小括号内
（2）子查询一般放在条件的右侧
（3）标量子查询，一般搭配着单行操作符使用
单行操作符：> < >= <= <> =
列子查询，一般搭配着多行操作符使用
多行此查询：IN, ANY/SOME, ALL

非法使用标量子查询的情形

‘>’：只能用于一行一列

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	# 查询结果为一行多列
	SELECT salary
	FROM employees 
	WHERE department_id=50
)

1.标量子查询（单行子查询）

案例1：谁的工资比Abel高

① 查询abel的工资
SELECT salary
FROM employees e
WHERE last_name='Abel'

② 查找员工信息，满足工资大于①
SELECT *
FROM employees
WHERE salary > (
	SELECT salary
	FROM employees
	WHERE last_name='Abel'
	);

案例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id和工资

① 查询141号job_id #结果一行一列
SELECT job_id
FROM employees
WHERE employee_id=141

② 查询143号员工salary #结果一行一列
SELECT salary
FROM employees
WHERE employee_id=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 MIN(salary)
FROM employees

② 查询员工的last_name,job_id和salary
SELECT last_name, job_id, salary
FROM employees
WHERE salary = (
	SELECT MIN(salary)
	FROM employees
);

案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资

① 查询50号部门的最低工资
SELECT MIN(salary)
FROM employees 
WHERE department_id=50

② 查询每个部门的最低工资
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id;

③ 在②基础上进行筛选，满足MIN(salary)>①
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT MIN(salary)
	FROM employees 
	WHERE department_id=50
)

2.列子查询（一列多行子查询）

多行比较操作符
IN/NOT IN 等于多行列表中的任意一个
ANY/SOME 任意的意思，和子查询返回的某一个值进行比较
ALL 和子查询返回的所有值比较

案例1：返回location_id是1400或者1700的部门中所有员工的姓名

# ① 查询location_id为1400或者1700的部门信息
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)

# ② 查询员工姓名
SELECT last_name
FROM employees 
WHERE department_id IN (
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
)

案例2：返回其它工种中比job_id为’IT_PROG’部门任一工资低的员工的员工号、姓名、job_id以及salary

# ①查询job_id为'IT_PROG'部门的员工工资
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'

# ② 查询每个部门的员工号、姓名、job_id及salary
SELECT last_name, employee_id, job_id, salary
FROM employees
WHERE salary < ANY(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id='IT_PROG'
)
AND job_id<>'IT_PROG';

# 等价于
SELECT last_name, employee_id, job_id, salary
FROM employees
WHERE salary <(
	SELECT MAX(salary)
	FROM employees
	WHERE job_id='IT_PROG'
)
AND job_id<>'IT_PROG';

案例3：返回其它工种中比job_id为’IT_PROG’部门所有工资低的员工的员工号、姓名、job_id以及salary

# ①查询job_id为'IT_PROG'部门的员工工资
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'

# ② 查询每个部门的员工号、姓名、job_id及salary
SELECT last_name, employee_id, job_id, salary
FROM employees
WHERE salary < ALL(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id='IT_PROG'
)
AND job_id<>'IT_PROG';

# 等价于
SELECT last_name, employee_id, job_id, salary
FROM employees
WHERE salary <(
	SELECT MIN(salary)
	FROM employees
	WHERE job_id='IT_PROG'
)
AND job_id<>'IT_PROG';

3.行子查询（多列多行）

结果集为一行多列或者多行多列

案例：查询员工编号最小并且工资最高的员工信息

# 注意：两个比较符号是一样的，将多个字段看作一个条件
SELECT *
FROM employees
WHERE (employee_id, salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
)


# ① 查询最小的员工编号
SELECT MIN(employee_id)
FROM employees

# ② 查询最高工资
SELECT MAX(salary)
FROM employees
 
# ③ 查询员工信息
SELECT *
FROM employees
WHERE employee_id=(
	SELECT MIN(employee_id)
	FROM employees
)AND salary=(
	SELECT MAX(salary)
	FROM employees
)

二、select后面

这种方式仅仅支持标量子查询

案例1：查询每个部门的员工数

SELECT d.*, (
	SELECT COUNT(*) 
	FROM employees e
	WHERE e.department_id=d.department_id
) AS 员工数
FROM departments d;

案例2：查询员工号=102的部门名

SELECT (
	SELECT department_name
	FROM departments d
	INNER JOIN employees e
	ON d.department_id=e.department_id
	WHERE e.employee_id=102
) AS 部门名;

三、from后面

注意：查询结果看作一张表格，要求必须起别名
案例：查询每个部门的平均工资的工资等级

# ① 查询每个部门的平均工资
SELECT 	AVG(salary), department_id
FROM employees 
GROUP BY department_id


SELECT * FROM job_grades

# ② 连接①的结果集和job_grades表，筛选条件平均工资between lowest_sal and highest_sal
SELECT ag_dep.*,g.grade_level
FROM(
	SELECT 	AVG(salary) ag, department_id
	FROM employees 
	GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal

四、exists后面【相关查询】

语法：
exists（完成的查询语句）
结果是1或者0

exists的外查询先执行

案例1：查询有员工名的部门名

SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.department_id=e.department_id
)

#等价于
SELECT department_name
FROM departments d
WHERE d.department_id IN(
	SELECT department_id
	FROM employees e
)

案例2：查询没有女朋友的男神信息

SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
	SELECT bo.id
	FROM beauty b
	WHERE b.`boyfriend_id`=bo.id
)

综合练习题

1.查询各部门中工资比本部门平均工资高的员工号，姓名和工资

# 1.查询各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

# 2.连接1的结果集和employees表，进行筛选
SELECT employee_id,last_name,salary,e.department_id
FROM employees e
INNER JOIN (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
ON e.department_id=ag_dep.department_id
WHERE salary>ag_dep.ag;

2.查询和姓名中包含字母 u 的员工在相同部门的员工的员工号和姓名

# 1.查询姓名中包含字母u的员工的部门
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%'

# 2.查询部门号等于1中的任意一个的员工号和姓名
SELECT last_name, employee_id
FROM employees 
WHERE department_id IN(
	SELECT DISTINCT department_id
	FROM employees
	WHERE last_name LIKE '%u%'
);

3.查询在部门的 location_id 为 1700 的部门工作的员工的员工号

#1. 查询location_id为1700的部门id
SELECT department_id
FROM departments
WHERE location_id=1700

#2. 查询部门位于这些部门id的员工号
SELECT employee_id
FROM employees 
WHERE department_id IN(
	SELECT department_id
	FROM departments
	WHERE location_id=1700
)

或者：

SELECT employee_id
FROM employees 
WHERE department_id=ANY(
	SELECT department_id
	FROM departments
	WHERE location_id=1700
)

4.案例：查询管理者是 King 的员工姓名和工资

# 1.查询king的员工id
SELECT employee_id
FROM employees
WHERE last_name='K_ing'

# 2.查询哪个员工的manager_id=1
SELECT last_name, salary, manager_id
FROM employees
WHERE manager_id IN(
	SELECT employee_id
	FROM employees
	WHERE last_name='K_ing'
)

5.案例：查询工资最高的员工的姓名，要求 first_name 和 last_name 显示为一列，列名为 姓.名

#1.查询最高工资
SELECT MAX(salary)
FROM employees

# 2.查询工资最高的员工
SELECT CONCAT(first_name,last_name) AS "姓.名"
FROM employees
WHERE salary=(
	SELECT MAX(salary)
	FROM employees
)