【SQL】实验九 视图的使用

这篇博客详细介绍了在SQL Server中使用视图的操作,包括如何创建、修改和删除视图,以及视图在数据查询和更新方面的限制。通过实例讲解了视图在聚合函数、排序和限制条件等方面的应用,强调了视图简化操作、逻辑独立性和安全保护等优点。同时,讨论了哪些类型的视图不可更新,并列举了不可更新视图的原因。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

 

一、实验目的

1.熟悉视图的操作。

 

二、实验内容及要求

用SQL语句完成下列功能。使用数据库为SCHOOL数据库。

 

1.建立一视图View_CSTeacher,列出计算机系各个老师的资料(姓名、性别、职称)。

(我使用的是嵌套查询,也可使用连接查询 )

create view View_CSTeacher
as select Teac_name,Teac_sex,TechPost from Teacher 
   where Depar_id=(select Depar_id from Deparment
                    where Depar_name='计算机系')

2.建立一视图View_Class,列出每个班级的名称、系别和班级人数。

create view View_Class
as select Class_name as 班级名称,Class.Depar_id as 系别,count(Student.Class_id) as 班级人数
   from Teacher,Class,Student
   where Class.Depar_id=Teacher.Depar_id and Class.Class_id=Student.Class_id

消息 8120,级别 16,状态 1,过程 View_Class,行 2 [批起始行 0]
选择列表中的列 'Class.Class_name' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。

包含count()等聚合函数的查询语句,select 后面的字段必须要么出现在group by中,要么出现在函数里。

因此加一个group by语句即可

create view View_Class
as select Class_name as 班级名称,Class.Depar_id as 系别,count(Student.Class_id) as 班级人数
   from Teacher,Class,Student
   where Class.Depar_id=Teacher.Depar_id and Class.Class_id=Student.Class_id
   group by Class_name,Class.Depar_id

3.建立一视图View_Student,列出每个学生的学号、选修课程门数和平均成绩,并按平均成绩从大到小排列。(体验在视图中使用order by。注意观察视图中数据是否实现按成绩排序? 

视图不能使用order by 语句,报错如下

create view View_Student
as select Student.Stu_id as 学生学号,count(StudentGrade.Stu_id) as 选修课程门数,avg(Grade) as 平均成绩
   from Student,StudentGrade
   where StudentGrade.Stu_id=Student.Stu_id
   group by Student.Stu_id
   order by avg(Grade)

消息 1033,级别 15,状态 1,过程 View_Student,行 6 [批起始行 0]
除非另外还指定了 TOP、OFFSET 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。 

 删去order by 语句,再使用如下代码查找视图数据,并使用order by 语句排序,即可得到

select * 
from View_Student
order by 平均成绩 desc

4.查询平均成绩大于85分的学生的所有信息。(要求使用视图View_Student完成查询)

select *
from View_Student
where 平均成绩>85

5.修改视图View_Student(修改视图定义),列出每个学生的学号、姓名、选修课程门数和平均成绩。

alter view View_Student
as select Student.Stu_id as 学生学号,Stu_name as 学生姓名,count(StudentGrade.Stu_id) as 选修课程门数,avg(Grade) as 平均成绩
   from Student,StudentGrade
   where StudentGrade.Stu_id=Student.Stu_id
   group by Student.Stu_id,Stu_name

6.要通过视图View_Student,将学号为“000503002”的平均成绩改为90分,是否可以实现?并说明原因

update View_Student
set 平均成绩=90
where 学生学号=000503002

消息 4406,级别 16,状态 1,第 1 行
对视图或函数 'View_Student' 的更新或插入失败,因其包含派生域或常量域。

因为视图里的数据是从其他表中得出的数据,修改视图中的数据最终表现为对表的修改,而表中不存在视图的某一属性,或属性的性质不相同,则无法更改,因此不能修改视图

解决方法:重新创建一个结构内容都一样的表,再进行修改

7.删除视图View_CSTeacher。

drop view View_CSTeacher

8.建一视图View_Studentnum,列出每个班级信息以及班级人数。

直接查找class的所有列名:

create view View_Studentnum
as select Class.Class_id,Class_name,Director,Monitor,Depar_id,count(Stu_id) as 班级人数
   from Student,Class
   where Class.Class_id=Student.Class_id
   group by Class.Class_id,Class_name,Director,Monitor,Depar_id

使用自身连接 :

create view View_Studentnum
as select c.*,count(Stu_id) as 班级人数
   from Student s,Class c
   where c.Class_id=s.Class_id
   group by c.*

 

三、实验小结

1.试述视图的优点;思考基本表与视图的区别和联系。

基本表是本身独立存在的表,在 SQL 中一个关系就对应一个表。

视图是从一个或几个基本表导出的表。它与基本表不同,是一个虚表。数据库中只存放视图的定义,而不存放视图中对应的数据,这些数据仍存放在原来的基本表中。所以一旦基本表中的数据发生变化,从视图中查询出来的数据也就随之改变了

视图的优点:

1.视图能够简化用户的操作

2.视图使用户能以多种角度看待同一数据

3.视图对重构数据库提供了一定程度的逻辑独立性

4.视图能够对机密数据提供安全保护

5.适当利用视图可以更清晰地表达查询

 

2.是否所有视图都可以更新?为什么?举例说明哪些视图不能更新。

不是,视图是不实际存储数据的虚表,因此对视图的更新,最终要转换为对基本表的更新。因为有些视图的更新不能唯一有意义地转换成对相应基本表的更新,所以,并不是所有的视图都可以更新的。

不能更新的视图如下:

1.若视图是由两个以上的基表导出的,则此视图不允许更新;

2.若视图的字段是来自字段表达式或常数,则不允许对此视图执行INSERT、UPDATE操作,允许执行DELETE操作;

3.若视图的字段是来自聚集函数,则此视图不允许更新;

4.若视图的定义中含有GROUP BY子句,则此视图不允许更新;

5.若视图的定义中含有DISTINCT短语,则此视图不允许更新;

6.若视图的定义中有嵌套查询,并且嵌套查询的FROM子句中涉及的表也是导出该视图的基表,则此视图不允许更新;

7.一个不允许更新的视图上定义的视图也不允许更新;

(由一个基表定义的视图,只含有基表的主键或候补键,并且视图中没有用表达式或函数定义的属性,才允许更新。)

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

老坛酸菜吃鸭子

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值