一、用SQL语句完成下列功能。

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

create view View_CSTeacher as

select Teac_name, Teac_sex, TechPost

from Teacher

where Depar_id in (select Depar_id from Deparment where Depar_name = '计算机系');

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

create view VIEW_Class(Class_name, Depar_id, 班级人数)

as

select c.Class_name, d.Depar_id, count(Stu_id) '班级人数'

from Class c

         join Deparment d on c.Depar_id = D.Depar_id

         join Student s on c.Class_id = s.Class_id

group by c.Class_name, d.Depar_id;

3.建立一视图View_Student,列出每个学生的学号、选修课程门数和平均成绩,并按平均成绩从大到小排列。

(体验在视图中使用order by。注意观察视图中数据是否实现按成绩排序?为什么?)

/*************************没有包含未选课的****************************8*/

create view VIEW_Student as

select top 100 percent Stu_id, count(course_id) '选修课程门数', avg(Grade) '平均成绩'

from StudentGrade

group by Stu_id

order by avg(Grade) desc;


-- △如果order by没有加上TOP 100 percent:

-- 那么报错:除非另外还指定了 TOP、OFFSET 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。


/*************************包含了未选课的******************************/

create view VIEW_Student as

select TOP 100 percent s.Stu_id, count(course_id) '选修课程门数', avg(Grade) '平均成绩'

from Student s

         left join StudentGrade sg on s.Stu_id = sg.Stu_id

group by s.Stu_id

order by avg(Grade) desc;

        视图中的数据没有按成绩排序。

4.查询平均成绩大于85分的学生的所有信息。

(要求使用视图View_Student完成查询)

select s.*,vs.平均成绩

from VIEW_Student vs,

     Student s

where vs.平均成绩 > 85

  and vs.Stu_id = s.Stu_id;

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

/*包含未选课学生*/

    alter view View_Student as

    select s.Stu_id, s.Stu_name, count(Course_id) '选修课程门数', avg(Grade) '平均成绩'

    from Student s

             left join StudentGrade SG on s.Stu_id = SG.Stu_id

    group by s.Stu_id, s.Stu_name;

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

UPDATE View_Student 

SET 平均成绩 = 90 WHERE Stu_id = '000503002';

/* 报错:对视图或函数 'View_Student' 的更新或插入失败,因其包含派生域或常量域。

不可以实现,因为平均成绩是聚合函数查询,而基本表中没有对应的属性,修改的含义不确定,故视图不可更新。*/

7.删除视图View_CSTeacher。

drop view View_CSTeacher;

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

/*包含没选课的班级*/

create view View_Studentnum as

select c.Class_id, Class_name, Director, Monitor, Depar_id, count(s.Stu_id) '班级人数'

from Class c

         left join Student s on c.Class_id = s.Class_id

group by c.Class_id, Class_name, Director, Monitor, Depar_id;

二、小结

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

1.1 视图的优点

数据安全性:视图可以限制用户对数据的访问权限。通过在视图上设置适当的权限,可以控制用户只能访问他们需要的数据,而不是整个数据库。这提高了数据的安全性,防止未经授权的用户访问敏感数据

简化复杂查询:视图可以通过组合多个表、使用聚合函数等方式,将复杂的查询逻辑封装在一个视图中。这样,用户只需对视图进行简单的查询,而无需了解底层的复杂查询逻辑。视图的存在简化了用户对数据的操作,提高了查询的可读性和易用性。

数据抽象和封装:视图可以隐藏底层数据结构和表之间的关系,为用户提供一个抽象的数据层。通过定义视图,可以将数据的逻辑结构与物理结构分离,使用户更专注于数据的业务逻辑而不需要关注底层的实现细节。这提高了应用程序的模块化和可维护性。

简化数据更新:通过视图,可以简化对数据的更新操作。对于复杂的数据更新操作,可以通过在视图上定义触发器(Trigger)来自动处理。这样,用户只需对视图进行更新,而不需要直接操作底层的表,减少了错误和冗余的代码

提高性能:视图可以预先计算和缓存查询结果,以提高查询的性能。当多个用户频繁访问相同的查询逻辑时,使用视图可以避免重复计算,提高系统的响应速度

1.2 基本表与视图的区别和联系

1区别:

存储方式:基本表是实际存储数据的物理表,而视图是一个虚拟表,不存储实际的数据,只是对基本表的查询结果进行封装和展示。

数据更新:基本表可以直接进行数据的插入、更新和删除操作,而视图一般只能进行查询操作。对于某些特定的视图,可能允许进行部分的数据更新,但这些更新操作实际上是对基本表的操作。

数据结构:基本表具有自己的列和约束,而视图是基于一个或多个基本表的查询结果,其列和约束是由查询语句决定的。

存储空间:基本表需要占用实际的存储空间,而视图不需要额外的存储空间,只是在查询时动态生成结果。

2联系:

数据来源:视图是基于一个或多个基本表的查询结果,因此视图的数据来自于基本表

数据展示:视图可以对基本表的数据进行加工、过滤和组合,以提供更方便的数据展示形式。

数据访问权限:视图可以通过设置权限来限制用户对基本表的访问。通过视图,可以隐藏基本表的某些列或行,只允许用户访问他们需要的数据。

查询优化:视图可以提前计算和缓存查询结果,以提高查询性能。通过使用视图,可以避免重复执行复杂查询,减少系统的负载。

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

不是所有视图都可以更新,原因是视图的可更新性取决于视图的定义以及底层表的结构和约束

以下是一些情况下视图不能更新的示例

包含聚合函数的视图:如果视图中包含了聚合函数(如SUM、COUNT等),则该视图通常是不可更新的,因为聚合函数会导致数据的汇总和计算,无法直接反映到底层表中。

包含GROUP BY子句的视图:如果视图中包含了GROUP BY子句,用于对数据进行分组,那么该视图通常是不可更新的。因为分组操作会导致数据的合并和汇总,无法直接更新到底层表中。

包含DISTINCT关键字的视图:如果视图中使用了DISTINCT关键字,用于去重操作,那么该视图通常是不可更新的。因为去重操作会导致数据的合并和删除,无法直接更新到底层表中。

包含JOIN操作的视图:如果视图中包含了JOIN操作,用于连接多个表的数据,那么该视图通常是不可更新的。因为JOIN操作会导致数据来源的不唯一性,无法直接确定要更新哪个底层表的数据。

使用了子查询的视图:如果视图中包含了子查询,用于从其他表中检索数据,那么该视图通常是不可更新的。因为子查询的结果集无法直接映射到底层表中。

需要注意的是,即使视图本身是可更新的,也需要满足一些额外的条件才能进行更新,例如视图必须基于单个表、视图必须包含主键等。

点赞(0) 打赏

评论列表 共有 0 条评论

暂无评论

微信公众账号

微信扫一扫加关注

发表
评论
返回
顶部