简介太原理工大学软件工程专业数据库概论课程的实验报告文档面向正在学习数据库系统原理、需要完成SQL上机实验的本科生与自学者。内容围绕交互式SQL语句的使用、数据完整性、用户鉴别与数据控制展开按实验目的、实验平台、主要内容与结果、总结等部分组织覆盖基本表的创建、修改与删除索引与视图的建立以及插入、更新、查询等数据操作并以Microsoft SQL Server 2016为实验平台附有可对照的语句与执行结果。压缩包共1个docx文件约1.69MB结构单一、便于直接查阅与打印。目前已有311人学习适合作为课程实验参考、SQL语法复习材料也可用于对照检查建表约束、外键设置、聚簇索引和视图定义等细节帮助读者理解数据完整性与安全控制的实际落地方式。1. 期末周拷来的一堆零散 SQL为什么值得当项目读一遍期末周A同学把 U 盘里那份数据库实验报告拷给我里面不是一份规整的文档而是二十几段零散的 SQL 加上几张截图SQL Server 2016 里从头往下跑报错能出到第五个。这份软件工程专业的数据库课程实验报告价值从来不在“交作业”而在于它把建表、索引、视图、增删改查、完整性约束这几件事串成了一条真能跑通的链路而且每一条语句后面都埋着一个“为什么跑挂了”的机会。刚上手 SQL 的人可以照着它建库、灌数据把每个报错查到底已经写过业务代码的人看的则是约束到底该声明在数据库里还是放在应用层。翻完这一遍你对 T-SQL 的直觉会比啃三章教材实在得多。2. 数据定义三张表、四个索引、四个视图怎么一次建对2.1 技术栈与脚本组织为什么是 SSMS 查询编辑器整套实验跑在 SQL Server 2016 上客户端用 SSMS 自带的查询编辑器。选它的理由很朴素T-SQL 对约束、触发器、INTERSECT/EXCEPT这些语法支持完整图形化执行计划看索引有没有命中很直观而且机房机器上装的就是它。代价是方言绑得比较死TOP、GETDATE()、dbo.前缀这些东西搬到别的库上得改。差异点SQL Server 2016MySQL 8 / PostgreSQL 迁移时要改取前 N 行SELECT TOP 10 *LIMIT 10自增IDENTITY(1,1)AUTO_INCREMENT/SERIAL集合运算原生INTERSECT、EXCEPTMySQL 8 起才支持5.7 要用JOIN模拟字符串char按单字节存中文建议nvarchar默认 utf8mb4一般不用改脚本按功能切成四个文件用 SSMS 的“打开文件”逐个执行或者用sqlcmd批量跑顺序不能乱# 按依赖顺序执行每个脚本里用分批符 GO 分隔 sqlcmd -S localhost -U sa -P yourpwd -i 00_init.sql sqlcmd -S localhost -U sa -P yourpwd -i 01_ddl.sql sqlcmd -S localhost -U sa -P yourpwd -i 02_dml.sql sqlcmd -S localhost -U sa -P yourpwd -i 03_query.sql-S指定实例-i指定输入脚本-U/-P是登录凭据。这么做的好处是重建环境只要一条命令不用在图形界面里点半天。比较常见的做法是让00_init.sql具备幂等性重复执行不报错-- 库不存在才建避免重复执行时报 1801 IF DB_ID(StudentCourse) IS NULL CREATE DATABASE StudentCourse; GO USE StudentCourse; GODB_ID返回库的内部 ID不存在时返回NULLGO是 SSMS 认识的批处理分隔符不是 T-SQL 语句走 JDBC 或sqlcmd要注意别把它当 SQL 发给服务端。2.2 三张核心表字段类型选错后面全是补丁学生选课这套模型是数据库课的“Hello World”但类型选错会连累后面所有环节。学号用char(8)是定长语义——所有学号长度一致比较和存储都省事姓名这里我改成了nvarchar(10)因为原始脚本写的char(10)在非中文排序规则下存中文会变问号成绩用smallint就够没必要上int系别用char(20)、班级用char(4)都符合“定长编码”的设定。-- 学生表学号定长姓名用 Unicode 以便存中文 CREATE TABLE Student( Sno char(8) NOT NULL PRIMARY KEY, -- 主键即唯一标识非空 Sname nvarchar(10) NOT NULL, Ssex char(2) NULL, Sage smallint NULL, -- 年龄不会超过 32767 Sdept char(20) NULL, Sclass char(4) NULL, Stotal smallint NOT NULL CONSTRAINT DF_Student_Stotal DEFAULT 0 ); GO -- 课程表Cpno 是间接先修课形成自引用外键 CREATE TABLE Course( Cno char(4) NOT NULL CONSTRAINT PK_Course PRIMARY KEY, Cname nvarchar(40) NULL, Cpno char(4) NULL, Ccredit smallint NULL ); GO -- 选课表组合主键保证一个学生对一门课只有一条记录 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_Student FOREIGN KEY (Sno) REFERENCES Student(Sno), CONSTRAINT FK_SC_Course FOREIGN KEY (Cno) REFERENCES Course(Cno) ); GO三个关键点值得说清楚。第一CONSTRAINT 名字是显式命名将来报错信息里会直接告诉你触发了哪个约束删改约束也有名字可用不命名的话系统会生成PK__Student__7A5E4B1F3C1D2E4A这种随机串排查时很难受。第二SC的主键(Sno, Cno)是组合主键天然防重复选课比在应用层写“先查再插”可靠。第三Cpno指向自己的Cno这叫自引用外键插入顺序会被卡住后面第 5 章会专门讲这个坑。还有一张练习用的Employee表字段用了中文列名编号、姓名、部门、工资、职称工资numeric(8,2)表示总长 8 位、小数 2 位最大能存 999999.99用float存钱迟早会对不上账。2.3 ALTER TABLE加列、改类型、补唯一约束表建好之后需求总会变ALTER TABLE就是给已经上线的表“打补丁”。原始实验里做了三件事加班级列、把年龄从int收窄成smallint、给课程名加唯一约束。-- 1. 加列历史行自动填 NULL如果要求非空必须同时给默认值 ALTER TABLE Student ADD Sclass char(4) NULL; GO -- 2. 改类型smallint 范围 -32768~32767收窄前先确认没有越界数据 ALTER TABLE Student ALTER COLUMN Sage smallint; GO -- 3. 加唯一约束前先查有没有重复值否则直接报错 SELECT Cname, COUNT(*) AS cnt FROM Course GROUP BY Cname HAVING COUNT(*) 1; GO ALTER TABLE Course ADD CONSTRAINT UQ_Course_Cname UNIQUE (Cname); GOALTER COLUMN有两个限制一是被索引、主键、CHECK引用的列改类型会失败得先删再改再建二是类型只能往“更宽”的方向改相对安全int改smallint时如果表里有超过 32767 的值会直接报算术溢出。加UNIQUE约束也是一样SQL Server 默认会顺手建一个唯一索引所以它本质上同时改了结构和索引。注意ALTER TABLE ... ADD 列 NOT NULL在表里已有数据时必须带DEFAULT否则报“无法将 NULL 插入不允许 NULL 的列”。2.4 索引与视图四个索引里有两个其实是冗余的原始脚本建了四个索引思路是对的但有两个值得掰开看-- 普通索引加速按课程名模糊/等值查找 CREATE INDEX iCname ON Course(Cname); GO -- 唯一索引保证姓名不重名现实中这条约束其实很危险 CREATE UNIQUE INDEX iSname ON Student(Sname); GO -- 聚簇索引决定数据物理存储顺序这里是 (Sno 升序, Cno 降序) CREATE CLUSTERED INDEX iSnoCno ON SC(Sno, Cno DESC); GO -- 这一条是重复的Cno 上已经由 PK_Course 建了聚簇唯一索引 CREATE UNIQUE INDEX iCno ON Course(Cno); GO聚簇索引一个表只能有一个因为它决定了行在磁盘上的排列。SC表的PK_SC是组合主键默认已经建了聚簇索引再建iSnoCno会冲突正确做法是先把主键改成非聚簇、再建自己的聚簇索引。iCno更是纯冗余——主键自带唯一索引重复建只是多占一份页和维护开销。这两处正是“照着抄会翻车”的典型。视图这部分原始报告建了三个IS_Student只暴露信息系学生的学号姓名年龄S_G按学号汇总平均分XK_VIEW想一次查出学生、课程、成绩全套信息。-- 简单视图行过滤 列裁剪天然是权限控制的抓手 CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept IS; GO -- 带统计的视图列必须显式起别名否则列名会是 (无列名) CREATE VIEW S_G(Sno, Gavg) AS SELECT Sno, AVG(CAST(Score AS DECIMAL(5,2))) FROM SC GROUP BY Sno; GO -- 多表视图不要写 Student.*, Course.*列名会撞车 CREATE VIEW XK_VIEW AS SELECT s.Sno AS 学号, s.Sname AS 姓名, c.Cno AS 课程号, c.Cname AS 课程名称, sc.Score AS 成绩 FROM Student s JOIN SC sc ON s.Sno sc.Sno JOIN Course c ON c.Cno sc.Cno; GOStudent.*加Course.*的写法在 SSMS 里会直接报“视图中列名重复”因为两张表都有Sno/Cno这类字段。带聚合的视图S_G是不可更新的只能查往里面INSERT会报错简单视图IS_Student在只涉及单表、且包含主键的情况下才可更新。3. 数据操作与查询从 INSERT 到 EXISTS 的完整链路3.1 插入数据与 SELECT INTO 造临时表插入这块原始脚本用的是最省事的INSERT INTO 表 VALUES (...)不带列清单。表建好之后如果中途加过列这种写法立刻就会因为“列数与值数不匹配”崩掉。稳妥的写法是永远写列清单并用多行VALUES一次插一批-- 显式列出列名避免表结构变更后语句失效 INSERT INTO Student (Sno, Sname, Ssex, Sage, Sdept, Sclass) VALUES (20100001, N李勇, N男, 20, CS, 1001), (20100002, N刘晨, N女, 19, CS, 1001), (20100021, N王敏, N女, 18, MA, 1002), (20100031, N张立, N男, 19, IS, 1003), (20100003, N刘洋, N女, NULL, NULL, 1001), (20100010, N赵斌, N男, 19, IS, 1005); GON李勇前缀是 Unicode 字符串字面量配合nvarchar列才能保证中文不乱码。多行VALUES在 SQL Server 2008 之后才支持单条语句最多 1000 行再多就要拆批。批量灌数据另有一个套路——用SELECT ... INTO直接从查询结果建表做临时的数据副本非常快-- 复制 SC 的结构和数据到执行用临时表不含主外键 SELECT * INTO tmpSC FROM SC; GO -- 验证行数是否一致 SELECT (SELECT COUNT(*) FROM SC) AS src, (SELECT COUNT(*) FROM tmpSC) AS dst;SELECT INTO只复制列的定义和数据主键、外键、索引、默认值统统不跟过来这一点很多人会误以为它等同于CREATE TABLE ... AS。它的好处是省事坏处是tmpSC上没有约束随便删随便改正好用来做“先在小表上演练删除”的活儿。3.2 更新与删除把子查询写进 WHERE更新部分最典型的场景是“按另一个表的值决定本表怎么改”。比如计算机系全体学生成绩加 5 分原始写法用的是关联子查询-- 原始写法CS (关联子查询)学生系别为 NULL 时条件为 UNKNOWN UPDATE SC SET Score Score 5 WHERE CS (SELECT s.Sdept FROM Student s WHERE s.Sno SC.Sno);这个写法能跑但有两个隐患一是学生Sdept为NULL时子查询返回NULL等值比较结果是UNKNOWN那一行不会被更新看起来像“漏更新”二是子查询如果因为数据脏返回多行会直接报“子查询返回的值不止一个”。换成IN或者JOIN更稳-- 推荐写法语义清晰NULL 行被自然排除 UPDATE SC SET Score ISNULL(Score, 0) 5 WHERE Sno IN (SELECT Sno FROM Student WHERE Sdept CS); GO -- 或者 UPDATE ... FROM多表条件下可读性更好 UPDATE sc SET sc.Score ISNULL(sc.Score, 0) 5 FROM SC sc JOIN Student s ON s.Sno sc.Sno WHERE s.Sdept CS; GOISNULL(Score, 0)处理的是“选课了但还没出成绩”的NULL行否则NULL 5还是NULL学生成绩永远补不上。这类空值处理是实际项目里最常见的坑之一。删除操作要注意的是执行顺序和不可逆。原始实验的办法很聪明先在tmpSC上删确认逻辑没错再对真表动手。BEGIN TRAN; -- 包一层事务出错能回滚 DELETE FROM tmpSC WHERE Sno IN (SELECT Sno FROM Student WHERE Sdept CS); -- 看一眼影响行数是否符合预期 SELECT ROWCOUNT AS 已删除行数; ROLLBACK TRAN; -- 确认无误后改成 COMMITROWCOUNT返回上一条语句影响的行数是最便宜的自检手段。BEGIN TRANROLLBACK这套组合我一般先跑一遍看数字改成COMMIT再跑第二遍比事后从备份恢复省事太多。3.3 单表查询与分组统计单表查询的花样基本集中在这几个关键字上DISTINCT去重、BETWEEN范围、IN集合、LIKE模糊、IS NULL判空、ORDER BY排序。写的时候有几个细节值得记-- 出生年份这种表达式列一定要起别名否则客户端拿到的列名很难看 SELECT Sname, 2024 - Sage AS 出生年份, LOWER(Sdept) AS 系别小写 FROM Student; GO -- 排序先按系升序同系内按年龄降序 SELECT Sno, Sname, Sdept, Sage FROM Student ORDER BY Sdept ASC, Sage DESC; GO -- 模糊查询姓刘的学生% 是任意长度通配 SELECT Sno, Sname FROM Student WHERE Sname LIKE N刘%; GO -- 空值判断只能用 IS NULLScore NULL 永远不成立 SELECT Sno, Cno FROM SC WHERE Score IS NULL; GODISTINCT作用在整个选择列表上SELECT DISTINCT Sno, Cno是对两列的组合去重不是只对Sno去重这个误解坑过不少人。LIKE前面带%如%刘会导致索引失效全表扫描数据量大时性能差异非常明显。分组统计的重点是WHERE和HAVING的分工WHERE在分组前过滤行HAVING在分组后过滤组。-- 每门课的选课人数、平均分、最高分 SELECT Cno AS 课程号, COUNT(*) AS 选课人数, AVG(CAST(Score AS DECIMAL(5,2))) AS 平均分, MAX(Score) AS 最高分 FROM SC GROUP BY Cno; GO -- 只保留平均分超过 90 的课程 SELECT Cno AS 课程号, COUNT(*) AS 选课人数, AVG(CAST(Score AS DECIMAL(5,2))) AS 平均分 FROM SC WHERE Score IS NOT NULL -- 分组前剔除空成绩 GROUP BY Cno HAVING AVG(CAST(Score AS DECIMAL(5,2))) 90; GOAVG会自动忽略NULL但分母是“非空行数”而不是总行数所以一门课有人没成绩时平均分会偏高。想要把没成绩的按 0 算得用AVG(ISNULL(Score,0))语义完全不同。CAST成DECIMAL是为了避免整数除法或者浮点尾数问题AVG对int列返回的是int85 和 86 的平均会变成 85这个细节能让统计报表对不上账。3.4 连接查询、嵌套查询与集合运算多表连接有三种写法本质一样可读性差别不小-- 1. WHERE 里写连接条件最老派但实验报告里最常见 SELECT s.Sno, s.Sname, c.Cname, sc.Score FROM Student s, SC sc, Course c WHERE s.Sno sc.Sno AND sc.Cno c.Cno; GO -- 2. JOIN ... ON连接条件和过滤条件分开推荐 SELECT s.Sno, s.Sname, c.Cname, sc.Score FROM Student s JOIN SC sc ON s.Sno sc.Sno JOIN Course c ON c.Cno sc.Cno WHERE sc.Score 90; GO -- 3. 左外连接没选课的学生也要出现在结果里 SELECT s.Sno, s.Sname, sc.Cno, sc.Score FROM Student s LEFT JOIN SC sc ON s.Sno sc.Sno; GO内连接会丢掉“没选课的学生”LEFT JOIN才会把他们保留下来右侧字段填NULL。做“哪些学生一门课都没选”这类统计时必须用外连接用内连接永远查不出空集。自连接是另一个高频场景比如查每门课的间接先修课-- first 是课程本身second 是先修课的先修课 SELECT first.Cno AS 课程号, second.Cpno AS 间接先修课 FROM Course first JOIN Course second ON first.Cpno second.Cno; GO嵌套查询里最有代表性的是相关子查询内层引用了外层的列逻辑上相当于对每一行外层数据跑一次内层-- 找出每门成绩超过该生平均分的课程 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); GO -- EXISTS选过 1 号课的学生只关心存在性不关心返回什么 SELECT s.Sname FROM Student s WHERE EXISTS (SELECT 1 FROM SC sc WHERE sc.Sno s.Sno AND sc.Cno 1); GOEXISTS的子查询SELECT 1和SELECT *性能一样优化器只判断有没有行不会真的把列取出来。相关子查询的代价是理论上 O(n²)数据量大时改成JOIN加派生表会快很多。集合运算三条语句值得单独记UNION并集并去重UNION ALL不去重更快INTERSECT交集EXCEPT差集。-- 计算机系的学生 并上 年龄不超过 19 的学生 SELECT * FROM Student WHERE Sdept CS UNION SELECT * FROM Student WHERE Sage 19; GO -- 计算机系 且 年龄不超过 19 SELECT * FROM Student WHERE Sdept CS INTERSECT SELECT * FROM Student WHERE Sage 19; GO -- 计算机系 但 年龄大于 19差集的语义要看清 SELECT * FROM Student WHERE Sdept CS EXCEPT SELECT * FROM Student WHERE Sage 19; GOUNION参与运算的两边列数、列序、类型必须兼容列名取第一条查询的。EXCEPT是“左边有、右边没有”方向反了结果完全不同这是最容易写错的地方。4. 完整性约束与触发器让数据库自己拒绝脏数据4.1 声明式约束的四种写法数据完整性这件事能交给数据库就别交给应用层。原因是应用层可能有多个入口Web、批处理脚本、手工运维哪个漏了校验就产生脏数据而数据库约束是所有入口都得过的闸门。CREATE TABLE Student( Sno char(8) NOT NULL, Sname nvarchar(10) NOT NULL, Ssex char(2) NULL, Sage smallint NULL, Sdept char(20) NULL, Sclass char(4) NOT NULL, Stotal smallint NOT NULL CONSTRAINT DF_Stotal DEFAULT 0, CONSTRAINT PK_Student PRIMARY KEY (Sno), -- 实体完整性 CONSTRAINT CK_Student_Ssex CHECK (Ssex IN (N男, N女)), -- 域完整性 CONSTRAINT CK_Student_Sage CHECK (Sage BETWEEN 10 AND 60) ); GO CREATE TABLE SC( Sno char(8) NOT NULL, Cno char(4) NOT NULL, Score smallint NULL, CONSTRAINT PK_SC PRIMARY KEY (Sno, Cno), CONSTRAINT CK_SC_Score CHECK (Score 0 AND Score 100), -- 成绩域 CONSTRAINT FK_SC_Student FOREIGN KEY (Sno) REFERENCES Student(Sno), CONSTRAINT FK_SC_Course FOREIGN KEY (Cno) REFERENCES Course(Cno) ); GO四类约束分工明确PRIMARY KEY管实体完整性FOREIGN KEY管参照完整性CHECK加DEFAULT管域完整性NOT NULL是最基础的域约束。这里有个细节——CHECK (Score 0)对NULL是放行的因为NULL 0结果是UNKNOWN而不是FALSE所以“允许空成绩”和“成绩必须大于 0”可以共存不需要额外处理。4.2 用“故意犯错”验证约束真的生效约束建完不验证等于没建。最有效的验证办法就是故意写几条违规语句看它报不报错。-- 1. 主键冲突同一学号插两次应报 2627 INSERT INTO Student (Sno, Sname, Ssex, Sage, Sdept, Sclass) VALUES (20100101, N李斌, N男, 20, CS, 1001); INSERT INTO Student (Sno, Sname, Ssex, Sage, Sdept, Sclass) VALUES (20100101, N李斌, N男, 20, CS, 1001); GO -- 2. 改主键撞车把张立的学号改成王敏的学号应报 2627 UPDATE Student SET Sno 20100021 WHERE Sname N张立; GO -- 3. 唯一约束插入同名课程应报 2627 INSERT INTO Course (Cno, Cname, Cpno, Ccredit) VALUES (9, N数据结构, 7, 3); GO -- 4. 检查约束成绩填 120应报 547 INSERT INTO SC (Sno, Cno, Score) VALUES (20100001, 1, 120); GO -- 5. 外键约束往不存在的学生上挂选课记录应报 547 INSERT INTO SC (Sno, Cno, Score) VALUES (99999999, 1, 80); GO -- 6. 默认值不给 Stotal 值应自动变成 0 INSERT INTO Student (Sno, Sname, Ssex, Sclass) VALUES (20100102, N张盛, N男, 1008); SELECT Sno, Sname, Stotal, Sage, Sdept FROM Student WHERE Sno 20100102; GO第 2 条特别值得看——很多人以为UPDATE主键不冲突其实改完之后如果和已有主键重复照样被拦。第 6 条验证的是DEFAULT和NULL的边界显式省略的列走默认值0而Sage、Sdept没给默认值也没写NOT NULL就变成NULL。报错代码记住几个就够用了2627主键或唯一约束冲突547外键或CHECK约束失败515非空列插入NULL8152字符串被截断。4.3 触发器与用户鉴别报告点到但没展开的部分原始实验目的里明确写了“了解触发器的机制和使用”正文却没有对应实现。这部分在实际项目里很有用典型场景是“选课成功后自动维护学生已选学分总数”。-- 选课插入后把该生的总学分加上这门课的学分 CREATE TRIGGER trg_SC_Insert ON SC AFTER INSERT AS BEGIN SET NOCOUNT ON; -- 不返回影响行数消息省得客户端误判 -- inserted 是伪表代表本次插入的行可能是多行 UPDATE s SET s.Stotal ISNULL(s.Stotal, 0) c.Ccredit FROM Student s JOIN inserted i ON s.Sno i.Sno JOIN Course c ON c.Cno i.Cno; END; GOAFTER表示语句执行成功后才触发inserted和deleted是两个伪表分别装新值和旧值UPDATE时两张表都有数据。这里必须按集合的方式写一次插入 100 行时触发器只跑一次inserted里就是 100 行如果写成SELECT sno Sno FROM inserted这种变量赋值只会处理最后一行这是触发器最经典的翻车点。用户鉴别与数据控制这块课程标题里点了名落地就是三步建登录名、建数据库用户、授权。-- 1. 服务器级登录名 CREATE LOGIN stu_reader WITH PASSWORD Str0ng!Passw0rd; GO -- 2. 数据库级用户映射到上面的登录名 USE StudentCourse; CREATE USER stu_reader FOR LOGIN stu_reader; GO -- 3. 只给视图的查询权限不给基表 GRANT SELECT ON dbo.IS_Student TO stu_reader; DENY SELECT ON dbo.Student TO stu_reader; GO -- 验证以 stu_reader 身份查 Student 应被拒绝 EXECUTE AS USER stu_reader; SELECT * FROM dbo.Student; -- 报错拒绝了对对象 Student 的 SELECT 权限 REVERT; GODENY优先级高于GRANT这在排查“明明授了权为什么还查不了”时非常关键。用视图做权限边界是很常见的做法基表锁死只暴露视图里那几列学生看不到别人的成绩也看不到表结构。5. 避坑与排查五条亲手踩出来的血泪经验5.1 建表与约束阶段的坑现象插入第一条课程记录就报 547外键冲突。原因Course.Cpno是自引用外键1 数据库系统原理的先修课是5而5还没插进去。外键检查在每条INSERT后立即执行不看整批数据插完之后的状态。解决要么按“先修课编号小于本课程编号”的顺序插入要么先把数据灌完、再ALTER TABLE补外键。原始实验用的是后一种先建表不带外键插完数据再ALTER TABLE Course ADD FOREIGN KEY (Cpno) REFERENCES Course(Cno)这个顺序是对的。生产环境还有一种做法是先ALTER TABLE ... NOCHECK CONSTRAINT禁用外键、灌完再WITH CHECK CHECK CONSTRAINT启用但中间那段窗口里数据是不设防的。现象给Course.Cname加UNIQUE约束时提示“违反了 UNIQUE KEY 约束”或者插入第二门“数据结构”时报 2627。原因加约束时会检查存量数据有重复就失败约束生效后再插重复值同样被拦。课程名在现实里本来就可能重复不同学院各开一门“数据结构”唯一约束在这里是过度设计。解决加约束前先跑一遍分组查重确实需要唯一性的话应该用(Cno)做主键、Cname只建普通索引或者加(开课学院, Cname)组合唯一。现象中文姓名查出来变成问号或者LIKE N刘%一条都匹配不到。原因Sname定义成char(10)时在非 Unicode 排序规则下每个中文汉字占两个字节char(10)只放得下 5 个汉字超了会被截断更糟的是如果列是变长编码写入时就已经丢了信息。解决建表时中文列一律用nvarchar(n)字符串字面量加N前缀。已经建了的表改类型ALTER TABLE Student ALTER COLUMN Sname nvarchar(10)改之前先确认没有索引依赖。5.2 查询与更新阶段的坑现象delete from Student where Sno201000022执行成功但提示影响 0 行数据还在。原因Sno是char(8)而201000022有 9 位。SQL Server 在等值比较时按列的定长语义处理超长字面量要么被截断成20100002、要么不匹配具体取决于比较上下文最终结果就是“删了但没删掉”。这种语句不报错最危险。解决学号一律校验长度WHERE LEN(Sno) 8或者干脆把Sno改成varchar(8)并在应用层做格式校验。更根本的办法是主键用自增整数业务编号单独一列加唯一索引。现象SELECT cno, AVG(grade) FROM sc GROUP BY cno报“列名 grade 无效”。原因同一份报告里成绩列时而叫Score、时而叫GradeALTER TABLE时代码复制来复制去就串了。SQL Server 不会帮你猜直接报列名无效。解决全库统一命名建表时把列名定死改名前用sp_rename SC.Score, Grade, COLUMN一次改干净别靠记。为了避免这类问题我习惯让所有脚本的列名都从一份数据字典里来别手敲。现象SELECT AVG(Score) FROM SC算出来的平均分是整数小数部分没了。原因Score是smallintAVG的返回类型跟着输入走整除截断。85 和 86 平均出来是 85。解决AVG(CAST(Score AS DECIMAL(5,2)))或者在表设计阶段就把成绩定义成numeric(5,2)。统计类查询里凡是涉及除法、平均的一律先转DECIMAL这是硬规矩。6. 二次开发把它改成你自己的练手库这套脚本跑通之后真正的价值在于它能当底座做二次开发。我的做法是保留三张核心表的结构往外扩三个方向。第一个方向是把“班级”从字符串变成实体。现在Sclass是char(4)班级名称改一次要更新全表。加一张Class(ClsId, ClsName, MajorId)Student里存ClsId外键统计“每个班每门课的平均分”就从三表连接变成四表连接正好练手。第二个方向是给成绩加历史版本SC表只存当前成绩再建一张SC_History(Sno, Cno, OldScore, NewScore, ChangedAt, Operator)用AFTER UPDATE触发器写入顺带把4.3节那个触发器改成写历史而不是累加学分。第三个方向是把查询封装成视图和存储过程报表类查询全部走视图参数化查询走CREATE PROCEDURE对外只暴露视图和存储过程基表权限全收掉。验证改造是否正确的办法很土但有效写一个99_verify.sql里面全是断言式的对比查询每条都返回一行“预期值 / 实际值 / 是否一致”。-- 断言每个学生的 Stotal 应等于其所有课程学分之和 SELECT s.Sno, s.Stotal AS 实际值, ISNULL(SUM(c.Ccredit), 0) AS 预期值, CASE WHEN s.Stotal ISNULL(SUM(c.Ccredit), 0) THEN OK ELSE MISMATCH END AS 结果 FROM Student s LEFT JOIN SC sc ON sc.Sno s.Sno LEFT JOIN Course c ON c.Cno sc.Cno GROUP BY s.Sno, s.Stotal HAVING s.Stotal ISNULL(SUM(c.Ccredit), 0); -- 只输出不一致的行空结果就是全对这个查询跑出来是空集就说明触发器维护的冗余字段没有漂移。我把这套断言脚本挂在每次改完触发器之后跑一遍比肉眼翻表可靠得多。最后提一句改造成本最低但收益最高的技巧把所有INSERT都改成显式列清单加多行VALUES把所有UPDATE/DELETE都套进BEGIN TRAN ... ROLLBACK/COMMIT把所有涉及聚合的数值列都CAST成DECIMAL。这三件事花不了半小时但能挡掉后面八成莫名其妙的报错。从那以后我每次新建练手库都强制先写00_init.sql并让它可重复执行再写一份99_verify.sql做断言——环境能一键重建、结果能自动校验剩下的才是安心写业务。希望帮到你。本文还有配套的精品资源点击获取
