sql server索引和完整性

《sql server索引和完整性》由会员分享，可在线阅读，更多相关《sql server索引和完整性（50页珍藏版）》请在金锄头文库上搜索。

1、第7章 索引和完整性,7.1 索 引,7.2 数据完整性,7.1 索 引,在数据库系统中建立索引主要有以下作用： （1）快速存取数据。 （2）保证数据记录的唯一性。 （3）实现表与表之间的参照完整性。 （4）在使用ORDER BY、GROUP BY子句进行数据检索时，利用索引可以减少排序和分组的时间。,7.1.1 索引的分类,1聚集索引 聚集索引使得数据表物理顺序与索引顺序一致。不论聚集索引里有表的哪个（或哪些）字段，这些字段都会按顺序保存在表中。由于存在这种排序，所以每个表只会有一个聚集索引。 2非聚集索引 在非聚集索引内，从索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页的存

2、储方式是堆集还是聚集。对于堆集，行定位器是指向行的指针。对于有聚集索引的表，行定位器是聚集索引键。如果一个表只有非聚集索引，则它的数据行将按无序的堆集方式存储。 一个表中可有一个或多个非聚集索引。 当在一个表中既要创建聚集索引，又要创建非聚集索引时，应先创建聚集索引，然后再创建非聚集索引，因为创建聚集索引时将改变数据记录的物理存放顺序。,7.1.2 索引的创建：CREATE INDEX,为了提高查询和更新速度，可以考虑对三个表建立如下索引： （1）对于xsb表，按学号建立主键索引（PRIMARY KEY约束），组织方式为聚集索引。 （2）对于kcb表，按课程号建立主键索引，组织方式为聚集索引。

3、 （3）对于kcb表，按课程名建立唯一索引（UNIQUE约束），组织方式为非聚集索引。 （4）对于cjb表，按学号+课程号建立唯一索引，组织方式为聚集索引。,7.1.2 索引的创建：CREATE INDEX,1界面（对象资源管理器）方式创建索引 【例7.1】 在pxscj数据库xsb表中按“出生时间”列建立索引。 （1）在“对象资源管理器”中选择pxscj数据库“dbo.xsb”表展开，右击其中的“索引”项，在弹出的快捷菜单上选择“新建索引”菜单项，并且其后选择索引类型，如图7.1所示。,7.1.2 索引的创建：CREATE INDEX,（2）新建索引。 在打开的“新建索引”中，单击“添加”按

4、钮，系统打开一个xsb表字段选择对话框，勾选需要索引的列（例如“出生时间” ），单击“确定”按钮，在“索引键列”中就会显示该列，如图7.2所示。,7.1.2 索引的创建：CREATE INDEX,2界面（表设计器）方式创建索引 【例7.2】 在pxscj数据库的xsb表中按“姓名”列建立非唯一索引。 （1）选择pxscj数据库中的“dbo.xsb”表，在弹出的快捷菜单中选择“设计”菜单项，打开“表设计器”窗口。在“表设计器”窗口中，选择任何列，右击鼠标，在弹出的快捷菜单中选择“索引/键”菜单项。系统显示已经创建的索引“PK_xsb”（学号作为主键的聚合索引）和PK_cssj（出生时间非聚合索引

5、）及其属性。 （2）单击“添加”按钮，系统创建一个默认的索引，索引名为“IX_xsb”，用户可在右边的“标识”属性区域进行修改。 名称：修改为“IX_xsb_xm”。 列：在该栏后面单击 按钮，修改要创建索引的列为“姓名”，默认“升序”。 是唯一的：保持“否”， 表示索引不是唯一索引。,7.1.2 索引的创建：CREATE INDEX,由于xsb表中已经存在聚集索引，所以这里的这个选项不可修改，如图7.3所示。,7.1.2 索引的创建：CREATE INDEX,3利用SQL命令建立索引 语法格式： CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX 索引名

6、 ON 表或视图名 ( 列 ASC | DESC ) WHERE子句 其中， （1）UNIQUE：表示为表或视图创建唯一索引（即不允许存在索引值相同的两行）。例如，对于xsb表，根据学号创建唯一索引，即不允许有两个相同的学号出现。 （2）CLUSTERED | NONCLUSTERED：CLUSTERED表示创建聚集索引，NONCLUSTERED表示创建非聚集索引。 （3）索引名：索引名在表或视图中必须唯一，但在数据库中不必唯一；“表或视图名”用于指定包含索引字段的表名或视图名，指定表名、视图名时可包含数据库和所属架构。 （4）列：指定建立索引的字段，可以为索引指定多个字段。指定索引字段时，注

7、意表或视图索引字段的类型不能为ntext、text或image。 （5）WHERE子句：通过指定索引中要包含哪些行来创建筛选索引。,7.1.2 索引的创建：CREATE INDEX,【例7.3】 在pxscj数据库中为kcb表的“课程名”列创建索引，为“课程号”列创建唯一聚集索引。 USE pxscj GO CREATE INDEX kc_name ON kcb(课程名) CREATE UNIQUE CLUSTERED INDEX kc_id ON kcb (课程号) 【例7.4】 根据cjb表的“学号”列和“课程号”列创建复合索引。 CREATE INDEX cjb_ind ON cjb(学

8、号, 课程号) WITH(DROP_EXISTING= ON) 其中，DROP_EXISTING指定删除已存在的同名聚集索引或非聚集索引。 例如，根据xsb表中“学号”列创建唯一聚集索引。如果输入了重复的键值，将忽略该INSERT或UPDATE语句。 CREATE UNIQUE CLUSTERED INDEX xs_ind ON xsb(学号) WITH IGNORE_DUP_KEY 其中，IGNORE_DUP_KEY指定对索引列插入操作时出现重复键值的错误响应。,7.1.2 索引的创建：CREATE INDEX,4在计算列和视图上创建索引 （1）在计算列上创建索引。 对于UNIQUE或PRI

9、MARY KEY索引，只要满足索引条件，就可以包含计算列，但计算列必须具有确定性，必须精确。若计算列中带有函数，则要求该函数有相同的参数输入，输出的结果也一定相同。 例如，pxscj数据库的xsb表中的“年龄”列是计算列，可以创建索引。 （2）在视图上创建索引。 可以在视图上定义索引。索引视图是一种在数据库中存储视图结果集的方法，可减少动态生成结果集的开销。索引视图还能自动反映出创建索引后对基表数据所做的修改。,7.1.2 索引的创建：CREATE INDEX,【例7.5】 创建pxscj数据库一个视图，并为该视图创建索引。 T-SQL命令如下： CREATE VIEW dbo.vxs1 WI

10、TH SCHEMABINDING AS SELECT 学号, 姓名 FROM dbo.xsb GO /*在视图上创建索引*/ CREATE UNIQUE CLUSTERED INDEX inx1 ON dbo.vxs1(学号) GO,7.1.3 重建索引：ALTER INDEX,索引使用一段时间后，可能需要重新创建。这时，可以使用ALTER INDEX语句来重新生成原来的索引。 语法格式： ALTER INDEX 索引名 | ALL ON 表或视图名 REBUILD 例如，重建kcb表上的所有索引： USE pxscj ALTER INDEX ALL ON kcb REBUILD 重建kcb表

11、上的kc_name索引： ALTER INDEX kc_name ON kcb REBUILD,7.1.4 索引的删除：DROP INDEX,1界面方式删除索引 在“对象资源管理器”中展开数据库“pxscj”“表”“dbo.xsb”“索引”，选择其中要删除的索引，单击鼠标右键，在弹出的快捷菜单上选择“删除”菜单项。在打开的“删除对象”窗口中单击“确定”按钮即可完成删除操作。 2通过执行SQL命令删除索引 从当前数据库中删除一个或多个索引。 语法格式： DROP INDEX 索引名 ON 表或视图名 其中，索引名是要删除的索引名称。表或视图名为索引所在的表名或视图名。,7.1.4 索引的删除：D

12、ROP INDEX,另外，DROP INDEX语句可以一次删除一个或多个索引。这个语句不适合删除通过定义PRIMARY KEY或UNIQUE约束创建的索引。若要删除PRIMARY KEY或UNIQUE约束创建的索引，必须通过删除约束实现。 另外，在系统表的索引上不能进行DROP INDEX操作。 【例7.6】 删除pxscj数据库中表kcb的一个索引名为kc_name的索引。 T-SQL命令如下： IF EXISTS (SELECT name FROM sysindexes WHERE name = kc_name) DROP INDEX kcb.kc_name,7.2 数据完整性,1实体完整

13、性 （1）PRIMARY KEY约束。 表中应有一个列或列的组合，其值能唯一地标识表中的每一行，选择这样的一列或多列作为主键，可实现表的实体完整性。通过定义PRIMARY KEY约束来创建主键。 （2）UNIQUE约束。 如果要确保一个表中的非主键列不输入重复值，则应在该列上定义唯一约束（UNIQUE约束）。例如，对于pxscj数据库中的xsb表，“学号”列是主键，在xsb表中增加一列“身份证号码”，可以定义一个UNIQUE约束来要求表中“身份证号码”列的取值是唯一的。,7.2 数据完整性,PRIMARY KEY约束与UNIQUE约束的主要区别如下： （1）一个数据表只能创建一个PRIMARY

14、 KEY约束，但一个表中可根据需要对表中不同的列创建若干个UNIQUE约束。 （2）PRIMARY KEY字段的值不允许为NULL，而UNIQUE字段的值可取NULL。 （3）一般创建PRIMARY KEY约束时，系统会自动产生索引，索引的默认类型为簇索引。创建UNIQUE约束时，系统会自动产生一个UNIQUE索引，索引的默认类型为非簇索引。 PRIMARY KEY约束与UNIQUE约束的相同点是：二者均不允许表中对应字段存在重复值。,7.2 数据完整性,2域完整性 域完整性又称为列完整性，指给定列输入的有效性。实现域完整性的方法有：通过CHECK约束、数据类型、DEFALUT定义、NOT N

15、ULL定义和规则等。 CHECK约束通过输入到列中的值来实现域完整性；DEFAULT定义后，如果列中没有输入值，则填充默认值来实现域完整性；通过定义列为NOT NULL限制输入的值不能为空，也能实现域完整性。 例如，对于学生数据库pxscj的kcb表，学生的学分应在16。为了对“学分”这一数据项输入的数据范围进行限制，可以在定义kcb表的同时定义学分的约束条件来达到这一目的。,7.2 数据完整性,3参照完整性 参照完整性确保键值在所有表中一致。 码：即前面所说的关键字，又称为“键”，是能唯一标识表中记录的字段或字段组合。如果一个表有多个码，可选其中一个作为主键（主码），其余的称为候选键。 外码

16、：如果一个表中的一个字段或若干个字段的组合是另一个表的码，则称该字段或字段组合为该表的外码（外键）。 例如，对于pxscj数据库中的xsb表的每一个学号，在cjb表中都有相关的课程成绩记录，将xsb作为主表，“学号”字段定义为主键，cjb作为从表，表中的“学号”字段定义为外键，从而建立主表和从表之间的联系，实现参照完整性。,7.2 数据完整性,如果定义了两个表之间的参照完整性，则要求： （1）从表不能引用不存在的键值。例如，cjb表中行记录出现的学号必须是xsb表中已存在的学号。 （2）如果主表中的键值更改了，那么在整个数据库中，对从表中该键值的所有引用要进行一致的更改。例如，如果对xsb表中的某一学号进行修改，则对cjb表中所有对应的学号也要进行相应的修改。 （3）如果主表中没有关联的记录，则不能将记录添加到从表。如果要删除主表中的某一记录，则应先删除从表中与该记录匹配的相关记录。,7.2.1 创建实体完整性：PRIMARY KEY/UNIQUE,1以界