MySQL 进阶内容涵盖事务、主外键、索引及高级查询技巧一、事务TCL1.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))engineInnoDBdefaultcharsetutf8mb4;insertintoaccountsvalues(1,张三,1000),(2,李四,0);-- 正常提交begin;updateaccountssetbalancebalance-500whereid1;updateaccountssetbalancebalance500whereid2;commit;-- 回滚撤销begin;updateaccountssetbalancebalance-200whereid1;updateaccountssetbalancebalance200whereid2;rollback;-- 保存点回滚begin;updateaccountssetbalance500whereid1;savepointsp1;updateaccountssetbalance500whereid2;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 必须允许 nullcreatetableorders_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 功能分类类型特点语法普通索引允许重复、允许 nullcreate index idx_name on 表(字段);唯一索引值必须唯一、允许 nullcreate 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 不支持直接修改索引需要先删除旧索引再重新创建新索引。四、高级 DQL4.1 条件判断case when类似编程中的 if-else根据条件生成新列selectname,price,casewhenprice500then低价whenpricebetween500and3000then中价else高价endas价格等级fromproducts;4.2 空值处理ifnullselectifnull(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_idc.id);-- 从未被购买过的商品select*fromproductsaspwherenotexists(select1fromorder_itemsasoiwhereoi.product_idp.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,3selectname,price,rank()over(orderbypricedesc)as价格排名fromproducts;-- dense_rank相同值并列排名不跳过后续序号如 1,1,2selectname,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 ...高级 DQLexistsexists/not exists高级 DQL合并结果集union/union all高级 DQL窗口函数row_number()/rank()/dense_rank()/over