[mysql] 子查询

当一个查询语句中又嵌套了另一个完整的select语句，则

1. 被嵌套的select语句称为子查询或内查询；
2. 外面的select语句称为主查询或外查询。

分类

按子查询出现的位置进行分类：

1. select后面：

   子查询的结果为单行单列（标量子查询）

2. from后面：

   子查询结果可以为多行多列

3. where或having后面

   子查询结果必须为单列

   1. 单行子查询
   2. 多行子查询

4. exists后面

   子查询结果必须为单列（相关子查询）

特点

1. 子查询放在条件中，要求必须放在条件的右侧

2. 子查询必须放在小括号中

3. 子查询的执行优先于主查询

4. 单行子查询对应 单行操作符： > < >= <= = <>

   多行子查询对应 多行操作符：any/some all in

1. where后面

1. 单行子查询(标量子查询)

# 1. 查询和Zlotkey 相同部门的员工姓名和工资
SELECT last_name,
       salary
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM employees
    WHERE last_name = 'Zlotkey'
);

# 2. 查询工资比公司平均工资高的员工的员工号可，姓名和工资
SELECT employee_id,
       last_name,
       salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
)

2. 多行子查询(列子查询)

1. in：判断某字段是否在指定列表内
2. any/some : 判断某字段的值是否满足其中任意一个
3. all ：判断某字段的值是否满足里面所有的

# 1. 返回location_id 是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees
WHERE department_id IN (
    SELECT departments.department_id
    FROM departments
    WHERE location_id IN (1400, 1700)
);

# 2. 返回其他部门中比job_id为`IT_PROG`部门任一工资低的员工的员工号、姓名、job_id以及salary
SELECT employee_id,
       last_name,
       salary
FROM employees
WHERE salary < ANY (
    SELECT DISTINCT salary
    FROM employees
    WHERE job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';

# 即
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';

# 3. 返回其他部门中比job_id为`IT_PROG`部门所有工资低的员工的员工号、姓名、job_id以及salary
SELECT employee_id,
       last_name,
       job_id,
       salary
FROM employees
WHERE salary < ALL (
    SELECT DISTINCT salary
    FROM employees
    WHERE job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';

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';

3. 行子查询（结果集一行多列或多行多列）

# 案例 查询员工编号最小并且工资最高的员工信息
# 1. 查询最小的员工编号
USE myemployees;
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
    );

2. select后面

# 案例1 查询每个部门的员工个数
SELECT d.*, (
    SELECT COUNT(*)
    FROM employees AS e
    WHERE e.department_id = d.department_id
    )
FROM departments AS d;

# 案例2 查询员工号=102的部门名
SELECT (
    SELECT department_name
    FROM departments AS d
    INNER JOIN employees AS e
    ON d.department_id = e.department_id
    WHERE e.employee_id = 102
           ) 部门名;

括号内只能返回一列。

仅仅支持标量子查询。

3. from后面

充当一个被查询表。

要求必须起别名。

# 案例 查询每个部门的平均工资的工资等级
# 1. 查询每个部门的平均工资
SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id;

-- 工资等级
SELECT * FROM job_grades;

# 2. 连接1的结果集和job_grades表，筛选平均工资
SELECT d.department_name,
       avg_dep.*,
       g.grade_level
FROM (
         SELECT AVG(salary) AS avg_salary, department_id
         FROM employees
         GROUP BY department_id
     ) AS avg_dep
INNER JOIN job_grades AS g
ON avg_dep.avg_salary BETWEEN g.lowest_sal AND g.highest_sal
INNER JOIN departments AS d
ON avg_dep.department_id = d.department_id;

4. exists后面(相关子查询)

测试exists

SELECT EXISTS(SELECT employee_id FROM employees);

结果：

即查询查询结果中有没有值。

SELECT EXISTS(SELECT employee_id FROM employees WHERE  salary = -1);

结果：

语法

exists(完整的查询语句)
## 结果
# 0 / 1

可以用其他的语句代替，用的比较少。

案例

# 案例1 查询有员工名的部门名
SELECT department_name
FROM departments AS d
WHERE EXISTS(
    SELECT *
    FROM employees AS e
    WHERE d.department_id = e.department_id
          );

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