简介这份PDF面向学习MySQL数据库原理与应用的高校学生及开发者围绕网络玩具销售系统的数据库设计案例帮助读者把E-R建模、关系模式转换、第三范式规范化等抽象理论落到真实项目场景中。资源包共1个文件为649KB的PDF文档内容以图文表格形式呈现便于对照阅读与整理笔记。目前已有2366人学习下载适合作为课程配套的课外拓展练习。文档完整梳理了客户、玩具、订单、品牌、类别、国家、月销售量、接受者、运货、运价、购物车、包装等实体集及其属性并给出E-R图到关系模式的转换思路与第三范式规范化过程。同时结合索引与视图优化讲解如何为Shopper与Orders连接字段、Toys的cToyld、Category的cCategoryld建立索引并通过视图简化多表联接查询帮助读者掌握数据库性能调优与查询设计的核心方法。1. 从一份课外拓展 PDF 说起MySQL 数据库原理到底该怎么落地很多人第一次接触 MySQL是从一份课程配套的课外拓展材料开始的比如《MySQL数据库原理及应用(第2版)(微课版)-课外拓展.pdf》这类文件。标题里既有“原理”又有“应用”还带“微课版”说明它面向的是课堂之外的动手环节E-R 图怎么画、第三范式怎么判、存储过程怎么写、事务和锁怎么理解。问题是PDF 看完容易真到自己装 MySQL、建库建表、写存储过程、调性能的时候翻车点一个接一个。这篇笔记不逐页复述那份材料而是把这类课外拓展真正要练的东西拆成能复现的路径从环境搭建、E-R 图到三范式的建模、存储过程与事务、再到索引和锁的排查。适合正在学数据库原理、准备课程设计或面试以及想把课本知识落到一台真实 MySQL 上的同学。下面按“先立住原理、再动手、最后避坑”的顺序讲。2. 环境先跑通MySQL 安装配置与最小验证2.1 为什么课外拓展第一步永远是环境数据库原理课最容易脱节的地方就是课本上讲 B 树、讲事务隔离级别学生却连一个能连上的 MySQL 实例都没有。课外拓展的价值恰恰在于把抽象概念绑到一条真实连接上。所以第一步不是背范式而是让mysql客户端能连上服务端能建库、能建表、能执行 SQL 脚本。常见做法有两种Windows 上直接装官方安装包或者用 Docker 起一个容器。前者适合长期在本机练习后者适合快速试错、随时删库重来。选哪个取决于你要不要保留数据如果只是做课程实验Docker 更省心但要注意容器内外的端口和字符集。安装过程中最常被搜到的几个问题——mysql安装教程8.0、mysql在windows10上怎么安装、net start mysql mysql 服务无法启动——本质都指向同一件事服务端进程没起来或者起来了但客户端连不上。判断顺序应该是先看服务状态再看端口再看认证方式。MySQL 8.0 默认认证插件是caching_sha2_password老客户端连不上多半是这里的问题不是密码错。2.2 Windows 本地安装与初始化下面这套流程对应官方 ZIP 或 MSI 安装后的初始化命令在管理员权限的终端里执行。路径按自己实际解压位置改。# 1. 初始化数据目录生成临时 root 密码--console 把日志打到屏幕 mysqld --initialize --console # 2. 注册为 Windows 服务服务名 mysql80 mysqld --install mysql80 # 3. 启动服务 net start mysql80 # 4. 用临时密码登录立刻改密码 mysql -u root -p-- 登录后第一件事改掉临时密码 ALTER USER rootlocalhost IDENTIFIED BY YourStrongPass123!; -- 确认字符集避免中文乱码 SHOW VARIABLES LIKE character_set_server;逻辑说明--initialize会创建系统库并生成一个临时 root 密码这个密码只打印一次没记下来就只能删数据目录重来。--install把 mysqld 注册成 Windows 服务之后用net start管理。参数上character_set_server建议是utf8mb4否则存 emoji 或部分中文会出问题。如果net start mysql80报错先去看数据目录下的.err日志文件里面会写清楚是端口占用、权限不足还是数据目录已存在。2.3 Docker 方式与连接验证Docker 方式适合不想污染本机环境的场景也是很多课程实验推荐的隔离做法。# 拉取并启动一个 MySQL 8 容器映射端口和挂载数据卷 docker run -d --name mysql-lab \ -p 3306:3306 \ -e MYSQL_ROOT_PASSWORDYourStrongPass123! \ -e MYSQL_DATABASEschool \ -v mysql-lab-data:/var/lib/mysql \ mysql:8.0 # 进入容器内的 mysql 客户端 docker exec -it mysql-lab mysql -uroot -p逻辑说明-p 3306:3306把容器端口映射到本机宿主机上的客户端才能连-v挂载数据卷容器删了数据还在这是后悔药。MYSQL_DATABASEschool会自动建一个库省得手动建。参数上如果本机 3306 已被占用把前面那个数字改成 3307连接时也要跟着改。docker安装mysql失败多数是镜像没拉下来或端口冲突先docker logs mysql-lab看日志。提示无论哪种方式装完先执行SELECT VERSION();确认版本再执行SHOW DATABASES;确认能读到系统库这两条过了才算环境通了。3. 从 E-R 图到第三范式把需求翻译成表结构3.1 E-R 图不是画着好看是建表前的推演E-R 图实体-联系图在课外拓展里几乎必考但很多人把它当成美术作业。实际上它是建表前的逻辑推演先找出实体学生、课程、教师再定联系选修、讲授最后定基数一个学生选多门课一门课被多个学生选就是多对多。多对多必须拆成中间表这是后面第三范式的物理落点。画 E-R 图时我一般会先问三个问题这个实体有没有唯一标识主键两个实体之间是一对多还是多对多联系本身有没有属性比如选课有成绩把这三个问题答清楚表结构基本就出来了。3.2 第三范式的判定与建表脚本第三范式3NF的要求是在满足第二范式的基础上非主属性不传递依赖于主键。翻译成人话就是一张表里不要出现“通过 A 能推出 BB 又决定 C”的链条。典型反例是学生表里同时存学院编号和学院名称学院名称依赖学院编号学院编号依赖学号这就是传递依赖应该把学院单独拆一张表。-- 学生表只存学院编号不存学院名称 CREATE TABLE student ( student_id CHAR(10) PRIMARY KEY, name VARCHAR(50) NOT NULL, college_id INT NOT NULL, enroll_year SMALLINT DEFAULT 2024, INDEX idx_college (college_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 学院表学院名称只在这里出现 CREATE TABLE college ( college_id INT PRIMARY KEY AUTO_INCREMENT, college_name VARCHAR(100) NOT NULL UNIQUE ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 选课表多对多拆中间表成绩是联系本身的属性 CREATE TABLE enrollment ( student_id CHAR(10), course_id CHAR(8), score DECIMAL(5,2) DEFAULT 0, PRIMARY KEY (student_id, course_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;逻辑说明student表里只留college_id学院名称放college表消除了传递依赖满足 3NF。enrollment用联合主键表达多对多score是联系属性放在中间表而不是学生表或课程表。参数上DEFAULT 2024和DEFAULT 0是给字段兜底避免插入时漏值报错INDEX idx_college是为后面按学院查询做准备。字符集统一utf8mb4引擎统一 InnoDB因为要事务和行锁。3.3 建表后必须做的三项验证建完表别急着写业务 SQL先验证结构是否符合预期。第一用SHOW CREATE TABLE student;看实际建出来的定义确认索引和外键都在。第二插入几条边界数据比如enroll_year不填看默认值是否生效。第三用EXPLAIN看一条按学院查询的执行计划确认走了索引。这三步做完表结构才算立住。很多课程设计后期改表改到崩溃就是因为建表时没验证等到数据多了才发现范式没拆干净。4. 存储过程与事务把业务逻辑写进数据库4.1 存储过程适合什么、不适合什么存储过程是课外拓展里绕不开的点热搜里mysql存储过程、mysql声明存储过程、建一个统计当前库下各表数据总量的存储过程都指向同一个需求把一段常用逻辑封装在数据库里。它适合做批量统计、定时清理、复杂多表写入不适合把整个业务逻辑都塞进去因为调试难、版本管理难。我一般只在“这段逻辑需要多次调用且对性能敏感”时才用存储过程。声明时注意DELIMITER的用法否则分号会提前结束语句。4.2 写一个统计各表数据量的存储过程下面这个存储过程遍历当前库所有表统计每张表的行数是课程实验里很典型的练习。DELIMITER $$ CREATE PROCEDURE count_all_tables() BEGIN DECLARE done INT DEFAULT 0; DECLARE tname VARCHAR(64); 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(64), row_count BIGINT ); OPEN cur; read_loop: LOOP FETCH cur INTO tname; IF done 1 THEN LEAVE read_loop; END IF; SET sql CONCAT(INSERT INTO tmp_table_count SELECT , tname, , COUNT(*) FROM , tname, ); PREPARE stmt FROM sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP; CLOSE cur; SELECT * FROM tmp_table_count ORDER BY row_count DESC; END$$ DELIMITER ; CALL count_all_tables();逻辑说明游标cur从information_schema.tables里取出当前库所有基表名CONTINUE HANDLER处理游标取完的情况。因为表名不能直接当变量用所以用CONCAT拼 SQL 再PREPARE执行这是动态 SQL 的标准写法。参数上table_schema DATABASE()限定当前库table_type BASE TABLE排除视图。临时表tmp_table_count只在当前会话可见不会污染正式表。注意反引号包住表名防止表名是关键字时出错。4.3 事务与锁把 ACID 落到一条 UPDATE 上事务处理是原理课的重点也是热搜里mysql事务处理、mysql锁的分类的落点。InnoDB 默认隔离级别是REPEATABLE READ行锁加在索引上。下面用一个转账场景说明。START TRANSACTION; UPDATE account SET balance balance - 100 WHERE id 1; UPDATE account SET balance balance 100 WHERE id 2; COMMIT; -- 出错时用 ROLLBACK; 回滚逻辑说明两条 UPDATE 要么都成功要么都回滚这就是原子性。id上有主键索引所以加的是行锁而不是表锁如果WHERE条件没走索引InnoDB 会退化成锁很多行甚至全表这是性能杀手。参数上隔离级别可以用SELECT transaction_isolation;查看用SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;临时改。锁的分类里共享锁S和排他锁X是基础间隙锁Gap Lock在可重复读下防止幻读理解这些才能解释为什么有些 UPDATE 会互相等待。5. 索引、排序与性能调优让查询真的快起来5.1 索引不是越多越好先看执行计划mysql创建索引、mysql性能调优、mysql排序这几个热搜词背后是同一个问题查询慢。索引的本质是把随机 IO 变成顺序 IOB 树让范围查询和排序都能走索引。但索引会占空间、拖慢写入所以不是越多越好。判断该不该建索引先看EXPLAIN的输出type是不是ref或rangekey是不是用上了rows扫描行数大不大。-- 看一条按学院和入学年份查询的执行计划 EXPLAIN SELECT s.name, c.college_name FROM student s JOIN college c ON s.college_id c.college_id WHERE s.college_id 1 AND s.enroll_year 2024 ORDER BY s.name;逻辑说明如果student表只有idx_college那么enroll_year的过滤要在回表后做扫描行数偏多。可以考虑建联合索引(college_id, enroll_year)让两个条件都走索引。参数上联合索引遵循最左前缀原则(college_id, enroll_year)能支持只查college_id但不能支持只查enroll_year。ORDER BY s.name如果name没索引会触发 filesort数据量大时很慢。5.2 排序与分页的常见陷阱mysql排序最常见的坑是深分页LIMIT 100000, 20会先扫描前 100020 行再丢掉前 100000 行。优化思路是用游标或覆盖索引比如记住上一页最后一个 id用WHERE id last_id LIMIT 20。另一个坑是ORDER BY的字段和WHERE用的索引不一致导致既要走索引过滤又要 filesort。我一般会尽量让排序字段包含在联合索引里顺序也要和索引一致。5.3 用慢查询日志定位问题调优不能靠猜要靠慢查询日志。开启方式是在配置里设slow_query_log ON和long_query_time 1然后分析日志里出现频率高、扫描行数多的 SQL。参数上long_query_time单位是秒设 1 表示超过 1 秒就记录生产环境可以先设 0.5 再逐步收紧。定位到具体 SQL 后再用EXPLAIN看执行计划形成“日志找问题、执行计划找原因、索引找解法”的闭环。6. 避坑与排查那些让课程设计翻车的细节6.1 服务起不来先看错误日志现象net start mysql80报“服务无法启动”。原因数据目录已存在但未初始化、端口 3306 被占用、或配置文件路径写错。解决先看数据目录下的.err文件里面会写明具体原因端口占用就改my.ini里的port数据目录冲突就换一个空目录重新--initialize。6.2 中文乱码多半是字符集没统一现象插入中文后查询显示问号或乱码。原因服务端、库、表、连接四层字符集不一致。解决服务端设utf8mb4建库建表显式指定DEFAULT CHARSETutf8mb4连接串加characterEncodingutf8。四层都对齐才不会乱。6.3 存储过程创建报语法错误现象CREATE PROCEDURE执行到一半报错。原因没改DELIMITER分号提前结束了语句。解决创建前DELIMITER $$结束后DELIMITER ;改回来。另外存储过程体内每条语句都要以分号结尾这是它和普通 SQL 的区别。6.4 事务没生效可能是引擎不对现象ROLLBACK之后数据还是变了。原因表用的是 MyISAM 引擎不支持事务。解决建表时显式写ENGINEInnoDB用SHOW TABLE STATUS确认引擎。这是原理课里最容易被忽略的一条。6.5 深分页越翻越慢现象LIMIT偏移量一大查询就卡。原因数据库要扫描并丢弃前面所有行。解决改用基于游标的分页用上一页最后一条记录的 id 作为下一页起点避免大偏移量。7. 进阶技巧用 information_schema 做一次库级体检学完原理和基本操作真正拉开差距的是会不会用系统库自查。information_schema里存着所有库、表、列、索引、权限的元数据写几条查询就能给整个库做体检。比如查没有主键的表、查冗余索引、查字段类型不合理的列。这些查询在课程设计答辩和面试里都很加分因为它证明你不只是会写 CRUD还理解数据库自身的结构。-- 1. 找出当前库中没有主键的表 SELECT t.table_name FROM information_schema.tables t LEFT JOIN information_schema.table_constraints c ON t.table_name c.table_name AND c.constraint_type PRIMARY KEY AND t.table_schema c.table_schema WHERE t.table_schema DATABASE() AND t.table_type BASE TABLE AND c.constraint_name IS NULL; -- 2. 找出重复的索引前缀相同的联合索引 SELECT table_name, index_name, GROUP_CONCAT(column_name ORDER BY seq_in_index) AS cols FROM information_schema.statistics WHERE table_schema DATABASE() GROUP BY table_name, index_name HAVING COUNT(*) 1;逻辑说明第一条用左连接找table_constraints里没有主键记录的表constraint_name IS NULL就是没主键。第二条按表和索引分组列出每个索引的列组合方便人工判断是否有前缀重复。参数上DATABASE()返回当前库名换成具体库名就能查别的库。这两条查询不修改数据纯读元数据可以放心在生产环境跑。体检项查询目标常见问题主键缺失无主键的基表无法做行级复制、更新易锁全表冗余索引前缀重复的联合索引浪费空间、拖慢写入字符集非 utf8mb4 的表中文和 emoji 存储异常引擎非 InnoDB 的表不支持事务和行锁我自己的习惯是每做完一个课程设计或上线一个小库先跑一遍这几条查询把没主键的表和冗余索引清掉。这个习惯帮我省过好几次“为什么更新这么慢”的排查时间。数据库原理不是背出来的是在这些具体查询和踩坑里长出来的。希望帮到你。本文还有配套的精品资源点击获取