数 据 库 系 统 概 论 第 三 章 关 系 数 据 库 标 准 语 言 SQL (II) 兴 义 民 族 师 范 学 院
数 据 查 询 语 句 格 式 SELECT [ALL DISTINCT] < 目 标 列 表 达 式 > [,< 目 标 列 表 达 式 >] FROM < 表 名 或 视 图 名 >[, < 表 名 或 视 图 名 > ] [ WHERE < 条 件 表 达 式 > ] [ GROUP BY < 列 名 1> [ HAVING < 条 件 表 达 式 > ] ] [ ORDER BY < 列 名 2> [ ASC DESC ] ];
3.4 数 据 查 询 3.4.1 单 表 查 询 3.4.2 连 接 查 询 3.4.3 嵌 套 查 询 3.4.4 集 合 查 询 3.4.5 Select 语 句 的 一 般 形 式
3.4.1 单 表 查 询 查 询 仅 涉 及 一 个 表 : 一 选 择 表 中 的 若 干 列 二 选 择 表 中 的 若 干 元 组 三 ORDER BY 子 句 四 聚 集 函 数 五 GROUP BY 子 句
一 选 择 表 中 的 若 干 列 查 询 指 定 列 [ 例 1] 查 询 全 体 学 生 的 学 号 与 姓 名 SELECT Sno,Sname FROM Student; [ 例 2] 查 询 全 体 学 生 的 姓 名 学 号 所 在 系 SELECT Sname,Sno,Sdept FROM Student;
2. 查 询 全 部 列 选 出 所 有 属 性 列 : 在 SELECT 关 键 字 后 面 列 出 所 有 列 名 将 < 目 标 列 表 达 式 > 指 定 为 * [ 例 3] 查 询 全 体 学 生 的 详 细 记 录 SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student; 或 SELECT * FROM Student;
3. 查 询 经 过 计 算 的 值 SELECT 子 句 的 < 目 标 列 表 达 式 > 可 以 为 : 算 术 表 达 式 字 符 串 常 量 函 数 列 别 名
查 询 经 过 计 算 的 值 ( 续 ) [ 例 4] 查 全 体 学 生 的 姓 名 及 其 出 生 年 份 SELECT Sname,2004-Sage /* 假 定 当 年 的 年 份 为 2004 年 */ FROM Student; 输 出 结 果 : Sname 2004-Sage 李 勇 1984 刘 晨 1985 王 敏 1986 张 立 1985
查 询 经 过 计 算 的 值 ( 续 ) [ 例 5] 查 询 全 体 学 生 的 姓 名 出 生 年 份 和 所 有 系, 要 求 用 小 写 字 母 表 示 所 有 系 名 SELECT Sname, Year of Birth: ',2004-Sage, ISLOWER(Sdept) FROM Student; 输 出 结 果 : Sname 'Year of Birth:' 2004-Sage ISLOWER(Sdept) 李 勇 Year of Birth: 1984 cs 刘 晨 Year of Birth: 1985 is 王 敏 Year of Birth: 1986 ma 张 立 Year of Birth: 1985 is
查 询 经 过 计 算 的 值 ( 续 ) 使 用 列 别 名 改 变 查 询 结 果 的 列 标 题 : SELECT Sname NAME,'Year of Birth: BIRTH, 2000-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT FROM Student; 输 出 结 果 : NAME BIRTH BIRTHDAY DEPARTMENT ------- ---------------- ------------- ------------------ 李 勇 Year of Birth: 1984 cs 刘 晨 Year of Birth: 1985 is 王 敏 Year of Birth: 1986 ma 张 立 Year of Birth: 1985 is
3.4.1 单 表 查 询 查 询 仅 涉 及 一 个 表 : 一 选 择 表 中 的 若 干 列 二 选 择 表 中 的 若 干 元 组 三 ORDER BY 子 句 四 聚 集 函 数 五 GROUP BY 子 句
二 选 择 表 中 的 若 干 元 组 1. 消 除 取 值 重 复 的 行 如 果 没 有 指 定 DISTINCT 关 键 词, 则 缺 省 为 ALL [ 例 6] 查 询 选 修 了 课 程 的 学 生 学 号 SELECT Sno FROM SC; 等 价 于 : SELECT ALL Sno FROM SC; 执 行 上 面 的 SELECT 语 句 后, 结 果 为 : Sno 200215121 200215121 200215121 200215122 200215122
消 除 取 值 重 复 的 行 ( 续 ) 指 定 DISTINCT 关 键 词, 去 掉 表 中 重 复 的 行 SELECT DISTINCT Sno FROM SC; 执 行 结 果 : Sno 200215121 200215122
2. 查 询 满 足 条 件 的 元 组 查 询 条 件 谓 词 比 较 =,>,<,>=,<=,!=,<>,!>,!<;NOT+ 上 述 比 较 运 算 符 确 定 范 围 表 3.4 常 用 的 查 询 条 件 BETWEEN AND,NOT BETWEEN AND 确 定 集 合 字 符 匹 配 IN,NOT IN LIKE,NOT LIKE 空 值 IS NULL,IS NOT NULL 多 重 条 件 ( 逻 辑 运 算 ) AND,OR,NOT
(1) 比 较 大 小 [ 例 7] 查 询 计 算 机 科 学 系 全 体 学 生 的 名 单 SELECT Sname FROM Student WHERE Sdept= CS ; [ 例 8] 查 询 所 有 年 龄 在 20 岁 以 下 的 学 生 姓 名 及 其 年 龄 SELECT Sname,Sage FROM Student WHERE Sage < 20; [ 例 9] 查 询 考 试 成 绩 有 不 及 格 的 学 生 的 学 号 SELECT DISTINCT Sno FROM SC WHERE Grade<60;
谓 词 : BETWEEN AND (2)) 确 定 范 围 NOT BETWEEN AND [ 例 10] 查 询 年 龄 在 20~23 岁 ( 包 括 20 岁 和 23 岁 ) 之 间 的 学 生 的 姓 名 系 别 和 年 龄 SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23; [ 例 11] 查 询 年 龄 不 在 20~23 岁 之 间 的 学 生 姓 名 系 别 和 年 龄 SELECT Sname,Sdept,Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23;
(3) 确 定 集 合 谓 词 :IN < 值 表 >, NOT IN < 值 表 > [ 例 12] 查 询 信 息 系 (IS) 数 学 系 (MA) 和 计 算 机 科 学 系 (CS) 学 生 的 姓 名 和 性 别 SELECT Sname,Ssex FROM Student WHERE Sdept IN ( 'IS','MA','CS' ); [ 例 13] 查 询 既 不 是 信 息 系 数 学 系, 也 不 是 计 算 机 科 学 系 的 学 生 的 姓 名 和 性 别 SELECT Sname,Ssex FROM Student WHERE Sdept NOT IN ( 'IS','MA','CS' );
(4) 字 符 匹 配 谓 词 : [NOT] LIKE < 匹 配 串 > [ESCAPE < 换 码 字 符 > ] 1) 匹 配 串 为 固 定 字 符 串 [ 例 14] 查 询 学 号 为 200215121 的 学 生 的 详 细 情 况 SELECT * FROM Student WHERE Sno LIKE 200215121'; 等 价 于 : SELECT * FROM Student WHERE Sno = ' 200215121 ';
2) 匹 配 串 为 含 通 配 符 的 字 符 串 字 符 匹 配 ( 续 ) [ 例 15] 查 询 所 有 姓 刘 学 生 的 姓 名 学 号 和 性 别 SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE 刘 % ; [ 例 16] 查 询 姓 " 欧 阳 " 且 全 名 为 三 个 汉 字 的 学 生 的 姓 名 SELECT Sname FROM Student WHERE Sname LIKE ' 欧 阳 ';
字 符 匹 配 ( 续 ) [ 例 17] 查 询 名 字 中 第 2 个 字 为 " 阳 " 字 的 学 生 的 姓 名 和 学 号 SELECT Sname,Sno FROM Student WHERE Sname LIKE 阳 % ; [ 例 18] 查 询 所 有 不 姓 刘 的 学 生 姓 名 SELECT Sname,Sno,Ssex FROM Student WHERE Sname NOT LIKE ' 刘 %';
字 符 匹 配 ( 续 ) 3) 使 用 换 码 字 符 将 通 配 符 转 义 为 普 通 字 符 [ 例 19] 查 询 DB_Design 课 程 的 课 程 号 和 学 分 SELECT Cno,Ccredit FROM Course WHERE Cname LIKE 'DB\_Design' ESCAPE '\ ; [ 例 20] 查 询 以 "DB_" 开 头, 且 倒 数 第 3 个 字 符 为 i 的 课 程 的 详 细 情 况 SELECT * FROM Course WHERE Cname LIKE 'DB\_%i ' ESCAPE ' \ ; ESCAPE '\' 表 示 \ 为 换 码 字 符
(5) 涉 及 空 值 的 查 询 谓 词 : IS NULL 或 IS NOT NULL IS 不 能 用 = 代 替 [ 例 21] 某 些 学 生 选 修 课 程 后 没 有 参 加 考 试, 所 以 有 选 课 记 录, 但 没 有 考 试 成 绩 查 询 缺 少 成 绩 的 学 生 的 学 号 和 相 应 的 课 程 号 SELECT Sno,Cno FROM SC WHERE Grade IS NULL [ 例 22] 查 所 有 有 成 绩 的 学 生 学 号 和 课 程 号 SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL;
(6) 多 重 条 件 查 询 逻 辑 运 算 符 :AND 和 OR 来 联 结 多 个 查 询 条 件 AND 的 优 先 级 高 于 OR 可 以 用 括 号 改 变 优 先 级 可 用 来 实 现 多 种 其 他 谓 词 [NOT] IN [NOT] BETWEEN AND
多 重 条 件 查 询 ( 续 ) [ 例 23] 查 询 计 算 机 系 年 龄 在 20 岁 以 下 的 学 生 姓 名 SELECT Sname FROM Student WHERE Sdept= 'CS' AND Sage<20;
多 重 条 件 查 询 ( 续 ) 改 写 [ 例 12] [ 例 12] 查 询 信 息 系 (IS) 数 学 系 (MA) 和 计 算 机 科 学 系 (CS) 学 生 的 姓 名 和 性 别 SELECT Sname,Ssex 可 改 写 为 : FROM Student WHERE Sdept IN ( 'IS','MA','CS' ) SELECT Sname,Ssex FROM Student WHERE Sdept= ' IS ' OR Sdept= ' MA' OR Sdept= ' CS ';
3.4.1 单 表 查 询 查 询 仅 涉 及 一 个 表 : 一 选 择 表 中 的 若 干 列 二 选 择 表 中 的 若 干 元 组 三 ORDER BY 子 句 四 聚 集 函 数 五 GROUP BY 子 句
三 ORDER ORDER BY 子 句 ORDER BY 子 句 可 以 按 一 个 或 多 个 属 性 列 排 序 升 序 :ASC; 降 序 :DESC; 缺 省 值 为 升 序 当 排 序 列 含 空 值 时 ASC: 排 序 列 为 空 值 的 元 组 最 后 显 示 DESC: 排 序 列 为 空 值 的 元 组 最 先 显 示
ORDER BY 子 句 ( 续 ) [ 例 24] 查 询 选 修 了 3 号 课 程 的 学 生 的 学 号 及 其 成 绩, 查 询 结 果 按 分 数 降 序 排 列 SELECT Sno,Grade FROM SC WHERE Cno= ' 3 ' ORDER BY Grade DESC; [ 例 25] 查 询 全 体 学 生 情 况, 查 询 结 果 按 所 在 系 的 系 号 升 序 排 列, 同 一 系 中 的 学 生 按 年 龄 降 序 排 列 SELECT * FROM Student ORDER BY Sdept,Sage DESC;
3.4.1 单 表 查 询 查 询 仅 涉 及 一 个 表 : 一 选 择 表 中 的 若 干 列 二 选 择 表 中 的 若 干 元 组 三 ORDER BY 子 句 四 聚 集 函 数 五 GROUP BY 子 句
四 聚 集 函 数 聚 集 函 数 : 计 数 COUNT([DISTINCT ALL] *) COUNT([DISTINCT ALL] < 列 名 >) 计 算 总 和 SUM([DISTINCT ALL] < 列 名 >) 计 算 平 均 值 AVG([DISTINCT ALL] < 列 名 >) 最 大 最 小 值 MAX([DISTINCT ALL] < 列 名 >) MIN([DISTINCT ALL] < 列 名 >)
聚 集 函 数 ( 续 ) [ 例 26] 查 询 学 生 总 人 数 SELECT COUNT(*) FROM Student; [ 例 27] 查 询 选 修 了 课 程 的 学 生 人 数 SELECT COUNT(DISTINCT Sno) FROM SC; [ 例 28] 计 算 1 号 课 程 的 学 生 平 均 成 绩 SELECT AVG(Grade) FROM SC WHERE Cno= ' 1 ';
聚 集 函 数 ( 续 ) [ 例 29] 查 询 选 修 1 号 课 程 的 学 生 最 高 分 数 SELECT MAX(Grade) FROM SC WHER Cno= 1 ; [ 例 30] 查 询 学 生 200215012 选 修 课 程 的 总 学 分 数 SELECT SUM(Ccredit) FROM SC, Course WHER Sno='200215012' AND SC.Cno=Course.Cno;
3.4.1 单 表 查 询 查 询 仅 涉 及 一 个 表 : 一 选 择 表 中 的 若 干 列 二 选 择 表 中 的 若 干 元 组 三 ORDER BY 子 句 四 聚 集 函 数 五 GROUP BY 子 句
五 GROUP GROUP BY 子 句 GROUP BY 子 句 分 组 : 细 化 聚 集 函 数 的 作 用 对 象 未 对 查 询 结 果 分 组, 聚 集 函 数 将 作 用 于 整 个 查 询 结 果 对 查 询 结 果 分 组 后, 聚 集 函 数 将 分 别 作 用 于 每 个 组 作 用 对 象 是 查 询 的 中 间 结 果 表 按 指 定 的 一 列 或 多 列 值 分 组, 值 相 等 的 为 一 组
GROUP BY 子 句 ( 续 ) [ 例 31] 求 各 个 课 程 号 及 相 应 的 选 课 人 数 SELECT Cno,COUNT(Sno) FROM SC GROUP BY Cno; 查 询 结 果 : Cno COUNT(Sno) 1 22 2 34 3 44 4 33 5 48
GROUP BY 子 句 ( 续 ) [ 例 32] 查 询 选 修 了 3 门 以 上 课 程 的 学 生 学 号 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) >3;
GROUP BY 子 句 ( 续 ) HAVING 短 语 与 WHERE 子 句 的 区 别 : 作 用 对 象 不 同 WHERE 子 句 作 用 于 基 表 或 视 图, 从 中 选 择 满 足 条 件 的 元 组 HAVING 短 语 作 用 于 组, 从 中 选 择 满 足 条 件 的 组
3.4 数 据 查 询 3.4.1 单 表 查 询 3.4.2 连 接 查 询 3.4.3 嵌 套 查 询 3.4.4 集 合 查 询 3.4.5 Select 语 句 的 一 般 形 式
3.4.2 连 接 查 询 连 接 查 询 : 同 时 涉 及 多 个 表 的 查 询 连 接 条 件 或 连 接 谓 词 : 用 来 连 接 两 个 表 的 条 件 一 般 格 式 : [< 表 名 1>.]< 列 名 1> < 比 较 运 算 符 > [< 表 名 2>.]< 列 名 2> [< 表 名 1>.]< 列 名 1> BETWEEN [< 表 名 2>.]< 列 名 2> AND [< 表 名 2>.]< 列 名 3> 连 接 字 段 : 连 接 谓 词 中 的 列 名 称 连 接 条 件 中 的 各 连 接 字 段 类 型 必 须 是 可 比 的, 但 名 字 不 必 是 相 同 的
连 接 操 作 的 执 行 过 程 嵌 套 循 环 法 (NESTED-LOOP) 首 先 在 表 1 中 找 到 第 一 个 元 组, 然 后 从 头 开 始 扫 描 表 2, 逐 一 查 找 满 足 连 接 件 的 元 组, 找 到 后 就 将 表 1 中 的 第 一 个 元 组 与 该 元 组 拼 接 起 来, 形 成 结 果 表 中 一 个 元 组 表 2 全 部 查 找 完 后, 再 找 表 1 中 第 二 个 元 组, 然 后 再 从 头 开 始 扫 描 表 2, 逐 一 查 找 满 足 连 接 条 件 的 元 组, 找 到 后 就 将 表 1 中 的 第 二 个 元 组 与 该 元 组 拼 接 起 来, 形 成 结 果 表 中 一 个 元 组 重 复 上 述 操 作, 直 到 表 1 中 的 全 部 元 组 都 处 理 完 毕
排 序 合 并 法 (SORT-MERGE) 常 用 于 = 连 接 首 先 按 连 接 属 性 对 表 1 和 表 2 排 序 对 表 1 的 第 一 个 元 组, 从 头 开 始 扫 描 表 2, 顺 序 查 找 满 足 连 接 条 件 的 元 组, 找 到 后 就 将 表 1 中 的 第 一 个 元 组 与 该 元 组 拼 接 起 来, 形 成 结 果 表 中 一 个 元 组 当 遇 到 表 2 中 第 一 条 大 于 表 1 连 接 字 段 值 的 元 组 时, 对 表 2 的 查 询 不 再 继 续
排 序 合 并 法 找 到 表 1 的 第 二 条 元 组, 然 后 从 刚 才 的 中 断 点 处 继 续 顺 序 扫 描 表 2, 查 找 满 足 连 接 条 件 的 元 组, 找 到 后 就 将 表 1 中 的 第 一 个 元 组 与 该 元 组 拼 接 起 来, 形 成 结 果 表 中 一 个 元 组 直 接 遇 到 表 2 中 大 于 表 1 连 接 字 段 值 的 元 组 时, 对 表 2 的 查 询 不 再 继 续 重 复 上 述 操 作, 直 到 表 1 或 表 2 中 的 全 部 元 组 都 处 理 完 毕 为 止
索 引 连 接 (INDEX-JOIN) 对 表 2 按 连 接 字 段 建 立 索 引 对 表 1 中 的 每 个 元 组, 依 次 根 据 其 连 接 字 段 值 查 询 表 2 的 索 引, 从 中 找 到 满 足 条 件 的 元 组, 找 到 后 就 将 表 1 中 的 第 一 个 元 组 与 该 元 组 拼 接 起 来, 形 成 结 果 表 中 一 个 元 组
连 接 查 询 ( 续 ) 一 等 值 与 非 等 值 连 接 查 询 二 自 身 连 接 三 外 连 接 四 复 合 条 件 连 接
一 等 值 与 非 等 值 连 接 查 询 等 值 连 接 : 连 接 运 算 符 为 = [ 例 33] 查 询 每 个 学 生 及 其 选 修 课 程 的 情 况 SELECT Student.*,SC.* FROM Student,SC WHERE Student.Sno = SC.Sno;
等 值 与 非 等 值 连 接 查 询 ( 续 ) 查 询 结 果 : Student.Sno Sname Ssex Sage Sdept SC.Sno Cno Grade 200215121 李 勇 男 20 CS 200215121 1 92 200215121 李 勇 男 20 CS 200215121 2 85 200215121 李 勇 男 20 CS 200215121 3 88 200215122 刘 晨 女 19 CS 200215122 2 90 200215122 刘 晨 女 19 CS 200215122 3 80
等 值 与 非 等 值 连 接 查 询 ( 续 ) 自 然 连 接 : [ 例 34] 对 [ 例 33] 用 自 然 连 接 完 成 SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student,SC WHERE Student.Sno = SC.Sno;
连 接 查 询 ( 续 ) 一 等 值 与 非 等 值 连 接 查 询 二 自 身 连 接 三 外 连 接 四 复 合 条 件 连 接
二 自 身 连 接 自 身 连 接 : 一 个 表 与 其 自 己 进 行 连 接 需 要 给 表 起 别 名 以 示 区 别 由 于 所 有 属 性 名 都 是 同 名 属 性, 因 此 必 须 使 用 别 名 前 缀 [ 例 35] 查 询 每 一 门 课 的 间 接 先 修 课 ( 即 先 修 课 的 先 修 课 ) SELECT FIRST.Cno,SECOND.Cpno FROM Course FIRST,Course SECOND WHERE FIRST.Cpno = SECOND.Cno;
自 身 连 接 ( 续 ) FIRST 表 (Course 表 ) Cno Cname Cpno Ccredit 1 数 据 库 5 4 2 数 学 2 3 信 息 系 统 1 4 4 操 作 系 统 6 3 5 数 据 结 构 7 4 6 数 据 处 理 2 7 PASCAL 语 言 6 4
自 身 连 接 ( 续 ) SECOND 表 (Course 表 ) Cno Cname Cpno Ccredit 1 数 据 库 5 4 2 数 学 2 3 信 息 系 统 1 4 4 操 作 系 统 6 3 5 数 据 结 构 7 4 6 数 据 处 理 2 7 PASCAL 语 言 6 4
自 身 连 接 ( 续 ) 查 询 结 果 : Cno Pcno 1 7 3 5 5 6
连 接 查 询 ( 续 ) 一 等 值 与 非 等 值 连 接 查 询 二 自 身 连 接 三 外 连 接 四 复 合 条 件 连 接
三 外 连 接 外 连 接 与 普 通 连 接 的 区 别 普 通 连 接 操 作 只 输 出 满 足 连 接 条 件 的 元 组 外 连 接 操 作 以 指 定 表 为 连 接 主 体, 将 主 体 表 中 不 满 足 连 接 条 件 的 元 组 一 并 输 出 [ 例 36] 改 写 [ 例 33] SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student LEFT OUT JOIN SC ON (Student.Sno=SC.Sno);
外 连 接 ( 续 ) 执 行 结 果 : Student.Sno Sname Ssex Sage Sdept Cno Grade 200215121 李 勇 男 20 CS 1 92 200215121 李 勇 男 20 CS 2 85 200215121 李 勇 男 20 CS 3 88 200215122 刘 晨 女 19 CS 2 90 200215122 刘 晨 女 19 CS 3 80 200215123 王 敏 女 18 MA NULL NULL 200215125 张 立 男 19 IS NULL NULL
外 连 接 ( 续 ) 左 外 连 接 列 出 左 边 关 系 ( 如 本 例 Student) 中 所 有 的 元 组 右 外 连 接 列 出 右 边 关 系 中 所 有 的 元 组
连 接 查 询 ( 续 ) 一 等 值 与 非 等 值 连 接 查 询 二 自 身 连 接 三 外 连 接 四 复 合 条 件 连 接
四 复 合 条 件 连 接 复 合 条 件 连 接 :WHERE 子 句 中 含 多 个 连 接 条 件 [ 例 37] 查 询 选 修 2 号 课 程 且 成 绩 在 90 分 以 上 的 所 有 学 生 SELECT Student.Sno, Sname FROM Student, SC WHERE Student.Sno = SC.Sno AND /* 连 接 谓 词 */ SC.Cno= 2 AND SC.Grade > 90; /* 其 他 限 定 条 件 */
复 合 条 件 连 接 ( 续 ) [ 例 38] 查 询 每 个 学 生 的 学 号 姓 名 选 修 的 课 程 名 及 成 绩 SELECT Student.Sno,Sname,Cname,Grade FROM Student,SC,Course /* 多 表 连 接 */ WHERE Student.Sno = SC.Sno and SC.Cno = Course.Cno;
3.4 数 据 查 询 3.4.1 单 表 查 询 3.4.2 连 接 查 询 3.4.3 嵌 套 查 询 3.4.4 集 合 查 询 3.4.5 Select 语 句 的 一 般 形 式
嵌 套 查 询 ( 续 ) 嵌 套 查 询 概 述 一 个 SELECT-FROM-WHERE 语 句 称 为 一 个 查 询 块 将 一 个 查 询 块 嵌 套 在 另 一 个 查 询 块 的 WHERE 子 句 或 HAVING 短 语 的 条 件 中 的 查 询 称 为 嵌 套 查 询
嵌 套 查 询 ( 续 ) SELECT Sname /* 外 层 查 询 / 父 查 询 */ FROM Student WHERE Sno IN (SELECT Sno /* 内 层 查 询 / 子 查 询 */ FROM SC WHERE Cno= ' 2 ');
嵌 套 查 询 ( 续 ) 子 查 询 的 限 制 不 能 使 用 ORDER BY 子 句 层 层 嵌 套 方 式 反 映 了 SQL 语 言 的 结 构 化 有 些 嵌 套 查 询 可 以 用 连 接 运 算 替 代
嵌 套 查 询 求 解 方 法 不 相 关 子 查 询 : 子 查 询 的 查 询 条 件 不 依 赖 于 父 查 询 由 里 向 外 逐 层 处 理 即 每 个 子 查 询 在 上 一 级 查 询 处 理 之 前 求 解, 子 查 询 的 结 果 用 于 建 立 其 父 查 询 的 查 找 条 件
嵌 套 查 询 求 解 方 法 ( 续 ) 相 关 子 查 询 : 子 查 询 的 查 询 条 件 依 赖 于 父 查 询 首 先 取 外 层 查 询 中 表 的 第 一 个 元 组, 根 据 它 与 内 层 查 询 相 关 的 属 性 值 处 理 内 层 查 询, 若 WHERE 子 句 返 回 值 为 真, 则 取 此 元 组 放 入 结 果 表 然 后 再 取 外 层 表 的 下 一 个 元 组 重 复 这 一 过 程, 直 至 外 层 表 全 部 检 查 完 为 止
3.4.3 嵌 套 查 询 一 带 有 IN 谓 词 的 子 查 询 二 带 有 比 较 运 算 符 的 子 查 询 三 带 有 ANY(SOME) 或 ALL 谓 词 的 子 查 询 四 带 有 EXISTS 谓 词 的 子 查 询
一 带 有 IN 谓 词 的 子 查 询 [ 例 39] 查 询 与 刘 晨 在 同 一 个 系 学 习 的 学 生 此 查 询 要 求 可 以 分 步 来 完 成 1 确 定 刘 晨 所 在 系 名 SELECT Sdept FROM Student WHERE Sname= ' 刘 晨 '; 结 果 为 : CS
带 有 IN 谓 词 的 子 查 询 ( 续 ) 2 查 找 所 有 在 IS 系 学 习 的 学 生 SELECT Sno,Sname,Sdept FROM Student WHERE Sdept= ' CS '; 结 果 为 : Sno Sname Sdept 200215121 李 勇 CS 200215122 刘 晨 CS
带 有 IN 谓 词 的 子 查 询 ( 续 ) 将 第 一 步 查 询 嵌 入 到 第 二 步 查 询 的 条 件 中 SELECT Sno,Sname,Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname= 刘 晨 ); 此 查 询 为 不 相 关 子 查 询
带 有 IN 谓 词 的 子 查 询 ( 续 ) 用 自 身 连 接 完 成 [ 例 39] 查 询 要 求 SELECT S1.Sno,S1.Sname,S1.Sdept FROM Student S1,Student S2 WHERE S1.Sdept = S2.Sdept AND S2.Sname = ' 刘 晨 ';
带 有 IN 谓 词 的 子 查 询 ( 续 ) [ 例 40] 查 询 选 修 了 课 程 名 为 信 息 系 统 的 学 生 学 号 和 姓 名 SELECT Sno,Sname 3 最 后 在 Student 关 系 中 FROM Student 取 出 Sno 和 Sname WHERE Sno IN (SELECT Sno 2 然 后 在 SC 关 系 中 找 出 选 FROM SC 修 了 3 号 课 程 的 学 生 学 号 WHERE Cno IN (SELECT Cno 1 首 先 在 Course 关 系 中 找 出 FROM Course 信 息 系 统 的 课 程 号, 为 3 号 WHERE Cname= 信 息 系 统 ) );
带 有 IN 谓 词 的 子 查 询 ( 续 ) 用 连 接 查 询 实 现 [ 例 40] SELECT Sno,Sname FROM Student,SC,Course WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Cname= 信 息 系 统 ;
3.4.3 嵌 套 查 询 一 带 有 IN 谓 词 的 子 查 询 二 带 有 比 较 运 算 符 的 子 查 询 三 带 有 ANY(SOME) 或 ALL 谓 词 的 子 查 询 四 带 有 EXISTS 谓 词 的 子 查 询
二 带 有 比 较 运 算 符 的 子 查 询 当 能 确 切 知 道 内 层 查 询 返 回 单 值 时, 可 用 比 较 运 算 符 (>,<,=,>=,<=,!= 或 < >) 与 ANY 或 ALL 谓 词 配 合 使 用
带 有 比 较 运 算 符 的 子 查 询 ( 续 ) 例 : 假 设 一 个 学 生 只 可 能 在 一 个 系 学 习, 并 且 必 须 属 于 一 个 系, 则 在 [ 例 39] 可 以 用 = 代 替 IN : SELECT Sno,Sname,Sdept FROM Student WHERE Sdept = (SELECT Sdept FROM Student WHERE Sname= 刘 晨 );
带 有 比 较 运 算 符 的 子 查 询 ( 续 ) 子 查 询 一 定 要 跟 在 比 较 符 之 后 错 误 的 例 子 : SELECT Sno,Sname,Sdept FROM Student WHERE ( SELECT Sdept FROM Student WHERE Sname= 刘 晨 ) = Sdept;
带 有 比 较 运 算 符 的 子 查 询 ( 续 ) [ 例 41] 找 出 每 个 学 生 超 过 他 选 修 课 程 平 均 成 绩 的 课 程 号 SELECT Sno, Cno FROM SC x 相 关 子 查 询 WHERE Grade >=(SELECT AVG(Grade) FROM SC y WHERE y.sno=x.sno);
带 有 比 较 运 算 符 的 子 查 询 ( 续 ) 可 能 的 执 行 过 程 : 1. 从 外 层 查 询 中 取 出 SC 的 一 个 元 组 x, 将 元 组 x 的 Sno 值 (200215121) 传 送 给 内 层 查 询 SELECT AVG(Grade) FROM SC y WHERE y.sno='200215121'; 2. 执 行 内 层 查 询, 得 到 值 88( 近 似 值 ), 用 该 值 代 替 内 层 查 询, 得 到 外 层 查 询 : SELECT Sno, Cno FROM SC x WHERE Grade >=88;
带 有 比 较 运 算 符 的 子 查 询 ( 续 ) 3. 执 行 这 个 查 询, 得 到 (200215121,1) (200215121,3) 4. 外 层 查 询 取 出 下 一 个 元 组 重 复 做 上 述 1 至 3 步 骤, 直 到 外 层 的 SC 元 组 全 部 处 理 完 毕 结 果 为 : (200215121,1) (200215121,3) (200215122,2)
3.4.3 嵌 套 查 询 一 带 有 IN 谓 词 的 子 查 询 二 带 有 比 较 运 算 符 的 子 查 询 三 带 有 ANY(SOME) 或 ALL 谓 词 的 子 查 询 四 带 有 EXISTS 谓 词 的 子 查 询
三 带 有 ANY(SOME SOME) ) 或 ALL 谓 词 的 谓 词 语 义 ANY: 任 意 一 个 值 ALL: 所 有 值 子 查 询
带 有 ANY(SOME SOME) ) 或 ALL 谓 词 的 子 查 询 ( 续 ) 需 要 配 合 使 用 比 较 运 算 符 > ANY 大 于 子 查 询 结 果 中 的 某 个 值 > ALL 大 于 子 查 询 结 果 中 的 所 有 值 < ANY 小 于 子 查 询 结 果 中 的 某 个 值 < ALL 小 于 子 查 询 结 果 中 的 所 有 值 >= ANY 大 于 等 于 子 查 询 结 果 中 的 某 个 值 >= ALL 大 于 等 于 子 查 询 结 果 中 的 所 有 值 <= ANY 小 于 等 于 子 查 询 结 果 中 的 某 个 值 <= ALL 小 于 等 于 子 查 询 结 果 中 的 所 有 值 = ANY 等 于 子 查 询 结 果 中 的 某 个 值 =ALL 等 于 子 查 询 结 果 中 的 所 有 值 ( 通 常 没 有 实 际 意 义 )!=( 或 <>)ANY 不 等 于 子 查 询 结 果 中 的 某 个 值!=( 或 <>)ALL 不 等 于 子 查 询 结 果 中 的 任 何 一 个 值
带 有 ANY(SOME SOME) ) 或 ALL 谓 词 的 子 查 询 ( 续 ) [ 例 42] 查 询 其 他 系 中 比 计 算 机 科 学 某 一 学 生 年 龄 小 的 学 生 姓 名 和 年 龄 SELECT Sname,Sage FROM Student WHERE Sage < ANY (SELECT Sage FROM Student WHERE Sdept= ' CS ') AND Sdept <> CS ' ; /* 父 查 询 块 中 的 条 件 */
带 有 ANY(SOME SOME) ) 或 ALL 谓 词 的 子 查 询 ( 续 ) 结 果 : Sname Sage 王 敏 18 张 立 19 执 行 过 程 : 1.RDBMS 执 行 此 查 询 时, 首 先 处 理 子 查 询, 找 出 CS 系 中 所 有 学 生 的 年 龄, 构 成 一 个 集 合 (20,19) 2. 处 理 父 查 询, 找 所 有 不 是 CS 系 且 年 龄 小 于 20 或 19 的 学 生
带 有 ANY(SOME SOME) ) 或 ALL 谓 词 的 子 查 询 ( 续 ) 用 聚 集 函 数 实 现 [ 例 42] SELECT Sname,Sage FROM Student WHERE Sage < (SELECT MAX(Sage) FROM Student WHERE Sdept= CS ') AND Sdept <> ' CS ;
带 有 ANY(SOME SOME) ) 或 ALL 谓 词 的 子 查 询 ( 续 ) [ 例 43] 查 询 其 他 系 中 比 计 算 机 科 学 系 所 有 学 生 年 龄 都 小 的 学 生 姓 名 及 年 龄 方 法 一 : 用 ALL 谓 词 SELECT Sname,Sage FROM Student WHERE Sage < ALL (SELECT Sage FROM Student WHERE Sdept= ' CS ') AND Sdept <> ' CS ;
带 有 ANY(SOME SOME) ) 或 ALL 谓 词 的 子 查 询 ( 续 ) 方 法 二 : 用 聚 集 函 数 SELECT Sname,Sage FROM Student WHERE Sage < (SELECT MIN(Sage) FROM Student WHERE Sdept= ' CS ') AND Sdept <>' CS ;
带 有 ANY(SOME SOME) ) 或 ALL 谓 词 的 子 查 询 ( 续 ) 表 3.5 ANY( 或 SOME),ALL 谓 词 与 聚 集 函 数 IN 谓 词 的 等 价 转 换 关 系 = <> 或!= < <= > >= ANY IN -- <MAX <=MAX >MIN >= MIN ALL -- NOT IN <MIN <= MIN >MAX >= MAX
3.4.3 嵌 套 查 询 一 带 有 IN 谓 词 的 子 查 询 二 带 有 比 较 运 算 符 的 子 查 询 三 带 有 ANY(SOME) 或 ALL 谓 词 的 子 查 询 四 带 有 EXISTS 谓 词 的 子 查 询
带 有 EXISTS 谓 词 的 子 查 询 ( 续 ) 1. EXISTS 谓 词 存 在 量 词 带 有 EXISTS 谓 词 的 子 查 询 不 返 回 任 何 数 据, 只 产 生 逻 辑 真 值 true 或 逻 辑 假 值 false 若 内 层 查 询 结 果 非 空, 则 外 层 的 WHERE 子 句 返 回 真 值 若 内 层 查 询 结 果 为 空, 则 外 层 的 WHERE 子 句 返 回 假 值 由 EXISTS 引 出 的 子 查 询, 其 目 标 列 表 达 式 通 常 都 用 *, 因 为 带 EXISTS 的 子 查 询 只 返 回 真 值 或 假 值, 给 出 列 名 无 实 际 意 义 2. NOT EXISTS 谓 词 若 内 层 查 询 结 果 非 空, 则 外 层 的 WHERE 子 句 返 回 假 值 若 内 层 查 询 结 果 为 空, 则 外 层 的 WHERE 子 句 返 回 真 值
带 有 EXISTS 谓 词 的 子 查 询 ( 续 ) [ 例 44] 查 询 所 有 选 修 了 1 号 课 程 的 学 生 姓 名 思 路 分 析 : 本 查 询 涉 及 Student 和 SC 关 系 在 Student 中 依 次 取 每 个 元 组 的 Sno 值, 用 此 值 去 检 查 SC 关 系 若 SC 中 存 在 这 样 的 元 组, 其 Sno 值 等 于 此 Student.Sno 值, 并 且 其 Cno= '1', 则 取 此 Student.Sname 送 入 结 果 关 系
带 有 EXISTS 谓 词 的 子 查 询 ( 续 ) 用 嵌 套 查 询 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= ' 1 ');
带 有 EXISTS 谓 词 的 子 查 询 ( 续 ) 用 连 接 运 算 SELECT Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno= '1';
带 有 EXISTS 谓 词 的 子 查 询 ( 续 ) [ 例 45] 查 询 没 有 选 修 1 号 课 程 的 学 生 姓 名 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno='1');
带 有 EXISTS 谓 词 的 子 查 询 ( 续 ) 不 同 形 式 的 查 询 间 的 替 换 一 些 带 EXISTS 或 NOT EXISTS 谓 词 的 子 查 询 不 能 被 其 他 形 式 的 子 查 询 等 价 替 换 所 有 带 IN 谓 词 比 较 运 算 符 ANY 和 ALL 谓 词 的 子 查 询 都 能 用 带 EXISTS 谓 词 的 子 查 询 等 价 替 换 用 EXISTS/NOT EXISTS 实 现 全 称 量 词 ( 难 点 ) SQL 语 言 中 没 有 全 称 量 词 (For all) 可 以 把 带 有 全 称 量 词 的 谓 词 转 换 为 等 价 的 带 有 存 在 量 词 的 谓 词 : ( x)p ( x( P))
带 有 EXISTS 谓 词 的 子 查 询 ( 续 ) 例 :[ 例 39] 查 询 与 刘 晨 在 同 一 个 系 学 习 的 学 生 可 以 用 带 EXISTS 谓 词 的 子 查 询 替 换 : SELECT Sno,Sname,Sdept FROM Student S1 WHERE EXISTS (SELECT * FROM Student S2 WHERE S2.Sdept = S1.Sdept AND S2.Sname = 刘 晨 );
带 有 EXISTS 谓 词 的 子 查 询 ( 续 ) [ 例 46] 查 询 选 修 了 全 部 课 程 的 学 生 姓 名 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno ) );
带 有 EXISTS 谓 词 的 子 查 询 ( 续 ) 用 EXISTS/NOT EXISTS 实 现 逻 辑 蕴 函 ( 难 点 ) SQL 语 言 中 没 有 蕴 函 (Implication) 逻 辑 运 算 可 以 利 用 谓 词 演 算 将 逻 辑 蕴 函 谓 词 等 价 转 换 为 : p q p q
带 有 EXISTS 谓 词 的 子 查 询 ( 续 ) [ 例 47] 查 询 至 少 选 修 了 学 生 200215122 选 修 的 全 部 课 程 的 学 生 号 码 解 题 思 路 : 用 逻 辑 蕴 函 表 达 : 查 询 学 号 为 x 的 学 生, 对 所 有 的 课 程 y, 只 要 200215122 学 生 选 修 了 课 程 y, 则 x 也 选 修 了 y 形 式 化 表 示 : 用 P 表 示 谓 词 学 生 200215122 选 修 了 课 程 y 用 q 表 示 谓 词 学 生 x 选 修 了 课 程 y 则 上 述 查 询 为 : ( y) p q
带 有 EXISTS 谓 词 的 子 查 询 ( 续 ) 等 价 变 换 : ( y)p q ( y ( (p q )) ( y ( ( p q) )) y(p q) 变 换 后 语 义 : 不 存 在 这 样 的 课 程 y, 学 生 200215122 选 修 了 y, 而 学 生 x 没 有 选
带 有 EXISTS 谓 词 的 子 查 询 ( 续 ) 用 NOT EXISTS 谓 词 表 示 : SELECT DISTINCT Sno FROM SC SCX WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno = ' 200215122 ' AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno));
3.4 数 据 查 询 3.4.1 单 表 查 询 3.4.2 连 接 查 询 3.4.3 嵌 套 查 询 3.4.4 集 合 查 询 3.4.5 Select 语 句 的 一 般 形 式
3.4.4 集 合 查 询 集 合 操 作 的 种 类 并 操 作 UNION 交 操 作 INTERSECT 差 操 作 EXCEPT 参 加 集 合 操 作 的 各 查 询 结 果 的 列 数 必 须 相 同 ; 对 应 项 的 数 据 类 型 也 必 须 相 同
集 合 查 询 ( 续 ) [ 例 48] 查 询 计 算 机 科 学 系 的 学 生 及 年 龄 不 大 于 19 岁 的 学 生 方 法 一 : SELECT * FROM Student WHERE Sdept= 'CS' UNION SELECT * FROM Student WHERE Sage<=19; UNION: 将 多 个 查 询 结 果 合 并 起 来 时, 系 统 自 动 去 掉 重 复 元 组 UNION ALL: 将 多 个 查 询 结 果 合 并 起 来 时, 保 留 重 复 元 组
集 合 查 询 ( 续 ) 方 法 二 : SELECT DISTINCT * FROM Student WHERE Sdept= 'CS' OR Sage<=19;
集 合 查 询 ( 续 ) [ 例 49] 查 询 选 修 了 课 程 1 或 者 选 修 了 课 程 2 的 学 生 SELECT Sno FROM SC WHERE Cno=' 1 ' UNION SELECT Sno FROM SC WHERE Cno= ' 2 ';
集 合 查 询 ( 续 ) [ 例 50] 查 询 计 算 机 科 学 系 的 学 生 与 年 龄 不 大 于 19 岁 的 学 生 的 交 集 SELECT * FROM Student WHERE Sdept='CS' INTERSECT SELECT * FROM Student WHERE Sage<=19
集 合 查 询 ( 续 ) [ 例 50] 实 际 上 就 是 查 询 计 算 机 科 学 系 中 年 龄 不 大 于 19 岁 的 学 生 SELECT * FROM Student WHERE Sdept= 'CS' AND Sage<=19;
集 合 查 询 ( 续 ) [ 例 51] 查 询 选 修 课 程 1 的 学 生 集 合 与 选 修 课 程 2 的 学 生 集 合 的 交 集 SELECT Sno FROM SC WHERE Cno=' 1 ' INTERSECT SELECT Sno FROM SC WHERE Cno='2 ';
集 合 查 询 ( 续 ) [ 例 51] 实 际 上 是 查 询 既 选 修 了 课 程 1 又 选 修 了 课 程 2 的 学 生 SELECT Sno FROM SC WHERE Cno=' 1 ' AND Sno IN (SELECT Sno FROM SC WHERE Cno=' 2 ');
集 合 查 询 ( 续 ) [ 例 52] 查 询 计 算 机 科 学 系 的 学 生 与 年 龄 不 大 于 19 岁 的 学 生 的 差 集 SELECT * FROM Student WHERE Sdept='CS' EXCEPT SELECT * FROM Student WHERE Sage <=19;
集 合 查 询 ( 续 ) [ 例 52] 实 际 上 是 查 询 计 算 机 科 学 系 中 年 龄 大 于 19 岁 的 学 生 SELECT * FROM Student WHERE Sdept= 'CS' AND Sage>19;
3.4 数 据 查 询 3.4.1 单 表 查 询 3.4.2 连 接 查 询 3.4.3 嵌 套 查 询 3.4.4 集 合 查 询 3.4.5 Select 语 句 的 一 般 形 式
3.4.5 SELECT 语 句 的 一 般 格 式 3.4.5 SELECT [ALL DISTINCT] < 目 标 列 表 达 式 > [ 别 名 ] [,< 目 标 列 表 达 式 > [ 别 名 ]] FROM < 表 名 或 视 图 名 > [ 别 名 ] [,< 表 名 或 视 图 名 > [ 别 名 ]] [WHERE < 条 件 表 达 式 >] [GROUP BY < 列 名 1> [HAVING < 条 件 表 达 式 >]] [ORDER BY < 列 名 2> [ASC DESC]
上 机 实 验 休 息 一 会 儿