05-MySQL子查询

---

概念：出现在其他语句内部的select语句，称为子查询或内查询

内部嵌套其他select语句的查询，称为外查询或主查询

示例：

select first_name 
from employees 
where department_id in(
	select department_id 
    from departments
	where location_id=1700
)

1. 子查询分类

（1）按子查询出现的位置：

• SELECT后面：仅仅支持标量子查询
• FROM后面：支持表子查询
• WHERE或HAVING后面：（☆）
  • 标量子查询（单行）（☆）
  • 列子查询（一列多行）（☆）
  • 行子查询（一行多列）
• EXISTS后面：表子查询

（2）按结果集的行列数不同：

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

（3）特点：

• 子查询(内查询) 在主查询之前一次执行完成。
• 子查询的结果被主查询(外查询)使用

2. WHERE或HAVING后面

• 标量子查询（单行子查询）（☆）
• 列子查询（多行子查询）（☆）
• 行子查询（多列子查询）

特点：

（1）子查询要包含在括号内。

（2）将子查询放在比较条件的右侧。

（3）单行操作符对应标量子查询（单行子查询），一般搭配单行操作符使用 > < >= <= = <>

（4）多行操作符对应列子查询（多行子查询），一般搭配多行操作符使用，例如 IN、ANY/SOME、ALL

2.1 标量子查询

案例1：谁的工资比Abel 高?

首先，子查询来查询Abel的工资(1)，然后在员工信息中查询满足salary>(1)的结果。

SELECT last_name
FROM employees
WHERE salary >(
    SELECT salary
    FROM employees
    WHERE last_name = 'Abel'
);

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

(1)查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141

(2)查询143员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143

(3)合并（1）（2）
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
)

在子查询中使用组函数

题目：返回公司工资最少的员工的 last_name, job_id 和 salary

SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
    SELECT MIN(salary)
	FROM employees
)

子查询中的HAVING 子句

首先执行子查询。向主查询中的HAVING 子句返回结果。

题目：查询最低工资大于50号部门最低工资的部门id和其最低工资

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

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

(3) 在(2)基础上选择MIN(salary)>(1)的
SELECT MIN(salary)，department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
    SELECT MIN(salary)
	FROM employees
	WHERE department_id = 50;
)

非法使用标量子查询(单行操作符>里面只能搭配标量子查询)

SELECT MIN(salary)，department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
    SELECT salary
	FROM employees
	WHERE department_id = 50;  #里面查询结果是50部门的工资列表，不是单列单行，不能搭配单行操作符>
)

子查询中的空值问题

SELECT last_name, job_id
FROM employees
WHERE job_id =(
    SELECT job_id
    FROM employees
    WHERE last_name = 'Haas' #没有符合该条件的值，里面是空值，也会报错
);

2.2 列子查询

返回多行。

使用多行比较操作符。

any和all一般可以用最大最小值替换，用的较少

题目：返回 location_id是1400或1700的部门中的所有员工姓名

(1)查询 location_id是1400或1700的部门编号
SELECT department_id
FROM departments
WHERE location_id IN(1400,1700)
#上面查询出来的结果是一列多行

(2)查询员工姓名，要求部门号是(1)列表中的某一个
SELECT last_name
FROM employees
WHERE department_id IN(
    SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
)

题目：返回其它部门中比 job_id 为 ‘IT_PROG’ 部门任一工资低的员工的员工号、姓名、job_id 以及salary

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY(
    SELECT salary
    FROM employees
    WHERE job_id = 'IT_PROG'
)AND job_id <> 'IT_PROG';

或者改为MAX()，实现效果也是一样的

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

题目：返回其它部门中比 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'
)AND job_id <> 'IT_PROG';

或者改成MIN()

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

2.3 行子查询（用得少）

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

以往查询方法

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

(2)查询最高工资
SELECT MAX(salary)
FROM employees

(3)查询员工信息
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
)

select后面子查询（用得少）

from后面子查询（用得少，将子查询结果充当一张表，要求必须起别名）

3. exists后面的子查询

语法：

exists(完整的查询语句)

结果：1或0

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

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

可以用in实现

SELECT department_name
FROM departments d
WHERE d.'department_id' IN(
    SELECT department_id
    FROM employees
)

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

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

#in
SELECT bo.*
FROM boys bo
WHERE bo.'id' NOT IN(
    SELECT b.'boyfriend_id'
    FROM beauty b
)