← 返回

MySQL 基础复习手册（强化校订版）

首发 2026/07/30 阅读 1 评论 0 更新 2026/07/30

MySQL 基础复习手册（强化校订版）

适用范围：MySQL 8.x。目标是复习 SQL、准备 Python 开发面试，而不是照抄安装教程。
使用方法：先浏览第 0 章的五张表；需要动手时执行初始化脚本。后文所有命令都基于同一批数据。
约定：SQL 关键字大写，表名和字段名使用小写 snake_case；结果集只展示能说明问题的列和行。

0. 统一示例数据库
1. 数据库、DBMS 与 SQL
2. SQL 分类与书写规则
3. DDL：数据库和表结构
4. 数据类型、约束与表关系
5. DML：增、改、删
6. DQL：单表查询
7. 常用函数
8. 多表查询
9. 子查询
10. 事务与并发
11. DCL：用户与权限
12. 索引入门
13. 面试速答与综合练习
14. 最后检查清单

0. 统一示例数据库

这套数据故意保留了几个边界情况：

人事部 暂时没有员工，用来演示外连接。
周宇 暂未分配部门，用来观察内连接与外连接的区别。
部分员工的 bonus 为 NULL，用来演示空值和聚合函数。
员工存在上下级关系，用来演示自连接。
招聘系统 暂无成员，用来演示 NOT EXISTS。

0.1 `departments`：部门表

text

+---------+-----------+--------+
| dept_id | dept_name | city   |
+---------+-----------+--------+
|       1 | 研发部    | 上海   |
|       2 | 市场部    | 北京   |
|       3 | 财务部    | 上海   |
|       4 | 人事部    | 深圳   |
+---------+-----------+--------+
4 rows in set

0.2 `employees`：员工表

这里只展示后文最常用的字段，完整字段见初始化脚本。

text

+--------+----------+-----+----------+---------+------------+---------+
| emp_id | emp_name | age | salary   | bonus   | manager_id | dept_id |
+--------+----------+-----+----------+---------+------------+---------+
|    101 | 张伟     |  29 | 18000.00 | 1500.00 |       NULL |       1 |
|    102 | 李娜     |  26 | 13000.00 |    NULL |        101 |       1 |
|    103 | 王强     |  35 | 16000.00 | 2000.00 |       NULL |       2 |
|    104 | 赵敏     |  24 |  9000.00 |    NULL |        103 |       2 |
|    105 | 陈晨     |  31 | 12000.00 |  500.00 |       NULL |       3 |
|    106 | 周宇     |  22 |  8000.00 |    NULL |        101 |    NULL |
+--------+----------+-----+----------+---------+------------+---------+
6 rows in set

0.3 `projects`：项目表

text

+------------+--------------+---------------+-----------+
| project_id | project_name | owner_dept_id | budget    |
+------------+--------------+---------------+-----------+
|        201 | 订单平台     |             1 | 300000.00 |
|        202 | 数据看板     |             2 | 180000.00 |
|        203 | 年度审计     |             3 |  80000.00 |
|        204 | 招聘系统     |             4 | 120000.00 |
+------------+--------------+---------------+-----------+
4 rows in set

0.4 `employee_projects`：员工项目中间表

一名员工可参加多个项目，一个项目也可有多名员工，因此使用中间表表示多对多关系。

text

+--------+------------+--------------+
| emp_id | project_id | project_role |
+--------+------------+--------------+
|    101 |        201 | 负责人       |
|    102 |        201 | 开发         |
|    101 |        202 | 顾问         |
|    103 |        202 | 负责人       |
|    104 |        202 | 运营         |
|    105 |        203 | 负责人       |
+--------+------------+--------------+
6 rows in set

0.5 `accounts`：账户表

text

+------------+------------+---------+
| account_id | owner_name | balance |
+------------+------------+---------+
|          1 | 张伟       | 5000.00 |
|          2 | 李娜       | 3000.00 |
+------------+------------+---------+
2 rows in set

0.6 表关系

departments 1 -> N employees：外键放在多的一方，即 employees.dept_id。
departments 1 -> N projects：一个部门可负责多个项目。
employees N <-> N projects：通过 employee_projects 中间表连接。
employees 1 -> N employees：manager_id 指向同表的 emp_id。

0.7 完整初始化脚本

脚本会删除同名示例表，只应在练习数据库中执行。
后文展示的结果均基于刚初始化的数据；带有 INSERT、UPDATE、DELETE、DDL 的片段是独立语法演示，不要全部连续执行。需要恢复时重新运行本脚本。

sql

CREATE DATABASE IF NOT EXISTS mysql_review
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

USE mysql_review;

DROP TABLE IF EXISTS employee_projects;
DROP TABLE IF EXISTS projects;
DROP TABLE IF EXISTS accounts;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;

CREATE TABLE departments (
    dept_id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(30) NOT NULL,
    city      VARCHAR(30) NOT NULL,
    CONSTRAINT uk_departments_name UNIQUE (dept_name)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

CREATE TABLE employees (
    emp_id     INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    emp_name   VARCHAR(30) NOT NULL,
    gender     VARCHAR(8) NOT NULL,
    age        TINYINT UNSIGNED NOT NULL,
    email      VARCHAR(100) NOT NULL,
    salary     DECIMAL(10, 2) NOT NULL,
    bonus      DECIMAL(10, 2) NULL,
    hire_date  DATE NOT NULL,
    status     VARCHAR(10) NOT NULL DEFAULT 'ACTIVE',
    manager_id INT UNSIGNED NULL,
    dept_id    INT UNSIGNED NULL,
    CONSTRAINT uk_employees_email UNIQUE (email),
    CONSTRAINT chk_employees_gender
        CHECK (gender IN ('男', '女', '未说明')),
    CONSTRAINT chk_employees_age
        CHECK (age BETWEEN 18 AND 70),
    CONSTRAINT chk_employees_salary
        CHECK (salary >= 0),
    CONSTRAINT chk_employees_bonus
        CHECK (bonus IS NULL OR bonus >= 0),
    CONSTRAINT chk_employees_status
        CHECK (status IN ('ACTIVE', 'LEAVE')),
    INDEX idx_employees_dept_salary (dept_id, salary),
    INDEX idx_employees_manager (manager_id),
    CONSTRAINT fk_employees_department
        FOREIGN KEY (dept_id) REFERENCES departments (dept_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_employees_manager
        FOREIGN KEY (manager_id) REFERENCES employees (emp_id)
        ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

CREATE TABLE projects (
    project_id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_name  VARCHAR(50) NOT NULL,
    owner_dept_id INT UNSIGNED NOT NULL,
    budget        DECIMAL(12, 2) NOT NULL,
    start_date    DATE NOT NULL,
    CONSTRAINT uk_projects_name UNIQUE (project_name),
    CONSTRAINT chk_projects_budget CHECK (budget >= 0),
    CONSTRAINT fk_projects_department
        FOREIGN KEY (owner_dept_id) REFERENCES departments (dept_id)
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

CREATE TABLE employee_projects (
    emp_id       INT UNSIGNED NOT NULL,
    project_id   INT UNSIGNED NOT NULL,
    project_role VARCHAR(20) NOT NULL,
    joined_on    DATE NOT NULL,
    PRIMARY KEY (emp_id, project_id),
    INDEX idx_employee_projects_project (project_id),
    CONSTRAINT fk_ep_employee
        FOREIGN KEY (emp_id) REFERENCES employees (emp_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_ep_project
        FOREIGN KEY (project_id) REFERENCES projects (project_id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

CREATE TABLE accounts (
    account_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    owner_name VARCHAR(30) NOT NULL,
    balance    DECIMAL(12, 2) NOT NULL,
    CONSTRAINT chk_accounts_balance CHECK (balance >= 0)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

INSERT INTO departments (dept_id, dept_name, city) VALUES
    (1, '研发部', '上海'),
    (2, '市场部', '北京'),
    (3, '财务部', '上海'),
    (4, '人事部', '深圳');

-- 先插入主管，再插入引用主管的员工。
INSERT INTO employees
    (emp_id, emp_name, gender, age, email, salary, bonus,
     hire_date, status, manager_id, dept_id)
VALUES
    (101, '张伟', '男', 29, 'zhangwei@example.com', 18000, 1500,
     '2021-03-15', 'ACTIVE', NULL, 1),
    (103, '王强', '男', 35, 'wangqiang@example.com', 16000, 2000,
     '2020-11-20', 'ACTIVE', NULL, 2),
    (105, '陈晨', '女', 31, 'chenchen@example.com', 12000, 500,
     '2022-09-05', 'LEAVE', NULL, 3);

INSERT INTO employees
    (emp_id, emp_name, gender, age, email, salary, bonus,
     hire_date, status, manager_id, dept_id)
VALUES
    (102, '李娜', '女', 26, 'lina@example.com', 13000, NULL,
     '2023-07-01', 'ACTIVE', 101, 1),
    (104, '赵敏', '女', 24, 'zhaomin@example.com', 9000, NULL,
     '2024-02-10', 'ACTIVE', 103, 2),
    (106, '周宇', '男', 22, 'zhouyu@example.com', 8000, NULL,
     '2025-06-18', 'ACTIVE', 101, NULL);

INSERT INTO projects
    (project_id, project_name, owner_dept_id, budget, start_date)
VALUES
    (201, '订单平台', 1, 300000, '2025-01-01'),
    (202, '数据看板', 2, 180000, '2025-03-01'),
    (203, '年度审计', 3, 80000, '2025-06-01'),
    (204, '招聘系统', 4, 120000, '2025-07-01');

INSERT INTO employee_projects
    (emp_id, project_id, project_role, joined_on)
VALUES
    (101, 201, '负责人', '2025-01-01'),
    (102, 201, '开发',   '2025-01-10'),
    (101, 202, '顾问',   '2025-03-01'),
    (103, 202, '负责人', '2025-03-01'),
    (104, 202, '运营',   '2025-03-08'),
    (105, 203, '负责人', '2025-06-01');

INSERT INTO accounts (account_id, owner_name, balance) VALUES
    (1, '张伟', 5000.00),
    (2, '李娜', 3000.00);

1. 数据库、DBMS 与 SQL

名称	含义	本例
数据库（Database）	按一定结构保存的数据集合	`mysql_review`
DBMS	管理数据库的软件	MySQL Server
SQL	与关系型数据库交互的语言	`SELECT`、`INSERT` 等
表	同一类实体的数据集合	`employees`
行	一条记录	张伟这名员工
列	实体的一项属性	`salary`

关系型数据库以表组织数据，并通过主键、外键建立关系。SQL 有标准，但不同 DBMS 仍存在方言差异，例如 MySQL 使用 LIMIT 分页；因此不能理解成“所有数据库写法完全一样”。

1.1 连接 MySQL

bash

mysql -h 127.0.0.1 -P 3306 -u root -p

-h：服务器地址。
-P：端口，MySQL 默认是 3306，注意大写。
-u：用户名。
-p：提示输入密码。不要把密码直接写进命令，避免进入终端历史。

连接后可确认服务器版本：

sql

SELECT VERSION();

MySQL Server 是服务端，命令行、DataGrip、DBeaver 等只是客户端。服务的启动方式取决于系统和安装方式，例如：

bash

# Windows 管理员终端，服务名可能不是 MySQL80
net start MySQL80
net stop MySQL80

# 常见 Linux 发行版
sudo systemctl status mysql
sudo systemctl start mysql

1.2 最常用的环境检查

sql

SHOW DATABASES;
SELECT DATABASE();
USE mysql_review;
SHOW TABLES;
DESC employees;
SHOW CREATE TABLE employees;

终端示意：

text

mysql> SELECT DATABASE();
+----------------+
| DATABASE()     |
+----------------+
| mysql_review   |
+----------------+
1 row in set

2. SQL 分类与书写规则

2.1 SQL 分类

分类	作用	常见关键字
DDL	定义数据库对象	`CREATE`、`ALTER`、`DROP`、`TRUNCATE`
DML	操作表中数据	`INSERT`、`UPDATE`、`DELETE`
DQL	查询数据，常见教学分类	`SELECT`
DCL	管理用户和权限	`CREATE USER`、`GRANT`、`REVOKE`
TCL	控制事务	`COMMIT`、`ROLLBACK`、`SAVEPOINT`

严格来说，不同资料对 SELECT 的分类略有差异；学习时单独称为 DQL 即可。

2.2 书写规则

sql

-- 单行注释
# MySQL 单行注释
/* 多行注释 */

SELECT emp_id, emp_name
FROM employees
WHERE status = 'ACTIVE';

语句通常以分号结束。
SQL 关键字通常不区分大小写，但推荐大写。
表名大小写是否敏感会受操作系统和配置影响，统一使用小写最稳妥。
字符串比较是否区分大小写由排序规则决定；名称含 _ci 的排序规则通常不区分大小写。
字符串和日期使用单引号；反引号主要用于引用特殊标识符，不要滥用。
MySQL 的 -- 注释符后必须跟空格或控制字符，写成 -- 注释。
条件优先使用标准 AND、OR、NOT，不要依赖 &&、|| 等方言写法。

3. DDL：数据库和表结构

DDL 改的是“结构”，不是普通业务数据。很多 DDL 会隐式提交事务，生产环境执行前必须确认。

3.1 数据库操作

sql

CREATE DATABASE IF NOT EXISTS mysql_review
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

SHOW DATABASES;
USE mysql_review;
SELECT DATABASE();

-- 危险操作示意：删除数据库及其中全部对象，不要对 mysql_review 执行
DROP DATABASE IF EXISTS old_demo_db;

utf8mb4 才是 MySQL 中完整的 UTF-8，能保存中文和 emoji。

IF NOT EXISTS 只是在数据库已存在时避免报错，不会把已有数据库的字符集或排序规则改成新值。可用下面的命令核对：

sql

SHOW CREATE DATABASE mysql_review;

3.2 表操作

sql

SHOW TABLES;
DESC employees;
SHOW CREATE TABLE employees;

修改表结构：

sql

-- 添加字段
ALTER TABLE employees
ADD COLUMN nickname VARCHAR(30) NULL COMMENT '昵称';

-- 只修改字段定义
ALTER TABLE employees
MODIFY COLUMN nickname VARCHAR(50) NULL COMMENT '昵称';

-- 改字段名
ALTER TABLE employees
RENAME COLUMN nickname TO display_name;

-- 删除字段
ALTER TABLE employees
DROP COLUMN display_name;

-- 修改表名
ALTER TABLE employees RENAME TO staff;
ALTER TABLE staff RENAME TO employees;

MODIFY COLUMN 改定义；RENAME COLUMN 只改名。老式教程常用 CHANGE old_name new_name type 同时改名和定义，但拆开写更清楚。

修改字段类型前必须确认现有数据能否转换。缩短字符串长度、把有符号整数改成无符号整数，都可能失败或造成数据丢失。

3.3 `DELETE`、`TRUNCATE`、`DROP`

命令	删除内容	可带 `WHERE`	表结构保留	显式事务提交前通常可回滚
`DELETE`	指定行或全部行	是	是	是
`TRUNCATE TABLE`	全部行	否	是	否，属于 DDL 并隐式提交
`DROP TABLE`	数据和表结构	否	否	否

sql

DELETE FROM employees WHERE emp_id = 106;

-- 以下仅演示语法，不要在后续仍要使用示例数据时执行
TRUNCATE TABLE temporary_demo;
DROP TABLE IF EXISTS temporary_demo;

TRUNCATE 不是普通的逐行删除，通常还会重置 AUTO_INCREMENT。不能简单理解成可随意回滚的“快速 DELETE”。

4. 数据类型、约束与表关系

4.1 常用数据类型

类型	适合保存	关键提醒
`TINYINT` / `INT` / `BIGINT`	整数、编号	按范围选，不要只看名字
`BOOLEAN` / `BOOL`	布尔状态	在 MySQL 中只是 `TINYINT(1)` 的别名
`DECIMAL(p,s)`	金额、精确小数	`p` 是总位数，`s` 是小数位数
`FLOAT` / `DOUBLE`	允许近似误差的科学计算	不适合金额
`CHAR(n)`	真正固定长度的短字符串	不要因为手机号“看似固定”就机械使用
`VARCHAR(n)`	姓名、邮箱、手机号等变长文本	`n` 是最大字符数
`TEXT`	较长文本	默认值和索引方式有额外限制，不要代替普通短字符串
`DATE`	日期	`YYYY-MM-DD`
`DATETIME`	业务日期时间	范围广，存取时不做会话时区转换
`TIMESTAMP`	时间点、审计时间	存取时按会话时区转换，范围约为 1970 至 2038 年
`JSON`	结构不固定的附加属性	不能代替正常的关系建模

典型选择：

sql

salary     DECIMAL(10, 2)  -- 金额要精确
phone      VARCHAR(20)     -- 可能有国家码、前导 0、加号
id_card    VARCHAR(30)     -- 证件号不是用于计算的数字
birth_date DATE            -- 正式系统更适合存生日，而不是会变化的年龄
created_at DATETIME        -- 业务创建时间

补充两点：

DECIMAL(10, 2) 一共最多 10 位，其中 2 位是小数，正数最大值为 99999999.99。
BOOLEAN 不会自动禁止写入 2；需要严格限制为真假时，仍应添加 CHECK (flag IN (0, 1))。

MySQL 的 DATETIME 和 TIMESTAMP 都支持自动维护时间：

sql

created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
                         ON UPDATE CURRENT_TIMESTAMP

4.2 `CHAR` 与 `VARCHAR`

CHAR(n) 是定长语义，适合长度确实固定的数据。
VARCHAR(n) 是变长语义，更适合绝大多数业务字符串。
“CHAR 一定比 VARCHAR 快”是过度简化；应先按数据含义和存储特征选择。
LENGTH() 返回字节数，CHAR_LENGTH() 返回字符数。

sql

SELECT LENGTH('中国') AS bytes, CHAR_LENGTH('中国') AS chars;

text

mysql> SELECT LENGTH('中国') AS bytes, CHAR_LENGTH('中国') AS chars;
+-------+-------+
| bytes | chars |
+-------+-------+
|     6 |     2 |
+-------+-------+
1 row in set

4.3 常用约束

约束	作用	易错点
`NOT NULL`	不允许空值	空字符串 `''` 不等于 `NULL`
`UNIQUE`	保证非空值不重复	MySQL 通常允许多个 `NULL`
`PRIMARY KEY`	唯一标识一行	一张表只能有一个主键，但可由多列组成
`DEFAULT`	未提供值时使用默认值	显式插入 `NULL` 不一定触发默认值
`CHECK`	限制值的范围	MySQL 8.0.16 起才真正执行检查
`FOREIGN KEY`	保证引用关系有效	定义在子表，也就是“多”的一方
`AUTO_INCREMENT`	自动生成递增编号	不保证永远连续，不应依赖连续性

新增约束示例：

初始化脚本已经包含下面两个约束，此处用于复习语法，不要重复执行。

sql

ALTER TABLE employees
ADD CONSTRAINT uk_employees_email UNIQUE (email);

ALTER TABLE employees
ADD CONSTRAINT fk_employees_department
FOREIGN KEY (dept_id) REFERENCES departments (dept_id)
ON UPDATE CASCADE
ON DELETE SET NULL;

删除约束前先用 SHOW CREATE TABLE employees; 查看真实名称：

sql

-- 仅演示语法；执行后会取消 employees.dept_id 的参照完整性
ALTER TABLE employees
DROP FOREIGN KEY fk_employees_department;

4.4 外键的删除和更新行为

行为	父表变化时子表如何处理
`RESTRICT` / `NO ACTION`	有引用就拒绝父表的删除或更新
`CASCADE`	同步删除或更新子表记录
`SET NULL`	将子表外键设为 `NULL`，外键列必须允许空

不要见到外键就使用 CASCADE。例如删除部门时通常不应该顺手删除全部员工；本例选择把员工的 dept_id 设为 NULL。

外键两端字段类型必须兼容。整数外键不仅大小要一致，是否带 UNSIGNED 也应一致；字符串外键还要注意字符集和排序规则。MySQL 也要求外键列有可用索引，缺少时 InnoDB 通常会自动创建，但在正式建表脚本中显式写出更清楚。

4.5 三种表关系

一对多

部门与员工：外键放在多的一方。

sql

employees.dept_id -> departments.dept_id

多对多

员工与项目：建立中间表，并用联合主键防止重复分配。

sql

PRIMARY KEY (emp_id, project_id)

一对一

常用于拆分不常读取的大字段。外键加 UNIQUE 才能保证一对一：

sql

CREATE TABLE employee_profiles (
    emp_id    INT UNSIGNED PRIMARY KEY,
    biography TEXT NULL,
    address   VARCHAR(200) NULL,
    CONSTRAINT fk_profiles_employee
        FOREIGN KEY (emp_id) REFERENCES employees (emp_id)
        ON DELETE CASCADE
);

5. DML：增、改、删

5.1 `INSERT`

推荐明确写字段名：

sql

INSERT INTO departments (dept_name, city)
VALUES ('法务部', '北京');

批量插入：

sql

INSERT INTO departments (dept_name, city) VALUES
    ('客服部', '成都'),
    ('采购部', '杭州');

不推荐长期使用下面这种写法，因为表结构一变就容易错位：

sql

INSERT INTO departments VALUES (5, '法务部', '北京');

5.2 `UPDATE`

先用相同条件查询，再更新：

sql

SELECT emp_id, emp_name, bonus
FROM employees
WHERE dept_id = 1;

UPDATE employees
SET bonus = COALESCE(bonus, 0) + 1000
WHERE dept_id = 1;

同时修改多个字段：

sql

UPDATE employees
SET salary = 9500.00,
    status = 'ACTIVE'
WHERE emp_id = 104;

没有 WHERE 会更新整张表：

sql

UPDATE employees SET bonus = 0;  -- 所有行都会被修改

5.3 `DELETE`

sql

DELETE FROM employee_projects
WHERE emp_id = 105 AND project_id = 203;

删除前同样应先查询：

sql

SELECT *
FROM employee_projects
WHERE emp_id = 105 AND project_id = 203;

注意：

语法是 DELETE FROM table，不是 DELETE * FROM table。
DELETE 删除行，不能删除某个字段；清空字段应使用 UPDATE ... SET column = NULL。
大批量修改或删除应放在事务中，并检查影响行数。
影响行数为 0 不一定代表 SQL 语法错误，也可能是条件没有匹配到记录；业务代码必须区分这些情况。

6. DQL：单表查询

6.1 完整语法骨架

sql

SELECT [DISTINCT] 字段或表达式
FROM 表
[JOIN 表 ON 连接条件]
[WHERE 行过滤条件]
[GROUP BY 分组字段]
[HAVING 组过滤条件]
[ORDER BY 排序字段 ASC | DESC]
[LIMIT 行数 OFFSET 偏移量];

逻辑处理顺序：

text

FROM / JOIN / ON
        ↓
      WHERE
        ↓
     GROUP BY
        ↓
   聚合计算 / HAVING
        ↓
 SELECT / DISTINCT
        ↓
     ORDER BY
        ↓
       LIMIT

这是帮助理解的逻辑顺序，不是数据库引擎固定不变的物理执行步骤；优化器可以改写执行计划。

为什么 WHERE 里通常不能使用 SELECT 别名，而 ORDER BY 可以：

sql

-- 错误：WHERE 处理时 salary_level 还没有产生
SELECT salary AS salary_level
FROM employees
WHERE salary_level >= 10000;

-- 正确
SELECT salary AS salary_level
FROM employees
WHERE salary >= 10000
ORDER BY salary_level DESC;

6.2 基础查询、别名与去重

sql

SELECT emp_id, emp_name, salary
FROM employees;

SELECT emp_name AS name, salary AS monthly_salary
FROM employees;

SELECT DISTINCT dept_id
FROM employees;

DISTINCT 作用于所选列的整体组合，不是分别对每一列去重：

sql

SELECT DISTINCT dept_id, status
FROM employees;

开发中少写 SELECT *：

读者看不出真正需要哪些列。
表新增大字段后可能增加网络和序列化开销。
多表查询时容易出现重名列。

6.3 条件查询

目的	写法
比较	`=`, `<>`, `!=`, `>`, `>=`, `<`, `<=`
闭区间	`BETWEEN a AND b`
集合	`IN (...)`
模糊匹配	`LIKE`，`%` 任意长度，`_` 单个字符
空值	`IS NULL`, `IS NOT NULL`
组合条件	`AND`, `OR`, `NOT`

sql

SELECT emp_id, emp_name, salary
FROM employees
WHERE status = 'ACTIVE'
  AND salary BETWEEN 10000 AND 18000
ORDER BY salary DESC, emp_id;

text

mysql> SELECT emp_id, emp_name, salary
    -> FROM employees
    -> WHERE status = 'ACTIVE'
    ->   AND salary BETWEEN 10000 AND 18000
    -> ORDER BY salary DESC, emp_id;
+--------+----------+----------+
| emp_id | emp_name | salary   |
+--------+----------+----------+
|    101 | 张伟     | 18000.00 |
|    103 | 王强     | 16000.00 |
|    102 | 李娜     | 13000.00 |
+--------+----------+----------+
3 rows in set

其他典型条件：

sql

-- 多选一
SELECT * FROM employees WHERE dept_id IN (1, 3);

-- 姓张
SELECT * FROM employees WHERE emp_name LIKE '张%';

-- 两个字符的姓名
SELECT * FROM employees WHERE emp_name LIKE '__';

-- AND 优先级高于 OR；复杂条件主动加括号
SELECT *
FROM employees
WHERE status = 'ACTIVE'
  AND (dept_id = 1 OR dept_id = 2);

查询日期时间范围时，推荐“左闭右开”，避免漏掉结束日当天的数据：

sql

-- 查询 2025 年第一季度启动的项目
SELECT project_id, project_name, start_date
FROM projects
WHERE start_date >= '2025-01-01'
  AND start_date <  '2025-04-01';

对 DATETIME 字段尤其不要写成 BETWEEN '2026-07-01' AND '2026-07-31'；后一个值只代表 2026-07-31 00:00:00。

6.4 `NULL`：最容易出错的知识点

NULL 表示“未知或缺失”，不是 0，也不是空字符串。

sql

-- 错误：结果不是 TRUE
SELECT * FROM employees WHERE bonus = NULL;

-- 正确
SELECT * FROM employees WHERE bonus IS NULL;
SELECT * FROM employees WHERE bonus IS NOT NULL;

任何普通算术与 NULL 运算，结果通常仍是 NULL：

sql

SELECT
    emp_name,
    bonus,
    COALESCE(bonus, 0) AS bonus_value
FROM employees
WHERE emp_id IN (101, 102);

text

+----------+---------+-------------+
| emp_name | bonus   | bonus_value |
+----------+---------+-------------+
| 张伟     | 1500.00 |     1500.00 |
| 李娜     |    NULL |        0.00 |
+----------+---------+-------------+
2 rows in set

SQL 条件存在三种逻辑结果：TRUE、FALSE、UNKNOWN。WHERE 只保留 TRUE，这也是很多 NULL 陷阱的根源。

6.5 聚合函数

函数	作用
`COUNT()`	计数
`SUM()`	求和
`AVG()`	平均值
`MAX()`	最大值
`MIN()`	最小值

sql

SELECT
    COUNT(*) AS row_count,
    COUNT(bonus) AS non_null_bonus_count,
    COUNT(DISTINCT dept_id) AS dept_count
FROM employees;

text

+-----------+----------------------+------------+
| row_count | non_null_bonus_count | dept_count |
+-----------+----------------------+------------+
|         6 |                    3 |          3 |
+-----------+----------------------+------------+
1 row in set

必须分清：

COUNT(*)：统计行数，不管某列是否为 NULL。
COUNT(column)：只统计该列非 NULL 的行。
COUNT(DISTINCT column)：统计不同的非 NULL 值。
SUM、AVG、MAX、MIN 一般忽略 NULL；如果参与计算的值全是 NULL，结果通常也是 NULL。

6.6 `GROUP BY` 与 `HAVING`

统计每个部门的在职人数和平均工资，并保留没有在职员工的部门：

sql

SELECT
    d.dept_name,
    COUNT(e.emp_id) AS active_count,
    ROUND(AVG(e.salary), 2) AS avg_salary
FROM departments AS d
LEFT JOIN employees AS e
    ON e.dept_id = d.dept_id
   AND e.status = 'ACTIVE'
GROUP BY d.dept_id, d.dept_name
ORDER BY d.dept_id;

text

+-----------+--------------+------------+
| dept_name | active_count | avg_salary |
+-----------+--------------+------------+
| 研发部    |            2 |   15500.00 |
| 市场部    |            2 |   12500.00 |
| 财务部    |            0 |       NULL |
| 人事部    |            0 |       NULL |
+-----------+--------------+------------+
4 rows in set

这里必须写 COUNT(e.emp_id)，不能写 COUNT(*)。外连接即使没有员工，也会为部门保留一行；COUNT(*) 会错误地把这行算成 1。

筛选“在职人数至少为 2 的部门”：

sql

SELECT dept_id, COUNT(*) AS employee_count
FROM employees
WHERE status = 'ACTIVE'
  AND dept_id IS NOT NULL
GROUP BY dept_id
HAVING COUNT(*) >= 2;

WHERE 与 HAVING：

WHERE 在分组前过滤行，不能直接判断聚合结果。
HAVING 在分组后过滤组，可以使用聚合函数。
能在 WHERE 完成的普通行过滤，不要拖到 HAVING。
MySQL 8.x 默认常启用 ONLY_FULL_GROUP_BY：SELECT 中的普通列应出现在 GROUP BY 中，或能由分组列唯一决定。
GROUP BY 的职责是分组，不保证输出顺序；需要固定顺序时仍要写 ORDER BY。

6.7 排序与分页

sql

SELECT emp_id, emp_name, salary
FROM employees
ORDER BY salary DESC, emp_id ASC;

ASC 升序，也是默认值。
DESC 降序。
第一个排序字段相同时，才看第二个字段。
分页必须使用稳定排序；只按可能重复的 salary 排序，翻页时结果可能漂移，所以补上唯一的 emp_id。

每页 2 条，查询第 2 页：

sql

SELECT emp_id, emp_name, salary
FROM employees
ORDER BY salary DESC, emp_id
LIMIT 2 OFFSET 2;

text

+--------+----------+----------+
| emp_id | emp_name | salary   |
+--------+----------+----------+
|    102 | 李娜     | 13000.00 |
|    105 | 陈晨     | 12000.00 |
+--------+----------+----------+
2 rows in set

MySQL 也支持：

sql

LIMIT 2, 2;  -- LIMIT 偏移量, 行数

偏移分页越往后通常越慢，深分页优化属于进阶内容。

7. 常用函数

7.1 字符串函数

函数	作用
`CONCAT(a,b,...)`	拼接字符串；任一参数为 `NULL` 时结果可能为 `NULL`
`CONCAT_WS(sep,...)`	按分隔符拼接，并跳过 `NULL`
`LOWER()` / `UPPER()`	转为小写 / 大写
`TRIM()`	去除首尾空格
`SUBSTRING(str,start,len)`	截取字符串，位置从 1 开始
`LPAD()` / `RPAD()`	左 / 右填充
`CHAR_LENGTH()`	字符数

sql

SELECT
    CONCAT(emp_name, ' - ', email) AS employee_info,
    UPPER(status) AS normalized_status
FROM employees
WHERE emp_id = 102;

text

+---------------------------+-------------------+
| employee_info             | normalized_status |
+---------------------------+-------------------+
| 李娜 - lina@example.com   | ACTIVE            |
+---------------------------+-------------------+
1 row in set

7.2 数值函数

sql

SELECT
    CEIL(12.1) AS ceil_value,
    FLOOR(12.9) AS floor_value,
    ROUND(12.345, 2) AS rounded,
    MOD(7, 4) AS remainder;

text

+------------+-------------+---------+-----------+
| ceil_value | floor_value | rounded | remainder |
+------------+-------------+---------+-----------+
|         13 |          12 |   12.35 |         3 |
+------------+-------------+---------+-----------+
1 row in set

RAND() 适合普通随机抽样，不适合生成密码、令牌或安全验证码。

7.3 日期函数

函数	作用
`CURDATE()`	当前日期
`CURTIME()`	当前时间
`NOW()`	当前日期时间
`YEAR()` / `MONTH()` / `DAY()`	提取年月日
`DATE_ADD()` / `DATE_SUB()`	增减时间
`DATEDIFF(a,b)`	返回 `a - b` 的天数

DATEDIFF() 只比较日期部分，会忽略参数中的具体时、分、秒。

sql

SELECT
    emp_name,
    hire_date,
    DATEDIFF(CURDATE(), hire_date) AS employed_days
FROM employees
ORDER BY employed_days DESC;

日期加 30 天：

sql

SELECT DATE_ADD('2026-07-29', INTERVAL 30 DAY);

7.4 条件函数

sql

SELECT
    emp_name,
    salary,
    CASE
        WHEN salary >= 16000 THEN '高'
        WHEN salary >= 10000 THEN '中'
        ELSE '基础'
    END AS salary_level,
    COALESCE(bonus, 0) AS bonus_value
FROM employees
ORDER BY salary DESC;

CASE 从上往下判断，命中第一项就停止，因此区间应从高到低写。

常见空值函数：

sql

IFNULL(value, default_value)       -- MySQL 写法，只接收两个参数
COALESCE(v1, v2, v3, ...)         -- 返回第一个非 NULL 值，更通用

MySQL 还提供 IF(condition, true_value, false_value)；复杂条件优先使用更清楚、也更通用的 CASE。

8. 多表查询

8.1 先判断要保留谁

需求	选择
只要两表成功匹配的记录	`INNER JOIN`
左表必须全部保留	`LEFT JOIN`
右表必须全部保留	`RIGHT JOIN`，交换表顺序后通常可改写为 `LEFT JOIN`
生成所有组合	`CROSS JOIN`，谨慎使用
同一张表扮演不同角色	自连接

日常开发优先使用显式 JOIN ... ON ...，比逗号连接更清楚。

8.2 内连接

查询已分配部门的员工：

sql

SELECT
    e.emp_name,
    d.dept_name
FROM employees AS e
INNER JOIN departments AS d
    ON d.dept_id = e.dept_id
ORDER BY e.emp_id;

text

+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
| 张伟     | 研发部    |
| 李娜     | 研发部    |
| 王强     | 市场部    |
| 赵敏     | 市场部    |
| 陈晨     | 财务部    |
+----------+-----------+
5 rows in set

周宇 没有部门，因此不在内连接结果中。

8.3 左外连接

保留所有员工：

sql

SELECT
    e.emp_name,
    d.dept_name
FROM employees AS e
LEFT JOIN departments AS d
    ON d.dept_id = e.dept_id
ORDER BY e.emp_id;

结果会多出：

text

+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
| 周宇     | NULL      |
+----------+-----------+

8.4 `ON` 与 `WHERE` 的位置会改变结果

保留所有部门，只连接在职员工：

sql

SELECT d.dept_name, e.emp_name
FROM departments AS d
LEFT JOIN employees AS e
    ON e.dept_id = d.dept_id
   AND e.status = 'ACTIVE';

如果把条件放进 WHERE：

sql

SELECT d.dept_name, e.emp_name
FROM departments AS d
LEFT JOIN employees AS e
    ON e.dept_id = d.dept_id
WHERE e.status = 'ACTIVE';

没有员工的部门右侧是 NULL，无法满足 WHERE e.status = 'ACTIVE'，于是被过滤掉，效果接近内连接。

记忆：

ON：决定“如何匹配”。
WHERE：决定“匹配后哪些结果还能留下”。

8.5 自连接

同一张 employees 表，一次扮演员工，一次扮演主管：

sql

SELECT
    e.emp_name AS employee_name,
    m.emp_name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
    ON m.emp_id = e.manager_id
ORDER BY e.emp_id;

text

+---------------+--------------+
| employee_name | manager_name |
+---------------+--------------+
| 张伟          | NULL         |
| 李娜          | 张伟         |
| 王强          | NULL         |
| 赵敏          | 王强         |
| 陈晨          | NULL         |
| 周宇          | 张伟         |
+---------------+--------------+
6 rows in set

自连接必须使用不同别名，否则无法分清每个字段属于哪个角色。

8.6 多对多查询

查询项目成员及其角色：

sql

SELECT
    p.project_name,
    e.emp_name,
    ep.project_role
FROM employee_projects AS ep
JOIN employees AS e
    ON e.emp_id = ep.emp_id
JOIN projects AS p
    ON p.project_id = ep.project_id
ORDER BY p.project_id, e.emp_id;

text

+--------------+----------+--------------+
| project_name | emp_name | project_role |
+--------------+----------+--------------+
| 订单平台     | 张伟     | 负责人       |
| 订单平台     | 李娜     | 开发         |
| 数据看板     | 张伟     | 顾问         |
| 数据看板     | 王强     | 负责人       |
| 数据看板     | 赵敏     | 运营         |
| 年度审计     | 陈晨     | 负责人       |
+--------------+----------+--------------+
6 rows in set

8.7 笛卡尔积

sql

SELECT *
FROM employees
CROSS JOIN departments;

6 名员工 × 4 个部门 = 24 种组合。普通关联查询忘记写连接条件，也可能产生这种结果。

8.8 `UNION` 与 `UNION ALL`

sql

SELECT emp_id, emp_name
FROM employees
WHERE salary < 10000

UNION ALL

SELECT emp_id, emp_name
FROM employees
WHERE age < 25;

每段查询的列数必须相同，对应类型应兼容。
UNION ALL 直接合并，保留重复，通常更快。
UNION 还要去重。
结果列名取第一段 SELECT 的列名。
合并后的最终顺序仍不保证；需要排序时，在整条联合查询最后写一个 ORDER BY。

9. 子查询

子查询就是嵌套在另一条 SQL 中的 SELECT。

9.1 标量子查询：返回一个值

查询工资高于全公司平均值的员工：

sql

SELECT emp_id, emp_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
)
ORDER BY salary DESC;

text

+--------+----------+----------+
| emp_id | emp_name | salary   |
+--------+----------+----------+
|    101 | 张伟     | 18000.00 |
|    103 | 王强     | 16000.00 |
|    102 | 李娜     | 13000.00 |
+--------+----------+----------+
3 rows in set

如果 = 右边的子查询返回多行，会报“Subquery returns more than 1 row”。

9.2 列子查询：返回一列多行

查询上海部门的员工：

sql

SELECT emp_id, emp_name
FROM employees
WHERE dept_id IN (
    SELECT dept_id
    FROM departments
    WHERE city = '上海'
);

列子查询还可配合 ANY / SOME / ALL：

sql

-- 高于研发部每一名员工的工资
SELECT emp_id, emp_name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE dept_id = 1
);

-- 高于研发部至少一名员工的工资
SELECT emp_id, emp_name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary
    FROM employees
    WHERE dept_id = 1
);

SOME 与 ANY 同义。实际开发中还要考虑空集合和 NULL，很多场景改写为 MAX()、MIN() 或 EXISTS 更直观。

9.3 行子查询：返回一行多列

查询与李娜“部门和状态”相同的员工：

sql

SELECT emp_id, emp_name, dept_id, status
FROM employees
WHERE (dept_id, status) = (
    SELECT dept_id, status
    FROM employees
    WHERE emp_id = 102
);

9.4 相关子查询

内层查询引用了外层当前行。查询工资高于本部门平均工资的员工：

sql

SELECT e.emp_id, e.emp_name, e.salary, e.dept_id
FROM employees AS e
WHERE e.salary > (
    SELECT AVG(x.salary)
    FROM employees AS x
    WHERE x.dept_id = e.dept_id
);

理解方式：外层每到一名员工，内层就计算其所在部门的平均工资。

9.5 `EXISTS` 与 `NOT EXISTS`

查询至少有一名成员的项目：

sql

SELECT p.project_id, p.project_name
FROM projects AS p
WHERE EXISTS (
    SELECT 1
    FROM employee_projects AS ep
    WHERE ep.project_id = p.project_id
);

查询没有成员的项目：

sql

SELECT p.project_id, p.project_name
FROM projects AS p
WHERE NOT EXISTS (
    SELECT 1
    FROM employee_projects AS ep
    WHERE ep.project_id = p.project_id
);

text

+------------+--------------+
| project_id | project_name |
+------------+--------------+
|        204 | 招聘系统     |
+------------+--------------+
1 row in set

EXISTS 只关心是否存在记录，SELECT 1 表达这个意图，并不是真的需要数字 1。

9.6 `NOT IN` 的 `NULL` 陷阱

如果子查询结果中含 NULL，NOT IN 可能让整个判断变成 UNKNOWN，最后一行也查不到。反向存在性判断优先考虑 NOT EXISTS：

sql

SELECT d.dept_id, d.dept_name
FROM departments AS d
WHERE NOT EXISTS (
    SELECT 1
    FROM employees AS e
    WHERE e.dept_id = d.dept_id
);

9.7 表子查询（派生表）

子查询放在 FROM 后会形成临时结果集，MySQL 要求为它起别名：

sql

SELECT x.dept_id, x.avg_salary
FROM (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    WHERE dept_id IS NOT NULL
    GROUP BY dept_id
) AS x
WHERE x.avg_salary >= 12000;

10. 事务与并发

10.1 什么是事务

事务把多条操作视为一个整体：要么全部提交，要么全部撤销。常见场景是转账、下单扣库存、创建主订单与明细。

前提：

表使用支持事务的存储引擎，例如 InnoDB。
不要在业务事务中混入会隐式提交的 DDL，如普通 CREATE、ALTER、DROP、TRUNCATE。
MySQL 默认开启自动提交，一条 DML 正常执行完通常立即提交。

sql

SELECT @@SESSION.autocommit;
SELECT @@SESSION.transaction_isolation;

10.2 基本控制

sql

START TRANSACTION;

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;

COMMIT;
-- 发生异常则执行 ROLLBACK;

COMMIT 后修改永久生效；ROLLBACK 是撤销未提交修改，不是“把错误数据永久保存”。

事务内部还可设置保存点，只回滚保存点之后的操作：

sql

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

SAVEPOINT after_debit;

UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
ROLLBACK TO SAVEPOINT after_debit;

ROLLBACK;  -- 本例最终撤销整个事务

10.3 更可靠的转账写法

sql

START TRANSACTION;

-- 固定加锁顺序，锁住两个账户直到事务结束。
SELECT account_id, balance
FROM accounts
WHERE account_id IN (1, 2)
ORDER BY account_id
FOR UPDATE;

-- 应用程序先确认查到了 2 个账户，否则立即 ROLLBACK。

-- 防止余额不足；应用程序必须检查影响行数是否为 1。
UPDATE accounts
SET balance = balance - 100.00
WHERE account_id = 1
  AND balance >= 100.00;

-- 若上一条影响 0 行，应用程序执行 ROLLBACK，不再继续。
UPDATE accounts
SET balance = balance + 100.00
WHERE account_id = 2;

-- 收款更新也必须影响 1 行；任一检查失败都应 ROLLBACK。
COMMIT;

数据库事务只能保证这些语句按事务提交或撤销；“确认两个账户存在、检查两次更新的影响行数、决定提交还是回滚”通常由 Python 业务代码负责。

10.4 ACID

特性	核心意思
Atomicity 原子性	全部成功或全部失败
Consistency 一致性	事务把数据库从一个合法状态带到另一个合法状态
Isolation 隔离性	并发事务相互影响受到控制
Durability 持久性	已提交结果在故障恢复后仍应保留

一致性是最终目标，通常依赖数据库约束、事务机制和业务代码共同保证。

10.5 并发问题

问题	含义
脏读	读到了其他事务尚未提交的数据
不可重复读	同一事务两次读取同一行，值发生变化
幻读	同一条件两次查询，匹配的行数发生变化
丢失更新	两个事务都基于旧值更新，后写入者覆盖先写入者

10.6 隔离级别

隔离级别	脏读	不可重复读	幻读
`READ UNCOMMITTED`	可能	可能	可能
`READ COMMITTED`	避免	可能	可能
`REPEATABLE READ`	避免	避免	标准定义下仍需关注
`SERIALIZABLE`	避免	避免	避免

MySQL InnoDB 默认是 REPEATABLE READ。它结合 MVCC、记录锁和间隙相关锁处理并发，实际行为比一张简单表格更细；普通一致性读与 FOR UPDATE 这类锁定读也不完全相同。

sql

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

隔离级别越高，通常并发能力越低。不要机械地全部设成 SERIALIZABLE。

10.7 锁与死锁的基础认识

SELECT ... FOR UPDATE 是锁定读，适合“先查再改”且必须防止并发修改的场景。
锁定读应放在显式事务中；自动提交模式下一条语句结束，锁通常也随即释放。
条件应尽量命中索引，避免锁定范围过大。
多个事务按相同顺序获取资源，可以降低死锁概率。
死锁无法保证永不发生；应用程序要能捕获死锁错误并重试整个事务。
事务应尽量短，不要在事务里等待用户输入或调用耗时外部接口。

11. DCL：用户与权限

生产中不要让应用使用 root，应建立最小权限账户。

sql

CREATE USER 'app_user'@'localhost'
IDENTIFIED BY '请替换为强密码';

GRANT SELECT, INSERT, UPDATE, DELETE
ON mysql_review.*
TO 'app_user'@'localhost';

SHOW GRANTS FOR 'app_user'@'localhost';

REVOKE DELETE
ON mysql_review.*
FROM 'app_user'@'localhost';

ALTER USER 'app_user'@'localhost'
IDENTIFIED BY '新的强密码';

DROP USER 'app_user'@'localhost';

账户由 '用户名'@'主机来源' 共同标识。'%' 表示来源很广，不应为了省事默认开放。

通过 CREATE USER、GRANT、REVOKE 修改账户和权限后，不需要再执行 FLUSH PRIVILEGES；只有直接修改授权系统表等特殊场景才可能涉及它。

旧资料常强行指定 mysql_native_password。现代 MySQL 中通常直接使用 IDENTIFIED BY，让服务器使用当前默认认证方式；mysql_native_password 已弃用，并且在 MySQL 8.4 中默认不启用，除非兼容旧客户端确有需要。

12. 索引入门

索引不属于这份基础 PDF 的主体，但 Python 开发面试经常会顺带问。

12.1 索引解决什么问题

索引类似目录，目的是减少查找数据时需要扫描的行。代价是：

占用额外空间。
INSERT、UPDATE、DELETE 时要维护索引。
索引太多会增加写入成本，优化器也不一定使用。

查看一张表已有的索引：

sql

SHOW INDEX FROM employees;

主键、唯一约束会建立相应索引；InnoDB 的外键列也必须有可用索引。建新索引前先检查现有联合索引能否复用，避免重复。

12.2 普通、唯一与联合索引

sql

-- 新建普通索引
CREATE INDEX idx_employees_hire_date
ON employees (hire_date);

-- 删除普通索引
DROP INDEX idx_employees_hire_date ON employees;

唯一索引和联合索引在初始化脚本中已经用下面的表内写法创建：

sql

CONSTRAINT uk_projects_name UNIQUE (project_name),
INDEX idx_employees_dept_salary (dept_id, salary)

其中 (dept_id, salary) 联合索引通常适合：

sql

WHERE dept_id = 1

WHERE dept_id = 1 AND salary >= 12000

但不一定适合只按 salary 查询。记住联合索引的“最左前缀”思想：能否有效利用，要从索引最左列开始判断。

12.3 常见失效或低效场景

对索引列做函数或复杂运算。
LIKE '%关键词' 以前导 % 开头。
隐式类型转换，例如数字列与字符串参数乱比。
返回数据占全表很大比例，优化器可能认为全表扫描更合算。
联合索引跳过最左列。

先用执行计划观察，不要凭感觉宣布“走索引”：

sql

EXPLAIN
SELECT emp_id, emp_name, salary
FROM employees
WHERE dept_id = 1
  AND salary >= 12000;

基础阶段重点看：

type：访问方式。
key：实际选择的索引。
rows：预计扫描行数。
Extra：额外执行信息。

EXPLAIN 中的 rows 是优化器估算值，不等于查询实际返回的行数。

13. 面试速答与综合练习

13.1 高频问题速答

1. `WHERE` 和 `HAVING` 有什么区别？

WHERE 在分组前过滤行，不能直接筛选聚合结果；HAVING 在分组后过滤组，可以使用聚合函数。

2. `COUNT(*)`、`COUNT(1)`、`COUNT(column)` 的区别？

COUNT(*) 和 COUNT(1) 都用于统计行数；在现代 MySQL 中不要凭传言断言谁一定更快。COUNT(column) 只统计该列非 NULL 的行。

3. 为什么金额用 `DECIMAL`，不用 `DOUBLE`？

DECIMAL 保存精确十进制数；DOUBLE 是二进制浮点近似值，可能出现精度误差。

4. 主键与唯一约束有什么区别？

主键用于唯一标识一行，必须非空，一张表只有一个主键；唯一约束可有多个，MySQL 中通常允许多个 NULL。

5. 外键写在哪一方？

一对多关系写在“多”的一方。例如一个部门有多名员工，外键写在员工表。

6. 内连接与左连接有什么区别？

内连接只返回匹配成功的行；左连接保留左表全部行，右表没有匹配时补 NULL。

7. `DELETE`、`TRUNCATE`、`DROP` 有什么区别？

DELETE 删除行且可带条件；TRUNCATE 快速清空整表并保留结构；DROP 连表结构一起删除。后两者属于 DDL，通常不能靠事务回滚。

8. 事务四大特性是什么？

原子性、一致性、隔离性、持久性，即 ACID。

9. MySQL 默认隔离级别是什么？

InnoDB 默认是 REPEATABLE READ（可重复读）。

10. 为什么 `column = NULL` 查不到数据？

NULL 表示未知，普通比较结果也是未知；必须使用 IS NULL 或 IS NOT NULL。

11. `IN` 和 `EXISTS` 怎么选？

先按语义写清楚，再看执行计划。IN 适合集合成员判断；EXISTS 适合判断相关记录是否存在。反向判断时要特别警惕 NOT IN 遇到 NULL。

12. SQL 的逻辑执行顺序？

常用记法：FROM/JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT。

13. 为什么 `LEFT JOIN` 有时写完却像内连接？

如果在 WHERE 中要求右表字段满足普通条件，右表未匹配时产生的 NULL 会被过滤。若需求是保留左表，应认真判断右表条件是否应写在 ON 中。

14. `DATETIME` 和 `TIMESTAMP` 怎么选？

DATETIME 范围更广，存取时不按会话时区转换；TIMESTAMP 会按会话时区转换且范围较窄。业务本地日期时间常用 DATETIME，明确表示时间点并统一时区时可考虑 TIMESTAMP，最终仍要结合系统的时区规范。

13.2 综合练习

先自己写，再看答案。

练习 1：查询在职且工资不低于 12000 的员工，工资从高到低

参考答案

sql

SELECT emp_id, emp_name, salary
FROM employees
WHERE status = 'ACTIVE'
  AND salary >= 12000
ORDER BY salary DESC, emp_id;

练习 2：统计每个部门的员工数，空部门也要显示为 0

参考答案

sql

SELECT
    d.dept_id,
    d.dept_name,
    COUNT(e.emp_id) AS employee_count
FROM departments AS d
LEFT JOIN employees AS e
    ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY d.dept_id;

练习 3：查询工资高于本部门平均工资的员工

参考答案

sql

SELECT e.emp_id, e.emp_name, e.salary, e.dept_id
FROM employees AS e
WHERE e.salary > (
    SELECT AVG(x.salary)
    FROM employees AS x
    WHERE x.dept_id = e.dept_id
);

练习 4：查询没有参加任何项目的员工

参考答案

sql

SELECT e.emp_id, e.emp_name
FROM employees AS e
WHERE NOT EXISTS (
    SELECT 1
    FROM employee_projects AS ep
    WHERE ep.emp_id = e.emp_id
);

预期结果：

text

+--------+----------+
| emp_id | emp_name |
+--------+----------+
|    106 | 周宇     |
+--------+----------+
1 row in set

练习 5：查询每名员工及其主管，没有主管也要显示

参考答案

sql

SELECT
    e.emp_name AS employee_name,
    m.emp_name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
    ON m.emp_id = e.manager_id
ORDER BY e.emp_id;

练习 6：查询项目及其成员数，无成员项目也要显示为 0

参考答案

sql

SELECT
    p.project_id,
    p.project_name,
    COUNT(ep.emp_id) AS member_count
FROM projects AS p
LEFT JOIN employee_projects AS ep
    ON ep.project_id = p.project_id
GROUP BY p.project_id, p.project_name
ORDER BY p.project_id;

14. 最后检查清单

写完 SQL 后快速检查：

UPDATE、DELETE 是否真的需要全表操作？WHERE 是否正确？
金额是否使用 DECIMAL？
判断空值是否使用 IS NULL，而不是 = NULL？
外连接右表的过滤条件应该放 ON 还是 WHERE？
分组查询是否混入了无意义的非分组字段？
COUNT(*) 与 COUNT(column) 是否选对？
分页是否有稳定的 ORDER BY？
多表查询是否漏了连接条件，产生笛卡尔积？
NOT IN 的子查询是否可能返回 NULL？
多步骤业务是否需要事务？是否检查了影响行数？
事务是否过长，是否混入 DDL 或外部耗时调用？
程序是否使用参数化查询，而不是拼接用户输入？

Python DB-API 参数化示意：

python

# 正确：值通过参数传入，驱动负责转义。
cursor.execute(
    "SELECT emp_id, emp_name FROM employees WHERE email = %s",
    (email,),
)

# 错误：直接拼接用户输入，存在 SQL 注入风险。
sql = f"SELECT * FROM employees WHERE email = '{email}'"

这里的 %s 是 MySQL Python 驱动的参数占位符，不是让你自己使用 Python 的 % 或 f-string 拼接。正常使用 Django ORM 的 filter()、get() 等查询也会参数化；手写原生 SQL 时仍要传 params。

掌握顺序建议：

text

建库建表
  -> 单表增删改查
  -> NULL / 聚合 / 分组
  -> 内连接与左连接
  -> 子查询与 EXISTS
  -> 事务与并发
  -> 索引与 EXPLAIN

MySQL 基础复习手册（强化校订版）

目录

0. 统一示例数据库

0.1 departments：部门表

0.2 employees：员工表

0.3 projects：项目表

0.4 employee_projects：员工项目中间表

0.5 accounts：账户表

0.6 表关系

0.7 完整初始化脚本

1. 数据库、DBMS 与 SQL

1.1 连接 MySQL

1.2 最常用的环境检查

2. SQL 分类与书写规则

2.1 SQL 分类

2.2 书写规则

3. DDL：数据库和表结构

3.1 数据库操作

3.2 表操作

3.3 DELETE、TRUNCATE、DROP

4. 数据类型、约束与表关系

4.1 常用数据类型

4.2 CHAR 与 VARCHAR

4.3 常用约束

4.4 外键的删除和更新行为

4.5 三种表关系

一对多

多对多

一对一

5. DML：增、改、删

5.1 INSERT

5.2 UPDATE

5.3 DELETE

6. DQL：单表查询

6.1 完整语法骨架

6.2 基础查询、别名与去重

6.3 条件查询

6.4 NULL：最容易出错的知识点

6.5 聚合函数

6.6 GROUP BY 与 HAVING

6.7 排序与分页

7. 常用函数

7.1 字符串函数

7.2 数值函数

7.3 日期函数

7.4 条件函数

8. 多表查询

8.1 先判断要保留谁

8.2 内连接

8.3 左外连接

8.4 ON 与 WHERE 的位置会改变结果

8.5 自连接

8.6 多对多查询

8.7 笛卡尔积

8.8 UNION 与 UNION ALL

9. 子查询

9.1 标量子查询：返回一个值

9.2 列子查询：返回一列多行

9.3 行子查询：返回一行多列

9.4 相关子查询

9.5 EXISTS 与 NOT EXISTS

9.6 NOT IN 的 NULL 陷阱

9.7 表子查询（派生表）

10. 事务与并发

10.1 什么是事务

10.2 基本控制

10.3 更可靠的转账写法

10.4 ACID

10.5 并发问题

10.6 隔离级别

10.7 锁与死锁的基础认识

11. DCL：用户与权限

12. 索引入门

12.1 索引解决什么问题

12.2 普通、唯一与联合索引

12.3 常见失效或低效场景

13. 面试速答与综合练习

13.1 高频问题速答

1. WHERE 和 HAVING 有什么区别？

2. COUNT(*)、COUNT(1)、COUNT(column) 的区别？

0.1 `departments`：部门表

0.2 `employees`：员工表

0.3 `projects`：项目表

0.4 `employee_projects`：员工项目中间表

0.5 `accounts`：账户表

3.3 `DELETE`、`TRUNCATE`、`DROP`

4.2 `CHAR` 与 `VARCHAR`

5.1 `INSERT`

5.2 `UPDATE`

5.3 `DELETE`

6.4 `NULL`：最容易出错的知识点

6.6 `GROUP BY` 与 `HAVING`

8.4 `ON` 与 `WHERE` 的位置会改变结果

8.8 `UNION` 与 `UNION ALL`

9.5 `EXISTS` 与 `NOT EXISTS`

9.6 `NOT IN` 的 `NULL` 陷阱

1. `WHERE` 和 `HAVING` 有什么区别？

2. `COUNT(*)`、`COUNT(1)`、`COUNT(column)` 的区别？

3. 为什么金额用 `DECIMAL`，不用 `DOUBLE`？

7. `DELETE`、`TRUNCATE`、`DROP` 有什么区别？

10. 为什么 `column = NULL` 查不到数据？

11. `IN` 和 `EXISTS` 怎么选？

13. 为什么 `LEFT JOIN` 有时写完却像内连接？

14. `DATETIME` 和 `TIMESTAMP` 怎么选？