实验四 视图的定义、更新与查询

一、实验目的

1. 通过实验理解和掌握基本表、视图的区别与联系；
2. 熟练掌握视图的定义与查询，熟悉视图的创建、删除、更新操作；
3. 通过实验体会视图技术的优点，理解视图的作用，掌握视图的应用场景；

• 实验内容

(一)完成教材3.6节相关例题的实验；

3.6.1

1.建立视图

语句格式 :CREATE VIEW <视图名>[(<列名> [,<列名>]…)] AS <子查询>[WITH CHECK OPTION];

WITH CHECK OPTION表示对视图进行UPDATE，INSERT 和DELETE操作时要

保证更新、插入或删除的行满足视图定 义中的谓词条件（即子查询中的条件表式）

[例3.86] 建立信息管理与信息系统专业学生的视图

◼ 省略视图IS_Student的列名，表示由子查询中SELECT子句中的五个列名组成。

◼ 关系数据库管理系统执行CREATE VIEW语句的结果只是把视图的定义存入数据

字典，并不执行其中的SELECT语句。

◼ 只有在对视图查询时，才按视图的定义从基本表中将数据查出。

[例3.87] 建立信息管理与信息系统专业学生的视图，并要求进行插入、修 改和删除

操作时仍需保证该视图只有信息管理与信息系统专业的学生。

·定义IS_Student1视图时加了WITH CHECK OPTION子句，对该视图进行插入、 修改和删除操作时，关系数据库管理系统会自动检查Smajor='信息管理与系统'的条件。   插入成功。   插入失败，不符合WITH CHECK OPTION条件

1）视图也可以建立在多个基本表上

 [例3.88] 建立计算机科学与技术专业选修了81001号课程的学生的 视图（包括学号、姓名、成绩属性）。

2）视图也可以建立在一个或多个已定义好的视图上，或建立在基本表与视图上。

[例3.89] 建立计算科学与技术专业选修了81001号课程且成绩在80分以上的学生的视图（包括学号、姓名、成绩属性） 。

3）带表达式的视图

[例3.90]将学生的学号、姓名、年龄定义为一个视图。

4）分组视图

[例3.91] 将学生的学号及平均成绩定义为一个视图。

[例3.92] 将Student表中所有女生记录定义为一个视图。

2.删除视图

❖语句的格式： DROP VIEW <视图名>[CASCADE];

◼ 视图定义从数据字典中删除

◼ 如果该视图上还导出了其他视图，使用CASCADE级联删除语句 ，把该视图和由它导出的所有视图一起删除

◼ 删除基表时，由该基表导出的所有视图定义都必须显式地使用 DROP VIEW语句删除

[例3.93] 删除视图S_AGE和视图IS_C1：

DROP VIEW S_AGE; /*成功执行*/

DROP VIEW IS_C1; /*成功执行*/

◼ 但是由于IS_C1视图上还导出了ISC2视图，所以查看ISC2会报告错误：视图 IS_C2依赖于视图ISC1。

 

◼ 级联删除语句：

DROP VIEW ISC1 CASCADE

执行此语句不仅删除了IS_C1视图，还级联删除了由它导出的IS_C2

使用SQL Server执行“  DROP VIEW ISC1 CASCADE; ”被提示有语法错误。

事实上报错的原因在于SQL Server不支持在删除过程中使用CASCADE关键字

3.6.2 查询视图

关系数据库管理系统实现视图查询的方法

 1）视图消解法（View Resolution）

进行有效性检查

转换成等价的对基本表的查询

执行修正后的查询

[例3.94]在计算机科学与技术专业学生的视图中，找出年龄小于等于20岁的 学生（包括学生的学号和出生日期）

[例3.95]查询选修了81001号课程计算机科学与技术专业学生。

关系数据库管理系统先从数据字典中取出视图IS_Student的定义，然后进行视图消解，把上面查询转换为： SELECT Student.Sno,Sname FROM Student,SC WHERE Student.Sno=SC.Sno AND SC.Cno='81001' AND Smajor='计算机科学与技术 ';

视图消解法的局限

有些情况下，视图查询的转换不能直接进行，查询会出问题

[例3.96]在S_GradeAVG视图中查询平均成绩在 83分以上的学生学号和平均成绩

❖视图消解法的局限

例3.91中视图S_GradeAVG的定义为：

CREATE VIEW S_GradeAVG(Sno,Gavg)

AS SELECT Sno,AVG(Grade)

 FROM SC

 GROUP BY Sno;

错误：

正确：

 

例3.96用如下SQL语句完成：

 

对视图查询与基于派生表查询的区别

视图一旦定义，将保存在数据字典，之后的所有查询都可以直接引用该视图

派生表只是在语句执行时临时定义，语句执行后该派生表定义即被删除

3.6.3 更新视图

1.更新视图：通过视图来插入（INSERT）、删除（DELETE）和修改 （UPDATE）数据。

2.视图的更新操作通过视图消解，转换为对基本表的更新操作

3.为防止用户有意无意地对不属于视图范围内的基本表数据进行操作，可在定义视图时加上WITH CHECK OPTION子句 （在视图上增、删、改数据时，关系数据库管理系统会检查视图定义中的条件，若不满足条件则拒绝执行该操作）

[例3.97]将计算机科学与技术系统专业学生视图IS_Student中学号为 “20180004”的学生姓名改为“刘新奇”。

视图消解后，对视图IS_Student的更新语句就转换为对基本表Student的更新： UPDATE Student SET Sname=‘刘新奇' WHERE Sno='20180004' AND Smajor='计算机科学与技术';

[例3.98]向信息管理与信息系统专业学生视图IS_Student1中插入一个新的学生记录(20180207，赵新，男，2001-7-19)

转换为对基本表的更新：

INSERT INTO Student(Sno,Sname,Ssex,Sbirthdate,Smajor)

VALUES('20180207','赵新','男','2001-7-19,'信息管理与信息系统’ );

系统自动将'信息管理与信息系统'放入VALUES子句中  

[例3.99]删除信息管理与信息系统专业学生视图IS_Student中学号为 “20180207”的记录。

❖并不是所有的视图都是可更新的

例3.91定义的视图S_GradeAVG是由学号和平均成绩两个属性列组成的， 平均成绩是由Student表中对元组分组后计算平均值得来 如果想把视图S_GradeAVG中学号为“20180001”学生的平均成绩改成90分

对视图的更新是无法转换成对基本表SC的更新，因为系统无法修改各科成绩，以使20180001学生平均成绩成为90所以S_GradeAVG视图是不可更新的

1）一般地，行列子集视图是可更新的

2）不可更新的视图与不允许更新的视图不同

 前者指理论上已证明是不可更新视图 ， 后者指实际系统中不支持其更新，但它本身有可能是可更新的视图

视图的作用：适当的利用视图可以更清晰的表达查询

查询“对每个同学找出他获得最高成绩的课程号”

可以先定义一个视图，求出每个同学获得的最高成绩

然后用如下的查询语句完成查询：

（二）完成P124页课后练习题9。

请为三建工程项目建立一个供应情况的视图，包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)。

1. 找出三建工程项目使用的各种零件代码及其数量。

1. 找出供应商S1的供应情况。

三、实验心得

视图是从数据库的基本表中选取出来的数据组成的逻辑窗口，是基本表部分行、部分列数据的组合。与基本表不同的是，视图是一个虚表，数据库中只存储视图的定义，而不存储视图的数据，这些数据仍存放在原来的基本表中。

使用视图的好处：

1、视图数据会始终与基本表数据保持一致。

2、节省存储空间。当数据量非常大时，重复存储数据是非常耗费空间的。

通过实验理解和掌握基本表、视图的区别与联系；熟练了掌握视图的定义与查询，熟悉了视图的创建、删除、更新操作；通过实验体会了视图技术的优点，理解了视图的作用。