SQL Server学生选课库实验报告拆解：建表查询避坑与二次开发

简介这份太原理工大学软件工程《数据库概论》实验报告面向数据库课程学习者和需要完成SQL上机实验的软件工程学生可用于对照实验流程、梳理报告结构并复盘课堂操作。压缩包仅含1个docx文档大小约1.69MB便于直接查阅与整理。内容基于Microsoft SQL Server 2016围绕基本表创建、修改和删除索引与视图创建以及插入、更新、查询等数据操作展开并涉及数据完整性、用户鉴别与数据控制等实验主题报告以具体SQL语句、执行结果和注意事项组织知识点能帮助读者理解建表约束、聚簇索引、唯一索引及多行插入等细节。它适合课前预习、实验课参考或期末复习时查漏补缺目前已有311人学习可作为数据库基础操作训练的实用样例。1. 一份数据库实验报告为什么值得当成项目来拆上个月帮一个刚转正的同学看代码他在一条三表关联的统计 SQL 上卡了一下午最后发现是 GROUP BY 少写了一列。我把手边一份某高校软件工程专业的《数据库系统原理》实验报告翻给他看从建表、约束、索引、视图到单表查询、分组统计、连接、嵌套与集合运算几十条 SQL 把业务查询的常见套路几乎走了一遍。这份实验报告就是接下来要拆的对象以 SQL Server 2016 为平台围绕学生选课这套经典模型做了交互式 SQL、数据完整性、用户鉴别与数据控制三次实验把 DDL、DML、DQL 和约束验证串成了一条线。它适合两类人刚学数据库、想找一份能照着敲的脚本库的新手和想补 SQL 基本功、把那些玄学报错搞明白的开发者。下面按环境、建表、查询、踩坑、改造的顺序往下拆。2. 实验结构与环境落地从三次实验到一套能跑的学生选课库先看清原料的组织方式再动手敲键盘。这份报告是三份实验文档的顺序叠加每份都按「实验目的 → 实验平台 → 实验内容 → 心得体会」四段走内容部分才是真正的技术资产。它没有给出任何可执行的工程结构只有散落在 Word 段落里的 SQL 片段所以第一步是把它还原成一套有执行顺序的脚本。2.1 三次实验各自解决什么问题实验主题覆盖语句直接产出实验二交互式 SQL 语句的使用CREATE / ALTER / DROP、INSERT / UPDATE / DELETE、SELECT 全家族、CREATE INDEX、CREATE VIEW一个能增删改查的选课库实验三数据完整性PRIMARY KEY、UNIQUE、CHECK、DEFAULT、FOREIGN KEY、ALTER 增删约束一组能拦住脏数据的约束实验四用户鉴别与数据控制登录名、数据库用户、角色、GRANT / REVOKE按角色分权的账号体系实验二是主体占了全部内容的七成以上数据定义、数据操作、数据查询三段层层递进实验三把重点从「能存进去」转到「不许乱存」用主键、唯一、检查、默认、外键五类约束去验证数据库的守门能力。这两部分是能在本机完整复现的。实验四在原料里只给了标题没有展开内容常见做法是先建一个服务器级登录名再映射成数据库用户然后用GRANT SELECT, INSERT ON SC TO 某角色这种方式按表授权最后用REVOKE收回。做二次开发时这一段是很自然的扩展点后面第六章还会提到。三份实验共用一个Student / Course / SC的数据模型这个一致性很关键它让你能在同一套数据上反复验证不用为每个实验重建环境。2.2 关系模型拆解三张核心表怎么咬合模型本身不复杂但细节决定了后面所有查询能不能写顺。Student保存学生基本信息主键是学号Snochar(8)其余字段有姓名、性别、年龄、所在系、班级。实验三还加了一个Stotal字段默认值 0用来演示 DEFAULT 约束。Course保存课程信息主键Cnochar(4)Cname课程名Ccredit学分关键字段是Cpno先修课号它是一个自引用外键指向本表的Cno。这个设计是整份报告里最有教学价值的一处也是最容易翻车的一处。SC是选课关系(Sno, Cno)联合主键Score成绩。两个外键分别指向Student和Course构成标准的 m:n 关系分解。三张表的关系是一个学生可以选多门课一门课可以被多个学生选选课记录上的成绩属于「这一对」而不是任何单方。理解这一点后面写连接查询和分组统计时就不会把COUNT(*)和COUNT(DISTINCT Sno)搞混。提示SC表在原料里字段名混用过Score和Grade建表用Score、查询里写Grade的情况都有。统一成Score之后所有查询脚本才不用改字段名这是整理这类文档型资源最容易忽略的一步。2.3 环境落地实例连接与脚本执行顺序平台在原料里写死了 SQL Server 2016实际用 2017、2019、2022 的 Developer 版都能跑语法层面没有差异。客户端用 SSMS连上本机实例后按下面的顺序组织脚本最省事。db-lab/ ├── 00_env/connect.md # 实例名、认证方式、默认库 ├── 01_ddl/ │ ├── 010_create_db.sql # 建库并切换上下文 │ ├── 020_create_table.sql # Student / Course / SC / Employee │ ├── 030_alter_table.sql # 加列、改类型、加唯一约束 │ ├── 040_index.sql # 普通索引、唯一索引、聚簇索引 │ └── 050_view.sql # IS_Student / S_G / XK_VIEW ├── 02_dml/ │ ├── 100_insert.sql │ ├── 110_update.sql │ └── 120_delete.sql ├── 03_query/ │ ├── 200_single.sql │ ├── 210_group.sql │ ├── 220_join.sql │ ├── 230_nested.sql │ └── 240_set.sql ├── 04_constraint/ │ ├── 300_check_pk.sql │ └── 310_check_fk.sql └── 99_teardown.sql # 一键清库方便重跑编号前缀不是装饰它解决的是执行顺序问题建库必须在建表前建表必须在插数据前外键约束的插入顺序还有额外要求第五章细讲。命名用三位数字是为了留出插入空间后来补一条025_seed.sql做基础数据播种时不用重命名任何文件。连接和建库本身没有技术含量但有两个细节值得固定下来-- 切到 master因为 DROP/CREATE DATABASE 不能在目标库上下文里执行 USE master; GO IF DB_ID(SCTest) IS NOT NULL DROP DATABASE SCTest; GO CREATE DATABASE SCTest; GO USE SCTest; GOGO是 SSMS 的批处理分隔符不是 T-SQL 语句。CREATE DATABASE之后必须换批次否则后面的USE SCTest会在编译期就找不到这个库。这一点在写自动化脚本时尤其重要因为用 ADO.NET 或 sqlcmd 执行时GO需要客户端自己拆分直接整段丢给服务端会报语法错误。环境跑通的标准很简单SELECT VERSION能返回版本号SELECT DB_NAME()返回SCTestSELECT * FROM Student不报「对象名无效」。三个都过了再往下走。3. 数据定义拆解建表语句里的约束设计与索引取舍数据定义是整个项目的地基。原料里给出的建表语句能跑但类型选择和约束写法有明显的手写痕迹比如性别用char(2)存中文、年龄先int后改smallint、约束全部匿名。把这些整理成一份规范的 DDL是二次开发的第一件事。3.1 建表语句字段类型、主键与那一次 ALTER先看整理后的Student表。CREATE TABLE Student ( Sno char(8) NOT NULL, -- 学号定长 8 位 Sname varchar(10) NOT NULL, -- 姓名变长更省空间 Ssex char(2) NULL, -- 性别一个汉字占 2 字节 Sage smallint NULL, -- 年龄smallint 足够 Sdept char(20) NULL, -- 系别代码如 CS / IS / MA Sclass char(4) NULL, -- 班级实验二里用 ALTER 补上的 CONSTRAINT PK_Student PRIMARY KEY (Sno), CONSTRAINT CK_Student_Ssex CHECK (Ssex IN (男,女)) );几个参数值得说清楚。Sno用char(8)而不是varchar(8)因为学号长度固定定长在索引比较时更快也不会因为变长存储产生额外开销Sname反过来用varchar中文姓名长度从 2 到 10 不等定长会浪费空间。Ssex用char(2)是因为在 GBK 或 UTF-8 的中文字符集下一个汉字占 2 或 3 字节用char(1)存不下。原料里Sclass是后来ALTER TABLE Student ADD Sclass char(4)补上去的Sage从int改成了smallint。这两步合并进建表语句更干净-- 原料里的两步修改合并后等价于 -- ALTER TABLE Student ADD Sclass char(4); -- ALTER TABLE Student ALTER COLUMN Sage smallint;改类型这一步有个坑如果表里已经有数据ALTER COLUMN会尝试隐式转换转换失败整条语句回滚。空表上改没风险有数据时先SELECT一遍确认取值范围。Course表的重点是自引用外键CREATE TABLE Course ( Cno char(4) NOT NULL, Cname varchar(40) NOT NULL, Cpno char(4) NULL, -- 先修课号指向本表 Cno Ccredit smallint NULL, CONSTRAINT PK_Course PRIMARY KEY (Cno), CONSTRAINT UQ_Course_Cname UNIQUE (Cname), CONSTRAINT FK_Course_Cpno FOREIGN KEY (Cpno) REFERENCES Course(Cno) );FK_Course_Cpno引用的是同一张表的主键这在 SQL Server 里完全合法但插入顺序被彻底约束住了插入的Cpno值必须在表中已经存在。原料里课程数据的插入顺序是 1、2、3、4、5、6、7而Cpno的引用关系是 1→5、3→1、4→6、5→7插第 1 条时5还不存在直接报外键冲突。第五章会给出两种解法。SC表是联合主键加双外键的标准写法CREATE TABLE SC ( Sno char(8) NOT NULL, Cno char(4) NOT NULL, Score smallint NULL, CONSTRAINT PK_SC PRIMARY KEY (Sno, Cno), CONSTRAINT FK_SC_Sno FOREIGN KEY (Sno) REFERENCES Student(Sno), CONSTRAINT FK_SC_Cno FOREIGN KEY (Cno) REFERENCES Course(Cno), CONSTRAINT CK_SC_Score CHECK (Score 0 AND Score 100) );注意Score允许为 NULL因为「选了课但还没出成绩」是真实业务状态原料里也确实插入了Score为 NULL 的记录。这条设计直接影响了后面聚合函数的写法AVG(Score)会自动跳过 NULLCOUNT(*)不会。3.2 约束的两种写法与命名习惯同一类约束可以写在列后面也可以写在表末尾原料里两种混着用读起来很累。列级写法Sno char(8) PRIMARY KEY短但约束名由系统随机生成叫PK__Student__4B4E1B1D2A3F5C11这种东西。将来要DROP CONSTRAINT的时候你得先去sys.key_constraints里查名字。表级写法CONSTRAINT PK_Student PRIMARY KEY (Sno)长一点但名字自己定可读、可维护、可脚本化。二次开发时推荐全部走表级写法尤其是要在不同环境之间迁移脚本的时候——系统生成的名字每次都不一样脚本没法直接复用。唯一约束也一样。ALTER TABLE Course ADD UNIQUE(Cname)和CONSTRAINT UQ_Course_Cname UNIQUE (Cname)功能等价后者在报错信息里会明确告诉你「违反了 UQ_Course_Cname」前者只会给你一串乱码名。线上排查时这一点差别不小。3.3 索引唯一、聚簇以及那个建不出来的索引原料里有四条建索引语句其中一条在 SQL Server 上必然失败。CREATE INDEX iCname ON Course(Cname); CREATE UNIQUE INDEX iSname ON Student(Sname); CREATE CLUSTERED INDEX iSnoCno ON SC(Sno, Cno DESC); CREATE UNIQUE INDEX iCno ON Course(Cno);前两条没问题。第三条CREATE CLUSTERED INDEX iSnoCno ON SC(...)会报错Cannot create more than one clustered index on table SC。原因在于SC的主键约束默认就是聚簇索引一张表只能有一个。想建这个索引得先把主键改成非聚簇-- 先让主键变非聚簇索引腾出聚簇的坑位 ALTER TABLE SC DROP CONSTRAINT PK_SC; ALTER TABLE SC ADD CONSTRAINT PK_SC PRIMARY KEY NONCLUSTERED (Sno, Cno); -- 现在聚簇索引可以建了按 Sno 升序、Cno 降序物理排列 CREATE CLUSTERED INDEX iSnoCno ON SC(Sno, Cno DESC);Cno DESC这个降序在单列上意义有限但在联合索引里是有用的它让「某学生的课程按课程号倒序」这类查询直接走索引顺序省掉一次排序。第四条CREATE UNIQUE INDEX iCno ON Course (Cno)是纯冗余因为主键已经保证了Cno唯一再建一个唯一索引只会增加维护成本——每次插入都要多维护一棵 B 树。原料里把它列出来应该是为了演示「唯一索引」这个概念实际项目里不必照抄。注意索引不是越多越好。Student表在这种规模下几十行全表扫描比走索引还快优化器自己会做判断。索引的价值要到数据量上来才体现实验环境下写索引主要是为了理解语法和结构别把它当成性能优化的全部。3.4 视图给宽表套一层只读外壳视图部分原料给了三个覆盖了三种典型用途。-- 用途一行过滤。只暴露信息系学生其他系的数据连看都看不到 CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept IS;-- 用途二预聚合。把每个学生的平均分算好封起来上层直接查 CREATE VIEW S_G (Sno, Gavg) AS SELECT Sno, AVG(Score) FROM SC GROUP BY Sno;第二个视图涉及分组聚合在 SQL Server 里是只读的不能对S_G做 INSERT 或 UPDATE。想更新只能回到SC表。这一点在做权限设计时反而是个优势把视图授给只读账号物理表不授权天然就是一层访问控制。-- 用途三宽表拼接。三表连接的结果固化成视图上层不用重复写 JOIN CREATE VIEW XK_VIEW AS SELECT s.*, c.*, sc.Score FROM Student s JOIN SC sc ON s.Sno sc.Sno JOIN Course c ON sc.Cno c.Cno;SELECT s.*, c.*这种写法在视图里要谨慎Student和Course都没有同名列还好一旦两边都有Sno之类的字段视图就会出现重复列名SELECT * FROM XK_VIEW会直接报错。稳妥的写法是显式列出需要的列而不是用*。4. 数据操作与查询拆解从单表到嵌套、再到集合运算查询是这份报告里篇幅最大的一块从最简单的SELECT Sno, Sname FROM Student一路走到EXCEPT集合差基本覆盖了关系代数在 SQL 里的全部落地形式。这一段按难度分四层看。4.1 插入与更新带子查询的 UPDATE 怎么写才不翻车插入部分没有太多可讲的除了一个细节多行插入用的是INSERT INTO ... SELECT。-- 把计算机系的学生抽到一张临时表里字段名和源表不同 CREATE TABLE cs_Student ( 学号 char(8), 姓名 varchar(10), 年龄 smallint ); INSERT INTO cs_Student (学号, 姓名, 年龄) SELECT Sno, Sname, Sage FROM Student WHERE Sdept CS;这种写法的价值在于「列按位置对应不按名字对应」。目标表的列名可以是中文可以是完全不同的命名风格只要列数一致、类型兼容就能灌进去。做数据迁移时这招很常用。更新部分原料里有个典型的写法问题-- 原料写法把子查询放在等号左边 UPDATE SC SET Score Score 5 WHERE CS (SELECT Sdept FROM Student WHERE Student.Sno SC.Sno);这个写法能跑但有三重风险。第一可读性差条件判断反过来了第二如果子查询返回多行比如Sno不唯一直接报Subquery returned more than 1 row第三如果某个学生在Student里查不到子查询返回 NULLCS NULL结果是 UNKNOWN这行不会被更新也不报错属于静默失败。改成EXISTS版本UPDATE SC SET Score Score 5 WHERE EXISTS ( SELECT 1 FROM Student WHERE Student.Sno SC.Sno AND Student.Sdept CS );EXISTS只关心子查询有没有行不关心返回什么值所以子查询返回多行也不报错SELECT 1是习惯写法写SELECT *效果一样优化器不会真的去取列。还有一个细节Score 5遇到Score为 NULL 的行结果还是 NULL不会变成 5。这是 SQL 三值逻辑的基本行为但每年都有人在这里翻车以为 NULL 会被当成 0 参与运算。4.2 分组统计GROUP BY、HAVING 与聚合里的 NULL分组统计的核心是把「行」变成「组」GROUP BY后面跟的是分组键SELECT里只能出现分组键和聚合函数。-- 统计各门课程的选课人数、均分和最高分只要均分超过 80 的 SELECT Cno AS 课程号, COUNT(*) AS 选课人数, AVG(Score) AS 均分, MAX(Score) AS 最高分 FROM SC GROUP BY Cno HAVING AVG(Score) 80;WHERE和HAVING的区别在这里体现得很清楚WHERE在分组前过滤行不能用聚合函数HAVING在分组后过滤组可以用聚合函数。写反了会直接报错。COUNT(*)和COUNT(Score)的差别是另一个高频坑。前者数行数包含Score为 NULL 的行后者只数Score非 NULL 的行。统计「有多少人选了这门课」应该用COUNT(*)统计「有多少人出了成绩」才用COUNT(Score)。原料里COUNT(DISTINCT Sno)的用法是对的——统计「有多少学生选了课」需要去重因为一个学生可能选多门。AVG(Score)同样忽略 NULL。如果某门课三个人选、只有两个人有成绩AVG除的是 2 不是 3。想让 NULL 参与分母得写AVG(ISNULL(Score, 0))或者SUM(Score) / COUNT(*)两种口径在业务上含义完全不同报报表前先跟需求方确认清楚。4.3 连接查询WHERE 连接、JOIN ON 与 LEFT OUTER JOIN 的差别同样的结果可以用三种写法拿到但语义不完全一样。-- 写法一连接条件写在 WHERE 里隐式连接 SELECT s.Sno, s.Sname, sc.Cno, sc.Score FROM Student s, SC sc WHERE s.Sno sc.Sno; -- 写法二JOIN ON显式连接 SELECT s.Sno, s.Sname, sc.Cno, sc.Score FROM Student s JOIN SC sc ON s.Sno sc.Sno; -- 写法三左外连接 SELECT s.Sno, s.Sname, sc.Cno, sc.Score FROM Student s LEFT OUTER JOIN SC sc ON s.Sno sc.Sno;写法一和写法二在结果上等价写法二把「连接」和「过滤」分开了三表以上时差别非常明显——FROM A, B, C WHERE ...里连接条件和过滤条件混在一起改一个条件得先想清楚它属于哪一类。写法三多了一种情况没选任何课的学生也会出现在结果里Cno和Score是 NULL。这个差别直接影响后续统计。要算「每个学生选了几门课包括一门都没选的」必须用左外连接加COUNT(sc.Cno)SELECT s.Sno, s.Sname, COUNT(sc.Cno) AS 选课数 FROM Student s LEFT OUTER JOIN SC sc ON s.Sno sc.Sno GROUP BY s.Sno, s.Sname;这里如果写成COUNT(*)没选课的学生会被算成 1因为左外连接给他补了一行全是 NULL 的记录结果就是错的而且错得很隐蔽——数据量小的时候根本看不出来。多表连接还有一个容易忽略的点SC里存的是Cno要显示课程名必须再连一次Course。SELECT s.Sno, s.Sname, c.Cname, sc.Score FROM Student s JOIN SC sc ON s.Sno sc.Sno JOIN Course c ON sc.Cno c.Cno WHERE c.Cname 数据库系统原理;原料里这条语句写成了Course.Cno Student.CnoStudent表根本没有Cno字段属于笔误直接执行会报列名无效。这类错误在整理文档型资源时很常见逐个跑一遍是必须的。4.4 嵌套与集合IN、EXISTS、ALL 和 UNION/INTERSECT/EXCEPT嵌套查询分两类非相关子查询和相关子查询。非相关子查询独立执行一次结果给外层用-- 查询与「张三」同系的学生 SELECT Sno, Sname, Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname 张三);相关子查询依赖外层的每一行逻辑上要执行多次-- 找出每门课中成绩超过该生平均分的选课记录 SELECT x.Sno, x.Cno, x.Score FROM SC x WHERE x.Score (SELECT AVG(y.Score) FROM SC y WHERE y.Sno x.Sno);这里的y.Sno x.Sno就是相关条件x是外层别名y是内层别名。这句的执行语义是「对 SC 的每一行 x算出该学生的平均分再比较」。实际执行时优化器可能会改写但理解语义必须按「逐行」来。EXISTS是相关子查询的典型场景也是NOT IN的安全替代-- 选修了 1 号课程的学生 SELECT Sname FROM Student s WHERE EXISTS ( SELECT 1 FROM SC WHERE SC.Sno s.Sno AND SC.Cno 1 ); -- 一门课都没选的学生NOT EXISTS 版本 SELECT Sname FROM Student s WHERE NOT EXISTS ( SELECT 1 FROM SC WHERE SC.Sno s.Sno );NOT IN的坑在于子查询结果里只要有一个 NULL整个条件就永远为 UNKNOWN查询返回空集。NOT EXISTS没有这个问题所以涉及「不在某个集合里」的语义我一律写NOT EXISTS。带量词的比较运算ALL/ANY在业务代码里用得少但实验里的例子很典型-- 其他系中比计算机系所有学生年龄都小的学生 SELECT Sname, Sage FROM Student WHERE Sage ALL (SELECT Sage FROM Student WHERE Sdept CS) AND Sdept CS; ALL等价于「小于最小值」 ANY等价于「大于最小值」。注意如果子查询返回空集 ALL恒为 TRUE——这也是个静默陷阱写之前最好确认子查询不会为空。集合运算三条语句把关系代数的并、交、差都落了一遍-- 并计算机系的学生加上年龄不大于 19 的自动去重 SELECT * FROM Student WHERE Sdept CS UNION SELECT * FROM Student WHERE Sage 19; -- 交既在计算机系、年龄又不大于 19 SELECT * FROM Student WHERE Sdept CS INTERSECT SELECT * FROM Student WHERE Sage 19; -- 差计算机系里年龄大于 19 的 SELECT * FROM Student WHERE Sdept CS EXCEPT SELECT * FROM Student WHERE Sage 19;UNION默认去重UNION ALL不去重性能上前者要多做一次排序或哈希数据量大时差别明显。INTERSECT和EXCEPT在 SQL Server 和 PostgreSQL 里都原生支持MySQL 要到 8.0.31 才有早期版本得用JOIN或NOT EXISTS改写。这套脚本往 MySQL 迁移时这两条是必须改的地方。5. 避坑与排查五条血泪经验实验报告里的语句能跑通是因为数据量小、执行顺序被人为安排过。真放到工程里下面五个坑几乎必踩。5.1 外键顺序报错INSERT conflicted with the FOREIGN KEY现象执行INSERT INTO SC VALUES(20100001,1,92)报错The INSERT statement conflicted with the FOREIGN KEY constraint FK_SC_Sno。原因SC.Sno引用的20100001在Student表里不存在或者SC.Cno引用的1在Course表里不存在。外键约束在插入时立即校验父表没有的行子表不许插。解决按依赖顺序插入先Student、Course再SC。如果要批量导入且顺序无法保证可以临时禁用约束ALTER TABLE SC NOCHECK CONSTRAINT FK_SC_Sno导完再WITH CHECK CHECK CONSTRAINT恢复。禁用期间的数据是不受保护的导完必须立刻恢复并验证别把这个状态留在生产库上。5.2 自引用外键Course.Cpno 指向自己现象INSERT INTO Course VALUES(1,数据库系统原理,5,4)报外键冲突但5号课程明明在后面会插进去。原因Cpno引用的是Course自己的Cno插入第 1 条记录时5号课程还不存在。解决有两种。一种是先插无先修课的课程Cpno为 NULL 的再插有依赖的如果依赖成环1→5→7→1 这种就得分两步先全部插入、Cpno先给 NULL再逐条UPDATE补上。-- 第一步全部课程先以 Cpno NULL 插入 INSERT INTO Course (Cno, Cname, Cpno, Ccredit) VALUES (1,数据库系统原理,NULL,4); INSERT INTO Course (Cno, Cname, Cpno, Ccredit) VALUES (5,数据结构,NULL,4); INSERT INTO Course (Cno, Cname, Cpno, Ccredit) VALUES (7,C 语言,NULL,4); -- 第二步补齐先修关系 UPDATE Course SET Cpno 5 WHERE Cno 1; UPDATE Course SET Cpno 7 WHERE Cno 5;如果表上还开着外键第二步的UPDATE也会校验所以顺序必须是「先补被引用的再补引用别人的」。这一步顺序反了报错一模一样排查时容易绕圈子。5.3 CHECK 约束放过 NULL现象CK_SC_Score CHECK (Score 0 AND Score 100)建好了插入Score 120被拦住但插入Score NULL顺利通过。原因CHECK 约束的判定结果是三值逻辑NULL 0是 UNKNOWN而约束只在结果为 FALSE 时才拒绝UNKNOWN 是放行的。解决如果业务上不允许成绩为空加Score smallint NOT NULL如果允许为空比如还没出成绩那这个行为就是对的别去改。同理CHECK (Ssex IN (男,女))也拦不住 NULL要拦必须显式写Ssex IS NOT NULL或者把列定义成 NOT NULL。这一点在做数据质量校验时特别容易漏。5.4 聚簇索引只能有一个现象CREATE CLUSTERED INDEX iSnoCno ON SC(Sno, Cno DESC)报Cannot create more than one clustered index on table SC。原因建表时PRIMARY KEY默认生成聚簇索引SC已经有了一个。解决要么把主键定义成PRIMARY KEY NONCLUSTERED要么先DROP CONSTRAINT再重建。还有一种更常见的做法是干脆不建这个聚簇索引——(Sno, Cno)上的主键索引已经能覆盖绝大多数按学号查的场景再建一个只是徒增写入开销。判断标准是看查询模式如果大量查询是「按学号范围扫」聚簇有意义如果都是点查主键的非聚簇索引足够了。5.5 GROUP BY 报 8120 / 子查询返回多行现象一SELECT Sno, Sname, AVG(Score) FROM SC GROUP BY Sno报Column Sname is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause错误号 8120。原因Sname不在GROUP BY里也不是聚合函数的结果SQL Server 不知道该取哪一行的值。解决把Sname加进GROUP BY或者先用子查询聚合再连接。后者的写法在只需要部分列时更清晰SELECT s.Sno, s.Sname, t.avg_score FROM Student s JOIN (SELECT Sno, AVG(Score) AS avg_score FROM SC GROUP BY Sno) t ON s.Sno t.Sno;现象二WHERE Sdept (SELECT Sdept FROM Student WHERE Sname LIKE 张%)报Subquery returned more than 1 row。原因要求子查询返回单值LIKE 张%匹配到了多个学生。解决把换成IN或者给子查询加TOP 1明确只要一个。选哪个取决于业务语义——「和所有姓张的同学同系」用IN「和某一位同系」用TOP 1加排序别用TOP 1去糊弄本该用IN的场景。6. 二次开发把这堆 SQL 片段改成能反复跑的实验脚手架这份报告最大的问题是不可重跑。每执行一次INSERT就多一批数据主键冲突、外键冲突轮着来改完一处又撞另一处。我在自己的机器上把它重构成了幂等脚本改造成本不高收益是每次验证都从干净状态开始。第一件事是把建库脚本做成可重复执行。IF DB_ID(...) IS NOT NULL DROP DATABASE加GO分批是最简单的幂等模式跑一百次结果都一样。第二件事是给每类约束配一条「故意违规」的断言用事务包起来验完就回滚不污染数据DECLARE blocked bit 0; BEGIN TRY BEGIN TRAN; -- 故意插入一条成绩越界的记录 INSERT INTO SC (Sno, Cno, Score) VALUES (20100001,1,120); ROLLBACK TRAN; END TRY BEGIN CATCH SET blocked 1; IF TRANCOUNT 0 ROLLBACK TRAN; END CATCH IF blocked 1 PRINT PASS: 成绩越界被 CK_SC_Score 拦截; ELSE PRINT FAIL: CHECK 约束没生效检查是否被 NOCHECK 掉了;这段模式可以复制成五份分别验主键重复、唯一约束、外键缺失、CHECK 越界、NOT NULL 为空。blocked这个标志位是关键不能在TRY里直接PRINT因为异常会跳到CATCHTRY块后面的语句根本不会执行。第三件事是做方言适配。这份脚本原始是 T-SQL往其他库迁移时改动集中在几处特性SQL ServerMySQL 8.0PostgreSQL集合交/差INTERSECT/EXCEPT8.0.31 起支持原生支持字符串拼接CONCAT()||空值替换ISNULL()IFNULL()COALESCE()自增列IDENTITY(1,1)AUTO_INCREMENTGENERATED ... AS IDENTITY批处理分隔GODELIMITER无GO这一行在跨库时最容易被忽略整段脚本丢进 MySQL 客户端会直接语法错误因为GO根本不是 SQL 语句。第四件事是补上原料里缺的实验四。按角色分权的最小闭环是建登录名、映射数据库用户、建角色、授权、验证。验证环节可以建一个只有SELECT权限的账号登录后试INSERT确认被拒绝这才算把「用户鉴别与数据控制」跑完整。最后说个习惯。这套脚本我后来每次改动都会先跑一遍99_teardown.sql清库再从头执行010到310一条不跳。有一次图快跳过了建约束那段直接跑查询结果AVG算出来的数一直对不上查了半小时才发现是SC表里还留着上一轮的脏数据。从那以后我每次动这类实验脚本都强制走一遍「清库 → 全量重建 → 断言校验」的完整流程宁可多花两分钟也不赌那点运气。希望帮到你。本文还有配套的精品资源点击获取