索引及数据完整性41索引ppt课件

1、第第4章章 索引及数据完好性索引及数据完好性4.1 索引 4.3 SQL SERVER数据完好性4.2 全文索引全文索引主要内容主要内容 v索引类型、数据完好性的分类索引类型、数据完好性的分类v创建索引的方法创建索引的方法v掌握索引的维护掌握索引的维护v各种约束及默许值各种约束及默许值v数据完好性的实现数据完好性的实现4.1.1 索引的简介及分类索引的简介及分类 索引是某个表中一列或假设干列值的集合和相应的指索引是某个表中一列或假设干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。它提供向表中物理标识这些值的数据页的逻辑指针清单。它提供了数据库中编排表中数据的内部方法。了数据库

2、中编排表中数据的内部方法。 通常情况下一个表的存储是由两部分组成的，一部分通常情况下一个表的存储是由两部分组成的，一部分用来存放表的数据页面，另一部分存放索引页面。索引就用来存放表的数据页面，另一部分存放索引页面。索引就存放在索引页面上，通常，索引页面相对于数据页面来说存放在索引页面上，通常，索引页面相对于数据页面来说小得多。小得多。 独一索引和非独一索引独一索引和非独一索引聚集索引和非聚集索引聚集索引和非聚集索引单列索引、复合索引和包含性列索引单列索引、复合索引和包含性列索引视图索引视图索引全文索引全文索引 4.1.2 索引的分类索引的分类4.1.2 创建索引的本卷须知创建索引的本卷须知1.

3、要合理的建立索引，而不要以为索引越多越好，要合理的建立索引，而不要以为索引越多越好，否那么不仅达不到提高性能的目的，反而会适得其反否那么不仅达不到提高性能的目的，反而会适得其反 2.每个表只能有一个聚簇每个表只能有一个聚簇(聚集聚集)索引索引 3.聚集索引适宜于检索延续键值聚集索引适宜于检索延续键值 表4.1 合理运用聚集索引和非聚集索引 4.1.3 创建索引创建索引创建索引的方法有直接和间接的方法创建索引的方法有直接和间接的方法 1.直接方法直接方法2.间接方法间接方法4.1.3.1 间接创建索引间接创建索引1. 主键索引主键索引Primary Key约束约束1翻开翻开SQL Server

4、Management Studio，衔接到，衔接到本地数据库实例，在【对象资源管理器】中展开目本地数据库实例，在【对象资源管理器】中展开目录树录树“数据库数据库|“xuesheng|“表表|“学生，单学生，单击鼠标右键，选择【修正】命令，系统翻开表设计击鼠标右键，选择【修正】命令，系统翻开表设计器窗口。器窗口。2在表设计器中，选择在表设计器中，选择“学号字段，单击【表设学号字段，单击【表设计器】工具栏中的【设置主键】工具，那么在字段计器】工具栏中的【设置主键】工具，那么在字段“学号字段的前面多了一个小钥匙的图标。如图学号字段的前面多了一个小钥匙的图标。如图4.1所示。所示。 图4.1 利用设置

5、主键间接创建索引2. 独一约束独一约束Unique约束约束例例4-1：创建一个成果表，其中包含主键：创建一个成果表，其中包含主键和和Unique约束，创建后查看其索引信息。约束，创建后查看其索引信息。其相应的其相应的SQL命令如下：命令如下：USE xueshengCREATE TABLE 成果成果( 学号学号 INT NOT NULL,课程号课程号 NUMERIC UNIQUE, /*独一约束独一约束*/成果成果 NUMERIC )4.1.3.2 在在SQL Server Management Studio中创建索引中创建索引1翻开“SQL Server Management Studio窗

6、口，衔接到本地实例，在【对象资源管理器】窗口中，依次展开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 ,.n ) withPAD_INDEX,FILLFACTOR=fillfactor,IGNORE_DUP_KEY,DROP

7、_EXISTING,STATISTICS_NORECOMPUTE,SORT_IN_TEMPDB ON filegroup CREATE INDEX命令创建索引各参数阐明如下： UNIQUE：用于指定为表或视图创建独一索引，即不允许存在索引值一样的两行。 CLUSTERED：用于指定创建的索引为聚集索引。 NONCLUSTERED：用于指定创建的索引为非聚集索引。ON:表示可以在表或视图上创建索引,这里指定表或视图的称号和相应的列称号。index_name：用于指定所创建的索引的称号。 table：用于指定创建索引的表的称号。 view：用于指定创建索引的视图的称号。 ASC|DESC：用于指定

8、详细某个索引列的升序或降序排序方向。 Column：用于指定被索引的列。PAD_INDEX：为非叶级索引页指定填充度。FILLFACTOR = fillfactor：用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor的值为1到100。IGNORE_DUP_KEY：指定出先冗余数据的系统行为。 DROP_EXISTING：用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引。 STATISTICS_NORECOMPUTE：用于指定过期的索引统计不会自动重新计算。 SORT_IN_TEMPDB：用于指定创建索引时的中间排序结果将存储在 tempdb 数据库

9、中。 ON filegroup：用于指定存放索引的文件组。例4-2: 为表“学生中字段“学号创建一聚集索引。 use xueshengIF EXISTS(SELECT name from sysindexes where name=PK_XH)DROP INDEX xuesheng.PK_XH GOCREATE INDEX PK_XH ON 学生(学号)例4-3: 为表“学生中 字段“姓名创建了一个独一聚集索引，要求运用FILLFACTOR子句。use xueshengIF EXISTS(SELECT name from sysindexes where name=XM_索引) DROP IN

10、DEX xuesheng.XM_索引GOCREATE UNIQUE CLUSTERED INDEX XM_索引 ON 学生(姓名)with pad_index, fillfactor=20, ignore_dup_key, drop_existing, statistics_norecompute4.1.4 查看修正索引查看修正索引1. 运用运用SQL Server Management Studio查看修正索引查看修正索引 2. 利用利用T-SQL查看修正索引查看修正索引 例例4-8: 查看学生表中的一切索引信息，其代码如下：查看学生表中的一切索引信息，其代码如下： Exec sp_help

11、index 学生学生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_索引这一独一索引。并查看学生索引这一独一索引。并查看学生

12、表信息。表信息。 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: 创建一视图，并为该视图建立聚集索引，

13、然后用索引视图检索数据。 USE xuesheng GO SET ANSI_PADDING,ANSI_NULLS ON GO CREATE 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 I

14、NDEX VIEW_学生 ON 学生(学号,姓名) |*在视图上建立聚集索引*| GO SELECT * FROM xuesheng_view GO 运转结果如图4.18所示。 图4.18 运转结果表4.3 常规索引与全文索引比较4.2 全文索引全文索引 全文索引包含在全文索引目录中全文索引包含在全文索引目录中Full-Text Catalogs，通常是由同一数据库中的一个或多个表的全，通常是由同一数据库中的一个或多个表的全文索引构成一个全文索引目录。一个表只能有一个全文索引，文索引构成一个全文索引目录。一个表只能有一个全文索引，因此每个有全文索引的表只隶属于一个全文索引目录。因此每个有全文索

15、引的表只隶属于一个全文索引目录。 4.2.1 全文索引引见全文索引引见 全文目录是存储全文索引的地方，全文目录必需保管在全文目录是存储全文索引的地方，全文目录必需保管在与与SQL Server实例相关联的本地硬盘上，每个全文目录可以实例相关联的本地硬盘上，每个全文目录可以用于满足数据库内的一个或多个表的索引需求。要想创建全用于满足数据库内的一个或多个表的索引需求。要想创建全文索引，首先必需创建全文目录。文索引，首先必需创建全文目录。 4.2.2 全文目录管理全文目录管理 4.2.2.1 启动全文搜索1.选择windows操作系统的【管理工具】|【效力】中，对【SQL Server Fullte

16、xt 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 和disable两个值。其中enable表示在当前数据库中启用全文索引；disable表示对于当前数据库中，删除文件

17、系统中一切的全文目录，并且将该数据库标志为曾经禁用全文索引。此操作并不在全文目录或表级上更改任何全文索引元数据。 4.2.2.2 创建全文目录创建全文目录 在在SQL Server Management Studio创建全文索引目录创建全文索引目录 运用运用T-SQL语句创建全文目录语句创建全文目录 创建全文目录的创建全文目录的T-SQL语句格式为：语句格式为： CREATE FULLTEXT CATALOG catalog_name /*全全文目录称号文目录称号/ ON FILLEGROUP filegroup /*包含全文目录的文包含全文目录的文件组名默许是主文件组件组名默许是主文件组*/

18、 IN PATH rootpath /*全文目录的途径全文目录的途径*/ WITH /*指定将运用于该目录的选项指定将运用于该目录的选项*/ AS DEFAULT /*指定该全文目录为默许目录指定该全文目录为默许目录*/ AUTHORIZATION owner_name /*将全文目录的一切将全文目录的一切者设为数据库用户名或角色的称号者设为数据库用户名或角色的称号*/4.2.3 创建全文索引创建全文索引例4-18 为“学生表的“姓名、“家庭住址2个字段创建全文索引，其代码如下： CREATE FULLTEXT INDEX ON 学生姓名，家庭住址 TYPE COLUMN扩展名 KEY IND

19、EX PK_学生 ON SQL全文目录4.2.4 运用全文搜索查询运用全文搜索查询 在一个表中创建了全文索引后，才可以对表中的数据进展全文检索。进展全文检索需求在SELECT命令的WHERE 字句中运用Transact-SQL 谓词：CONTAINS 和FRETEXT。4.2.4.1 运用运用CONTAINS搜索搜索v简单词的搜索方式简单词的搜索方式v简单词就是搜索一个或多个特定的词或短语。简单词就是搜索一个或多个特定的词或短语。v例例4-19: 搜索学生表中的家庭住址中含有搜索学生表中的家庭住址中含有“黑龙江省的记录，其代码如下：黑龙江省的记录，其代码如下：v SELECT * FROM 学

20、生学生 WHERE CONTAINS (家庭住址家庭住址,黑龙江省黑龙江省) v前缀词的搜索方式前缀词的搜索方式v例例4-20: 搜索学生表中的家庭住址中含有搜索学生表中的家庭住址中含有“上海开头的单词的记录，其代码如下：上海开头的单词的记录，其代码如下：v SELECT * FROM 学生学生v WHERE CONTAINS (家庭住址家庭住址, 上海上海*)v其中其中*只能放在英文字母之后，代表一个或多个字符。只能放在英文字母之后，代表一个或多个字符。 临近词的搜索方式临近词的搜索方式例例4-21: 搜索学生表中家庭住址中与黑龙江省考生相关的记录。搜索学生表中家庭住址中与黑龙江省考生相关的

21、记录。 use xueshengSELECT * FROM 学生学生 WHERE CONTAINS (家庭住址家庭住址, 黑龙江省黑龙江省 NEAR家庭家庭住址住址 )FREETEXT的语法代码为： FREETEXT(column_name|(column_list)|*, freetext_string,LANGUAGE language_term)其中：column_name：字段名column_list：字段列表*表示一切字段freetext_string：要搜索的字符串LANGUAGE language_term：用于单词断字、词干分析、同义词查询以及干扰词删除特定的言语 4.2.4.

22、2 运用运用FREETEXT搜索搜索例4-22 查询“黑龙江省的学生信息。 use xuesheng Select * from 学生 Where freetext (家庭住址,黑龙江省)4.3.1 数据完好性概念数据完好性概念数据完好性是指数据库中的数据在逻辑上的一致性和准确性。 在关系数据库系统中，比较重要的完好性有实体完好性、域完好性、参照完好性和用户自定义的完好性等。 4.3 SQL SERVER数据完好性数据完好性4.3.2 域完好性域完好性 域完好性又称列完好性，是指给定列的输入有效性。强迫域有效性的方法有：限制类型经过数据类型、格式经过CHECK约束和规那么或能够值的范围经过FO

23、REIGN KEY约束、CHECK约束、DEFAULT定义、NOT NULL定义和规那么来实现。4.3.3 实体完好性实体完好性 实体完好性又称行完好性，是指将行定义为特定表的独一实体。要求表中有一个主键，并且其值不能为空且不允许有反复的值与之对应。实体完好性强迫表的标识符列或主键的完好性经过索引、UNIQUE约束、PRIMARY KEY约束或IDENTITY属性来实现。4.3.4 参照完好性参照完好性 参照完好性又称援用完好性，是指主表中的数据与从表中的数据的一致性。在输入或删除其中一个表的记录时，另一个表对应的约束应满足，即参照完好性坚持表之间已定义的关系。在SQL Server中，参照完

24、好性基于外键与主键之间或外键与独一键之间的关系经过FOREIGN KEY和CHECK约束。参照完好性确保键值在一切表中一致。这样的一致性要求不能援用不存在的值，假设键值更改了，那么在整个数据库中，对该键值的一切援用要进展一致的更改。 4.3.5 完好性的实现完好性的实现v4.3.5.1 运用运用PRIMARY KEY约束实现约束实现v4.3.5.2 利用利用CHECK约束实现约束实现v4.5.3.3 UNIUE约束约束v4.3.5.4 运用运用Forein Key约束约束v4.3.5.5 运用默许值约束运用默许值约束4.3.5.1 运用运用PRIMARY KEY约束实现约束实现 对于任何一个数

25、据表中，通常具有包含独一标识表中每一行的值的一列或一组列。这样的一列或多列称为表的主键PRIMARY KEY，用于强迫表的实体完好性。在创建或修正表时，可以经过定义PRIMARY KEY约束来创建主键。 定义主键的方法可以参照4.1.3.1间接创建索引中创建。 4.3.5 完好性的实现完好性的实现4.3.5.2 利用利用CHECK约束实现约束实现 1. 经过经过SQL Server Management Studio创建与删除创建与删除CHECK约束约束 如教材中的图如教材中的图4.24、4.25所示。所示。2. 利用利用SQL语句在创建表的同时创建语句在创建表的同时创建CHECK约束约束例例

26、4-23 对数据库对数据库xuesheng中的学生表，要求其年龄在中的学生表，要求其年龄在0到到30岁以内。岁以内。 USE xuesheng CREATE TABLE 学生学生 ( 学号学号 char(6) not null, 姓名姓名 char(6) null, 年龄年龄 smallint not null CHECK (年龄年龄=0), 出生日期出生日期 datetime not null) 3. 修正表时创建修正表时创建CHECK约束约束例例4-24修正数据库修正数据库xuesheng中的学生表，要求其年龄在中的学生表，要求其年龄在30岁岁以内。以内。 USE xuesheng ALTER TABLE 学生学生 ADD CONSTRAINT 年龄年龄 CHECK (年龄年龄=0)4.5.3.3 UNIUE约束约束 UNIQUE约束在列集内强迫执行值的独一性。可以运用UNIQUE约束确保在非主键列中不输入反复的值。虽然UNIQUE约束和PRIMARY KEY约束都强迫独一性，但想要强迫一列或多列组合不是主键的独一性时应运用UNIQUE约束而不是PRIMARY KEY约束。 定义独一键的方法可以参照4.1.3.1间接创建索引中创建。可以对一个表定义多个 UNIQUE 约束，但只能定义一个 PRIMARY