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

高等教育出版社目录单元1SQLSERVER2008基础单元2创建数据库和表单元3数据库的查询和视图单元4数据库的索引、默认值和规则单元5数据库的存储过程和触发器单元6数据库的日常管理与维护单元7数据库的安全管理单元8SQLServer数据库应用程序开发单元9数据库在电子商务中的应用单元4数据库的索引、默认值和规则索引、默认值和规则都是SQLServer数据库中包含的对象，索引类似于图书的目录，它可以帮助数据库应用程序快速找到表中特定的数据，而无需扫描表中的全部数据，主要用于提高数据的查询速度。默认值和规则用来控制应用系统中的数据，类似于信息预过滤器，主要用于将数据写入数据库之前先校验规则及默认值。本单元以“图书信息管理”数据库为例，学习SQLServer中索引、默认值和规则三个数据库对象的创建和使用方法。单元说明单元4数据库的索引、默认值和规则1．掌握使用对象资源管理器和CREATEINDEX语句创建索引的方法。2．会管理索引。3．掌握创建默认值对象和规则对象的方法。4．会绑定默认值对象和规则对象。5．会解除默认值对象和规则对象的绑定。6．会删除默认值对象和规则对象。技能目标1．了解索引的概念、类型和作用。2．掌握默认值对象的创建、绑定、解除绑定、删除等操作。3．掌握规则对象的创建、绑定、解除绑定、删除等操作。知识目标单元4数据库的索引、默认值和规则任务4.1使用索引任务4.2使用默认值对象任务4.3使用规则对象任务4.1使用索引用对象资源管理器和CREATEINDEX语句创建索引，并学会管理索引。任务说明1.会用对象资源管理器创建索引。2.会用CREATEINDEX语句创建索引。3.会用对象资源管理器查看索引及删除索引。4.会用DROPINDEX语句删除索引。任务目标任务4.1使用索引活动4.1.1创建索引活动4.1.2管理索引活动4.1.1创建索引索引是数据库重要对象之一，可以用它来提高表中数据的访问速度。在SQLServer2008中创建索引的方法主要有两种，一是在对象资源管理器中通过方便的图形化向导创建，二是通过编写CREATEINDEX语句创建。案例1使用对象资源管理器给“图书信息”表的“书名”列设置唯一非聚集索引，索引名称为“IX_书名”。『小贴士』当给表设置PRIMARYKEY约束或UNIQUE约束时，SQLSERVER会自动在相应的列上创建聚集索引或非聚集索引。案例2使用CREATEINDEX语句给“图书库存”表的“ISBN”列设置非唯一非聚集索引，索引名称为“IX_ISBN”。【代码如下】USE图书信息管理GOCREATENONCLUSTEREDINDEXIX_ISBNON图书库存(ISBN)活动4.1.1创建索引【体验活动】1.用对象资源管理器给“学生信息”表的“姓名”列设置非唯一非聚集索引，索引名称为“IX_姓名”。2.用CREATEINDEX语句给“课程信息”表的“课程名称”列设置唯一非聚集索引，索引名称为“IX_课程名称”。活动4.1.2管理索引对于创建好的索引，有时需要查看索引的属性，对于不再使用或者很少使用的索引，应该及时删除。案例3使用对象资源管理器查看“图书库存”表的“IX_ISBN”索引的相关属性。『小贴士』①如图4.1.6所示，在弹出的快捷菜单中有一个“重命名”命令，选择该命令可以直接给索引重命令。②一个表只能有一个聚集索引，如果要更改，则必须先删除已有的聚集索引。③一个表可以创建若干个非聚集索引。④通过图4.1.7示的“索引属性”对话框，不但可以查看该索引是聚集索引还是非聚集索引，还能查看是否唯一、所引用的索引列有哪些等信息。并能通过该属性对话框更改索引的类型，添加或删除索引列等。活动4.1.2管理索引案例4使用对象资源管理器删除“图书信息”表的索引“IX_书名”。案例5使用DORPINDEX语句删除“图书库存”表的索引“IX_ISBN”。【代码如下】USE图书信息管理GODROPINDEXIX_ISBNON图书库存或USE图书信息管理GODROPINDEX图书库存.IX_ISBN『小贴士』SQLServer自动建立的索引不能用DROPINDEX删除，只能用ALTERTABLE语句中的DROPCONSTRAINT子句来删除该字段上的主键约束或唯一性约束，这些约束删除了，相关的索引也被删除了。活动4.1.2管理索引1.认识索引我们都有过查字典的经历，在查找一个生字时，首先会根据拼音或部首按照规定的查找方法到目录中找到对应汉字所在的页码，再翻阅到指定的页码找到要查找的生字。索引是对表或视图中的一列或多列的值进行排序的一种物理结构，它包含从表或视图中的一个或多个列生成的键，以及映射到指定数据的存储位置的指针。不同的索引对应不同的排序方法，就像查字典时如果使用拼音法，则按汉字的拼音进行排序；如果使用部首法，则按汉字的部首进行排序。大家知道，这两种方法也只是对应了目录中的不同排序方式，而字典中汉字的物理顺序却始终只有一种。知识宝库活动4.1.2管理索引索引类似于图书的目录，在一本书中，利用目录可以快速找到所需信息在书中的位置，而不用翻阅整本书。在数据库中，索引使数据库应用程序快速找到表中特定的数据，而不用扫描整个表，可以大大提高数据的查询速度。索引和原始表之间的关系如图4.1.9所示。知识宝库活动4.1.2管理索引2.索引的分类（1）按数据的物理存放顺序：①聚集索引(CLUSTERED)：聚集索引是对表的物理数据按照索引列进行排序，然后再重新存储到磁盘上。由于聚集索引对表中的数据一一进行了排序，因此用聚集索引查找数据很快。但由于聚集索引对表中的数据进行了重新排列，它所需要的存储空间会特别大，大概相当于表中数据所占空间的120%。表的数据行只能按一种排序方式存储在磁盘上，所以一个表只能有一个聚集索引。②非聚集索引(NONCLUSTERED)：非聚集索引具有与表的数据完全分离的结构，使用非聚集索引不用将物理数据按列排序，而是存储索引行，每个索引行均包含非聚集索引键值和一个或多个指向包含该值的数据行的行定位器。SQLServe在搜索数据时，先对非聚集索引进行搜索，然后再通过相应的行定位器从表中找到对应的数据。一个表可以有多个非聚集索引。知识宝库活动4.1.2管理索引一般情况下，先创建聚集索引，后创建非聚集索引，因为创建聚集索引会改变表中行的顺序，从而会影响到非聚集索引。（2）按索引列取值的唯一性：①唯一索引：要求被索引列不能有重复值，也不能有两个NULL。创建主键约束时，系统会自动为表创建一个唯一聚集索引。创建唯一约束时，系统会自动为表创建一个唯一非聚集索引。②非唯一索引：不受重复值的约束。3.索引的作用建立索引有以下优点：①建立了以索引列作为查询条件时，能大大提高数据的查询速度。②索引可以加快表与表之间的连接。③在查询中，可以明显减少排序和分组所用的时间。知识宝库活动4.1.2管理索引4.创建索引的规则创建索引的规则有以下几点：①避免在一个表上创建大量的索引，因为创建索引要花费时间和占用物理空间，而且虽然索引能加快查询速度，但减慢了数据更新的速度，因为每执行一次数据修改，就需要对索引进行维护。②对于经常需要作为查询条件的列可以创建索引。③在经常需要排序的列上可以创建索引。④当修改的性能需求远大于查询的性能需求时，不要创建索引。知识宝库活动4.1.2管理索引5.创建索引索引可以使用对象资源管理器直接创建，也可以使用CREATEINDEX语句进行创建，使用CREATEINDEX创建索引的语句格式如下：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX索引名ON表或视图(列名[ASC|DESC])①UNIQUE表示创建唯一索引，若省略则默认创建非唯一索引。②CLUSTERED|NONCLUSTERED表示创建的索引为聚集索引还是非聚集索引，若省略则默认创建非聚集索引。③ASC|DESC表示索引是按升序还是降序排列，若省略则默认为升序。知识宝库活动4.1.2管理索引6.删除索引对于不再需要的索引，应该及时进行删除，删除索引既可以使用对象资源管理器直接删除，也可以使用DROPINDEX语句进行删除，使用DROPINDEX删除索引有两种方法，语句格式分别如下：DROPINDEX索引名ON表名或视图名或者DROPINDEX表名.索引名或视图名.索引名知识宝库活动4.1.2管理索引【体验活动】1.使用对象资源管理器查看“课程信息”表的“IX_课程名称”索引属性。2.使用对象资源管理器删除“学生信息”表的索引“IX_姓名”。3.使用DROPINDEX语句删除“课程信息”表的索引“IX_课程名称”。任务4.2使用默认值对象掌握默认值对象的创建、绑定、解除绑定、删除等操作。任务说明1.会创建默认值对象。2.会绑定默认值对象。3.会解除默认值对象的绑定。4.会删除默认值对象。任务目标任务4.2使用默认值对象活动4.2.1创建默认值对象活动4.2.2默认值对象的绑定活动4.2.3解除默认值对象的绑定活动4.2.4删除默认值对象活动4.2.1创建默认值对象使用默认值对象之前必须先创建默认值对象，可以使用CREATEDEFAULT语句创建默认值对象。案例7在“图书信息管理”数据库中使用CREATEDEFAULT语句创建默认值对象“MR_所在地”，值为“北京市”。【代码如下】USE图书信息管理GOCREATEDEFAULTMR_所在地AS'北京市'活动4.2.2默认值对象的绑定默认值对象创建好后，需要把它绑定到需要的列上，这样才能实现在插入数据时，如果没有为该列指定数值，则系统自动为其添加上指定的默认值。案例8将“MR_所在地”默认值对象绑定到“出版社”表“所在地”列。【代码如下】USE图书信息管理GOSP_BINDEFAULT'MR_所在地','出版社.所在地'『小贴士』①如果列同时绑定默认值对象和规则对象，则默认值不能违反规则。与规则冲突的默认值将永远不能插入列，因为每次试图插入这样的默认值时都会出错。②一个默认值对象可以绑定到多个不同的列上。③不能将默认值对象绑定到标识列或已经有DEFAULT约束的列上。活动4.2.3解除默认值对象的绑定如果不再需要绑定默认值对象，则可以使用SP_UNBINDEFAULT语句将绑定从列上解除。案例9解除“MR_所在地”默认值对象在“出版社”表“所在地”列的绑定。【代码如下】USE图书信息管理GOSP_UNBINDEFAULT'出版社.所在地'活动4.2.4删除默认值对象当不再使用默认值对象时，可以使用DROPDEFAULT语句来删除默认值对象，删除默认值对象前一定要确保该默认值对象已经没有绑定到任何列或用户定义数据类型上。案例10删除“图书信息管理”数据库中的“MR_所在地”默认值对象。【代码如下】USE图书信息管理GODROPDEFAULTMR_所在地『小贴士』①在对象资源管理器中，右键单击要删除的默认值对象，然后选择“删除”命令，也可以删除默认值对象。②删除默认值对象前，必须先将默认值对象从它所绑定的列上解除，否则在执行删除语句时会出现如图4.2.6所示错误信息。活动4.2.4删除默认值对象在SQLServer2008数据库系统中，除了使用DEFAULT约束外，还可以建立默认值对象。建立默认值对象后，需要把它绑定到表中的列或用户定义的数据类型上才能发挥作用。1.创建默认值对象默认值就是当用户向表中添加数据时，没有明确地给出，而由SQLServer自动指派的值。默认值可以定义为数据库对象，使用时需将其绑定到特定的列上。在SQLServer2008中使用CREATEDEFAULT语句来创建默认值对象，语句格式如下：CREATEDEFAULT默认值对象名AS常量表达式①常量表达式可以是常量、内置函数或表达式。②常量表达式如果是字符或日期时间型数据，必须用单引号（’’）括起来。③CREATEDEFAULT必须是查询批次中的第一条语句。④默认值必须与要绑定的列数据类型兼容，否则在插入默认值时会出错。⑤只能在当前数据库中创建默认值对象。⑥如果默认值