第5章数据库的视图和索引

1、数据库原理及应用第5章 数据库的视图索引数据库的视图索引主讲：时君丽5.1 视图视图 n视图概念视图概念 视图是从一个或多个表（或视图）导出的表。视图是数视图是从一个或多个表（或视图）导出的表。视图是数据库的用户使用数据库的观点。据库的用户使用数据库的观点。 视图与表有本质的区别：视图也称为虚表视图与表有本质的区别：视图也称为虚表,在在数据库中存储的视图是视图的定义数据库中存储的视图是视图的定义,即一串即一串SQL SELECT语句语句.而不是查询的数据而不是查询的数据.因此因此,对视图的查对视图的查询最终转化为对基表的查询询最终转化为对基表的查询. 一、视图的简介一、视图的简介n视图产生的源

2、表称为基表，视图可以派视图产生的源表称为基表，视图可以派生自一个或多个基表，也可以从其他的生自一个或多个基表，也可以从其他的视图派生。（数据源）视图派生。（数据源）n视图的操作与表的操作相类似，通过视视图的操作与表的操作相类似，通过视图既可以查询表的数据，也可以修改表图既可以查询表的数据，也可以修改表的数据。的数据。视图的优点视图的优点n为用户简化查询：将复杂的查询定义为视图，从而简为用户简化查询：将复杂的查询定义为视图，从而简化操作。化操作。n为用户定制数据：基表的行为用户定制数据：基表的行/列的子集，或者是多个基列的子集，或者是多个基表的不同列。表的不同列。n简化用户权限的管理：通过视图，

3、用户只能查询或修简化用户权限的管理：通过视图，用户只能查询或修改他们所得到的数据，而基表对用户来讲是不可见的。改他们所得到的数据，而基表对用户来讲是不可见的。从而在一定程序提高了基表的安全性。从而在一定程序提高了基表的安全性。视图的缺点视图的缺点n降低系统性能：降低系统性能：SQL SERVER 2000把对视图的查询把对视图的查询转化为对基表的查询。即使是对视图的简单查询，若转化为对基表的查询。即使是对视图的简单查询，若视图的定义是复杂的（比如来自多张表），则系统转视图的定义是复杂的（比如来自多张表），则系统转换的结果也是复杂，执行也将花费一定的时间。换的结果也是复杂，执行也将花费一定的时间

4、。n修改限制：对视图数据的修改，系统将它转化为对基修改限制：对视图数据的修改，系统将它转化为对基表的修改，对于复杂的视图，则可能是不可修改的。表的修改，对于复杂的视图，则可能是不可修改的。创建创建视图视图 n创建视图创建视图 n在企业管理器中创建视图在企业管理器中创建视图 n通过视图向导创建视图通过视图向导创建视图n 使用使用CREATE VIEW语句创建视图语句创建视图 5.2在企业管理器中创建视图在企业管理器中创建视图 n在在SQL Server Enterprise Manager中创建视图中创建视图 n第第1步步 打开打开SQL Server Enterprise Manager，在，

5、在数据库数据库XSCJ上单击鼠标右键，在弹出的快捷菜上单击鼠标右键，在弹出的快捷菜单上选择单上选择“新建新建”“视图视图.”n第第2步步 在所出现的窗口的第一个子窗口中单击鼠在所出现的窗口的第一个子窗口中单击鼠标右键，将弹出一个标右键，将弹出一个“增加表增加表”快捷菜单快捷菜单 n在在SQL Server Enterprise Manager中创建视图中创建视图 n第第3步步 在在“增加表增加表”快捷菜单中选择快捷菜单中选择“添添加表加表”功能项，将出现所示的功能项，将出现所示的“添加表添加表”对话框。对话框。 n第第4步步 在在“添加表添加表”对话框中选择与视图对话框中选择与视图相关联的表、

6、视图或函数，可以使用相关联的表、视图或函数，可以使用Ctrl或或Shift键进行多选，选择完毕后，单击键进行多选，选择完毕后，单击“添加添加”按钮按钮 5.2在企业管理器中创建视图在企业管理器中创建视图 n在在SQL Server Enterprise Manager中创建视图中创建视图 n第第5步步 在窗口的第在窗口的第2个子窗口中选择创建视图所个子窗口中选择创建视图所需的字段，可以指定列的别名、排序方式和规则需的字段，可以指定列的别名、排序方式和规则（本例指定（本例指定“专业名专业名”字段的规则为字段的规则为“计算机计算机”）等等 n第第6步步 上一步完成后，单击保存按钮，出现保存上一步完

7、成后，单击保存按钮，出现保存视图对话框，在其中输入视图名，并单击视图对话框，在其中输入视图名，并单击“确定确定“按钮，便完成了视图的创建按钮，便完成了视图的创建 5.2在企业管理器中创建视图在企业管理器中创建视图 5.3 通过视图向导创建视图通过视图向导创建视图n通过视图向导创建视图通过视图向导创建视图n第第1步打开步打开SQL Server Enterprise Manager，选择选择“工具工具”菜单中的菜单中的“向导向导”功能项，选功能项，选择向导对话框。择向导对话框。n第第2步步 在选择向导对话框中展开在选择向导对话框中展开“数据库数据库”，选择选择“创建视图向导创建视图向导”功能项，

8、单击功能项，单击“确确定定”，将出欢迎使用向导界面。，将出欢迎使用向导界面。n通过视图向导创建视图通过视图向导创建视图n第第3步步 在欢迎使用向导界面中单击在欢迎使用向导界面中单击“下一下一步步”，选择数据库名，单击，选择数据库名，单击“下一步下一步”。n第第4步步 在选择表对话框中选择与视图相关联在选择表对话框中选择与视图相关联的表，单击的表，单击“下一步下一步”，出现选择列对话框。，出现选择列对话框。5.3 通过视图向导创建视图通过视图向导创建视图n通过视图向导创建视图通过视图向导创建视图n第第5步步 在图选择列对话框中选择视图所需要的列，在图选择列对话框中选择视图所需要的列，单击单击“下

9、一步下一步”，出现视图限制对话框。，出现视图限制对话框。n第第6步步 在视图限制对话框中输入对视图的限制条件，在视图限制对话框中输入对视图的限制条件，本例为：本例为：WHERE 专业名专业名=计算机计算机，单击，单击“下一下一步步”，出现输入视图名对话框。，出现输入视图名对话框。n第第7步步 在输入视图名对话框中输入视图名，单击在输入视图名对话框中输入视图名，单击“下一步下一步”，出现视图创建完成对话框，单击，出现视图创建完成对话框，单击“完完成成”，则视图即创建完毕。，则视图即创建完毕。5.3 通过视图向导创建视图通过视图向导创建视图5.4 使用使用CREATE VIEW语句创建视图语句创建

10、视图n 使用使用CREATE VIEW语句创建视图语句创建视图 n用该语句创建视图用该语句创建视图CS_XS，其表示形式为：，其表示形式为： CREATE VIEW CS_XS AS SELECT * FROM XS WHERE 专业名专业名=计算机计算机 GO视图创建的语法视图创建的语法n语法：语法：CREATE VIEW . . view_name ( column ,.n ) WITH ,.n AS select_statement WITH CHECK OPTION := ENCRYPTION | SCHEMABINDING | VIEW_METADATA 语法说明语法说明1nColu

11、mn：是视图中的列名。只有在下列情况下，才是视图中的列名。只有在下列情况下，才必须命名必须命名 CREATE VIEW 中的列：中的列：n当当列是从算术表达式、函数或常量派生列是从算术表达式、函数或常量派生的，两个或更的，两个或更多的列可能会具有相同的名称（通常是因为联接），多的列可能会具有相同的名称（通常是因为联接），视图中的某列被赋予了不同于派生来源列的名称。还视图中的某列被赋予了不同于派生来源列的名称。还可以在可以在 SELECT 语句中指派列名。语句中指派列名。select_statement：是定义视图的是定义视图的 SELECT 语句。语句。该语句可以使用多个表或其它视图。在该语句

12、可以使用多个表或其它视图。在 select_statement 中可以使用函数对基表进行统计中可以使用函数对基表进行统计汇总。汇总。select_statement 可使用多个由可使用多个由 UNION 或或 UNION ALL 分隔的分隔的 SELECT 语句。语句。语法说明语法说明2nCREATE VIEW 语句不能：语句不能： 1.包含包含 COMPUTE 或或 COMPUTE BY 子句。子句。 2.包含包含 ORDER BY 子句，除非在子句，除非在 SELECT 语语句的选择列表中也有一个句的选择列表中也有一个 TOP 子句。子句。 3.包含包含 INTO 关键字。关键字。 4.引

13、用临时表或表变量。引用临时表或表变量。语法说明语法说明3n不能将规则或不能将规则或default定义关联于视图定义关联于视图 nWITH CHECK OPTION：强制视图上执行的所有数：强制视图上执行的所有数据修改语句都必须符合由据修改语句都必须符合由 select_statement 设置的准则。设置的准则。通过视图修改行时，通过视图修改行时，WITH CHECK OPTION 可确保可确保提交修改后，仍可通过视图看到修改的数据。提交修改后，仍可通过视图看到修改的数据。nWITH ENCRYPTION：表示：表示 SQL Server 加密包含加密包含 CREATE VIEW 语句文本的系

14、统表列。使用语句文本的系统表列。使用 WITH ENCRYPTION 可防止将视图作为可防止将视图作为 SQL Server 复制的复制的一部分发布。一部分发布。注意：注意：n创建视图时，视图的名称存储在创建视图时，视图的名称存储在 sysobjects 表中。有关视图中所定义的列的信息添加到表中。有关视图中所定义的列的信息添加到 syscolumns 表中，而有关视图相关性的信息表中，而有关视图相关性的信息添加到添加到 sysdepends 表中。另外，表中。另外，CREATE VIEW 语句的文本添加到语句的文本添加到 syscomments 表表中。中。nCreate view 语句不能

15、在一个批中与其他语句不能在一个批中与其他transact-sql 语句一起使用。语句一起使用。【例例】创建】创建CS_KC视图，包括计算机专业各学视图，包括计算机专业各学生的学号、其选修的课程号及成绩。要保证生的学号、其选修的课程号及成绩。要保证对该视图的修改都要符合专业名为计算机这对该视图的修改都要符合专业名为计算机这个条件。个条件。CREATE VIEW CS_KC WITH ENCRYPTION AS SELECT XS.学号学号,课程号课程号,成绩成绩 FROM XS,XS_KC WHERE XS.学号学号 = XS_KC.学号学号 AND 专业名专业名 = 计计算机算机WITH CH

16、ECK OPTION5.4 使用使用CREATE VIEW语句创建视图语句创建视图【例例】创建计算机专业学生的平均成绩视图】创建计算机专业学生的平均成绩视图CS_KC_AVG，包括学号（在视图中列名为，包括学号（在视图中列名为num）和平均成绩（在视图中列名为和平均成绩（在视图中列名为score_avg）。）。 CREATE VIEW CS_KC_AVG(num,score_avg) AS SELECT 学号学号,AVG(成绩成绩) FROM XS_KC GROUP BY 学号学号5.4 使用使用CREATE VIEW语句创建视图语句创建视图 视图定义后，就可以如同查询基本表那样视图定义后，就

17、可以如同查询基本表那样对视图进行查询对视图进行查询,再查找再查找80分以上的同学分以上的同学5.4 使用使用CREATE VIEW语句创建视图语句创建视图Select * from xs_kc_avg where score_avg=805.6 更新视图更新视图 n 可更新视图可更新视图 n一个可更新视图可以是以下情形之一：一个可更新视图可以是以下情形之一：n满足以下条件的视图：满足以下条件的视图：n 创建视图的创建视图的SELECT语句中没有聚合函数，且语句中没有聚合函数，且没 有没 有 TO P 、 G R O U P B Y 、 U N I O N 子 句 及子 句 及DISTINCT关

18、键字；关键字；n 创建视图的创建视图的SELECT语句中不包含从基本表列语句中不包含从基本表列通过计算所得的列；通过计算所得的列；n 创建视图的创建视图的SELECT语句的语句的FROM子句中至少子句中至少要包含一个基本表。要包含一个基本表。n插入数据插入数据 【例例】向】向CS_XS视图中插入一条记录：视图中插入一条记录：( 001115, 刘明仪刘明仪, 计算机计算机, 1, 1978-3-2, 50 , NULL) INSERT INTO CS_XS VALUES(001115, 刘明仪刘明仪,计算机计算机,1,1978-3-2,50,NULL)5.6 更新视图更新视图 n修改数据修改数

19、据 【例例】将】将CS_XS视图中所有学生的总学分增加视图中所有学生的总学分增加8。 UPDATE CS_XS SET 总学分总学分 = 总学分总学分+ 8 【例例】将】将CS_KC视图中学号为视图中学号为001101的学生的的学生的101课程成课程成绩改为绩改为90。 UPDATE CS_KC SET 成绩成绩=90 WHERE 学号学号=001101 AND 课程号课程号=1015.6 更新视图更新视图 注：对于依赖于多个基本表的视图注：对于依赖于多个基本表的视图(不包括分区视图不包括分区视图)，不能使用不能使用DELETE语句语句删除数据删除数据使用使用delete语句通过视图删除基本表

20、的数据语句通过视图删除基本表的数据删除删除cs_xs中女同学的记录中女同学的记录 delete from cs_xs where 性别性别=05.6 更新视图更新视图 5.7 修改视图的定义修改视图的定义 n通过通过SQL Server Enterprise Manager修修改视图改视图 n 使用使用ALTER VIEW语句修改视图语句修改视图nALTER VIEW语句的语法格式为：语句的语法格式为：ALTER VIEW . . view_name ( column_name ,n ) WITH ,n AS select_statement WITH CHECK OPTION 5.7 修改视

21、图的定义修改视图的定义 【例例】将将CS_XS视图修改为只包含计算机专业学生视图修改为只包含计算机专业学生的学号、姓名和总学分。的学号、姓名和总学分。USE XSCJGOALTER VIEW CS_XSASSELECT 学号学号,姓名姓名,总学分总学分FROM XSWHERE 专业名专业名 = 计算机计算机GO5.7 修改视图的定义修改视图的定义 n删除视图同样也可以通过企业管理器界删除视图同样也可以通过企业管理器界面和面和T-SQL语句两种方式来实现。语句两种方式来实现。 n删除视图的删除视图的T-SQL语句是语句是DROP VIEW，格，格式为：式为： DROP VIEW view , n

22、 其中其中view是视图名，使用是视图名，使用DROP VIEW一次一次可删除多个视图。例如：可删除多个视图。例如： DROP VIEW CS_KC 将删除视图将删除视图CS_KC。 5.8 删除视图删除视图 5.9索引索引索引：表中数据和相应存索引：表中数据和相应存储位置的列表。建立索引储位置的列表。建立索引可以大大提高系统的性能。可以大大提高系统的性能。5.9. 1 索引的分类索引的分类 n聚集索引：聚集索引：表中各行的物理顺序与索引表中各行的物理顺序与索引键值的逻辑（索引）顺序相同。表只能包含键值的逻辑（索引）顺序相同。表只能包含一个聚集索引。一个聚集索引。n聚集索引通常可加快聚集索引通

23、常可加快 UPDATE 和和 DELETE 操作的速度，因为这两个操作需要读取大量操作的速度，因为这两个操作需要读取大量的数据。创建或修改聚集索引可能要花很长的数据。创建或修改聚集索引可能要花很长时间，因为执行这两个操作时要在磁盘上对时间，因为执行这两个操作时要在磁盘上对表的行进行重组。表的行进行重组。5.9. 1 索引的分类索引的分类 n聚集索聚集索引引应用于：应用于： n包含数量有限的唯一值的列。包含数量有限的唯一值的列。n使用下列运算符返回一个范围值的查询：使用下列运算符返回一个范围值的查询：BETWEEN、=、 和和 =0 and 成绩成绩=0 AND 总学分总学分=10)NULL,

24、/* 通过通过CHECK子句定义约束条件子句定义约束条件 */备注备注 text NULL)GOn规则对象的定义、使规则对象的定义、使用与删除用与删除 n利用企业管理器定义利用企业管理器定义规则对象并绑定到自规则对象并绑定到自定义类型或列定义类型或列第第1步步 进入企业管理进入企业管理器，展开控制台目录器，展开控制台目录树中的树中的XSCJ数据库数据库图标，选中图标，选中“规则规则”图标右击，出现如图图标右击，出现如图所示的快捷菜单；所示的快捷菜单； 5.10.2域完整性的实现域完整性的实现 第第2步步 选择选择“新建规新建规则则”，进入规则创建窗，进入规则创建窗口，如图所示，在窗口口，如图所

25、示，在窗口中输入规则名及规则表中输入规则名及规则表达式，本例的规则名为达式，本例的规则名为kc_rule，规则表，规则表 达式为：达式为：num like 1-50-90-9，其中其中num为任意为任意输入的输入的局部变量，但必局部变量，但必须以须以开头。开头。 5.10.2域完整性的实现域完整性的实现 n利用企业管理器定利用企业管理器定义规则对象并绑定义规则对象并绑定到自定义类型或列到自定义类型或列第第3步步 选择选择“确定确定”按钮，则新建规则按钮，则新建规则创建成功，出现如创建成功，出现如图的图标。图的图标。 5.10.2域完整性的实域完整性的实现现 第第4步步 在规则图标下选中刚定义的

26、规在规则图标下选中刚定义的规则对象则对象kc_rule对象双击，出现如图对象双击，出现如图6.13所示的规则属性界面；所示的规则属性界面；第第5步步 在规则属性窗口中有两个按钮：在规则属性窗口中有两个按钮：一个是一个是“绑定绑定UDT”，即绑定到用，即绑定到用户自定义类型；另一个按钮是户自定义类型；另一个按钮是“绑绑定列定列”，对于本例选中，对于本例选中“绑绑 定列定列”，进入如图进入如图6.14所示将规则对象绑定所示将规则对象绑定到列的界面；到列的界面；5.10.2域完整性的实现域完整性的实现 n利用企业管理器创建和删除利用企业管理器创建和删除PRIMARY KEY约束约束 n利用企业管理器

27、创建利用企业管理器创建PRIMARY KEY约束约束 第第1步步 选择选择XS表图标，右击，打开表图标，右击，打开XS表的表设计器，表的表设计器，进入如图进入如图6.16所示的表设计器界面。所示的表设计器界面。5.10.3实体完整性的实现实体完整性的实现 n第第2步步 选中选中“学号学号”对应的这一行，选择主键对应的这一行，选择主键图标，这样在图标，这样在“学号学号”对应的这一行前面，对应的这一行前面，将出现一主键图标。将出现一主键图标。5.10.3实体完整性的实现实体完整性的实现 n利用企业管理器删除利用企业管理器删除PRIMARY KEY约束约束第第2步步 选中选中XS表设计器中主键对应的

28、行，选表设计器中主键对应的行，选择工具栏的择工具栏的“设置主键图设置主键图 标标”，则取消了，则取消了原来定义的主键。原来定义的主键。5.10.3实体完整性的实现实体完整性的实现 n利用企业管理器创建和删除利用企业管理器创建和删除UNIQUE约束约束 n利用企业管理器创建利用企业管理器创建UNIQUE约束约束 如果要对如果要对XS表中的表中的“身份证号码身份证号码”列创建列创建UNIQUE约束，以保证该列取值的唯一性，约束，以保证该列取值的唯一性，可按以下步骤进行：可按以下步骤进行：5.10.3实体完整性的实现实体完整性的实现 n第第1步步 选择选择XS表图标，右击，打开表图标，右击，打开XS

29、表设计器，表设计器，在表设计器中右击，出现如图所示的快捷菜单。在表设计器中右击，出现如图所示的快捷菜单。5.10.3实体完整性的实现实体完整性的实现 第第2步步 选择选择“属性属性”菜单项，出现属性界面，菜单项，出现属性界面，在此界面中选择在此界面中选择“索引索引/键键”选项卡，如图所选项卡，如图所示。示。 5.10.3实体完整性的实现实体完整性的实现 第第3步步 在图在图6.19的属性界面中选择的属性界面中选择“新建新建”，输入新建索，输入新建索引的名字或使用系统缺省名，在引的名字或使用系统缺省名，在“列名列名”下拉表中选择下拉表中选择“身份证号码身份证号码”，并设置索引顺序，将，并设置索引

30、顺序，将“创建创建UNIQUE约束约束”单选按钮设置为选中状态，如图所示。单选按钮设置为选中状态，如图所示。 5.10.3实体完整性的实现实体完整性的实现 5.10.4参照完整性的实现参照完整性的实现 n利用企业管理器定义表间的参照关系利用企业管理器定义表间的参照关系 例如：要建立例如：要建立XS表与表与XS_KC表之间的参照完表之间的参照完整性，操作步骤如下：整性，操作步骤如下： 第第1步步 按照前面所介绍的方法定义主表的主按照前面所介绍的方法定义主表的主码（或唯一码）。在此，定义码（或唯一码）。在此，定义XS表中的学号字表中的学号字段为主码。段为主码。n第第2步步 选择企业管理器目录树中选择企业管理器目录树中XSCJ数据库数据库目录下的目录下的“关系图关系图”图标右击，出现如图图标右击，出现如图6.21所示的快捷菜单。所示的快捷菜单。5.10.4参