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

1、任务：任务：了解视图及索引的作用，分析“高职院校教学管理系统”中索引及视图的应用，使用SSMS和T-SQL语句创建、管理“高职院校教学管理系统”中的索引及视图。6.1 创建索引6.2 创建视图6.3 知识进阶 项目实训任务六任务六 优化数据查询和数据管理优化数据查询和数据管理 在SQL Server 2005中应用索引及视图理解索引的概念、分类、作用及使用原则使用SSMS创建、管理索引使用CREATE INDEX语句创建、管理索引理解视图的概念、作用及特点使用SSMS创建、管理视图使用CREATE VIEW语句创建、管理视图 学习目标学习目标在SQL Server 2005中应用索引及视图6.

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

3、x，也称聚类索引、簇集索引）。聚集索引是表数据的物理排序。（2）非聚集索引（Nonclustered Index，也称非聚类索引、非簇集索引）。非聚集索引具有与表数据完全分离的存储结构。索引的优缺点：索引的优缺点：索引的应用原则：索引的应用原则：6.1.1 认识索引创建索引时需要注意以下几点：创建索引时需要注意以下几点：（1）必须是表或视图的所有者才能创建索引，并且无论表中是否已经存在数据。（2）只有定义时使用了参数SCHEMABINDING的视图上才可以创建索引。（3）创建聚集索引时系统会首先对表进行复制，然后对表中的数据进行排序后存储，最后再删除原始的表。因此，创建聚集索引时数据库上必须有

4、足够的空闲空间来容纳数据副本。（4）一个表中最多可以创建249个非聚集索引。（5）复合索引的列最大数目为16个，而且各列组合后的最大长度为900B。创建步骤略。6.1.2 使用SSMS创建和管理索引l提示：在一个表上，主键和唯一索引都可以限制数据的唯一性。但是通过PRIMARY KEY 约束指定的主键只能有一个，而唯一索引却可以有多个在SQL Server 2005中应用索引及视图1使用使用T-SQL语句创建索引语句创建索引可以使用T-SQL的CREATE INDEX语句创建索引。CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX index_nameON t

5、able|view (column ASC|DESC ,.n )WITH ,.n ON filegroup ;:=PAD_INDEX|FILLFACTOR=fillfactor|IGNORE_DUP_KEY|DROP_EXISTING|STATISTICS_NORECOMPUTE|SORT_IN_TEMPDB 6.1.3 使用T-SQL创建和管理索引在SQL Server 2005中应用索引及视图其中：UNIQUE：指明创建的是唯一索引。CLUSTERED|NONCLUSTERED：指明创建的索引为聚集索引还是非聚集索引。系统默认创建的索引为非聚集索引。ASC|DESC：指定特定的索引列的排序

6、方式，默认为升序（ASC）。Column：指定被索引的列。如果使用两个或两个以上的列组成一个索引，则称为复合索引。PAD_INDEX：指定填充索引的内部节点的行数，至少应大于或等于两行。PAD_INDEX 选项只有在FILLFACTOR 选项指定后才起作用。IGNORE_DUP_KEY：指定往包含在唯一约束中的列中插入重复数据时SQL Server 所作的反应。DROP_EXISTING：指定要删除并重新创建聚集索引。ON filegroup：指定存放索引的文件组。2使用系统存储过程查看索引信息使用系统存储过程查看索引信息 通过执行系统存储过程sp_helpindex或sp_help，可以查看

7、数据 表的索引信息。基本语法如下：EXEC sp_helpindex|sp_help table_name【例6-2】查看表student_info中的索引信息。代码如下：sp_helpindex student_infoGOsp_helpindex student_infoGO6.1.3 使用T-SQL创建和管理索引在SQL Server 2005中应用索引及视图3使用系统存储过程更改索引名称使用系统存储过程更改索引名称 使用系统存储过程sp_rename可以更改索引的名称，基本语法如下：EXEC sp_rename old_table_ name,new_table_ name【例6-3】

8、将索引idx_student的名称修改为idx_class,然后再重新修改回来。代码如下：sp_rename student_info.idx_student,student_info.idx_classGOsp_rename student_info.idx_class,student_info.idx_studentGO6.1.3 使用T-SQL创建和管理索引在SQL Server 2005中应用索引及视图4使用使用T-SQL语句删除索引语句删除索引 当不再需要某个索引时，要及时把索引从数据库中删除掉。对因为设置PRIMARY KEY约束或UNIQUE约束时由系统创建的索引，可以通过删除约

9、束的方法删除索引；对于用户手工创建的其他索引，可以使用DROP INDEX语句删除，基本语法如下：DROP INDEX table_name.index_name,n【例6-4】将表student_info中的索引idx_student_class删除。代码如下：DROP INDEX student_info.idx_student_classGO 6.1.3 使用T-SQL创建和管理索引在SQL Server 2005中应用索引及视图在主键列上建立索引在外键列上建立索引在经常查询的字段上建立索引数据行数很少的表不建立索引很少或从来不在查询中作为条件引用的列不要建立索引只有很少取值的列不建立索

10、引定义为text、image和bit数据类型的列不建立索引6.1.4 在“高职院校教学管理系统”中应用索引在SQL Server 2005中应用索引及视图6.2 创建视图 任务：任务：理解视图的概念和作用；了解视图的分类和通过视图进行数据修改的限制。分析“高职院校教学管理系统”中视图的应用，使用SSMS和T-SQL创建、管理“高职院校教学管理系统”中的视图。6.1.1 认识视图6.1.2 使用SSMS创建和管理视图6.1.3 使用T-SQL创建和管理视图6.1.4 在“高职院校教学管理系统”中应用视图在SQL Server 2005中应用索引及视图视图的概念：视图的概念：视图是一张从一个或多个

11、数据表或视图中导出的虚拟表，生成视图的表称为视图的基表。视图的分类：视图的分类：（1）标准视图。标准视图是最常用的普通视图。（2）分区视图。分区视图与标准视图相似，只是分区视图在一个或多个服务器间水平连接一组成员表中的分区数据，使数据看起来就像来自一个表。（3）索引视图。在SQL Server 2005中，可以在视图上创建唯一的聚集索引和多个非聚集索引，改进最复杂查询的数据访问性能。这种拥有一个聚集索引的视图被称为索引视图。视图的作用：视图的作用：简单性；安全性；逻辑数据独立性。6.2.1 认识视图在SQL Server 2005中应用索引及视图创建步骤略。6.2.2 使用SSMS创建和管理视

12、图在SQL Server 2005中应用索引及视图使用使用T-SQL语句创建视图：语句创建视图：基本语法如下：CREATE VIEW database_name.owner.view_name(column,.n)WITH,.n AS select_statement WITH CHECK OPTION;:=ENCRYPTION|SCHEMABINDING 6.2.3 使用T-SQL创建和管理视图其中：其中：view_name：视图的名称。column：视图中各个列的字段名称。AS：指定视图要执行的操作。select_statement：视图定义的 SELECT 语句。WITH CHECK O

13、PTION：强制针对视图执行的所有数据修改都必须符合在 select_statement 中设置的条件。ENCRYPTION：对 sys.syscomments 表中包含 CREATE VIEW 语句文本的项进行加密。使用 WITH ENCRYPTION 可防止在 SQL Server复制过程中发布视图。SCHEMABINDING：将视图绑定到基础表的架构chema。在SQL Server 2005中应用索引及视图【例6-6】创建一个新视图v_student_score，该视图包含“学号”、“姓名”、“班级”、“课程号”和“考试成绩”。数据来源于表student_info和表student_g

14、rade的连接查询结果集。代码如下：CREATE VIEW v_student_score(stud_id,stud_name,class_id,course_id,grade)AS(SELECT a.stud_id,a.stud_name,a.class_id,b.course_id,b.grade FROM student_info a join student_grade b ON a.stud_id=b.stud_id )GO6.2.3 使用T-SQL创建和管理视图在SQL Server 2005中应用索引及视图【例6-7】创建一个视图v_person_list，使用户可以看到所有学生

15、 和老师的“编号”、“姓名”和人员类型（“学生”或“教师”）。数据来源于表student_info和表teacher_info。代码如下：CREATE VIEW v_person_listAS(SELECT stud_id AS id,stud_name AS name,N学生 AS person_type FROM student_infoUNION SELECT teac_id AS id,teac_name AS name,N教师 AS person_type FROM teacher_info )GO6.2.3 使用T-SQL创建和管理视图在SQL Server 2005中应用索引及视

16、图2使用存储过程查看视图定义使用存储过程查看视图定义 对于已经创建的视图，为了修改视图的定义或了解视图是从哪些基表中按照什么条件得到数据时，需要查看视图的定义。除了使用SSMS外，可以使用系统存储过程sp_helptext获得视图定义的有关信息。基本语法如下：EXEC sp_helptext object_name其中，object_name为需要查看定义的视图名称。【例6-8】使用存储过程查看v_person_list的详细定义信息。代码如下：EXEC sp_helptext v_person_listGO6.2.3 使用T-SQL创建和管理视图在SQL Server 2005中应用索引及视

17、图3使用使用T-SQL修改视图修改视图使用T-SQL中的ALTER VIEW语句修改视图定义，基本语法如下：ALTER VIEW database_name.owner.view_name(column,.n)AS select_statement 其中各参数的含义与CREATE VIEW语句中的参数含义一致。6.2.3 使用T-SQL创建和管理视图提示：提示：对于没有指定WITH ENCRYPTION参数加密定义的视图，除了可以通过SSMS修改外，还可以使用T-SQL语句中的ALTER VIEW命令进行修改；而对于加密的视图则只能通过T-SQL语句修改。如果只是需要对视图的名称进行修改，可以

18、直接通过系统存储过程sp_rename对视图重新命名。在SQL Server 2005中应用索引及视图4使用使用T-SQL删除视图删除视图使用T-SQL的DROP VIEW语句可以删除不再使用的视图。删除视图后，其对应的基表中的数据并不受任何影响。基本语法：DROP VIEW database_name.owner.view_name.,n ;6.2.3 使用T-SQL创建和管理视图提示：提示：使用DROP VIEW语句可以同时删除多个视图，视图名称之间用逗号“，”隔开即可。在SQL Server 2005中应用索引及视图5通过视图查询数据通过视图查询数据定义视图后，可以通过视图进行数据查询。

19、【例6-11】通过视图v_student_list查询学生的基本信息。SELECT*FROM v_student_listGO【例6-12】通过视图v_student_list和表student_grade关联查询得到学生的“学号”、“姓名”、“课程编号”和“考试成绩”，并且只显示成绩在80分以上的记录。代码如下：SELECT a.stud_id,a.stud_name,b.course_id,b.gradeFROM v_student_list a join student_grade b ON a.stud_id=b.stud_idWHERE b.grade=80GO6.2.3 使用T-S

20、QL创建和管理视图在SQL Server 2005中应用索引及视图6通过视图修改数据通过视图修改数据（1）只能引用一个基表的列，不能在一个语句中同时对分属不同基表的列数据进行修改。如果要修改由多个基表定义的视图数据，则必须进行多次修改，每次修改只能影响一个基表。（2）通过视图中修改的列必须是直接引用基表中的列。对于通过使用集合函数得到，或使用表达式由多个字段得到的列，不能进行修改操作。（3）如果在视图定义中使用了 WITH CHECK OPTION 子句，则所有在视图上执行的UPDATE操作都必须符合定义视图的SELECT 语句中所设置的条件。（4）通过视图修改数据时，必须符合基表中对这些列的

21、约束条件，如数据类型、是否为空、外键约束等。6.2.3 使用T-SQL创建和管理视图在SQL Server 2005中应用索引及视图通过视图屏蔽对所有数据的查看通过修改视图数据，简化数据库中数据的修改通过查询视图来查看需要的数据通过视图修改基本表中的数据并保证基表中的数据与视图中要求的数据一致6.2.4 在“高职院校教学管理系统”中应用视图在SQL Server 2005中应用索引及视图6.3 6.3 知识进阶知识进阶全文索引全文索引 任务：任务：了解全文索引的概念，区分全文索引和普通索引，创建和使用全文索引。在SQL Server 2005中应用索引及视图全文索引全文索引1全文索引的概念全文

22、索引的概念 全文索引为在字符串数据中进行复杂的词搜索提供有效支持。全文索引存储关于重要词和这些词在特定列中的位置的信息。全文索引利用这些信息，可快速搜索包含具体某个词或一组词的行。全文索引包含在全文目录中；全文目录和索引不存储在它们所属的数据库中。2全文索引和普通索引的区别全文索引和普通索引的区别普通索引普通索引 全文索引全文索引 存储时受定义它们所在的数据库的控制存储在文件系统中，但通过数据库管理每个表允许有若干个普通索引每个表只允许有一个全文索引当对作为其基础的数据进行插入、更新或删除时，它们会自动更新将数据添加到全文索引称为填充，全文索引可通过调度或特定请求来更新，也可以在添加新数据时自

23、动发生不分组在同一个数据库内分组为一个或多个全文目录使用SSMS、向导或T-SQL语句创建和删除使用SSMS、向导或存储过程创建、管理、删除在SQL Server 2005中应用索引及视图全文索引全文索引3全文索引的启用和创建 在MS SQL SERVER 2005里，全文索引是一个单独的服务项，默认 是启动的。如果要在某个数据库中创建全文索引，先要启用数据库的全文索引。方法是：运行SSMS，在具体的数据库节点上右击，选择快捷菜单中的“属性”命令，在弹出窗口的“文件”选项卡中选中“启用全文索引”复选框即可。使用T-SQL创建全文索引的基本语法为CREATE FULLTEXT INDEX ON

24、tablename(索引列1,索引列2.)KEY INDEX indexname ON 索引目录名 4全文索引的使用 主要是通过在SELECT语句中使用CONSTAINT，FREETEXT进行条件匹配查询，来让全文索引提高查询速度。当然，也可以使用匹配模式进行包含条件组合，还可以使用and连接条件。在SQL Server 2005中应用索引及视图项目实训项目实训 1实训题目实训题目在“销售合同管理系统”数据库中应用索引及视图。2实训目的实训目的（1）认识索引，了解索引的用途。（2）使用SSMS和T-SQL创建和管理索引。（3）了解视图的优点，使用SSMS和T-SQL创建和管理视图。3实训内容实训内容（1）使用SSMS查看“销售合同管理系统”数据库表中的索引。（2）分别使用SSMS和T-SQL为CONTRACT_PRODUCTS的开始日期（START_DATE）字段创建索引。（3）创建视图。在SQL Server 2005中应用索引及视图