11级_Java_曹建波6.04 数据库的视图

数据库的视图

视图概念

前面已经提到过视图（View），这一节专门讨论视图的概念、定义和操作。

视图是从一个或多个表（或视图）导出的表。视图是数据库的用户使用数据库的观点。例如，对于一个学校，其学生的情况存于数据库的一个或多个表中，而作为学校的不同职能部门，所关心的学生数据的内容是不同的。即使是同样的数据，也可能有不同的操作要求，于是就可以根据他们的不同需求，在物理的数据库上定义他们对数据库所要求的数据结构，这种根据用户观点所定义的数据结构就是视图。

视图与表（有时为与视图区别，也称表为基本表——Base Table）不同，视图是一个虚表，即视图所对应的数据不进行实际存储，数据库中只存储视图的定义，对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基本表。

视图一经定义以后，就可以像表一样被查询、修改、删除和更新。使用视图有下列优点：

（1）为用户集中数据，简化用户的数据查询和处理。有时用户所需要的数据分散在多个表中，定义视图可将它们集中在一起，从而方便用户的数据查询和处理。

（2）屏蔽数据库的复杂性。用户不必了解复杂的数据库中的表结构，并且数据库表的更改也不影响用户对数据库的使用。

（3）简化用户权限的管理。只需授予用户使用视图的权限，而不必指定用户只能使用表的特定列，也增加了安全性。

（4）便于数据共享。各用户不必都定义和存储自己所需的数据，可共享数据库的数据，这样同样的数据只需存储一次。

（5）可以重新组织数据以便输出到其他应用程序中。

MySQL 在5.0版中就已经实现了视图功能（包括可更新视图）。

1. 使用CREATE VIEW语句创建视图

语法格式：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

说明：

● view_name：视图名。

● column_list：要想为视图的列定义明确的名称，可使用可选的column_list子句，列出由逗号隔开的列名。column_list中的名称数目必须等于SELECT语句检索的列数。若使用与源表或视图中相同的列名时可以省略column_list。

● OR REPLACE：给定了OR REPLACE子句，语句能够替换已有的同名视图。

● ALGORITHM子句：可选的ALGORITHM子句是对标准SQL的MySQL扩展，规定了MySQL的算法，算法会影响MySQL处理视图的方式。ALGORITHM可取3个值：MERGE、TEMPTABLE或UNDEFINED。如果没有ALGORITHM子句，默认算法是UNDEFINED（未定义的）。指定了MERGE选项，会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。MERGE算法要求视图中的行和基表中的行具有一对一的关系，如果不具有该关系，必须使用临时表取而代之。指定了TEMPTABLE选项，视图的结果将被置于临时表中，然后使用它执行语句。

● select_statement：用来创建视图的SELECT语句，可在SELECT语句中查询多个表或视图。但对SELECT语句有以下的限制：

（1）定义视图的用户必须对所参照的表或视图有查询（即可执行SELECT语句）权限；

（2）不能包含FROM子句中的子查询；

（3）不能引用系统或用户变量；

（4）不能引用预处理语句参数；

（5）在定义中引用的表或视图必须存在；

（6）若引用不是当前数据库的表或视图时，要在表或视图前加上数据库的名称；

（7）在视图定义中允许使用ORDER BY，但是，如果从特定视图进行了选择，而该视图使用了具有自己ORDER BY的语句，则视图定义中的ORDER BY将被忽略。

（8）对于SELECT语句中的其他选项或子句，若视图中也包含了这些选项，则效果未定义。例如，如果在视图定义中包含LIMIT子句，而SELECT语句使用了自己的LIMIT子句，MySQL对使用哪个LIMIT未做定义。

● WITH CHECK OPTION：指出在可更新视图上所进行的修改都要符合select_statement所指定的限制条件，这样可以确保数据修改后，仍可通过视图看到修改的数据。当视图是根据另一个视图定义的时，WITH CHECK OPTION给出两个参数：LOCAL和CASCADED。它们决定了检查测试的范围。LOCAL关键字使CHECK OPTION只对定义的视图进行检查，CASCADED则会对所有视图进行检查。如果未给定任一关键字，默认值为CASCADED。

注意，使用视图时，要注意下列事项：

（1）在默认情况下，将在当前数据库创建新视图。要想在给定数据库中明确创建视图，创建时，应将名称指定为db_name.view_name。

（2）视图的命名必须遵循标志符命名规则，不能与表同名，且对每个用户视图名必须是唯一的，即对不同用户，即使是定义相同的视图，也必须使用不同的名字。

（3）不能把规则、默认值或触发器与视图相关联。

（4）不能在视图上建立任何索引，包括全文索引。

假设当前数据库是TEST，创建XSCJ数据库上的CS_KC视图，包括计算机专业各学生的学号、其选修的课程号及成绩。要保证对该视图的修改都要符合专业名为计算机这个条件。

CREATE OR REPLACE VIEW XSCJ.CS_KC

AS

SELECT XS.学号,课程号,成绩

FROM XSCJ.XS, XSCJ.XS_KC

WHERE XS.学号 = XS_KC.学号 AND XS.专业名 = '计算机'

WITH CHECK OPTION;

创建XSCJ数据库上的计算机专业学生的平均成绩视图CS_KC_AVG，包括学号（在视图中列名为num）和平均成绩（在视图中列名为score_avg）。

USE XSCJ

CREATE VIEW CS_KC_AVG(num, score_avg)

AS

SELECT 学号,AVG(成绩)

FROM CS_KC

GROUP BY 学号;

说明：这里SELECT语句直接从CS_KC视图中查询出结果。

视图定义后，就可以如同查询基本表那样对视图进行查询。

在视图CS_KC中查找计算机专业的学生学号和选修的课程号。

SELECT 学号,课程号

FROM CS_KC

查找平均成绩在80分以上的学生的学号和平均成绩。

本例首先创建学生平均成绩视图XS_KC_AVG，包括学号（在视图中列名为num）和平均成绩（在视图中列名为score_avg）。

创建学生平均成绩视图XS_KC_AVG：

CREATE VIEW XS_KC_AVG ( num,score_avg )

AS

SELECT 学号, AVG(成绩)

FROM XS_KC

GROUP BY 学号;

再对XS_KC_AVG视图进行查询。

SELECT *

FROM XS_KC_AVG

WHERE score_avg>=80;

执行结果为：

从以上两例可以看出，创建视图可以向最终用户隐藏复杂的表连接，简化了用户的SQL程序设计。

注意：使用视图查询时，若其关联的基本表中添加了新字段，则该视图将不包含新字段。例如，视图CS_XS中的列关联了XS表中所有列，若XS表新增了“籍贯”字段，那么CS_XS视图中将查询不到“籍贯”字段的数据。

如果与视图相关联的表或视图被删除，则该视图将不能再使用。

查询视图也可以在MySQL Query Browser查询工具中进行，方法与查询表数据类似。

1. 可更新视图

要通过视图更新基本表数据，必须保证视图是可更新视图，即可以在INSET、UPDATE或DELETE等语句当中使用它们。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构，这类结构会使得视图不可更新。如果视图包含下述结构中的任何一种，那么它就是不可更新的：

（1）聚合函数；

（2）DISTINCT关键字；

（3）GROUP BY子句；

（4）ORDER BY子句；

（5）HAVING子句；

（6）UNION运算符；

（7）位于选择列表中的子查询；

（8）FROM子句中包含多个表；

（9）SELECT语句中引用了不可更新视图；

（10）WHERE子句中的子查询，引用FROM子句中的表；

（11）ALGORITHM选项指定为TEMPTABLE（使用临时表总会使视图成为不可更新的）。

2. 插入数据

使用INSERT语句通过视图向基本表插入数据，有关INSERT语句的语法介绍见第 3章。

创建视图CS_XS，视图中包含计算机专业的学生信息，并向CS_XS视图中插入一条记录：（'081255'，'李牧'，'计算机'，1，'1990-10-21'，50，NULL，NULL）。

首先创建视图CS_XS：

CREATE OR REPLACE VIEW CS_XS

AS

SELECT *

FROM XS

WHERE 专业名 = '计算机'

WITH CHECK OPTION;

注意：在创建视图的时候加上WITH CHECK OPTION子句，是因为WITH CHECK OPTION子句会在更新数据的时候检查新数据是否符合视图定义中WHERE子句的条件。WITH CHECK OPTION子句只能和可更新视图一起使用。

接下来插入记录：

INSERT INTO CS_XS

VALUES('081255', '李牧', '计算机', 1, '1990-10-14', 50, NULL, NULL);

注意：这里插入记录时专业名只能为“计算机”。

这时，使用SELECT语句查询CS_XS视图和基本表XS，就可发现XS表中该记录已经被添加。

当视图所依赖的基本表有多个时，不能向该视图插入数据，因为这将会影响多个基本表。例如，不能向视图CS_KC插入数据，因为CS_KC依赖两个基本表：XS和XS_KC。

对INSERT语句还有一个限制：SELECT语句中必须包含FROM子句中指定表的所有不能为空的列。例如，若CS_XS视图定义的时候不加上“姓名”字段，则插入数据的时候会出错。

3. 修改数据

使用UPDATE语句可以通过视图修改基本表的数据，有关UPDATE语句的语法介绍见第3章。

将CS_XS视图中所有学生的总学分增加8。

UPDATE CS_XS

SET 总学分 =总学分+ 8;

该语句实际上是将CS_XS视图所依赖的基本表XS中所有记录的总学分字段值在原来基础上增加8。

若一个视图依赖于多个基本表，则一次修改该视图只能变动一个基本表的数据。

将CS_KC视图中学号为081101的学生的101课程成绩改为90。

UPDATE CS_KC

SET 成绩=90

WHERE 学号='081101' AND课程号='101';

本例中，视图CS_KC依赖于两个基本表：XS和XS_KC，对CS_KC视图的一次修改只能改变学号（源于XS表）或者课程号和成绩（源于XS_KC表）。

以下的修改是错误的：

UPDATE CS_KC

SET 学号='081120',课程号='208'

WHERE 成绩=90;

4. 删除数据

使用DELETE语句可以通过视图删除基本表的数据，有关DELETE语句的语法介绍见第3章。

删除CS_XS中女同学的记录。

DELETE FROM CS_XS

WHERE 性别 = 0;

注意：对依赖于多个基本表的视图，不能使用DELETE语句。例如，不能通过对CS_KC视图执行DELETE语句而删除与之相关的基本表XS及XS_KC表的数据。

使用ALTER语句可以对已有视图的定义进行修改。

语法格式：

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

ALTER VIEW语句的语法和CREATE VIEW类似，这里不过多叙述。

将CS_XS视图修改为只包含计算机专业学生的学号、姓名和总学分。

USE XSCJ

ALTER VIEW CS_XS

AS

SELECT 学号,姓名,总学分

FROM XS

WHERE 专业名 = '计算机';

1. 使用SQL语句删除视图

语法格式：

DROP VIEW [IF EXISTS]

view_name [, view_name] ...

[RESTRICT | CASCADE]

其中，view_name是视图名，声明了IF EXISTS，若视图不存在的话，也不会出现错误信息。也可以声明RESTRICT和CASCADE，但它们没什么影响。

使用DROP VIEW一次可删除多个视图。例如：

DROP VIEW CS_KC, CS_XS;

将删除视图CS_KC和CS_XS。

2. 使用MySQL Administrator删除视图

MySQL Administrator中删除视图的操作方法是：展开数据库和视图，单击需要删除的视图，选择下方的选项“Drop View”，出现如图4.34所示的对话框，在其中单击“Drop View（s）”按钮即可删除指定的视图。