索引及数据完整性41索引.ppt

《索引及数据完整性41索引.ppt》由会员分享，可在线阅读，更多相关《索引及数据完整性41索引.ppt（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 xueshengCREATE TABLE 成绩(学号 INT NOT NULL,课程号 NUMERIC UNIQUE,/*唯一约束*/成绩 NUMERIC),4.1.3.2 在SQL Server Management Studio中创

4、建索引,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,.n)withPAD_INDEX,FILLFACTOR=fillf

5、actor,IGNORE_DUP_KEY,DROP_EXISTING,STATISTICS_NORECOMPUTE,SORT_IN_TEMPDB ON filegroup,CREATE INDEX命令创建索引各参数说明如下：UNIQUE：用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行。CLUSTERED：用于指定创建的索引为聚集索引。NONCLUSTERED：用于指定创建的索引为非聚集索引。ON:表示可以在表或视图上创建索引,这里指定表或视图的名称和相应的列名称。index_name：用于指定所创建的索引的名称。table：用于指定创建索引的表的名称。view：用于指定创建索引的

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

7、存储在 tempdb 数据库中。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 nam

8、e=XM_索引)DROP INDEX xuesheng.XM_索引GOCREATE 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-SQL查看修改索引 例4-8:查看学生表中的所有索引信息，其代码如下：Exec sp_helpindex 学生,4.1.5 重新生成索引,例4-10:重新生成xu

9、esheng数据库中的学生表里的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_索引这一唯一索引。并查看学生表信息。ALTER INDEX XM_索引 ON 学生 DISABLE GO运行后，再次使用select*from 学生，则可以显示数据表中信息。,4.1.7 删除索引,利用T

10、-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 CREATE VIEW xuesheng_VIEW/*建立视图名为xuesheng_VIEW*/WITH SCHEMABINDING AS SELECT 姓名,学号 FROM DBO.学生 GO,SET

11、 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_view GO 运行结果如图4.18所示。,图4.18 运行结果,表4.3 常规索引与全文索引比较,4.2 全文索引,全文索引包含在全文索引目录中（Full-Text Catalogs），通常是由同一数据库

12、中的一个或多个表的全文索引构成一个全文索引目录。一个表只能有一个全文索引，因此每个有全文索引的表只隶属于一个全文索引目录。,4.2.1 全文索引介绍,全文目录是存储全文索引的地方，全文目录必须保存在与SQL Server实例相关联的本地硬盘上，每个全文目录可以用于满足数据库内的一个或多个表的索引需求。要想创建全文索引，首先必须创建全文目录。,4.2.2 全文目录管理,4.2.2.1 启动全文搜索,1.选择windows操作系统的【管理工具】|【服务】中，对【SQL Server Fulltext Search】进行启动。如图4.19所示。,图4.19 利用windows启动全文搜索,2.可以直

13、接在SQL Server 2005中依次选择【管理】|【全文搜索】，单击右键选择【启动】命令即可。如教材中图4.20所示。3.利用T-SQL命令实现 对应的T-SQL语句命令格式如下：sp_fulltext_database action=action 其中 action=action表示将要执行的操作。action 的数据类型为 varchar(20)，可以是enable 和disable两个值。其中enable表示在当前数据库中启用全文索引；disable表示对于当前数据库中，删除文件系统中所有的全文目录，并且将该数据库标记为已经禁用全文索引。此操作并不在全文目录或表级上更改任何全文索引元

14、数据。,4.2.2.2 创建全文目录,在SQL Server Management Studio创建全文索引目录使用T-SQL语句创建全文目录 创建全文目录的T-SQL语句格式为：CREATE FULLTEXT CATALOG catalog_name/*全文目录名称/ON FILLEGROUP filegroup/*包含全文目录的文件组名默认是主文件组*/IN PATH rootpath/*全文目录的路径*/WITH/*指定将应用于该目录的选项*/AS DEFAULT/*指定该全文目录为默认目录*/AUTHORIZATION owner_name/*将全文目录的所有者设为数据库用户名或角色的

15、名称*/,4.2.3 创建全文索引,例4-18 为“学生”表的“姓名”、“家庭住址”2个字段创建全文索引，其代码如下：CREATE FULLTEXT INDEX ON 学生（姓名，家庭住址 TYPE COLUMN扩展名）KEY INDEX PK_学生 ON SQL全文目录,4.2.4 使用全文搜索查询,在一个表中创建了全文索引后，才可以对表中的数据进行全文检索。进行全文检索需要在SELECT命令的WHERE 字句中使用Transact-SQL 谓词：CONTAINS 和FRETEXT。,4.2.4.1 使用CONTAINS搜索,简单词的搜索方式简单词就是搜索一个或多个特定的词或短语。例4-19

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

17、ETEXT(column_name|(column_list)|*,freetext_string,LANGUAGE language_term)其中：column_name：字段名column_list：字段列表*表示所有字段freetext_string：要搜索的字符串LANGUAGE language_term：用于单词断字、词干分析、同义词查询以及干扰词删除特定的语言,4.2.4.2 使用FREETEXT搜索,例4-22 查询“黑龙江省”的学生信息。use xuesheng Select*from 学生 Where freetext(家庭住址,黑龙江省),4.3.1 数据完整性概念,数

18、据完整性是指数据库中的数据在逻辑上的一致性和准确性。在关系数据库系统中，比较重要的完整性有实体完整性、域完整性、参照完整性和用户自定义的完整性等。,4.3 SQL SERVER数据完整性,4.3.2 域完整性,域完整性又称列完整性，是指给定列的输入有效性。强制域有效性的方法有：限制类型（通过数据类型）、格式（通过CHECK约束和规则）或可能值的范围（通过FOREIGN KEY约束、CHECK约束、DEFAULT定义、NOT NULL定义和规则来实现）。,4.3.3 实体完整性,实体完整性又称行完整性，是指将行定义为特定表的惟一实体。要求表中有一个主键，并且其值不能为空且不允许有重复的值与之对应

19、。实体完整性强制表的标识符列或主键的完整性（通过索引、UNIQUE约束、PRIMARY KEY约束或IDENTITY属性来实现）。,4.3.4 参照完整性,参照完整性又称引用完整性，是指主表中的数据与从表中的数据的一致性。在输入或删除其中一个表的记录时，另一个表对应的约束应满足，即参照完整性保持表之间已定义的关系。在SQL Server中，参照完整性基于外键与主键之间或外键与惟一键之间的关系（通过FOREIGN KEY和CHECK约束）。参照完整性确保键值在所有表中一致。这样的一致性要求不能引用不存在的值，如果键值更改了，那么在整个数据库中，对该键值的所有引用要进行一致的更改。,4.3.5 完

20、整性的实现,4.3.5.1 使用PRIMARY KEY约束实现4.3.5.2 利用CHECK约束实现4.5.3.3 UNIUE约束4.3.5.4 使用Forein Key约束4.3.5.5 使用默认值约束,4.3.5.1 使用PRIMARY KEY约束实现,对于任何一个数据表中，通常具有包含唯一标识表中每一行的值的一列或一组列。这样的一列或多列称为表的主键（PRIMARY KEY），用于强制表的实体完整性。在创建或修改表时，可以通过定义PRIMARY KEY约束来创建主键。定义主键的方法可以参照间接创建索引中创建。,4.3.5 完整性的实现,4.3.5.2 利用CHECK约束实现,1.通过SQ

21、L Server Management Studio创建与删除CHECK约束 如教材中的图4.24、4.25所示。,2.利用SQL语句在创建表的同时创建CHECK约束例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 xues

22、heng ALTER TABLE 学生 ADD CONSTRAINT 年龄 CHECK(年龄=0),4.5.3.3 UNIUE约束,UNIQUE约束在列集内强制执行值的惟一性。可以使用UNIQUE约束确保在非主键列中不输入重复的值。尽管UNIQUE约束和PRIMARY KEY约束都强制唯一性，但想要强制一列或多列组合（不是主键）的唯一性时应使用UNIQUE约束而不是PRIMARY KEY约束。定义唯一键的方法可以参照间接创建索引中创建。可以对一个表定义多个 UNIQUE 约束，但只能定义一个 PRIMARY KEY 约束。对于UNIQUE约束中的列，表中不允许有两行包含相同的非空值。主键也强制

23、执行惟一性，但主键不允许空值。UNIQUE约束优先于惟一索引。,4.3.5.4 使用Forein Key约束,1.利用表设计器实现2.利用数据库对象实现,使用FOREIGN KEY约束需要注意的问题1.一个表最多可以有可以建立一个或者多个FOREIGN KEY约束，最多可以建253个FOREIGN KEY约束。2.创建FOREIGN KEY约束的主表字段可以是主键约束也可以是UNIQUE约束。3.创建FOREIGN KEY约束的表可以和同数据库中其他表间建立约束，也可以和它本身建立FOREIGN KEY约束。例如：总经理也是公司成员中的一员，则可以将总经理字段（zjl）和成员（cy）字段间建立

24、FOREIGN KEY约束。4.建立FOREIGN KEY约束的主表和子表间两个字段的数据类型和数据宽度必须相同。5.创建FOREIGN KEY约束前应该确定那个表是主表，那个是子表。假如A表删了一条记录，表B也要删一条对应的记录，这时候应该在表B上设定关系，A是主表，B是子表（有的称为从表）。,4.3.5.5 使用默认值约束,1.创建默认值 例4-25 创建字符默认值 unknown。use xuesheng go create default 家庭住址 as unknown go,2.删除默认值例4-26 删除默认值。如果默认值没有绑定到列或用户定义的数据类型，可以很容易地使用 DROP DEFAULT 将其除去。如下为删除用户创建的名为族别_hz 的默认值。use pubs go if exists(select name from sysobjects where name=族别_hz and type=c)drop default族别_hz go,