MySQL数据库课外拓展实战：玩具销售系统从E-R图到二进制日志恢复

简介面向MySQL初学者与数据库课程实践者这份课外拓展PDF以“网络玩具销售系统”为完整贯穿案例讲解从需求分析、实体提取、E-R图设计到关系模式转换与第三范式规范化的全流程。内容覆盖客户、订单、玩具、品牌、类别、购物车等14类核心实体完整列示了客户、订单、玩具、购物车等表的字段名、键值和约束说明并给出订单总价、玩具品牌等典型查询的SQL语句及视图定义便于对照练习和直接复用。资源为单份PDF文件大小仅649KB方便直接下载阅读。目前已有2363人学习。通过这份PDF可掌握E-R图建模、外键关联、消除传递依赖、创建索引加速查询及视图封装复杂查询等数据库核心技能同时可参考其中的索引优化思路完成性能调优练习对理解MySQL原理在实际业务中的应用很有帮助。1. 这份 MySQL 课外拓展 PDF实际是一套 GlobalToys 玩具销售系统的完整课设MySQL 数据库的课外拓展资源我拆过不少大多数是习题和理论总结翻两页就放一边了。这份「MySQL数据库原理及应用(第2版)(微课版)-课外拓展.pdf」不太一样整本内容围绕一个 GlobalToys 网络玩具销售系统展开从 E-R 图设计、关系模式转换、第三范式规范化一直做到建表、查询、索引、视图、存储过程、触发器、备份还原和二进制日志恢复是一条完整的数据库课程设计链路。适合两类人一是要做数据库课程设计、需要一份能讲清楚设计逻辑的作业蓝图的新手二是日常用 MySQL 写 SQL、但没系统走完「设计—优化—恢复」全流程的开发者。里面的 14 张表、20 多道查询练习和日志恢复实验每一节都能单独拆出来落地值得照着做一遍。2. E-R 图到第三范式把 14 张表的设计逻辑拆开讲2.1 先做实体提取玩具、客户、订单之外别漏了国家拿到需求描述后第一件事不是画图而是把名词逐个圈出来。这个系统里能称为实体的有顾客 Shopper、玩具 Toys、玩具品牌 ToyBrand、玩具类别 Category、订单 Orders、订单明细 OrderDetail、购物车 ShoppingCart、包装 Wrapper、运货 Shipment、运货方式 ShippingMode、运价表 ShippingRate、接受者 Recipient、国家 Country、月销售量 PickOfMonth一共 14 个。初学者最容易漏的是 Country。很多人会把国家当成 Shopper 的一个普通属性直接放一列「国籍」完事。但这个系统里运价是按「国家 运货方式」联合计算的ShippingRate 表的主键就是 cCountryId cModelId同时 Shopper 和 Recipient 都要引用国家。国家在这里是一个被多表引用的业务维度必须独立成表否则运价表和顾客表里会出现大量重复的国家名改一个名称要动好几张表。实体提取还有个常见误区把「订单总价 mTotalCost」设计成 Orders 表的普通属性。它是从明细行算出来的聚合值不是业务录入项。数据库设计里「算出来的字段」要么做成视图要么在程序中计算放进表里会埋下不一致的隐患。2.2 关系模式转换1:N 放外键M:N 建中间表E-R 图确认后转关系模式的规则是固定的实体各成一张表1:N 联系把「1」方主键放到「N」方做外键M:N 联系必须抽一张中间表。我一般会先把联系画一遍再动手避免漏掉中间表。这个系统里几组关键联系如下联系双方基数转换方式外键落点Shopper - Orders1:N顾客主键进订单表Orders.cShopperIdOrders - ToysM:N抽中间表 OrderDetailcOrderNo cToyIdToys - ToyBrandN:1品牌主键进玩具表Toys.cBrandIdToys - CategoryN:1类别主键进玩具表Toys.cCategoryIdShoppingCart - ToysM:N抽中间表购物车明细cCartId cToyIdOrders - Shipment1:1可并入一侧教材单独成表Shipment.cOrderNoCountry - ShippingRate1:N国家主键进运价表ShippingRate.cCountryIdOrderDetail 是关键中的关键。订单和玩具是多对多一次订货可以包含多个玩具一个玩具也会出现在多次订货里。如果不建 OrderDetail就得在 Orders 里塞多个玩具字段或者让 Toys 表管订单两种都违反第一范式。中间表的主键通常是 (cOrderNo, cToyId) 联合主键数量、单价、包装选择这些订单维度的属性全部放明细行。2.3 第三范式检查哪些冗余能留哪些必须拆第三范式的检查标准一句话非主属性不能对候选键存在传递依赖。翻译成人话就是——每一列都应该直接描述主键而不能「通过另一列间接描述主键」。以 Orders 为例如果设计时图省事把顾客地址也塞进 Orders就会出现这样的依赖链cOrderNo → cShopperId → vAddress。订单号确认了顾客顾客确认了地址地址就成了传递依赖。正确做法是 Orders 只留 cShopperId 外键地址查询时再 JOIN Shopper 表。这也解释了为什么这个案例里 Recipient 和 Shopper 是两张表下单人和收货人可能不是同一个人地址、电话、姓名都各自归属于不同实体。第三范式并不等于消灭所有冗余。OrderDetail 里的 mToyCost玩具价格看起来冗余——玩具价格明明在 Toys.mToyRate 里查询时 JOIN 一下就能拿到。但订单确认后玩具可能涨价如果明细行不存价格快照历史订单的金额会跟着玩具表一起变财务上完全不可接受。这个「冗余」是业务快照是符合第三范式的因为它直接依赖 (cOrderNo, cToyId) 联合主键不存在传递路径。3. 建表与完整性约束从 DDL 看 MySQL 的约束边界3.1 先建 Category 和 ToyBrand被引用的表永远先建建表顺序有个硬规则先建被引用方再建引用方。Toys 表的外键指向 Category 和 ToyBrand所以这两张表必须先落地。Category 的表结构如下CREATE TABLE Category ( cCategoryId CHAR(3) PRIMARY KEY, cCategory CHAR(20) NOT NULL UNIQUE, vDescription VARCHAR(100) NULL ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这段 DDL 里cCategoryId CHAR(3)是主键cCategory CHAR(20) NOT NULL UNIQUE同时加了非空和唯一约束对应教材要求里「种类名唯一但不是主键」vDescription VARCHAR(100) NULL允许描述为空。注意类别代码用 CHAR(3) 而不是 INT是因为业务上的类别代码就是 001、002 这样的定长编码用 CHAR 排序更直观也避免 INT 自动去掉前导零。ToyBrand 的结构同理主键 cBrandId品牌名唯一CREATE TABLE ToyBrand ( cBrandId CHAR(3) PRIMARY KEY, cBrandName CHAR(20) NOT NULL UNIQUE ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;两张表都显式指定了ENGINEInnoDB。这不是废话——InnoDB 才支持外键约束和事务MyISAM 会把外键当摆设存储过程里做事务回滚也会失效。3.2 Toys 主表主键、CHECK、默认值、外键一次写全Toys 是这个系统的核心表约束最多。教材把约束拆成了创建和修改两步我习惯一次写全逻辑更清晰CREATE TABLE Toys ( cToyId CHAR(6) PRIMARY KEY, cToyName VARCHAR(20) NOT NULL, vToyDescription VARCHAR(250) NOT NULL, cCategoryId CHAR(3) NOT NULL, mToyRate DECIMAL(10,2) NOT NULL, cBrandId CHAR(3) NOT NULL, imPhoto BLOB, siToyQoh SMALLINT NOT NULL, siLowerAge SMALLINT DEFAULT 1, siUpperAge SMALLINT DEFAULT 1, iToyWeight SMALLINT DEFAULT 1, vToyImgPath VARCHAR(50), CONSTRAINT chk_toys_qoh CHECK (siToyQoh BETWEEN 0 AND 200), CONSTRAINT fk_toys_category FOREIGN KEY (cCategoryId) REFERENCES Category(cCategoryId), CONSTRAINT fk_toys_brand FOREIGN KEY (cBrandId) REFERENCES ToyBrand(cBrandId) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;几个点说明一下。siLowerAge DEFAULT 1和iToyWeight DEFAULT 1对应教材「年龄下限默认 1、重量默认 1」的要求CHECK (siToyQoh BETWEEN 0 AND 200)限制库存数量范围。但这里有个必须知道的边界MySQL 5.7 及更早版本会解析 CHECK 子句却直接忽略8.0.16 之后才默认强制。如果你用的是 5.7这个库存范围约束并不会真正生效需要在应用层校验或者用触发器兜底。imPhoto BLOB存图片二进制vToyImgPath VARCHAR(50)存图片路径教材附件里原字段名是 vToyImgPath扫描版里经常被 OCR 成 vToylmgPathl 和 I 混淆抄表结构时别照抄否则后面写 SQL 全在报错。3.3 数据维护操作改价格、删品牌、复制新表表建好后教材要求录入品牌、类别和玩具数据再做几个典型维护操作-- 玩具 000001 价格上调 1 元 UPDATE Toys SET mToyRate mToyRate 1 WHERE cToyId 000001; -- 删除品牌 Largo DELETE FROM ToyBrand WHERE cBrandId 004; -- 复制 Activity 类信息到新表 PreferredCategory CREATE TABLE PreferredCategory AS SELECT * FROM Category WHERE cCategoryId 001;DELETE 品牌这里有个注意点如果 Toys 表里还有玩具引用 cBrandId004外键约束会让 DELETE 直接失败。教材里的业务顺序是先把该品牌的玩具处理掉再删品牌实际作业里如果直接执行报错先检查 Toys 表的关联数据。CREATE TABLE AS SELECT复制出来的 PreferredCategory 有一个隐蔽问题它只复制了数据和列定义主键、唯一约束、外键全部丢失。如果作业要求新表保持完整性正解是先CREATE TABLE PreferredCategory LIKE Category复制结构再INSERT INTO ... SELECT复制数据。4. 查询、索引与视图20 道 SQL 练习里藏着优化主线4.1 多表连接购物者、订单、玩具的三表 JOIN 怎么写教材里大量查询需要连接 Shopper、Orders、OrderDetail、Toys 四张表。核心练习「显示购物者名字和所订购玩具名称」SELECT s.vFirstName, t.cToyName FROM shopper s JOIN orders o ON s.cShopperId o.cShopperId JOIN orderdetail od ON o.cOrderNo od.cOrderNo JOIN toys t ON od.cToyId t.cToyId;这段 SQL 的连接路径是顾客 → 订单 → 明细 → 玩具。很多人写多表 JOIN 时不知道从哪张表起步我的习惯是「从业务主语出发」——主语是购物者就从 shopper 起然后沿着外键方向一步步走。注意我给表都起了短别名四张表连出来 SQL 不至于淹没在一长串表名里。如果还想带出订购数量在 SELECT 里加od.siQty就行JOIN 结构不用动。这类练习的价值在于让你熟悉从主表到明细表的常规路径后面写存储过程批量生成数据时同样的 JOIN 结构直接复用。4.2 分组统计与排序GROUP BY、HAVING 和取前 N 名统计类查询是这套练习里最容易丢分的部分。「按运货方式统计运货费用」用 GROUP BY HAVINGSELECT cShippingModelId, SUM(mShippingCharges) AS total_charges FROM orders GROUP BY cShippingModelId HAVING SUM(mShippingCharges) 100;GROUP BY 按运货方式分组SUM 算每组总费用HAVING 在分组后过滤——注意 WHERE 是在分组前过滤行HAVING 是在分组后过滤组两者不能互换。「总费用最高的前 3 个订单」这类题MySQL 用 ORDER BY LIMIT 就够SELECT cOrderNo, mTotalCost FROM orders ORDER BY mTotalCost DESC LIMIT 3;这里有个细节如果总费用有并列LIMIT 3 可能漏掉并列的第 4 名。严格做法是用窗口函数DENSE_RANK() OVER (ORDER BY mTotalCost DESC)再过滤但教材阶段 ORDER BY LIMIT 是标准答案。4.3 索引设计与 EXPLAIN连接字段慢在哪、加在哪教材专门提到一条慢查询查询购物者名字及订购玩具总价要创建索引优化。这条查询的 WHERE 和 JOIN 条件都在shopper.cShopperId Orders.cShopperId。shopper 表的主键天然有索引缺的是 Orders 表上的 cShopperId 索引——没有它MySQL 对 Orders 只能全表扫描。CREATE INDEX idx_orders_shopper ON orders(cShopperId);如果查询还要回表取 mTotalCost可以进一步做覆盖索引让索引直接包含查询列跳过回表CREATE INDEX idx_orders_shopper_cost ON orders(cShopperId, mTotalCost);执行后必须用 EXPLAIN 验证效果EXPLAIN SELECT s.vFirstName, o.mTotalCost FROM shopper s JOIN orders o ON s.cShopperId o.cShopperId WHERE s.cShopperId 000001;正常情况下 type 列会从 ALL全表扫描变成 ref通过索引查找rows 估算值明显下降这就是索引生效的证据。教材里「确保 cToyId 没有重复」「确保 cCategory 唯一」对应的做法分别是CREATE UNIQUE INDEX和ALTER TABLE Category ADD UNIQUE(cCategory)原理都是用唯一索引让数据库在写入时就拦截重复值。4.4 视图更新失败的根源多表视图的更新边界教材里有一个非常经典的报错案例视图 vwOrderWrapper 由 OrderDetail JOIN Wrapper 构成执行更新报错-- 报错多表视图无法跨基表更新 UPDATE vwOrderWrapper SET siQty 2, mWrapperRate 3.50 WHERE cOrderNo 000001;报错原因是 MySQL 对可更新视图有严格限制视图涉及多表连接时更新操作必须能映射回单一基表。这里的 siQty 属于 OrderDetailmWrapperRate 属于 Wrapper一条 UPDATE 同时改两张不同基表的列MySQL 直接拒绝。正确做法是拆成两条基表更新UPDATE OrderDetail SET siQty 2 WHERE cOrderNo 000001; UPDATE Wrapper SET mWrapperRate 3.50 WHERE cWrapperId ( SELECT cWrapperId FROM OrderDetail WHERE cOrderNo 000001 );这个案例值得记住视图是查询的封装不是表的替代品。视图可以让查询变简洁但它更新不了的东西最终还是要回基表操作。5. 存储过程与触发器事务原子性的实现与常见问题排查5.1 动态订单号生成prcGenOrder 的边界和变量坑教材里 prcGenOrder 的目标是读取 Orders 表当前最大订单号加 1 后补零成 6 位编码。原始参考代码是 SQL Server 风格的 convert 写法在 MySQL 里直接跑会报语法错而且变量命名和列名冲突。我用 MySQL 8.0 语法重写DELIMITER $$ CREATE PROCEDURE prcGenOrder(OUT v_NewOrderNo CHAR(6)) BEGIN DECLARE v_MaxNo CHAR(6); DECLARE v_NextNo INT; SELECT MAX(cOrderNo) INTO v_MaxNo FROM orders; IF v_MaxNo IS NULL THEN SET v_NextNo 1; ELSE SET v_NextNo CAST(v_MaxNo AS UNSIGNED) 1; END IF; SET v_NewOrderNo LPAD(v_NextNo, 6, 0); END$$ DELIMITER ;两个关键点。第一LPAD(v_NextNo, 6, 0) 把数字补成 6 位1 变 000001999 变 000999比教材里一串 if 分支简洁得多效果完全一致。第二变量名我用 v_ 前缀避免和列名 cOrderNo 混淆——教材原版里 DECLARE OrderNo 和列名重名SELECT INTO 时会出现变量永远取不到值的诡异现象这是新手写存储过程最常见的翻车点。5.2 确认订购的四个步骤用事务包住 DML确认订购时四个步骤必须同时成功或同时失败生成订单号、写入 Orders、写入 OrderDetail、更新明细价格。这就是教材反复强调的原子性。拆成存储过程用显式事务包住DELIMITER $$ CREATE PROCEDURE prcConfirmOrder( IN p_ShopperId CHAR(6), IN p_ToyId CHAR(6), IN p_Qty SMALLINT ) BEGIN DECLARE v_OrderNo CHAR(6); DECLARE v_ToyRate DECIMAL(10,2); START TRANSACTION; CALL prcGenOrder(v_OrderNo); INSERT INTO orders(cOrderNo, cShopperId, dOrderDate) VALUES (v_OrderNo, p_ShopperId, CURDATE()); INSERT INTO orderdetail(cOrderNo, cToyId, siQty) VALUES (v_OrderNo, p_ToyId, p_Qty); SELECT mToyRate INTO v_ToyRate FROM toys WHERE cToyId p_ToyId; UPDATE orderdetail SET mToyCost p_Qty * v_ToyRate WHERE cOrderNo v_OrderNo AND cToyId p_ToyId; COMMIT; END$$ DELIMITER ;事务的意义在于如果后面某个 INSERT 失败前面已写入的数据不会残留。中间的 SELECT INTO 是当前场景下从 Toys 拿单价的常见做法如果订单要求价格快照这里还可以在明细表存一份单价和 Toys 表解耦。另一个必须注意的边界MySQL 的事务里不能混入 DDL 语句比如 CREATE TABLE、ALTER TABLE。DDL 会导致隐式提交前面未提交的 DML 全部被固化ROLLBACK 也救不回来。事务存储过程里只放 INSERT、UPDATE、DELETE这是铁律。5.3 MySQL 触发器的能力边界prcCharges 该用过程还是触发器教材要求「创建一个叫 prcCharges 的触发器按照给定的订货代码返回船运费和包装费」。这里有一个 MySQL 和教材预期不一致的坑MySQL 的触发器不支持 RETURN 返回值。触发器只能在你指定的时机自动执行一段 DML它没法像函数一样被 SELECT 调用并返回结果。在这个场景下我的做法是把「计算船运费 包装费」写成存储过程用 OUT 参数带出结果DELIMITER $$ CREATE PROCEDURE prcGetCharges( IN p_OrderNo CHAR(6), OUT o_ShipCharge DECIMAL(10,2), OUT o_WrapCharge DECIMAL(10,2) ) BEGIN SELECT mShippingCharges INTO o_ShipCharge FROM shipment WHERE cOrderNo p_OrderNo; SELECT COALESCE(SUM(w.mWrapperRate), 0) INTO o_WrapCharge FROM orderdetail od JOIN wrapper w ON od.cWrapperId w.cWrapperId WHERE od.cOrderNo p_OrderNo; END$$ DELIMITER ;这个写法能跑通教材后续的 prcHandlingCharges 需求处理费 船运费 包装费CALL prcGetCharges 拿到两个 OUT 参数相加即可。触发器的正确使用场景是「改库存」这类自动动作——订单确认后把 Toys.siToyQoh 减掉订购数量这不需要返回值才适合用 AFTER INSERT 触发器。5.4 常见问题排查五条踩坑记录踩坑一视图更新报错。现象UPDATE vwOrderWrapper 直接报错提示视图不可更新。原因多表 JOIN 视图无法映射回单一基表。解决拆成对 OrderDetail 和 Wrapper 的两次基表 UPDATE先查子查询拿到 cWrapperId 再更新对应表。踩坑二存储过程变量永远取不到值。现象SELECT MAX(cOrderNo) INTO OrderNo 后OrderNo 一直是 NULL往下走逻辑全乱。原因变量名和列名同名MySQL 解析时把变量当成了列引用。解决变量统一加 v_ 前缀改成SELECT MAX(cOrderNo) INTO v_MaxNo靠命名规范规避解析歧义。踩坑三触发器「不生效」。现象建好触发器后调用无任何反应也没报错。原因MySQL 触发器不能像函数那样被主动调用它只在对应事件INSERT/UPDATE/DELETE发生时自动执行。解决明确触发器的被动触发特性需要按订单号动态计算的逻辑改用存储过程 OUT 参数。踩坑四CHECK 约束被无视。现象向 Toys 插入 siToyQoh500 的记录数据库没有拦截。原因MySQL 5.7 及以下版本解析 CHECK 但不强制执行8.0.16 以后才默认启用。解决确认版本SELECT VERSION()5.7 环境用触发器或生成列兜底不要依赖 CHECK。踩坑五事务回滚失效。现象存储过程某步报错后前面已 INSERT 的数据还在表里。原因事务里混入了 DDL 语句导致隐式提交或者报错后没有显式 ROLLBACKMySQL 默认把未提交事务在连接关闭时回滚但显式事务中需要你自己处理异常分支。解决BEGIN...EXCEPTION 块或 DECLARE EXIT HANDLER 捕获异常后执行 ROLLBACK事务体内只放 DML。6. 备份还原与日志mysqlbinlog 定点恢复与恢复后四步验证6.1 mysqldump 与 source最基础的全库备份恢复日常备份用 mysqldump 一条命令搞定注意必须加--routines --triggers否则存储过程和触发器不会进备份文件mysqldump -u root -p --single-transaction --routines --triggers \ GlobalToys D:/GlobalToysbak/gbak.sql恢复时两种方式等价命令行执行mysql -u root -p gbak.sql或进入 mysql 客户端后用source D:/GlobalToysbak/gbak.sql。source 是客户端内建命令路径分隔符用正斜杠反斜杠会被转义掉——这一步我踩过多次路径不对直接报文件不存在。6.2 二进制日志定点恢复删表之后把数据找回来有一次我按教材实验步骤操作开启二进制日志、插入两条玩具记录、然后暂停日志并清空了 Toys 表。重新开启日志后用普通备份只能恢复到备份点新插入的两条记录全丢了。正确做法是用二进制日志定位到 DELETE 之前的日志位置mysqlbinlog --stop-position1895 --databaseGlobalToys \ D:/GlobalToys/log/binlog.000001 | mysql -u root -p先在SHOW BINLOG EVENTS里找到 DELETE 事务的起始 position把 stop-position 卡在它之前重放日志就会执行到删除动作前停下那两条 INSERT 记录自然回来了。--database参数限定只恢复目标库防止日志里其他库的操作被误重放。日志文件不要直接改mysqlbinlog输出重定向到 mysql 命令即可。6.3 恢复后的四步验证与习惯数据恢复回来不是结束我现在的习惯是强制走完四步才让业务碰库第一步SHOW TABLES确认表数量第二步对关键表SELECT COUNT(*)对比备份前的行数快照第三步看SHOW TABLE STATUS LIKE toys检查 AUTO_INCREMENT——用日志恢复后自增值经常回退不修正的话后面插入新玩具直接主键冲突第四步抽查外键关联比如挑一个订单 JOIN 明细和玩具表确认数据对得上。有一次恢复完行数都对唯独自增值留在了备份点业务插入立刻报主键重复从那以后我每次恢复都强制把这四步走完才收工再没出过幺蛾子。希望帮到你。本文还有配套的精品资源点击获取