幻灯片 1

Transcription

1 幻 灯 片 1 从 多 表 中 显 示 数 据 Copyright Oracle Corporation, All rights reserved. 进 度 表 : 时 间 主 题 55 分 钟 讲 演 55 分 钟 练 习 110 分 钟 总 共

2 幻 灯 片 2 目 标 完 成 本 课 后, 您 应 当 能 够 执 行 下 列 操 作 : 写 SELECT 语 句 使 用 等 值 和 非 等 值 连 接 从 多 个 表 中 访 问 数 据 使 用 外 连 接 查 看 不 满 足 连 接 条 件 的 数 据 使 用 一 个 自 连 接, 连 接 一 个 表 到 它 自 己 4-2 Copyright Oracle Corporation, All rights reserved. 课 程 目 标 本 课 学 习 怎 样 从 多 个 表 中 获 得 数 据

3 幻 灯 片 3 从 多 表 中 获 得 数 据 EMPLOYEES DEPARTMENTS 4-3 Copyright Oracle Corporation, All rights reserved. 来 自 多 表 的 数 据 有 时 你 需 要 使 用 来 自 多 表 的 数 据 在 幻 灯 片 中, 报 告 显 示 了 来 自 单 独 的 两 个 表 的 数 据 数 据 Employee ID 在 EMPLOYEES 表 中 Department ID 在 EMPLOYEES 和 DEPARTMENTS 表 中 都 有 Location IDs 在 DEPARTMENTS 表 中 为 了 生 成 报 告, 你 需 要 连 接 EMPLOYEES 和 DEPARTMENTS 表, 并 从 两 个 表 中 访 问

4 幻 灯 片 4 笛 卡 尔 乘 积 笛 卡 尔 乘 积 的 形 成, 当 : 一 个 连 接 条 件 被 遗 漏 时 一 个 连 接 条 件 不 正 确 时 在 第 一 个 表 中 的 所 有 行 被 连 接 到 第 二 个 表 的 所 有 行 时 为 了 避 免 笛 卡 尔 乘 积 的 形 成, 在 WHERE 子 句 中 应 当 总 是 包 含 正 确 的 连 接 条 件 4-4 Copyright Oracle Corporation, All rights reserved. 笛 卡 尔 乘 积 当 一 个 连 接 条 件 无 效 或 被 遗 漏 时, 其 结 果 是 一 个 笛 卡 尔 乘 积 (Cartesian product), 其 中 所 有 行 的 组 合 都 被 显 示 第 一 个 表 中 的 所 有 行 连 接 到 第 二 个 表 中 的 所 有 行 一 个 笛 卡 尔 乘 积 会 产 生 大 量 的 行, 其 结 果 没 有 什 么 用 你 应 该 在 WHERE 子 句 中 始 终 包 含 一 个 有 效 的 连 接 条 件, 除 非 你 有 特 殊 的 需 求, 需 要 从 所 有 表 中 组 合 所 有 的 行 对 于 一 些 测 试 笛 卡 尔 乘 积 是 有 用 的, 例 如 你 需 要 产 生 大 量 的 行 来 模 拟 一 个 相 当 大 的 数 据 量

5 幻 灯 片 5 笛 卡 尔 乘 积 的 产 生 EMPLOYEES (20 行 ) DEPARTMENTS (8 行 ) 笛 卡 尔 乘 积 : 20x8=160 行 4-5 Copyright Oracle Corporation, All rights reserved. 笛 卡 尔 乘 积 ( 续 ) 如 果 连 接 条 件 被 遗 漏, 就 会 产 生 笛 卡 尔 乘 积 幻 灯 片 中 的 例 子 从 EMPLOYEES 和 DEPARTMENTS 表 中 显 示 雇 员 的 名 字 和 部 门 名 字 因 为 无 WHERE 子 句 被 指 定, EMPLOYEES 表 中 所 有 的 行 (20 行 ) 被 与 DEPARTMENTS 表 中 的 所 有 行 (8 行 ) 连 接, 因 此 产 生 160 行 的 输 出 SELECT last_name, department_name dept_name FROM employees, departments;

6 幻 灯 片 6 连 接 的 类 型 Oracle 所 有 的 连 接 (8i 以 前 ): Equijoin 等 值 Non-equijoin 非 等 值 Outer join 外 连 接 Self join 自 连 接 SQL: 1999 适 应 连 接 : Cross joins 交 叉 连 接 Natural joins 自 然 连 接 Using clause 使 用 子 句 Full or two sided outer joins 全 连 接 或 双 向 外 连 接 Arbitrary join conditions for outer joins 对 于 外 连 接 的 任 意 连 接 条 件 4-6 Copyright Oracle Corporation, All rights reserved. 连 接 的 类 型 Oracle9i 数 据 库 提 供 SQL: 1999 兼 容 的 连 接 语 法 在 9i 发 布 以 前, 连 接 语 法 不 同 于 ANSI 标 准 新 的 SQL: 1999 兼 容 连 接 语 法 不 提 供 任 何 对 Oracle 以 前 发 布 的 版 本 中 私 有 连 接 语 法 性 能 的 改 进

7 幻 灯 片 7 用 Oracle 语 法 连 接 表 使 用 一 个 连 接 从 多 个 表 中 查 询 数 据 SELECT FROM WHERE table1.column, table2.column table1, table2 table1.column1 = table2.column2; 在 WHERE 子 句 中 写 连 接 条 件 当 多 个 表 中 有 相 同 的 列 名 时, 将 表 名 作 为 列 名 的 前 缀 4-7 Copyright Oracle Corporation, All rights reserved. 定 义 连 接 当 数 据 从 多 表 中 查 询 时, 要 使 用 连 接 (join) 条 件 一 个 表 中 的 行 按 照 存 在 于 相 应 列 中 的 公 值 被 连 接 到 另 一 个 表 中 的 行, 即, 通 常 所 说 的 主 键 和 外 键 列 从 多 个 表 中 显 示 数 据, 在 WHERE 子 句 中 写 一 个 简 单 的 连 接 条 件 在 语 法 中 : table1.column 指 示 获 取 数 据 的 表 和 列 table1.column1 = 是 连 接 表 的 条 件 table2.column2 原 则 在 写 一 个 连 接 表 的 SELECT 语 句 时, 在 列 名 前 面 用 表 名 可 以 使 语 义 清 楚, 并 且 加 快 数 据 库 访 问 如 果 相 同 的 列 名 出 现 在 多 个 表 中, 列 名 必 须 前 缀 表 名 为 了 连 接 n 个 表 在 一 起, 你 最 少 需 要 n-1 个 连 接 条 件 例 如, 为 了 连 接 4 个 表, 最 少 需 要 3 个 连 接 条 件 如 果 表 中 有 一 个 连 接 主 键, 该 规 则 可 能 不 适 用, 其 中 可 能 有 多 行 用 来 唯 一 地 标 识 每 一 行 更 多 信 息, 见 Oracle9i SQL Reference, SELECT

8 幻 灯 片 8 什 么 是 等 值 连 接? EMPLOYEES DEPARTMENTS 外 键 FK 主 键 PK 4-8 Copyright Oracle Corporation, All rights reserved. 等 值 连 接 为 了 确 定 一 个 雇 员 的 部 门 名, 需 要 比 较 EMPLOYEES 表 中 的 DEPARTMENT_ID 列 与 DEPARTMENTS 表 中 的 DEPARTMENT_ID 列 的 值 在 EMPLOYEES 和 DEPARTMENTS 表 之 间 的 关 系 是 一 个 相 等 (equijoin) 关 系, 即, 两 个 表 中 DEPARTMENT_ID 列 的 值 必 须 相 等 通 常, 这 种 连 接 类 型 包 括 主 键 和 外 键 注 : 等 值 连 接 也 被 称 为 简 单 连 接 (simple joins) 或 内 连 接 (inner joins) 教 师 注 释 解 释 抉 择 矩 阵 (decision matrix) 用 于 简 化 写 连 接 的 使 用, 例 如, 如 果 你 想 显 示 同 一 个 部 门 中 所 有 姓 Goyal 的 雇 员 的 名 字 和 部 门 号, 可 以 写 出 下 面 的 决 策 矩 阵 : 显 示 列 源 表 条 件 last_name employees last_name='goyal' department_name departments employees.department_id = departments.department_id 现 在 看 着 上 面 的 抉 择 矩 阵,SQL 语 句 可 以 容 易 地 写 出 第 一 列 给 出 SELECT 语 句 的 字 段 列 表, 第 二 列 给 出 FROM 子 句, 第 三 列 给 出 WHERE 子 句 的 条 件

9 幻 灯 片 9 用 等 值 连 接 返 回 记 录 SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_id FROM employees, departments WHERE employees.department_id = departments.department_id; 4-9 Copyright Oracle Corporation, All rights reserved. 用 等 值 连 接 返 回 记 录 在 幻 灯 片 的 例 子 中 : SELECT 子 句 指 定 要 返 回 的 列 名 : employee last name employee number 和 department number, 这 些 是 EMPLOYEES 表 中 的 列 department number department name 和 location ID, 这 些 是 DEPARTMENTS 表 中 的 列 FROM 子 句 指 定 数 据 库 必 须 访 问 的 两 个 表 : EMPLOYEES 表 DEPARTMENTS 表 WHERE 子 句 指 定 表 怎 样 被 连 接 : EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID 因 为 DEPARTMENT_ID 列 是 两 个 表 的 公 共 列, 它 必 须 用 表 名 做 前 缀 以 避 免 混 淆

10 幻 灯 片 10 使 用 AND 操 作 符 附 加 搜 索 条 件 EMPLOYEES DEPARTMENTS 4-10 Copyright Oracle Corporation, All rights reserved. 添 加 查 询 条 件 除 连 接 之 外, 你 可 能 还 要 求 用 WHERE 子 句 在 连 接 中 限 制 一 个 或 多 个 表 中 的 行 例 如, 为 了 显 示 雇 员 Matos 的 部 门 号 和 部 门 名, 你 需 要 添 加 条 件 到 WHERE 子 句 中 SELECT last_name, employees.department_id, department_name FROM employees, departments WHERE employees.department_id = departments.department_id AND last_name = 'Matos';

11 幻 灯 片 11 限 制 不 明 确 的 列 名 在 多 表 中 使 用 表 前 缀 限 制 修 饰 列 名 用 表 前 缀 改 善 性 能 用 列 别 名 区 别 有 相 同 名 称, 但 在 不 同 表 中 的 列 4-11 Copyright Oracle Corporation, All rights reserved. 限 制 不 明 确 的 列 名 你 需 要 在 WHERE 子 句 中 用 表 的 名 字 限 制 列 的 名 字 以 避 免 含 糊 不 清 没 有 表 前 缀, DEPARTMENT_ID 列 可 能 来 自 DEPARTMENTS 表, 也 可 能 来 自 EMPLOYEES 表, 这 种 情 况 下 需 要 添 加 表 前 缀 来 执 行 查 询 如 果 列 名 在 两 个 表 之 间 不 相 同, 就 不 需 要 限 定 列 但 是, 使 用 表 前 缀 可 以 改 善 性 能, 因 为 你 确 切 地 告 诉 Oracle 服 务 器 在 那 里 找 到 列 必 须 限 定 不 明 确 的 列 名 也 适 用 于 在 其 它 子 句 中 可 能 引 起 混 淆 的 那 些 列, 例 如 SELECT 子 句 或 ORDER BY 子 句

12 幻 灯 片 12 使 用 表 别 名 使 用 表 别 名 简 化 查 询 使 用 表 别 名 改 善 性 能 SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e, departments d WHERE e.department_id = d.department_id; 4-12 Copyright Oracle Corporation, All rights reserved. 表 别 名 用 表 名 限 制 列 名 可 能 是 非 常 耗 时 的, 特 别 是 当 表 名 字 很 长 时, 你 可 以 使 用 表 别 名 代 替 表 名 就 象 列 别 名 给 列 另 一 个 名 字 一 样, 表 别 名 给 表 另 一 个 名 字 表 别 名 有 助 于 保 持 SQL 代 码 较 小, 因 此 使 用 的 存 储 器 也 少 注 意 在 例 子 中 表 的 FROM 子 句 中 怎 样 定 义 表 别 名 表 名 完 全 指 定, 然 后 跟 着 别 名 EMPLOYEES 表 被 给 予 别 名 e,departments 表 被 给 予 别 名 d 原 则 表 别 名 最 多 可 以 有 30 个 字 符, 但 短 一 些 更 好 如 果 在 FROM 子 句 中 表 别 名 被 用 于 指 定 的 表, 那 么 在 整 个 SELECT 语 句 中 都 要 使 用 表 别 名 表 别 名 应 该 是 有 意 义 的 表 别 名 只 对 当 前 的 SELECT 语 句 有 效

13 幻 灯 片 13 EMPLOYEES 多 于 两 个 表 的 连 接 DEPARTMENTS LOCATIONS 为 了 连 接 n 个 表, 你 最 少 需 要 n-1 个 连 接 条 件 例 如, 为 了 连 接 3 个 表, 最 少 需 要 两 个 连 接 4-13 Copyright Oracle Corporation, All rights reserved. 添 加 查 询 条 件 有 时 你 可 能 需 要 连 接 两 个 以 上 的 表 例 如, 为 了 显 示 每 个 雇 员 的 last name department name 和 city, 你 必 须 连 接 EMPLOYEES DEPARTMENTS 和 LOCATIONS 表 SELECT e.last_name, d.department_name, l.city FROM employees e, departments d, locations l WHERE e.department_id = d.department_id AND d.location_id = l.location_id;

14 幻 灯 片 14 非 等 值 连 接 EMPLOYEES JOB_GRADES 在 EMPLOYEES 表 中 的 工 资 必 须 在 JOB_GRADES 表 中 的 最 低 工 资 和 最 高 工 资 之 间 4-14 Copyright Oracle Corporation, All rights reserved. 非 等 值 连 接 一 个 非 等 值 连 接 是 一 种 不 同 于 等 值 操 作 的 连 接 条 件 EMPLOYEES 表 和 JOB_GRADES A 表 之 间 的 关 系 有 一 个 非 等 值 连 接 例 子 在 两 个 表 之 间 的 关 系 是 EMPLOYEES 表 中 的 SALARY 列 必 须 是 JOB_GRADES 表 的 LOWEST_SALARY 和 HIGHEST_SALARY 列 之 间 的 值 使 用 不 同 于 等 于 (=) 的 操 作 符 获 得 关 系

15 幻 灯 片 15 用 非 等 值 连 接 返 回 记 录 SELECT e.last_name, e.salary, j.grade_level FROM employees e, job_grades j WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal; 4-15 Copyright Oracle Corporation, All rights reserved. 非 等 值 连 接 ( 续 ) 幻 灯 片 的 例 子 创 建 一 个 非 等 值 连 接 来 求 一 个 雇 员 的 薪 水 级 别 薪 水 必 须 在 任 何 一 对 最 低 和 最 高 薪 水 范 围 内 要 注 意 的 是 当 查 询 被 执 行 时, 所 有 雇 员 只 出 现 一 次 是 重 要 的 没 有 雇 员 在 列 表 中 重 复 对 此 有 两 个 理 由 : 在 工 作 等 级 表 中, 没 有 行 是 交 迭 的, 即, 一 个 雇 员 的 薪 水 值 只 能 位 于 薪 水 级 别 表 的 最 低 和 最 高 薪 水 值 之 间 所 有 雇 员 的 薪 水 位 于 由 工 作 级 别 表 提 供 的 限 制 中 即, 没 有 雇 员 的 收 入 少 于 LOWEST_SAL 列 所 包 含 的 最 低 值, 或 高 于 HIGHEST_SAL 列 所 包 含 的 最 高 值 注 : 其 它 条 件, 例 如 <= 和 >= 可 以 被 使 用, 但 BETWEEN 是 最 简 单 的 在 使 用 BETWEEN 时 先 指 定 最 低 值 后 指 定 最 高 值 在 幻 灯 片 的 例 子 中 指 定 表 别 名 是 因 为 性 能 的 原 因, 而 不 是 因 为 可 能 产 生 含 糊 教 师 注 释 解 释 BETWEEN AND 实 际 上 被 Oeacle 服 务 器 转 换 为 一 对 AND 条 件 (a >= 最 小 值 ) and (a <= 最 大 值 ),IN ( ) 被 Oracle 服 务 器 转 换 为 一 组 OR 条 件 (a = value1 OR a = value2 OR a = value3 ) 所 以 用 BETWEEN AND IN() 并 没 有 性 能 上 的 提 高 ; 好 处 是 逻 辑 上 简 单

16 幻 灯 片 16 外 连 接 DEPARTMENTS EMPLOYEES 在 部 门 190 中 无 雇 员 4-16 Copyright Oracle Corporation, All rights reserved. 用 外 连 接 返 回 不 直 接 匹 配 的 记 录 如 果 一 个 行 不 满 足 连 接 条 件, 该 行 将 不 出 现 在 查 询 结 果 中 例 如, 在 EMPLOYEES 和 DEPARTMENTS 表 的 等 值 连 接 条 件 中, 雇 员 Grant 不 出 现, 因 为 在 EMPLOYEES 表 中 没 有 她 的 department ID 记 录 在 结 果 集 中 有 20 个 雇 员, 你 只 看 得 见 19 个 记 录 SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id;

17 幻 灯 片 17 外 连 接 语 法 你 可 以 用 一 个 外 连 接 查 看 那 些 不 满 足 连 接 条 件 的 行 外 连 接 运 算 符 是 加 号 (+) SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column(+) = table2.column; SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column = table2.column(+); 4-17 Copyright Oracle Corporation, All rights reserved. 用 外 连 接 返 回 不 直 接 匹 配 的 记 录 如 果 在 连 接 条 件 中 使 用 外 连 接 操 作, 缺 少 的 行 就 可 以 被 返 回 操 作 符 是 一 个 在 圆 括 号 中 的 加 号 (+), 它 被 放 置 在 连 接 的 缺 少 信 息 的 一 侧 为 了 使 来 自 不 完 善 表 的 一 行 或 多 行 能 够 被 连 接, 该 操 作 符 有 产 生 一 个 或 多 个 空 行 的 作 用 在 语 法 中 : table1.column = 是 连 接 表 在 一 起 的 条 件 table2.column (+) 是 外 连 接 符 号, 它 可 以 放 在 WHERE 子 句 的 条 件 的 任 一 边, 但 不 能 两 边 都 放 ( 跟 着 没 有 匹 配 行 的 表 中 列 的 名 字 放 置 外 连 接 符 号 )

18 幻 灯 片 18 使 用 外 连 接 SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id(+) = d.department_id ; 4-18 Copyright Oracle Corporation, All rights reserved. 使 用 外 连 接 返 回 不 直 接 匹 配 的 记 录 ( 续 ) 幻 灯 片 的 例 子 显 示 雇 员 的 last name department ID 和 department names Contracting 部 门 还 没 有 雇 员 空 值 被 显 示 在 输 出 中 外 连 接 约 束 外 连 接 操 作 符 只 能 出 现 在 表 达 式 一 侧 缺 少 信 息 的 一 侧 它 从 一 个 表 中 返 回 那 些 在 另 一 个 表 中 没 有 直 接 匹 配 的 行 包 括 一 个 外 连 接 的 条 件 不 能 用 IN 操 作 符 或 连 接 到 另 一 个 用 OR 操 作 符 的 条 件

19 幻 灯 片 19 自 连 接 EMPLOYEES ( 工 人 ) EMPLOYEES ( 经 理 ) 在 WORKER 表 中 的 MANAGER_ID 等 于 MANAGER 表 中 的 EMPLOYEE_ID 4-19 Copyright Oracle Corporation, All rights reserved. 连 接 一 个 表 到 它 自 己 有 时 你 需 要 连 接 一 个 表 到 它 自 己 为 了 找 到 每 个 雇 员 的 经 理 的 名 字, 你 需 要 连 接 EMPLOYEES 表 到 它 自 己, 或 执 行 一 个 自 连 接 例 如, 为 了 找 到 Whalen 的 经 理 的 名 字, 你 需 要 : 在 EMPLOYEES 的 LAST_NAME 列 找 到 Whalen 在 MANAGER_ID 列 找 到 Whalen 的 经 理 号 Whalen 的 经 理 号 是 101 用 EMPLOYEE_ID 101 在 LAST_NAME 列 找 到 经 理 的 名 字 Kochhar 的 雇 员 号 是 101, 所 以 Kochhar 是 Whalen 的 经 理 在 这 个 过 程 中, 你 要 查 找 表 两 次, 第 一 次 你 在 LAST_NAME 列 中 查 找 Whalen 并 且 在 找 到 对 应 的 MANAGER_ID 列 的 值 101 第 二 次 你 在 EMPLOYEE_ID 列 查 找 101 并 且 在 LAST_NAME 列 找 到 Kochhar 教 师 注 释 从 EMPLOYEES 表 显 示 数 据 并 且 指 出 每 个 经 理 同 时 也 是 雇 员

20 幻 灯 片 20 连 接 一 个 表 到 它 本 身 SELECT worker.last_name ' works for ' manager.last_name FROM employees worker, employees manager WHERE worker.manager_id = manager.employee_id ; 4-20 Copyright Oracle Corporation, All rights reserved. 连 接 一 个 表 到 它 自 己 ( 续 ) 幻 灯 片 的 例 子 连 接 EMPLOYEES 表 到 它 自 己 为 了 在 FROM 子 句 中 模 拟 两 个 表, 对 于 相 同 的 表 EMPLOYEES, 用 两 个 别 名, 分 别 为 w 和 m 在 该 例 中,WHERE 子 句 包 含 的 连 接 意 味 着 一 个 工 人 的 经 理 号 匹 配 该 经 理 的 雇 员 号 教 师 注 释 给 学 生 指 出 : 在 幻 灯 片 中, 查 询 结 果 中 的 列 标 题 似 乎 不 重 要 应 该 用 一 个 有 意 义 的 列 别 名 在 输 出 中 只 有 19 行, 但 在 EMPLOYEES 表 中 有 20 行 产 生 这 个 结 果 是 因 为 雇 员 King, 他 是 总 经 理, 他 上 面 没 有 经 理

21 幻 灯 片 21 练 习 4, 第 一 部 分 : 概 览 这 部 分 练 习 包 括 用 Oracle 语 法 写 将 表 连 接 在 一 起 的 查 询 4-21 Copyright Oracle Corporation, All rights reserved. 练 习 4, 第 一 部 分 该 练 习 设 计 了 多 种 到 目 前 为 止 在 课 程 中 学 过 的 Oracle 的 将 连 接 表 在 一 起 的 语 法 的 习 题 完 成 本 课 后 面 的 练 习 1-4

22 幻 灯 片 22 用 SQL 连 接 表 : 1999 语 法 用 一 个 连 接 从 多 个 表 中 查 询 数 据 SELECT table1.column, table2.column FROM table1 [CROSS JOIN table2] [NATURAL JOIN table2] [JOIN table2 USING (column_name)] [JOIN table2 ON(table1.column_name = table2.column_name)] [LEFT RIGHT FULL OUTER JOIN table2 ON ON (table1.column_name = table2.column_name)]; 4-22 Copyright Oracle Corporation, All rights reserved. 定 义 连 接 用 SQL: 1999 语 法, 你 可 以 获 得 与 用 前 面 讲 述 的 Oracle 语 法 同 样 的 结 果 在 语 法 中 : table1.column 表 示 要 从 其 中 返 回 数 据 的 表 和 列 CROSS JOIN 从 两 个 表 中 返 回 笛 卡 尔 乘 积 NATURAL JOIN 基 于 相 同 的 列 名 连 接 两 个 表 JOIN table USING column_name JOIN table ON 执 行 一 个 基 于 列 名 的 等 值 连 接 table1.column_name 执 行 一 个 基 于 在 ON 子 句 中 的 条 件 的 等 值 连 接 = table2.column_name LEFT/RIGHT/FULL OUTER 更 多 信 息, 见 Oracle9i SQL Reference, SELECT

23 幻 灯 片 23 创 建 交 叉 连 接 CROSS JOIN 子 句 导 致 两 个 表 的 交 叉 乘 积 该 连 接 和 两 个 表 之 间 的 笛 卡 尔 乘 积 是 一 样 的 SELECT last_name, department_name FROM employees CROSS JOIN departments ; 4-23 Copyright Oracle Corporation, All rights reserved. 创 建 交 叉 连 接 幻 灯 片 中 例 子 给 出 与 下 面 语 句 相 同 的 结 果 : SELECT last_name, department_name FROM employees, departments;

24 幻 灯 片 24 创 建 自 然 连 接 NATURAL JOIN 子 句 基 于 两 个 表 之 间 有 相 同 名 字 的 所 有 列 它 从 两 个 表 中 选 择 在 所 有 的 匹 配 列 中 有 相 等 值 的 行 如 果 有 相 同 名 字 的 列 的 数 据 类 型 不 同, 返 回 一 个 错 误 4-24 Copyright Oracle Corporation, All rights reserved. 创 建 自 然 连 接 在 以 前 发 布 的 Oracle 中 做 一 个 在 相 应 表 中 无 明 确 指 定 的 列 的 连 接 是 不 可 能 的 在 Oracle9i 中, 让 连 接 完 全 自 动 基 于 有 匹 配 数 据 类 型 和 名 字 的 两 个 表 中 的 列 是 可 能 的, 使 用 NATURAL JOIN 关 键 字 注 : 连 接 只 能 发 生 在 两 个 表 中 有 相 同 名 字 和 数 据 类 型 的 列 上 如 果 列 有 相 同 的 名 字, 但 数 据 类 型 不 同,NATURAL JOIN 语 法 会 引 起 错 误

25 幻 灯 片 25 用 自 然 连 接 返 回 记 录 SELECT department_id, department_name, location_id, city FROM departments NATURAL JOIN locations ; 4-25 Copyright Oracle Corporation, All rights reserved. 用 自 然 连 接 返 回 记 录 在 幻 灯 片 的 例 子 中,LOCATIONS 表 被 用 LOCATION_ID 列 连 接 到 DEPARTMENT 表, 这 是 在 两 个 表 中 唯 一 名 字 相 同 的 列 如 果 存 在 其 它 的 公 共 列, 连 接 会 全 部 使 用 他 们 等 值 连 接 自 然 连 接 也 可 以 被 写 为 等 值 连 接 : SELECT department_id, department_name, departments.location_id, city FROM departments, locations WHERE departments.location_id = locations.location_id; 带 WHERE 子 句 的 自 然 连 接 可 以 用 WHERE 子 句 实 现 在 一 个 自 然 连 接 中 添 加 约 束 下 面 的 例 子 限 制 部 门 号 department ID 等 于 20 或 50 那 些 输 出 的 行 SELECT department_id, department_name, location_id, city FROM departments NATURAL JOIN locations WHERE department_id IN (20, 50);

26 幻 灯 片 26 用 USING 子 句 创 建 连 接 如 果 一 些 列 有 相 同 的 名 字, 但 数 据 类 型 不 匹 配, NATURAL JOIN 子 句 能 够 用 USING 子 句 修 改 以 指 定 将 被 用 于 一 个 等 值 连 接 的 列 当 有 多 个 列 匹 配 时, 用 USING 子 句 匹 配 唯 一 的 列 在 引 用 列 不 要 使 用 表 名 或 者 别 名 NATURAL JOIN 和 USING 子 句 是 相 互 排 斥 的 4-26 Copyright Oracle Corporation, All rights reserved. USING 子 句 自 然 连 接 (Natural joins) 用 具 有 相 匹 配 的 名 字 和 数 据 类 型 的 所 有 列 来 连 接 表 USING 子 句 可 以 被 用 来 指 定 那 些 将 被 用 语 一 个 等 值 连 接 的 列 中 的 唯 一 列 在 USING 子 句 中 引 用 的 列 不 应 该 在 SQL 语 句 的 任 何 地 方 用 表 名 或 表 别 名 限 制 ( 前 缀 ) 例 如, 该 语 句 是 有 效 的 : SELECT l.city, d.department_name FROM locations l JOIN departments d USING (location_id) WHERE location_id = 1400; 该 语 句 是 无 效 的, 因 为 LOCATION_ID 在 WHERE 子 句 中 被 限 制 了 : SELECT l.city, d.department_name FROM locations l JOIN departments d USING (location_id) WHERE d.location_id = 1400; ORA-25154: column part of USING clause cannot have qualifier 同 样 的 限 制 也 用 于 NATURAL 连 接 因 此, 那 些 在 两 个 表 中 有 相 同 名 字 的 列 不 能 没 有 任 何 限 定 词

27 幻 灯 片 27 用 USING 子 句 返 回 记 录 SELECT e.employee_id, e.last_name, d.location_id FROM employees e JOIN departments d USING (department_id) ; 4-27 Copyright Oracle Corporation, All rights reserved. USING 子 句 ( 续 ) 该 例 子 示 范 连 接 EMPLOYEES 和 DEPARTMENTS 表 中 的 DEPARTMENT_ID 列, 并 以 此 显 示 雇 员 工 作 的 场 所 编 号 该 例 子 也 写 成 一 个 等 值 连 接 : SELECT employee_id, last_name, employees.department_id, location_id FROM employees, departments WHERE employees.department_id = departments.department_id;

28 幻 灯 片 28 用 ON 子 句 创 建 连 接 对 于 自 然 连 接 的 连 接 条 件, 基 本 上 是 带 有 相 同 名 字 的 所 有 列 的 等 值 连 接 为 了 指 定 任 意 条 件, 或 者 指 定 要 连 接 的 列, 可 以 使 用 ON 子 句 连 接 条 件 从 另 一 个 搜 索 条 件 中 被 分 开 ON 子 句 使 得 代 码 易 懂 4-28 Copyright Oracle Corporation, All rights reserved. ON 条 件 用 ON 子 句 指 定 一 个 连 接 条 件 这 让 你 从 在 WHERE 子 句 中 的 查 找 或 过 滤 条 件 中 分 离 指 定 的 连 接 条 件

29 幻 灯 片 29 用 ON 子 句 返 回 记 录 SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id); 4-29 Copyright Oracle Corporation, All rights reserved. 创 建 带 ON 子 句 的 连 接 ON 子 句 也 可 以 象 下 面 一 样 被 用 于 有 不 同 名 字 的 连 接 列 : SELECT e.last_name emp, m.last_name mgr FROM employees e JOIN employees m ON (e.manager_id = m.employee_id); 前 面 的 例 子 是 EMPLOYEE 表 基 于 EMPLOYEE_ID 和 MANAGER_ID 列 的 到 它 自 己 的 自 连 接

30 幻 灯 片 30 用 ON 子 句 创 建 三 向 连 接 SELECT employee_id, city, department_name FROM employees e JOIN departments d ON d.department_id = e.department_id JOIN locations l ON d.location_id = l.location_id; 4-30 Copyright Oracle Corporation, All rights reserved. 三 向 连 接 三 向 连 接 是 三 个 表 的 连 接 在 SQL: 1999 兼 容 语 法 中, 连 接 被 从 左 到 右 执 行, 所 以 第 一 个 连 接 执 行 EMPLOYEES JOIN DEPARTMENTS 第 一 个 连 接 条 件 可 以 引 用 在 EMPLOYEES 和 DEPARTMENTS 中 的 列, 但 不 能 引 用 在 LOCATIONS 中 的 列 第 二 个 条 件 可 以 引 用 所 有 三 个 表 中 的 列 这 也 可 以 被 写 为 三 个 等 值 连 接 : SELECT employee_id, city, department_name FROM employees, departments, locations WHERE employees.department_id = departments.department_id AND departments.location_id = locations.location_id; 教 师 注 释 下 面 的 例 子 显 示 也 可 以 用 USING 子 句 完 成 同 样 的 连 接 : SELECT e.employee_id, l.city, d.department_name FROM employees e JOIN departments d USING (department_id) JOIN locations l USING (location_id);

31 幻 灯 片 31 内 与 外 连 接 在 SQL: 1999 中, 连 接 两 个 表, 仅 返 回 匹 配 的 行 的 连 接, 称 为 内 连 接 在 两 个 表 之 间 的 连 接, 返 回 内 连 接 的 结 果, 同 时 还 返 回 不 匹 配 行 的 左 ( 或 右 ) 表 的 连 接, 称 为 左 ( 或 右 ) 连 接 在 两 个 表 之 间 的 连 接, 返 回 内 连 接 的 结 果, 同 时 还 返 回 左 和 右 连 接, 称 为 全 连 接 4-31 Copyright Oracle Corporation, All rights reserved. 连 接 比 较 SQL: 1999 和 Oracle 语 法 Oracle SQL: 1999 Equijoin Natural or Inner Join Outerjoin Left Outer Join Selfjoin Join ON Nonequijoin Join USING Cartesian Product Cross Join

32 幻 灯 片 32 左 外 连 接 SELECT e.last_name, e.department_id, d.department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id) ; 4-32 Copyright Oracle Corporation, All rights reserved. 左 外 连 接 的 例 子 左 边 的 表 (EMPLOYEES) 中 即 使 没 有 与 DEPARTMENTS 表 中 匹 配 的 行, 该 查 询 也 会 取 回 EMPLOYEES 表 中 所 有 的 行 该 查 询 可 以 用 如 下 的 更 容 易 的 语 句 完 成 : SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE d.department_id (+) = e.department_id;

33 幻 灯 片 33 右 外 连 接 SELECT e.last_name, e.department_id, d.department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id) ; 4-33 Copyright Oracle Corporation, All rights reserved. 右 外 连 接 右 边 的 表 (DEPARTMENTS ) 中 即 使 没 有 与 EMPLOYEES 表 中 匹 配 的 行, 该 查 询 也 会 取 回 DEPARTMENTS 表 中 所 有 的 行 该 查 询 可 以 用 如 下 的 更 容 易 的 语 句 完 成 : SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE d.department_id = e.department_id (+);

34 幻 灯 片 34 全 外 连 接 SELECT e.last_name, e.department_id, d.department_name FROM employees e FULL OUTER JOIN departments d ON (e.department_id = d.department_id) ; 4-34 Copyright Oracle Corporation, All rights reserved. 全 外 连 接 的 例 子 该 查 询 取 回 EMPLOYEES 表 中 所 有 的 行, 即 使 在 DEPARTMENTS 表 中 没 有 相 匹 配 的 行 它 也 取 回 DEPARTMENTS 表 中 所 有 的 行, 即 使 EMPLOYEES 表 中 没 有 相 匹 配 的 行 教 师 注 释 可 以 更 容 易 的 完 成 全 外 连 接, 你 可 以 用 UNION 操 作 符 得 到 相 同 的 结 果 SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id (+) = d.department_id UNION SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id (+);

35 幻 灯 片 35 附 加 条 件 SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id) AND e.manager_id = 149 ; 4-35 Copyright Oracle Corporation, All rights reserved. 应 用 附 加 条 件 你 可 以 在 WHERE 子 句 中 应 用 附 加 条 件 该 例 子 显 示 在 EMPLOYEES 和 DEPARTMENTS 表 上 执 行 一 个 连 接, 并 且 附 加 条 件, 只 显 示 经 理 ID 等 于 149 的 雇 员

36 幻 灯 片 36 小 结 在 本 课 中, 您 应 该 已 经 学 会 如 何 使 用 连 接 从 多 表 中 显 示 数 据 : Oracle 8i 和 早 期 版 本 的 私 有 语 法 9i 以 后 版 本 的 SQL: 1999 兼 容 语 法 4-36 Copyright Oracle Corporation, All rights reserved. 小 结 有 多 种 方 法 连 接 表 连 接 类 型 等 值 Equijoins 非 等 值 Non-equijoins 外 连 接 Outer joins 自 连 接 Self joins 交 叉 连 接 Cross joins 自 然 连 接 Natural joins 全 外 连 接 Full or outer joins 笛 卡 尔 乘 积 一 个 笛 卡 尔 乘 积 导 致 所 有 行 的 组 合 被 显 示 其 原 因 可 能 是 忽 略 了 WHERE 子 句 或 指 定 了 CROSS JOIN 子 句 表 别 名 使 用 表 别 名 加 速 数 据 库 的 访 问 表 别 名 有 助 于 保 持 SQL 代 码 较 小, 并 节 省 存 储 器

37 幻 灯 片 37 练 习 4, 第 二 部 分 Part Two: 概 览 本 练 习 包 括 下 面 的 主 题 : 用 等 值 连 接 来 连 接 表 演 示 外 连 接 和 自 连 接 附 加 条 件 4-37 Copyright Oracle Corporation, All rights reserved. 练 习 4, 第 二 部 分 该 练 习 有 意 给 你 从 多 表 中 提 取 数 据 的 实 际 的 经 验 试 用 Oracle 专 用 语 法 和 SQL: 1999 兼 容 语 法 在 第 二 部 分 中, 问 题 5-8, 试 写 出 使 用 ANSI 语 法 的 连 接 语 句 在 第 二 部 分 中, 问 题 9-11, 用 Oracle 语 法 和 ANSI 语 法 试 写 出 连 接 语 句

38 练 习 4 第 一 部 分 1. 写 一 个 查 询 显 示 所 有 雇 员 的 last name department number and department name SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id; 2. 创 建 一 个 在 部 门 80 中 的 所 有 工 作 岗 位 的 唯 一 列 表, 在 输 出 中 包 括 部 门 的 地 点 SELECT DISTINCT job_id, location_id FROM employees, departments WHERE employees.department_id = departments.department_id AND employees.department_id = 80; 3. 写 一 个 查 询 显 示 所 有 有 佣 金 的 雇 员 的 last name department name location ID 和 城 市 SELECT e.last_name, d.department_name, d.location_id, l.c ity FROM employees e, departments d, locations l

39 WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND e.commission_pct IS NOT NULL; 4. 显 示 所 有 在 其 last names 中 有 一 个 小 写 a 的 雇 员 的 last name 和 department name 请 将 你 的 SQL 语 句 用 文 件 名 lab4_4.sql 存 为 脚 本 文 件 SELECT last_name, department_name FROM employees, departments WHERE employees.department_id = departments.department_id AND last_name LIKE '%a%'; 练 习 4 第 二 部 分 5. 写 一 个 查 询 显 示 那 些 工 作 在 Toronto 的 所 有 雇 员 的 last name job department number 和 department name SELECT e.last_name, e.job_id, e.department_id, d.department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) JOIN locations l ON (d.location_id = l.location_id) WHERE LOWER(l.city) = 'toronto'; 6. 显 示 雇 员 的 last name 和 employee number 连 同 他 们 的 经 理 的 last name 和

40 manager number 列 标 签 分 别 为 Employee Emp# Manager 和 Mgr# 将 你 的 SQL 语 句 存 放 在 名 为 lab4_6.sql 的 文 本 文 件 中 SELECT w.last_name "Employee", w.employee_id "EMP#", m.last_name "Manager", m.employee_id "Mgr#" FROM employees w join employees m ON (w.manager_id = m.employee_id); 7. 修 改 lab4_6.sql 显 示 所 有 雇 员 包 括 King, 他 没 有 经 理 用 雇 员 号 排 序 结 果 将 你 的 SQL 语 句 存 放 在 名 为 lab4_7.sql 的 文 本 文 件 中 运 行 lab4_7.sql 中 的 查 询 SELECT w.last_name "Employee", w.employee_id "EMP#", m.last_name "Manager", m.employee_id "Mgr#" FROM employees w LEFT OUTER JOIN employees m ON (w.manager_id = m.employee_id);

41 如 果 你 有 时 间, 完 成 下 面 的 习 题 : 8. 创 建 一 个 查 询 显 示 所 有 与 被 指 定 雇 员 工 作 在 同 一 部 门 的 雇 员 ( 同 事 ) 的 last names department numbers 给 每 列 一 个 适 当 的 标 签 SELECT e.department_id department, e.last_name employee, c.last_name colleague FROM employees e JOIN employees c ON (e.department_id = c.department_id) WHERE e.employee_id <> c.employee_id ORDER BY e.department_id, e.last_name, c.last_name; 9. 显 示 JOB_GRADES 表 的 结 构 创 建 一 个 查 询 显 示 所 有 雇 员 的 name job department name salary 和 grade

42 DESC JOB_GRADES SELECT e.last_name, e.job_id, d.department_name, e.salary, j.grade_level FROM employees e, departments d, job_grades j WHERE e.department_id = d.department_id AND e.salary BETWEEN j.lowest_sal AND j.highest_sal; -- OR SELECT e.last_name, e.job_id, d.department_name, e.salary, j.grade_level FROM employees e JOIN departments d ON (e.department_id = d.department_id) JOIN job_grades j ON (e.salary BETWEEN j.lowest_sal AND j.highest_sal); 如 果 你 想 要 接 受 额 外 的 挑 战, 完 成 下 面 的 习 题 : 10. 创 建 一 个 查 询 显 示 那 些 在 雇 员 Davies 之 后 入 本 公 司 工 作 的 雇 员 的 name 和 hire date SELECT e.last_name, e.hire_date FROM employees e, employees davies WHERE davies.last_name = 'Davies' AND davies.hire_date < e.hire_date -- OR SELECT e.last_name, e.hire_date FROM employees e JOIN employees davies ON (davies.last_name = 'Davies') WHERE davies.hire_date < e.hire_date;

43 11. 显 示 所 有 雇 员 的 names 和 hire dates, 他 们 在 他 们 的 经 理 之 前 进 入 本 公 司, 连 同 他 们 的 经 理 的 名 字 和 受 雇 日 期 一 起 显 示 列 标 签 分 别 为 Employee Emp Hired Manager 和 Mgr Hired SELECT w.last_name, w.hire_date, m.last_name, m.hire_date FROM employees w, employees m WHERE w.manager_id = m.employee_id AND w.hire_date < m.hire_date; -- OR SELECT w.last_name, w.hire_date, m.last_name, m.hire_date FROM employees w JOIN employees m ON (w.manager_id = m.employee_id) WHERE w.hire_date < m.hire_date;