第10章视图和索引

《第10章视图和索引》由会员分享，可在线阅读，更多相关《第10章视图和索引（63页珍藏版）》请在装配图网上搜索。

1、2021/6/41数据库原理与应用教程数据库原理与应用教程第第10章章 视图和索引视图和索引2021/6/42第第10章章 视图和索引视图和索引o数据库的基本表是根据所有用户的需求按照数据库数据库的基本表是根据所有用户的需求按照数据库设计人员的观点设计的，并不一定符合用户的应用设计人员的观点设计的，并不一定符合用户的应用需求。需求。SQL Server可以根据各个用户的需求重新定可以根据各个用户的需求重新定义表的数据结构，这种数据结构就是视图。索引是义表的数据结构，这种数据结构就是视图。索引是以表列为基础的数据库对象，它保存着表中排序的以表列为基础的数据库对象，它保存着表中排序的索引列，并且记

2、录了索引列在数据表中的物理存储索引列，并且记录了索引列在数据表中的物理存储位置，实现了表中数据的逻辑排序。索引可以使数位置，实现了表中数据的逻辑排序。索引可以使数据库程序在最短的时间内找到所需要的数据，而不据库程序在最短的时间内找到所需要的数据，而不必查找整个数据库，这样可以节省时间，提高查找必查找整个数据库，这样可以节省时间，提高查找效率。效率。2021/6/43第第10章章 视图和索引视图和索引o在数据库的三级模式结构当中，索引对应的是内模在数据库的三级模式结构当中，索引对应的是内模式部分，基本表对应的是模式部分，而视图对应的式部分，基本表对应的是模式部分，而视图对应的是外模式部分。是外模

3、式部分。o本章主要介绍视图的基本概念，视图的创建、修改本章主要介绍视图的基本概念，视图的创建、修改和删除，利用视图实现对基本表中数据的操作；索和删除，利用视图实现对基本表中数据的操作；索引的基本概念，索引的分类以及创建、修改和删除引的基本概念，索引的分类以及创建、修改和删除索引等操作。索引等操作。2021/6/4410.1 视图视图o视图视图(View)是关系数据库系统提供给用户以多种是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制，在用户看来，角度观察数据库中数据的重要机制，在用户看来，视图是通过不同路径去看一个实际表，就像一个视图是通过不同路径去看一个实际表，就像一个窗口，我

4、们通过窗口去看外面的高楼，可以看到窗口，我们通过窗口去看外面的高楼，可以看到高楼的不同部分，而透过视图可以看到数据库中高楼的不同部分，而透过视图可以看到数据库中自己感兴趣的内容。自己感兴趣的内容。2021/6/4510.1.1 视图概述视图概述o视图作为一种数据库对象，为用户提供了一个可视图作为一种数据库对象，为用户提供了一个可以检索数据表中数据的方式。视图是一个虚表，以检索数据表中数据的方式。视图是一个虚表，可以视为另一种形式的表，是从一个或多个表中可以视为另一种形式的表，是从一个或多个表中使用使用SELECT语句导出的虚表，那些用来导出视语句导出的虚表，那些用来导出视图的表称为基本表。图的

5、表称为基本表。2021/6/4610.1.1 视图概述视图概述o用户通过视图来浏览数据表中感兴趣的部分或全部用户通过视图来浏览数据表中感兴趣的部分或全部数据，而数据的物理存储位置仍然在基本表中。所数据，而数据的物理存储位置仍然在基本表中。所以视图并不是以一组数据的形式存储在数据库中，以视图并不是以一组数据的形式存储在数据库中，数据库中只存储视图的定义，而不存储视图对应的数据库中只存储视图的定义，而不存储视图对应的数据，这些数据仍存储在导出视图的基本表中，视数据，这些数据仍存储在导出视图的基本表中，视图实际上是一个查询结果。当基本表中的数据发生图实际上是一个查询结果。当基本表中的数据发生变化时，

6、从视图中查询出来的数据也随之改变。变化时，从视图中查询出来的数据也随之改变。o 使用视图可以集中、简化和定制用户的数据库显使用视图可以集中、简化和定制用户的数据库显示，用户可以通过视图来访问数据，而不必直接去示，用户可以通过视图来访问数据，而不必直接去访问该视图的基本表。访问该视图的基本表。2021/6/471.视图的优点视图的优点o(1)为用户集中数据，简化用户的数据查询和处理。使为用户集中数据，简化用户的数据查询和处理。使得分散在多个表中的数据，通过视图定义在一起，屏蔽得分散在多个表中的数据，通过视图定义在一起，屏蔽了数据库的复杂性，用户不必输入复杂的查询语句，只了数据库的复杂性，用户不必

7、输入复杂的查询语句，只需针对此视图做简单的查询即可。需针对此视图做简单的查询即可。o(2)保证数据的逻辑独立性。对于视图的操作，例如，保证数据的逻辑独立性。对于视图的操作，例如，查询只依赖于视图的定义，当构成视图的基本表需要修查询只依赖于视图的定义，当构成视图的基本表需要修改时，只需要修改视图定义中的子查询部分，而基于视改时，只需要修改视图定义中的子查询部分，而基于视图的查询不用改变。图的查询不用改变。o(3)重新定制数据，使得数据便于共享。重新定制数据，使得数据便于共享。o(4)数据保密。对不同的用户定义不同的视图，使用户数据保密。对不同的用户定义不同的视图，使用户只能看到与自己有关的数据。

8、同时简化了用户权限的管只能看到与自己有关的数据。同时简化了用户权限的管理，增加了安全性。理，增加了安全性。2021/6/482.视图的分类视图的分类oSQL Server 2005中，视图可以分为标准视图、索引视图和分区视中，视图可以分为标准视图、索引视图和分区视图。图。o（1）标准视图）标准视图o标准视图组合了一个或多个表中的数据，可以获得使用视图的大多标准视图组合了一个或多个表中的数据，可以获得使用视图的大多数好处，可以实现对数据库的查询、修改和删除等基本操作。数好处，可以实现对数据库的查询、修改和删除等基本操作。o（2）索引视图）索引视图o索引视图是被具体化了的视图，它已经过计算并存储。

9、可以为视图索引视图是被具体化了的视图，它已经过计算并存储。可以为视图创建索引，即对视图创建一个唯一的聚集索引。索引视图可以显著创建索引，即对视图创建一个唯一的聚集索引。索引视图可以显著提高某些类型查询的性能。索引视图尤其适于聚合许多行的查询，提高某些类型查询的性能。索引视图尤其适于聚合许多行的查询，但不太适合于经常更新的基本数据集。但不太适合于经常更新的基本数据集。o（3）分区视图）分区视图o分区视图在一台或多台服务器间水平连接一组成员中的分区数据。分区视图在一台或多台服务器间水平连接一组成员中的分区数据。这样，数据看上去如同来自于一个表。这样，数据看上去如同来自于一个表。2021/6/491

10、0.1.2 创建视图创建视图o要使用视图，首先必须创建视图。视图在数据库中是作为一要使用视图，首先必须创建视图。视图在数据库中是作为一个独立的对象存储的，必须遵循以下原则。个独立的对象存储的，必须遵循以下原则。o(1)只能在当前数据库中创建视图。但是，如果使用分布式查只能在当前数据库中创建视图。但是，如果使用分布式查询定义视图，则新视图所引用的表和视图可以存在于其他数询定义视图，则新视图所引用的表和视图可以存在于其他数据库中，甚至其他服务器上。据库中，甚至其他服务器上。o(2)视图名称必须遵循标识符的规则，且对每个用户必须唯一。视图名称必须遵循标识符的规则，且对每个用户必须唯一。此外，该名称不

11、得与该用户拥有的任何表的名称相同。此外，该名称不得与该用户拥有的任何表的名称相同。o(3)用户可以在其他视图之上建立视图。用户可以在其他视图之上建立视图。o(4)如果视图中的某一列是一个算术表达式、内置函数或常量如果视图中的某一列是一个算术表达式、内置函数或常量派生而来，而且视图中两个或者更多的不同列拥有一个相同派生而来，而且视图中两个或者更多的不同列拥有一个相同的名字的名字(这种情况通常是因为在视图的定义中有一个连接，而这种情况通常是因为在视图的定义中有一个连接，而且这两个或者多个来自不同表的列拥有相同的名字且这两个或者多个来自不同表的列拥有相同的名字)，此时，此时，用户需要为视图的每一列指

12、定特定的名称。用户需要为视图的每一列指定特定的名称。2021/6/41010.1.2 创建视图创建视图o(5)定义视图的查询不可以包含定义视图的查询不可以包含ORDER BY、COMPUTE或或COMPUTE BY子句或子句或INTO关键字。关键字。o(6)不能在视图上定义全文索引定义。不能在视图上定义全文索引定义。o(7)不能创建临时视图，也不能在临时表上创建视图。不能创建临时视图，也不能在临时表上创建视图。o(8)不能对视图执行全文查询，但是如果查询所引用的表支持不能对视图执行全文查询，但是如果查询所引用的表支持全文索引，就可以在视图定义中包含全文查询。全文索引，就可以在视图定义中包含全文

13、查询。o(9)不能将规则或者不能将规则或者DEFAULT定义关联于视图。定义关联于视图。2021/6/41110.1.2 创建视图创建视图o在在SQL Server 2005中创建视图主要有两种方式：中创建视图主要有两种方式：o一种方式是在一种方式是在SQL Server Management Studio中使用中使用向导创建视图；另一种方式是通过在查询窗口中执向导创建视图；另一种方式是通过在查询窗口中执行行T-SQL语句创建视图。语句创建视图。2021/6/41210.1.2 创建视图创建视图o1在在SQL Server Management Studio中创建视图中创建视图o在在SQL S

14、erver Management Studio使用向导创建视图，是一使用向导创建视图，是一种图形界面环境下最快捷的创建方式，其步骤如下：种图形界面环境下最快捷的创建方式，其步骤如下：o(1)在在“对象资源管理器对象资源管理器”中展开要创建视图的数据库如中展开要创建视图的数据库如“teaching”，展开，展开“视图视图”选项，可以看到视图列表中系统选项，可以看到视图列表中系统自动为数据库创建的系统视图。右键单击自动为数据库创建的系统视图。右键单击“视图视图”选项，选选项，选择择“新建视图新建视图”菜单，打开菜单，打开“添加表添加表”对话框，在此对话框对话框，在此对话框中，可以选择表、视图或者函

15、数，然后单击中，可以选择表、视图或者函数，然后单击“添加添加”按钮，按钮，就可以将其添加到视图查询中。就可以将其添加到视图查询中。2021/6/41310.1.2 创建视图创建视图o(2)以创建学生表中所有以创建学生表中所有“男男”生信息的视图为例。选择生信息的视图为例。选择“student”表后，单击表后，单击“添加添加”按钮，单击按钮，单击“关闭关闭”按钮，按钮，返回返回“新建视图新建视图”对话框。对话框。o(3)在对话框上半部分，可看到添加进来的在对话框上半部分，可看到添加进来的“student”表，表，选择视图所用的列；在对话框中间网格窗格部分，可看选择视图所用的列；在对话框中间网格窗

16、格部分，可看到在上半部分的复选框中所选择的对应表的列，在到在上半部分的复选框中所选择的对应表的列，在“ssex”列的筛选器中写出筛选条件列的筛选器中写出筛选条件“=男男”；在对话；在对话框下半部分，可看到系统同时生成的框下半部分，可看到系统同时生成的T-SQL语句。然后，语句。然后，单击工具栏上的单击工具栏上的“保存保存”按钮，视图取名为按钮，视图取名为“male_view”。2021/6/41410.1.2 创建视图创建视图o2使用使用T-SQL语句创建视图语句创建视图oSQL Server 2005提供了提供了CREATE VIEW语句创建视语句创建视图，语法格式如下：图，语法格式如下：o

17、CREATE VIEW schema_name.view_name (column_name,.n )owith,noAS select_statementoWITH CHECK OPTION2021/6/41510.1.2 创建视图创建视图o【例【例10-1】创建】创建“s_c_sc”视图，包括视图，包括“计算机计算机”专专业的学生的学号、姓名，和他们选修的课程号、课业的学生的学号、姓名，和他们选修的课程号、课程名和成绩。程名和成绩。oUSE teachingoGOoCREATE VIEW s_c_scoASoSELECT student.sno,sname,o,cname,scoreoFR

18、OM student,sc,course WHERE student.sno=sc.sno oAND o=o AND specialty=计算机计算机2021/6/41610.1.2 创建视图创建视图o【例【例10-2】创建】创建“inve_count”库存统计视图，求每种商品的库存统计视图，求每种商品的总库存量，要求包括商品编号和商品名称。总库存量，要求包括商品编号和商品名称。oUSE inventoryoGOoCREATE VIEW inve_countoASoSELECT goods.gno,gname,SUM(number)AS snumberoFROM goods,inventoWH

19、ERE goods.gno=invent.gnooGROUP BY goods.gno,gnameoGO2021/6/41710.1.3 修改视图修改视图o1在在SQL Server Management Studio中修改视图中修改视图o2使用使用T-SQL语句修改视图语句修改视图oT-SQL提供了提供了ALTER VIEW语句修改视图，语法格语句修改视图，语法格式如下：式如下：oALTER VIEW schema_name.view_nameo(column_name,.n )with noAS select_statemento WITH CHECK OPTION 2021/6/4181

20、0.1.3 修改视图修改视图o【例【例10-3】修改】修改“inve_count”视图，求每种商品的总库存数视图，求每种商品的总库存数量和所在仓库的个数，要求包括商品编号和商品名称。量和所在仓库的个数，要求包括商品编号和商品名称。oUSE inventoryoGO oALTER VIEW inve_countoASoSELECT goods.gno,gname,SUM(number)AS snumber,COUNT(stno)AS storenumoFROM goods,inventoWHERE goods.gno=invent.gnooGROUP BY goods.gno,gnameoGO2

21、021/6/41910.1.3 修改视图修改视图o【例【例10-4】在视图上创建视图：创建】在视图上创建视图：创建“goodscount”商品统计商品统计视图，求每种商品的总库存数量和和总价值，要求包括商品视图，求每种商品的总库存数量和和总价值，要求包括商品编号和商品名称。编号和商品名称。oUSE inventoryoGOoCREATE VIEW goodscountoASoSELECT goods.gno,goods.gname,snumber,snumber*price as sumpriceoFROM goods,inve_countoWHERE goods.gno=inve_count

22、.gnooGO2021/6/42010.1.4 使用视图使用视图o视图创建完毕，就可以如同查询基本表一样通过视视图创建完毕，就可以如同查询基本表一样通过视图查询所需要的数据，而且有些查询需求的数据直图查询所需要的数据，而且有些查询需求的数据直接从视图中获取比从基表中获取数据要简单，也可接从视图中获取比从基表中获取数据要简单，也可以通过视图修改基表中的数据。以通过视图修改基表中的数据。2021/6/42110.1.4 使用视图使用视图o1使用视图进行数据查询使用视图进行数据查询o可以在可以在SQL Server Management Studio中选中要查询的中选中要查询的视图并打开，浏览该视图

23、的数据；也可以在查询窗口中视图并打开，浏览该视图的数据；也可以在查询窗口中执行执行T-SQL语句查询视图。语句查询视图。o例如，要查询各种商品的库存统计信息，就可以在例如，要查询各种商品的库存统计信息，就可以在SQL Server Management Studio中右键单击中右键单击“inve_count”视视图，选择图，选择“打开视图打开视图”选项，即可浏览各种商品的库存选项，即可浏览各种商品的库存统计信息。统计信息。o也可以在查询窗口中执行如下也可以在查询窗口中执行如下T-SQL语句：语句：oSELECT*FROM inve_count 2021/6/42210.1.4 使用视图使用视图

24、o【例【例10-5】在查询窗口中查询】在查询窗口中查询s_c_sc视图，统计视图，统计“C+语言语言”课程的总分和平均分。课程的总分和平均分。oUSE teachingoSELECT sumscore=SUM(score),avgscore=AVG(score)FROM s_c_scoWHERE cname=C+语言语言o【例【例10-6】查询】查询“inve_count”视图中视图中“冰箱冰箱”的商的商品统计信息。品统计信息。oUSE inventoryoSELECT*FROM inve_countoWHERE gname=冰箱冰箱2021/6/42310.1.4 使用视图使用视图o2使用视

25、图修改基本表中数据使用视图修改基本表中数据o 修改视图的数据，其实就是对基本表进行修改，修改视图的数据，其实就是对基本表进行修改，真正插入数据的地方是基本表，而不是视图。同样真正插入数据的地方是基本表，而不是视图。同样使用使用INSERT、UPDATE、DELETE语句来完成。但语句来完成。但是在对视图进行修改的时候也要注意一些事项，并是在对视图进行修改的时候也要注意一些事项，并不是所有的视图都可以更新，只有对满足以下可更不是所有的视图都可以更新，只有对满足以下可更新条件的视图才能进行更新。新条件的视图才能进行更新。2021/6/42410.1.4 使用视图使用视图o(1)任何通过视图的数据修

26、改（包括任何通过视图的数据修改（包括UPDATE、INSERT和和DELETE语句）都只能引用一个基本表的列。语句）都只能引用一个基本表的列。o如果视图数据为一个表的行、列子集，则此视图可更新如果视图数据为一个表的行、列子集，则此视图可更新（包括（包括UPDATE、INSERT和和DELETE语句）；但如果视图中语句）；但如果视图中没有包含表中某个不允许取空值又没有默认值约束的列，则没有包含表中某个不允许取空值又没有默认值约束的列，则不能利用视图插入数据。不能利用视图插入数据。o如果视图所依赖的基本表有多个时，完全不能向该视图添如果视图所依赖的基本表有多个时，完全不能向该视图添加（加（INSE

27、RT）数据。）数据。o若视图依赖于多个基本表，那么一次修改只能修改若视图依赖于多个基本表，那么一次修改只能修改（UPDATE）一个基本表中的数据。）一个基本表中的数据。o若视图依赖于多个基本表，那么不能通过视图删除若视图依赖于多个基本表，那么不能通过视图删除（DELETE）数据。）数据。2021/6/42510.1.4 使用视图使用视图o(2)视图中被修改的列必须直接引用表列中的基础数视图中被修改的列必须直接引用表列中的基础数据。据。o不能是通过任何其他方式对这些列进行派生而来的不能是通过任何其他方式对这些列进行派生而来的数据，比如通过聚合函数、计算（如表达式计算）、数据，比如通过聚合函数、计

28、算（如表达式计算）、集合运算等。集合运算等。o(3)被修改的列不应是在创建视图时受被修改的列不应是在创建视图时受GROUP BY、HAVING、DISTINCT或或TOP子句影响的。子句影响的。o注意：通常有可能插入并不满足视图查询的注意：通常有可能插入并不满足视图查询的WHERE子句条件中的一行。为了限制此操作，可以在创建子句条件中的一行。为了限制此操作，可以在创建视图时使用视图时使用WITH CHECK OPTION选项。选项。2021/6/42610.1.4 使用视图使用视图o【例【例10-7】通过】通过“male_view”视图向视图向“student”表中插入一个表中插入一个“男男”

29、生。生。oINSERT INTO male_view VALUES(1501005,张三张三,男男,19)o如果通过如果通过“male_view”视图向视图向“student”表中插入一个表中插入一个“女女”生，也可以完成；如果不希望用户通过生，也可以完成；如果不希望用户通过“male_view”视图插视图插入入“女女”生，在创建生，在创建“male_view”视图时应该使用视图时应该使用WITH CHECK OPTION选项。命令格式如下：选项。命令格式如下：oCREATE VIEW male_view AS oSELECT sno,sname,ssex,sage,en_time,speci

30、alty,gradeoFROM student WHERE ssex=男男 WITH CHECK OPTION2021/6/42710.1.5 删除视图删除视图o在不需要该视图的时候或想清除视图定义及与之相在不需要该视图的时候或想清除视图定义及与之相关联的权限时，可以删除该视图。视图的删除不会关联的权限时，可以删除该视图。视图的删除不会影响所依附的基本表的数据，定义在系统表影响所依附的基本表的数据，定义在系统表sysahjects、syscolumns、syscomments、sysdepends和和sysprotects中的视图信息也会被删除。中的视图信息也会被删除。o1在在SQL Serv

31、er Management Studio删除视图删除视图o在在SQL Server Management Studio中选中要中选中要删除的视图，右键单击选择删除的视图，右键单击选择“删除删除”命令，如图命令，如图10-13所示；进入所示；进入“删除对象删除对象”窗口，单击窗口，单击“确定确定”按按钮就可以删除视图。钮就可以删除视图。2021/6/42810.1.5 删除视图删除视图o2在查询窗口中执行在查询窗口中执行T-SQL语句删除视图语句删除视图oT-SQL提供了视图删除语句提供了视图删除语句DROP VIEW。o其语法格式如下：其语法格式如下：oDROP VIEW view_name图

32、图10-13删除视图菜单删除视图菜单o【例【例10-8】删除例】删除例10-1创建的创建的s_c_sc视图。视图。oUSE teachingoGO oDROP VIEW s_c_scoGO 2021/6/42910.2 索引索引o索引索引(Index)是对数据库表中一个或多个列的值进行是对数据库表中一个或多个列的值进行排序的结构，其主要目的是提高排序的结构，其主要目的是提高SQL Server系统的系统的性能，加快数据的查询速度和减少系统的响应时间。性能，加快数据的查询速度和减少系统的响应时间。所以，索引就是加快检索表中数据的方法。所以，索引就是加快检索表中数据的方法。2021/6/43010

33、.2.1 索引简介索引简介o数据库的索引就类似于书籍的目录，如果想快速查找而数据库的索引就类似于书籍的目录，如果想快速查找而不是逐页查找指定的内容，可以通过目录中章节的页号不是逐页查找指定的内容，可以通过目录中章节的页号找到其对应的内容。类似地，索引通过记录表中的关键找到其对应的内容。类似地，索引通过记录表中的关键值指向表中的记录，这样数据库引擎就不用扫描整个表值指向表中的记录，这样数据库引擎就不用扫描整个表而定位到相关的记录。相反，如果没有索引，则会导致而定位到相关的记录。相反，如果没有索引，则会导致SQL Server搜索表中的所有记录，以获取匹配结果。搜索表中的所有记录，以获取匹配结果。

34、o索引包含从表或视图中一个或多个列生成的键，以及映索引包含从表或视图中一个或多个列生成的键，以及映射到指定数据的存储位置的指针，它是以射到指定数据的存储位置的指针，它是以B+树结构与表树结构与表或视图相关联的。或视图相关联的。2021/6/43110.2.1 索引简介索引简介o索引的优点包括：索引的优点包括：o（1）大大加快数据的检索速度，这是创建索引的最）大大加快数据的检索速度，这是创建索引的最主要的原因。主要的原因。o（2）创建唯一性索引，保证表中每一行数据的唯一）创建唯一性索引，保证表中每一行数据的唯一性。性。o（3）加速表和表之间的连接。）加速表和表之间的连接。o（4）在使用分组和排序

35、子句进行数据检索时，同样）在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。可以显著减少查询中分组和排序的时间。o（5）查询优化器可以提高系统的性能，但它是依靠）查询优化器可以提高系统的性能，但它是依靠索引起作用的。索引起作用的。2021/6/43210.2.1 索引简介索引简介o虽然索引具有如此多的优点，但索引的存在也让系虽然索引具有如此多的优点，但索引的存在也让系统付出了一定的代价。创建索引和维护索引都会消统付出了一定的代价。创建索引和维护索引都会消耗时间，当对表中的数据进行增加、删除和修改操耗时间，当对表中的数据进行增加、删除和修改操作时，索引就要进行维护，否则

36、索引的作用就会下作时，索引就要进行维护，否则索引的作用就会下降。降。o另外，每个索引都会占用一定的物理空间，如果占另外，每个索引都会占用一定的物理空间，如果占用的物理空间过多，就会影响到整个用的物理空间过多，就会影响到整个SQL Server系系统的性能。统的性能。2021/6/43310.2.2 索引类型索引类型oSQL Server 2005支持在表中任何列（包括计算列）上定支持在表中任何列（包括计算列）上定义索引。索引可以是唯一的，即索引列不会有两行记录义索引。索引可以是唯一的，即索引列不会有两行记录相同，这样的索引称为唯一索引。例如，如果在表中的相同，这样的索引称为唯一索引。例如，如果

37、在表中的“sname”列上创建了唯一索引，则以后输入的姓名将不列上创建了唯一索引，则以后输入的姓名将不能同名。索引也可以是不唯一的，即索引列上可以有多能同名。索引也可以是不唯一的，即索引列上可以有多行记录相同。如果索引是根据单列创建的，这样的索引行记录相同。如果索引是根据单列创建的，这样的索引称为单列索引，根据多列组合创建的索引则称为复合索称为单列索引，根据多列组合创建的索引则称为复合索引。引。o索引的组织方式的不同，可以将索引分为聚集索引和非索引的组织方式的不同，可以将索引分为聚集索引和非聚集索引。聚集索引。2021/6/43410.2.2 索引类型索引类型o1.聚集索引聚集索引o聚集索引会

38、对表和视图进行物理排序，所以这种索聚集索引会对表和视图进行物理排序，所以这种索引对查询非常有效，在表和视图中只能有一个聚集引对查询非常有效，在表和视图中只能有一个聚集索引。当建立主键约束时，如果表中没有聚集索引，索引。当建立主键约束时，如果表中没有聚集索引，SQL Server会用主键列作为聚集索引键。可以在表会用主键列作为聚集索引键。可以在表的任何列或列的组合上建立索引，实际应用中一般的任何列或列的组合上建立索引，实际应用中一般为定义成主键约束的列建立聚集索引。为定义成主键约束的列建立聚集索引。o例如，汉语字典的正文就是一个聚集索引的顺序结例如，汉语字典的正文就是一个聚集索引的顺序结构。构。

39、2021/6/43510.2.2 索引类型索引类型o比如，要查比如，要查“安安”字，就可以翻开字典的前几页，字，就可以翻开字典的前几页，因为因为“安安”的拼音是的拼音是“an”，而按拼音排序字典是以，而按拼音排序字典是以字母字母“a”开头以开头以“z”结尾的，那么结尾的，那么“安安”字就自然字就自然地排在字典的前部。如果翻完了所有地排在字典的前部。如果翻完了所有“an”读音的部读音的部分仍然找不到这个字，那么就说明字典中没有这个分仍然找不到这个字，那么就说明字典中没有这个字。字。o同样，如果查同样，如果查“张张”字，可以将字典翻到最后部分，字，可以将字典翻到最后部分，因为因为“张张”的拼音是的

40、拼音是“zhang”。o也就是说，字典的正文内容本身就是按照音序排列也就是说，字典的正文内容本身就是按照音序排列的，而的，而“汉语拼音音节索引汉语拼音音节索引”就可以称为就可以称为“聚集索聚集索引引”。2021/6/43610.2.2 索引类型索引类型o2.非聚集索引非聚集索引o非聚集索引不会对表和视图进行物理排序。如果表中不存在非聚集索引不会对表和视图进行物理排序。如果表中不存在聚集索引，则表是未排序的。在表或视图中，最多可以建立聚集索引，则表是未排序的。在表或视图中，最多可以建立250个非聚集索引，或者个非聚集索引，或者249个非聚集索引和个非聚集索引和1个聚集索引。个聚集索引。o例如，查

41、字典时，不认识的字就不能按照上面的方法来查找。例如，查字典时，不认识的字就不能按照上面的方法来查找。o可以根据可以根据“偏旁部首偏旁部首”来查（以下内容因所使用字典不同而来查（以下内容因所使用字典不同而异）。比如查异）。比如查“张张”字，在查部首之后的检字表中字，在查部首之后的检字表中“张张”的的页码是页码是622页，检字表中页，检字表中“张张”的上面是的上面是“弛弛”字，但页码却字，但页码却是是60页，页，“张张”的下面是的下面是“弟弟”字，页码是字，页码是95页，正文中这页，正文中这些字并不是真正的分别位于些字并不是真正的分别位于“张张”字的上下方。字的上下方。2021/6/43710.2

42、.2 索引类型索引类型o所以，现在看到的连续的所以，现在看到的连续的“弛、张、弟弛、张、弟”三字实际三字实际上就是他们在非聚集索引中的排序，是字典正文中上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。这种方式来找到所需的字在非聚集索引中的映射。这种方式来找到所需要的字要两个过程，先找到目录中的结果，然后再要的字要两个过程，先找到目录中的结果，然后再翻到所需要的页码。翻到所需要的页码。o这种目录纯粹是目录，正文纯粹是正文的排序方式这种目录纯粹是目录，正文纯粹是正文的排序方式就称为就称为“非聚集索引非聚集索引”。2021/6/43810.2.2 索引类型索引类型o聚集索引和非

43、聚集索引都可以是唯一的索引。因此，只要列聚集索引和非聚集索引都可以是唯一的索引。因此，只要列中数据是唯一的，就可在同一个表上创建一个唯一的聚集索中数据是唯一的，就可在同一个表上创建一个唯一的聚集索引。如果必须实施唯一性以确保数据的完整性，则应在列上引。如果必须实施唯一性以确保数据的完整性，则应在列上创建创建UNIQUE或或PRIMARY KEY约束，而不要创建唯一索引。约束，而不要创建唯一索引。o 创建创建PRIMARY KEY或或UNIQUE约束会在表中指定的列上约束会在表中指定的列上自动创建唯一索引。创建自动创建唯一索引。创建UNIQUE约束与手动创建唯一索引约束与手动创建唯一索引没有明显

44、的区别，进行数据查询时，查询方式相同，而且查没有明显的区别，进行数据查询时，查询方式相同，而且查询优化器不区分唯一索引是由约束创建还是手动创建的。如询优化器不区分唯一索引是由约束创建还是手动创建的。如果存在重复的键值，则无法创建唯一索引和果存在重复的键值，则无法创建唯一索引和PRIMARY KEY或或UNIQUE约束。如果是复合的唯一索引，则该索引可以确约束。如果是复合的唯一索引，则该索引可以确保索引列中每个组合都是唯一的，创建复合唯一索引可为查保索引列中每个组合都是唯一的，创建复合唯一索引可为查询优化器提供附加信息，所以对多列创建复合索引时最好是询优化器提供附加信息，所以对多列创建复合索引时

45、最好是唯一索引。唯一索引。2021/6/43910.2.3 创建索引创建索引o我们已经知道，创建索引虽然可以提高查询速度，但是它需我们已经知道，创建索引虽然可以提高查询速度，但是它需要牺牲一定的系统性能。要牺牲一定的系统性能。o因此，在创建时，哪些列适合创建索引，哪些列不适合创建因此，在创建时，哪些列适合创建索引，哪些列不适合创建索引，需要进行详细的考察。索引，需要进行详细的考察。o1创建索引时应考虑的问题创建索引时应考虑的问题o（1）对一个表中建大量的索引，应进行权衡。）对一个表中建大量的索引，应进行权衡。o对于对于SELECT查询，大量索引可以提高性能，可以从中选择查询，大量索引可以提高性

46、能，可以从中选择最快的查询方法；但是，会影响最快的查询方法；但是，会影响INSERT、UPDATE和和DELETE语句的性能，因为对表中的数据进行修改时，索引语句的性能，因为对表中的数据进行修改时，索引也要动态的维护，维护索引耗费的时间会随着数据量的增加也要动态的维护，维护索引耗费的时间会随着数据量的增加而增加，所以应避免对经常更新的表建立过多的索引，而对而增加，所以应避免对经常更新的表建立过多的索引，而对更新少而且数据量大的表创建多个索引，可以大大提高查询更新少而且数据量大的表创建多个索引，可以大大提高查询性能。性能。2021/6/44010.2.3 创建索引创建索引o（2）对于小型表（行数

47、较少）进行索引可能不会产）对于小型表（行数较少）进行索引可能不会产生优化效果。生优化效果。o（3）对于主键和外键列应考虑建索引，因为经常通）对于主键和外键列应考虑建索引，因为经常通过主键查询数据，而外键用于表间的连接。过主键查询数据，而外键用于表间的连接。o（4）很少在查询中使用的列以及值很少的列不应考）很少在查询中使用的列以及值很少的列不应考虑建索引。虑建索引。o（5）视图中如果包含聚集函数或连接时，创建视图）视图中如果包含聚集函数或连接时，创建视图的索引可以显著提升查询性能。的索引可以显著提升查询性能。2021/6/44110.2.3 创建索引创建索引o2通过通过SQL Server Ma

48、nagement Studio创建索引创建索引o在在SQL Server Management Studio中使用向导创建索中使用向导创建索引是一种图形界面环境下最快捷的创建方式，其步引是一种图形界面环境下最快捷的创建方式，其步骤如下：骤如下：o（1）在）在SQL Server Management Studio的的“对象资对象资源管理器源管理器”面板中，选择要创建索引的表面板中，选择要创建索引的表(如如teaching库中的库中的student表表)，然后展开，然后展开student表前面表前面的的“+”号，选中号，选中“索引索引”选顶右击，在弹出的快捷选顶右击，在弹出的快捷菜单中选择菜单中

49、选择“新建索引新建索引”命令命令。2021/6/44210.2.3 创建索引创建索引o（2）选择）选择“新建索引新建索引”命令，进入命令，进入“新建索引新建索引”窗口。在窗口。在“常规常规”选项窗口中，可以创建索引，在选项窗口中，可以创建索引，在“索引名称索引名称”文本文本框中输入索引名称，在框中输入索引名称，在“索引类型索引类型”下拉列表中选择是不是下拉列表中选择是不是聚集索引，在聚集索引，在“唯一索引唯一索引”单选框中选择是否设置唯一索引单选框中选择是否设置唯一索引等。例如，输入索引名称为等。例如，输入索引名称为“index_sname”，选择，选择“非聚集非聚集”选项。选项。o（3）通过

50、选择索引设置按钮，可以为新建的索引添加、删除、）通过选择索引设置按钮，可以为新建的索引添加、删除、移动索引列。例如，选择移动索引列。例如，选择“添加添加”按钮，进入如图按钮，进入如图10-15所示所示的的“添加索引列添加索引列”窗口，选中窗口，选中“sname”列前的多选按钮，单列前的多选按钮，单击击“确定确定”按钮即可添加一个按按钮即可添加一个按“sname”列升序排序的非聚列升序排序的非聚集索引。再选择集索引。再选择“确定确定”按钮，索引创建完成。按钮，索引创建完成。2021/6/44310.2.3 创建索引创建索引o（4）索引创建完成后，在）索引创建完成后，在SQL Server Man

51、agement Studio的的“对象资源管理器对象资源管理器”面板中，选择创建了索面板中，选择创建了索引的表引的表(student)，展开，展开student表前面的表前面的“+”号，再号，再展开展开“索引索引”选顶前面的选顶前面的“+”号，就会出现新建的号，就会出现新建的索引索引“index_sname”。2021/6/44410.2.3 创建索引创建索引o3利用利用T-SQL语句创建索引语句创建索引o语法格式如下：语法格式如下：oCREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX index_name o ON table_name|view_name

52、(column_name ASC|DESC ,.n )o WITH ,.n ON filegroup o :=o PAD_INDEX|FILLFACTOR=fillfactor o|IGNORE_DUP_KEY|DROP_EXISTING o|STATISTICS_NORECOMPUTE 2021/6/44510.2.3 创建索引创建索引o【例【例10-9】同前例，根据】同前例，根据teaching库中库中student表的姓表的姓名列的升序创建一个名为名列的升序创建一个名为“index_sname”的普通索的普通索引，用引，用T-SQL语句完成。语句完成。o USE teachingoGOo

53、 CREATE INDEX index_sname ON student(sname)2021/6/44610.2.3 创建索引创建索引o【例【例10-10】根据】根据“inventory”库中库中“goods”表的商品表的商品名称、生产商创建一个名为名称、生产商创建一个名为“goods_producer”的唯的唯一性复合索引，其中商品名称为升序，生产商为降一性复合索引，其中商品名称为升序，生产商为降序。序。oUSE inventoryoGOoCREATE UNIQUE INDEX goods_produceroON goods(gname ASC,producer DESC)2021/6/4

54、4710.2.3 创建索引创建索引o4间接创建索引间接创建索引o在定义表结构或修改表结构时，如果定义了主键约束在定义表结构或修改表结构时，如果定义了主键约束（PRAMARY KEY）或者唯一性约束（）或者唯一性约束（UNIQUE），可以间），可以间接创建索引。接创建索引。o【例【例10-11】创建一个】创建一个“student1”表，并定义了主键约束。表，并定义了主键约束。oUSE teachingoGOoCREATE TABLE student1(osno char(6)PRAMARY KEY,osname char(8)o此例中，就按此例中，就按sno升序创建了一个聚集索引。升序创建了一个

55、聚集索引。2021/6/44810.2.3 创建索引创建索引o【例【例10-12】创建一个】创建一个“teacher”教师表，并定义了教师表，并定义了主键约束和唯一性约束。主键约束和唯一性约束。oUSE teachingoGOoCREATE TABLE teacher(otno char(6)PRAMARY KEY,otname char(8)UNIQUE )o此例中，创建了两个索引，按此例中，创建了两个索引，按tno升序创建了一个聚升序创建了一个聚集索引，按集索引，按tname升序创建了一个非聚集唯一索引。升序创建了一个非聚集唯一索引。2021/6/44910.2.3 创建索引创建索引o索引

56、一经创建，就完全由系统自动选择和维护，不需要用户索引一经创建，就完全由系统自动选择和维护，不需要用户指定使用索引，也不需要用户执行打开索引或进行重新索引指定使用索引，也不需要用户执行打开索引或进行重新索引等操作，所有的工作都由等操作，所有的工作都由SQL Server数据库管理系统自动完数据库管理系统自动完成。但对于读者来讲，应该明白为什么要创建这些索引，即成。但对于读者来讲，应该明白为什么要创建这些索引，即这些索引可能在什么情况下被选择使用。例如，这些索引可能在什么情况下被选择使用。例如，student表中表中按姓名列升序创建的按姓名列升序创建的“index_sname”索引，下面的索引，下

57、面的T-SQL语语句在执行时系统就可以利用此索引来加快查询速度。句在执行时系统就可以利用此索引来加快查询速度。o(1)SELECT sno,specialty FROM student WHERE sname=郑郑丽丽o(2)DELETE FROM student WHERE sname=郑丽郑丽2021/6/45010.2.3 创建索引创建索引o5创建索引视图创建索引视图o视图也称为虚拟表，这是因为由视图返回的结果集其一般格视图也称为虚拟表，这是因为由视图返回的结果集其一般格式与由列和行组成的表相似，并且，在式与由列和行组成的表相似，并且，在 SQL 语句中引用视图语句中引用视图的方式也与引

58、用表的方式相同。的方式也与引用表的方式相同。o对于标准视图而言，结果集不是永久地存储在数据库中，为对于标准视图而言，结果集不是永久地存储在数据库中，为每个引用视图的查询动态生成结果集的开销很大，特别是对每个引用视图的查询动态生成结果集的开销很大，特别是对于那些涉及对大量行进行复杂处理（如聚合大量数据或连接于那些涉及对大量行进行复杂处理（如聚合大量数据或连接许多行）的视图更为可观。若经常在查询中引用这类视图，许多行）的视图更为可观。若经常在查询中引用这类视图，可通过在视图上创建唯一聚集索引来提高性能。在视图上创可通过在视图上创建唯一聚集索引来提高性能。在视图上创建唯一聚集索引时将执行该视图，并且

59、结果集在数据库中的建唯一聚集索引时将执行该视图，并且结果集在数据库中的存储方式与带聚集索引的表的存储方式相同。存储方式与带聚集索引的表的存储方式相同。2021/6/45110.2.3 创建索引创建索引o在视图上创建索引的另一个好处是：查询优化器开始在查询中在视图上创建索引的另一个好处是：查询优化器开始在查询中使用视图索引，而不是直接在使用视图索引，而不是直接在 FROM 子句中命名视图。这样子句中命名视图。这样一来，可从索引视图检索数据而无需重新编码，由此带来的高一来，可从索引视图检索数据而无需重新编码，由此带来的高效率也使现有查询获益。效率也使现有查询获益。o在视图上创建聚集索引可存储创建索

60、引时存在的数据。索引视在视图上创建聚集索引可存储创建索引时存在的数据。索引视图还自动反映自创建索引后对基表数据所做的更改，这一点与图还自动反映自创建索引后对基表数据所做的更改，这一点与在基表上创建的索引相同。当对基表中的数据进行更改时，索在基表上创建的索引相同。当对基表中的数据进行更改时，索引视图中存储的数据也反映数据更改。视图的聚集索引必须唯引视图中存储的数据也反映数据更改。视图的聚集索引必须唯一，从而提高了一，从而提高了SQL Server 在索引中查找受任何数据更改影在索引中查找受任何数据更改影响的行的效率。响的行的效率。2021/6/45210.2.3 创建索引创建索引o与基本表上的索

61、引相比，对索引视图的维护可能更复杂。只与基本表上的索引相比，对索引视图的维护可能更复杂。只有当视图的结果检索速度的效益超过了修改所需的开销时，有当视图的结果检索速度的效益超过了修改所需的开销时，才应在视图上创建索引。这样的视图通常包括映射到相对静才应在视图上创建索引。这样的视图通常包括映射到相对静态的数据上、处理多行以及由许多查询引用的视图。态的数据上、处理多行以及由许多查询引用的视图。o在视图上创建聚集索引之前，该视图必须满足下列要求：在视图上创建聚集索引之前，该视图必须满足下列要求：o(1)当执行当执行 CREATE VIEW 语句时，语句时，ANSI_NULLS 和和 QUOTED_ID

62、ENTIFIER 选项必须设置为选项必须设置为 ON。OBJECTPROPERTY 函数通过函数通过 ExecIsAnsiNullsOn 或或 ExecIsQuotedIdentOn 属性为视图报告此信息。属性为视图报告此信息。o(2)为执行所有为执行所有 CREATE TABLE 语句以创建视图引用的表，语句以创建视图引用的表，ANSI_NULLS 选项必须设置为选项必须设置为 ON。2021/6/45310.2.3 创建索引创建索引o(3)视图不能引用任何其它视图，只能引用基本表。视图不能引用任何其它视图，只能引用基本表。o(4)视图引用的所有基本表必须与视图位于同一个数据视图引用的所有基

63、本表必须与视图位于同一个数据库中，并且所有者也与视图相同。库中，并且所有者也与视图相同。o(5)必须使用必须使用 SCHEMABINDING 选项创建视图。选项创建视图。SCHEMABINDING 将视图绑定到基础基本表的架构将视图绑定到基础基本表的架构上。上。o(6)必须已使用必须已使用 SCHEMABINDING 选项创建了视图中选项创建了视图中引用的用户定义的函数。引用的用户定义的函数。o(7)表和用户定义的函数必须由两部分的名称引用。表和用户定义的函数必须由两部分的名称引用。2021/6/45410.2.3 创建索引创建索引o(8)IGNORE DUP_KEY：指在使用：指在使用INS

64、ERT或或UPDATE命令修改数据且加入相同关键字内容时对操命令修改数据且加入相同关键字内容时对操作的反应。作的反应。o(9)DROP EXISTING：删除并重新建立原来存在的聚：删除并重新建立原来存在的聚集索引或非聚集索引，新指定的索引名必须与现有的集索引或非聚集索引，新指定的索引名必须与现有的索引名相同。索引名相同。o(10)STATISTICS_NORECOMPUTE：过期的索引统计，：过期的索引统计，不会自动重新计算。不会自动重新计算。o(11)filegroup：在己经创建的文件组上指定索引。：在己经创建的文件组上指定索引。2021/6/45510.2.3 创建索引创建索引o【例【

65、例10-13】创建一个】创建一个“female_view”女生视图，并为该视女生视图，并为该视图按图按“sno”升序创建一个具有唯一性的聚集索引。升序创建一个具有唯一性的聚集索引。o创建视图：创建视图：oUSE teachingoGOoCREATE VIEW female_viewoWITH SCHEMABINDINGoASoSELECT sno,sname,ssex,specialty FROM dbo.studentoWHERE ssex=女女o创建索引：创建索引：oCREATE UNIQUE CLUSTERED INDEX index_female ON female_view(sno)

66、2021/6/45610.2.4 查看索引信息查看索引信息o 在实际使用索引的过程中，有时需要对表的索引信息进行在实际使用索引的过程中，有时需要对表的索引信息进行查询，了解在表中曾经建立的索引。可以使用查询，了解在表中曾经建立的索引。可以使用SQL Server Management Studio进行查询；也可以在查询窗口中使用进行查询；也可以在查询窗口中使用T-SQL语言句进行查询。语言句进行查询。o1.在在SQL Server Management Studio中查看索引信息中查看索引信息o在在SQL Server Management Studio中，选择要查看的表，然中，选择要查看的表，然后使用鼠标右键单击相应的表，从菜单中选择后使用鼠标右键单击相应的表，从菜单中选择“修改修改”，进，进入入“表设计器表设计器”窗口，右键单击任意位置，选择窗口，右键单击任意位置，选择“索引索引/键键”即可查看此表上所有的索引信息。即可查看此表上所有的索引信息。2021/6/45710.2.4 查看索引信息查看索引信息o2.使用使用T_SQL语句查看索引信息语句查看索引信息o可以使用系统存储过程可