《数据库原理与应用》06.表数据的选择操作

孙 发 勤 扬 州 大 学 新 闻 与 传 媒 学 院第 6章SQL Server 2005 T-SQL数 据 查 询 数 据 库 原 理 与 应 用 SQL Server 2005 SQL Server 2005第 6讲 SQL Server 2005 T-SQL数 据 查 询教 学 目 标 ：熟 练 掌 握 查 询 语 法 格 式 和 使 用 掌 握 投 影 查 询 、 连 接 查 询 、 选 择 查 询 、 分 组 统 计 查 询 、限 定 查 询 、 排 序 查 询 和 保 存 查 询掌 握 常 用 的 连 接 查 询 一 般 掌 握 子 查 询 SQL Server 2005第 6章 主 要 内 容 SELECT查 询 语 句1 连 接 查 询2 子 查 询3 联 合 查 询41 小 结5 SQL Server 20056.1 SELECT查 询 语 句6.1.1 SELECT语 句 的 语 法 格 式SELECT ALL|DISTINCT 列 表 达 式 INTO 新 表 名 FROM 表 名 列 表 WHERE 逻 辑 表 达 式 GROUP BY 列 名 HAVING逻 辑 表 达 式 ORDER BY 列 名 ASC|DESC SQL Server 20056.1.2 SELECT语 句 的 执 行 方 式 步 骤 ： 单 击 工 具 栏 上 的 “ 新 建 查 询 ” 按 钮 ， 在 右 边 窗 口 输 入 查 询 语 句 ， 单 击 工 具栏 或 “ 查 询 ” 菜 单 中 的 “ 执 行 ” ， 可 在 右 下 方 的 窗 口 看 到 查 询 的 结 果 。例 子 ： 图 书 管 理 系 统 数 据 库 Library表 Reader(RID,Rname,Rsex,RnativeP,Rbirthday,TypeID,Lendnum) PK： RID FK:TypeID表 Book (BID,Bname,Author, PubComp, PubDate,Price) PK： BID 表 Borrow (RID, BID,LendDate, ReturnDate)PK： RID, BID FK： RID和 BID表 ReaderType(TypeID, Typename,LimitNnm, LimitDays) SQL Server 20056.1.3 SELECT子 句 投 影 查 询语 法 ： SELECT ALL|DISTINCT TOP integer|TOP integer PERCENT WITH TIES 列 名 表 达 式 1, 列 名 表 达 式2, 列 名 表 达 式 n 其 中 ： 表 达 式 中 含 列 名 ， 常 量 ， 运 算 符 ， 列 函 数下 面 通 过 几 个 例 子 将 参 数 应 用 进 行 说 明 ：1. 投 影 部 分 列2. 投 影 所 有 列3. TOP关 键 字 限 制 返 回 行 数4. 是 否 去 重 复 元 组5. 自 定 义 列 名6. 字 段 函 数 （ 列 函 数 ）7. INTO保 存 查 询 SQL Server 20056.1.3 SELECT子 句 投 影 查 询投 影 部 分 列 Exp1:从 读 者 表 Reader中 中 查 询 出 读 者 的 编 号 、 姓 名 和 性 别三 列 的 记 录 。 USE Lib GO SELECT RID,Rname,Rsex FROM Reader SQL Server 20056.1.3 SELECT子 句 投 影 查 询投 影 所 有 列 ： (通 配 符 *： 所 有 字 段 ) Exp2： 从 图 书 管 理 数 据 库 Library的 读 者 类 型 表 ReaderType中 查 询 所 有 纪 录 . USE Lib SELECT * FROM ReaderType SQL Server 20056.1.3 SELECT子 句 投 影 查 询TOP关 键 字 限 制 返 回 行 数 Exp3:图 书 管 理 数 据 库 Library的 图 书 表 Book中 查 询 出 前 5条 纪录 。 USE Lib GO SELECT TOP 5 BID,Bname,Author FROM Book GO SQL Server 20056.1.3 SELECT子 句 投 影 查 询是 否 去 重 复 元 组 ： All： 检 出 全 部 信 息 （ 默 认 ） Distinct： 去 掉 重 复 信 息 Exp4:从 读 者 表 Reader中 查 询 出 籍 贯 RnativeP的 名 称 。 USE Lib GO SELECT DISTINCT RnativeP FROM Reader SQL Server 20056.1.3 SELECT子 句 投 影 查 询自 定 义 列 名 ： 指 定 的 列 标 题 =列 名 或 者 列 名 AS 指 定 的 列 标 题 Exp5： 用 中 文 显 示 ReaderType列 名 SELECT TypeID AS 类 型 编 号 , Typename AS 类 型 名 称 , LimitNum AS 限 借 数 量 , LimitDays AS 限 借 天 数 FROM ReaderType SQL Server 20056.1.3 SELECT子 句 投 影 查 询字 段 函 数 （ 列 函 数 ） ： 求 和 ： SUM 平 均 ： AVG 最 大 ： MAX 最 小 ： MIN 统 计 ： COUNT Exp6:从 图 书 管 理 数 据 库 Library中 图 书 表 Book中 查 询 出 图 书 最 高 价 和 最 低 价 SELECT MAX(Price) AS 最 高 价 , MIN(Price) AS 最 低 价FROM Book SQL Server 20056.1.3 SELECT子 句 投 影 查 询INTO保 存 查 询 ： into 临 时 表 名 （ 根 据 查 询 建 立 临 时 基 本 表 ） Exp7： 从 借 阅 表 中 将 读 者 的 借 书 证 号 、 所 借 书 号 、 借 出 日 期 的 内 容 另 存 在 临 时 表 Reader_Borrrow中 SELECT RID,BID,LendDate INTO Reader_Borrow FROM Borrow SQL Server 20056.1.4 WHERE子 句 选 择 查 询格 式 ： WHERE 逻 辑 表 达 式 功 能 ： 实 现 有 条 件 的 查 询 运 算下 面 通 过 几 个 例 子 将 不 同 运 算 符 应 用 进 行 说 明 ：1. 比 较 运 算 符 2. 逻 辑 运 算 符3. 范 围 运 算 符4. 模 式 匹 配 运 算 符5. 列 表 运 算 符6. 空 值 判 断 符 SQL Server 20056.1.4 WHERE子 句 选 择 查 询比 较 运 算 符 ( ， ,=,22 and RSex =女 SQL Server 20056.1.4 WHERE子 句 选 择 查 询范 围 运 算 符 格 式 ： 列 名 not between 开 始 值 and 结 束 值 说 明 ： 列 名 是 否 在 开 始 值 and 结 束 值 之 间 。 等 效 ： 列 名 =开 始 值 and 列 名 =结 束 值 列 名 结 束 值 （ 选 not） 例 3： 从 图 书 表 中 查 询 出 定 价 在 10元 到 15元 之 间 的 图 书 信 息 SELECT BID AS 图 书 编 号 , Bname AS 书 名 , Price AS 定 价 FROM BookWHERE Price between 10 and 15 SQL Server 20056.1.4 WHERE子 句 选 择 查 询模 式 匹 配 运 算 符 语 法 ： NOT LIKE 通 配 符 说 明 ： 通 配 符 _： 一 个 任 意 字 符 ； 通 配 符 ： 任 意 个 任 意 字 符 例 4： 查 询 出 姓 “ 王 ” 的 所 有 读 者 的 信 息 SELECT * FROM Reader WHERE Rname LIKE 王 % ms-help:/MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHS/tsqlref9/html/581fb289-29f9-412b-869c-18d33a9e93d5.htm SQL Server 20056.1.4 WHERE子 句 选 择 查 询列 表 运 算 符 语 法 ： 表 达 式 NOT IN (列 表 |子 查 询 ) 说 明 ： 表 达 式 的 值 在 （ 不 在 ） 列 表 所 列 出 的 值 中 子 查 询 在 6.3中 介 绍 例 5： 查 询 借 书 证 号 为 001和 002的 学 生 的 信 息 SELECT * FROM ReaderWHERE RID in( 001 , 002 ) SQL Server 20056.1.4 WHERE子 句 选 择 查 询空 值 判 断 符 语 法 ： IS NOT NULL 例 6： 从 借 阅 表 中 查 询 出 没 有 还 的 图 书 编 号 .SELECT BIDFROM BorrowWHERE ReturnDate IS NULL SQL Server 20056.1.5 GROUP BY子 句 分 组 统 计 查 询格 式 ： GROUP BY 列 名 功 能 ： 与 列 名 或 列 函 数 配 合 实 现 分 组 统 计说 明 ： 投 影 列 名 必 须 出 现 相 应 的 GROUP BY列 名 例 7： 从 图 书 表 中 查 询 各 出 版 社 图 书 的 总 价 。 SELECT PubComp, 总 价 =SUM(price)FROM Book GROUP BY PubComp SQL Server 20056.1.6 HAVING子 句 限 定 查 询格 式 ： HAVING 逻 辑 表 达 式功 能 ： 与 GROUP BY选 项 配 合 筛 选 （ 选 择 ） 统 计 结 果 说 明 ： 常 用 列 函 数 作 为 条 件 ， 列 函 数 不 能 放 在 WHERE中 例 8： 从 借 阅 表 中 查 询 借 书 超 过 10本 的 读 者 的 编 号 SELECT RID, 借 书 本 数 =Count(BID) FROM Borrow GROUP BY RID HAVING Count(BID) 10 SQL Server 20056.1.7 ORDER BY排 序 查 询格 式 ： ORDER BY 列 名 表 达 式 表 asc/ desc功 能 ： 排 序 例 子 ： 查 询 每 个 读 者 借 书 的 本 数 并 按 借 书 的 多 少 进 行 排 序 SELECT RID, COUNT(BID) AS 借 书 本 数FROM BorrowGROUP BY RIDORDER BY COUNT(BID) SQL Server 20056.2 连 接 查 询连 接 方 法 和 种 类1 内 连 接2341 外 连 接5 自 连 接6 SQL Server 20056.2.1 连 接 方 法 和 种 类SQL Server提 供 了 不 同 的 语 法 格 式 支 持 不 同 的 连 接 方 式 用 于 FROM子 句 的 连 接 语 法 形 式 SELECT 列 名 列 表 FROM 表 名 1连 接 类 型 JOIN 表 名 2 ON 连 接 条 件 WHERE 逻 辑 表 达 式 用 于 WHERE子 句 的 SQL Server连 接 语 法 形 式 SELECT 列 名 列 表 FROM 表 名 列 表 WHERE 表 名 .列 名 JOIN_OPERATOR 表 名 .列 名 n ON 逻 辑 表 达 式 连 接 种 类 内 连 接 ， 外 连 接 ， 交 叉 连 接 SQL Server 20056.2.2 内 连 接格 式 ： from 表 名 1 inner join 表 名 2 on 连 接 表 达 式（ 1） 等 值 连 接 在 连 接 条 件 中 使 用 等 于 号 (=)运 算 符 比 较 被 连 接 列 的 列 值 ，其 查 询 结 果 中 列 出 被 连 接 表 中 的 所 有 列 ， 包 括 其 中 的 重 复列 。例 1： 查 询 每 个 读 者 的 详 细 信 息 包 括 读 者 信 息 以 及 借 阅 图 书 信 息 ， 允许 有 重 复 列 。 SELECT Reader.*, Borrow.* FROM Reader INNER JOIN Borrow ON Reader.RID=Borrow.RID SQL Server 20056.2.2 内 连 接（ 2） 自 然 连 接 例 2： 查 询 每 个 读 者 的 详 细 信 息 包 括 读 者 信 息 以 及 借 阅 图 书 信 息 ，不 允 许 有 重 复 列 。 SELECT Reader.RID,Reader.Rname,Book.BID,Book.Bname,LendDate, ReturnDate FROM Borrow INNER JOIN Reader ON Reader.RID=Borrow.RID INNER JOIN Book ON Borrow.BID=Book.BID SQL Server 20056.2.3 外 连 接（ 1） 左 外 连 接格 式 ： from 表 名 1 left outer join 表 名 2 on 连 接 表 达 式功 能 ： 加 入 表 1没 形 成 连 接 的 元 组 ， 表 2列 为 NULL。SELECT Reader.*,RID,BIDFROM Reader LEFT OUTER JOIN Borrow ON Reader.RID=Borrow.RID SQL Server 20056.2.3 外 连 接（ 2） 右 外 连 接格 式 ： from 表 名 1 right outer join 表 名 2 on 连 接 表 达 式功 能 ： 加 入 表 2没 形 成 连 接 的 元 组 ， 表 1列 为 NULL。 SELECT Reader.*, RID, BID FROM Reader RIGHT OUTER JOIN Borrow ON Reader.RID=Borrow.RID SQL Server 20056.2.3 外 连 接（ 3） 全 外 连 接格 式 ： from 表 名 1 full outer join 表 名 2 on 连 接 表 达 式功 能 ： 加 入 表 1没 形 成 连 接 的 元 组 ， 表 2列 为 NULL， 加 入 表 2没 形 成 连 接 的 元 组 ， 表 1列 为 NULL。SELECT Reader.*, Borrow.RID, BIDFROM Borrow FULL OUTER JOIN Reader ON Reader.RID=Borrow.RID SQL Server 20056.2.4 自 连 接格 式 ： from表 名 1 a join 表 名 1 b on 连 接 表 达 式 SELECT a.BID, a.Bname, a.AuthorFROM book a JOIN book b on a.Bname=b.BnameWHERE a.BIDb.BID SQL Server 20056.2.5 交 叉 连 接格 式 ： from 表 名 1 cross join 表 名 2 on 连 接 表 达 式 说 明 ： 两 个 表 做 笛 卡 尔 积 SELECT Reader.*, Borrow.RID,BID FROM Reader CROSS JOIN Borrow SQL Server 20056.3 子 查 询NOTIN子 查 询1 比 较 子 查 询2 NOTEXISTS子 查 询3 在 其 他 语 句 中 使 用4 SQL Server 20056.3.1 NOT IN 子 查 询 列 名 not in (常 量 表 )|(子 查 询 ) 说 明 ： 列 值 被 包 含 或 不 （ not） 被 包 含 在 集 合 中 等 价 ： 列 名 =any(子 查 询 )例 子 ： 查 询 没 有 借 过 书 的 读 者 的 信 息 SELECT * FROM Reader WHERE RID NOT IN (SELECT DISTINCT RID FROM Borrow) SQL Server 20056.3.2 比 较 子 查 询 （ 1） 列 名 比 较 符 all （ 子 查 询 ） 说 明 ： 子 查 询 中 的 每 个 值 都 满 足 比 较 条 件例 子 ： 查 询 读 者 编 号 RID最 大 的 读 者 的 借 书 情 况 SELECT * FROM Borrow WHERE RID=ALL (SELECT RID FROM Reader) SQL Server 20056.3.2 比 较 子 查 询 （ 2） 列 名 比 较 符 any|some （ 子 查 询 ） 说 明 ： 子 查 询 中 的 任 一 个 值 满 足 比 较 条 件 例 子 ： 查 询 选 修 C+语 言 课 程 的 学 生 SELECT 姓 名 , 成 绩 FROM 学 生 AS x INNER JOIN 选 修 AS y ON x.学 号 =y.学 号 WHERE 课 程 号 =any (select 课 程 号 from 课 程 where 课 程 名 =C+语 言 ); SQL Server 20056.3.3 NOT EXISTS子 查 询 功 能 ： 用 集 合 运 算 实 现 元 组 与 （ 子 查 询 ）说 明 ： 子 查 询 中 空 或 非 空 例 子 ： 查 询 借 阅 了 清 华 大 学 的 图 书 的 读 者 的 编 号 SELECT DISTINCT RID FROM Borrow WHERE EXISTS (SELECT * FROM books WHERE Borrow. BID= books. BID AND PubComp= 清 华 大 学 ) SQL Server 20056.3.4在 其 他 语 句 中 使 用 子 查 询 例 子 ： 计 算 读 者 中 的 已 借 数 量 的 值 UPDATE Reader SET Lendnum =( SELECT COUNT(*) FROM dbo.Borrow WHERE ReturnDate IS NULL and Reader. RID =Borrow. RID) SQL Server 20056.4 联 合 查 询UNION操 作 符1 联 合 查 询 结 果 排 序2 对 单 张 表 使 用 UNION操 作 符3 交 操 作 和 叉 操 作41 UNION操 作 符 和 JOIN操 作 符 区 别 与 联 系5 连 接 查 询 和 子 查 询 的 比 较6 SQL Server 20056.4.1 UNION操 作 符格 式 ： SELECT_1 UNION ALL SELECT_2 UNION ALL SELECT_n 例 1： 查 询 出 “ 清 华 大 学 ” 出 版 的 图 书 的 编 号 和 被 借 阅 过 的 图 书 的 编号 ， 不 包 括 重 复 的 列 SELECT BID FROM books WHERE PubComp= 清 华 大 学 UNION SELECT BID FROM Borrow SQL Server 20056.4.1 UNION操 作 符例 2： 查 询 出 “ 清 华 大 学 ” 出 版 的 图 书 的 编 号 和 被 借 阅 过 的 图 书 的 编号 ， 可 以 包 括 重 复 的 列 SELECT BID FROM books WHERE PubComp= 清 华 大 学 UNION ALL SELECT BID FROM Borrow SQL Server 20056.4.2 联 合 查 询 结 果 排 序 例 3： 查 询 出 “ 清 华 大 学 ” 出 版 的 图 书 和 被 借 阅 过 的 图 书 的 编 号 ， 不包 括 重 复 的 列 并 将 查 询 结 果 进 行 排 序 SELECT BID FROM books WHERE PubComp= 人 民 出 版 社 UNION SELECT BID FROM Borrow ORDER BY SQL Server 20056.4.3 对 单 张 表 使 用 UNION操 作 符 例 4： 查 询 “ 清 华 大 学 ” 和 “ 微 软 ” 出 版 的 图 书 的 书 名 和 作 者 SELECT Bname,Author FROM books WHERE PubComp= 清 华 大 学 UNION SELECT Bname,Author FROM books WHERE PubComp= 微 软 SQL Server 20056.4.4 交 操 作 和 差 操 作例 1： 查 询 被 借 阅 过 的 “ 清 华 大 学 ” 出 版 的 图 书 的 图 书 编 号 SELECT BID FROM books WHERE PubComp= 清 华 大 学 AND EXISTS (SELECT * FROM Borrow WHERE Borrow. BID= books. BID) SQL Server 20056.4.4 交 操 作 和 差 操 作例 2： 查 询 没 被 借 阅 过 的 “ 清 华 大 学 ” 出 版 的 图 书 的 图 书 编 号 SELECT BID FROM books WHERE PubComp= 清 华 大 学 AND NOT EXISTS (SELECT * FROM Borrow WHERE Borrow. BID= books. BID) SQL Server 20056.4.5 UNION操 作 符 和 JOIN操 作 符 区 别 与 联 系UNION是 将 相 同 字 段 的 若 干 条 记 录 进 行 合 并 ， 而 JOIN是 将 两个 或 多 个 表 的 若 干 个 字 段 进 行 连 接 ；二 者 均 是 进 行 连 接 操 作 ；但 是 一 个 是 对 行 （ 记 录 ） 进 行 操 作 ， 一 个 是 对 列 （ 字 段 ） 进 行操 作 ； SQL Server 20056.4.6 连 接 查 询 和 子 查 询 的 比 较例 1（ 子 查 询 ） ： 查 询 books中 价 格 最 低 的 图 书 编 号 和 书 名 USE Library GO SELECT BID,Bname FROM books WHERE Price=(SELECT min(Price) FROM books) GO 例 2（ 连 接 查 询 ） ： 查 询 读 者 编 号 、 读 者 姓 名 、 所 借 图 书 名 和 借 阅时 间USE Library GOSELECT Reader.RID,Reader.Rname,books.Bname,Borrow.借 阅 时间FROM Reader,books,Borrow WHERE Reader.RID=Borrow. RID AND books.BID= Borrow.BIDGO SQL Server 20056.5 小 结 本 章 重 点 讲 解 了 SELECT查 询 语 句 （ 包 括 SELECT语 句 的语 法 格 式 和 执 行 方 式 ） ， 介 绍 了 投 影 查 询 、 连 接 查 询 、 选择 查 询 、 分 组 统 计 查 询 、 限 定 查 询 、 排 序 查 询 、 保 存 查 询 ，每 种 查 询 均 通 过 “ 格 式 实 例 ” 的 方 式 加 以 说 明 ； 由 于 连接 查 询 和 子 查 询 的 难 度 和 复 杂 性 ， 于 是 将 连 接 查 询 （ 左 连接 、 中 间 连 接 、 右 连 接 、 自 然 连 接 、 交 叉 连 接 和 多 表 连 接 ）和 子 查 询 （ 包 括 IN子 查 询 、 比 较 子 查 询 和 EXISTS子 查 询 ）各 作 为 一 节 的 内 容 加 以 介 绍 。 希 望 同 学 们 通 过 实 例 和 作 业加 深 理 解 ， 提 高 自 己 的 数 据 查 询 能 力 。 谢 谢 !