简介本资源是太原理工大学软件工程专业《数据库概论》课程配套的完整实验报告面向高校数据库初学者与SQL实践者聚焦SQL Server 2016环境下数据库对象管理、数据定义与操作等核心能力训练。报告覆盖创建/修改/删除表、索引含唯一索引与聚簇索引、视图及INSERT/UPDATE/SELECT等关键语句的实操过程并附带大量可直接运行的SQL示例如Student/Course/Sc三表建模、IS_Student视图构建、多表插入与条件更新助力读者夯实数据库系统原理与工程实践基础。资源为单文件docx文档大小1.69MB结构清晰、排版规范含实验目的、平台配置、分步代码、执行结果与注意事项便于课堂复现与自学对照。已有312人学习下载适合作为课程复习资料、实验预习模板或SQL语法速查参考。1. 主键不是“加个 PRIMARY KEY 就完事”为什么 Student 表里插入两条Sno20100001的记录会直接报错而UPDATE Student SET Sno20100001 WHERE Sname张立却能成功执行一半就卡住这不是 SQL Server 的 bug也不是学生手抖输错了——这是数据库内核在用最原始的方式告诉你主键约束不是语法糖而是存储引擎层面的强制性物理保障机制。你写的那行PRIMARY KEY(Sno)在 SQL Server 2016 底层会立刻触发三个不可绕过的动作① 自动为Sno列创建一个唯一聚簇索引除非显式指定非聚簇② 在插入/更新前强制走 B 树索引查找路径比对键值③ 若发现冲突不抛“逻辑错误”而是直接中断事务并回滚整个语句——连部分更新都不允许。更反直觉的是实验报告里那句UPDATE Student SET Sno20100021 WHERE Sname 张立看似只是改名字实则因Sno是主键SQL Server 必须先定位原记录、再检查新值是否已存在、最后才执行覆盖——三步缺一不可。而INSERT INTO Student VALUES(20100001,李斌,男,20,CS,1001,0)直接失败是因为聚簇索引页上Sno20100001的槽位已被占用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 Sno20100001时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 keySQL 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)实际执行了两件事创建一个非聚簇索引UQ__Course__Cname键列为Cname在该索引的 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 Screferenced_table Student证明外键已建立物理关联。而实验中DELETE FROM Student WHERE Sno20100002失败正是因为 SQL Server 在删除前检测到Sc表中存在Sno20100002的行触发了外键保护机制——这不是应用层逻辑而是存储引擎在事务日志中写入的强制检查点。3. 数据操作INSERT/UPDATE/DELETE 背后的事务日志与锁协议3.1 INSERT 的三阶段从解析计划到日志刷盘的完整生命周期实验报告中insert into Student Values (20100001,李勇,男,20,CS,1001)看似简单但 SQL Server 2016 执行时会经历严格流程解析与编译将 SQL 文本解析为语法树生成执行计划此处为Clustered Index Insert锁获取对Student表的聚簇索引根页加IX意向排他锁对目标数据页加X排他锁日志写入在事务日志LDF 文件中写入LOP_INSERT_ROWS日志记录包含完整行数据、页ID、槽位号数据写入将数据写入内存中的数据页并标记为DIRTY提交调用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 Sage22 where Sno20100001可能导致页分裂UPDATE并非总在原位置修改数据。当更新的列如Sage长度不变时SQL Server 会直接修改数据页中的对应字节但若更新VARCHAR列或导致行长度超过页容量则触发行迁移Row Forwarding原数据页中该行被标记为FORWARDED_RECORD并存入指向新位置的指针新数据写入另一数据页形成“跳转链”后续查询需两次 I/O先读原页指针再读新页数据。实验报告中Student表Sage定义为int固定 4 字节所以UPDATE Student Set Sage22属于安全更新不会迁移。但若你尝试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 SageSage1对全表生效SQL Server 会按聚簇索引顺序逐页扫描对每行执行就地更新——这正是ORDER BY Sno隐式生效的原因也是全表更新比随机更新慢的本质。3.3 DELETE 的“假删除”真相为什么delete from Student where Sno20100022后空间不立即回收DELETE操作并非物理擦除数据而是将数据页中对应行的槽位Slot标记为DELETED在页头的GAMGlobal Allocation Map和SGAMShared GAM中标记该页为“可重用”空间实际回收由后台Ghost Cleanup Task异步完成通常延迟数秒。这意味着DELETE后立即SELECT COUNT(*) FROM Student返回正确行数但DBCC SHOWCONTIG会显示页碎片率上升。实验报告中delete from Student where Sno20100022执行后若你紧接着插入新行SQL Server 会优先复用Sno20100022原位置的槽位而非申请新页。验证方式-- 查看 Student 表的页使用详情 DBCC IND(YourDatabaseName, Student, -1); -- 输出中关注 PageType1数据页的 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 表示聚簇索引 -- 查看实际执行计划CtrlM观察 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.SnoSC.Sno)的执行计划取决于两表数据量和索引Nested Loops若Student行数少 100、SC有Sno索引则对Student每行在SC索引中做一次查找Merge Join若两表均按Sno排序Student有聚簇索引SC有(Sno,Cno)聚簇索引则双指针归并O(mn) 时间Hash Join若两表都大且无序则构建哈希表O(mn) 但内存消耗大。验证方法在 SSMS 中执行查询前按CtrlM查看执行计划图标。实验环境下Student行数约 7SC行数约 8优化器几乎必选Nested Loops。而SELECT * FROM course first,course second WHERE first.Cpnosecond.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.Snox.Sno)是相关子查询而IN子查询可能是半连接相关子查询Correlated Subquery的致命特征是子查询中引用了外部查询的列如y.Snox.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.Snox.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 SnoStudent.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 Sno20100001返回空原因Sno列定义为char(8)而20100001是 8 字符但char(8)会右补空格至 8 位。若之前插入的记录是20100001 带空格则Sno20100001实际比较的是20100001vs20100001 因char比较时忽略尾部空格视为相同键值。解决统一用varchar(8)替代char(8)或插入时显式RTRIM(20100001 )验证用SELECT DATALENGTH(Sno),Sno FROM Student查看实际字节长度。5.2 现象UPDATE Student Set Sno20100021 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 LASTSQL Server 2022或旧版用ORDER BY Sdept, ISNULL(Sage, -1) DESC将NULL显式转为最小值。5.4 现象CREATE VIEW IS_Student AS select Sno,Sname,Sage from Student where SdeptIS创建后SELECT * FROM IS_Student返回空但SELECT * FROM Student WHERE SdeptIS有数据原因Sdept列定义为char(20)而IS是 2 字符char(20)存储为IS 18 个空格。WHERE SdeptIS比较时忽略空格但视图定义中若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 SageSage1SQL 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。数据库不是黑匣子它的每一行、每一页、每一个锁都在用最诚实的方式告诉你抽象之上的性能永远由物理细节决定。希望帮到你。本文还有配套的精品资源点击获取