简介数据库实验四是一份围绕T-SQL完整性约束操作的实验笔记适合正在学习数据库原理、需要完成同类课内上机任务的高校学生。内容以实验任务为主线覆盖主键与唯一约束的创建/删除、强制表间引用完整性、对主表与从表执行插入更新删除时的影响测试以及级联引用的设置与验证并给出了各操作失败时的错误信息和验证结论思考题部分对增删改破坏完整性、约束拒绝后的处理方式等作了举例说明课外任务还补充了外键级联更新的实现与失败原因。资源共1个文件为docx文档压缩包大小711KB便于直接打开查看或按需修改。目前已有343人学习适合拿来对照练习、梳理完整性约束知识点或作为实验报告参考。1. 数据库实验四到底在练什么一份SQL实验文档承载的实战分量数据库实验四.docx 这类实验文档在高校数据库课程里几乎是标配它不会让你从头实现一个数据库引擎而是把 SQL 语言里最常用、也最容易被忽略的细节集中起来建库建表、外键约束、数据插入、单表查询、多表连接、聚合分组以及视图和索引。我见过不少同学快速把文档翻完照着答案把 SQL 敲进去交差后却连“为什么这里要加 HAVING 而不是 WHERE”都说不清而认真做一遍、把每个报错都解决掉的人在后续课程设计和面试笔试里明显更顺。这里按我自己的实验习惯来拆从建库到执行计划每一步都给出可复现的 SQL、参数和踩坑点适合正在写实验报告的学生也适合想系统过一遍 SQL 实战的从业者。2. 拆解实验四的标准步骤从创建数据库到数据准备拿到实验四文档第一步永远是创建数据库。很多同学直接用图形界面默认选项点完成结果插入中文时出现问号或者后续在别的机器上导入 .sql 文件时报字符集不兼容。我一般会在 SQL 里显式指定字符集和排序规则这个习惯能省掉后续一大半乱码问题。2.1 创建数据库字符集与存储引擎为什么要在第一步定好CREATE DATABASE IF NOT EXISTS db_lab4 DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;这里有两个参数值得解释。第一个是utf8mb4它不是老式的utf8而是能覆盖四字节字符的完整 UTF-8 编码中文、生僻字、Emoji 都能存。老版本 MySQL 里utf8实际是utf8mb3遇到特殊字符会截断甚至报错。第二个是COLLATE排序规则utf8mb4_general_ci是大小写不敏感的比较方式适合课程实验如果实验要求区分大小写可以换成utf8mb4_bin。这一步定下来后面建表、导数据都不容易出字符问题。除了字符集存储引擎在实验里容易被忽略。默认的 InnoDB 支持外键和事务实验四里如果涉及外键约束和批量插入必须用 InnoDB如果用的是 MyISAM外键语句能写但不生效这是最隐蔽的翻车点之一。建库语句里可以补充DEFAULT ENGINE InnoDB或者在建表时指定。接下来建表。实验四通常会给三张基础表学生表、课程表、选课表分别对应student、course、sc。表结构设计要满足三范式也就是每列不可再分、每行有唯一标识、非主键列只依赖主键。落到代码里是这样CREATE TABLE student ( sno VARCHAR(10) PRIMARY KEY, -- 学号主键 sname VARCHAR(20) NOT NULL, -- 姓名不允许为空 ssex CHAR(1) DEFAULT M, -- 性别默认 M sage INT, -- 年龄 sdept VARCHAR(20) -- 所在系 );student表里sno是学生号作为主键。我用VARCHAR(10)而不是INT因为学号可能是A001这类带前导字符的编号用整型会丢失前导零sname的VARCHAR(20)在设计时按实际最大长度加余量来定不要写成VARCHAR(255)这种偷懒做法后面建索引时字段过长会影响索引长度ssex用CHAR(1)只存一个字符比VARCHAR(1)更省空间。课程表有一点特殊它有“先行课”字段指向同表的主键CREATE TABLE course ( cno VARCHAR(10) PRIMARY KEY, -- 课程号 cname VARCHAR(30) NOT NULL, -- 课程名 cpno VARCHAR(10), -- 先行课课程号允许为空 ccredit INT, -- 学分 CONSTRAINT fk_course_cpno FOREIGN KEY (cpno) REFERENCES course(cno) );这段代码里cpno是先行课编号比如“数据库原理”的先修课是“程序设计”那cpno就指向course表的某一行cno。这种自引用外键在实验里常出现用来练习“外键可以指向本表”这一概念。需要注意cpno允许为空因为最基础的课程没有先行课而cno是非空的这样外键约束不会阻止课程表的正常插入。建外键时我给约束起了名字fk_course_cpno如果不命名数据库会自动生成一个随机名后续删除约束时要去系统表里查才知道名字。选课表是三张表里最典型的关联表主键由两个外键共同组成CREATE TABLE sc ( sno VARCHAR(10), -- 学生号外键 cno VARCHAR(10), -- 课程号外键 grade DECIMAL(4,1), -- 成绩总长4位含1位小数 PRIMARY KEY (sno, cno), -- 联合主键 FOREIGN KEY (sno) REFERENCES student(sno), FOREIGN KEY (cno) REFERENCES course(cno) );sc表把学生和课程关联起来联合主键保证同一个学生选同一门课只出现一次grade用DECIMAL(4,1)表示能存三位整数加一位小数比如100.0如果用FLOAT或DOUBLE成绩计算时可能出现 89.99999 这种浮点噪声实验里用 DECIMAL 更稳妥。外键字段的类型必须和引用表的主键类型完全一致student.sno是VARCHAR(10)sc.sno也必须是VARCHAR(10)长度不同会导致建表失败。提示在 MySQL 里CHAR和VARCHAR的等值比较是否区分大小写由排序规则决定。上一节指定的utf8mb4_general_ci会让A001和a001被视为相等如果实验要求严格区分就把排序规则改成utf8mb4_bin。2.2 数据插入先父后子的顺序和外键约束的边界建完表之后是插入数据这一步看似简单但在实际作业里报错最多。我先插入student和course再插入sc因为sc的外键依赖前两张表。举一组典型数据INSERT INTO student VALUES (001, 张三, M, 20, CS), (002, 李四, F, 19, IS), (003, 王五, M, 21, CS); INSERT INTO course VALUES (C01, 数据库原理, NULL, 4), (C02, 程序设计, C01, 3); INSERT INTO sc VALUES (001, C01, 92.5), (001, C02, 88.0), (002, C01, 76.5);执行顺序上sc的插入必须排在student和course之后如果先执行数据库会报Cannot add or update a child row的外键错误。另外course表里C02的cpno指向C01意味着C01得先存在否则自引用外键同样会失败。把它们按依赖顺序放在同一个批处理里执行能消除这种不确定性。如果实验给了大量数据比如几百条学生记录我不会用INSERT ... VALUES一条条手敲而是把数据整理成 CSV用LOAD DATA导入LOAD DATA LOCAL INFILE /path/students.csv INTO TABLE student CHARACTER SET utf8mb4 -- 指定文件字符集避免中文乱码 FIELDS TERMINATED BY , -- 列分隔符 LINES TERMINATED BY \n -- 行分隔符 (sno, sname, ssex, sage, sdept);LOAD DATA的参数里CHARACTER SET utf8mb4是保证中文不出乱码的关键FIELDS TERMINATED BY ,指定 CSV 的列分隔符如果数据本身包含逗号就换成\tLINES TERMINATED BY \n是行分隔符Windows 下导出的 CSV 可能是\r\n这里要对应改成\r\n否则每行末尾会多出一个不可见字符导致最后字段入库异常。我一般还会在 CSV 首行放列名然后加IGNORE 1 LINES跳过表头。数据插入之后强烈建议先做一次完整性验证而不是立刻开始写查询。用下面几条语句检查行数和关键内容SELECT COUNT(*) AS total_students FROM student; SELECT COUNT(*) AS total_courses FROM course; SELECT COUNT(*) AS total_scores FROM sc; SELECT cno, cpno FROM course WHERE cpno IS NOT NULL;如果sc的行数明显少于选课记录数多半是外键约束拦截了部分数据如果course里查不到cpno非空的记录说明自引用外键没有生效要回头检查建表语句。这一步花不了两分钟但能避免后面查询实验里出现“结果怎么少了几行”的困惑。2.3 修改结构与常用管理命令ALTER 的正确姿势实验四偶尔会要求“给 student 表增加手机号字段”或“删除某个约束”。这类操作不常考但一旦出现很多同学直接DROP TABLE重来结果把已有数据也清掉了。正确做法是用ALTERALTER TABLE student ADD COLUMN phone VARCHAR(20); ALTER TABLE sc DROP FOREIGN KEY fk_sc_sno;ADD COLUMN默认加在表尾希望指定位置可以用AFTER sname删除外键时DROP FOREIGN KEY后面跟的是约束名而不是字段名。实验四里的表数据量小重建影响不大但养成用 ALTER 的习惯对后续真实的数据库维护很重要。3. 实验四的核心查询语法从单表筛选到多表连接实验文档通常会给出十几个查询题目比如“查询计算机系学生的学号和姓名”“查询每门课的平均成绩”。这些题目看似独立其实可以归纳成三类语法单表查询、多表连接、聚合分组。把这三类彻底搞懂大部分题目都能直接套用。3.1 单表查询WHERE 的过滤逻辑与 LIKE 的边界最简单的查询是筛选某个系的学生并按年龄排序。注意 SQL 的书写顺序和实际执行顺序不同这一点对新手很关键SELECT sno, sname, sage FROM student WHERE sdept CS ORDER BY sage DESC;书写顺序是 SELECT → FROM → WHERE → ORDER BY但数据库引擎先做 FROM 找到表再做 WHERE 过滤行然后执行 SELECT 投影最后才排序。这个顺序的意义在于你没法在 WHERE 里引用 SELECT 里定义的别名比如WHERE sage_diff 3中sage_diff如果是在 SELECT 里算出来的就会报“字段不存在”。实验里这种报错经常让同学绕圈子。去重和模糊匹配是单表查询的另外两个高频点SELECT DISTINCT sdept FROM student; SELECT sno, sname FROM student WHERE sname LIKE 张%;DISTINCT会对返回的所有列做组合去重不是只对第一列去重。LIKE 张%中的%匹配任意多个字符_只匹配一个字符比如张_匹配“张三”但不匹配“张伟杰”。实验里如果要求“查姓张的学生”用张%如果要求“查名字只有两个字的张姓学生”用张_。单表查询里还有一个容易翻车的是LIMIT。MySQL 支持LIMIT offset, count第一个数字是跳过的行数第二个是取几行PostgreSQL 对应写成LIMIT count OFFSET offset。如果实验环境不是同一种数据库这套写法在语法和语义上都有差异换库前要先确认。3.2 多表连接JOIN、子查询与 EXISTS 的取舍实验四里必有一两道题要求“查询选了某门课的学生姓名和成绩”这需要把三张表连接起来。最直观的是内连接SELECT student.sname, course.cname, sc.grade FROM student JOIN sc ON student.sno sc.sno JOIN course ON course.cno sc.cno WHERE sc.grade 90 ORDER BY student.sname;我故意在 SELECT、JOIN、WHERE 里都写了表前缀比如student.sname、sc.grade。多表连接时如果两表都有名为sno的字段不带前缀会直接报Column sno in field list is ambiguous即使不报错不带前缀也会让代码的可读性变差。另一个细节是JOIN ... ON和WHERE的职责不同ON是连接条件WHERE是过滤条件。实验数据量小时两者结果可能一样但一旦用到左连接这个区别会立刻显现。左连接的典型场景是“查询所有学生的选课情况没选课的学生也要显示”SELECT student.sno, student.sname, sc.cno, sc.grade FROM student LEFT JOIN sc ON student.sno sc.sno;没选课的学生右侧的sc字段会是NULL。如果把sc.grade 60写进WHERE左连接会被转换成内连接没选课的学生就消失了正确做法是把它放进ON条件或者用sc.sno IS NULL来筛出没有选课的学生。这是实验报告里的加分点也是面试常问的“ON 和 WHERE 在 OUTER JOIN 下有什么区别”。子查询是多表连接的另一种写法。比如“查询年龄大于平均年龄的学生”SELECT sname, sage FROM student WHERE sage (SELECT AVG(sage) FROM student);括号里的子查询返回单个值叫标量子查询如果子查询返回一列值用IN或NOT IN返回一张表时可以用EXISTS或IN配合。实验里常常纠结用连接还是子查询我的原则是业务上只需要从一张表取结果时用子查询需要同时取多张表字段时用 JOIN逻辑清晰优先于运行速度。3.3 聚合查询GROUP BY 与 HAVING 的正确搭配“统计每门课的平均分”“查询选课超过两门的学生”这类题考察的是分组聚合。先看最基础的分组统计SELECT cno, COUNT(*) AS cnt, AVG(grade) AS avg_grade FROM sc GROUP BY cno;GROUP BY cno把选课表按课程编号分组COUNT(*)数每组行数AVG(grade)算每组成绩均值。注意COUNT(*)和COUNT(grade)不同COUNT(*)统计本组所有行COUNT(grade)只统计grade非NULL的行。选课记录如果还没录成绩grade为NULL两个计数结果就不一样实验题里问“选课人数”时通常应该统计选课行数而不是成绩非空数。分组后过滤要用HAVING而不是WHERESELECT sno, COUNT(*) AS course_count FROM sc GROUP BY sno HAVING COUNT(*) 2;WHERE 在分组前过滤行HAVING 在分组后过滤分组。比如“计算机系学生中选课超过两门的人”筛选系别的条件放 WHERE筛“选课超过两门”放 HAVING。如果把第二个条件写进 WHERE数据库会直接报错因为分组还没发生COUNT(*)根本不存在。MySQL 对GROUP BY的默认行为在不同版本里不同。在启用ONLY_FULL_GROUP_BY模式时SELECT 里的非聚合列必须出现在 GROUP BY 中但在某些配置下 SELECT 可以带未分组的列它返回的是这一组的第一行结果不可预期。实验四里我建议一律按“SELECT 列要么是分组列要么是聚合函数”的规范来写既符合标准 SQL也不会因为数据库配置不同而翻车。4. 实验四避坑指南七个高频翻车现场与排查方法这一章按我实际排查同学和项目里遇到的问题来写全部是“现象 → 原因 → 解决”的结构按出现频率排序适用于 MySQL其他数据库可以对照参考。4.1 中文乱码三级字符集不一致现象插入和查询中文时显示为问号或乱码但表结构里的中文注释正常。原因客户端、数据库、表三级字符集不一致。Windows 命令行默认代码页可能是 GBK而数据库和表建成了 utf8mb4客户端发送的 SQL 被当成 GBK 解析入库后自然乱掉。解决建库建表统一用 utf8mb4连接后立刻执行SET NAMES utf8mb4Windows 命令行启动时加参数--default-character-setutf8mb4已经乱掉的数据只能删除重插没有后悔药。我最早是靠逐字符对比排查出来的后来凡建库先执行SET NAMES utf8mb4再没在这上面栽过。4.2 外键约束报错插不进去现象向sc插入数据时报Cannot add or update a child row删除父表记录时报Cannot delete or update a parent row。原因插入的子表记录在外键对应父表中不存在删除父表记录时子表仍有引用。解决先插父表、再插子表删除时先删子表、再删父表确实需要临时禁用检查时用SET FOREIGN_KEY_CHECKS 0但实验结束前必须恢复为 1。另外检查外键字段类型是否完全一致VARCHAR(10)和CHAR(10)在部分数据库里是不同类型长度不匹配也会被拒。4.3 NULL 值参与运算的连锁错误现象WHERE grade 60查不出成绩为空的记录AVG(grade)的结果看起来比手算小。原因NULL 不能参与比较运算所有比较结果都是“未知”AVG聚合时自动忽略 NULL。解决判断空值用IS NULL/IS NOT NULL不要用 NULL统计人数时想清楚用COUNT(*)还是COUNT(grade)。空值不是 0也不是空字符串而是一个“未定义”的状态这个认知直接影响查询结果。4.4 GROUP BY 与 SELECT 列对不齐现象MySQL 报this is incompatible with sql_modeonly_full_group_by。原因SELECT 的列没有出现在 GROUP BY 里也不是聚合函数在严格模式下被拒绝。解决让 SELECT 的每个非聚合列都出现在 GROUP BY 中如果确实想取组内某行数据用ANY_VALUE()或改写为子查询但至少要知道自己在做什么不要无脑加列掩盖错误。4.5 多表连接同名字段歧义现象Column sno in field list is ambiguous。原因两个表都有sno字段SELECT 没写表前缀。解决多表查询统一写成表名.字段名比如student.sno、sc.grade做连接时尽量给表起简短别名比如FROM student st JOIN sc ON st.sno sc.sno既解决歧义又减少书写量。4.6 批量操作没有事务改错收不回来现象执行多条 INSERT 或 UPDATE 后发现自己改错了关掉客户端无法恢复或者批量插入到一半报错前一半已经生效。原因默认每条 SQL 独立自动提交没有包裹在事务里。解决操作前用BEGIN或START TRANSACTION开启事务确认无误后COMMIT发现错误直接ROLLBACK。实验四里如果要“插入以下 10 条记录”用事务包住能避免中途失败留下半截数据比反复DELETE清理高效得多。4.7 分组取前 N 名ORDER BY 与 LIMIT 不是万能药现象想查“每门课成绩最高的学生”用ORDER BY grade DESC再取第一条结果每个分组只取到一条但取到的是全表排序后的头几条不是每个分组各自的最大值或者LIMIT 1只返回全局一行。原因ORDER BY LIMIT 是全局排序后截取不会自动按分组取极值。解决用关联子查询SELECT sc.sno, sc.cno, sc.grade FROM sc WHERE sc.grade ( SELECT MAX(s2.grade) FROM sc s2 WHERE s2.cno sc.cno );这段子查询对每一行sc都在同课程分组里找最高分再和该行成绩比较结果就是每门课的最高分记录。如果实验环境支持窗口函数MySQL 8.0 及以上也可以写ROW_NUMBER() OVER (PARTITION BY cno ORDER BY grade DESC)后者更简洁但解释起来不如子查询直观。这个问题在实验四里经常作为思考题出现会写的人不多属于拉开差距的一道题。5. 实验四加分的落地习惯视图、索引与 EXPLAIN 验证实验文档最后有时会留思考题比如“能否用视图实现某查询”“为什么加了索引后查询变快”。把下面三个工具做完实验报告的价值会高一个层次。5.1 用视图封装高频查询视图本质是一条保存下来的 SELECT 语句。实验里常把“计算机系学生”这种反复使用的条件做成视图CREATE VIEW v_cs_student AS SELECT sno, sname, sage FROM student WHERE sdept CS;创建后直接SELECT * FROM v_cs_student查询代码简洁还能模拟权限控制给只读用户开放视图而不开放原始表。注意视图依赖的列如果被删掉视图会失效但实验四的数据结构一般不会频繁变动所以不用太担心。5.2 用 EXPLAIN 验证索引的效果先不建索引直接看查询计划EXPLAIN SELECT * FROM sc WHERE sno 001;重点关注type、key、rows三列。没有索引时type通常是ALL表示全表扫描建索引后再看CREATE INDEX idx_sc_sno ON sc(sno); EXPLAIN SELECT * FROM sc WHERE sno 001;此时type变成ref等值匹配非唯一索引rows从全表行数下降到实际命中的行数key显示idx_sc_sno。这个前后对比不用任何计时工具就能证明索引的作用。需要提醒的是索引不是越多越好每建一个索引插入和更新都要多维护一份实验里为了演示建一两个足够生产环境要按查询模型取舍。5.3 执行计划里 type 字段的参考顺序EXPLAIN 结果里常见的type从好到差大致如下type含义是否理想const主键等值匹配最多一行结果很好eq_ref连接时按主键逐行匹配很好ref普通索引等值匹配较好range索引范围扫描如 BETWEEN中等index扫描整个索引树较差ALL全表扫描最差实验报告里如果能看到某个查询从ALL优化到ref这条优化结论非常直观。我看执行计划有个习惯先看rows再看key最后看type用数值判断扫描量比只看类型名可靠。回到实验四本身我最初帮同学排查问题的时候习惯把每一条报错原文复制下来再和成功语句做逐字符对比很多经验就是这么积累起来的。比如 4.1 里的乱码我第一次遇到时以为是数据文件的问题折腾很久才发现是客户端字符集不一致后来凡是建库我都先执行SET NAMES utf8mb4再也没在这上面翻过车。做完视图、索引和 EXPLAIN 这套组合你对实验四的掌握已经从“能出结果”到了“知道结果为什么快”这个差距面试或课程答辩时一开口就能被听出来。希望这个思路能帮到你。本文还有配套的精品资源点击获取