第3章〓视图
视图是一种常用的数据库对象，是由一个或几个基本表(或视图)导出的一个虚拟表，数据库中只存储视图的定义，并不存储视图对应的数据，数据都存储在基本表中。在使用视图时，DBMS自动进行“视图消解”，将对视图的一切操作(增、删、改、查)最终转换为对相应基本表的操作。当基本表中的数据发生变化时，通过视图查询的数据也随之改变； 当修改视图中的数据时，基本表中的数据也随之变化。视图为查看和存取数据提供了另外一种途径。

行列子集视图： 若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行或某些列，但保留了主码，称这类视图为行列子集视图。一般RDBMS都允许对行列子集视图进行更新，对其他类型视图的更新，不同系统有不同限制。

视图的主要作用： ①简化操作； ②提高数据安全性； ③屏蔽数据库的复杂性； ④使用户能以多种角度看待同一数据； ⑤对重构数据库提供了一定程度的逻辑独立性。

3．1创建视图

1) 创建视图的语法

CREATE VIEW 视图名 ［ (列名 ［ ,…n ］ ) ］ ［ WITH ENCRYPTION ］

AS 

select_statement ［ WITH CHECK OPTION ］ 

参数说明如下。

列名： 表示视图中的列名，需要全部指定或者全部省略。

WITH ENCRYPTION： 对语句文本加密。

AS： 关键字，后面接视图定义的语句。

select_statement： 定义视图的 SELECT 语句，也就是外模式到模式的映射。 

WITH CHECK OPTION： 强制针对视图执行的所有数据修改语句都必须符合在 select_statement 中设置的条件。

说明： 定义视图时，组成视图的列名或者全部省略或者全部指定。如果全部省略，则由SELECT目标列中的列名作为视图列； 如果SELECT某目标列是聚集函数或表达式，并且没有给出别名，则视图的列名不可省略。

2) 创建视图的例题

【例题31】建立所有少数民族学生的信息视图v_Student_ssmz。

代码： 

USE stuDB

GO  

CREATE VIEW v_Student_ssmz

AS 

SELECT * FROM  Student WHERE nation <> '汉族'


说明： 本章的例题都是基于stuDB数据库中的Student表、Course表、SC表，表结构如表11~表13所示。





【例题32】建立所有少数民族学生的信息视图v_Student_ssmz，并要求进行修改和插入操作时仍需保证该视图只有少数民族的学生。

代码： 

DROP VIEW v_Student_ssmz 

GO 

CREATE VIEW v_Student_ssmz

AS 

SELECT * FROM  Student WHERE nation <> '汉族'

WITH CHECK OPTION


说明： 创建视图时加入WITH CHECK OPTION选项，在通过视图插入、修改数据时会自动检查数据是否符合定义视图的条件： nation<> '汉族'，不符合则拒绝操作。

可以通过查询系统视图的方式来判断视图是否存在，存在则先删除后创建，将删除视图的代码改为： 

IF exists ( SELECT * FROM INFORMATION_SCHEMA．VIEWS 

WHERE table_name = 'v_Student_ssmz' )

DROP VIEW v_Student_ssmz

【例题33】基于多个基表的视图。建立学生的成绩视图(包括学号、姓名、民族、课程名、成绩)。

代码： 

CREATE VIEW v_grade

AS 

SELECT SC．Sno as 学号 , name as 姓名 , nation as 民族 ,

Cname as 课程名 , Grade as 成绩

FROM  Student,Course,SC

WHERE Student．Sno = SC．Sno and Course．Cno = SC．Cno


【例题34】基于视图的视图。建立少数民族学生的成绩视图(包括学号、姓名、课程名、成绩)。

代码： 

CREATE VIEW v_grade_ssmz

AS 

SELECT 学号 , 姓名 , 课程名 , 成绩 

FROM  v_grade

WHERE 民族 <>'汉族'


说明： 例题33已经创建了全体学生成绩情况的视图，本例题的视图内容是例题33视图的子集，视图上还可以再建视图。

【例题35】带表达式的视图。定义一个反映学生年龄的视图。

代码： 

CREATE VIEW v_studentNL( Sno , name , Sex , Nation , nl )

AS

SELECT Sno , name , Sex , Nation , year(GETDATE( )) - year(Birthday) FROM Student

GO


说明： 前面例题中都没有给出视图中的列名，表示用SELECT查询语句中的列名作为了视图的列名。本例题的SELECT 语句中有一个计算学生年龄的计算列，而且没有给计算列别名，因此必须在视图名字后面给出视图的列名。

【例题36】分组视图。将学生的学号及其平均成绩定义为一个视图。

代码： 

CREATE VIEW v_G

AS 

SELECT Sno , AVG( Grade ) as Gavg FROM SC GROUP BY Sno


说明： 本例题的SELECT语句中用聚合函数计算平均成绩，在语句中给出了列的别名，此时视图可以省略列名，表示使用SELECT语句中的列名作为视图列名。

【例题37】不指定属性列。将Student表中的所有女生记录定义为一个视图。

代码： 

CREATE VIEW v_Student(Sno , name , sex , nation , birthday)

AS

SELECT * FROM Student WHERE sex = '女'


说明： 本例题给出了视图的列名，但SELECT 语句中没有写具体列名，用*号查询表中的所有列，这样写语句会影响数据逻辑独立性。因为如果Student表需要增加列，Student表与视图v_Student的列就不再对应，映象关系被破坏，导致该视图错误，也影响相关应用程序。所以提醒读者，定义视图时不应该写SELECT *，应该把列名一个个写上。

3．2修改视图

1) 修改视图的语法

ALTER VIEW 视图名 ［ ( 列名 ［,…n］ ) ］ ［WITH ENCRYPTION］

AS

select_statement ［ WITH CHECK OPTION ］

说明： 修改视图需要给出视图的完整定义，此操作可以用先删除视图，然后再重新创建的操作替代。视图中不保存数据，数据都保存在基本表中，删除视图不会造成数据丢失。

2) 修改视图的例题

【例题38】修改视图v_Student_ssmz，改为查询所有汉族的学生。

代码： 

ALTER VIEW v_Student_ssmz

AS 

SELECT * FROM  Student WHERE nation = '汉族'

WITH CHECK OPTION


3．3删除视图

1) 删除视图的语法

DROP VIEW 视图名［,…n］

说明： 该语句为从数据字典中删除指定视图的定义，不会删除数据。删除基本表时，由该表导出的所有视图依旧存在，只是不可用，该表重新建好后，视图继续可用。

2) 删除视图的例题

【例题39】删除视图v_Student_ssmz。

代码： 

DROP VIEW v_Student_ssmz


3．4使用视图

1) 使用视图查询数据的例题

【例题310】在V_G视图中查询平均成绩在90分以上的学生学号和平均成绩。

代码： 

SELECT * FROM  v_G  WHERE Gavg >= 90


说明： FROM后面的数据源可以是三种表： 基本表、查询表和视图表。使用视图可以简化查询操作。

如果事先没有创建V_G视图，查询平均成绩在90分以上的学生学号和平均成绩的操作可以用如下代码完成。

代码： 

SELECT Sno , AVG(Grade) AS Gavg FROM SC 

GROUP BY Sno having  AVG(Grade) >= 60


2) 使用视图更新数据的例题

【例题311】利用少数民族学生的信息视图v_Student_ssmz修改学生数据，将学号为1003的学生姓名改为“晴空万里”。

代码： 

UPDATE v_Student_ssmz 

SET name = '晴空万里' 

WHERE Sno = 1003


说明： 视图并不存储数据，修改视图中的数据，实际上是修改视图对应的基本表中的数据。执行语句时系统自动根据视图的定义将语句转换为对基本表数据的修改，此转换过程称为“视图消解”，转换后的语句如表31所示。


表31视图消解后的语句


转换后的语句视图定义语句


UPDATE Student

SET name = '晴空万里'

WHERE Sno = 1003 and nation <> '汉族'
CREATE VIEW v_Student_ssmz

AS 

SELECT *

FROM  Student

WHERE nation <> '汉族'

WITH CHECK OPTION



【例题312】向少数民族学生信息视图v_Student_ssmz中插入一条新的学生记录： 张亮，男，鄂伦春族，生日为1997．10．10。

代码： 

USE stuDB

GO 

INSERT INTO v_Student_ssmz 

VALUES( '张亮','男','鄂伦春族','1997．10．10' )


说明： Student表中学号（Sno）是标识列，自动赋值，所以不需要给值。只要除Sno之外的列的数量和顺序与VALUES语句中值的数量和顺序一致，视图名后面就可以省略列名。

【例题313】向少数民族学生信息视图v_Student_ssmz中插入一条新的学生记录： 赵凯，男，汉族。

代码： 

INSERT INTO v_Student_ssmz(name , Sex , Nation) 

VALUES( '赵凯' , '男' , '汉族' )


说明： 同样都是插入数据的语句，例题312的语句执行成功，例题313的语句就执行失败。因为视图创建语句中有WITH CHECK OPTION选项，限制只能插入民族不是“汉族”的数据。

【例题314】删除少数民族学生信息视图v_Student_ssmz中学号为1001的学生信息。

代码： 

DELETE  FROM v_Student_ssmz 

WHERE Sno = 1001


说明： 此语句能够正确执行，但学号为1001的学生是汉族，并未删除。因为删除语句由系统自动转换为对基本表数据的删除语句： DELETE FROM Student WHERE Sno = 1001 AND nation<> '汉族'。

【例题315】通过视图v_G修改学号为1001学生的平均成绩为90分。

代码： 

UPDATE V_G SET Gavg = 90 WHERE Sno = 1001


说明： 此语句执行失败，因为该语句无法通过视图的定义转换为对基本表数据修改的语句。不是所有的视图都是可更新的，一般DBMS只允许对行列子集视图进行更新，视图v_Student_ssmz是行列子集视图。

实验7视图的使用

一、 实验目的

(1) 了解视图的用途。

(2) 熟悉视图的定义和使用方法。

二、 实验内容

继续使用实验2创建的银行储蓄数据库，客户信息（customerInfo）表、账户信息（accountInfo）表和交易信息（transInfo）表3个表的表结构见表218~表220。

请使用SQL语句完成如下实验内容。

(1) 创建视图v_1，包含客户编号、客户姓名、证件号码、电话，以便限制一些用户只能访问这一部分信息。

(2) 创建联合客户信息表和账户信息表两个表的视图v_2，显示客户编号、客户姓名、证件类型、证件号码、账号、存款类型、账户余额、账户状态，要求都显示汉字名称。

(3) 创建日统计视图v_3，每天显示一条，显示内容为日期、日存入金额、日支取金额、日合计金额(日存入金额-日支取金额)。

(4) 创建月统计视图v_4，显示内容为年月、月存入金额，月支取金额，月合计金额(月存入金额-月支取金额)。

(5) 通过视图v_1查询客户编号、客户姓名、证件号码、电话。

(6) 通过视图v_1增加一个客户信息。

(7) 通过视图v_2查询存款总额小于1万元的客户姓名、账号、账户余额。

(8) 通过视图v_2查询每种存款类型有多少个客户。

(9) 通过视图v_3删除一条信息，检验是否能删除。

(10) 通过视图v_4查询某一个月的存入和支取情况。

实验8SQL综合练习

一、 实验目的

(1) 熟练使用SQL语句完成各种操作，包括DDL语句(建库、建表)、DML语句(数据增、删、改)、DQL语句(数据查询)。

(2) 熟练进行视图的创建、使用和删除等基本操作。

二、 实验内容

(1) 使用SQL语句创建一个数据库，数据库名称为自己姓名的全拼，如zhangsan。

(2) 使用USE 命令打开刚创建的数据库。

(3) 使用SQL语句在刚创建的数据库中创建学生表、课程表和成绩表3张表，表名都加上自己姓名的全拼，表结构如表32~表34所示。


表32S+姓名全拼


列名
数 据 类 型
长度/字节
为空性
说明
sid
int
—
Not Null
学号，主键
class
varchar
10
Not Null
班级
name
varchar
8
Not Null
姓名
sex
char
2
—
性别，只可以为“男”或“女”
nation
varchar
20
—
民族
pid
char
18
—
身份证号，唯一
birthday
smalldatetime
—
—
出生日期



表33C+姓名全拼


列名
数 据 类 型
长度/字节
为空性
说明
cid
int
—
Not Null
课程号，主键
cname
varchar
30
Not Null
课程名
semester
char
1
—
开课学期
hour
int
—
—
学时



表34G+姓名全拼


列名
数 据 类 型
长度/字节
为空性
说明
ID
int
—
Not Null
主键，标识列(1，1)
sid
int
—
Not Null
来自学生表关系的外部关键字
cid
int
—
Not Null
来自课程表关系的外部关键字
grade
int
—
—
—



(4) 使用SQL语句增加数据。

① 在“学生”表中插入记录。

第一条为你个人的信息(如果无法录入完整的学号，录入最后两位短学号)，继续插入小组其他成员的信息，至少录入两位同组成员的信息。

② 在课程表中插入记录。

课程号课程名开课学期学时

101数据库264

102C语言180

③ 在成绩表中插入记录。

为你和你小组成员录入选课信息及成绩。

(5) 使用SQL语句修改数据。

按照姓名将某一位同学的民族改为“满族”，出生日期改为19991224。

(6) 使用SQL语句查询数据。

① 从“课程”表中查询课程的课程名、开课学期和学时。

② 在“学生”表中查询年龄为20~22岁的学生信息。

③ 查询学生的学号、姓名、课程名和分数，查询结果按课程名和分数降序排列。

④ 查询每个班级、每门课程的平均成绩，显示班级、课程名、平均成绩。

⑤ 用嵌套语句查询成绩在90分以上的学生的姓名和班级。

(7) 使用SQL语句删除数据。

① 按照学号在“学生”表中删除一名同学的信息。

② 按姓名删除“成绩”表中某位同学的信息(嵌套查询)。

(8) 创建使用视图。

① 创建一个视图“v_姓名全拼”，显示学生的学号、姓名、班级、课程名、分数。

② 在新建的视图中，按照姓名查询你自己的学号、姓名、课程名、分数。 

(9) 分离复制数据库文件(平台操作)。

保存代码，分离数据库，将代码和两个数据库文件都上传平台交作业，并填写学习心得。



习题解析


习题

一、 选择题

1． SQL中，创建视图的命令是（）。



A．  create tableB． create viewC． create indexD． create proc

2．  SQL中，删除一个视图的命令是（）。

A．  delete viewB． drop viewC． clear viewD． remove view

3．  下列在物理存储上并不存在的是（）。

A．  数据库B． 本地表C． 视图D． 自由表

4．  以下关于视图的说法中不正确的是（）。

A．  视图是个虚表B． 所有的视图均可以更新

C．  可以对视图进行查询D． 视图可以简化用户的操作

5．  在数据库系统中，当视图创建完毕后，数据字典中保存的是（）。

A．  查询语句B． 所引用的基本表的定义

C．  视图定义D． 查询结果

6．  视图机制提高了数据库系统的（）。

A．  完整性B． 安全性C． 一致性D． 并发控制

7．  以下关于视图的叙述中错误的是（）。

A．  视图不存储数据，但可以通过视图访问数据

B．  视图提供一种数据安全机制

C．  视图可以实现数据的逻辑独立性

D．  视图能够提高对数据的访问效率

8．  关系模式图书(图书编号，图书类型，图书名称，作者，出版社，出版日期)，图书编号唯一标识一本图书，建立“计算机”类图书视图VBOOK，并要求进行修改、插入操作时保证视图只有计算机类图书，实现上述要求的SQL语句是： Create view VBOOK as select 图书编号，图书名称，作者 from 图书 where 图书类型='计算机'()。

A．  FOR ALLB． PUBLIC

C． WITH CHECK OPTIOND． WITH GRANT OPTION

9．  数据库的视图和表之间通过建立（）之间的映像，保证数据的逻辑独立性。

A．  模式到内模式B． 外模式到内模式

C．  外模式到模式D． 外模式到外模式

10．  以下定义的4个视图中，能够进行更新操作的是（）。



A．  CREATEVIEWS_G(学号，姓名，课程名，分数)AS
SELECTS．学号，姓名，课程名，分数FROMstudent S，score SC，course C
WHERES．学号=SC．学号ANDSC．课程号=C．课程号

B．  CREATEVIEWS_AVG(学号，平均分数)AS
SELECT 学号，AVG(分数)FROMscoreWHERE 分数 ISNOTNULL
GROUPBY学号

C．  CREATEVIEWS_MALE(学号，姓名)AS
SELECT学号，姓名FROMstudentWHERE班号='1501'

D．  CREATEVIEWS_FEMALE(姓名，出生日期)AS
SELECT姓名，出生日期FROMstudentWHERE 性别='女'

11．  在视图上不能完成的操作是（）。

A．  更新视图B． 查询

C．  在视图上定义新的基本表D． 在视图上定义新视图

12．  视图是一个“虚表”，视图的构造基于（）。

A．  基本表B． 视图C． 基本表或视图D． 数据字典

13．  定义视图的语句是： create view v_emas select EmployeeID, EmployeeName, Sexfrom Employee。如果希望加密该视图定义语句，应该使用（）语句。



习题解析


A．  ENCRYPTIONB． WITH ENCRYPTION

C．  WITH CHECK OPTIOND． WITH GRANT OPTION

二、 判断题

1． 视图是观察数据的一种方法，只能基于基本表建立。（）

2． 视图有助于实现数据的逻辑独立性。（）

3． 因为通过视图可以插入、修改或删除数据，因此视图也是一个实在表，SQL Server将它保存在syscommens系统表中。 （）

4． 视图定义如果有变化，需要修改视图语句，而不能轻易删除视图再重新创建，因为删除视图可能会丢失数据。（）

5． 通过视图查询数据时，DBMS会从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换为等价的对基本表的查询，然后再执行修正后的查询，这一过程称为视图消解。()

6． 创建视图的语句“CREATE VIEW v1(ID，name)AS select sno,sname,sex from student；”是正确的。（）

7． 创建视图的语句“CREATE VIEW v2 AS select sno,avg(grade) from sc group by sno；”是正确的。（）