数据库系统原理实践：以MySQL为例的课程实验包操作指南

简介华中科技大学数据库系统原理实践课程以MySQL为例的配套实验资源包适合正在学习数据库原理的本科生、研究生以及需要系统梳理MySQL实践的开发者。资源围绕课程全部核心实验组织既有数据库/表定义、完整性约束、数据查询、增删改操作等基础内容也覆盖视图、存储过程、触发器、并发控制与隔离级别、备份恢复、安全性控制及数据库应用开发等进阶专题。压缩包共92个文件整体仅1.42MB以62个SQL脚本为主辅以Java、C源码、Shell备份脚本、ER图设计文件drawio/mwb/png、Word报告模板及PDF任务书便于按需查阅和对照学习。目前已有67人学习使用适合同步跟做课程实验或考前完整复盘。资源包目录按实验关卡的序号命名定位清晰包含B树索引实现、金融场景SQL查询等具有一定挑战的案例以及完整任务书和报告模板可帮助理解数据库底层机制和设计方法也能直接用于实验报告的撰写与提交。总之是一份针对性强、结构完整的课程实践参考资料。1. 数据库系统原理实践以MySQL为例的课程包值得照着做一遍吗拿到一份名为“数据库系统原理实践 - 以MySQL为例”的课程资料压缩包第一反应往往是先解压看目录。这类实践包和网上零散的 SQL 教程最大的区别在于它是按数据库原理课的知识点组织的从 ER 模型到范式、事务、索引每一项都对应可运行的任务逼着你在 MySQL 里把原理课上学过的概念重新验证一遍。对正在做课程设计的学生来说它能直接告诉你实验报告里每个小节该输出什么对想补数据库实操的开发者它也是一条现成的练习路径。别指望靠背 SQL 语句过关——实践课的评分点几乎都在“能不能把原理讲清楚”和“能不能让数据库按你的设计跑起来”这两件事上而这恰好是这类以 MySQL 为例的实践资料最花力气的地方。2. 拿到课程实践包先做什么环境选型与最小化跑通2.1 为什么课程实践普遍选 MySQL选型理由与边界数据库原理课程的教材和实验指导书绝大多数把默认数据库定在 MySQL 上。这不是偶然而是因为 MySQL 的语法和 SQL 标准贴合度好课程里讲的关系代数、嵌套查询、分组聚合都能直接对应到具体语句上。用 Oracle 或 PostgreSQL 不是不行但实验指导书里的验证脚本往往没有为它们做适配学生在环境差异上消耗的时间会远超做实验本身。MySQL 的 InnoDB 存储引擎默认支持事务和外键这刚好覆盖了数据库原理课的核心实验点完整性约束、事务 ACID、并发控制。MyISAM 引擎虽然读快但既不支持事务也不支持外键课程实践里基本不会选它。需要说明的是边界MySQL 在复杂窗口函数、部分高级优化能力上弱于 PostgreSQL但本科阶段的课程实践深度MySQL 的能力已经绰绰有余。2.2 最小化安装与初始化两条命令和两个必调参数不同系统的安装命令不同但思路一致装好服务端确认服务启动然后做安全初始化。这里给 Debian/Ubuntu 和 RHEL/CentOS 两个常见分支# Debian / Ubuntu 系 sudo apt update sudo apt install -y mysql-server # RHEL / CentOS 系 sudo yum install -y mysql-server sudo systemctl start mysqld sudo systemctl enable mysqld装完后执行安全初始化向导注意两个参数。第一个是密码强度校验插件课程环境可以关掉否则设一个像123456这样的弱密码会被直接拒绝平白给自己添堵。第二个是 root 的认证插件Ubuntu 上默认走auth_socket表现为sudo mysql能进、mysql -u root -p却报 Access deniedCentOS 上安装时会生成临时密码需要先看日志再改密码。如果登录方式不对后续所有实践步骤都推不下去。# 检查服务状态 systemctl status mysql # 用 root 进入 MySQL 交互终端 sudo mysql -u root进入后立即确认版本和当前认证方式这一步相当于给整个实践过程定基线SELECT VERSION(); SELECT user, host, plugin FROM mysql.user WHERE userroot;2.3 导入样例库命令行导入与两个失败分支课程实践包的压缩包里SQL 脚本是核心资源一般会按章节组织建库脚本、建表脚本、插入数据脚本、查询练习脚本。常见做法是先用命令行把整个库建起来再逐步执行单独的练习脚本。最小化导入只需要两步# 创建数据库并指定默认字符集 mysql -u root -p -e CREATE DATABASE IF NOT EXISTS school DEFAULT CHARSET utf8mb4; # 把课程包里的 init.sql 导入 school 库 mysql -u root -p school /path/to/course_package/init.sql-u指定用户-p表示需要密码把文件内容重定向给 mysql 客户端执行。如果 init.sql 里已经包含CREATE DATABASE第一步可以跳过但显式创建能让你自己控制库名和字符集避免脚本里的库名和你的预期不一致。导入失败时先看两个地方错误码是1049说明库不存在1146说明表不存在如果是1366或1265说明某一列的数据在严格模式下被拒绝最常见的原因就是字符集或日期格式不兼容。另一个常见做法是在 mysql 客户端里执行source /path/to/init.sql效果和重定向一样好处是能实时看到每条语句的报错。导入完成后做一次快速验证确认表和数据都进来了mysql -u root -p -e USE school; SHOW TABLES; SELECT COUNT(*) FROM student;SHOW TABLES列出所有表COUNT(*)是最廉价的数据完整性检查。如果显示 0 条或直接报错回 2.3 的导入步骤排查不要急着往下走。2.4 验证连接用一条 SQL 和一个 Python 脚本确认环境可用环境验证分两层mysql 客户端能连只是第一层后面课程实践如果要写小型应用还会用到编程语言连接 MySQL。这里给一个典型的 Python 连接验证脚本用 pymysqlimport pymysql conn pymysql.connect( host127.0.0.1, userroot, passwdyour_password, dbschool, charsetutf8mb4 ) cursor conn.cursor() cursor.execute(SHOW TABLES) for row in cursor.fetchall(): print(row) conn.close()host写 127.0.0.1 而不是 localhost能绕开部分系统上 socket 连接的权限差异charset必须显式指定 utf8mb4否则查询中文结果可能出现乱码。这一步跑通后后面无论是做课程报告里的应用演示还是做期末的小型系统都不会在连接层卡住。3. 核心实验一从 ER 模型到物理表——建库建表与完整性约束3.1 把需求翻译成表结构ER 图到关系模式的三个步骤课程实践通常从一个小型业务场景开始比如学生选课管理系统。这类场景的 ER 图高度相似核心是三个实体和两个联系学生、课程、选课学生和课程之间是 M:N 联系。从 ER 图到关系模式我一般按三步走。第一步每个实体转成一张表实体的属性就是字段。第二步根据联系的基数决定外键怎么放1:N 的联系把“一”方的主键放到“多”方做外键M:N 联系则必须单独拆出一张中间表这张表的主键通常是两个外键的联合。第三步把每张表过一遍范式看有没有部分依赖和传递依赖比如选课表的成绩只依赖联合主键不存在只依赖学号或只依赖课程号的字段这就是 2NF。这里最容易翻车的点是把 M:N 联系省略成在课程表里加一个学号字段。这样做会大量冗余插入、修改、删除的异常在实验报告里根本圆不回来。课程答辩时导师最常追问的就是“你这个外键为什么放在这张表里设计依据是什么”如果答不出基数分析和范式推导表建的再漂亮也要扣分。3.2 建库建表 SQL主键、外键、唯一约束的落地写法以学生选课为例标准的建表语句如下注意约束和存储引擎的写法CREATE DATABASE IF NOT EXISTS school DEFAULT CHARSET utf8mb4; USE school; CREATE TABLE student ( sno CHAR(9) NOT NULL COMMENT 学号, sname VARCHAR(20) NOT NULL COMMENT 姓名, sdept VARCHAR(20) DEFAULT 计算机系 COMMENT 系别, PRIMARY KEY (sno) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT学生表; CREATE TABLE course ( cno CHAR(4) NOT NULL COMMENT 课程号, cname VARCHAR(40) NOT NULL COMMENT 课程名, credit TINYINT UNSIGNED DEFAULT 2 COMMENT 学分, PRIMARY KEY (cno) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT课程表; CREATE TABLE sc ( sno CHAR(9) NOT NULL COMMENT 学号, cno CHAR(4) NOT NULL COMMENT 课程号, grade DECIMAL(5,2) DEFAULT NULL COMMENT 成绩, PRIMARY KEY (sno, cno), CONSTRAINT fk_sc_sno FOREIGN KEY (sno) REFERENCES student(sno) ON DELETE CASCADE, CONSTRAINT fk_sc_cno FOREIGN KEY (cno) REFERENCES course(cno) ON DELETE CASCADE ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT选课表;参数说明是课程报告里必须写清楚的部分。CHAR(9)是定长字符串9 表示 9 个字符学号长度固定用 CHAR姓名长度不固定用VARCHAR(20)更省空间。credit TINYINT UNSIGNED表示学分是非负小整数范围 0 到 255够用且省空间。DECIMAL(5,2)表示成绩最多 5 位小数 2 位可以精确存 0 到 999.99不会出现浮点误差。ENGINEInnoDB是必须写的只有 InnoDB 支持事务和外键。ON DELETE CASCADE表示删除学生时自动删除该学生的选课记录避免出现悬空外键。PRIMARY KEY (sno, cno)是联合主键这也是中间表的标准写法保证同一学生对同一门课程只能有一条选课记录。3.3 表结构设计错了怎么办ALTER TABLE 补齐约束的三种场景建表时漏了外键是很常见的事特别是先建表后导入数据的情况。不要删表重建用 ALTER TABLE 补约束-- 场景一给已有选课表补外键 ALTER TABLE sc ADD CONSTRAINT fk_sc_sno FOREIGN KEY (sno) REFERENCES student(sno); -- 场景二给课程名加唯一约束 ALTER TABLE course ADD UNIQUE KEY uk_cname (cname); -- 场景三修改字段长度注意会造成全表重建 ALTER TABLE student MODIFY sname VARCHAR(30) NOT NULL;ADD CONSTRAINT用来加约束并命名命名规范通常是fk_表名_字段名方便后面根据约束名删除。UNIQUE KEY创建唯一索引同时起到唯一约束的效果课程里讲“唯一性约束”时对应的就是这条语句。MODIFY修改字段定义时不保留原 COMMENT所以改写时要连同COMMENT一起写全否则注释会丢。需要强调的是MODIFY在表数据量大时会锁表重建课程实践里表就几十行无所谓但如果是线上大表这种操作要放到低峰期执行。实验报告里可以主动写一句“修改字段类型需评估对现有数据的影响”这句话能让报告显得更专业。3.4 数据填充与 CHECK 约束MySQL 版本差异是个玄学往表里插数据时CHECK 约束是个容易踩坑的点。MySQL 在 8.0.16 之前并不会真正执行 CHECK 约束语句能解析但不会拦截非法数据。8.0.16 之后才正式生效-- 低版本CHECK 会被解析但不会真正拦截 CREATE TABLE sc_test ( sno CHAR(9) NOT NULL, grade DECIMAL(5,2) CHECK (grade BETWEEN 0 AND 100) ); -- 插一条明显非法的数据8.0.16 之前的版本能插进去 INSERT INTO sc_test (sno, grade) VALUES (202300001, 120); -- 8.0.16 之后的版本这条 INSERT 会直接被拒绝检查约束是否生效最直接的办法是用SHOW CREATE TABLE sc_test看表定义。如果输出里没有 CHECK 子句说明约束根本没被识别。低版本环境下更可靠的方案是在应用层做校验或者用触发器实现等价逻辑。课程报告里如果写了 CHECK 约束一定要注明当前 MySQL 版本是否真正支持否则答辩时被问到“低版本怎么保证数据合法性”会很难收场。4. 核心实验二事务、隔离级别与索引——原理课考点的实操映射4.1 事务 ACID 在 MySQL 里怎么观察原理课上讲事务 ACID 特性讲得再清楚也不如在终端里亲眼看到一次回滚。事务实验的标准动作是在一个会话里开事务、改数据、查询验证再决定提交还是回滚START TRANSACTION; UPDATE sc SET grade grade 5 WHERE sno 202300001; SELECT * FROM sc WHERE sno 202300001; -- 此时数据只在当前会话可见 ROLLBACK;START TRANSACTION显式开启一个事务之后执行的 DML 操作不会立即生效直到COMMIT或ROLLBACK。ROLLBACK会把数据恢复到事务开始前的状态。这里的核心观察点是执行 UPDATE 后不提交立即打开另一个终端查询同一条数据看不到修改。这个现象就是对事务隔离性最直观的验证课程报告里要记录的就是这类“眼见为实”的证据。另一个实用的观察命令是SHOW ENGINE INNODB STATUS它会输出当前 InnoDB 引擎的运行状态包括活跃事务列表、锁等待信息。事务卡死或锁冲突时可以用它定位是哪一条事务占了锁。输出内容很长实验报告里截取事务相关的段落即可不要整段贴上去。4.2 四个隔离级别用两个终端演示脏读与不可重复读隔离级别是原理课的重点也是实践中最容易演示出效果的知识点。标准做法是用两个终端连接同一个库一个终端改数据不提交另一个终端在指定隔离级别下查询。先看脏读-- 终端 A把隔离级别调到最低 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT * FROM sc WHERE sno 202300001;-- 终端 B开启事务修改成绩但不提交 START TRANSACTION; UPDATE sc SET grade 90 WHERE sno 202300001; -- 切回终端 A 查询终端 A 在READ UNCOMMITTED下能查到终端 B 未提交的 90 分这就是脏读。把隔离级别换成READ COMMITTED再重复一遍终端 A 查到的还是旧值直到终端 B 提交后才能看到 90。READ COMMITTED解决了脏读但会出现不可重复读同一个事务里两次相同查询结果不一致。演示方法是终端 A 开启事务后查询一次终端 B 提交修改终端 A 再查询一次两次结果不同。这就是课程实践里最常用的“双终端对照法”实验报告里把四个隔离级别各跑一遍记录每个级别下脏读、不可重复读、幻读是否出现对照原理课的理论表格完整性比单纯背定义强得多。需要注意MySQL 默认隔离级别是REPEATABLE READ演示之前先执行SELECT transaction_isolation确认当前位置避免结果和预期对不上。4.3 EXPLAIN 看执行计划索引失效的三种典型场景索引实验的核心工具是EXPLAIN。它不会真正执行查询而是让优化器把执行计划输出给你是判断一条 SQL 有没有走索引的黄金手段EXPLAIN SELECT * FROM student WHERE sno 202300001;重点关注type和key两列type为const或ref说明走了索引为ALL说明全表扫描key为NULL说明这条 SQL 没用上任何索引。课程实践里最常见的三个索引失效场景-- 场景一隐式类型转换字符串列和数字比较 EXPLAIN SELECT * FROM student WHERE sno 202300001; -- 场景二前导通配符 EXPLAIN SELECT * FROM student WHERE sname LIKE %张%; -- 场景三在索引列上做函数运算 EXPLAIN SELECT * FROM student WHERE YEAR(create_time) 2024;三条语句的type大概率都是ALLkey为NULL。sno建了主键索引但sno 202300001把字符串列和整型常量比较优化器会做类型转换导致索引失效LIKE %张%的前导百分号让 B 树无法定位起点YEAR(create_time)对索引列套了函数索引值被破坏。这三个场景在实验报告里值一个独立的对比表格每条 SQL 的执行计划都贴出来再用一句话解释失效原理是很好的加分项。4.4 课程报告里索引与事务部分怎么组织这两个实验点在答辩时是高频提问区报告组织建议用“现象证据 原理解释”的格式。每个实验点给出证据表格比如索引实验用如下结构查询写法type 列key 列是否走索引原因sno 202300001constPRIMARY是主键等值匹配sno 202300001ALLNULL否隐式类型转换sname LIKE %张%ALLNULL否前导通配符YEAR(create_time) 2024ALLNULL否索引列函数运算表格能让评分老师一眼看清你做了对比实验而不是只写了“索引很重要”这类空话。事务部分的报告同理四个隔离级别各跑一遍记录脏读和不可重复读的观察结果再贴关键会话截图比抄一段教材定义有用得多。5. 避坑与排查数据库实践包最常见的五个翻车现场5.1 现象插入中文变成乱码中文乱码是课程实践里出现频率最高的问题。插入的中文查出来变成问号或乱码几乎可以肯定是字符集链路不一致。原因通常是数据库、表、连接三者的字符集没有全部统一比如建库用了latin1但客户端用utf8mb4连接。解决方法是把整条链路统一到 utf8mb4。先看现状再改SHOW VARIABLES LIKE character_set_%; ALTER DATABASE school DEFAULT CHARSET utf8mb4; ALTER TABLE student CONVERT TO CHARACTER SET utf8mb4;已经插进去的乱码数据改不回来只能清掉重插。这是一个血泪经验建库建表时就把DEFAULT CHARSET utf8mb4写上连接字符串里也显式传charsetutf8mb4不要依赖默认值。5.2 现象外键建不上执行ALTER TABLE sc ADD FOREIGN KEY ...时报错常见错误码 1215 或 1005。原因通常是三个字段类型或长度不一致比如 student 表的sno是CHAR(9)sc 表里却是VARCHAR(20)两个表的存储引擎不一致一个是 InnoDB 一个是 MyISAM或者被参照表里已有违反外键约束的数据比如 sc 表里有一个学号在 student 表中不存在。解决方法是先比对两边的字段定义统一类型和字符集再确认两张表都是 InnoDB用SHOW TABLE STATUS WHERE Namesc查看 Engine 列最后清理掉孤儿数据再重新加约束。实践课里最常见的是第二种和第三种因为建表时漏写 ENGINE 参数MySQL 会走默认引擎很容易造出 MyISAM 表加外键失败。5.3 现象分组查询直接报错执行SELECT sdept, AVG(grade) FROM sc GROUP BY sdept时报错提示this is incompatible with sql_modeonly_full_group_by。原因很明确MySQL 8.0 默认开启ONLY_FULL_GROUP_BY模式SELECT 里出现的非聚合列必须出现在 GROUP BY 子句中这是对 SQL 语义的严格约束。解决方法是改 SQL而不是关 sql_mode。比如只查系别和平均分写法是SELECT sdept, AVG(grade) FROM student JOIN sc ON student.sno sc.sno GROUP BY student.sdept。如果想临时关闭严格模式做验证可以执行SET sql_mode但这只是当前会话生效而且不推荐在报告里这么写因为关掉严格模式会让很多 SQL 标准检查失效反而掩盖了问题。5.4 现象普通用户执行导入时报权限不足用非 root 账号执行mysql -u normal_user -p school init.sql时报Access denied for user。原因是 init.sql 里可能包含CREATE DATABASE或CREATE TABLE而普通用户对这些库的权限是空的。课程实践环境里最简单的做法是先用 root 把样例库建好再给普通用户授权GRANT ALL PRIVILEGES ON school.* TO normal_userlocalhost; FLUSH PRIVILEGES;school.*表示 school 库的全部对象FLUSH PRIVILEGES刷新权限表。还有一种场景是 root 用sudo mysql能进但远程连不上这是因为 root 的 host 是 localhost需要在另一台机器连库时创建root%账号或改用普通用户连接。课程实践做完后把本机权限最小化也是一项加分项说明你有安全意识。5.5 现象事务执行后数据没写进去UPDATE 或 DELETE 执行后 SELECT 能看到数据但重开终端发现数据没变。原因十有八九是忘记 COMMIT程序连接里的事务一直挂着。这个问题的隐蔽之处在于当前会话里所有查询都能看到自己事务内的修改看起来一切正常只有事务外的连接能看到真实状态。解决方法是养成“改完就提交”的习惯尤其是通过编程语言连接数据库时确认事务边界。排查时用SELECT * FROM information_schema.innodb_trx查看活跃事务如果有长时间未提交的事务先 COMMIT 或 ROLLBACK 再追查代码逻辑。在课程实践里这个问题经常出现在最后演示环节现场翻车很影响心态早点把自动提交开着或显式提交能省掉很多尴尬。6. 收尾前必做从能跑到能答辩的验证清单6.1 对照实践要求自查六个检查项提交实践报告前我一般按下面这个清单过一遍每项对不上就回去补建库建表脚本能在干净环境下一次执行成功不依赖人工干预每张表的主键、外键、唯一约束、CHECK 约束用SHOW CREATE TABLE验证四个隔离级别各跑了一遍脏读和不可重复读的观察结果有记录至少三条 SQL 的EXPLAIN执行计划贴进报告并标明各自是否走索引中文数据从插入到查询全部无乱码每个事务都明确提交或回滚没有遗留的未提交事务这六项覆盖了数据库原理实践的核心评分点完整性约束、事务、并发、索引、字符集。任何一项缺失答辩时被问到对应知识点都会很被动。6.2 把普通实验做成亮点的两个方向第一个方向是给索引实验加一张真实数据量的对比表。把样例数据用存储过程扩到十万行对比同一查询在有索引和没有索引时的耗时差异用SHOW PROFILE或客户端计时截图当证据。这个操作不复杂但能直观展示索引对查询性能的影响比单纯贴 EXPLAIN 输出更有说服力。第二个方向是做锁等待演示。两个终端同时对同一行做 UPDATE第一个事务不提交第二个事务会进入锁等待状态用SHOW ENGINE INNODB STATUS抓一段锁信息放进报告。这能让事务章节从“背概念”升级成“演示并发控制”也是答辩时最容易被认可的实验深度。我自己做这类实践课的习惯是宁可少做一个实验点也要把已做的实验点做到“能讲清楚原理 有证据截图 能回答追问”三层。一套实践做下来收获最大的不是那几个 SQL 语句而是数据一致性和约束意识这在之后写业务代码时价值很高。希望帮到你。本文还有配套的精品资源点击获取