SQL

# SQL学习笔记

1 SQL基础
- 1.1 SQL通用语法
- 1.2 SQL分类
2 DDL
- 2.1 数据库操作
  - 2.1.1 查询
  - 2.1.2 创建
  - 2.1.3 删除
  - 2.1.4 使用
- 2.2 表操作
  - 2.2.1 查询
  - 2.2.2 创建
  - 2.2.3 修改
  - 2.2.4 删除
- 2.3 SQL常用字段和字段类型
  - 2.3.1 一、数值类型（Numeric Types）
    - 2.3.1.1 整数类型
    - 2.3.1.2 小数类型
  - 2.3.2 二、字符串类型（String Types）
    - 2.3.2.1 定长 vs 变长
    - 2.3.2.2 大文本类型
  - 2.3.3 三、日期和时间类型（Date & Time）
  - 2.3.4 四、枚举与集合类型（慎用）
  - 2.3.5 五、二进制与特殊类型
  - 2.3.6 六、选择字段类型的黄金原则
  - 2.3.7 七、常见字段类型搭配示例
3 DML
- 3.1 添加数据
- 3.2 修改数据
- 3.3 删除数据
4 DQL
- 4.1 语法
  - 4.1.1 常见查询分类
- 4.2 基本查询
  - 4.2.1 查询多个字段
  - 4.2.2 设置别名
  - 4.2.3 去除重复记录
- 4.3 条件查询
  - 4.3.1 语法
    - 4.3.1.1 比较运算符
    - 4.3.1.2 逻辑运算符
- 4.4 聚合函数
  - 4.4.1 介绍
  - 4.4.2 常见聚合函数
  - 4.4.3 语法
- 4.5 分组查询
  - 4.5.1 语法
  - 4.5.2 WHERE 与 HAVING 区别
- 4.6 排序查询
  - 4.6.1 语法
  - 4.6.2 排序方式
- 4.7 分页查询
  - 4.7.1 语法
- 4.8 执行顺序
  - 4.8.1 编写顺序
  - 4.8.2 执行顺序
5 DCL
- 5.1 管理用户
  - 5.1.1 查询用户
  - 5.1.2 创建用户
  - 5.1.3 修改用户密码
  - 5.1.4 删除用户
- 5.2 权限控制
  - 5.2.1 常用权限
  - 5.2.2 查询权限
  - 5.2.3 授予权限
  - 5.2.4 撤销权限
6 常用函数
- 6.1 常用函数概述
- 6.2 字符串函数
  - 6.2.1 函数说明
  - 6.2.2 示例
  - 6.2.3 案例
- 6.3 数值函数
  - 6.3.1 函数说明
  - 6.3.2 示例
  - 6.3.3 案例
- 6.4 日期函数
  - 6.4.1 函数说明
  - 6.4.2 示例
  - 6.4.3 案例
- 6.5 流程函数
  - 6.5.1 函数说明
  - 6.5.2 示例
  - 6.5.3 案例1
  - 6.5.4 案例2
- 6.6 窗口函数
  - 6.6.1 概述
  - 6.6.2 基本语法
  - 6.6.3 常见窗口函数
  - 6.6.4 排名函数示例
  - 6.6.5 分析函数示例
7 约束
- 7.1 概述
- 7.2 常见约束
- 7.3 约束示例
- 7.4 外键约束
  - 7.4.1 介绍
  - 7.4.2 添加外键
  - 7.4.3 删除外键
  - 7.4.4 删除 / 更新行为
  - 7.4.5 指定级联行为
8 多表查询
- 8.1 多表关系
  - 8.1.1 一对多
  - 8.1.2 多对多
  - 8.1.3 一对一
- 8.2 多表查询概述
  - 8.2.1 概述
  - 8.2.2 分类
  - 8.2.3 连接查询分类
- 8.3 内连接
  - 8.3.1 隐式内连接
  - 8.3.2 显式内连接
  - 8.3.3 示例
- 8.4 外连接
  - 8.4.1 左外连接
  - 8.4.2 右外连接
  - 8.4.3 示例
- 8.5 自连接
  - 8.5.1 语法
  - 8.5.2 示例
- 8.6 联合查询
  - 8.6.1 语法
  - 8.6.2 说明
  - 8.6.3 示例
- 8.7 子查询
  - 8.7.1 概述
    - 8.7.1.1 按结果分类
    - 8.7.1.2 按位置分类
- 8.8 标量子查询
  - 8.8.1 常用操作符
  - 8.8.2 示例
- 8.9 列子查询
  - 8.9.1 常用操作符
  - 8.9.2 示例
- 8.10 行子查询
  - 8.10.1 常用操作符
  - 8.10.2 示例
- 8.11 表子查询
  - 8.11.1 常用操作符
  - 8.11.2 示例
- 8.12 多表查询案例
  - 8.12.1 查询所有部门信息，并统计部门员工人数
  - 8.12.2 查询所有学生的选课情况
9 事务
- 9.1 事务简介
- 9.2 事务操作
  - 9.2.1 方式一：控制自动提交
  - 9.2.2 方式二：手动开启事务
  - 9.2.3 转账案例
- 9.3 事务四大特性
- 9.4 并发事务问题
  - 9.4.1 脏读
  - 9.4.2 不可重复读
  - 9.4.3 幻读
- 9.5 事务隔离级别
  - 9.5.1 隔离级别说明
  - 9.5.2 查看隔离级别
  - 9.5.3 设置隔离级别
- 9.6 补充-事务控制过程
  - 9.6.1 未控制事务
    - 9.6.1.1 正常情况
    - 9.6.1.2 异常情况
  - 9.6.2 控制事务一
    - 9.6.2.1 查看 / 设置事务提交方式
    - 9.6.2.2 提交事务
    - 9.6.2.3 回滚事务
  - 9.6.3 控制事务二
    - 9.6.3.1 开启事务
    - 9.6.3.2 提交事务
    - 9.6.3.3 回滚事务
10 综合案例-员工与部门管理
- 10.1 案例说明
- 10.2 创建数据库并切换
- 10.3 创建表
- 10.4 插入数据
- 10.5 修改数据
- 10.6 删除数据
- 10.7 基础查询
  - 10.7.1 查询所有员工
  - 10.7.2 查询指定字段并设置别名
  - 10.7.3 去重查询
- 10.8 条件查询
  - 10.8.1 比较 / 范围 / 模糊 / 空值判断
  - 10.8.2 IN / OR / NOT
- 10.9 聚合、分组、排序、分页
  - 10.9.1 聚合函数
  - 10.9.2 分组查询
  - 10.9.3 排序 + 分页
- 10.10 常用函数
  - 10.10.1 字符串函数
  - 10.10.2 日期函数
  - 10.10.3 流程函数
- 10.11 多表查询
  - 10.11.1 内连接
  - 10.11.2 左外连接
  - 10.11.3 自连接
  - 10.11.4 联合查询
- 10.12 子查询
  - 10.12.1 标量子查询
  - 10.12.2 列子查询
  - 10.12.3 行子查询
  - 10.12.4 表子查询
- 10.13 事务
- 10.14 执行顺序理解
11 正则表达式
- 11.1 概述
- 11.2 常用元字符
- 11.3 常用正则函数
- 11.4 示例操作
  - 11.4.1 基础匹配 (REGEXP / RLIKE)
  - 11.4.2 提取子串 (REGEXP_SUBSTR)
  - 11.4.3 替换子串 (REGEXP_REPLACE)
  - 11.4.4 查找位置 (REGEXP_INSTR)
- 11.5 注意事项

1 SQL基础

1.1 SQL通用语法

SQL 语句可以单行或多行书写，以分号 ; 结尾。
SQL 语句可以使用空格 / 缩进来增强语句的可读性。
MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
注释：
- 单行注释：-- 注释内容 或 # 注释内容
- 多行注释：/* 注释内容 */

1.2 SQL分类

分类	全称	说明
`DDL`	Data Definition Language	数据定义语言，用来定义数据库对象（数据库、表、字段）
`DML`	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
`DQL`	Data Query Language	数据查询语言，用来查询数据库中表的记录
`DCL`	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

2 DDL

2.1 数据库操作

2.1.1 查询

查询所有数据库
1
SHOW DATABASES;
查询当前数据库
1
SELECT DATABASE();

2.1.2 创建

1	`CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集 ] [ COLLATE 排序规则 ];`

2.1.3 删除

1	`DROP DATABASE [ IF EXISTS ] 数据库名;`

2.1.4 使用

1	`USE 数据库名;`

2.2 表操作

2.2.1 查询

查询当前数据库所有表
1
SHOW TABLES;
查询表结构
1
DESC 表名;
查询指定表的建表语句
1
SHOW CREATE TABLE 表名;

2.2.2 创建

CREATE TABLE 表名(
    字段1 字段1类型 [COMMENT 字段1注释],
    字段2 字段2类型 [COMMENT 字段2注释],
    字段3 字段3类型 [COMMENT 字段3注释],
    ......
    字段n 字段n类型 [COMMENT 字段n注释]
)[COMMENT 表注释];

注意：[...]) 为可选参数，最后一个字段后面没有逗号

2.2.3 修改

添加字段

1	`ALTER TABLE 表名 ADD 字段名类型 [COMMENT 注释];`

修改字段类型

1	`ALTER TABLE 表名 MODIFY 字段名新类型 [COMMENT 注释];`

修改字段名和类型

1	`ALTER TABLE 表名 CHANGE 旧字段名新字段名新类型 [COMMENT 注释];`

删除字段
1
ALTER TABLE 表名 DROP 字段名;

修改表名

1	`ALTER TABLE 旧表名 RENAME TO 新表名;`

2.2.4 删除

删除表
1
DROP TABLE [ IF EXISTS ] 表名;
删除指定表，并重新创建该表
1
TRUNCATE TABLE 表名;

2.3 SQL常用字段和字段类型

在 SQL（特别是 MySQL）中，字段类型（也叫数据类型）决定了该字段能存储什么种类的数据、占用多少存储空间、以及支持哪些操作。选择合适的数据类型对数据库的性能、存储效率和数据准确性至关重要。

2.3.1 一、数值类型（Numeric Types）

2.3.1.1 整数类型

类型	范围	占用空间	适用场景
`TINYINT`	-128 ~ 127（有符号）0 ~ 255（无符号）	1 字节	状态标志（如 `status: 0/1`）、性别
`SMALLINT`	-32,768 ~ 32,767	2 字节	小计数器、年份
`MEDIUMINT`	-8,388,608 ~ 8,388,607	3 字节	中等ID或计数
`INT` / `INTEGER`	-21亿 ~ 21亿	4 字节	用户ID、订单ID（中小系统）
`BIGINT`	±9.2×10¹⁸	8 字节	高并发ID、分布式系统主键

建议：

主键优先用 BIGINT（避免 ID 耗尽）
状态字段用 TINYINT UNSIGNED（0~255）

2.3.1.2 小数类型

类型	说明	适用场景
`DECIMAL(M,D)`	精确小数，M=总位数，D=小数位例如 `DECIMAL(10,2)` 表示最多 8 位整数 + 2 位小数	金额、价格、财务数据（必须用它！）
`FLOAT`	单精度浮点（约7位有效数字）	科学计算、不需精确的场景
`DOUBLE`	双精度浮点（约15位有效数字）	地理坐标、物理量

重要：不要用 FLOAT/DOUBLE 存金额！会有精度丢失（如 0.1 + 0.2 ≠ 0.3）。
正确做法：price DECIMAL(10,2)

2.3.2 二、字符串类型（String Types）

2.3.2.1 定长 vs 变长

类型	最大长度	特点	适用场景
`CHAR(n)`	0~255 字符	固定长度，不足补空格	国家代码（’CN’）、MD5（32位）
`VARCHAR(n)`	0~65,535 字节	可变长度，节省空间	姓名、标题、邮箱、地址

建议：

大多数文本用 VARCHAR
长度明确且固定用 CHAR

2.3.2.2 大文本类型

类型	最大容量	适用场景
`TINYTEXT`	255 字节	极短描述
`TEXT`	65,535 字节（≈64KB）	文章内容、评论
`MEDIUMTEXT`	16MB	长文章、日志
`LONGTEXT`	4GB	小说、PDF文本提取

提示：TEXT 类型不能有默认值，且可能影响查询性能（尽量不 SELECT *）

2.3.3 三、日期和时间类型（Date & Time）

类型	格式	范围	适用场景
`DATE`	`YYYY-MM-DD`	1000-01-01 ~ 9999-12-31	生日、日期选择
`TIME`	`HH:MM:SS`	-838:59:59 ~ 838:59:59	持续时间、时间段
`DATETIME`	`YYYY-MM-DD HH:MM:SS`	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	创建时间、更新时间（推荐）
`TIMESTAMP`	同上	1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC	需要自动时区转换的场景（但有 2038 年问题）

建议：

优先使用 DATETIME（范围大、无时区副作用）

自动设置当前时间：

1	`created_at DATETIME DEFAULT CURRENT_TIMESTAMP`

2.3.4 四、枚举与集合类型（慎用）

类型	说明	示例
`ENUM('值1','值2',...)`	枚举，只能选预定义值	`gender ENUM('男','女','未知')`
`SET('值1','值2',...)`	集合，可多选	`hobby SET('读书','电影','旅行')`

缺点：

修改选项需改表结构
不利于扩展（微服务、多语言场景）
替代方案：用 TINYINT + 代码注释，或单独建字典表

2.3.5 五、二进制与特殊类型

类型	用途
`BLOB` / `LONGBLOB`	存储图片、文件等二进制数据（一般不推荐，建议存文件路径）
`JSON`（MySQL 5.7+）	存储 JSON 文档，支持 JSON 查询
`BOOLEAN`	实际是 `TINYINT(1)` 的别名

JSON 示例：

1	`config JSON COMMENT '用户个性化设置'`

查询：

1	`SELECT * FROM users WHERE config->'$.theme' = 'dark';`

2.3.6 六、选择字段类型的黄金原则

够用就好：能用 TINYINT 就不用 INT，节省空间 = 提升性能。
避免 NULL：除非必要，字段设为 NOT NULL + 默认值，减少判断复杂度。
金额用 DECIMAL：永远不要用浮点数存钱！
时间用 DATETIME：避开 TIMESTAMP 的 2038 问题。
大文本分离：如果某字段很少查询，可拆到单独表（垂直分表）。
字符集统一：建议 utf8mb4（支持 emoji）。

2.3.7 七、常见字段类型搭配示例

业务含义	推荐类型
用户ID	`BIGINT`
用户名	`VARCHAR(50)`
邮箱	`VARCHAR(150)`
手机号	`VARCHAR(20)`（含国际区号）
密码哈希	`VARCHAR(255)`（兼容 bcrypt）
性别	`TINYINT`（0/1/2）或 `CHAR(1)`（’M’/‘F’）
价格	`DECIMAL(10,2)`
商品描述	`TEXT`
注册时间	`DATETIME`
是否启用	`TINYINT`（0/1）
头像URL	`VARCHAR(255)`

3 DML

3.1 添加数据

语法

1 2	`INSERT INTO 表名(字段名1, 字段名2, ...) VALUES (值1, 值2, ...);`

给全部字段添加数据

1 2	`INSERT INTO 表名 VALUES (值1, 值2, ...);`

批量添加数据

1 2	`INSERT INTO 表名(字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);`

1 2	`INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);`

注意：

插入数据时，指定的字段顺序需要与值的顺序一一对应。
字符串和日期类型数据应该包含在引号中。
插入的数据大小，应该在字段的规定范围内。

3.2 修改数据

1
2
3

UPDATE 表名
SET 字段名1 = 值1, 字段名2 = 值2, ...
[WHERE 条件];

注意：

修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

3.3 删除数据

1	`DELETE FROM 表名 [WHERE 条件];`

注意：

DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
DELETE 语句不能删除某一个字段的值（可以使用 UPDATE）。

4 DQL

4.1 语法

SELECT
    字段列表，聚合函数（字段列表）
FROM
    表名列表
WHERE
    条件列表 #分组之前进行过滤
GROUP BY
    分组字段列表
HAVING
    分组后条件列表
ORDER BY
    排序字段列表
LIMIT
    分页参数;

4.1.1 常见查询分类

基本查询
条件查询（WHERE）
聚合函数（COUNT、MAX、MIN、AVG、SUM）
分组查询（GROUP BY）
排序查询（ORDER BY）
分页查询（LIMIT）

4.2 基本查询

4.2.1 查询多个字段

1 2	`SELECT 字段1, 字段2, 字段3, ... FROM 表名;`

1 2	`SELECT * FROM 表名;`

4.2.2 设置别名

1 2	`SELECT 字段1 [AS 别名1], 字段2 [AS 别名2], ... FROM 表名;`

4.2.3 去除重复记录

1 2	`SELECT DISTINCT 字段列表 FROM 表名;`

4.3 条件查询

4.3.1 语法

1
2
3

SELECT 字段列表
FROM 表名
WHERE 条件列表;

4.3.1.1 比较运算符

运算符	功能
`>`	大于
`>=`	大于等于
`<`	小于
`<=`	小于等于
`=`	等于
`<>` / `!=`	不等于
`BETWEEN ... AND ...`	在某个范围之内（包含最小值、最大值）
`IN(...)`	在 `IN` 之后的列表中的值，多选一
`LIKE`	模糊匹配（`_` 匹配单个字符，`%` 匹配任意个字符）
`IS NULL`	是 `NULL`

4.3.1.2 逻辑运算符

运算符	功能
`AND` / `&&`	并且（多个条件同时成立）
`OR` / `
`NOT` / `!`	非，不是

4.4 聚合函数

4.4.1 介绍

将一列数据作为一个整体，进行纵向计算。

4.4.2 常见聚合函数

函数	功能
`COUNT`	统计数量
`MAX`	最大值
`MIN`	最小值
`AVG`	平均值
`SUM`	求和

注：COUNT函数还有一种用法 COUNT(DISTINCT 字段1, 字段2) 意为(字段1, 字段2) 这个组合有多少种不同的值
COUNT用法详解：
COUNT(*) = 数行数
COUNT(字段) = 数该字段有值的行数
COUNT(DISTINCT 字段) = 数该字段有几种不同的值

4.4.3 语法

1 2	`SELECT 聚合函数(字段列表) FROM 表名;`

4.5 分组查询

4.5.1 语法

SELECT 字段列表
FROM 表名
[WHERE 条件]
GROUP BY 分组字段名
[HAVING 分组后过滤条件];

4.5.2 `WHERE` 与 `HAVING` 区别

执行时机不同：WHERE 是分组之前进行过滤，不满足 WHERE 条件，不参与分组；HAVING 是分组之后对结果进行过滤。
判断条件不同：WHERE 不能对聚合函数进行判断，而 HAVING 可以。

注意：

执行顺序：WHERE > 聚合函数 > HAVING。
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无实际意义。

4.6 排序查询

4.6.1 语法

1
2
3

SELECT 字段列表
FROM 表名
ORDER BY 字段1 排序方式1, 字段2 排序方式2;

4.6.2 排序方式

ASC：升序（默认值）
DESC：降序

注意：

如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

4.7 分页查询

4.7.1 语法

1
2
3

SELECT 字段列表
FROM 表名
LIMIT 起始索引, 查询记录数;

注意：

起始索引从 0 开始，起始索引 = （查询页码 - 1） * 每页显示记录数。
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL 中是 LIMIT。
如果查询的是第一页数据，起始索引可以省略，直接简写为 LIMIT 10。

4.8 执行顺序

4.8.1 编写顺序

SELECT 字段列表
FROM 表名列表
JOIN 其他表名
ON 连接条件 (若还要连其他的表就继续JOIN……ON……)
WHERE 条件列表
GROUP BY 分组字段列表
HAVING 分组后条件列表
ORDER BY 排序字段列表
LIMIT 分页参数;

4.8.2 执行顺序

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT

注意：

实际执行时并不是从 SELECT 开始，而是先 FROM 再逐步过滤、分组、筛选、排序和分页。

5 DCL

5.1 管理用户

5.1.1 查询用户

1	`SELECT * FROM mysql.user;`

说明：

Host 代表当前用户允许访问的主机。
User 代表访问该数据库的用户名。
在 MySQL 中，需要通过 Host 和 User 来唯一标识一个用户。

5.1.2 创建用户

1	`CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';`

5.1.3 修改用户密码

1	`ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';`

注意：

主机名可以使用 % 通配。
这类 SQL 开发人员操作得比较少，主要是 DBA（数据库管理员）使用。

5.1.4 删除用户

1	`DROP USER '用户名'@'主机名';`

5.2 权限控制

5.2.1 常用权限

权限	说明
`ALL` / `ALL PRIVILEGES`	所有权限
`SELECT`	查询数据
`INSERT`	插入数据
`UPDATE`	修改数据
`DELETE`	删除数据
`ALTER`	修改表
`DROP`	删除数据库 / 表 / 视图
`CREATE`	创建数据库 / 表

5.2.2 查询权限

1	`SHOW GRANTS FOR '用户名'@'主机名';`

5.2.3 授予权限

1	`GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';`

5.2.4 撤销权限

1	`REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';`

注意：

多个权限之间，使用逗号分隔。
授权时，数据库名和表名可以使用 * 进行通配，代表所有。

6 常用函数

6.1 常用函数概述

MySQL 中常用函数主要分为五类：

字符串函数
数值函数
日期函数
流程函数
窗口函数

6.2 字符串函数

6.2.1 函数说明

函数	功能
`CONCAT(S1,S2,...Sn)`	字符串拼接，将多个字符串拼接成一个字符串
`LOWER(str)`	将字符串全部转为小写
`UPPER(str)`	将字符串全部转为大写
`LPAD(str,n,pad)`	左填充，用 `pad` 对左边进行填充，达到长度 `n`
`RPAD(str,n,pad)`	右填充，用 `pad` 对右边进行填充，达到长度 `n`
`TRIM(str)`	去掉字符串头部和尾部的空格
`SUBSTRING(str,start,len)`	从指定位置截取指定长度的字符串
`SUBSTRING_INDEX(str, delim, count)`

6.2.2 示例

SELECT CONCAT('Hello', ' MySQL');
SELECT LOWER('Hello');
SELECT UPPER('Hello');
SELECT LPAD('01', 5, '-');
SELECT RPAD('01', 5, '-');
SELECT TRIM(' Hello MySQL ');
SELECT SUBSTRING('Hello MySQL', 1, 5);

6.2.3 案例

将员工工号统一补齐为 5 位，不足部分左侧补 0：

1 2	`UPDATE emp SET workno = LPAD(workno, 5, '0');`

6.3 数值函数

6.3.1 函数说明

函数	功能
`CEIL(x)`	向上取整
`FLOOR(x)`	向下取整
`MOD(x,y)`	返回 `x / y` 的模
`RAND()`	返回 `0~1` 内的随机数
`ROUND(x,y)`	对 `x` 四舍五入，保留 `y` 位小数
`ABS()`	取绝对值

6.3.2 示例

SELECT CEIL(1.1);
SELECT FLOOR(1.9);
SELECT MOD(7, 4);
SELECT RAND();
SELECT ROUND(2.344, 2);

6.3.3 案例

生成 6 位随机验证码：

1	`SELECT LPAD(ROUND(RAND() * 1000000, 0), 6, '0');`

6.4 日期函数

6.4.1 函数说明

函数	功能
`CURDATE()`	返回当前日期
`CURTIME()`	返回当前时间
`NOW()`	返回当前日期和时间
`YEAR(date)`	获取指定日期的年份
`MONTH(date)`	获取指定日期的月份
`DAY(date)`	获取指定日期的日
`DATE_ADD(date, INTERVAL expr type)`	返回加上时间间隔后的日期 / 时间值
`DATEDIFF(date1,date2)`	返回两个日期之间相差的天数(前减后)

6.4.2 示例

SELECT CURDATE();
SELECT CURTIME();
SELECT NOW();
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT DATE_ADD(NOW(), INTERVAL 70 YEAR);
SELECT DATEDIFF('2021-10-01', '2021-12-01');

6.4.3 案例

查询所有员工的入职天数，并按入职天数倒序排序：

1
2
3

SELECT name, DATEDIFF(CURDATE(), entrydate) AS entrydays
FROM emp
ORDER BY entrydays DESC;

6.5 流程函数

6.5.1 函数说明

函数	功能
`IF(value, t, f)`	如果 `value` 为真，返回 `t`，否则返回 `f`
`IFNULL(value1, value2)`	如果 `value1` 不为空，返回 `value1`，否则返回 `value2`
`CASE WHEN [条件] THEN [结果] ... ELSE [默认值] END`	类似多分支判断
`CASE expr WHEN val1 THEN res1 ... ELSE default END`	判断表达式的值并返回对应结果

6.5.2 示例

SELECT IF(FALSE, 'Ok', 'Error');
SELECT IFNULL('Ok', 'Default');
SELECT IFNULL('', 'Default');
SELECT IFNULL(NULL, 'Default');

6.5.3 案例1

查询员工姓名和工作地址等级：

SELECT
    name,
    (CASE workaddress
        WHEN '北京' THEN '一线城市'
        WHEN '上海' THEN '一线城市'
        ELSE '二线城市'
    END) AS 工作地址
FROM emp;

6.5.4 案例2

按成绩判断等级：

SELECT
    id,
    name,
    (CASE WHEN math >= 85 THEN '优秀' WHEN math >= 60 THEN '及格' ELSE '不及格' END) AS 数学,
    (CASE WHEN english >= 85 THEN '优秀' WHEN english >= 60 THEN '及格' ELSE '不及格' END) AS 英语,
    (CASE WHEN chinese >= 85 THEN '优秀' WHEN chinese >= 60 THEN '及格' ELSE '不及格' END) AS 语文
FROM score;

6.6 窗口函数

6.6.1 概述

窗口函数用于在保留明细行的前提下，对某一组数据进行统计、排序、排名或前后行分析。
它和普通聚合函数的区别是：聚合函数通常会“多行变一行”，而窗口函数不会减少结果行数。
窗口函数通常搭配 OVER() 使用，可以在窗口中指定分组、排序和计算范围。

6.6.2 基本语法

函数名() OVER(
    [PARTITION BY 分组字段]
    [ORDER BY 排序字段]
)

说明：

PARTITION BY：把结果集划分为多个窗口，类似分组，但不会合并行。
ORDER BY：定义窗口内部的排序规则，常用于排名、累计计算等场景。
如果只写 OVER()，表示在整个结果集范围内计算。
OVER关键字的作用是：新增一列，其计算规则写在括号里
窗口函数不改变原表行数，只是在每一行旁边加一个或几个你想要的字段
OVER前面的函数返回的是按照OVER里定义的规则，为当前行计算出的单个值，也就是一个字段的值

6.6.3 常见窗口函数

函数	功能
`ROW_NUMBER() OVER(...)`	为窗口中的每一行生成唯一顺序号，不会并列
`RANK() OVER(...)`	排名，遇到并列时跳号(返回所有并列的)
`DENSE_RANK() OVER(...)`	密集排名，遇到并列时不跳号
`SUM(col) OVER(...)`	在窗口内做累计求和 / 分组求和
`AVG(col) OVER(...)`	在窗口内做平均值统计
`COUNT(col) OVER(...)`	在窗口内统计记录数
`MAX(col) OVER(...)`	在窗口内求最大值
`MIN(col) OVER(...)`	在窗口内求最小值
`LAG(col, n, default) OVER(...)`	取当前行前第 `n` 行的值
`LEAD(col, n, default) OVER(...)`	取当前行后第 `n` 行的值

6.6.4 排名函数示例

按部门内员工工资从高到低排名：

SELECT
    name,
    dept_id,
    salary,
    ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS rn,
    RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS rk,
    DENSE_RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS drk
FROM emp;

区别：

ROW_NUMBER()：即使工资相同，也会给不同序号，如 1、2、3
RANK()：工资相同会并列，后面的名次跳号，如 1、1、3
DENSE_RANK()：工资相同会并列，但后面的名次不跳号，如 1、1、2

6.6.5 分析函数示例

统计每个部门的员工人数，同时保留员工明细：

SELECT
    name,
    dept_id,
    COUNT(*) OVER(PARTITION BY dept_id) AS dept_count
FROM emp;

统计每个部门的工资累计值：

SELECT
    name,
    dept_id,
    salary,
    SUM(salary) OVER(PARTITION BY dept_id ORDER BY salary DESC) AS dept_salary_sum
FROM emp;

查看当前员工与上一位员工的工资差异：

SELECT
    name,
    salary,
    LAG(salary, 1, 0) OVER(ORDER BY salary DESC) AS prev_salary,
    salary - LAG(salary, 1, 0) OVER(ORDER BY salary DESC) AS diff_salary
FROM emp;

适用场景：

TopN 排名
分组内排序
累计求和 / 累计平均
同比、环比、前后行对比
保留明细的同时做统计分析

7 约束

7.1 概述

约束是作用于表中字段上的规则，用于限制存储在表中的数据。
目的是保证数据库中数据的正确、有效性和完整性。

7.2 常见约束

约束	描述	关键字
非空约束	限制该字段不能为 `NULL`	`NOT NULL`
唯一约束	保证该字段所有数据唯一、不重复	`UNIQUE`
主键约束	一行数据的唯一标识，要求非空且唯一	`PRIMARY KEY`
默认约束	未指定该字段值时采用默认值	`DEFAULT`
检查约束	保证字段值满足某个条件	`CHECK`
外键约束	让两张表建立连接，保证一致性和完整性	`FOREIGN KEY`

注意：

约束可以在创建表时添加，也可以在修改表时添加。

7.3 约束示例

CREATE TABLE tb_user(
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'ID 唯一标识',
    name VARCHAR(10) NOT NULL UNIQUE COMMENT '姓名',
    age INT CHECK (age > 0 AND age <= 120) COMMENT '年龄',
    status CHAR(1) DEFAULT '1' COMMENT '状态',
    gender CHAR(1) COMMENT '性别'
);

7.4 外键约束

7.4.1 介绍

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。
一般子表中保存外键字段，关联父表主键。

7.4.2 添加外键

CREATE TABLE 表名(
    字段名 数据类型,
    ...
    [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名)
);

1 2	`ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);`

1 2	`ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id);`

7.4.3 删除外键

1	`ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;`

1	`ALTER TABLE emp DROP FOREIGN KEY fk_emp_dept_id;`

7.4.4 删除 / 更新行为

行为	说明
`NO ACTION`	有关联外键时，不允许删除 / 更新父表记录，默认行为
`RESTRICT`	与 `NO ACTION` 一致
`CASCADE`	父表删除 / 更新时，子表对应记录也同步删除 / 更新
`SET NULL`	父表删除 / 更新时，子表外键字段置为 `NULL`
`SET DEFAULT`	父表变更时，子表外键列设为默认值，`InnoDB` 不支持

7.4.5 指定级联行为

ALTER TABLE 表名
ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名)
ON UPDATE CASCADE
ON DELETE CASCADE;

8 多表查询

8.1 多表关系

8.1.1 一对多

例子：部门和员工
关系：一个部门对应多个员工，一个员工对应一个部门
实现：在多的一方建立外键，指向一的一方主键

8.1.2 多对多

例子：学生和课程
关系：一个学生可以选多门课程，一门课程也可以被多个学生选择
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两边主键

8.1.3 一对一

例子：用户和用户详情
关系：常用于单表拆分，把基础字段和详情字段拆到两张表
实现：在任意一方加入外键关联另一方主键，并且设置该外键唯一 UNIQUE

8.2 多表查询概述

8.2.1 概述

多表查询就是从多张表中查询数据。
直接查询多张表会出现笛卡尔积，需要通过连接条件消除无效数据。

8.2.2 分类

连接查询
子查询

8.2.3 连接查询分类

内连接：查询两张表交集部分数据
左外连接：查询左表所有数据，以及交集部分数据
右外连接：查询右表所有数据，以及交集部分数据
自连接：当前表与自身进行连接查询

8.3 内连接

8.3.1 隐式内连接

1
2
3

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

8.3.2 显式内连接

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

8.3.3 示例

1
2
3

SELECT e.name, d.name
FROM emp e, dept d
WHERE e.dept_id = d.id;

1
2
3

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

注意：

一旦为表起了别名，就不能再使用原表名引用字段，只能使用别名。

8.4 外连接

8.4.1 左外连接

1
2
3

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

8.4.2 右外连接

1
2
3

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

8.4.3 示例

1
2
3

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

1
2
3

SELECT d.*, e.*
FROM dept d
LEFT JOIN emp e ON e.dept_id = d.id;

注意：

左外连接和右外连接可以相互替换，开发中更常用左外连接。
判断表的相对位置取决于表名在JOIN关键字的哪一边。
JOIN 左边的就是左表，右边的就是右表，与 LEFT 或 RIGHT 关键字无关——这两个关键字只是告诉你保留哪一侧的全部数据。
我自己的理解：
ON：定义连接规则，根据规则把多张表横向拼接成一张包含所有列的“虚拟中间表”。
WHERE：对这个“虚拟中间表”进行纵向筛选（按行过滤），得到最终要用的数据。

8.5 自连接

8.5.1 语法

1
2
3

SELECT 字段列表
FROM 表A 别名A
JOIN 表A 别名B ON 条件;

8.5.2 示例

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

1
2
3

SELECT a.name AS 员工, b.name AS 领导
FROM emp a
LEFT JOIN emp b ON a.managerid = b.id;

注意：

自连接必须起别名，否则无法区分字段来自哪一张表。

8.6 联合查询

8.6.1 语法

1
2
3

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

8.6.2 说明

UNION ALL：直接合并结果，不去重
UNION：合并结果并去重

8.6.3 示例

1
2
3

SELECT * FROM emp WHERE salary < 5000
UNION ALL
SELECT * FROM emp WHERE age > 50;

注意：

多个查询结果的列数必须一致，字段类型也应保持一致。

8.7 子查询

8.7.1 概述

SQL 语句中嵌套 SELECT 语句，称为子查询。
子查询外部语句可以是 INSERT、UPDATE、DELETE、SELECT。

8.7.1.1 按结果分类

标量子查询：结果为单个值
列子查询：结果为一列
行子查询：结果为一行
表子查询：结果为多行多列

8.7.1.2 按位置分类

WHERE 之后
FROM 之后
SELECT 之后

8.8 标量子查询

8.8.1 常用操作符

=
<>
>
>=
<
<=

8.8.2 示例

查询销售部所有员工：

1
2
3

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

查询在“方东白”入职之后的员工信息：

1
2
3

SELECT *
FROM emp
WHERE entrydate > (SELECT entrydate FROM emp WHERE name = '方东白');

8.9 列子查询

8.9.1 常用操作符

IN
NOT IN
ANY
SOME
ALL

8.9.2 示例

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

SELECT *
FROM emp
WHERE dept_id IN (
    SELECT id FROM dept WHERE name = '销售部' OR name = '市场部'
);

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

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

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

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

8.10 行子查询

8.10.1 常用操作符

=
<>
IN
NOT IN

8.10.2 示例

查询与“张无忌”的薪资和直属领导相同的员工：

SELECT *
FROM emp
WHERE (salary, managerid) = (
    SELECT salary, managerid
    FROM emp
    WHERE name = '张无忌'
);

8.11 表子查询

8.11.1 常用操作符

IN

8.11.2 示例

查询与“鹿杖客”、“宋远桥”的职位和薪资相同的员工：

SELECT *
FROM emp
WHERE (job, salary) IN (
    SELECT job, salary
    FROM emp
    WHERE name = '鹿杖客' OR name = '宋远桥'
);

查询入职日期在 2006-01-01 之后的员工信息及其部门信息：

SELECT e.*, d.*
FROM (
    SELECT *
    FROM emp
    WHERE entrydate > '2006-01-01'
) e
LEFT JOIN dept d ON e.dept_id = d.id;

8.12 多表查询案例

8.12.1 查询所有部门信息，并统计部门员工人数

SELECT d.id, d.name, (
    SELECT COUNT(*)
    FROM emp e
    WHERE e.dept_id = d.id
) AS 人数
FROM dept d;

8.12.2 查询所有学生的选课情况

SELECT s.name, s.no, c.name
FROM student s, student_course sc, course c
WHERE s.id = sc.studentid
  AND sc.courseid = c.id;

9 事务

9.1 事务简介

事务是一组操作的集合，是一个不可分割的工作单位。
事务中的操作要么同时成功，要么同时失败。
MySQL 默认自动提交事务，执行一条 DML 语句后会立即隐式提交。
典型场景：转账操作通常至少包含“扣款”和“加款”两个步骤，这两个步骤必须作为一个整体执行。
如果执行过程中中途报错，而前面的语句已经生效、后面的语句没有生效，就会造成数据不一致。
因此事务的核心价值就是：在业务操作成功时统一提交，在业务操作失败时统一回滚，把数据恢复到事务开始之前的状态。

9.2 事务操作

9.2.1 方式一：控制自动提交

SELECT @@autocommit;
SET @@autocommit = 0;
COMMIT;
ROLLBACK;

说明：

把 autocommit 设为 0 后，后续执行的 DML 语句不会自动提交，需要手动执行 COMMIT。
如果执行过程中出现异常，可以执行 ROLLBACK 撤销本次事务中的修改。

9.2.2 方式二：手动开启事务

START TRANSACTION;
-- 或 BEGIN;

COMMIT;
ROLLBACK;

说明：

这种方式更常用，也更清晰：显式开启事务，业务执行完成后再决定提交或回滚。
START TRANSACTION 和 BEGIN 都可以用来开启事务。

9.2.3 转账案例

START TRANSACTION;

SELECT * FROM account WHERE name = '张三';
UPDATE account SET money = money - 1000 WHERE name = '张三';
UPDATE account SET money = money + 1000 WHERE name = '李四';

COMMIT;
-- 如果中途报错，则执行 ROLLBACK;

9.3 事务四大特性

特性	说明
原子性（Atomicity）	事务是最小操作单元，要么全部成功，要么全部失败
一致性（Consistency）	事务完成时，所有数据都必须保持一致状态
隔离性（Isolation）	多个事务之间相互隔离，互不干扰
持久性（Durability）	事务一旦提交或回滚，对数据的改变就是永久的

9.4 并发事务问题

9.4.1 脏读

一个事务读到另一个事务还没有提交的数据。
比如事务 A 修改了一条记录但还没提交，事务 B 已经读取到了这条新值；如果事务 A 随后回滚，那么事务 B 读到的就是“脏数据”。

9.4.2 不可重复读

一个事务先后读取同一条记录，两次读到的数据不同。
一般是因为两个读取之间，另一个事务已经提交了对该记录的更新。

9.4.3 幻读

一个事务按条件查询时没有对应数据，但插入时却发现这行数据已存在，像出现了“幻影”。
本质上是同一事务中按照相同条件前后读取，结果集的“行数”发生了变化，通常和其他事务的插入操作有关。

9.5 事务隔离级别

9.5.1 隔离级别说明

隔离级别	脏读	不可重复读	幻读
`READ UNCOMMITTED`	会	会	会
`READ COMMITTED`	不会	会	会
`REPEATABLE READ`（默认）	不会	不会	会
`SERIALIZABLE`	不会	不会	不会

9.5.2 查看隔离级别

1	`SELECT @@TRANSACTION_ISOLATION;`

9.5.3 设置隔离级别

1 2	`SET [SESSION \| GLOBAL] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED \| READ COMMITTED \| REPEATABLE READ \| SERIALIZABLE };`

注意：

隔离级别越高，数据越安全，但性能越低。
REPEATABLE READ 是 MySQL 中常见的默认隔离级别。
记忆规律可以简单理解为：隔离级别越往上，能解决的并发问题越多。

9.6 补充-事务控制过程

9.6.1 未控制事务

9.6.1.1 正常情况

1
2
3

SELECT * FROM account WHERE name = '张三';
UPDATE account SET money = money - 1000 WHERE name = '张三';
UPDATE account SET money = money + 1000 WHERE name = '李四';

9.6.1.2 异常情况

SELECT * FROM account WHERE name = '张三';
UPDATE account SET money = money - 1000 WHERE name = '张三';
-- 中途报错
UPDATE account SET money = money + 1000 WHERE name = '李四';

9.6.2 控制事务一

9.6.2.1 查看 / 设置事务提交方式

1 2	`SELECT @@autocommit; SET @@autocommit = 0;`

9.6.2.2 提交事务

COMMIT;

9.6.2.3 回滚事务

1	`ROLLBACK;`

9.6.3 控制事务二

9.6.3.1 开启事务

1 2	`START TRANSACTION; -- 或 BEGIN;`

9.6.3.2 提交事务

COMMIT;

9.6.3.3 回滚事务

1	`ROLLBACK;`

10 综合案例-员工与部门管理

10.1 案例说明

下面这个案例尽量串起常用 / 核心语法：数据库、建表、约束、外键、增删改查、函数、多表查询、子查询、事务。

10.2 创建数据库并切换

1 2	`CREATE DATABASE IF NOT EXISTS company_db DEFAULT CHARSET utf8mb4; USE company_db;`

10.3 创建表

CREATE TABLE dept(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '部门ID',
    name VARCHAR(20) NOT NULL UNIQUE COMMENT '部门名称'
) COMMENT '部门表';

CREATE TABLE emp(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工ID',
    name VARCHAR(20) NOT NULL COMMENT '姓名',
    gender CHAR(1) DEFAULT '男' COMMENT '性别',
    age INT CHECK (age > 0 AND age <= 120) COMMENT '年龄',
    job VARCHAR(20) COMMENT '岗位',
    salary DECIMAL(10,2) DEFAULT 0 COMMENT '薪资',
    entrydate DATE COMMENT '入职日期',
    managerid INT COMMENT '直属领导ID',
    dept_id INT COMMENT '部门ID',
    workno VARCHAR(10) UNIQUE COMMENT '工号',
    CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES dept(id)
) COMMENT '员工表';

10.4 插入数据

1 2	`INSERT INTO dept(name) VALUES ('研发部'), ('市场部'), ('财务部'), ('销售部');`

INSERT INTO emp(name, gender, age, job, salary, entrydate, managerid, dept_id, workno)
VALUES
('张三', '男', 28, '开发', 9000, '2021-03-15', NULL, 1, '1'),
('李四', '男', 32, '开发', 12000, '2020-07-01', 1, 1, '2'),
('王五', '女', 26, '测试', 8000, '2022-01-10', 2, 1, '3'),
('赵六', '男', 35, '市场专员', 7000, '2019-11-20', NULL, 2, '4'),
('钱七', '女', 30, '会计', 8500, '2018-05-08', NULL, 3, '5'),
('孙八', '男', 40, '销售经理', 15000, '2017-09-12', NULL, 4, '6');

10.5 修改数据

统一把工号补齐为 5 位：

1 2	`UPDATE emp SET workno = LPAD(workno, 5, '0');`

给研发部员工涨薪 1000：

1
2
3

UPDATE emp
SET salary = salary + 1000
WHERE dept_id = (SELECT id FROM dept WHERE name = '研发部');

10.6 删除数据

删除年龄小于 25 的员工：

1 2	`DELETE FROM emp WHERE age < 25;`

10.7 基础查询

10.7.1 查询所有员工

1 2	`SELECT * FROM emp;`

10.7.2 查询指定字段并设置别名

1 2	`SELECT name AS 姓名, job AS 岗位, salary AS 薪资 FROM emp;`

10.7.3 去重查询

1 2	`SELECT DISTINCT job FROM emp;`

10.8 条件查询

10.8.1 比较 / 范围 / 模糊 / 空值判断

SELECT *
FROM emp
WHERE salary >= 9000
  AND age BETWEEN 25 AND 35
  AND name LIKE '张%'
  AND managerid IS NULL;

10.8.2 IN / OR / NOT

SELECT *
FROM emp
WHERE dept_id IN (1, 2, 4)
   OR NOT gender = '女';

10.9 聚合、分组、排序、分页

10.9.1 聚合函数

SELECT COUNT(*) AS 员工数,
       MAX(salary) AS 最高薪资,
       MIN(salary) AS 最低薪资,
       AVG(salary) AS 平均薪资,
       SUM(salary) AS 薪资总和
FROM emp;

10.9.2 分组查询

SELECT dept_id, COUNT(*) AS 人数, AVG(salary) AS 平均薪资
FROM emp
GROUP BY dept_id
HAVING COUNT(*) >= 1;

10.9.3 排序 + 分页

SELECT name, salary
FROM emp
ORDER BY salary DESC, age ASC
LIMIT 0, 3;

10.10 常用函数

10.10.1 字符串函数

SELECT CONCAT(name, '-', job) AS 员工信息,
       UPPER(job) AS 大写岗位,
       SUBSTRING(name, 1, 1) AS 姓
FROM emp;

10.10.2 日期函数

SELECT name,
       CURDATE() AS 当前日期,
       DATEDIFF(CURDATE(), entrydate) AS 入职天数
FROM emp;

10.10.3 流程函数

SELECT name,
       IFNULL(job, '未分配岗位') AS 岗位,
       CASE
           WHEN salary >= 12000 THEN '高'
           WHEN salary >= 8000 THEN '中'
           ELSE '低'
       END AS 薪资等级
FROM emp;

10.11 多表查询

10.11.1 内连接

1
2
3

SELECT e.name AS 员工姓名, d.name AS 部门名称
FROM emp e
INNER JOIN dept d ON e.dept_id = d.id;

10.11.2 左外连接

1
2
3

SELECT e.name AS 员工姓名, d.name AS 部门名称
FROM emp e
LEFT JOIN dept d ON e.dept_id = d.id;

10.11.3 自连接

1
2
3

SELECT e.name AS 员工, m.name AS 领导
FROM emp e
LEFT JOIN emp m ON e.managerid = m.id;

10.11.4 联合查询

SELECT name, job, salary
FROM emp
WHERE salary < 9000
UNION
SELECT name, job, salary
FROM emp
WHERE age > 35;

10.12 子查询

10.12.1 标量子查询

查询研发部员工：

1
2
3

SELECT *
FROM emp
WHERE dept_id = (SELECT id FROM dept WHERE name = '研发部');

10.12.2 列子查询

查询市场部和销售部员工：

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

10.12.3 行子查询

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

SELECT *
FROM emp
WHERE (salary, managerid) = (
    SELECT salary, managerid
    FROM emp
    WHERE name = '李四'
);

10.12.4 表子查询

查询 2020 年以后入职员工及其部门信息：

SELECT t.*, d.name AS 部门名称
FROM (
    SELECT *
    FROM emp
    WHERE entrydate >= '2020-01-01'
) t
LEFT JOIN dept d ON t.dept_id = d.id;

10.13 事务

模拟转账：

START TRANSACTION;

UPDATE emp SET salary = salary - 500 WHERE name = '李四';
UPDATE emp SET salary = salary + 500 WHERE name = '张三';

COMMIT;
-- 如果中途报错，则执行 ROLLBACK;

10.14 执行顺序理解

上面这类复杂查询的执行顺序通常是：

1	`FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT`

11 正则表达式

11.1 概述

正则表达式（Regular Expression）是一种强大的文本模式匹配工具，在 SQL 中常用于复杂字符串的查找、替换和提取操作。MySQL 提供了对正则表达式的内置支持。

11.2 常用元字符

元字符	描述	示例
`^`	匹配字符串的开始	`^a` 匹配以 a 开头的字符串
`$`	匹配字符串的结束	`a$` 匹配以 a 结尾的字符串
`.`	匹配任意单个字符（除换行符外）	`a.c` 匹配 abc、a1c 等
`*`	匹配前一个字符零次或多次	`a*b` 匹配 b、ab、aab 等
`+`	匹配前一个字符一次或多次	`a+b` 匹配 ab、aab 等，不匹配 b
`?`	匹配前一个字符零次或一次	`a?b` 匹配 b、ab
`[]`	匹配括号内的任意一个字符	`[abc]` 匹配 a、b 或 c
`[^]`	匹配不在括号内的任意字符	`[^abc]` 匹配除 a、b、c 外的任意字符
`\|`	匹配两个或多个分支选择（或）	`a\|b` 匹配 a 或 b
`{n}`	匹配前一个字符恰好 n 次	`a{3}` 匹配 aaa
`{n,}`	匹配前一个字符至少 n 次	`a{2,}` 匹配 aa、aaa 等
`{n,m}`	匹配前一个字符至少 n 次，最多 m 次	`a{1,3}` 匹配 a、aa、aaa

11.3 常用正则函数

MySQL 8.0 引入了多个强大的正则表达式函数（旧版本主要使用 REGEXP / RLIKE 运算符）。

函数 / 运算符	功能说明
`expr REGEXP pat` / `expr RLIKE pat`	模式匹配，如果 `expr` 匹配 `pat` 则返回 1，否则返回 0
`REGEXP_LIKE(expr, pat[, match_type])`	同 `REGEXP`，但可以指定匹配参数（如区分大小写）
`REGEXP_INSTR(expr, pat[, pos[, occurrence[, return_option[, match_type]]]])`	返回匹配模式的子串的起始索引（找不到返回 0）
`REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])`	替换匹配模式的子串
`REGEXP_SUBSTR(expr, pat[, pos[, occurrence[, match_type]]])`	返回匹配模式的子串

11.4 示例操作

11.4.1 基础匹配 (`REGEXP` / `RLIKE`)

查询姓名以“张”开头或以“三”结尾的员工：

1
2
3

SELECT *
FROM emp
WHERE name REGEXP '^张|三$';

查询工号包含连续两个数字的员工：

1
2
3

SELECT *
FROM emp
WHERE workno REGEXP '[0-9]{2}';

11.4.2 提取子串 (`REGEXP_SUBSTR`)

从邮箱中提取域名部分：

1 2	`SELECT email, REGEXP_SUBSTR(email, '@.*$') AS domain FROM users;`

11.4.3 替换子串 (`REGEXP_REPLACE`)

将手机号中间四位替换为星号：

1 2	`SELECT phone, REGEXP_REPLACE(phone, '([0-9]{3})[0-9]{4}([0-9]{4})', '$1****$2') AS masked_phone FROM users;`

11.4.4 查找位置 (`REGEXP_INSTR`)

查找第一个非数字字符出现的位置：

1 2	`SELECT text_col, REGEXP_INSTR(text_col, '[^0-9]') AS first_non_digit_pos FROM table_name;`

11.5 注意事项

MySQL 正则表达式默认不区分大小写，若要区分大小写，可以使用 BINARY 关键字（如 REGEXP BINARY 'a'）或在 match_type 参数中指定 'c'。
正则表达式查询通常会导致全表扫描，无法使用索引，在数据量大的表上使用需谨慎。
在匹配具有特殊含义的元字符（如 .、*、[ 等）本身时，需要使用转义符（MySQL 中通常使用 \\，如 \\.）。

评论区

技术学习

SQL

https://aoiblog.top/2026/03/31/sql/

作者

Aoitsuki

发布于

2026年4月1日

许可协议

业务与产品上一篇

博客更新声明下一篇

SQL

SQL

目录

1 SQL基础

1.1 SQL通用语法

1.2 SQL分类

2 DDL

2.1 数据库操作

2.1.1 查询

2.1.2 创建

2.1.3 删除

2.1.4 使用

2.2 表操作

2.2.1 查询

2.2.2 创建

2.2.3 修改

2.2.4 删除

2.3 SQL常用字段和字段类型

2.3.1 一、数值类型（Numeric Types）

2.3.1.1 整数类型

2.3.1.2 小数类型

2.3.2 二、字符串类型（String Types）

2.3.2.1 定长 vs 变长

2.3.2.2 大文本类型

2.3.3 三、日期和时间类型（Date & Time）

2.3.4 四、枚举与集合类型（慎用）

2.3.5 五、二进制与特殊类型

2.3.6 六、选择字段类型的黄金原则

2.3.7 七、常见字段类型搭配示例

3 DML

3.1 添加数据

3.2 修改数据

3.3 删除数据

4 DQL

4.1 语法

4.1.1 常见查询分类

4.2 基本查询

4.2.1 查询多个字段

4.2.2 设置别名

4.2.3 去除重复记录

4.3 条件查询

4.3.1 语法

4.3.1.1 比较运算符

4.3.1.2 逻辑运算符

4.4 聚合函数

4.4.1 介绍

4.4.2 常见聚合函数

4.4.3 语法

4.5 分组查询

4.5.1 语法

4.5.2 WHERE 与 HAVING 区别

4.6 排序查询

4.6.1 语法

4.6.2 排序方式

4.7 分页查询

4.7.1 语法

4.8 执行顺序

4.8.1 编写顺序

4.8.2 执行顺序

5 DCL

5.1 管理用户

5.1.1 查询用户

5.1.2 创建用户

5.1.3 修改用户密码

5.1.4 删除用户

5.2 权限控制

5.2.1 常用权限

5.2.2 查询权限

5.2.3 授予权限

5.2.4 撤销权限

6 常用函数

6.1 常用函数概述

6.2 字符串函数

6.2.1 函数说明

6.2.2 示例

6.2.3 案例

6.3 数值函数

6.3.1 函数说明

6.3.2 示例

6.3.3 案例

4.5.2 `WHERE` 与 `HAVING` 区别