《SQLServer数据库应用与开发》-第六章

项目八索引的创建和删除一、创建索引的原因当查阅某本书的某个章节时，为了提高查阅速度，不是从书的第一页开始顺序查找，而是首先找到书的目录索引，找到需要的章节在目录中的页码，然后根据这一页码直接找到需要的章节。如果把表的数据看作书的内容，则索引就是书的目录。书的目录指向了书的内容（通过页码），同样，索引是表的关键值，它提供了指向表中行（记录）的指针。目录中的页码是到达书的内容的直接路径，而索引也是到达表数据的直接路径，它使人们可更高效地访问数据。本项目利用索引快速访问数据库表中的特定信息，并为选定的表创建、编辑或删除索引。下一页返回项目八索引的创建和删除二、索引的概念索引是以表为基础的数据库对象，保存着表中排序的索引列，并且记录了索引列在数据表中的物理存储位置，实现了表中的数据的逻辑排序，提高了SQLServer系统的性能，加快了数据的查询速度，减少了系统的响应时间。它是由除存放表的数据页面以外的索引页面组成的。每个索引页面中的行都包含逻辑指针，通过该指针可以直接检索到数据，这会加速物理数据的检索速度。对表中的列（字段）是否创建索引以及创建何种索引，对检索的速度会有很大的影响。创建了索引的列几乎是立即响应，而未创建索引的列就需要等很长时间。上一页下一页返回项目八索引的创建和删除因为对于未创建索引的列，SQLServer需要逐行进行搜索，这种搜索耗费的时间直接同表中的数据量成正比。当数据量很大时，耗费的时间是难以想象的。三、创建索引应考虑的主要因素（1）如果一个表建有大量索引，其会影响insert、update和delete语句的性能，因为在表中的数据更改时，所有索引都需进行适当的调整。（2）覆盖的查询可以提高性能。覆盖的查询是指查询中所有指定的列都包含在同一个索引中。创建覆盖一个查询的索引可以提高性能，因为该查询的所有数据都包含在索引自身当中。上一页下一页返回项目八索引的创建和删除（3）对小型表进行索引可能不会产生优化效果，因为SQLServer在遍历索引以搜索数据时，花费的时间可能会比简单的表扫描还长。四、索引的分类1.聚集索引聚集索引会对表和视图进行物理排序，所以这种索引非常有效，每个表或视图只能有一个聚集索引。将表中的记录在物理数据页中的位置按索引字段值重新排序，再将重排后的结果写回磁盘。如果表中没有聚集索引，SQLServer会用主键列作为聚集索引。在语句“createindex”中，使用“clustered”选项建立聚集索引。上一页下一页返回项目八索引的创建和删除创建时应注意：（1）每个表只能有一个聚集索引；（2）表中的物理顺序和索引中的物理顺序是相同的；（3）保证有足够的空间创建聚集索引。可考虑将聚集索引用于下面几种情况：（4）包含大量非重复值的列；（5）使用下列运算符返回一个范围值的查询：between、＞、＞=、＜和＜=；（6）被连续访问的列；上一页下一页返回项目八索引的创建和删除（7）返回大型结果集的查询；（8）经常被使用连接或groupby子句的列。2.非聚集索引非聚集索引与书中的索引类似，数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。非聚集索引不会对表或视图进行物理排序，具有与表的数据完全分离的结构，其由数据行指针和一个索引值构成。考虑将非聚集索引用于下面的情况：（1）包含大量非重复值的列，如姓氏和名字的组合（如果聚集索引用于其他列）。上一页下一页返回项目八索引的创建和删除（2）不返回大型结果集的查询。（3）返回精确匹配的查询的搜索条件（where子句）中经常使用的列。（4）在特定的查询中覆盖一个表中的所有列。3.唯一索引唯一索引（uniqueindex）表示表中任何两行记录的索引值都不相同，与表的主键类似。它可以确保索引列不包含重复的值。在多列唯一索引的情况下，该索引可以确保索引列中每个值组合都是唯一的。如果该索引列上已经存在重复值，系统就会报错。上一页下一页返回项目八索引的创建和删除五、合理使用索引选择创建不同的索引时要考虑实际情况，在不同情况下创建聚集索引还是非聚集索引可参考表6−1。任务索引的创建和删除【任务准备】1.使用T−SQL语言创建索引的方法只有表或视图的所有者才能为表创建索引，可以随时创建索引，无论表中是否有数据。创建索引是通过createindex语句来完成的，其语法格式如下：上一页下一页返回项目八索引的创建和删除create[unique][clustered|nonclustered]index索引名on{表|视图}(列[asc|desc][,…n])各选项的含义如下：（1）unique为表或视图创建唯一索引（不允许存在索引值相同的两行）。视图上的聚集索引必须是unique索引。（2）clustered创建聚集索引。如果没有指定clustered，则创建非聚集索引。具有聚集索引的视图称为索引视图。必须先为视图创建唯一聚集索引，然后才能为该视图定义其他索引。上一页下一页返回项目八索引的创建和删除（3）nonclustered创建一个指定表的逻辑排序的对象，即非聚集索引。每个表最多可以有249个非聚集索引（无论这些非聚集索引的创建方式如何，是使用primarykey和unique约束隐式创建，还是使用createindex显式创建）。（4）索引名：在表或视图中必须唯一，但在数据库中不必唯一。索引名必须遵循标识符规则。（5）表：包含要创建索引的列的表。可以选择指定数据库和表所有者。（6）视图：要建立索引的视图的名称。上一页下一页返回项目八索引的创建和删除（7）列：应用索引的列。指定两个或多个列名，可为指定列的组合值创建组合索引。在table后的圆括号中列出组合索引中要包括的列（按排序优先级排列）。（8）［asc|desc］确定具体某个索引列的排序方向［升序（asc）或降序（desc）］，默认设置为asc（升序）。（9）n表示可以为特定索引指定多个列的占位符。2.使用T−SQL语言删除索引的方法从当前数据表中删除索引使用dropindex语句，其语法格式如下：上一页下一页返回项目八索引的创建和删除dropindex表索引名|视图索引名[,…n]其中，表索引名和视图索引名是索引列所在的表或索引视图；index是要除去的索引名称。索引名必须符合标识符的规则。n表示可以指定多个索引的占位符。【任务实施】一、索引的界面操作（1）通过SQLServer2008界面操作为表tb_student中的“studentid”（学号）字段创建聚集索引方法如下：上一页下一页返回项目八索引的创建和删除①打开“SQLServerManagementStudio”窗口，选择数据库STUDY中的表tb_student，然后在“索引”处单击鼠标右键，在打开的快捷菜单中执行“新建索引”命令，打开“新建索引”窗口，如图6−1所示。②在“索引名称”文本框中输入索引名称，这里输入“studentID”，如图6−2所示。③单击“添加”按钮，打开“从‘dbo.tb_student’中选择列”对话框，如图6−3所示。④设置其他选项，例如是创建聚集索引还是创建非聚集索引、是否创建唯一索引等。上一页下一页返回项目八索引的创建和删除⑤在“选择页”列表中，选择“选项”选项，可打开“选项”选项卡。在此选项卡中，可以设置一些其他选项。⑥完成后，单击“确定”按钮，即可创建一个新的索引。（2）通过SQLServer2008界面操作查看索引的方法如下：①在“对象资源管理器”窗口里，选择数据库STUDY中的表tb_student，然后在“索引”处单击鼠标右键，在打开的快捷菜单中执行“属性”命令，出现“属性索引”窗口，如图6−4所示。②在“索引属性”窗口的“常规”页中，可以查看或修改所选择的表或视图的索引的类型等属性信息。上一页下一页返回项目八索引的创建和删除③在“索引属性”窗口的“选项”页中，可以查看或修改所选索引的属性。④在“索引属性”窗口的“包含性列”页中，可以修改以非键列的形式包含在非聚集索引中的列集。⑤在“索引属性”窗口的“存储”页中，可以查看或修改所选索引的文件组或分区方案属性。此页仅对聚集索引可用。⑥在“索引属性”窗口的“空间”页中，可以查看或修改索引的空间属性。修改任何空间属性后，将会删除并重新创建该空间索引。⑦在“索引属性”窗口的“碎片”页中，可以查看索引碎片的状态和重新组织索引。上一页下一页返回项目八索引的创建和删除（3）通过SQLServer2008界面操作删除索引的方法如下：①打开“SQLServerManagementStudio”窗口，选择数据库STYDY中的表tb_student，在要删除的索引上单击鼠标右键，如图6−5所示。②出现“删除对象”对话框，确认无误后，单击“确定”按钮，完成删除操作。（4）通过SQLServer2008界面操作为表tb_student中的“name”（姓名）字段创建非聚集索引方法如下：①详见【任务实施】“一、索引的界面操作”的步骤（1）。上一页下一页返回项目八索引的创建和删除②在“索引名称”文本框中输入索引名称，这里输入“name1”，如图6−6所示。③单击“添加”按钮，打开“从‘dbo.tb_student’中选择列”对话框，如图6−7所示。④其余步骤详见【任务实施】“一、索引的界面操作”的步骤（4）、（5）、（6）。二、索引的语法使用（1）通过T−SQL语言为表tb_course的“coursename”（课程名）”列创建索引。上一页下一页返回项目八索引的创建和删除useSTUDYgocreateindexcourse_nameontb_course(coursename)（2）创建聚集索引，根据表tb_student中“studentid（学号）”列创建唯一聚集索引。createuniqueclusteredindexstudent_nameontb_student(studentid)（3）删除STUDY数据库中表tb_course中一个索引名为“course_name”的索引。dropindextb_course.course_name上一页返回项目九数据约束和数据完整性一、数据的约束SQLServer2008提供了多种对表数据的强制约束，主要有以下几种。1.primarykey（主键）约束在表中定义一个主键，唯一地标识表中的行，一个表有且只有一个主键约束。也就是说，在数据表中不能存在主键值相同的两行数据，而且位于主键约束下的数据应使用确定的数据，不能输入NULL来代替确定的数据。在管理数据时，应确保每个数据表都拥有自己唯一的主键，从而实现数据的实体完整性。下一页返回项目九数据约束和数据完整性在SQLServer2008中，主键约束的创建方式有两种：（1）作为表定义的一部分在创建表时创建；（2）对没有主键的表添加主键约束。2.unique约束unique约束强制执行值的唯一性。对于unique约束中的列，表中不允许有两行包含相同的非空值。3.foreignkey（外键）约束用于强制实现表之间的参照完整性，外键必须和主表的主键或唯一键对应，外键约束不允许为空值。4.CHECK约束上一页下一页返回项目九数据约束和数据完整性CHECK约束实际上是字段输入内容的验证规则，表示一个字段的输入内容必须满足CHECK约束的条件，若不满足，则数据无法正常输入。可以为每列指定多个CHECK约束，它通过对一个逻辑表达式的结果进行判断来对数据进行检查。5.default（默认值）约束默认值约束是指在用户没有提供某些列的数据时，数据库系统为用户提供的默认值。若将表中某列定义了默认值约束后，用户在插入新的数据行时，如果没有为该列指定数据，那么系统将默认值赋给该列，当然该默认值也可以是空值（NULL）。上一页下一页返回项目九数据约束和数据完整性默认值（约束）是一种数据库对象，可以绑定到一个或多个列上。6.identity属性identity是标识的意思，也即“唯一”的意思。每张数据库表都需要有主键，用于唯一标识该数据集，然而在现实社会中常常有许多事物本身不存在唯一的属性，例如：同样规格、色彩，同样价格的衣服每天被销售许多次，销售时间固然可以作为唯一属性，但人们并不关心，只要有简单的销售序号就可解决该表主键的问题了，事实上序号的值是多少人们也并不关心，这时候，就可以为该表增加一个具有唯一标识的序号列。上一页下一页返回项目九数据约束和数据完整性SQLServer2008在系统中专门安排了列的identity属性（即标识属性），它有唯一值，作为表的主键，当然也不为空，一旦设置了某列的identity属性，则其属性值无须用户提供，系统在每增加一行记录时自动增量赋值，在默认情况下标识列的初始值（标识种子）和增量（标识增量）均为一。需要注意的是，每个表只能为一个列设置标识属性，且该列的数据类型只能是整型类的，如decimal、int、numeric、smallint、bigint或tinyint等。上一页下一页返回项目九数据约束和数据完整性在数据库STUDY的表tb_score中，同一个学号可以选修多门课程并获取相应的成绩，既可以将学号和课程号这两列联合起来组成主键，也可以为该表增加一个标识列作为主键。比较起来，后者更简便、更常用。二、数据完整性数据完整性是数据库设计方面一个非常重要的问题，数据完整性代表数据的正确性、一致性与可靠性。实施数据完整性的目的在于确保数据的质量。根据数据的完整性所作用的数据库对象和范围的不同，数据完整性分为实体完整性、域完整性、参照完整性三种。上一页下一页返回项目九数据约束和数据完整性（1）实体完整性：又称为行完整性，把数据表中的每行看作一个实体，要求所有行都具有唯一标识。实体完整性可由主键来实现。表中的主键在所有记录上的取值必须唯一。例如，图书表中，图书编号必须唯一，以保证每一种图书的唯一性。通过索引、unique约束、主键约束或identity属性可实现数据的实体完整性。（2）域完整性：又称为列完整性，指给定列输入的有效性。要求数据表中指定列的数据具有正确的数据类型、格式和有效的数据范围。实现域完整性的方法有：限制类型（通过数据类型）、格式（通过CHECK约束和规则）或可能的取值范围（通过CHECK约束、default定义、notNULL定义和规则）等。上一页下一页返回项目九数据约束和数据完整性（3）参照完整性：又称为引用完整性，用于确保相关联的表间的数据保持一致，避免因一个表的记录修改，造成另一个表的内容变为无效的值。参照完整性是相关联的两个表之间的约束，是通过表的外键和主键来维护的。对于两张存在实际联系的表，而不建立相互之间的约束，那么，独立地对其中一张表的记录进行增加、修改、删除，将会影响另一张表的数据完整性。任务一设置默认值约束、unique约束、CHECK约束和identity属性【知识准备】上一页下一页返回项目九数据约束和数据完整性参见本项目【相关知识和技能】中“一、数据的约束”的内容。【任务实施】一、利用界面操作设置默认值约束为数据库表tb_student的“sex”字段设置默认值的步骤如下：启动SQLServerManagementStudio工具，在“对象资源管理器”窗口中展开数据库STUDY，选择表tb_student，用鼠标右键单击“设计”，在右面的表设计器中选中需要设置默认值的字段（“sex”字段），在下面的“列属性”窗口中“常规”栏的“默认值或绑定”行中输入默认值为1，保存后再打开，就可看到图6−8所示的情况。上一页下一页返回项目九数据约束和数据完整性二、利用界面操作设置unique约束为表tb_student的“name”字段设置unique约束的步骤如下：（1）启动SQLServerManagementStudio工具，在“对象资源管理器”窗口中展开数据库STUDY，选择表tb_student，用鼠标右键单击“设计”，用鼠标右键单击界面的空白处，选择“索引/键”命令，如图6−9所示，出现“索引/键”窗口，在“类型”栏中选择“唯一键”，如图6−10所示。上一页下一页返回项目九数据约束和数据完整性（2）在“列”栏的右边单击，弹出“索引列”窗口，选择字段“name”，如图6−11所示，单击“确定”按钮返回，关闭“索引/键”窗口，保存表tb_student，刷新界面，可在图6−12所示的界面上看到该unique是一个字段“name”上的非聚集唯一索引（也可通过新建索引来完成创建，同学们可自己尝试）。三、利用界面操作设置CHECK约束为表tb_student的“sumcredit（总学分）”字段设置CHECK约束的步骤如下：上一页下一页返回项目九数据约束和数据完整性（1）启动SQLServerManagementStudio工具，在“对象资源管理器”窗口中展开数据库STUDY，选择表tb_score，用鼠标右键单击“设计”，用鼠标右键单击界面的空白处，选择“CHECK约束”命令，如图6−13所示，出现“CHECK约束”窗口，如图6−14所示。（2）在“CHECK约束”窗口中单击“添加”按钮，单击“表达式”栏的右面，出现“CHECK约束表达式”窗口，如图6−15所示，输入“score＞=0andscore＜=100”，单击“确定”按钮，单击“关闭”按钮，保存表设计器窗口，完成了对“sumcredit”字段进行的CHECK约束的设置。上一页下一页返回项目九数据约束和数据完整性四、利用界面操作设置表字段的identity属性将表tb_score中的“id”字段设置成identity属性的步骤如下：（1）启动SQLServerManagementStudio工具，在“对象资源管理器”窗口中展开数据库STUDY，选择tb_score表，单击鼠标右键，选择“设计”。（2）选择“id”字段（其类型为tinyint，属于整型系列），在“列属性”栏中展开“标识规范”节点，在“是标识”右面的下拉菜单里选择“是”，则标量增量和标量种子均自动变为1（若需要可以更改“1”这个值），如图6−16所示。上一页下一页返回项目九数据约束和数据完整性任务二创建主键、外键并实现参照完整性【知识准备】（1）创建主键约束的语法如下：createtable数据表名({<列定义><列的约束>}[,…n][约束名>]primarykey(列名1)[,...n]（2）参照完整性知识请参见本项目【相关知识与技能】中的“二、数据完整性”。上一页下一页返回项目九数据约束和数据完整性【任务实施】（1）通过“对象资源管理器”为表tb_student中的studentid定义主键约束。①启动SQLServerManagementStudio工具，在“对象资源管理器”窗口中依次打开各节点到要修改的表，例如表tb_student，单击鼠标右键,在弹出的快捷菜单中选择“设计”命令,打开表设计窗口。②选择指定的列，在列名的左侧出现三角符号，如果设置的主键为多个，则按住Ctrl键单击相应的列，在选中的列上单击鼠标右键，在弹出的快捷菜单中选择“设置主键”命令，如图6−17所示，这时选定的列左侧显示一个钥匙图标,表示该列是主键列，如图6−18所示。上一页下一页返回项目九数据约束和数据完整性（2）通过“对象资源管理器”定义主、从表间的参照关系。①创建表的主键，如果在创建表的时候已经定义了表tb_student中的学号字段“studentid”为主键，那么就不需要再定义主表的主键了。②启动“SQLServerManagementStudio”，在“对象资源管理器”中展