索引和视图-PPT课件.ppt

1、第第7章章 索引和视图索引和视图本章学习目标本章学习目标l掌握索引的创建、管理、维护和删除等操作 l掌握视图的创建、修改、使用和删除等操作 l学会使用视图查询、修改、更新和删除数据 7.1 索引索引1、索引基础知识、索引基础知识 索引的概念和作用 索引是对数据库表中一个或多个列（例如Student表的“学号”列）的值进行排序的结构。 索引提供指针以指向存储在表中指定列的数据值，然后根据指定的排序次序排列这些指针。 索引的分类 SQL Server 中的索引分为3类，分别是：聚集索引非聚集索引唯一索引 聚集索引聚集索引 聚集索引会对表和视图进行物理排序，所以这种索引对查询非常有效，在表和视图中只

2、能有一个聚集索引。当建立主键约束时，如果表中没有聚集索引，SQL Server 会用主键列作为聚集索引键。也可以手动在表的任何列或列的组合上建立索引，但在实际应用中，一般为定义成主键约束的列建立聚集索引。返回返回非聚集索引非聚集索引 非聚集索引不会对表和视图进行物理排序。如果表或视图中不存储聚集索引，则表或视图是未排序的。在表和视图中最多可以建立250个非聚集索引，或者249个非聚集索引和一个聚集索引。 返回返回唯一索引唯一索引 唯一索引确保索引键不包含重复的值，因此，表或视图中的每一行在某种程度上是唯一的。例如，如果在表中的“姓名”字段上创建了唯一索引，则以后输入的姓名将不能同名。返回返回2

3、、建立索引、建立索引 创建表或视图的索引的方法有两种：使用对象资源管理器和T-SQL语句。使用“对象资源管理器”创建索引 具体的方法请参考本书第7章使用使用“对象资源管理器对象资源管理器”创建索创建索引引使用使用“对象资源管理器对象资源管理器”创建索创建索引引使用使用“对象资源管理器对象资源管理器”创建索创建索引引使用T-SQL语句创建索引 T-SQL语句创建索引的基本语法如下：CREATE UNIQUECLUSTERED|NONCLUSTEREDINDEX index_name ON (columnASC|DESC,.n) INCLUDE(column_name,.n)WITH(,.n)属性

4、和参数说明：UNIQUE：建立唯一索引。CLUSTERED：建立聚集索引。NONCLUSTERED：建立非聚集索引。index_name：索引名称，索引名称在表或视图中必须唯一，但在数据库中不必唯一。object：表名或视图名。column：索引所基于的一列或多列。ASC|DESC：确定特定索引列的升序或降序排序方向。默认值为ASC（升序）。INCLUDE(column_name,.n)：指定要添加到非聚集索引的叶级别的非键列。非聚集索引可以唯一，也可以不唯一。relational_index_option：索引属性。如DROP INDEX ix_Student表示先删除已经存在的索引，因为索

5、引名称不能重复，否则会出错。【例7.1】使用T-SQL语句在CJGL数据库中的Student表上创建名为IX_Student的唯一、聚集索引，该索引基于表中的“学号”列。T-SQL脚本语句如下：USE CJGLGOCREATE UNIQUE CLUSTEREDINDEX IX_StudentON Student(StuNo)GO 注意：注意：UNIQUE和CLUSTERED之间不能用“,”号“学号”列中不能有重复值关键字的大小写不影响结果只有表的所有者才能执行CREATE INDEX语句更多请参考本书第页3、管理索引、管理索引 管理索引的操作包括：显示索引信息重命名索引删除索引其他显示索引显示

6、索引 在索引创建完成后，可以查看索引的相关信息，包括查看索引的名称、类型、索引键列等。查看索引信息有两种方法：一种是用“对象资源管理器”进行显示；另一种是用T-SQL的相关语句进行显示。 使用“对象资源管理器”显示索引 在“对象资源管理器”中找到相应的表，展开“索引”前面的“+”号后，会列出已经存在的索引列表，可以用鼠标右键单击其中一个索引名称，在打开的快捷菜单中选择“属性”，即可弹出相应的“索引”属性窗口，进行查看相关索引的信息。 如下页图所示：使用T-SQL语句显示索引 使用T-SQL语句显示相关索引信息，实际是调用了系统自定义的存储过程sp_helpindex（存储过程的相关知识请参看本

7、书第9章的内容）。【例7.2】使用T-SQL语句显示CJGL数据库中Student表的索引信息。 在查询窗口中使用的T-SQL语句如下：USE CJGLGOEXEC sp_helpindex StudentGO 执行结果如图所示返回返回重命名索引重命名索引 重命名索引和显示索引信息一样，同样有两种方法：利用“对象资源管理器”和T-SQL命令。 使用“对象资源管理器”重命名索引 此种方法重命名索引比较简单，和重命名表名的操作一样。使用T-SQL语句重命名索引利用T-SQL命令重命名索引，同样也是调用了系统自定义的存储过程sp_rename。其基本语法如下：EXEC sp_rename table

8、.old_index,new_index参数说明：table.old_index：当前的索引名称。new_index：新的索引名称。【例7.3】用sp_rename存储过程重命名CJGL数据库中的Student表下的IX_Student索引为IX_new_Student。在查询窗口中输出以下T-SQL命令：USE CJGLGOEXEC sp_renamesp_renamestudent.IX_Studentstudent.IX_Student, ,IX_new_StudentIX_new_StudentGO返回返回删除索引删除索引删除索引同样有两种方法：利用“对象资源管理器”和T-SQL命令。

9、使用“对象资源管理器”重命名索引右键单击要删除的索引，在弹出的快捷菜单中执行“删除”命令，此时会弹出 “删除对象”窗口，单击窗口中的“确定”按钮即可删除此索引。 使用T-SQL语句删除索引 使用T-SQL命令的DROP INDEX语句删除索引的基本语法如下：DROP INDEX table_or_view_name.index_name,DROP INDEX table_or_view_name.index_name,nn属性和参数说明：table_or_view_name：表或视图名index_name：索引名n：删除多个索引【例7.5】删除CJGL数据库下的Student表中的名为IX_n

10、ew_Student的索引。 在查询窗口中输入如下T-SQL语句： USE CJGL GO DROP INDEX Student.IX_new_Student Go返回返回3、索引分析和维护、索引分析和维护索引分析 SQL Server 提供了分析索引和查询性能的方法，这里只介绍常用的两种命令：SHOWPLAN_ALLSTATISTICS IOSHOWPLAN_ALLSHOWPLAN_ALL命令命令 SQL Server将显示在执行查询的过程中连接表时所采取的每个步骤，以及是否选择和选择了哪个索引，从而达到帮助用户分析有哪些索引被系统采用的目的。 SHOWPLAN_ALL的基本语法如下：SET

11、 SHOWPLAN_ALLON|OFF【例7.6】在CJGL数据库中的Student表中查询姓为“王”的所有学生信息，并且分析哪些索引被采用。 在本例中，假设已经创建了三个索引，一个是基于“学号”PRIMARY KEY约束的索引“PK_Student”，一个是基于“性别”的名为“IX_SSex_Student”的索引，一个是基于“姓名”的名为“IX_SName_Student”的索引。 在查询窗口中输入如下命令，并查看结果。 USE CJGL GO SET SHOWPLAN_ALL ON GO SELECT * FROM Student WHERE 姓名 LIKE 王% GO SET SHOW

12、PLAN_ALL OFF GO执行结果和索引的使用情况如下图所示执行结果分析： 在执行语句SHOWPLAN_ALL后，能发现在建立的三个索引中，只有基于“学号”PRIMARY KEY约束的索引“PK_Student”被调用(图中红框)，而其余两个并未出现，则说明其余的两个索引在这里是毫无用处的。索引维护 判断索引是否需要维护的标准是索引碎片的大小。数据库上进行很多次的插入、更新和删除操作，久而久之就会使数据变得杂乱无序，造成索引碎片，从而造成性能的下降。 可以通过DBCC SHOWCONTIG命令来扫描表，同其返回值来确定该索引页的碎片是否严重。 DBCC SHOWCONTIG语句的基本语法是

13、：DBCC SHOWCONTIG ( table_name|table_id |view_name|view_id ,index_name|index_id )【例7.8】利用DBCC SHOWCONTIG命令返回CJGL数据库中Student表的IX_new_Student索引的碎片信息。在查询窗口中输入如下命令：use CJGLgoDBCC SHOWCONTIG(Student,IX_new_Student)go返回的信息如下图所示利用DBCC SHOWCONTIG命令扫描Student表 当碎片较多时就需要整理，碎片整理的命令为：DBCC INDEXDEFRAG。 DBCC INDEXD

14、EFRAG命令的基本语法格式为：DBCC INDEXDEFRAG （database_name,table_name|view_name,index_name） 参考本书的【例7.9】7.2 视图视图1、视图基础知识、视图基础知识视图的概念和作用 视图是从一个或多个实际表中获得的，这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。 视图中存放的是基表中用户感兴趣的数据。 视图具有以下优点：简单性安全性逻辑数据独立性 视图也有一些缺点：性能方面修改受限制2、创建视图、创建视图使用“对象资源管理器”创建视图 使用“对象资源管理器”创建视图的方法较为

15、复杂，步骤较为繁琐，具体操作请读者参考本书第7章相关示例。使用使用“对象资源管理器对象资源管理器”创建视创建视图图使用使用“对象资源管理器对象资源管理器”创建视创建视图图使用使用“对象资源管理器对象资源管理器”创建视创建视图图使用使用“对象资源管理器对象资源管理器”创建视创建视图图使用T-SQL语句创建视图T-SQL语句创建视图的基本语法如下：CREATE VIEW view_name (column,.n) WITH,.n AS select_statement ;属性和参数说明： view_name： column： WITH ： AS： select_statement：视图的名称。视图

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

17、L语句。use CJGLgoselect * from v_Studentgo结果如图所示 接下来看一个较为复杂的例子（基表是多张表）【例7.12】创建一个名为“v_C语言成绩”的视图，要求该视图能显示所有“C语言程序设计”的成绩为合格的学生的学号、姓名和分数。题目分析：根据本例的要求可知，所要查询的列有学号、姓名和分数，但是由于Score表中并没有相对应的列，所以这里的操作应该在表Score、Course和Student上进行。在“查询”窗口中输入以下代码：use CJGLgocreate view v_C语言成绩asselect distinct Student.StuNo,Student

18、.StuName,Score.Gradefrom Student,Score,Coursewhere Score.Grade=60 and Student.StuNo=Score.StuNo and Score.CouNo= ( select Course.CouNofrom Coursewhere CouName=C语言程序设计 )go 使用Group by子句、命名新列【例7.13】创建一个名为“v_专业学生数”的视图，要求该视图能显示各专业的学生总数。在“查询”窗口中输入以下代码：use CJGLgocreate view v_专业学生数as select 专业班级,count(*) 各

19、专业学生数 from Studentgroup by 专业班级go3、管理视图、管理视图 对视图进行管理，主要有：重命名视图修改视图通过视图修改数据删除视图查看视图定义信息重命名视图重命名视图使用“对象资源管理器”重命名视图 操作比较简单，如下图所示：重命名视图 使用T-SQL语句重命名视图 使用T-SQL命令进行视图的重命名实际上是使用了系统存储过程sp_rename，其语法格式为： sp_rename old_name,new_name参数说明： old_name：原视图名称。 new_name：新视图名称。【例7.14】用T-SQL语句将例7.13中创建的视图“v_专业学生数”改名为“v

20、_学生数”。在“查询”窗口中输入以下代码：use CJGLgosp_rename v_专业学生数,v_学生数go修改视图修改视图使用“对象资源管理器”修改视图使用“对象资源管理器”创建视图的方法较为复杂，步骤较为繁琐，具体操作请读者参考本书第7章的【例7.16】。使用T-SQL语句修改视图 修改视图的T-SQL命令为ALTER VIEW语句，其基本语法为：ALTER VIEW view_name (column,.n) WITH,.n AS select_statement;WITH CHECK OPTION属性和参数说明：view_name：要修改的视图名称column：列名view_ at

21、tribute：视图的属性select_statement：定义视图的select语句WITH CHECK OPTION：要求对该视图执行的所有数据修改语句都必须符合select_statement中所设置的条件【例7.16】将【例7.11】创建的视图v_Student中的筛选条件改为“所有姓王的女生”。 分析：【例7.11】中创建视图的条件为“姓王的学生”，而本例是将其条件增加一项“女生”。在“查询”窗口中输入以下T-SQL命令：use CJGLgoalter view v_Studentasselect * from Studentwhere 姓名like 王% and 性别=女go通过视

22、图修改数据通过视图修改数据 在使用视图修改数据时，要注意以下一些事项：Q不能在一个语句中对多个基本表使用数据修改语句。如果要修改由两个或两个以上基本表得到的视图，必须实行多次修改，每次修改只能影响一个基表。Q对于基表中需要更新而又不允许空值的所有列，它们的值在INSERT语句或DEFAULT定义中指定。这将确保基表中所有需要值的列都可以获取值。Q不能修改经过计算得到结果的列。Q在视图定义中使用了“WITH CHECK OPTION”子句，则所有在视图上执行的数据修改语句都必须符合定义视图的SELECT语句中所设定的条件。Q在基表的列中修改的数据必须符合对这些列的约束条件，如：是否为空、约束、D

23、EFAULT定义等。（1）更新视图数据达到更改表中数据的目的【例7.18】 将“v_Student”视图中学号为“2007230441”的学生的家庭住址改为“四川成都”在“查询”窗口中输入以下代码：use CJGLgoupdate v_Studentset 住址=四川成都where 学号=2007230441go（2）向视图中插入数据达到向表中插入数据的目的【例7.19】向视图“v_Student”中插入一条新的记录。在“查询”窗口中输入以下代码：use CJGLgoinsert into v_Studentvalues(2007250400,王俊海,男,GIS,湖北襄樊,班长)go 执上页的

24、命令后，刷新该视图就能看到新增的内容；同样刷新Student表，也能看到表中新增加的行，下图：删除视图删除视图使用“对象资源管理器”删除视图使用T-SQL语句删除视图 使用T-SQL命令的DROP VIEW语句删除视图，其基本语法为： DROP VIEW view_name .,n查询视图定义信息查询视图定义信息使用“对象资源管理器”查看视图定义信息 右键单击需要查看的视图，在弹出的列表中选择“修改”命令，打开如下的对话框即可：使用T-SQL命令查看视图的定义信息 用系统提供的存储过程sp_helptext来查看视图的定义信息。 其基本语法格式为：sp_helptext name7.3 本章小结本章小结 本章介绍了索引和视图的作用，什么情况下需要创建索引及索引的分析和维护，以及什么情况下需要创建视图，和对视图的修改等操作。 应该掌握使用对象资源管理器和T-SQL语句创建、修改、删除索引和视图，以及使用系统提供的存储过程查看索引和视图的定义信息。