数据库-SQL语言（视图）

目录

视图

1、视图的作用

2、定义视图

建立视图

常见的视图形式

3、查询视图

4、更新视图

5、删除视图

 

---

视图

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

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

1、视图的作用

1、视图机制使用户可以将注意力集中放在所关心的数据上。如果这些数据不是直接来自基本表，则可以通过定义视图使数据库看起来结构简单清晰，并且可以简化用户的数据查询操作。例如，那些定义了若干张表连接的视图就将表与表之间的连接操作对用户隐蔽起来，也就是说，用户所做的只是对一个虚表的简单查询，而这个虚表是怎么得来的，用户无需了解。

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

3、视图对重构数据库提供了一定程度的逻辑独立性。具体就不解释了，想了解的去看书或者查资料。

4、视图能够对机密数据提供安全保护。有了视图机制，就可以在设计数据库应用系统时对不同的用户定义不同的视图，使机密数据不出现在不应该看到这些数据的用户视图上。这样视图机制就自动提供了对机密数据的安全保护功能。例如，Student 表涉及全校15个院系的学生数据，可以在其上定义15个视图，每个视图只包含一个院系的学生数据，并只允许每个院系的主任查询和修改本院系的学生视图。

5、适当利用视图可以更清晰的表达查询。例：

--对每个同学找出他获得最高成绩的课程号。
--我们可以先定义一个视图，求出每个同学的最高成绩
create view Vmgrade as
	select Sno , max(Grade) Mgrade
	from SC
	group by Sno ;
--然后用如下查询语句完成查询
select SC.Sno , Cno
	from SC , Vmgrade
	where Vmgrade.Sno=SC.Sno and Vmgrade.Mgrade=SC.Grade ; 

 

2、定义视图

建立视图

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

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

as <子查询>  [ with check option ]

其中，子查询可以是任意的 select 语句，是否可以含有 order by 子句和 distinct 短语，取决于具体系统的实现。with check option 表示对视图进行 update 、 insert 和 delete 操作时要保证更新。插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）。

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

1）、某个目标列不是单纯的属性名，而是聚集函数或者列表达式；2）、多表连接时选出了几个同名列作为视图的字段；3）、需要在视图中为某个列启用新的更合适的名字。

另外，DBMS执行 create view 语句时只是存入视图的定义，并不执行其中的 select 语句。在对视图查询时，按视图的定义从基本表中将数据查出。

 

常见的视图形式

1、行列子集视图：

从单个基本表导出，保留了码，只是去掉了基本表的某些行和某些列。

例：建立 CS 系学生的视图（不需要性别）。

create view CS_Student
as
	select Sno , Sname ,Sage ,Sdept
	from Student
	where Sdept='CS'

--用来查看视图里边的数据
select* from CS_Student

2、基于多个基表的视图：

create view CS_Student1
as 
	select Student.Sno 学号,Sname 姓名, Sage 年龄, Sdept 系别,Grade 成绩 
	from SC , Student
	where SC.Sno=Student.Sno and Sdept='CS' and Cno='1'

select* from CS_Student1

3、基于视图的视图

--建立一个CS系，年龄超过20岁的学生的视图
--前边我们已有一个关于CS系的学生的视图，在此基础上建立新视图
create view CS_Student2
as
	select*
	from CS_Student
	where Sage>20

select* from CS_Student2

4、带表达式的视图

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

create view B_S(Sno,Sname,Birth)
--create view B_S(学号，姓名，出生年月)
as
	select Sno ,Sname , 2019-Sage
	from Student

select* from B_S

5、分组视图

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

--将学生的学号和他的平均成绩定义为一个视图
create view S_G(学号,平均成绩)
as
	select Sno , avg(Grade)
	from SC
	group by Sno ;

select* from S_G
--drop view S_G

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

 

注：一类不容易扩充的视图

以 select* 方式创建的视图可扩充性差，应尽可能避免。如将 Student表中所有女生记录定义为一个视图，有，

create view G_Student(Sno,name,sex,age,dept)
--create view G_Student
as
	select*
	from Student
	where Ssex='女'

select* from G_Student
--drop view G_Student

这里的视图是由子查询 select* 建立的， 视图的属性列与表 Student 的属性列一一对应，如果以后修改了基本表 Student 的结构（如增加新的属性列），则Student表与 G_Student 视图的映像关系就会被破坏，该视图就不能正常工作了。为避免出现这种问题，最好在修改基本表之后删除由该基本表导出的视图，然后在重新创建这个视图。或者在创建视图时子查询语句直接表明属性列，那样在修改基表结构时也不会破坏彼此之间的映像关系。

 

3、查询视图

视图定义后，用户就可以像对基本表一样对视图进行查询。从用户角度来看，查询视图与查询基本表用法相同。RDBMS在执行对视图的查询时，首先进行有效性检查，检查查询中涉及的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询。这一转换过程称为视图消解。

例：在 CS系学生的视图中找出年龄小于22岁的学生。

select *
from CS_Student
where Sage<22

经视图消解转换后的查询语句为：

select *
from Student
where Sdept='CS' and Sage<22

在查询视图时，视图消解法存在局限性，在有些情况下不能完成正确查询，有以下例子：

--建立一个视图，存放每个学生的学号和平均成绩
create view S_G(Sno,Gavg)
as
	select Sno,avg(Grade)
	from SC
	group by Sno ;
--drop view S_G
--在视图中查询平均成绩在85分以上的学生学号和平均成绩
select*
from S_G
where Gavg>=85

例子中，对视图的查询语句和定义视图的子查询结合，形成下列查询语句：

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

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

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

目前多数关系数据库系统对行列子集视图的查询均能进行正确转换。但对非行列子集视图的查询就不一定能做转换了。说明一点，不同版本中的SQL语句效果不是一样的，以上情况在2008版本中是成立的，我用的是2014版本的，对视图的查询没有问题。

看以下一个例子：

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

上面例子中，子查询生成了一个 派生表 S_G 。定义视图并查询视图与基于派生表的查询是有区别的。视图一旦定义，其定义将永远保存在数据字典中，之后的所有查询可以直接饮用该视图。而派生表只是在语句执行时临时定义，语句执行后该定义即被删除。

 

4、更新视图

更新视图是指通过视图来插入（insert）、删除（delete）和修改（update）数据。由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新。同查询视图一样，对视图的更新操作也是通过视图消解，转换为对基本表的更新操作。

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

例：向 CS系学生视图 CS_Student 中插入一个新的学生记录 ： 201212126 ， 赵信 ， 20岁 ，IS系。

insert into CS_Student
	values ('201215126','赵信',20,'IS')

实际上转换为对基本表的更新：

insert into Student(Sno,Sname,Sage,Sdept)
        values ('201215126','赵信',20,'IS')

另外我们会发现，语句虽然执行成功了，但没有出现在该视图中，其基本表 Student 中多了该记录，这是因为视图中只显示CS系的，而这个指明的系为 IS（没有指明默认为 null ）。此时的视图CS-Student 并没有带有 with check option 子句，故在对视图进行增删改操作时，不满足子查询中条件时也可以执行。下面我们试试建有带 with check option 子句的视图 CS_Student1 ，向其中插入学生记录：201212127 ， 德邦 ， 20岁 ， IS 系 ，如下：

--建立视图
create view CS_Student1
as
	select Sno , Sname , Sage , Sdept
	from Student
	where Sdept='CS'
	with check option

--记录插入
insert into CS_Student1
	values ('201215127','德邦',20,'IS')

会提示我们说，目标视图指定了 with check option ，所操作的结果不符合 check option 约束。指定过 with check option 子句后DBMS 在更新视图时会进行检查，以防止用户通过视图对不属于视图范围内的基本表数据进行更新。

从用户的角度来看，更新视图和更新基本表相同，更新视图最终是要转换成对基本表的更新。在带有 with check option 选项时，对视图更新操作需要注意，以上述例子来说明：

插入操作：系统自动检查 Sdept 的属性值是否为 CS ，如果是则可以插入；如果不是则拒绝插入；如果没有提供 Sdept属性值，默认为 null ，也会拒绝（注意有些DBMS如果没有提供 Sdept 属性值，则自动定义为 ‘CS’ ）。

不管是否带有 with check option 子句，在进行修改、删除和查询操作时，都会自动加上 Sdept= ' CS ' 的条件。

对视图的修改：

例：将 CS 系学生视图 CS_Student 中学号为 201215121 的学生姓名改为 李晨 。

update CS_Student
set Sname='李晨'
where Sno='201215121'

转化后的语句是：

update Student
	set Sname='李晨'
	where Sno='201215121' and Sdept='CS'

对视图的删除：

例：删除 CS 系学生视图 CS_Student 中学号为 201215121 的记录 。

delete from CS_Student
    where Sno='201215121'

 

更新视图的限制：

在关系数据库中，并不是所有的视图都是可更新的，因为有些视图的更新不能唯一地有意义地转换成对相应基本表的更新。有如下一个例子：视图S_G是由学号和平均成绩两个属性列组成，其中平均成绩列是由 Student 表中对元组分组后计算平均值得来的 ，即：

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

select* from S_G

如果想把视图中学号为 201215121 的学生的平均成绩改为 90 分，即：

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

这个对视图的更新是无法转换成对基本表的更新的，因为系统无法修改各科成绩，以使平均成绩为 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)、一个不允许更新的视图上定义的视图也不允许更新。

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

 

5、删除视图

该语句的格式为

drop view <视图名> [ cascade ]

视图被删除后视图的定义将从数据字典中删除。如果该视图上还导出了其他视图时，DBMS只删除这个视图，由它导出的视图还存在，但是已经失效，可以使用 cascade 级联删除语句把该视图和由它导出的所有视图一起删除，如果删除视图时没有 cascade 选项，则其导出的视图必须显式删除。同样，在基本表被删除后，由该基本表导出的所有视图均无法使用，但是视图的定义没有从数据字典中清除。删除这些视图定义还需要显式地使用 drop view 语句。