05-MySQL子查询

Myth丶恋晨 2022-09-08 15:52 103阅读 0赞

### 文章目录 ###

*   *  1. 子查询分类
     *  2. WHERE或HAVING后面
     *   *  2.1 标量子查询
         *  2.2 列子查询
         *  2.3 行子查询（用得少）
     *  3. exists后面的子查询

--------------------

概念：出现在其他语句内部的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 列子查询 ###

返回多行。

使用多行比较操作符。

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAU3dlZXRfcGlu_size_20_color_FFFFFF_t_70_g_se_x_16]  
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
    )

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAU3dlZXRfcGlu_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/bfae324cd3e749d8b2730243a0565e0a.png