单元4数据库的索引、默认值和规则

1、高等教育出版社目录单元1 SQL SERVER 2008基础单元2 创建数据库和表单元3 数据库的查询和视图单元4 数据库的索引、默认值和规则单元5 数据库的存储过程和触发器单元6 数据库的日常管理与维护单元7 数据库的安全管理单元8 SQL Server 数据库应用程序开发单元9 数据库在电子商务中的应用单元4 数据库的索引、默认值和规则索引、默认值和规则都是SQL Server数据库中包含的对象，索引类似于图书的目录，它可以帮助数据库应用程序快速找到表中特定的数据，而无需扫描表中的全部数据，主要用于提高数据的查询速度。默认值和规则用来控制应用系统中的数据，类似于信息预过滤器，主要用于将数据

2、写入数据库之前先校验规则及默认值。本单元以“图书信息管理”数据库为例，学习SQL Server中索引、默认值和规则三个数据库对象的创建和使用方法。单元说明单元4 数据库的索引、默认值和规则1掌握使用对象资源管理器和CREATE INDEX语句创建索引的方法。2会管理索引。3掌握创建默认值对象和规则对象的方法。4会绑定默认值对象和规则对象。5会解除默认值对象和规则对象的绑定。6会删除默认值对象和规则对象。技能目标1 了解索引的概念、类型和作用。2 掌握默认值对象的创建、绑定、解除绑定、删除等操作。3 掌握规则对象的创建、绑定、解除绑定、删除等操作。知识目标单元4 数据库的索引、默认值和规则任务4

3、.1 使用索引任务4.2 使用默认值对象任务4.3 使用规则对象任务4.1 使用索引用对象资源管理器和CREATE INDEX语句创建索引，并学会管理索引。任务说明1.会用对象资源管理器创建索引。2.会用CREATE INDEX语句创建索引。3.会用对象资源管理器查看索引及删除索引。4.会用DROP INDEX语句删除索引。任务目标任务4.1 使用索引活动4.1.1 创建索引活动4.1.2 管理索引活动4.1.1 创建索引索引是数据库重要对象之一，可以用它来提高表中数据的访问速度。在SQL Server 2008中创建索引的方法主要有两种，一是在对象资源管理器中通过方便的图形化向导创建，二是通

4、过编写CREATE INDEX语句创建。案例1 使用对象资源管理器给“图书信息”表的“书名”列设置唯一非聚集索引，索引名称为“IX_书名”。小贴士当给表设置PRIMARY KEY约束或UNIQUE约束时，SQL SERVER会自动在相应的列上创建聚集索引或非聚集索引。案例2 使用CREATE INDEX语句给“图书库存”表的“ISBN”列设置非唯一非聚集索引，索引名称为“IX_ISBN”。【代码如下】USE 图书信息管理GOCREATE NONCLUSTERED INDEX IX_ISBN ON 图书库存(ISBN)活动4.1.1 创建索引【体验活动】1.用对象资源管理器给“学生信息”表的“姓

5、名”列设置非唯一非聚集索引，索引名称为“IX_姓名”。2.用CREATE INDEX语句给“课程信息”表的“课程名称”列设置唯一非聚集索引，索引名称为“IX_课程名称”。活动4.1.2 管理索引对于创建好的索引，有时需要查看索引的属性，对于不再使用或者很少使用的索引，应该及时删除。案例3 使用对象资源管理器查看“图书库存”表的“IX_ISBN”索引的相关属性。小贴士如图4.1.6所示，在弹出的快捷菜单中有一个“重命名”命令，选择该命令可以直接给索引重命令。一个表只能有一个聚集索引，如果要更改，则必须先删除已有的聚集索引。一个表可以创建若干个非聚集索引。通过图4.1.7示的“索引属性”对话框，不

6、但可以查看该索引是聚集索引还是非聚集索引，还能查看是否唯一、所引用的索引列有哪些等信息。并能通过该属性对话框更改索引的类型，添加或删除索引列等。活动4.1.2 管理索引案例4 使用对象资源管理器删除“图书信息”表的索引“IX_书名”。案例5 使用DORP INDEX语句删除“图书库存”表的索引“IX_ISBN”。【代码如下】USE 图书信息管理GODROP INDEX IX_ISBN ON 图书库存或USE 图书信息管理GODROP INDEX 图书库存.IX_ISBN小贴士SQL Server自动建立的索引不能用DROP INDEX删除，只能用ALTER TABLE语句中的DROP CONS

7、TRAINT子句来删除该字段上的主键约束或唯一性约束，这些约束删除了，相关的索引也被删除了。活动4.1.2 管理索引1.认识索引我们都有过查字典的经历，在查找一个生字时，首先会根据拼音或部首按照规定的查找方法到目录中找到对应汉字所在的页码，再翻阅到指定的页码找到要查找的生字。索引是对表或视图中的一列或多列的值进行排序的一种物理结构，它包含从表或视图中的一个或多个列生成的键，以及映射到指定数据的存储位置的指针。不同的索引对应不同的排序方法，就像查字典时如果使用拼音法，则按汉字的拼音进行排序；如果使用部首法，则按汉字的部首进行排序。大家知道，这两种方法也只是对应了目录中的不同排序方式，而字典中汉字

8、的物理顺序却始终只有一种。知识宝库活动4.1.2 管理索引索引类似于图书的目录，在一本书中，利用目录可以快速找到所需信息在书中的位置，而不用翻阅整本书。在数据库中，索引使数据库应用程序快速找到表中特定的数据，而不用扫描整个表，可以大大提高数据的查询速度。索引和原始表之间的关系如图4.1.9所示。知识宝库活动4.1.2 管理索引2.索引的分类（1）按数据的物理存放顺序：聚集索引(CLUSTERED)：聚集索引是对表的物理数据按照索引列进行排序，然后再重新存储到磁盘上。由于聚集索引对表中的数据一一进行了排序，因此用聚集索引查找数据很快。但由于聚集索引对表中的数据进行了重新排列，它所需要的存储空间会

9、特别大，大概相当于表中数据所占空间的120%。表的数据行只能按一种排序方式存储在磁盘上，所以一个表只能有一个聚集索引。非聚集索引(NONCLUSTERED)：非聚集索引具有与表的数据完全分离的结构，使用非聚集索引不用将物理数据按列排序，而是存储索引行，每个索引行均包含非聚集索引键值和一个或多个指向包含该值的数据行的行定位器。SQL Serve 在搜索数据时，先对非聚集索引进行搜索，然后再通过相应的行定位器从表中找到对应的数据。一个表可以有多个非聚集索引。知识宝库活动4.1.2 管理索引一般情况下，先创建聚集索引，后创建非聚集索引，因为创建聚集索引会改变表中行的顺序，从而会影响到非聚集索引。（2

10、）按索引列取值的唯一性：唯一索引：要求被索引列不能有重复值，也不能有两个NULL。创建主键约束时，系统会自动为表创建一个唯一聚集索引。创建唯一约束时，系统会自动为表创建一个唯一非聚集索引。非唯一索引：不受重复值的约束。3.索引的作用建立索引有以下优点：建立了以索引列作为查询条件时，能大大提高数据的查询速度。索引可以加快表与表之间的连接。在查询中，可以明显减少排序和分组所用的时间。知识宝库活动4.1.2 管理索引4.创建索引的规则创建索引的规则有以下几点：避免在一个表上创建大量的索引，因为创建索引要花费时间和占用物理空间，而且虽然索引能加快查询速度，但减慢了数据更新的速度，因为每执行一次数据修改

11、，就需要对索引进行维护。 对于经常需要作为查询条件的列可以创建索引。在经常需要排序的列上可以创建索引。当修改的性能需求远大于查询的性能需求时，不要创建索引。知识宝库活动4.1.2 管理索引5.创建索引索引可以使用对象资源管理器直接创建，也可以使用CREATE INDEX语句进行创建，使用CREATE INDEX创建索引的语句格式如下：CREATE UNIQUECLUSTERED|NONCLUSTERED INDEX 索引名ON 表或视图(列名ASC|DESC)UNIQUE表示创建唯一索引，若省略则默认创建非唯一索引。CLUSTERED|NONCLUSTERED表示创建的索引为聚集索引还是非聚集

12、索引，若省略则默认创建非聚集索引。ASC|DESC表示索引是按升序还是降序排列，若省略则默认为升序。知识宝库活动4.1.2 管理索引6.删除索引对于不再需要的索引，应该及时进行删除，删除索引既可以使用对象资源管理器直接删除，也可以使用DROP INDEX语句进行删除，使用DROP INDEX删除索引有两种方法，语句格式分别如下：DROP INDEX 索引名 ON 表名或视图名或者DROP INDEX 表名.索引名或视图名.索引名知识宝库活动4.1.2 管理索引【体验活动】1.使用对象资源管理器查看“课程信息”表的“IX_课程名称”索引属性。2.使用对象资源管理器删除“学生信息”表的索引“IX_

13、姓名”。3.使用DROP INDEX语句删除“课程信息”表的索引“IX_课程名称”。任务4.2 使用默认值对象掌握默认值对象的创建、绑定、解除绑定、删除等操作。任务说明1.会创建默认值对象。2.会绑定默认值对象。3.会解除默认值对象的绑定。4.会删除默认值对象。任务目标任务4.2 使用默认值对象活动4.2.1 创建默认值对象活动4.2.2 默认值对象的绑定活动4.2.3 解除默认值对象的绑定活动4.2.4 删除默认值对象活动4.2.1 创建默认值对象使用默认值对象之前必须先创建默认值对象，可以使用CREATE DEFAULT语句创建默认值对象。案例7 在“图书信息管理”数据库中使用CREATE

14、 DEFAULT语句创建默认值对象“MR_所在地”，值为“北京市”。【代码如下】USE 图书信息管理GOCREATE DEFAULT MR_所在地 AS 北京市活动4.2.2 默认值对象的绑定默认值对象创建好后，需要把它绑定到需要的列上，这样才能实现在插入数据时，如果没有为该列指定数值，则系统自动为其添加上指定的默认值。案例8 将“MR_所在地”默认值对象绑定到“出版社”表“所在地”列。【代码如下】USE 图书信息管理GOSP_BINDEFAULT MR_所在地,出版社.所在地小贴士如果列同时绑定默认值对象和规则对象，则默认值不能违反规则。与规则冲突的默认值将永远不能插入列，因为每次试图插入这

15、样的默认值时都会出错。一个默认值对象可以绑定到多个不同的列上。不能将默认值对象绑定到标识列或已经有DEFAULT约束的列上。活动4.2.3 解除默认值对象的绑定如果不再需要绑定默认值对象，则可以使用SP_UNBINDEFAULT语句将绑定从列上解除。案例9 解除“MR_所在地”默认值对象在“出版社”表“所在地”列的绑定。【代码如下】USE 图书信息管理GOSP_UNBINDEFAULT 出版社.所在地活动4.2.4 删除默认值对象当不再使用默认值对象时，可以使用DROP DEFAULT语句来删除默认值对象，删除默认值对象前一定要确保该默认值对象已经没有绑定到任何列或用户定义数据类型上。案例10

16、 删除“图书信息管理”数据库中的“MR_所在地”默认值对象。【代码如下】USE 图书信息管理GODROP DEFAULT MR_所在地小贴士在对象资源管理器中，右键单击要删除的默认值对象，然后选择“删除”命令，也可以删除默认值对象。删除默认值对象前，必须先将默认值对象从它所绑定的列上解除，否则在执行删除语句时会出现如图4.2.6所示错误信息。活动4.2.4 删除默认值对象在SQL Server 2008数据库系统中，除了使用DEFAULT约束外，还可以建立默认值对象。建立默认值对象后，需要把它绑定到表中的列或用户定义的数据类型上才能发挥作用。1.创建默认值对象默认值就是当用户向表中添加数据时，

17、没有明确地给出，而由SQL Server自动指派的值。默认值可以定义为数据库对象，使用时需将其绑定到特定的列上。在SQL Server 2008中使用CREATE DEFAULT语句来创建默认值对象，语句格式如下：CREATE DEFAULT 默认值对象名 AS 常量表达式常量表达式可以是常量、内置函数或表达式。常量表达式如果是字符或日期时间型数据，必须用单引号（）括起来。CREATE DEFAULT必须是查询批次中的第一条语句。默认值必须与要绑定的列数据类型兼容，否则在插入默认值时会出错。只能在当前数据库中创建默认值对象。如果默认值的长度大于它所绑定列的最大长度，该默认值会被截断。知识宝库活

18、动4.2.4 删除默认值对象2.绑定默认值对象默认值对象创建好后，应该将默认值对象绑定到列上，这样才能将创建的默认值对象应用到列中。绑定默认值对象的语句格式如下：EXEC SP_BINDEFAULT 默认值对象名，表名.列名一个默认值对象可以绑定到多个不同的列上。不能将默认值对象绑定到标识列或已经有DEFAULT约束的列上。3.解除默认值对象的绑定如果不再需要使用默认值对象，可以把它从表列上解除绑定，语句格式如下：EXEC SP_UNBINDEFAULT 表名.列名解除默认值对象的绑定后，只是把默认值对象与所绑定列的关系解除掉了，但并没有删除默认值对象，所以该默认值对象还在系统内，如果默认值对

19、象绑定到多个列上，则对没有解除绑定的列还继续有效。知识宝库活动4.2.4 删除默认值对象4.删除默认值对象从数据库中删除一个默认值对象时，分两种情况来处理：若该默认值对象没有绑定到表列中，则直接使用DROP DEFAULT语句进行删除；若该默认值对象已经绑定到表列中，则应该先使用SP_UNBINDEFAULT解除该默认值对象在表列上的绑定，再使用DROP DEFAULT语句进行删除。使用DROP DEFAULT语句删除默认值对象的语句格式如下：DROP DEFAULT 默认值对象名，默认值对象名可以一次删除多个默认值对象，注意多个默认值对象之间要用逗号(，)隔开。知识宝库活动4.2.4 删除默

20、认值对象5.默认约束（DEFAULT约束）与默认值对象的区别默认约束是在创建或修改表时，用DEFAULT子句为表中的列定义默认值，它是针对表中的某个列定义的，仅对该列有效。因为该约束定义与表存储在一起，当删除表时，将自动删除该约束。如果多个列需要相同的默认值时，使用默认约束就必须对每个列都进行定义。默认值对象是在数据库内用CREATE DEFAULT语句创建的，是数据库对象之一，可以绑定到用户定义数据类型或数据库中的一个或多个列上。使用时必须将它绑定到列上。如果多个列需要相同的默认值时，只需要在数据库中创建一个默认值对象，然后把它绑定到需要的列上即可。知识宝库活动4.2.4 删除默认值对象【体

21、验活动】1. 在“学生成绩管理”数据库中使用CREATE DEFAULT语句创建默认值对象“MR_所学专业”，值为“电子商务”。2. 将“MR_所学专业”默认值对象绑定到“学生信息”表“所学专业”列。3. 解除“MR_所学专业”默认值对象在“学生信息”表“所学专业”列的绑定。4. 删除“学生成绩管理”数据库中的“MR_所学专业”默认值对象。任务4.3 使用规则对象掌握规则对象的创建、绑定、解除绑定、删除等操作。任务说明1.会创建规则对象。2.会绑定规则对象。3.会解除规则对象的绑定。4.会删除规则对象。任务目标任务4.3 使用规则对象活动4.3.1 创建规则对象活动4.3.2 绑定规则对象活动

22、4.3.3 解除规则对象的绑定活动4.3.4 删除规则对象活动4.3.1 创建规则对象使用规则对象之前必须先创建规则对象，可以使用CREATE RULE语句创建规则对象。案例11 在“图书信息管理”数据库中用CREATE RULE语句创建规则对象“GZ_数量”，用来限制列的取值必须大于0。【代码如下】USE 图书信息管理GOCREATE RULE GZ_数量 AS SL0 说明：上述CREATE RULE语句中“GZ_数量”是规则名，“SL0”是规则的条件活动4.3.2 绑定规则对象规则对象创建好后，必须要绑定到列上才能生效。绑定规则可以使用SP_BINDRULE语句。案例12 将“GZ_数量

23、”规则对象绑定到“图书信息”表“数量”列，用来限制“数量”列的取值必须大于0。【代码如下】USE 图书信息管理GOSP_BINDRULE GZ_数量,图书信息.数量 小贴士一个列上只能绑定一个规则，如果列上已经绑定了规则，当再次向它们绑定规则时，旧规则将被新规则覆盖。绑定规则后，当有不符合规则的数据输入时，系统会给出错误信息，拒绝接受该数据。活动4.3.3 解除规则对象的绑定如果某列绑定的规则对象不再需要，可以使用SP_UNBINDRULE语句将绑定从列上解除。案例13 解除“GZ_数量”规则对象在“图书信息”表“数量”列的绑定。【代码如下】USE 图书信息管理GOSP_UNBINDRULE

24、图书信息.数量 活动4.3.4 删除规则对象当不再使用规则对象时，可以使用DROP RULE语句来删除规则对象，删除规则对象前一定要确保该规则对象已经没有绑定到任何列上。案例14 删除“图书信息管理”数据库中的“GZ_数量”规则对象【代码如下】USE 图书信息管理GODROP RULE GZ_数量 小贴士在对象资源管理器中右键单击要删除的规则对象，选择“删除”命令，也可以删除规则对象。删除规则对象前，必须先将规则对象从它所绑定的列上解除，否则在执行删除语句时会出现如图4.3.5所示的错误信息。活动4.3.4 删除规则对象规则也是数据库的一个对象，用于执行一些与CHECK约束相同的功能。规则在使

25、用中也需要将其绑定到列上，用来指定列可以接受哪些数值。1.创建规则对象在SQL Server 2008中使用CREATE RULE语句来创建规则对象，语句格式如下：CREATE RULE 规则对象名 AS 条件表达式条件表达式用于指定列值的格式或取值范围，不能引用列名或其他数据库对象名。条件表达式中包含一个以开头的局部变量，该变量代表插入或修改该列时用户输入的数值。CREATE RULE必须是查询批次中的第一条语句。只能在当前数据库中创建规则对象。知识宝库活动4.3.4 删除规则对象2.绑定规则对象规则对象创建好后，必须绑定到列上才能生效。绑定规则对象的语句格式如下：EXEC SP_BINDRULE 规则对象名，表名.列名一个规则对象可以绑定到多个不同的列上。一个列上只能绑定一个规则，如果列上已经绑