索引及数据完整性41索引

《索引及数据完整性41索引》由会员分享，可在线阅读，更多相关《索引及数据完整性41索引（50页珍藏版）》请在装配图网上搜索。

1、第 4章 索 引 及 数 据 完 整 性4.1 索 引 4.3 SQL SERVER数 据 完 整 性4.2 全 文 索 引 主 要 内 容 v 索 引 类 型 、 数 据 完 整 性 的 分 类v 创 建 索 引 的 方 法v 掌 握 索 引 的 维 护v 各 种 约 束 及 默 认 值v 数 据 完 整 性 的 实 现 4.1.1 索 引 的 简 介 及 分 类 索 引 是 某 个 表 中 一 列 或 若 干 列 值 的 集 合 和 相 应 的 指 向表 中 物 理 标 识 这 些 值 的 数 据 页 的 逻 辑 指 针 清 单 。 它 提 供 了数 据 库 中 编 排 表 中 数 据 的

2、 内 部 方 法 。 通 常 情 况 下 一 个 表 的 存 储 是 由 两 部 分 组 成 的 ， 一 部 分用 来 存 放 表 的 数 据 页 面 ， 另 一 部 分 存 放 索 引 页 面 。 索 引 就存 放 在 索 引 页 面 上 ， 通 常 ， 索 引 页 面 相 对 于 数 据 页 面 来 说小 得 多 。 唯 一 索 引 和 非 唯 一 索 引 聚 集 索 引 和 非 聚 集 索 引 单 列 索 引 、 复 合 索 引 和 包 含 性 列 索 引 视 图 索 引 全 文 索 引 4.1.2 索 引 的 分 类 4.1.2 创 建 索 引 的 注 意 事 项1.要 合 理 的 建

3、 立 索 引 ， 而 不 要 认 为 索 引 越 多 越 好 ，否 则 不 仅 达 不 到 提 高 性 能 的 目 的 ， 反 而 会 适 得 其 反 2.每 个 表 只 能 有 一 个 聚 簇 (聚 集 )索 引 3.聚 集 索 引 适 合 于 检 索 连 续 键 值 表 4.1 合 理 使 用 聚 集 索 引 和 非 聚 集 索 引 4.1.3 创 建 索 引创 建 索 引 的 方 法 有 直 接 和 间 接 的 方 法 1.直 接 方 法2.间 接 方 法 4.1.3.1 间 接 创 建 索 引1. 主 键 索 引 （ Primary Key约 束 ）1） 打 开 SQL Server

4、Management Studio， 连 接 到 本 地 数 据库 实 例 ， 在 【 对 象 资 源 管 理 器 】 中 展 开 目 录 树 “ 数 据库 ” |“xuesheng”|“表 ” |“学 生 ” ， 单 击 鼠 标 右 键 ， 选 择【 修 改 】 命 令 ， 系 统 打 开 表 设 计 器 窗 口 。2） 在 表 设 计 器 中 ， 选 择 “ 学 号 ” 字 段 ， 单 击 【 表 设 计 器 】 工具 栏 中 的 【 设 置 主 键 】 工 具 ， 则 在 字 段 “ 学 号 ” 字 段 的 前 面多 了 一 个 小 钥 匙 的 图 标 。 如 图 4.1所 示 。 图

5、4.1 利 用 设 置 主 键 间 接 创 建 索 引 2. 唯 一 约 束 （ Unique约 束 ）例 4-1： 创 建 一 个 成 绩 表 ， 其 中 包 含 主 键 和 Unique约 束 ， 创 建后 查 看 其 索 引 信 息 。 其 相 应 的 SQL命 令 如 下 ：USE xueshengCREATE TABLE 成 绩( 学 号 INT NOT NULL,课 程 号 NUMERIC UNIQUE, /*唯 一 约 束 */成 绩 NUMERIC ) 4.1.3.2 在 SQL Server Management Studio中 创 建 索 引1 打 开 “ SQL Serv

6、er Management Studio”窗 口 ， 连 接 到 本 地 实例 ， 在 【 对 象 资 源 管 理 器 】 窗 口 中 ， 依 次 展 开 XUEXIAO-6BNZNGY|数 据 库 |xuesheng|索 引 的 树 型 目 录 。2 右 击 “ 索 引 ” ， 在 弹 开 的 快 捷 菜 单 中 选 择 【 新 建 索 引 】 命 令 ，如 图 4.5所 示 。 图 4.5 直 接 方 法 创 建 索 引 4.1.3.3 用 Create index 语 句 创 建 索 引CREATE UNIQUE CLUSTERED| NONCLUSTERED INDEX index_n

7、ame ON table | view ( column ASC | DESC ,.n ) withPAD_INDEX,FILLFACTOR=fillfactor,IGNORE_DUP_KEY,DROP_EXISTING,STATISTICS_NORECOMPUTE,SORT_IN_TEMPDB ON filegroup CREATE INDEX命 令 创 建 索 引 各 参 数 说 明 如 下 ： UNIQUE： 用 于 指 定 为 表 或 视 图 创 建 唯 一 索 引 ， 即 不 允 许 存在 索 引 值 相 同 的 两 行 。 CLUSTERED： 用 于 指 定 创 建 的 索 引

8、为 聚 集 索 引 。 NONCLUSTERED： 用 于 指 定 创 建 的 索 引 为 非 聚 集 索 引 。 ON:表 示 可 以 在 表 或 视 图 上 创 建 索 引 ,这 里 指 定 表 或 视 图 的 名称 和 相 应 的 列 名 称 。 index_name： 用 于 指 定 所 创 建 的 索 引 的 名 称 。 table： 用 于 指 定 创 建 索 引 的 表 的 名 称 。 view： 用 于 指 定 创 建 索 引 的 视 图 的 名 称 。 ASC|DESC： 用 于 指 定 具 体 某 个 索 引 列 的 升 序 或 降 序 排 序 方向 。 Column： 用

9、 于 指 定 被 索 引 的 列 。 PAD_INDEX： 为 非 叶 级 索 引 页 指 定 填 充 度 。 FILLFACTOR = fillfactor： 用 于 指 定 在 创 建 索 引 时 ， 每 个 索引 页 的 数 据 占 索 引 页 大 小 的 百 分 比 ， fillfactor的 值 为 1到 100。 IGNORE_DUP_KEY： 指 定 出 先 冗 余 数 据 的 系 统 行 为 。 DROP_EXISTING： 用 于 指 定 应 删 除 并 重 新 创 建 已 命 名 的 先前 存 在 的 聚 集 索 引 或 者 非 聚 集 索 引 。 STATISTICS_N

10、ORECOMPUTE： 用 于 指 定 过 期 的 索 引 统 计不 会 自 动 重 新 计 算 。 SORT_IN_TEMPDB： 用 于 指 定 创 建 索 引 时 的 中 间 排 序 结 果将 存 储 在 tempdb 数 据 库 中 。 ON filegroup： 用 于 指 定 存 放 索 引 的 文 件 组 。 例 4-2: 为 表 “ 学 生 ” 中 字 段 “ 学 号 ” 创 建 一 聚 集 索 引 。 use xueshengIF EXISTS(SELECT name from sysindexes where name=PK_XH)DROP INDEX xuesheng.P

11、K_XH GOCREATE INDEX PK_XH ON 学 生 (学 号 ) 例 4-3: 为 表 “ 学 生 ” 中 字 段 “ 姓 名 ” 创 建 了 一 个 唯 一 聚 集索 引 ， 要 求 使 用 FILLFACTOR子 句 。use xueshengIF EXISTS(SELECT name from sysindexes where name=XM_索 引 ) DROP INDEX xuesheng.XM_索 引GOCREATE UNIQUE CLUSTERED INDEX XM_索 引 ON 学 生 (姓 名 )with pad_index, fillfactor=20, ig

12、nore_dup_key, drop_existing, statistics_norecompute 4.1.4 查 看 修 改 索 引1. 使 用 SQL Server Management Studio查 看 修 改 索 引 2. 利 用 T-SQL查 看 修 改 索 引 例 4-8: 查 看 学 生 表 中 的 所 有 索 引 信 息 ， 其 代 码 如 下 ： Exec sp_helpindex 学 生 4.1.5 重 新 生 成 索 引例 4-10: 重 新 生 成 xuesheng数 据 库 中 的 学 生 表 里 的 PK_学 生 ，并 设 置 索 引 填 充 ， 填 充 因

13、子 为 60， 其 代 码 如 下 ： ALTER INDEX PK_学 生 ON 学 生 REBUILD WITH (PAD_INDEX =ON, FILLFACTOR=60) 4.1.6 禁 用 索 引1.利 用 SQL Server Management Studio禁 用 索 引2.使 用 T-SQL语 句 中 的 DISABLE参 数 禁 用 索 引例 4-12: 禁 用 学 生 表 中 的 XM_索 引 这 一 唯 一 索 引 。 并 查 看 学 生表 信 息 。 ALTER INDEX XM_索 引 ON 学 生 DISABLE GO运 行 后 ， 再 次 使 用 select

14、* from 学 生 ， 则 可 以 显 示 数 据 表 中 信息 。 4.1.7 删 除 索 引利 用 T-SQL的 DROP INDEX语 句 删 除 索 引 例 4-13: 将 成 绩 表 中 的 课 程 号 字 段 的 UQ_成 绩 _023D5A04删 除 。 USE xuesheng GO DROP INDEX 成 绩 .UQ_成 绩 _023D5A04 4.1.8 视 图 索 引例 4-15: 创 建 一 视 图 ， 并 为 该 视 图 建 立 聚 集 索 引 ， 然 后 用 索 引视 图 检 索 数 据 。 USE xuesheng GO SET ANSI_PADDING,AN

15、SI_NULLS ON GO CREATE VIEW xuesheng_VIEW /*建 立 视 图 名 为xuesheng_VIEW*/ WITH SCHEMABINDING AS SELECT 姓 名 ,学 号 FROM DBO.学 生 GO SET NUMERIC_ROUNDABORT OFF GO SET ARITHABORT,ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ON图 4.18 运 行 结 果 GO CREATE UNIQUE CLUSTERED INDEX VIEW_学 生 ON 学 生 (学 号 ,姓 名 ) |*

16、在 视 图 上 建 立 聚 集 索 引 *| GO SELECT * FROM xuesheng_view GO 运 行 结 果 如 图 4.18所 示 。 图 4.18 运 行 结 果 表 4.3 常 规 索 引 与 全 文 索 引 比 较4.2 全 文 索 引 全 文 索 引 包 含 在 全 文 索 引 目 录 中 （ Full-Text Catalogs） ， 通 常 是 由 同 一 数 据 库 中 的 一 个 或 多 个 表 的 全文 索 引 构 成 一 个 全 文 索 引 目 录 。 一 个 表 只 能 有 一 个 全 文 索 引 ，因 此 每 个 有 全 文 索 引 的 表 只 隶

17、 属 于 一 个 全 文 索 引 目 录 。 4.2.1 全 文 索 引 介 绍 全 文 目 录 是 存 储 全 文 索 引 的 地 方 ， 全 文 目 录 必 须 保 存 在与 SQL Server实 例 相 关 联 的 本 地 硬 盘 上 ， 每 个 全 文 目 录 可 以用 于 满 足 数 据 库 内 的 一 个 或 多 个 表 的 索 引 需 求 。 要 想 创 建 全文 索 引 ， 首 先 必 须 创 建 全 文 目 录 。 4.2.2 全 文 目 录 管 理 4.2.2.1 启 动 全 文 搜 索1.选 择 windows操 作 系 统 的 【 管 理 工 具 】 |【 服 务 】

18、 中 ， 对【 SQL Server Fulltext Search】 进 行 启 动 。 如 图 4.19所 示 。 图 4.19 利 用 windows启 动 全 文 搜 索 2.可 以 直 接 在 SQL Server 2005中 依 次 选 择 【 管 理 】 |【 全文 搜 索 】 ， 单 击 右 键 选 择 【 启 动 】 命 令 即 可 。 如 教 材 中 图4.20所 示 。3.利 用 T-SQL命 令 实 现 对 应 的 T-SQL语 句 命 令 格 式 如 下 ： sp_fulltext_database action=action 其 中 action= action表

19、示 将 要 执 行 的 操 作 。 action 的数 据 类 型 为 varchar(20)， 可 以 是 enable 和 disable两 个 值 。其 中 enable表 示 在 当 前 数 据 库 中 启 用 全 文 索 引 ； disable表示 对 于 当 前 数 据 库 中 ， 删 除 文 件 系 统 中 所 有 的 全 文 目 录 ，并 且 将 该 数 据 库 标 记 为 已 经 禁 用 全 文 索 引 。 此 操 作 并 不 在全 文 目 录 或 表 级 上 更 改 任 何 全 文 索 引 元 数 据 。 4.2.2.2 创 建 全 文 目 录 在 SQL Server M

20、anagement Studio创 建 全 文 索 引 目 录 使 用 T-SQL语 句 创 建 全 文 目 录 创 建 全 文 目 录 的 T-SQL语 句 格 式 为 ： CREATE FULLTEXT CATALOG catalog_name /*全 文 目录 名 称 / ON FILLEGROUP filegroup /*包 含 全 文 目 录 的 文 件组 名 默 认 是 主 文 件 组 */ IN PATH rootpath /*全 文 目 录 的 路 径 */WITH /*指 定 将 应 用 于 该 目 录 的 选 项 */AS DEFAULT /*指 定 该 全 文 目 录 为

21、 默 认 目 录 */AUTHORIZATION owner_name /*将 全 文 目 录 的 所 有 者 设 为 数 据 库 用 户 名 或 角 色 的 名 称 */ 4.2.3 创 建 全 文 索 引例 4-18 为 “ 学 生 ” 表 的 “ 姓 名 ” 、 “ 家 庭 住 址 ” 2个 字 段创 建 全 文 索 引 ， 其 代 码 如 下 ： CREATE FULLTEXT INDEX ON 学 生 （ 姓 名 ， 家 庭 住 址 TYPE COLUMN扩 展 名 ） KEY INDEX PK_学 生 ON SQL全 文 目 录 4.2.4 使 用 全 文 搜 索 查 询 在 一

22、个 表 中 创 建 了 全 文 索 引 后 ， 才 可 以 对 表 中 的数 据 进 行 全 文 检 索 。 进 行 全 文 检 索 需 要 在 SELECT命令 的 WHERE 字 句 中 使 用 Transact-SQL 谓 词 ：CONTAINS 和 FRETEXT。 4.2.4.1 使 用 CONTAINS搜 索简 单 词 的 搜 索 方 式简 单 词 就 是 搜 索 一 个 或 多 个 特 定 的 词 或 短 语 。例 4-19: 搜 索 学 生 表 中 的 家 庭 住 址 中 含 有 “ 黑 龙 江 省 ” 的 记 录 ，其 代 码 如 下 ： SELECT * FROM 学 生

23、WHERE CONTAINS (家 庭 住 址 ,黑龙 江 省 ) 前 缀 词 的 搜 索 方 式例 4-20: 搜 索 学 生 表 中 的 家 庭 住 址 中 含 有 “ 上 海 ” 开 头 的 单 词的 记 录 ， 其 代 码 如 下 ： SELECT * FROM 学 生 WHERE CONTAINS (家 庭 住 址 , 上 海 *)其 中 *只 能 放 在 英 文 字 母 之 后 ， 代 表 一 个 或 多 个 字 符 。 邻 近 词 的 搜 索 方 式例 4-21: 搜 索 学 生 表 中 家 庭 住 址 中 与 黑 龙 江 省 考 生 相 关 的 记 录 。 use xueshe

24、ngSELECT * FROM 学 生 WHERE CONTAINS (家 庭 住 址 , 黑 龙 江 省 NEAR家 庭住 址 ) FREETEXT的 语 法 代 码 为 ： FREETEXT(column_name|(column_list)|*, freetext_string,LANGUAGE language_term)其 中 ： column_name： 字 段 名column_list： 字 段 列 表*表 示 所 有 字 段freetext_string： 要 搜 索 的 字 符 串LANGUAGE language_term： 用 于 单 词 断 字 、 词 干 分 析 、同

25、 义 词 查 询 以 及 干 扰 词 删 除 特 定 的 语 言 4.2.4.2 使 用 FREETEXT搜 索 例 4-22 查 询 “ 黑 龙 江 省 ” 的 学 生 信 息 。 use xuesheng Select * from 学 生 Where freetext (家 庭 住 址 ,黑 龙 江 省 ) 4.3.1 数 据 完 整 性 概 念数 据 完 整 性 是 指 数 据 库 中 的 数 据 在 逻 辑 上 的 一 致 性 和 准 确 性 。 在 关 系 数 据 库 系 统 中 ， 比 较 重 要 的 完 整 性 有 实 体 完 整 性 、域 完 整 性 、 参 照 完 整 性

26、和 用 户 自 定 义 的 完 整 性 等 。 4.3 SQL SERVER数 据 完 整 性 4.3.2 域 完 整 性 域 完 整 性 又 称 列 完 整 性 ， 是 指 给 定 列 的 输 入 有 效 性 。 强 制 域有 效 性 的 方 法 有 ： 限 制 类 型 （ 通 过 数 据 类 型 ） 、 格 式 （ 通 过CHECK约 束 和 规 则 ） 或 可 能 值 的 范 围 （ 通 过 FOREIGN KEY约 束 、 CHECK约 束 、 DEFAULT定 义 、 NOT NULL定 义和 规 则 来 实 现 ） 。 4.3.3 实 体 完 整 性 实 体 完 整 性 又 称 行

27、 完 整 性 ， 是 指 将 行 定 义 为 特 定 表 的 惟 一 实体 。 要 求 表 中 有 一 个 主 键 ， 并 且 其 值 不 能 为 空 且 不 允 许 有 重复 的 值 与 之 对 应 。 实 体 完 整 性 强 制 表 的 标 识 符 列 或 主 键 的 完整 性 （ 通 过 索 引 、 UNIQUE约 束 、 PRIMARY KEY约 束 或IDENTITY属 性 来 实 现 ） 。 4.3.4 参 照 完 整 性 参 照 完 整 性 又 称 引 用 完 整 性 ， 是 指 主 表 中 的 数 据 与 从 表 中 的数 据 的 一 致 性 。 在 输 入 或 删 除 其 中

28、 一 个 表 的 记 录 时 ， 另 一 个表 对 应 的 约 束 应 满 足 ， 即 参 照 完 整 性 保 持 表 之 间 已 定 义 的 关系 。 在 SQL Server中 ， 参 照 完 整 性 基 于 外 键 与 主 键 之 间 或 外键 与 惟 一 键 之 间 的 关 系 （ 通 过 FOREIGN KEY和 CHECK约束 ） 。 参 照 完 整 性 确 保 键 值 在 所 有 表 中 一 致 。 这 样 的 一 致 性要 求 不 能 引 用 不 存 在 的 值 ， 如 果 键 值 更 改 了 ， 那 么 在 整 个 数据 库 中 ， 对 该 键 值 的 所 有 引 用 要 进

29、 行 一 致 的 更 改 。 4.3.5 完 整 性 的 实 现v 4.3.5.1 使 用 PRIMARY KEY约 束 实 现v 4.3.5.2 利 用 CHECK约 束 实 现v 4.5.3.3 UNIUE约 束v 4.3.5.4 使 用 Forein Key约 束v 4.3.5.5 使 用 默 认 值 约 束 4.3.5.1 使 用 PRIMARY KEY约 束 实 现 对 于 任 何 一 个 数 据 表 中 ， 通 常 具 有 包 含 唯 一 标 识 表 中 每一 行 的 值 的 一 列 或 一 组 列 。 这 样 的 一 列 或 多 列 称 为 表 的 主 键（ PRIMARY KE

30、Y） ， 用 于 强 制 表 的 实 体 完 整 性 。 在 创 建 或修 改 表 时 ， 可 以 通 过 定 义 PRIMARY KEY约 束 来 创 建 主 键 。 定 义 主 键 的 方 法 可 以 参 照 4.1.3.1间 接 创 建 索 引 中 创 建 。 4.3.5 完 整 性 的 实 现 4.3.5.2 利 用 CHECK约 束 实 现 1. 通 过 SQL Server Management Studio创 建 与 删 除CHECK约 束 如 教 材 中 的 图 4.24、 4.25所 示 。 2. 利 用 SQL语 句 在 创 建 表 的 同 时 创 建 CHECK约 束例

31、4-23 对 数 据 库 xuesheng中 的 学 生 表 ， 要 求 其 年 龄 在 0到 30岁以 内 。 USE xuesheng CREATE TABLE 学 生 ( 学 号 char(6) not null, 姓 名 char(6) null, 年 龄 smallint not null CHECK (年 龄 =0), 出 生 日 期 datetime not null) 3. 修 改 表 时 创 建 CHECK约 束例 4-24修 改 数 据 库 xuesheng中 的 学 生 表 ， 要 求 其 年 龄 在 30岁以 内 。 USE xuesheng ALTER TABLE 学

32、 生 ADD CONSTRAINT 年 龄 CHECK (年 龄 =0) 4.5.3.3 UNIUE约 束 UNIQUE约 束 在 列 集 内 强 制 执 行 值 的 惟 一 性 。 可 以 使 用UNIQUE约 束 确 保 在 非 主 键 列 中 不 输 入 重 复 的 值 。 尽 管UNIQUE约 束 和 PRIMARY KEY约 束 都 强 制 唯 一 性 ， 但 想 要 强 制一 列 或 多 列 组 合 （ 不 是 主 键 ） 的 唯 一 性 时 应 使 用 UNIQUE约 束而 不 是 PRIMARY KEY约 束 。 定 义 唯 一 键 的 方 法 可 以 参 照 4.1.3.1间

33、 接 创 建 索 引 中 创 建 。可 以 对 一 个 表 定 义 多 个 UNIQUE 约 束 ， 但 只 能 定 义 一 个 PRIMARY KEY 约 束 。 对 于 UNIQUE约 束 中 的 列 ， 表 中 不 允 许 有 两 行 包 含 相 同 的非 空 值 。 主 键 也 强 制 执 行 惟 一 性 ， 但 主 键 不 允 许 空 值 。UNIQUE约 束 优 先 于 惟 一 索 引 。 4.3.5.4 使 用 Forein Key约 束1. 利 用 表 设 计 器 实 现2. 利 用 数 据 库 对 象 实 现 使 用 FOREIGN KEY约 束 需 要 注 意 的 问 题1

34、.一 个 表 最 多 可 以 有 可 以 建 立 一 个 或 者 多 个 FOREIGN KEY约 束 ， 最 多 可 以 建 253个 FOREIGN KEY约 束 。2.创 建 FOREIGN KEY约 束 的 主 表 字 段 可 以 是 主 键 约 束 也 可以 是 UNIQUE约 束 。3.创 建 FOREIGN KEY约 束 的 表 可 以 和 同 数 据 库 中 其 他 表 间建 立 约 束 ， 也 可 以 和 它 本 身 建 立 FOREIGN KEY约 束 。 例如 ： 总 经 理 也 是 公 司 成 员 中 的 一 员 ， 则 可 以 将 总 经 理 字 段（ zjl） 和

35、成 员 （ cy） 字 段 间 建 立 FOREIGN KEY约 束 。4.建 立 FOREIGN KEY约 束 的 主 表 和 子 表 间 两 个 字 段 的 数 据类 型 和 数 据 宽 度 必 须 相 同 。5.创 建 FOREIGN KEY约 束 前 应 该 确 定 那 个 表 是 主 表 ， 那 个是 子 表 。 假 如 A表 删 了 一 条 记 录 ， 表 B也 要 删 一 条 对 应 的记 录 ， 这 时 候 应 该 在 表 B上 设 定 关 系 ， A是 主 表 ， B是 子 表（ 有 的 称 为 从 表 ） 。 4.3.5.5 使 用 默 认 值 约 束1. 创 建 默 认

36、值 例 4-25 创 建 字 符 默 认 值 unknown。 use xuesheng go create default 家 庭 住 址 as unknown go 2. 删 除 默 认 值例 4-26 删 除 默 认 值 。 如 果 默 认 值 没 有 绑 定 到 列 或 用 户 定 义 的 数 据 类 型 ， 可 以 很 容 易地 使 用 DROP DEFAULT 将 其 除 去 。 如 下 为 删 除 用 户 创 建 的名 为 族 别 _hz 的 默 认 值 。 use pubs go if exists (select name from sysobjects where name = 族 别 _hz and type = c) drop default族 别 _hz go