《索引与数据完整性》PPT课件.ppt

陈章良 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.1 索引,索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合，以及相应的指向表中物理标识这些值的数据页的逻辑指针清单。 索引包括：数据和指针。,索引页,数据页,山东工商学院 陈章良,6.1 索引,数据检索流程： (1)搜索索引页； (2)找到所需数据的指针； (3)直接通过指针从数据页面中读取数据。,索引页,数据页,DZG 0x04,0x04 DZG 电子工业出版社,假设查找”电子工业出版社”,山东工商学院 陈章良,6.1 索引,select * from user_files where user_name = 李木声 该表总记录数：211,695,案例,(1)不使用索引 耗时：41 s,(2) 使用索引 耗时：0.0001 s,山东工商学院 陈章良,6.1 索引,快速存取数据； 保证数据记录的唯一性； 实现表与表之间的参照完整性； 在使用ORDER BY、GROUP BY子句进行数据检索时，利用索引可以减少排序和分组的时间。,索引是根据表中一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表。 在数据库系统中建立索引主要有以下作用：,山东工商学院 陈章良,6.1.1 索引的分类,SQL Server中索引包括： （1）聚集索引(Clustered) （2）非聚集索引(NonClustered),1.聚集索引(Clustered),(1)聚集索引指数据行的物理存储顺序与索引顺序完全相同，即聚集索引与数据是混为一体的。 (2)用聚集索引查找数据很快，它所需要的空间也就特别大。 (3)表的数据行只能以一种排序方式存储在磁盘上，所以一个表只能有一个聚簇索引。 (4)创建表主健时自动创建的索引是聚集索引.,山东工商学院 陈章良,6.1.1 索引的分类,2.非聚集索引(NonClustered),(1)非聚集索引不改变表数据行的物理顺序，数据存储在一个地方，索引存储在另一个地方，数据与索引完全分离。 (2)一个表可以建多个非聚集索引。,山东工商学院 陈章良,6.1.2 索引的创建,(1)对于XSB表，按学号建立主键（PRIMARY KEY约束），索引组织方式为聚集索引； (2)对于XSB表，按姓名建立唯一索引（UNIQUE约束），索引组织方式为非聚集索引； (3)对于KCB表，按课程号建立主键，索引组织方式为聚集索引； (4)对于CJB表，按学号+课程号建立主键，索引组织方式为聚集索引。,下面以XSB表中按姓名建立索引为例，介绍索引的创建方法。,在PXSCJ数据库中，经常要对XSB、KCB、CJB三个表查询和更新，为了提高查询和更新速度，可以考虑对三个表建立如下索引：,山东工商学院 陈章良,6.1.2 索引的创建,第2步 选择选择XSB表的图标，右击，出现如图6.1所示的快捷菜单，选择“设计表”菜单项，出现如图6.2所示XSB的表设计器界面；,第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 索引的创建,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.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_ind) 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 CLUSTERED 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 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步 为要删除索引的表打开表设计器，在表设计器中右击，然后从快捷菜单中选择“属性”命令； 第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 默认值约束与默认值对象,用户在插入新的数据行时，如果没有为某一列指定数据，那么系统将默认值赋给该列。 （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 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(),山东工商学院 陈章良,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 默认值对象的定义、使用与删除,-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表的总学分字段，可用如下程序段实现初始值设置为0。,山东工商学院 陈章良,6.2.2 默认值对象的定义、使用与删除,USE PXSCJ EXEC sp_unbindefault xsb.zxf DROP DEFAULT zxf_default,【例6.14】解除默认值对象zxf_default与xsb.zxf的绑定关系，然后删除名为zxf_default的默认值对象。,（3) 默认值对象的删除,山东工商学院 陈章良,6.3.3 域完整性的实现,6.3.1 数据完整性的分类,6.3 数据完整性,6.3.2 实体完整性的实现,6.3.4 参照完整性的实现,山东工商学院 陈章良,6.3.1 数据完整性的分类,2. 域完整性,3. 参照完整性,又称为引用完整性,又称为列完整性，指给定列输入的有效性,山东工商学院 陈章良,6.3.1 数据完整性的分类,XSB和 CJB表的对应关系如表6.2、6.3所示。,(1) 从表不能引用不存在的键值。 (2) 如果主表中的键值更改了，那么在整个数据库中，对从表中键值的所有引用要进行一致的更改。 (3) 如果主表中没有关联的记录，则不能将记录添加到从表。,山东工商学院 陈章良,6.3.2 实体完整性的实现,实体完整性,(1) 一个数据表只能创建一个PRIMARY KEY约束，但一个表中可根据需要对不同的列创建若干个UNIQUE约束； (2) PRIMARY KEY字段的值不允许为NULL，而UNIQUE字段的值可取NULL； (3) 一般创建PRIMARY KEY约束时，系统会自动产生索引，索引的缺省类型为簇索引。,PRIMARY KEY约束与UNIQUE约束的主要区别如下：,PRIMARY KEY约束与UNIQUE约束的相同点在于：二者均不允许表中对应字段存在重复值。,山东工商学院 陈章良,6.3.2 实体完整性的实现,1利用资源管理器创建和删除PRIMARY KEY约束,（1) 利用资源管理器创建PRIMARY KEY约束,第1步 选择XSB表图标，右击，打开XSB表的表设计器，进入如图6.16所示的表设计器界面。,【例6.22】 XSB表按学号建立PRIMARY KEY约束,第2步 选中“学号”对应的这一行，选择主键图标，这样在“学号”对应的这一行前面，将出现一主键图标。,山东工商学院 陈章良,6.3.2 实体完整性的实现,第1步 选择XSB表图标，右击，打开XS表设计器，进入如图6.17所示的表设计器界面。 第2步 选中XSB表设计器中主键对应的行，选择工具栏的“设置主键图标”，则取消了原来定义的主键。,如果要对XSB表中的“身份证号码”列创建UNIQUE约束，以保证该列取值的唯一性，可按以下步骤进行： 第1步 选择XS表图标，右击，打开XSB表设计器，在表设计器中右击，出现如图6.18所示的快捷菜单。 第2步 选择“属性”菜单项，出现属性界面，在此界面中选择“索引/键”选项卡，如图6.19所示。,2利用资源管理器创建和删除UNIQUE约束,（2) 利用资源管理器删除PRIMARY KEY约束,（1) 利用资源管理器创建UNIQUE约束,山东工商学院 陈章良,6.3.2 实体完整性的实现,第3步 在图6.19的属性界面中选择“新建”，输入新建索引的名字或使用系统缺省名，在“列名”下拉表中选择“身份证号码”，并设置索引顺序，将“创建UNIQUE约束”单选按钮设置为选中状态，如图6.20所示。,山东工商学院 陈章良,6.3.2 实体完整性的实现,进入如图6.20所示的属性窗口，在“选定的索引”下拉框中选择要删除的UNIQUE约束的索引名，再点击“删除”按钮即删除了指定的UNIQUE约束。,（2) 利用资源管理器删除UNIQUE约束,山东工商学院 陈章良,6.3.2 实体完整性的实现,3利用SQL命令创建及删除PRIMARY KEY约束或UNIQUE约束,(1) 创建表的同时创建PRIMARY KEY约束或UNIQUE约束,(2) 通过修改表创建PRIMARY KEY约束或UNIQUE约束创建PRIMARY KEY约束,山东工商学院 陈章良,6.3.2 实体完整性的实现,USE XSCJ CREATE TABLE XSB (xh varchar(6) NOT NULL CONSTRAINT pk_xh PRIMARY KEY, xm varchar(8) NOT NULL, sfz varchar(20) CONSTRAINT uk_sfz UNIQUE,-身份证 zy varchar(10) NULL, xb char(1) NOT NULL, cssj datetime NOT NULL, zxf numeric(6,2) NULL, bz text NULL, rxsj datetime CONSTRAINT dft_rxsj default getdate() ),【例6.24】 对PXSCJ数据库中XS表的学号字段创建PRIMARY KEY约束，对身份证号码字段定义UNIQUE约束。,山东工商学院 陈章良,6.3.2 实体完整性的实现,【例6.25】先在PXSCJ数据库中创建表XSB，然后通过修改表，对学号字段创建PRIMARY KEY约束，对身份证号码字段定义UNIQUE约束。,USE PXSCJ CREATE TABLE XSB ( xh varchar(6) NOT NULL, xm varchar(8) NOT NULL, sfz varchar(20) , zy varchar(10) NULL, xb char(1) NOT NULL, cssj datetime NOT NULL, zxf numeric(6,2) NULL, bz text NULL, rxsj datetime CONSTRAINT rxsjflt default getdate() ) GO,ALTER TABLE XSB ADD CONSTRAINT pk_xs PRIMARY KEY(xh) GO ALTER TABLE XSB ADD CONSTRAINT uk_sfz UNIQUE(sfz) GO,山东工商学院 陈章良,6.3.2 实体完整性的实现,(3) 删除PRIMARY KEY约束或UNIQUE约束,ALTER TABLE XSB DROP CONSTRAINT pk_xs,uk_sfz GO,【例6.26】 删除前面例中创建的PRIMARY KEY约束和UNIQUE约束。,山东工商学院 陈章良,6.3.3 域完整性的实现,域完整性,注意：SQL Server 的未来版本中将删除Rule对象。,山东工商学院 陈章良,6.3.3 域完整性的实现,第1步 在CJB表设计器界面右击鼠标，出现如图6.8所示的快捷菜单；,1CHECK约束的定义与删除,（1) 通过资源管理器创建与删除CHECK约束,山东工商学院 陈章良,6.3.3 域完整性的实现,第2步 选择CHECK约束菜单项进入如图6.9所示的CHECK选项卡属性窗口；,第3步 选择“新建”，进入CHECK约束的输入窗口，输入约束表达式“cj=0 and cj=100”，如图6.10所示，选择“关闭”。,山东工商学院 陈章良,6.3.3 域完整性的实现,（2) 利用SQL语句在创建表时创建CHECK约束,山东工商学院 陈章良,6.3.3 域完整性的实现,语法格式： ALTER TABLE table_name ADD CONSTRAINT check_name CHECK(logical_expression),USE PXSCJ ALTER TABLE CJB ADD CONSTRAINT chk_cj CHECK (cj=0 and cj=100),语法格式： ALTER TABLE table_name DROP CONSTRAINT check_name,USE PXSCJ ALTER TABLE CJB DROP CONSTRAINT chk_cj GO,（3) 利用SQL语句在修改表时创建CHECK约束,【例6.18】删除PXSCJ数据库中CJB表成绩字段的CHECK约束。,【例6.17】通过修改XSCJ数据库的XS_KC表，增加成绩字段的CHECK约束。,（4) 利用SQL语句删除CHECK约束,山东工商学院 陈章良,6.3.3 域完整性的实现,注意：SQL Server 的未来版本中将删除Rule对象。,山东工商学院 陈章良,6.3.3 域完整性的实现,另外有如下几点需说明： 创建的规则对先前已存在于数据库中的数据无效。 规则表达式的类型必须与列的数据类型兼容。 如果列同时有默认值和规则与之关联，则默认值必须满足规则的定义。,3)应用举例,山东工商学院 陈章良,6.3.3 域完整性的实现,USE PXSCJ CREATE RULE kc_rule AS range like 1-50-90-9 GO USE PXSCJ EXEC sp_bindrule kc_rule, KCB.kch GO,USE PXSCJ CREATE RULE list_rule AS list IN (C语言, 离散数学, 微机原理) GO GOUSE PXSCJ EXEC sp_bindrule list_rule, KCB.kcm GO,【例6.20】创建一个规则，用以限制输入到该规则所绑定的列中的值只能是该规则中列出的值。,【例6.19】如下程序创建一个规则，并绑定到表KCB的课程号列，用于限制课程号的输入范围。,山东工商学院 陈章良,6.3.3 域完整性的实现,USE PXSCJ EXEC sp_unbindrule KCB.kch DROP RULE kc_rule,【例6.22】解除课程号列与kc_rule之间的绑定关系，并删除规则对象kc_rule。,4)规则对象的删除,山东工商学院 陈章良,6.3.4 参照完整性的实现,第1步 定义XSB表中的学号字段为主码。 第2步 选择企业管理器目录树中PXSCJ数据库目录下的“关系图”图标右击，出现如图6.21所示的快捷菜单。,第3步 选择菜单项“新建数据库关系图”，进入如图6.22所示的欢迎界面。,1利用资源管理器定义表间的参照关系,例如：要建立XSB表与CJB表之间的参照完整性,山东工商学院 陈章良,6.3.4 参照完整性的实现,第4步 选择欢迎界面中的“下一步”按钮，进入如图6.23所示“创建关系图向导”的界面，从可用表中选择要添加到关系图中的表，本例中选择了XSB表和CJB表。,第5步 点击“下一步”按钮，并按提示进行后继操作，进入如图6.24所示的关系图界面。,山东工商学院 陈章良,6.3.4 参照完整性的实现,第6步 在关系图上，将鼠标指向主表的主键并拖动到从表。对于本例：将XSB表中的学号字段拖动到从表CJB，出现如图6.25所示的关系设置界面。,第7步 退出图6.26的关系图界面，并根据提示，将关系图的有关信息存盘，即创建了主表与从表之间的参照关系。,山东工商学院 陈章良,6.3.4 参照完整性的实现,第1步 进入XSB表设计器右击，出现一快捷菜单如图6.27所示课程类别。,第2步 选择菜单项“关系”，出