尧图网页设计 
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 20Oracle 要写成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课程表cnocpno 指向 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) ) ENGINEInnoDB 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) ) ENGINEInnoDB 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) ) ENGINEInnoDB 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 ) ENGINEInnoDB COMMENT选课成绩表;几个设计决策需要解释一下。sc表用的是复合主键(sno, cno)而不是加一个自增 id 列。这么做的好处是从表结构层面就杜绝了同一个学生同一门课选两次这种脏数据不需要额外写业务校验。代价是主键索引比较宽86 字节但实训数据量下完全无所谓。外键的删除策略也值得说。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 JOINSELECT 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.snosc.sno AND sc.grade80会保留所有学生成绩不够的补 NULL而LEFT JOIN sc ON s.snosc.sno WHERE sc.grade80会退化成 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这里要换成NVLSQL Server 里是ISNULLPostgreSQL 里是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 EXISTSSELECT sno, sname, sdept FROM students s WHERE NOT EXISTS (SELECT 1 FROM sc WHERE sc.sno s.sno);两种写法都能出题但NOT IN要慎用因为如果子查询结果里含 NULLNOT 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_BYSELECT 含未聚合的裸字段补进 GROUP BY 或用聚合函数Unknown column字段名拼写错或别名未生效检查别名定义位置WHERE 不能用 SELECT 别名Subquery returns more than 1 row标量子查询返回多行改写成 IN 或加 LIMIT 16.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 写错了立刻发现不需要人工逐条核对结果集。等到数据量真的上来、需要处理数据库同步或者迁移时这套清晰的脚本结构也能直接复用不至于重头再来。 大学数据库创建与多表查询实战：MySQL JOIN与统计
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 20Oracle 要写成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课程表cnocpno 指向 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) ) ENGINEInnoDB 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) ) ENGINEInnoDB 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) ) ENGINEInnoDB 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 ) ENGINEInnoDB COMMENT选课成绩表;几个设计决策需要解释一下。sc表用的是复合主键(sno, cno)而不是加一个自增 id 列。这么做的好处是从表结构层面就杜绝了同一个学生同一门课选两次这种脏数据不需要额外写业务校验。代价是主键索引比较宽86 字节但实训数据量下完全无所谓。外键的删除策略也值得说。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 JOINSELECT 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.snosc.sno AND sc.grade80会保留所有学生成绩不够的补 NULL而LEFT JOIN sc ON s.snosc.sno WHERE sc.grade80会退化成 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这里要换成NVLSQL Server 里是ISNULLPostgreSQL 里是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 EXISTSSELECT sno, sname, sdept FROM students s WHERE NOT EXISTS (SELECT 1 FROM sc WHERE sc.sno s.sno);两种写法都能出题但NOT IN要慎用因为如果子查询结果里含 NULLNOT 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_BYSELECT 含未聚合的裸字段补进 GROUP BY 或用聚合函数Unknown column字段名拼写错或别名未生效检查别名定义位置WHERE 不能用 SELECT 别名Subquery returns more than 1 row标量子查询返回多行改写成 IN 或加 LIMIT 16.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 写错了立刻发现不需要人工逐条核对结果集。等到数据量真的上来、需要处理数据库同步或者迁移时这套清晰的脚本结构也能直接复用不至于重头再来。