当前位置： 首页 > ds >正文

数据库原理实验：视图与索引

ds 2025/5/14 6:21:40

数据库原理实验：视图与索引

一、实验概述

实验目的

  1. 掌握视图与索引的定义及语法。
  2. 理解视图与索引的作用、原理及应用场景。
  3. 熟练使用SQL Server 2022进行视图和索引的创建、修改、删除及数据操作。

实验环境

  • 操作系统：Windows 11
  • 数据库管理系统：SQL Server 2022
  • 实验数据：
    • 学生表（student057）
    • 成绩表（score057）
    • 课程表（course057）
    • 教师表（teacher057）

二、验证性实验：基础语法与功能验证

（一）视图的创建与操作

1. 创建基础视图
-- 创建视图V1，连接三张表获取学生成绩信息
CREATE VIEW V1_057 AS
SELECT s.sno, sname, cname, degree
FROM student057 s
JOIN score057 sc ON sc.sno = s.sno
JOIN course057 c ON c.cno = sc.cno;
GO
  • 关键点：通过JOIN语句实现多表关联，视图简化了复杂查询。在这里插入图片描述
2. 指定列名与别名
-- 方式1：SELECT中指定列别名
CREATE VIEW V2_057 AS
SELECT s.sno 学号, sname 姓名, cname 课程名, degree 成绩
FROM student057 s
JOIN score057 sc ON sc.sno = s.sno
JOIN course057 c ON c.cno = sc.cno;-- 方式2：CREATE VIEW时显式声明列名
CREATE VIEW V3_057(学号, 姓名, 课程名, 成绩) AS
SELECT s.sno, sname, cname, degree
FROM student057 s
JOIN score057 sc ON sc.sno = s.sno
JOIN course057 c ON c.cno = sc.cno;

注意列名
在这里插入图片描述

  • 注意：两种方式均可定义视图列名，显式声明更清晰，避免列名歧义。
3. 加密视图（WITH ENCRYPTION）
CREATE VIEW V4_057 WITH ENCRYPTION AS
SELECT s.sno, sname, cname, degree
FROM student057 s
JOIN score057 sc ON sc.sno = s.sno
JOIN course057 c ON c.cno = sc.cno;-- 验证加密效果（无法查看视图定义）
SELECT name, text FROM sysobjects o JOIN syscomments c ON o.id = c.id WHERE name = 'V4_057';
select name,text from sysobjects o join syscomments c on o.id=c.id where name='v1_057'

V4_057,V1_057查询对比图：
在这里插入图片描述
在这里插入图片描述

  • 作用：保护视图定义不被他人查看，适用于敏感业务逻辑。
4. 通过视图修改数据
CREATE VIEW V5_057 AS
SELECT s.sno, sname, CLASS, c.CNO, cname, degree
FROM student057 s
JOIN score057 sc ON sc.sno = s.sno
JOIN course057 c ON c.cno = sc.cno
WHERE degree > 80;-- 更新学生信息（直接影响基表）
UPDATE V5_057 SET sname = '李君帅1', CLASS = '95031' 
WHERE sname = '李君帅' AND cname = '数字电路';

在这里插入图片描述
在这里插入图片描述

  • 限制：视图修改需满足基表约束，且WHERE子句需包含关键列以定位唯一行。
5. 检查选项（WITH CHECK OPTION）
CREATE VIEW V6_057 AS
SELECT s.sno, sname, c.cno, cname, degree
FROM student057 s
JOIN score057 sc ON sc.sno = s.sno
JOIN course057 c ON c.cno = sc.cno
WHERE degree > 80
WITH CHECK OPTION;-- 尝试将成绩改为75（违反条件，操作失败）
UPDATE V6_057 SET degree -= 5 WHERE sno = '101' AND cno = '4-101';

在这里插入图片描述

  • 作用：确保通过视图插入/更新的数据满足WHERE条件，防止无效数据进入。

（二）索引的创建与操作

1. 简单索引（提升查询速度）
-- 创建姓名索引
CREATE INDEX sname_ind ON student057(sname);-- 插入测试数据（索引自动维护）
INSERT INTO student057 VALUES('110', '曾华庆', '男', '1999-09-01', '95033');

在这里插入图片描述

  • 适用场景：频繁查询的非主键列，如按姓名检索学生。
2. 唯一索引（确保数据唯一性）
CREATE UNIQUE INDEX sname_UNI_ind ON student057(sname);-- 插入重复姓名会报错
INSERT INTO student057 VALUES('110', '曾华庆', '男', '1999-09-01', '95033');

在这里插入图片描述

  • 注意：唯一索引允许NULL，但多个NULL视为重复（除聚集索引外）。
3. 唯一聚集索引（改变数据物理存储顺序）
CREATE UNIQUE CLUSTERED INDEX sno_cl_ind ON student057(sno);
  • 特性：一个表最多一个聚集索引，数据按索引列顺序存储，提升范围查询效率。
4. 组合索引（多列联合索引）
-- 按班级和生日创建组合索引
CREATE INDEX class_birth_ind ON student057(class, sbirthday);-- 索引顺序影响查询效率（左前缀匹配原则）
-- 有效查询：WHERE class='95031' 或 WHERE class='95031' AND sbirthday<'2000-01-01'
-- 无效查询：仅按sbirthday查询
  • 最佳实践：将高频查询的列放在索引前列。
5. 视图索引（提升视图查询性能）
-- 先创建绑定架构的视图
CREATE VIEW V13_057 WITH SCHEMABINDING AS
SELECT s.sno, sname, CLASS, sbirthday
FROM dbo.student057 s;-- 创建视图索引（需先有唯一聚集索引）
CREATE UNIQUE CLUSTERED INDEX sno_clu_ind ON V13_057(sno);

在这里插入图片描述

  • 前提：视图必须使用SCHEMABINDING绑定架构，且索引需从唯一聚集索引开始。

三、设计性实验：综合应用与场景实践

（一）复杂视图设计

1. 创建多列视图V_A
CREATE VIEW V_A_057 AS
SELECT s.sno 学号, sname 姓名, ssex 性别, c.cno 课程号, cname 课程名, degree 成绩
FROM student057 s
JOIN score057 sc ON s.sno = sc.sno
JOIN course057 c ON sc.cno = c.cno;-- 批量修改数据
UPDATE V_A_057 SET 姓名 = '陆君茹1', 性别 = '女'
WHERE 学号 = '103' AND 课程号 = '3-105';

在这里插入图片描述

  • 注意：修改涉及基表的多个字段，需确保视图可更新（无聚合函数、JOIN操作限制）。
--一个语句把学号101 课程号4-101  的姓名改为李君帅1，课程名为[马克思主义基本原理]概论]？如何才能完成修改
CREATE TRIGGER trg_update_V_A_057
ON V_A_057
INSTEAD OF UPDATE
AS
BEGIN-- 更新学生表姓名UPDATE student057SET sname = i.姓名FROM inserted iWHERE student057.sno = i.学号;-- 更新课程表课程名UPDATE course057SET cname = i.课程名FROM inserted iWHERE course057.cno = i.课程号;
END;
GO-- 执行单个 UPDATE 语句（现在可以同时修改两个基表）
UPDATE V_A_057
SET 姓名 = '李君帅1', 课程名 = '[马克思主义基本原理]概论]'
WHERE 学号 = '101' AND 课程号 = '4-101';

在这里插入图片描述

2. 加密优秀学生视图V_B
CREATE VIEW V_B_057 WITH ENCRYPTION AS
SELECT sname 姓名, cname 课程名, degree 成绩
FROM student057 s
JOIN score057 sc ON s.sno = sc.sno
JOIN course057 c ON sc.cno = c.cno
WHERE degree >= 80;
  • 验证加密：通过系统视图查询时，视图定义显示为乱码或不可见。
3. 带检查选项的视图V_C
CREATE VIEW V_C_057 AS
SELECT sname 姓名, cname 课程名, degree 成绩
FROM student057 s
JOIN score057 sc ON s.sno = sc.sno
JOIN course057 c ON sc.cno = c.cno
WHERE degree >= 80
WITH CHECK OPTION;-- 修改成绩为78时失败（违反CHECK OPTION）
UPDATE V_C_057 SET degree = 78 WHERE 姓名 = '王丽娜' AND 课程名 = '计算机导论';

（二）索引

1. 教师表唯一性索引
-- 正确表名：teacher057
CREATE UNIQUE INDEX teacher_name_uni_057 ON teacher057(tname);
  • 作用：防止教师表中出现重复姓名。
2. 课程表聚集索引
-- 正确表名：course057
CREATE CLUSTERED INDEX course_name_clu_057 ON course057(cname);
  • 效果：课程表数据按课程名排序存储，提升课程名相关查询效率。
3. 索引视图V_D
CREATE VIEW V_D_057 WITH SCHEMABINDING AS
SELECT s.sno 学号, sname 姓名, ssex 性别, c.cno 课程号, cname 课程名, degree 成绩
FROM dbo.student057 s
JOIN dbo.score057 sc ON s.sno = sc.sno
JOIN dbo.course057 c ON sc.cno = c.cno;-- 创建索引
CREATE UNIQUE CLUSTERED INDEX idx_d_sno ON V_D_057(学号);
CREATE INDEX idx_d_name ON V_D_057(姓名);

四、思考题解答

  1. 视图的作用

    • 简化复杂查询，封装多表关联逻辑。
    • 控制数据访问，通过视图限制用户可见列和行。
    • 数据安全，加密视图定义或通过CHECK OPTION保证数据合法性。

  2. 索引视图

    • 定义：在视图上创建索引，提升频繁查询的视图性能。
    • 创建步骤：
      1. 用SCHEMABINDING选项创建视图，绑定架构。
      2. 先创建唯一聚集索引，再创建非聚集索引。

  3. 聚集索引 vs 非聚集索引

    • 聚集索引：数据按索引顺序物理存储，一个表仅有一个（如主键索引）。
    • 非聚集索引：存储索引键和数据地址，不改变数据物理顺序，可创建多个（如姓名索引）。

  4. 索引检索过程

    • 聚集索引：直接定位数据页，适合范围查询（如查询分数80-90分）。
    • 非聚集索引：先查索引页获取数据地址，再访问数据页，适合精确查询（如按学号查询）。

  5. 全文索引（选做）

    • 作用：针对文本数据（如备注、描述）的高效搜索，支持模糊匹配和分词。
    • 创建：通过CREATE FULLTEXT INDEX语句，需先启用全文搜索功能。

五、实验总结

遇到的问题

  1. 语法错误：中英文逗号混用、GO命令缺失导致批量语句执行失败。
  2. 视图可更新性：包含JOIN或聚合函数的视图无法修改数据，需确保视图基于单表或满足可更新条件。
  3. 索引冲突：创建唯一索引时未注意已有重复数据，导致创建失败。

解决方法

  • 严格检查SQL语句语法，使用SSMS的语法提示功能。
  • 查阅MSDN文档，明确视图可更新的条件（如无GROUP BY、DISTINCT等）。
  • 插入数据前先通过SELECT COUNT(*) FROM TABLE GROUP BY COLUMN HAVING COUNT(*) > 1检查重复值。

实验收获

  • 深入理解视图作为“虚拟表”的本质，掌握其在数据封装和权限控制中的应用。
  • 明确不同索引类型的适用场景，学会根据查询需求选择索引策略（如组合索引的左前缀原则）。
  • 认识到数据库对象命名规范的重要性（如实验中对象名添加学号后缀避免冲突）。

通过本次实验，不仅掌握了视图和索引的核心语法，更理解了它们在优化查询性能和保障数据安全中的实际价值。后续可进一步探索全文索引、索引维护（如重建、重组）等高级主题，提升数据库设计与优化能力。

http://www.xdnf.cn/news/5989.html

相关文章：

  • 游戏引擎学习第276天:调整身体动画
  • 【计算机网络】3数据链路层②
  • STM32 __main汇编分析
  • 基于 mathematical-expression 的动态数学方程求解器实现
  • 如何使用C51的Timer0实现定时功能
  • DeepBook 与 CEX 的不同
  • HDFS的客户端操作（2）文件上传
  • 重庆医科大学附属第二医院外科楼外挡墙自动化监测
  • 鸿蒙5.0项目开发——鸿蒙天气项目的实现（介绍）
  • OJ判题系统第6期之判题逻辑开发——设计思路、实现步骤、代码实现（策略模式）
  • 目标检测任务 - 数据增强
  • Linux文件编程——标准库函数（fopen等）和系统调用函数（open等）的区别
  • 无锡哲讯科技：引领芯片封装SAP系统的智能化革命
  • Flannel Host-gw模式的优缺点
  • Leetcode 3544. Subtree Inversion Sum
  • 深入学习 Java 泛型实现方式：擦除法！
  • 43、Server.UrlEncode、HttpUtility.UrlDecode的区别？
  • 物理：篮球为什么能被拍起来？
  • .Net HttpClient 使用Json数据
  • Centos7安装部署wordpress个人博客保姆级教程
  • iVX 研发基座：大型系统开发的协作与安全架构实践
  • 基于MATLAB的生物量数据拟合模型研究
  • 云蝠智能大模型呼叫优势：技术驱动全链路升级，重塑智能交互服务新体验
  • 前端性能优化3:深入分析 Web Worker 和 Service Worker
  • 【源码+文档+调试讲解】驾校报名小程序2
  • python打卡day24
  • ppy/osu构建
  • window 显示驱动开发-创建分配时指定段
  • 块设备代码分析
  • 测试集群的功能-执行wordcount程序