创建索引与约束.ppt

1 第5章创建索引 idex 与约束 constraint 2 第5章创建索引 idex 与约束 constraint 5 1索引5 2约束和默认5 3数据完整性 3 一 什么叫索引 数据库中的索引与书籍中的目录类似 在数据库中 索引使数据库程序无须对整个表进行扫描 就可以在其中找到所需数据 数据库中的索引是某个表中一列或者若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单 4 二 索引的作用 通过创建唯一索引 可以保证数据记录的唯一性 可以大大加快数据检索速度 可以加速表与表之间的连接 这一点在实现数据的参照完整性方面有特别的意义 在使用ORDERBY和GROUPBY子句中进行检索数据时 可以显著减少查询中分组和排序的时间 使用索引可以在检索数据的过程中使用优化器 提高系统性能 5 三 聚集索引与非聚集索引 聚集索引 簇索引 clustered 对表的物理数据页中的数据按列进行排序 然后再重新存储到磁盘上 聚集索引的顺序和数据行的物理顺序一致 一个表或视图只能有一个聚集索引 非聚集索引 非簇索引 nonclustered 具有完全独立于数据行的结构 非聚集索引存储了组成非聚集索引的关键字值和行定位器 见下页图 一个表中可以有多个非聚集索引 6 7 四 增加索引带来的不利因素 索引要占用物理空间 当对表中数据进行了添加 删除和修改操作后 系统需要对索引进行动态维护 需要占用一定的时间 8 五 创建索引必须基于以下原则 在主关键字列中创建索引 自动 在经常要查询的列中创建索引 在经常用于连接的列 如外关键字列中创建索引 在经常出现在WHERE子句和ORDERBY子句中的列中创建索引 当UPDATE的性能需求远大于SELECT的性能需求时 不应该创建索引 9 七 另外应当注意 当在表中创建主关键字约束或惟一性约束时 SQLServer自动创建一个惟一性索引 如果表中已有数据 那么在创建索引时 SQLServer会检查数据的合法性 当有多个列作为关键字时 应创建复合索引 10 5 1 1创建索引5 1 2查看 修改和删除索引 11 5 1 1创建索引 利用SSMS直接创建索引 利用Transact SQL语句中的CREATEINDEX命令创建索引 12 1 利用SSMS创建索引 如图所示 13 使用索引效果的测试 程序 14 练习 为表course创建一个唯一聚集索引 为表student创建一个年龄 学号复合索引 15 7 1 2查看 修改和删除索引 1 利用SSMS查看 修改和删除索引 16 2 用系统存储过程查看和更改索引名称 系统存储过程sp helpindex可以返回表的所有索引信息 其语法形式如下 sp helpindex name 其中 name 参数用于指定当前数据库中的表的名称 另外 系统存储过程sp rename可以用来更改索引的名称 其语法形式如下 sp rename object name new name 17 例 更改employees表中的索引employees name index名称为employees name ind 其程序清单如下 Execsp rename employees employees name index employees name ind index 18 3 使用Transact SQL语句中的DROPINDEX命令删除索引 当不再需要某个索引时 可以将其删除 DROPINDEX命令可以删除一个或者多个当前数据库中的索引 其语法形式如下 DROPINDEX table index view index n 其中 table view用于指定索引列所在的表或索引视图 index用于指定要删除的索引名称 注意 DROPINDEX命令不能删除由CREATETABLE或者ALTERTABLE命令创建的主键或者唯一性约束索引 也不能删除系统表中的索引 19 5 2约束和默认创建 在这里我们介绍5种 创建主键约束 primarykeyconstraint 创建外键约束 foreignkeyconstraint 创建缺省值约束 defaultconstraint 创建检查约束 checkconstraint 创建唯一性约束 uniqueconstraint 20 1 创建主键约束 表中经常有一个列或列的组合 其值能唯一地标识表中的每一行 这样的一列或多列称为表的主键 通过它可强制表的实体完整性 当为表指定PRIMARYKEY约束时 SQLServer2000通过为主键列创建唯一索引强制数据的唯一性 主键的添加 删除和修改操作方法有两种 一 SSMS操作法 在SSMS中 用右键单击要操作的数据库表 从弹出的快捷菜单中选择设计表选项 然后根据提示操作 二 Transact SQL语句操作法 21 2 创建外键约束 外键一个关系表中的某个属性虽然不是主键 但是却是另一个表中的主键 则称为外键ForeignKey FK ForeignKey FK 是用于建立和加强两个表数据之间的链接的一列或多列 当创建或修改表时可通过定义FOREIGNKEY约束来创建外键 在外键引用中 当一个表的列被引用作为另一个表的主键值的列时 就在两表之间创建了链接 这个列就成为第二个表的外键 22 外键约束需要用户手动创建一个表中最多可以有31个外键约束在临时表中 不能使用外键约束主键和外键的数据类型必须严格匹配 23 外键约束用于强制参照完整性 提供单个字段或者多个字段的参照完整性 当使用外部键约束时 应该考虑以下几个因素 外键约束提供了字段参照完整性 外键约束不能自动创建索引 需要用户手动创建 例 为产品表和产品分类表创建外键约束 注意 级联更新 级联删除的概念 24 3 创建缺省 默认 值 缺省约束能够定义一个值 每当用户没有在某一列中输入值时 则将所定义的值提供给这一列 使用缺省约束时 应该注意以下几点 每个字段只能定义一个缺省约束 如果定义的缺省值长于其对应字段的允许长度 那么输入到表中的缺省值将被截断 例 在仓库表的上级仓库字段建立缺省约束 25 4 创建检查约束 检查约束通过限制输入到列中的值来强制域的完整性 一个表中可以定义多个检查约束 每个CREATETABLE语句中每个字段只能定义一个检查约束 在多个字段上定义检查约束 则必须将检查约束定义为表级约束 当执行INSERT语句或者UPDATE语句时 检查约束将验证数据 26 创建检查约束常用的操作方法有两种 1 SSMS操作法 使用SSMS创建检查约束 与创建唯一性约束类似 2 Transact SQL语句操作法 例 将Sc表的成绩字段建立检查约束 27 5 唯一性约束 唯一性约束用于指定一个或者多个列的组合的值具有唯一性 以防止在列中输入重复的值 当使用唯一性约束时 需要考虑以下几个因素 使用唯一性约束的字段允许为空值 一个表中可以允许有多个唯一性约束 可以把唯一性约束定义在多个字段上 唯一性约束用于强制在指定字段上创建一个唯一性索引 缺省情况下 创建的索引类型为非聚簇索引 28 创建和修改唯一性约束的操作方法 创建和修改唯一性约束的操作方法有两种 Transact SQL语句操作法 例 将员工表的姓名字段设计唯一性约束 AddanewCHECKCONSTRAINTtothetableALTERTABLE员工表ADDCONSTRAINTu 姓名unique 员工姓名 29 5 3数据完整性 数据完整性 存储在数据库中的数据值均正确的一种状态 分类 实体完整性域完整性参照 引用 完整性用户定义完整性 30 实体完整性 实体完整性将行定义为特定表的唯一实体 实体完整性强制表的标识符列或主键的完整性 通过索引 UNIQUE约束 PRIMARYKEY约束等 31 域完整性 域完整性是指给定列的输入有效性 强制域有效性的方法有 限制类型 通过数据类型 格式 通过CHECK约束和规则 或可能值的范围 通过FOREIGNKEY约束 CHECK约束 DEFAULT定义 NOTNULL定义和规则 32 参照完整性 在输入或删除记录时 引用完整性保持表之间已定义的关系 在SQLServer2000中 引用完整性基于外键与主键之间或外键与唯一键之间的关系 通过FOREIGNKEY和CHECK约束 引用完整性确保键值在所有表中一致 这样的一致性要求不能引用不存在的值 如果键值更改了 那么在整个数据库中 对该键值的所有引用要进行一致的更改 强制引用完整性时 SQLServer禁止用户进行下列操作 当主表中没有关联的记录时 将记录添加到相关表中 更改主表中的值并导致相关表中的记录孤立 从主表中删除记录 但仍存在与该记录匹配的相关记录 33 例如 对于pubs数据库中的sales和titles表 引用完整性基于sales表中的外键 title id 与titles表中的主键 title id 之间的关系 34 用户定义完整性 用户定义完整性使用户得以定义不属于其它任何完整性分类的特定业务规则 35 本课要求掌握的专业英语词汇 1 索引 index 2 主键约束 primarykeyconstraint 3 唯一性约束