Appearance
MySQL 笔记
导航目录
- 1. 数据库与 SQL 基础
- 2. 常用字段类型（速查）
- 2.1 MySQL 连接与常见环境问题
- 2.2 字符集与排序规则
- 3. DDL：库与表操作
- 3.1 数据库操作
- 3.2 表操作
- 3.3 修改表结构（ALTER）
- 4. DML：增删改
- 4.1 插入数据
- 4.2 更新数据
- 4.3 删除数据
- 5. 约束（重点）
- 5.1 主键与自增
- 5.2 非空与唯一
- 5.3 外键约束（一对多示例）
- 5.4 默认值与检查约束
- 6. 单表查询（DQL）
- 6.1 基础查询
- 6.2 条件查询
- 6.3 排序查询
- 6.4 聚合查询
- 6.5 分组查询
- 6.6 分页查询
- 6.7 SQL 关键字顺序（书写/执行）
- 7. 多表关系与多表查询
- 7.1 三种关系
- 7.2 内连接、外连接
- 7.3 子查询
- 7.4 交叉查询与 UNION
- 7.5 多对多关系建模
- 7.6 EXISTS/NOT EXISTS 与自连接（补充）
- 8. 查询性能优化
- 8.1 常见性能瓶颈
- 8.2 索引设计基础
- 8.3 用 EXPLAIN 看执行计划
- 8.4 避免索引失效的写法
- 8.5 分页性能优化
- 8.6 查询性能优化检查清单
- 8.7 覆盖索引、回表、索引下推（ICP）
- 8.8 EXPLAIN 访问类型速查（type）
- 9. 常用函数（字符串、数值、日期、流程）
- 9.1 字符串函数
- 9.2 数值函数
- 9.3 日期函数
- 9.4 流程函数
- 10. DCL：用户与权限
- 10.1 创建用户与授权
- 10.2 撤销、查看、删除
- 10.3 修改用户密码（补充）
- 11. 事务（Transaction）
- 12. 备份与还原
- 13. 设计规范与复习建议
- 13.1 数据库设计三范式（1NF/2NF/3NF）
- 13.2 实战规范建议
1. 数据库与 SQL 基础
数据库可以理解为一个长期存储数据的仓库。
相比内存中的集合，数据库数据在程序结束后依然存在，并且支持按条件快速查询。
SQL 是关系型数据库的通用语言，核心分类如下：
| 分类 | 全称 | 常用关键字 | 作用 |
|---|---|---|---|
| DDL | Data Definition Language | CREATE ALTER DROP | 定义数据库对象（库、表、列） |
| DML | Data Manipulation Language | INSERT UPDATE DELETE | 操作表中的数据 |
| DQL | Data Query Language | SELECT | 查询数据 |
| DCL | Data Control Language | GRANT REVOKE CREATE USER | 用户与权限管理 |
| TCL | Transaction Control Language | BEGIN/START TRANSACTION COMMIT ROLLBACK | 事务控制 |
专业补充：
- 标准 SQL 与方言：MySQL 遵循 SQL 标准，但也有自己的方言（如
LIMIT）。 - SQL 不区分关键字大小写，但工程实践建议关键字大写、对象名小写。
- 一条 SQL 一个职责：便于排错、审计和回滚。
2. 常用字段类型（速查）
| 类型 | 写法示例 | 说明 | 典型场景 |
|---|---|---|---|
| 布尔/状态 | TINYINT | 常用 0/1 存状态 | 启用/禁用、是否删除 |
| 小整数 | SMALLINT | 范围小于 INT | 较小计数值 |
| 整数 | INT | 常规整数 | 编号、数量 |
| 大整数 | BIGINT | 大范围整数 | 大数据量主键、流水号 |
| 定点小数 | DECIMAL(10,2) | 精确小数 | 金额、价格 |
| 浮点数 | DOUBLE | 近似小数，可能有误差 | 非金额统计值 |
| 定长字符串 | CHAR(11) | 固定长度，长度不足补空格 | 固定长度编码 |
| 可变字符串 | VARCHAR(50) | 可变长度文本 | 名称、手机号 |
| 文本 | TEXT | 长文本 | 描述、备注 |
| 定长二进制 | BINARY(n) | 固定长度字节 | 固定长度二进制标记 |
| 可变二进制 | VARBINARY(n) | 可变长度字节 | 二进制协议数据 |
| 二进制大对象 | BLOB | 二进制大数据 | 图片、文件二进制内容 |
| 日期 | DATE | 年月日 | 入职日期 |
| 时间 | TIME | 时分秒 | 时长、时间点 |
| 日期时间 | DATETIME | 年月日时分秒 | 创建时间 |
| 时间戳 | TIMESTAMP | 时间戳类型，受时区影响 | 记录更新时间 |
涉及金额时建议用
DECIMAL，避免DOUBLE的精度误差。
字段设计建议：
- 长度按业务上限设计，避免盲目给超大长度。
- 字段允许为空要有业务理由，核心字段建议配合
NOT NULL。 - 涉及“状态”字段时，建议配套注释，明确枚举含义（如
0=禁用,1=启用）。
2.1 MySQL 连接与常见环境问题
bash
# 登录（推荐，不在命令行明文显示密码）
mysql -u root -pbash
# Windows 服务启动/停止（MySQL80 为常见服务名）
net start MySQL80
net stop MySQL80常见问题与处理：
| 问题 | 常见原因 | 处理方式 |
|---|---|---|
mysql 不是内部或外部命令 | 未配置 bin 到 PATH | 配置 MySQL bin 目录环境变量 |
ERROR 1045 | 用户名/密码错误 | 检查账号密码或重置密码 |
ERROR 2003 | 服务未启动 | 先启动 MySQL 服务 |
| 控制台中文乱码 | 客户端与服务端字符集不一致 | 临时 set names gbk 或统一配置 utf8/utf8mb4 |
2.2 字符集与排序规则
字符集决定“如何存储字符”，排序规则（Collation）决定“如何比较和排序字符”。
| 配置项 | 建议值 | 说明 |
|---|---|---|
| 数据库字符集 | utf8mb4 | 完整 UTF-8，兼容 emoji |
| 排序规则 | utf8mb4_general_ci 或 utf8mb4_0900_ai_ci | ci 表示大小写不敏感 |
| 连接字符集 | 与库表保持一致 | 防止插入/查询乱码 |
sql
-- 查看字符集与排序规则
SHOW VARIABLES LIKE 'character_%';
SHOW VARIABLES LIKE 'collation_%';sql
-- 创建数据库时指定字符集与排序规则
CREATE DATABASE `mytest01`
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;sql
-- 修改表字符集
ALTER TABLE `product`
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;sql
-- 会话级临时设置（仅当前连接生效）
SET NAMES utf8mb4;3. DDL：库与表操作
3.1 数据库操作
sql
-- 创建库
CREATE DATABASE `mytest01`;
-- 查看所有库
SHOW DATABASES;
-- 切换库
USE `mytest01`;
-- 删除库
DROP DATABASE `mytest01`;建议：
- 开发和测试环境可频繁建删库；生产环境删除库属于高危操作，必须审批。
- 建库时可显式指定字符集，减少乱码问题。
sql
CREATE DATABASE `mytest01` CHARACTER SET utf8mb4;3.2 表操作
sql
-- 创建商品表
CREATE TABLE `product`(
`pid` INT PRIMARY KEY,
`pname` VARCHAR(20),
`price` DOUBLE
);
-- 查看所有表
SHOW TABLES;
-- 查看表结构
DESC `product`;
-- 删除表
DROP TABLE `product`;3.3 修改表结构（ALTER）
sql
-- 添加列
ALTER TABLE `product` ADD `pdesc` VARCHAR(100);
-- 修改列类型
ALTER TABLE `product` MODIFY `price` DECIMAL(10,2);
-- 修改列名
ALTER TABLE `product` CHANGE `pdesc` `description` VARCHAR(100);
-- 删除列
ALTER TABLE `product` DROP `description`;
-- 修改表名
RENAME TABLE `product` TO `products`;
RENAME TABLE `products` TO `product`;工程提示：
- 结构变更要遵循 先备份 -> 再变更 -> 再验证。
- 频繁变更字段名会增加应用代码改造成本，建议先在设计阶段定稿命名。
4. DML：增删改
4.1 插入数据
sql
INSERT INTO `product`(`pid`,`pname`,`price`) VALUES (1,'联想',5000);
INSERT INTO `product`(`pid`,`pname`,`price`) VALUES (2,'海尔',3000);
-- 批量插入
INSERT INTO `product`(`pid`,`pname`,`price`) VALUES
(3,'雷神',5000),
(4,'JACK JONES',800),
(5,'真维斯',200);4.2 更新数据
sql
-- 将 pid=5 的价格更新为 260
UPDATE `product`
SET `price` = 260
WHERE `pid` = 5;4.3 删除数据
sql
-- 删除 pid=4 的数据
DELETE FROM `product`
WHERE `pid` = 4;执行
UPDATE/DELETE前，建议先执行同条件SELECT确认命中范围。
DELETE 与 TRUNCATE 区别（来自原笔记高频点）：
| 操作 | 是否可带条件 | 自增计数影响 | 适用场景 |
|---|---|---|---|
DELETE | 可带 WHERE | 一般不重置 | 按条件删除 |
TRUNCATE TABLE | 不可带 WHERE | 会重置（常见） | 清空整表数据 |
sql
-- 清空表数据并重置自增计数（常见行为）
TRUNCATE TABLE `product`;5. 约束（重点）
约束用于保证数据质量。
| 约束 | 关键字 | 作用 |
|---|---|---|
| 主键 | PRIMARY KEY | 唯一且非空，标识一条记录 |
| 自增 | AUTO_INCREMENT | 自动生成递增编号 |
| 非空 | NOT NULL | 字段值不能为空 |
| 唯一 | UNIQUE | 字段值不能重复 |
| 外键 | FOREIGN KEY | 保证表关系的引用有效 |
专业补充：
- 主键 vs 唯一约束：都能保证唯一；主键不能为
NULL且一张表只能有一个主键。 - 外键本质是“引用完整性约束”，能阻止无效引用数据进入子表。
5.1 主键与自增
sql
CREATE TABLE `user`(
`uid` INT PRIMARY KEY AUTO_INCREMENT,
`username` VARCHAR(20),
`password` VARCHAR(20)
);
INSERT INTO `user`(`username`,`password`) VALUES ('tom','111');
INSERT INTO `user`(`username`,`password`) VALUES ('jack','222');5.2 非空与唯一
sql
CREATE TABLE `role`(
`rid` INT PRIMARY KEY AUTO_INCREMENT,
`rname` VARCHAR(20) UNIQUE NOT NULL
);5.3 外键约束（一对多示例）
sql
CREATE TABLE `category` (
`cid` VARCHAR(32) PRIMARY KEY,
`cname` VARCHAR(50)
);
CREATE TABLE `products`(
`pid` VARCHAR(32) PRIMARY KEY,
`pname` VARCHAR(50),
`price` DOUBLE,
`category_id` VARCHAR(32)
);
ALTER TABLE `products`
ADD CONSTRAINT `cp`
FOREIGN KEY (`category_id`) REFERENCES `category`(`cid`);5.4 默认值与检查约束
sql
-- 默认值约束
CREATE TABLE `student`(
`id` INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
`status` TINYINT DEFAULT 1
);
-- 检查约束（MySQL 8.0+）
CREATE TABLE `score_record`(
`id` INT PRIMARY KEY AUTO_INCREMENT,
`score` INT,
CONSTRAINT `chk_score_range` CHECK (`score` >= 0 AND `score` <= 100)
);说明：
DEFAULT用于兜底默认值，减少漏填导致的数据异常。CHECK用于限制数值范围、状态范围等业务规则（建议在 MySQL 8.0+ 使用）。
6. 单表查询（DQL）
6.1 基础查询
sql
-- 查询所有列
SELECT * FROM `product`;
-- 查询指定列
SELECT `pid`,`pname` FROM `product`;
-- 去重
SELECT DISTINCT(`price`) FROM `product`;6.2 条件查询
常用条件运算符速查：
| 类型 | 写法 | 含义 |
|---|---|---|
| 比较 | =, <>, >, >=, <, <= | 基础比较 |
| 区间 | BETWEEN a AND b | 闭区间 [a,b] |
| 集合 | IN (...) | 匹配集合中的任一值 |
| 模糊 | LIKE | % 任意长度，_ 单字符 |
| 空判断 | IS NULL / IS NOT NULL | 判断空值 |
sql
SELECT * FROM `product` WHERE `price` > 60;
SELECT * FROM `product` WHERE `price` BETWEEN 200 AND 1000;
SELECT * FROM `product` WHERE `price` IN (200,800);
SELECT * FROM `product` WHERE `pname` LIKE '香%';
SELECT * FROM `product` WHERE `pname` LIKE '%霸%';
SELECT * FROM `product` WHERE `pname` IS NULL;6.3 排序查询
sql
SELECT * FROM `product` ORDER BY `price` DESC;
SELECT * FROM `product` ORDER BY `price` ASC;6.4 聚合查询
聚合函数列表（速查）：
| 函数 | 说明 | 是否忽略 NULL | 常见用法 |
|---|---|---|---|
COUNT(*) | 统计总行数 | 不忽略（统计行） | SELECT COUNT(*) FROM product; |
COUNT(col) | 统计某列非空行数 | 忽略 | SELECT COUNT(price) FROM product; |
SUM(col) | 对某列求和 | 忽略 | SELECT SUM(price) FROM product; |
AVG(col) | 对某列求平均值 | 忽略 | SELECT AVG(price) FROM product; |
MIN(col) | 取最小值 | 忽略 | SELECT MIN(price) FROM product; |
MAX(col) | 取最大值 | 忽略 | SELECT MAX(price) FROM product; |
sql
SELECT COUNT(*) FROM `product`;
SELECT SUM(`price`) FROM `product`;
SELECT AVG(`price`) FROM `product` WHERE `pid` IN (1,3,7);
SELECT MIN(`price`), MAX(`price`) FROM `product`;说明：
COUNT(*)统计行数；COUNT(字段)会忽略该字段为NULL的行。- 聚合查询常与
GROUP BY组合，形成统计报表。
6.5 分组查询
sql
-- 按商品名分组，统计价格总和
SELECT `pname`, SUM(`price`) AS total_price
FROM `product`
GROUP BY `pname`;
-- 分组后筛选
SELECT `pname`, SUM(`price`) AS total_price
FROM `product`
GROUP BY `pname`
HAVING SUM(`price`) >= 2000;重点区别：
WHERE是分组前过滤，HAVING是分组后过滤。
6.6 分页查询
分页公式：起始下标 = (当前页 - 1) * 每页条数
sql
-- 每页 5 条，第 1 页
SELECT * FROM `product` LIMIT 0,5;
-- 第 2 页
SELECT * FROM `product` LIMIT 5,5;6.7 SQL 关键字顺序（书写/执行）
| 维度 | 顺序 |
|---|---|
| 书写顺序 | SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT |
| 执行顺序（逻辑） | FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT |
7. 多表关系与多表查询
7.1 三种关系
- 一对一：如人员和身份证
- 一对多：如分类和商品、部门和员工
- 多对多：如订单和商品（通过中间表）
7.2 内连接、外连接
连接方式对比：
| 连接方式 | 关键字 | 返回结果特征 |
|---|---|---|
| 内连接 | JOIN ... ON ... | 仅返回两表匹配成功的记录 |
| 左外连接 | LEFT JOIN ... ON ... | 返回左表全部 + 右表匹配 |
| 右外连接 | RIGHT JOIN ... ON ... | 返回右表全部 + 左表匹配 |
sql
-- 内连接（显式）
SELECT *
FROM `category` c
JOIN `products` p ON c.`cid` = p.`category_id`;
-- 内连接（隐式）
SELECT *
FROM `category` c, `products` p
WHERE c.`cid` = p.`category_id`;
-- 左外连接
SELECT *
FROM `category` c
LEFT JOIN `products` p ON c.`cid` = p.`category_id`;
-- 右外连接
SELECT *
FROM `category` c
RIGHT JOIN `products` p ON c.`cid` = p.`category_id`;7.3 子查询
sql
-- 查询“化妆品”分类下的商品
SELECT *
FROM `products`
WHERE `category_id` = (
SELECT `cid` FROM `category` WHERE `cname` = '化妆品'
);
-- 查询“化妆品”和“家电”分类下的商品
SELECT *
FROM `products`
WHERE `category_id` IN (
SELECT `cid` FROM `category` WHERE `cname` IN ('化妆品','家电')
);实践建议：
- 子查询能写清楚业务逻辑时优先保证可读性；复杂场景再考虑与连接写法做性能对比。
- 使用子查询时注意返回结果类型：单值子查询配
=，多值子查询配IN。
7.4 交叉查询与 UNION
sql
-- 交叉查询（会产生笛卡尔积，通常不直接使用）
SELECT *
FROM `category`, `products`;
-- 交叉查询 + 连接条件（等价于隐式内连接）
SELECT *
FROM `category`, `products`
WHERE `category`.`cid` = `products`.`category_id`;sql
-- 用 UNION 模拟全外连接（MySQL 无 FULL OUTER JOIN）
SELECT *
FROM `category` c
LEFT JOIN `products` p ON c.`cid` = p.`category_id`
UNION
SELECT *
FROM `category` c
RIGHT JOIN `products` p ON c.`cid` = p.`category_id`;7.5 多对多关系建模
sql
-- 主表1：订单
CREATE TABLE `orders`(
`oid` VARCHAR(32) PRIMARY KEY,
`totalprice` DOUBLE
);
-- 主表2：商品（示例沿用 products）
-- 中间表：订单项（从表）
CREATE TABLE `orderitem`(
`pid` VARCHAR(50),
`oid` VARCHAR(50)
);
ALTER TABLE `orderitem`
ADD CONSTRAINT `po` FOREIGN KEY (`pid`) REFERENCES `products`(`pid`);
ALTER TABLE `orderitem`
ADD CONSTRAINT `oo` FOREIGN KEY (`oid`) REFERENCES `orders`(`oid`);7.6 EXISTS/NOT EXISTS 与自连接（补充）
sql
-- EXISTS：查询有员工的部门
SELECT *
FROM `dept` d
WHERE EXISTS (
SELECT 1 FROM `emp` e WHERE e.`dept_id` = d.`id`
);
-- NOT EXISTS：查询没有员工的部门
SELECT *
FROM `dept` d
WHERE NOT EXISTS (
SELECT 1 FROM `emp` e WHERE e.`dept_id` = d.`id`
);sql
-- 自连接：同部门员工两两配对（去重）
SELECT e1.`name` AS emp1, e2.`name` AS emp2, e1.`dept_id`
FROM `emp` e1
JOIN `emp` e2 ON e1.`dept_id` = e2.`dept_id`
WHERE e1.`id` < e2.`id`;8. 查询性能优化
查询性能优化的核心思路是：减少扫描行数、让 SQL 命中索引、避免无效排序和回表。
8.1 常见性能瓶颈
| 瓶颈类型 | 典型表现 | 常见原因 |
|---|---|---|
| 全表扫描 | 查询慢、CPU 高 | 没有可用索引或条件写法导致索引失效 |
| 文件排序 | ORDER BY 慢 | 排序字段无索引，或与过滤字段索引不匹配 |
| 深分页 | 页码越大越慢 | LIMIT offset,n 的 offset 很大 |
| 回表过多 | I/O 偏高 | SELECT * 导致从索引再回主表取大量列 |
8.2 索引设计基础
常见索引类型与场景：
| 索引 | 适用场景 |
|---|---|
| 主键索引 | 主键查询、关联主键 |
| 唯一索引 | 唯一字段查找（如账号） |
| 普通索引 | 高频过滤字段（如 dept_id、join_date） |
| 联合索引 | 多条件组合查询、排序 |
sql
-- 单列索引
CREATE INDEX `idx_emp_dept_id` ON `emp`(`dept_id`);
CREATE INDEX `idx_emp_join_date` ON `emp`(`join_date`);
-- 联合索引（按部门过滤并按薪资排序）
CREATE INDEX `idx_emp_dept_salary` ON `emp`(`dept_id`, `salary`);联合索引遵循最左前缀原则：优先使用从左到右连续命中的字段。
8.3 用 EXPLAIN 看执行计划
sql
EXPLAIN
SELECT `id`, `name`, `salary`
FROM `emp`
WHERE `dept_id` = 2
ORDER BY `salary` DESC;重点关注字段：
| 字段 | 含义 | 目标 |
|---|---|---|
type | 访问类型 | 至少达到 range，最好是 ref/const |
key | 实际命中的索引 | 不为 NULL |
rows | 预估扫描行数 | 越小越好 |
Extra | 额外信息 | 尽量避免 Using filesort、Using temporary |
8.4 避免索引失效的写法
sql
-- 不推荐：对索引列做函数运算，容易失效
SELECT * FROM `emp` WHERE YEAR(`join_date`) = 2018;
-- 推荐：改成范围查询
SELECT * FROM `emp`
WHERE `join_date` >= '2018-01-01' AND `join_date` < '2019-01-01';sql
-- 不推荐：前导模糊匹配，通常无法走普通索引
SELECT * FROM `product` WHERE `pname` LIKE '%香%';
-- 推荐：能前缀匹配时尽量前缀匹配
SELECT * FROM `product` WHERE `pname` LIKE '香%';8.5 分页性能优化
普通分页（数据量大时会变慢）：
sql
SELECT * FROM `emp` ORDER BY `id` LIMIT 100000, 20;推荐“基于主键游标”的分页方式：
sql
-- 记住上一页最后一条 id，例如 last_id = 100000
SELECT `id`, `name`, `salary`
FROM `emp`
WHERE `id` > 100000
ORDER BY `id`
LIMIT 20;这种方式在大页码场景通常更稳定，因为不需要跳过大量无用记录。
8.6 查询性能优化检查清单
- 是否只查询必要列（避免
SELECT *） - 过滤条件字段是否有索引
- 排序字段是否与索引顺序匹配
- 是否使用
EXPLAIN验证执行计划 - 是否避免了函数计算、隐式类型转换导致的索引失效
- 深分页是否改为游标分页/主键分页
8.7 覆盖索引、回表、索引下推（ICP）
| 概念 | 说明 | 性能影响 |
|---|---|---|
| 覆盖索引 | 查询字段都在索引中，直接返回结果 | 最优，通常减少 I/O |
| 回表查询 | 二级索引命中后再回主键索引取完整行 | I/O 增加，查询变慢 |
| 索引下推（ICP） | 在索引层提前过滤部分条件 | 减少回表次数，提升效率 |
sql
-- 更容易走覆盖索引（只查必要列）
EXPLAIN SELECT `id`, `dept_id`, `salary`
FROM `emp`
WHERE `dept_id` = 2;
-- 容易产生更多回表（SELECT *）
EXPLAIN SELECT *
FROM `emp`
WHERE `dept_id` = 2;8.8 EXPLAIN 访问类型速查（type）
| type | 含义 | 一般性能 |
|---|---|---|
ALL | 全表扫描 | 差 |
index | 全索引扫描 | 较差 |
range | 索引范围扫描 | 中 |
ref | 非唯一索引等值匹配 | 良 |
eq_ref | 主键/唯一索引关联匹配 | 优 |
const | 主键/唯一索引常量匹配 | 最优 |
优化目标：尽量避免 ALL，优先让查询达到 range/ref/eq_ref/const。
9. 常用函数（字符串、数值、日期、流程）
以 t_user(id, uname, age, sex) 为例。
函数类型速查：
| 函数类型 | 常用函数 | 典型用途 |
|---|---|---|
| 字符串 | CONCAT UPPER LOWER TRIM SUBSTRING | 文本拼接、清洗、截取 |
| 数值 | ABS CEIL FLOOR POW RAND | 数值计算与随机值 |
| 日期 | CURDATE CURTIME NOW DATEDIFF | 时间展示与日期差计算 |
| 流程 | IF IFNULL CASE WHEN | 条件分支与空值兜底 |
函数明细速查表：
| 类型 | 函数 | 作用 | 示例 |
|---|---|---|---|
| 字符串 | CONCAT(s1,s2,...) | 字符串拼接 | CONCAT('你','好') |
| 字符串 | CONCAT_WS(sep,s1,s2,...) | 按分隔符拼接 | CONCAT_WS('-', 'A', 'B') |
| 字符串 | UPPER(str) | 转大写 | UPPER('abc') |
| 字符串 | LOWER(str) | 转小写 | LOWER('ABC') |
| 字符串 | TRIM(str) | 去首尾空格 | TRIM(' hi ') |
| 字符串 | SUBSTRING(str,pos,len) | 截取子串 | SUBSTRING('abcdef',2,3) |
| 数值 | ABS(x) | 绝对值 | ABS(-12) |
| 数值 | CEIL(x) | 向上取整 | CEIL(1.2) |
| 数值 | FLOOR(x) | 向下取整 | FLOOR(1.8) |
| 数值 | POW(x,y) | 幂运算 | POW(2,3) |
| 数值 | RAND() | 随机数 | RAND()*100 |
| 日期 | CURDATE() | 当前日期 | CURDATE() |
| 日期 | CURTIME() | 当前时间 | CURTIME() |
| 日期 | NOW() | 当前日期时间 | NOW() |
| 日期 | DATEDIFF(d1,d2) | 日期差（天） | DATEDIFF('2026-12-31',NOW()) |
| 流程 | IF(cond,a,b) | 条件分支 | IF(score>=60,'及格','不及格') |
| 流程 | IFNULL(v1,v2) | 空值兜底 | IFNULL(sex,0) |
| 流程 | CASE WHEN ... THEN ... END | 多分支判断 | 见下方示例 |
9.1 字符串函数
sql
SELECT CONCAT('你好', `uname`) FROM `t_user`;
SELECT CONCAT_WS(',', '你好', `uname`) FROM `t_user`;
SELECT UPPER(`uname`) FROM `t_user`;
SELECT LOWER(`uname`) FROM `t_user`;
SELECT TRIM(`uname`) FROM `t_user` WHERE `id` = 9;
SELECT SUBSTRING('abcdefg', 2, 2);9.2 数值函数
sql
SELECT ABS(-12);
SELECT CEIL(-11.2);
SELECT FLOOR(1.6);
SELECT POW(2,2);
SELECT RAND() * 100;9.3 日期函数
sql
SELECT CURDATE();
SELECT CURTIME();
SELECT NOW();
SELECT DATEDIFF('2026-12-31', NOW());9.4 流程函数
sql
SELECT `uname`, IF(`sex`=1,'男','女') AS sex_text FROM `t_user`;
SELECT `uname`, IFNULL(`sex`,0) AS safe_sex FROM `t_user`;
-- 搜索 CASE 表达式（根据条件判断）
SELECT
`uname`,
CASE
WHEN `age` <= 12 THEN '儿童'
WHEN `age` <= 18 THEN '少年'
WHEN `age` <= 40 THEN '青年'
ELSE '中老年'
END AS age_stage
FROM `t_user`;
-- 简单 CASE 表达式（根据值匹配）
SELECT
`uname`,
`sex`,
CASE `sex`
WHEN 1 THEN '男'
WHEN 0 THEN '女'
ELSE '未知'
END AS sex_text
FROM `t_user`;
-- CASE 查询在实际业务场景中的应用
-- 示例：根据分数等级计算评级
SELECT
`student_id`,
`student_name`,
`score`,
CASE
WHEN `score` >= 90 THEN '优秀'
WHEN `score` >= 80 THEN '良好'
WHEN `score` >= 60 THEN '及格'
ELSE '不及格'
END AS grade,
CASE
WHEN `score` >= 90 THEN 'A'
WHEN `score` >= 80 THEN 'B'
WHEN `score` >= 60 THEN 'C'
ELSE 'D'
END AS grade_level
FROM `student_score`;
-- 示例：根据订单金额计算折扣
SELECT
`order_id`,
`order_amount`,
CASE
WHEN `order_amount` >= 1000 THEN `order_amount` * 0.8
WHEN `order_amount` >= 500 THEN `order_amount` * 0.9
ELSE `order_amount`
END AS discounted_amount
FROM `orders`;10. DCL：用户与权限
10.1 创建用户与授权
sql
-- 创建用户
CREATE USER 'user1'@'localhost' IDENTIFIED BY '123';
CREATE USER 'user2'@'%' IDENTIFIED BY '123';
-- 授权
GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT
ON `test`.* TO 'user1'@'localhost';
GRANT ALL ON *.* TO 'user2'@'%';权限模型说明：
'user'@'localhost'：仅本机可登录。'user'@'%'：允许任意主机登录（生产环境谨慎使用）。ON 库名.*：权限限定在指定库；ON *.*表示全库全表。
10.2 撤销、查看、删除
sql
REVOKE ALL ON `test`.* FROM 'user1'@'localhost';
SHOW GRANTS FOR 'user1'@'localhost';
DROP USER 'user2'@'%';生产环境建议遵循 最小权限原则：给够用的权限，不给多余权限。
10.3 修改用户密码（补充）
sql
-- 修改普通用户密码（登录 MySQL 后执行）
SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('666666');bash
# 修改 root 密码（未登录 MySQL 时执行）
mysqladmin -uroot -p password root11. 事务（Transaction）
事务用于把多条 SQL 作为一个整体执行，保证要么全部成功，要么全部失败回滚。
事务四大特性（ACID）：
| 特性 | 含义 |
|---|---|
| 原子性（Atomicity） | 事务中的操作不可再分割，要么全成要么全败 |
| 一致性（Consistency） | 事务前后数据满足业务约束 |
| 隔离性（Isolation） | 并发事务之间相互隔离 |
| 持久性（Durability） | 事务提交后结果持久保存 |
sql
-- 手动控制事务
START TRANSACTION;
UPDATE `account` SET `balance` = `balance` - 100 WHERE `id` = 1;
UPDATE `account` SET `balance` = `balance` + 100 WHERE `id` = 2;
COMMIT; -- 成功提交
-- ROLLBACK; -- 失败回滚隔离级别（MySQL）：
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 并发性能 |
|---|---|---|---|---|
| READ UNCOMMITTED | 可能 | 可能 | 可能 | 高 |
| READ COMMITTED | 不会 | 可能 | 可能 | 较高 |
| REPEATABLE READ（MySQL 默认） | 不会 | 不会 | 可能（InnoDB 通过机制尽量避免） | 中 |
| SERIALIZABLE | 不会 | 不会 | 不会 | 低 |
sql
-- 查看/设置隔离级别
SELECT @@transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;12. 备份与还原
bash
# 备份
mysqldump -uroot -p mytest01 > D:/backup/mytest01.sql
# 还原（先创建目标数据库）
mysql -uroot -p mytest01 < D:/backup/mytest01.sql补充说明：
- 命令行还原前，通常需要先创建目标数据库。
- 图形化工具（如 SQLyog、Navicat）也支持导入导出，适合教学和小规模维护场景。
13. 设计规范与复习建议
13.1 数据库设计三范式（1NF/2NF/3NF）
| 范式 | 核心要求 | 典型问题 | 优化思路 |
|---|---|---|---|
| 1NF（第一范式） | 字段保持原子性，不可再拆分 | 一个字段塞多个含义（如“省市区+详细地址”） | 拆分为多个字段 |
| 2NF（第二范式） | 每行可由主键唯一标识，非主键完全依赖主键 | 复合主键下部分依赖 | 拆表或调整主键设计 |
| 3NF（第三范式） | 非主键字段不能传递依赖 | 冗余字段导致更新异常 | 将可独立主题拆到新表，用外键关联 |
快速记忆：
- 1NF：一列只存一个原子值
- 2NF：每张表必须有主键，且依赖完整
- 3NF：一张表尽量只描述一个主题
13.2 实战规范建议
- 一张表至少要有主键
- 能拆分的字段要拆分，保持原子性
- 一张表尽量只描述一个主题，减少冗余
- 先把单表查询练熟，再练多表查询
- DML 操作先查后改，避免误删误改