前 言 学 习 计 算 机 的 我 们 无 时 无 刻 不 在 和 数 据 打 交 道, 怎 么 有 效 的 管 理 这 些 数 据 变 成 我 们 必 须 要 谈 论 的 话 题 这 个 问 题 也 正 是 本 书 需 要 探 讨 的 问 题 : 数 据 库 技 术, 目 前 的 数 据 技 术

Transcription

1 2010 开 发 者 智 慧 之 mysql 数 据 库 作 者 :TelesLi 思 想 有 多 远 就 可 以 走 多 远 2010

2 前 言 学 习 计 算 机 的 我 们 无 时 无 刻 不 在 和 数 据 打 交 道, 怎 么 有 效 的 管 理 这 些 数 据 变 成 我 们 必 须 要 谈 论 的 话 题 这 个 问 题 也 正 是 本 书 需 要 探 讨 的 问 题 : 数 据 库 技 术, 目 前 的 数 据 技 术 已 经 日 渐 成 熟, 作 为 一 个 程 序 开 发 人 员, 最 重 要 的 是 如 何 使 用 数 据 库, 在 此 基 础 上 开 发 出 更 好 的 软 件 目 前 较 为 流 行 的 数 据 库 有 以 下 几 种 : DB2 Oracle Informix Sybase SQL Server PostgreSQL mysql, 在 这 款 数 据 库 中,mysql 是 免 费 开 放 源 代 码 的, 所 以 他 成 为 了 很 多 中 小 企 业 的 首 选, 选 择 mysql 不 光 光 是 因 为 他 的 免 费, 更 重 要 的 是 性 能 安 全 等 重 要 因 素 决 定 的 MySQL 是 一 个 小 型 关 系 型 数 据 库 管 理 系 统, 开 发 者 为 瑞 典 MySQL AB 公 司 在 2008 年 1 月 16 号 被 Sun 公 司 收 购 而 2009 年,SUN 又 被 Oracle 收 购. 对 于 Mysql 的 前 途, 没 有 任 何 人 抱 乐 观 的 态 度. 目 前 MySQL 被 广 泛 地 应 用 在 Internet 上 的 中 小 型 网 站 中 由 于 其 体 积 小 速 度 快 总 体 拥 有 成 本 低, 尤 其 是 开 放 源 码 这 一 特 点, 许 多 中 小 型 网 站 为 了 降 低 网 站 总 体 拥 有 成 本 而 选 择 了 MySQL 作 为 网 站 数 据 库 这 书 将 从 围 绕 mysql 来 讲 解 数 据 库 知 识, 也 基 于 mysql 讲 解 SQL 语 句, 这 样 在 熟 悉 mysql 的 同 时 我 们 可 以 熟 练 掌 握 数 据 库 的 而 基 本 知 识 以 及 SQL 知 识, 为 我 们 的 开 发 打 下 基 础 本 人 见 识 有 限, 难 免 会 有 纰 漏, 在 此 希 望 大 家 能 和 我 一 起 交 流 学 习, 共 同 成 长 当 然 更 加 希 望 大 家 能 有 任 何 软 件 开 发 方 面 的 问 题 和 我 一 起 探 讨 作 者 :TelesLi e_mail:telesli1013@gmail.com QQ: 个 人 技 术 主 页 : 2

3 前 言... 2 第 一 章 数 据 库 概 述 数 据 (Data) 的 定 义 数 据 库 数 据 库 定 义 数 据 库 发 展 数 据 库 管 理 系 统 DBMS 的 定 义 DBMS 的 组 成 DBMS 的 功 能 时 下 比 较 流 行 的 DBMS 数 据 库 系 统 数 据 库 系 统 的 定 义 构 成 和 性 能 MySQL 数 据 库 MySQL 简 介 MySQL 的 基 本 操 作...10 第 二 章 数 据 库 基 础 知 识 数 据 库 基 础 知 识 概 述 SQL 概 述 SQL 的 语 法 构 成 MySQL 中 使 用 SQL 语 言 几 点 说 明 MySQL 的 数 据 类 型 创 建 数 据 库 创 建 表 表 结 构 操 作 第 三 章 MySQL 运 算 符 以 及 SQl 语 句 MySQL 运 算 符 算 术 运 算 符 比 较 运 算 符 逻 辑 运 算 符 SQL 语 句 SQL 指 令 表 处 理 进 阶 SQL 第 一 章 数 据 库 概 述 1.1 数 据 (Data Data) 的 定 义 对 客 观 事 物 的 符 号 表 示, 如 图 形 符 号 数 字 字 母 等, 数 据 是 数 据 库 中 存 储 的 基 本 对 象 3

4 在 日 常 生 活 中, 人 们 直 接 用 语 言 来 描 述 事 物 ; 在 计 算 机 中, 为 了 存 储 和 处 理 这 些 事 物, 就 要 将 事 物 的 特 征 抽 象 出 来 组 成 一 个 记 录 来 描 述 数 据 的 种 类 文 字 图 形 图 象 声 音 数 据 的 特 点 数 据 与 其 语 义 是 不 可 分 的 数 据 举 例 : 学 生 档 案 中 的 学 生 记 录 (TelesLi, 男,1987, 安 徽, 计 算 机 系,2005) 数 据 的 形 式 不 能 完 全 表 达 其 内 容 数 据 的 解 释 语 义 : 学 生 姓 名 性 别 出 生 年 月 籍 贯 所 在 系 别 入 学 时 间 解 释 :TelesLi 是 个 大 学 生,1987 年 出 生, 安 徽 人,2005 年 考 入 计 算 机 系 1.2 数 据 库 数 据 库 定 义 数 据 库 (Database, 简 称 DB) 的 定 义 : 按 照 数 据 结 构 来 组 织 存 储 和 管 理 数 据 的 仓 库 J.Martin 给 数 据 库 下 了 一 个 比 较 完 整 的 定 义 : 数 据 库 是 存 储 在 一 起 的 相 关 数 据 的 集 合, 这 些 数 据 是 结 构 化 的, 无 有 害 的 或 不 必 要 的 冗 余, 并 为 多 种 应 用 提 供 服 务 数 据 库 发 展 数 据 库 发 展 阶 段 大 致 划 分 为 如 下 几 个 阶 段 : 人 工 管 理 阶 段 文 件 系 统 阶 段 数 据 库 系 统 阶 段 高 级 数 据 库 阶 段 人 工 管 理 阶 段 : 50 年 代 中 期 之 前, 计 算 机 的 软 硬 件 均 不 完 善 硬 件 存 储 设 备 只 有 磁 带 卡 片 和 纸 带, 软 件 方 面 还 没 有 操 作 系 统, 当 时 的 计 算 机 主 要 用 于 科 学 计 算 这 个 阶 段 由 于 还 没 有 软 件 系 统 对 数 据 进 行 管 理, 程 序 员 在 程 序 中 不 仅 要 规 定 数 据 的 逻 辑 结 构, 还 要 设 计 其 物 理 结 构, 包 括 存 储 结 构 存 取 方 法 输 入 输 出 方 式 等 当 数 据 的 物 理 组 织 或 存 储 设 备 改 变 时, 用 户 程 序 就 必 须 重 新 编 制 由 于 数 据 的 组 织 面 向 应 用, 不 同 的 计 算 程 序 之 间 不 能 共 享 数 据, 使 得 不 同 的 应 用 之 间 存 在 大 量 的 重 复 数 据, 很 难 维 护 应 用 程 序 之 间 数 据 的 一 致 性 这 一 阶 段 的 主 要 特 征 可 归 纳 为 如 下 几 点 : * 计 算 机 中 没 有 支 持 数 据 管 理 的 软 件 * 数 据 组 织 面 向 应 用, 数 据 不 能 共 享, 数 据 重 复 * 在 程 序 中 要 规 定 数 据 的 逻 辑 结 构 和 物 理 结 构, 数 据 与 程 序 不 独 立 4

5 * 数 据 处 理 方 式 批 处 理 文 件 系 统 阶 段 : 这 一 阶 段 的 主 要 标 志 是 计 算 机 中 有 了 专 门 管 理 数 据 库 的 软 件 操 作 系 统 ( 文 件 管 理 ) 上 世 纪 50 年 代 中 期 到 60 年 代 中 期, 由 于 计 算 机 大 容 量 存 储 设 备 ( 如 硬 盘 ) 的 出 现, 推 动 了 软 件 技 术 的 发 展, 而 操 作 系 统 的 出 现 标 志 着 数 据 管 理 步 入 一 个 新 的 阶 段 在 文 件 系 统 阶 段, 数 据 以 文 件 为 单 位 存 储 在 外 存, 且 由 操 作 系 统 统 一 管 理 操 作 系 统 为 用 户 使 用 文 件 提 供 了 友 好 界 面 文 件 的 逻 辑 结 构 与 物 理 结 构 脱 钩, 程 序 和 数 据 分 离, 使 数 据 与 程 序 有 了 一 定 的 独 立 性 用 户 的 程 序 与 数 据 可 分 别 存 放 在 外 存 储 器 上, 各 个 应 用 程 序 可 以 共 享 一 组 数 据, 实 现 了 以 文 件 为 单 位 的 数 据 共 享 但 由 于 数 据 的 组 织 仍 然 是 面 向 程 序, 所 以 存 在 大 量 的 数 据 冗 余 而 且 数 据 的 逻 辑 结 构 不 能 方 便 地 修 改 和 扩 充, 数 据 逻 辑 结 构 的 每 一 点 微 小 改 变 都 会 影 响 到 应 用 程 序 由 于 文 件 之 间 互 相 独 立, 因 而 它 们 不 能 反 映 现 实 世 界 中 事 物 之 间 的 联 系, 操 作 系 统 不 负 责 维 护 文 件 之 间 的 联 系 信 息 如 果 文 件 之 间 有 内 容 上 的 联 系, 那 也 只 能 由 应 用 程 序 去 处 理 数 据 库 系 统 阶 段 : 60 年 代 后, 随 着 计 算 机 在 数 据 管 理 领 域 的 普 遍 应 用, 人 们 对 数 据 管 理 技 术 提 出 了 更 高 的 要 求 : 希 望 面 向 企 业 或 部 门, 以 数 据 为 中 心 组 织 数 据, 减 少 数 据 的 冗 余, 提 供 更 高 的 数 据 共 享 能 力, 同 时 要 求 程 序 和 数 据 具 有 较 高 的 独 立 性, 当 数 据 的 逻 辑 结 构 改 变 时, 不 涉 及 数 据 的 物 理 结 构, 也 不 影 响 应 用 程 序, 以 降 低 应 用 程 序 研 制 与 维 护 的 费 用 数 据 库 技 术 正 是 在 这 样 一 个 应 用 需 求 的 基 础 上 发 展 起 来 的 数 据 库 技 术 有 如 下 特 点 : * 面 向 企 业 或 部 门, 以 数 据 为 中 心 组 织 数 据, 形 成 综 合 性 的 数 据 库, 为 各 应 用 共 享 * 采 用 一 定 的 数 据 模 型 数 据 模 型 不 仅 要 描 述 数 据 本 身 的 特 点, 而 且 要 描 述 数 据 之 间 的 联 系 * 数 据 冗 余 小, 易 修 改 易 扩 充 不 同 的 应 用 程 序 根 据 处 理 要 求, 从 数 据 库 中 获 取 需 要 的 数 据, 这 样 就 减 少 了 数 据 的 重 复 存 储, 也 便 于 增 加 新 的 数 据 结 构, 便 于 维 护 数 据 的 一 致 性 * 程 序 和 数 据 有 较 高 的 独 立 性 * 具 有 良 好 的 用 户 接 口, 用 户 可 方 便 地 开 发 和 使 用 数 据 库 * 对 数 据 进 行 统 一 管 理 和 控 制, 提 供 了 数 据 的 安 全 性 完 整 性 以 及 并 发 控 制 从 文 件 系 统 发 展 到 数 据 库 系 统, 这 在 信 息 领 域 中 具 有 里 程 碑 的 意 义 在 文 件 系 统 阶 段, 人 们 在 信 息 处 理 中 关 注 的 中 心 问 题 是 系 统 功 能 的 设 计, 因 此 程 序 设 计 占 主 导 地 位 ; 而 在 数 据 库 方 式 下, 数 据 开 始 占 据 了 中 心 位 置, 数 据 的 结 构 设 计 成 为 信 息 系 统 首 先 关 心 的 问 题, 而 应 用 程 序 则 以 既 定 的 书 结 构 为 基 础 进 行 设 计 大 事 记 1951:Univac 系 统 使 用 磁 带 和 穿 孔 卡 片 作 为 数 据 存 储 1956:IBM 公 司 在 其 Model 305 RAMAC 中 第 一 次 引 入 了 磁 盘 驱 动 器 1961: 通 用 电 气 (GE) 公 司 的 Charles Bachman 开 发 了 第 一 个 数 据 库 管 理 系 统 IDS 1969:E.F. Codd 发 明 了 关 系 数 据 库 5

6 1973: 由 John J.Cullinane 领 导 Cullinane 公 司 开 发 了 IDMS 一 个 针 对 IBM 主 机 的 基 于 网 络 模 型 的 数 据 库 1976: Honeywell 公 司 推 出 了 Multics Relational Data Store 第 一 个 商 用 关 系 数 据 库 产 品 1979: Oracle 公 司 引 入 了 第 一 个 商 用 SQL 关 系 数 据 库 管 理 系 统 1983: IBM 推 出 了 DB2 数 据 库 产 品 1985: 为 Procter & Gamble 系 统 设 计 的 第 一 个 商 务 智 能 系 统 产 生 1991: W.H. Bill Inmon 发 表 了 构 建 数 据 仓 库 未 来 发 展 趋 势 : 随 着 信 息 管 理 内 容 的 不 断 扩 展, 出 现 了 丰 富 多 样 的 数 据 模 型 ( 层 次 模 型, 网 状 模 型, 关 系 模 型, 面 向 对 象 模 型, 半 结 构 化 模 型 等 ), 新 技 术 也 层 出 不 穷 ( 数 据 流,Web 数 据 管 理, 数 据 挖 掘 等 ) 目 前 每 隔 几 年, 国 际 上 一 些 资 深 的 数 据 库 专 家 就 会 聚 集 一 堂, 探 讨 数 据 库 研 究 现 状, 存 在 的 问 题 和 未 来 需 要 关 注 的 新 技 术 焦 点 过 去 已 有 的 几 个 类 似 报 告 包 括 :1989 年 Future Directions indbms Research-The Laguna BeachParticipants,1990 年 DatabaseSystems : Achievements and Opportunities,1995 年 的 Database 1991:W.H. Inmon 发 表 了 构 建 数 据 仓 库 1.3 数 据 库 管 理 系 统 DBMS 的 定 义 数 据 库 管 理 系 统 (database management system) 是 一 种 操 纵 和 管 理 数 据 库 的 大 型 软 件, 是 用 于 建 立 使 用 和 维 护 数 据 库, 简 称 dbms 它 对 数 据 库 进 行 统 一 的 管 理 和 控 制, 以 保 证 数 据 库 的 安 全 性 和 完 整 性 用 户 通 过 dbms 访 问 数 据 库 中 的 数 据, 数 据 库 管 理 员 也 通 过 dbms 进 行 数 据 库 的 维 护 工 作 它 提 供 多 种 功 能, 可 使 多 个 应 用 程 序 和 用 户 用 不 同 的 方 法 在 同 时 或 不 同 时 刻 去 建 立, 修 改 和 询 问 数 据 库 它 使 用 户 能 方 便 地 定 义 和 操 纵 数 据, 维 护 数 据 的 安 全 性 和 完 整 性, 以 及 进 行 多 用 户 下 的 并 发 控 制 和 恢 复 数 据 库 DBMS 的 组 成 按 功 能 划 分, 数 据 库 管 理 系 统 大 致 可 分 为 6 个 部 分 : (1) 模 式 翻 译 : 提 供 数 据 定 义 语 言 (ddl) 用 它 书 写 的 数 据 库 模 式 被 翻 译 为 内 部 表 示 数 据 库 的 逻 辑 结 构 完 整 性 约 束 和 物 理 储 存 结 构 保 存 在 内 部 的 数 据 字 典 中 数 据 库 的 各 种 数 据 操 作 ( 如 查 找 修 改 插 入 和 删 除 等 ) 和 数 据 库 的 维 护 管 理 都 是 以 数 据 库 模 式 为 依 据 的 (2) 应 用 程 序 的 编 译 : 把 包 含 着 访 问 数 据 库 语 句 的 应 用 程 序, 编 译 成 在 dbms 支 持 下 可 运 行 的 目 标 程 序 (3) 交 互 式 查 询 : 提 供 易 使 用 的 交 互 式 查 询 语 言, 如 sql dbms 负 责 执 行 查 询 命 令, 并 将 查 询 结 果 显 示 在 屏 幕 上 (4) 数 据 的 组 织 与 存 取 : 提 供 数 据 在 外 围 储 存 设 备 上 的 物 理 组 织 与 存 取 方 法 6

7 ⑸ 事 务 运 行 管 理 : 提 供 事 务 运 行 管 理 及 运 行 日 志, 事 务 运 行 的 安 全 性 监 控 和 数 据 完 整 性 检 查, 事 务 的 并 发 控 制 及 系 统 恢 复 等 功 能 (6) 数 据 库 的 维 护 : 为 数 据 库 管 理 员 提 供 软 件 支 持, 包 括 数 据 安 全 控 制 完 整 性 保 障 数 据 库 备 份 数 据 库 重 组 以 及 性 能 监 控 等 维 护 工 具 基 于 关 系 模 型 的 数 据 库 管 理 系 统 已 日 臻 完 善, 并 已 作 为 商 品 化 软 件 广 泛 应 用 于 各 行 各 业 它 在 各 户 服 务 器 结 构 的 分 布 式 多 用 户 环 境 中 的 应 用, 使 数 据 库 系 统 的 应 用 进 一 步 扩 展 随 着 新 型 数 据 模 型 及 数 据 管 理 的 实 现 技 术 的 推 进, 可 以 预 期 dbms 软 件 的 性 能 还 将 更 新 和 完 善, 应 用 领 域 也 将 进 一 步 地 拓 宽 DBMS 的 功 能 提 供 的 功 能 有 以 下 几 项 : (1) 数 据 定 义 功 能 DBMS 提 供 相 应 数 据 语 言 来 定 义 (DDL) 数 据 库 结 构, 它 们 是 刻 画 数 据 库 框 架, 并 被 保 存 在 数 据 字 典 中 (2) 数 据 存 取 功 能 DBMS 提 供 数 据 操 纵 语 言 (DML), 实 现 对 数 据 库 数 据 的 基 本 存 取 操 作 : 检 索, 插 入, 修 改 和 删 除 (3) 数 据 库 运 行 管 理 功 能 DBMS 提 供 数 据 控 制 功 能, 即 是 数 据 的 安 全 性 完 整 性 和 并 发 控 制 等 对 数 据 库 运 行 进 行 有 效 地 控 制 和 管 理, 以 确 保 数 据 正 确 有 效 (4) 数 据 库 的 建 立 和 维 护 功 能 包 括 数 据 库 初 始 数 据 的 装 入, 数 据 库 的 转 储 恢 复 重 组 织, 系 统 性 能 监 视 分 析 等 功 能 (5) 数 据 库 的 传 输 DBMS 提 供 处 理 数 据 的 传 输, 实 现 用 户 程 序 与 DBMS 之 间 的 通 信, 通 常 与 操 作 系 统 协 调 完 成 说 到 DBMS 的 功 能 我 们 还 可 以 分 别 针 对 不 同 的 用 户 来 说 : 针 对 程 序 员 来 说 : 数 据 定 义 功 能 : 提 供 数 据 定 义 语 言 (DDL) 定 义 数 据 库 中 的 数 据 对 象 数 据 操 纵 功 能 : 提 供 数 据 操 纵 语 言 (DML) 操 纵 数 据 实 现 对 数 据 库 的 基 本 操 作 ( 查 询 插 入 删 除 和 修 改 ) 针 对 数 据 库 管 理 员 来 说 : 数 据 库 的 运 行 管 理 保 证 数 据 的 安 全 性 完 整 性 多 用 户 对 数 据 的 并 发 使 用 发 生 故 障 后 的 系 统 恢 复 数 据 库 的 建 立 和 维 护 功 能 ( 实 用 程 序 ) 7

8 数 据 库 数 据 批 量 装 载 数 据 库 转 储 介 质 故 障 恢 复 数 据 库 的 重 组 织 性 能 监 视 等 时 下 比 较 流 行 的 DBMS 目 前 有 许 多 数 据 库 产 品, 如 Oracle Sybase Informix Microsoft SQL Server MySQL Microsoft Access Visual FoxPro 等 产 品 各 以 自 己 特 有 的 功 能, 在 数 据 库 市 场 上 占 有 一 席 之 地 1.4 数 据 库 系 统 数 据 库 系 统 的 定 义 数 据 库 系 统 (database systems), 是 由 数 据 库 及 其 管 理 软 件 组 成 的 系 统 它 是 为 适 应 数 据 处 理 的 需 要 而 发 展 起 来 的 一 种 较 为 理 想 的 数 据 处 理 的 核 心 机 构 它 是 一 个 实 际 可 运 行 的 存 储 维 护 和 应 用 系 统 提 供 数 据 的 软 件 系 统, 是 存 储 介 质 处 理 对 象 和 管 理 系 统 的 集 合 体 数 据 库 系 统 DBS(Data Base System, 简 称 DBS) 是 一 个 实 际 可 运 行 的 存 储 维 护 和 应 用 系 统 提 供 数 据 的 软 件 系 统, 是 存 储 介 质 处 理 对 象 和 管 理 系 统 的 集 合 体 它 通 常 由 软 件 数 据 库 和 数 据 管 理 员 组 成 其 软 件 主 要 包 括 操 作 系 统 各 种 宿 主 语 言 实 用 程 序 以 及 数 据 库 管 理 系 统 数 据 库 由 数 据 库 管 理 系 统 统 一 管 理, 数 据 的 插 入 修 改 和 检 索 均 要 通 过 数 据 库 管 理 系 统 进 行 数 据 管 理 员 负 责 创 建 监 控 和 维 护 整 个 数 据 库, 使 数 据 能 被 任 何 有 权 使 用 的 人 有 效 使 用 数 据 库 管 理 员 一 般 是 由 业 务 水 平 较 高 资 历 较 深 的 人 员 担 任 数 据 库 系 统 的 个 体 含 义 是 指 一 个 具 体 的 数 据 库 管 理 系 统 软 件 和 用 它 建 立 起 来 的 数 据 库 ; 它 的 学 科 含 义 是 指 研 究 开 发 建 立 维 护 和 应 用 数 据 库 系 统 所 涉 及 的 理 论 方 法 技 术 所 构 成 的 学 科 在 这 一 含 义 下, 数 据 库 系 统 是 软 件 研 究 领 域 的 一 个 重 要 分 支, 常 称 为 数 据 库 领 域 数 据 库 系 统 是 为 适 应 数 据 处 理 的 需 要 而 发 展 起 来 的 一 种 较 为 理 想 的 数 据 处 理 的 核 心 机 构 计 算 机 的 高 速 处 理 能 力 和 大 容 量 存 储 器 提 供 了 实 现 数 据 管 理 自 动 化 的 条 件 构 成 和 性 能 构 成 : 数 据 库 系 统 一 般 由 4 个 部 分 组 成 :1 数 据 库, 即 存 储 在 磁 带 磁 盘 光 盘 或 其 他 外 存 8

9 介 质 上 按 一 定 结 构 组 织 在 一 起 的 相 关 数 据 的 集 合 2 数 据 库 管 理 系 统 (DBMS) 它 是 一 组 能 完 成 描 述 管 理 维 护 数 据 库 的 程 序 系 统 它 按 照 一 种 公 用 的 和 可 控 制 的 方 法 完 成 插 入 新 数 据 修 改 和 检 索 原 有 数 据 的 操 作 3 数 据 库 管 理 员 (DBA) 4 用 户 和 应 用 程 序 性 能 : 1 能 够 保 证 数 据 的 独 立 性 数 据 和 程 序 相 互 独 立 有 利 于 加 快 软 件 开 发 速 度, 节 省 开 发 费 用 2 冗 余 数 据 少, 数 据 共 享 程 度 高 3 系 统 的 用 户 接 口 简 单, 用 户 容 易 掌 握, 使 用 方 便 4 能 够 确 保 系 统 运 行 可 靠, 出 现 故 障 时 能 迅 速 排 除 ; 能 够 保 护 数 据 不 受 非 受 权 者 访 问 或 破 坏 ; 能 够 防 止 错 误 数 据 的 产 生, 一 旦 产 生 也 能 及 时 发 现 5 有 重 新 组 织 数 据 的 能 力, 能 改 变 数 据 的 存 储 结 构 或 数 据 存 储 位 置, 以 适 应 用 户 操 作 特 性 的 变 化, 改 善 由 于 频 繁 插 入 删 除 操 作 造 成 的 数 据 组 织 零 乱 和 时 空 性 能 变 坏 的 状 况 6 具 有 可 修 改 性 和 可 扩 充 性 7 能 够 充 分 描 述 数 据 间 的 内 在 联 系 1.5 MySQL 数 据 库 MySQL 简 介 MySQL 是 一 个 小 型 关 系 型 数 据 库 管 理 系 统, 开 发 者 为 瑞 典 MySQL AB 公 司 在 2008 年 1 月 16 号 被 Sun 公 司 收 购 而 2009 年,SUN 又 被 Oracle 收 购. 对 于 Mysql 的 前 途, 没 有 任 何 人 抱 乐 观 的 态 度. 目 前 MySQL 被 广 泛 地 应 用 在 Internet 上 的 中 小 型 网 站 中 由 于 其 体 积 小 速 度 快 总 体 拥 有 成 本 低, 尤 其 是 开 放 源 码 这 一 特 点, 许 多 中 小 型 网 站 为 了 降 低 网 站 总 体 拥 有 成 本 而 选 择 了 MySQL 作 为 网 站 数 据 库 MySQL 这 个 名 字 是 怎 么 来 的 已 经 不 清 楚 了 基 本 指 南 和 大 量 的 库 和 工 具 带 有 前 缀 my 已 经 有 10 年 以 上, 而 且 不 管 怎 样,MySQL AB 创 始 人 之 一 的 Monty Widenius 的 女 儿 也 叫 My 这 两 个 到 底 是 哪 一 个 给 出 了 MySQL 这 个 名 字 至 今 依 然 是 个 迷, 包 括 开 发 者 在 内 也 不 知 道 自 身 特 性 : 1. 使 用 C 和 C++ 编 写, 并 使 用 了 多 种 编 译 器 进 行 测 试, 保 证 源 代 码 的 可 移 植 性 2. 支 持 AIX FreeBSD HP-UX Linux Mac OS Novell Netware OpenBSD OS/2 Wrap Solaris Windows 等 多 种 操 作 系 统 3. 为 多 种 编 程 语 言 提 供 了 API 这 些 编 程 语 言 包 括 C C++ Python Java Perl PHP Eiffel Ruby 和 Tcl 等 4. 支 持 多 线 程, 充 分 利 用 CPU 资 源 5. 优 化 的 SQL 查 询 算 法, 有 效 地 提 高 查 询 速 度 9

10 6. 既 能 够 作 为 一 个 单 独 的 应 用 程 序 应 用 在 客 户 端 服 务 器 网 络 环 境 中, 也 能 够 作 为 一 个 库 而 嵌 入 到 其 他 的 软 件 中 提 供 多 语 言 支 持, 常 见 的 编 码 如 中 文 的 GB 2312 BIG5, 日 文 的 Shift_JIS 等 都 可 以 用 作 数 据 表 名 和 数 据 列 名 7. 提 供 TCP/IP ODBC 和 JDBC 等 多 种 数 据 库 连 接 途 径 8. 提 供 用 于 管 理 检 查 优 化 数 据 库 操 作 的 管 理 工 具 9. 可 以 处 理 拥 有 上 千 万 条 记 录 的 大 型 数 据 库 MySQL 的 基 本 操 作 mysql 命 令 mysql 命 令 mysql -h host_name -u user_name p 系 统 提 示 输 入 : password h: 当 连 接 MySQL 服 务 器 不 在 同 台 主 机 时, 填 写 主 机 名 或 IP 地 址 u: 登 录 MySQL 的 用 户 名 p: 登 录 MySQL 的 密 码 注 意 : 密 码 如 果 写 在 命 令 行 的 时 候 一 定 不 能 有 空 格 如 果 使 用 的 系 统 为 linux 并 且 登 陆 用 户 名 字 与 MySQL 的 用 户 名 相 同 即 可 不 用 输 入 用 户 名 密 码,linux 默 认 是 以 root 登 陆,windows 默 认 用 户 是 ODBC 我 们 采 用 的 是 安 装 在 Linux 系 统 下 的 MySQL 数 据 库, 在 这 里 具 体 如 何 安 装 就 不 再 赘 述 了, 网 上 资 料 很 多, 而 且 不 同 系 统 也 不 尽 相 同 无 法 同 意 说 明 我 们 这 里 用 的 ubuntu 系 统, 这 是 目 前 比 较 大 众 化 的 Linux 系 统 发 行 版 本, 我 们 启 动 系 统 登 陆 进 去 如 下 图 : 我 们 事 先 已 经 安 装 好 了 一 个 MySQL 数 据 库 在 系 统 上, 所 以 我 们 按 照 上 面 的 命 令 格 式 来 进 入 MySQL 数 据 库 管 理 系 统, 敲 入 命 令 :mysql -h host_name -u user_name p 10

11 输 入 密 码 有 如 下 结 果 : MySQL 常 用 命 令 : 选 定 默 认 数 据 库 :use dbname; 显 示 所 有 数 据 库 :show databases; 显 示 默 认 数 据 库 中 所 有 表 :show tables; 放 弃 正 在 输 入 的 命 令 :\c 显 示 命 令 清 单 :\h 11

12 查 看 MySQL 服 务 器 状 态 信 息 :\s 退 出 mysql 程 序 :\q 处 理 存 放 在 文 件 里 的 SQL 处 理 存 放 在 文 件 里 的 SQL 命 令 格 式 :mysql [option] dbname < file.sql mysql u root p --default-character-set=latin1 dbname < backup.sql 注 :latin1 是 MySQL4.0 的 默 认 字 符 集 或 者 utf-8 是 MySQL5.0 的 默 认 字 符 集, 如 果 不 知 道 使 用 什 么 字 符 集 的 时 候, 可 以 选 用 这 两 个 其 中 一 个 在 Linux 系 统 下 建 立 一 个 目 录 sql, 然 后 在 该 目 录 下 建 立 一 个 文 件 : demo.sql 命 令 如 下 : 然 后 利 用 Vi 编 辑 器 去 编 辑 文 件, 内 容 如 下 : 然 后 输 入 命 令 : 12

13 如 果 不 是 在 虚 拟 主 机 上, 那 -h 选 项 可 以 不 要 输 入 密 码 就 好 了, 然 后 我 们 进 入 数 据 库 看 一 下 结 果 : 表 结 构 已 经 有 了. mysqldump 命 令 mysqldump 命 令 用 于 为 数 据 库 创 建 备 份 格 式 :mysqldump [option] dbname > bak.sql mysqldump u root p tmpdb > backuptmp.sql 备 份 出 来 的 是 一 个 文 本 文 件, 默 认 为 utf-8 字 符 集, 如 果 想 使 用 另 外 一 种 字 符 集, 必 须 使 用 --default-character-set=gbk 选 项 来 设 置 我 们 去 查 看 一 下 : 以 上 的 两 种 命 令 是 用 来 备 份 和 回 复 数 据 库 使 用 的 MySQL 的 基 本 操 作 我 们 暂 时 就 说 到 这 里 了, 下 面 一 章 我 们 会 来 讲 一 下 数 据 库 的 基 础 知 识 13

14 第 二 章 数 据 库 基 础 知 识 2.1 数 据 库 基 础 知 识 概 述 数 据 库 由 一 批 数 据 构 成 的 有 序 集 合, 这 些 数 据 被 分 门 别 类 地 存 放 在 一 些 结 构 化 的 数 据 表 (table) 里, 而 数 据 表 之 间 又 往 往 存 在 交 叉 引 用 的 关 系, 这 种 关 系 使 数 据 库 又 被 称 为 关 系 型 数 据 库 档 案 柜 = 数 据 库 抽 屉 = 表 文 件 = 记 录 数 据 表 是 数 据 库 中 一 个 非 常 重 要 的 对 象, 是 其 他 对 象 的 基 础 没 有 数 据 表, 关 键 字 主 键 索 引 等 也 就 无 从 谈 起 在 数 据 库 画 板 中 可 以 显 示 数 据 库 中 的 所 有 数 据 表 ( 即 使 不 是 用 PowerBuilder 创 建 的 表 ), 创 建 数 据 表, 修 改 表 的 定 义 等 数 据 表 是 数 据 库 中 一 个 非 常 重 要 的 对 象, 是 其 他 对 象 的 基 础 数 据 表 ( 或 称 表 ) 是 数 据 库 最 重 要 的 组 成 部 分 之 一 数 据 库 只 是 一 个 框 架, 数 据 表 才 是 其 实 质 内 容 根 据 信 息 的 分 类 情 况, 一 个 数 据 库 中 可 能 包 含 若 干 个 数 据 表 如 教 学 管 理 系 统 中, 教 学 管 理 数 据 库 包 含 分 别 围 绕 特 定 主 题 的 6 个 数 据 表 : 教 师 表 课 程 表 成 绩 表 学 生 表 班 级 表 和 授 课 表, 用 来 管 理 教 学 过 程 中 学 生 教 师 课 程 等 信 息 这 些 各 自 独 立 的 数 据 表 通 过 建 立 关 系 被 联 接 起 来, 成 为 可 以 交 叉 查 阅 一 目 了 然 的 数 据 库 数 据 库 中 的 数 据 是 从 外 界 输 入 的, 而 数 据 的 输 入 由 于 种 种 原 因, 会 发 生 输 入 无 效 或 错 误 信 息 保 证 输 入 的 数 据 符 合 规 定, 成 为 了 数 据 库 系 统, 尤 其 是 多 用 户 的 关 系 数 据 库 系 统 首 要 关 注 的 问 题 数 据 完 整 性 因 此 而 提 出 本 章 将 讲 述 数 据 完 整 性 的 概 念 及 其 在 SQL Server 中 的 实 现 方 法 数 据 完 整 性 (Data Integrity) 是 指 数 据 的 精 确 性 (Accuracy) 和 可 靠 性 (Reliability) 它 是 应 防 止 数 据 库 中 存 在 不 符 合 语 义 规 定 的 数 据 和 防 止 因 错 误 信 息 的 输 入 输 出 造 成 无 效 操 作 或 错 误 信 息 而 提 出 的 数 据 完 整 性 分 为 四 类 : 实 体 完 整 性 (Entity Integrity) 域 完 整 性 (Domain Integrity) 参 照 完 整 性 (Referential Integrity) 用 户 定 义 的 完 整 性 (User-definedIntegrity) 数 据 库 采 用 多 种 方 法 来 保 证 数 据 完 整 性, 包 括 外 键 约 束 规 则 和 触 发 器 系 统 很 好 地 处 理 了 这 四 者 的 关 系, 并 针 对 不 同 的 具 体 情 况 用 不 同 的 方 法 进 行, 相 互 交 叉 使 用, 相 补 缺 点 实 体 完 整 性 (Entity Integrity): 14

15 域 完 整 性 (Domain Integrity): 参 照 完 整 性 (Referential Integrity): 用 户 定 义 的 完 整 性 (User-definedIntegrity): 15

16 2.2 SQL 概 述 什 么 是 SQL? SQL 是 Structured Query Language( 结 构 化 查 询 语 言 ) 的 缩 写 SQL 是 专 为 数 据 库 而 建 立 的 操 作 命 令 集, 是 一 种 功 能 齐 全 的 数 据 库 语 言 在 使 用 它 时, 只 需 要 发 出 做 什 么 的 命 令, 怎 么 做 是 不 用 使 用 者 考 虑 的 SQL 的 语 法 构 成 DML( Data Manipulation Language 数 据 操 作 语 言 ) 查 询 插 入 删 除 和 修 改 数 据 库 中 的 数 据 ; SELECT INSERT UPDATE DELETE 等 ; DCL( Data Control Language 数 据 控 制 语 言 ) 用 来 控 制 存 取 许 可 存 取 权 限 等 ; GRANT REVOKE 等 ; DDL( Data Definition Language 数 据 定 义 语 言 ) 用 来 建 立 数 据 库 数 据 库 对 象 和 定 义 其 列 CREATE TABLE DROP TABLE ALTER TABLE 等 功 能 函 数 日 期 函 数 数 学 函 数 字 符 函 数 系 统 函 数 等 MySQL 中 使 用 SQL 语 言 几 点 说 明 属 于 一 个 SQL 语 句, 使 用 分 号 (;) 结 尾, 否 则 mysql 认 为 语 句 没 有 输 入 完 箭 头 (->) 代 表 SQL 语 句 没 有 输 入 完 取 消 SQL 语 句 使 用 (\c) SQL 语 句 关 键 字 和 函 数 名 不 区 分 大 小 写 (Linux 区 分,Windows 不 区 分 ) 使 用 函 数 时, 函 数 名 和 后 面 的 括 号 之 间 不 能 有 空 格 16

17 2.2.3 MySQL 的 数 据 类 型 数 值 列 类 型 : MySQL 为 除 了 NULL 值 外 的 所 有 通 用 数 据 类 型 提 供 了 列 类 型 列 类 型 是 一 种 手 段, 通 过 这 种 手 段 可 以 描 述 表 的 列 可 以 包 含 什 么 样 类 型 的 值 所 有 数 值 列 类 型 的 类 型 名 及 其 说 明 和 所 占 的 字 节 数 见 下 表 : 这 一 类 型 的 存 储 需 求 如 下 : 数 值 列 类 型 包 括 整 型 和 浮 点 型 :TINYINT, SAMLLINT, MEDIUMINT, INT, BIGINT 每 个 数 据 类 型 的 取 值 范 围 不 同, 故 分 别 可 以 表 示 不 同 的 数 值 范 围 在 定 义 整 型 列 时, 可 以 指 定 可 选 的 显 示 尺 寸 M( 见 上 表 ),M 是 从 1 到 255 的 值, 它 表 示 显 示 列 中 值 的 字 符 数 例 如 :INT(4) 意 思 是 指 定 了 一 个 具 有 4 个 字 符 显 示 宽 度 的 INT 列 如 果 定 义 了 一 个 没 有 明 确 宽 度 的 整 数 列, 则 会 分 配 缺 省 的 宽 度, 缺 省 值 为 每 种 类 型 的 最 长 值 的 长 度 对 于 每 种 浮 点 型, 可 指 定 一 个 最 大 的 显 示 尺 寸 M 和 小 数 位 数 D,M 的 取 值 应 该 是 0-30, 但 小 于 M-2 M 和 D 对 于 DECIMAL 是 必 须 的 数 值 列 的 完 整 性 约 束 : AUTO_INCREMENT 自 动 标 识 列, 在 需 要 产 生 唯 一 标 志 符 号 或 者 顺 序 值 时 候, 可 用 此 属 性 值 一 般 17

18 从 1 开 始, 每 行 增 加 1, 在 插 入 NULL 到 一 个 AUTO_INCREMENT 列 时,MySQL 会 插 入 一 个 比 该 列 中 当 前 最 大 值 大 1 的 值, 一 个 表 中 最 多 能 有 一 个 有 此 属 性 的 列 对 于 想 使 用 此 属 性 的 列 应 该 定 义 为 NOT NULL, 并 定 义 为 PRIMARY KEY 或 者 定 义 为 UNIQUE 键 举 例 : create table t(id int auto_increment not null primary key); UNSIGNED 无 符 号, 此 属 性 禁 用 负 值, 将 数 值 的 取 值 范 围 从 零 开 始 举 例 : create table t(num tinyint, num2 tinyint unsigned); NULL 和 NOT NULL 默 认 为 NULL, 即 插 入 值 时 没 有 在 此 字 段 插 入 值 时 自 动 填 NULL, 如 果 指 定 了 NOT NULL, 则 必 须 在 插 入 值 时 在 此 字 段 添 入 值, 不 允 许 插 入 NULL 值 DEFAULT 可 以 通 过 此 属 性 来 指 定 一 个 缺 省 值, 即 如 果 没 有 在 此 列 添 加 值, 那 么 默 认 添 加 DEFAULT 后 指 定 值 ZEROFILL 前 导 零 填 充 数 值 类 型 值 以 达 到 列 的 显 示 宽 度 举 例 : create table test2(num1 int default 1,num2 int zerofill); 下 面 我 们 来 进 到 MySQL 数 据 库 下 面 来 看 一 下 操 作 如 下 : 字 符 串 列 类 型 : 所 有 字 符 串 列 类 型 及 其 说 明 和 所 占 的 字 节 数 见 下 表 : 18

19 存 储 需 求 : 说 明 : 对 于 可 变 长 的 字 符 串 类 型, 其 长 度 取 决 于 实 际 存 放 在 列 中 的 值 的 长 度 此 长 度 在 上 ( 中 用 L 来 表 示 ) 加 若 干 额 外 字 节, 用 来 记 录 字 符 串 长 度, 也 就 是 说 总 长 度 等 于 L +n(n= ) CHAR 和 VARCHAR 类 型 CHAR 类 型 和 VARCHAR 类 型 长 度 范 围 都 是 0~255 之 间 的 大 小 他 们 之 间 的 差 别 在 于 MySQL 处 理 存 储 的 方 式 : CHAR 把 这 个 大 小 视 为 值 的 准 确 大 小 ( 用 空 格 填 补 比 较 短 的 值 ) VARCHAR 类 型 把 它 视 为 最 大 值 并 且 只 使 用 了 存 储 字 符 串 实 际 上 需 要 的 字 节 数 ( 增 加 了 一 个 额 外 的 字 节 记 录 长 度 ) 因 而, 较 短 的 值 当 被 插 入 一 个 语 句 为 VARCHAR 类 型 的 字 段 时, 将 不 会 用 空 格 填 补 ( 然 而, 较 长 的 值 仍 然 被 截 短 ) BLOB 和 TEXT 类 型 BLOB 是 二 进 制 大 对 象, 如 果 想 存 储 二 进 制 数 BLOB 将 是 最 佳 选 择, 而 TEXT 与 之 相 同, 只 是 BOLOB 按 照 二 进 制 编 码 排 序 ( 区 分 大 小 写 ),TEXT 按 照 字 符 文 本 模 式 排 序 ( 不 区 分 大 小 写 ) ENUM 和 SET 类 型 19

20 是 特 殊 的 串 类 型, 其 列 值 必 须 从 固 定 的 串 集 中 选 择, 二 者 差 别 为 前 者 必 须 是 只 能 选 择 其 中 的 一 个 值, 而 后 者 可 以 多 选 ( 不 推 荐 使 用 ) 字 符 串 列 类 型 的 可 选 属 性 : BINARY 使 用 此 属 性 可 以 使 列 值 作 为 二 进 制 串 处 理, 即 看 成 BLOB 类 型 NULL 和 NOT NULL 同 数 值 型 功 能 相 同 DEFAULT 同 数 值 型 功 能 相 同 日 期 和 时 间 列 类 型 : 所 有 时 间 和 日 期 列 类 型 的 类 型 名 及 其 说 明 和 所 占 的 字 节 数 见 下 表 : 存 储 需 求 : 说 明 : 每 个 时 间 和 日 期 列 类 型 都 有 一 个 零 值, 当 插 入 非 法 数 值 时 就 用 零 值 来 添 加 20

21 表 示 日 期 时 必 须 先 按 : 年, 月, 日 的 顺 序 给 出 DATE,TIME,DATETIME 分 别 是 存 储 日 期, 时 间 与 日 期 和 时 间 的 组 合, 其 格 式 为 YYYY-MM-DD, hh:mm:ss 和 YYYY-MM-DD hh:mm:ss, 对 于 DATETIME 类 型, 日 期 和 时 间 部 分 都 需 要 TIMESTAMP 时 间 戳 列 类 型 以 YYYYMMDDhhmmss 的 格 式 来 表 示 值, 其 取 值 范 围 是 到 2037 年 的 某 个 时 间, 主 要 用 于 记 录 更 改 或 创 建 某 个 记 录 now() 函 数 是 MySQL 中 取 当 前 时 间 的 函 数, 下 面 我 们 到 数 据 库 中 看 : 总 结 : 常 用 数 据 类 型 : 创 建 数 据 库 建 立 数 据 库 操 作 : 语 法 :create database 数 据 库 名 叙 述 : 创 建 一 个 具 有 指 定 名 称 的 数 据 库 如 果 要 创 建 的 数 据 库 已 经 存 在, 或 者 没 有 创 建 它 的 适 当 权 限, 则 此 语 句 失 败 21

22 例 : 建 立 一 个 student 库 mysql> create database student 创 建 表 建 立 表 操 作 : 语 法 :create table 表 名 ( 列 名 1 列 类 型 [< 列 的 完 整 性 约 束 >], 列 名 2 列 类 型 [< 列 的 完 整 性 约 束 >], ); 叙 述 : 在 当 前 数 据 库 下 新 创 建 一 个 数 据 表 列 类 型 : 表 示 该 列 的 数 据 类 型 例 : 建 立 一 个 表 school, 其 由 两 列 组 成, 第 一 列 属 性 为 非 空, 并 做 为 主 键, 并 自 增 create table school( ); school_id int(10) not null auto_increment primary key, school_name varchar(20) 常 见 完 整 性 约 束 : PRIMARY KEY 主 码 约 束 ( 主 键 ) UNIQUE 唯 一 性 约 束 NOT NULL 非 空 值 约 束 AUTO_INCREMENT 用 于 整 数 列 默 认 自 增 1 UNSIGNED 无 符 号 整 数 DEFAULT default_value 默 认 值 约 束 22

23 DEFAULT cur_timestamp 创 建 新 记 录 时 默 认 保 存 当 前 时 间 ( 仅 适 用 timestamp 数 据 列 ) ON UPDATE cur_timestamp 修 改 记 录 时 默 认 保 存 当 前 时 间 ( 仅 适 用 timestamp 数 据 列 ) CHARACTER SET name 指 定 字 符 集 ( 仅 适 用 字 符 串 ) 数 据 表 类 型 : 在 创 建 一 个 新 的 MySQL 数 据 表 时, 可 以 为 它 设 置 一 个 类 型, 其 中 最 重 要 的 3 种 类 型 是 MyISAM: 成 熟 稳 定 和 易 于 管 理 InnoDB: 加 入 事 物 数 据 行 级 锁 定 机 制 外 键 约 束 条 件 崩 溃 恢 复 等 新 功 能 HEAP: 只 存 在 于 内 存 中, 可 做 临 时 表 create table tmp( )ENGINE=MyISAM 主 键 与 外 键 : 数 据 表 之 间 的 关 联 / 引 用 关 系 是 依 靠 具 体 的 主 键 (primary key) 和 外 键 (foreign key) 建 立 起 来 的 主 键 : 帮 助 MySQL 以 最 快 的 速 度 把 一 条 特 点 的 数 据 记 录 的 位 置 确 定 下 来 主 键 必 须 是 唯 一 的 主 键 应 该 是 紧 凑 的, 因 此 整 数 类 型 比 较 适 合 外 键 : 引 用 另 外 一 个 数 据 表 的 某 条 记 录 外 键 列 类 型 尽 可 能 与 主 键 列 类 型 保 持 一 致 外 键 列 应 该 加 上 NOT NULL 23

24 主 键 create table student( ); sid int not null auto_increment increment, name varchar(20) not null, primary key(sid) 外 键 ( 自 动 检 查 外 键 是 否 匹 配, 仅 适 用 InnoDB) create table score( cid int not null auto_increment primary key, ); score int, sid int, foreign key(sid) references student(sid) 主 表 和 从 表 : 1 当 主 表 中 没 有 对 应 的 记 录 时, 不 能 将 记 录 添 加 到 子 表 成 绩 表 中 不 能 出 现 在 学 员 信 息 表 中 不 存 在 的 学 号 ; 2 不 能 更 改 主 表 中 的 值 而 导 致 子 表 中 的 记 录 孤 立 把 学 员 信 息 表 中 的 学 号 改 变 了, 学 员 成 绩 表 中 的 学 号 也 应 当 随 之 改 变 ; 3 子 表 存 在 与 主 表 对 应 的 记 录, 不 能 从 主 表 中 删 除 该 行 不 能 把 有 成 绩 的 学 员 删 除 了 4 删 除 主 表 前, 先 删 子 表 先 删 学 员 成 绩 表 后 删 除 学 员 信 息 表 例 : 建 立 一 个 学 生 信 息 表 (student) 定 义 列 sno 学 号, 类 型 为 5 位 定 长 字 符 串, 非 空, 主 键 定 义 列 sname 姓 名, 类 型 为 20 位 不 定 长 字 符 串, 非 空 定 义 列 ssex 性 别, 取 值 1 或 0 定 义 列 sage 年 龄, 类 型 为 短 整 型 定 义 列 sdept 系 名, 数 据 类 型 为 20 位 变 长 字 符 串 24

25 例 : 建 立 一 个 课 程 信 息 表 (course) 定 义 列 courseid 课 程 号, 类 型 为 整 型, 非 空, 自 动 增 长, 主 键 定 义 列 cname 课 程 名, 类 型 为 16 位 变 长 字 符 串, 非 空 例 : 建 立 一 个 学 生 考 试 成 绩 信 息 表 (sc) 定 义 列 grade 成 绩, 类 型 为 整 型 要 求 把 表 字 段 补 充 完 整, 可 以 通 过 学 号 查 看 学 生 相 关 课 程 的 成 绩 建 立 一 个 学 生 信 息 表 (student): 建 立 一 个 课 程 信 息 表 (course): 建 立 一 个 学 生 考 试 成 绩 信 息 表 (sc): 25

26 2.2.6 表 结 构 操 作 显 示 表 结 构 操 作 语 法 :describe 表 名 ; desc 表 名 ; 叙 述 : 用 于 显 示 表 的 创 建 结 构 删 除 数 据 库 或 表 操 作 删 除 表 操 作 语 法 :drop table [if exists] tab_name [,tab_name]... 叙 述 : 从 数 据 库 中 删 除 给 定 的 表 如 果 给 出 if exists 子 句, 则 删 除 不 存 在 的 表 不 会 出 错 删 除 数 据 库 操 作 语 法 :drop database [if exists] db_name 叙 述 : 删 除 给 定 的 数 据 库 在 删 除 一 个 数 据 库 后, 它 就 永 远 没 有 了, 因 此 要 特 别 小 心 如 果 给 出 if exists 子 句, 则 删 除 不 存 在 的 数 据 库 不 会 出 错 更 改 表 结 构 操 作 语 法 :alter table 表 名 action; 说 明 :action 可 以 是 如 下 语 句 : add 列 名 建 表 语 句 [first after 列 名 ] 26

27 可 以 为 表 添 加 一 列, 如 果 没 指 定 first 或 者 after, 则 在 列 尾 添 加 一 列, 否 则 在 指 定 列 添 加 新 列 add primary key ( 列 名 ) 为 表 添 加 一 个 主 键, 如 果 主 键 已 经 存 在, 则 出 现 错 误 add foreign key( 列 名 ) references 表 名 ( 列 名 ) 为 表 添 加 一 个 外 键 alter 列 名 set default 默 认 值 可 以 更 改 指 定 列 默 认 值 change 旧 列 名 新 列 名 < 建 表 语 句 > [first after 列 名 ] 可 以 更 改 列 类 型 和 列 名 称, 如 果 原 列 的 名 字 和 新 列 的 名 字 相 同 modify 列 名 < 建 表 语 句 > [first after 列 名 ] 和 change 的 作 用 相 同 drop 列 名 // 可 以 删 除 一 列 drop primary key // 可 以 删 除 主 键 engine 类 型 名 // 可 以 改 变 表 类 型 rename as 新 表 名 // 可 以 将 表 名 更 改 第 三 章 MySQL 运 算 符 以 及 SQl 语 句 3.1 MySQL 运 算 符 算 术 运 算 符 MySQL 中 的 运 算 符 如 下 表 罗 列 : 27

28 MySQL 中 有 字 符 串 自 动 转 换 为 数 字 的 功 能 : 用 字 符 串 表 示 的 数 字 在 任 何 可 能 便 于 计 算 的 地 方 都 被 自 动 地 转 换 为 数 字 当 执 行 转 换 时, MySQL 遵 循 两 个 基 本 规 则 : 如 果 第 一 位 是 数 字 的 字 符 串 被 用 于 一 个 算 数 运 算 中, 那 么 它 被 转 换 为 这 个 数 字 的 值 如 果 一 个 包 含 字 符 和 数 字 混 合 的 字 符 串 不 能 被 正 确 的 转 换 为 数 字, 那 么 它 被 转 换 成 比 较 运 算 符 比 较 运 算 符 允 许 我 们 对 表 达 式 的 左 边 和 右 边 进 行 比 较 一 个 比 较 运 算 符 的 结 果 总 是 1( 真 ),0( 假 ), 或 是 为 NULL( 不 能 确 定 ) 比 较 运 算 符 可 以 用 于 比 较 数 字 和 字 符 串 数 字 作 为 浮 点 值 比 较, 而 字 符 串 以 不 区 分 大 小 写 的 方 式 进 行 比 较 ( 除 非 使 用 特 殊 的 BINARY 二 进 制 关 键 字 ) 所 有 的 比 较 运 算 符 如 下 表 所 示 : 28

29 对 于 <> 运 算 符, 如 果 表 达 式 两 边 不 相 等 返 回 真 值, 相 等 返 回 假 值 还 可 以 比 较 字 符 串 BETWEEN 运 算 符 用 于 检 验 一 个 值 ( 或 者 一 个 求 值 表 达 式 ) 是 否 存 在 一 个 指 定 的 范 围 内 : IN 运 算 符 用 于 检 验 一 个 值 ( 或 者 一 个 求 值 表 达 式 ) 是 否 包 含 在 一 个 指 定 的 值 集 合 中 可 以 使 用 IS NULL 或 者 IS NOT NULL 运 算 符 来 测 定 是 否 为 空 可 以 使 用 特 殊 的 <=> 运 算 符,MySQL 称 为 NULL 安 全 的 等 于 运 算 符 这 意 味 着 即 使 当 包 含 在 比 较 运 算 符 中 的 表 达 式 含 有 一 个 NULL 值 时,MySQL 也 会 为 比 较 运 算 符 返 回 一 个 真 值 或 假 值 如 果 想 执 行 通 配 符 数 据 搜 索, 应 该 使 用 LIKE 运 算 符 它 通 过 在 表 达 式 中 允 许 使 用 专 门 的 通 配 字 符, 可 以 找 出 与 指 定 搜 索 字 符 串 全 部 或 部 分 匹 配 的 记 录 默 认 情 况 下, 比 较 是 不 区 分 大 小 写 的 方 式 执 行 的 然 而, 以 前 我 们 注 意 到, 可 以 添 加 BINARY 关 键 字 让 MySQL 执 行 区 分 大 小 写 的 比 较 MySQL 数 据 库 中 的 通 配 符 举 例 : % ( 百 分 号 ) 代 表 任 意 长 度 ( 长 度 可 以 为 0) 的 字 符 串 a%b 表 示 以 a 开 头, 以 b 结 尾 的 任 意 长 度 的 字 符 串 如 acb,addgb,ab 等 都 满 足 该 匹 配 串 举 例 : _ ( 下 横 线 ) 代 表 任 意 单 个 字 符 a_b 表 示 以 a 开 头, 以 b 结 尾 的 长 度 为 3 的 任 意 字 符 串 如 acb,afb 等 都 满 足 该 匹 配 串 REGEXP 运 算 符 允 许 我 们 执 行 更 复 杂 的 字 符 串 比 较 运 算, 在 这 里 使 用 UNIX 正 则 表 达 式 29

30 3.1.3 逻 辑 运 算 符 3.2 SQL 语 句 SQL 指 令 SELECT 是 用 来 做 什 么 的 呢? 一 个 最 常 用 的 方 式 是 将 资 料 从 数 据 库 中 的 表 格 内 选 出 从 这 一 句 回 答 中, 我 们 马 上 可 以 看 到 两 个 关 键 字 : 从 (FROM) 数 据 库 中 的 表 格 内 选 出 (SELECT) ( 表 格 是 一 个 数 据 库 内 的 结 构, 它 的 目 的 是 储 存 资 料 在 表 格 处 理 这 一 部 分 中, 我 们 会 提 到 如 何 使 用 SQL 来 设 定 表 格 ) 我 们 由 这 里 可 以 看 到 最 基 本 的 SQL 架 构 : SELECT " 栏 位 名 " FROM " 表 格 名 " 我 们 用 以 下 的 例 子 来 看 看 实 际 上 是 怎 么 用 的 假 设 我 们 有 以 下 这 个 表 格 : store_name Sales Date Los Angeles $1500 Jan San Diego $250 Jan Los Angeles $300 Jan Boston $700 Jan 若 要 选 出 所 有 的 店 名 (store_name), 我 们 就 打 入 : SELECT store_name FROM Store_Information 结 果 : store_name Los Angeles 30

31 San Diego Los Angeles Boston 我 们 一 次 可 以 读 取 好 几 个 栏 位, 也 可 以 同 时 由 好 几 个 表 格 中 选 资 料 DISTINCT SELECT 指 令 让 我 们 能 够 读 取 表 格 中 一 个 或 数 个 栏 位 的 所 有 资 料 这 将 把 所 有 的 资 料 都 抓 出, 无 论 资 料 值 有 无 重 复 在 资 料 处 理 中, 我 们 会 经 常 碰 到 需 要 找 出 表 格 内 的 不 同 资 料 值 的 情 况 换 句 话 说, 我 们 需 要 知 道 这 个 表 格 / 栏 位 内 有 哪 些 不 同 的 值, 而 每 个 值 出 现 的 次 数 并 不 重 要 这 要 如 何 达 成 呢? 在 SQL 中, 这 是 很 容 易 做 到 的 我 们 只 要 在 SELECT 后 加 上 一 个 DISTINCT 就 可 以 了 DISTINCT 的 语 法 如 下 : SELECT DISTINCT " 栏 位 名 " FROM " 表 格 名 " 举 例 来 说, 若 要 在 以 下 的 表 格,Store_Information, 找 出 所 有 不 同 的 店 名 时, Store_Information 表 格 store_name Sales Date Los Angeles $1500 Jan San Diego $250 Jan Los Angeles $300 Jan Boston $700 Jan 我 们 就 键 入, SELECT DISTINCT store_name FROM Store_Information 结 果 : store_name Los Angeles San Diego Boston 31

32 WHERE 我 们 并 不 一 定 每 一 次 都 要 将 表 格 内 的 资 料 都 完 全 抓 出 在 许 多 时 候, 我 们 会 需 要 选 择 性 地 抓 资 料 就 我 们 的 例 子 来 说, 我 们 可 能 只 要 抓 出 营 业 额 超 过 $1,000 的 资 料 要 做 到 这 一 点, 我 们 就 需 要 用 到 WHERE 这 个 指 令 这 个 指 令 的 语 法 如 下 : SELECT " 栏 位 名 " FROM " 表 格 名 " WHERE " 条 件 " 若 我 们 要 由 以 下 的 表 格 抓 出 营 业 额 超 过 $1,000 的 资 料, Store_Information 表 格 store_name Sales Date Los Angeles $1500 Jan San Diego $250 Jan Los Angeles $300 Jan Boston $700 Jan 我 们 就 键 入, SELECT store_name FROM Store_Information WHERE Sales > 1000 结 果 : store_name Los Angeles AND OR 在 上 一 页 中, 我 们 看 到 WHERE 指 令 可 以 被 用 来 由 表 格 中 有 条 件 地 选 取 资 料 这 个 条 件 可 能 是 简 单 的 ( 像 上 一 页 的 例 子 ), 也 可 能 是 复 杂 的 复 杂 条 件 是 由 二 或 多 个 简 单 条 件 透 过 AND 或 是 OR 的 连 接 而 成 一 个 SQL 语 句 中 可 以 有 无 限 多 个 简 单 条 件 的 存 在 复 杂 条 件 的 语 法 如 下 : SELECT " 栏 位 名 " FROM " 表 格 名 " WHERE " 简 单 条 件 " {[AND OR] " 简 单 条 件 "}+ 32

33 {}+ 代 表 {} 之 内 的 情 况 会 发 生 一 或 多 次 在 这 里 的 意 思 就 是 AND 加 简 单 条 件 及 OR 加 简 单 条 件 的 情 况 可 以 发 生 一 或 多 次 另 外, 我 们 可 以 用 () 来 代 表 条 件 的 先 后 次 序 举 例 来 说, 我 们 若 要 在 Store_Information 表 格 中 选 出 所 有 Sales 高 于 $1,000 或 是 Sales 在 $500 及 $275 之 间 的 资 料 的 话, Store_Information 表 格 store_name Sales Date Los Angeles $1500 Jan San Diego $250 Jan San Francisco $300 Jan Boston $700 Jan 我 们 就 键 入, SELECT store_name FROM Store_Information WHERE Sales > 1000 OR (Sales < 500 AND Sales > 275) 结 果 : store_name Los Angeles San Francisco IN 在 SQL 中, 在 两 个 情 况 下 会 用 到 IN 这 个 指 令 ; 这 一 页 将 介 绍 其 中 之 一 : 与 WHERE 有 关 的 那 一 个 情 况 在 这 个 用 法 下, 我 们 事 先 已 知 道 至 少 一 个 我 们 需 要 的 值, 而 我 们 将 这 些 知 道 的 值 都 放 入 IN 这 个 子 句 IN 指 令 的 语 法 为 下 : SELECT " 栏 位 名 " FROM " 表 格 名 " WHERE " 栏 位 名 " IN (' 值 一 ', ' 值 二 ',...) 在 括 号 内 可 以 有 一 或 多 个 值, 而 不 同 值 之 间 由 逗 点 分 开 值 可 以 是 数 目 或 是 文 字 若 在 括 号 内 只 有 一 个 值, 那 这 个 子 句 就 等 于 WHERE " 栏 位 名 " = ' 值 一 ' 举 例 来 说, 若 我 们 要 在 Store_Information 表 格 中 找 出 所 有 含 盖 Los Angeles 或 San Diego 的 资 料, 33

34 Store_Information 表 格 store_name Sales Date Los Angeles $1500 Jan San Diego $250 Jan San Francisco $300 Jan Boston $700 Jan 我 们 就 键 入, SELECT * FROM Store_Information WHERE store_name IN ('Los Angeles', 'San Diego') 结 果 : store_name Sales Date Los Angeles $1500 Jan San Diego $250 Jan BETWEEN IN 这 个 指 令 可 以 让 我 们 依 照 一 或 数 个 不 连 续 (discrete) 的 值 的 限 制 之 内 抓 出 资 料 库 中 的 值, 而 BETWEEN 则 是 让 我 们 可 以 运 用 一 个 范 围 (range) 内 抓 出 资 料 库 中 的 值 BETWEEN 这 个 子 句 的 语 法 如 下 : SELECT " 栏 位 名 " FROM " 表 格 名 " WHERE " 栏 位 名 " BETWEEN ' 值 一 ' AND ' 值 二 ' 这 将 选 出 栏 位 值 包 含 在 值 一 及 值 二 之 间 的 每 一 笔 资 料 举 例 来 说, 若 我 们 要 由 Store_Information 表 格 中 找 出 所 有 介 于 January 6, 1999 及 January 10, 1999 中 的 资 料, Store_Information 表 格 store_name Sales Date Los Angeles $1500 Jan San Diego $250 Jan San Francisco $300 Jan Boston $700 Jan 我 们 就 键 入, SELECT * 34

35 FROM Store_Information WHERE Date BETWEEN 'Jan ' AND 'Jan ' 请 读 者 注 意 : 在 不 同 的 数 据 库 中, 日 期 的 储 存 法 可 能 会 有 所 不 同 在 这 里 我 们 选 择 了 其 中 一 种 储 存 法 结 果 : store_name Sales Date San Diego $250 Jan San Francisco $300 Jan Boston $700 Jan LIKE LIKE 是 另 一 个 在 WHERE 子 句 中 会 用 到 的 指 令 基 本 上, LIKE 能 让 我 们 依 据 一 个 模 式 (pattern) 来 找 出 我 们 要 的 资 料 相 对 来 说, 在 运 用 IN 的 时 候, 我 们 完 全 地 知 道 我 们 需 要 的 条 件 ; 在 运 用 BETWEEN 的 时 候, 我 们 则 是 列 出 一 个 范 围 LIKE 的 语 法 如 下 : SELECT " 栏 位 名 " FROM " 表 格 名 " WHERE " 栏 位 名 " LIKE { 模 式 } { 模 式 } 经 常 包 括 野 卡 (wildcard). 以 下 是 几 个 例 子 : 'A_Z': 所 有 以 'A' 起 头, 另 一 个 任 何 值 的 字 原, 且 以 'Z' 为 结 尾 的 字 符 串 'ABZ' 和 'A2Z' 都 符 合 这 一 个 模 式, 而 'AKKZ' 并 不 符 合 ( 因 为 在 A 和 Z 之 间 有 两 个 字 原, 而 不 是 一 个 字 原 ) 'ABC%': 所 有 以 'ABC' 起 头 的 字 符 串 举 例 来 说,'ABCD' 和 'ABCABC' 都 符 合 这 个 模 式 '%XYZ': 所 有 以 'XYZ' 结 尾 的 字 符 串 举 例 来 说,'WXYZ' 和 'ZZXYZ' 都 符 合 这 个 模 式 '%AN%': 所 有 含 有 'AN' 这 个 模 式 的 字 符 串 举 例 来 说, 'LOS ANGELES' 和 'SAN FRANCISCO' 都 符 合 这 个 模 式 我 们 将 以 上 最 后 一 个 例 子 用 在 我 们 的 Store_Information 表 格 上 : Store_Information 表 格 store_name Sales Date LOS ANGELES $1500 Jan SAN DIEGO $250 Jan

36 SAN FRANCISCO $300 Jan BOSTON $700 Jan 我 们 就 键 入, SELECT * FROM Store_Information WHERE store_name LIKE '%AN%' 结 果 : store_name Sales Date LOS ANGELES $1500 Jan SAN FRANCISCO $300 Jan SAN DIEGO $250 Jan ORDER BY 到 目 前 为 止, 我 们 已 学 到 如 何 藉 由 SELECT 及 WHERE 这 两 个 指 令 将 资 料 由 表 格 中 抓 出 不 过 我 们 尚 未 提 到 这 些 资 料 要 如 何 排 列 这 其 实 是 一 个 很 重 要 的 问 题 事 实 上, 我 们 经 常 需 要 能 够 将 抓 出 的 资 料 做 一 个 有 系 统 的 显 示 这 可 能 是 由 小 往 大 (ascending) 或 是 由 大 往 小 (descending) 在 这 种 情 况 下, 我 们 就 可 以 运 用 ORDER BY 这 个 指 令 来 达 到 我 们 的 目 的 ORDER BY 的 语 法 如 下 : SELECT " 栏 位 名 " FROM " 表 格 名 " [WHERE " 条 件 "] ORDER BY " 栏 位 名 " [ASC, DESC] [] 代 表 WHERE 子 句 不 是 一 定 需 要 的 不 过, 如 果 WHERE 子 句 存 在 的 话, 它 是 在 ORDER BY 子 句 之 前 ASC 代 表 结 果 会 以 由 小 往 大 的 顺 序 列 出, 而 DESC 代 表 结 果 会 以 由 大 往 小 的 顺 序 列 出 如 果 两 者 皆 没 有 被 写 出 的 话, 那 我 们 就 会 用 ASC 我 们 可 以 照 好 几 个 不 同 的 栏 位 来 排 顺 序 在 这 个 情 况 下, ORDER BY 子 句 的 语 法 如 下 ( 假 设 有 两 个 栏 位 ): ORDER BY " 栏 位 一 " [ASC, DESC], " 栏 位 二 " [ASC, DESC] 若 我 们 对 这 两 个 栏 位 都 选 择 由 小 往 大 的 话, 那 这 个 子 句 就 会 造 成 结 果 是 依 据 " 栏 位 一 " 由 小 往 大 排 若 有 好 几 笔 资 料 " 栏 位 一 " 的 值 相 等, 那 这 几 笔 资 料 就 依 据 " 栏 位 二 " 由 小 往 大 排 36

37 举 例 来 说, 若 我 们 要 依 照 Sales 栏 位 的 由 大 往 小 列 出 Store_Information 表 格 中 的 资 料, Store_Information 表 格 store_name Sales Date Los Angeles $1500 Jan San Diego $250 Jan San Francisco $300 an Boston $700 Jan 我 们 就 键 入, SELECT store_name, Sales, Date FROM Store_Information ORDER BY Sales DESC 结 果 : store_name Sales Date Los Angeles $1500 Jan Boston $700 Jan San Francisco $300 Jan San Diego $250 Jan 在 以 上 的 例 子 中, 我 们 用 栏 位 名 来 指 定 排 列 顺 序 的 依 据 除 了 栏 位 名 外, 我 们 也 可 以 用 栏 位 的 顺 序 ( 依 据 SQL 句 中 的 顺 序 ) 在 SELECT 后 的 第 一 个 栏 位 为 1, 第 二 个 栏 位 为 2, 以 此 类 推 在 上 面 这 个 例 子 中, 我 们 用 以 下 这 句 SQL 可 以 达 到 完 全 一 样 的 效 果 : SELECT store_name, Sales, Date FROM Store_Information ORDER BY 2 DESC 函 数 既 然 数 据 库 中 有 许 多 资 料 都 是 已 数 字 的 型 态 存 在, 一 个 很 重 要 的 用 途 就 是 要 能 够 对 这 些 数 字 做 一 些 运 算, 例 如 将 它 们 总 合 起 来, 或 是 找 出 它 们 的 平 均 值 SQL 有 提 供 一 些 这 一 类 的 函 数 它 们 是 : AVG ( 平 均 ) COUNT ( 计 数 ) MAX ( 最 大 值 ) 37

38 MIN ( 最 小 值 ) SUM ( 总 合 ) 运 用 函 数 的 语 法 是 : SELECT " 函 数 名 "(" 栏 位 名 ") FROM " 表 格 名 " 举 例 来 说, 若 我 们 要 由 我 们 的 范 例 表 格 中 求 出 Sales 栏 位 的 总 合, Store_Information 表 格 store_name Sales Date Los Angeles $1500 Jan San Diego $250 Jan Los Angeles $300 Jan Boston $700 Jan 我 们 就 键 入, SELECT SUM(Sales) FROM Store_Information 结 果 : SUM(Sales) $2750 $2750 代 表 所 有 Sales 栏 位 的 总 合 : $ $250 + $300 + $700. 除 了 函 数 的 运 用 外,SQL 也 可 以 做 简 单 的 数 学 运 算, 例 如 加 (+) 和 减 (-) 对 于 文 字 类 的 资 料,SQL 也 有 好 几 个 文 字 处 理 方 面 的 函 数, 例 如 文 字 相 连 (concatenation), 文 字 修 整 (trim), 以 及 子 字 符 串 (substring) 不 同 的 数 据 库 对 这 些 函 数 有 不 同 的 语 法, 所 以 最 好 是 参 考 您 所 用 数 据 库 的 信 息, 来 确 定 在 那 个 数 据 库 中, 这 些 函 数 是 如 何 被 运 用 的 COUNT 在 上 一 页 有 提 到, COUNT 是 函 数 之 一 由 于 它 的 使 用 广 泛, 我 们 在 这 里 特 别 提 出 来 讨 论 基 本 上, COUNT 让 我 们 能 够 数 出 在 表 格 中 有 多 少 笔 资 料 被 选 出 来 它 的 语 法 是 : SELECT COUNT(" 栏 位 名 ") FROM " 表 格 名 " 举 例 来 说, 若 我 们 要 找 出 我 们 的 范 例 表 格 中 有 几 笔 store_name 栏 不 是 空 白 的 资 料 时, Store_Information 表 格 38

39 store_name Sales Date Los Angeles $1500 Jan San Diego $250 Jan Los Angeles $300 Jan Boston $700 Jan 我 们 就 键 入, SELECT COUNT(store_name) FROM Store_Information WHERE store_name is not NULL 结 果 : Count(store_name) 4 "is not NULL" 是 " 这 个 栏 位 不 是 空 白 " 的 意 思 COUNT 和 DISTINCT 经 常 被 合 起 来 使 用, 目 的 是 找 出 表 格 中 有 多 少 笔 不 同 的 资 料 ( 至 于 这 些 资 料 实 际 上 是 什 么 并 不 重 要 ) 举 例 来 说, 如 果 我 们 要 找 出 我 们 的 表 格 中 有 多 少 个 不 同 的 store_name, 我 们 就 键 入, SELECT COUNT(DISTINCT store_name) FROM Store_Information 结 果 : Count(DISTINCT store_name) 3 GROUP BY 我 们 现 在 回 到 函 数 上 记 得 我 们 用 SUM 这 个 指 令 来 算 出 所 有 的 Sales ( 营 业 额 ) 吧! 如 果 我 们 的 需 求 变 成 是 要 算 出 每 一 间 店 (store_name) 的 营 业 额 (sales), 那 怎 么 办 呢? 在 这 个 情 况 下, 我 们 要 做 到 两 件 事 : 第 一, 我 们 对 于 store_name 及 Sales 这 两 个 栏 位 都 要 选 出 第 二, 我 们 需 要 确 认 所 有 的 sales 都 要 依 照 各 个 store_name 来 分 开 算 这 个 语 法 为 : SELECT " 栏 位 1", SUM(" 栏 位 2") FROM " 表 格 名 " GROUP BY " 栏 位 1" 在 我 们 的 范 例 上, Store_Information 表 格 39

40 store_name Sales Date Los Angeles $1500 Jan San Diego $250 Jan Los Angeles $300 Jan Boston $700 Jan 我 们 就 键 入, SELECT store_name, SUM(Sales) FROM Store_Information GROUP BY store_name 结 果 : store_name SUM(Sales) Los Angeles $1800 San Diego $250 Boston $700 当 我 们 选 不 只 一 个 栏 位, 且 其 中 至 少 一 个 栏 位 有 包 含 函 数 的 运 用 时, 我 们 就 需 要 用 到 GROUP BY 这 个 指 令 在 这 个 情 况 下, 我 们 需 要 确 定 我 们 有 GROUP BY 所 有 其 他 的 栏 位 换 句 话 说, 除 了 有 包 括 函 数 的 栏 位 外, 我 们 都 需 要 将 其 放 在 GROUP BY 的 子 句 中 HAVING 那 我 们 如 何 对 函 数 产 生 的 值 来 设 定 条 件 呢? 举 例 来 说, 我 们 可 能 只 需 要 知 道 哪 些 店 的 营 业 额 有 超 过 $1,500 在 这 个 情 况 下, 我 们 不 能 使 用 WHERE 的 指 令 那 要 怎 么 办 呢? 很 幸 运 地,SQL 有 提 供 一 个 HAVING 的 指 令, 而 我 们 就 可 以 用 这 个 指 令 来 达 到 这 个 目 标 HAVING 子 句 通 常 是 在 一 个 SQL 句 子 的 最 后 一 个 含 有 HAVING 子 句 的 SQL 并 不 一 定 要 包 含 GROUP BY 子 句 HAVING 的 语 法 如 下 : SELECT " 栏 位 1", SUM(" 栏 位 2") FROM " 表 格 名 " GROUP BY " 栏 位 1" HAVING ( 函 数 条 件 ) 请 读 者 注 意 : 如 果 被 SELECT 的 只 有 函 数 栏, 那 就 不 需 要 GROUP BY 子 句 在 我 们 Store_Information 表 格 这 个 例 子 中, Store_Information 表 格 store_name Sales Date 40

41 Los Angeles $1500 Jan San Diego $250 Jan Los Angeles $300 Jan Boston $700 Jan 若 我 们 要 找 出 Sales 大 于 $1,500 的 store_name, 我 们 就 键 入, SELECT store_name, SUM(sales) FROM Store_Information GROUP BY store_name HAVING SUM(sales) > 1500 结 果 : store_name SUM(Sales) Los Angeles $1800 ALIAS 接 下 来, 我 们 讨 论 alias ( 别 名 ) 在 SQL 上 的 用 处 最 常 用 到 的 别 名 有 两 种 : 栏 位 别 名 及 表 格 别 名 简 单 地 来 说, 栏 位 别 名 的 目 的 是 为 了 让 SQL 产 生 的 结 果 易 读 在 之 前 的 例 子 中, 每 当 我 们 有 营 业 额 总 合 时, 栏 位 名 都 是 SUM(sales) 虽 然 在 这 个 情 况 下 没 有 什 么 问 题, 可 是 如 果 这 个 栏 位 不 是 一 个 简 单 的 总 合, 而 是 一 个 复 杂 的 计 算, 那 栏 位 名 就 没 有 这 么 易 懂 了 若 我 们 用 栏 位 别 名 的 话, 就 可 以 确 认 结 果 中 的 栏 位 名 是 简 单 易 懂 的 第 二 种 别 名 是 表 格 别 名 要 给 一 个 表 格 取 一 个 别 名, 只 要 在 FROM 子 句 中 的 表 格 名 后 空 一 格, 然 后 再 列 出 要 用 的 表 格 别 名 就 可 以 了 这 在 我 们 要 用 SQL 由 数 个 不 同 的 表 格 中 获 取 资 料 时 是 很 方 便 的 这 一 点 我 们 在 之 后 谈 到 连 接 (join) 时 会 看 到 我 们 先 来 看 一 下 栏 位 别 名 和 表 格 别 名 的 语 法 : SELECT " 表 格 别 名 "." 栏 位 1" " 栏 位 别 名 " FROM " 表 格 名 " " 表 格 别 名 " 基 本 上, 这 两 种 别 名 都 是 放 在 它 们 要 替 代 的 物 件 后 面, 而 它 们 中 间 由 一 个 空 白 分 开 我 们 继 续 使 用 Store_Information 这 个 表 格 来 做 例 子 : Store_Information 表 格 store_name Sales Date Los Angeles $1500 Jan

42 San Diego $250 Jan Los Angeles $300 Jan Boston $700 Jan 我 们 用 跟 SQL GROUP BY 那 一 页 一 样 的 例 子 这 里 的 不 同 处 是 我 们 加 上 了 栏 位 别 名 以 及 表 格 别 名 : SELECT A1.store_name Store, SUM(A1.Sales) "Total Sales" FROM Store_Information A1 GROUP BY A1.store_name 结 果 : Store Total Sales Los Angeles $1800 San Diego $250 Boston $700 在 结 果 中, 资 料 本 身 没 有 不 同 不 同 的 是 栏 位 的 标 题 这 是 运 用 栏 位 别 名 的 结 果 在 第 二 个 栏 位 上, 原 本 我 们 的 标 题 是 "Sum(Sales)", 而 现 在 我 们 有 一 个 很 清 楚 的 "Total Sales" 很 明 显 地, "Total Sales" 能 够 比 "Sum(Sales)" 更 精 确 地 阐 述 这 个 栏 位 的 含 意 用 表 格 别 名 的 好 处 在 这 里 并 没 有 显 现 出 来, 不 过 这 在 下 一 页 就 会 很 清 楚 了 表 格 链 接 现 在 我 们 介 绍 连 接 (join) 的 概 念 要 了 解 连 接, 我 们 需 要 用 到 许 多 我 们 之 前 已 介 绍 过 的 指 令 我 们 先 假 设 我 们 有 以 下 的 两 个 表 格, Store_Information 表 格 store_name Sales Date Los Angeles $1500 Jan San Diego $250 Jan Los Angeles $300 Jan Boston $700 Jan Geography 表 格 region_name store_name East Boston East New York West Los Angeles 42

43 West San Diego 而 我 们 要 知 道 每 一 区 (region_name) 的 营 业 额 (sales) Geography 这 个 表 格 告 诉 我 们 每 一 区 有 哪 些 店, 而 Store_Information 告 诉 我 们 每 一 个 店 的 营 业 额 若 我 们 要 知 道 每 一 区 的 营 业 额, 我 们 需 要 将 这 两 个 不 同 表 格 中 的 资 料 串 联 起 来 当 我 们 仔 细 了 解 这 两 个 表 格 后, 我 们 会 发 现 它 们 可 经 由 一 个 相 同 的 栏 位, store_name, 连 接 起 来 我 们 先 将 SQL 句 列 出, 之 后 再 讨 论 每 一 个 子 句 的 意 义 : SELECT A1.region_name REGION, SUM(A2.Sales) SALES FROM Geography A1, Store_Information A2 WHERE A1.store_name = A2.store_name GROUP BY A1.region_name 结 果 : REGION SALES East $700 West $2050 在 第 一 行 中, 我 们 告 诉 SQL 去 选 出 两 个 栏 位 : 第 一 个 栏 位 是 Geography 表 格 中 的 region_name 栏 位 ( 我 们 取 了 一 个 别 名 叫 做 REGION); 第 二 个 栏 位 是 Store_Information 表 格 中 的 sales 栏 位 ( 别 名 为 SALES) 请 注 意 在 这 里 我 们 有 用 到 表 格 别 名 :Geography 表 格 的 别 名 是 A1,Store_Information 表 格 的 别 名 是 A2 若 我 们 没 有 用 表 格 别 名 的 话, 第 一 行 就 会 变 成 SELECT Geography.region_name REGION, SUM(Store_Information.Sales) SALES 很 明 显 地, 这 就 复 杂 多 了 在 这 里 我 们 可 以 看 到 表 格 别 名 的 功 用 : 它 能 让 SQL 句 容 易 被 了 解, 尤 其 是 这 个 SQL 句 含 盖 好 几 个 不 同 的 表 格 时 接 下 来 我 们 看 第 三 行, 就 是 WHERE 子 句 这 是 我 们 阐 述 连 接 条 件 的 地 方 在 这 里, 我 们 要 确 认 Geography 表 格 中 store_name 栏 位 的 值 与 Store_Information 表 格 中 store_name 栏 位 的 值 是 相 等 的 这 个 WHERE 子 句 是 一 个 连 接 的 灵 魂 人 物, 因 为 它 的 角 色 是 确 定 两 个 表 格 之 间 的 连 接 是 正 确 的 如 果 WHERE 子 句 是 错 误 的, 我 们 就 极 可 能 得 到 一 个 笛 卡 儿 连 接 (Cartesian join) 笛 卡 儿 连 接 会 造 成 我 们 得 到 所 有 两 个 表 格 每 两 行 之 间 所 有 可 能 的 组 合 在 这 个 例 子 中, 笛 卡 儿 连 接 会 让 我 们 得 到 4 x 4 = 16 行 的 结 果 外 部 链 接 之 前 我 们 看 到 的 左 连 接 (left join), 又 称 内 部 连 接 (inner join) 在 这 个 情 况 下, 要 两 个 表 格 内 都 有 同 样 的 值, 那 一 笔 资 料 才 会 被 选 出 那 如 果 我 们 想 要 列 43

44 出 一 个 表 格 中 每 一 笔 的 资 料, 无 论 它 的 值 在 另 一 个 表 格 中 有 没 有 出 现, 那 该 怎 么 办 呢? 在 这 个 时 候, 我 们 就 需 要 用 到 SQL OUTER JOIN ( 外 部 连 接 ) 的 指 令 外 部 连 接 的 语 法 是 依 数 据 库 的 不 同 而 有 所 不 同 的 举 例 来 说, 在 Oracle 上, 我 们 会 在 WHERE 子 句 中 要 选 出 所 有 资 料 的 那 个 表 格 之 后 加 上 一 个 "(+)" 来 代 表 说 这 个 表 格 中 的 所 有 资 料 我 们 都 要 假 设 我 们 有 以 下 的 两 个 表 格 : Store_Information 表 格 store_name Sales Date Los Angeles $1500 Jan San Diego $250 Jan Los Angeles $300 Jan Boston $700 Jan Geography 表 格 region_name store_name East Boston East New York West Los Angeles West San Diego 我 们 需 要 知 道 每 一 间 店 的 营 业 额 如 果 我 们 用 一 个 普 通 的 连 接, 我 们 将 会 漏 失 掉 'New York' 这 个 店, 因 为 它 并 不 存 在 于 Store_Information 这 个 表 格 所 以, 在 这 个 情 况 下, 我 们 需 要 用 外 部 连 接 来 串 联 这 两 个 表 格 : SELECT A1.store_name, SUM(A2.Sales) SALES FROM Georgraphy A1, Store_Information A2 WHERE A1.store_name = A2.store_name (+) GROUP BY A1.store_name 我 们 在 这 里 是 使 用 了 Oracle 的 外 部 连 接 语 法 结 果 : store_name SALES Boston $700 New York Los Angeles $1800 San Diego $250 44

45 请 注 意 : 当 第 二 个 表 格 没 有 相 对 的 资 料 时, SQL 会 传 回 NULL 值 在 这 一 个 例 子 中, 'New York' 并 不 存 在 于 Store_Information 表 格, 所 以 它 的 "SALES" 栏 位 是 NULL. CONCATENATE 有 的 时 候, 我 们 有 需 要 将 由 不 同 栏 位 获 得 的 资 料 串 连 在 一 起 每 一 种 数 据 库 都 有 提 供 方 法 来 达 到 这 个 目 的 : MySQL: CONCAT() Oracle: CONCAT(), SQL Server: + CONCAT() 的 语 法 如 下 : CONCAT( 字 符 串 1, 字 符 串 2, 字 符 串 3,...): 将 字 符 串 1 字 符 串 2 字 符 串 3, 等 字 符 串 连 在 一 起 请 注 意,Oracle 的 CONCAT() 只 允 许 两 个 参 数 ; 换 言 之, 一 次 只 能 将 两 个 字 符 串 串 连 起 来 不 过, 在 Oracle 中, 我 们 可 以 用 ' ' 来 一 次 串 连 多 个 字 符 串 来 看 一 个 例 子 假 设 我 们 有 以 下 的 表 格 : Geography 表 格 region_name store_name East Boston East New York West Los Angeles West San Diego 例 子 1: MySQL/Oracle: SELECT CONCAT(region_name,store_name) FROM Geography WHERE store_name = 'Boston'; 结 果 : 'EastBoston' 例 子 2: Oracle: SELECT region_name ' ' store_name FROM Geography WHERE store_name = 'Boston'; 结 果 : 45

46 'East Boston' 例 子 3: SQL Server: SELECT region_name + ' ' + store_name FROM Geography WHERE store_name = 'Boston'; 结 果 : 'East Boston' SUBSTRING SQL 中 的 substring 函 数 是 用 来 抓 出 一 个 栏 位 资 料 中 的 其 中 一 部 分 这 个 函 数 的 名 称 在 不 同 的 数 据 库 中 不 完 全 一 样 : MySQL: SUBSTR(), SUBSTRING() Oracle: SUBSTR() SQL Server: SUBSTRING() 最 常 用 到 的 方 式 如 下 ( 在 这 里 我 们 用 SUBSTR() 为 例 ): SUBSTR(str,pos): 由 <str> 中, 选 出 所 有 从 第 <pos> 位 置 开 始 的 字 符 请 注 意, 这 个 语 法 不 适 用 于 SQL Server 上 SUBSTR(str,pos,len): 由 <str> 中 的 第 <pos> 位 置 开 始, 选 出 接 下 去 的 <len> 个 字 符 假 设 我 们 有 以 下 的 表 格 : Geography 表 格 region_name store_name East Boston East New York West Los Angeles West San Diego 例 1: SELECT SUBSTR(store_name, 3) FROM Geography WHERE store_name = 'Los Angeles'; 结 果 : 's Angeles' 例 2: 46

47 SELECT SUBSTR(store_name,2,4) FROM Geography WHERE store_name = 'San Diego'; 结 果 : 'an D' TRIM SQL 中 的 TRIM 函 数 是 用 来 移 除 掉 一 个 字 符 串 中 的 字 头 或 字 尾 最 常 见 的 用 途 是 移 除 字 首 或 字 尾 的 空 白 这 个 函 数 在 不 同 的 数 据 库 中 有 不 同 的 名 称 : MySQL: TRIM(), RTRIM(), LTRIM() Oracle: RTRIM(), LTRIM() SQL Server: RTRIM(), LTRIM() 各 种 trim 函 数 的 语 法 如 下 : TRIM([[ 位 置 ] [ 要 移 除 的 字 符 串 ] FROM ] 字 符 串 ): [ 位 置 ] 的 可 能 值 为 LEADING ( 起 头 ), TRAILING ( 结 尾 ), or BOTH ( 起 头 及 结 尾 ) 这 个 函 数 将 把 [ 要 移 除 的 字 符 串 ] 从 字 符 串 的 起 头 结 尾, 或 是 起 头 及 结 尾 移 除 如 果 我 们 没 有 列 出 [ 要 移 除 的 字 符 串 ] 是 什 么 的 话, 那 空 白 就 会 被 移 除 LTRIM( 字 符 串 ): 将 所 有 字 符 串 起 头 的 空 白 移 除 RTRIM( 字 符 串 ): 将 所 有 字 符 串 结 尾 的 空 白 移 除 例 1: SELECT TRIM(' Sample '); 结 果 : 'Sample' 例 2: SELECT LTRIM(' Sample '); 结 果 : 'Sample ' 例 3: SELECT RTRIM(' Sample '); 结 果 : ' Sample' 47

48 3.1.2 表 处 理 CREATE TABLE 表 格 是 数 据 库 中 储 存 资 料 的 基 本 架 构 在 绝 大 部 份 的 情 况 下, 数 据 库 厂 商 不 可 能 知 道 您 需 要 如 何 储 存 您 的 资 料, 所 以 通 常 您 会 需 要 自 己 在 数 据 库 中 建 立 表 格 虽 然 许 多 数 据 库 工 具 可 以 让 您 在 不 需 用 到 SQL 的 情 况 下 建 立 表 格, 不 过 由 于 表 格 是 一 个 最 基 本 的 架 构, 我 们 决 定 包 括 CREATE TABLE 的 语 法 在 这 个 网 站 中 在 我 们 跳 入 CREATE TABLE 的 语 法 之 前, 我 们 最 好 先 对 表 格 这 个 东 西 有 些 多 一 点 的 了 解 表 格 被 分 为 栏 位 (column) 及 列 位 (row) 每 一 列 代 表 一 笔 资 料, 而 每 一 栏 代 表 一 笔 资 料 的 一 部 份 举 例 来 说, 如 果 我 们 有 一 个 记 载 顾 客 资 料 的 表 格, 那 栏 位 就 有 可 能 包 括 姓 名 地 址 城 市 国 家 生 日??? 等 等 当 我 们 对 表 格 下 定 义 时, 我 们 需 要 注 明 栏 位 的 标 题, 以 及 那 个 栏 位 的 资 料 种 类 那, 资 料 种 类 是 什 么 呢? 资 料 可 能 是 以 许 多 不 同 的 形 式 存 在 的 它 可 能 是 一 个 整 数 ( 例 如 1), 一 个 实 数 ( 例 如 0.55) 一 个 字 符 串 ( 例 如 'sql') 一 个 日 期 / 时 间 ( 例 如 '2000-JAN-25 03:22:22') 或 甚 至 是 以 二 进 法 (binary) 的 状 态 存 在 当 我 们 在 对 一 个 表 格 下 定 义 时, 我 们 需 要 对 每 一 个 栏 位 的 资 料 种 类 下 定 义 ( 例 如 ' 姓 ' 这 个 栏 位 的 资 料 种 类 是 char(50) 代 表 这 是 一 个 50 个 字 符 的 字 符 串 ) 我 们 需 要 注 意 的 一 点 是 不 同 的 数 据 库 有 不 同 的 资 料 种 类, 所 以 在 对 表 格 做 出 定 义 之 前 最 好 先 参 考 一 下 数 据 库 本 身 的 说 明 CREATE TABLE 的 语 法 是 : CREATE TABLE " 表 格 名 " (" 栏 位 1" " 栏 位 1 资 料 种 类 ", " 栏 位 2" " 栏 位 2 资 料 种 类 ",... ) 若 我 们 要 建 立 我 们 上 面 提 过 的 顾 客 表 格, 我 们 就 键 入 以 下 的 SQL: CREATE TABLE customer (First_Name char(50), Last_Name char(50), Address char(50), City char(50), Country char(25), Birth_Date date) 48

49 CONSTRAINT 我 们 可 以 限 制 哪 一 些 资 料 可 以 存 入 表 格 中 这 些 限 制 可 以 在 表 格 初 创 时 藉 由 CREATE TABLE 语 句 来 指 定, 或 是 之 后 藉 由 ALTER TABLE 语 句 来 指 定 常 见 的 限 制 有 以 下 几 种 : NOT NULL UNIQUE CHECK 主 键 (Primary Key) 外 来 键 (Foreign Key) 以 下 对 这 几 种 限 制 分 别 做 个 介 绍 : NOT NULL 在 没 有 做 出 任 何 限 制 的 情 况 下, 一 个 栏 位 是 允 许 有 NULL 值 得 如 果 我 们 不 允 许 一 个 栏 位 含 有 NULL 值, 我 们 就 需 要 对 那 个 栏 位 做 出 NOT NULL 的 指 定 举 例 来 说, 在 以 下 的 语 句 中, CREATE TABLE Customer (SID integer NOT NULL, Last_Name varchar (30) NOT NULL, First_Name varchar(30)); "SID" 和 "Last_Name" 这 两 个 栏 位 是 不 允 许 有 NULL 值, 而 "First_Name" 这 个 栏 位 是 可 以 有 NULL 值 得 UNIQUE UNIQUE 限 制 是 保 证 一 个 栏 位 中 的 所 有 资 料 都 是 有 不 一 样 的 值 举 例 来 说, 在 以 下 的 语 句 中, CREATE TABLE Customer (SID integer Unique, Last_Name varchar (30), First_Name varchar(30)); "SID" 栏 位 不 能 有 重 复 值 存 在, 而 "Last_Name" 及 "First_Name" 这 两 个 栏 位 则 是 允 许 有 重 复 值 存 在 49

50 请 注 意, 一 个 被 指 定 为 主 键 的 栏 位 也 一 定 会 含 有 UNIQUE 的 特 性 相 对 来 说, 一 个 UNIQUE 的 栏 位 并 不 一 定 会 是 一 个 主 键 CHECK CHECK 限 制 是 保 证 一 个 栏 位 中 的 所 有 资 料 都 是 符 合 某 些 条 件 举 例 来 说, 在 以 下 的 语 句 中, CREATE TABLE Customer (SID integer CHECK (SID > 0), Last_Name varchar (30), First_Name varchar(30)); "SID" 拦 只 能 包 含 大 于 0 的 整 数 请 注 意,CHECK 限 制 目 前 尚 未 被 执 行 于 MySQL 数 据 库 上 主 键 and 外 来 键 将 于 下 两 页 中 讨 论 主 键 主 键 (Primary Key) 中 的 每 一 笔 资 料 都 是 表 格 中 的 唯 一 值 换 言 之, 它 是 用 来 独 一 无 二 地 确 认 一 个 表 格 中 的 每 一 行 资 料 主 键 可 以 是 原 本 资 料 内 的 一 个 栏 位, 或 是 一 个 人 造 栏 位 ( 与 原 本 资 料 没 有 关 系 的 栏 位 ) 主 键 可 以 包 含 一 或 多 个 栏 位 当 主 键 包 含 多 个 栏 位 时, 称 为 组 合 键 (Composite Key) 主 键 可 以 在 建 置 新 表 格 时 设 定 ( 运 用 CREATE TABLE 语 句 ), 或 是 以 改 变 现 有 的 表 格 架 构 方 式 设 定 ( 运 用 ALTER TABLE) 以 下 举 几 个 在 建 置 新 表 格 时 设 定 主 键 的 方 式 : MySQL: CREATE TABLE Customer (SID integer, Last_Name varchar(30), First_Name varchar(30), PRIMARY KEY (SID)); Oracle: CREATE TABLE Customer (SID integer PRIMARY KEY, Last_Name varchar(30), 50

51 First_Name varchar(30)); SQL Server: CREATE TABLE Customer (SID integer PRIMARY KEY, Last_Name varchar(30), First_Name varchar(30)); 以 下 则 是 以 改 变 现 有 表 格 架 构 来 设 定 主 键 的 方 式 : MySQL: ALTER TABLE Customer ADD PRIMARY KEY (SID); Oracle: ALTER TABLE Customer ADD PRIMARY KEY (SID); SQL Server: ALTER TABLE Customer ADD PRIMARY KEY (SID); 请 注 意, 在 用 ALTER TABLE 语 句 来 添 加 主 键 之 前, 我 们 需 要 确 认 被 用 来 当 做 主 键 的 栏 位 是 设 定 为 NOT NULL ; 也 就 是 说, 那 个 栏 位 一 定 不 能 没 有 资 料 外 来 键 外 来 键 是 一 个 ( 或 数 个 ) 指 向 另 外 一 个 表 格 主 键 的 栏 位 外 来 键 的 目 的 是 确 定 资 料 的 参 考 完 整 性 (referential integrity) 换 言 之, 只 有 被 准 许 的 资 料 值 才 会 被 存 入 数 据 库 内 举 例 来 说, 假 设 我 们 有 两 个 表 格 : 一 个 CUSTOMER 表 格, 里 面 记 录 了 所 有 顾 客 的 资 料 ; 另 一 个 ORDERS 表 格, 里 面 记 录 了 所 有 顾 客 订 购 的 资 料 在 这 里 的 一 个 限 制, 就 是 所 有 的 订 购 资 料 中 的 顾 客, 都 一 定 是 要 跟 在 CUSTOMER 表 格 中 存 在 在 这 里, 我 们 就 会 在 ORDERS 表 格 中 设 定 一 个 外 来 键, 而 这 个 外 来 键 是 指 向 CUSTOMER 表 格 中 的 主 键 这 样 一 来, 我 们 就 可 以 确 定 所 有 在 ORDERS 表 格 中 的 顾 客 都 存 在 CUSTOMER 表 格 中 换 句 话 说,ORDERS 表 格 之 中, 不 能 有 任 何 顾 客 是 不 存 在 于 CUSTOMER 表 格 中 的 资 料 这 两 个 表 格 的 结 构 将 会 是 如 下 : CUSTOMER 表 格 栏 位 名 性 质 SID 主 键 Last_Name First_Name 51

52 ORDERS 表 格 栏 位 名 性 质 Order_ID 主 键 Order_Date Customer_SID 外 来 键 Amount 在 以 上 的 例 子 中,ORDERS 表 格 中 的 customer_sid 栏 位 是 一 个 指 向 CUSTOMERS 表 格 中 SID 栏 位 的 外 来 键 以 下 列 出 几 个 在 建 置 ORDERS 表 格 时 指 定 外 来 键 的 方 式 : MySQL: CREATE TABLE ORDERS (Order_ID integer, Order_Date date, Customer_SID integer, Amount double, Primary Key (Order_ID), Foreign Key (Customer_SID) references CUSTOMER(SID)); Oracle: CREATE TABLE ORDERS (Order_ID integer primary key, Order_Date date, Customer_SID integer references CUSTOMER(SID), Amount double); SQL Server: CREATE TABLE ORDERS (Order_ID integer primary key, Order_Date datetime, Customer_SID integer references CUSTOMER(SID), Amount double); 以 下 的 例 子 则 是 藉 着 改 变 表 格 架 构 来 指 定 外 来 键 这 里 假 设 ORDERS 表 格 已 经 被 建 置, 而 外 来 键 尚 未 被 指 定 : MySQL: ALTER TABLE ORDERS ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(sid); 52

53 Oracle: ALTER TABLE ORDERS ADD (CONSTRAINT fk_orders1) FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(sid); SQL Server: ALTER TABLE ORDERS ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(sid); CREATE VIEW 视 观 表 (Views) 可 以 被 当 作 是 虚 拟 表 格 它 跟 表 格 的 不 同 是, 表 格 中 有 实 际 储 存 资 料, 而 视 观 表 是 建 立 在 表 格 之 上 的 一 个 架 构, 它 本 身 并 不 实 际 储 存 资 料 建 立 一 个 视 观 表 的 语 法 如 下 : CREATE VIEW "VIEW_NAME" AS "SQL 语 句 " "SQL 语 句 " 可 以 是 任 何 一 个 我 们 在 这 个 教 材 中 有 提 到 的 SQL 来 看 一 个 例 子 假 设 我 们 有 以 下 的 表 格 : TABLE Customer (First_Name char(50), Last_Name char(50), Address char(50), City char(50), Country char(25), Birth_Date date) 若 要 在 这 个 表 格 上 建 立 一 个 包 括 First_Name, Last_Name, 和 Country 这 三 个 栏 位 的 视 观 表, 我 们 就 打 入, CREATE VIEW V_Customer AS SELECT First_Name, Last_Name, Country FROM Customer 现 在, 我 们 就 有 一 个 叫 做 V_Customer 的 视 观 表 : View V_Customer (First_Name char(50), Last_Name char(50), Country char(25)) 53

54 我 们 也 可 以 用 视 观 表 来 连 接 两 个 表 格 在 这 个 情 况 下, 使 用 者 就 可 以 直 接 由 一 个 视 观 表 中 找 出 她 要 的 信 息, 而 不 需 要 由 两 个 不 同 的 表 格 中 去 做 一 次 连 接 的 动 作 假 设 有 以 下 的 两 个 表 格 : Store_Information 表 格 store_name Sales Date Los Angeles $1500 Jan San Diego $250 Jan Los Angeles $300 Jan Boston $700 Jan Geography 表 格 region_name store_name East Boston East New York West Los Angeles West San Diego 我 们 就 可 以 用 以 下 的 指 令 来 建 一 个 包 括 每 个 地 区 (region) 销 售 额 (sales) 的 视 观 表 : CREATE VIEW V_REGION_SALES AS SELECT A1.region_name REGION, SUM(A2.Sales) SALES FROM Geography A1, Store_Information A2 WHERE A1.store_name = A2.store_name GROUP BY A1.region_name 这 就 给 我 们 有 一 个 名 为 V_REGION_SALES 的 视 观 表 这 个 视 观 表 包 含 不 同 地 区 的 销 售 哦 如 果 我 们 要 从 这 个 视 观 表 中 获 取 资 料, 我 们 就 打 入, SELECT * FROM V_REGION_SALES 结 果 : REGION SALES East $700 West $2050 CREATE INDEX 索 引 (Index) 可 以 帮 助 我 们 从 表 格 中 快 速 地 找 到 需 要 的 资 料 举 例 来 说, 假 设 我 们 要 在 一 本 园 艺 书 中 找 如 何 种 植 青 椒 的 讯 息 若 这 本 书 没 有 索 引 的 话, 那 我 们 是 必 须 要 从 头 开 始 读, 直 到 我 们 找 到 有 关 种 直 青 椒 的 地 方 为 止 若 这 本 书 有 索 引 54

55 的 话, 我 们 就 可 以 先 去 索 引 找 出 种 植 青 椒 的 信 息 是 在 哪 一 页, 然 后 直 接 到 那 一 页 去 阅 读 很 明 显 地, 运 用 索 引 是 一 种 有 效 且 省 时 的 方 式 从 数 据 库 表 格 中 寻 找 资 料 也 是 同 样 的 原 理 如 果 一 个 表 格 没 有 索 引 的 话, 数 据 库 系 统 就 需 要 将 整 个 表 格 的 资 料 读 出 ( 这 个 过 程 叫 做 'table scan') 若 有 适 当 的 索 引 存 在, 数 据 库 系 统 就 可 以 先 由 这 个 索 引 去 找 出 需 要 的 资 料 是 在 表 格 的 什 么 地 方, 然 后 直 接 去 那 些 地 方 抓 资 料 这 样 子 速 度 就 快 多 了 因 此, 在 表 格 上 建 立 索 引 是 一 件 有 利 于 系 统 效 率 的 事 一 个 索 引 可 以 涵 盖 一 或 多 个 栏 位 建 立 索 引 的 语 法 如 下 : CREATE INDEX "INDEX_NAME" ON "TABLE_NAME" (COLUMN_NAME) 现 在 假 设 我 们 有 以 下 这 个 表 格, TABLE Customer (First_Name char(50), Last_Name char(50), Address char(50), City char(50), Country char(25), Birth_Date date) 若 我 们 要 在 Last_Name 这 个 栏 位 上 建 一 个 索 引, 我 们 就 打 入 以 下 的 指 令, CREATE INDEX IDX_CUSTOMER_LAST_NAME on CUSTOMER (Last_Name) 我 们 要 在 City 及 Country 这 两 个 栏 位 上 建 一 个 索 引, 我 们 就 打 入 以 下 的 指 令, CREATE INDEX IDX_CUSTOMER_LOCATION on CUSTOMER (City, Country) 索 引 的 命 名 并 没 有 一 个 固 定 的 方 式 通 常 会 用 的 方 式 是 在 名 称 前 加 一 个 字 首, 例 如 "IDX_", 来 避 免 与 数 据 库 中 的 其 他 物 件 混 淆 另 外, 在 索 引 名 之 内 包 括 表 格 名 及 栏 位 名 也 是 一 个 好 的 方 式 请 读 者 注 意, 每 个 数 据 库 会 有 它 本 身 的 CREATE INDEX 语 法, 而 不 同 数 据 库 的 语 法 会 有 不 同 因 此, 在 下 指 令 前, 请 先 由 数 据 库 使 用 手 册 中 确 认 正 确 的 语 法 ALTER TABLE 在 表 格 被 建 立 在 数 据 库 中 后, 我 们 常 常 会 发 现, 这 个 表 格 的 结 构 需 要 有 所 改 变 常 见 的 改 变 如 下 : 加 一 个 栏 位 55