索引和视图-课件

第7章索引和视图第7章索引和视图本章学习目标掌握索引的创建、管理、维护和删除等操作掌握视图的创建、修改、使用和删除等操作学会使用视图查询、修改、更新和删除数据

本章学习目标掌握索引的创建、管理、维护和删除等操作7.1索引1、索引基础知识索引的概念和作用

索引是对数据库表中一个或多个列（例如Student表的“学号”列）的值进行排序的结构。 索引提供指针以指向存储在表中指定列的数据值，然后根据指定的排序次序排列这些指针。

7.1索引1、索引基础知识索引的分类

SQLServer中的索引分为3类，分别是：聚集索引非聚集索引唯一索引

索引的分类聚集索引

聚集索引会对表和视图进行物理排序，所以这种索引对查询非常有效，在表和视图中只能有一个聚集索引。当建立主键约束时，如果表中没有聚集索引，SQLServer会用主键列作为聚集索引键。也可以手动在表的任何列或列的组合上建立索引，但在实际应用中，一般为定义成主键约束的列建立聚集索引。返回聚集索引返回非聚集索引 非聚集索引不会对表和视图进行物理排序。如果表或视图中不存储聚集索引，则表或视图是未排序的。在表和视图中最多可以建立250个非聚集索引，或者249个非聚集索引和一个聚集索引。

返回非聚集索引返回唯一索引 唯一索引确保索引键不包含重复的值，因此，表或视图中的每一行在某种程度上是唯一的。例如，如果在表中的“姓名”字段上创建了唯一索引，则以后输入的姓名将不能同名。返回唯一索引返回2、建立索引 创建表或视图的索引的方法有两种：使用对象资源管理器和T-SQL语句。使用“对象资源管理器”创建索引

具体的方法请参考本书第7章2、建立索引使用“对象资源管理器”创建索引

使用“对象资源管理器”创建索引

使用“对象资源管理器”创建索引使用“对象资源管理器”创建索引使用“对象资源管理器”创建索引使用“对象资源管理器”创建索引使用T-SQL语句创建索引

T-SQL语句创建索引的基本语法如下：

CREATE[UNIQUE][CLUSTERED|NONCLUSTERED] INDEXindex_name ON<object>(column[ASC|DESC][,...n]) [INCLUDE(column_name[,...n])] [WITH(<relational_index_option>[,...n])]使用T-SQL语句创建索引属性和参数说明：

UNIQUE：建立唯一索引。

CLUSTERED：建立聚集索引。

NONCLUSTERED：建立非聚集索引。

index_name：索引名称，索引名称在表或视图中必须唯一，但 在数据库中不必唯一。

object：表名或视图名。

column：索引所基于的一列或多列。

ASC|DESC：确定特定索引列的升序或降序排序方向。默认值 为ASC（升序）。

INCLUDE(column_name[,...n])：指定要添加到非聚集索引的 叶级别的非键列。非聚集索引可以唯一，也可以不 唯一。

relational_index_option：索引属性。如DROPINDEX ix_Student表示先删除已经存在的索引，因为索引 名称不能重复，否则会出错。属性和参数说明：【例7.1】使用T-SQL语句在CJGL数据库中的Student表上创建名为IX_Student的唯一、聚集索引，该索引基于表中的“学号”列。

T-SQL脚本语句如下：

USECJGL GO CREATEUNIQUECLUSTERED INDEXIX_Student ONStudent(StuNo) GO

注意：①UNIQUE和CLUSTERED之间不能用“,”号②“学号”列中不能有重复值③关键字的大小写不影响结果④只有表的所有者才能执行CREATEINDEX语句⑤更多请参考本书第页【例7.1】使用T-SQL语句在CJGL数据库中的Stude3、管理索引 管理索引的操作包括：显示索引信息重命名索引删除索引其他3、管理索引显示索引 在索引创建完成后，可以查看索引的相关信息，包括查看索引的名称、类型、索引键列等。查看索引信息有两种方法：一种是用“对象资源管理器”进行显示；另一种是用T-SQL的相关语句进行显示。

显示索引使用“对象资源管理器”显示索引 在“对象资源管理器”中找到相应的表，展开“索引”前面的“+”号后，会列出已经存在的索引列表，可以用鼠标右键单击其中一个索引名称，在打开的快捷菜单中选择“属性”，即可弹出相应的“索引”属性窗口，进行查看相关索引的信息。 如下页图所示：使用“对象资源管理器”显示索引索引和视图-课件使用T-SQL语句显示索引 使用T-SQL语句显示相关索引信息，实际是调用了系统自定义的存储过程sp_helpindex（存储过程的相关知识请参看本书第9章的内容）。【例7.2】使用T-SQL语句显示CJGL数据库中Student表的索引信息。 在查询窗口中使用的T-SQL语句如下：

USECJGL GO EXECsp_helpindexStudent GO使用T-SQL语句显示索引

执行结果如图所示返回 执行结果如图所示返回重命名索引 重命名索引和显示索引信息一样，同样有两种方法：利用“对象资源管理器”和T-SQL命令。

重命名索引使用“对象资源管理器”重命名索引 此种方法重命名索引比较简单，和重命名表名的操作一样。使用“对象资源管理器”重命名索引使用T-SQL语句重命名索引 利用T-SQL命令重命名索引，同样也是调用了系统自定义的存储过程sp_rename。其基本语法如下：

EXECsp_rename'table.old_index','new_index'

参数说明：

table.old_index：当前的索引名称。

new_index：新的索引名称。使用T-SQL语句重命名索引【例7.3】用sp_rename存储过程重命名CJGL数据库中的Student表下的IX_Student索引为IX_new_Student。 在查询窗口中输出以下T-SQL命令：

USECJGL GO EXECsp_rename‘student.IX_Student’,’IX_new_Student’ GO返回【例7.3】用sp_rename存储过程重命名CJGL数据库删除索引 删除索引同样有两种方法：利用“对象资源管理器”和T-SQL命令。使用“对象资源管理器”重命名索引 右键单击要删除的索引，在弹出的快捷菜单中执行“删除”命令，此时会弹出“删除对象”窗口，单击窗口中的“确定”按钮即可删除此索引。

删除索引索引和视图-课件使用T-SQL语句删除索引 使用T-SQL命令的DROPINDEX语句删除索引的基本语法如下：

DROPINDEXtable_or_view_name.index_name[,…n]

属性和参数说明：

table_or_view_name：表或视图名

index_name：索引名

…n：删除多个索引使用T-SQL语句删除索引【例7.5】删除CJGL数据库下的Student表中的名为IX_new_Student的索引。 在查询窗口中输入如下T-SQL语句：

USECJGL GO DROPINDEXStudent.IX_new_Student Go返回【例7.5】删除CJGL数据库下的Student表中的名为返3、索引分析和维护索引分析

SQLServer提供了分析索引和查询性能的方法，这里只介绍常用的两种命令：SHOWPLAN_ALLSTATISTICSIO3、索引分析和维护SHOWPLAN_ALL命令

SQLServer将显示在执行查询的过程中连接表时所采取的每个步骤，以及是否选择和选择了哪个索引，从而达到帮助用户分析有哪些索引被系统采用的目的。

SHOWPLAN_ALL的基本语法如下：

SETSHOWPLAN_ALL{ON|OFF}SHOWPLAN_ALL命令【例7.6】在CJGL数据库中的Student表中查询姓为“王”的所有学生信息，并且分析哪些索引被采用。

在本例中，假设已经创建了三个索引，一个是基于“学号”PRIMARYKEY约束的索引“PK_Student”，一个是基于“性别”的名为“IX_SSex_Student”的索引，一个是基于“姓名”的名为“IX_SName_Student”的索引。在查询窗口中输入如下命令，并查看结果。【例7.6】在CJGL数据库中的Student表中查询姓为“ USECJGL GO SETSHOWPLAN_ALLON GO SELECT*FROMStudentWHERE姓名LIKE'王%' GO SETSHOWPLAN_ALLOFF GO

执行结果和索引的使用情况如下图所示 USECJGL执行结果分析：在执行语句SHOWPLAN_ALL后，能发现在建立的三个索引中，只有基于“学号”PRIMARYKEY约束的索引“PK_Student”被调用(图中红框)，而其余两个并未出现，则说明其余的两个索引在这里是毫无用处的。执行结果分析：索引维护 判断索引是否需要维护的标准是索引碎片的大小。数据库上进行很多次的插入、更新和删除操作，久而久之就会使数据变得杂乱无序，造成索引碎片，从而造成性能的下降。索引维护

可以通过DBCCSHOWCONTIG命令来扫描表，同其返回值来确定该索引页的碎片是否严重。

DBCCSHOWCONTIG语句的基本语法是：

DBCCSHOWCONTIG [( {‘table_name|table_id|‘view_name’|view_id} [,’index_name’|index_id] )] 可以通过DBCCSHOWCONTIG命令来扫描表，同【例7.8】利用DBCCSHOWCONTIG命令返回CJGL数据库中Student表的IX_new_Student索引的碎片信息。 在查询窗口中输入如下命令：

useCJGL go DBCCSHOWCONTIG(Student,IX_new_Student) go

返回的信息如下图所示【例7.8】利用DBCCSHOWCONTIG命令返回CJG利用DBCCSHOWCONTIG命令扫描Student表

当碎片较多时就需要整理，碎片整理的命令为：

DBCCINDEXDEFRAG。

DBCCINDEXDEFRAG命令的基本语法格式为：

DBCCINDEXDEFRAG （database_name,table_name|view_name,index_name）参考本书的【例7.9】利用DBCCSHOWCONTIG命令扫描Student表7.2视图1、视图基础知识视图的概念和作用 视图是从一个或多个实际表中获得的，这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。 视图中存放的是基表中用户感兴趣的数据。7.2视图1、视图基础知识

视图具有以下优点：简单性安全性逻辑数据独立性

视图也有一些缺点：性能方面修改受限制 视图具有以下优点：2、创建视图使用“对象资源管理器”创建视图

使用“对象资源管理器”创建视图的方法较为复杂，步骤较为繁琐，具体操作请读者参考本书第7章相关示例。2、创建视图使用“对象资源管理器”创建视图

使用“对象资源管理器”创建视图

使用“对象资源管理器”创建视图使用“对象资源管理器”创建视图使用“对象资源管理器”创建视图使用“对象资源管理器”创建视图使用“对象资源管理器”创建视图使用“对象资源管理器”创建视图使用T-SQL语句创建视图

T-SQL语句创建视图的基本语法如下：

CREATEVIEWview_name[(column[,...n])] [WITH<view_attribute>[,...n]] ASselect_statement[;]

属性和参数说明：

view_name：

column：

WITH<view_attribute>：

AS：

select_statement：视图的名称。视图名称必须符合有关标识符的规则视图中的列使用的名称对视图进行属性设置，比如：WITHENCRYPTION对视图进行加密，防止在SQLServer复制过程中发布视图指定视图要执行的操作定义视图的SELECT语句使用T-SQL语句创建视图视图的名称。视图名称必须符合有关标【例7.11】用T-SQL语句在Student表上创建名为v_Student的视图，该视图只具有查看所有姓“王”学生信息的功能。 （1）创建视图 在“新建查询”窗口中输入以下SQL语句：

useCJGL go createviewv_Student as select*fromStudentwhere姓名like'王%' go【例7.11】用T-SQL语句在Student表上创建名为

（2）查看该视图的内容 在“新建查询”窗口中输入以下SQL语句。

useCJGL go select*fromv_Student go

结果如图所示 （2）查看该视图的内容

接下来看一个较为复杂的例子（基表是多张表）【例7.12】创建一个名为“v_C语言成绩”的视图，要求该视图能显示所有“C语言程序设计”的成绩为合格的学生的学号、姓名和分数。 题目分析： 根据本例的要求可知，所要查询的列有学号、姓名和分数，但是由于Score表中并没有相对应的列，所以这里的操作应该在表Score、Course和Student上进行。 接下来看一个较为复杂的例子（基表是多张表）在“查询”窗口中输入以下代码：useCJGLgocreateviewv_C语言成绩asselectdistinctStudent.StuNo,Student.StuName,Score.GradefromStudent,Score,CoursewhereScore.Grade>='60'andStudent.StuNo=Score.StuNoand Score.CouNo=(selectCourse.CouNo fromCourse whereCouName='C语言程序设计')go在“查询”窗口中输入以下代码：

使用Groupby子句、命名新列【例7.13】创建一个名为“v_专业学生数”的视图，要求该视图能显示各专业的学生总数。 在“查询”窗口中输入以下代码：

useCJGL go createviewv_专业学生数

as select专业班级,count(*)各专业学生数fromStudent groupby专业班级

go 使用Groupby子句、命名新列3、管理视图 对视图进行管理，主要有：重命名视图修改视图通过视图修改数据删除视图查看视图定义信息3、管理视图重命名视图使用“对象资源管理器”重命名视图 操作比较简单，如下图所示：重命名视图重命名视图重命名视图使用T-SQL语句重命名视图 使用T-SQL命令进行视图的重命名实际上是使用了系统存储过程sp_rename，其语法格式为：

sp_renameold_name,new_name

参数说明：

old_name：原视图名称。

new_name：新视图名称。使用T-SQL语句重命名视图【例7.14】用T-SQL语句将例7.13中创建的视图“v_专业学生数”改名为“v_学生数”。 在“查询”窗口中输入以下代码：

useCJGL go sp_renamev_专业学生数,v_学生数

go【例7.14】用T-SQL语句将例7.13中创建的视图“v_修改视图使用“对象资源管理器”修改视图

使用“对象资源管理器”创建视图的方法较为复杂，步骤较为繁琐，具体操作请读者参考本书第7章的【例7.16】。修改视图使用T-SQL语句修改视图 修改视图的T-SQL命令为ALTERVIEW语句，其基本语法为：

ALTERVIEWview_name[(column[,...n])] [WITH<view_attribute>[,...n]] ASselect_statement[;] [WITHCHECKOPTION]

属性和参数说明：

view_name：要修改的视图名称

column：列名

view_attribute：视图的属性

select_statement：定义视图的select语句

WITHCHECKOPTION：要求对该视图执行的所有数据修改语句都必须符合select_statement中所设置的条件使用T-SQL语句修改视图【例7.16】将【例7.11】创建的视图v_Student中的筛选条件改为“所有姓王的女生”。

分析：【例7.11】中创建视图的条件为“姓王的学生”，而本例是将其条件增加一项——“女生”。 在“查询”窗口中输入以下T-SQL命令：

useCJGL go alterviewv_Student as select*fromStudent where姓名like'王%'and性别='女' go【例7.16】将【例7.11】创建的视图v_Student中通过视图修改数据 在使用视图修改数据时，要注意以下一些事项：不能在一个语句中对多个基本表使用数据修改语句。如果要修改由两个或两个以上基本表得到的视图，必须实行多次修改，每次修改只能影响一个基表。对于基表中需要更新而又不允许空值的所有列，它们的值在INSERT语句或DEFAULT定义中指定。这将确保基表中所有需要值的列都可以获取值。通过视图修改数据不能修改经过计算得到结果的列。在视图定义中使用了“WITHCHECKOPTION”子句，则所有在视图上执行的数据修改语句都必须符合定义视图的SELECT语句中所设定的条件。在基表的列中修改的数据必须符合对这些列的约束条件，如：是否为空、约束、DEFAULT定义等。不能修改经过计算得到结果的列。（1）更新视图数据达到更改表中数据的目的【例7.18】将“v_Student”视图中学号为“