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 联系必须抽一张中间表。我一般会先把联系画一遍再动手，避免漏掉中间表。这个系统里几组关键联系如下：

OrderDetail 是关键中的关键。订单和玩具是多对多：一次订货可以包含多个玩具，一个玩具也会出现在多次订货里。如果不建 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 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这段 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 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

两张表都显式指定了ENGINE=InnoDB。这不是废话——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) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

几个点说明一下。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 成 vToylmgPath（l 和 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 表里还有玩具引用 cBrandId='004'，外键约束会让 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 + HAVING：

SELECT 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 属于 OrderDetail，mWrapperRate 属于 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 变 000001，999 变 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 插入 siToyQoh=500 的记录，数据库没有拦截。原因：MySQL 5.7 及以下版本解析 CHECK 但不强制执行，8.0.16 以后才默认启用。解决：确认版本SELECT VERSION()；5.7 环境用触发器或生成列兜底，不要依赖 CHECK。

踩坑五：事务回滚失效。现象：存储过程某步报错后，前面已 INSERT 的数据还在表里。原因：事务里混入了 DDL 语句导致隐式提交，或者报错后没有显式 ROLLBACK，MySQL 默认把未提交事务在连接关闭时回滚，但显式事务中需要你自己处理异常分支。解决：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-position=1895 --database=GlobalToys \ 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 明细和玩具表确认数据对得上。有一次恢复完行数都对，唯独自增值留在了备份点，业务插入立刻报主键重复，从那以后我每次恢复都强制把这四步走完才收工，再没出过幺蛾子。希望帮到你。

本文还有配套的精品资源，点击获取