简介这是一份配合《数据库系统概论》课程的SQL建表与数据插入练习文档面向正在学习数据库原理与SQL基础操作的学生。PDF内完整给出了student、course、sc三张经典表的建表语句包含主键、唯一约束、默认值及外键关联并附带覆盖学生选课场景的示例插入数据可帮助读者理解关系模型、参照完整性及SC复合主键的设计思路。整个压缩包仅含1个PDF文件大小48KB轻量易用适合课前预习、实验参考或考前快速复习。文档将创建数据库、建表、插入数据与更新预修课程学分等步骤按顺序拆解并在course表插入中特别说明了外键约束下不能一次性插入的原因便于读者避开常见操作误区。目前已有1907人学习下载对初学数据库系统概论、需要动手搭建练习环境的学习者具有直接参考价值。1. 数据库系统概论里的 student、sc、course一套练习表为什么能讲透 SQL数据库系统概论课程里流传最广的一套SQL练习就是用 student、sc、course 这三张表反复折腾查询很多学校的实验课、期末复习题、甚至面试前突击都在用它。这套表的数据量不大结构却把主键、外键、多对多关系、聚合、连接、子查询这些核心概念全串起来了比去 LeetCode 刷零散题目要完整得多。网上流传的配套 PDF 往往只给题目和答案缺了建表脚本和边界情况的说明新手拿到手第一反应是“表长什么样”都不知道。我按最常见的教材结构把整套环境重建一遍从建库到验证一条龙讲清楚学生能照着跑准备 SQL 面试的能当复习提纲想自己出练习题的也能直接参考这套设计。2. 先建库再建表student、sc、course 的结构设计与建表 SQL2.1 三张表的字段设计外键怎么连、主键怎么定先看这套表的数据模型。student 表存学生基本信息course 表存课程信息sc 表是学生选课成绩表用来连接前两者。之所以需要 sc 这张中间表是因为一个学生可以选多门课一门课也可以被多个学生选学生和课程之间是多对多关系直接在一个表里加字段会造成大量数据冗余和更新异常。sc 表把学号和课程号各存一条记录再加上成绩字段就把多对多关系拆成了两个一对多关系。student 表的常见字段是学号、姓名、性别、年龄、系别主键用学号 sno。course 表的主键是课程号 cno同时会有一个 cpno 字段表示先修课程号这个字段指向课程表自身的主键形成自引用外键用来做“查先修课”这类的递归查询练习。sc 表的主键是 (sno, cno) 联合主键sno 和 cno 分别引用 student 和 course 的主键grade 字段存百分制成绩。这个设计的巧妙之处在于两个字段的联合主键能防止同一学生同一课程出现两条重复选课记录这是数据完整性约束里的典型考点。练习时还会用到 cpno 为 NULL 的“没有先修课”的记录这就把 SQL 里的 NULL 处理难题也带出来了。字段类型也要留意学号和课程号虽然是数字但常见教材把它们定义成 CHAR 而不是 INT目的是让前导零能保留也方便练习字符串比较。2.2 标准建表脚本MySQL 与 SQL Server 的语法差异很多初学者从 PDF 里抄答案时发现在自己电脑上建表总是报错原因往往是教材示例用的 SQL Server 语法而本机装的是 MySQL。两种数据库的建表语法大体相似但在字符集、自增列、外键约束这些细节上有差异。下面给出两套脚本先看 MySQL 版本这也是目前自学环境最常见的配置。CREATE DATABASE IF NOT EXISTS school DEFAULT CHARSET utf8mb4; USE school; CREATE TABLE student ( sno CHAR(9) PRIMARY KEY, sname VARCHAR(20) NOT NULL, ssex CHAR(2) DEFAULT 男, sage SMALLINT, sdept VARCHAR(20) ) ENGINEInnoDB; CREATE TABLE course ( cno CHAR(4) PRIMARY KEY, cname VARCHAR(40) NOT NULL, cpno CHAR(4), ccredit SMALLINT ) ENGINEInnoDB; CREATE TABLE sc ( sno CHAR(9) NOT NULL, cno CHAR(4) NOT NULL, grade DECIMAL(5,1), PRIMARY KEY (sno, cno), FOREIGN KEY (sno) REFERENCES student(sno), FOREIGN KEY (cno) REFERENCES course(cno) ) ENGINEInnoDB;这段脚本里几个参数要单独说明。DEFAULT CHARSET utf8mb4 是为了让中文姓名和课程名正常存储如果使用默认的 latin1 字符集插入中文时会变成乱码或者直接报错utf8mb4 比 utf8 多支持 emoji 等四字节字符对练习环境来说更保险。表引擎指定 InnoDB是因为外键约束只有 InnoDB 支持MyISAM 引擎虽然查询快但建外键时 MySQL 会静默忽略不报错也不生效这是最隐蔽的一个翻车点。sage 用 SMALLINT 而不是 INT是教材里的经典设计用来让大家理解“够用就好”的字段长度设计思路。grade 用 DECIMAL(5,1)意思是总长度 5 位、小数 1 位最高能存 999.9百分制成绩足够用而且能保留一位小数方便练习 AVG 聚合时看到非整数值。如果是 SQL Server 环境比如学校机房或者公司里的老项目建表脚本要微调。SQL Server 不区分 utf8mb4默认就是在排序规则属性里处理中文自增列需要 IDENTITY 关键字CHAR 字段在 SQL Server 里默认非 Unicode插入中文要改成 NCHAR 或者设置合适的排序规则。CREATE DATABASE school; GO USE school; GO CREATE TABLE student ( sno CHAR(9) PRIMARY KEY, sname NVARCHAR(20) NOT NULL, ssex NCHAR(1) DEFAULT N男, sage SMALLINT, sdept NVARCHAR(20) ); GO CREATE TABLE course ( cno CHAR(4) PRIMARY KEY, cname NVARCHAR(40) NOT NULL, cpno CHAR(4), ccredit SMALLINT ); GO CREATE TABLE sc ( sno CHAR(9) NOT NULL, cno CHAR(4) NOT NULL, grade DECIMAL(5,1), PRIMARY KEY (sno, cno), FOREIGN KEY (sno) REFERENCES student(sno), FOREIGN KEY (cno) REFERENCES course(cno) ); GO这里把姓名、系别、课程名都换成了 NVARCHAR原因很实在SQL Server 的 VARCHAR 只能存英文字符N 前缀类型才能存中文给 ssex 字段加 DEFAULT N男 是因为 SQL Server 默认值里如果直接写中文字符常量在有些中文排序规则下会报“对象名无效”一类的怪异错误实测最稳的方式就是加 N 前缀。用了 GO 分隔批次是因为 SQL Server 的 CREATE DATABASE 之后必须 USE 切换上下文如果两个语句在同一个批次里执行后面的 CREATE TABLE 可能仍然被解析到 master 库。这块属于 SQL Server 特有的玄学问题按上面分批次写就不会出错。2.3 插入练习数据覆盖基础查询需要的典型取值建完表之后必须插入数据才能练习。数据量不用大但要覆盖边界情况NULL 成绩、先修课缺失、同系别多人、男女都有、成绩重复这些都会在后续查询练习里反复用到。下面这套插入数据是经典教材里的标准样本数值我按常用版本保持基本一致。INSERT INTO student (sno, sname, ssex, sage, sdept) VALUES (201215121, 李勇, 男, 20, CS), (201215122, 刘晨, 女, 19, CS), (201215123, 王敏, 女, 18, MA), (201215125, 张立, 男, 19, IS); INSERT INTO course (cno, cname, cpno, ccredit) VALUES (1, 数据库, NULL, 4), (2, 数学, NULL, 2), (3, 信息系统, 1, 4), (4, 操作系统, 6, 3), (5, 数据结构, 7, 4), (6, 数据处理, NULL, 2), (7, PASCAL语言, 6, 4); INSERT INTO sc (sno, cno, grade) VALUES (201215121, 1, 92), (201215121, 2, 85), (201215121, 3, 88), (201215122, 2, 90), (201215122, 3, 80), (201215123, 2, 85), (201215123, 1, NULL);这段插入脚本里有两处需要细看。course 表里课程 3 的 cpno 是课程 1课程 5 的 cpno 是课程 7形成了一个有向的“先修课链条”练习自连接时可以查“间接先修课”这类题目。sc 表里最后一条记录 grade 为 NULL表示王敏选了数据库课但还没出成绩这就是聚合函数和 WHERE 条件练习里最容易出错的地方COUNT(grade) 会忽略 NULL而 COUNT(*) 不会。INSERT 语句用多值逗号分隔语法比逐条 INSERT 效率高很多而且一旦中间某行出错整个语句会回滚不会出现“插了一半”的脏数据。数据插入后建议先跑两条验证语句确认环境可用。SELECT COUNT() FROM student; 应该返回 4SELECT COUNT() FROM sc; 应该返回 7。如果数字对不上优先检查是否漏跑了某个批次或者外键约束拦截了部分插入。3. 练习题的梯度设计从单表查询到窗口函数3.1 第一梯度单表查询、排序与去重表建好、数据插完就可以开始真正的练习。第一梯度只涉及单张表目标是练 SELECT 的基础功投影、选择、排序、去重。这类题目在面试里看着简单但很多人写出来的 SQL 在边界情况下是错误的尤其是去重和 NULL 处理。先看一个最基础的查询查所有学生的学号和姓名。这道题没有难度但能验证表结构是否正常。SELECT sno, sname FROM student;接着看一个稍微有考点的题目查询所有学生的系别。如果直接写 SELECT sdept FROM student结果里会出现重复的 CS。要得到唯一的系别列表得去重。SELECT DISTINCT sdept FROM student;DISTINCT 在这里的语义是“对查询结果整体去重”也就是说只有两行所有选中列的值都相同才合并。它作用于整个 SELECT 的投影列表而不是只作用于紧挨着的那个字段。很多人以为 SELECT DISTINCT sname, sdept 的意思是“只对 sname 去重保留 sdept”实际上它是把 (sname, sdept) 作为一个整体判断重复。这是一个高频面试题也解释了为什么同样一句 DISTINCT 在不同组合列下结果差异巨大。第二梯度之前还要过一个排序练习查全体学生名单按年龄降序排列。SELECT sno, sname, sage FROM student ORDER BY sage DESC;ORDER BY 的执行顺序在 SQL 逻辑中排在 SELECT 之后所以它可以使用 SELECT 列表里的别名比如 ORDER BY 年龄。这个顺序问题很多人混淆实际上 SQL 语句的书写顺序是 SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY但逻辑执行顺序是 FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY。搞清楚这个顺序后面理解“为什么 WHERE 里不能使用别名”才不会靠死记硬背。3.2 第二梯度多表连接、聚合与分组第二梯度是这套练习表的重头戏也是 SQL 能力分水岭。连接查询要把两张或多张表按条件拼接聚合查询则把多行汇总成一行。两者经常组合出现。最常见的连接练习查询每个学生的选课成绩需要 student 表和 sc 表通过 sno 连接。SELECT student.sno, sname, cno, grade FROM student, sc WHERE student.sno sc.sno;这是隐式连接写法教材里用得最多但实际项目里我更推荐显式 JOIN 写法结构更清晰也更容易排查漏连接条件的问题。SELECT s.sno, s.sname, sc.cno, sc.grade FROM student s JOIN sc ON s.sno sc.sno;这两条 SQL 逻辑等价但显式写法把连接条件从 WHERE 中分离出来WHERE 里只留过滤条件。这种拆分的实际意义是当查询超过三张表时隐式连接特别容易漏写一个连接条件导致产生笛卡尔积结果行数爆炸到几十万条这就是俗称的“翻车现场”排查时还不好定位。显式 JOIN 配合缩进每个连接关系一眼就能看全。连接之后接聚合查询每门课的选课人数和平均成绩。SELECT cno, COUNT(*) AS cnt, AVG(grade) AS avg_grade FROM sc GROUP BY cno;这段 SQL 的执行顺序是先 FROM sc 拿到全表GROUP BY cno 把相同课程号的记录分到一组然后 SELECT 对每组执行 COUNT 和 AVG。这里有一个关键细节AVG(grade) 会自动忽略 NULL所以王敏那条没有成绩的记录不会拉低平均值。如果想把 NULL 当作 0 参与平均就得先写 CASE WHEN grade IS NULL THEN 0 ELSE grade END 把 NULL 转换掉但这在实际业务中是否合理要看场景一般成绩表里 NULL 代表“缺考/未出分”忽略比当 0 处理更符合业务语义。再进阶一步查询选了 2 门及以上课程的学生学号。这要用 HAVING 对分组结果做过滤。SELECT sno FROM sc GROUP BY sno HAVING COUNT(*) 2;HAVING 和 WHERE 的区别必须在这里说透WHERE 在分组之前过滤行HAVING 在分组之后过滤组。同一个条件写在 WHERE 和写在 HAVING 里语义完全不同。比如查出平均成绩大于 85 的课程AVG(grade) 85 这个条件只能放在 HAVING 里因为它是“组”的特征不是“行”的特征。很多初学者把聚合条件误写在 WHERE 里报错就是因为没理解这个执行顺序。3.3 第三梯度子查询、EXISTS 与窗口函数第三梯度是进阶内容也是 PDF 练习里区分“会写”和“写得优雅”的部分。子查询分相关子查询和非相关子查询两类。非相关子查询的结果不依赖外层查询先执行一次即可相关子查询每处理一行都要执行一次子查询性能差异很大。经典题目查询选修了课程 1 且成绩高于该课程平均分的学生学号。SELECT sno FROM sc WHERE cno 1 AND grade ( SELECT AVG(grade) FROM sc WHERE cno 1 );这个子查询是典型的非相关子查询内层执行一次得到课程 1 的平均分外层再逐行比较。注意如果 AVG 结果带多位小数和外层 grade 比较时不会因类型问题出错因为 MySQL 会按 DECIMAL 精度自动比较。这道题练习的价值在于它要求先理解聚合结果是标量值然后才能把它当作 WHERE 的右值。再来看 EXISTS 子查询这也是 SQL 面试题的常驻嘉宾。查出所有选了课程 2 的学生姓名。SELECT sname FROM student s WHERE EXISTS ( SELECT 1 FROM sc WHERE sc.sno s.sno AND sc.cno 2 );EXISTS 子查询只关心“有没有返回行”不关心返回什么列所以子查询里写 SELECT 1 是业界惯例比 SELECT * 性能略优且语义清晰。这个查询是相关子查询对 student 表每一行都要执行一次子查询但数据库优化器通常会把它改写成 JOIN 或 semi-join 来提升性能。练习时可以把 EXISTS、IN、JOIN 三种写法都试一遍观察执行计划差异这是理解优化器行为的很好素材。窗口函数是近几年 SQL 面试的高频考点这套表同样能练。比如查每门课成绩排名前 2 的学生。SELECT cno, sno, grade FROM ( SELECT cno, sno, grade, ROW_NUMBER() OVER (PARTITION BY cno ORDER BY grade DESC) AS rk FROM sc WHERE grade IS NOT NULL ) t WHERE rk 2 ORDER BY cno;窗口函数的逻辑是OVER 子句定义窗口范围PARTITION BY 划分组ORDER BY 组内排序ROW_NUMBER() 在窗口内生成序号。和 GROUP BY 不同的是窗口函数不会把多行合并成一行每行仍然保留自己的身份。这个特性让“分组 top N”这类查询变得非常简洁。注意内层先 WHERE grade IS NOT NULL 过滤掉 NULL 成绩否则排序时空值会默认排最前面结果就错了。3.4 练习答案的验证方式用 EXPLAIN 看执行计划光写出能跑出结果的 SQL 不算完还得知道它是怎么跑的。数据库提供了 EXPLAIN 命令来查看执行计划这是从“会写 SQL”到“能调 SQL”的必经一步。以 MySQL 为例EXPLAIN SELECT s.sno, s.sname, sc.grade FROM student s JOIN sc ON s.sno sc.sno WHERE sc.cno 1;执行计划里最需要关注的是 type 列和 rows 列。type 显示访问表的方式从好到差依次是 system、const、eq_ref、ref、range、index、ALL。如果看到 ALL说明这条 SQL 正在做全表扫描。rows 列是预估扫描行数两张表 join 的最终扫描行数异常大时要检查外键列上有没有索引。sc 表的 sno 和 cno 列如果没建索引JOIN 就会退化成嵌套循环全表扫数据量小的时候感觉不到到了生产环境几百万行就彻底卡死了。做练习时每写完一道题都顺手把执行计划拉出来看一眼。等把 system→const→ref→range→ALL 这几档都亲眼见过一遍才算真正对 SQL 优化有了体感。到这一步student、sc、course 这套练习表的价值才算被完全榨干——它不仅教你写 SQL还教你诊断 SQL。4. 做这套练习常见的 5 个坑从建表失败到结果对不上4.1 主键重复与 NULL插入数据时的第一道坎现象执行 INSERT 语句报错提示 duplicate entry 或者不能为 NULL。原因联合主键的任意重复都会触发冲突比如向 sc 表重复插入 (201215121, 1) 这条选课记录。另外student 表的 sno 是主键插入 NULL 也会被拒绝但 sc 表的 sno 和 cno 是外键同时又是联合主键的一部分如果这两列有一个为 NULLInnoDB 对主键列有隐式 NOT NULL 约束同样报错。解决插入前先用 SELECT 确认数据是否存在。习惯性在插入脚本里加 INSERT IGNORE 或者 ON DUPLICATE KEY UPDATE 确实能跳过错误但练习场景不建议这么做因为这会掩盖数据本身的重复问题。更推荐的方法是直接面对报错通过 SELECT sno, cno, COUNT() FROM sc GROUP BY sno, cno HAVING COUNT() 1 查重复数据再决定保留哪一条。4.2 字符集与排序规则中文乱码的根源现象插入中文后查询显示乱码或者 CREATE TABLE 直接报错字符集不识别。原因MySQL 默认字符集可能是 latin1中文无法存储。SQL Server 则可能是排序规则不匹配导致数据库兼容级别或排序规则冲突。解决MySQL 建库时显式指定 DEFAULT CHARSET utf8mb4并且连接字符串里也要加上 characterEncodingutf8 或 utf8mb4。一个容易被忽略的坑是即使数据库和表的字符集都对了如果客户端连接的字符集不对插入时仍会出现乱码。先执行 SHOW VARIABLES LIKE character_set%; 看当前会话的字符集再把连接参数统一。SQL Server 那边建库时把排序规则指定为 Chinese_PRC_CI_AS 基本可以一劳永逸。4.3 外键约束与删除顺序先删 sc 还是先删 student现象执行 DELETE FROM student WHERE sno201215121; 报错提示 foreign key constraint fails。原因sc 表里有引用这个学号的记录而 sc 表的外键约束默认行为是 NO ACTION即存在子记录时不允许删除父记录。解决先删子表再删父表。正确顺序是 DELETE FROM sc WHERE sno201215121; 然后 DELETE FROM student WHERE sno201215121;。这个顺序问题的价值不仅在于完成删除操作它本身就是关系模型里“引用完整性”的绝佳教学案例。实际业务中还有一种做法是给外键加上 ON DELETE CASCADE让数据库自动删子记录但这在生产环境非常危险一不小心就级联删掉大量业务数据我一般建议显式控制删除顺序而不是依赖级联。4.4 连接条件漏写笛卡尔积的翻车现场现象多表连接查询返回的结果行数异常多比如 student 4 行和 sc 7 行连接后返回 28 行。原因连接条件没写或者写错数据库把两表做了笛卡尔积每行和另一表的每行配对。解决连接查询时先确认结果行数是否等于符合业务逻辑的行数。经验判断标准是如果多表连接没有 WHERE 或 JOIN ON 条件返回行数是各表行数乘积必然有问题。连接条件漏写时数据库不会报错因为笛卡尔积在语法上是合法的这是最让人头疼的地方。实践中写多表查询时先分别跑一遍 SELECT COUNT(*) FROM 各表估算出预期结果行数范围再执行连接查询。另外养成每张表用别名、JOIN 条件显式写在 ON 子句里的习惯能显著降低漏写概率。4.5 聚合与非聚合混用GROUP BY 的经典报错现象执行 SELECT sno, cno, AVG(grade) FROM sc GROUP BY sno; 报错提示 cno 不在 GROUP BY 子句中。原因SQL 标准要求 SELECT 列表中出现的非聚合列必须全部出现在 GROUP BY 中。这是因为分组后每组只有一行非聚合列如果不在分组键里数据库不知道该取组内哪行的值。MySQL 的 ONLY_FULL_GROUP_BY 模式默认开启所以直接报错如果关掉这个模式虽然能执行但返回的 cno 是组内随机一行结果不可预测。解决搞清楚查询意图。如果确实想查每个学生的学号和他每门课的成绩就不该用 GROUP BY如果只想查每个学生平均分SELECT 里就不该出现 cno。这个报错其实是数据库在保护你比暗中返回随机值好得多。练习时遇到这种错误先停下来理清“我要的是明细还是汇总”再动手改 SQL是最快的路径。5. 把练习成果接到真项目验证方法、索引设计与一条实用技巧5.1 用索引设计与执行计划对比来验证练习效果练习做完后建议做一个加索引前后的对比实验。以 sc 表为例在不建任何辅助索引的情况下执行按 cno 过滤的查询EXPLAIN 结果会显示 typeALL 全表扫描。然后手动加一个索引CREATE INDEX idx_sc_cno ON sc(cno);再执行同样的查询EXPLAIN 里 type 会变成 refrows 预估数明显下降。这个对比就是把前面练习里学到的东西真正用到性能优化上的关键一步。实际业务中联合索引的设计比单列索引更值得关注。sc 表最常见的查询模式是按 (sno, cno) 查成绩或者按 cno 查选课名单。如果业务偏向前者联合主键已经覆盖如果后面要频繁按 cno 查单建 idx_sc_cno 就能避免每次回表。5.2 把三张表迁移到真实业务场景边界在哪里student、sc、course 毕竟是教学模型迁移到真实项目时有三个明显的边界要注意。一是数据量生产环境单表百万行起步这套练习表没有分区、没有数据生命周期管理直接照搬会有性能问题。二是缺失审计字段真实业务表几乎都要有 create_time、update_time、操作人这几个字段教学表里完全没有上线前要补。三是没有软删除和状态字段真实业务不会物理删除记录而是用 status 字段标记有效性练习表的 DELETE 操作要改成 UPDATE status。一个比较实用的落地方式是把这三张表当作一个“最小可运行的数据模型”在公司内部做技术分享或者新人培训时用来演示慢 SQL 优化和连接查询原理。数据量小、字段简单、问题复现快比拿生产库演练安全得多。我有一个习惯每次讲 SQL 优化之前先在本地用这套表造 10 万行数据然后故意写几条不带 WHERE 的连接查询让新人亲眼看到全表扫描导致查询时间从毫秒级变成秒级再让他们自己用 EXPLAIN 定位问题。这种直观对比比讲半小时理论都有用。希望帮到你。本文还有配套的精品资源点击获取 student、sc、course 三张表讲透 SQL：建表、查询与优化
简介这是一份配合《数据库系统概论》课程的SQL建表与数据插入练习文档面向正在学习数据库原理与SQL基础操作的学生。PDF内完整给出了student、course、sc三张经典表的建表语句包含主键、唯一约束、默认值及外键关联并附带覆盖学生选课场景的示例插入数据可帮助读者理解关系模型、参照完整性及SC复合主键的设计思路。整个压缩包仅含1个PDF文件大小48KB轻量易用适合课前预习、实验参考或考前快速复习。文档将创建数据库、建表、插入数据与更新预修课程学分等步骤按顺序拆解并在course表插入中特别说明了外键约束下不能一次性插入的原因便于读者避开常见操作误区。目前已有1907人学习下载对初学数据库系统概论、需要动手搭建练习环境的学习者具有直接参考价值。1. 数据库系统概论里的 student、sc、course一套练习表为什么能讲透 SQL数据库系统概论课程里流传最广的一套SQL练习就是用 student、sc、course 这三张表反复折腾查询很多学校的实验课、期末复习题、甚至面试前突击都在用它。这套表的数据量不大结构却把主键、外键、多对多关系、聚合、连接、子查询这些核心概念全串起来了比去 LeetCode 刷零散题目要完整得多。网上流传的配套 PDF 往往只给题目和答案缺了建表脚本和边界情况的说明新手拿到手第一反应是“表长什么样”都不知道。我按最常见的教材结构把整套环境重建一遍从建库到验证一条龙讲清楚学生能照着跑准备 SQL 面试的能当复习提纲想自己出练习题的也能直接参考这套设计。2. 先建库再建表student、sc、course 的结构设计与建表 SQL2.1 三张表的字段设计外键怎么连、主键怎么定先看这套表的数据模型。student 表存学生基本信息course 表存课程信息sc 表是学生选课成绩表用来连接前两者。之所以需要 sc 这张中间表是因为一个学生可以选多门课一门课也可以被多个学生选学生和课程之间是多对多关系直接在一个表里加字段会造成大量数据冗余和更新异常。sc 表把学号和课程号各存一条记录再加上成绩字段就把多对多关系拆成了两个一对多关系。student 表的常见字段是学号、姓名、性别、年龄、系别主键用学号 sno。course 表的主键是课程号 cno同时会有一个 cpno 字段表示先修课程号这个字段指向课程表自身的主键形成自引用外键用来做“查先修课”这类的递归查询练习。sc 表的主键是 (sno, cno) 联合主键sno 和 cno 分别引用 student 和 course 的主键grade 字段存百分制成绩。这个设计的巧妙之处在于两个字段的联合主键能防止同一学生同一课程出现两条重复选课记录这是数据完整性约束里的典型考点。练习时还会用到 cpno 为 NULL 的“没有先修课”的记录这就把 SQL 里的 NULL 处理难题也带出来了。字段类型也要留意学号和课程号虽然是数字但常见教材把它们定义成 CHAR 而不是 INT目的是让前导零能保留也方便练习字符串比较。2.2 标准建表脚本MySQL 与 SQL Server 的语法差异很多初学者从 PDF 里抄答案时发现在自己电脑上建表总是报错原因往往是教材示例用的 SQL Server 语法而本机装的是 MySQL。两种数据库的建表语法大体相似但在字符集、自增列、外键约束这些细节上有差异。下面给出两套脚本先看 MySQL 版本这也是目前自学环境最常见的配置。CREATE DATABASE IF NOT EXISTS school DEFAULT CHARSET utf8mb4; USE school; CREATE TABLE student ( sno CHAR(9) PRIMARY KEY, sname VARCHAR(20) NOT NULL, ssex CHAR(2) DEFAULT 男, sage SMALLINT, sdept VARCHAR(20) ) ENGINEInnoDB; CREATE TABLE course ( cno CHAR(4) PRIMARY KEY, cname VARCHAR(40) NOT NULL, cpno CHAR(4), ccredit SMALLINT ) ENGINEInnoDB; CREATE TABLE sc ( sno CHAR(9) NOT NULL, cno CHAR(4) NOT NULL, grade DECIMAL(5,1), PRIMARY KEY (sno, cno), FOREIGN KEY (sno) REFERENCES student(sno), FOREIGN KEY (cno) REFERENCES course(cno) ) ENGINEInnoDB;这段脚本里几个参数要单独说明。DEFAULT CHARSET utf8mb4 是为了让中文姓名和课程名正常存储如果使用默认的 latin1 字符集插入中文时会变成乱码或者直接报错utf8mb4 比 utf8 多支持 emoji 等四字节字符对练习环境来说更保险。表引擎指定 InnoDB是因为外键约束只有 InnoDB 支持MyISAM 引擎虽然查询快但建外键时 MySQL 会静默忽略不报错也不生效这是最隐蔽的一个翻车点。sage 用 SMALLINT 而不是 INT是教材里的经典设计用来让大家理解“够用就好”的字段长度设计思路。grade 用 DECIMAL(5,1)意思是总长度 5 位、小数 1 位最高能存 999.9百分制成绩足够用而且能保留一位小数方便练习 AVG 聚合时看到非整数值。如果是 SQL Server 环境比如学校机房或者公司里的老项目建表脚本要微调。SQL Server 不区分 utf8mb4默认就是在排序规则属性里处理中文自增列需要 IDENTITY 关键字CHAR 字段在 SQL Server 里默认非 Unicode插入中文要改成 NCHAR 或者设置合适的排序规则。CREATE DATABASE school; GO USE school; GO CREATE TABLE student ( sno CHAR(9) PRIMARY KEY, sname NVARCHAR(20) NOT NULL, ssex NCHAR(1) DEFAULT N男, sage SMALLINT, sdept NVARCHAR(20) ); GO CREATE TABLE course ( cno CHAR(4) PRIMARY KEY, cname NVARCHAR(40) NOT NULL, cpno CHAR(4), ccredit SMALLINT ); GO CREATE TABLE sc ( sno CHAR(9) NOT NULL, cno CHAR(4) NOT NULL, grade DECIMAL(5,1), PRIMARY KEY (sno, cno), FOREIGN KEY (sno) REFERENCES student(sno), FOREIGN KEY (cno) REFERENCES course(cno) ); GO这里把姓名、系别、课程名都换成了 NVARCHAR原因很实在SQL Server 的 VARCHAR 只能存英文字符N 前缀类型才能存中文给 ssex 字段加 DEFAULT N男 是因为 SQL Server 默认值里如果直接写中文字符常量在有些中文排序规则下会报“对象名无效”一类的怪异错误实测最稳的方式就是加 N 前缀。用了 GO 分隔批次是因为 SQL Server 的 CREATE DATABASE 之后必须 USE 切换上下文如果两个语句在同一个批次里执行后面的 CREATE TABLE 可能仍然被解析到 master 库。这块属于 SQL Server 特有的玄学问题按上面分批次写就不会出错。2.3 插入练习数据覆盖基础查询需要的典型取值建完表之后必须插入数据才能练习。数据量不用大但要覆盖边界情况NULL 成绩、先修课缺失、同系别多人、男女都有、成绩重复这些都会在后续查询练习里反复用到。下面这套插入数据是经典教材里的标准样本数值我按常用版本保持基本一致。INSERT INTO student (sno, sname, ssex, sage, sdept) VALUES (201215121, 李勇, 男, 20, CS), (201215122, 刘晨, 女, 19, CS), (201215123, 王敏, 女, 18, MA), (201215125, 张立, 男, 19, IS); INSERT INTO course (cno, cname, cpno, ccredit) VALUES (1, 数据库, NULL, 4), (2, 数学, NULL, 2), (3, 信息系统, 1, 4), (4, 操作系统, 6, 3), (5, 数据结构, 7, 4), (6, 数据处理, NULL, 2), (7, PASCAL语言, 6, 4); INSERT INTO sc (sno, cno, grade) VALUES (201215121, 1, 92), (201215121, 2, 85), (201215121, 3, 88), (201215122, 2, 90), (201215122, 3, 80), (201215123, 2, 85), (201215123, 1, NULL);这段插入脚本里有两处需要细看。course 表里课程 3 的 cpno 是课程 1课程 5 的 cpno 是课程 7形成了一个有向的“先修课链条”练习自连接时可以查“间接先修课”这类题目。sc 表里最后一条记录 grade 为 NULL表示王敏选了数据库课但还没出成绩这就是聚合函数和 WHERE 条件练习里最容易出错的地方COUNT(grade) 会忽略 NULL而 COUNT(*) 不会。INSERT 语句用多值逗号分隔语法比逐条 INSERT 效率高很多而且一旦中间某行出错整个语句会回滚不会出现“插了一半”的脏数据。数据插入后建议先跑两条验证语句确认环境可用。SELECT COUNT() FROM student; 应该返回 4SELECT COUNT() FROM sc; 应该返回 7。如果数字对不上优先检查是否漏跑了某个批次或者外键约束拦截了部分插入。3. 练习题的梯度设计从单表查询到窗口函数3.1 第一梯度单表查询、排序与去重表建好、数据插完就可以开始真正的练习。第一梯度只涉及单张表目标是练 SELECT 的基础功投影、选择、排序、去重。这类题目在面试里看着简单但很多人写出来的 SQL 在边界情况下是错误的尤其是去重和 NULL 处理。先看一个最基础的查询查所有学生的学号和姓名。这道题没有难度但能验证表结构是否正常。SELECT sno, sname FROM student;接着看一个稍微有考点的题目查询所有学生的系别。如果直接写 SELECT sdept FROM student结果里会出现重复的 CS。要得到唯一的系别列表得去重。SELECT DISTINCT sdept FROM student;DISTINCT 在这里的语义是“对查询结果整体去重”也就是说只有两行所有选中列的值都相同才合并。它作用于整个 SELECT 的投影列表而不是只作用于紧挨着的那个字段。很多人以为 SELECT DISTINCT sname, sdept 的意思是“只对 sname 去重保留 sdept”实际上它是把 (sname, sdept) 作为一个整体判断重复。这是一个高频面试题也解释了为什么同样一句 DISTINCT 在不同组合列下结果差异巨大。第二梯度之前还要过一个排序练习查全体学生名单按年龄降序排列。SELECT sno, sname, sage FROM student ORDER BY sage DESC;ORDER BY 的执行顺序在 SQL 逻辑中排在 SELECT 之后所以它可以使用 SELECT 列表里的别名比如 ORDER BY 年龄。这个顺序问题很多人混淆实际上 SQL 语句的书写顺序是 SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY但逻辑执行顺序是 FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY。搞清楚这个顺序后面理解“为什么 WHERE 里不能使用别名”才不会靠死记硬背。3.2 第二梯度多表连接、聚合与分组第二梯度是这套练习表的重头戏也是 SQL 能力分水岭。连接查询要把两张或多张表按条件拼接聚合查询则把多行汇总成一行。两者经常组合出现。最常见的连接练习查询每个学生的选课成绩需要 student 表和 sc 表通过 sno 连接。SELECT student.sno, sname, cno, grade FROM student, sc WHERE student.sno sc.sno;这是隐式连接写法教材里用得最多但实际项目里我更推荐显式 JOIN 写法结构更清晰也更容易排查漏连接条件的问题。SELECT s.sno, s.sname, sc.cno, sc.grade FROM student s JOIN sc ON s.sno sc.sno;这两条 SQL 逻辑等价但显式写法把连接条件从 WHERE 中分离出来WHERE 里只留过滤条件。这种拆分的实际意义是当查询超过三张表时隐式连接特别容易漏写一个连接条件导致产生笛卡尔积结果行数爆炸到几十万条这就是俗称的“翻车现场”排查时还不好定位。显式 JOIN 配合缩进每个连接关系一眼就能看全。连接之后接聚合查询每门课的选课人数和平均成绩。SELECT cno, COUNT(*) AS cnt, AVG(grade) AS avg_grade FROM sc GROUP BY cno;这段 SQL 的执行顺序是先 FROM sc 拿到全表GROUP BY cno 把相同课程号的记录分到一组然后 SELECT 对每组执行 COUNT 和 AVG。这里有一个关键细节AVG(grade) 会自动忽略 NULL所以王敏那条没有成绩的记录不会拉低平均值。如果想把 NULL 当作 0 参与平均就得先写 CASE WHEN grade IS NULL THEN 0 ELSE grade END 把 NULL 转换掉但这在实际业务中是否合理要看场景一般成绩表里 NULL 代表“缺考/未出分”忽略比当 0 处理更符合业务语义。再进阶一步查询选了 2 门及以上课程的学生学号。这要用 HAVING 对分组结果做过滤。SELECT sno FROM sc GROUP BY sno HAVING COUNT(*) 2;HAVING 和 WHERE 的区别必须在这里说透WHERE 在分组之前过滤行HAVING 在分组之后过滤组。同一个条件写在 WHERE 和写在 HAVING 里语义完全不同。比如查出平均成绩大于 85 的课程AVG(grade) 85 这个条件只能放在 HAVING 里因为它是“组”的特征不是“行”的特征。很多初学者把聚合条件误写在 WHERE 里报错就是因为没理解这个执行顺序。3.3 第三梯度子查询、EXISTS 与窗口函数第三梯度是进阶内容也是 PDF 练习里区分“会写”和“写得优雅”的部分。子查询分相关子查询和非相关子查询两类。非相关子查询的结果不依赖外层查询先执行一次即可相关子查询每处理一行都要执行一次子查询性能差异很大。经典题目查询选修了课程 1 且成绩高于该课程平均分的学生学号。SELECT sno FROM sc WHERE cno 1 AND grade ( SELECT AVG(grade) FROM sc WHERE cno 1 );这个子查询是典型的非相关子查询内层执行一次得到课程 1 的平均分外层再逐行比较。注意如果 AVG 结果带多位小数和外层 grade 比较时不会因类型问题出错因为 MySQL 会按 DECIMAL 精度自动比较。这道题练习的价值在于它要求先理解聚合结果是标量值然后才能把它当作 WHERE 的右值。再来看 EXISTS 子查询这也是 SQL 面试题的常驻嘉宾。查出所有选了课程 2 的学生姓名。SELECT sname FROM student s WHERE EXISTS ( SELECT 1 FROM sc WHERE sc.sno s.sno AND sc.cno 2 );EXISTS 子查询只关心“有没有返回行”不关心返回什么列所以子查询里写 SELECT 1 是业界惯例比 SELECT * 性能略优且语义清晰。这个查询是相关子查询对 student 表每一行都要执行一次子查询但数据库优化器通常会把它改写成 JOIN 或 semi-join 来提升性能。练习时可以把 EXISTS、IN、JOIN 三种写法都试一遍观察执行计划差异这是理解优化器行为的很好素材。窗口函数是近几年 SQL 面试的高频考点这套表同样能练。比如查每门课成绩排名前 2 的学生。SELECT cno, sno, grade FROM ( SELECT cno, sno, grade, ROW_NUMBER() OVER (PARTITION BY cno ORDER BY grade DESC) AS rk FROM sc WHERE grade IS NOT NULL ) t WHERE rk 2 ORDER BY cno;窗口函数的逻辑是OVER 子句定义窗口范围PARTITION BY 划分组ORDER BY 组内排序ROW_NUMBER() 在窗口内生成序号。和 GROUP BY 不同的是窗口函数不会把多行合并成一行每行仍然保留自己的身份。这个特性让“分组 top N”这类查询变得非常简洁。注意内层先 WHERE grade IS NOT NULL 过滤掉 NULL 成绩否则排序时空值会默认排最前面结果就错了。3.4 练习答案的验证方式用 EXPLAIN 看执行计划光写出能跑出结果的 SQL 不算完还得知道它是怎么跑的。数据库提供了 EXPLAIN 命令来查看执行计划这是从“会写 SQL”到“能调 SQL”的必经一步。以 MySQL 为例EXPLAIN SELECT s.sno, s.sname, sc.grade FROM student s JOIN sc ON s.sno sc.sno WHERE sc.cno 1;执行计划里最需要关注的是 type 列和 rows 列。type 显示访问表的方式从好到差依次是 system、const、eq_ref、ref、range、index、ALL。如果看到 ALL说明这条 SQL 正在做全表扫描。rows 列是预估扫描行数两张表 join 的最终扫描行数异常大时要检查外键列上有没有索引。sc 表的 sno 和 cno 列如果没建索引JOIN 就会退化成嵌套循环全表扫数据量小的时候感觉不到到了生产环境几百万行就彻底卡死了。做练习时每写完一道题都顺手把执行计划拉出来看一眼。等把 system→const→ref→range→ALL 这几档都亲眼见过一遍才算真正对 SQL 优化有了体感。到这一步student、sc、course 这套练习表的价值才算被完全榨干——它不仅教你写 SQL还教你诊断 SQL。4. 做这套练习常见的 5 个坑从建表失败到结果对不上4.1 主键重复与 NULL插入数据时的第一道坎现象执行 INSERT 语句报错提示 duplicate entry 或者不能为 NULL。原因联合主键的任意重复都会触发冲突比如向 sc 表重复插入 (201215121, 1) 这条选课记录。另外student 表的 sno 是主键插入 NULL 也会被拒绝但 sc 表的 sno 和 cno 是外键同时又是联合主键的一部分如果这两列有一个为 NULLInnoDB 对主键列有隐式 NOT NULL 约束同样报错。解决插入前先用 SELECT 确认数据是否存在。习惯性在插入脚本里加 INSERT IGNORE 或者 ON DUPLICATE KEY UPDATE 确实能跳过错误但练习场景不建议这么做因为这会掩盖数据本身的重复问题。更推荐的方法是直接面对报错通过 SELECT sno, cno, COUNT() FROM sc GROUP BY sno, cno HAVING COUNT() 1 查重复数据再决定保留哪一条。4.2 字符集与排序规则中文乱码的根源现象插入中文后查询显示乱码或者 CREATE TABLE 直接报错字符集不识别。原因MySQL 默认字符集可能是 latin1中文无法存储。SQL Server 则可能是排序规则不匹配导致数据库兼容级别或排序规则冲突。解决MySQL 建库时显式指定 DEFAULT CHARSET utf8mb4并且连接字符串里也要加上 characterEncodingutf8 或 utf8mb4。一个容易被忽略的坑是即使数据库和表的字符集都对了如果客户端连接的字符集不对插入时仍会出现乱码。先执行 SHOW VARIABLES LIKE character_set%; 看当前会话的字符集再把连接参数统一。SQL Server 那边建库时把排序规则指定为 Chinese_PRC_CI_AS 基本可以一劳永逸。4.3 外键约束与删除顺序先删 sc 还是先删 student现象执行 DELETE FROM student WHERE sno201215121; 报错提示 foreign key constraint fails。原因sc 表里有引用这个学号的记录而 sc 表的外键约束默认行为是 NO ACTION即存在子记录时不允许删除父记录。解决先删子表再删父表。正确顺序是 DELETE FROM sc WHERE sno201215121; 然后 DELETE FROM student WHERE sno201215121;。这个顺序问题的价值不仅在于完成删除操作它本身就是关系模型里“引用完整性”的绝佳教学案例。实际业务中还有一种做法是给外键加上 ON DELETE CASCADE让数据库自动删子记录但这在生产环境非常危险一不小心就级联删掉大量业务数据我一般建议显式控制删除顺序而不是依赖级联。4.4 连接条件漏写笛卡尔积的翻车现场现象多表连接查询返回的结果行数异常多比如 student 4 行和 sc 7 行连接后返回 28 行。原因连接条件没写或者写错数据库把两表做了笛卡尔积每行和另一表的每行配对。解决连接查询时先确认结果行数是否等于符合业务逻辑的行数。经验判断标准是如果多表连接没有 WHERE 或 JOIN ON 条件返回行数是各表行数乘积必然有问题。连接条件漏写时数据库不会报错因为笛卡尔积在语法上是合法的这是最让人头疼的地方。实践中写多表查询时先分别跑一遍 SELECT COUNT(*) FROM 各表估算出预期结果行数范围再执行连接查询。另外养成每张表用别名、JOIN 条件显式写在 ON 子句里的习惯能显著降低漏写概率。4.5 聚合与非聚合混用GROUP BY 的经典报错现象执行 SELECT sno, cno, AVG(grade) FROM sc GROUP BY sno; 报错提示 cno 不在 GROUP BY 子句中。原因SQL 标准要求 SELECT 列表中出现的非聚合列必须全部出现在 GROUP BY 中。这是因为分组后每组只有一行非聚合列如果不在分组键里数据库不知道该取组内哪行的值。MySQL 的 ONLY_FULL_GROUP_BY 模式默认开启所以直接报错如果关掉这个模式虽然能执行但返回的 cno 是组内随机一行结果不可预测。解决搞清楚查询意图。如果确实想查每个学生的学号和他每门课的成绩就不该用 GROUP BY如果只想查每个学生平均分SELECT 里就不该出现 cno。这个报错其实是数据库在保护你比暗中返回随机值好得多。练习时遇到这种错误先停下来理清“我要的是明细还是汇总”再动手改 SQL是最快的路径。5. 把练习成果接到真项目验证方法、索引设计与一条实用技巧5.1 用索引设计与执行计划对比来验证练习效果练习做完后建议做一个加索引前后的对比实验。以 sc 表为例在不建任何辅助索引的情况下执行按 cno 过滤的查询EXPLAIN 结果会显示 typeALL 全表扫描。然后手动加一个索引CREATE INDEX idx_sc_cno ON sc(cno);再执行同样的查询EXPLAIN 里 type 会变成 refrows 预估数明显下降。这个对比就是把前面练习里学到的东西真正用到性能优化上的关键一步。实际业务中联合索引的设计比单列索引更值得关注。sc 表最常见的查询模式是按 (sno, cno) 查成绩或者按 cno 查选课名单。如果业务偏向前者联合主键已经覆盖如果后面要频繁按 cno 查单建 idx_sc_cno 就能避免每次回表。5.2 把三张表迁移到真实业务场景边界在哪里student、sc、course 毕竟是教学模型迁移到真实项目时有三个明显的边界要注意。一是数据量生产环境单表百万行起步这套练习表没有分区、没有数据生命周期管理直接照搬会有性能问题。二是缺失审计字段真实业务表几乎都要有 create_time、update_time、操作人这几个字段教学表里完全没有上线前要补。三是没有软删除和状态字段真实业务不会物理删除记录而是用 status 字段标记有效性练习表的 DELETE 操作要改成 UPDATE status。一个比较实用的落地方式是把这三张表当作一个“最小可运行的数据模型”在公司内部做技术分享或者新人培训时用来演示慢 SQL 优化和连接查询原理。数据量小、字段简单、问题复现快比拿生产库演练安全得多。我有一个习惯每次讲 SQL 优化之前先在本地用这套表造 10 万行数据然后故意写几条不带 WHERE 的连接查询让新人亲眼看到全表扫描导致查询时间从毫秒级变成秒级再让他们自己用 EXPLAIN 定位问题。这种直观对比比讲半小时理论都有用。希望帮到你。本文还有配套的精品资源点击获取