第7章视图与索引.ppt

《第7章视图与索引.ppt》由会员分享，可在线阅读，更多相关《第7章视图与索引.ppt（36页珍藏版）》请在三一文库上搜索。

1、2019/3/12,1/38,第7章 视图与索引,软件工程系 刘金岭,2019/3/12,2/31,7.1 视图,主要内容 视图的基本概念 创建视图 修改视图 删除视图 使用视图,2019/3/12,3/31,视图的基本概念,视图是从一个或几个表导出来的表，它不是真实存在的基本表而是一张虚表，视图所对应的数据并不实际地以视图结构存储在数据库中，而是存储在视图所引用的表中。 视图的主要作用 体现在如下三个方面： (1) 简单性。被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。 (2) 安全性。通过视图用户只能查询和修改他们所能见到的数据。数据库中的其它数据则既看

2、不见也取不到。 (3) 逻辑数据独立性。视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，应用一定是建立在表上的。,2019/3/12,4/31,使用SSMS图形化方式 例7.1 利用例5.9教学管理数据库的三个基本表，创建信息系（IS）学生的成绩表视图V_IS。其结构为: V_IS(SNO,SNAME,CNAME,GRADE,SDEPT) 具体步骤如下： (1) 在“对象资源管理器”中展开“数据库”文件夹，并进一步展开“JXGL”文件夹。 (2) 右击“视图”选项，在弹出的快捷菜单中选择“新建视图”菜单项，进入视图设计界面。 (3) 在弹出的“添加表”对话框中”，可以选择创建视图所

3、需的表、视或者函数等。本例中选择了基本表S、SC和C。单击“添加”按钮，即可将这三个表添加到视图查询中。,创建视图 (1),2019/3/12,5/31,(4) 单击对话框中的“关闭”按钮，则返回到SQL Server Management Studio的视图设计界面。 在窗口右侧的“视图设计器”中包括以下4个区域： 关系图区域：以图形方式显示正在查询的表和其它表结构化对象，同时也显示它们之间的关联关系。 列条件区域：是一个类似于电子表格的网格，用户可以在其中指定视图的选项。 SQL区域：显示视图所要存储的查询语句。可以对设计器自动生成的SQL语句进行编辑，也可以输入自己的SQL语句。 结果区

4、域：显示最近执行的选择查询的结果。,创建视图 (2),2019/3/12,6/31,(5) 为视图选择包含的列。可以通过“关系图区域”、“列条件区域”或“SQL区域”的任何一个区域做出修改，另外两个区域都会自动更新以保持一致。 (6) 在“列条件区域”的“SDEPT”列的筛选器中写上筛选条件“=IS”。在SQL区域中就可以看到所生成相应的T-SQL语句。如图7.2所示。 (7) 单击工具栏上的“执行”按钮 ，在数据区域将显示包含在视图中的数据行。单击“保存”按钮，视图取名“V_IS”，即可保存视图。 使用T-SQL语句 其语句格式为： CREATE VIEW (, n ) AS WITH CH

5、ECK OPTION,创建视图 (3),表示对视图进行UPDATE，INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的子查询条件。,2019/3/12,7/31,例7.2 建立数学系（MA）学生的视图V_MA，并要求进行修改和插入操作时仍需保证该视图只有数学系的学生。 USE JXGL GO CREATE VIEW V_MA AS SELECT SNO,SNAME,AGE FROM S WHERE SDEPT=MA WITH CHECK OPTION GO 由于在定义V_MA视图时加上了WITH CHECK OPTION子句，以后对该视图进行插入、修改和删除操作时，RD

6、BMS会验证条件SDEPT=MA。,创建视图 (4),2019/3/12,8/31,例7.3 创建学生选修课程的门数和平均成绩的视图C_G，其中包含的属性列为(SNO,C_NUM, AVG_GRADE) USE JXGL GO CREATE VIEW C_G(SNO,C_NUM,AVG_GRADE) AS SELECT SNO,COUNT(CNO),AVG(GRADE) FROM SC WHERE GRADE IS NOT NULL GROUP BY SNO GO 组成视图的属性列名或者全部省略或者全部指定，如果省略了视图的各个属性列名，则隐含该视图由子查询中SELECT子句目标列中的诸项组成

7、。,创建视图 (5),2019/3/12,9/31,使用SSMS图形化方式 例7.4 修改例7.3创建的视图C_G，使之只查询计算机科学系（CS）的学生选修课程的门数和平均成绩。 具体步骤如下： (1) 在“对象资源管理器”中展开“数据库”文件夹，并进一步展开“JXGL”文件夹。 (2) 展开“视图”选项，右击要修改的视图，在弹出的快捷菜单中选择“设计”菜单项，打开视图设计对话框就可以修改视图的定义了。 (3) 本例中，一是需要添加表S，只需在关系图区域中的空白处右击，在弹出的快捷菜单中选择“添加表”菜单项；二是需要修改筛选条件，在“列条件区域”的“SDEPT”列的筛选器中写上筛选条件“=CS

8、”。在SQL区域中就可以看到所生成相应的T-SQL语句。如下图所示。,修改视图 (6),2019/3/12,10/31,使用T-SQL语句 T-SQL提供了ALTER VIEW语句修改视图，语句格式如下： ALTER VIEW (, n ) AS WITH CHECK OPTION 例7.5 修改例7.2中视图V_MA，并要求该视图只查询数学系（MA）的男学生。 USE JXGL GO ALTER VIEW V_MA AS SELECT SNO,SNAME,AGE FROM S WHERE SDEPT=MA AND SEX=M WITH CHECK OPTION GO,创建视图 (7),201

9、9/3/12,11/31,使用SSMS图形化方式 (1) 在“对象资源管理器”中展开“数据库”文件夹，并进一步展开视图所在数据库文件夹。 (2) 展开“视图”选项，右击要删除的视图，在弹出的快捷菜单中选择“删除”菜单项，进入“删除对象”窗口，单击“确定”按钮就可以删除视图。 使用T-SQL语句 T-SQL提供了DROP VIEW语句删除视图，语句格式如下： DROP VIEW ,删除视图,2019/3/12,12/31,数据查询 (1) 使用SSMS图形化方式 在“对象资源管理器”中展开“数据库”文件夹，并进一步展开视图所在数据库文件夹。 展开“视图”选项，右击要查询数据的视图，在弹出的快捷菜

10、单中选择“选择前1000行”菜单项，进入数据浏览窗口。 (2) 使用T-SQL语句 与表的数据查询一样，在查询窗口可以使用查询语句，格式如下： SELECT * FROM ,使用视图 (1),2019/3/12,13/31,数据修改 更新视图的数据，其实就是对基本表的更新。这是由于视图是不实际存储数据的虚表，对视图的更新最终要转换为对基本表的更新。对于视图数据的更新操作（INSERT、DELETE、UPDATA），有以下三条规则： 如果一个视图是从多个基本表使用连接操作导出的，那么不允许对这个视图执行更新操作。 如果在导出视图的过程中，使用了分组和统计函数操作，也不允许对这个视图执行更新操作。

11、 去掉同一个表的若干行或列，切保留了主键所得到的视图称为行列子集视图。行列子集视图是可以执行更新操作的。,使用视图 (2),2019/3/12,14/31,(1) 使用SSMS图形化方式 在“对象资源管理器”中展开“数据库”文件夹，并进一步展开视图所在数据库文件夹。 展开“视图”选项，右击要更新数据的视图，在弹出的快捷菜单中选择“编辑前200行”菜单项，进入数据更新窗口。 (2) 使用T-SQL语句 与表的数据更新一样，在查询窗口可以使用数据更新语句。 例7.6 在例7.2 建立的数学系（MA）学生的视图V_MA中，将学号为“S6”的学生姓名改为“马常友”。 USE JXGL GO UPDAT

12、E V_MA SET SNAME=马常友 WHERE SNO=S6 GO,使用视图 (3),转换为对基本表的更新语句为： USE JXGL GO UPDATE S SET SNAME=马常友 WHERE SNO=S6 AND SDEPT=MA GO,2019/3/12,15/31,在关系数据库中，有些视图是不可以更新的，其原因是这些视图的更新不能唯一地有意义地转换成对相应基本表的更新。 例如，前面例7.3中定义的视图C_GE是由学生选修课程的门数和平均成绩两个属性列组成的，其中平均成绩一项AVG_GRADE是由SC表中对元组分组后计算平均值得来的，如果想把视图C_G中学号为“S5”的学生的平均

13、成绩改成90分，SQL语句如下： USE JXGL GO UPDATE C_G SET AVG_GRADE=90 WHERE SNO=S5 GO 但这个对视图的更新是无法转换成对基本表SC的更新的，因为系统无法修改各科成绩，以使平均成绩成为90。所以C_G视图是不可更新的。,使用视图 (4),一般地，行列子集视图是可更新的。除行列子集视图外，还有些 视图理论上是可以更新的，但它们确切特征还是尚待研究的课题。还 有些视图从理论上就是不可以更新的。,2019/3/12,16/31,7.2 索引 主要内容 索引的基本概念 创建索引 管理索引,2019/3/12,17/31,索引的基本概念(1),索引

14、是对数据库表中一个或多个列的值进行排序的结构，其主要目的是提高SQL Server系统的性能，加快数据的查询速度和减少系统的响应时间。 索引表是与基本表关联的一种数据结构，它包含由基本表中的一列或多列生成的索引键和基本表中包含各个索引键的行所在的存储位置。不论基本表中是否按索引键有序，但索引中总是按索引键有序的。,2019/3/12,18/31,索引的优点 创建索引可以大大提高系统的性能。其优点主要表现在： (1) 通过创建唯一性索引。 (2) 可以大大加快数据的检索速度。 (3) 可以加速表和表之间的连接。 (4) 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

15、 (5) 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。,索引的基本概念(2),2019/3/12,19/31,索引的缺点 索引的存在也让系统付出一定的代价，主要表现在： (1) 创建索引和维护索引要耗费时间。 (2) 索引需要占用物理空间，每一个索引还要占用一定的物理空间。 (3) 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。 创建索引虽然可以提高查询速度，但是他需要牺牲一定的系统性能。因此，哪些列适合创建索引，哪些列不适合创建索引，需要进行详细的考察。,索引的基本概念(3),2019/3/12,20/31,索引的分类 (1)

16、 聚集索引和非聚集索引 聚集索引会对基本表进行物理排序，所以这种索引对查询非常有效，在每一张基本表中只能有一个聚集索引。当建立主键约束时，如果基本表中没有聚集索引，SQL Server会用主键列作聚集索引键。 (2) 唯一索引和非唯一索引 唯一索引确保在被索引的列中，所有数据都是唯一的，不包含重复的值。 (3) 简单索引和复合索引 只针对基本表的一列建立的索引，这种索引称为简单索引（single index）。针对多个列（最多包含16列）建立的索引称为复合索引或组合索引（composite index）。,索引的基本概念(4),2019/3/12,21/31,创建索引的基本原则 (1) 大量的

17、索引，会影响INSERT、UPDATE和DELETE语句的性能。 (2) 若基本表的数据量大，且对基本表的更新操作较少而查询操作较多时，可以创建多个索引来提高性能。 (3) 当视图包含统计函数、表连接或两者组合时，在视图上创建索引可以显著地提高性能。 (4) 可以对唯一列或非空列创建聚集索引。 (5) 每个表只能创建一个聚集索引。,创建索引(1),2019/3/12,22/31,使用SSMS图形化方式 例7.8 为教学管理数据库的学生表S的SNAME创建索引I_SNAME。 具体步骤如下： (1) 在“对象资源管理器”中，依次展开数据库“JXGL”、“表”。 (2) 选择要创建索引的表S，单击

18、该表左侧的“+”号，然后选择“索引”项，单击右键，在弹出的快捷菜单中选择“新建索引”菜单项，出现“新建索引”对话框。 (3) 在弹出的“新建索引”对话框中输入索引的名称I_SNAME，设置索引的类型，此例选为“非聚集”。 本例中并选择了“唯一”选项。如图所示。,创建索引(2),2019/3/12,23/31,(4) 在对话框中单击“添加”按钮，将弹出“选择列”对话框，如图所示。选择要添加到索引键的表列，本例中选择SNAME列。,创建索引(3),2019/3/12,24/31,(5) 单击“确定”按钮关闭该对话框，返回到“新建索引”对话框，在“索引键列”中的“排序顺序”下拉列表框中选择“升序”。

19、 (6) 在“新建索引”对话框中打开“选项”、“包含性列”、“存储“等选项页进行必要的设置后，单击“确定”按钮，即完成了创建非聚集索引I_SNAME的操作。,创建索引(4),2019/3/12,25/31,使用T-SQL语句 T-SQL提供了CREATE INDEX 语句创建索引，语句格式如下： CREATEUNIQUECLUSTERED|NONCLUSTEREDINDEX ON (ASC|DESC, n) WITH PAD_INDEX ,FILLFACTOR= ,IGNORE_DUP_KEY ,DROP_EXISTING UNIQUE：指定创建的索引为唯一索引。 CLUSTERED | NO

20、NCLUSTERED：用于指定创建的索引为聚集索引/非聚集索引。 ASC|DESC：用于指定索引列升序/降序，默认设置为ASC。,创建索引(5),2019/3/12,26/31,PAD_INDEX：指定索引填充。取值为ON|OFF，默认值为OFF。PAD_INDEX选项只用来连接FILLFACTOR，它指定在索引的中间级别页打开的自由空间特定的百分比。例如： WITH (PAD_INDEX=ON,FILLFACTOR=50) 在这个例子中，填充因子配置为50%，为新行保留50%的索引页自由空间。同时也启用了PAD_INDEX，因此中间索引页也将保留一半的自由空间。 FILLFACTOR：指定填

21、充因子的大小。使用FILLFACTOR是读与写之间的一个平衡操作。 IGNORE_DUP_KEY：当向唯一聚集索引或唯一非聚集索引中插入重复数据时，用于忽略重复值输入。此子句要与UNIQUE保留字同时使用。 DROP_EXISTING：指定应删除并重新创建同名的先前存在的聚集索引或非聚集索引。,创建索引(6),2019/3/12,27/31,例7.9 为例5.9教学管理数据库的课程表C的列CNAME创建名为I_CNAME的唯一索引。 USE JXGL GO CREATE UNIQUE INDEX I_CNAME ON C(CNAME) GO 例7.10 为选修课程表SC的CNO、GRADE列创

22、建名为I_CNO_GRADE的复合索引。其中CNO为升序，GRAD为降序。 USE JXGL GO CREATE INDEX I_CNO_GRADE ON SC(CNO ASC,GRADE DESC) GO,创建索引(7),2019/3/12,28/31,例7.11 为C表创建输入成批数据时忽略重复值的索引，索引名为I_CNAME_TNAME。填充因子取60。 USE JXGL GO CREATE UNIQUE NONCLUSTERED INDEX I_CNAME_TNAME ON C(CNAME ASC,TNAME ASC) WITH PAD_INDEX, FILLFACTOR=60, IG

23、NORE_DUP_KEY GO,创建索引(8),2019/3/12,29/31,索引需要定期的管理，以提高空间的利用率。 只有删除索引块当中所有索引行，索引块空间才会被释放。 在索引列上频繁执行UPDATE或INSERT操作也应当定期重建索引以提高空间利用率。 查看与修改索引 (1) 使用SSMS图形化方式 具体步骤如下： 在“对象资源管理器”中，依次展开“数据库”、“表”。 展开要查看索引的表的下属对象，选择“索引”对象。 单击主菜单“视图”的“对象资源管理器详细信息”菜单项，在工作界面的右边会列出该表的所有索引。如图所示。 如果要查看、修改索引的相关属性，在上图中选择相应的索引，按右键，在

24、弹出的快捷菜单中选择“属性”菜单项，弹出“索引属性”对话框，如图所示。 在“索引属性”对话框中的各个选项页中可以查看、修改索引的相关属性。,管理索引(1),2019/3/12,30/31,(2) 使用系统存储过程 使用sp_helpindex系统存储过程可以查看基本表中的所有索引信息。 EXEC sp_helpindex objname 例7.13 查看教学管理数据库的S表的索引。 EXEC sp_helpindex s 也可以使用系统存储等过程sp_rename更改索引的名称： EXEC sp_rename .,新名称 例7.14 将例7.9中的索引I_CNAME更名为I_C。 EXEC s

25、p_rename C.I_CNAME,I_C,管理索引(2),2019/3/12,31/31,删除索引 (1) 使用SSMS图形化方式 具体步骤如下： 在“对象资源管理器”中，依次展开“数据库”、“表”。 展开要查看索引的表的下属对象，选择“索引”对象。 单击要删除的索引对象，按右键，在弹出的快捷菜单中选择“删除”菜单项。 在弹出的“删除对象”对话框中，单击“确定”按钮即可完成删除操作。 (2) 利用T-SQL语句 DROP INDEX ., n 例7.15 删除索引I_C。 DROP INDEX C.I_C,管理索引(3),2019/3/12,32/31,维护索引 (1) 检查整理索引碎片

26、检查相关表中有无索引的碎片信息。 DBCC SHOWCONTIG() 使用DBCC INDEXDEFRAG整理索引碎片。它对索引的叶级进行碎片整理，以便使页的物理顺序与叶节点从左到右的逻辑顺序相匹配，从而提高索引扫描性能。 DBCC INDEXDEFRAG (,) 注意：如果碎片很小，则不需要重新生成或重新组织碎片，如果碎片小于30%，则可以选择重新组织索引，如果碎片大于30%，则可以重新生成索引。,管理索引(4),2019/3/12,33/31,(2) 重新组织索引 ALTER INDEX |ALL ON REBUILD WITH (,PAD_INDEX=ON|OFF ,FILLFACTOR

27、= ,SORT_IN_TEMPDB=ON|OFF) 其中，SORT_IN_TEMPDB = ON|OFF指定是否在 tempdb 中存储排序结果。 默认值为 OFF。,管理索引(5),2019/3/12,34/31,例7.16 对教学管理数据库S表的所有索引重建，并设定填充因子为60。 USE JXGL GO ALTER INDEX ALL ON S REBUILD WITH (FILLFACTOR = 60, SORT_IN_TEMPDB = ON ) GO 当创建或重新生成索引时，通过将 SORT_IN_TEMPDB 选项设置为ON，可以指定 SQL Server 数据库引擎使用 temp

28、db 来存储用于生成索引的中间排序结果。,管理索引(6),2019/3/12,35/31,(3) 维护索引统计信息 数据库选项AUTO_UPDATE_STATISTICS提供了统计信息自动更新功能，它的默认设置是ON。 数据库选项AUTO_UPDATE_STATISTICS_ASYNC提供了统计信息异步更新功能。当此项设置为ON时，查询不等待统计信息更新，即可进行编译。而过期的统计信息置于队列中，由后台进程中的工作线程来更新。 用户也可以通过执行UPDATE STATISTICS语句手动更新统计信息。 例7.17 以下语句开启数据库JXGL自动更新统计信息功能。 ALTER DATABASE JXGL SET AUTO_UPDATE_STATISTICS ON,管理索引(7),2019/3/12,36/31,本次课小结,重点内容 视图的基本概念 视图的创建、修改、删除 使用视图 索引的基本概念 管理索引 作业：1，4，5,