简介：本资源是太原理工大学软件工程专业《数据库概论》课程配套的完整实验报告，面向高校数据库初学者与SQL实践者，聚焦SQL Server 2016环境下数据库对象管理、数据定义与操作等核心能力训练。报告覆盖创建/修改/删除表、索引（含唯一索引与聚簇索引）、视图及INSERT/UPDATE/SELECT等关键语句的实操过程，并附带大量可直接运行的SQL示例（如Student/Course/Sc三表建模、IS_Student视图构建、多表插入与条件更新），助力读者夯实数据库系统原理与工程实践基础。资源为单文件docx文档，大小1.69MB，结构清晰、排版规范，含实验目的、平台配置、分步代码、执行结果与注意事项，便于课堂复现与自学对照。已有312人学习下载，适合作为课程复习资料、实验预习模板或SQL语法速查参考。

1. 主键不是“加个 PRIMARY KEY 就完事”：为什么 Student 表里插入两条Sno='20100001'的记录会直接报错，而UPDATE Student SET Sno='20100001' WHERE Sname='张立'却能成功执行一半就卡住？

这不是 SQL Server 的 bug，也不是学生手抖输错了——这是数据库内核在用最原始的方式告诉你：主键约束不是语法糖，而是存储引擎层面的强制性物理保障机制。你写的那行PRIMARY KEY(Sno)，在 SQL Server 2016 底层，会立刻触发三个不可绕过的动作：① 自动为Sno列创建一个唯一聚簇索引（除非显式指定非聚簇）；② 在插入/更新前，强制走 B+ 树索引查找路径比对键值；③ 若发现冲突，不抛“逻辑错误”，而是直接中断事务并回滚整个语句——连部分更新都不允许。更反直觉的是：实验报告里那句UPDATE Student SET Sno='20100021' WHERE Sname = '张立'看似只是改名字，实则因Sno是主键，SQL Server 必须先定位原记录、再检查新值是否已存在、最后才执行覆盖——三步缺一不可。而INSERT INTO Student VALUES('20100001','李斌','男',20,'CS','1001',0)直接失败，是因为聚簇索引页上Sno=20100001的槽位已被占用，B+ 树搜索返回FOUND，引擎连数据页解析都省了。这份实验报告表面是教你怎么敲CREATE TABLE，实际是把数据库最硬核的“物理一致性防线”拆开给你看：约束不是校验器，是存储结构本身。搞懂这点，你才能真正理解为什么删表比删索引快十倍、为什么ALTER COLUMN改类型要重建表、为什么NULL在主键列里根本不可能存在——这些都不是 SQL 标准的教条，而是 B+ 树索引、页分裂、锁协议共同写死的底层契约。

2. 数据定义：从 CREATE TABLE 到物理页布局的完整映射链

2.1 主键声明如何决定数据存储形态：聚簇索引不是可选项，而是默认契约

当你写下CREATE TABLE Student(Sno char(8) primary key, ...)，SQL Server 2016 并不会先建表再加索引。它直接将Sno列作为聚簇索引键，并以此组织所有数据页。这意味着：

• 整个Student表的数据行，按Sno的字典序物理排序存储在数据页中（不是逻辑顺序）；
• 每个数据页（8KB）包含页头、行偏移数组、实际数据行，而行偏移数组按Sno排序排列；
• 查询SELECT * FROM Student WHERE Sno='20100001'时，SQL Server 不扫描全表，而是从根页开始，沿 B+ 树逐层下探，最终定位到精确的数据页和行偏移位置——整个过程平均只需 3~4 次 I/O。

验证这一点只需一条命令：

-- 查看 Student 表的索引物理属性 SELECT i.name AS index_name, i.type_desc AS index_type, i.is_primary_key, i.is_unique, c.name AS column_name, ic.key_ordinal FROM sys.indexes i JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE i.object_id = OBJECT_ID('Student') ORDER BY i.index_id, ic.key_ordinal;

输出中你会看到index_type = 'CLUSTERED'且is_primary_key = 1，证明主键已固化为聚簇索引。而实验报告中create table Sc (Sno char(8), Cno char(4), ..., primary key(Sno,Cno))这句，本质是创建了一个复合聚簇索引，其物理排序规则是：先按Sno升序，Sno相同时再按Cno降序（注意create clustered index iSnoCno on Sc(Sno,Cno desc)中的desc）。这种排序直接影响SELECT * FROM Sc ORDER BY Sno, Cno DESC的执行效率——它无需额外排序，直接按页顺序读取即可。

提示：聚簇索引的键值不能为NULL，因为 B+ 树无法对NULL做大小比较。实验报告中Student表Sno定义为char(8) primary key，SQL Server 会隐式添加NOT NULL约束，哪怕你没写。这是物理存储决定的刚性规则，不是语法约定。

2.2 UNIQUE 约束的双重实现：索引 vs. 触发器，为什么ALTER TABLE Course ADD UNIQUE(Cname)会自动建索引？

UNIQUE(Cname)看似只是逻辑约束，但在 SQL Server 底层，它必须通过唯一非聚簇索引来强制实施。原因在于：唯一性检查需要 O(log n) 时间复杂度，而全表扫描是 O(n)——当表有百万行时，后者直接不可用。因此，ALTER TABLE Course ADD UNIQUE(Cname)实际执行了两件事：

1. 创建一个非聚簇索引UQ__Course__Cname，键列为Cname；
2. 在该索引的 B+ 树中，对每个插入/更新的Cname值执行唯一性查找。

你可以用以下语句验证：

-- 查看 Course 表的所有索引 SELECT i.name AS index_name, i.type_desc, i.is_unique, COL_NAME(ic.object_id, ic.column_id) AS column_name FROM sys.indexes i JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id WHERE i.object_id = OBJECT_ID('Course');

若输出中出现type_desc = 'NONCLUSTERED'且is_unique = 1的索引，其column_name为Cname，即证明UNIQUE已落地为物理索引。而实验报告中INSERT INTO Course VALUES('9','数据结构',7,3)失败，正是因为该语句试图在Cname='数据结构'的索引键中插入重复值，B+ 树搜索返回DUPLICATE KEY，引擎立即终止操作。这里没有“先插入再校验”的中间态——索引查找和数据写入是原子绑定的。

2.3 FOREIGN KEY 的级联行为：为什么foreign key(Sno) references Student(Sno)不仅校验，还锁住 Student 表？

外键约束远不止“检查值是否存在”。在 SQL Server 中，FOREIGN KEY默认启用NO ACTION行为，但其底层机制是：

• 插入Sc行前，必须对Student表的Sno列执行共享锁（S Lock）查询；
• 若Student表正在被其他事务修改Sno，则Sc插入会被阻塞，直到锁释放；
• 删除Student行时，若Sc中存在关联记录，NO ACTION会直接报错，而非级联删除。

实验报告中create table Sc (... foreign key(Sno)references Student(Sno) ...)这句，让 SQL Server 在Sc表上创建了一个外键约束对象，并在Student.Sno的聚簇索引上建立引用关系。你可以通过以下查询确认外键依赖：

-- 查看 Sc 表的外键及其引用目标 SELECT fk.name AS fk_name, t1.name AS referencing_table, c1.name AS referencing_column, t2.name AS referenced_table, c2.name AS referenced_column FROM sys.foreign_keys fk JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id JOIN sys.tables t1 ON fkc.parent_object_id = t1.object_id JOIN sys.columns c1 ON fkc.parent_object_id = c1.object_id AND fkc.parent_column_id = c1.column_id JOIN sys.tables t2 ON fkc.referenced_object_id = t2.object_id JOIN sys.columns c2 ON fkc.referenced_object_id = c2.object_id AND fkc.referenced_column_id = c2.column_id WHERE t1.name = 'Sc';

输出会显示referencing_table = 'Sc'，referenced_table = 'Student'，证明外键已建立物理关联。而实验中DELETE FROM Student WHERE Sno='20100002'失败，正是因为 SQL Server 在删除前检测到Sc表中存在Sno='20100002'的行，触发了外键保护机制——这不是应用层逻辑，而是存储引擎在事务日志中写入的强制检查点。

3. 数据操作：INSERT/UPDATE/DELETE 背后的事务日志与锁协议

3.1 INSERT 的三阶段：从解析计划到日志刷盘的完整生命周期

实验报告中insert into Student Values ('20100001','李勇','男',20,'CS','1001')看似简单，但 SQL Server 2016 执行时会经历严格流程：

1. 解析与编译：将 SQL 文本解析为语法树，生成执行计划（此处为Clustered Index Insert）；
2. 锁获取：对Student表的聚簇索引根页加IX（意向排他锁），对目标数据页加X（排他锁）；
3. 日志写入：在事务日志（LDF 文件）中写入LOP_INSERT_ROWS日志记录，包含完整行数据、页ID、槽位号；
4. 数据写入：将数据写入内存中的数据页，并标记为DIRTY；
5. 提交：调用CHECKPOINT或日志满时，将DIRTY页刷入磁盘（.mdf文件）。

关键点在于：日志写入（Write-Ahead Logging, WAL）必须在数据页刷盘前完成。这意味着即使服务器突然断电，重启后 SQL Server 也能通过重放日志恢复未刷盘的数据。你可以用以下语句查看当前事务日志状态：

-- 查看 Student 表最近的事务日志活动 SELECT operation, context, alloc_unit_name, [transaction name], [begin time], [end time] FROM fn_dblog(NULL, NULL) WHERE alloc_unit_name LIKE '%Student%' ORDER BY [Current LSN] DESC;

执行INSERT后运行此查询，你会看到operation = 'LOP_INSERT_ROWS'的记录，其context = 'LCX_CLUSTERED_INDEX'，证明操作已进入聚簇索引路径。而实验中insert into Student Values ('20100001','李斌','男',20,'CS','1001',0)失败，是因为日志写入前的唯一性检查已返回冲突，整个事务被回滚，日志中甚至不会留下LOP_INSERT_ROWS记录。

3.2 UPDATE 的“原地修改”幻觉：为什么UPDATE Student Set Sage=22 where Sno='20100001'可能导致页分裂？

UPDATE并非总在原位置修改数据。当更新的列（如Sage）长度不变时，SQL Server 会直接修改数据页中的对应字节；但若更新VARCHAR列或导致行长度超过页容量，则触发行迁移（Row Forwarding）：

• 原数据页中该行被标记为FORWARDED_RECORD，并存入指向新位置的指针；
• 新数据写入另一数据页，形成“跳转链”；
• 后续查询需两次 I/O：先读原页指针，再读新页数据。

实验报告中Student表Sage定义为int（固定 4 字节），所以UPDATE Student Set Sage=22属于安全更新，不会迁移。但若你尝试ALTER TABLE Student ALTER COLUMN Sname varchar(20)后再更新姓名，就可能触发迁移。验证方法：

-- 查看 Student 表是否存在 forwarded records SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count FROM sys.dm_db_index_physical_stats( DB_ID(), OBJECT_ID('Student'), NULL, NULL, 'DETAILED' );

若forwarded_record_count > 0，说明已发生行迁移。而实验中update Student Set Sage=Sage+1对全表生效，SQL Server 会按聚簇索引顺序逐页扫描，对每行执行就地更新——这正是ORDER BY Sno隐式生效的原因，也是全表更新比随机更新慢的本质。

3.3 DELETE 的“假删除”真相：为什么delete from Student where Sno='20100022'后空间不立即回收？

DELETE操作并非物理擦除数据，而是：

• 将数据页中对应行的槽位（Slot）标记为DELETED；
• 在页头的GAM（Global Allocation Map）和SGAM（Shared GAM）中标记该页为“可重用”；
• 空间实际回收由后台Ghost Cleanup Task异步完成，通常延迟数秒。

这意味着：DELETE后立即SELECT COUNT(*) FROM Student返回正确行数，但DBCC SHOWCONTIG会显示页碎片率上升。实验报告中delete from Student where Sno='20100022'执行后，若你紧接着插入新行，SQL Server 会优先复用Sno='20100022'原位置的槽位，而非申请新页。验证方式：

-- 查看 Student 表的页使用详情 DBCC IND('YourDatabaseName', 'Student', -1); -- 输出中关注 PageType=1（数据页）的 PageFID、PagePID，再用 DBCC PAGE('YourDatabaseName', FileID, PagePID, 3); -- 查看具体页内行状态（需开启 trace flag 3604）

在DBCC PAGE输出中，你会看到m_slotCnt（槽位总数）不变，但m_deleted字段标记了已删除行。这才是DELETE的真实面目：它只是给数据打了个“待回收”标签，真正的物理清理交给后台线程——这也是为什么高并发删除场景下，tempdb日志暴涨的根本原因。

4. 数据查询：从单表扫描到嵌套子查询的执行计划解剖

4.1 单表查询的执行路径选择：为什么SELECT * FROM Student用聚簇索引扫描，而SELECT Sno,Sname FROM Student可能用索引覆盖？

SQL Server 优化器为每个查询生成执行计划，其核心决策依据是I/O 成本。对于SELECT * FROM Student，由于需返回所有列，优化器必然选择聚簇索引扫描（Clustered Index Scan），因为所有数据都在聚簇索引页中。但SELECT Sno,Sname FROM Student不同：若存在一个只包含Sno和Sname的非聚簇索引（如CREATE INDEX IX_SnoSname ON Student(Sno,Sname)），优化器会选索引查找（Index Seek），因为：

• 索引页比数据页小（只存两列 + 行指针），同样数据量下 I/O 更少；
• 若索引是INCLUDE形式（如CREATE INDEX IX_SnoSname ON Student(Sno) INCLUDE (Sname)），则完全避免回表，称为索引覆盖（Covering Index）。

实验报告中select Sno,Sname,Sage from Student未建对应索引，故执行计划必为聚簇索引扫描。但你可以手动验证：

-- 强制使用索引提示（仅用于测试） SELECT Sno,Sname,Sage FROM Student WITH (INDEX(1)); -- 1 表示聚簇索引 -- 查看实际执行计划（Ctrl+M），观察 "PhysicalOp" 列

在执行计划中，你会看到PhysicalOp = 'Clustered Index Scan'，且Estimated Number of Rows与表行数一致。而select DISTINCT Sno from Sc则不同：由于Sc表主键为(Sno,Cno)，优化器会利用聚簇索引的有序性，通过Stream Aggregate消除重复，而非哈希匹配——这正是DISTINCT比GROUP BY在有序场景下更快的底层原因。

4.2 连接查询的三种物理算法：Nested Loops、Hash Join、Merge Join 如何被实验中的JOIN触发？

实验报告中SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student JOIN SC ON (Student.Sno=SC.Sno)的执行计划，取决于两表数据量和索引：

• Nested Loops：若Student行数少（< 100）、SC有Sno索引，则对Student每行，在SC索引中做一次查找；
• Merge Join：若两表均按Sno排序（Student有聚簇索引，SC有(Sno,Cno)聚簇索引），则双指针归并，O(m+n) 时间；
• Hash Join：若两表都大且无序，则构建哈希表，O(m+n) 但内存消耗大。

验证方法：在 SSMS 中执行查询前按Ctrl+M，查看执行计划图标。实验环境下Student行数约 7，SC行数约 8，优化器几乎必选Nested Loops。而SELECT * FROM course first,course second WHERE first.Cpno=second.Cno这种自连接，因Course表小且Cpno无索引，优化器会选Nested Loops并对second表全扫描——这就是为什么实验要求你CREATE INDEX iCpno ON Course(Cpno)来加速它。

4.3 嵌套查询的执行时机：为什么SELECT Sno, Cno FROM SC x WHERE Grade >= ( SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno)是相关子查询，而IN子查询可能是半连接？

相关子查询（Correlated Subquery）的致命特征是：子查询中引用了外部查询的列（如y.Sno=x.Sno）。这意味着对SC表的每一行，都要执行一次子查询计算AVG(Grade)。实验中SC有 8 行，子查询就被执行 8 次，I/O 成本爆炸。而IN子查询WHERE Sdept IN ( SELECT Sdept FROM Student WHERE Sname= '刘晨')，若子查询结果集小，优化器会转为Semi Join，只判断存在性，不返回子查询数据。

你可以用执行计划对比：

-- 相关子查询（慢） SELECT Sno, Cno FROM SC x WHERE Grade >= (SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno); -- 改写为 JOIN（快） SELECT x.Sno, x.Cno FROM SC x JOIN (SELECT Sno, AVG(Grade) AS avg_grade FROM SC GROUP BY Sno) y ON x.Sno = y.Sno AND x.Grade >= y.avg_grade;

后者执行计划中会出现Hash Match或Merge Join，而前者是Compute Scalar嵌套Aggregate。实验报告中EXISTS子查询SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= '1')是最优解，因为EXISTS只需找到一行即停止，且优化器会将其转为Left Semi Join，性能远超IN。

5. 常见问题排查：那些让你在实验室抓狂的“玄学”错误根源

5.1 现象：INSERT INTO Student VALUES('20100001','李斌','男',20,'CS','1001',0)报错 “违反主键约束”，但SELECT * FROM Student WHERE Sno='20100001'返回空

原因：Sno列定义为char(8)，而'20100001'是 8 字符，但char(8)会右补空格至 8 位。若之前插入的记录是'20100001 '（带空格），则Sno='20100001'实际比较的是'20100001'vs'20100001 '，因char比较时忽略尾部空格，视为相同键值。
解决：统一用varchar(8)替代char(8)，或插入时显式RTRIM('20100001 ')；验证用SELECT DATALENGTH(Sno),Sno FROM Student查看实际字节长度。

5.2 现象：UPDATE Student Set Sno='20100021' WHERE Sname = '张立'执行后，SELECT * FROM Student WHERE Sname='张立'仍返回原记录，但Sno未变

原因：Sname列定义为char(10)，而'张立'是 2 字符，char(10)会右补 8 个空格。WHERE Sname = '张立'实际比较'张立 'vs'张立'，因空格被忽略，条件成立；但UPDATE试图将Sno改为'20100021'，触发主键唯一性检查失败，整个语句回滚，数据无变化。
解决：用WHERE Sname = RTRIM('张立')或改用varchar；或先SELECT Sname, DATALENGTH(Sname) FROM Student确认存储格式。

5.3 现象：SELECT * FROM Student order by Sdept,Sage Desc结果中，同一系内Sage未严格降序

原因：Sage列定义为smallint，但实验中插入了NULL（如INSERT INTO Student VALUES('20100003','刘洋','女',null,null,'1001',0)）。NULL在排序中被视为最小值，且NULLs之间无序，导致Sage IS NULL的行排在最前，破坏了Desc语义。
解决：ORDER BY Sdept, Sage DESC NULLS LAST（SQL Server 2022+），或旧版用ORDER BY Sdept, ISNULL(Sage, -1) DESC将NULL显式转为最小值。

5.4 现象：CREATE VIEW IS_Student AS select Sno,Sname,Sage from Student where Sdept='IS'创建后，SELECT * FROM IS_Student返回空，但SELECT * FROM Student WHERE Sdept='IS'有数据

原因：Sdept列定义为char(20)，而'IS'是 2 字符，char(20)存储为'IS '（18 个空格）。WHERE Sdept='IS'比较时忽略空格，但视图定义中若Sdept值含空格，SELECT时可能因隐式转换出错。
解决：视图中用WHERE RTRIM(Sdept)='IS'；或建表时用varchar(20)。

5.5 现象：INSERT INTO SC VALUES('20100001','1',78)成功，但INSERT INTO SC VALUES('20100001',null,78)失败，报 “不能将值 NULL 插入列 'Cno'”

原因：SC表定义中Cno char(4)未声明NULL，即默认NOT NULL。而foreign key(Cno) references Course(Cno)要求Cno必须存在，故NULL被禁止。
解决：若需支持未选课，应显式定义Cno char(4) NULL，并确保外键约束设为ON DELETE SET NULL（但需Cno允许NULL）。

6. 设计取舍思考：为什么 SQL Server 2016 用聚簇索引实现主键，而 PostgreSQL 用堆表+独立索引？

这个问题的答案藏在存储引擎的设计哲学里。SQL Server 2016 的聚簇索引模型，本质是以空间换时间的确定性优化：它牺牲了插入灵活性（主键必须单调递增以避免页分裂），换取了查询的极致可预测性——任何基于主键的查询，I/O 次数恒定为log2(N)，且范围查询天然有序。而 PostgreSQL 的堆表模型，是以时间换空间的通用性妥协：数据行物理位置与键值无关，插入极快（追加到堆末尾），但查询必须依赖二级索引+回表，范围扫描需额外排序。实验报告中create clustered index iSnoCno on Sc(Sno,Cno desc)的desc修饰，正是 SQL Server 允许你精细控制物理布局的体现——它让(Sno,Cno)的降序查询免于排序，而 PostgreSQL 即使建DESC索引，也需在执行时做Sort操作。

这种差异直接反映在实验操作中：当你执行UPDATE Student Set Sage=Sage+1，SQL Server 因聚簇索引有序，可顺序扫描页；而若在 PostgreSQL 中执行同样语句，优化器可能选Seq Scan（全表扫描），因堆表无序，索引扫描反而更慢。再看DELETE操作：SQL Server 的Ghost Cleanup异步回收，保证了高并发删除时主线程不阻塞；PostgreSQL 的VACUUM则需显式触发，否则死元组堆积导致膨胀。实验报告里没提这些，但你在 SSMS 中执行DBCC SHOWCONTIG看到的Scan Density，就是聚簇索引碎片的量化指标——它提醒你：主键设计不是写完PRIMARY KEY就结束，而是要持续监控avg_page_space_used_in_percent，低于 75% 就该REBUILD。

我第一次在某高校实验室带学生做这个实验时，发现他们总在ALTER TABLE Student ADD Sclass char(4)后抱怨查询变慢。查执行计划才发现，Sclass加入后，Student表行宽增大，原本 1 页存 50 行变成存 40 行，页数增加 25%，Clustered Index Scan的 I/O 次数直接上升。从那以后，我每次设计表结构，都强制走一遍sp_spaceused 'Student'和DBCC SHOWCONTIG，再根据avg_page_space_used_in_percent决定是否调整char长度或改用varchar。数据库不是黑匣子，它的每一行、每一页、每一个锁，都在用最诚实的方式告诉你：抽象之上的性能，永远由物理细节决定。希望帮到你。

本文还有配套的精品资源，点击获取