《《索引与数据完整性》ppt课件》由会员分享，可在线阅读，更多相关《《索引与数据完整性》ppt课件（59页珍藏版）》请在金锄头文库上搜索。
1、陈章良 E-MAIL：SDIBT2008126.COM,SQL Server实用教程,山东工商学院 陈章良,第6章 索引与数据完整性,select * from user_files where user_name = 李木声 go,insert into person(bh,xm,xb,dz) values(01001,王长好,1,山东烟台) go,山东工商学院 陈章良,第6章 索引与数据完整性,6.3 数据完整性,6.2 默认值约束与默认值对象,6.1 索引,山东工商学院 陈章良,6.1.2 索引的创建,6.1.1 索引的分类,6.1 索引,6.1.3 索引的删除,山东工商学院 陈章良,6
2、.1 索引,索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合，以及相应的指向表中物理标识这些值的数据页的逻辑指针清单。 索引包括：数据和指针。,索引页,数据页,山东工商学院 陈章良,6.1 索引,数据检索流程： (1)搜索索引页； (2)找到所需数据的指针； (3)直接通过指针从数据页面中读取数据。,索引页,数据页,DZG 0x04,0x04 DZG 电子工业出版社,假设查找”电子工业出版社”,山东工商学院 陈章良,6.1 索引,select * from user_files where user_name = 李木声 该表总记录数：211,695,案例,(1)不使用索引
3、 耗时：41 s,(2) 使用索引 耗时：0.0001 s,山东工商学院 陈章良,6.1 索引,快速存取数据； 保证数据记录的唯一性； 实现表与表之间的参照完整性； 在使用ORDER BY、GROUP BY子句进行数据检索时，利用索引可以减少排序和分组的时间。,索引是根据表中一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表。 在数据库系统中建立索引主要有以下作用：,山东工商学院 陈章良,6.1.1 索引的分类,SQL Server中索引包括： （1）聚集索引(Clustered) （2）非聚集索引(NonClustered),1.聚集索引(Clustered),(1)聚集索引指数据行
4、的物理存储顺序与索引顺序完全相同，即聚集索引与数据是混为一体的。 (2)用聚集索引查找数据很快，它所需要的空间也就特别大。 (3)表的数据行只能以一种排序方式存储在磁盘上，所以一个表只能有一个聚簇索引。 (4)创建表主健时自动创建的索引是聚集索引.,山东工商学院 陈章良,6.1.1 索引的分类,2.非聚集索引(NonClustered),(1)非聚集索引不改变表数据行的物理顺序，数据存储在一个地方，索引存储在另一个地方，数据与索引完全分离。 (2)一个表可以建多个非聚集索引。,山东工商学院 陈章良,6.1.2 索引的创建,(1)对于XSB表，按学号建立主键（PRIMARY KEY约束），索引组
5、织方式为聚集索引； (2)对于XSB表，按姓名建立唯一索引（UNIQUE约束），索引组织方式为非聚集索引； (3)对于KCB表，按课程号建立主键，索引组织方式为聚集索引； (4)对于CJB表，按学号+课程号建立主键，索引组织方式为聚集索引。,下面以XSB表中按姓名建立索引为例，介绍索引的创建方法。,在PXSCJ数据库中，经常要对XSB、KCB、CJB三个表查询和更新，为了提高查询和更新速度，可以考虑对三个表建立如下索引：,山东工商学院 陈章良,6.1.2 索引的创建,第2步 选择选择XSB表的图标，右击，出现如图6.1所示的快捷菜单，选择“设计表”菜单项，出现如图6.2所示XSB的表设计器界面
6、；,第1步 进入资源管理器，打开PXSCJ数据库；,1.通过资源管理器建立索引,山东工商学院 陈章良,6.1.2 索引的创建,第3步 在XSB表设计器界面上右击， 出现如图6.3所示的快捷菜单，快捷菜单中选择“索引/键”菜单项，出现如图6.4所示XSB的属性界面；,山东工商学院 陈章良,6.1.2 索引的创建,第4步 在“索引键”属性页选择“新建”命令，然后，确定新索引的名称（用系统缺省的名或重新取名）；在“列名”下选择要创建索引的列，在此选择“学号”这一列，最多可选择16 列。,山东工商学院 陈章良,6.1.2 索引的创建,2.利用SQL命令建立索引,山东工商学院 陈章良,6.1.2 索引的
7、创建,USE PXSCJ IF EXISTS (SELECT name FROM sysindexes WHERE name =kc_name_ind) DROP INDEX KCB.ix_kc_name GO CREATE INDEX ix_kc_name ON KCB(kcm) GO,USE PXSCJ IF EXISTS (SELECT name FROM sysindexes WHERE name=kc_id_ind) DROP INDEX KCB.ix_kc_id GO CREATE UNIQUE CLUSTERED INDEX ix_kc_id ON KCB(kch) GO,【例6
8、.1】为KCB表的课程名列创建索引。,【例6.2】根据KCB表的课程号列创建唯一聚集索引，因为指定了 CLUSTERED 子句，所以该索引将对磁盘上的数据进行物理排序。,山东工商学院 陈章良,6.1.2 索引的创建,USE PXSCJ IF EXISTS (SELECT name FROM sysindexes WHERE name=cj_ind) DROP INDEX ix_cj GO CREATE INDEX ix_cj ON CJB(xh,kch) GO,USE PXSCJ IF EXISTS (SELECT name FROM sysindexes WHERE name=score_i
9、nd) DROP INDEX ix_score GO CREATE NONCLUSTERED INDEX ix_score ON XSB(xf) WITH FILLFACTOR = 60 GO,【例6.3】根据CJB表的学号列和课程号列创建复合索引。,【例6.4】根据XSB表中的总学分列创建索引，例中使用了FILLFACTOR 子句。,山东工商学院 陈章良,6.1.2 索引的创建,USE PXSCJ IF EXISTS (SELECT name FROM sysindexes WHERE name= xs_ind) DROP INDEX XSB.ix_xs CREATE UNIQUE CLUS
10、TERED INDEX ix_xs ON XSB(xh) WITH IGNORE_DUP_KEY,【例6.5】根据XSB表中学号列创建唯一聚集索引。如果输入了重复的键，将忽略该INSERT或UPDATE语句。,山东工商学院 陈章良,6.1.2 索引的创建,Create view v_xs with schemabinding As Select xm,zy From dbo.xsb Where dbo.xsb.zy=计算机,【例6.6】 创建一个视图，并为该视图创建索引。,USE PXSCJ GO -在视图上创建索引 CREATE UNIQUE CLUSTERED INDEX ix_xm ON
11、 v_xs(xm) GO,山东工商学院 陈章良,6.1.3 索引的重建,语法格式： ALTER INDEX index_name|All On table_name REBUILD,ALTER INDEX ix_xsb_xm On XSB REBUILD,【例6.7】重建表XSB的一个索引名为ix_xsb_xm 的索引。,ALTER INDEX All On XSB REBUILD,【例6.8】重建XSB上所有索引,山东工商学院 陈章良,6.1.4 索引的删除,第1步 进入资源管理器，并与指定的数据库相连接； 第2步 为要删除索引的表打开表设计器，在表设计器中右击，然后从快捷菜单中选择“属性”
12、命令； 第3步 选择“索引/键”选项卡，从“选定的索引”列表中选择要删除的索引，选择“删除”按钮。,1.通过资源管理器界面删除索引,山东工商学院 陈章良,6.1.4 索引的删除,语法格式： DROP INDEX table.index | view.index ,.n ,DROP INDEX XSB.ix_xm,2.通过SQL命令删除索引,【例6.9】删除PXSCJ数据库中表XSB的一个索引名为ix_xm 的索引。,山东工商学院 陈章良,6.2.2 默认值对象的定义、使用与删除,6.2.1 在表中定义及删除默认值约束,6.2 默认值约束与默认值对象,用户在插入新的数据行时，如果没有为某一列指定
13、数据，那么系统将默认值赋给该列。 （1）默认约束 （2）默认对象,注意：SQL Server 的未来版本中将删除Default对象。,山东工商学院 陈章良,6.2.1 在表中定义及删除默认值约束,USE PXSCJ CREATE TABLE XSB ( xh varchar(6) NOT NULL, xm varchar(8) NOT NULL, zy varchar(10) NULL, xb char(1) NOT NULL, cssj datetime NOT NULL, zxf numeric(6,2) NULL, bz text NULL, rxsj datetime default
14、getdate() ) GO,【例6.8】在定义表时定义一个字段的默认值约束。,1.默认值约束的定义,山东工商学院 陈章良,6.2.1 在表中定义及删除默认值约束,USE PXSCJ ALTER TABLE XS B ADD rxsj datetime NULL DEFAULT getdate(),USE PXSCJ ALTER TABLE XSB DROP CONSTRAINT dft_rxsj GO,【例6.9】在修改表时定义一个字段的默认值约束。,2.默认值约束的删除,【例6.10】删除上例定义的默认值约束。,CONSTRAINT dft_rxsj DEFAULT getdate(),山
15、东工商学院 陈章良,6.2.2 默认值对象的定义、使用与删除,语法格式： CREATE DEFAULT default_name AS constant_expression,语法格式： sp_bindefault defname = default , objname = object_name , futureonly = futureonly_flag ,注意：SQL Server 的未来版本中将删除Default对象。,（1）通过SQL命令定义DEFAULT默认值对象,（2) 通过系统存储过程绑定DEFAULT默认值对象,山东工商学院 陈章良,6.2.2 默认值对象的定义、使用与删除,
16、-1定义表 CREATE TABLE book ( book_id char(6) name varchar(20) NOT NULL, hire_date datetime NOT NULL ) GO,【例6.12】首先在PXSCJ数据库中定义表book以及名为today 的默认值，然后将其绑定到 book表的 hire_date 列。,-3绑定默认值对象 EXEC sp_bindefault today, book.hire_date,-2创建默认值对象 CREATE DEFAULT today AS getdate() GO,山东工商学院 陈章良,6.2.2 默认值对象的定义、使用与删除,CREATE DEFAULT zxf_default AS 0 USE PXSCJ EXEC sp_bindefault zxf_default ,XSB.zxf GO,【例6.11】对于如前所述的PXSCJ数据库中XSB表的总学分字段
