MySQL基础-3

多表查询

多表查询是指从多张表中查询数据。

在实际项目中，为了减少数据冗余，一项业务的数据通常会被拆分到多张表中，因此查询时经常需要将多张表关联起来。

多表关系与查询分类

一对多

例如：

一个部门可以有多个员工
一个员工只能属于一个部门

实现方式：

在“多”的一方建立外键，指向“一”的一方的主键

结构：

dept.id ← emp.dept_id

多对多

例如：

一个学生可以选择多门课程
一门课程可以被多个学生选择

多对多不能只靠双方各自的一个外键直接表示，通常需要建立中间表。

结构：

student
   ↑
student_course
   ↓
course

建表示例：

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    no VARCHAR(20) NOT NULL UNIQUE
);

CREATE TABLE course (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL
);

CREATE TABLE student_course (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    CONSTRAINT fk_sc_student
        FOREIGN KEY (student_id) REFERENCES student (id),
    CONSTRAINT fk_sc_course
        FOREIGN KEY (course_id) REFERENCES course (id)
);

中间表至少包含两个外键，分别指向两张主表。

一对一

例如：

用户基本信息
用户教育信息

一对一常用于单表拆分，将常用字段与不常用字段分开保存。

实现方式：

在任意一方建立外键，并给外键添加 UNIQUE 约束

示例：

CREATE TABLE tb_user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    age INT,
    gender CHAR(1),
    phone CHAR(11)
);

CREATE TABLE tb_user_edu (
    id INT PRIMARY KEY AUTO_INCREMENT,
    degree VARCHAR(20),
    major VARCHAR(50),
    university VARCHAR(50),
    user_id INT UNIQUE,
    CONSTRAINT fk_user_edu_user
        FOREIGN KEY (user_id) REFERENCES tb_user (id)
);

笛卡尔积

直接查询两张表：

SELECT *
FROM emp2, dept;

如果 emp2 有 10 条记录，dept 有 5 条记录，结果会得到：

10 × 5 = 50 条

这称为笛卡尔积，即两个集合的所有组合情况。

多表查询通常需要通过连接条件消除无效的笛卡尔积：

SELECT *
FROM emp2, dept
WHERE emp2.dept_id = dept.id;

多表查询时，必须先找出表与表之间的关联字段。

多表查询分类

多表查询
├─ 连接查询
│  ├─ 内连接
│  ├─ 外连接
│  └─ 自连接
├─ 联合查询
└─ 子查询
   ├─ 标量子查询
   ├─ 列子查询
   ├─ 行子查询
   └─ 表子查询

连接查询

内连接

内连接查询两张表中满足连接条件的交集部分。

隐式内连接：

SELECT 字段列表
FROM 表1, 表2
WHERE 连接条件;

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

SELECT emp2.name,
       dept.name
FROM emp2, dept
WHERE emp2.dept_id = dept.id;

使用别名：

SELECT e.name AS employee_name,
       d.name AS department_name
FROM emp2 e, dept d
WHERE e.dept_id = d.id;

显式内连接：

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

示例：

SELECT e.name AS employee_name,
       d.name AS department_name
FROM emp2 e
INNER JOIN dept d
    ON e.dept_id = d.id;

INNER 可以省略：

SELECT e.name,
       d.name
FROM emp2 e
JOIN dept d
    ON e.dept_id = d.id;

内连接特点：

只返回两张表能够匹配的记录。
没有部门的员工不会被查询出来。
没有员工的部门也不会被查询出来。

左外连接

左外连接会返回：

左表的全部数据 + 两张表匹配的数据

语法：

SELECT 字段列表
FROM 表1
LEFT [OUTER] JOIN 表2
ON 连接条件;

查询所有员工以及员工对应的部门信息：

SELECT e.*,
       d.name AS department_name
FROM emp2 e
LEFT JOIN dept d
    ON e.dept_id = d.id;

即使某个员工没有分配部门，该员工仍然会被查询出来，部门字段显示为 NULL。

右外连接

右外连接会返回：

右表的全部数据 + 两张表匹配的数据

语法：

SELECT 字段列表
FROM 表1
RIGHT [OUTER] JOIN 表2
ON 连接条件;

查询所有部门以及部门中的员工：

SELECT d.*,
       e.name AS employee_name
FROM emp2 e
RIGHT JOIN dept d
    ON e.dept_id = d.id;

即使某个部门没有员工，该部门也会被查询出来，员工字段显示为 NULL。

右外连接通常可以通过交换表的位置改写为左外连接：

SELECT d.*,
       e.name AS employee_name
FROM dept d
LEFT JOIN emp2 e
    ON e.dept_id = d.id;

实际开发中，左外连接通常更常见。

自连接

自连接是当前表与自身进行连接查询。

语法：

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

自连接必须为同一张表设置不同别名，将其看作两张逻辑表。

员工表中：

id         员工编号
managerid  直属领导编号

查询员工姓名及其直属领导姓名：

SELECT e.name AS employee_name,
       m.name AS manager_name
FROM emp2 e
JOIN emp2 m
    ON e.managerid = m.id;

上面的内连接不会显示没有领导的员工。

需要显示所有员工时，使用左外连接：

SELECT e.name AS employee_name,
       m.name AS manager_name
FROM emp2 e
LEFT JOIN emp2 m
    ON e.managerid = m.id;

此时没有直属领导的员工，manager_name 为 NULL。

多表连接

查询员工姓名、部门名称和工资等级，可能需要同时连接三张表。

薪资等级表：

CREATE TABLE salgrade (
    grade INT COMMENT '薪资等级',
    losal INT COMMENT '最低薪资',
    hisal INT COMMENT '最高薪资'
) COMMENT '薪资等级表';

INSERT INTO salgrade
VALUES
    (1, 0, 3000),
    (2, 3001, 5000),
    (3, 5001, 8000),
    (4, 8001, 10000),
    (5, 10001, 15000),
    (6, 15001, 20000),
    (7, 20001, 25000),
    (8, 25001, 30000);

查询员工、部门与工资等级：

SELECT e.name AS employee_name,
       d.name AS department_name,
       e.salary,
       s.grade
FROM emp2 e
JOIN dept d
    ON e.dept_id = d.id
JOIN salgrade s
    ON e.salary BETWEEN s.losal AND s.hisal;

联合查询

联合查询用于将多个查询结果合并成一个结果集。

基本语法：

SELECT 字段列表
FROM 表A
UNION [ALL]
SELECT 字段列表
FROM 表B;

查询薪资低于 5000 的员工，以及年龄大于 50 岁的员工：

SELECT *
FROM emp2
WHERE salary < 5000

UNION ALL

SELECT *
FROM emp2
WHERE age > 50;

UNION ALL：

直接合并全部结果。
不去除重复数据。
通常效率更高。

UNION：

合并结果后自动去重。
去重会带来额外开销。

SELECT *
FROM emp2
WHERE salary < 5000

UNION

SELECT *
FROM emp2
WHERE age > 50;

联合查询注意事项：

每个查询返回的列数必须一致。
对应位置的字段类型应保持兼容。
最终结果的列名通常由第一个查询决定。
不需要去重时优先使用 UNION ALL。

错误示例：

SELECT id, name
FROM emp2

UNION ALL

SELECT id, name, age
FROM emp2;

两次查询的列数不同，因此无法联合。

子查询

SQL 语句中嵌套的 SELECT 语句称为子查询，也叫嵌套查询。

基本形式：

SELECT *
FROM t1
WHERE column1 = (
    SELECT column1
    FROM t2
);

子查询外部的语句可以是：

SELECT
INSERT
UPDATE
DELETE

根据子查询返回结果，可以分为：

类型	子查询结果
标量子查询	单个值
列子查询	一列多行
行子查询	一行多列
表子查询	多行多列

根据子查询出现的位置，也可以分为：

WHERE 后的子查询
FROM 后的子查询
SELECT 后的子查询

标量子查询

标量子查询返回单个值，例如数字、字符串或日期。

常用运算符：

=  <>  >  >=  <  <=

查询“销售部”的所有员工：

第一步，查询销售部编号：

SELECT id
FROM dept
WHERE name = '销售部';

第二步，根据部门编号查询员工：

SELECT *
FROM emp2
WHERE dept_id = 4;

合并为标量子查询：

SELECT *
FROM emp2
WHERE dept_id = (
    SELECT id
    FROM dept
    WHERE name = '销售部'
);

查询在“韦一笑”之后入职的员工：

SELECT *
FROM emp2
WHERE entrydate > (
    SELECT entrydate
    FROM emp2
    WHERE name = '韦一笑'
);

使用 =、> 等单值运算符时，子查询必须只返回一个值，否则会报错。

列子查询

列子查询返回一列数据，可以包含多行。

常用运算符：

运算符	说明
`IN`	在返回的集合中
`NOT IN`	不在返回的集合中
`ANY`	与结果集合中的任意一个值比较，任意一个满足即可
`SOME`	与 `ANY` 等价
`ALL`	与结果集合中的所有值比较，全部满足才成立

查询“销售部”和“市场部”的所有员工：

SELECT *
FROM emp2
WHERE dept_id IN (
    SELECT id
    FROM dept
    WHERE name IN ('销售部', '市场部')
);

查询比财务部所有员工工资都高的员工：

SELECT *
FROM emp2
WHERE salary > ALL (
    SELECT salary
    FROM emp2
    WHERE dept_id = (
        SELECT id
        FROM dept
        WHERE name = '财务部'
    )
);

查询比研发部任意一名员工工资高的员工：

SELECT *
FROM emp2
WHERE salary > ANY (
    SELECT salary
    FROM emp2
    WHERE dept_id = (
        SELECT id
        FROM dept
        WHERE name = '研发部'
    )
);

便于理解：

> ALL  相当于大于集合中的最大值
> ANY  相当于大于集合中的最小值
< ALL  相当于小于集合中的最小值
< ANY  相当于小于集合中的最大值

行子查询

行子查询返回一行数据，一行中可以有多个字段。

常用运算符：

=  <>  IN  NOT IN

查询与“杨逍”的薪资和直属领导都相同的员工：

第一步：

SELECT salary, managerid
FROM emp2
WHERE name = '杨逍';

合并为行子查询：

SELECT *
FROM emp2
WHERE (salary, managerid) = (
    SELECT salary, managerid
    FROM emp2
    WHERE name = '杨逍'
);

不包含杨逍本人：

SELECT *
FROM emp2
WHERE (salary, managerid) = (
    SELECT salary, managerid
    FROM emp2
    WHERE name = '杨逍'
)
AND name <> '杨逍';

表子查询

表子查询返回多行多列，可以将结果看作一张临时表。

查询与“韦一笑”或“杨逍”的职位和薪资相同的员工：

SELECT *
FROM emp2
WHERE (job, salary) IN (
    SELECT job, salary
    FROM emp2
    WHERE name IN ('韦一笑', '杨逍')
);

查询 2000 年 1 月 1 日之后入职的员工及其部门信息：

SELECT e.*,
       d.name AS department_name
FROM (
    SELECT *
    FROM emp2
    WHERE entrydate > '2000-01-01'
) e
LEFT JOIN dept d
    ON e.dept_id = d.id;

FROM 后面的子查询必须设置别名：

(...) e

这里的 e 就代表子查询生成的临时结果表。

相关子查询

相关子查询会引用外层查询中的字段。

查询工资低于本部门平均工资的员工：

SELECT e.*
FROM emp2 e
WHERE e.salary < (
    SELECT AVG(e2.salary)
    FROM emp2 e2
    WHERE e2.dept_id = e.dept_id
);

执行时，可以理解为：

外层取出一名员工。
子查询根据该员工的部门计算平均工资。
判断该员工工资是否低于部门平均工资。
对下一名员工重复上述过程。

查询所有部门信息，并统计每个部门的员工人数：

SELECT d.id,
       d.name,
       (
           SELECT COUNT(*)
           FROM emp2 e
           WHERE e.dept_id = d.id
       ) AS employee_count
FROM dept d;

综合练习

以下案例对应基础篇多表查询练习的核心题型。

1. 查询员工姓名、年龄、职位和部门名称

隐式内连接：

SELECT e.name,
       e.age,
       e.job,
       d.name AS department_name
FROM emp2 e, dept d
WHERE e.dept_id = d.id;

2. 查询年龄小于 30 岁的员工及其部门信息

SELECT e.name,
       e.age,
       e.job,
       d.name AS department_name
FROM emp2 e
JOIN dept d
    ON e.dept_id = d.id
WHERE e.age < 30;

3. 查询拥有员工的部门 ID 和部门名称

SELECT DISTINCT d.id,
                d.name
FROM emp2 e
JOIN dept d
    ON e.dept_id = d.id;

使用 DISTINCT 去除重复部门。

4. 查询年龄大于 40 岁的所有员工及其部门，没有部门的员工也要显示

SELECT e.*,
       d.name AS department_name
FROM emp2 e
LEFT JOIN dept d
    ON e.dept_id = d.id
WHERE e.age > 40;

5. 查询所有员工的工资等级

SELECT e.name,
       e.salary,
       s.grade
FROM emp2 e
JOIN salgrade s
    ON e.salary BETWEEN s.losal AND s.hisal;

6. 查询研发部所有员工的信息及工资等级

SELECT e.*,
       s.grade
FROM emp2 e
JOIN dept d
    ON e.dept_id = d.id
JOIN salgrade s
    ON e.salary BETWEEN s.losal AND s.hisal
WHERE d.name = '研发部';

7. 查询研发部员工的平均工资

SELECT AVG(e.salary) AS average_salary
FROM emp2 e
JOIN dept d
    ON e.dept_id = d.id
WHERE d.name = '研发部';

8. 查询工资比“灭绝”高的员工信息

SELECT *
FROM emp2
WHERE salary > (
    SELECT salary
    FROM emp2
    WHERE name = '灭绝'
);

9. 查询工资高于全体员工平均工资的员工

SELECT *
FROM emp2
WHERE salary > (
    SELECT AVG(salary)
    FROM emp2
);

10. 查询工资低于本部门平均工资的员工

SELECT e.*
FROM emp2 e
WHERE e.salary < (
    SELECT AVG(e2.salary)
    FROM emp2 e2
    WHERE e2.dept_id = e.dept_id
);

11. 查询所有部门，并统计每个部门的员工人数

SELECT d.id,
       d.name,
       (
           SELECT COUNT(*)
           FROM emp2 e
           WHERE e.dept_id = d.id
       ) AS employee_count
FROM dept d;

也可以使用左外连接与分组：

SELECT d.id,
       d.name,
       COUNT(e.id) AS employee_count
FROM dept d
LEFT JOIN emp2 e
    ON e.dept_id = d.id
GROUP BY d.id, d.name;

这里应使用：

COUNT(e.id)

不要使用：

COUNT(*)

因为左外连接会为没有员工的部门保留一行，使用 COUNT(*) 可能将其统计为 1。

12. 查询所有学生的选课情况

SELECT s.name AS student_name,
       s.no AS student_no,
       c.name AS course_name
FROM student_course sc
JOIN student s
    ON sc.student_id = s.id
JOIN course c
    ON sc.course_id = c.id;

多表查询小结

内连接：查询两张表能够匹配的部分
左连接：左表全部 + 匹配数据
右连接：右表全部 + 匹配数据
自连接：一张表以不同别名连接自身
UNION：合并结果并去重
UNION ALL：合并全部结果，不去重
标量子查询：返回一个值
列子查询：返回一列多行
行子查询：返回一行多列
表子查询：返回多行多列

事务

事务是一组操作的集合，是一个不可分割的工作单位。

事务会将一组操作作为一个整体提交或撤销：

要么全部成功
要么全部失败

经典场景：银行转账。

张三向李四转账 1000 元，需要执行：

1. 张三余额减少 1000
2. 李四余额增加 1000

两步必须同时成功。若第一步成功、第二步失败，数据就会不一致，因此需要事务。

事务操作

准备账户表

CREATE TABLE account (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '账户ID',
    name VARCHAR(10) NOT NULL COMMENT '姓名',
    money DECIMAL(10, 2) NOT NULL COMMENT '余额'
) COMMENT '账户表';

INSERT INTO account (name, money)
VALUES
    ('张三', 2000),
    ('李四', 2000);

普通转账操作：

UPDATE account
SET money = money - 1000
WHERE name = '张三';

UPDATE account
SET money = money + 1000
WHERE name = '李四';

默认情况下，MySQL 通常开启自动提交：

SELECT @@autocommit;

结果为 1 表示开启自动提交。

开启自动提交时，每一条独立的 DML 语句执行成功后会立即提交。

方式一：关闭自动提交

关闭当前会话的自动提交：

SET autocommit = 0;

执行转账：

UPDATE account
SET money = money - 1000
WHERE name = '张三';

UPDATE account
SET money = money + 1000
WHERE name = '李四';

全部成功后提交：

COMMIT;

出现错误时回滚：

ROLLBACK;

重新开启自动提交：

SET autocommit = 1;

SET autocommit = 0 会持续影响当前连接，直到重新设置或连接断开。

方式二：显式开启事务

开启事务：

START TRANSACTION;

也可以写成：

BEGIN;

完整转账过程：

START TRANSACTION;

UPDATE account
SET money = money - 1000
WHERE name = '张三';

UPDATE account
SET money = money + 1000
WHERE name = '李四';

COMMIT;

发生异常时：

START TRANSACTION;

UPDATE account
SET money = money - 1000
WHERE name = '张三';

-- 中间发生错误

ROLLBACK;

事务控制语句：

语句	功能
`START TRANSACTION` / `BEGIN`	开启事务
`COMMIT`	提交事务，使修改永久生效
`ROLLBACK`	回滚事务，撤销未提交的修改

事务操作注意事项

事务主要用于保证一组 DML 操作的完整性。
COMMIT 后无法通过普通 ROLLBACK 撤销。
ROLLBACK 只能撤销当前事务中尚未提交的修改。
多数 DDL 语句会触发隐式提交，不应与普通 DML 事务混用。
事务通常由支持事务的存储引擎实现，InnoDB 支持事务。

ACID 与并发问题

事务具有四个基本特性，简称 ACID。

特性	英文	说明
原子性	Atomicity	事务中的操作要么全部成功，要么全部失败
一致性	Consistency	事务完成前后，数据必须保持合法和一致
隔离性	Isolation	并发事务之间尽量互不干扰
持久性	Durability	事务提交后，对数据的修改会被永久保存

原子性

转账中的扣款和加款是一个整体：

不能只扣款而不加款

发生错误时，整个事务应回滚。

一致性

假设转账前两人总余额为：

2000 + 2000 = 4000

转账后仍然应该为：

1000 + 3000 = 4000

数据库应从一个一致状态转移到另一个一致状态。

隔离性

多个事务同时执行时，一个事务的中间状态不应该随意影响另一个事务。

持久性

事务提交后，即使数据库服务重新启动，已经提交的数据也应该保留。

并发事务问题

多个事务同时访问和修改相同数据时，可能出现以下问题：

问题	描述
脏读	一个事务读取到另一个事务尚未提交的数据
不可重复读	同一事务中两次读取同一条记录，结果不同
幻读	同一事务中按相同条件查询，前后出现了新增或消失的记录

脏读

事务 A 修改数据，但尚未提交
事务 B 读取到了事务 A 修改后的数据
事务 A 随后回滚
事务 B 读到的就是从未真正生效的数据

示意：

事务 A                         事务 B
------------------------------------------------
START TRANSACTION
余额改为 1000
                               查询余额，读到 1000
ROLLBACK
实际余额恢复为 2000

事务 B 读取到的 1000 就是脏数据。

不可重复读

事务 A 第一次读取某行数据
事务 B 修改该行并提交
事务 A 再次读取同一行，结果发生变化

示意：

事务 A                         事务 B
------------------------------------------------
读取余额：2000
                               修改余额为 3000
                               COMMIT
再次读取余额：3000

同一事务中，两次读取同一条记录得到不同结果。

幻读

事务 A 按条件查询，没有发现某条记录
事务 B 插入符合条件的记录并提交
事务 A 再次执行相同条件查询，发现多出一条记录

看起来像出现了“幻影”。

教程常用“查询不存在但插入时提示已存在”的场景帮助理解幻读；从数据库理论上说，幻读核心是同一范围查询前后出现新增或消失的行。

隔离级别

事务隔离级别用于控制事务之间能够看到多少数据。

MySQL InnoDB 支持四种标准隔离级别：

隔离级别	脏读	不可重复读	幻读	隔离程度
`READ UNCOMMITTED`	可能	可能	可能	最低
`READ COMMITTED`	避免	可能	可能	较低
`REPEATABLE READ`	避免	避免	标准定义下可能	较高
`SERIALIZABLE`	避免	避免	避免	最高

InnoDB 默认隔离级别为：

REPEATABLE READ

InnoDB 在 REPEATABLE READ 下结合 MVCC 和间隙锁等机制，实际能够避免许多普通幻读场景。教材中的表格主要按照 SQL 标准的经典定义理解。

查看当前隔离级别

SELECT @@TRANSACTION_ISOLATION;

查看全局隔离级别：

SELECT @@GLOBAL.TRANSACTION_ISOLATION;

设置当前会话的隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

设置全局隔离级别

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

全局设置通常影响之后新建立的连接，不一定立即改变已经存在的会话。

READ UNCOMMITTED：读未提交

特点：

隔离级别最低。
一个事务可以读取另一个事务尚未提交的数据。
可能产生脏读、不可重复读和幻读。
并发能力较高，但数据可靠性最低。

READ COMMITTED：读已提交

特点：

只能读取其他事务已经提交的数据。
可以避免脏读。
同一事务内两次读取结果仍可能不同。
可能产生不可重复读和幻读。

REPEATABLE READ：可重复读

特点：

MySQL InnoDB 的默认隔离级别。
同一事务中，多次普通一致性读取通常看到相同的数据快照。
可以避免脏读和不可重复读。
隔离性与并发性能较为均衡。

SERIALIZABLE：串行化

特点：

隔离级别最高。
事务之间的执行效果接近串行执行。
可以避免脏读、不可重复读和幻读。
并发性能最低，容易产生等待和阻塞。

隔离级别选择

隔离级别越高：
数据一致性通常越强
并发性能通常越低

隔离级别越低：
并发性能通常越高
数据异常风险通常越大

实际项目中不应只追求最高隔离级别，应根据业务一致性要求与性能要求进行选择。

事务小结

事务：一组不可分割的数据库操作
COMMIT：提交修改
ROLLBACK：撤销未提交修改
ACID：原子性、一致性、隔离性、持久性
并发问题：脏读、不可重复读、幻读
默认隔离级别：REPEATABLE READ