《《SQL Server数据库应用与开发》-李德有 彭德林-电子教案 SQL 2005第4章》由会员分享，可在线阅读，更多相关《《SQL Server数据库应用与开发》-李德有 彭德林-电子教案 SQL 2005第4章（50页珍藏版）》请在金锄头文库上搜索。
1、第4章 索引及数据完整性,4.1 索引,4.3 SQL SERVER数据完整性,4.2 全文索引,主要内容,索引类型、数据完整性的分类 创建索引的方法 掌握索引的维护 各种约束及默认值 数据完整性的实现,4.1.1 索引的简介及分类,索引是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。它提供了数据库中编排表中数据的内部方法。 通常情况下一个表的存储是由两部分组成的，一部分用来存放表的数据页面，另一部分存放索引页面。索引就存放在索引页面上，通常，索引页面相对于数据页面来说小得多。,唯一索引和非唯一索引 聚集索引和非聚集索引 单列索引、复合索引和包含性列索引 视
2、图索引 全文索引,4.1.2 索引的分类,4.1.2 创建索引的注意事项,1.要合理的建立索引，而不要认为索引越多越好， 否则不仅达不到提高性能的目的，反而会适得其反 2.每个表只能有一个聚簇(聚集)索引 3.聚集索引适合于检索连续键值,表4.1 合理使用聚集索引和非聚集索引,4.1.3 创建索引,创建索引的方法有直接和间接的方法 1.直接方法 2.间接方法,4.1.3.1 间接创建索引,1. 主键索引（Primary Key约束） 1）打开SQL Server Management Studio，连接到本地数据库实例，在【对象资源管理器】中展开目录树“数据库”|“xuesheng”|“表”|
3、“学生”，单击鼠标右键，选择【修改】命令，系统打开表设计器窗口。 2）在表设计器中，选择“学号”字段，单击【表设计器】工具栏中的【设置主键】工具，则在字段“学号”字段的前面多了一个小钥匙的图标。如图4.1所示。,图4.1 利用设置主键间接创建索引,2. 唯一约束（Unique约束） 例4-1：创建一个成绩表，其中包含主键和Unique约束，创建后查看其索引信息。其相应的SQL命令如下： USE xuesheng CREATE TABLE 成绩 ( 学号 INT NOT NULL, 课程号 NUMERIC UNIQUE, /*唯一约束*/ 成绩 NUMERIC ),4.1.3.2 在SQL Se
4、rver Management Studio中创建索引,1打开“SQL Server Management Studio”窗口，连接到本地实例，在【对象资源管理器】窗口中，依次展开XUEXIAO-6BNZNGY|数据库|xuesheng|索引的树型目录。 2右击“索引”，在弹开的快捷菜单中选择【新建索引】命令，如图4.5所示。,图4.5 直接方法创建索引,4.1.3.3 用Create index 语句创建索引,CREATE UNIQUE CLUSTERED| NONCLUSTERED INDEX index_name ON table | view ( column ASC | DESC ,
5、.n ) withPAD_INDEX ,FILLFACTOR=fillfactor ,IGNORE_DUP_KEY ,DROP_EXISTING ,STATISTICS_NORECOMPUTE ,SORT_IN_TEMPDB ON filegroup ,CREATE INDEX命令创建索引各参数说明如下： UNIQUE：用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行。 CLUSTERED：用于指定创建的索引为聚集索引。 NONCLUSTERED：用于指定创建的索引为非聚集索引。 ON:表示可以在表或视图上创建索引,这里指定表或视图的名称和相应的列名称。 index_name：用于
6、指定所创建的索引的名称。 table：用于指定创建索引的表的名称。 view：用于指定创建索引的视图的名称。 ASC|DESC：用于指定具体某个索引列的升序或降序排序方向。,Column：用于指定被索引的列。 PAD_INDEX：为非叶级索引页指定填充度。 FILLFACTOR = fillfactor：用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor的值为1到100。 IGNORE_DUP_KEY：指定出先冗余数据的系统行为。 DROP_EXISTING：用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引。 STATISTICS_NORECOMP
7、UTE：用于指定过期的索引统计不会自动重新计算。 SORT_IN_TEMPDB：用于指定创建索引时的中间排序结果将存储在 tempdb 数据库中。 ON filegroup：用于指定存放索引的文件组。,例4-2: 为表“学生”中字段“学号”创建一聚集索引。 use xuesheng IF EXISTS(SELECT name from sysindexes where name=“PK_XH“) DROP INDEX xuesheng.PK_XH GO CREATE INDEX PK_XH ON 学生(学号),例4-3: 为表“学生”中 字段“姓名”创建了一个唯一聚集索引，要求使用FILLFA
8、CTOR子句。 use xuesheng IF EXISTS(SELECT name from sysindexes where name=“XM_索引“) DROP INDEX xuesheng.XM_索引 GO CREATE UNIQUE CLUSTERED INDEX XM_索引 ON 学生(姓名) with pad_index, fillfactor=20, ignore_dup_key, drop_existing, statistics_norecompute,4.1.4 查看修改索引,1. 使用SQL Server Management Studio查看修改索引 2. 利用T-S
9、QL查看修改索引 例4-8: 查看学生表中的所有索引信息，其代码如下： Exec sp_helpindex 学生,4.1.5 重新生成索引,例4-10: 重新生成xuesheng数据库中的学生表里的PK_学生，并设置索引填充，填充因子为60，其代码如下： ALTER INDEX PK_学生 ON 学生 REBUILD WITH (PAD_INDEX =ON, FILLFACTOR=60),4.1.6 禁用索引,1.利用SQL Server Management Studio禁用索引 2.使用T-SQL语句中的DISABLE参数禁用索引 例4-12: 禁用学生表中的XM_索引这一唯一索引。并查看
10、学生表信息。 ALTER INDEX XM_索引 ON 学生 DISABLE GO 运行后，再次使用select * from 学生，则可以显示数据表中信息。,4.1.7 删除索引,利用T-SQL的DROP INDEX语句删除索引 例4-13: 将成绩表中的课程号字段的UQ_成绩_023D5A04删除。 USE xuesheng GO DROP INDEX 成绩.UQ_成绩_023D5A04,4.1.8 视图索引,例4-15: 创建一视图，并为该视图建立聚集索引，然后用索引视图检索数据。 USE xuesheng GO SET ANSI_PADDING,ANSI_NULLS ON GO CRE
11、ATE VIEW xuesheng_VIEW /*建立视图名为xuesheng_VIEW*/ WITH SCHEMABINDING AS SELECT 姓名,学号 FROM DBO.学生 GO,SET NUMERIC_ROUNDABORT OFF GO SET ARITHABORT,ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ON图4.18 运行结果 GO CREATE UNIQUE CLUSTERED INDEX VIEW_学生 ON 学生(学号,姓名) |*在视图上建立聚集索引*| GO SELECT * FROM xuesheng
12、_view GO 运行结果如图4.18所示。,图4.18 运行结果,表4.3 常规索引与全文索引比较,4.2 全文索引,全文索引包含在全文索引目录中（Full-Text Catalogs），通常是由同一数据库中的一个或多个表的全文索引构成一个全文索引目录。一个表只能有一个全文索引，因此每个有全文索引的表只隶属于一个全文索引目录。,4.2.1 全文索引介绍,全文目录是存储全文索引的地方，全文目录必须保存在与SQL Server实例相关联的本地硬盘上，每个全文目录可以用于满足数据库内的一个或多个表的索引需求。要想创建全文索引，首先必须创建全文目录。,4.2.2 全文目录管理,4.2.2.1 启动全
13、文搜索,1.选择windows操作系统的【管理工具】|【服务】中，对【SQL Server Fulltext Search】进行启动。如图4.19所示。,图4.19 利用windows启动全文搜索,2.可以直接在SQL Server 2005中依次选择【管理】|【全文搜索】，单击右键选择【启动】命令即可。如教材中图4.20所示。 3.利用T-SQL命令实现 对应的T-SQL语句命令格式如下： sp_fulltext_database action=action 其中 action= action表示将要执行的操作。action 的数据类型为 varchar(20)，可以是enable 和dis
14、able两个值。其中enable表示在当前数据库中启用全文索引；disable表示对于当前数据库中，删除文件系统中所有的全文目录，并且将该数据库标记为已经禁用全文索引。此操作并不在全文目录或表级上更改任何全文索引元数据。,4.2.2.2 创建全文目录,在SQL Server Management Studio创建全文索引目录 使用T-SQL语句创建全文目录 创建全文目录的T-SQL语句格式为： CREATE FULLTEXT CATALOG catalog_name /*全文目录名称/ ON FILLEGROUP filegroup /*包含全文目录的文件组名默认是主文件组*/ IN PATH
15、 rootpath /*全文目录的路径*/ WITH /*指定将应用于该目录的选项*/ AS DEFAULT /*指定该全文目录为默认目录*/ AUTHORIZATION owner_name /*将全文目录的所有者设为数据库用户名或角色的名称*/,4.2.3 创建全文索引,例4-18 为“学生”表的“姓名”、“家庭住址”2个字段创建全文索引，其代码如下： CREATE FULLTEXT INDEX ON 学生（姓名，家庭住址 TYPE COLUMN扩展名） KEY INDEX PK_学生 ON SQL全文目录,4.2.4 使用全文搜索查询,在一个表中创建了全文索引后，才可以对表中的数据进行全
16、文检索。进行全文检索需要在SELECT命令的WHERE 字句中使用Transact-SQL 谓词：CONTAINS 和FRETEXT。,4.2.4.1 使用CONTAINS搜索,简单词的搜索方式 简单词就是搜索一个或多个特定的词或短语。 例4-19: 搜索学生表中的家庭住址中含有“黑龙江省”的记录，其代码如下： SELECT * FROM 学生 WHERE CONTAINS (家庭住址,黑龙江省) 前缀词的搜索方式 例4-20: 搜索学生表中的家庭住址中含有“上海”开头的单词的记录，其代码如下： SELECT * FROM 学生 WHERE CONTAINS (家庭住址, “ 上海*“) 其中*只能放在英文字母之后，代表一个或多个字符。,邻近词的搜索方式 例4-21: 搜索学生表中家庭住址中与黑龙江省考生相关的记录。 use xuesheng SELECT * F
