简介这份PDF面向学习MySQL数据库原理与应用的高校学生及开发者围绕教材第2版微课版的课外拓展内容以网络玩具销售系统为完整案例解决从理论到数据库设计落地的衔接问题。资源为单个PDF文件压缩包约649KB内容以图文与表格形式呈现便于在电脑或平板上阅读。案例覆盖客户、玩具、订单、品牌、类别、国家、月销售量、接受者、运货、运价、购物车、包装等实体集完整演示E-R图建模、关系模式转换与第三范式规范化过程并给出索引创建、视图定义及多表联接查询的优化思路。读者可据此掌握实体关系提取、主外键关联、消除传递依赖、利用索引与视图提升查询效率等核心技能适合作为课程设计、实验报告或数据库综合练习的参考素材。目前已有2366人学习下载。1. 从一份课外拓展 PDF 说起网络玩具销售系统的库表到底怎么落地很多人拿到《MySQL数据库原理及应用(第2版)(微课版)-课外拓展.pdf》的第一反应是这不就是一份课后习题吗但如果你真按它的要求把 GlobalToys 这个网络玩具销售系统的库建出来会发现它其实是一份被低估的实战清单——从 E-R 图到第三范式从索引视图到存储过程触发器再到 mysqldump 备份和二进制日志恢复几乎把 MySQL 在互联网业务里最常用的那套东西全串了一遍。它适合两类人一是刚学完 SQL 语法、想找个完整案例把知识点焊死的在校生二是工作里要接手电商类库表、但没系统走过一遍设计流程的初中级开发。这份 PDF 的价值不在题目本身而在于它逼你把“设计—建表—查询—优化—运维”这条链路完整走一遍中间任何一个环节偷懒后面都会翻车。2. 把 E-R 图翻译成能建的表实体、外键与第三范式2.1 先认清这套系统里到底有几个实体PDF 里列了一长串表Toys、Category、ToyBrand、Shopper、Orders、OrderDetail、ShoppingCart、Wrapper、Recipient、Shipment、ShippingMode、ShippingRate、Country、PickOfMonth。乍一看很乱其实按业务动作归类就清楚了商品侧是 Toys、Category、ToyBrand用户侧是 Shopper、ShoppingCart交易侧是 Orders、OrderDetail、Wrapper履约侧是 Recipient、Shipment、ShippingMode、ShippingRate、Country统计侧是 PickOfMonth。实体识别最容易踩的坑是把“订单明细”当成独立实体。OrderDetail 本质是 Orders 和 Toys 的多对多桥接表它的主键应该是 (cOrderNo, cToyId) 这样的复合键而不是单独造一个自增 ID。PDF 里给的字段 cOrderNo、cToyId、siQty、mToyCost 正好印证了这一点——mToyCost 是冗余的成交价快照因为玩具单价会变订单必须锁住下单那一刻的价格。2.2 E-R 转关系模式外键该挂在哪张表转换规则不复杂一对多关系外键放在“多”的那一侧多对多关系单独建桥接表。放到这个案例里Shopper 与 Orders 是一对多cShopperId 作为外键放在 Orders 里。Orders 与 Toys 是多对多用 OrderDetail 桥接同时挂 cOrderNo 和 cToyId 两个外键。Toys 与 Category、ToyBrand 都是多对一cCategoryId、cBrandId 放在 Toys 里。Recipient 与 Orders 是一对一或一对多cOrderNo 放在 Recipient 里作为外键。这里有个细节PDF 表 4.51 里 Recipient 的 cOrderNo 标注为 PK/FK说明它把订单号同时当主键和外键用意味着一张订单只对应一个收件人。如果你的业务允许多个收件地址就得改成独立主键加普通外键这是设计时要根据需求确认的边界。2.3 规范化到第三范式哪些字段必须拆出去第三范式的要求是消除非主属性对候选键的传递依赖。拿 Toys 表举例如果直接把品牌名称 cBrandName 塞进 Toys那就违反了 3NF——cBrandName 依赖 cBrandId而 cBrandId 依赖 cToyId形成传递依赖。所以必须拆出 ToyBrand 表Toys 里只留 cBrandId。同理Category 的类别描述 vDescription 也不能塞进 Toys。PDF 表 4.28 到 4.30 给出的结构正是规范化后的结果。但要注意规范化不是越狠越好。OrderDetail 里的 mToyCost 就是故意保留的冗余因为它记录的是历史成交价不是当前单价这种“有业务含义的冗余”在 3NF 里是允许的别一刀切删掉。建表时把完整性规则一起写进去比事后补约束省事得多。下面这段是 Toys 表的核心建表语句把 PDF 要求的检查约束和默认值都带上了CREATE TABLE Toys ( cToyId CHAR(6) NOT NULL, 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, imPhotos BLOB NULL, siToyQoh SMALLINT NOT NULL, siLowerAges SMALLINT DEFAULT 1, siUpperAges SMALLINT DEFAULT 1, siToyWeight SMALLINT DEFAULT 1, vToyImgPath VARCHAR(50) NULL, PRIMARY KEY (cToyId), CONSTRAINT chk_qoh CHECK (siToyQoh BETWEEN 0 AND 200), CONSTRAINT chk_rate CHECK (mToyRate 0), CONSTRAINT fk_toy_cat FOREIGN KEY (cCategoryId) REFERENCES Category(cCategoryId), CONSTRAINT fk_toy_brand FOREIGN KEY (cBrandId) REFERENCES ToyBrand(cBrandId) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;逻辑说明主键 cToyId 保证玩具唯一chk_qoh 把库存锁在 0 到 200防止超卖或负数chk_rate 保证价格为正两个外键分别指向 Category 和 ToyBrand保证引用完整性。参数上siLowerAges 和 siUpperAges 默认值设为 1 是 PDF 明确要求的实际业务里年龄下限通常不会低于 0这里按题目走即可。存储引擎选 InnoDB 是因为后面要用事务和触发器MyISAM 不支持这些。提示建表顺序有讲究必须先建 Category 和 ToyBrand再建 Toys否则外键会报 1215 错误。删表时顺序反过来。3. 索引、视图与那几条“跑得慢”的查询怎么救3.1 索引不是越多越好得看查询打在哪PDF 里反复提到“上述查询执行要花费很长时间创建相应的索引来优化”。这句话背后是典型的连接查询性能问题。以“查询购物者名字及其订购玩具总价”为例SQL 要 join Shopper、Orders、OrderDetail 三张表如果 Orders.cShopperId 和 OrderDetail.cOrderNo 上没有索引MySQL 只能走全表扫描数据量一上来就是秒级甚至更久。常见做法是在外键列上建普通索引-- 加速 Shopper 与 Orders 的连接 CREATE INDEX idx_orders_shopper ON Orders(cShopperId); -- 加速 Orders 与 OrderDetail 的连接 CREATE INDEX idx_orderdetail_order ON OrderDetail(cOrderNo); -- Toys 经常按 cToyId 查询且要求不重复 CREATE UNIQUE INDEX idx_toys_id ON Toys(cToyId); -- Category 按 cCategoryId 查询主键已唯一补一个唯一索引兜底 CREATE UNIQUE INDEX idx_category_id ON Category(cCategoryId);逻辑说明前两条是普通索引服务于 join 条件后两条是唯一索引既加速查询又强制唯一性。参数上索引列的选择原则是“出现在 WHERE、JOIN ON、ORDER BY 里的列优先”但不要给低基数列比如性别建索引优化器大概率不走。另外Toys 的 cToyId 已经是主键InnoDB 会自动建聚簇索引再建一个唯一索引属于冗余PDF 要求“确保没有重复”主键本身已经保证了这条可以按需取舍。3.2 视图把复杂 join 封装成一个“虚拟表”PDF 里定义了 vwOrderWrapper 视图把 OrderDetail 和 Wrapper 连起来。视图的好处是复杂查询写一次后面直接 select减少网络传输和重复解析。但视图有个经典坑含 join 的视图默认不可更新。PDF 里那条 update vwOrderWrapper 报错就是这个原因。解决办法是绕过视图直接更新基表-- 不要这样写会报错 -- UPDATE vwOrderWrapper SET siQty 2 WHERE cOrderNo 000001; -- 正确做法直接更新 OrderDetail 基表 UPDATE OrderDetail SET siQty 2 WHERE cOrderNo 000001; -- 包装费在 Wrapper 表里单独更新 UPDATE Wrapper w JOIN OrderDetail od ON w.cWrapperId od.cWrapperId SET w.mWrapperRate w.mWrapperRate WHERE od.cOrderNo 000001;逻辑说明第一条 update 直接打在基表上因为 siQty 本来就属于 OrderDetail第二条通过 join 定位到对应包装记录再改费率。参数上cOrderNo 是订单号必须加引号因为是 char 类型。如果非要让视图可更新得满足“一对一映射且不含聚合、distinct、group by”等条件实际项目里很少这么干直接操作基表更稳。3.3 那几条业务查询的写法要点PDF 里列了十几条查询挑几条容易写错的说说。“显示所有玩具的名称和购物车代码如果不在购物车上显示 NULL”——这是左外连接的标准场景SELECT t.cToyName, sc.cCartId FROM Toys t LEFT JOIN ShoppingCart sc ON t.cToyId sc.cToyId;逻辑说明以 Toys 为主表左连 ShoppingCart没被加入购物车的玩具sc.cCartId 自然为 NULL。如果写成 inner join不在购物车里的玩具就丢了。“显示订货代码和每次订货的玩具总价只显示总价超过 50 的”——用 group by havingSELECT cOrderNo, SUM(mToyCost) AS TotalCostOfToys FROM OrderDetail GROUP BY cOrderNo HAVING SUM(mToyCost) 50;逻辑说明先按订单分组求和再用 having 过滤。注意 having 和 where 的区别——where 在分组前过滤行having 在分组后过滤组聚合条件只能放 having。4. 存储过程与触发器把事务原子性焊进数据库4.1 为什么要把逻辑放进存储过程PDF 里 prcGenOrder 那段逻辑很典型生成订单号、插 Orders、插 OrderDetail、更新玩具价格四步必须原子完成。放在应用层用代码拼网络一抖就可能只执行一半数据就脏了。放进存储过程由数据库保证事务边界是更稳的做法。生成订单号的逻辑是取当前最大订单号加一再补零到 6 位。PDF 给的写法用了一长串 case when其实可以用 LPAD 简化DELIMITER $$ CREATE PROCEDURE prcGenOrder( IN pShippingMode CHAR(2), IN pShopperId CHAR(6) ) BEGIN DECLARE vNewOrderNo CHAR(6); DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; START TRANSACTION; -- 取最大订单号并加一补零到 6 位 SELECT LPAD(IFNULL(MAX(cOrderNo), 0) 1, 6, 0) INTO vNewOrderNo FROM Orders; -- 插入订单主表 INSERT INTO Orders(cOrderNo, dOrderDate, cShippingMode, cShopperId) VALUES (vNewOrderNo, CURDATE(), pShippingMode, pShopperId); -- 把购物车内容转入订单明细 INSERT INTO OrderDetail(cOrderNo, cToyId, siQty, mToyCost) SELECT vNewOrderNo, sc.cToyId, sc.siQty, sc.siQty * t.mToyRate FROM ShoppingCart sc JOIN Toys t ON sc.cToyId t.cToyId WHERE sc.cShopperId pShopperId; COMMIT; END$$ DELIMITER ;逻辑说明START TRANSACTION 开启事务四步操作要么全成要么全回滚EXIT HANDLER 捕获异常后回滚并重新抛出避免吞错。参数 pShippingMode 是运货方式代码pShopperId 是购物者编号。LPAD 比一长串 case when 简洁得多效果一样。注意 DELIMITER 要改否则分号会提前结束语句。4.2 触发器库存扣减和价格联动PDF 要求“订购完成后自动减少库存”和“改变订货数量时自动修改玩具价格”。这两个用触发器实现最自然DELIMITER $$ CREATE TRIGGER trg_after_orderdetail_insert AFTER INSERT ON OrderDetail FOR EACH ROW BEGIN UPDATE Toys SET siToyQoh siToyQoh - NEW.siQty WHERE cToyId NEW.cToyId; END$$ CREATE TRIGGER trg_after_orderdetail_update AFTER UPDATE ON OrderDetail FOR EACH ROW BEGIN -- 数量变化时同步更新该明细的玩具总价 UPDATE OrderDetail SET mToyCost NEW.siQty * (SELECT mToyRate FROM Toys WHERE cToyId NEW.cToyId) WHERE cOrderNo NEW.cOrderNo AND cToyId NEW.cToyId; END$$ DELIMITER ;逻辑说明第一个触发器在插入订单明细后自动扣库存NEW.siQty 是刚插入的数量第二个在更新明细后重算 mToyCost。参数上NEW 代表新行OLD 代表旧行AFTER 表示操作成功后触发。注意触发器里不要再写 COMMITMySQL 触发器本身在事务里执行手动提交会报错。注意触发器是双刃剑逻辑藏在数据库里应用层看不到排查问题时容易变成黑匣子。生产环境里我一般只把“强一致性且逻辑极简”的操作放触发器复杂业务还是回应用层。5. 备份、日志与恢复别等数据没了才想起 mysqldump5.1 逻辑备份mysqldump 的三种用法PDF 要求用 Navicat 和命令行两种方式备份 GlobalToys。命令行更通用也更容易脚本化# 备份整个数据库 mysqldump -u root -p --databases GlobalToys D:/GlobalToysBak/gbak.sql # 只备份 Toys 和 Orders 两张表 mysqldump -u root -p GlobalToys Toys Orders D:/GlobalToysBak/bak.sql # 恢复先建库再导入 mysql -u root -p -e CREATE DATABASE IF NOT EXISTS GlobalToys mysql -u root -p GlobalToys D:/GlobalToysBak/gbak.sql逻辑说明第一条用 --databases 会带上 CREATE DATABASE 语句恢复时不用手动建库第二条不带 --databases只导出表结构和数据恢复前得先有库第三条恢复时用重定向导入。参数上-u 是用户名-p 后面不跟密码回车后输入避免密码进历史记录。如果数据量大加 --single-transaction 可以在 InnoDB 上做一致性快照而不锁表。5.2 二进制日志误删之后的后悔药PDF 里那段“插入两条记录→暂停 binlog→删光 Toys→重开 binlog→用 binlog 恢复”是经典演练。核心命令是 mysqlbinlog# 查看当前 binlog 列表 mysql -u root -p -e SHOW BINARY LOGS; # 从 binlog 中提取指定时间段的 SQL 并恢复 mysqlbinlog --start-datetime2024-01-01 10:00:00 \ --stop-datetime2024-01-01 10:30:00 \ D:/GlobalToys/log/mysql-bin.000001 | mysql -u root -p GlobalToys逻辑说明SHOW BINARY LOGS 列出所有日志文件mysqlbinlog 按时间范围解析出 SQL 事件管道给 mysql 执行。参数上--start-datetime 和 --stop-datetime 要精确到秒范围取误操作之前到之后。注意 binlog 格式建议用 ROW恢复时更精确STATEMENT 格式遇到 now() 这类函数可能不一致。5.3 慢查询日志找出拖后腿的 SQLPDF 要求开启慢查询日志。配置方式有两种临时生效用命令永久生效改 my.cnf-- 临时开启阈值 2 秒 SET GLOBAL slow_query_log ON; SET GLOBAL slow_query_log_file D:/GlobalToys/log/slow.log; SET GLOBAL long_query_time 2;逻辑说明slow_query_log 开关日志slow_query_log_file 指定路径long_query_time 是阈值超过这个秒数的 SQL 会被记录。参数上生产环境一般设 1 到 2 秒测试环境可以设 0 抓全部。改完用 SHOW VARIABLES LIKE slow_query_log% 确认生效。6. 进阶技巧用 EXPLAIN 和存储过程把性能问题摁在开发阶段前面把建表、索引、存储过程、备份都过了一遍但真正让这套库跑得稳的是养成“写完 SQL 先看执行计划”的习惯。EXPLAIN 是 MySQL 里最便宜的性能诊断工具没有之一。拿 PDF 里那条“查询购物者名字及订购玩具总价”的 SQL 举例加个 EXPLAIN 就能看出索引有没有被用上EXPLAIN SELECT s.vFirstName, SUM(od.mToyCost) AS mTotalCost FROM Shopper s JOIN Orders o ON s.cShopperId o.cShopperId JOIN OrderDetail od ON o.cOrderNo od.cOrderNo GROUP BY s.cShopperId, s.vFirstName;看结果重点盯三列type、key、rows。type 出现 ALL 就是全表扫描得补索引key 显示实际用了哪个索引如果是 NULL 说明没走索引rows 是预估扫描行数越大越危险。我一般要求自己写的每条多表 join 都过一遍 EXPLAINtype 至少要到 ref 或 range出现 ALL 就回去改。再说一个存储过程的进阶用法把“生成订单 扣库存 算总价”打包成一个过程应用层只调一次。这样做的额外好处是DBA 可以在过程里埋审计日志记录谁在什么时候下了单出问题能追溯。PDF 里 prcGenOrder 只做了订单生成实际项目里我会再补一张 order_audit 表在过程末尾插一条记录INSERT INTO order_audit(cOrderNo, cShopperId, dCreated, vAction) VALUES (vNewOrderNo, pShopperId, NOW(), CREATE_ORDER);这条插入和主流程在同一个事务里要么一起成功要么一起回滚不会出现“订单建了但审计没记”的情况。参数上vAction 用枚举值区分创建、修改、取消方便后续按动作筛选。还有一个容易被忽略的点存储过程的参数校验。PDF 里的过程大多假设入参合法但生产环境必须防脏数据。比如 prcGenOrder 里应该先检查 pShopperId 是否存在于 Shopper 表不存在就 SIGNAL SQLSTATE 抛错IF NOT EXISTS (SELECT 1 FROM Shopper WHERE cShopperId pShopperId) THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT Shopper not found; END IF;这段放在 START TRANSACTION 之前避免开了事务才发现参数错。SIGNAL 是 MySQL 5.5 之后的标准抛错方式比手动插错误表干净。最后说个血泪经验这套库的字符集一定要统一用 utf8mb4。PDF 里没提字符集但玩具名称、描述里出现中文和特殊符号是常事用 latin1 或 utf8 会丢字符。建库时就定好CREATE DATABASE GlobalToys DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;从那以后我每次建库都强制走一遍“字符集确认 EXPLAIN 检查 备份脚本挂定时任务”这三步少一步后面都可能还债。希望帮到你。本文还有配套的精品资源点击获取