简介面向软件工程专业学生及数据库课程学习者的实验报告文档对应太原理工大学《数据库概论》课程聚焦交互式SQL语句的使用、数据完整性、用户鉴别与数据控制等实验主题适合课内实验参考、期末复习与上机操作对照。压缩包内共1个docx文件约1.69MB集中呈现实验目的、实验平台与主要实验内容及结果便于按实验流程查阅。已有311人学习具备一定的课程参考价值。文档围绕Microsoft SQL Server 2016展开涵盖基本表的创建、修改与删除索引与视图创建以及插入、修改数据等操作并给出可执行的SQL语句和检查结果。读者可据此复现Student、Course、SC等表的定义与约束理解主键、外键、唯一索引、聚簇索引的用法梳理常见语法与排错思路快速完成实验报告整理与知识点复盘。1. 从建表到授权一套数据库实验的完整知识地图多数人第一次做数据库实验把重点放在“把 SQL 敲对”结果一写 INSERT 就报错一写子查询就没结果。真正拉开差距的不是记住多少条语句而是是否具备“约束先行”的思维在动手建表之前先想清楚这张表的主键由哪几列构成、哪些列允许为空、哪些列必须指向另一张表。这份实验报告的价值在于它把数据库系统原理里最容易分开学习的四层串成了一条线对象层表、索引、视图、操作层插入、更新、删除、查询层单表→分组→连接→嵌套→集合、控制层约束、触发器、授权。正在系统学数据库的软件工程方向同学、准备实验验收或期末复习的人都可以把它当成一张复习地图哪一层薄弱就翻到对应的那一章。2. 数据定义与对象管理建表、索引、视图的落点数据定义是整门课的地基。地基没打好后面所有查询、约束、授权的报错都会找上门来而且报错信息往往只告诉你“违反了某个约束”不会告诉你约束当初是怎么写的。这一章把实验里出现过的四类对象——表、约束、索引、视图——按“先建什么、后改什么、什么时候删”的顺序理一遍。2.1 CREATE TABLE 的骨架字段、类型、约束三件套一张表的定义语句可以拆成三段来看列名与数据类型是第一段列级约束是第二段表级约束是第三段。实验里学生表、课程表、选课表三张表的写法恰好覆盖了三种典型结构单列主键、可空外键、复合主键。先看下面这段按实验原始结构整理的建表语句。CREATE TABLE Student( Sno CHAR(8) PRIMARY KEY, -- 列级主键学号唯一标识一名学生 Sname CHAR(10), Ssex CHAR(2) CHECK(Ssex IN (男,女)), -- 取值只能是两个汉字之一 Sage INT, Sdept CHAR(20), Sclass CHAR(4) NOT NULL DEFAULT 1001 -- 非空 默认值插入时可省略 ); CREATE TABLE Course( Cno CHAR(4) PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4), -- 先修课号可以为空 Ccredit INT ); CREATE TABLE SC( Sno CHAR(8), Cno CHAR(4), Score INT, PRIMARY KEY(Sno, Cno), -- 表级复合主键一个学生一门课只能一条 FOREIGN KEY(Sno) REFERENCES Student(Sno), FOREIGN KEY(Cno) REFERENCES Course(Cno) );这段代码里有四个参数值得反复看。CHAR(8)是定长字符存2010001这种七位学号时会自动补一个空格后面做等值比较容易出问题所以学号长度必须严格对齐如果长度不定用VARCHAR更稳。SC表的主键写成表级约束PRIMARY KEY(Sno, Cno)因为主键由两列组成没法写在某一列后面这是复合主键唯一正确的写法。两个FOREIGN KEY让选课表同时依赖学生表和课程表意味着插入一条选课记录之前对应的学生和课程必须已经存在。DEFAULT 1001只在省略该列时生效显式写NULL是会被拒绝的——这一点在检查默认值约束时经常被忽略。数据类型的选择也有讲究。工资用NUMERIC(8,2)而不是FLOAT因为金额需要精确到分浮点数会出现0.10.2这类精度漂移生日用DATETIME而不是字符串才能在后面做日期函数运算。类型选错不会立刻报错但会在统计和比较时悄悄出错属于典型的“当时不疼、事后难受”。2.2 ALTER TABLE 与 DROP TABLE改结构时的三个边界建表之后改结构实验里出现了三类操作加列、改列类型、加约束。三者的风险等级完全不同。ALTER TABLE Student ADD Sclass CHAR(4); -- 新增列已有行该列自动为 NULL ALTER TABLE Student ALTER COLUMN Sage SMALLINT; -- 缩小数值范围超出范围会报错 ALTER TABLE Course ADD UNIQUE(Cname); -- 给已有列补唯一约束 ALTER TABLE SC DROP CONSTRAINT SC_CHECK; -- 先删旧约束 ALTER TABLE SC ADD CONSTRAINT SC_CHECK CHECK(Grade 0 AND Grade 100); -- 再建新约束ADD加列最安全但要注意新列如果没有DEFAULT且允许为空已有行的该列全是NULL后面写NOT NULL查询时就会漏数据。ALTER COLUMN改类型是风险最高的一步把INT改成SMALLINT一旦表里存在超过 32767 的年龄值虽然现实中不可能但测试数据里常见整条语句直接失败把CHAR改短还会截断数据。改约束必须走“先 DROP 再 ADD”两步SQL Server 不支持直接覆盖同名约束DROP CONSTRAINT后面跟的是约束名而不是列名这是最容易写错的地方。DROP TABLE的边界在外键上。实验里删除Employee表能一次成功是因为它没有任何表引用它如果去删Student表而SC表的外键还指着它语句会被拒绝。正确顺序是先删子表SC再删父表Student。这个顺序在后面讲数据完整性的章节还会再出现一次因为插入和删除数据时遵循的是同一条依赖链。2.3 索引三分类普通、唯一、聚簇分别解决什么问题索引是这门课里最容易被当成“加分项”而忽略的模块。实验里建了四个索引分别对应三种类型值得逐条拆开看。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); -- 主键上再建唯一索引冗余演示普通索引只影响查询计划不改变任何数据规则Cname上建索引是为了让按课程名模糊查询少扫全表。唯一索引多了一层约束含义如果表里已经存在两个同名课程CREATE UNIQUE INDEX会直接失败所以它常用于“业务上必须唯一但没设成主键”的列。聚簇索引则决定数据行的物理排列顺序一张表只能有一个实验里SC表按(Sno, Cno DESC)建聚簇索引意味着数据在磁盘上先按学号排、同学号内按课程号倒序排范围查询Sno BETWEEN ...会非常快。有两个细节容易漏。第一复合索引的列顺序不能随便调(Sno, Cno)和(Cno, Sno)是两个完全不同的索引前者能支持“按学号查”后者不能。第二主键默认已经建了聚簇或非聚簇索引再在Cno上建唯一索引属于重复劳动实验里保留它更多是为了演示语法真实项目里应该先查sys.indexes再决定建不建。2.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; CREATE VIEW XK_VIEW AS -- 多表连接视图把三表查询封装成一个名字 SELECT Student.*, Course.*, Score FROM Student, SC, Course WHERE Student.Sno SC.Sno AND SC.Cno Course.Cno;S_G这个视图说明了一条硬规则只要查询里有聚合函数或表达式列视图的列名就必须在CREATE VIEW后面逐个列出来不能指望系统自动命名。XK_VIEW用的是SELECT Student.*, Course.*两张表都有Sno之外的公共列吗没有但Student和Course各自的全列展开后列名可能重复比如都有Cno之外的字段真实项目里更推荐显式列出需要的列避免视图定义因为基表加列而失效。视图的真正价值是把复杂查询固化成一个名字让后续的SELECT * FROM XK_VIEW WHERE ...读起来像查一张普通表。代价是更新受限含聚合、DISTINCT、GROUP BY的视图基本不能直接INSERT或UPDATES_G就属于这一类。复习时记住一句判断能从视图无损反推回基表行的才可能更新。3. 数据操纵与查询从增删改到五类查询的推进数据操作是实验里代码量最大的部分也是最容易“语法对了、结果错了”的部分。这一章按“写数据增删改→ 读数据五类查询”的顺序推进每一节都对应实验里出现过的一类操作重点放在那些运行结果和预期不一致的场景上。3.1 插入的四种形态与列顺序陷阱实验里的插入操作出现了四种写法覆盖面比想象中全。-- 形态一不写列名按建表顺序给全所有列 INSERT INTO Student VALUES(20100001,李勇,男,20,CS,1001); -- 形态二显式列名可以跳过有默认值或允许为空的列 INSERT INTO Student(Sno, Sname, Ssex, Sclass) VALUES(20100102,张盛,男,1008); -- 形态三从另一张表批量导入 INSERT INTO cs_Student(学号, 姓名, 年龄) SELECT Sno, Sname, Sage FROM Student WHERE Sdept CS; -- 形态四SELECT INTO 直接建表并灌数据 SELECT * INTO tmpSC FROM SC;形态一最省事也最危险一旦表结构后期加了列比如ALTER TABLE Student ADD Sclass原来那条不写列名的INSERT立刻报“列数不匹配”所有历史脚本都要跟着改。形态二才是工程里推荐的写法列名和值的对应关系一目了然加列不影响旧语句。形态三要求源列和目标列的类型兼容、数量一致SELECT里的列顺序决定写入顺序不是按列名匹配。形态四的SELECT INTO会新建一张表并复制数据但不复制约束、索引、主键所以tmpSC里可以插入重复的(Sno, Cno)这一点在做删除演练时反而是好事因为删错了不影响原表。还有一个隐蔽陷阱CHAR类型的列插入短字符串会自动补空格CS存进CHAR(20)会变成CS 18个空格显示时看不出来但WHERE Sdept CS能匹配上SQL Server 比较时会忽略尾部空格换到别的数据库就未必。复习时记住定长字符是补齐的变长字符是不补的。3.2 UPDATE 与 DELETEWHERE 决定生死更新和删除的语法只有一句风险全在WHERE上。实验里最有价值的是那几个带子查询的更新它们展示了“条件不在目标表里”时怎么写。-- 单表条件更新 UPDATE Student SET Sage 22 WHERE Sno 20100001; -- 全表更新没有 WHERE所有行都变 UPDATE Student SET Sage Sage 1; -- 带相关子查询的更新给计算机系学生成绩加 5 分 UPDATE SC SET Score Score 5 WHERE CS (SELECT Sdept FROM Student WHERE Student.Sno SC.Sno); -- 修改主键值会触发外键检查 UPDATE Student SET Sno 20100025 WHERE Sno 20100021;第三条语句的写法值得注意把常量CS写在等号左边、子查询写在右边是因为子查询返回的是单值SC.Sno作为相关条件把内外层连起来。如果写成WHERE Sdept CS系统会报“列名不明确”因为SC表里没有Sdept。第四条修改主键是整章最容易出错的一步Student.Sno被SC表的外键引用如果SC里存在20100021的选课记录这条UPDATE会直接被拒绝除非外键定义了ON UPDATE CASCADE。实验里能成功是因为该学生当时没有选课记录。删除操作同理。实验里先用SELECT * INTO tmpSC FROM SC复制一份再在副本上练删除这是一种很实用的“后悔药”习惯真实环境里删数据之前先SELECT出要删的行确认数量再执行DELETE最后用同样的WHERE查一次确认剩 0 行。删除全部记录用DELETE FROM tmpSC不要写DROP TABLE前者只清数据保留结构后者连表一起没了。3.3 单表查询的四类条件与结果集整形单表查询是后面所有复杂查询的零件库。实验里出现的条件类型可以归成四类每一类都有对应的关键字和边界。条件类型关键字实验中的例子边界提醒范围BETWEEN ... AND ...Sage BETWEEN 20 AND 23闭区间包含两端集合IN (...)Sdept IN (IS,MA,CS)列表里有 NULL 不影响匹配模糊LIKESname LIKE 刘%%任意长度_单个字符空值IS NULLScore IS NULL不能写 NULL永远不成立结果集的整形手段同样有四类用表达式列算出衍生值2004 - Sage得到出生年份、用AS或空格给列起别名DEPARTMENT LOWER(Sdept)是 SQL Server 特有的写法标准写法是LOWER(Sdept) AS Department、用DISTINCT去重SELECT DISTINCT Sno FROM SC把重复选课记录压成一人一条、用ORDER BY排序ORDER BY Sdept, Sage DESC表示先按系升序、系内按年龄降序。DISTINCT和ALL的区别值得单独记一笔ALL是默认行为保留重复行DISTINCT对整行去重。如果写SELECT DISTINCT Sno, Cno FROM SC去重的是(Sno, Cno)这个组合不是单独对Sno去重。很多人在这里翻车以为加了DISTINCT就能得到“选课学生人数”正确写法是COUNT(DISTINCT Sno)。3.4 分组统计聚合函数、GROUP BY 与 HAVING 的三段式分组查询的执行顺序是WHERE → GROUP BY → 聚合 → HAVING → ORDER BY这个顺序决定了什么能写在哪。实验里的几个例子恰好覆盖了三个阶段。-- 阶段一只聚合不分组 SELECT COUNT(*) AS 总人数 FROM Student; -- 阶段二分组后聚合 SELECT Cno AS 课程号, COUNT(*) AS 人数, AVG(Score) AS 均分, MAX(Score) AS 最高分 FROM SC GROUP BY Cno; -- 阶段三对分组结果再过滤 SELECT Cno AS 课程号, AVG(Score) AS 均分 FROM SC GROUP BY Cno HAVING AVG(Score) 90;整个过程最容易错的是“SELECT列表里的列必须出现在GROUP BY里”。SELECT Cno, Sname, AVG(Score) ... GROUP BY Cno这种写法在严格模式下直接报错因为一个课程号对应多个姓名系统不知道该显示哪个。HAVING和WHERE的分工也常混WHERE在分组前过滤行不能用聚合函数HAVING在分组后过滤组可以用聚合函数。想筛“成绩大于 60 的选课记录再统计”条件放WHERE想筛“平均分大于 90 的课程”条件放HAVING。聚合函数对NULL的处理是个隐形考点AVG(Score)会跳过NULL行分母是实际有成绩的记录数不是总选课数COUNT(*)统计所有行包括NULLCOUNT(Score)只统计非空值。这两组差异在计算“选课人数”和“平均分”时会导致完全不同的数字复习时必须能说清每个聚合函数对NULL的态度。3.5 连接与嵌套从 WHERE 连接走到 EXISTS多表查询在实验里出现了两种写法老式的WHERE连接和新式的JOIN ... ON。两者等价但后者把连接条件和过滤条件分开了可读性更好。-- WHERE 连接连接条件和过滤条件混在一起 SELECT Student.Sno, Sname, Cno, Score FROM Student, SC WHERE Student.Sno SC.Sno AND SC.Cno 2 AND Score 90; -- JOIN ON连接条件和过滤条件分离 SELECT Student.Sno, Sname, Cno, Score FROM Student JOIN SC ON Student.Sno SC.Sno WHERE SC.Cno 2 AND SC.Score 90; -- 外连接保留左表全部行右表没匹配的补 NULL SELECT Student.Sno, Sname, Cno, Score FROM Student LEFT OUTER JOIN SC ON Student.Sno SC.Sno;外连接和普通连接的区别在“没选课的学生”上内连接会把这类学生直接丢掉左外连接会保留他们Cno和Score显示为NULL。判断该用哪种只看一个问题——没有匹配行的主体要不要出现在结果里。要就用外连接不要就用内连接。自连接是连接查询里最绕的一种。实验里“查询每一门课的间接先修课”需要把Course表当成两张表用一张叫first当前课程一张叫second先修课的先修课SELECT first.Cno, second.Cpno FROM Course first, Course second WHERE first.Cpno second.Cno;这里必须给两张表起别名否则Course.Cpno Course.Cno无法区分是哪一行的列。自连接的本质是“同一张表按不同角色参与连接”树形结构、层级关系、前后记录对比都靠它。嵌套查询则是另一种思路把内层查询的结果当成外层查询的条件值。IN适合内层返回多值加比较符适合内层返回单值ANY/ALL适合和内层的一组值整体比较EXISTS只看内层有没有行、不关心具体值。-- IN和“刘晨”同系的学生 SELECT Sno, Sname FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname 刘晨); -- ALL比计算机系所有学生年龄都小 SELECT Sname, Sage FROM Student WHERE Sage ALL (SELECT Sage FROM Student WHERE Sdept CS) AND Sdept CS; -- EXISTS选修了 1 号课程的学生 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno Student.Sno AND Cno 1);EXISTS的子查询里写SELECT *是标准做法因为系统根本不读列只看有没有行返回。它和IN的一个重要差别是EXISTS遇到NULL不受影响而NOT IN的子查询一旦返回NULL整个条件会变成未知结果集为空——这是数据库考试里最爱出的陷阱之一。3.6 集合查询UNION、INTERSECT、EXCEPT 的边界集合查询把两个独立查询的结果按行做并、交、差前提是两边的列数相同、对应列类型兼容。-- 并集计算机系的学生 或者 年龄不大于 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 ALL。UNION的结果列名取自第一个查询排序要写在最后一条语句之后。EXCEPT的方向不能反A EXCEPT B是“在 A 里但不在 B 里”和B EXCEPT A是两个完全不同的结果。用集合查询改写OR条件时要注意UNION会自动去重如果业务上需要保留重复必须写UNION ALL。4. 数据完整性约束、触发器与授权的分工完整性这一章在实验里单独成篇因为它回答的是“数据为什么不能乱写”。约束是数据库替应用层守的第一道门触发器是第二道授权则是决定谁能推这扇门。4.1 五类约束各自守哪道门约束类型关键字守的是什么实验中的例子主键PRIMARY KEY行唯一 非空Sno CHAR(8) PRIMARY KEY外键FOREIGN KEY ... REFERENCES引用完整性SC.Cno指向Course.Cno唯一UNIQUE列值不重复允许一个 NULLALTER TABLE Course ADD UNIQUE(Cname)检查CHECK列值满足布尔表达式Ssex IN (男,女)、Grade BETWEEN 0 AND 100默认/非空DEFAULT/NOT NULL缺省填充、禁止空值Stotal SMALLINT DEFAULT 0这五类约束有明确的优先级和分工。主键约束最强自带唯一和非空两重含义唯一约束比主键弱一点允许存在一个NULLSQL Server 的行为常用于“业务上不该重复但确实可能为空”的列CHECK约束只做单行内的值判断不能跨行、不能查别的表外键约束负责跨表引用是唯一一个会“锁住”删除和更新操作的约束。复习时要能回答一个对比题UNIQUE和PRIMARY KEY到底差在哪答案有三点——主键不允许NULL唯一约束允许一张表只能有一个主键可以有多个唯一约束主键默认会成为聚簇索引的候选唯一约束默认建非聚簇索引。4.2 用“故意违规”验证约束约束写完了不代表生效实验里最有价值的部分是那组“故意写错”的语句。每一条报错都在告诉你哪一层约束在起作用。-- 主键冲突20100001 已存在 INSERT INTO Student VALUES(20100001,李斌,男,20,CS,1001,0); -- 修改主键撞上已有值20100021 属于王敏 UPDATE Student SET Sno 20100021 WHERE Sname 张立; -- 复合主键冲突同一学生同一课程插两次 INSERT INTO SC VALUES(20100001,1,78); -- 复合主键的一部分为 NULL INSERT INTO SC VALUES(20100001, NULL, 78); -- 唯一约束冲突课程名重复 INSERT INTO Course VALUES(9,数据结构,7,3); -- CHECK 约束冲突成绩越界 INSERT INTO SC VALUES(20100002,3,120);第一条和第三条报的是主键重复区别在于前者是单列主键、后者是复合主键报错信息里会明确指出是哪个约束名冲突。第二条报的是主键冲突而不是外键错误因为20100021这个值已经存在于Student表里。第四条报的是“不能将 NULL 插入列”因为复合主键的每一列都隐含NOT NULL。第五条报唯一约束冲突说明UNIQUE(Cname)已经生效。第六条报CHECK约束冲突前提是先把旧的SC_CHECK删掉换成Grade 0 AND Grade 100否则旧的Grade 0 AND Grade 100会先拦住。读报错信息有一个通用方法先看它提到的是哪个约束名PK_、FK_、UQ_、CK_前缀能直接暴露类型再看它说的是“冲突”还是“引用不存在”。前者是重复写入后者是顺序问题。4.3 触发器与约束的边界约束能表达的是“单行内的静态规则”一旦规则需要跨表、需要读取修改前的值、需要在插入后自动更新另一张表就得靠触发器。实验里提到了触发器的机制但没有展开写这里按最常用的两类补一个示意。-- AFTER 触发器插入选课记录后自动累加学生已修学分 CREATE TRIGGER trg_AddCredit ON SC AFTER INSERT AS BEGIN UPDATE Student SET Stotal Stotal (SELECT Ccredit FROM Course WHERE Cno (SELECT Cno FROM inserted)) WHERE Sno (SELECT Sno FROM inserted); END;这段代码里的inserted是触发器专用的逻辑表存放本次插入的所有新行。AFTER表示语句执行成功之后再触发如果AFTER触发器里报错整个插入语句会回滚。另一类是INSTEAD OF触发器它替代原操作执行常用于让不可更新的视图变得可更新。触发器和约束的分工可以记成一句话能用约束表达的绝不用触发器。约束由系统内核执行效率高、行为确定触发器是用户代码嵌套调用、递归触发、事务回滚都会让排查难度翻倍。实验里那些“插入重复主键报错”的场景用约束一行搞定用触发器反而要自己写判断逻辑。4.4 用户鉴别与数据控制授权语句的位置实验名称里提到了“用户鉴别与数据控制”这部分内容在报告正文里没有展开但它是完整性链条的最后一环约束管“数据长什么样”授权管“谁能碰这些数据”。概念上分三层——登录名负责能不能连上服务器数据库用户负责在某个库里是谁角色是一组权限的打包。授权语句的基本形态是两条-- 把 Student 表的查询权限授予某个数据库用户 GRANT SELECT ON Student TO user_a; -- 允许该用户把权限继续转授给别人 GRANT SELECT ON Student TO user_a WITH GRANT OPTION; -- 收回权限 REVOKE SELECT ON Student FROM user_a;GRANT的粒度可以细到列比如只允许查Sno和Sname两列WITH GRANT OPTION是“转授权”收权时要连带收回别人转授出去的权限否则会留下权限残留。这一块在实验里通常只要求跑通语句、看懂效果但复习时要能和约束串起来约束是数据层面的完整性授权是访问层面的完整性两者合起来才叫数据库的安全性。5. 易错点盘点五个最容易翻车的细节这一章单独列出来是因为下面五个问题在实验验收和笔试里出现频率极高而且每一个都属于“看答案觉得懂、自己写就错”的类型。易漏点一NULL不是 0也不是空字符串。为什么容易漏——写WHERE Score NULL语法不报错只是永远返回空结果看起来像“没有数据”而不是“写错了”。更隐蔽的是NOT IN搭配子查询只要子查询结果里有一个NULL整个NOT IN条件就变成未知外层查询返回空集。怎么补——所有涉及空值的判断一律写IS NULL/IS NOT NULL用NOT IN之前先确认子查询列有没有NULL有就改用NOT EXISTS统计平均分时想清楚分母是COUNT(*)还是COUNT(列)。易漏点二CHAR定长补齐导致的比较与长度问题。为什么容易漏——CHAR(8)存七位学号会补一个尾部空格LEN()在 SQL Server 里会忽略尾部空格返回 7而DATALENGTH()返回 8两个函数给出不同答案很容易误判。字符串拼接、导出文件、和外部系统对接时尾部空格会变成脏数据。怎么补——长度不固定的列一律用VARCHAR必须用CHAR时比较前统一RTRIM()建表阶段就确定好学号、身份证号这类“看起来定长但将来会变”的列该用哪种类型。易漏点三GROUP BY与SELECT列表不对应WHERE和HAVING混用。为什么容易漏——SELECT Sno, Sname, AVG(Score) ... GROUP BY Sno在部分宽松配置下能跑出结果只是Sname取的是组内任意一行的值结果“看起来对”但不可复现。HAVING写成WHERE AVG(Score) 90会直接报聚合函数不能出现在WHERE里但很多人靠试错改到HAVING之后就忘了为什么。怎么补——写分组查询前先在纸上列出“哪些列要进GROUP BY、哪些是聚合、哪些是过滤”过滤条件按“分组前/分组后”二分凡是出现聚合函数的条件一律放HAVING。易漏点四外键依赖顺序在建表、插数、删数、改主键四个环节都会发作。为什么容易漏——建表时先建SC后建Student会报“引用的表不存在”插入选课记录时学生还没插会报外键冲突删除学生时选课记录还在会报引用冲突修改被引用的主键值时同样报错。四个环节四条报错本质是同一条依赖链。怎么补——建表顺序按“被引用者在前”删表和数据按“引用者在后”做删除或改主键演练前先在子表里SELECT一下有没有关联行真要级联就在外键上写ON DELETE CASCADE/ON UPDATE CASCADE但要清楚这会绕过应用层的确认逻辑。易漏点五集合查询的列数、类型与去重。为什么容易漏——UNION两边列数不一致会直接报错这个容易发现但列数一致、类型不同比如一边INT一边CHAR会走隐式转换结果可能悄悄丢精度。更常见的是忘了UNION默认去重把“并集”当成“拼接”本该保留的重复行被吃掉。怎么补——写集合查询前先对齐两边的列清单和类型需要保留重复就显式写UNION ALLEXCEPT和INTERSECT的方向写完后用一句自然语言复述一遍确认“谁减谁”没反。6. 复习清单考前把这张表过一遍复习这门课我后来固定成一套流程先不看题把“题目要我改哪张表、这张表被谁引用、改动会不会破坏约束”这三个问题在草稿纸上答完再动笔写 SQL。下面这张表是我按实验题型整理的对照清单考前十分钟扫一遍比翻书有用。看到这类题先问自己语句骨架建表 / 加约束主键是单列还是复合哪些列非空CREATE TABLE 列级/表级约束改结构新列有没有默认值改类型会不会截断ALTER TABLE ... ADD / ALTER COLUMN插入数据列顺序对不对外键指向的行存在吗INSERT INTO 表(列...) VALUES(...)带条件的更新条件列在目标表里吗不在就用子查询UPDATE ... WHERE ... (SELECT ...)统计类查询分组列是谁过滤发生在分组前还是后GROUP BYHAVING多表查询要不要保留没匹配的行内连接 /LEFT OUTER JOIN子查询内层返回单值还是多值会不会有 NULL/IN/EXISTS集合运算两边列数和类型一致吗要不要去重UNION [ALL]/EXCEPT综合题最能检验这张表有没有内化。比如“统计每门课的选课人数和平均分只保留平均分大于 80 的课程按平均分降序”解题路径应该是先确定分组列是Cno聚合列是COUNT(*)和AVG(Score)过滤条件是聚合结果所以放HAVING最后排序。SELECT Cno AS 课程号, COUNT(*) AS 选课人数, AVG(Score) AS 平均分 FROM SC WHERE Score IS NOT NULL -- 先排除空成绩避免拉低均分 GROUP BY Cno HAVING AVG(Score) 80 -- 对分组结果过滤 ORDER BY 平均分 DESC; -- 排序放在最后这条语句里每一行都对应清单上的一个问题WHERE处理空值、GROUP BY定分组、HAVING过滤组、ORDER BY收尾。写完之后我习惯再做一次反向检查——把HAVING的条件临时挪到WHERE里跑一遍如果报错说明我对“聚合发生在分组之后”的理解是对的如果不报错但结果不同说明我漏掉了某个非聚合的过滤条件。血泪经验是这套流程我第一次做实验时完全没走直接照着题目顺序一条条敲结果在“删除计算机系学生选课记录”那题上卡了很久——子查询里的Student.Sno tmpSC.Sno写反了方向删出来是空集还以为是数据没插进去。从那以后我每次写带子查询的增删改都强制先用同样的WHERE写一条SELECT看一眼命中行数确认无误再换成DELETE或UPDATE。这个习惯看起来多一步实际省下的排查时间远不止一步。希望帮到你。本文还有配套的精品资源点击获取 
长河君 · 2026/10/31 11:39:53 · 阅读 1.2k SQL数据库实验知识地图：建表、查询、约束与授权复习指南
简介面向软件工程专业学生及数据库课程学习者的实验报告文档对应太原理工大学《数据库概论》课程聚焦交互式SQL语句的使用、数据完整性、用户鉴别与数据控制等实验主题适合课内实验参考、期末复习与上机操作对照。压缩包内共1个docx文件约1.69MB集中呈现实验目的、实验平台与主要实验内容及结果便于按实验流程查阅。已有311人学习具备一定的课程参考价值。文档围绕Microsoft SQL Server 2016展开涵盖基本表的创建、修改与删除索引与视图创建以及插入、修改数据等操作并给出可执行的SQL语句和检查结果。读者可据此复现Student、Course、SC等表的定义与约束理解主键、外键、唯一索引、聚簇索引的用法梳理常见语法与排错思路快速完成实验报告整理与知识点复盘。1. 从建表到授权一套数据库实验的完整知识地图多数人第一次做数据库实验把重点放在“把 SQL 敲对”结果一写 INSERT 就报错一写子查询就没结果。真正拉开差距的不是记住多少条语句而是是否具备“约束先行”的思维在动手建表之前先想清楚这张表的主键由哪几列构成、哪些列允许为空、哪些列必须指向另一张表。这份实验报告的价值在于它把数据库系统原理里最容易分开学习的四层串成了一条线对象层表、索引、视图、操作层插入、更新、删除、查询层单表→分组→连接→嵌套→集合、控制层约束、触发器、授权。正在系统学数据库的软件工程方向同学、准备实验验收或期末复习的人都可以把它当成一张复习地图哪一层薄弱就翻到对应的那一章。2. 数据定义与对象管理建表、索引、视图的落点数据定义是整门课的地基。地基没打好后面所有查询、约束、授权的报错都会找上门来而且报错信息往往只告诉你“违反了某个约束”不会告诉你约束当初是怎么写的。这一章把实验里出现过的四类对象——表、约束、索引、视图——按“先建什么、后改什么、什么时候删”的顺序理一遍。2.1 CREATE TABLE 的骨架字段、类型、约束三件套一张表的定义语句可以拆成三段来看列名与数据类型是第一段列级约束是第二段表级约束是第三段。实验里学生表、课程表、选课表三张表的写法恰好覆盖了三种典型结构单列主键、可空外键、复合主键。先看下面这段按实验原始结构整理的建表语句。CREATE TABLE Student( Sno CHAR(8) PRIMARY KEY, -- 列级主键学号唯一标识一名学生 Sname CHAR(10), Ssex CHAR(2) CHECK(Ssex IN (男,女)), -- 取值只能是两个汉字之一 Sage INT, Sdept CHAR(20), Sclass CHAR(4) NOT NULL DEFAULT 1001 -- 非空 默认值插入时可省略 ); CREATE TABLE Course( Cno CHAR(4) PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4), -- 先修课号可以为空 Ccredit INT ); CREATE TABLE SC( Sno CHAR(8), Cno CHAR(4), Score INT, PRIMARY KEY(Sno, Cno), -- 表级复合主键一个学生一门课只能一条 FOREIGN KEY(Sno) REFERENCES Student(Sno), FOREIGN KEY(Cno) REFERENCES Course(Cno) );这段代码里有四个参数值得反复看。CHAR(8)是定长字符存2010001这种七位学号时会自动补一个空格后面做等值比较容易出问题所以学号长度必须严格对齐如果长度不定用VARCHAR更稳。SC表的主键写成表级约束PRIMARY KEY(Sno, Cno)因为主键由两列组成没法写在某一列后面这是复合主键唯一正确的写法。两个FOREIGN KEY让选课表同时依赖学生表和课程表意味着插入一条选课记录之前对应的学生和课程必须已经存在。DEFAULT 1001只在省略该列时生效显式写NULL是会被拒绝的——这一点在检查默认值约束时经常被忽略。数据类型的选择也有讲究。工资用NUMERIC(8,2)而不是FLOAT因为金额需要精确到分浮点数会出现0.10.2这类精度漂移生日用DATETIME而不是字符串才能在后面做日期函数运算。类型选错不会立刻报错但会在统计和比较时悄悄出错属于典型的“当时不疼、事后难受”。2.2 ALTER TABLE 与 DROP TABLE改结构时的三个边界建表之后改结构实验里出现了三类操作加列、改列类型、加约束。三者的风险等级完全不同。ALTER TABLE Student ADD Sclass CHAR(4); -- 新增列已有行该列自动为 NULL ALTER TABLE Student ALTER COLUMN Sage SMALLINT; -- 缩小数值范围超出范围会报错 ALTER TABLE Course ADD UNIQUE(Cname); -- 给已有列补唯一约束 ALTER TABLE SC DROP CONSTRAINT SC_CHECK; -- 先删旧约束 ALTER TABLE SC ADD CONSTRAINT SC_CHECK CHECK(Grade 0 AND Grade 100); -- 再建新约束ADD加列最安全但要注意新列如果没有DEFAULT且允许为空已有行的该列全是NULL后面写NOT NULL查询时就会漏数据。ALTER COLUMN改类型是风险最高的一步把INT改成SMALLINT一旦表里存在超过 32767 的年龄值虽然现实中不可能但测试数据里常见整条语句直接失败把CHAR改短还会截断数据。改约束必须走“先 DROP 再 ADD”两步SQL Server 不支持直接覆盖同名约束DROP CONSTRAINT后面跟的是约束名而不是列名这是最容易写错的地方。DROP TABLE的边界在外键上。实验里删除Employee表能一次成功是因为它没有任何表引用它如果去删Student表而SC表的外键还指着它语句会被拒绝。正确顺序是先删子表SC再删父表Student。这个顺序在后面讲数据完整性的章节还会再出现一次因为插入和删除数据时遵循的是同一条依赖链。2.3 索引三分类普通、唯一、聚簇分别解决什么问题索引是这门课里最容易被当成“加分项”而忽略的模块。实验里建了四个索引分别对应三种类型值得逐条拆开看。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); -- 主键上再建唯一索引冗余演示普通索引只影响查询计划不改变任何数据规则Cname上建索引是为了让按课程名模糊查询少扫全表。唯一索引多了一层约束含义如果表里已经存在两个同名课程CREATE UNIQUE INDEX会直接失败所以它常用于“业务上必须唯一但没设成主键”的列。聚簇索引则决定数据行的物理排列顺序一张表只能有一个实验里SC表按(Sno, Cno DESC)建聚簇索引意味着数据在磁盘上先按学号排、同学号内按课程号倒序排范围查询Sno BETWEEN ...会非常快。有两个细节容易漏。第一复合索引的列顺序不能随便调(Sno, Cno)和(Cno, Sno)是两个完全不同的索引前者能支持“按学号查”后者不能。第二主键默认已经建了聚簇或非聚簇索引再在Cno上建唯一索引属于重复劳动实验里保留它更多是为了演示语法真实项目里应该先查sys.indexes再决定建不建。2.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; CREATE VIEW XK_VIEW AS -- 多表连接视图把三表查询封装成一个名字 SELECT Student.*, Course.*, Score FROM Student, SC, Course WHERE Student.Sno SC.Sno AND SC.Cno Course.Cno;S_G这个视图说明了一条硬规则只要查询里有聚合函数或表达式列视图的列名就必须在CREATE VIEW后面逐个列出来不能指望系统自动命名。XK_VIEW用的是SELECT Student.*, Course.*两张表都有Sno之外的公共列吗没有但Student和Course各自的全列展开后列名可能重复比如都有Cno之外的字段真实项目里更推荐显式列出需要的列避免视图定义因为基表加列而失效。视图的真正价值是把复杂查询固化成一个名字让后续的SELECT * FROM XK_VIEW WHERE ...读起来像查一张普通表。代价是更新受限含聚合、DISTINCT、GROUP BY的视图基本不能直接INSERT或UPDATES_G就属于这一类。复习时记住一句判断能从视图无损反推回基表行的才可能更新。3. 数据操纵与查询从增删改到五类查询的推进数据操作是实验里代码量最大的部分也是最容易“语法对了、结果错了”的部分。这一章按“写数据增删改→ 读数据五类查询”的顺序推进每一节都对应实验里出现过的一类操作重点放在那些运行结果和预期不一致的场景上。3.1 插入的四种形态与列顺序陷阱实验里的插入操作出现了四种写法覆盖面比想象中全。-- 形态一不写列名按建表顺序给全所有列 INSERT INTO Student VALUES(20100001,李勇,男,20,CS,1001); -- 形态二显式列名可以跳过有默认值或允许为空的列 INSERT INTO Student(Sno, Sname, Ssex, Sclass) VALUES(20100102,张盛,男,1008); -- 形态三从另一张表批量导入 INSERT INTO cs_Student(学号, 姓名, 年龄) SELECT Sno, Sname, Sage FROM Student WHERE Sdept CS; -- 形态四SELECT INTO 直接建表并灌数据 SELECT * INTO tmpSC FROM SC;形态一最省事也最危险一旦表结构后期加了列比如ALTER TABLE Student ADD Sclass原来那条不写列名的INSERT立刻报“列数不匹配”所有历史脚本都要跟着改。形态二才是工程里推荐的写法列名和值的对应关系一目了然加列不影响旧语句。形态三要求源列和目标列的类型兼容、数量一致SELECT里的列顺序决定写入顺序不是按列名匹配。形态四的SELECT INTO会新建一张表并复制数据但不复制约束、索引、主键所以tmpSC里可以插入重复的(Sno, Cno)这一点在做删除演练时反而是好事因为删错了不影响原表。还有一个隐蔽陷阱CHAR类型的列插入短字符串会自动补空格CS存进CHAR(20)会变成CS 18个空格显示时看不出来但WHERE Sdept CS能匹配上SQL Server 比较时会忽略尾部空格换到别的数据库就未必。复习时记住定长字符是补齐的变长字符是不补的。3.2 UPDATE 与 DELETEWHERE 决定生死更新和删除的语法只有一句风险全在WHERE上。实验里最有价值的是那几个带子查询的更新它们展示了“条件不在目标表里”时怎么写。-- 单表条件更新 UPDATE Student SET Sage 22 WHERE Sno 20100001; -- 全表更新没有 WHERE所有行都变 UPDATE Student SET Sage Sage 1; -- 带相关子查询的更新给计算机系学生成绩加 5 分 UPDATE SC SET Score Score 5 WHERE CS (SELECT Sdept FROM Student WHERE Student.Sno SC.Sno); -- 修改主键值会触发外键检查 UPDATE Student SET Sno 20100025 WHERE Sno 20100021;第三条语句的写法值得注意把常量CS写在等号左边、子查询写在右边是因为子查询返回的是单值SC.Sno作为相关条件把内外层连起来。如果写成WHERE Sdept CS系统会报“列名不明确”因为SC表里没有Sdept。第四条修改主键是整章最容易出错的一步Student.Sno被SC表的外键引用如果SC里存在20100021的选课记录这条UPDATE会直接被拒绝除非外键定义了ON UPDATE CASCADE。实验里能成功是因为该学生当时没有选课记录。删除操作同理。实验里先用SELECT * INTO tmpSC FROM SC复制一份再在副本上练删除这是一种很实用的“后悔药”习惯真实环境里删数据之前先SELECT出要删的行确认数量再执行DELETE最后用同样的WHERE查一次确认剩 0 行。删除全部记录用DELETE FROM tmpSC不要写DROP TABLE前者只清数据保留结构后者连表一起没了。3.3 单表查询的四类条件与结果集整形单表查询是后面所有复杂查询的零件库。实验里出现的条件类型可以归成四类每一类都有对应的关键字和边界。条件类型关键字实验中的例子边界提醒范围BETWEEN ... AND ...Sage BETWEEN 20 AND 23闭区间包含两端集合IN (...)Sdept IN (IS,MA,CS)列表里有 NULL 不影响匹配模糊LIKESname LIKE 刘%%任意长度_单个字符空值IS NULLScore IS NULL不能写 NULL永远不成立结果集的整形手段同样有四类用表达式列算出衍生值2004 - Sage得到出生年份、用AS或空格给列起别名DEPARTMENT LOWER(Sdept)是 SQL Server 特有的写法标准写法是LOWER(Sdept) AS Department、用DISTINCT去重SELECT DISTINCT Sno FROM SC把重复选课记录压成一人一条、用ORDER BY排序ORDER BY Sdept, Sage DESC表示先按系升序、系内按年龄降序。DISTINCT和ALL的区别值得单独记一笔ALL是默认行为保留重复行DISTINCT对整行去重。如果写SELECT DISTINCT Sno, Cno FROM SC去重的是(Sno, Cno)这个组合不是单独对Sno去重。很多人在这里翻车以为加了DISTINCT就能得到“选课学生人数”正确写法是COUNT(DISTINCT Sno)。3.4 分组统计聚合函数、GROUP BY 与 HAVING 的三段式分组查询的执行顺序是WHERE → GROUP BY → 聚合 → HAVING → ORDER BY这个顺序决定了什么能写在哪。实验里的几个例子恰好覆盖了三个阶段。-- 阶段一只聚合不分组 SELECT COUNT(*) AS 总人数 FROM Student; -- 阶段二分组后聚合 SELECT Cno AS 课程号, COUNT(*) AS 人数, AVG(Score) AS 均分, MAX(Score) AS 最高分 FROM SC GROUP BY Cno; -- 阶段三对分组结果再过滤 SELECT Cno AS 课程号, AVG(Score) AS 均分 FROM SC GROUP BY Cno HAVING AVG(Score) 90;整个过程最容易错的是“SELECT列表里的列必须出现在GROUP BY里”。SELECT Cno, Sname, AVG(Score) ... GROUP BY Cno这种写法在严格模式下直接报错因为一个课程号对应多个姓名系统不知道该显示哪个。HAVING和WHERE的分工也常混WHERE在分组前过滤行不能用聚合函数HAVING在分组后过滤组可以用聚合函数。想筛“成绩大于 60 的选课记录再统计”条件放WHERE想筛“平均分大于 90 的课程”条件放HAVING。聚合函数对NULL的处理是个隐形考点AVG(Score)会跳过NULL行分母是实际有成绩的记录数不是总选课数COUNT(*)统计所有行包括NULLCOUNT(Score)只统计非空值。这两组差异在计算“选课人数”和“平均分”时会导致完全不同的数字复习时必须能说清每个聚合函数对NULL的态度。3.5 连接与嵌套从 WHERE 连接走到 EXISTS多表查询在实验里出现了两种写法老式的WHERE连接和新式的JOIN ... ON。两者等价但后者把连接条件和过滤条件分开了可读性更好。-- WHERE 连接连接条件和过滤条件混在一起 SELECT Student.Sno, Sname, Cno, Score FROM Student, SC WHERE Student.Sno SC.Sno AND SC.Cno 2 AND Score 90; -- JOIN ON连接条件和过滤条件分离 SELECT Student.Sno, Sname, Cno, Score FROM Student JOIN SC ON Student.Sno SC.Sno WHERE SC.Cno 2 AND SC.Score 90; -- 外连接保留左表全部行右表没匹配的补 NULL SELECT Student.Sno, Sname, Cno, Score FROM Student LEFT OUTER JOIN SC ON Student.Sno SC.Sno;外连接和普通连接的区别在“没选课的学生”上内连接会把这类学生直接丢掉左外连接会保留他们Cno和Score显示为NULL。判断该用哪种只看一个问题——没有匹配行的主体要不要出现在结果里。要就用外连接不要就用内连接。自连接是连接查询里最绕的一种。实验里“查询每一门课的间接先修课”需要把Course表当成两张表用一张叫first当前课程一张叫second先修课的先修课SELECT first.Cno, second.Cpno FROM Course first, Course second WHERE first.Cpno second.Cno;这里必须给两张表起别名否则Course.Cpno Course.Cno无法区分是哪一行的列。自连接的本质是“同一张表按不同角色参与连接”树形结构、层级关系、前后记录对比都靠它。嵌套查询则是另一种思路把内层查询的结果当成外层查询的条件值。IN适合内层返回多值加比较符适合内层返回单值ANY/ALL适合和内层的一组值整体比较EXISTS只看内层有没有行、不关心具体值。-- IN和“刘晨”同系的学生 SELECT Sno, Sname FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname 刘晨); -- ALL比计算机系所有学生年龄都小 SELECT Sname, Sage FROM Student WHERE Sage ALL (SELECT Sage FROM Student WHERE Sdept CS) AND Sdept CS; -- EXISTS选修了 1 号课程的学生 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno Student.Sno AND Cno 1);EXISTS的子查询里写SELECT *是标准做法因为系统根本不读列只看有没有行返回。它和IN的一个重要差别是EXISTS遇到NULL不受影响而NOT IN的子查询一旦返回NULL整个条件会变成未知结果集为空——这是数据库考试里最爱出的陷阱之一。3.6 集合查询UNION、INTERSECT、EXCEPT 的边界集合查询把两个独立查询的结果按行做并、交、差前提是两边的列数相同、对应列类型兼容。-- 并集计算机系的学生 或者 年龄不大于 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 ALL。UNION的结果列名取自第一个查询排序要写在最后一条语句之后。EXCEPT的方向不能反A EXCEPT B是“在 A 里但不在 B 里”和B EXCEPT A是两个完全不同的结果。用集合查询改写OR条件时要注意UNION会自动去重如果业务上需要保留重复必须写UNION ALL。4. 数据完整性约束、触发器与授权的分工完整性这一章在实验里单独成篇因为它回答的是“数据为什么不能乱写”。约束是数据库替应用层守的第一道门触发器是第二道授权则是决定谁能推这扇门。4.1 五类约束各自守哪道门约束类型关键字守的是什么实验中的例子主键PRIMARY KEY行唯一 非空Sno CHAR(8) PRIMARY KEY外键FOREIGN KEY ... REFERENCES引用完整性SC.Cno指向Course.Cno唯一UNIQUE列值不重复允许一个 NULLALTER TABLE Course ADD UNIQUE(Cname)检查CHECK列值满足布尔表达式Ssex IN (男,女)、Grade BETWEEN 0 AND 100默认/非空DEFAULT/NOT NULL缺省填充、禁止空值Stotal SMALLINT DEFAULT 0这五类约束有明确的优先级和分工。主键约束最强自带唯一和非空两重含义唯一约束比主键弱一点允许存在一个NULLSQL Server 的行为常用于“业务上不该重复但确实可能为空”的列CHECK约束只做单行内的值判断不能跨行、不能查别的表外键约束负责跨表引用是唯一一个会“锁住”删除和更新操作的约束。复习时要能回答一个对比题UNIQUE和PRIMARY KEY到底差在哪答案有三点——主键不允许NULL唯一约束允许一张表只能有一个主键可以有多个唯一约束主键默认会成为聚簇索引的候选唯一约束默认建非聚簇索引。4.2 用“故意违规”验证约束约束写完了不代表生效实验里最有价值的部分是那组“故意写错”的语句。每一条报错都在告诉你哪一层约束在起作用。-- 主键冲突20100001 已存在 INSERT INTO Student VALUES(20100001,李斌,男,20,CS,1001,0); -- 修改主键撞上已有值20100021 属于王敏 UPDATE Student SET Sno 20100021 WHERE Sname 张立; -- 复合主键冲突同一学生同一课程插两次 INSERT INTO SC VALUES(20100001,1,78); -- 复合主键的一部分为 NULL INSERT INTO SC VALUES(20100001, NULL, 78); -- 唯一约束冲突课程名重复 INSERT INTO Course VALUES(9,数据结构,7,3); -- CHECK 约束冲突成绩越界 INSERT INTO SC VALUES(20100002,3,120);第一条和第三条报的是主键重复区别在于前者是单列主键、后者是复合主键报错信息里会明确指出是哪个约束名冲突。第二条报的是主键冲突而不是外键错误因为20100021这个值已经存在于Student表里。第四条报的是“不能将 NULL 插入列”因为复合主键的每一列都隐含NOT NULL。第五条报唯一约束冲突说明UNIQUE(Cname)已经生效。第六条报CHECK约束冲突前提是先把旧的SC_CHECK删掉换成Grade 0 AND Grade 100否则旧的Grade 0 AND Grade 100会先拦住。读报错信息有一个通用方法先看它提到的是哪个约束名PK_、FK_、UQ_、CK_前缀能直接暴露类型再看它说的是“冲突”还是“引用不存在”。前者是重复写入后者是顺序问题。4.3 触发器与约束的边界约束能表达的是“单行内的静态规则”一旦规则需要跨表、需要读取修改前的值、需要在插入后自动更新另一张表就得靠触发器。实验里提到了触发器的机制但没有展开写这里按最常用的两类补一个示意。-- AFTER 触发器插入选课记录后自动累加学生已修学分 CREATE TRIGGER trg_AddCredit ON SC AFTER INSERT AS BEGIN UPDATE Student SET Stotal Stotal (SELECT Ccredit FROM Course WHERE Cno (SELECT Cno FROM inserted)) WHERE Sno (SELECT Sno FROM inserted); END;这段代码里的inserted是触发器专用的逻辑表存放本次插入的所有新行。AFTER表示语句执行成功之后再触发如果AFTER触发器里报错整个插入语句会回滚。另一类是INSTEAD OF触发器它替代原操作执行常用于让不可更新的视图变得可更新。触发器和约束的分工可以记成一句话能用约束表达的绝不用触发器。约束由系统内核执行效率高、行为确定触发器是用户代码嵌套调用、递归触发、事务回滚都会让排查难度翻倍。实验里那些“插入重复主键报错”的场景用约束一行搞定用触发器反而要自己写判断逻辑。4.4 用户鉴别与数据控制授权语句的位置实验名称里提到了“用户鉴别与数据控制”这部分内容在报告正文里没有展开但它是完整性链条的最后一环约束管“数据长什么样”授权管“谁能碰这些数据”。概念上分三层——登录名负责能不能连上服务器数据库用户负责在某个库里是谁角色是一组权限的打包。授权语句的基本形态是两条-- 把 Student 表的查询权限授予某个数据库用户 GRANT SELECT ON Student TO user_a; -- 允许该用户把权限继续转授给别人 GRANT SELECT ON Student TO user_a WITH GRANT OPTION; -- 收回权限 REVOKE SELECT ON Student FROM user_a;GRANT的粒度可以细到列比如只允许查Sno和Sname两列WITH GRANT OPTION是“转授权”收权时要连带收回别人转授出去的权限否则会留下权限残留。这一块在实验里通常只要求跑通语句、看懂效果但复习时要能和约束串起来约束是数据层面的完整性授权是访问层面的完整性两者合起来才叫数据库的安全性。5. 易错点盘点五个最容易翻车的细节这一章单独列出来是因为下面五个问题在实验验收和笔试里出现频率极高而且每一个都属于“看答案觉得懂、自己写就错”的类型。易漏点一NULL不是 0也不是空字符串。为什么容易漏——写WHERE Score NULL语法不报错只是永远返回空结果看起来像“没有数据”而不是“写错了”。更隐蔽的是NOT IN搭配子查询只要子查询结果里有一个NULL整个NOT IN条件就变成未知外层查询返回空集。怎么补——所有涉及空值的判断一律写IS NULL/IS NOT NULL用NOT IN之前先确认子查询列有没有NULL有就改用NOT EXISTS统计平均分时想清楚分母是COUNT(*)还是COUNT(列)。易漏点二CHAR定长补齐导致的比较与长度问题。为什么容易漏——CHAR(8)存七位学号会补一个尾部空格LEN()在 SQL Server 里会忽略尾部空格返回 7而DATALENGTH()返回 8两个函数给出不同答案很容易误判。字符串拼接、导出文件、和外部系统对接时尾部空格会变成脏数据。怎么补——长度不固定的列一律用VARCHAR必须用CHAR时比较前统一RTRIM()建表阶段就确定好学号、身份证号这类“看起来定长但将来会变”的列该用哪种类型。易漏点三GROUP BY与SELECT列表不对应WHERE和HAVING混用。为什么容易漏——SELECT Sno, Sname, AVG(Score) ... GROUP BY Sno在部分宽松配置下能跑出结果只是Sname取的是组内任意一行的值结果“看起来对”但不可复现。HAVING写成WHERE AVG(Score) 90会直接报聚合函数不能出现在WHERE里但很多人靠试错改到HAVING之后就忘了为什么。怎么补——写分组查询前先在纸上列出“哪些列要进GROUP BY、哪些是聚合、哪些是过滤”过滤条件按“分组前/分组后”二分凡是出现聚合函数的条件一律放HAVING。易漏点四外键依赖顺序在建表、插数、删数、改主键四个环节都会发作。为什么容易漏——建表时先建SC后建Student会报“引用的表不存在”插入选课记录时学生还没插会报外键冲突删除学生时选课记录还在会报引用冲突修改被引用的主键值时同样报错。四个环节四条报错本质是同一条依赖链。怎么补——建表顺序按“被引用者在前”删表和数据按“引用者在后”做删除或改主键演练前先在子表里SELECT一下有没有关联行真要级联就在外键上写ON DELETE CASCADE/ON UPDATE CASCADE但要清楚这会绕过应用层的确认逻辑。易漏点五集合查询的列数、类型与去重。为什么容易漏——UNION两边列数不一致会直接报错这个容易发现但列数一致、类型不同比如一边INT一边CHAR会走隐式转换结果可能悄悄丢精度。更常见的是忘了UNION默认去重把“并集”当成“拼接”本该保留的重复行被吃掉。怎么补——写集合查询前先对齐两边的列清单和类型需要保留重复就显式写UNION ALLEXCEPT和INTERSECT的方向写完后用一句自然语言复述一遍确认“谁减谁”没反。6. 复习清单考前把这张表过一遍复习这门课我后来固定成一套流程先不看题把“题目要我改哪张表、这张表被谁引用、改动会不会破坏约束”这三个问题在草稿纸上答完再动笔写 SQL。下面这张表是我按实验题型整理的对照清单考前十分钟扫一遍比翻书有用。看到这类题先问自己语句骨架建表 / 加约束主键是单列还是复合哪些列非空CREATE TABLE 列级/表级约束改结构新列有没有默认值改类型会不会截断ALTER TABLE ... ADD / ALTER COLUMN插入数据列顺序对不对外键指向的行存在吗INSERT INTO 表(列...) VALUES(...)带条件的更新条件列在目标表里吗不在就用子查询UPDATE ... WHERE ... (SELECT ...)统计类查询分组列是谁过滤发生在分组前还是后GROUP BYHAVING多表查询要不要保留没匹配的行内连接 /LEFT OUTER JOIN子查询内层返回单值还是多值会不会有 NULL/IN/EXISTS集合运算两边列数和类型一致吗要不要去重UNION [ALL]/EXCEPT综合题最能检验这张表有没有内化。比如“统计每门课的选课人数和平均分只保留平均分大于 80 的课程按平均分降序”解题路径应该是先确定分组列是Cno聚合列是COUNT(*)和AVG(Score)过滤条件是聚合结果所以放HAVING最后排序。SELECT Cno AS 课程号, COUNT(*) AS 选课人数, AVG(Score) AS 平均分 FROM SC WHERE Score IS NOT NULL -- 先排除空成绩避免拉低均分 GROUP BY Cno HAVING AVG(Score) 80 -- 对分组结果过滤 ORDER BY 平均分 DESC; -- 排序放在最后这条语句里每一行都对应清单上的一个问题WHERE处理空值、GROUP BY定分组、HAVING过滤组、ORDER BY收尾。写完之后我习惯再做一次反向检查——把HAVING的条件临时挪到WHERE里跑一遍如果报错说明我对“聚合发生在分组之后”的理解是对的如果不报错但结果不同说明我漏掉了某个非聚合的过滤条件。血泪经验是这套流程我第一次做实验时完全没走直接照着题目顺序一条条敲结果在“删除计算机系学生选课记录”那题上卡了很久——子查询里的Student.Sno tmpSC.Sno写反了方向删出来是空集还以为是数据没插进去。从那以后我每次写带子查询的增删改都强制先用同样的WHERE写一条SELECT看一眼命中行数确认无误再换成DELETE或UPDATE。这个习惯看起来多一步实际省下的排查时间远不止一步。希望帮到你。本文还有配套的精品资源点击获取