拿到这个项目名字的时候我先笑了一下计算机等级考试、数据库完整性、进销存再加一个“东方仙盟练气期”这四样东西放在一起怎么看都像个梗。但真把这个练手项目做完我发现这个命名其实很科学。进销存是数据库设计题里出现频率最高的业务场景之一数据库完整性是三级数据库考试里绕不开的核心考点“练气期”则恰好对应了打好地基的学习阶段。这篇内容我就按我实际做完这套练习的路线来写从业务模型分析入手梳理三范式再把完整性约束、触发器、事务一层层落到建表SQL上最后整理我在练习过程中踩过的几个坑。正准备备考计算机等级考试三级数据库的人或者数据库学了半天只会写单表增删改查的初学者都可以照着走一遍。走完这一轮你再看数据库完整性的真题基本不会觉得抽象。1. 一个修仙名字背后的学习策略为什么偏偏用进销存练完整性第一次看到“进销存”这个词很多人容易把它想成“一个系统”好像很高大上。实际上它就是“采购入库—库存管理—销售出库”这一条业务线。商品、供应商、客户、采购单、销售单、订单明细大约六七张表就能搭起完整的骨架。恰恰是这种“看起来简单做起来全是细节”的业务模型最适合用来考试和练手。1.1 进销存的业务模型正好覆盖三级数据库的完整性考点三级数据库考试里数据完整性是绝对的重点它分成三块实体完整性、参照完整性、用户定义完整性。这三块在进销存里都能找到非常自然的落点。实体完整性主要指主键不能为空、不能重复。商品表有商品编号供应商表有供应商编号销售单有销售单号这就是主键的天然载体。参照完整性就是外键规则。比如销售明细表里的销售单号必须能对应到销售单表里真实存在的一行商品编号必须对应到商品表。这些关系在进销存业务里本来就有不需要硬编造。用户定义完整性就是各种业务规则约束。商品价格不能为负数库存数量不能小于零供应商信用等级只在1到5之间订单状态默认是“待审核”。这类规则在进销存里到处都是写起来不牵强。所以用进销存来练完整性本质上就是把考试大纲里的所有概念放到了一个你一眼能看懂业务含义的场景里。知识树是抽象的但“商品价格不能是负数”这句话任何做过生意的人都懂。1.2 练气期的知识边界先学什么、后学什么练气期在修仙体系里是第一重境界对应到数据库学习上就是最基础但最关键的阶段。我给自己划了一条清晰的分界线练气期只解决“数据能不能被正确存进去”的问题暂时不碰“数据能不能被快速查出来”的问题。这个定位很关键。很多人学数据库上来就钻索引优化、存储过程、分区表结果连主键外键都说不清楚。我的建议是先把下面这张表里的事情全部做完再去谈更高级的东西。学习阶段核心内容对应三级考试章节掌握标准练气期数据完整性、三范式、建表约束、触发器、事务数据库设计、关系数据库规范化理论、SQL与事务管理能独立设计进销存库并写出完整约束筑基期索引原理、查询计划、SQL调优数据库物理结构设计、性能调优能解释索引失效场景并优化慢查询金丹期并发控制、隔离级别、死锁检测事务与并发控制能分析事务并发异常并设置隔离级别元婴期备份恢复、数据归档、高可用架构数据库管理能制定备份策略并完成恢复演练练气期的目标就是在建表阶段把所有能通过结构保证的正确性都保证掉。你后面写多少业务代码都不需要反复去校验“这个商品到底存在不存在”因为数据库根本不让你插入一条引用不存在商品的记录。这就是“把正确性交给结构而不是交给业务代码”的思路。2. 设计阶段就要守规矩进销存模型与三范式拆解很多初学者拿到需求直接开写建表SQL我的建议是先停下来。进销存这种系统表结构没想清楚就动手后面改起来会非常痛苦。三级数据库考试里有一类经典大题给你一段文字描述的需求让你画出E-R图、再转换成关系模式、判断范式等级。这个流程就跟实际做项目完全一致。2.1 实体、属性、联系先画出一张能落地的E-R图E-R图就是画实体和联系用我自己的话说就是把业务里那些“名词”和“动词”先找出来。先看名词也就是实体。做进销存至少有这么几个实体商品、供应商、客户、采购单、销售单。再细化属性商品有名称、规格、单位、进价、零售价、当前库存供应商有名称、联系人、电话、地址、信用等级客户有名称、联系电话、会员等级。再看动词也就是实体之间的联系。供应商和采购单是“一对多”关系一个供应商可以多次供货每张采购单只属于一个供应商。采购单和商品是“多对多”关系因为一张采购单会采购多种商品一种商品也可能出现在多张采购单里。销售单和客户的关系与之类似客户与销售单一对多销售单与商品多对多。E-R图转换关系模式有几条固定规则每个实体转成一张表实体的属性就是表的字段一对多联系在“多”的那一端表里加上“一”那一端的主键作为外键多对多联系单独转成一张中间表中间表的主键通常是两方主键的组合。所以进销存里采购单和商品的多对多关系就需要一张“采购明细表”来承接它的主键可以是采购单号商品编号的组合键同时这两个字段又分别引用采购单表和商品表。销售那边同理要一张“销售明细表”。这个设计不是拍脑袋而是规范化理论推出来的必然结果。2.2 逐级检查范式从1NF到3NF进销存表是怎么一步步“瘦身”的范式是三级数据库考试的爱考内容但在实际设计里它讲的就是一件事表怎么设计才能不冗余、不更新出错。第一范式要求字段不可再分。反例是供应商表里写一个字段“联系人及电话”内容是“张三138xxxx李四139xxxx”这在业务里看似方便但数据库层面完全没法查询和保证一致性。正确的做法是联系人一个字段、电话一个字段。第二范式要求消除部分函数依赖。这句话初学者最难理解我直接拿采购明细表举例。假设采购明细表主键是采购单号商品编号的组合键其中“商品编号”决定“商品名称”那么商品名称就只依赖于联合主键的其中一部分这就是部分函数依赖。后果是什么同一商品被采购十次商品名称就被存了十遍一旦商品改名要么遗漏更新要么产生矛盾。正确的做法是商品名称只放在商品表里采购明细表里只保留商品编号。第三范式要求消除传递函数依赖。反例在销售单表里很常见。销售单表主键是销售单号它决定了客户编号客户编号又决定了客户等级于是销售单号间接决定了客户等级客户等级就属于传递依赖。同样客户等级只放客户表销售单表里通过客户编号去关联需要时再查出来。范式化的本质就是“每个非主属性只能依赖于主键而不能依赖其它非主属性”。你可以把一张表理解成一个主题的档案柜商品表只管商品属性客户表只管客户属性订单表只管订单自身信息。这样设计出来的表修改和删除才不会牵连出一堆隐藏的异常。2.3 关系模式落地标注主外键落实参照关系完成E-R图和范式检查后关系模式就可以直接列出来。这也是考试里经常让你做的一步。我给这个项目标注了一下主键和外键商品表商品编号商品名称规格单位进价零售价当前库存主键商品编号供应商表供应商编号供应商名称联系人联系电话地址信用等级主键供应商编号客户表客户编号客户名称联系电话会员等级主键客户编号采购单表采购单号供应商编号采购日期采购总金额状态主键采购单号外键供应商编号采购明细表采购单号商品编号采购数量采购单价明细金额主键采购单号商品编号外键采购单号、商品编号销售单表销售单号客户编号销售日期销售总金额状态主键销售单号外键客户编号销售明细表销售单号商品编号销售数量销售单价明细金额主键销售单号商品编号外键销售单号、商品编号把表结构落到纸面上之后我通常还会做一件事自检每一张表的外键字段是否真的在业务上存在“必须属于某个父表”的约束。比如销售明细的商品编号业务上不允许销售一个根本不存在的商品那么这个外键必须加上。通过这一轮检查后面写建表SQL就会非常快因为每一个字段都已经想清楚了。3. 建表SQL里的完整性实现把约束写进数据库设计图做得再漂亮最后还是要落到SQL建表语句上。三级数据库的SQL题最喜欢考的就是约束的写法。下面这几类约束每一条我都在这个进销存项目里实际验证过。3.1 实体完整性主键的三种写法与适用场景主键约束保证记录的每个实体能被唯一区分。对应到进销存商品编号、销售单号这类字段都是天然主键。主键在创建表时既可以在列定义后面直接写也可以在所有列定义结束后单独声明。后者在处理复合主键时是唯一选择。-- 单列主键直接在字段上声明 CREATE TABLE supplier ( supplier_id INT PRIMARY KEY, supplier_name VARCHAR(50) NOT NULL ); -- 复合主键需要在表定义末尾声明 CREATE TABLE purchase_item ( purchase_id INT, goods_id INT, quantity INT, PRIMARY KEY (purchase_id, goods_id) );还有一个实际开发里很常见的变体用UNIQUE加NOT NULL来模拟主键。比如商品表里商品名称加唯一约束且非空业务上也不允许两个商品重名那它就和主键效果一样。但在考试里设计关系模式时通常还是以主键为准UNIQUE只能算补充约束。项目里我会习惯把主键定义为无业务含义的自增编号同时把供应商名称加上UNIQUE约束防止重复创建同名供应商。3.2 参照完整性外键级联策略到底怎么选外键是三级数据库考试的另一个高频点。很多初学者以为外键就是“加一个FOREIGN KEY”但外键后面还有ON DELETE和ON UPDATE两个动作这才是真正体现业务判断能力的地方。拿销售单场景举个例子。销售单销售单删除时它的销售明细还有意义吗业务凭证被作废明细跟着删掉这是合理的所以可以用ON DELETE CASCADE。但商品被删除时销售明细里这个商品编号绝对不能跟着级联删除历史销售记录是凭证必须保留。这种情况下就应该用ON DELETE RESTRICT数据库会在有人试图删除被引用的商品时直接报错阻止。CREATE TABLE sale_item ( sale_id INT NOT NULL, goods_id INT NOT NULL, quantity INT NOT NULL CHECK (quantity 0), unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price 0), PRIMARY KEY (sale_id, goods_id), CONSTRAINT fk_sale_item_sale FOREIGN KEY (sale_id) REFERENCES sale_order(sale_id) ON DELETE CASCADE, CONSTRAINT fk_sale_item_goods FOREIGN KEY (goods_id) REFERENCES goods(goods_id) ON DELETE RESTRICT );同一张表上两个外键使用不同的级联策略这在进销存里完全是合理的。设计时遵循一个原则业务凭证类的明细跟着主单走主数据类的引用绝不让它级联删除。供应商能否被删除要看有没有历史采购单引用它正常情况下供应商不会物理删除而是用一个状态字段标为“停用”。3.3 用户定义完整性CHECK、UNIQUE、DEFAULT的组合应用用户定义完整性是考纲里经常提到的术语落到SQL就是CHECK、UNIQUE、DEFAULT、NOT NULL这些约束。在这套进销存项目里这几个约束我用得特别频繁供应商信用等级限制在1到5之间销售明细里销售数量必须大于0商品零售价必须大于等于进价订单状态默认是“待审核”客户名称非空且唯一。CREATE TABLE supplier ( supplier_id INT AUTO_INCREMENT PRIMARY KEY, supplier_name VARCHAR(50) NOT NULL UNIQUE, contact_person VARCHAR(20), phone VARCHAR(20), credit_rating TINYINT NOT NULL DEFAULT 3, CONSTRAINT chk_sup_credit CHECK (credit_rating BETWEEN 1 AND 5) );这里有个容易让人忽略的细节CHECK是MySQL 8.0.16版本之后才真正执行的约束之前的版本虽然能写进SQL但会被直接忽略。后面我会在常见问题里专门讲这个坑。在考试这种标准SQL环境里CHECK按照规范写就可以不需要考虑版本差异。4. 触发器与事务程序逻辑管不到的完整性建表约束能挡住大部分明显错误但有些业务完整性规则用普通约束表达不出来。最典型的就是“销售出库后库存自动扣减”和“扣库存失败时销售单必须整体回滚”。这就要请触发器与事务出场了。4.1 库存自动扣减一个销售触发器怎么写进销存最核心的业务动作就是销售出库。用户完成一笔销售系统往销售明细表里插入一条记录同时商品表的当前库存要自动减少对应数量。这两个动作不能分开做否则就会出现库存对不上账的故障。在插入销售明细表之后自动更新库存表用触发器是最直观的方案DELIMITER // CREATE TRIGGER trg_sale_item_after_insert AFTER INSERT ON sale_item FOR EACH ROW BEGIN IF NEW.quantity (SELECT stock_quantity FROM goods WHERE goods_id NEW.goods_id) THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 库存不足无法销售; END IF; UPDATE goods SET stock_quantity stock_quantity - NEW.quantity WHERE goods_id NEW.goods_id; END// DELIMITER ;AFTER INSERT指的是“在销售明细插入成功后”执行触发器逻辑FOR EACH ROW表示每一行明细都会触发一次。NEW.quantity就是本次插入的那一行的销售数量。这里我先判断库存是否足够不够就直接抛异常让整个插入操作失败。这其实是用触发器实现了一个普通CHECK做不到的“手动完整性约束”因为库存是否充足依赖于另一张表里的实时数据单独的列约束管不了这种跨表规则。4.2 事务ACID在进销存里如何体现触发器解决了“卖完要扣库存”但还有一个问题一个销售动作包含生成销售单、插入多条销售明细、扣减库存如果最后一步失败前两步已经写了怎么办答案就是事务。ACID四个性质里原子性和一致性在这套进销存里体现得最直接。原子性要求“要么全做要么全不做”一致性要求事务结束后数据库从一个正确状态变成另一个正确状态。下订单和扣库存必须在一个事务里执行START TRANSACTION; INSERT INTO sale_order (sale_id, customer_id, sale_date, status) VALUES (1001, 88, 2025-06-01, PAID); INSERT INTO sale_item (sale_id, goods_id, quantity, unit_price) VALUES (1001, 3001, 2, 199.00); -- 更新库存注意此时库存表的stock_quantity已经通过触发器更新过 -- 这里更多的是体现事务回滚的语义如果后续逻辑失败前面全部撤销 COMMIT;如果中间任何一条SQL失败直接执行ROLLBACK前面做的所有插入全部撤销数据库回到操作前的一致状态。不管触发器、约束还是存储过程只要在一个事务里都共享同一个回滚范围。这是我觉得练气期项目里最值得反复实验的一环因为考试考ACID概念很容易但真正理解“原子性是靠回滚日志实现的”这件事必须亲手写一个会失败的事务才能体会。5. 实战验证把完整性的“地雷”亲手踩一遍背概念和看报错完全是两码事。我把三类完整性破坏SQL挨个试了一遍把报错信息记下来之后再看考试题目心里踏实多了。下面这三个冲突场景是整个练气期阶段最值得做的实验。5.1 三个必试的冲突SQL报错信息就是考纲第一个实体完整性冲突。往商品表里插入两条相同商品编号的记录第二条会直接被拒绝MySQL报ERROR 1062 Duplicate entry。报错信息里会提示哪个键冲突。这个实验会让你真正记住主键的唯一性不是靠程序判断的而是数据库底层索引保证的。第二个参照完整性冲突。往销售明细表里插入一条商品编号为99999的记录商品表里根本没有这个编号。MySQL报ERROR 1452意思是外键约束失败。报错信息会直接告诉你“不能添加或更新子行因为外键约束失败了”。这条报错我建议手动复现一次因为它能解释外键存在的价值。第三个用户定义完整性冲突。往供应商表里插入一条信用等级为9的记录CHECK约束直接拒绝。如果版本较老这条实验可能不会报错这就是一个很好的理解起点版本差异如何影响约束行为。我把这三类报错整理成了一张速查表复习的时候扫一眼就知道对应哪个知识点冲突类型插入的内容数据库报错关键字对应完整性分类主键重复已存在的商品编号Duplicate entry实体完整性外键无效不存在的商品编号foreign key constraint fails参照完整性CHECK越界信用等级9check constraint violated用户定义完整性5.2 对照真题看项目三级数据库的题目长什么样练完项目之后再翻三级数据库真题很多题目都能一眼对上号。选择题里“数据库完整性分为哪三类”填空题里“判断关系模式属于第几范式”设计题里“根据业务需求画出E-R图并转换为关系模式”最后还可能让你“写出实现库存更新的触发器”。这些题看起来分散其实全都能在这套进销存项目里找到实践原型。我备考的时候做了一件事把近几年的真题大题场景换成进销存或者同类业务尝试自己重新设计一遍。如果我不看答案能独立画出E-R图、写出关系模式、标出主外键那这道题基本就稳了。一个项目练熟之后考场上遇到“图书馆借阅系统”“学生选课系统”这类题目你会发现套路完全一样找实体找联系转换关系模式检查范式。5.3 “练气期”三天的修炼路线这套进销存项目如果集中精力做三天可以完成一个完整的练气期修炼。第一天做需求分析和E-R图把实体、联系、属性全部交代清楚然后做一遍范式检查。第二天把关系模式转成建表SQL写全主键、外键、CHECK、UNIQUE并且亲手把上一节里那三个破坏性SQL执行一遍。第三天学习触发器和事务把库存自动扣减的触发器写出来模拟一次“库存不足”的失败回滚最后做题检验成果。每天结束前我会写一段一句总结。第一天的总结是“实体找全了联系理清了关系模式才能在纸上先成立”。第二天的总结是“约束不是写完就算完要亲手验证约束真的在起作用”。第三天的总结是“触发器和事务一配合业务正确性就不靠代码碰运气了”。这个方法比刷十套题管用因为每句话都对应着我亲手做过的操作。6. 常见问题排查这些坑不踩一遍等于白练练气期项目做完踩的坑比我看文档一个月学到的都多。这里挑几个典型的记下来给后面的人省点时间。6.1 外键创建失败先检查三件事外键建不上的问题90%出在三个硬性条件上。第一个表引擎必须是InnoDBMyISAM不支持外键约束。第二个外键字段和被引用字段的数据类型需要完全一致INT对应INTVARCHAR长度也要一样。有时候你感觉两个字段都是数字但一个设成INT一个设成BIGINT外键就会报错。第三个被引用的列必须是有索引的列通常就是主键或UNIQUE键。我在项目里曾把供应商编号设成INT UNSIGNED销售单表里引用时用了INT结果反复报错。排查了十分钟才发现是无符号和有符号的问题。这类问题在考场上不会出现但实际项目里非常隐蔽。6.2 MySQL里CHECK约束“形同虚设”的版本问题这个坑特别重要。MySQL 8.0.16之前写进去的CHECK约束会被数据库解析并记录但不会真正对插入更新数据进行拦截。所以你在5.7版本上做实验能不能验证成功。生产环境里如果确实要限制字段范围最好用触发器或者应用层校验兜底。考试是按标准SQL出题的所以遇到CHECK约束的题就正常按规范作答但你在自己的电脑上跑实验结果却可能不一样心里要有数。我也是因为这个现象才去查了版本文档彻底理解了“SQL标准”和“数据库实现”之间的差异。6.3 级联删除把历史数据也删了ON DELETE CASCADE用起来很爽但风险很大。我在实验时用一个DELETE语句删掉了某张销售单结果它下面所有销售明细全被级联删除了。一个还开着的门店订单和明细都是凭证物理删除会让对账、审计全部失去依据。进销存里的核心单据表我最后基本都不用物理DELETE而是加一个status字段用“作废”状态代替删除。这样数据永远留存在库里查询时过滤状态即可。外键级联策略只用于“主单删除时清理临时明细”这种场景涉及正式凭证的都要保守使用。6.4 触发器递归和自己给自己挖坑触发器逻辑如果写得不好很容易挖坑。举例来说销售明细的触发器更新商品库存表如果商品库存表上还有一个触发器反向去更新销售明细表两个触发器就会互相触发形成无限循环数据库会直接报错。另一个问题是MySQL的触发器内部并不能读取或操作当前正在触发它的那张表有时候你会收到ERROR 1442提示“无法更新表因为触发器已经被另一条语句调用”。写触发器有个经验法则保持简单一个触发器只做一件事并且明确知道它会影响哪些表涉及多步业务操作时优先考虑用事务和存储过程组合而不是把所有逻辑都堆到触发器里。做完这个练气期项目我最大的收获不是记住了一堆SQL语法而是彻底理解了“数据一致性要靠结构来保证”这句话。考试里那些关于完整性、范式、约束的选择题和设计题你背十遍不如亲手把一张销售单插进一张没有外键的表里再看着库存对不上账来得震撼。我给你的建议是把商品表叫“t_spirit_herb”把供应商表叫“t_sect”用自己喜欢的方式把枯燥练习包装一下然后认认真真把每一类约束、每一个报错都亲手触发一次。只要这一轮练扎实了后面学索引、学并发控制、学数据库调优都不会再感到根基不稳。 用进销存项目搞定数据库完整性：从三范式到触发器实战
拿到这个项目名字的时候我先笑了一下计算机等级考试、数据库完整性、进销存再加一个“东方仙盟练气期”这四样东西放在一起怎么看都像个梗。但真把这个练手项目做完我发现这个命名其实很科学。进销存是数据库设计题里出现频率最高的业务场景之一数据库完整性是三级数据库考试里绕不开的核心考点“练气期”则恰好对应了打好地基的学习阶段。这篇内容我就按我实际做完这套练习的路线来写从业务模型分析入手梳理三范式再把完整性约束、触发器、事务一层层落到建表SQL上最后整理我在练习过程中踩过的几个坑。正准备备考计算机等级考试三级数据库的人或者数据库学了半天只会写单表增删改查的初学者都可以照着走一遍。走完这一轮你再看数据库完整性的真题基本不会觉得抽象。1. 一个修仙名字背后的学习策略为什么偏偏用进销存练完整性第一次看到“进销存”这个词很多人容易把它想成“一个系统”好像很高大上。实际上它就是“采购入库—库存管理—销售出库”这一条业务线。商品、供应商、客户、采购单、销售单、订单明细大约六七张表就能搭起完整的骨架。恰恰是这种“看起来简单做起来全是细节”的业务模型最适合用来考试和练手。1.1 进销存的业务模型正好覆盖三级数据库的完整性考点三级数据库考试里数据完整性是绝对的重点它分成三块实体完整性、参照完整性、用户定义完整性。这三块在进销存里都能找到非常自然的落点。实体完整性主要指主键不能为空、不能重复。商品表有商品编号供应商表有供应商编号销售单有销售单号这就是主键的天然载体。参照完整性就是外键规则。比如销售明细表里的销售单号必须能对应到销售单表里真实存在的一行商品编号必须对应到商品表。这些关系在进销存业务里本来就有不需要硬编造。用户定义完整性就是各种业务规则约束。商品价格不能为负数库存数量不能小于零供应商信用等级只在1到5之间订单状态默认是“待审核”。这类规则在进销存里到处都是写起来不牵强。所以用进销存来练完整性本质上就是把考试大纲里的所有概念放到了一个你一眼能看懂业务含义的场景里。知识树是抽象的但“商品价格不能是负数”这句话任何做过生意的人都懂。1.2 练气期的知识边界先学什么、后学什么练气期在修仙体系里是第一重境界对应到数据库学习上就是最基础但最关键的阶段。我给自己划了一条清晰的分界线练气期只解决“数据能不能被正确存进去”的问题暂时不碰“数据能不能被快速查出来”的问题。这个定位很关键。很多人学数据库上来就钻索引优化、存储过程、分区表结果连主键外键都说不清楚。我的建议是先把下面这张表里的事情全部做完再去谈更高级的东西。学习阶段核心内容对应三级考试章节掌握标准练气期数据完整性、三范式、建表约束、触发器、事务数据库设计、关系数据库规范化理论、SQL与事务管理能独立设计进销存库并写出完整约束筑基期索引原理、查询计划、SQL调优数据库物理结构设计、性能调优能解释索引失效场景并优化慢查询金丹期并发控制、隔离级别、死锁检测事务与并发控制能分析事务并发异常并设置隔离级别元婴期备份恢复、数据归档、高可用架构数据库管理能制定备份策略并完成恢复演练练气期的目标就是在建表阶段把所有能通过结构保证的正确性都保证掉。你后面写多少业务代码都不需要反复去校验“这个商品到底存在不存在”因为数据库根本不让你插入一条引用不存在商品的记录。这就是“把正确性交给结构而不是交给业务代码”的思路。2. 设计阶段就要守规矩进销存模型与三范式拆解很多初学者拿到需求直接开写建表SQL我的建议是先停下来。进销存这种系统表结构没想清楚就动手后面改起来会非常痛苦。三级数据库考试里有一类经典大题给你一段文字描述的需求让你画出E-R图、再转换成关系模式、判断范式等级。这个流程就跟实际做项目完全一致。2.1 实体、属性、联系先画出一张能落地的E-R图E-R图就是画实体和联系用我自己的话说就是把业务里那些“名词”和“动词”先找出来。先看名词也就是实体。做进销存至少有这么几个实体商品、供应商、客户、采购单、销售单。再细化属性商品有名称、规格、单位、进价、零售价、当前库存供应商有名称、联系人、电话、地址、信用等级客户有名称、联系电话、会员等级。再看动词也就是实体之间的联系。供应商和采购单是“一对多”关系一个供应商可以多次供货每张采购单只属于一个供应商。采购单和商品是“多对多”关系因为一张采购单会采购多种商品一种商品也可能出现在多张采购单里。销售单和客户的关系与之类似客户与销售单一对多销售单与商品多对多。E-R图转换关系模式有几条固定规则每个实体转成一张表实体的属性就是表的字段一对多联系在“多”的那一端表里加上“一”那一端的主键作为外键多对多联系单独转成一张中间表中间表的主键通常是两方主键的组合。所以进销存里采购单和商品的多对多关系就需要一张“采购明细表”来承接它的主键可以是采购单号商品编号的组合键同时这两个字段又分别引用采购单表和商品表。销售那边同理要一张“销售明细表”。这个设计不是拍脑袋而是规范化理论推出来的必然结果。2.2 逐级检查范式从1NF到3NF进销存表是怎么一步步“瘦身”的范式是三级数据库考试的爱考内容但在实际设计里它讲的就是一件事表怎么设计才能不冗余、不更新出错。第一范式要求字段不可再分。反例是供应商表里写一个字段“联系人及电话”内容是“张三138xxxx李四139xxxx”这在业务里看似方便但数据库层面完全没法查询和保证一致性。正确的做法是联系人一个字段、电话一个字段。第二范式要求消除部分函数依赖。这句话初学者最难理解我直接拿采购明细表举例。假设采购明细表主键是采购单号商品编号的组合键其中“商品编号”决定“商品名称”那么商品名称就只依赖于联合主键的其中一部分这就是部分函数依赖。后果是什么同一商品被采购十次商品名称就被存了十遍一旦商品改名要么遗漏更新要么产生矛盾。正确的做法是商品名称只放在商品表里采购明细表里只保留商品编号。第三范式要求消除传递函数依赖。反例在销售单表里很常见。销售单表主键是销售单号它决定了客户编号客户编号又决定了客户等级于是销售单号间接决定了客户等级客户等级就属于传递依赖。同样客户等级只放客户表销售单表里通过客户编号去关联需要时再查出来。范式化的本质就是“每个非主属性只能依赖于主键而不能依赖其它非主属性”。你可以把一张表理解成一个主题的档案柜商品表只管商品属性客户表只管客户属性订单表只管订单自身信息。这样设计出来的表修改和删除才不会牵连出一堆隐藏的异常。2.3 关系模式落地标注主外键落实参照关系完成E-R图和范式检查后关系模式就可以直接列出来。这也是考试里经常让你做的一步。我给这个项目标注了一下主键和外键商品表商品编号商品名称规格单位进价零售价当前库存主键商品编号供应商表供应商编号供应商名称联系人联系电话地址信用等级主键供应商编号客户表客户编号客户名称联系电话会员等级主键客户编号采购单表采购单号供应商编号采购日期采购总金额状态主键采购单号外键供应商编号采购明细表采购单号商品编号采购数量采购单价明细金额主键采购单号商品编号外键采购单号、商品编号销售单表销售单号客户编号销售日期销售总金额状态主键销售单号外键客户编号销售明细表销售单号商品编号销售数量销售单价明细金额主键销售单号商品编号外键销售单号、商品编号把表结构落到纸面上之后我通常还会做一件事自检每一张表的外键字段是否真的在业务上存在“必须属于某个父表”的约束。比如销售明细的商品编号业务上不允许销售一个根本不存在的商品那么这个外键必须加上。通过这一轮检查后面写建表SQL就会非常快因为每一个字段都已经想清楚了。3. 建表SQL里的完整性实现把约束写进数据库设计图做得再漂亮最后还是要落到SQL建表语句上。三级数据库的SQL题最喜欢考的就是约束的写法。下面这几类约束每一条我都在这个进销存项目里实际验证过。3.1 实体完整性主键的三种写法与适用场景主键约束保证记录的每个实体能被唯一区分。对应到进销存商品编号、销售单号这类字段都是天然主键。主键在创建表时既可以在列定义后面直接写也可以在所有列定义结束后单独声明。后者在处理复合主键时是唯一选择。-- 单列主键直接在字段上声明 CREATE TABLE supplier ( supplier_id INT PRIMARY KEY, supplier_name VARCHAR(50) NOT NULL ); -- 复合主键需要在表定义末尾声明 CREATE TABLE purchase_item ( purchase_id INT, goods_id INT, quantity INT, PRIMARY KEY (purchase_id, goods_id) );还有一个实际开发里很常见的变体用UNIQUE加NOT NULL来模拟主键。比如商品表里商品名称加唯一约束且非空业务上也不允许两个商品重名那它就和主键效果一样。但在考试里设计关系模式时通常还是以主键为准UNIQUE只能算补充约束。项目里我会习惯把主键定义为无业务含义的自增编号同时把供应商名称加上UNIQUE约束防止重复创建同名供应商。3.2 参照完整性外键级联策略到底怎么选外键是三级数据库考试的另一个高频点。很多初学者以为外键就是“加一个FOREIGN KEY”但外键后面还有ON DELETE和ON UPDATE两个动作这才是真正体现业务判断能力的地方。拿销售单场景举个例子。销售单销售单删除时它的销售明细还有意义吗业务凭证被作废明细跟着删掉这是合理的所以可以用ON DELETE CASCADE。但商品被删除时销售明细里这个商品编号绝对不能跟着级联删除历史销售记录是凭证必须保留。这种情况下就应该用ON DELETE RESTRICT数据库会在有人试图删除被引用的商品时直接报错阻止。CREATE TABLE sale_item ( sale_id INT NOT NULL, goods_id INT NOT NULL, quantity INT NOT NULL CHECK (quantity 0), unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price 0), PRIMARY KEY (sale_id, goods_id), CONSTRAINT fk_sale_item_sale FOREIGN KEY (sale_id) REFERENCES sale_order(sale_id) ON DELETE CASCADE, CONSTRAINT fk_sale_item_goods FOREIGN KEY (goods_id) REFERENCES goods(goods_id) ON DELETE RESTRICT );同一张表上两个外键使用不同的级联策略这在进销存里完全是合理的。设计时遵循一个原则业务凭证类的明细跟着主单走主数据类的引用绝不让它级联删除。供应商能否被删除要看有没有历史采购单引用它正常情况下供应商不会物理删除而是用一个状态字段标为“停用”。3.3 用户定义完整性CHECK、UNIQUE、DEFAULT的组合应用用户定义完整性是考纲里经常提到的术语落到SQL就是CHECK、UNIQUE、DEFAULT、NOT NULL这些约束。在这套进销存项目里这几个约束我用得特别频繁供应商信用等级限制在1到5之间销售明细里销售数量必须大于0商品零售价必须大于等于进价订单状态默认是“待审核”客户名称非空且唯一。CREATE TABLE supplier ( supplier_id INT AUTO_INCREMENT PRIMARY KEY, supplier_name VARCHAR(50) NOT NULL UNIQUE, contact_person VARCHAR(20), phone VARCHAR(20), credit_rating TINYINT NOT NULL DEFAULT 3, CONSTRAINT chk_sup_credit CHECK (credit_rating BETWEEN 1 AND 5) );这里有个容易让人忽略的细节CHECK是MySQL 8.0.16版本之后才真正执行的约束之前的版本虽然能写进SQL但会被直接忽略。后面我会在常见问题里专门讲这个坑。在考试这种标准SQL环境里CHECK按照规范写就可以不需要考虑版本差异。4. 触发器与事务程序逻辑管不到的完整性建表约束能挡住大部分明显错误但有些业务完整性规则用普通约束表达不出来。最典型的就是“销售出库后库存自动扣减”和“扣库存失败时销售单必须整体回滚”。这就要请触发器与事务出场了。4.1 库存自动扣减一个销售触发器怎么写进销存最核心的业务动作就是销售出库。用户完成一笔销售系统往销售明细表里插入一条记录同时商品表的当前库存要自动减少对应数量。这两个动作不能分开做否则就会出现库存对不上账的故障。在插入销售明细表之后自动更新库存表用触发器是最直观的方案DELIMITER // CREATE TRIGGER trg_sale_item_after_insert AFTER INSERT ON sale_item FOR EACH ROW BEGIN IF NEW.quantity (SELECT stock_quantity FROM goods WHERE goods_id NEW.goods_id) THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 库存不足无法销售; END IF; UPDATE goods SET stock_quantity stock_quantity - NEW.quantity WHERE goods_id NEW.goods_id; END// DELIMITER ;AFTER INSERT指的是“在销售明细插入成功后”执行触发器逻辑FOR EACH ROW表示每一行明细都会触发一次。NEW.quantity就是本次插入的那一行的销售数量。这里我先判断库存是否足够不够就直接抛异常让整个插入操作失败。这其实是用触发器实现了一个普通CHECK做不到的“手动完整性约束”因为库存是否充足依赖于另一张表里的实时数据单独的列约束管不了这种跨表规则。4.2 事务ACID在进销存里如何体现触发器解决了“卖完要扣库存”但还有一个问题一个销售动作包含生成销售单、插入多条销售明细、扣减库存如果最后一步失败前两步已经写了怎么办答案就是事务。ACID四个性质里原子性和一致性在这套进销存里体现得最直接。原子性要求“要么全做要么全不做”一致性要求事务结束后数据库从一个正确状态变成另一个正确状态。下订单和扣库存必须在一个事务里执行START TRANSACTION; INSERT INTO sale_order (sale_id, customer_id, sale_date, status) VALUES (1001, 88, 2025-06-01, PAID); INSERT INTO sale_item (sale_id, goods_id, quantity, unit_price) VALUES (1001, 3001, 2, 199.00); -- 更新库存注意此时库存表的stock_quantity已经通过触发器更新过 -- 这里更多的是体现事务回滚的语义如果后续逻辑失败前面全部撤销 COMMIT;如果中间任何一条SQL失败直接执行ROLLBACK前面做的所有插入全部撤销数据库回到操作前的一致状态。不管触发器、约束还是存储过程只要在一个事务里都共享同一个回滚范围。这是我觉得练气期项目里最值得反复实验的一环因为考试考ACID概念很容易但真正理解“原子性是靠回滚日志实现的”这件事必须亲手写一个会失败的事务才能体会。5. 实战验证把完整性的“地雷”亲手踩一遍背概念和看报错完全是两码事。我把三类完整性破坏SQL挨个试了一遍把报错信息记下来之后再看考试题目心里踏实多了。下面这三个冲突场景是整个练气期阶段最值得做的实验。5.1 三个必试的冲突SQL报错信息就是考纲第一个实体完整性冲突。往商品表里插入两条相同商品编号的记录第二条会直接被拒绝MySQL报ERROR 1062 Duplicate entry。报错信息里会提示哪个键冲突。这个实验会让你真正记住主键的唯一性不是靠程序判断的而是数据库底层索引保证的。第二个参照完整性冲突。往销售明细表里插入一条商品编号为99999的记录商品表里根本没有这个编号。MySQL报ERROR 1452意思是外键约束失败。报错信息会直接告诉你“不能添加或更新子行因为外键约束失败了”。这条报错我建议手动复现一次因为它能解释外键存在的价值。第三个用户定义完整性冲突。往供应商表里插入一条信用等级为9的记录CHECK约束直接拒绝。如果版本较老这条实验可能不会报错这就是一个很好的理解起点版本差异如何影响约束行为。我把这三类报错整理成了一张速查表复习的时候扫一眼就知道对应哪个知识点冲突类型插入的内容数据库报错关键字对应完整性分类主键重复已存在的商品编号Duplicate entry实体完整性外键无效不存在的商品编号foreign key constraint fails参照完整性CHECK越界信用等级9check constraint violated用户定义完整性5.2 对照真题看项目三级数据库的题目长什么样练完项目之后再翻三级数据库真题很多题目都能一眼对上号。选择题里“数据库完整性分为哪三类”填空题里“判断关系模式属于第几范式”设计题里“根据业务需求画出E-R图并转换为关系模式”最后还可能让你“写出实现库存更新的触发器”。这些题看起来分散其实全都能在这套进销存项目里找到实践原型。我备考的时候做了一件事把近几年的真题大题场景换成进销存或者同类业务尝试自己重新设计一遍。如果我不看答案能独立画出E-R图、写出关系模式、标出主外键那这道题基本就稳了。一个项目练熟之后考场上遇到“图书馆借阅系统”“学生选课系统”这类题目你会发现套路完全一样找实体找联系转换关系模式检查范式。5.3 “练气期”三天的修炼路线这套进销存项目如果集中精力做三天可以完成一个完整的练气期修炼。第一天做需求分析和E-R图把实体、联系、属性全部交代清楚然后做一遍范式检查。第二天把关系模式转成建表SQL写全主键、外键、CHECK、UNIQUE并且亲手把上一节里那三个破坏性SQL执行一遍。第三天学习触发器和事务把库存自动扣减的触发器写出来模拟一次“库存不足”的失败回滚最后做题检验成果。每天结束前我会写一段一句总结。第一天的总结是“实体找全了联系理清了关系模式才能在纸上先成立”。第二天的总结是“约束不是写完就算完要亲手验证约束真的在起作用”。第三天的总结是“触发器和事务一配合业务正确性就不靠代码碰运气了”。这个方法比刷十套题管用因为每句话都对应着我亲手做过的操作。6. 常见问题排查这些坑不踩一遍等于白练练气期项目做完踩的坑比我看文档一个月学到的都多。这里挑几个典型的记下来给后面的人省点时间。6.1 外键创建失败先检查三件事外键建不上的问题90%出在三个硬性条件上。第一个表引擎必须是InnoDBMyISAM不支持外键约束。第二个外键字段和被引用字段的数据类型需要完全一致INT对应INTVARCHAR长度也要一样。有时候你感觉两个字段都是数字但一个设成INT一个设成BIGINT外键就会报错。第三个被引用的列必须是有索引的列通常就是主键或UNIQUE键。我在项目里曾把供应商编号设成INT UNSIGNED销售单表里引用时用了INT结果反复报错。排查了十分钟才发现是无符号和有符号的问题。这类问题在考场上不会出现但实际项目里非常隐蔽。6.2 MySQL里CHECK约束“形同虚设”的版本问题这个坑特别重要。MySQL 8.0.16之前写进去的CHECK约束会被数据库解析并记录但不会真正对插入更新数据进行拦截。所以你在5.7版本上做实验能不能验证成功。生产环境里如果确实要限制字段范围最好用触发器或者应用层校验兜底。考试是按标准SQL出题的所以遇到CHECK约束的题就正常按规范作答但你在自己的电脑上跑实验结果却可能不一样心里要有数。我也是因为这个现象才去查了版本文档彻底理解了“SQL标准”和“数据库实现”之间的差异。6.3 级联删除把历史数据也删了ON DELETE CASCADE用起来很爽但风险很大。我在实验时用一个DELETE语句删掉了某张销售单结果它下面所有销售明细全被级联删除了。一个还开着的门店订单和明细都是凭证物理删除会让对账、审计全部失去依据。进销存里的核心单据表我最后基本都不用物理DELETE而是加一个status字段用“作废”状态代替删除。这样数据永远留存在库里查询时过滤状态即可。外键级联策略只用于“主单删除时清理临时明细”这种场景涉及正式凭证的都要保守使用。6.4 触发器递归和自己给自己挖坑触发器逻辑如果写得不好很容易挖坑。举例来说销售明细的触发器更新商品库存表如果商品库存表上还有一个触发器反向去更新销售明细表两个触发器就会互相触发形成无限循环数据库会直接报错。另一个问题是MySQL的触发器内部并不能读取或操作当前正在触发它的那张表有时候你会收到ERROR 1442提示“无法更新表因为触发器已经被另一条语句调用”。写触发器有个经验法则保持简单一个触发器只做一件事并且明确知道它会影响哪些表涉及多步业务操作时优先考虑用事务和存储过程组合而不是把所有逻辑都堆到触发器里。做完这个练气期项目我最大的收获不是记住了一堆SQL语法而是彻底理解了“数据一致性要靠结构来保证”这句话。考试里那些关于完整性、范式、约束的选择题和设计题你背十遍不如亲手把一张销售单插进一张没有外键的表里再看着库存对不上账来得震撼。我给你的建议是把商品表叫“t_spirit_herb”把供应商表叫“t_sect”用自己喜欢的方式把枯燥练习包装一下然后认认真真把每一类约束、每一个报错都亲手触发一次。只要这一轮练扎实了后面学索引、学并发控制、学数据库调优都不会再感到根基不稳。