《SQL Server数据库应用技术实例教程》课件任务六

任务：了解视图及索引的作用，分析“高职院校教学管理系统”中索引及视图的应用，使用SSMS和T-SQL语句创建、管理“高职院校教学管理系统”中的索引及视图。6.1创建索引6.2创建视图6.3知识进阶项目实训任务六优化数据查询和数据管理

—在SQLServer2005中应用索引及视图理解索引的概念、分类、作用及使用原则使用SSMS创建、管理索引使用CREATEINDEX语句创建、管理索引理解视图的概念、作用及特点使用SSMS创建、管理视图使用CREATEVIEW语句创建、管理视图学习目标在SQLServer2005中应用索引及视图6.1创建索引任务：理解索引的概念、优缺点及分类；掌握索引的应用原则；分析“高职院校教学管理系统”中索引的应用，使用SSMS和T-SQL创建、管理“高职院校教学管理系统”中的索引。6.1.1认识索引6.1.2使用SSMS创建和管理索引6.1.3使用T-SQL创建和管理索引6.1.4在“高职院校教学管理系统”中应用索引在SQLServer2005中应用索引及视图索引的概念：数据库中的索引是一个数据列表，这个列表包含某个表中的一列或若干列的值的有序集合，并记录与这些值相对应的数据在表中存储的物理地址。索引的分类：

（1）聚集索引（ClusteredIndex，也称聚类索引、簇集索引）。聚集索引是表数据的物理排序。（2）非聚集索引（NonclusteredIndex，也称非聚类索引、非簇集索引）。非聚集索引具有与表数据完全分离的存储结构。索引的优缺点：索引的应用原则：6.1.1认识索引在SQLServer2005中应用索引及视图创建索引时需要注意以下几点：（1）必须是表或视图的所有者才能创建索引，并且无论表中是否已经存在数据。（2）只有定义时使用了参数SCHEMABINDING的视图上才可以创建索引。（3）创建聚集索引时系统会首先对表进行复制，然后对表中的数据进行排序后存储，最后再删除原始的表。因此，创建聚集索引时数据库上必须有足够的空闲空间来容纳数据副本。（4）一个表中最多可以创建249个非聚集索引。（5）复合索引的列最大数目为16个，而且各列组合后的最大长度为900B。创建步骤略。6.1.2使用SSMS创建和管理索引提示：在一个表上，主键和唯一索引都可以限制数据的唯一性。但是通过PRIMARYKEY约束指定的主键只能有一个，而唯一索引却可以有多个在SQLServer2005中应用索引及视图1．使用T-SQL语句创建索引可以使用T-SQL的CREATEINDEX语句创建索引。CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_nameON{table|view}(column[ASC|DESC][,...n])[WITH<index_option>[,...n]][ONfilegroup][;]<index_option>::=PAD_INDEX|FILLFACTOR=fillfactor|IGNORE_DUP_KEY|DROP_EXISTING|STATISTICS_NORECOMPUTE|SORT__IN__TEMPDB6.1.3使用T-SQL创建和管理索引在SQLServer2005中应用索引及视图其中：UNIQUE：指明创建的是唯一索引。CLUSTERED|NONCLUSTERED：指明创建的索引为聚集索引还是非聚集索引。系统默认创建的索引为非聚集索引。ASC|DESC：指定特定的索引列的排序方式，默认为升序（ASC）。Column：指定被索引的列。如果使用两个或两个以上的列组成一个索引，则称为复合索引。PAD_INDEX：指定填充索引的内部节点的行数，至少应大于或等于两行。PAD_INDEX选项只有在FILLFACTOR选项指定后才起作用。IGNORE_DUP_KEY：指定往包含在唯一约束中的列中插入重复数据时SQLServer所作的反应。DROP_EXISTING：指定要删除并重新创建聚集索引。ONfilegroup：指定存放索引的文件组。2．使用系统存储过程查看索引信息通过执行系统存储过程sp_helpindex或sp_help，可以查看数据表的索引信息。基本语法如下：

[EXEC]{sp_helpindex|sp_help}table_name【例6-2】查看表student_info中的索引信息。代码如下：sp_helpindexstudent_infoGOsp_helpindex'student_info'GO6.1.3使用T-SQL创建和管理索引在SQLServer2005中应用索引及视图3．使用系统存储过程更改索引名称使用系统存储过程sp_rename可以更改索引的名称，基本语法如下：

[EXEC]sp_rename'old_table_name','new_table_name'【例6-3】将索引idx_student的名称修改为idx_class,然后再重新修改回来。代码如下：sp_rename'student_info.idx_student','student_info.idx_class'GOsp_rename'student_info.idx_class','student_info.idx_student'GO6.1.3使用T-SQL创建和管理索引在SQLServer2005中应用索引及视图4．使用T-SQL语句删除索引当不再需要某个索引时，要及时把索引从数据库中删除掉。对因为设置PRIMARYKEY约束或UNIQUE约束时由系统创建的索引，可以通过删除约束的方法删除索引；对于用户手工创建的其他索引，可以使用DROPINDEX语句删除，基本语法如下：

DROPINDEXtable_name.index_name[,…n]【例6-4】将表student_info中的索引idx_student_class删除。代码如下：DROPINDEXstudent_info.idx_student_classGO6.1.3使用T-SQL创建和管理索引在SQLServer2005中应用索引及视图在主键列上建立索引在外键列上建立索引在经常查询的字段上建立索引数据行数很少的表不建立索引很少或从来不在查询中作为条件引用的列不要建立索引只有很少取值的列不建立索引定义为text、image和bit数据类型的列不建立索引6.1.4在“高职院校教学管理系统”中应用索引在SQLServer2005中应用索引及视图6.2创建视图任务：理解视图的概念和作用；了解视图的分类和通过视图进行数据修改的限制。分析“高职院校教学管理系统”中视图的应用，使用SSMS和T-SQL创建、管理“高职院校教学管理系统”中的视图。

6.1.1认识视图6.1.2使用SSMS创建和管理视图6.1.3使用T-SQL创建和管理视图6.1.4在“高职院校教学管理系统”中应用视图在SQLServer2005中应用索引及视图视图的概念：视图是一张从一个或多个数据表或视图中导出的虚拟表，生成视图的表称为视图的基表。视图的分类：

（1）标准视图。标准视图是最常用的普通视图。（2）分区视图。分区视图与标准视图相似，只是分区视图在一个或多个服务器间水平连接一组成员表中的分区数据，使数据看起来就像来自一个表。（3）索引视图。在SQLServer2005中，可以在视图上创建唯一的聚集索引和多个非聚集索引，改进最复杂查询的数据访问性能。这种拥有一个聚集索引的视图被称为索引视图。视图的作用：简单性；安全性；逻辑数据独立性。6.2.1认识视图在SQLServer2005中应用索引及视图创建步骤略。6.2.2使用SSMS创建和管理视图在SQLServer2005中应用索引及视图使用T-SQL语句创建视图：基本语法如下：CREATEVIEW[database_name.][owner.]view_name[(column[,...n])][WITH<view_attribute>[,...n]]ASselect_statement[WITHCHECKOPTION][;]

<view_attribute>::={ENCRYPTION|SCHEMABINDING}6.2.3使用T-SQL创建和管理视图其中：view_name：视图的名称。column：视图中各个列的字段名称。AS：指定视图要执行的操作。select_statement：视图定义的SELECT语句。WITHCHECKOPTION：强制针对视图执行的所有数据修改都必须符合在select_statement中设置的条件。ENCRYPTION：对sys.syscomments

表中包含CREATEVIEW语句文本的项进行加密。使用WITHENCRYPTION可防止在SQLServer复制过程中发布视图。SCHEMABINDING：将视图绑定到基础表的架构chema。在SQLServer2005中应用索引及视图【例6-6】创建一个新视图v_student_score，该视图包含“学号”、“姓名”、“班级”、“课程号”和“考试成绩”。数据来源于表student_info和表student_grade的连接查询结果集。代码如下：CREATEVIEWv_student_score(stud_id,stud_name,class_id,course_id,grade)AS(SELECTa.stud_id,a.stud_name,a.class_id,b.course_id,b.gradeFROMstudent_infoajoinstudent_gradebONa.stud_id=b.stud_id)GO6.2.3使用T-SQL创建和管理视图在SQLServer2005中应用索引及视图【例6-7】创建一个视图v_person_list，使用户可以看到所有学生和老师的“编号”、“姓名”和人员类型（“学生”或“教师”）。数据来源于表student_info和表teacher_info。代码如下：CREATEVIEWv_person_listAS(SELECTstud_idASid,stud_nameASname,N'学生'ASperson_typeFROMstudent_infoUNIONSELECTteac_idASid,teac_nameASname,N'教师'ASperson_typeFROMteacher_info)GO6.2.3使用T-SQL创建和管理视图在SQLServer2005中应用索引及视图2．使用存储过程查看视图定义对于已经创建的视图，为了修改视图的定义或了解视图是从哪些基表中按照什么条件得到数据时，需要查看视图的定义。除了使用SSMS外，可以使用系统存储过程sp_helptext获得视图定义的有关信息。基本语法如下：[EXEC]sp_helptextobject_name其中，object_name为需要查看定义的视图名称。【例6-8】使用存储过程查看v_person_list的详细定义信息。代码如下：EXECsp_helptextv_person_listGO6.2.3使用T-SQL创建和管理视图在SQLServer2005中应用索引及视图3．使用T-SQL修改视图使用T-SQL中的ALTERVIEW语句修改视图定义，基本语法如下：ALTERVIEW[database_name.][owner.]view_name[(column[,...n])]ASselect_statement其中各参数的含义与CREATEVIEW语句中的参数含义一致。6.2.3使用T-SQL创建和管理视图提示：对于没有指定WITHENCRYPTION参数加密定义的视图，除了可以通过SSMS修改外，还可以使用T-SQL语句中的ALTERVIEW命令进行修改；而对于加密的视图则只能通过T-SQL语句修改。如果只是需要对视图的名称进行修改，可以直接通过系统存储过程sp_rename对视图重新命名。在SQLServer2005中应用索引及视图4．使用T-SQL删除视图使用T-SQL的DROPVIEW语句可以删除不再使用的视图。删除视图后，其对应的基表中的数据并不受任何影响。基本语法：

DROPVIEW[database_name.][owner.]view_name[...,n][;]6.2.3使用T-SQL创建和管理视图提示：使用DROPVIEW语句可以同时删除多个视图，视图名称之间用逗号“，”隔开即可。在SQLServer2005中应用索引及视图5．通过视图查询数据定义视图后，可以通过视图进行数据查询。【例6-11】通过视图v_student_list查询学生的基本信息。SELECT*FROMv_student_listGO【例6-12】通过视图v_student_list和表student_grade关联查询得到学生的“学号”、“姓名”、“课程编号”和“考试成绩”，并且只显示成绩在80分以上的记录。代码如下：SELECTa.stud_id,a.stud_name,b.course_id,b.gradeFROMv_student_listajoinstudent_gradebONa.stud_id=b.stud_idWHEREb.grade>=80GO6.2.3使用T-SQL创建和管理视图在SQLServer2005中应用索引及视图6．通过视图修改数据（1）只能引用一个基表的列，不能在一个语句中同时对分属不同基表的列数据进行修改。如果要修改由多个基表定义的视图数据，则必须进行多次修改，每次修改只能影响一个基表。（2）通过视图中修改的列必须是直接引用基表中的列。对于通过使用集合函数得到，或使用表达式由多个字段得到的列，不能进行修改操作。（3）如果在视图定义中使用了WITHCHECKOPTION子句，则所有在视图上执行的UPDATE操作都必须符合定义视图的SELECT语句中所设置的条件。（4）通过视图修改数据时，必须符合基表中对这些列的约束条件，如数据类型、是否为空、外键约束等。6.2.3使用T-SQL创建和管理视图在SQLServer2005中应用索引及视图通过视图屏蔽对所有数据的查看通过修改视图数据，简化数据库中数据的修改通过查询视图来查看需要的数据通过视图修改基本表中的数据并保证基表中的数据与视图中要求的数据一致6.2.4在“高职院校教学管理系统”中应用视图在SQLServer2005中应用索引及视图6.3知识进阶——全文索引任务：了解全文索引的概念，区分全文索引和普通索引，创建和使用全文索引。在SQLServer2005中应用索引及视图全文索引1．全文索引的概念全文索引为在字符串数据中进行复杂的词搜索提供有效支持。全文索引存储关于重要词和这些词在特定列中的位置的信息。全文索引利用这些信息，可快速搜索包含具体某个词或一组词的行。全文索引包含在全文目录中；全文目录和索引不存储在它们所属的数据库中。2．全文索引和普通索引的区别普通索引全文索引存储时受定义它们所在的数据库的控制存储在文件系统中，但通过数据库管理每个表允许有若干个普通索引每个表只允许有一个全文索引当对作为其基础的数据进行插入、更新或删除时，它们会自动更新将数据添加到全文索引称为填充，全文索引可通过调度或特定请求来更新，也可以在添加新数据时自动发生不分组在同一个数据库内分组为一个或多个全文目录使用SSMS、向导或T-S