MySQL4️⃣多表查询（❗）

1、多表查询

1.1、简介

多表查询：从多个存在关联关系的表中查询记录。

  • 使用：
    1. 使用逻辑外键，不使用 FOREIGN KEY。
    2. 通常会为表起别名，简化书写。
    3. 若多表查询涉及同名字段，起别名加以区分。
  • 说明：在实际业务场景中，建议使用单表查询为主。
    1. 原因：多表查询影响数据库性能。
    2. 解决：在代码层面，将涉及多表查询的业务拆分为多次单表查询，拼接查询结果。

1.2、多表关系

外键位置 外键要求 示例
One to One 任意表 另一方的主键，UNIQUE 约束 用户 - 用户详情
One to Many Many 表 One 表的主键 部门 - 员工
Many to Many 中间表 两张表的主键 学生 - 课程

1.3、连接查询

1.3.1、笛卡尔积

关键字 FROM：计算笛卡尔积。

  1. 在多表查询中，需要消除无效的笛卡尔积，仅保留关联部分（交集）。
  2. 使用连接查询，即可达成以上目的。

1.3.2、连接类型（❗）

  1. 连接查询：
    • 内连接：交集（INNER）
    • 外连接（OUTER）：
      • 左外连接：左表所有数据 + 交集（LEFT [OUTER]）
      • 右外连接：右表所有数据 + 交集（RIGHT [OUTER]）
    • 自连接：同一张表连接多次。
  2. 联合查询：合并多个查询结果，形成新的结果集（UNION [ALL]）
  3. 子查询：同一张表连接多次（👎 性能差，不推荐）
    • 需要指定别名，可以是内/外连接。
    • 常用于数据库表维护的树状结构，即包含“父 id”字段。

2、连接查询

2.1、格式

join_type 的取值如下

  1. INNER：内连接，可省略。

  2. LEFT OUTER：左外连接，可省略 OUTER。

  3. RIGHT OUTER：右外连接，可省略 OUTER。

    SELECT 字段列表
FROM 表1 别名
	[join_type] JOIN 表2 别名
		ON 连接条件;

2.2、示例

2.2.1、内连接

交集

示例：查询员工姓名及其部门名称。

（没有部门的员工不会被查出）

SELECT e.name AS '姓名',
		d.name AS '部门'
FROM t_emp AS e
		INNER JOIN t_dept AS d
		ON e.dept_id = d.id;
		
# 等价写法
SELECT e.name AS '姓名',
		d.name AS '部门'
FROM t_emp AS e, t_dept AS d
WHERE e.dept_id = d.id;

2.2.2、外连接

左/右表 + 交集

示例：查询所有员工姓名及其部门名称。

（没有部门的员工也会查出，其部门名称为 null）

# 左外连接
SELECT e.name AS '姓名',
		d.name AS '部门'
FROM t_emp AS e
		LEFT JOIN t_dept AS d
			ON e.dept_id = d.id;

# 右外连接
SELECT e.name AS '姓名',
		d.name AS '部门'
FROM t_dept AS d
	RIGHT JOIN t_emp AS e
			ON d.id = e.dept_id;

2.2.3、自连接

同一张表连接多次

示例：查询员工姓名及其领导姓名。

# 内连接：顶级领导不会被查出
SELECT a.name '姓名',
		b.name '领导'
FROM t_emp a 
	JOIN t_emp b
		ON a.managerid = b.id;

# 外连接：所有员工都会查出
SELECT a.name '姓名',
		b.name '领导'
FROM t_emp a 
		LEFT JOIN t_emp b
        	ON a.managerid = b.id;

3、联合查询

合并多个查询结果，形成新的结果集。

  1. 格式：

    • UNION：合并，去重。

    • UNION ALL：仅合并。

      SELECT 字段列表 FROM 表1
UNION [ALL]
SELECT 字段列表 FROM 表2;

  2. 说明：

    • 待合并的查询结果集，字段列表需一致（列数，类型）。
    • 通常可使用多条件查询（逻辑运算符 OR）等价实现。

示例：查询工资低于 5000 或年龄高于 40 的员工

  • 联合查询：

    SELECT * FROM t_tmp WHERE salary < 5000
UNION [ALL]
SELECT * FROM t_tmp WHERE age > 40;

  • 多条件查询：

    SELECT * 
FROM t_tmp
WHERE salary < 5000
	OR age > 40;

4、子查询

嵌套查询：SQL 语句中嵌套 SELECT 语句，以内层查询结果作为外层字段列表。

  • 使用位置：
    1. WHERE
    2. FROM
    3. SELECT
  • 分类：根据子查询返回结果。
    1. 标量子查询：单个值
    2. 列子查询：一列
    3. 行子查询：一行
    4. 表子查询：多行多列
  • 说明：大部分操作可通过连接查询等价实现。

4.1、标量子查询

4.1.1、常用运算符

>、<、>=、<=、=、<=>、!= 或 <>

4.1.2、示例

  1. 查询【研发部】的员工信息：

    （👉 子查询 dept 研发部 id，外层查询 emp 员工信息）

    SELECT *
FROM t_emp
WHERE dept_id = (
    # 子查询
    SELECT id
    FROM t_dept
    WHERE name = '研发部');

  2. 查询比【7 号员工】晚入职的员工信息：

    （👉 子查询 7 号员工的入职日期，外层查询条件查询）

    SELECT *
FROM t_emp
WHERE entrydate > (
    # 子查询
    SELECT entrydate
    FROM t_emp
    WHERE id = 7);

4.2、列子查询

常用运算符

含义 备注
IN 集合范围内，任意一个 后接小表（外层表 > 内层表）
EXISTS 对外层表进行逐行循环，子查询结果集非空时取出当前记录 后接大表（外层表 < 内层表）
ANY（SOME） 子查询返回列表中，有任意一个满足 可用 MAX/MIN 等价
ALL 子查询返回列表中，所有值都需满足 可用 MAX/MIN 等价

4.2.1、IN & EXISTS

① 示例

示例：查询【研发部】和【质量部】的员工信息。

  • IN：子查询 dept 部门 id，外层查询 emp 员工信息。

    SELECT *
FROM t_emp
WHERE dept_id IN (
    # 子查询
    SELECT id
    FROM t_dept
    WHERE name = '研发部' OR name = '质量部');

  • EXISTS：遍历外层表，每轮循环向子查询传入 dept_id。

    SELECT *
FROM t_emp AS a
WHERE EXISTS (
    SELECT id
    FROM t_emp
    WHERE a.dept_id = (
        SELECT id
        FROM t_dept
        WHERE name = '研发部' OR name = '质量部'));

② 对比

IN 与 EXISTS 可等价替换，但原理不同。

IN EXISTS
场景 子表数据量小 子表数据量大
原理 1. 查询子表所有数据并保存到内存；
2. 循环子表，嵌套循环主表，逐条匹配记录。		 1. 查询主表所有数据并保存到内存；
2. 循环主表，嵌套循环子表，逐条匹配记录。
核心 先从后主，子表驱动主表 先主后从，主表驱动子表

示例分析：主表有 10000 条数据，子表有 200 条记录

（即子表是小表）

  • 使用 IN：先从后主，访问 200 次数据库（👍）

    for(int i = 0; i < 200; i++) {
    for (int j = 0; j < 10000; j++) {
        ...
    }
}

  • 使用 EXISTS：先主后从，访问 10000 次数据库。

    for(int i = 0; i < 10000; i++) {
    for (int j = 0; j < 200; j++) {
        ...
    }
}

4.2.2、ALL & ANY

通常可与 MAX/MIN 等价替换。

① ALL

查询高于【研发部】所有员工工资都高的员工信息：

  • 列子查询：子子查询 dept 研发部 id，子查询 emp 工资，外层查询 ALL 比较。

    SELECT *
FROM t_emp
WHERE salary > ALL(
    # 子查询
    SELECT salary
    FROM t_emp
    WHERE dept_id = (
        # 子子查询
        SELECT id
        FROM t_dept
        WHERE name = '研发部'));

  • 等价：子子查询 dept 研发部 id，子查询 emp MAX(工资)，外层查询 > 比较。

    SELECT *
FROM t_emp
WHERE salary > (
    # 子查询
    SELECT MAX(salary)
    FROM t_emp
    WHERE dept_id = (
        # 子子查询
        SELECT id
        FROM t_dept
        WHERE name = '研发部'));

② ANY

查询高于【研发部】任一员工工资的员工信息：

  • 列子查询：相比例 2，改为 ANY

    SELECT *
FROM t_emp
WHERE salary > ANY(
    # 子查询
    SELECT salary
    FROM t_emp
    WHERE dept_id = (
        # 子子查询
        SELECT id
        FROM t_dept
        WHERE name = '研发部'));

  • 等价：相比 2，改为 MIN

    SELECT *
FROM t_emp
WHERE salary > (
    # 子查询
    SELECT MIN(salary)
    FROM t_emp
    WHERE dept_id = (
        # 子子查询
        SELECT id
        FROM t_dept
        WHERE name = '研发部'));

4.3、行子查询

常用操作符

=、!= 或 <>、[NOT] IN

示例：查询与【3 号员工】的工资、部门都相同的员工信息。

Hint：此写法包括 3 号员工本身。

SELECT *
FROM t_emp
WHERE (salary, dept_id) = (
    # 子查询
    SELECT salary, dept_id
    FROM t_emp
    WHERE id = '3');

4.4、表子查询

常用操作符

IN

示例：查询与【1号，3 号员工】的工资，部门相同的员工。

SELECT *
FROM t_emp
WHERE (salary, dept_id) IN (
    # 子查询
    SELECT salary, dept_id
    FROM t_emp
    WHERE id = '1' OR id = '3');
posted @ 2022-03-14 01:04  Jaywee  阅读(134)  评论(0)  编辑  收藏  举报

☝

👇