第3章 关系数据库标准语言SQL——3.7 视图

3.7  视图

     视图是从一个或几个基本表（或视图）导出的表。它与基本表不同，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。所以一旦基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。从这个意义上讲，视图就像一个窗口，透过它可以看到数据库中自己感兴趣的数据及其变化。

     视图一经定义，就可以和基本表一样被查询、被删除。也可以在一个视图之上再定义新的视图，但对视图的更新（增、删、改）操作则有一定的限制。

     本节专门讨论视图的定义、操作及作用。

3.7.1  定义视图

01 建立视图

     SQL语言用create view命令建立视图，其一般格式为

     create view <视图名> [(<列名> [,<列名>]···)]

     as <子查询>

     [with check option];

     其中，子查询可以是任意的select语句，是否可以含有order by子句和distinct短语，则取决于具体系统的实现。

    with check option表示对视图进行update、insert和delete操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）。

     组成视图的属性列名或者全部省略或者全部指定，没有第三种选择。如果省略了视图的各个属性列名，则隐含该视图由子查询中select子句目标列中的诸字段组成。但在下列三种情况下必须明确指定组成视图的所有列名：

（1）某个目标列不是单纯的属性名，而是聚集函数或列表达式；

（2）多表连接时选出了几个同名列作为视图的字段；

（3）需要在视图中为某个列启用新的更合适的名字。

例3.84  建立信息系学生的视图。

create view IS_Student
as
select Sno,Sname,Sage
from Student
where Sdept = ‘IS’;

     本例中省略了视图IS_Student的列名，隐含了由子查询中select子句中的三个列名组成。

     关系数据库管理系统执行create view语句的结果只是把视图的定义存入数据字典，并不执行其中的select语句。只是在对视图查询时，才按视图的定义从基本表中将数据查出。

例3.85  建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。

create view IS_Student
as
select Sno,Sname,Sage
from Student
where Sdept = ‘IS’
with check option;

     由于在定义IS_Student视图时加上了with check option子句，以后对该视图进行插入、修改和删除操作时，关系数据库管理系统会自动加上Sdept = 'IS'的条件。

     若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，则称这类视图为行列子集视图。IS_Student视图就是一个行列子集视图。

    视图不仅可以建立在单个基本表上，也可以建立在多个基本表上。

例3.86  建立信息系选修了1号课程的学生的视图(包括学号、姓名、成绩)。

create view IS_S1(Sno,Sname,Grade)
as
select Student.Sno,Sname,Grade
from Student,SC
where Sdept = ‘IS’ and Student.Sno = SC.Sno and SC.Cno = ‘1’;

     由于视图IS_S1的属性列中包含了Student表与SC表的同名列Sno，所以必须在视图名后面明确说明视图的各个属性列名。

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

例3.87  建立信息系选修了1号课程且成绩在90分以上的学生的视图。

create view IS_S2
as
select Sno,Sname,Grade
from IS_S1
where Grade >= 90;

     这里的视图IS_S2就是建立在视图IS_S1之上的。

     定义基本表时，为了减少数据库中的冗余数据，表中只存放基本数据，由基本数据经过各种计算派生出的数据一般是不存储的。由于视图中的数据并不实际存储，所以定义视图时可以根据应用的需要设置一些派生属性列。这些派生属性由于在基本表中并不实际存在，也称它们为虚拟列。带虚拟列的视图也称为带表达式的视图。

例3.88  定义一个反映学生出生年份的视图。

create view BT_S(Sno,Sname,Sbirth)
as
select Sno,Sname,2014 - Sage
from Student;

     这里视图BT_S是一个带表达式的视图。视图中的出生年份值是通过计算得到的。

    还可以用带有聚集函数和group by子句的查询来定义视图，这种视图称为分组视图。

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

create view S_G(Sno,Gavg)
as
select Sno.avg(Grade)
from SC
group by Sno;

     由于as子句中select语句的目标列平均成绩是通过作用聚集函数得到的，所以create view中必须明确定义组成S_G 视图的各个属性列名。S_G是一个分组视图。

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

create view F_Student(F_sno,name,sex,age,dept)
as
select *
from Student
where Ssex = ‘女’;

     这里视图F_Student是由子查询“select * ”建立的。F_Student视图的属性列与Student表的属性列一一对应。如果以后修改了基本表Student的结构，则Student表与F_Student视图的映像关系就会被破坏，该视图就不能正常工作了。为避免出现这类问题，最好在修改基本表之后删除由该基本表导出的视图，然后重建这个视图。

02 删除视图

     该语句的格式为

    drop view <视图名> [cascade];

     视图删除后视图的定义将从数据字典中删除。如果该视图上还导出了其他视图，则使用cascade级联删除语句把该视图和由它导出的所有视图一起删除。

     基本表删除后，由该基本表导出的所有视图均无法使用了，但是视图的定义没有从字典中清除。删除这些视图定义需要显式地使用drop view语句。

例3.91  删除视图BT_S和视图IS_S1:

drop view BT_S;                         /*成功执行*/
drop view IS_S1;                        /*拒绝执行*/

     执行此语句时由于IS_S1视图上还导出了IS_S2视图，所以该语句被拒绝执行。如果确定要删除，则使用级联删除语句：

drop view IS_S1 cascade;                   /*删除了视图IS_S1和由它导出的所有视图*/

3.7.2  查询视图

     视图定义后，用户就可以像对基本表一样对视图进行查询了。

例3.92  在信息系学生的视图中找出年龄小于20岁的学生。

select Sno,Sage
from IS_Student
where Sage < 20;

     关系数据库管理系统执行对视图的查询时，首先进行有效性检查，检查查询中涉及的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询。这一转换过程称为视图消解(view resolution)。

本例转换后的查询语句为

select Sno,Sage
from Student
where Sdept = ’IS' and Sage < 20;

例3.93  查询选修了1号课程的信息系学生。

select IS_Student.Sno,Sname
from IS_Student,SC
where IS_Student.Sno = SC.Sno and SC.Cno = '1';

     本查询涉及视图IS_Student(虚表)和基本表SC，通过这两个表的连接来完成用户请求。

    在一般情况下，视图查询的转换是直截了当的。但有些情况下，这种转换不能直接进行，查询时就会出现问题，如例3.94。

     例3.94 在S_G视图（例3.89中定义的视图）中查询平均成绩在90分以上的学生学号和平均成绩，语句为

select *
from S_G
where Gavg >= 90;

例3.89中定义S_G视图的子查询为

select Sno.AVG(Grade)
from SC
group by Sno;

     将本例中的查询语句与定义S_G视图的子查询结合，形成下列查询语句：

select Sno,avg(Grade)
from SC
where avg(Grade)>=90
group by Sno;

    因为WHERE子句中是不能用聚集函数作为条件表达式的，因此执行此修正后的查询将会出现语法错误。正确转换的查询语句应该是

select Sno,avg(Grade)
from SC
group by Sno
having avg(Grade)>=90;

     目前多数关系数据库系统对行列子集视图的查询均能进行正确转换。但对非行列子集视图的查询（如例3.94）就不一定能做转换了，因此这类查询应该直接对基本表进行。

例3.94也可以用如下SQL 语句完成：

select *
from (select Sno,avg(Grade)
from SC
group by Sno) as S_G(Sno,Gavg)
where Gavg >= 90;

     但定义视图并查询视图与基于派生表的查询是有区别的。视图一旦定义，其定义将永久保存在数据字典中，之后的所有查询都可以直接引用该视图。而派生表只是在语句执行时临时定义，语句执行后该定义即被删除。

3.7.3  更新视图

     更新视图是指通过视图来插入(insert)、删除(delete)和修改(update)数据。

     由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新。像查询视图那样，对视图的更新操作也是通过视图消解，转换为对基本表的更新操作。

     为防止用户通过视图对数据进行增加、删除、修改时，有意无意地对不属于视图范围内的基本表数据进行操作，可在定义视图时加上with check option子句。这样在视图上增、删、改数据时，关系数据库管理系统会检查视图定义中的条件，若不满足条件则拒绝执行该操作。

例3.95  将信息系学生视图IS_Student中学号为“201215122”的学生姓名改为“刘辰”。

update IS_Student
set Sname = '刘辰'
where Sno = ’201215122';

转换后的更新语句为

update Student
set Sname = 刘辰'
where Sno = ’201215122' and Sdept = ’IS';

例3.96  向信息系学生视图IS_Student 中插入一个新的学生记录，其中学号为“201215129”，姓名为“赵新”，年龄为20岁。

insert
into IS_Student
values(‘201215129’,’赵新’,20);

转换为对基本表的更新：

insert
into Student(Sno,Sname,Sage,Sdept)
values(‘201215129’,’赵新’,20,’IS’);

     这里系统自动将系名‘IS’放入values子句中。

例3.97  删除信息系学生视图IS_Student中学号为“201215129”的记录。

delete
from IS_Student
where Sno = '201215129';

转换为对基本表的更新：

delete
from Student
where Sno='201215129' and Sdept='IS';

      在关系数据库中，并不是所有的视图都是可更新的，因为有些视图的更新不能唯一地有意义地转换成对相应基本表的更新。

例如，例3.89定义的视图S_G是由学号和平均成绩两个属性列组成的，其中平均成绩一项是由Student表中对元组分组后计算平均值得来的：

create view S_G(Sno,Gavg)
as
select Sno,avg(Grade)
from SC
group by Sno;

     如果想把视图S_G中学号为“201215121”的学生的平均成绩改成90分，SQL语句如下：

update S_G
set Gavg = 90
where Sno = '201215121';

     但这个对视图的更新是无法转换成对基本表SC的更新的，因为系统无法修改各科成绩，以使平均成绩成为90。所以S_G视图是不可更新的。

     一般地，行列子集视图是可更新的。除行列子集视图外，有些视图理论上是可更新的，但它们的确切特征还是尚待研究的课题。还有些视图从理论上就是不可更新的。

     目前，各个关系数据库管理系统一般都只允许对行列子集视图进行更新，而且各个系统对视图的更新还有更进一步的规定。由于各系统实现方法上的差异，这些规定也不尽相同。

例如，DB2规定：

（1）若视图是由两个以上基本表导出的，则此视图不允许更新。

（2）若视图的字段来自字段表达式或常数，则不允许对此视图执行insert和update操作，但允许执行delete操作。

（3）若视图的字段来自聚集函数，则此视图不允许更新。

（4）若视图定义中含有group by子句，则此视图不允许更新。

（5）若视图定义中含有distinct短语，则此视图不允许更新。

（6）若视图定义中有嵌套查询，并且内层查询的from子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。

     例如，将SC表中成绩在平均成绩之上的元组定义成一个视图GOOD_SC:

create view GOOD_SC
as
select Sno,Cno,Grade
from SC
where Grade >
(select avg(Grade)
from SC);

     导出视图GOOD_SC的基本表是SC，内层查询中涉及的表也是SC，所以视图GOOD_SC是不允许更新的。

（7）一个不允许更新的视图上定义的视图也不允许更新。

     应该指出的是，不可更新的视图与不允许更新的视图是两个不同的概念。前者指理论上已证明其是不可更新的视图。后者指实际系统中不支持其更新，但它本身有可能是可更新的视图。

3.7.4  视图的作用

     视图最终是定义在基本表之上的，对视图的一切操作最终也要转换为对基本表的操作。而且对于非行列子集视图进行查询或更新时还有可能出现问题。既然如此，为什么还要定义视图呢？这是因为合理使用视图能够带来许多好处。

01 视图能够简化用户的操作

     视图机制使用户可以将注意力集中在所关心的数据上。如果这些数据不是直接来自基本表，则可以通过定义视图使数据库看起来结构简单、清晰，并且可以简化用户的数据查询操作。

     例如，那些定义了若干张表连接的视图就将表与表之间的连接操作对用户隐蔽起来了。换句话说，用户所做的只是对一个虚表的简单查询，而这个虚表是怎样得来的，用户无须了解。

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

     视图机制能使不同的用户以不同的方式看待同一数据，当许多不同种类的用户共享同一个数据库时，这种灵活性是非常重要的。

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

     第1章中已经介绍过数据的物理独立性与逻辑独立性的概念。数据的物理独立性是指用户的应用程序不依赖于数据库的物理结构。数据的逻辑独立性是指当数据库重构造时，如增加新的关系或对原有关系增加新的字段等，用户的应用程序不会受影响。层次数据库和网状数据库一般能较好地支持数据的物理独立性，而对于逻辑独立性则不能完全地支持。

     在关系数据库中，数据库的重构往往是不可避免的。重构数据库最常见的是将一个基本表“垂直”地分成多个基本表。例如：将学生关系

Student(Sno, Sname, Ssex, Sage, Sdept)

     分为SX(Sno,Sname,Sage)和SY(Sno, Ssex, Sdept)两个关系。这时原表Student为SX表和SY表自然连接的结果。如果建立一个视图Student：

create view Student(Sno, Sname, Ssex, Sage, Sdept)
as
select SX.Sno, SX.Sname, SY.Ssex, SX.Sage, SY.Sdept
from SX,SY
where SX.Sno=SY.Sno;

     这样尽管数据库的逻辑结构改变了（变为SX和SY两个表），但应用程序不必修改，因为新建立的视图定义为用户原来的关系，使用户的外模式保持不变，用户的应用程序通过视图仍然能够查找数据。

     当然，视图只能在一定程度上提供数据的逻辑独立性，比如由于对视图的更新是有条件的，因此应用程序中修改数据的语句可能仍会因基本表结构的改变而需要做相应修改。

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

     有了视图机制，就可以在设计数据库应用系统时对不同的用户定义不同的视图，使机密数据不出现在不应看到这些数据的用户视图上。这样视图机制就自动提供了对机密数据的安全保护功能。

     例如，Student表涉及全校15个院系的学生数据，可以在其上定义15个视图，每个视图只包含一个院系的学生数据，并只允许每个院系的主任查询和修改本院系的学生视图。

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

     例如，经常需要执行这样的查询“对每个同学找出他获得最高成绩的课程号”。可以先定义一个视图，求出每个同学获得的最高成绩：

create view VMGRADE
      as      select SC.Sno,Cno
      from SC,VMGRADE
      where SC.Sno = VMGRADE.Sno and SC.Grade = VMGRADE.Mgrade;select Sno,max(Grade) Mgrade
      from SC
      group by Sno;

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

select SC.Sno,Cno
      from SC,VMGRADE
      where SC.Sno = VMGRADE.Sno and SC.Grade = VMGRADE.Mgrade;