数据库系统实验三，在很多学校的人才培养方案里都是一道分水岭。某高校软件学院的这门实验，题目看起来只是几张表、几条SQL，但真正动手后才会发现，它把关系代数、查询优化、事务控制这些课堂理论全部揉在了一起。这个实验能帮你打通从“会写SQL”到“会用数据库”的那一步，尤其是复杂查询和性能分析能力，是后续做应用开发的底层功。适合正在赶实验报告的同学，也适合想系统巩固数据库实操的人。

1. 实验全貌与设计思路拆解

1.1 实验三到底在考查什么

数据库系统实验三通常不会只让你做增删改查，而是把复杂查询、索引、事务三个模块串起来。先说复杂查询，一般会通过学生选课这种经典场景，要求你完成多表连接、子查询、分组聚合、集合运算，甚至要处理“选修了全部课程”这类关系代数除法问题。这部分考查的是你能否把自然语言描述的业务需求，准确翻译成没有逻辑漏洞的SQL语句。

索引模块是对查询性能的进一步要求。你不仅要会写查询，还要能判断一条慢查询为什么慢，并且通过建立索引、改写SQL来让它变快。这一步会涉及执行计划分析，很多同学第一次看到EXPLAIN的输出会懵，但实际上它只是把优化器的决策摊开给你看。

事务与并发控制是实验三里最容易被忽视的部分，因为不像是“写一条SQL出结果”那么直接。它需要你打开多个数据库会话，去制造脏读、不可重复读、幻读，再通过隔离级别或加锁来规避。这个过程看起来像在做实验演示，但恰恰是理解数据库一致性的关键。

设计者把这些环节放在实验三，也是循序渐进的。前面实验要求你建表、插数据、写基础查询，到实验三才开始模拟真实业务中的“统计、报表、并发更新”等场景。说白了，前两次实验是教你说单词，实验三是教你怎么写句子和段落。

1.2 数据库选型与实验环境准备

实验本身对数据库没有硬性限制，但选对工具能省很多事。我个人建议使用MySQL 8.0，原因是它语法通用、网上的资料和踩坑记录最多，而且EXPLAIN输出清晰，默认隔离级别是REPEATABLE READ，很适合做事务实验。如果你学校机房预装的是SQL Server或PostgreSQL，问题也不大，核心SQL语法基本一致，只有少量差异。

安装MySQL时，建议把默认字符集设置为utf8mb4，避免后面插入中文数据出现乱码。到这一步很多同学会疑惑：界面工具不是更直观吗？我的态度是，DataGrip、DBeaver这些工具可以用来看表结构和执行计划的图形化展示，但实验报告里要求的是能直接运行的SQL文本。所以至少一半的调试过程要在命令行或代码编辑器里完成，避免过度依赖自动补全。

我在本机一般会同时准备两种环境：一个MySQL 8.0实例用来跑查询，一个Navicat或DBeaver连接作为辅助工具。要注意的是，做事务实验时需要开多个会话窗口，图形工具通常也支持多标签页，不一定非要开多个命令行窗口。

2. 核心细节解析与实操要点

2.1 多表连接：别让结果集失控

多表连接最容易犯的错就是忘记写连接条件，导致笛卡尔积。比如三张表，学生表20条记录、课程表10条、选课表50条，如果不加任何WHERE或ON条件直接JOIN，结果集会有20乘以10乘以50等于一万行。我见过有同学统计“每门课的平均分”，就是因为JOIN条件写错，把每一个学生和每一门课都匹配了一遍，最后平均值完全不对。

写多表连接时要形成肌肉记忆：先确认需要从哪些表取字段，再画出表之间的关联键，最后在ON子句里写等值条件。对于外连接，要明确主表是哪一张。LEFT JOIN会保留左边表的全部行，RIGHT JOIN反之。自连接是难点，本质上是给同一张表起两个别名，然后当成两张不同的表来操作。比如查询“年龄比某位同学大的所有学生”，就可以把student表与自己连接。

还有一个细节，外连接加上WHERE条件时容易踩坑。举个例子，你希望列出所有学生以及他们选修某门课的成绩，如果用如下写法：

SELECT s.s_name, sc.score FROM student s LEFT JOIN sc ON s.s_id = sc.s_id WHERE sc.c_id = 1;

表面看没问题，但如果某个学生没有选任何课，sc.c_id是NULL，WHERE条件就把这行过滤掉了，最终效果跟内连接一样。正确做法是把筛选放在子查询里，或者直接作为一个派生表：

SELECT s.s_name, t.score FROM student s LEFT JOIN ( SELECT s_id, score FROM sc WHERE c_id = 1 ) t ON s.s_id = t.s_id;

这种细节在实验报告里非常加分，因为它体现的是对SQL逻辑的理解，而不是只会调API。

2.2 子查询与集合运算的易错点

子查询可以出现在SELECT子句、FROM子句和WHERE子句，每种位置都有各自规则。SELECT子句里的标量子查询必须返回一行一列，否则直接报错；FROM子句里的派生表必须有别名；WHERE子句里的IN和EXISTS要重点辨析。

很多人以为IN和EXISTS只是写法不同，性能差不多，但实验里要写出合理的查询，就必须知道它们的差异。IN适合驱动表数据量小、子查询结果集也小的场景；EXISTS更像循环匹配，适合外表行数多、子查询走索引的场景。现代优化器有时候会互相改写，但你不能依赖这一点。

更关键的是NULL的三值逻辑陷阱。以“查询没有选修任何课程的学生”为例，如果用NOT IN：

SELECT s_id, s_name FROM student WHERE s_id NOT IN (SELECT s_id FROM sc);

如果sc表中的s_id本身就是外键，一般不会有NULL，但如果子查询里混入了NULL，NOT IN会返回空集。这是因为SQL中NULL既不是真也不是假，而是未知，导致最终结果无法确定为真。稳妥方案是用NOT EXISTS：

SELECT s_id, s_name FROM student s WHERE NOT EXISTS ( SELECT 1 FROM sc WHERE sc.s_id = s.s_id );

在实验报告里可以把这两种写法及其结果都展示出来，加上一句“为避免NULL对三值逻辑的影响，推荐使用NOT EXISTS”，老师会知道你真正踩过这个坑。

集合运算方面，UNION会去重，UNION ALL不会去重且性能更好。如果题目只是要求合并两个结果集，且明确要包含重复项，就必须用UNION ALL。还有一个容易忽略的点：集合运算要求两个查询的列数相同，对应列的数据类型也要兼容，不然会报错。

2.3 索引优化：不要盲目建索引

索引优化的核心不是“建了索引查询就会快”，而是要理解B+树索引能加速什么。简单来说，B+树可以让数据库快速定位到目标数据，而不用整表扫。WHERE中的等值条件、范围条件、JOIN的关联字段、ORDER BY和GROUP BY的字段，都是适合建索引的候选。

但索引不是越多越好。每次插入、更新、删除时，数据库都要同步维护索引，索引太多写入性能就会变差。此外，如果字段区分度太低，比如性别只有“男”“女”，建了索引也可能不被优化器使用，因为它算下来全表扫描更划算。实验里通常要求你对比加索引前后的扫描行数和执行时间，所以数据量要足够大。只插几十行的话，性能差异根本看不出来。

组合索引要遵循最左前缀原则。比如建立索引(a, b, c)，它可以加速包含a条件、包含a和b条件、包含a和b和c条件的查询，但不会加速只包含b或c的查询。这好比查字典，你只能按照字母顺序从前到后翻，跳过第一个字母直接查第二个字母是行不通的。

EXPLAIN是最重要的调试工具。执行EXPLAIN SELECT ...后，重点看type列，从好到差大致是const、eq_ref、ref、range、index、ALL。还有key列表示实际用到的索引，rows列表示预估扫描行数。实验报告里可以贴上优化前后的EXPLAIN输出，再用表格对比，比写一大段文字更直观。

3. 实操过程与核心环节实现

3.1 选课系统的建表与数据准备

下面我们用一个可复现的模拟项目X来演示。表结构设计如下：学生表student、课程表course、选课表sc。选课表以(s_id, c_id)作为复合主键，并设置外键保证完整性。成绩字段score允许为空，模拟“还没考试”的情况。

CREATE DATABASE IF NOT EXISTS db_lab3 CHARACTER SET utf8mb4; USE db_lab3; CREATE TABLE student ( s_id INT PRIMARY KEY AUTO_INCREMENT, s_name VARCHAR(50) NOT NULL, s_gender ENUM('M','F'), s_age TINYINT, s_major VARCHAR(50) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE course ( c_id INT PRIMARY KEY AUTO_INCREMENT, c_name VARCHAR(100) NOT NULL, c_credit DECIMAL(3,1) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE sc ( s_id INT NOT NULL, c_id INT NOT NULL, score DECIMAL(4,1), PRIMARY KEY (s_id, c_id), CONSTRAINT fk_sc_student FOREIGN KEY (s_id) REFERENCES student(s_id), CONSTRAINT fk_sc_course FOREIGN KEY (c_id) REFERENCES course(c_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入示例数据时要注意外键顺序，先插入student和course，再插入sc。如果偷懒用工具可视化操作，后面做外键级联时会很麻烦，所以建议手工维护。

INSERT INTO student (s_name, s_gender, s_age, s_major) VALUES ('张同','M', 20, '软件工程'), ('李茜','F', 21, '数据科学'), ('王磊','M', 22, '人工智能'), ('赵欣','F', 20, '网络工程'), ('陈宇','M', 21, '软件工程'); INSERT INTO course (c_name, c_credit) VALUES ('数据库系统', 4.0), ('操作系统', 3.5), ('计算机网络', 3.0), ('数据结构', 4.0), ('软件工程', 2.5); INSERT INTO sc (s_id, c_id, score) VALUES (1, 1, 91.0), (1, 2, 82.5), (2, 1, 88.0), (2, 3, 95.0), (3, 1, 76.0), (3, 4, 68.5), (4, 5, NULL), (5, 1, 79.0), (5, 2, 86.0);

实际实验时，这些数据远远不够看索引效果。我当时写了一个存储过程，往sc表里插入了上万条随机记录，再把student和course也扩充到几百行，这样EXPLAIN的输出才有说服力。存储过程不是实验重点，但值得写在报告“附加工作”里。

3.2 典型查询题的SQL实现与解释

第一题，查询每个学生的选课数和平均分。注意使用LEFT JOIN，保证没选课的学生也能显示出来，平均分如果NULL就显示为0：

SELECT s.s_id, s.s_name, COUNT(sc.c_id) AS course_num, ROUND(AVG(COALESCE(sc.score, 0)), 2) AS avg_score FROM student s LEFT JOIN sc ON s.s_id = sc.s_id GROUP BY s.s_id, s.s_name;

这里有一个细节：COUNT(sc.c_id)只统计非NULL的选课记录，所以没选课的学生course_num会是0；而AVG(COALESCE(sc.score, 0))会把NULL当成0分计算。这个写法在业务上是否合理需要看题目要求，但实验报告里可以解释清楚。

第二题，查询每门课的选课人数、最高分、最低分。同样用LEFT JOIN列出无人选的课程：

SELECT c.c_id, c.c_name, COUNT(sc.s_id) AS stu_num, MAX(sc.score) AS max_score, MIN(sc.score) AS min_score FROM course c LEFT JOIN sc ON c.c_id = sc.c_id GROUP BY c.c_id, c.c_name;

第三题，“查询选修了数据库系统课程且成绩高于该课程平均分的学生”。我的第一版SQL是这样写的：

SELECT s.s_id, s.s_name, sc.score FROM student s JOIN sc ON s.s_id = sc.s_id JOIN course c ON sc.c_id = c.c_id WHERE c.c_name = '数据库系统' AND sc.score > ( SELECT AVG(sc2.score) FROM sc sc2 JOIN course c2 ON sc2.c_id = c2.c_id WHERE c2.c_name = '数据库系统' );

这个查询会先子查询算出平均分，再比较每条记录。如果数据量大，可以考虑用窗口函数，但实验要求基础SQL的话，子查询版本完全够用。窗口函数版本是这样：

SELECT s.s_id, s.s_name, sc.score FROM ( SELECT s_id, c_id, score, AVG(score) OVER (PARTITION BY c_id) AS avg_score FROM sc JOIN course ON sc.c_id = course.c_id WHERE course.c_name = '数据库系统' ) t JOIN student s ON t.s_id = s.s_id WHERE t.score > t.avg_score;

两个版本都能跑，但是窗口函数可读性更好，在MySQL 8.0中性能也不错。实验报告里我建议主推子查询版本，并把窗口函数作为“进一步优化”提一下，显得思考全面。

第四题是“查询选修了全部课程的学生”。这是关系代数里的除法问题。一个常用解法是统计课程总数，再按学生分组统计去重选课数，两者相等：

SELECT s.s_id, s.s_name FROM student s JOIN sc ON s.s_id = sc.s_id GROUP BY s.s_id, s.s_name HAVING COUNT(DISTINCT sc.c_id) = (SELECT COUNT(*) FROM course);

注意COUNT(DISTINCT sc.c_id)要去重，防止同一个人重复选修同一门课导致计数虚高。还有另一种“双重NOT EXISTS”写法，可以解决零除问题，但做实验时这种不使用聚合的写法更经典：

SELECT s.s_id, s.s_name FROM student s WHERE NOT EXISTS ( SELECT c.c_id FROM course c WHERE NOT EXISTS ( SELECT 1 FROM sc WHERE sc.s_id = s.s_id AND sc.c_id = c.c_id ) );

这个逻辑需要一点耐心理解：内层NOT EXISTS选出“该学生没选的课程”，外层NOT EXISTS则保证不存在这样的课程，也就是学生选了所有课。

3.3 索引优化的完整对比

索引优化题通常会给一条慢查询，要求你通过加索引或改写SQL来提升性能。我们可以拿这个查询做例子：

EXPLAIN SELECT * FROM sc WHERE score BETWEEN 70 AND 90;

没有索引时，EXPLAIN的type是ALL，rows是全部数据行。加上普通索引：

ALTER TABLE sc ADD INDEX idx_score (score);

再执行EXPLAIN，type会变成range，rows会明显减少。但要注意，如果数据量不大，或者范围覆盖的数据占比太高，优化器可能仍然选择全表扫描。这不代表索引没用，而是优化器认为访问整表比走索引回表更快。所以在实验报告里“没有优化效果”也可能是一条有效结论，关键是你能解释清楚为什么。

还有一个常见操作：在student表的s_major字段上建索引，来优化“查询某专业所有学生的选课信息”这类查询。这里有个陷阱，如果s_major的区分度不高，索引效果可能不明显。我建议用组合索引，比如(s_major, s_id)，这样既能快速定位专业，又能通过s_id再连接选课表。这个选择背后的原理就是尽量让索引覆盖查询条件，减少回表次数。

如果还想深入，可以尝试EXPLAIN ANALYZE，MySQL 8.0.18以后支持，会输出实际执行时间。但在提交实验报告之前，建议先确认老师是否接受这种高级工具，因为有些评分标准只要EXPLAIN输出。

3.4 事务与并发控制实验

事务部分需要开多个会话窗口。先演示最基础的事务提交和回滚：

-- 会话A START TRANSACTION; UPDATE sc SET score = 99 WHERE s_id = 1 AND c_id = 1; ROLLBACK; SELECT score FROM sc WHERE s_id = 1 AND c_id = 1;

如果ROLLBACK生效，查出来的分数应该还是原来的91.0。然后演示脏读。先把会话A的隔离级别设为READ UNCOMMITTED，更新后不提交；另一个会话也设为READ UNCOMMITTED，就能读到未提交的修改。等会话A回滚，那个会话再查就是旧值。这种先读到后消失的数据，就是脏读。

不可重复读需要在两个隔离级别下对比。把会话A设为READ COMMITTED，然后在同一个事务里两次查询同一条记录，中间让另一个会话修改并提交该记录，第一次读和第二次读的值不一样，这就是不可重复读。注意在MySQL默认的REPEATABLE READ下，普通SELECT是快照读，两次查询结果一致，所以这个现象必须在READ COMMITTED下演示。

幻读更复杂一些，往往涉及范围查询。比如事务A按条件SELECT ... FOR UPDATE锁住一部分记录，事务B插入一条满足条件的新记录并提交，然后事务A再查，发现自己锁住的范围内出现了本不存在的记录。这种问题通常靠间隙锁解决，但这也是死锁容易发生的地方。实验报告里只需要记录现象和结论，并不需要写太底层的东西。

锁等待的实验相对简单：会话A执行SELECT * FROM sc WHERE s_id = 1 FOR UPDATE;不提交；会话B执行同一行更新，会一直等待直到超时。用SHOW ENGINE INNODB STATUS可以看到锁等待信息。这个是加分项，显示出你真正操作过并发场景。

4. 常见问题与排查技巧实录

4.1 查询结果出现重复行

统计类查询最怕结果翻倍。有一次我写“每个学生的选课数”，用了三表连接，结果学生选了两门课就出现两行，COUNT数变成了2、4，看起来完全不对。后来我单独对sc表和student表分组查询，再对比JOIN后的明细，才发现问题出在course表被错误引入。排查重复行的思路很简单：先去掉非必要的表，确定最小表集，再逐步加表观察行数变化。如果最终确实需要多表，再考虑使用DISTINCT或者子查询来消除重复，但根本解法是理清业务关系和连接条件。

4.2 GROUP BY报错 only_full_group_by

很多同学在MySQL 5.7以上环境运行这样的SQL会报错：

SELECT s_name, COUNT(*) FROM sc JOIN student ON sc.s_id = student.s_id GROUP BY student.s_id;

因为s_name没有被包含在GROUP BY中。严格模式下，选择列表里出现的非聚合列，必须被GROUP BY子句包含，或者被聚合函数包裹。如果确定s_name函数依赖于s_id，即一个s_id只会有一个s_name，可以加ANY_VALUE(s_name)绕过。但是实验报告不建议用这个手法，因为容易掩盖逻辑问题。最稳妥的写法是把s_name也写进GROUP BY，代价是多一点分组开销，但语义清楚。

4.3 NOT IN 永远查不出结果

之前说过NOT IN和NULL的问题。实际排查时，可以先运行子查询单独检查是否有NULL：

SELECT s_id FROM sc WHERE s_id IS NULL;

如果有NULL，就会导致整个NOT IN为假。最好的替代方案是NOT EXISTS。这个坑在很多生产环境的SQL里也会遇到，实验里能自己发现并解决，说明你对三值逻辑的理解是到位的。

4.4 事务实验中的锁等待超时

事务实验中最常见的问题是“Lock wait timeout exceeded”。遇到这种提示，先别急着杀进程。用SHOW PROCESSLIST;看看哪个连接在等锁；再用SHOW ENGINE INNODB STATUS;查看最近的死锁或锁等待详情。我遇到过一次死锁，两个事务一个先更新student再更新sc，另一个先更新sc再更新student，InnoDB检测到了死锁并回滚了其中一个事务。解决方式就是让所有事务按照相同的表顺序加锁，保持事务短小，不在事务里做不必要的外部查询。

另外，锁等待超时时间默认50秒，可以在客户端设置成更短，比如5秒，这样实验演示时不用等那么久。命令是：

SET innodb_lock_wait_timeout = 5;

这个参数只对当前会话生效，做演示足够了。

4.5 字符集和排序规则导致的比较问题

中文数据最容易踩字符集坑。两个表的字符集不一致，连接时可能报错，或者比较时不相等。建议建表时都明确指定CHARSET=utf8mb4，排序规则统一用utf8mb4_general_ci或utf8mb4_unicode_ci。另外，字符串含有尾随空格也会造成比较结果异常，用LIKE或者TRIM()处理。实验里如果遇到“明明数据一样，却查不出来”，优先检查字符集和空格。

数据库系统实验三做完之后，我个人最大的收获不是背会了多少SQL语法，而是养成了一个习惯：每次查询都先EXPLAIN，每次结果不对都先怀疑连接条件。这个习惯让我在后来的很多项目里避免了不少线上事故。如果你正在做这个实验，请一定自己亲手把慢查询调优一遍，不要直接复制别人的答案。等你看到EXPLAIN里type从ALL变成ref的时候，那种感觉比拿满分还爽。