简介这是一份针对《数据库系统概论》课程设计的SQL建表与数据插入练习文档适合初学数据库原理、正在练习SQL语句的学生使用。内容围绕student、sc、course三张核心表展开完整给出了创建数据库、定义表结构、设置主键与外键约束以及分步插入示例数据的SQL语句。资源为单个PDF文件大小约48KB便于打印或对照学习目前已吸引1904人浏览下载。通过该文档读者可以直观理解关系模型中实体表、选课关系表的建表方法掌握外键关联的实现方式同时借助课程表“先插入、再更新预修课程号”的案例体会参照完整性约束在实际操作中的影响为后续编写复杂查询打下基础。1. 一套 student、sc、course 练习表藏着数据库课程的三个关键考点《数据库系统概论》第三章之后的所有查询例题几乎都跑在同一组数据上student、sc、course。这三张表加起来不过十几行记录建表语句却同时踩中了主键与唯一约束、自引用外键、双外键关联表三个关键考点恰好是新手在建表、插数环节最容易翻车的地方。这份PDF把从 create database 到 insert 的完整脚本按序排好适合两类人一是刚学到 SQL 语句、需要本地复现教材例题的在校生二是想快速造一套带外键约束的种子数据来练手或带新人的工程师。我先按脚本把库和表建出来再把为什么这么建、插入顺序为什么不能乱讲清楚。2. 建库与 student 表CHAR(20) 主键、UNIQUE KEY 的取舍与字符集选择2.1 先建库create database sql_test 的默认参数脚本第一步是CREATE DATABASE sql_test;在 MySQL 8.0 中这条语句默认使用 utf8mb4 字符集和 utf8mb4_0900_ai_ci 排序规则如果跑在 5.7 或更早版本默认字符集可能是 latin1中文姓名和系名写入后会出现乱码。我通常会把语句写完整CREATE DATABASE sql_test DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;utf8mb4 是 UTF-8 的超集能正常存储中文和四字节字符utf8mb4_general_ci 是大小写不敏感的排序规则等值查询姓名字段时更符合直觉。后面建表时所有 char 字段都会继承这个库级字符集所以这一步的参数直接影响三张表的中文存储。PDF 里的脚本是从 MySQL 管理工具导出的直接复制时要注意全角引号、中文逗号和 Windows 换行符建议先粘贴到纯文本编辑器里过一遍再执行。2.2 student 表结构与字段语义CREATE TABLE student ( Sno char(20) NOT NULL, Sname char(20) DEFAULT NULL, Ssex char(2) DEFAULT NULL, Sage smallint DEFAULT NULL, Sdept char(20) DEFAULT NULL, PRIMARY KEY (Sno), UNIQUE KEY Sname (Sname) );这张表里最值得琢磨的是 Sno 的类型。学号用 char(20) 而非 varchar(20)因为学号在业务上是固定位数编码定长字符串不需要额外的长度前缀存储结构简单等值检索时比较路径更短代价是尾部会补空格但 MySQL 在读取 char 时会自动去除尾部空格练习场景下不需要关注。Sage 用 smallint2 字节范围 -32768 到 32767放学生年龄绰绰有余改成 tinyint 也够教材统一用 smallint 是为了和 course 表的 Ccredit 保持整数类型的一致性。PRIMARY KEY (Sno) 自动创建唯一索引Sno 列本身声明了 NOT NULL两者叠加实现实体完整性学号既不能为空也不能重复。建表脚本里的反引号用于转义字段名是 MySQL 管理工具导出的常见写法没有特殊语义。如果把这个脚本迁到 SQL Server 或 PostgreSQL语法要做不少调整——SQL Server 不用反引号外键约束的写法也不同。这套练习表整体是 MySQL 方言直接跑在 MySQL 5.7 或 8.0 上最省事。2.3 UNIQUE KEY Sname 的教学边界对比项PRIMARY KEYUNIQUE KEY空值不允许允许MySQL 可存在多个 NULL每表数量1 个可多个自动索引是是外键引用通常引用主键可引用唯一键但少见Sname 加唯一约束是这个脚本里最值得讨论的设计。真实选课系统里姓名绝不能加唯一约束同名同姓太常见但课程练习里它恰好能讲清主键与唯一键的区别主键非空且唯一一张表只能有一个唯一键允许 NULL一张表可以有多个。两者都会自动生成索引这也是后面 sc 表的外键能够快速回表检查的原因。考试常问外键能不能引用唯一键答案是可以但主键语义上代表实体的唯一标识实际建模几乎总是引用主键。Sname 的唯一索引是用户定义完整性的演示道具理解它的边界比照抄它更重要。3. course 表自引用外键为什么课程数据必须分两段插入3.1 course 表结构与自引用外键建模CREATE TABLE course ( Cno char(4) NOT NULL, Cname char(40) NOT NULL, Cpno char(4) DEFAULT NULL, Ccredit smallint DEFAULT NULL, PRIMARY KEY (Cno), KEY Cpno (Cpno), CONSTRAINT course_ibfk_1 FOREIGN KEY (Cpno) REFERENCES course (Cno) );Cpno 是先修课字段值为另一门课的课程号所以外键指向同一张表的 Cno。这种自引用外键在关系模型中处理的是实体内部的有向关系比普通外键更考验对参照完整性的理解。外键检查发生在每一行写入或更新时系统会拿 Cpno 的值去 course 表的主键索引里查找找得到才放行。数据库Cno1的先修课是数据结构Cno5这条关系就落在同一张表的两行之间理解这个结构后面才看得懂为什么插入顺序如此敏感。注意约束名 course_ibfk_1——ibfk 是 InnoDB Foreign Key 的缩写这是 MySQL 图形工具导出时的默认命名风格手动建表时可以换成更语义化的名字如 fk_course_cpno。约束名只影响后续 ALTER TABLE DROP FOREIGN KEY 时的定位不影响约束行为。KEY Cpno (Cpno) 是显式为外键列建二级索引InnoDB 要求外键列必须有索引不写系统也会自动创建但显式写出来能让执行计划更可控。这里用普通 KEY 而不是 UNIQUE KEY 也是刻意的多门课可以同时以同一门课为先修课这门先修课编号在外键列中允许重复出现。3.2 第一段插入为什么不能一次性写入完整记录如果按直觉一次性插入完整课程记录INSERT INTO course (Cno, Cname, Cpno, Ccredit) VALUES (1, 数据库, 5, 4);MySQL 会立即返回 1452 错误Cannot add or update a child row: a foreign key constraint fails。原因很清楚——course 表此时还是空的被引用的 Cno5 根本不存在参照完整性检查不通过。这不仅发生在新表上往已有数据里插入一条先修课编号不存在的记录同样会被拒绝。正确做法是只插入 Cno 和 Cname让所有课程行先占位此时 Cpno 保持 NULL-- 先只写入课程号与课程名先修课列保持 NULL INSERT INTO course (Cno, Cname) VALUES (1, 数据库), (2, 数学), (3, 信息系统), (4, 操作系统), (5, 数据结构), (6, 数据处理), (7, PASCAL语言);外键约束对 NULL 值是放行的因为 SQL 对 NULL 的语义是未知未知值无法判断是否违反引用关系这也是外键列允许为空的原因。Cno 是主键插入重复值会报 1062 Duplicate entryCname 虽然允许重复但课程名在业务上理应各不同教材表结构里已经给它加了 NOT NULL保证这门课有名字。3.3 第二段 UPDATE回填先修课与学分7 行主键全部落库后任何一条先修课编号都能在表中找到对应主键这时才允许补写外键列UPDATE course SET Cpno 5, Ccredit 4 WHERE Cno 1; UPDATE course SET Cpno 4, Ccredit 2 WHERE Cno 2; UPDATE course SET Cpno 1, Ccredit 4 WHERE Cno 3; UPDATE course SET Cpno 6, Ccredit 3 WHERE Cno 4; UPDATE course SET Cpno 7, Ccredit 4 WHERE Cno 5; UPDATE course SET Cpno 5, Ccredit 2 WHERE Cno 6; UPDATE course SET Cpno 6, Ccredit 4 WHERE Cno 7;CnoCnameCpno先修课Ccredit1数据库542数学423信息系统144操作系统635数据结构746数据处理527PASCAL语言64这段 UPDATE 是先占位、再回填的标准解法。UPDATE 修改同一行既有数据外键检查发生在语句执行时此时所有被引用的 Cno 都已存在所以能一次跑完。教材把课程数据拆成两步本质不是性能考虑而是满足参照完整性的时序约束被引用行必须先于引用行存在。上表还能看出课程之间存在环4 先修 66 先修 55 先修 77 又先修 6。第二段 UPDATE 能全部成功靠的就是全量占位后逐个回填把环状依赖拆成了安全的串行操作。提示如果把 UPDATE 和 INSERT 混插比如先插 Cno1 再立即 UPDATE 它的 Cpno5会因 Cno5 尚未占位而报错。遇到底层设计不良的老课程系统改课程先修关系时也常见类似错误。4. sc 表联合主键与外键链三张表的数据插入顺序怎么定4.1 联合主键 (Sno, Cno) 的多对多语义CREATE TABLE sc ( Sno char(20) NOT NULL, Cno char(4) NOT NULL, Grade smallint DEFAULT NULL, PRIMARY KEY (Sno, Cno), KEY Cno (Cno), CONSTRAINT sc_ibfk_1 FOREIGN KEY (Sno) REFERENCES student (Sno), CONSTRAINT sc_ibfk_2 FOREIGN KEY (Cno) REFERENCES course (Cno) );主键由 Sno 和 Cno 联合构成这一行声明的语义是同一个学生选同一门课在这张表里只能出现一行。成绩 Grade 是依附于这个二元组的属性它既不属于学生也不属于课程而是学生—课程这个关系实例上的属性所以只能放在 sc 表里。这是关系模型处理多对多关系时的标准落法把两方主键各取一份拼成关联表的联合主键。学生与课程之间没有直接外键它们通过 sc 表间接建立联系业务查询时再用连接操作把三张表拼回去。从 InnoDB 存储层面看联合主键就是聚簇索引(Sno, Cno) 的列顺序决定了索引叶节点的排列方式。按 Sno 前缀过滤查某个学生的所有选课可以直接走聚簇索引按 Cno 查询查某门课的所有选课人则要靠 KEY Cno 这个二级索引回表这也是为什么 sc 表里需要单独给 Cno 建索引。建外键时系统会检查该列是否已有索引没有就自动补建sc 表这里两条外键各依赖一个已有索引不需要隐式创建。4.2 外键依赖顺序先 student再 course最后 scsc 表同时拥有两条外键Sno 引用 student.SnoCno 引用 course.Cno。这意味着写入 sc 的每一行都要同时检查两个方向的被引用行是否存在。插入顺序因此被约束为固定三步-- 1. 先插学生表 INSERT INTO student VALUES (201215121, 李勇, 男, 20, CS), (201215122, 刘晨, 女, 19, CS), (201215123, 王敏, 女, 19, MA), (201215125, 张立, 男, 19, IS); -- 2. 课程表按第 3 章的两段式写入 -- 3. 最后插选课记录 INSERT INTO sc (Sno, Cno, Grade) VALUES (201215121, 1, 92), (201215121, 2, 85), (201215121, 3, 88), (201215122, 2, 90), (201215122, 3, 80);student 和 course 互不引用两者谁先谁后不影响正确性但它们都必须赶在 sc 之前就绪。插入 sc 时MySQL 会先查 student 表确认 Sno 存在再查 course 表确认 Cno 存在两条都通过才写入。注意原始素材里没有 201215124 这个学号这是教材原样保留的练习时不用补后面做查询题时也不要臆造不存在的学生数据。如果想在插入前检查外键依赖关系可以查 information_schemaSELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA sql_test AND TABLE_NAME IN (student, course, sc);这条查询会列出 sql_test 库内所有外键约束的明细包括引用表和被引用表。日常排查外键报错时先用它确认依赖方向比对着建表脚本猜要快得多。4.3 插入异常与排查思路练习现场最常见的报错有两种。第一种是外键约束失败返回 ERROR 145223000Cannot add or update a child row。例如INSERT INTO sc (Sno, Cno, Grade) VALUES (201215121, 8, 60);course 表里没有 Cno8回表检查失败语句被整体拒绝。第二种是主键冲突返回 ERROR 106223000Duplicate entry。例如再插入 (201215121, 1, 95)联合主键已存在InnoDB 在唯一性检查阶段就会中止语句不会出现部分写入的中间状态。报错码常见场景修复方向1452外键引用的行不存在先补插被引用表或修正外键列值1062主键或唯一键重复换业务主键或用 INSERT ... ON DUPLICATE KEY UPDATE1264smallint 数值溢出检查 Grade、Sage 是否超出类型范围这三类错误覆盖了刷《数据库系统概论》课后题时八成以上的插入失败场景。如果遇到不认识的报错先执行 SHOW WARNINGS 看详细提示再定位到具体表和字段比直接改数据要稳。提示外键错误详情可以看 SHOW ENGINE INNODB STATUS\G 输出里的 LATEST FOREIGN KEY ERROR 段里面记录了被拒绝语句尝试插入的具体值和失败原因。5. 三表联查与约束验证把这套练习数据当成测试基座5.1 用连接查询确认数据完整性表建好、数据插入完成后先跑一条三表连接查询确认链路是通的SELECT s.Sname, c.Cname, sc.Grade FROM student s JOIN sc ON s.Sno sc.Sno JOIN course c ON sc.Cno c.Cno ORDER BY s.Sno, sc.Grade DESC;正确输出是李勇的三门成绩加刘晨的两门成绩共五行。这条查询通过 student → sc → course 的路径把三张表串起来JOIN 条件恰好就是 sc 表那两条外键。如果结果正确说明外键关联的两条索引都能正常工作。再跑一条分组聚合查平均分SELECT s.Sno, s.Sname, COUNT(*) AS total_courses, ROUND(AVG(sc.Grade), 2) AS avg_grade FROM student s JOIN sc ON s.Sno sc.Sno GROUP BY s.Sno, s.Sname HAVING COUNT(*) 0;GROUP BY 后面显式带上 s.Sname或者把它包进聚合函数否则在 MySQL 5.7 之后的 ONLY_FULL_GROUP_BY 模式下会直接报错这是入门阶段最常见的 SQL 语法报错之一。5.2 用约束演练验证 InnoDB 默认策略外键约束不仅能拦住非法插入也能拦住非法删除。试着删掉李勇的学号DELETE FROM student WHERE Sno 201215121;sc 表里有 3 行记录引用这个 Sno而建表时没有写 ON DELETE CASCADEInnoDB 默认采用 RESTRICT 策略这条 DELETE 会被拒绝返回 1451 错误Cannot delete or update a parent row。在这一步能直观感受到外键让删除行为不再自由。5.3 把整套脚本固化成 seed.sql把建库、建表、插入、更新按本节顺序整理成一个 seed.sql后续每次重建环境只需一步导入mysql -u root -p sql_test seed.sql这套数据会每次都生成相同的外键约束结构适合作为以后练习子查询、窗口函数、慢查询分析时的固定测试基座。比如验证索引是否生效可以直接在 course 表和 sc 表上跑 EXPLAIN观察外键列和主键列是否都走了索引。遇到任何查询结果和预期不符先回头检查外键依赖顺序有没有被破坏再确认三条插入是否按先 student、再 course、最后 sc 的顺序执行。本文还有配套的精品资源点击获取