《sqlserver2005数据库应用与开发教学课件作者李新德6》由会员分享，可在线阅读，更多相关《sqlserver2005数据库应用与开发教学课件作者李新德6（64页珍藏版）》请在金锄头文库上搜索。
1、第六章 索引与数据完整性,2,4,项目八 索引的创建和删除,任务 索引的创建和删除,项目九 数据约束和数据完整性,任务一 设置默认值约束、unique约束、check约束 identity列属性,任务二 创建主键、外键并实现参照完整性,返回,项目八 索引的创建和删除,【项目需求】 在SQL Server 2005 中，针对数据库STUDY 中的表创建聚集索引和非聚集索引。 【完成项目的条件】 （1）数据库管理系统SQL Server 2005 处于运行状态，用户数据库STUDY完好。 （2）掌握数据库系统中“对象资源管理器”的使用方法。 （3）掌握聚集索引和非聚集索引的概念及其创建方法。 【方
2、案设计】 每个表只能有一个聚集索引，但可以有多个非聚集索引。一张表的主键本身就是一个聚集索引，因此若要对包含有主键的表再创建聚集索引，必须先移除主键才能创建。,下一页,返回,项目八 索引的创建和删除,索引可以创建在一个字段上，也可以创建在多个字段上。应先创建聚集索引而后再创建非聚集索引，因为表的正文数据是按照聚集索引来排序的。若先建非聚集索引再建聚集索引，则正文数据会按照聚集索引的列重新排序，会导致非聚集索引的无效。 【相关知识和技能】 一、创建索引的原因 当我们查阅某本书的某个章节时，为了提高查阅速度，不是从书的第一页开始顺序查找，而是首先找到书的目录索引，找到需要的章节在目录中的页码，然后
3、根据这一页码直接找到需要的章节。如果把表的数据看做书的内容，则索引就是书的目录。书的目录指向了书的内容（通过页码），同样，索引是表的关键值，它提供了指向表中行（记录）的指针。,上一页,下一页,返回,项目八 索引的创建和删除,目录中的页码是到达书内容的直接路径，而索引也是到达表数据的直接路径，从而可更高效地访问数据。在本项目中，我们要利用索引快速访问数据库表中的特定信息，为选定的表创建、编辑或删除索引。 二、索引的概念 索引是以表为基础的数据库对象，保存着表中排序的索引列，并且记录了索引列在数据表中的物理存储位置，实现了表中的数据的逻辑排序，提高了SQL Server 系统的性能，加快了数据的查
4、询速度和减少了系统的响应时间。它是由除存放表的数据页面以外的索引页面组成的。每个索引页面中的行都包含逻辑指针，通过该指针可以直接检索到数据，这就会加速物理数据的检索。对表中的列（字段）是否创建索引以及创建何种索引，对检索的速度会有很大的影响。,上一页,下一页,返回,项目八 索引的创建和删除,创建了索引的列几乎是立即响应，而未创建索引的列就需要等很长时间。因为对于未创建索引的列，SQL Server 需要逐行进行搜索，这种搜索耗费的时间直接同表中的数据量成正比。当数据量很大时，耗费的时间是难以想象的。 三、创建索引应考虑的主要因素 （1）如果一个表建有大量索引会影响insert、update 和
5、delete 语句的性能，因为在表中的数据更改时，所有索引都须进行适当的调整。 （2）覆盖的查询可以提高性能。覆盖的查询是指查询中所有指定的列都包含在同一个索引中。创建覆盖一个查询的索引可以提高性能，因为该查询的所有数据都包含在索引自身当中。 （3）对小型表进行索引可能不会产生优化效果，因为SQL Server 在遍历索引以搜索数据时，花费的时间可能会比简单的表扫描还长。,上一页,下一页,返回,项目八 索引的创建和删除,四、索引的分类 聚集索引会对表和视图进行物理排序，所以这种索引非常有效，每个表或视图只能有一个聚集索引。将表中的记录在物理数据页中的位置按索引字段值重新排序，再将重排后的结果写
6、回到磁盘上。如果表中没有聚集索引，SQL Server 会用主键列作为聚集索引。在语句“create index”中，使用“clustered”选项建立聚集索引。创建时注意： （1）每个表只能有一个聚集索引。 （2）表中的物理顺序和索引中的物理顺序是相同的。 （3）保证有足够的空间创建聚集索引。 可考虑将聚集索引用于下面几种情况：,上一页,下一页,返回,项目八 索引的创建和删除,（1）包含大量非重复值的列。 （2）使用下列运算符返回一个范围值的查询：“between”“”“=”“”和“=”。 （3）被连续访问的列。 （4）返回大型结果集的查询。 （5）经常被使用连接或group by 子句的列
7、。 2. 非聚集索引 非聚集索引与书中的索引类似，数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。非聚集索引不会对表或视图进行物理排序，具有与表的数据完全分离的结构，是由数据行指针和一个索引值构成的。考虑将非聚集索引用于下面的情况：,上一页,下一页,返回,项目八 索引的创建和删除,（1）包含大量非重复值的列，如姓氏和名字的组合（如果聚集索引用于其他列）。 （2）不返回大型结果集的查询。 （3）返回精确匹配查询的搜索条件（where 子句）中经常使用的列。 （4）在特定的查询中覆盖一个表中的所有列。 3. 唯一索引 唯一索引（Unique Index）表示表中任何两行记
8、录的索引值都不相同，与表的主键类似。它可以确保索引列不包含重复的值。,上一页,下一页,返回,项目八 索引的创建和删除,在多列唯一索引的情况下，该索引可以确保索引列中每个值组合都是唯一的。如果该索引列上已经存在重复值，系统就会报错。 五、合理使用索引 选择创建不同的索引要考虑实际情况，不同情况下创建聚集索引还是非聚集索引可参见表6-1。,上一页,返回,任务 索引的创建和删除,任务目标 （1）掌握在SQL Server Management Studio 中创建索引的方法。 （2）掌握在SQL Server Management Studio 中删除索引的方法。 （3）掌握使用SQL 语言创建索引
9、的方法。 （4）掌握使用SQL 语言删除索引的方法。 任务分析 对tb_student 学生信息表中的“studentid 学号”字段创建聚集索引，因为“studentid 学号”字段在整个列表中是唯一的；对学生信息表中的“name 学生姓名”字段创建非聚集索引，因为“name 学生姓名”字段可能会出现重复值；对tb_course 课程信息表中的“coursename 课程名称”字段创建唯一性索引；并掌握删除学生成绩数据库中表的索引的方法。,下一页,返回,任务 索引的创建和删除,任务准备 1. 使用SQL 语言创建索引的方法 只有表或视图的所有者才能为表创建索引，可以随时创建索引，无论表中是否
10、有数据。创建索引是通过create index 语句来完成的，其语法格式如下： create unique clustered | nonclustered index 索引名 on表|视图 (列 asc|desc, n) 各选项的含义如下： （1）unique：为表或视图创建唯一索引（不允许存在索引值相同的两行）。视图上的聚集索引必须是unique 索引。,上一页,下一页,返回,任务 索引的创建和删除,（2）clustered：创建聚集索引。如果没有指定clustered，则创建非聚集索引。具有聚集索引的视图称为索引视图。必须先为视图创建唯一聚集索引，然后才能为该视图定义其他索引。 （3）n
11、onclustered：创建一个指定表的逻辑排序的对象，即非聚集索引。每个表最多可以有249 个非聚集索引（无论这些非聚集索引的创建方式如何，是使用primary key 和unique约束隐式创建，还是使用create index 显式创建）。 （4）索引名：在表或视图中必须唯一，但在数据库中不必唯一。索引名必须遵循标识符规则。 （5）表：包含要创建索引的列的表。可以选择指定数据库和表所有者。 （6）视图：要建立索引的视图的名称。,上一页,下一页,返回,任务 索引的创建和删除,（7）列：应用索引的列。指定两个或多个列名，可为指定列的组合值创建组合索引。在table 后的圆括号中列出组合索引中
12、要包括的列（按排序优先级排列）。 （8）asc| desc 确定具体某个索引列的升序（asc）或降序（desc）排序方向。默认设置为asc（升序）。 （9）n 表示可以为特定索引指定多个列的占位符。 2. 使用SQL 语言删除索引的方法 从当前数据表中删除索引使用drop index 语句，其语法格式如下： drop index 表索引名 | 视图索引名 , n 其中表索引名和视图索引名是索引列所在的表或索引视图；index 是要除去的索引名称。索引名必须符合标识符的规则。n 表示可以指定多个索引的占位符。,上一页,下一页,返回,任务 索引的创建和删除,任务实施 一、索引的界面操作 1. 通过
13、SQL Server 2005界面操作为tb_student中的学号studentid字段创建聚集索引的方法 （1）打开SQL Server Management Studio 窗口，依次选择“数据库”“STUDY”中的表tb_student，然后在“索引”处右击，在打开的快捷菜单中执行“新建索引”命令，打开“新建索引”窗口，如图6-1 所示。 （2）在“索引名称”文本框中输入索引名称，这里输入studentID，如图6-2 所示。 （3）单击“添加”按钮，打开“从tb_student中选择列”对话框，如图6-3 所示：,上一页,下一页,返回,任务 索引的创建和删除,（4）设置其他选项，例如：
14、是创建聚集索引还是创建非聚集索引；是否创建唯一索引等。 （5）在“选择页”列表中，选择“选项”选项，可打开“选项”选项卡。在此选项卡中，可以设置其他一些选项。 （6）完成后，单击“确定”按钮，即可创建一个新的索引。 2. 通过SQL Server 2005 界面操作查看索引的方法 （1）在“对象资源管理器”窗口里，依次选择数据库“STUDY”中的表tb_student，然后在“索引”处右击鼠标，在打开的快捷菜单中执行“属性”命令，出现“属性索引”窗口，如图6-4 所示。 （2）在“索引属性”窗口的“常规”页中，可以查看或修改所选择的表或视图的索引的类型等属性信息。,上一页,下一页,返回,任务
15、索引的创建和删除,（3）在“索引属性”窗口的“选项”页中，可以查看或修改所选索引的属性。 （4）在“索引属性”窗口的“包含性列”页中，可以修改以非键列的形式包含在非聚集索引中的列集。 （5）在“索引属性”窗口的“存储”页中，可以查看或修改所选索引的文件组或分区方案属性。此页仅对聚集索引可用。 （6）在“索引属性”窗口的“空间”页中，可以查看或修改索引的空间属性。修改任何空间属性后，将会删除并重新创建该空间索引。 （7）在“索引属性”窗口的“碎片”页中，可以查看索引碎片的状态和重新组织索引。 3. 通过SQL Server 2005 界面操作删除索引的方法,上一页,下一页,返回,任务 索引的创建
16、和删除,（1）打开SQL Server Management Studio 窗口，依次选择数据库“STYDY”中的表tb_student，在要删除的索引上右击鼠标，如图6-5 所示。 （2）出现“删除对象”对话框，确认无误后，单击“确定”按钮，完成删除操作。 4. 通过SQL Server 2005 界面操作为表tb_student 中的姓名name 字段创建非聚集索引的方法 （1）详见“任务实施一、索引的界面操作的步骤（1）”。 （2）在“索引名称”文本框中输入索引名称，这里输入name1，如图6-6 所示。 （3）单击“添加”按钮，打开“从tb_student中选择列”对话框，如图6-7 所示。,上一页,下一页,返回,任务 索引的创建和删除,（4）其余步骤详见任务实施“一、索引的界面操作”的步骤1 中的（4）、（5）和（6）。 二、索引的语法使用 （1）通过T-SQL语言为表tb_course的coursename 课程名列创建索引。 use STUDY go create index cour
