1. 先想清楚：这套"大学数据库"实训到底在练什么
带过几届数据库课程设计之后，我越来越确定一件事：多表查询不是一门"语法课"，而是一门"建模思维课"。标题里写的"大学数据库创建与查询实战"，表面上是让你建几张表、写几条 SQL，实际上它训练的是三件事——你能不能把一个真实教务场景抽象成关系模型、你能不能预料到数据规模上来之后查询会怎么退化、你能不能在 JOIN 写完之后一眼看出结果对不对。
很多同学第一次拿到这个实训任务，第一反应是"不就是建个学生表、课程表、成绩表吗"，然后三十分钟写完 DDL，剩下的时间全花在凑 SQL 练习题上。这套流程跑下来，成绩单能交上去，但遇到"查每门课平均分最高的那个学生"这种稍微绕一点的题，立刻就卡住。问题出在哪？出在他把表当成了孤立的表格，而不是一个有关联的整体。
我打算把这次实训从头到尾拆一遍：表怎么设计、环境怎么搭、JOIN 怎么写、统计类查询怎么组织、报错怎么排查。适合两类人看——一类是正在做数据库课程设计、需要一份能直接抄作业的方案；另一类是把 SQL 当吃饭家伙、但多表查询这块一直靠"试出来"的开发者。文中所有的建表语句、查询示例、参数取值，都是我在本地反复跑过、确认能落地的版本，你可以照搬到自己的环境里。
2. 数据库选型与环境搭建：别在第一步就给自己挖坑
2.1 MySQL 为什么是这类实训的默认答案
说到学习用数据库，选项其实不少：MySQL、PostgreSQL、SQL Server、Oracle、还有这两年热起来的达梦、人大金仓这类国产库。为什么实训场景里 MySQL 几乎是默认解？我给你几个实打实的理由。
第一是安装成本低。MySQL 8.0 在 Windows 和 Linux 上都有现成安装包，装完之后用 root 建个库就能开工，不需要额外配置监听器、实例名这些概念。对比一下 Oracle 的安装流程——建库、建监听、配 tnsnames，光环境就能耗掉半天，对于一门只想练查询的实训来说，性价比太低。第二是资料密度高，mysql join这种关键词一搜，出来的都是能直接用的例子，遇到ONLY_FULL_GROUP_BY这类报错也容易找到中文解释。
不过我得提醒一句：如果你所在的实训环境指定了达梦数据库或者 Oracle，别硬套 MySQL 的语法。分页写法的差异就很典型——MySQL 用LIMIT 10 OFFSET 20，Oracle 要写成ROWNUM嵌套或者 12c 以后的OFFSET ... FETCH，达梦则兼容了一部分 Oracle 语法。实训报告里如果要求"说明选型理由"，这一条就是很好的素材。
2.2 用 DBeaver 建库建表，比命令行顺手在哪
命令行mysql -u root -p敲 DDL 当然没问题，但实训里我更推荐用客户端工具，尤其是DBeaver这类通用数据库管理工具。原因很实际：你需要频繁地看数据、改数据、验证查询结果，图形界面里点一下就能看到表结构、外键关系、执行计划，效率比反复敲DESC table_name高得多。
具体操作路径是这样：新建连接，选 MySQL，填 host、port（默认 3306）、用户名密码，测试连接通过后保存。然后在左侧导航里右键新建数据库，字符集选utf8mb4，排序规则utf8mb4_general_ci。这里有个小细节——字符集一定要选 utf8mb4 而不是 utf8，因为 MySQL 里的utf8其实是不完整的三字节实现，存中文姓名、生僻字、甚至以后要存带表情的备注都可能出问题。排序规则里_ci表示大小写不敏感，_bin是二进制敏感，做教务系统用_ci更符合直觉，因为学号S001和s001应该被当作同一个才合理。
如果你用的是数据库自带的命令行客户端，记住一个高频操作：source /path/to/init.sql可以一次性执行整个脚本文件。我习惯把建表、建索引、插测试数据全部写进一个init.sql，换台机器一句 source 就能重建环境，比手工点鼠标可靠得多。实训报告里附上这个脚本，答辩的时候老师一看就知道你是有工程习惯的。
注意：建库语句里的库名不要和实训题目里已存在的库重名。建议用
university_lab这种带后缀的名字，避免误删或者覆盖别人的库。
2.3 一个容易忽略的初始配置
MySQL 8.0 默认开启了ONLY_FULL_GROUP_BY，这个模式会强制要求 GROUP BY 后面列出所有非聚合字段。这在规范上是对的，但做实训时经常让新手抓狂——写个SELECT sdept, COUNT(*) FROM students GROUP BY sdept没问题，可一旦写成SELECT sname, sdept, COUNT(*) ...就报错。我的建议是：别急着关掉它，先把规范写法学会，SELECT里要么放聚合函数，要么放进GROUP BY。如果实在被卡住影响进度，临时用SET SESSION sql_mode = '';关掉本会话的校验即可，但记得报告里说明原因，否则被问到会很尴尬。
3. 大学数据库的表结构设计：四张表撑起整个场景
3.1 核心实体与它们的字段规划
教务场景里最重要的一批实体是：学生、课程、选课成绩、教师。选课成绩这一张表是典型的联系表（关联表），它把学生和课程以多对多关系连起来，同时携带"成绩"这个联系本身的属性。这个建模判断，是整个实训最核心的一步。
| 表名 | 中文含义 | 主键 | 关键外键 | 记录量级（参考） |
|---|---|---|---|---|
| students | 学生表 | sno | 无 | 约 200 条 |
| courses | 课程表 | cno | cpno 指向 courses.cno | 约 30 条 |
| sc | 选课成绩表 | (sno, cno) | sno→students, cno→courses | 约 1500 条 |
| teachers | 教师表 | tno | 无 | 约 40 条 |
student 表字段设计上，我建议保留ssex（性别）、sage（年龄或出生日期）、sdept（院系）、sphone（联系电话）。为什么放sdept而不是单独建一张院系表？因为实训规模下院系信息没有其他属性可存，拆表反而增加 JOIN 成本。但如果题目明确要求"能统计每个院系的教师数量"，那就要拆出院系表，让 students 和 teachers 都外键指向它，这时候三表联查的价值才体现出来。
courses 表里有个很有意思的字段：cpno，表示这门课的先修课，它指向 courses 自己的主键。这叫自引用外键，是一个典型的一对多自关联结构。别小看它，实训里经常有"查询所有间接先修课"的题，这种题必须用自连接（self join）才能解，是考察多表查询思维的经典题型。
3.2 完整可执行的建表脚本
下面这套 DDL 是我本地跑通验证过的版本，你可以直接复制执行：
CREATE DATABASE IF NOT EXISTS university_lab DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci; USE university_lab; CREATE TABLE students ( sno CHAR(8) NOT NULL COMMENT '学号', sname VARCHAR(30) NOT NULL COMMENT '姓名', ssex ENUM('男','女') DEFAULT '男' COMMENT '性别', sage TINYINT DEFAULT NULL COMMENT '年龄', sdept VARCHAR(40) DEFAULT NULL COMMENT '所在院系', sphone VARCHAR(20) DEFAULT NULL COMMENT '联系电话', PRIMARY KEY (sno), UNIQUE KEY uk_phone (sphone) ) ENGINE=InnoDB COMMENT='学生表'; CREATE TABLE courses ( cno CHAR(6) NOT NULL COMMENT '课程号', cname VARCHAR(50) NOT NULL COMMENT '课程名', ccredit TINYINT DEFAULT NULL COMMENT '学分', tno CHAR(6) DEFAULT NULL COMMENT '授课教师号', cpno CHAR(6) DEFAULT NULL COMMENT '先修课课程号', PRIMARY KEY (cno), KEY idx_cname (cname), CONSTRAINT fk_course_cpno FOREIGN KEY (cpno) REFERENCES courses(cno) ) ENGINE=InnoDB COMMENT='课程表'; CREATE TABLE teachers ( tno CHAR(6) NOT NULL COMMENT '教师号', tname VARCHAR(30) NOT NULL COMMENT '教师姓名', ttitle VARCHAR(20) DEFAULT NULL COMMENT '职称', tdept VARCHAR(40) DEFAULT NULL COMMENT '所属院系', PRIMARY KEY (tno) ) ENGINE=InnoDB COMMENT='教师表'; CREATE TABLE sc ( sno CHAR(8) NOT NULL COMMENT '学号', cno CHAR(6) NOT NULL COMMENT '课程号', grade DECIMAL(5,2) DEFAULT NULL COMMENT '成绩', PRIMARY KEY (sno, cno), KEY idx_cno (cno), CONSTRAINT fk_sc_student FOREIGN KEY (sno) REFERENCES students(sno) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_sc_course FOREIGN KEY (cno) REFERENCES courses(cno) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB COMMENT='选课成绩表';几个设计决策需要解释一下。sc表用的是复合主键(sno, cno)，而不是加一个自增 id 列。这么做的好处是从表结构层面就杜绝了"同一个学生同一门课选两次"这种脏数据，不需要额外写业务校验。代价是主键索引比较宽（8+6 字节），但实训数据量下完全无所谓。
外键的删除策略也值得说。sc表对 students 用了ON DELETE CASCADE，意思是删除学生时自动清理他的选课记录，这在教务场景里是合理的——学生退学了，成绩单自然也不该留着。但对 courses 用的是ON DELETE RESTRICT，即课程只要有人选过就不允许删，必须先把选课记录处理掉。这个不对称是有意为之的，因为误删一门课的影响面比误删一个学生大得多，值得加一道保护。
3.3 造测试数据的两个技巧
手工INSERT几十条还行，几百条就崩溃了。我一般用两种方式：一是写一个存储过程循环插入，配合RAND()生成随机成绩；二是能拿到真实脱敏数据就用真实的，因为真实数据里的分布（比如某些热门课选课人数特别多）能暴露你在均匀数据里看不见的问题。
成绩字段我建议用DECIMAL(5,2)而不是FLOAT。原因在于浮点数在比较和求和时会有精度误差，SUM(grade)/COUNT(*)出来的平均分可能出现78.33333333333333这种鬼东西，而 DECIMAL 会老老实实给你78.33。另外grade允许为 NULL 是个重要设定——它代表"选了课但还没出成绩"，这个语义在查询里非常关键，下面讲外连接的时候会反复用到。
4. 多表查询的核心：把 JOIN 的逻辑吃透
4.1 INNER JOIN 和 LEFT JOIN 的分水岭
mysql join这个关键词下面最高频的困惑，就是搞不清 INNER JOIN 和 LEFT JOIN 什么时候用哪个。我给一个特别好记的判断法：你想要的最终结果里，是否包含"在另一张表里找不到匹配"的行？如果包含，就必须用 LEFT JOIN（或 RIGHT JOIN 换个方向写）；如果不包含，用 INNER JOIN。
举个具体例子。"查询每个学生的选课数量"——如果有个学生一门课都没选，你要不要把他列出来？如果要，用 LEFT JOIN：
SELECT s.sno, s.sname, COUNT(sc.cno) AS course_cnt FROM students s LEFT JOIN sc ON s.sno = sc.sno GROUP BY s.sno, s.sname;这里有个初学者必踩的坑：COUNT(sc.cno)和COUNT(*)在这个查询里结果不一样。COUNT(*)会统计结果集的行数，对于没选课的学生，LEFT JOIN 会补一行sc.cno为 NULL 的记录，COUNT(*)会把它算成 1，于是"没选课的学生"被统计成选了 1 门课。COUNT(sc.cno)会忽略 NULL，结果才是正确的 0。这个细节在实训报告里写出来，是非常加分的点。
再强调一个语义差异：WHERE后面加条件会先过滤再连接，ON后面加条件在 LEFT JOIN 时先连接再过滤右表，两者结果可能天差地别。比如LEFT JOIN sc ON s.sno=sc.sno AND sc.grade>80会保留所有学生（成绩不够的补 NULL），而LEFT JOIN sc ON s.sno=sc.sno WHERE sc.grade>80会退化成 INNER JOIN 的效果，把没成绩的学生全滤掉。记住这个区别，你在写多条件连接时就不会翻车。
4.2 三表及以上联查的执行顺序
实训里最常见的三表查询是"学生—选课—课程"组合，用来查某个学生的选课明细：
SELECT s.sname, c.cname, c.ccredit, sc.grade FROM students s JOIN sc ON s.sno = sc.sno JOIN courses c ON sc.cno = c.cno WHERE s.sdept = '计算机学院' ORDER BY s.sname, c.cname;写三表联查时，我的习惯是始终给每张表起别名，然后用别名.字段的方式引用列。这样做不光是好看，更重要的是避免"字段歧义"报错——如果 students 和 courses 都有cname这类同名字段，不加前缀 MySQL 会直接报Column 'xxx' is ambiguous。另外，逻辑上 MySQL 是先做s JOIN sc得到中间结果，再和courses连接，但执行顺序由优化器决定，不一定按你写的顺序来。想确认的话，在查询前面加EXPLAIN看执行计划，观察rows列的估算值，能发现一些明显的性能问题。
如果连接的表超过四张，我建议在 SQL 里加上注释说明每张表的角色，或者在报告里画一张表关系说明。不是为了好看，而是四表以上的 JOIN 一旦出结果偏差，排查起来会非常痛苦，有注释能帮你快速定位是哪一层连接出了问题。
4.3 子查询、IN、EXISTS 该怎么选
同一道查询题，往往能用子查询、IN、EXISTS、JOIN四种写法实现，这时候选哪个？我的经验是把"能不能用连接替代子查询"作为第一判断，能用 JOIN 就用 JOIN，因为 MySQL 对 JOIN 的优化相对成熟，而某些子查询在老版本里会被执行成相关子查询，每行都跑一次，性能差得离谱。
来看一道经典题：查询选修了"数据库"这门课的学生姓名。两种写法对比：
-- 写法一：子查询 + IN SELECT sname FROM students WHERE sno IN (SELECT sno FROM sc WHERE cno = (SELECT cno FROM courses WHERE cname = '数据库')); -- 写法二：JOIN SELECT DISTINCT s.sname FROM students s JOIN sc ON s.sno = sc.sno JOIN courses c ON sc.cno = c.cno WHERE c.cname = '数据库';写法二通常更快，而且结果更直观。但写法一的IN版本有个优势：可读性好，特别是当子查询层级不深时。注意写法二加了DISTINCT——因为一个学生理论上只会选一次这门课（复合主键保证了），但如果你写的查询逻辑没那么严谨，DISTINCT能兜底去重。
至于EXISTS和IN的取舍，有个流传很广但需要修正的说法："IN 适合小表，EXISTS 适合大表"。更准确的说法是：MySQL 里IN会被优化成半连接，多数情况下性能都不错；而EXISTS作为相关子查询，在外表小、内表大且有合适索引时表现好。实训作业里其实不必纠结，写IN更省事，写出来的 SQL 也更好读。
4.4 统计类查询：GROUP BY 和聚合函数的组合拳
教务场景一半以上的查询需求都是统计类的：每门课的平均分、每个院系的人数、选修人数超过 30 的课程。这类查询的固定套路是：GROUP BY 分组字段 + 聚合函数 + HAVING 过滤。
SELECT c.cno, c.cname, COUNT(sc.sno) AS 选课人数, AVG(sc.grade) AS 平均分, MAX(sc.grade) AS 最高分, MIN(sc.grade) AS 最低分 FROM courses c LEFT JOIN sc ON c.cno = sc.cno GROUP BY c.cno, c.cname HAVING COUNT(sc.sno) > 10 ORDER BY 平均分 DESC;这段 SQL 有好几个值得琢磨的地方。用LEFT JOIN是为了让没人选的课程也出现在结果里（选课人数显示为 0），如果用 INNER JOIN，冷门课就直接消失了，统计报告就不完整。AVG(sc.grade)会自动忽略 NULL，这点很贴心——选了课还没出成绩的记录不会把平均分拉低。
HAVING和WHERE的区别也要说清楚：WHERE作用于分组前的原始行，HAVING作用于分组后的聚合结果。所以WHERE COUNT(...)是语法错误，而HAVING AVG(grade) > 80才是正确写法。因为聚合函数的结果在分组完成前根本不存在，逻辑上就说不通。
5. 实训题精讲：四道典型查询逐条拆解
5.1 查询每个学生的选课门数和总学分
这道题考查的是三表联查加聚合，学分需要从 courses 表取，所以要连三张表：
SELECT s.sno, s.sname, COUNT(sc.cno) AS 选课门数, IFNULL(SUM(c.ccredit), 0) AS 已修学分 FROM students s LEFT JOIN sc ON s.sno = sc.sno LEFT JOIN courses c ON sc.cno = c.cno GROUP BY s.sno, s.sname ORDER BY 已修学分 DESC;关键点是IFNULL(SUM(c.ccredit), 0)。一个学生没选任何课时，SUM返回 NULL，直接显示在报表上很难看，用IFNULL包装成 0 更专业。如果你的实训环境是 Oracle，这里要换成NVL；SQL Server 里是ISNULL；PostgreSQL 里是COALESCE。COALESCE是 SQL 标准函数，MySQL 也支持，我一般优先用它，方便跨库移植。
还有个隐藏陷阱：如果同一个学生同一门课存在重复记录（虽然复合主键能避免），COUNT(sc.cno)会把重复算进去，所以要统计"门数"更稳妥的写法是COUNT(DISTINCT sc.cno)。实训数据干净的话无所谓，但养成这个习惯没坏处。
5.2 统计每门课程的平均分与选课人数
延续上一节的统计查询，这里加一个进阶需求：排除掉成绩全为 NULL 的课程。
SELECT c.cname, COUNT(sc.sno) AS 选课人数, ROUND(AVG(sc.grade), 1) AS 平均分 FROM courses c JOIN sc ON c.cno = sc.cno WHERE sc.grade IS NOT NULL GROUP BY c.cno, c.cname HAVING COUNT(sc.sno) >= 5 ORDER BY 平均分 DESC;ROUND(AVG(sc.grade), 1)保留一位小数，报表上更好看。这里用 JOIN 而不是 LEFT JOIN，因为我们要的是"有成绩的课程"，冷门课和没出成绩的课都该排除。WHERE sc.grade IS NOT NULL把选了但没出成绩的记录滤掉，保证平均分不掺水。这个处理和上一节不冲突，只是业务口径不同，报告里说明你的统计口径就行。
5.3 找出没选任何课的学生
这是 LEFT JOIN 的经典应用场景，也是考察你是否理解外连接的关键题：
SELECT s.sno, s.sname, s.sdept FROM students s LEFT JOIN sc ON s.sno = sc.sno WHERE sc.sno IS NULL;原理很朴素：LEFT JOIN 保留了所有学生，匹配不到选课记录的学生，右侧字段全部补 NULL，所以WHERE sc.sno IS NULL正好把他们筛出来。注意必须是IS NULL而不是= NULL，这是 SQL 里一个经典陷阱——NULL和任何值比较都返回"未知"，包括NULL = NULL，都必须用IS来判空。
另一种写法是用NOT EXISTS：
SELECT sno, sname, sdept FROM students s WHERE NOT EXISTS (SELECT 1 FROM sc WHERE sc.sno = s.sno);两种写法都能出题，但NOT IN要慎用，因为如果子查询结果里含 NULL，NOT IN会返回空结果集，这是 SQL 三值逻辑埋的坑，实训报告里可以专门写一段讨论。
5.4 查询平均分高于全校平均分的课程
这道题涉及到聚合函数嵌套，属于实训里偏难的一档：
SELECT c.cname, AVG(sc.grade) AS 课程平均分 FROM courses c JOIN sc ON c.cno = sc.cno GROUP BY c.cno, c.cname HAVING AVG(sc.grade) > (SELECT AVG(grade) FROM sc WHERE grade IS NOT NULL);注意HAVING后面接的子查询只能返回单个值（标量子查询），这里返回全校总平均分，然后逐组比较。如果写成HAVING AVG(sc.grade) > AVG(sc.grade)那就永远是 false，因为两边算的是同一个东西。这类题最容易出错的点是把"组内平均"和"全局平均"搞混，写之前先在纸上把口径理清楚。
6. 踩坑实录：多表查询最容易翻车的几种情况
6.1 笛卡尔积：结果突然从 1500 行变成 45000 行
新手最典型的翻车现场：忘了写ON条件，或者条件写错，结果执行出来几万行。比如SELECT * FROM students, sc不写连接条件，MySQL 会把 200 个学生和 1500 条选课记录做笛卡尔积，生成 30 万行。这时候别慌，先检查 JOIN 条件是否覆盖了所有表之间的关联关系——三张表连接至少需要两个关联条件，四张表至少三个，缺一个就乘积膨胀。
排查方法也简单：逐层加表。先把两张表连起来看行数对不对，再往上加第三张。别一口气写完五张表的 JOIN 然后对着异常结果发呆。
6.2 ONLY_FULL_GROUP_BY 报错的规范解法
报错信息长这样：Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column...。含义是 SELECT 里出现了没被聚合、也没在 GROUP BY 里的字段。解法有三种：最规范的是把该字段补进 GROUP BY；如果这个字段和分组字段是一一对应的（比如 cno 和 cname），可以直接用ANY_VALUE(cname)告诉 MySQL 你不在乎取哪个值；实在不想改就临时关掉这个模式，但不推荐。我建议用第一种，因为规范写法在换数据库时不会失效。
6.3 字段歧义与别名冲突
Column 'sname' in field list is ambiguous这个报错，通常发生在两个表有同名字段且没加表前缀时。解法就是给每张表起短别名，所有字段引用都带上别名前缀。另外ORDER BY 1这种按序号排序的写法，在字段多的查询里非常容易看错列，我强烈建议直接写字段名或别名。
| 报错信息关键词 | 根本原因 | 推荐解法 |
|---|---|---|
| ambiguous | 多表有同名字段未加前缀 | 全部加表别名前缀 |
| ONLY_FULL_GROUP_BY | SELECT 含未聚合的裸字段 | 补进 GROUP BY 或用聚合函数 |
| Unknown column | 字段名拼写错或别名未生效 | 检查别名定义位置（WHERE 不能用 SELECT 别名） |
| Subquery returns more than 1 row | 标量子查询返回多行 | 改写成 IN 或加 LIMIT 1 |
6.4 索引失效与慢查询初判
数据量上到几十万行之后，你会开始感受到"查询变慢"。这时候用EXPLAIN看执行计划，重点关注三个字段：type最好是ref或range，出现ALL表示全表扫描要警惕；key显示实际用到的索引；rows是预估扫描行数。常见导致索引失效的写法包括：在索引列上做函数运算（WHERE YEAR(sage)=20改成WHERE sage BETWEEN ...）、使用LIKE '%xx'前置通配符、隐式类型转换（字符串列和数字比较）。这些技巧在实训阶段用不上，但写进报告里是绝对的加分项，因为它证明你考虑过"数据规模上来之后会怎样"。
7. 交付前的一点个人经验
跑完整的实训流程下来，我最大的体会是：把建库脚本和一份数据字典当成交付物的一部分。很多人交作业只交 SQL 查询语句和几张结果截图，结果老师问"你这张表为什么这么设计""选课表为什么用复合主键"，答不上来。我自己习惯在脚本开头加一段注释，把表关系、字段含义、设计取舍写清楚，答辩时直接指着注释讲，比临场组织语言靠谱得多。
另外一个实用小技巧：把所有查询语句按难度分文件存放，比如basic_join.sql、aggregate.sql、subquery.sql，每个文件顶部用注释写清楚题目原文和你预期结果的特征（比如"预期返回 5 行，最高分为 96"）。这样跑一遍就能自动对照预期，哪条 SQL 写错了立刻发现，不需要人工逐条核对结果集。等到数据量真的上来、需要处理数据库同步或者迁移时，这套清晰的脚本结构也能直接复用，不至于重头再来。
