简介这份PDF面向学习MySQL数据库原理与应用的高校学生及开发者以网络玩具销售系统为完整案例帮助读者把E-R建模、关系模式转换与规范化等抽象理论落到实际项目设计中。资源包共1个PDF文件大小约649KB内容为课外拓展练习包含实体集与联系分析、E-R图绘制、关系模式转换、第三范式规范化以及索引与视图优化等任务。案例覆盖客户、库存玩具、订单、品牌、类别、国家、月销售量、接受者、运货、运价、购物车、包装等十余张表的字段设计与主外键关系并给出多表联接查询、索引创建、视图定义与更新报错分析等练习。已有2366人学习适合作为课程实验、课程设计或期末复习的配套训练材料帮助读者掌握从需求分析到性能优化的完整数据库设计流程。1. 课外拓展里的 MySQL为什么“会写 SQL”和“懂数据库”是两回事很多人学 MySQL 是从SELECT * FROM table开始的装完 mysql 8.0 或者 5.7.44能建表、能插数据、能写个WHERE条件就觉得自己“会数据库”了。但真到了面试或者项目里被问到“你这个表满足第三范式吗”“E-R 图怎么画的”“存储过程能不能做数据统计”往往就卡住了。课外拓展这类材料恰恰补的就是这块它不教你怎么装 mysql而是逼你回答“为什么这样设计”。这篇笔记围绕 MySQL 数据库原理及应用第2版的课外拓展内容展开把 E-R 图、第三范式、存储过程、事务处理、索引与锁这几条线串起来。适合已经能跑通 mysql 安装配置教程、写过增删改查但一遇到“设计”和“调优”就心里没底的人。目标很直接让你从“会敲命令”走到“能讲清楚为什么这么建表、为什么这么写存储过程”。2. 从 E-R 图到第三范式建表之前先把关系想清楚2.1 E-R 图不是画着玩的它是建表的施工图很多人建表是“想到哪列加哪列”结果后期改结构改到崩溃。E-R 图实体-联系图解决的就是这个问题先把业务里的实体、属性和联系画出来再翻译成表。常见做法是实体画矩形属性画椭圆联系画菱形一对多用 1:N 标注多对多用 M:N 标注。举个课外拓展里常见的例子学生选课。学生是一个实体课程是一个实体选课是一个联系。如果直接建一张大表把学生信息和课程信息塞在一起就会出现大量重复。正确做法是拆成三张表学生表、课程表、选课表。选课表里放学生 ID 和课程 ID外加成绩、选课时间这些属于“联系”的属性。提示E-R 图阶段不要急着想字段类型先把“谁和谁有关系、关系是一对多还是多对多”定下来。关系定错后面范式再标准也救不回来。2.2 第三范式消除传递依赖但别教条第三范式3NF的要求是在满足第二范式的基础上非主属性不能依赖于其他非主属性。说人话就是一张表里不能出现“A 决定 BB 决定 C”这种链式依赖。比如一张订单表里放了订单号、客户 ID、客户姓名、客户电话。客户姓名和电话其实依赖于客户 ID而客户 ID 依赖于订单号。这就形成了传递依赖不符合 3NF。拆法是把客户信息单独放到客户表订单表只留客户 ID。但实际项目里第三范式不是越严格越好。课外拓展里也会提到反范式为了查询性能有时会故意冗余一两个字段。比如订单表里冗余一个“客户姓名”避免每次查订单都去 join 客户表。判断标准是冗余字段是否频繁更新。如果客户改名很少发生冗余就是划算的如果天天改那就别冗余。-- 符合第三范式的拆表示例 CREATE TABLE student ( student_id INT PRIMARY KEY AUTO_INCREMENT, student_name VARCHAR(50) NOT NULL, class_id INT NOT NULL ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE course ( course_id INT PRIMARY KEY AUTO_INCREMENT, course_name VARCHAR(100) NOT NULL, credit DECIMAL(3,1) NOT NULL ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE student_course ( id INT PRIMARY KEY AUTO_INCREMENT, student_id INT NOT NULL, course_id INT NOT NULL, score DECIMAL(5,2) DEFAULT 0, select_time DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uk_student_course (student_id, course_id), FOREIGN KEY (student_id) REFERENCES student(student_id), FOREIGN KEY (course_id) REFERENCES course(course_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;上面这段代码里student_course表的主键是自增 id但真正防止重复选课的是uk_student_course这个唯一索引。score字段设置了默认值 0对应热搜里“mysql设置默认值为0”的场景。外键约束保证选课记录不会指向不存在的学生或课程。参数上ENGINEInnoDB是必须的因为只有 InnoDB 支持事务和外键utf8mb4支持完整的 Unicode避免 emoji 存不进去。2.3 用 SQL 反向检查你的表设计设计完表之后可以用几条 SQL 反向验证是否合理。比如检查有没有重复的选课记录-- 检查选课表是否存在重复记录 SELECT student_id, course_id, COUNT(*) AS cnt FROM student_course GROUP BY student_id, course_id HAVING cnt 1;如果这条查询返回了结果说明唯一索引没生效或者数据是绕过约束插进去的。正常情况应该返回空集。再比如检查外键有没有孤儿记录-- 检查选课表中是否存在无效的学生ID SELECT sc.* FROM student_course sc LEFT JOIN student s ON sc.student_id s.student_id WHERE s.student_id IS NULL;这两条检查语句在课外拓展里经常被忽略但实际项目上线前跑一遍能省掉很多“数据对不上”的扯皮。3. 存储过程把统计逻辑沉到数据库里还是放到应用层3.1 什么时候该用存储过程存储过程是一组预编译的 SQL 语句集合存在数据库里通过名字调用。热搜里“mysql存储过程”“mysql声明存储过程”“建一个统计当前库下各表数据总量的存储过程”都是这个方向。但先别急着写得想清楚这段逻辑放应用层好还是放数据库好适合用存储过程的场景批量数据处理、定时统计、复杂的事务逻辑需要原子执行。不适合的场景业务逻辑频繁变更、需要跨数据库移植、团队里没人熟悉存储过程语法。课外拓展里通常会强调存储过程是工具不是信仰。3.2 写一个统计各表数据量的存储过程下面这个存储过程统计当前库下每张表的数据行数对应热搜里那个具体需求。思路是查information_schema.TABLES拿到表名然后拼动态 SQL 去 count。DELIMITER $$ CREATE PROCEDURE count_all_tables() BEGIN DECLARE done INT DEFAULT 0; DECLARE tbl_name VARCHAR(255); DECLARE tbl_count BIGINT DEFAULT 0; -- 游标遍历当前库所有表 DECLARE cur CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA DATABASE() AND TABLE_TYPE BASE TABLE; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done 1; -- 临时结果表存放统计结果 DROP TEMPORARY TABLE IF EXISTS tmp_table_count; CREATE TEMPORARY TABLE tmp_table_count ( table_name VARCHAR(255), row_count BIGINT ); OPEN cur; read_loop: LOOP FETCH cur INTO tbl_name; IF done THEN LEAVE read_loop; END IF; -- 动态 SQL 统计每张表的行数 SET sql CONCAT(SELECT COUNT(*) INTO cnt FROM , tbl_name, ); PREPARE stmt FROM sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; INSERT INTO tmp_table_count VALUES (tbl_name, cnt); END LOOP; CLOSE cur; SELECT * FROM tmp_table_count ORDER BY row_count DESC; END$$ DELIMITER ;调用方式CALL count_all_tables();逻辑说明DELIMITER $$是为了让 MySQL 把整个存储过程当成一个语句而不是遇到分号就结束。游标cur从information_schema.TABLES里取出当前库的所有基础表。CONTINUE HANDLER FOR NOT FOUND是游标遍历结束的标准写法。动态 SQL 用PREPARE和EXECUTE执行因为表名不能直接当变量用。临时表tmp_table_count只在当前会话可见不会污染正式表。参数说明TABLE_SCHEMA DATABASE()限定当前数据库避免统计到其他库。TABLE_TYPE BASE TABLE排除视图。如果表特别多游标方式会慢可以考虑用information_schema.TABLES.TABLE_ROWS近似值但那个值在 InnoDB 里不精确只能参考。3.3 存储过程的调试与权限写完存储过程常见问题是“创建成功但调用报错”。先检查权限当前用户需要有CREATE ROUTINE和EXECUTE权限。再检查sql_mode如果开启了ONLY_FULL_GROUP_BY某些统计 SQL 会报错。调试时可以在存储过程里加SELECT输出中间变量但生产环境记得删掉。注意存储过程一旦创建修改需要DROP PROCEDURE再重建或者用ALTER PROCEDURE改特性但不能改 body。所以版本管理要跟上别直接在生产库上改。4. 事务、索引与锁课外拓展里最容易翻车的三块4.1 事务处理别让“部分成功”变成脏数据事务处理对应热搜里的“mysql事务处理”。课外拓展里通常会用一个转账例子A 扣钱、B 加钱两步必须一起成功或一起失败。MySQL 里用START TRANSACTION、COMMIT、ROLLBACK控制。START TRANSACTION; UPDATE account SET balance balance - 100 WHERE user_id 1; -- 检查余额是否足够如果不够就回滚 -- 这里假设应用层已经判断过数据库层再兜底 UPDATE account SET balance balance 100 WHERE user_id 2; COMMIT;如果中间任何一步失败执行ROLLBACK。InnoDB 默认开启 autocommit所以显式事务必须用START TRANSACTION包起来。隔离级别默认是REPEATABLE READ能避免脏读和不可重复读但幻读需要靠间隙锁解决。4.2 索引创建之前先看执行计划“mysql创建索引”是高频操作但索引不是越多越好。每个索引都会增加写入成本。创建之前先用EXPLAIN看查询走了什么索引。-- 查看查询执行计划 EXPLAIN SELECT * FROM student_course WHERE student_id 100; -- 如果 type 是 ALL说明全表扫描考虑加索引 CREATE INDEX idx_student_id ON student_course(student_id);联合索引要注意最左前缀原则。比如idx_a_b_c (a, b, c)查询条件里必须有a才能用上这个索引。如果只查b和c索引不生效。课外拓展里常考的“mysql排序”也和索引有关ORDER BY的字段如果和索引顺序一致可以避免 filesort。4.3 锁的分类读锁、写锁、间隙锁“mysql锁的分类”在面试里出现频率很高。按粒度分表锁、行锁、间隙锁。按模式分共享锁S、排他锁X。InnoDB 的行锁是加在索引上的如果查询没走索引行锁会升级成表锁这是很多“锁等待超时”的根因。-- 查看当前锁等待情况 SELECT * FROM performance_schema.data_lock_waits; -- 查看当前事务 SELECT * FROM information_schema.INNODB_TRX;如果发现大量锁等待先看INNODB_TRX里有没有长时间未提交的事务。常见原因是应用层开了事务但忘了 commit或者做了全表 update 没走索引。5. 避坑与排查那些课外拓展不会写但一定会遇到的事5.1 现象存储过程创建成功调用报“Table doesnt exist”原因存储过程里用了动态 SQL 拼表名但表名带了反引号或者库名没指定。解决在CONCAT里用反引号包住表名并且确保TABLE_SCHEMA限定正确。如果跨库要写db_name.table_name。5.2 现象事务回滚了但自增 ID 还是跳了原因InnoDB 的自增计数器在事务回滚后不会回退这是设计行为。解决不要依赖自增 ID 的连续性。如果业务需要连续编号用单独的序列表加行锁生成。5.3 现象加了索引查询反而变慢原因索引选择性太低比如性别字段只有男/女两个值优化器觉得全表扫描更快。或者索引字段参与了函数运算比如WHERE DATE(create_time) 2024-01-01索引失效。解决用EXPLAIN确认选择性低的字段不要单独建索引函数运算改成范围查询。5.4 现象net start mysql启动失败原因对应热搜里“net start mysql mysql 服务无法启动”。常见原因是 my.ini 配置路径错误、端口被占用、或者 data 目录权限不对。解决先看错误日志Windows 下在data目录找.err文件。如果是端口冲突改port参数如果是权限问题用管理员身份运行 cmd。5.5 现象docker 安装 mysql 后连不上原因对应热搜里“docker安装mysql失败”“访问docker容器内的mysql”。常见原因是容器内 MySQL 只监听了 localhost或者端口映射写错。解决启动容器时加-p 3306:3306并且确保 MySQL 用户允许从%主机连接。如果还是不行进容器docker exec -it mysql bash用mysql -uroot -p本地登录测试。6. 把课外拓展变成自己的东西一个验证习惯和一条进阶路线课外拓展的价值不在于多背几个定义而在于建立“设计-验证-调优”的闭环。我自己的习惯是每建一张表先画 E-R 图再检查 3NF然后写两条反向查询验证约束。每写一个存储过程先在测试库跑一遍用SHOW PROCEDURE STATUS确认创建成功再用CALL调用看结果。每加一个索引先用EXPLAIN看 type 从 ALL 变成 ref 或 range再决定要不要保留。进阶路线可以这样走先把单表 CRUD 和索引玩熟再练多表 join 和子查询然后上存储过程、事务和锁最后接触性能调优和主从复制。热搜里“mysql性能调优”“mysql面试题”这些方向本质上都是这条路线上的节点。别跳过 E-R 图和范式直接去背调优参数那样背了也记不住。提示课外拓展里的题目最好在本地 mysql 8.0 或者 5.7.44 上实际跑一遍。看别人写和自己跑通中间差着至少三次报错。我踩过最深的坑是早期建表从不画 E-R 图结果一个订单系统改了七次表结构每次都要写数据迁移脚本。后来强迫自己先画图再建表改结构次数直接降到一两次。希望帮到你。本文还有配套的精品资源点击获取
 与 MockCache 隔离缓存依赖的完整实践)
