标题

Transcription

1 第 3 章 结 构 化 查 询 语 言 SQL 主 要 内 容 结 构 化 查 询 语 言 (StructuredQueryLanguage,SQL) 是 一 种 介 于 关 系 代 数 与 关 系 运 算 之 间 的 语 言, 其 功 能 包 括 定 义 查 询 操 纵 和 控 制 四 个 方 面, 是 一 种 通 用 的 功 能 极 强 的 关 系 数 据 库 标 准 语 言 目 前,SQL 语 言 已 被 确 定 为 关 系 数 据 库 系 统 的 国 际 标 准, 被 绝 大 多 数 商 品 化 的 关 系 数 据 库 系 统 采 用, 得 到 用 户 的 普 遍 接 受 学 习 目 标 了 解 SQL 的 发 展 过 程 和 主 要 功 能 ; 熟 练 掌 握 SQL 的 数 据 表 定 义 修 改 和 删 除 操 作 ; 熟 练 掌 握 SQL 的 数 据 插 入 更 新 和 删 除 操 作 ; 熟 练 掌 握 SQL 的 数 据 查 询 操 作 ; 理 解 关 系 数 据 库 的 视 图 概 念, 掌 握 视 图 的 定 义 和 使 用 方 法 重 点 与 难 点 重 点 : 数 据 表 的 定 义 ; 数 据 表 数 据 的 插 入 更 新 和 删 除 操 作 ; 数 据 表 数 据 的 查 询 操 作 难 点 : 数 据 表 定 义 中 完 整 性 约 束 定 义 ; 连 接 查 询 和 嵌 套 查 询 3.1 SQL 概 述 最 早 的 SQL 原 型 是 IBM 的 研 究 人 员 在 20 世 纪 70 年 代 开 发 出 来 的, 由 于 其 具 有 功 能 丰 富 使 用 方 便 灵 活 和 语 言 简 洁 易 学 等 突 出 优 点, 在 计 算 机 行 业 和 计 算 机 用 户 中 备 受 欢 迎 20 世 纪 80 年 代,SQL 被 ANSI( 美 国 国 家 标 准 化 协 会 ) 批 准 为 关 系 数 据 库 美 国 标 准 随 后,ISO( 国 际 化 标 准 组 织 ) 正 式 采 纳 SQL 为 国 际 标 准 随 着 SQL 标 准 化 的 不 断 完 善, 相 继 出 现 了 SQL89 SQL2 SQL3 SQL2003 和 SQL2005 等 随 着 数 据 库 技 术 的 不 断 发 展, 新 的 SQL 标 准 还 会 出 现 SQL 语 言 主 要 有 以 下 特 点 : (1)SQL 是 一 种 一 体 化 的 语 言, 集 数 据 定 义 数 据 查 询 数 据 操 纵 和 数 据 控 制 等 功 能

2 36 数 据 库 原 理 与 应 用 于 一 身, 可 以 完 成 数 据 库 活 动 中 的 全 部 工 作 (2)SQL 语 言 是 一 种 高 度 非 过 程 化 语 言, 不 用 告 诉 计 算 机 如 何 去 做, 而 只 需 要 描 述 清 楚 用 户 要 做 什 么, 把 要 求 交 给 系 统, 由 系 统 自 动 完 成 全 部 工 作 (3)SQL 语 言 非 常 简 洁, 只 有 为 数 不 多 的 几 条 命 令, 但 功 能 却 很 丰 富, 如 表 3-1 所 示 另 外 SQL 的 语 法 也 简 单 易 学, 容 易 掌 握 表 3-1 SQL 命 令 SQL 功 能 命 令 数 据 定 义 CREATE ALTER DROP 数 据 查 询 SELECT 数 据 操 纵 INSERT UPDATE DELETE 数 据 控 制 GRANT REVOKE DENY (4)SQL 语 言 使 用 方 便 灵 活 SQL 既 可 以 以 命 令 的 方 式 交 互 使 用, 也 可 以 嵌 入 到 程 序 设 计 语 言 中 以 程 序 的 方 式 使 用 虽 然 使 用 方 式 不 同, 但 SQL 语 言 的 语 法 基 本 是 一 致 的 目 前 很 多 数 据 库 应 用 开 发 工 具 都 支 持 SQL 语 言 3.2 数 据 定 义 功 能 SQL 的 数 据 定 义 功 能 包 括 数 据 表 视 图 存 储 过 程 触 发 器 规 则 和 默 认 值 等 的 定 义 本 节 主 要 介 绍 数 据 表 的 定 义, 视 图 的 定 义 将 在 3.5 节 中 介 绍, 其 他 对 象 的 定 义 将 在 后 续 章 节 介 绍 本 章 及 后 续 章 节 的 所 有 数 据 操 作 对 象 主 要 是 XSCJGL 数 据 库 中 所 包 含 的 二 级 学 院 专 业 班 级 学 生 选 课 课 程 和 教 学 计 划 等 数 据 表, 其 关 系 如 图 3-1 所 示 数 据 表 的 定 义 图 3-1 XSCJGL 数 据 库 SQL 的 CREATETABLE 命 令 实 现 数 据 表 的 定 义 ( 创 建 表 结 构 ), 命 令 格 式 如 下 : CREATETABLE < 表 名 >( < 列 名 >< 数 据 类 型 > [< 列 级 完 整 性 约 束 >],

3 第 3 章 结 构 化 查 询 语 言 SQL 37 < 列 名 >< 数 据 类 型 > [< 列 级 完 整 性 约 束 >],, [< 表 级 完 整 性 约 束 >] )[< 其 他 参 数 >] 说 明 : (1) < 表 名 > 给 出 要 创 建 的 基 本 表 的 名 称 (2) < 列 名 > 给 出 列 名 或 字 段 名 (3) < 数 据 类 型 >, 表 3-2 列 出 了 SQLServer2005 常 用 的 数 据 类 型 (4) < 列 级 完 整 性 约 束 > 用 于 定 义 列 或 字 段 一 级 的 完 整 性 约 束, 一 般 包 括 以 下 内 容 : 1NULL 和 NOT NULL: 字 段 允 许 或 不 允 许 取 空 值 2PRIMARY KEY: 主 键 约 束 3UNIQUE: 唯 一 性 约 束 4FOREIGN KEY: 外 部 关 键 字 约 束 5DEFAULT: 默 认 值 约 束 6CHECK: 域 完 整 性 约 束 (5) < 表 级 完 整 性 约 束 > 用 于 定 义 表 一 级 的 完 整 性 约 束, 一 般 包 括 以 下 内 容 : 1PRIMARY KEY: 复 合 属 性 构 成 的 主 关 键 字 说 明 2FOREIGN KEY: 外 部 关 键 字 及 参 照 关 系 说 明 3CHECK: 同 时 涉 及 多 个 属 性 的 域 完 整 性 约 束 表 3-2 SQLServer2005 数 据 类 型 数 据 类 型 关 键 字 说 明 整 型 bigint int smalint tinyint 长 度 分 别 为 8 字 节 4 字 节 2 字 节 1 字 节 数 值 型 numeric decimal 精 确 数 值 型 浮 点 型 float real 双 精 度 8 字 节 单 精 度 4 字 节 货 币 型 money smalmoney 8 字 节 4 字 节 位 型 bit 位, 取 值 为 0 1 或 NULL 字 符 型 char varchar varchar(max) 定 长 或 可 变 长 度 字 符 串,varchar(MAX) 超 8000 字 符 使 用 Unicode 字 符 型 nchar nvarchar nvarchar(max) 定 长 或 可 变 长 度 字 符 串,nvarchar(MAX) 超 4000 字 符 使 用 文 本 型 text ntext 存 储 超 过 8000 字 符 信 息 二 进 制 型 binary varbinary varbinary(max) 定 长 或 可 变 长 度,varbinary(MAX) 超 8000 字 节 使 用 日 期 时 间 型 datetime smaldatetime 8 字 节 精 确 到 百 分 之 三 秒 4 字 节 精 确 到 分 钟 时 间 戳 型 timestamp 8 字 节 表 类 型 table 用 于 临 时 存 储 查 询 结 果 集 以 供 后 续 使 用 唯 一 标 识 类 型 uniqueidentifier 系 统 自 动 产 生 唯 一 标 识 值 XML 类 型 xml 在 数 据 库 中 保 存 xml 文 档 等 例 3-1 在 数 据 库 XSCJGL 中 定 义 二 级 学 院 数 据 表 CREATETABLE 二 级 学 院 ( 学 院 编 号 CHAR(2)PRIMARY KEY,

4 38 数 据 库 原 理 与 应 用 学 院 名 称 CHAR(30)UNIQUE NOT NULL, 院 长 CHAR(10) ) 例 3-2 在 数 据 库 XSCJGL 中 定 义 专 业 数 据 表 CREATETABLE 专 业 ( 专 业 编 号 CHAR(4)PRIMARY KEY, 专 业 名 称 CHAR(30)UNIQUE NOT NULL, 学 院 编 号 CHAR(2)NULLFOREIGN KEY REFERENCES 二 级 学 院 ON DELETESET NULL ON UPDATECASCADE ) 说 明 : (1) 外 部 关 键 字 引 用 二 级 学 院 关 系 中 的 同 名 属 性 ( 主 键 ) (2) ON DELETESET NULL : 专 业 数 据 表 中 的 学 院 编 号 是 外 部 关 键 字, 允 许 NULL, 当 删 除 被 参 照 记 录 ( 二 级 学 院 数 据 表 中 的 记 录 ) 时, 参 照 记 录 ( 专 业 数 据 表 中 的 记 录 ) 的 学 院 编 号 字 段 值 设 置 为 NULL (3) ON UPDATECASCADE : 修 改 被 参 照 记 录 ( 二 级 学 院 数 据 表 中 的 记 录 ) 的 主 键 ( 学 院 编 号 ) 时, 将 级 联 修 改 专 业 数 据 表 中 相 应 记 录 的 学 院 编 号 字 段 值 CREATETABLE 专 业 ( 专 业 编 号 CHAR(4)PRIMARY KEY, 专 业 名 称 CHAR(30)UNIQUE NOT NULL, 学 院 编 号 CHAR(2)NULLFOREIGN KEY REFERENCES 二 级 学 院 ( 学 院 编 号 ) ON DELETESET NULL ON UPDATECASCADE ) 说 明 : 外 部 关 键 字 与 引 用 的 二 级 学 院 数 据 表 中 的 主 键 可 以 不 具 有 相 同 的 名 字 例 3-3 在 数 据 库 XSCJGL 中 定 义 班 级 数 据 表 CREATETABLE 班 级 ( 班 级 编 号 CHAR(8)PRIMARY KEY, 班 级 名 称 CHAR(30)UNIQUE NOT NULL, 专 业 编 号 CHAR(4)FOREIGN KEY REFERENCES 专 业 ) 例 3-4 在 数 据 库 XSCJGL 中 定 义 学 生 数 据 表 CREATETABLE 学 生 ( 学 号 CHAR(12)PRIMARY KEY, 姓 名 CHAR(10)NOT NULL, 性 别 CHAR(2)CHECK( 性 别 = 男 OR 性 别 = 女 ), 出 生 日 期 DATETIME, 籍 贯 CHAR(30),

5 第 3 章 结 构 化 查 询 语 言 SQL 39 入 学 时 间 CHAR(4), 入 学 成 绩 INTCHECK( 入 学 成 绩 BETWEEN400AND800), 班 级 编 号 CHAR(8)FOREIGN KEY REFERENCES 班 级 ) 例 3-5 在 数 据 库 XSCJGL 中 定 义 课 程 数 据 表 CREATETABLE 课 程 ( 课 程 编 号 CHAR(6)PRIMARY KEY, 课 程 名 称 CHAR(30)NOT NULL ) 例 3-6 在 数 据 库 XSCJGL 中 定 义 成 绩 数 据 表 CREATETABLE 成 绩 ( 学 号 CHAR(12)FOREIGN KEY REFERENCES 学 生, 课 程 编 号 CHAR(6)FOREIGN KEY REFERENCES 课 程, 开 课 模 式 CHAR(6)CHECK( 开 课 模 式 = 必 修 课 OR 开 课 模 式 = 选 修 课 ), 学 时 INT, 学 分 NUMERIC(4,1), 考 核 方 式 CHAR(4)CHECK( 考 核 方 式 = 考 试 OR 考 核 方 式 = 考 查 ), 成 绩 INTCHECK( 成 绩 BETWEEN0AND100), 学 期 CHAR(12), PRIMARY KEY( 学 号, 课 程 编 号 ) ) 例 3-7 在 数 据 库 XSCJGL 中 定 义 教 学 计 划 数 据 表 CREATETABLE 教 学 计 划 ( 课 程 编 号 CHAR(6)FOREIGN KEY REFERENCES 课 程, 专 业 编 号 CHAR(4)FOREIGN KEY REFERENCES 专 业, 年 级 CHAR(4), 课 程 性 质 CHAR(10)CHECK ( 课 程 性 质 IN( 基 础 课, 学 科 基 础 课, 系 级 选 修 课, 专 业 课, 实 践 环 节 )), 开 课 模 式 CHAR(6)CHECK( 开 课 模 式 = 必 修 课 OR 开 课 模 式 = 选 修 课 ), 学 时 INT, 学 分 NUMERIC(4,1), 考 核 方 式 CHAR(4)CHECK( 考 核 方 式 = 考 试 OR 考 核 方 式 = 考 查 ), 学 期 CHAR(12), PRIMARY KEY( 课 程 编 号, 专 业 编 号, 年 级 ) ) 例 3-8 在 数 据 库 XSCJGL 中 定 义 选 课 数 据 表 CREATETABLE 选 课 ( 学 号 CHAR(12)FOREIGN KEY REFERENCES 学 生, 课 程 编 号 CHAR(6)FOREIGN KEY REFERENCES 课 程, 成 绩 CHAR(6)DEFAULT NULL, 学 期 CHAR(12),

6 40 数 据 库 原 理 与 应 用 PRIMARY KEY( 学 号, 课 程 编 号 ) ) 上 述 例 题, 在 对 数 据 表 定 义 的 同 时 完 成 了 完 整 性 约 束 定 义, 主 要 包 括 UNIQUE NULL NOT NULL PRIMARY KEY 多 属 性 主 键 与 单 属 性 主 键 外 部 关 键 字 的 两 种 引 用 方 法 CHECK 和 默 认 值 DEFAULT 应 用 等 数 据 表 的 修 改 和 删 除 一 个 数 据 表 建 立 之 后, 可 以 根 据 需 要 对 其 表 结 构 进 行 修 改 或 删 除 整 个 数 据 表 1. 修 改 数 据 表 结 构 修 改 数 据 表 结 构 就 是 添 加 修 改 和 删 除 属 性 列 及 完 整 性 约 束 SQL 的 ALTER TABLE 命 令 实 现 数 据 表 结 构 的 修 改, 命 令 格 式 如 下 : ALTERTABLE < 表 名 > ADD < 列 名 >< 数 据 类 型 > [< 列 级 完 整 性 约 束 >] DROP < 完 整 性 约 束 名 > DROPCOLUMN < 列 名 > ALTERCOLUMN < 列 名 >< 数 据 类 型 > [< 列 级 完 整 性 约 束 >] 说 明 : (1) ADD 增 加 新 的 属 性 列 (2) DROP 删 除 完 整 性 约 束 ( 只 能 删 除 有 名 称 的 完 整 性 约 束 ) 或 删 除 属 性 列 (3) ALTERCOLUMN 修 改 原 有 属 性 列 的 定 义 例 3-9 为 二 级 学 院 数 据 表 增 加 备 注 属 性 列 ALTERTABLE 二 级 学 院 ADD 备 注 CHAR(20)NULL 例 3-10 修 改 二 级 学 院 数 据 表 中 备 注 属 性 列 的 宽 度 ALTERTABLE 二 级 学 院 ALTERCOLUMN 备 注 CHAR(30)NULL 例 3-11 删 除 二 级 学 院 数 据 表 中 的 备 注 属 性 列 ALTERTABLE 二 级 学 院 DROPCOLUMN 备 注 例 3-12 删 除 选 课 数 据 表 中 成 绩 属 性 列 的 约 束 ALTERTABLE 选 课 DROPCONSTRAINTSCORE 2. 删 除 数 据 表 删 除 一 个 数 据 表, 则 数 据 表 的 结 构 以 及 数 据 表 中 的 所 有 记 录 约 束 和 索 引 等 将 被 永 久 地 删 除 SQL 的 DROPTABLE 命 令 实 现 数 据 表 的 删 除, 命 令 格 式 如 下 : DROPTABLE < 表 名 > 例 3-13 删 除 选 课 数 据 表 DROPTABLE 选 课 3.3 数 据 操 纵 功 能 SQL 的 数 据 操 纵 功 能 主 要 实 现 对 数 据 表 中 的 数 据 进 行 插 入 (INSERT) 更 新 (UP-

7 第 3 章 结 构 化 查 询 语 言 SQL 41 DATE) 和 删 除 (DELETE) 等 操 作 数 据 的 完 整 性 约 束 将 保 证 变 化 后 数 据 的 正 确 性 前 面 完 成 了 XSCJGL 数 据 库 中 二 级 学 院 专 业 班 级 学 生 选 课 成 绩 教 学 计 划 和 课 程 等 数 据 表 的 创 建, 本 节 将 在 此 基 础 上 实 现 数 据 的 插 入 更 新 和 删 除 XSCJGL 数 据 库 对 象 本 章 和 后 续 章 节 使 用 XSCJGL 数 据 库 对 象, 即 数 据 表, 为 便 于 理 解 下 面 数 据 操 作 和 数 据 表 之 间 的 关 系, 下 面 对 XSCJGL 数 据 库 中 的 数 据 表 和 表 中 记 录 进 行 介 绍 二 级 学 院 数 据 表 包 括 数 据 项 学 院 编 号 学 院 名 称 和 院 长, 其 中 记 录 如 表 3-3 所 示 表 3-3 二 级 学 院 数 据 表 学 院 编 号 学 院 名 称 院 长 01 计 算 机 学 院 李 军 02 机 电 学 院 张 岩 03 民 航 安 全 学 院 吴 跃 平 08 经 济 与 管 理 学 院 赵 君 专 业 数 据 表 包 括 数 据 项 专 业 编 号 专 业 名 称 和 学 院 编 号, 其 中 记 录 如 表 3-4 所 示 表 3-4 专 业 数 据 表 专 业 编 号 专 业 名 称 学 院 编 号 0101 计 算 机 网 络 工 程 计 算 机 应 用 公 共 事 业 管 理 信 息 管 理 与 信 息 系 统 08 班 级 数 据 表 包 括 数 据 项 班 级 编 号 班 级 名 称 和 专 业 编 号, 其 中 记 录 如 表 3-5 所 示 表 3-5 班 级 数 据 表 班 级 编 号 班 级 名 称 专 业 编 号 信 管 1 班 信 管 2 班 公 管 1 班 公 管 2 班 0801 学 生 数 据 表 包 括 数 据 项 学 号 姓 名 性 别 出 生 日 期 籍 贯 入 学 时 间 入 学 成 绩 和 班 级 编 号, 其 中 记 录 如 表 3-6 所 示 表 3-6 学 生 数 据 表 学 号 姓 名 性 别 出 生 日 期 籍 贯 入 学 时 间 入 学 成 绩 班 级 编 号 欧 阳 博 学 男 黑 龙 江 左 飞 男 辽 宁 解 梦 娇 女 辽 宁 王 欣 欣 女 山 东 选 课 数 据 表 包 括 数 据 项 学 号 课 程 编 号 成 绩 和 学 期, 其 中 记 录 如 表 3-7 所 示

8 42 数 据 库 原 理 与 应 用 表 3-7 选 课 数 据 表 学 号 课 程 编 号 成 绩 学 期 优 (1) 良 (1) (1) (1) 成 绩 数 据 表 包 括 数 据 项 学 号 课 程 编 号 开 课 模 式 学 时 学 分 考 核 方 式 成 绩 和 学 期, 其 中 记 录 如 表 3-8 所 示 表 3-8 成 绩 数 据 表 学 号 课 程 编 号 开 课 模 式 学 时 学 分 考 核 方 式 成 绩 学 期 必 修 课 32 2 考 查 优 (1) 必 修 课 32 2 考 查 良 (1) 必 修 课 考 试 (1) 必 修 课 64 4 考 试 (1) 教 学 计 划 数 据 表 包 括 数 据 项 课 程 编 号 专 业 编 号 年 级 课 程 性 质 开 课 模 式 学 时 学 分 考 核 方 式 和 学 期, 其 中 记 录 如 表 3-9 所 示 表 3-9 教 学 计 划 数 据 表 课 程 编 号 专 业 编 号 年 级 课 程 性 质 开 课 模 式 学 时 学 分 考 核 方 式 学 期 基 础 课 必 修 课 32 2 考 查 (1) 基 础 课 必 修 课 32 2 考 查 (1) 学 科 基 础 课 必 修 课 考 试 (1) 基 础 课 必 修 课 64 4 考 试 (1) 课 程 数 据 表 包 括 数 据 项 课 程 编 号 课 程 名 称 和 备 注, 其 中 记 录 如 表 3-10 所 示 表 3-10 课 程 数 据 表 课 程 编 号 课 程 名 称 备 注 平 面 设 计 项 目 管 理 C 语 言 程 序 设 计 英 语 (3) 学 生 成 绩 查 询 结 果 学 生 成 绩 表 中 的 记 录 如 表 3-11 所 示 表 3-11 学 生 成 绩 表 院 系 : 经 济 与 管 理 学 院 专 业 : 信 息 管 理 与 信 息 系 统 班 级 : 学 号 : 姓 名 : 欧 阳 博 学 学 期 课 程 编 号 课 程 名 称 开 课 模 式 学 时 学 分 考 核 方 式 成 绩 (1) 形 势 与 政 策 (3) 必 修 课 考 查 通 过 (1) 两 课 综 合 实 践 (2) 必 修 课 1 周 1 考 查 优 (1) 体 育 (3) 必 修 课 26 1 考 查 良 (1) Internet 网 页 设 计 必 修 课 2 周 2 考 查 优

9 第 3 章 结 构 化 查 询 语 言 SQL 43 院 系 : 经 济 与 管 理 学 院 专 业 : 信 息 管 理 与 信 息 系 统 班 级 : ( 续 表 ) 学 号 : 姓 名 : 欧 阳 博 学 学 期 课 程 编 号 课 程 名 称 开 课 模 式 学 时 学 分 考 核 方 式 成 绩 (1) 管 理 心 理 学 必 修 课 32 2 考 查 优 (1) 平 面 设 计 必 修 课 32 2 考 查 优 (1) 项 目 管 理 必 修 课 32 2 考 查 良 (1) C 语 言 程 序 设 计 必 修 课 考 试 (1) 英 语 (3) 必 修 课 64 4 考 试 (1) 马 克 思 主 义 基 本 原 理 必 修 课 32 2 考 试 插 入 操 作 SQL 的 INSERT 语 句 实 现 向 数 据 表 中 插 入 数 据, 常 用 的 有 两 种 格 式, 一 种 是 向 数 据 表 中 直 接 插 入 一 个 元 组 ( 记 录 ), 另 一 种 是 将 一 个 查 询 结 果 插 入 到 数 据 表 中 (1) 插 入 一 个 元 组 的 INSERT 语 句 的 格 式 如 下 : INSERTINTO < 表 名 >[(< 列 名 >[,< 列 名 > ])] VALUES(< 表 达 式 >[,< 表 达 式 > ]) 说 明 : VALUES 子 句 中 表 达 式 的 顺 序 应 该 与 列 名 的 顺 序 一 致, 若 省 略 了 列 名, 则 VALUES 子 句 应 给 出 每 一 列 ( 包 括 标 识 列 允 许 为 NULL 的 列 或 有 默 认 值 的 列 ) 的 值 (2) 插 入 一 个 查 询 结 果 ( 多 个 元 组 ) 的 INSERT 语 句 的 格 式 如 下 : INSERTINTO < 表 名 >[(< 列 名 >[,< 列 名 > ])] <SELECT 查 询 > 说 明 : INSERT 语 句 的 列 名 和 SELECT 语 句 中 列 名 的 个 数 顺 序 及 数 据 类 型 应 一 致 1. 向 数 据 表 中 插 入 一 个 元 组 ( 记 录 ) 例 3-14 向 二 级 学 院 数 据 表 中 插 入 记 录 INSERTINTO 二 级 学 院 ( 学 院 编 号, 学 院 名 称, 院 长 ) VALUES( 01, 计 算 机 学 院, 李 军 ) -- 假 定 表 中 没 有 记 录 或 INSERTINTO 二 级 学 院 VALUES( 01, 计 算 机 学 院, 李 军 ) 如 果 向 数 据 表 中 插 入 一 条 完 整 记 录, 可 以 省 略 列 名, 否 则 列 名 不 能 省 略 例 3-15 在 上 例 基 础 上, 向 二 级 学 院 数 据 表 中 插 入 表 3-3 中 的 其 他 记 录 INSERTINTO 二 级 学 院 ( 学 院 编 号, 学 院 名 称, 院 长 ) VALUES( 01, 机 电 学 院, 张 岩 ) 此 命 令 不 能 成 功 执 行 违 反 PRIMARY KEY 约 束, 学 院 编 号 是 主 键, 而 学 院 编 号 为 01 的 学 院 已 经 存 在 INSERTINTO 二 级 学 院 ( 学 院 编 号, 学 院 名 称, 院 长 ) VALUES( 02, 计 算 机 学 院, 张 岩 )

10 44 数 据 库 原 理 与 应 用 此 命 令 不 能 成 功 执 行 违 反 UNIQUE 约 束, 学 院 名 称 不 允 许 重 复, 而 计 算 机 学 院 已 经 存 在 INSERTINTO 二 级 学 院 ( 学 院 编 号, 院 长 ) VALUES( 02, 张 岩 ) 此 命 令 不 能 成 功 执 行 违 反 NOT NULL 约 束, 学 院 名 称 不 允 许 为 空, 而 数 据 表 定 义 中 也 没 有 设 置 默 认 值 正 确 的 插 入 命 令, 请 读 者 自 行 完 成 例 3-16 向 专 业 数 据 表 中 插 入 记 录 INSERTINTO 专 业 ( 专 业 编 号, 专 业 名 称, 学 院 编 号 ) VALUES( 0804, 信 息 管 理 与 信 息 系 统, 00 ) 此 命 令 不 能 成 功 执 行 违 反 参 照 完 整 性 约 束, 专 业 数 据 表 中 的 学 院 编 号 是 外 键, 参 照 二 级 学 院 数 据 表 中 的 学 院 编 号 值, 而 在 二 级 学 院 数 据 表 中 没 有 值 为 00 的 学 院 编 号 例 3-17 向 学 生 数 据 表 中 插 入 记 录 INSERTINTO 学 生 ( 学 号, 姓 名, 性 别, 入 学 成 绩, 班 级 编 号 ) VALUES( , 欧 阳 博 学, 男,500, ) 此 命 令 不 能 成 功 执 行 违 反 域 完 整 性 约 束, 学 生 数 据 表 中 要 求 入 学 成 绩 >=400 AND 入 学 成 绩 <=800 例 3-18 向 选 课 数 据 表 中 插 入 记 录 INSERTINTO 选 课 ( 学 号, 课 程 编 号, 成 绩, 学 期 ) VALUES( , 18345,90, (1) ) 如 果 暂 时 不 输 入 成 绩, 由 于 选 课 数 据 表 定 义 时, 成 绩 列 设 置 默 认 值 (DE- FAULT) 为 NULL, 在 不 违 反 其 他 约 束 的 情 况 下, 可 以 用 如 下 命 令 完 成 记 录 插 入 : INSERTINTO 选 课 ( 学 号, 课 程 编 号, 成 绩, 学 期 ) VALUES( , 18345,NULL, (1) ) 或 INSERTINTO 选 课 ( 学 号, 课 程 编 号, 成 绩, 学 期 ) VALUES( , 18345,DEFAULT, (1) ) 或 INSERTINTO 选 课 ( 学 号, 课 程 编 号, 学 期 ) VALUES( , 18345, (1) ) 2. 将 一 个 查 询 结 果 ( 多 条 记 录 ) 插 入 到 数 据 表 中 例 3-19 假 定 有 一 个 与 学 生 数 据 表 结 构 相 同 的 学 生 _2009 数 据 表, 在 该 表 中 备 份 2009 年 入 学 的 学 生 记 录 INSERTINTO 学 生 _2009 SELECT * FROM 学 生 WHERE 入 学 时 间 = 2009 提 示 : 还 可 以 用 SELECT 命 令 实 现 备 份 功 能

11 第 3 章 结 构 化 查 询 语 言 SQL 删 除 操 作 SQL 的 DELETE 语 句 实 现 删 除 数 据 表 中 的 数 据, 命 令 格 式 如 下 : DELETEFROM < 表 名 > [[FROM < 表 名 >]WHERE < 逻 辑 表 达 式 > 子 查 询 ] 说 明 : (1)DELETE 命 令 删 除 满 足 逻 辑 表 达 式 条 件 的 记 录 ; (2) 没 有 WHERE 子 句 时, 删 除 全 部 元 组 ( 记 录 ); (3) 删 除 的 条 件 可 以 与 其 他 数 据 表 相 关, 在 WHERE 子 句 中 也 可 以 嵌 入 子 查 询 例 3-20 删 除 专 业 数 据 表 中 学 院 编 号 为 08 的 所 有 记 录 DELETEFROM 专 业 WHERE 学 院 编 号 = 08 例 3-21 删 除 专 业 数 据 表 中 学 院 名 称 为 经 济 与 管 理 学 院 的 所 有 记 录 DELETEFROM 专 业 FROM 二 级 学 院 WHERE 二 级 学 院. 学 院 编 号 = 专 业. 学 院 编 号 AND 学 院 名 称 = 经 济 与 管 理 学 院 或 DELETEFROM 专 业 WHERE 学 院 编 号 IN (SELECT 学 院 编 号 FROM 二 级 学 院 WHERE 学 院 名 称 = 经 济 与 管 理 学 院 ) 更 新 操 作 SQL 的 UPDATE 语 句 实 现 更 新 数 据 表 中 的 数 据, 命 令 格 式 如 下 : UPDATE < 表 名 > SET < 列 名 >=< 表 达 式 >[,< 列 名 >=< 表 达 式 > ] [[FROM < 表 名 >]WHERE < 逻 辑 表 达 式 > 子 查 询 ] 说 明 : (1)UPDATE 更 新 满 足 逻 辑 表 达 式 条 件 的 记 录 ; (2) 没 有 WHERE 子 句 时, 更 新 全 部 元 组 ( 记 录 ); (3) 修 改 的 条 件 可 以 与 其 他 数 据 表 相 关, 在 WHERE 子 句 中 也 可 以 嵌 入 子 查 询 例 3-22 将 教 学 计 划 数 据 表 中 学 期 为 (1) 且 课 程 编 号 为 的 课 程 的 学 时 修 改 为 64, 学 分 修 改 为 4 UPDATE 教 学 计 划 SET 学 时 =64, 学 分 =4 WHERE 学 期 = (1) AND 课 程 编 号 = 例 3-23 将 专 业 数 据 表 中 专 业 名 称 为 信 息 管 理 与 信 息 系 统 的 专 业 的 学 院 编 号 修 改 为 68 UPDATE 专 业 SET 学 院 编 号 = 68 WHERE 专 业 名 称 = 信 息 管 理 与 信 息 系 统 此 命 令 不 能 成 功 执 行 违 反 参 照 完 整 性 约 束, 专 业 数 据 表 中 的 学 院 编 号 是 外 键, 参 照 二 级 学 院 数 据 表 中 的 学 院 编 号 值, 而 在 二 级 学 院 数 据 表 中 没 有 值 为 68 的 学

12 46 数 据 库 原 理 与 应 用 院 编 号 例 3-24 将 二 级 学 院 数 据 表 中 学 院 名 称 为 经 济 与 管 理 学 院 的 学 院 的 学 院 编 号 修 改 为 68 UPDATE 二 级 学 院 SET 学 院 编 号 = 68 WHERE 学 院 名 称 = 经 济 与 管 理 学 院 此 命 令 成 功 执 行 由 于 专 业 数 据 表 的 学 院 编 号 更 新 参 照 完 整 性 定 义 为 ON UPDATECASCADE ( 级 联 ), 参 照 该 记 录 的 专 业 数 据 表 中 的 学 院 编 号 字 段 值 自 动 更 新 例 3-25 用 教 学 计 划 数 据 表 中 学 期 为 (1) 的 学 期 课 程 的 学 时 和 学 分 更 新 成 绩 数 据 表 中 的 相 应 数 据 UPDATE 成 绩 SET 成 绩. 学 时 = 教 学 计 划. 学 时, 成 绩. 学 分 = 教 学 计 划. 学 分 FROM 教 学 计 划 WHERE 教 学 计 划. 课 程 编 号 = 成 绩. 课 程 编 号 AND 学 期 = (1) 例 3-26 将 成 绩 数 据 表 中 课 程 名 称 为 C 语 言 程 序 设 计 的 课 程 的 成 绩 乘 以 0.7 UPDATE 成 绩 SET 成 绩 *0.7 WHERE 课 程 编 号 =(SELECT 课 程 编 号 FROM 课 程 WHERE 课 程 名 称 = C 语 言 程 序 设 计 )AND 学 期 = (1) 成 绩 数 据 表 中 不 含 课 程 名 称 数 据 项, 因 此 通 过 课 程 数 据 表 的 子 查 询 实 现 对 成 绩 表 的 更 新 通 过 上 例 可 以 看 出 数 据 完 整 性 在 INSERT UPDATE 和 DELETE 操 作 中 的 约 束 作 用 执 行 插 入 操 作 时 需 要 分 别 检 查 实 体 完 整 性 规 则 参 照 完 整 性 规 则 和 用 户 定 义 完 整 性 规 则 ; 执 行 删 除 操 作 时 一 般 只 需 要 检 查 参 照 完 整 性 规 则 ; 执 行 更 新 操 作 可 以 看 做 是 先 删 除 旧 的 元 组, 然 后 再 插 入 新 的 元 组, 所 以 执 行 更 新 操 作 时 的 完 整 性 检 查 综 合 了 上 述 两 种 情 况 注 意 : 数 据 完 整 性 的 作 用 就 是 保 证 数 据 库 中 数 据 的 正 确 性, 但 这 种 保 证 是 相 对 的 例 如, 在 选 课 数 据 表 中 对 成 绩 属 性 的 取 值 有 0~100 的 约 束, 如 果 将 60 输 入 为 66, 这 种 错 误 无 法 靠 数 据 完 整 性 检 查 出 来 3.4 数 据 查 询 功 能 数 据 查 询 是 结 构 化 查 询 语 言 最 重 要 的 功 能, 可 以 通 过 SQLSELECT 命 令 实 现 简 单 或 复 杂 的 查 询, 使 用 灵 活 方 便 SQLSELECT 命 令 基 本 格 式 如 下 : SELECT [ALL DISTINCT TOP]< 表 达 式 >[,< 表 达 式 > ] [INTO < 新 表 名 >] FROM < 表 名 >[,< 表 名 > ] [WHERE < 逻 辑 表 达 式 >] [GROUPBY < 列 名 >[,< 列 名 > ][HAVING < 逻 辑 表 达 式 >]]

13 第 3 章 结 构 化 查 询 语 言 SQL 47 [ORDERBY < 列 名 >[ASC DESC][,< 列 名 >[ASC DESC]] ] 说 明 : (1) SELECT 列 出 要 查 询 的 结 果 ; (2) ALL DISTINCT 表 示 查 询 结 果 不 去 掉 或 去 掉 重 复 元 组 ; (3) TOP 表 示 查 询 结 果 的 前 一 组 记 录, 必 须 与 ORDERBY 一 起 使 用 ; (4) < 表 达 式 > 指 定 要 查 询 的 表 中 的 列 名 或 虚 列 ( 表 达 式 ), 如 果 为 * 表 示 所 有 列 ; (5) INTO 表 示 查 询 结 果 存 入 新 表 ; (6) FROM 表 示 查 询 的 数 据 表 或 视 图 ; (7) WHERE 表 示 查 询 条 件 ; (8) GROUPBY 指 定 分 组 查 询, HAVING 指 出 分 组 统 计 条 件 ; (9) ORDERBY 指 定 查 询 结 果 排 序 方 式, ASC 表 示 升 序, DESC 表 示 降 序, 默 认 为 升 序 单 表 查 询 单 表 查 询 只 涉 及 对 一 个 数 据 表 或 视 图 的 查 询 例 3-27 查 询 学 生 数 据 表 中 的 全 体 学 生 记 录 SELECT * FROM 学 生 例 3-28 查 询 学 生 数 据 表 中 全 体 学 生 的 学 号 姓 名 和 入 学 成 绩 SELECT 学 号, 姓 名, 入 学 成 绩 FROM 学 生 例 3-29 查 询 学 生 数 据 表 中 全 体 学 生 的 学 号 姓 名 和 年 龄 SELECT 学 号, 姓 名,YEAR(GETDATE())-YEAR( 出 生 日 期 )AS 年 龄 FROM 学 生 学 生 数 据 表 中 没 有 年 龄 列, 可 为 查 询 列 或 表 达 式 指 定 别 名 例 3-30 查 询 学 生 数 据 表 中 的 前 5 条 记 录 SELECT TOP5 * FROM 学 生 此 命 令 不 能 执 行,TOP 需 要 与 ORDERBY 一 起 使 用 例 3-31 查 询 教 学 计 划 数 据 表 中 的 课 程 性 质 SELECTDISTINCT 课 程 性 质 FROM 教 学 计 划 查 询 结 果 去 掉 重 复 的 课 程 性 质 记 录 行 例 3-32 查 询 学 生 数 据 表 中 入 学 成 绩 大 于 等 于 500 的 学 生 记 录 SELECT * FROM 学 生 WHERE 入 学 成 绩 >=500 例 3-33 查 询 学 生 数 据 表 中 入 学 成 绩 大 于 等 于 550 且 小 于 等 于 600 的 学 生 记 录 SELECT * FROM 学 生 WHERE 入 学 成 绩 >=550AND 入 学 成 绩 <=600 或 SELECT * FROM 学 生 WHERE 入 学 成 绩 BETWEEN550AND600 NOTBETWEEN550AND600 则 表 示 小 于 550 或 大 于 600 例 3-34 查 询 学 生 数 据 表 中 出 生 日 期 在 1990 年 至 1995 年 之 间 的 学 生 的 学 号 姓 名 和 出 生 日 期

14 48 数 据 库 原 理 与 应 用 SELECT 学 号, 姓 名, 出 生 日 期 FROM 学 生 WHERE 出 生 日 期 BETWEEN AND 例 3-35 查 询 专 业 数 据 表 中 学 院 编 号 为 06 和 08 的 专 业 记 录 SELECT * FROM 专 业 WHERE 学 院 编 号 = 06 OR 学 院 编 号 = 08 或 SELECT * FROM 专 业 WHERE 学 院 编 号 IN ( 06, 08 ) 学 院 编 号 NOTIN ( 06, 08 ) 表 示 什 么 查 询 条 件, 请 读 者 自 己 总 结 例 3-36 查 询 学 生 数 据 表 中 姓 张 的 学 生 记 录 SELECT * FROM 学 生 WHERE 姓 名 = 张 或 SELECT * FROM 学 生 WHERE 姓 名 LIKE 张 % 查 询 有 精 确 查 询 和 模 糊 查 询, 可 以 使 用 LIKE 或 NOTLIKE 进 行 字 符 串 匹 配 模 糊 查 询, 其 通 配 符 如 表 3-12 所 示 表 3-12 通 配 符 通 配 符 说 明 % 零 个 或 多 个 任 意 字 符 _( 下 划 线 ) 任 何 一 个 字 符 [ ] 指 定 范 围 或 集 合 中 的 任 何 单 个 字 符 [^] 不 属 于 指 定 范 围 或 集 合 中 的 任 何 单 个 字 符 查 询 班 级 为 且 学 号 最 后 一 位 是 1 的 所 有 学 生 记 录 : SELECT * FROM 学 生 WHERE 学 号 LIKE %1 AND 班 级 = 查 询 专 业 编 号 前 两 位 为 01 或 08 的 所 有 专 业 记 录 : SELECT * FROM 专 业 WHERE 专 业 编 号 LIKE 0[18]% 查 询 专 业 编 号 前 两 位 从 01 到 08 的 所 有 专 业 记 录 : SELECT * FROM 专 业 WHERE 专 业 编 号 LIKE 0[1~8]% 查 询 专 业 编 号 最 后 一 位 不 是 1 到 3 的 所 有 专 业 记 录 : SELECT * FROM 专 业 WHERE 专 业 编 号 LIKE %[^1~3] 查 询 专 业 编 号 最 后 一 位 不 是 1 或 3 的 所 有 专 业 记 录 : SELECT * FROM 专 业 WHERE 专 业 编 号 LIKE %[^13] 例 3-37 查 询 课 程 数 据 表 中 课 程 名 称 为 Delphi_6.0 的 课 程 编 号 SELECT 课 程 编 号 FROM 课 程 WHERE 课 程 名 称 = Delphi_6.0 或 SELECT 课 程 编 号 FROM 课 程 WHERE 课 程 名 称 LIKE Delphi/_6.0 ESCAPE / 在 LIKE Delphi/_6.0 ESCAPE / 中, ESCAPE / 表 示 字 符 串 中 / 后 的 下 划 线 _ 不 作 为 通 配 符 处 理 例 3-38 查 询 成 绩 数 据 表 中, 在 (1) 学 期 选 修 了 课 程 编 号 为 的 课 程 的 学 生 记 录, 结 果 按 成 绩 降 序 排 列 SELECT * FROM 成 绩

15 第 3 章 结 构 化 查 询 语 言 SQL 49 WHERE 学 期 = (1) AND 课 程 编 号 = ORDERBY 成 绩 DESC 例 3-39 查 询 成 绩 数 据 表 中, 在 (1) 学 期 选 修 了 课 程 的 学 生 记 录, 结 果 按 课 程 编 号 升 序, 相 同 课 程 中 按 成 绩 降 序 排 列 SELECT * FROM 成 绩 WHERE 学 期 = (1) ORDERBY 课 程 编 号, 成 绩 DESC 例 3-40 查 询 成 绩 数 据 表 中, 在 (1) 学 期 选 修 了 课 程 编 号 为 的 课 程 且 成 绩 为 前 5 名 的 学 生 记 录 SELECT TOP5 * FROM 成 绩 WHERE 学 期 = (1) AND 课 程 编 号 = ORDERBY 成 绩 DESC 例 3-41 查 询 统 计 成 绩 数 据 表 中, 在 (1) 学 期 选 修 了 课 程 编 号 为 的 课 程 的 学 生 总 数 SELECTCOUNT(*)AS 学 生 数 FROM 成 绩 WHERE 学 期 = (1) AND 课 程 编 号 = SQLSELECT 查 询 可 以 直 接 对 查 询 结 果 进 行 汇 总 计 算 和 分 组 计 算, 实 现 汇 总 计 算 的 函 数 称 为 聚 合 函 数 SQL 中 常 用 的 聚 合 函 数 如 表 3-13 所 示 表 3-13 聚 合 函 数 函 数 功 能 COUNT 计 数 ( 记 录 数 ) SUM 求 和 ( 数 值 列 ) AVG 平 均 值 ( 数 值 列 ) MAX MIN 最 大 值 最 小 值 例 3-42 查 询 统 计 成 绩 数 据 表 中, 在 (1) 学 期 选 修 了 课 程 的 学 生 总 数 SELECTCOUNT(DISTINCT 学 号 )AS 选 修 课 程 学 生 数 FROM 成 绩 WHERE 学 期 = (1) COUNT(DISTINCT 学 号 ) 是 对 消 除 重 复 元 组 后 的 记 录 进 行 计 数, 因 为 在 同 一 学 期 一 名 学 生 可 以 选 修 多 门 课 程 例 3-43 查 询 统 计 专 业 数 据 表 中, 各 个 学 院 所 包 括 的 专 业 个 数 SELECT 学 院 编 号,COUNT(*)AS 专 业 数 FROM 专 业 GROUPBY 学 院 编 号 例 3-44 查 询 专 业 数 据 表 中 专 业 数 超 过 3 个 的 学 院 SELECT 学 院 编 号,COUNT(*)AS 专 业 数

16 50 数 据 库 原 理 与 应 用 FROM 专 业 GROUPBY 学 院 编 号 HAVINGCOUNT(*)>3 例 3-45 查 询 统 计 成 绩 数 据 表 中, (1) 学 期 学 生 所 选 课 程 中 每 门 课 程 的 平 均 成 绩 SELECT 课 程 编 号,AVG( 成 绩 )AS 平 均 成 绩 FROM 成 绩 GROUPBY 课 程 编 号 WHERE 学 期 = (1) 例 3-46 创 建 成 绩 数 据 表 的 一 个 副 本, 数 据 表 名 为 成 绩 _ , 其 中 只 包 含 (1) 学 期 的 所 有 记 录 SELECT * INTO 成 绩 _ FROM 成 绩 WHERE 学 期 = (1) 例 3-47 创 建 成 绩 数 据 表 的 一 个 副 本, 数 据 表 名 为 成 绩 _ 副 本, 要 求 只 包 含 结 构, 不 包 含 记 录 SELECT * INTO 成 绩 _ 副 本 FROM 成 绩 WHERE1=2 条 件 1=2 不 成 立, 所 以 成 绩 _ 副 本 数 据 表 中 不 含 任 何 记 录, 只 是 数 据 表 结 构 与 成 绩 数 据 表 相 同 例 3-48 查 询 专 业 数 据 表 中 学 院 编 号 为 06 和 08 的 专 业 记 录 SELECT * FROM 专 业 WHERE 学 院 编 号 = 06 UNION SELECT * FROM 专 业 WHERE 学 院 编 号 = 08 SQLServer2005 支 持 集 合 的 并 (UNION) 交 (INTERSECT) 和 差 (EXCEPT) 运 算, 有 些 查 询 可 通 过 集 合 运 算 实 现 查 询 课 程 性 质 为 学 科 基 础 课 并 被 学 号 为 的 学 生 选 修 了 的 课 程 的 课 程 编 号 : SELECT 课 程 编 号 FROM 教 学 计 划 WHERE 课 程 性 质 = 学 科 基 础 课 INTERSECT SELECT 课 程 编 号 FROM 选 课 WHERE 学 号 = 查 询 (1) 学 期 没 有 被 学 生 选 修 的 课 程 的 课 程 编 号 : SELECT 课 程 编 号 FROM 教 学 计 划 WHERE 学 期 = (1) EXCEPT SELECT 课 程 编 号 FROM 选 课 WHERE 学 期 = (1) 连 接 查 询 连 接 查 询 就 是 查 询 条 件 或 查 询 结 果 涉 及 两 个 或 两 个 以 上 数 据 表 的 查 询 连 接 查 询 是 关 系 数 据 库 中 最 主 要 的 查 询, 主 要 包 括 内 连 接 外 连 接 和 交 叉 连 接 等 下

17 第 3 章 结 构 化 查 询 语 言 SQL 51 面 主 要 介 绍 内 连 接 和 外 连 接 1. 内 连 接 内 连 接 中 只 有 满 足 连 接 条 件 的 记 录 信 息 才 会 出 现 在 查 询 结 果 中 在 非 ANSI 标 准 的 实 现 中, 连 接 查 询 操 作 是 在 WHERE 子 句 中 执 行 的, 即 在 WHERE 子 句 中 指 定 表 连 接 条 件 在 ANSI 标 准 的 实 现 中, 连 接 查 询 操 作 是 在 JOIN 子 句 中 执 行 的 内 连 接 的 格 式 为 : SELECT < 属 性 或 表 达 式 列 表 > FROM < 表 1>[INNER]JOIN< 表 2>ON< 连 接 条 件 > [WHERE < 查 询 条 件 >] 说 明 : (1) 属 性 或 表 达 式 列 表 中, 连 接 表 中 如 果 有 相 同 属 性, 属 性 前 应 加 所 属 表 名, 如 表 1. 属 性 ; (2) INNER 是 系 统 默 认 的, 可 以 省 略 INNER 关 键 字 ; (3) 连 接 条 件 的 一 般 格 式 为 [< 表 1>.]< 列 名 1>< 比 较 运 算 符 >[< 表 2.>] < 列 名 2> ; (4) 比 较 运 算 符 为 = 时, 称 为 等 值 连 接 ; (5) 若 在 目 标 列 中 去 除 相 同 的 字 段 名, 称 为 自 然 连 接 ; (6) JOIN ON 连 接 可 以 实 现 多 表 连 接 例 3-49 查 询 各 个 学 院 的 专 业 编 号 专 业 名 称 和 学 院 名 称 SELECT 专 业 编 号, 专 业 名 称, 学 院 名 称 FROM 专 业, 二 级 学 院 WHERE 专 业. 学 院 编 号 = 二 级 学 院. 学 院 编 号 或 SELECT 专 业 编 号, 专 业 名 称, 学 院 名 称 FROM 专 业 JOIN 二 级 学 院 ON 专 业. 学 院 编 号 = 二 级 学 院. 学 院 编 号 例 3-50 查 询 学 院 名 称 为 经 济 与 管 理 学 院 的 所 有 专 业 记 录 SELECT 专 业 编 号, 专 业 名 称 FROM 专 业, 二 级 学 院 WHERE 专 业. 学 院 编 号 = 二 级 学 院. 学 院 编 号 AND 学 院 名 称 = 经 济 与 管 理 学 院 或 SELECT 专 业 编 号, 专 业 名 称 FROM 专 业 JOIN 二 级 学 院 ON 专 业. 学 院 编 号 = 二 级 学 院. 学 院 编 号 WHERE 学 院 名 称 = 经 济 与 管 理 学 院 例 3-51 查 询 (1) 学 期 成 绩 在 80 分 以 上 的 所 有 学 生 的 学 号 姓 名 班 级 编 号 课 程 名 称 和 成 绩 SELECT 学 生. 学 号, 学 生. 姓 名, 学 生. 班 级 编 号, 课 程 名 称, 成 绩 FROM 学 生, 成 绩, 课 程 WHERE 学 生. 学 号 = 成 绩. 学 号 AND 成 绩. 课 程 编 号 = 课 程. 课 程 编 号 AND 学 期 =

18 52 数 据 库 原 理 与 应 用 (1) AND 成 绩 >80 或 SELECT 学 生. 学 号, 姓 名, 班 级 编 号, 课 程 名 称, 成 绩 FROM 学 生 JOIN 成 绩 JOIN 课 程 ON 成 绩. 课 程 编 号 = 课 程. 课 程 编 号 ON 学 生. 学 号 = 成 绩. 学 号 WHERE 学 期 = (1) AND 成 绩 >80 注 意 :JOIN 的 顺 序 与 连 接 条 件 ON 的 顺 序 相 反 例 3-52 查 询 (1) 学 期 中 考 核 方 式 为 考 试 的 各 门 课 程 的 平 均 成 绩 和 最 高 成 绩 SELECT 课 程 名 称,AVG( 成 绩 )AS 平 均 成 绩,MAX( 成 绩 )AS 最 高 成 绩 FROM 课 程 JOIN 选 课 JOIN 教 学 计 划 ON 选 课. 课 程 编 号 = 教 学 计 划. 课 程 编 号 ON 课 程. 课 程 编 号 = 选 课. 课 程 编 号 GROUPBY 课 程 名 称 WHERE 学 期 = (1) AND 考 核 方 式 = 考 试 如 果 用 成 绩 数 据 表 代 替 选 课 数 据 表, 教 学 计 划 数 据 表 是 否 可 以 省 略? 例 3-53 查 询 (1) 学 期 班 级 为 的 学 生 考 试 课 程 的 成 绩, 显 示 学 号 姓 名 课 程 名 称 和 成 绩, 同 时 计 算 每 个 学 生 的 平 均 成 绩 最 高 成 绩 和 最 低 成 绩 SELECT 学 生. 学 号, 学 生. 姓 名, 课 程. 课 程 名 称, 成 绩 FROM 学 生, 选 课, 教 学 计 划, 课 程 WHERE 学 生. 学 号 = 选 课. 学 号 AND 选 课. 课 程 编 号 = 教 学 计 划. 课 程 编 号 AND 课 程. 课 程 编 号 = 教 学 计 划. 课 程 编 号 AND 选 课. 学 期 = (1) AND 班 级 = AND 考 核 方 式 = 考 试 ORDERBY 学 生. 学 号 COMPUTE AVG( 成 绩 ),MAX( 成 绩 ),MIN( 成 绩 )BY 学 生. 学 号 说 明 : SQLServer 支 持 COMPUTE 计 算 和 汇 总, 不 仅 可 以 显 示 汇 总 或 分 组 计 算 的 结 果, 还 可 以 显 示 明 细 信 息 (1)SELECT 子 句 指 出 查 询 明 细 ; (2) COMPUTE < 聚 合 函 数 > BY < 列 名 > 说 明 进 行 分 组 计 算, 必 须 同 时 使 用 ORDERBY < 列 名 >, 要 求 列 名 相 对 应 ; (3) COMPUTE < 聚 合 函 数 > 说 明 对 整 个 查 询 结 果 进 行 汇 总 计 算 例 3-54 查 询 (1) 学 期 班 级 学 生 考 试 课 程 的 成 绩, 显 示 学 号 姓 名 课 程 名 称 和 成 绩, 同 时 计 算 每 个 学 生 的 平 均 成 绩 最 高 成 绩 和 最 低 成 绩, 在 此 基 础 上 计 算 所 有 学 生 的 平 均 成 绩 最 高 成 绩 和 最 低 成 绩 SELECT 学 生. 学 号, 学 生. 姓 名, 课 程. 课 程 名 称, 成 绩 FROM 学 生, 选 课, 教 学 计 划, 课 程 WHERE 学 生. 学 号 = 选 课. 学 号 AND 选 课. 课 程 编 号 = 教 学 计 划. 课 程 编 号

19 第 3 章 结 构 化 查 询 语 言 SQL 53 AND 课 程. 课 程 编 号 = 教 学 计 划. 课 程 编 号 AND 选 课. 学 期 = (1) AND 班 级 = AND 考 核 方 式 = 考 试 ORDERBY 学 生. 学 号 COMPUTE AVG( 成 绩 ),MAX( 成 绩 ),MIN( 成 绩 )BY 学 生. 学 号 COMPUTE AVG( 成 绩 ),MAX( 成 绩 ),MIN( 成 绩 ) 比 较 两 个 COMPUTE 和 COMPUTEBY 的 区 别 2. 外 连 接 外 连 接 只 限 制 一 张 表 中 的 数 据 必 须 满 足 连 接 条 件, 而 另 一 张 表 中 的 数 据 可 以 不 满 足 连 接 条 件 外 连 接 主 要 包 括 左 连 接 右 连 接 和 完 全 连 接, 一 般 格 式 为 : SELECT < 属 性 或 表 达 式 列 表 > FROM < 表 1>LEFT RIGHT FULL [OUTER]JOIN< 表 2>ON< 连 接 条 件 > [WHERE < 查 询 条 件 >] 说 明 : (1)LEFTJOIN 结 果 中 除 了 包 括 满 足 连 接 条 件 的 记 录 外, 还 包 括 左 表 中 的 所 有 记 录, 在 不 满 足 连 接 条 件 的 结 果 记 录 行 上 的 右 表 列 上 返 回 NULL; (2)RIGHTJOIN 结 果 中 除 了 包 括 满 足 连 接 条 件 的 记 录 外, 还 包 括 右 表 中 的 所 有 记 录, 在 不 满 足 连 接 条 件 的 结 果 记 录 行 上 的 左 表 列 上 返 回 NULL; (3)FULLJOIN 结 果 中 除 了 包 括 满 足 条 件 的 记 录 外, 还 包 括 两 个 表 中 的 所 有 记 录 ; (4) 外 连 接 只 能 在 两 个 表 之 间 进 行 例 3-55 查 询 (1) 学 期 学 生 的 选 课 情 况, 包 括 选 修 课 程 的 学 生 和 没 有 选 修 课 程 的 学 生 SELECT 学 生. 学 号, 姓 名, 课 程 编 号, 成 绩 FROM 学 生 LEFTJOIN 成 绩 ON 学 生. 学 号 = 成 绩. 学 号 WHERE 学 期 = (1) 注 意 观 察 查 询 结 果 中 满 足 条 件 和 不 满 足 条 件 的 记 录 以 及 没 有 选 修 课 程 的 学 生 的 课 程 编 号 和 成 绩 的 值 嵌 套 查 询 嵌 套 查 询 就 是 将 一 个 SQLSELECT 查 询 ( 称 为 子 查 询 或 内 层 查 询 ) 的 结 果 作 为 另 一 个 SQL SELECT 查 询 ( 称 为 父 查 询 或 外 层 查 询 ) 的 条 件 嵌 套 查 询 主 要 有 使 用 IN/ ANY/SOME/ALL 运 算 的 子 查 询 和 使 用 EXISTS 的 子 查 询 1. 使 用 IN 运 算 的 子 查 询 IN 运 算 子 查 询 的 结 果 是 一 个 集 合 父 查 询 通 过 IN 运 算 符 将 父 查 询 中 的 一 个 表 达 式 与 子 查 询 结 果 集 中 的 每 一 个 值 进 行 比 较, 如 果 表 达 式 的 值 与 子 查 询 中 的 某 个 值 相 等 则 返 回 TRUE, 否 则 返 回 FALSE NOTIN 与 IN 返 回 结 果 相 反 IN 子 查 询 用 于 判 断 一 个 给 定 值 是 否 在 子 查 询 结 果 集 中 例 3-56 查 询 学 院 名 称 为 经 济 与 管 理 学 院 的 学 院 的 所 有 专 业

20 54 数 据 库 原 理 与 应 用 SELECT * FROM 专 业 WHERE 学 院 编 号 IN(SELECT 学 院 编 号 FROM 二 级 学 院 WHERE 学 院 名 称 = 经 济 与 管 理 学 院 ) 查 询 结 果 来 自 专 业 数 据 表, 但 查 询 条 件 学 院 名 称 在 表 中 并 不 存 在, 可 以 先 从 二 级 学 院 数 据 表 中 查 询 经 济 与 管 理 学 院 的 学 院 编 号, 然 后 将 其 作 为 专 业 数 据 表 的 查 询 条 件, 查 询 相 应 专 业 例 3-57 查 询 不 属 于 学 院 名 称 为 经 济 与 管 理 学 院 的 学 院 的 所 有 专 业 SELECT * FROM 专 业 WHERE 学 院 编 号 NOTIN( SELECT 学 院 编 号 FROM 二 级 学 院 WHERE 学 院 名 称 = 经 济 与 管 理 学 院 ) 例 3-58 查 询 2009 年 入 学 且 姓 名 为 欧 阳 博 学 的 学 生 所 在 的 二 级 学 院 SELECT 学 院 名 称 FROM 二 级 学 院 WHERE 学 院 编 号 IN ( SELECT 学 院 编 号 FROM 专 业 WHERE 专 业 编 号 IN ( SELECT 专 业 编 号 FROM 班 级 WHERE 班 级 编 号 IN ( SELECT 班 级 编 号 FROM 学 生 WHERE 入 学 时 间 = 2009 AND 姓 名 = 欧 阳 博 学 ))) 本 例 查 询 过 程 : 入 学 时 间 = 2009 AND 姓 名 = 欧 阳 博 学 班 级 编 号 ( 学 生 ) 专 业 编 号 ( 班 级 ) 学 院 编 号 ( 专 业 ) 学 院 名 称 ( 二 级 学 院 ) 例 3-59 应 用 连 接 操 作 完 成 上 例 的 查 询 SELECT 学 院 名 称 FROM 二 级 学 院, 专 业, 班 级, 学 生 WHERE 入 学 时 间 = 2009 AND 姓 名 = 欧 阳 博 学 AND 二 级 学 院. 学 院 编 号 = 专 业. 学 院 编 号 AND 专 业. 专 业 编 号 = 班 级. 专 业 编 号 AND 班 级. 班 级 编 号 = 学 生. 班 级 编 号 或 SELECT 学 院 名 称 FROM 二 级 学 院 JOIN 专 业 JOIN 班 级 JOIN 学 生 ON 班 级. 班 级 编 号 = 学 生. 班 级 编 号 ON 专 业. 专 业 编 号 = 班 级. 专 业 编 号

21 第 3 章 结 构 化 查 询 语 言 SQL 55 ON 二 级 学 院. 学 院 编 号 = 专 业. 学 院 编 号 WHERE 入 学 时 间 = 2009 AND 姓 名 = 欧 阳 博 学 2. 使 用 比 较 运 算 的 子 查 询 比 较 运 算 符 子 查 询 的 结 果 是 一 个 单 值 如 果 能 确 定 子 查 询 的 结 果 是 一 个 单 值, 就 可 以 使 用 比 较 子 查 询 常 用 的 比 较 运 算 符 有 :> >= < <= = <>!=!> 和!< 例 3-60 查 询 与 学 生 欧 阳 博 学 在 同 一 个 班 级 的 所 有 学 生 的 学 号 姓 名 和 入 学 成 绩 SELECT 学 号, 姓 名, 入 学 成 绩 FROM 学 生 WHERE 班 级 编 号 =( SELECT 班 级 编 号 FROM 学 生 WHERE 姓 名 = 欧 阳 博 学 ) 此 查 询 结 果 中 包 含 欧 阳 博 学 请 学 生 自 行 改 写 SQL 命 令, 使 查 询 结 果 排 除 欧 阳 博 学 本 例 的 查 询 也 可 以 用 自 连 接 完 成 自 连 接 是 一 种 特 殊 的 内 连 接, 相 互 连 接 的 表 在 物 理 上 是 同 一 张 表, 但 逻 辑 上 分 为 两 张 表 使 用 自 连 接 时 必 须 为 两 个 表 取 别 名, 使 其 在 逻 辑 上 成 为 两 张 表 SELECT 学 号, 姓 名, 入 学 成 绩 FROM 学 生 a, 学 生 b WHEREa. 班 级 编 号 =b. 班 级 编 号 andb. 姓 名 = 欧 阳 博 学 3. 使 用 ANY SOME 或 ALL 的 子 查 询 在 嵌 套 查 询 中 可 以 使 用 ANY SOME 或 ALL 等 关 键 字 完 成 嵌 套 查 询, 表 达 形 式 如 下 : < 表 达 式 >< 比 较 运 算 符 >[ANY SOME ALL]( 子 查 询 ) (1) 比 较 运 算 符 :> >= < <= = <>!=!> 和!< (2)ANY 与 SOME 同 义, 表 示 表 达 式 与 子 查 询 结 果 集 中 的 某 个 值 满 足 比 较 关 系 时 返 回 TRUE; 否 则, 返 回 FALSE (3)ALL 表 示 表 达 式 与 子 查 询 结 果 集 中 的 每 个 值 都 满 足 比 较 关 系 时 返 回 TRUE; 否 则, 返 回 FALSE 例 3-61 查 询 (1) 学 期, 班 级 所 开 课 程 的 课 程 性 质 和 考 核 方 式 SELECT 教 学 计 划. 课 程 编 号, 课 程 名 称, 课 程 性 质, 考 核 方 式 FROM 教 学 计 划 JOIN 课 程 ON 教 学 计 划. 课 程 编 号 = 课 程. 课 程 编 号 WHERE 课 程 编 号 =ANY(SELECTDISTINCT 课 程 编 号 FROM 选 课 WHERE 学 号 IN(SELECT 学 号 FROM 学 生 WHERE 班 级 编 号 = )

22 56 数 据 库 原 理 与 应 用 AND 学 期 = (1) ) 或 SELECT 成 绩. 课 程 编 号, 课 程 名 称, 课 程 性 质, 考 核 方 式 FROM 课 程 JOIN 成 绩 JOIN 学 生 ON 成 绩. 学 号 = 学 生. 学 号 ON 成 绩. 课 程 编 号 = 课 程. 课 程 编 号 WHERE 班 级 编 号 = AND 学 期 = (1) 例 3-62 查 询 比 班 中 某 一 学 生 年 龄 小 的 其 他 班 的 学 生 学 号 姓 名 和 班 级 SELECT 学 号, 姓 名, 班 级 FROM 学 生 WHERE 出 生 日 期 >ANY(SELECT 出 生 日 期 FROM 学 生 WHERE 班 级 编 号 = ) AND 班 级 编 号 <> 例 3-63 查 询 比 班 中 所 有 学 生 年 龄 都 小 的 其 他 班 的 学 生 学 号 姓 名 和 班 级 SELECT 学 号, 姓 名, 班 级 FROM 学 生 WHERE 出 生 日 期 >ALL(SELECT 出 生 日 期 FROM 学 生 WHERE 班 级 编 号 = ) AND 班 级 编 号 <> 使 用 EXISTS 的 子 查 询 EXISTS 或 [NOT]EXISTS 用 来 检 查 在 子 查 询 中 是 否 存 在 符 合 条 件 的 元 组 (1) 带 EXISTS 关 键 字 的 查 询 先 执 行 外 层 查 询, 然 后 执 行 内 层 查 询 外 层 查 询 的 值 决 定 了 内 层 查 询 的 结 果, 内 层 查 询 的 执 行 次 数 由 外 层 查 询 的 结 果 决 定 (2) 由 于 带 有 EXISTS 的 子 查 询 不 返 回 查 询 的 数 据, 只 产 生 逻 辑 值 TRUE 或 FALSE, 因 此 在 子 查 询 的 SELECT 中 指 定 列 名 是 没 有 意 义 的, 通 常 使 用 * 例 3-64 查 询 所 有 选 修 了 课 程 编 号 为 的 课 程 的 学 生 学 号 姓 名 和 班 级 SELECT 学 号, 姓 名, 班 级 编 号 FROM 学 生 WHEREEXISTS (SELECT * FROM 成 绩 WHERE 学 号 = 学 生. 学 号 AND 课 程 编 号 = ) 查 询 过 程 : (1) 先 查 询 外 层 学 生 表 中 第 1 条 记 录 的 学 号 ; (2) 根 据 此 学 号 查 询 内 层 成 绩 表 中 是 否 有 符 合 条 件 的 记 录 ( 学 号 相 同 );

23 第 3 章 结 构 化 查 询 语 言 SQL 57 (3) 如 果 有 符 合 条 件 记 录, 则 WHERE 条 件 为 TRUE, 并 把 第 1 条 记 录 的 学 号 姓 名 和 班 级 编 号 作 为 结 果 集 的 一 行 ; (4) 然 后 查 询 外 层 学 生 表 中 第 2 条 第 3 条 记 录 重 复 上 述 处 理 查 询 过 程, 直 到 学 生 表 中 所 有 记 录 查 询 完 毕 为 止 注 意 : 课 程 编 号 = 在 内 层 查 询 中 具 有 约 束 作 用 例 3-65 查 询 有 没 有 参 加 (1) 学 期 中 课 程 编 号 为 的 课 程 考 试 ( 成 绩 为 NULL) 的 学 生 记 录 SELECT 学 号, 姓 名, 班 级 编 号 FROM 学 生 WHEREEXISTS (SELECT * FROM 成 绩 WHERE 成 绩 ISNULL AND 课 程 编 号 = AND 学 期 = (1) AND 学 号 = 学 生. 学 号 ) 3.5 视 图 视 图 是 一 种 常 用 的 数 据 库 对 象, 常 用 于 集 中 简 化 和 定 制 显 示 数 据 库 中 的 数 据 信 息, 为 用 户 从 多 种 角 度 观 察 数 据 库 中 的 数 据 提 供 方 便 为 了 屏 蔽 数 据 的 复 杂 性, 简 化 用 户 对 数 据 的 操 作, 或 者 控 制 用 户 访 问 数 据, 保 护 数 据 安 全, 常 为 不 同 的 用 户 创 建 不 同 的 视 图 视 图 的 基 本 概 念 视 图 是 一 个 虚 拟 表, 从 一 个 或 多 个 基 本 表 中 导 出, 也 可 以 从 视 图 中 导 出, 其 内 容 由 查 询 语 句 定 义 生 成 视 图 与 基 本 表 一 样, 具 有 结 构 与 数 据, 但 依 赖 于 基 本 表, 不 能 独 立 存 在 数 据 库 中 只 存 储 视 图 的 定 义, 而 不 存 储 视 图 对 应 的 数 据, 这 些 数 据 仍 然 存 储 在 视 图 所 引 用 的 基 本 表 中 基 本 表 中 的 数 据 发 生 变 化 时, 视 图 中 的 数 据 也 自 动 发 生 变 化 视 图 中 的 数 据 实 际 上 是 对 基 本 表 中 数 据 的 筛 选 和 再 组 织, 对 视 图 的 操 作 最 终 要 转 化 为 对 基 本 表 的 操 作 如 图 3-2 所 示, 在 数 据 库 三 层 模 式 结 构 中 可 以 反 映 视 图 与 基 本 表 的 关 系 以 及 与 外 部 模 式 的 对 应 关 系 视 图 一 经 定 义, 即 可 通 过 视 图 查 询 更 新 和 删 除 基 本 表 中 的 数 据, 但 对 视 图 的 更 新 操 作 能 否 成 功, 取 决 于 定 义 在 基 本 表 上 的 数 据 完 整 性 约 束 虽 然 视 图 完 全 依 赖 基 本 表, 对 视 图 的 操 作 最 终 要 转 化 为 对 基 本 表 的 操 作, 但 其 作 用 仍 然 有 很 多 体 现 : (1) 集 中 数 据, 简 化 数 据 查 询 和 处 理 通 过 视 图, 将 用 户 需 要 的 分 散 在 多 个 数 据 表 中 的 数 据 集 中 在 一 起, 方 便 数 据 查 询 和 处 理 (2) 屏 蔽 数 据 库 的 复 杂 性 用 户 不 必 了 解 数 据 库 中 复 杂 的 基 本 表 结 构, 修 改 基 本 表 结 构 一 般 不 影 响 视 图 的 使 用 (3) 为 数 据 提 供 安 全 保 护 对 不 同 权 限 的 用 户 定 义 不 同 的 视 图, 使 其 只 能 看 到 基 本 表

24 58 数 据 库 原 理 与 应 用 图 3-2 数 据 库 三 层 模 式 结 构 中 指 定 的 数 据 记 录 和 数 据 项 (4) 方 便 数 据 交 换 当 需 要 将 多 个 基 本 表 中 的 相 关 数 据 导 出 时, 可 以 将 相 关 数 据 集 中 到 一 个 视 图 内, 然 后 将 视 图 中 的 数 据 导 出, 简 化 了 数 据 交 换 操 作 视 图 的 操 作 视 图 操 作 主 要 包 括 创 建 视 图 修 改 视 图 删 除 视 图 以 及 查 看 和 修 改 视 图 属 性, 在 SQL Server2005 中, 可 以 通 过 SQLServer 管 理 平 台 或 T-SQL 语 句 来 完 成 1. 创 建 视 图 在 SQLServer2005 中, 使 用 创 建 视 图 向 导 SQLServerManagementStudio 或 在 查 询 设 计 窗 口 执 行 CREATE VIEW 语 句 都 可 以 创 建 视 图 下 面 主 要 介 绍 通 过 CREATE VIEW 语 句 创 建 视 图 视 图 是 根 据 对 基 本 表 的 查 询 定 义 生 成 的, 其 命 令 格 式 如 下 : CREATE VIEW < 视 图 名 > [< 列 名 1>,< 列 名 2>,,< 列 名 n>] [WITH ENCRYTION] AS <SELECT 查 询 语 句 > [WITH CHECK OPTION] 说 明 : (1) 列 名 如 果 省 略, 则 以 子 查 询 基 本 表 中 相 应 列 的 字 段 名 作 为 视 图 的 字 段 别 名 ; (2) 如 果 视 图 的 字 段 来 自 表 达 式 或 SQL 函 数, 必 须 指 定 列 名 ; (3) WITH ENCRYTION 子 句 表 示 SQLServer 对 包 含 CREATEVIEW 语 句 的 文 本 进 行 加 密 ; (4) WITH CHECK OPTION 子 句 表 示 对 视 图 执 行 的 INSERT UPDATE 和 DELETE 操 作 都 必 须 遵 守 定 义 视 图 的 <SELECT 查 询 语 句 > 中 的 WHERE 子 句 所 指 定 的 条 件 例 3-66 创 建 一 个 名 为 学 生 _2010 的 视 图, 视 图 中 只 包 括 2010 年 入 学 的 学 生 学 号 姓 名 班 级 和 入 学 时 间 CREATE VIEW 学 生 _2010 AS

25 第 3 章 结 构 化 查 询 语 言 SQL 59 SELECT 学 号, 姓 名, 班 级 编 号, 入 学 时 间 FROM 学 生 WHERE 入 学 时 间 = 2010 通 过 视 图 学 生 _2010 查 询 学 生 数 据 SELECT * FROM 学 生 _2010 通 过 视 图 学 生 _2010 更 新 学 生 数 据, 将 入 学 时 间 修 改 为 2011 UPDATE 学 生 _2010 SET 入 学 时 间 = 2011 例 3-67 创 建 一 个 带 WITH ENCRYTION 和 WITH CHECK OPTION 的 名 为 学 生 _2011 的 视 图, 视 图 中 包 括 2011 年 入 学 的 学 生 学 号 姓 名 性 别 班 级 入 学 成 绩 和 入 学 时 间 CREATE VIEW 学 生 _2011 WITH ENCRYTION AS SELECT 学 号, 姓 名, 性 别, 班 级 编 号, 入 学 成 绩, 入 学 时 间 FROM 学 生 WHERE 入 学 时 间 = 2011 WITH CHECK OPTION 向 视 图 插 入 一 个 入 学 时 间 为 2012 的 记 录 INSERTINTO 学 生 ( 学 号, 姓 名, 性 别, 班 级 编 号, 入 学 成 绩, 入 学 时 间 ) VALUES( , 佳 阳, 男, ,600, 2012 ) 执 行 上 述 语 句, 将 显 示 不 能 插 入 的 错 误 信 息 因 为 视 图 的 约 束 条 件 是 2011 年 入 学 的 学 生, 而 插 入 的 是 2012 年 入 学 的 记 录 通 过 系 统 存 储 过 程 SP_HELPTEXT 查 看 视 图 学 生 _2011 的 文 本 EXECSP_HELPTEXT 学 生 _2011 执 行 上 述 语 句, 消 息 显 示 视 图 文 本 已 加 密 虽 然 视 图 可 以 像 基 本 表 一 样 操 作, 但 对 视 图 进 行 更 新 操 作 需 要 相 应 限 制 : (1) 视 图 的 字 段 来 自 表 达 式, 不 允 许 对 视 图 执 行 INSERT 和 UPDATE 操 作, 允 许 执 行 DELETE 操 作 (2) 视 图 由 多 个 基 本 表 导 出, 不 允 许 对 视 图 进 行 更 新 操 作 (3) 视 图 的 字 段 来 自 聚 合 函 数, 不 允 许 对 视 图 进 行 更 新 操 作 (4) 视 图 定 义 中 含 有 GROUPBY 子 句, 不 允 许 对 视 图 进 行 更 新 操 作 (5) 视 图 定 义 中 含 有 DISTINCT 关 键 字, 不 允 许 对 视 图 进 行 更 新 操 作 例 3-68 创 建 一 个 名 为 专 业 _view 的 视 图, 视 图 中 包 含 专 业 编 号 专 业 名 称 和 学 院 名 称 CREATE VIEW 专 业 _view ( 专 业 编 号, 专 业 名 称, 学 院 ) AS SELECT 专 业 编 号, 专 业 名 称, 学 院 名 称 FROM 专 业 JOIN 二 级 学 院 ON 专 业. 学 院 编 号 = 二 级 学 院. 学 院 编 号 例 3-69 在 成 绩 数 据 表 的 基 础 上, 创 建 一 个 名 为 成 绩 _view 的 视 图, 视 图 中 包

26 60 数 据 库 原 理 与 应 用 含 (1) 学 期 选 修 了 课 程 编 号 为 的 课 程 的 所 有 学 生 的 学 号 课 程 编 号 期 末 成 绩 最 终 成 绩 和 学 期, 其 中 最 终 成 绩 = 成 绩 *0.7 CREATE VIEW 成 绩 _view( 学 号, 课 程 编 号, 期 末 考 试, 最 终 成 绩, 学 期 ) AS SELECT 学 号, 课 程 编 号, 成 绩, 成 绩 *0.7, 学 期 FROM 成 绩 WHERE 课 程 编 号 = AND 学 期 = (1) 在 此 例 中, 最 终 成 绩 字 段 在 成 绩 数 据 表 中 并 不 存 在, 通 过 表 达 式 成 绩 *0.7 得 到, 称 为 虚 列 期 末 成 绩 与 成 绩 数 据 表 中 的 成 绩 对 应, 视 图 中 可 以 重 新 命 名 2. 修 改 视 图 修 改 视 图 可 以 在 SQLServerManagementStudio 视 图 设 计 器 中 完 成, 也 可 以 在 查 询 设 计 窗 口 中 执 行 ALTERVIEW 语 句 完 成 使 用 ALTERVIEW 语 句 修 改 视 图 的 命 令 格 式 如 下 : ALTER VIEW < 视 图 名 > [< 列 名 1>,< 列 名 2>,,< 列 名 n>] [WITH ENCRYTION] AS <SELECT 查 询 语 句 > [WITH CHECK OPTION] 说 明 : 参 数 和 子 句 与 CREATE VIEW 中 相 同 例 3-70 修 改 视 图 学 生 _2010, 将 筛 选 条 件 修 改 为 入 学 时 间 = 2011 ALTER VIEW 学 生 _2010 AS SELECT 学 号, 姓 名, 班 级 编 号, 入 学 时 间 FROM 学 生 WHERE 入 学 时 间 = 删 除 视 图 视 图 可 以 在 SQLServer ManagementStudio 中 删 除, 也 可 以 在 查 询 设 计 窗 口 执 行 DROPVIEW 语 句 删 除 使 用 ALTER VIEW 语 句 修 改 视 图 的 命 令 格 式 如 下 : DROPVIEW < 视 图 名 > [,< 视 图 名 1>,< 视 图 名 2>,,< 视 图 名 n>] 说 明 :DROPVIEW 语 句 可 以 同 时 删 除 多 个 视 图 例 如, 删 除 视 图 学 生 _2010 : DROPVIEW 学 生 _2010 本 章 比 较 详 细 地 介 绍 了 SQL 语 言 的 数 据 定 义 功 能 数 据 操 纵 功 能 和 数 据 查 询 功 能 在 数 据 定 义 功 能 中, 主 要 介 绍 了 数 据 表 结 构 的 定 义 修 改 和 删 除, 其 中 特 别 强 调 了 数 据 完 整 性 约 束 的 定 义 在 数 据 操 纵 功 能 中, 特 别 关 注 了 数 据 完 整 性 在 INSERT UPDATE 和 DELETE 操 作

27 第 3 章 结 构 化 查 询 语 言 SQL 61 中 能 否 成 功 执 行 的 约 束 作 用 执 行 插 入 操 作 时 需 要 分 别 检 查 实 体 完 整 性 规 则 参 照 完 整 性 规 则 和 用 户 定 义 完 整 性 规 则 ; 执 行 删 除 操 作 时 一 般 只 需 要 检 查 参 照 完 整 性 规 则 ; 执 行 更 新 操 作 可 以 看 做 是 先 删 除 旧 的 元 组, 然 后 再 插 入 新 的 元 组, 所 以 执 行 更 新 操 作 时 的 完 整 性 检 查 综 合 了 上 述 两 种 情 况 在 数 据 查 询 功 能 中, 全 面 介 绍 了 SQL SELECT 查 询 语 句 的 使 用 方 法 SQL SELECT 查 询 分 为 单 表 简 单 查 询 多 表 连 接 查 询 和 嵌 套 查 询 等 SQLServer 还 支 持 集 合 的 并 交 和 差 等 运 算 本 章 最 后 介 绍 了 视 图 的 概 念 作 用 和 使 用 视 图 是 从 基 本 表 导 出 的 虚 表, 其 内 容 由 查 询 语 句 定 义 生 成, 本 身 不 包 含 数 据, 依 赖 于 基 本 表, 数 据 仍 然 存 储 在 基 本 表 中 视 图 操 作 与 基 本 表 一 样, 但 对 视 图 的 操 作 最 终 转 化 为 对 基 本 表 的 操 作, 因 此 受 基 本 表 上 数 据 完 整 性 的 约 束 SQL 结 构 化 查 询 语 言 不 仅 是 学 习 和 掌 握 关 系 数 据 库 的 基 础, 更 是 开 发 数 据 库 应 用 系 统 的 基 础 1.SQL 的 主 要 功 能 是 什 么? 2. 空 值 NULL 的 作 用 是 什 么? 3. 如 何 定 义 数 据 表 的 实 体 完 整 性 约 束? 4. 什 么 是 参 照 表? 什 么 是 被 参 照 表? 如 何 定 义 参 照 表 的 参 照 完 整 性 约 束? 5. 在 数 据 表 中 定 义 约 束 时,PRIMARY KEY 与 UNIQUE 有 什 么 区 别? 6. 修 改 数 据 表 结 构 时, 可 以 同 时 修 改 多 个 字 段 属 性 吗? 7. 用 DROPTABLE 命 令 删 除 数 据 表 时 应 考 虑 哪 些 约 束? 8. 约 束 一 般 定 义 在 列 上, 为 什 么 还 需 要 表 级 约 束? 9. 数 据 表 定 义 中 ON DELETESET NULL 和 ON UPDATECASCADE 的 作 用 是 什 么? 10. 进 行 INSERT DELETE 和 UPDATE 操 作 时 分 别 需 要 检 查 哪 些 完 整 性 约 束? 11. 插 入 字 符 型 数 据 的 数 字 时, 带 引 号 与 不 带 引 号 有 什 么 区 别? 12. 嵌 套 查 询 是 否 可 以 写 出 等 价 的 连 接 查 询? 13. 在 ORDERBY 子 句 中 所 引 用 列 的 数 量 能 否 多 于 SELECT 语 句 中 列 的 数 量? 14.WHERE 条 件 表 达 式 中 使 用 BETWEEN AND 与 使 用 IN 有 什 么 区 别? 15. 在 SELECT 中 使 用 列 的 别 名 有 几 种 方 法? 16. 执 行 SELECT 学 号, 成 绩 FROM 成 绩, 查 询 结 果 中 的 学 号 和 成 绩 如 何 保 存 到 变 量 中? 17. 如 何 将 查 询 结 果 中 的 成 绩 转 换 为 优 秀 良 好 中 等 及 格 和 不 及 格 等 级? 18.DELETE 操 作 与 DROP 操 作 有 何 区 别? 19.HAVING 子 句 与 WHERE 字 句 有 何 区 别?

28 62 数 据 库 原 理 与 应 用 20.SELECT 子 句 中 DISTINCT 的 作 用 是 什 么? 21. 简 述 视 图 与 基 本 表 的 区 别 和 联 系 22. 哪 类 视 图 可 以 更 新, 哪 类 视 图 不 可 以 更 新? 23. 基 本 表 被 删 除, 由 其 导 出 的 视 图 如 何 变 化? 24. 上 机 调 试 分 析 和 执 行 本 章 例 题 25. 某 企 业 订 货 数 据 库 包 含 的 数 据 表 如 下 : 仓 库 ( 仓 库 号, 城 市, 面 积 ) 职 工 ( 仓 库 号, 职 工 号, 工 资 ) 订 购 单 ( 职 工 号, 供 应 商 号, 订 购 单 号, 订 购 日 期 ) 供 应 商 ( 供 应 商 号, 供 应 商 名, 地 址 ) 用 SQL 完 成 以 下 操 作 : (1) 检 索 出 职 工 张 军 发 给 供 应 商 李 波 的 订 购 单 信 息 (2) 检 索 出 向 供 应 商 白 雪 发 过 订 购 单 的 职 工 的 职 工 号 和 仓 库 号 (3) 检 索 出 目 前 与 白 雪 供 应 商 没 有 联 系 的 职 工 信 息 (4) 检 索 出 目 前 没 有 任 何 订 购 单 的 供 应 商 信 息 (5) 检 索 出 和 职 工 张 军 刘 凯 都 有 联 系 的 北 京 的 供 应 商 信 息 (6) 检 索 出 向 白 雪 供 应 商 发 出 订 购 单 的 仓 库 所 在 的 城 市 (7) 检 索 出 有 最 大 面 积 的 仓 库 信 息 (8) 检 索 出 每 个 城 市 的 供 应 商 个 数 (9) 检 索 出 每 个 仓 库 中 工 资 多 于 1220 元 的 职 工 个 数 (10) 检 索 出 工 资 低 于 本 仓 库 平 均 工 资 的 职 工 信 息 (11) 删 除 目 前 没 有 任 何 订 购 单 的 供 应 商 (12) 给 低 于 所 有 职 工 平 均 工 资 的 职 工 提 高 5% 的 工 资

29 第 4 章 数 据 库 编 程 主 要 内 容 数 据 库 编 程 不 仅 可 以 使 用 开 发 工 具, 而 且 可 以 借 助 数 据 库 管 理 系 统 本 身 提 供 的 编 程 功 能 本 章 以 SQLServer2005 为 背 景, 主 要 介 绍 Transact-SQL 游 标 存 储 过 程 和 触 发 器 学 习 目 标 熟 练 掌 握 T-SQL 编 程 语 句 和 流 程 控 制 方 法 ; 理 解 游 标 的 概 念, 熟 练 掌 握 游 标 的 定 义 和 使 用 方 法 ; 理 解 存 储 过 程 的 概 念, 熟 练 掌 握 存 储 过 程 的 创 建 和 使 用 方 法 ; 理 解 触 发 器 的 概 念, 熟 练 掌 握 触 发 器 的 创 建 和 使 用 方 法 重 点 与 难 点 重 点 : 游 标 的 定 义 和 应 用 ; 存 储 过 程 和 触 发 器 的 创 建 和 应 用 难 点 : 灵 活 应 用 游 标 存 储 过 程 和 触 发 器 实 现 数 据 库 应 用 4.1 T-SQL SQL-Server 使 用 的 语 言 称 作 Transact-SQL, 简 称 T-SQL, 即 在 标 准 的 SQL 语 言 的 基 础 上, 加 上 流 程 控 制 语 句, 实 现 比 较 复 杂 的 数 据 库 编 程 数 据 与 表 达 式 程 序 设 计 涉 及 不 同 数 据 类 型 的 表 达 式,SQLServer 也 提 供 了 用 于 编 写 结 构 化 程 序 的 数 据 类 型 变 量 和 表 达 式 等 语 法 1. 数 据 类 型 数 据 存 储 在 数 据 库 的 数 据 表 中, 是 程 序 处 理 的 对 象 数 据 类 型 体 现 了 数 据 结 构 的 特 点 数 据 表 中 字 段 的 数 据 类 型 决 定 了 数 据 的 取 值 范 围 和 存 储 格 式 数 据 类 型 包 括 SQLServer 提 供 的 系 统 数 据 类 型 和 用 户 定 义 数 据 类 型 SQLServer 提

30 64 数 据 库 原 理 与 应 用 供 了 丰 富 的 数 据 类 型, 参 见 第 3 章 表 3-2 用 户 定 义 数 据 类 型 参 阅 SQLServer 相 关 手 册 2. 变 量 变 量 用 于 编 程 中 存 储 数 据 Transact-SQL 语 言 中 有 两 类 变 量, 即 用 户 定 义 的 局 部 变 量 和 系 统 提 供 的 全 局 变 量 两 类 变 量 的 使 用 方 法 有 较 大 区 别 (1) 局 部 变 量 是 在 批 处 理 ( 存 储 过 程 或 触 发 器 ) 内 由 用 户 定 义 并 使 用 的 变 量, 开 头, 用 DECLARE 语 句 声 明 (2) 全 局 变 量 在 SQLServer 中 作 为 一 种 特 殊 函 数 由 系 统 预 先 定 义 全 局 变 开 头, 作 用 于 整 个 系 统, 主 要 用 来 检 测 系 统 的 设 置 值 或 执 行 查 询 命 令 后 的 状 态 值 表 4-1 列 出 了 SQLServer 中 常 用 的 全 局 变 量 表 4-1 SQLServer 中 常 用 的 全 局 变 量 变 量 前 一 条 命 令 处 理 的 行 数 前 一 条 SQL 语 句 报 告 的 错 误 号 事 务 嵌 套 的 级 别 事 务 的 当 前 状 当 前 事 务 的 模 式 ( 链 接 或 非 链 本 地 SQLServer 的 名 称 SQLServer 的 版 本 级 别 当 前 进 程 ID 上 次 INSERT 操 作 中 使 用 的 identity 存 储 过 程 / 触 发 器 中 的 嵌 套 层 游 标 中 上 条 FETCH 语 句 的 状 态 3. 局 部 变 量 的 使 用 (1) 局 部 变 量 的 定 义 在 批 处 理 过 程 中, 用 DECLARE 语 句 声 明 局 部 变 量, 所 有 局 部 变 量 在 声 明 后 均 初 始 化 为 NULL 语 法 格 式 如 下 : 局 部 变 量 1 数 据 类 型,@ 局 部 变 量 2 数 据 类 型,,@ 局 部 变 量 n 数 据 类 型 (2) 局 部 变 量 赋 值 SET 或 SELECT 语 句 实 现 对 局 部 变 量 赋 值 语 法 格 式 如 下 : 局 部 变 量 = 表 达 式 或 局 部 变 量 1= 表 达 式 1,@ 局 部 变 量 2= 表 达 式 2,,@ 局 部 变 量 n= 表 达 式 n 说 明 : 局 部 变 量 是 除 cursor text ntext image 和 table 外 的 任 何 类 型 变 量 ; 2 表 达 式 是 任 意 有 效 的 SQLServer 表 达 式 ; 3SET 只 能 对 一 个 变 量 赋 值 ; 4SELECT 赋 值 语 句 通 过 FROM 子 句 可 以 从 一 个 表 中 检 索 出 数 据 并 赋 值 给 局 部 变 量, 如 果 查 询 返 回 多 个 值, 则 只 将 最 后 一 个 值 赋 给 变 量 例 4-1 定 义 局 部 变 量, 并 对 其 赋 值 输 出

31 第 4 章 数 据 库 编 程 65 例 4-2 定 义 局 部 变 量, 并 对 其 赋 值, 查 询 输 出 变 量 的 值 SQLServer 数 据 库 例 4-3 定 义 局 部 变 并 赋 值, 查 询 XSCJGL 数 据 库 的 学 生 数 据 表 中 班 级 编 号 值 相 同 的 所 有 学 生 信 息 USEXSCJGL SELECT * FROM 学 生 WHERE 班 级 编 号 =@CLASS 例 4-4 定 义 局 部 变 查 询 XSCJGL 数 据 库 的 学 生 数 据 表 中 班 级 编 号 为 的 所 有 学 生 姓 名, 并 将 结 果 赋 给 变 量 输 出 USEXSCJGL 姓 名 FROM 学 生 WHERE 班 级 编 号 = ) 子 查 询 返 回 的 值 多 于 一 个 如 果 子 查 询 跟 随 在 =!= < <= > 和 >= 之 后, 或 子 查 询 用 作 表 达 式, 用 SET 赋 值 是 不 允 许 的 正 确 的 语 句 是 : USEXSCJGL 姓 名 FROM 学 生 WHERE 班 级 编 号 = 除 用 SET 或 SELECT 语 句 实 现 对 局 部 变 量 赋 值 外, 还 可 以 利 用 UPDATE 语 句 实 现 例 4-5 定 义 局 部 变 将 XSCJGL 的 数 据 库 中 选 课 数 据 表 中 学 号 为 且 课 程 编 号 为 的 学 生 成 绩 提 高 5 分, 并 将 结 果 赋 给 变 量 输 出 USEXSCJGL UPDATE 选 课 成 绩 +5 WHERE 学 号 = AND 课 程 编 号 = 运 算 符 与 表 达 式 表 达 式 是 运 算 对 象 ( 常 量 变 量 字 段 和 函 数 等 ) 与 运 算 符 的 组 合 SQLServer 提 供

32 66 数 据 库 原 理 与 应 用 的 运 算 符 主 要 有 : (1) 算 术 运 算 符 :+ - * / 和 % (2) 位 运 算 符 :& ^ 和 ~ (3) 比 较 运 算 符 :> < >= <=! = 和 = (4) 逻 辑 运 算 符 :AND OR NOT LIKE IN ALL ANY BETWEEN 和 EXISTS (5) 连 结 运 算 符 :+ 运 算 符 使 用 的 详 细 介 绍 请 参 阅 SQLServer 相 关 手 册 函 数 在 程 序 设 计 过 程 中, 经 常 调 用 系 统 提 供 的 函 数 T-SQL 提 供 了 4 种 系 统 内 置 函 数, 分 别 是 行 集 函 数 聚 合 函 数 排 名 函 数 和 标 量 函 数, 这 些 函 数 是 T-SQL 命 令 的 扩 充 SQL Server 各 类 函 数 如 表 4-2 所 示 表 4-2 SQLServer 中 常 用 函 数 函 数 类 型 函 数 说 明 聚 合 函 数 Count Sum Avg Max Min 将 多 个 值 合 并 为 一 个 值 字 符 串 函 数 Len Substring Str Rtrim Upper 对 字 符 串 进 行 操 作 或 计 算 数 学 函 数 Abs Rand Sqrt Mod 执 行 数 学 运 算 元 数 据 函 数 Db_id Db_name 返 回 数 据 库 和 数 据 库 对 象 的 属 性 信 息 配 置 函 可 返 回 有 关 配 置 设 置 信 息, 实 际 是 一 组 为 前 缀 的 全 局 变 量 行 集 函 数 Opendatasource Containstable 返 回 一 个 结 果 集 ( 表 或 视 图 ) 游 标 返 回 有 关 游 标 状 态 信 息 日 期 时 间 函 数 Getdate Year Datedif Datename 对 日 期 和 时 间 执 行 操 作 类 型 转 换 函 数 Case Cast Convert Isnumbric 数 据 类 型 转 换 下 面 举 例 说 明 常 用 函 数 的 用 法, -- 表 示 注 释 (1) 字 符 串 函 数 NCHAR(20) SQLServer 数 据 库 SELECTLEN(@MYSTR) -- 字 符 串 长 度 函 数 LEN(), 显 示 长 度 为 14 SELECTSUBSTRING(@MYSTR,1,10) -- 取 子 串 函 数 SUBSTRING(), 显 示 SQLServer (2) 日 期 函 数 SELECT GETDATE() -- 取 系 统 日 期 函 数 GETDATE(), 显 示 SELECT YEAR(GETDATE()) -- 取 日 期 中 的 年 份 函 数 YEAR(), 显 示 2012 SELECTDATEDIF(YY, ,GETDATE()) -- 求 两 日 期 之 差 函 数 DATEDIF(), 显 示 22 (3) 数 据 类 型 转 换 函 数 -- 显 示 小 萌 的 年 龄 :22 SELECT 小 萌 的 年 龄 : +CAST(DATEDIF(YY, ,GETDATE())ASCHAR(2)) -- CONVERT() 函 数 应 用

33 第 4 章 数 据 库 编 程 67 02/01/201222:30:45PM SELECTCONVERT(VARCHAR(30),@DT,108) -- 显 示 日 期 中 的 时 间 22:30:45 SELECTCONVERT(VARCHAR(30),@DT,111) -- 显 示 日 期 中 的 年 月 日 2012/02/01 SELECTCONVERT(VARCHAR(30),@DT,120) -- 显 示 日 期 中 的 年 月 日 与 时 间 :30:45 函 数 使 用 的 详 细 介 绍 请 参 阅 SQLServer 相 关 函 数 手 册 流 程 控 制 语 句 T-SQL 程 序 语 句 的 集 合 构 成 T-SQL 程 序, 实 现 特 定 的 功 能 流 程 控 制 语 句 用 来 控 制 程 序 执 行 和 流 程 分 支 的 命 令, 主 要 包 括 顺 序 结 构 选 择 结 构 和 循 环 结 构 在 SQLServer 中 流 程 控 制 语 句 主 要 有 IF ELSE CASE GOTO WHILE CONTINUE BREAK RETURN 和 WAITFOR 等 1.BEGIN END 语 句 块 BEGIN END 语 句 相 当 于 编 程 语 言 中 的 {} 或 复 合 语 句, 把 要 执 行 的 多 条 语 句 写 在 语 句 块 中, 特 别 应 用 于 选 择 结 构 和 循 环 结 构 中, 使 其 中 的 语 句 作 为 一 个 整 体 来 执 行 BE- GIN END 语 句 可 以 嵌 套 使 用 例 4-6 显 示 二 级 学 院 数 据 表 和 专 业 数 据 表 中 的 所 有 记 录 信 息 USEXSCJGL GO BEGIN SELECT * FROM 二 级 学 院 SELECT * FROM 专 业 END 2. 选 择 控 制 SQLServer 提 供 了 多 个 流 程 控 制 语 句 IF ELSE 是 最 常 用 的 流 程 控 制 语 句 ; CASE 可 以 进 行 多 分 支 流 程 控 制 ;GOTO 语 句 无 条 件 地 改 变 流 程 ;RETURN 语 句 中 断 正 在 执 行 的 批 处 理 和 存 储 过 程 等 (1)IF ELSE 语 句 例 4-7 比 较 输 出 两 个 数 中 的 较 大 数 @Z=@X 例 4-8 统 计 学 生 数 据 表 中 班 级 学 生 的 平 均 年 龄 如 果 平 均 年 龄 小 于 20, 则 输 出 学 生 年 龄 组 合 正 常, 否 则 输 出 年 龄 大 于 20 的 学 生 信 息 USEXSCJGL GO

34 68 数 据 库 原 理 与 应 用 IF(SELECT AVG(YEAR(DATE())-YEAR( 出 生 日 期 ))FROM 学 生 WHERE 班 级 编 号 = )<20 PRINT 年 龄 组 合 正 常! ELSE GO SELECT * FROM 学 生 WHERE 班 级 编 号 = AND (YEAR(DATE())-YEAR( 出 生 日 期 ))>20 例 4-9 在 学 生 数 据 表 中 查 询 是 否 存 在 学 号 为 的 学 生, 存 在 则 显 示 存 在 信 息, 否 则 插 入 该 学 生 的 记 录 USEXSCJGL GO IFEXISTS(SELECT * FROM 学 生 WHERE 学 号 = ) PRINT 此 学 生 已 存 在! ELSE GO BEGIN END PRINT 此 学 生 不 存 在! INSERTINTO 学 生 ( 学 号, 姓 名 )VALUES( , 欧 阳 博 学 ) (2)CASE 语 句 CASE 结 构 主 要 应 用 于 多 分 支 选 择 判 断, 分 为 简 单 CASE 函 数 和 搜 索 CASE 函 数 两 种 类 型, 格 式 如 下 : CASE 测 试 表 达 式 END 或 WHEN 测 试 值 1THEN 结 果 表 达 式 1 WHEN 测 试 值 2THEN 结 果 表 达 式 2 CASE END ELSE 结 果 表 达 式 n WHEN 布 尔 表 达 式 1THEN 结 果 表 达 式 1 WHEN 布 尔 表 达 式 2THEN 结 果 表 达 式 2 说 明 : ELSE 结 果 表 达 式 n 1 简 单 CASE 函 数 : 测 试 表 达 式 与 测 试 值 是 否 相 等, 相 等 则 返 回 相 应 的 结 果 表 达 式 ; 2 搜 索 CASE 函 数 : 布 尔 表 达 式 的 值 是 TRUE 或 FALSE, 返 回 相 应 的 结 果 表 达 式 例 4-10 输 出 XSCJGL 数 据 库 的 选 课 数 据 表 中 (1) 学 期 课 程 编 号 为 的 记 录 的 学 号 课 程 编 号 和 成 绩 等 级 成 绩 >=90, 成 绩 等 级 为 优 秀 ; 成 绩 >=80, 成 绩 等 级 为 良 好 ; 成 绩 >=70, 成 绩 等 级 为 中 等 ; 成 绩 >=60, 成 绩

35 第 4 章 数 据 库 编 程 69 等 级 为 及 格 ; 否 则 成 绩 等 级 为 不 及 格 USEXSCJGL GO SELECT 学 号, 课 程 编 号, 成 绩 等 级 = CASE WHEN 成 绩 >=90THEN 优 秀 WHEN 成 绩 >=80THEN 良 好 WHEN 成 绩 >=70THEN 中 等 WHEN 成 绩 >=60THEN 及 格 ELSE 不 及 格 END AS 成 绩 等 级 FROM 选 课 WHERE 学 期 = (1) AND 课 程 编 号 = 例 4-11 输 出 XSCJGL 数 据 库 的 选 课 数 据 表 中 (1) 学 期 课 程 编 号 为 的 记 录 的 学 号 课 程 编 号 和 成 绩 范 围 成 绩 范 围 的 确 定 规 则 为 : 成 绩 为 优 秀, 成 绩 为 >=90; 成 绩 为 良 好, 成 绩 >=80; 成 绩 为 中 等, 成 绩 >=70; 成 绩 为 及 格, 成 绩 >=60; 否 则 成 绩 <60 USEXSCJGL GO SELECT 学 号, 课 程 编 号, 成 绩 范 围 = CASE 成 绩 WHEN 优 秀 THEN 成 绩 >=90 WHEN 良 好 THEN 成 绩 >=80 WHEN 中 等 THEN 成 绩 >=70 WHEN 及 格 THEN 成 绩 >=60 ELSE 成 绩 <60 END FROM 选 课 WHERE 学 期 = (1) AND 课 程 编 号 = 例 4-12 假 定 XSCJGL 数 据 库 中 的 选 课 数 据 表 增 加 了 等 级 字 段, 更 新 表 中 的 数 据 成 绩 >=80, 等 级 为 优 秀 ;80> 成 绩 >=60, 等 级 为 合 格 ; 否 则 等 级 为 不 合 格 USEXSCJGL GO UPDATE 选 课 SET 等 级 = CASE WHEN 成 绩 >=80THEN 优 秀 WHEN 成 绩 >=60AND 成 绩 <80THEN 合 格 ELSE 不 合 格 END (3)WAITFOR 语 句 WAITFOR 语 句 可 以 使 程 序 暂 停 一 段 时 间 之 后 继 续 执 行, 也 可 以 暂 停 执 行 程 序 到 所

36 70 数 据 库 原 理 与 应 用 规 定 的 时 间 后 再 继 续 执 行 WAITFOR 语 句 的 格 式 如 下 : WAITFORDELAY 时 间 例 4-13 在 查 询 学 生 表 之 前 用 WAITFOR TIME 时 间 暂 停 1 分 钟 WAITFORDELAY 00:01:00 SELECT * FROM 学 生 例 4-14 在 晚 上 10:00 执 行 数 据 表 的 查 询 WAITFORTIME 22:00 SELECT * FROM 学 生 3. 循 环 控 制 WHILE 循 环 控 制 结 构 与 编 程 语 言 中 的 循 环 结 构 相 似, 根 据 条 件 重 复 执 行 循 环 体 中 的 T-SQL 语 句 WHILE 循 环 控 制 结 构 的 格 式 如 下 : WHILE 条 件 表 达 式 BEGIN 语 句 序 列 1 [BREAK ] 语 句 序 列 2 [CONTINUE ] 语 句 序 列 3 END 说 明 : (1)BREAK 语 句 和 CONTINUE 语 句 一 般 是 有 条 件 执 行 使 用 BREAK 语 句 的 作 用 是 跳 出 本 层 循 环 结 构,CONTINUE 语 句 的 作 用 是 结 束 本 次 循 环, 返 回 循 环 条 件 判 断 处 例 4-15 计 算 1 到 100 之 间 的 自 然 数 累 加 和, 累 加 和 大 于 等 于 1000 停 止 BEGIN BREAK END 例 4-16 如 果 选 课 数 据 表 中 成 绩 低 于 60 分 的 学 生 多 于 20 人, 则 将 低 于 60 分 的 成 绩 加 5 分, 加 分 后 若 成 绩 低 于 60 分 的 学 生 仍 多 于 20 人, 则 继 续 加 5 分 直 到 少 于 等 于 20 人 USEXSCJGL GO

37 第 4 章 数 据 库 编 程 71 WHILE (SELECTCOUNT(*)FROM 选 课 WHERE 成 绩 <60)>20 BEGIN UPDATE 选 课 SET 成 绩 = 成 绩 +5 WHERE 成 绩 <60 IF (SELECTCOUNT(*)FROM 选 课 WHERE 成 绩 <60)<=20 BREAK ELSE CONTINUE END SELECT * FROM 选 课 WHERE 成 绩 <60 PRINT 以 上 学 生 为 不 及 格! 4.2 游 标 SQLSELECT 的 查 询 结 果 是 满 足 条 件 的 记 录 集 合, 称 为 结 果 集, 如 果 需 要 交 互 地 处 理 这 些 记 录, 或 者 需 要 根 据 这 些 记 录 完 成 对 其 他 信 息 的 处 理, 则 需 要 把 查 询 结 果 暂 时 存 储 并 能 够 读 取 和 使 用 但 应 用 程 序 并 不 能 将 整 个 结 果 集 作 为 一 个 单 元 进 行 有 效 处 理, 因 此 需 要 一 种 机 制 以 便 每 次 处 理 一 条 记 录 游 标 (Cursor) 就 是 提 供 这 种 机 制 的 有 效 手 段 游 标 及 其 操 作 语 句 游 标 是 SQLSELECT 查 询 结 果 的 临 时 存 储 ( 临 时 表 ) 游 标 的 使 用 主 要 包 括 定 义 游 标 打 开 游 标 从 游 标 中 读 记 录 循 环 遍 历 游 标 的 所 有 记 录 关 闭 游 标 和 释 放 游 标 1. 定 义 游 标 定 义 游 标 的 语 句 是 DECLARECURSOR, 其 语 法 格 式 如 下 : DECLARE < 游 标 名 > [INSENSITIVE][SCROLL]CURSOR FOR <SELECT 查 询 > [FOR {READ ONLY UPDATE [OF < 列 名 > [,< 列 名 > ]]}] 说 明 : (1)INSENSITIVE 定 义 的 游 标 使 用 查 询 结 果 集 的 临 时 副 本, 保 存 在 tempdb 的 临 时 表 中, 即 定 义 静 态 游 标 如 果 省 略 INSENSITIVE, 则 定 义 动 态 游 标, 对 基 本 表 的 修 改 都 反 映 在 后 面 的 提 取 中 (2)SCROLL 指 定 游 标 使 用 的 提 取 选 项, 默 认 为 NEXT 选 项 包 括 : 1FIRST: 提 取 结 果 集 的 第 1 行 ; 2NEXT: 提 取 结 果 集 当 前 行 的 下 一 行 ; 3PRIOR: 提 取 结 果 集 当 前 行 的 前 一 行 ; 4LAST: 提 取 结 果 集 的 最 后 一 行 ; 5RELATIVEn: 返 回 结 果 集 当 前 行 后 的 第 n 行 如 果 n 为 负 数, 则 返 回 当 前 行 开 始 倒 数 第 n 行 ; 6ABSOLUTEn: 返 回 结 果 集 的 第 n 行 如 果 n 为 负 数, 则 返 回 结 果 集 的 倒 数 第 n 行

38 72 数 据 库 原 理 与 应 用 (3)READ ONLY 定 义 的 游 标 为 只 读 游 标, 禁 止 UPDATE 和 DELETE 语 句 通 过 游 标 修 改 基 本 表 中 的 数 据 (4)UPDATE 指 定 游 标 可 修 改 的 列, 若 未 指 定 则 允 许 修 改 所 有 列 2. 打 开 游 标 打 开 游 标 的 语 句 是 OPEN, 其 语 法 格 式 如 下 : OPEN < 游 标 名 > 打 开 游 标 意 味 着 执 行 游 标 定 义 中 的 SELECT 查 询, 并 使 游 标 指 针 指 向 结 果 集 的 第 1 条 记 录 3. 从 游 标 中 读 记 录 从 游 标 中 读 记 录 的 语 句 是 FETCH, 其 语 法 格 式 如 下 : FETCH [[NEXT PRIOR FIRST LAST ABSOLUTEn RELATIVEn]FROM ]< 游 标 名 > [INTO <@ 变 量 1>,<@ 变 量 2>, ] 说 明 : (1)NEXT 是 默 认 选 项, 返 回 结 果 集 的 下 一 行 如 果 是 在 OPEN 后 第 1 次 执 行 FETCH, 则 返 回 第 1 行 ; (2)PRIOR 返 回 结 果 集 的 前 一 行 ; (3)FIRST 返 回 结 果 集 的 第 1 行 ; (4)LAST 返 回 结 果 集 的 最 后 一 行 ; (5)ABSOLUTEn 返 回 结 果 集 的 第 n 行 如 果 n 为 负 数, 则 返 回 结 果 集 的 倒 数 第 n 行 ; (6)RELATIVEn 返 回 结 果 集 当 前 行 后 的 第 n 行 如 果 n 为 负 数, 则 返 回 当 前 行 开 始 倒 数 第 n 行 ; (7)INTO 后 的 变 量 要 与 游 标 定 义 中 SELECT 查 询 字 段 相 对 应, 将 游 标 当 前 记 录 送 入 变 量 中 循 环 遍 历 游 标 的 所 有 记 录 时, 需 要 全 局 和 循 环 语 句 配 合 使 是 一 个 系 统 预 定 义 的 全 局 变 量, 也 可 以 看 作 是 一 个 系 统 函 数, 其 取 值 和 对 应 的 含 义 如 下 : 10:FETCH 语 句 成 功 ; 2-1:FETCH 语 句 失 败 或 行 不 在 结 果 集 中 ; 3-2: 提 取 的 行 不 存 在 4. 关 闭 游 标 关 闭 游 标 的 语 句 是 CLOSE, 其 语 法 格 式 如 下 : CLOSE < 游 标 名 > 游 标 使 用 完 后, 要 及 时 关 闭 以 释 放 数 据 结 果 集 关 闭 游 标 并 不 释 放 游 标 占 用 的 数 据 结 构 5. 释 放 游 标 释 放 游 标 的 语 句 是 DEALLOCATE, 其 语 法 格 式 如 下 : DEALLOCATE < 游 标 名 >