实验4、SQL的视图

1. 实验目的

熟悉SQL支持的有关视图的操作，能够熟练使用SQL语句来创建需要的视图，对视图进行查询和删除视图。

1. 1. 实验内容

1. 1. 定义常见的视图形式，包括：
2. 行列子集视图
3. WITH CHECK OPTION的视图
4. 基于多个基表的视图
5. 基于视图的视图
6. 带表达式的视图
7. 分组视图
   1. 通过实验考察WITH CHECK OPTION这一语句在视图定义后产生的影响，包括对修改操作、删除操作、插入操作的影响。
   2. 讨论视图的数据更新情况，对子行列视图进行数据更新。
   3. 使用DROP语句删除一个视图，由该视图导出的其他视图定义仍在数据字典中，但已不能使用，必须显式删除。同样的原因，删除基表时，由该基表导出的所有视图定义都必须显式删除。

1. 1. 实验步骤

1. 创建一个行列子集视图xun_CS_View，给出选课成绩合格的学生的编号、教师编号、所选课程号和该课程成绩。
2. 创建基于多个基本表的视图xun_SCT_View，这个视图由学生姓名和他所选修的课程名及讲授该课程的教师姓名构成。
3. 创建带表达式的视图xun_EXP_View，由学生姓名及所选课程名和所有课程成绩都比原来多5分这几个属性组成。
4. 创建分组视图xun_Group_View，将学生的学号及他的平均成绩定义为一个视图。
5. 创建一个基于视图的视图，基于（1）中建立的视图，定义一个包括学生编号、学生所选课程数目和平均成绩的视图VV_View。
6. 查询所有选修课程“数据库原理及其应用”的学生姓名。
7. 插入元组（S52,T02,C02,59）到视图xun_CS_View中。若是在视图的定义中存在WITH CHECK OPTION字句对插入操作由什么影响。
8. 将视图xun_CS_View（包括定义WITH CHECK OPTION）中，所有课程编号为C01的课程的成绩都减去5分。这个操作数据库是否会正确执行，为什么？如果加上5分（原来95分以上的不变）呢？
9. 在视图xun_CS_View（包括定义WITH CHECK OPTION）删除编号S03学生的记录，会产生什么结果？
10. 删除视图xun_SCT_View和视图xun_CS_View

4.4实验结果

1.代码如下：

create view xun_CS_View
as
select xn_Sno,xn_Tno,xn_Cno,xn_Scroce
from xun_Reports
where xn_Scroce>=60
with  check option

2.代码如下：

create view xun_SCT_View
as 
select xn_Sname,xn_Cname,xn_Tname
from xun_Students,xun_Reports,xun_Courses,xun_Teachers
where xun_Students.xn_Sno=xun_Reports.xn_Sno
and xun_Reports.xn_Cno=xun_Courses.xn_Cno
and xun_Reports.xn_Tno=xun_Teachers.xn_Tno

3.代码如下：

create view xun_EXP_View(xn_Sname,xn_Cname,xn_Scroce)
as
select xn_Sname,xn_Cname,xn_Scroce+5
from xun_Students,xun_Reports,xun_Courses
where xun_Students.xn_Sno=xun_Reports.xn_Sno
and xun_Reports.xn_Cno=xun_Courses.xn_Cno

视图如下：

 

原先的xun_Reports表视图如下，可知成绩xn_Scroce已经加5.

 

4.代码如下：

create view xun_Group_View
as
select xn_Sno,avg(xn_Scroce) avgscore
from xun_Reports
group by xn_Sno

xun_Group_View视图如下：

5.

create view xun_VV_View
as 
select xn_Sno,xn_Cno,avg(xn_Scroce) avgscore
from xun_CS_View
group by xn_Sno,xn_Cno
having avg(xn_Scroce)>=60

6.

select xn_Sname
from xun_Students
where xn_Sno in(
 select xn_Sno
 from xun_Reports
    where xn_Cno in(
    select xn_Cno
    from xun_Courses
    where xn_Cname='数据库原理及其应用'
    )
)

7.

insert into xun_CS_View
values('S52','T02','C02',59)

下会有错误信息弹出，如下：

错误信息：

消息 550，级别 16，状态 1，第 1 行

试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了 WITH CHECK OPTION，而该操作的一个或多个结果行又不符合 CHECK OPTION 约束。

语句已终止。

 

因为建立xun_CS_View视图时加了with check option，有限制条件xn_Scroce要大于等于60，但这条记录的成绩不满足要求，所以不能成功插入。

若在建立xun_CS_View视图时不加了with check option，则可以成功插入，但视图不显示。

8.

减5

update xun_CS_View
set xn_Scroce-=5
where xn_Cno='C01'

下会有错误信息弹出，如下：

错误信息：

消息 550，级别 16，状态 1，第 1 行

试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了 WITH CHECK OPTION，而该操作的一个或多个结果行又不符合 CHECK OPTION 约束。

语句已终止。

 

因为减5后,有些记录的xn_Scroce值会有小于60，不满足约束要求，所以报错。

加5

update xun_CS_View
set xn_Scroce+=5
where xn_Cno='C01'and xn_Scroce<95

代码正常执行

 视图变为下图：

 加5后，不影响约束条件，所以可以正常执行。

9.

delete
from xun_CS_View
where xn_Sno='S03'

正常执行

10.

drop view xun_CS_View,xun_SCT_View

4.5实验体会：

1.with check option会限制视图的更新update，插入insert和删除delete,需要保证满足视图定义中的谓词条件。

2.不使用with check option语句，但是有谓词条件，不满足谓词条件的操作代码可以正常执行，但是这条数据就不会显示在视图中。

3.基于视图建立的视图和基于表建立视图的操作很相似，基本一样的。

4.使用DROP语句删除一个视图，由该视图导出的其他视图定义仍在数据字典中，但已不能使用，必须显式删除。

4.6附件：

1.
create view xun_CS_View
as
select xn_Sno,xn_Tno,xn_Cno,xn_Scroce
from xun_Reports
where xn_Scroce>=60
with  check option
 
2.
create view xun_SCT_View
as 
select xn_Sname,xn_Cname,xn_Tname
from xun_Students,xun_Reports,xun_Courses,xun_Teachers
where xun_Students.xn_Sno=xun_Reports.xn_Sno
and xun_Reports.xn_Cno=xun_Courses.xn_Cno
and xun_Reports.xn_Tno=xun_Teachers.xn_Tno
 
3.
create view xun_EXP_View(xn_Sname,xn_Cname,xn_Scroce)
as
select xn_Sname,xn_Cname,xn_Scroce+5
from xun_Students,xun_Reports,xun_Courses
where xun_Students.xn_Sno=xun_Reports.xn_Sno
and xun_Reports.xn_Cno=xun_Courses.xn_Cno
 
4.
create view xun_Group_View
as
select xn_Sno,avg(xn_Scroce) avgscore
from xun_Reports
group by xn_Sno
 
5.
create view xun_VV_View
as 
select xn_Sno,xn_Cno,avg(xn_Scroce) avgscore
from xun_CS_View
group by xn_Sno,xn_Cno
having avg(xn_Scroce)>=60
 
6.
select xn_Sname
from xun_Students
where xn_Sno in(
 select xn_Sno
 from xun_Reports
    where xn_Cno in(
    select xn_Cno
    from xun_Courses
    where xn_Cname='数据库原理及其应用'
    )
)
 
 
7.
insert into xun_CS_View
values('S52','T02','C02',59)
 
8.
减5
update xun_CS_View
set xn_Scroce-=5
where xn_Cno='C01'
加5
update xun_CS_View
set xn_Scroce+=5
where xn_Cno='C01'and xn_Scroce<95
 
9.
delete
from xun_CS_View
where xn_Sno='S03'
 
10.
drop view xun_CS_View,xun_SCT_View