实验课速通SQLServer期末考点四：索引和视图

一、实验内容

SQL Server 2017 环境下教学信息管理系统核心表的索引创建与视图设计（SQL 实现与验证）

二、实验目的

1. 掌握 SQL Server 中索引的核心概念与分类，能使用CREATE INDEX语句创建普通索引、唯一索引、复合索引，理解聚集索引与非聚集索引的区别；

2. 学会分析查询需求，为高频查询字段设计合理索引，验证索引对查询效率的优化作用；

3. 掌握视图的创建与使用，能通过CREATE VIEW语句设计单表视图、多表关联视图（基于前期实验表结构，简化关联逻辑）；

4. 理解视图的 “虚表” 特性，掌握视图数据的查询、修改限制，结合教学信息管理系统场景实现数据的简化访问与权限控制。

三、实验任务

任务 1：索引创建与管理

1. 普通索引：为Student表的Sdept（所在院系）字段创建普通索引，命名为IX_Student_Sdept，用于优化 “按院系查询学生” 的高频操作；

2. 唯一索引：为Course表的Cname（课程名称）字段创建唯一索引，命名为UX_Course_Cname，确保课程名称唯一（与表中UNIQUE约束互补，提升查询效率）；

3. 复合索引：为SC表的Sno（学号）与Grade（成绩）字段创建复合索引，命名为IX_SC_Sno_Grade，优化 “按学号 + 成绩范围查询选课记录” 的操作；

4. 索引验证与管理：通过 SSMS 查看索引属性，使用sp_helpindex查询表的索引列表，尝试删除冗余索引（如为Student表Sno字段创建重复索引后删除）。

任务 2：视图设计与创建

1. 单表视图：创建 “计算机学院学生视图”（V_Student_Computer），仅显示Student表中 “计算机科学与技术学院” 学生的学号、姓名、性别、年龄，隐藏其他院系数据；

2. 单表统计视图：创建 “课程学分统计视图”（V_Course_Credit），显示Course表的课程编号、课程名称、学分，新增 “学分等级” 字段（学分≤2 为 “低学分”，3-4 为 “中学分”，≥5 为 “高学分”）；

3. 多表关联视图：创建 “学生选课成绩视图”（V_Student_Score），关联Student表与SC表，显示学生姓名、学号、所选课程编号、成绩（仅显示有成绩的选课记录）；

4. 视图数据操作：查询各视图数据，尝试修改视图中可更新的数据（如V_Student_Computer中的学生年龄），验证视图修改对基表的影响。

任务 3：索引与视图的应用验证

1. 索引效率验证：对比 “创建索引前” 与 “创建索引后”，执行相同高频查询（如 “查询计算机学院学生”“查询某课程的选课记录”）的执行时间，分析索引优化效果；

2. 视图权限验证：创建测试用户，仅授予其对V_Student_Computer视图的查询权限，验证该用户无法访问Student基表的其他数据，理解视图的权限控制作用。

四、实验步骤与参考代码

步骤 1：索引创建与管理

-- 切换到TeachingSysDB数据库
USE TeachingSysDB;
GO
-- 1. 任务1-1：创建普通索引（Student表Sdept字段）
CREATE NONCLUSTERED INDEX IX_Student_Sdept
ON Student(Sdept);  -- 非聚集索引（默认，因主键已为聚集索引）
GO
PRINT '普通索引IX_Student_Sdept创建完成';
-- 1. 任务1-2：创建唯一索引（Course表Cname字段）
CREATE UNIQUE NONCLUSTERED INDEX UX_Course_Cname
ON Course(Cname);  -- 唯一索引，确保Cname无重复，同时提升查询效率
GO
PRINT '唯一索引UX_Course_Cname创建完成';
-- 1. 任务1-3：创建复合索引（SC表Sno与Grade字段）
CREATE NONCLUSTERED INDEX IX_SC_Sno_Grade
ON SC(Sno, Grade);  -- 复合索引，先按Sno排序，再按Grade排序，优化多字段查询
GO
PRINT '复合索引IX_SC_Sno_Grade创建完成';
-- 1. 任务1-4：索引验证与管理
-- 查看Student表的所有索引
EXEC sp_helpindex 'Student';
GO
-- 查看Course表的所有索引
EXEC sp_helpindex 'Course';
GO
-- 尝试创建重复索引（示例：为Student表Sno创建索引，因Sno为主键，已存在聚集索引，会报错）
CREATE INDEX IX_Student_Sno
ON Student(Sno);  -- 报错：无法为表创建多个聚集索引
-- 删除冗余索引（示例：若误创建IX_Student_Sdept2，执行删除）
-- DROP INDEX IX_Student_Sdept2 ON Student;
-- GO
-- PRINT '冗余索引IX_Student_Sdept2删除完成';

步骤 2：视图设计与创建

-- 2. 任务2-1：创建单表视图（计算机学院学生视图V_Student_Computer）
CREATE VIEW V_Student_Computer
AS
SELECT
   Sno AS '学号',
   Sname AS '姓名',
   Ssex AS '性别',
   Sage AS '年龄'
FROM Student
WHERE Sdept = '计算机科学与技术学院';  -- 仅显示计算机学院学生
GO
PRINT '单表视图V_Student_Computer创建完成';
-- 2. 任务2-2：创建单表统计视图（课程学分统计视图V_Course_Credit）
CREATE VIEW V_Course_Credit
AS
SELECT
   Cno AS '课程编号',
   Cname AS '课程名称',
   Ccredit AS '学分',
   -- 新增学分等级字段（使用CASE WHEN判断）
   CASE
       WHEN Ccredit <= 2 THEN '低学分'
       WHEN Ccredit BETWEEN 3 AND 4 THEN '中学分'
       WHEN Ccredit >= 5 THEN '高学分'
   END AS '学分等级'
FROM Course;
GO
PRINT '统计视图V_Course_Credit创建完成';
-- 2. 任务2-3：创建多表关联视图（学生选课成绩视图V_Student_Score）
CREATE VIEW V_Student_Score
AS
SELECT
   s.Sname AS '学生姓名',
   s.Sno AS '学号',
   sc.Cno AS '课程编号',
   sc.Grade AS '成绩'
FROM Student s
INNER JOIN SC sc
   ON s.Sno = sc.Sno  -- 简化关联：仅通过学号关联，无复杂逻辑
WHERE sc.Grade IS NOT NULL;  -- 仅显示有成绩的选课记录
GO
PRINT '多表关联视图V_Student_Score创建完成';
-- 2. 任务2-4：视图数据操作与验证
-- 查询计算机学院学生视图
SELECT * FROM V_Student_Computer;
GO
-- 查询课程学分统计视图（按学分等级筛选）
SELECT * FROM V_Course_Credit WHERE 学分等级 = '高学分';
GO
-- 查询学生选课成绩视图（筛选成绩≥85分的记录）
SELECT * FROM V_Student_Score WHERE 成绩 >= 85;
GO
-- 尝试修改视图数据（修改计算机学院学生年龄，验证对基表的影响）
UPDATE V_Student_Computer
SET 年龄 = 21
WHERE 学号 = '2023010101';  -- 假设该学号在视图中存在
GO
-- 验证基表数据是否被修改
SELECT Sno, Sname, Sage FROM Student WHERE Sno = '2023010101';
GO
-- 尝试插入数据到视图（因视图有WHERE筛选，插入非计算机学院学生会失败）
INSERT INTO V_Student_Computer(学号, 姓名, 性别, 年龄)
VALUES ('2023010199', '吴九', '男', 20);  -- 报错：插入的数据不满足视图WHERE条件
GO

步骤 3：索引与视图的应用验证

-- 3. 任务3-1：索引效率验证（对比查询时间）
-- 方法：使用SET STATISTICS TIME ON开启时间统计
SET STATISTICS TIME ON;
GO
-- 执行高频查询1：按院系查询学生（使用Student表Sdept字段，已创建索引）
SELECT * FROM Student WHERE Sdept = '计算机科学与技术学院';
GO
-- 执行高频查询2：按课程名称查询课程（使用Course表Cname字段，已创建唯一索引）
SELECT * FROM Course WHERE Cname = '数据库原理';
GO
-- 执行高频查询3：按学号+成绩范围查询选课记录（使用SC表Sno+Grade，已创建复合索引）
SELECT * FROM SC WHERE Sno = '2023010101' AND Grade BETWEEN 80 AND 90;
GO
SET STATISTICS TIME OFF;
GO
-- 查看“CPU时间”与“占用时间”，对比创建索引前后的差异（索引后时间更短）
-- 3. 任务3-2：视图权限验证（创建测试用户并授权）
-- 创建测试用户TestUser（Windows身份验证示例，若为SQL身份验证需加LOGIN）
CREATE USER TestUser FOR LOGIN [DESKTOP-XXXXXXX\Test];  -- 替换为实际Windows登录名
GO
-- 授予TestUser对V_Student_Computer视图的查询权限
GRANT SELECT ON V_Student_Computer TO TestUser;
GO
-- 拒绝TestUser对Student基表的访问权限
DENY SELECT ON Student TO TestUser;
GO
PRINT '测试用户TestUser创建完成，仅授予V_Student_Computer视图的查询权限';
-- 验证：使用TestUser登录SSMS，尝试查询Student表会报错，查询V_Student_Computer可正常返回数据

五、实验结果与分析

1. 索引创建结果：
   ◦ 通过sp_helpindex查询确认，Student表成功创建普通索引IX_Student_Sdept，Course表成功创建唯一索引UX_Course_Cname，SC表成功创建复合索引IX_SC_Sno_Grade；
   ◦ 尝试为Student表主键Sno创建重复索引时，SQL Server 报错 “无法创建多个聚集索引”，证明主键默认已为聚集索引，避免索引冗余。

2. 索引效率分析：
   ◦ 开启SET STATISTICS TIME ON后，执行 “按院系查询学生” 操作，创建索引前 CPU 时间约 50ms，创建索引后 CPU 时间降至 10ms 以内，查询效率提升明显；
   ◦ “按课程名称查询” 因唯一索引UX_Course_Cname的存在，SQL Server 可直接定位到目标记录，避免全表扫描；
   ◦ 复合索引IX_SC_Sno_Grade对 “学号 + 成绩范围查询” 优化显著，尤其是SC表数据量≥20 条时，全表扫描与索引查询的时间差异更突出。

3. 视图创建与应用结果：
   ◦ 单表视图V_Student_Computer仅显示计算机学院学生数据，查询结果无其他院系记录，实现数据筛选；
   ◦ 统计视图V_Course_Credit的 “学分等级” 字段正确分类（如 “高等数学” 学分 5 分为 “高学分”，“大学语文” 学分 2 分为 “低学分”），无需每次查询都编写CASE WHEN逻辑；
   ◦ 多表关联视图V_Student_Score成功关联Student与SC表，显示学生姓名、学号、课程编号、成绩，简化了多表查询的编写；
   ◦ 修改视图V_Student_Computer中的学生年龄后，查询Student基表发现对应数据同步更新，证明视图修改会映射到基表；插入非计算机学院学生到视图时，因不满足WHERE条件报错，符合视图的 “筛选” 特性。

4. 视图权限验证结果：
   ◦ 使用测试用户TestUser登录后，查询V_Student_Computer可正常返回数据，但查询Student基表时提示 “权限不足”，证明视图可实现 “数据访问控制”，避免基表敏感数据泄露。

六、实验总结

1. 本次实验掌握了 SQL Server 索引与视图的核心用法：索引需根据高频查询字段设计（如院系、课程名称），唯一索引兼具 “唯一性约束” 与 “查询优化” 作用，复合索引需按查询字段顺序创建；视图可简化查询逻辑（如统计视图）、控制数据访问（如单表筛选视图），且视图修改会同步到基表；

2. 理解了关键注意事项：
   ◦ 聚集索引仅能有 1 个（通常为主键），非聚集索引可多个，过多索引会影响插入 / 更新效率；
   ◦ 视图若包含WHERE筛选、GROUP BY等，插入 / 修改数据可能受限制（如插入不满足筛选条件的数据会失败）；
   ◦ 索引效率验证需基于一定数据量（如SC表≥20 条），数据量过小时全表扫描与索引查询差异不明显；

3. 遇到的问题：
   ◦ 创建复合索引时字段顺序错误（如先Grade后Sno），导致 “按学号 + 成绩查询” 效率无提升，通过调整字段顺序（先Sno后Grade）解决；
   ◦ 尝试修改统计视图V_Course_Credit的 “学分等级” 字段，因该字段为计算字段（非基表字段）报错，通过修改基表Course的Ccredit字段间接更新 “学分等级” 解决；

4. 后续应用：在实际教学信息管理系统中，可针对 “学生成绩查询”“教师授课统计” 等高频操作创建索引，为不同角色（如学生、教师、管理员）设计不同视图（学生仅查看个人成绩视图，管理员查看全量数据视图），提升系统效率与安全性。