MySQL 进阶内容，涵盖事务、主外键、索引及高级查询技巧
一、事务（TCL）
1.1 什么是事务
一组 SQL 操作的逻辑单元，要么全部成功，要么全部失败。
1.2 ACID 特性
| 特性 | 说明 |
|---|
| 原子性 | 所有操作全部成功或全部失败 |
| 一致性 | 事务前后数据完整性一致 |
| 隔离性 | 多个事务并发互不影响 |
| 持久性 | 事务提交后永久保存 |
1.3 事务操作语句
| 语句 | 作用 |
|---|
begin | 开始事务 |
commit | 提交事务（永久保存） |
rollback | 回滚事务（撤销全部操作） |
savepoint 名称 | 设置保存点 |
rollback to 名称 | 回滚到指定保存点 |
多个窗口可多事务并发，但单窗口只能有一个事务。事务结束标志是rollback或commit。下一个事务开启会隐式提交上一个事务。
1.4 事务示例
-- 前提：只有 InnoDB 引擎支持事务createtableaccounts(idintunsignedprimarykeyauto_increment,namevarchar(20),balancedecimal(10,2))engine=InnoDBdefaultcharset=utf8mb4;insertintoaccountsvalues(1,'张三',1000),(2,'李四',0);-- 正常提交begin;updateaccountssetbalance=balance-500whereid=1;updateaccountssetbalance=balance+500whereid=2;commit;-- 回滚撤销begin;updateaccountssetbalance=balance-200whereid=1;updateaccountssetbalance=balance+200whereid=2;rollback;-- 保存点回滚begin;updateaccountssetbalance=500whereid=1;savepointsp1;updateaccountssetbalance=500whereid=2;rollbacktosp1;commit;
二、主键与外键
2.1 主键（primary key）
| 特点 | 说明 |
|---|
| 字段值必须唯一 | 不允许重复 |
| 字段值不可为空 | not null |
| 一张表仅一个主键 | 可单字段或组合字段 |
| 自动生成唯一索引 | 创建主键时自动附带 |
2.2 外键（foreign key）
引用另一张表的主键，用于建立表间关联。
| 特点 | 说明 |
|---|
| 字段值可以不唯一 | 允许重复 |
| 字段值可以为空 | 允许null |
| 一张表可有多个外键 |
| 自动生成普通索引 | 创建外键时自动附带 |
2.3 创建外键失败常见原因
- 关联的两个字段数据类型不匹配
- 子表已有存量数据违反外键约束
- 被引用的主表主键缺少配套索引
2.4 级联规则（on delete / on update）
| 规则 | 行为 |
|---|
on delete cascade | 主表删除，子表关联数据同步删除 |
on delete set null | 主表删除，子表外键置 null（需允许 null） |
| 默认（不写规则） | 子表有关联数据时，禁止删除主表 |
-- 主表createtableproducts_2(pidintunsignedprimarykeyauto_increment,pnamevarchar(40),pricedecimal(10,2));-- 子表：级联删除createtableorders_2(oidintunsignedprimarykeyauto_increment,pidintunsigned,buy_numint,foreignkeyfk_pid(pid)referencesproducts_2(pid)ondeletecascadeonupdatecascade);-- 子表：置空处理（pid 必须允许 null）createtableorders_3(oidintunsignedprimarykeyauto_increment,pidintunsignednull,buy_numint,foreignkeyfk_pid2(pid)referencesproducts_2(pid)ondeletesetnull);
三、索引
3.1 什么是索引
类似书籍目录，作用是加快数据查询速度。
| 有索引 | 无索引 |
|---|
| 直接定位数据，类似查目录 | 逐行全表扫描 |
| 查询效率大幅提升 | 数据量大时极慢 |
3.2 优缺点
| 优点 | 缺点 |
|---|
| 大幅提高查询速度 | 占用额外磁盘空间 |
| 减少磁盘 IO | 增删改需同步维护索引，降低写入性能 |
| 避免全表扫描 | 数据越大，索引占用越多 |
3.3 存储结构分类
| 类型 | 适用场景 |
|---|
| B+ 树索引 | 范围查询、in查询（默认） |
| 哈希索引 | 仅支持精准等值=查询 |
| 全文索引 | 文本模糊like检索 |
3.4 功能分类
| 类型 | 特点 | 语法 |
|---|
| 普通索引 | 允许重复、允许 null | create index idx_name on 表(字段); |
| 唯一索引 | 值必须唯一、允许 null | create unique index idx_name on 表(字段); |
| 主键索引 | 唯一 + 非空，一张表仅一个 | 创建主键自动生成 |
| 组合索引 | 多字段联合 | create index idx_a_b on 表(字段1,字段2); |
| 全文索引 | 长文本检索 | create fulltext index idx_text on 表(字段); |
-- 创建普通索引createindexidx_pnameonproducts(pname);-- 创建唯一索引createuniqueindexidx_priceonproducts(price);-- 创建组合索引createindexidx_name_stockonproducts(name,stock);-- 查看索引showindexfromproducts;showcreatetableproducts;-- 删除索引dropindexidx_pnameonproducts;
MySQL 不支持直接修改索引，需要先删除旧索引再重新创建新索引。
四、高级 DQL
4.1 条件判断（case when）
类似编程中的 if-else，根据条件生成新列：
selectname,price,casewhenprice<500then'低价'whenpricebetween500and3000then'中价'else'高价'endas'价格等级'fromproducts;
4.2 空值处理（ifnull）
selectifnull(name,'无名商品')as'商品名称',pricefromproducts;
4.3 字符串函数
selectconcat(name,' - ',city)as'顾客信息'fromcustomers;selectname,char_length(name)as'名称长度'fromproducts;
4.4 分页查询（limit offset）
-- limit 每页条数 offset 跳过条数select*fromproductsorderbypricedesclimit3offset0;-- 第 1 页select*fromproductsorderbypricedesclimit3offset3;-- 第 2 页
4.5 exists
-- 有下过单的顾客select*fromcustomersascwhereexists(select1fromordersasowhereo.customer_id=c.id);-- 从未被购买过的商品select*fromproductsaspwherenotexists(select1fromorder_itemsasoiwhereoi.product_id=p.id);
4.6 union 合并结果集
selectnameas'名称'fromcustomersunionselectnameas'名称'fromproducts;-- union all 保留重复（更快）selectnameas'名称'fromcustomersunionallselectnameas'名称'fromproducts;
4.7 窗口函数（window function）
在不改变行数的情况下对分组内数据排序、排名、累计：
-- row_number：为每行赋予唯一连续排名selectname,price,row_number()over(orderbypricedesc)as'价格排名'fromproducts;-- rank：相同值并列排名，会跳过后续序号（如 1,1,3）selectname,price,rank()over(orderbypricedesc)as'价格排名'fromproducts;-- dense_rank：相同值并列排名，不跳过后续序号（如 1,1,2）selectname,price,dense_rank()over(orderbypricedesc)as'价格排名'fromproducts;
五、总结
| 分类 | 核心操作 | 关键字/语法 |
|---|
| 事务 | 开启/提交/回滚 | begin/commit/rollback/savepoint |
| 事务 | ACID | 原子性、一致性、隔离性、持久性 |
| 外键 | 级联删除 | on delete cascade |
| 外键 | 置空处理 | on delete set null |
| 索引 | 普通/唯一/组合/全文 | create index/create unique index/create fulltext index |
| 索引 | 查看/删除 | show index from/drop index |
| 高级 DQL | 条件判断 | case when ... then ... else ... end |
| 高级 DQL | 空值处理 | ifnull() |
| 高级 DQL | 字符串函数 | concat()/char_length() |
| 高级 DQL | 分页 | limit ... offset ... |
| 高级 DQL | exists | exists/not exists |
| 高级 DQL | 合并结果集 | union/union all |
| 高级 DQL | 窗口函数 | row_number()/rank()/dense_rank()/over |
