文前.indd

Transcription

1

2

3 图书在版编目 (CIP) 数据 SQL 基础教程 / ( 日 )MICK 著 ; 孙淼, 罗勇译. -- 北京 : 人民邮电出版社, ( 图灵程序设计丛书 ) ISBN Ⅰ. 1S Ⅱ. 1M 2 孙 3 罗 Ⅲ. 1 关系数据库系统 Ⅳ. 1TP 中国版本图书馆 CIP 数据核字 (2013) 第 号 SQL ゼロからはじめるデータベース操作 (ISBN ) Copyright 2010 by Mick. Original Japanese edition published by SHOEISHA Co.,Ltd Simplified Chinese Character translation rights arranged with SHOEISHA Co.,Ltd through CREEK & RIVER Co.,Ltd Simplified Chinese Character translation copyright 2013 by Posts & Telecom Press 本书中文简体字版由 SHOEISHA Co.,Ltd 授权人民邮电出版社独家出版 未经出版者书面许可, 不得以任何方式复制或抄袭本书内容 版权所有, 侵权必究 内容提要本书介绍了关系数据库以及用来操作关系数据库的 SQL 语言的使用方法, 提供了大量的示例程序和详实的操作步骤说明, 读者可以亲自动手解决具体问题, 循序渐进地掌握 SQL 的基础知识和技巧, 切实提高自身的编程能力 在每章结尾备有习题, 用来检验读者对该章内容的理解程度 另外本书还将重要知识点总结为 法则, 方便大家随时查阅 本书适合完全没有或者具备较少编程和系统开发经验的初学者, 也可以作为大中专院校的教材及企业新人的培训用书 著 [ 日 ]MICK 译孙淼罗勇责任编辑乐馨执行编辑徐骞 人民邮电出版社出版发行北京市崇文区夕照寺街 14 号邮编 电子邮件 315@ptpress.com.cn 网址 北京印刷 开本 : /16 印张 :19.25 字数 :455 千字 2013 年 7 月第 1 版印数 : 册 2013 年 7 月北京第 1 次印刷著作权合同登记号图字 : 号 定价 :69.00 元读者服务热线 :(010) 转 604 印装质量热线 :(010) 反盗版热线 :(010) 广告经营许可证 : 京崇工商广字第 0021 号

4 目录 IX 目录 前言关于本书读者对象学习本书前的预备知识本书涉及的关系数据库本书的学习安排随书光盘简介 III IV IV IV V V VI 第 1 章数据库和 SQL 数据库是什么 3 我们身边的数据库 3 为什么 DBMS 那么重要 4 DBMS 种类 数据库的结构 8 RDBMS 的常见系统结构 8 表的结构 SQL 概要 13 标准 SQL 13 SQL 语句及其种类 14 SQL 的基本书写规则 表的创建 18 表的内容的创建 18 数据库的创建 (CREATE DATABASE 语句 ) 19 表的创建 (CREATE TABLE 语句 ) 19 命名规则 21 数据类型的指定 22 约束的设置 表的删除和更新 25 表的删除 (DROP TABLE 语句 ) 25 表定义的更新 (ALTER TABLE 语句 ) 26 向 Shohin 表中插入数据 27 练习题 30

5 X 目录 第 2 章查询基础 SELECT 语句基础 33 列的查询 33 查询出表中所有的列 35 为列设定别名 36 常数的查询 38 从结果中删除重复行 38 根据 WHERE 语句来选择记录 41 注释的书写方法 算术运算符和比较运算符 45 算术运算符 45 需要注意 NULL 46 比较运算符 48 对字符串使用不等号时的注意事项 50 不能对 NULL 使用比较运算符 逻辑运算符 56 NOT 运算符 56 AND 运算符和 OR 运算符 58 通过括号进行强化 60 逻辑运算符和真值 62 含有 NULL 时的真值 64 练习题 66 第 3 章聚合与排序 对表进行聚合查询 69 聚合函数 69 计算表中数据的行数 70 计算 NULL 以外数据的行数 71 计算合计值 72 计算平均值 74 计算最大值和最小值 75

6 目录 XI 使用聚合函数删除重复值 ( 关键字 DISTINCT) 对表进行分组 79 GROUP BY 子句 79 聚合键中包含 NULL 的情况 81 使用 WHERE 子句时 GROUP BY 的执行结果 82 与聚合函数和 GROUP BY 子句有关的常见错误 为聚合结果指定条件 89 HAVING 子句 89 HAVING 子句的构成要素 92 相对于 HAVING 子句, 更适合写在 WHERE 子句中的条件 对查询结果进行排序 96 ORDER BY 子句 96 指定升序或降序 98 指定多个排序键 99 NULL 的顺序 99 在排序键中使用显示用别名 100 ORDER BY 子句中可以使用的列 102 不要使用列编号 102 练习题 104 第 4 章数据更新 数据的插入 (INSERT 语句的使用方法 ) 107 什么是 INSERT 107 INSERT 语句的基本语法 108 列清单的省略 111 插入 NULL 111 插入默认值 112 从其他表中复制数据 数据的删除 (DELETE 语句的使用方法 ) 117 DROP TABLE 语句和 DELETE 语句 117 DELETE 语句的基本语法 117

7 XII 目录 指定删除对象的 DELETE 语句 ( 搜索型 DELETE) 数据的更新 (UPDATE 语句的使用方法 ) 121 UPDATE 语句的基本语法 121 指定条件的 UPDATE 语句 ( 搜索型 UPDATE) 122 使用 NULL 进行更新 123 多列更新 事务 126 什么是事务 126 创建事务 127 ACID 特性 132 练习题 133 第 5 章复杂查询 视图 137 视图和表 137 创建视图的方法 139 视图的限制 1 定义视图时不能使用 ORDER BY 子句 142 视图的限制 2 对视图进行更新 143 删除视图 子查询 148 子查询和视图 148 子查询的名称 151 标量子查询 151 标量子查询的书写位置 154 使用标量子查询时的注意事项 关联子查询 156 普通的子查询和关联子查询的区别 156 关联子查询也是用来对集合进行切分的 159 结合条件一定要写在子查询中 160 练习题 161

8 目录 XIII 第 6 章函数 谓词 CASE 表达式 各种各样的函数 165 函数的种类 165 算术函数 166 字符串函数 170 日期函数 178 转换函数 谓词 186 什么是谓词 186 LIKE 谓词 字符串的部分一致查询 186 BETWEENT 谓词 范围查询 190 IS NULL IS NOT NULL 判断是否为 NULL 191 IN 谓词 OR 的简便用法 192 使用子查询作为 IN 谓词的参数 193 EXIST 谓词 CASE 表达式 202 什么是 CASE 表达式 202 CASE 表达式的语法 202 CASE 表达式的使用方法 203 练习题 209 第 7 章集合运算 表的加减法 213 什么是集合运算 213 表的加法 UNION 213 集合运算的注意事项 216 包含重复行的集合运算 ALL 选项 217 选取表中公共部分 INTERSECT 218 记录的减法 EXCEPT 联结 ( 以列为单位对表进行联结 ) 222 什么是联结 222

9 XIV 目录 内联结 INNER JOIN 223 外联结 OUTER JOIN 张以上表的联结 231 交叉联结 CROSS JOIN 234 特定的联结语句和过时的语法 237 练习题 242 第 8 章 SQL 高级处理 窗口函数 245 什么是窗口函数 245 窗口函数的语法 246 语法的基本使用方法 使用 RANK 函数 246 无须指定 PARTITION BY 249 专用窗口函数的种类 250 窗口函数的适用范围 251 作为窗口函数使用的聚合函数 252 计算移动平均 254 两个 ORDER BY GROUPING 运算符 259 同时计算出合计值 259 ROLLUP 同时计算出合计值和小计值 260 GROUPING 函数 让 NULL 更加容易分辨 265 CUBE 用数据来搭积木 267 GROUPING SETS 取得期望的积木 269 练习题 270 附录 A 安装 PostgreSQL 271 附录 B 在 PostgreSQL 中执行 SQL 的方法 278 附录 C 练习题答案 284

10 前言 III 前言 本书面向完全没有编程和系统开发经验的初学者, 介绍了关系数据库以及用来操作关系数据库的 SQL 语言的使用方法 各个章节结合具体示例进行解说, 并在每章的结尾安排了习题, 用来检验读者对该章内容的理解程度 大家可以从第 1 章开始, 亲自验证示例程序, 循序渐进地掌握 SQL 的基础知识和技巧 另外, 本书还将重要知识点总结为法则, 方便读者在学习完本书之后随时查阅 近年来, 和其他系统领域一样, 数据库领域也实现了飞速发展, 应用范围不断扩大 不但出现了具有新功能的数据库, 而且操作的数据量也大幅增长 本书将要介绍的关系数据库是时下最流行的数据库, 也是理解其他数据库的基础 在 系统领域, 通常所讲的数据库指的就是关系数据库, 其重要性可见一斑 估计很多读者今后都会慢慢积累各个领域 各种规模的系统开发经验 ( 或者可能已经开始从事开发方面的工作了 ) 到那时, 所有的系统必定都需要使用数据库 它们使用的数据库, 即便不是关系数据库, 也一定是以关系数据库为基础的数据库 从这个意义上看, 如果掌握了关系数据库和 SQL, 就能成为任何系统开发都需要的数据库专家了 帮助 本书旨在把数据库领域的精彩展示给大家, 衷心希望本书能为大家的进步提供一些 MICK

11 IV 前言 关于本书 本书是编程学习系列的 SQL 和关系数据库篇 该系列注重对初学者编程能力的培养, 本书秉承了这一宗旨 本书不仅可以用于自学, 也可以作为大学 专科学校和企业新人的培训用书 这里提供了大量的示例程序和详实的操作步骤说明, 读者可以亲自动手解决具体的问题, 切实提高自身的编程能力 另外, 在各章的结尾处还安排了习题来帮助大家复习该章的知识要点 习题的答案和讲解收录在附录 C 中, 大家可以验证自己的学习成果 读者对象 不了解数据库和 SQL 知识的人 虽然自学了一些 SQL 知识, 但仍希望进行系统学习的人 需要使用数据库, 但不知道从何入手的人 在大学 专科学校和企业的教育部门等从事数据库和 SQL 教学的人 希望了解信息处理考试中 SQL 部分应试策略的人 学习本书前的预备知识 了解 Windows 的基本操作方法 能够使用 Windows 的资源管理器创建文件夹并进行文件复制 能够使用 Windows 的记事本 ( 或者其他文本编辑器 ) 创建文本文件

12 前言 V 本书涉及的关系数据库 本书中使用的 SQL 语句全部都在下列关系数据库系统 (RDBMS) 中进行了验证 Oracle Database 11g SQL Server 2008 DB2 9.7 PostgreSQL 8.4 MySQL 5.5 在这 5 种数据库之间存在差异的 SQL 语句, 或者只能在某种特定的 RDBMS 中使用的 SQL 语句, 本书都用下列图标进行标识, 用来提示 SQL 语句执行所使用的 RDBMS Oracle SQL Server DB2 PostgreSQL MySQL 反之, 在所有 RDBMS 中都能正常执行的 SQL 语句则不用图标标识 本书的学习安排 首先, 在第 1 章前半部分学习关系数据库和 SQL 的基础知识, 然后结合具体的 SQL 示 例程序再进行循序渐进的学习 在 SQL 学习方面, 最重要的就是以下两点 : 亲自编写 SQL 语句 通过执行 SQL 语句来学习和理解数据库操作 要提高学习效率, 则需尽量亲自执行并验证本书中的示例程序, 逐步深入学习 随书光盘中收录了 SQL 的学习环境 PostgreSQL 在开始学习之前, 请在自己的电 脑上安装该数据库, 为执行 SQL 语句做好准备 在附录 A 和附录 B 中总结了该数据库的安

13 VI 前言 装和 SQL 语句的执行方法 如果你已经安装了前述 本书涉及的关系数据库 中记载的数据库, 也可以直接使用 另外, 如无特殊说明, 本书中记述的 SQL 语句的执行结果, 都是在 PostgreSQL 8.4 中执行的结果 编程学习系列 SQL 基础教程 的教辅网站 随书光盘简介 附赠的随书光盘中收录了本书使用的示例程序和 SQL 的学习环境 (PostgreSQL) 随书光盘的文件结构如下所示 : ReadMe.txt 随书光盘的使用要点 PostgreSQL_Installer SQL 的学习环境 (PostgreSQL) Sample 第 1 章到第 8 章的示例程序 answer 习题答案 ( 示例程序 ) ReadMe.txt 文件介绍了随书光盘的内容和要点, 使用前请务必阅读该文件 PostgreSQL_Installer 文件夹你可以很容易地将 PostgreSQL 安装在 Windows 系统的电脑上 PostgreSQL 的安装方 法以及在 PostgreSQL 中录入和执行 SQL 语句的方法, 请参考附录 A 和附录 B Sample 文件夹本书中所使用的示例程序分别保存在以章节为单位的文件夹中 在 Sample\CreateTable 文件夹中, 按照 RDBMS 的不同, 分别保存了用来创建示例用表的 SQL 语句

14 前言 VII Sample Ch01 第 1 章的示例程序 1_4 1-4 节的示例程序 1_5 1-5 节的示例程序 Ch08 第 8 章的示例程序 8_1 8-1 节的示例程序 8_2 8-2 节的示例程序 CreateTable 创建示例用表的 SQL 语句 DB2 MySQL Oracle SQLServer PostgreSQL answer 文件夹 各章末习题的答案 ( 示例程序 ), 分别保存在以章为单位的目录中 关于示例程序 随书光盘中收录的示例程序的文件名, 与书中记述的列表号码相对应 例如,1-5 节记述的代码清单 1-3 的示例程序, 保存的位置和文件名如下所示 Sample Ch01 1_5 List1_3.sql 另外, 像如下代码清单这样, 在不同的 RDBMS 中存在差异的 SQL 语句, 会在其文件名的末尾加上 RDBMS 的名称 代码清单 1-4 增加一个可以保存 100 位可变长度字符串的列 shohin_mei_kana DB2 PostgreSQL MySQL ALTER TABLE Shohin ADD COLUMN shohin_mei_kana VARCHAR(100);

15 VIII 前言 Oracle ALTER TABLE Shohin ADD (shohin_mei_kana VARCHAR(100)); SQL Server ALTER TABLE Shohin ADD shohin_mei_kana VARCHAR(100); 这种情况下, 示例程序的文件名如下所示 List1_4_DB2_PostgreSQL_MySQL.sql List1_4_Oracle.sql List1_4_SQL Server.sql 创建示例用表的 SQL 语句 创建示例用表用的 SQL 文件保存在 Sample\CreateTable 文件夹中 文件名为 CreateTable 表名.sql 例如,PostgreSQL 用到的表 Shohin 保存在下述目录中 Sample CreateTable PostgreSQL CreateTableShohin.sql 保存在 Sample 文件夹中的示例程序文件, 可以使用 Windows 的记事本 ( 或者其他文 本编辑器 ) 打开 声明 翔泳有限公司 随书光盘中的文件已经经过编辑部确认, 在正常使用时不会出现任何问题 对于文件执行结果所造成的任何损失, 本书作者 软件开发人员和翔泳公司概不承担相关责任 随书光盘 Sample 文件夹中所收录文件的著作权归本书作者所有 读者可以出于个人目的, 根据需要自行使用和修改其中的程序 对于个别环境相关的问题, 以及由本书对应范围外的环境设置所造成的执行错误, 本公司概不负责解答

16 第 5 章复杂查询 视图子查询关联子查询

17 本章重点 前几章和大家一起学习了表的创建 查询和更新这样一整套数据库的操作方法 从本章开始, 大家将会在这些基本方法的基础上, 学习一些实际应用中的方法 本章将以此前学过的 SELECT 语句, 以及嵌套在 SELECT 语句中的视图和子查询等技术为中心进行学习 由于视图和子查询可以像表一样进行使用, 所以如果能恰当的使用这些技术, 就可以写出更加灵活的 SQL 了 5-1 视图 视图和表 创建的视图方法 视图的限制 1 定义视图时不能使用 ORDER BY 子句 视图的限制 2 对视图进行更新 删除视图 5-2 子查询 子查询和视图 子查询的名称 标量子查询 标量子查询的书写位置 使用标量子查询时的注意事项 5-3 关联子查询 普通的子查询和关联子查询的区别 关联子查询也是用来对集合进行切分的 结合条件一定要写在子查询中

18 5-1 视图 第 5 章复杂查询 视图 学习重点 从 SQL 的角度来看, 视图和表是相同的 两者的区别在于表中保存的是实际的数据, 而视图中保存的时 SELECT 语句 ( 视图本身并不存储数据 ) 使用视图, 可以轻松完成跨多表查询数据等复杂操作 可以将常用的 SELECT 语句做成视图来使用 创建视图需要使用 CREATE VIEW 语句 视图包含 不能使用 ORDER BY 和 可对其进行有限制的更新 两项限制 删除视图需要使用 DROP VIEW 语句 视图和表 KEYWORD 视图 我们首先要学习的是一个新的工具 视图 究竟视图是什么呢? 如果用一句话概述的话, 就是 从 SQL 的角度来看视图就是一张表 实际上, 在 SQL 语句中并不需要区分哪些是表, 哪些是视图 只需要知道在更新时它们之间存在一些不同就可以了, 之后会为大家进行介绍 至少在编写 SELECT 语句时并不需要特别在意表和视图有什么不同 那么视图和表到底有什么不同呢? 区别只有一个, 那就是 是否保存了实际的数据 通常, 我们在创建表时, 会通过 INSERT 语句将数据保存到数据库之中 而数据库中的数据实际上会被保存到计算机的存储设备 ( 通常是硬盘 ) 中 因此, 我们通过 SELECT 语句查询数据时, 实际上就是从存储设备 ( 硬盘 ) 中读取数据, 进行各种计算之后, 再将结果返回给用户这样一个过程 但是使用视图时并不会将数据保存到存储设备之中, 而且也不会将数据保存到其他任何地方 实际上视图保存的是 SELECT 语句 ( 图 5-1) 我们从视图中读取数据时, 视图会在内部执行该 SELECT 语句并创建出一张临时表

19 138 第 5 章复杂查询 图 5-1 视图和表 存储设备 ( 硬盘 ) 使用视图时会执行 SELECT 语句并创建出一张临时表 数据库 表 执行 SELECT 语句 视图 SELECT FROM Shohin GROUP BY shohin_bunrui; 表中保存的是实际数据 视图中保存的是 SELECT 语句 视图的优点 视图的优点大体有两点 第一点是由于视图无需保存数据, 因此可以节省存储设备的容量 例 如, 我们在 4-1 节中创建了用来汇总商品种类 (shohin_bunrui) 的表 由 于该表中的数据最终都会保存到存储设备之中, 因此会占用存储设备的数 据领域 但是, 如果把同样的数据作为视图保存起来的话, 就只需要代码 清单 5-1 那样的 SELECT 语句就可以了, 这样就节省了存储设备的数据领域 代码清单 5-1 通过视图等 SELECT 语句保存数据 SELECT shohin_bunrui, SUM(hanbai_tanka), SUM(shiire_tanka) FROM Shohin GROUP BY shohin_bunrui; 由于本示例中表的数据量充其量只有几行, 所以使用视图并不会大幅 缩小数据的大小 但是在实际的业务中数据量往往非常大, 这时使用视图 所节省的容量就会非常可观了 法则 5-1 表中存储的是实际数据, 而视图中保存的是从表中取出数据所使用的 SELECT 语句 第二个优点就是可以将频繁使用的 SELECT 语句保存成视图, 这样

20 5-1 视图 139 就不用每次都重新书写了 创建好视图之后, 只需在 SELECT 语句中进行调用, 就可以方便地得到想要的结果了 特别是在计算合计, 以及由于包含复杂的查询条件导致 SELECT 语句非常庞大时, 使用视图可以大大提高效率 而且, 视图中的数据会随着原表的变化自动更新 视图归根到底就是 SELECT 语句, 所谓 参照视图 也就是 执行 SELECT 语句 的意思 因 注 A 数据保存在表中时, 必须要执行明示的 SQL 更新语句才能对数据进行更新 此可以保证数据的最新状态 这也是将数据保存在表中所不具备的优势 A 法则 5-2 应该将经常使用的 SELECT 语句做成视图 创建视图的方法 KEYWORD CREATE VIEW 语句 创建视图需要使用 CREATE VIEW 语句 语法如下所示 语法 5-1 创建视图的 CREATE VIEW 语句 CREATE VIEW 视图名称 (< 视图列名 1>, < 视图列名 2>, ) AS <SELECT 语句 > SELECT 语句需要书写在 AS 关键字之后 SELECT 语句中列的排列顺序和视图中列的排列顺序相同,SELECT 语句中的第 1 列就是视图中的第 1 列,SELECT 语句中的第 2 列就是视图中的第 2 列, 以此类推 视图的列名在视图名称之后的列表中定义 备忘接下来, 我们将会以此前使用的 Shohin( 商品 ) 表为基础来创建视图 如果大家已经根据之前章节的内容更新了 Shohin 表中的数据, 请在创建视图之前将数据恢复到初始状态 操作步骤如下所示 1 删除 Shohin 表中的数据, 将表清空 DELETE FROM Shohin;

21 140 第 5 章复杂查询 2 执行代码清单 1-6 中的 SQL 语句, 将数据插入到空表 Shohin 中 2 中的 SQL 语句 (CreateTableShohin.sql) 收录在随书光盘的 CD-ROM\ Sample\CreateTable\PostgreSQL 目录中 下面就让我们试着来创建视图吧 和此前一样, 这次我们还是将 Shohin 表 ( 代码清单 5-2) 作为基本表 代码清单 5-2 ShohinSum 视图 CREATE VIEW ShohinSum (shohin_bunrui, cnt_shohin) AS SELECT shohin_bunrui, COUNT(*) FROM Shohin GROUP BY shohin_bunrui; 视图定义中的主体 ( 内容只是一条 SELECT 语句 ) 视图的列名 这样我们就在数据库中创建出了一幅名为 ShohinSum( 商品合计 ) 的视图 请大家一定不要省略第 2 行的关键字 AS 这里的 AS 与定义别名 时使用的 AS 并不相同, 如果省略就会发生错误 虽然很容易混淆, 但是 语法就是这么决定的, 所以还是请大家将其当作一条规定加以牢记 接下来, 我们来学习视图的使用方法 视图和表一样, 可以书写在 SELECT 语句的 FROM 子句之中 ( 代码清单 5-3) 代码清单 5-3 使用视图 SELECT shohin_bunrui, cnt_shohin FROM ShohinSum; 执行结果 shohin_bunrui cnt_shohin 衣服 2 办公用品 2 厨房用具 4 在 FROM 子句中使用视图来代替表 通过上述视图 ShohinSum 定义的主体 (SELECT 语句 ) 我们可以看出, 该视图将根据商品种类 (shohin_bunrui) 统计出的商品数量 (cnt_ shohin) 作为结果保存了起来 这样如果大家在工作中需要频繁进行统计时, 就不用每次都书写使用 GROUP BY 和 COUNT 函数的 SELECT 语句, 从 Shohin 表中取得数据了 创建出视图之后, 就可以通过非常简单的

22 5-1 视图 141 SELECT 语句, 随时得到想要的合计结果了 并且如前所述,Shohin 表中的数据更新之后, 视图也会自动更新, 非常灵活方便 之所以能够实现上述功能, 是因为视图就是保存好的 SELECT 语句 定义视图时可以使用任何 SELECT 语句 既可以使用 WHERE GROUP BY HAVING, 也可以通过 SELECT * 来指定全部列 使用视图的查询 在 FROM 子句中使用视图的查询, 通常有如下两个步骤 : 1 首先执行定义视图的 SELECT 语句, 注 A 但是根据实现方式的不同, 也存在内部使用视图的 SELECT 语句本身进行重组的 DBMS KEYWORD 多重视图 2 根据得到的结果, 再执行在 FROM 子句中使用视图的 SELECT 语句也就是说, 使用视图的查询通常需要执行 2 条以上的 SELECT 语句 A 这里没有使用 2 条 而使用了 2 条以上, 是因为还可能出现以视图为基础创建出的类似楼中楼那样的多重视图 ( 图 5-2) 例如, 我们可以像代码清单 5-4 那样以 ShohinSum 为基础创建出视图 ShohinSumJim 图 5-2 可以在视图的基础上创建视图 视图 D 视图 A 视图 C 视图 B 表

23 142 第 5 章复杂查询 代码清单 5-4 视图 ShohinSumJim CREATE VIEW ShohinSumJim (shohin_bunrui, cnt_shohin) AS SELECT shohin_bunrui, cnt_shohin FROM ShohinSum 以视图为基础创建视图 WHERE shohin_bunrui = ' 办公用品 '; -- 确认是否创建出了视图 SELECT shohin_bunrui, cnt_shohin FROM ShohinSumJim; 执行结果 shohin_bunrui cnt_shohin 办公用品 2 虽然语法上没有错误, 但是我们还是应该尽量避免在视图的基础上创 建视图 这是因为对多数 DBMS 来说, 多重视图会降低 SQL 的性能 因此, 希望大家 ( 特别是刚刚接触视图的读者 ) 能够使用单一视图 法则 5-3 应该避免在视图的基础上创建视图 介绍 除此之外, 在使用时还要注意视图有两个限制, 接下来会给大家详细 视图的限制 1 定义视图时不能使用 ORDER BY 子句 虽然之前我们说过在定义视图时可以使用任何 SELECT 语句, 但其实有一种情况例外, 那就是不能使用 ORDER BY 子句 因此下述的视图定义语句是错误的 -- 不能像这样定义视图 CREATE VIEW ShohinSum (shohin_bunrui, cnt_shohin) AS SELECT shohin_bunrui, COUNT(*) FROM Shohin GROUP BY shohin_bunrui ORDER BY shohin_bunrui; 定义视图时不能使用 ORDER BY 子句

24 5-1 视图 143 为什么不能使用 ORDER BY 子句呢? 这是因为视图和表一样, 数据行 都是没有顺序的 实际上, 有些 DBMS 在定义视图的语句中是可以使用 注 A 例如, 在 PostgreSQL 中上述 SQL 语句就没有问题, 可以执行 ORDER BY 子句的 A, 但是这并不是通用的语法 因此, 在定义视图时请 不要使用 ORDER BY 子句 法则 5-4 定义视图时不要使用 ORDER BY 子句 视图的限制 2 对视图进行更新 之前我们说过, 在 SELECT 语句中视图可以和表一样进行使用 那么, 对于 INSERT DELETE UPDATE 这类更新语句 ( 更新数据的 SQL) 来说会怎么样呢? 实际上, 虽然这其中有很严格的限制, 但是某些时候也可以对视图进行更新 标准 SQL 中有这样的规定 : 如果定义视图的 SELECT 语句能够满足某些条件, 那么这个视图就可以被更新 下面就给大家列举一些比较具有代表性的条件 1 SELECT 子句中未使用 DISTINCT 2 FROM 子句中只有一张表 3 未使用 GROUP BY 子句 4 未使用 HAVING 子句 此前几章的例子中,FROM 子句里通常只有一张表 因此, 大家可能会觉得 2 中的条件有些奇怪, 但其实 FROM 子句中也可以并列使用多张表 大家在学习完下一章 表结合 的操作之后就明白了 其他的条件大多数都与聚合有关 简单来说, 像这次的例子中使用的 ShohinSum 那样, 使用视图来保存原表聚合结果时, 是无法判断如何将视图的更改反映到原表中的 例如, 对 ShohinSum 视图执行如下 INSERT 语句 INSERT INTO ShohinSum VALUES (' 电器制品 ', 5);

25 144 第 5 章复杂查询 但是, 上述 INSERT 语句会发生错误 这是因为视图 ShohinSum 是 通过 GROUP BY 子句对原表进行聚合而得到的 为什么通过聚合得到的 视图不能进行更新呢? 视图归根结底还是从表派生出来的, 因此, 如果原表可以更新, 那么 视图中的数据也可以更新 反之亦然, 如果视图发生了改变, 而原表没有 进行相应更新的话, 就无法保证数据的一致性了 使用前述 INSERT 语句, 向视图 ShohinSum 中添加数据 ( 电器制 品,5) 时, 原表 Shohin 应该如何更新才好呢? 按理说应该向表中添加 商品种类为 电器制品 的 5 行数据, 但是这些商品对应的商品编号 商 品名称和销售单价等我们都不清楚 ( 图 5-3) 数据库在这里就遇到了麻烦 图 5-3 通过聚合得到的视图无法更新 视图 ShohinSum( 商品合计 ) 视图 ShohinSum( 商品合计 ) shohin_bunrui ( 商品分类 ) cnt_shohin ( 商品数量 ) shohin_bunrui ( 商品分类 ) cnt_shohin ( 商品数量 ) 衣服 2 INSERT 衣服 2 办公用品 2 办公用品 2 厨房用具 4 厨房用具 4 电器制品 5 INSERT 语句 INSERT INTO ShohinSum VALUES (' 电器制品 ', 5); 只清楚商品种类和添加行数 INSERT 视图 ShohinSum( 商品合计 ) shohin_id shohin_mei ( 商品编号 ) ( 商品名称 ) shohin_bunrui ( 商品分类 ) hanbai_tanka ( 销售单价 ) shiire_tanka ( 进货单价 ) torokubi ( 登记日期 ) 0001 T 恤衫 衣服 打孔器 办公用品 运动 T 恤 衣服 菜刀 厨房用具 高压锅 厨房用具 叉子 厨房用具 擦菜板 厨房用具 圆珠笔 办公用品 ?? 电器制品????? 电器制品????? 电器制品????? 电器制品????? 电器制品???

26 5-1 视图 145 法则 5-5 视图和表需要同时进行更新, 因此通过聚合得到的视图无法进行更新 能够更新视图的情况 像代码清单 5-5 这样, 不是通过聚合得到的视图就可以进行更新 代码清单 5-5 可以更新的视图 CREATE VIEW ShohinJim (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) AS SELECT * FROM Shohin 既没有聚合又没有结合的 SELECT 语句 WHERE E shohin_bunrui nr i = ' 办公用品 '; 表示下一行接续本行, 只是由于版面所限而换行 对于上述只包含办公用品类商品的视图 ShohinJim 来说, 就可以执行类似代码清单 5-6 这样的 INSERT 语句 代码清单 5-6 向视图中添加数据行 INSERT INTO ShohinJim VALUES ('0009', ' 印章 ', ' 办公用品 ', 95, 10, ' '); 向视图中添加一行 表示下一行接续本行, 只是由于版面所限而换行 注意事项 由于 PostgreSQL 中的视图会被初始设定为只读, 所以执行代码清单 5-6 中的 INSERT 语句时, 会发生下面这样的错误 执行结果 ( 使用 PostgreSQL) ERROR: 不能向视图中插入数据 HINT: 需要一个无条件的 ON INSERT DO INSTEAD 规则 因此, 在执行 INSERT 语句执行之前, 需要使用代码清单 5-A 中的指令来允许更新操作 在 DB2 和 MySQL 等其他 DBMS 中, 并不需要执行这样的指令 代码清单 5-A 允许 PostgreSQL 对视图进行更新 PostgreSQL CREATE OR REPLACE RULE insert_rule AS ON INSERT TO ShohinJim DO INSTEAD INSERT INTO Shohin VALUES ( new.shohin_id,

27 146 第 5 章复杂查询 new.shohin_mei, new.shohin_bunrui, new.hanbai_tanka, new.shiire_tanka, new.torokubi); 下面让我们使用 SELECT 语句来确认数据行是否添加成功吧 视图 -- 确认数据是否已经添加到视图中了 SELECT * FROM ShohinJim; 执行结果 shohin_id shohin_mei shohin_bunrui hanbai_tanka shiire_tanka torokubi 打孔器 办公用品 圆珠笔 办公用品 印章鑑 办公用品 数据已经被添加进来了 原表 -- 确认数据是否已经添加到原表中了 SELECT * FROM Shohin; 执行结果 shohin_id shohin_mei shohin_bunrui hanbai_tanka shiire_tanka torokubi T 恤衫 衣服 打孔器 办公用品 运动 T 恤 衣服 菜刀 厨房用具 高压锅 厨房用具 叉子 厨房用具 擦菜板 厨房用具 圆珠笔 办公用品 印章鑑 办公用品 UPDATE 语句和 DELETE 语句当然也可以像操作表时那样正常执行, 但是对于原表来说却需要设置各种各样的约束 ( 主键和 NOT NULL 等 ), 需要特别注意 数据已经被添加进来了

28 5-1 视图 147 删除视图 KEYWORD DROP VIEW 语句 删除视图需要使用 DROP VIEW 语句 语法如下所示 语法 5-2 删除视图的 DROP VIEW 语句 DROP VIEW 视图名称 (< 视图列名 1>, < 视图列名 2>, ) 例如, 想要删除视图 ShohinSum 时, 就可以使用代码清单 5-7 中的 SQL 语句 代码清单 5-7 删除视图 DROP VIEW ShohinSum; 特定的 SQL 在 PostgreSQL 中, 如果想要删除以视图为基础创建出来的多重视图的话, 由于存在关联的视图, 所以会发生如下错误 执行结果 ( 使用 Postgre) ERROR: 由于存在关联视图, 所以无法删除视图 shohinsum DETAIL: 视图 shohinsumjim 与视图 shohinsum 相关联 HINT: 删除关联对象请使用 DROP CASCADE 表示下一行接续本行, 只是由于版面所限而换行 这时可以像下面这样, 使用 CASCADE 选项来删除关联视图 PostgreSQL DROP VIEW ShohinSum CASCADE; 备忘 下面我们再次将 Shohin 表恢复到初始状态 (8 行 ) 请执行如下 DELETE 语句, 删除之前添加的 1 行数据 代码清单 5-B -- 删除商品编号为 0009( 印章 ) 的数据 DELETE FROM Shohin WHERE shohin_id = '0009';

29 148 第 5 章复杂查询 5-2 第 5 章复杂查询 子查询 学习重点 一言以蔽之, 子查询就是一次性的视图 (SELECT 语句 ) 与视图不同, 子 查询在 SELECT 语句执行完毕之后就会消失 由于子查询需要命名, 因此需要根据处理内容来指定恰当的名称 标量子查询就是只能返回一行一列的子查询 子查询和视图 KEYWORD 子查询 前一节我们学习了视图这个非常方便的工具, 本节将学习以视图为基础的子查询 子查询的特点概括起来就是一张一次性视图 我们先来复习一下视图的概念, 视图并不是用来保存数据的, 而是通过保存读取数据的 SELECT 语句的方法来为用户提供便利的工具 反之, 子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中 接下来, 就让我们拿前一节使用的视图 ShohinSum( 商品合计 ) 来与子查询进行一番比较吧 首先, 我们再来看一下视图 ShohinSum 的定义和视图所对应的 SELECT 语句 ( 代码清单 5-8) 代码清单 5-8 视图 ShohinSum 和确认用的 SELECT 语句 -- 根据商品种类统计商品数量的视图 CREATE VIEW ShohinSum (shohin_bunrui, cnt_shohin) AS SELECT shohin_bunrui, COUNT(*) FROM Shohin GROUP BY shohin_bunrui; -- 确认视图是否已经创建成功 SELECT shohin_bunrui, cnt_shohin FROM ShohinSum; 能够实现同样功能的子查询如代码清单 5-9 所示

30 5-2 子查询 149 代码清单 5-9 子查询 SQL Server DB2 PostgreSQL MySQL -- 直接在 FROM 子句中使用定义视图的 SELECT 语句 SELECT shohin_bunrui, cnt_shohin FROM ( SELECT shohin_bunrui, COUNT(*) AS cnt_shohin FROM Shohin GROUP BY shohin_bunrui nr ) AS ShohinSum; 直接使用定义视图的 SELECT 语句 特定的 SQL 在 Oracle 的 FROM 子句中, 不能使用 AS( 会发生错误 ) 因此, 在 Oracle 中执行代码清单 5-9 时, 需要将 1 中的 ) AS ShohinSum; 变为 ) ShohinSum; 执行结果 两种方法得到结果完全相同 shohin_bunrui cnt_shohin 衣服 2 办公用品 2 厨房用具 4 如上所示, 子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中 虽然 AS ShohinSum 就是子查询的名称, 但由于该名称是一次性的, 因此不会像视图那样保存在存储介质 ( 硬盘 ) 之中, 而是在 SELECT 语句执行之后就消失了 子查询 (subquery) 就是 次级 (sub) 的 查询 (query) 实际上, 该 SELECT 语句包含嵌套的结构, 首先会执行 FROM 子句中的 SELECT 语句, 然后才会执行外层的 SELECT 语句 ( 图 5-4) 图 5-4 SELECT 语句的执行顺序 2 外层的查询 (SELECT 子句 ) SELECT shohin_bunrui, cnt_shohin FROM ( SELECT shohin_bunrui, COUNT(*) AS cnt_shohin FROM Shohin GROUP BY shohin_bunrui ) AS ShohinSum; 1 内层的查询 (FROM 子句中的 SELECT 子句 )

31 150 第 5 章复杂查询 1 首先执行 FROM 子句中的 SELECT 语句 ( 子查询 ) SELECT shohin_bunrui, COUNT(*) AS cnt_shohin FROM Shohin GROUP BY shohin_bunrui; 2 根据 1 的结果执行外层的 SELECT 语句 SELECT shohin_bunrui, cnt_shohin FROM ShohinSum; 法则 5-6 子查询作为内层查询会首先执行 增加子查询的层数 由于子查询的层数原则上没有限制, 因此可以像 子查询的 FROM 子 句中还可以继续使用子查询, 该子查询的 FROM 子句中还可以再使用子查 询 这样无限嵌套下去 ( 代码清单 5-10) 代码清单 5-10 尝试增加子查询的嵌套层数 SQL Server DB2 PostgreSQL MySQL SELECT shohin_bunrui, cnt_shohin FROM (SELECT * FROM (SELECT shohin_bunrui, COUNT(*) AS cnt_shohin FROM Shohin GROUP BY shohin_bunrui) AS ShohinSum 1 WHERE cnt_shohin = 4) AS ShohinSum2; 2 特定的 SQL 在 Oracle 的 FROM 子句中不能使用 AS( 会发生错误 ) 因此, 在 Oracle 中执行代码清单 5-10 时, 需要将 1 中的 ) As AS ShohinSum : 变为 ShohinSum :, 将 2 中的 ) AS ShohinSum2; 变为 ShohinSum2; 执行结果 shohin_bunrui cnt_shohin 厨房用具 4 最内层的子查询 (ShohinSum) 与之前一样, 根据商品种类 (shohin_ bunrui) 对数据进行聚合, 其外层的子查询将商品数量 (cnt_shohin)

32 5-2 子查询 151 限定为 4, 结果就得到了 1 行厨房用具的数据 但是, 随着子查询嵌套层数的增加,SQL 语句会变得越来越难读懂, 性能也会越来越差 因此, 请大家尽量避免使用多层嵌套的子查询 子查询的名称 之前的例子中我们给子查询设定了 ShohinSum 等名称 原则上子查询必须设定名称, 因此请大家尽量从处理内容的角度出发为子查询设定恰当的名称 在上述例子中, 子查询用来对 Shohin 表的数据进行汇集, 因此我们使用了后缀 Sum 作为其名称 注 A 其中也有像 Oracle 这样, 在名称之前使用 AS 关键字就会发生错误的数据库, 大家可以将其视为例外的情况 省略 A 为子查询设定名称时需要使用 AS 关键字, 该关键字有时也可以 标量子查询 KEYWORD 标量子查询 标量 接下来我们学习子查询中的标量子查询 (scalar subquery) 什么是标量 标量就是单一的意思, 在数据库之外的领域也经常使用 上一节我们学习的子查询基本上都会返回多行结果 ( 虽然偶尔也会只返回 1 行数据 ) 由于结构和表相同, 所以也会有查询不到结果的情况 而标量子查询则有一个特殊的限制, 那就是必须而且只能返回 1 行 1 列的结果 也就是返回表中某一行的某一列的值, 例如 10 或者 东京都 KEYWORD 返回值返回值就是函数或者 SQL 语句等处理执行之后作为结果返回的值 这样的值 法则 5-7 标量子查询就是返回单一值的子查询 细心的读者可能会发现, 由于返回的是单一的值, 因此标量子查询的 返回值可以用在 = 或者 <> 这样需要单一值的比较运算符之中 这也正是 标量子查询的优势所在 下面就让我们赶快来试试看吧

33 152 第 5 章复杂查询 在 WHERE 子句中使用标量子查询在 4-2 节中, 我们练习了通过各种各样的条件从 Shohin( 商品 ) 表中读取数据 大家有没有想过通过下面这样的条件查询数据呢? 查询出销售单价高于平均销售单价的商品 或者说想知道价格处于上游的商品时, 也可以通过上述条件进行查询 然而这并不是用普通方法就能解决的 如果我们像下面这样使用 AVG 函数的话, 就会发生错误 -- 在 WHERE 子句中不能使用聚合函数 SELECT shohin_id, shohinmei, hanbai_tanka FROM Shohin WHERE hanbai_tanka > AVG(hanbai_tanka); 大于销售平均单价 这样的条件 虽然这样的 SELECT 语句看上去能够满足我们的要求, 但是由于在 WHERE 子句中不能使用聚合函数, 因此这样的 SELECT 语句是错误的 那么究竟什么样的 SELECT 语句才能满足上述条件呢? 这时标量子查询就可以发挥它的功效了 首先, 如果想要求出 Shohin 表中商品的平均销售单价 (hanbai_tanka), 可以使用代码清 单 5-11 中的 SELECT 语句 代码清单 5-11 计算平均销售单价的标量子查询 SELECT AVG(hanbai_tanka) FROM Shohin; 执行结果 avg AVG 函数的使用方法和 COUNT 函数相同 其计算表达式如下所示 ( ) / 8= 这样计算出的平均单价大约就是 2100 日元 不难发现, 代码清单 5-11 中的 SELECT 语句的查询结果是单一的值 (2097.5) 因此, 我们可以直接将这个结果用到之前失败的查询之中 正确的 SQL 如代码清单 5-12 所示

34 5-2 子查询 153 代码清单 5-12 选取出销售单价 (hanbai_tanka) 高于全部商品的平均单价的商品 SELECT shohin_id, shohin_mei, hanbai_tanka FROM Shohin WHERE hanbai_tanka > (SELECT AVG(hanbai_tanka) FROM Shohin); 执行结果 shohin_id shohin_mei hanbai_tanka 运动 T 恤 菜刀 高压锅 6800 计算平均销售单价的标量子查询 前一节我们已经介绍过, 使用子查询的 SQL 会从子查询开始执行 因此, 这种情况下也会先执行下述计算平均单价的子查询 ( 图 5-5) -- 1 内层的子查询 SELECT AVG(hanbai_tanka) FROM Shohin ; 子查询的结果是 , 因此会用该值替换子查询的部分, 生成 如下 SELECT 语句 -- 2 外层的查询 SELECT shohin_id, shohin_mei, hanbai_tanka FROM Shohin WHERE hanbai_tanka > 大家都能看出该 SQL 没有任何问题可以正常执行 结果如上所述 图 5-5 SELECT 语句的执行顺序 ( 标量子查询 ) 1 首先执行内层的子查询 结果是 SELECT shohin_id, shohin_mei, hanbai_tanka FROM Shohin WHERE hanbai_tanka > (SELECT AVG(hanbai_tanka) FROM Shohin); 将 1 的结果代入到 2 中, 执行外层的查询 SELECT shohin_id, shohin_mei, hanbai_tanka FROM Shohin WHERE hanbai_tanka > ;

35 154 第 5 章复杂查询 标量子查询的书写位置 标量子查询的书写位置并不仅仅局限于 WHERE 子句中, 通常任何可以使用单一值的位置都可以使用 也就是说, 能够使用常数或者列名的地方, 无论是 SELECT 子句 GROUP BY 子句 HAVING 子句, 还是 ORDER BY 子句, 几乎所有的地方都可以使用 例如, 在 SELECT 子句当中使用之前计算平均值的标量子查询的 SQL 语句, 如代码清单 5-13 所示 代码清单 5-13 在 SELECT 子句中使用标量子查询 SELECT shohin_id, shohin_mei, hanbai_tanka, (SELECT AVG(hanbai_tanka) FROM Shohin) AS avg_tanka FROM Shohin; 标量子查询 执行结果 shohin_id shohin_mei hanbai_tanka avg_tanka T 恤衫 打孔器 运动 T 恤 菜刀 高压锅 叉子 擦菜板 圆珠笔 从上述结果可以看出, 在商品一览表中加入了全部商品的平均单价 有时我们会需要这样的单据 此外, 我们还可以像代码清单 5-14 中的 SELECT 语句那样, 在 HAVING 子句中使用标量子查询 代码清单 5-14 在 HAVING 子句中使用标量子查询 SELECT shohin_bunrui, AVG(hanbai_tanka) FROM Shohin GROUP BY shohin_bunrui HAVING AVG(hanbai_tanka) > (SELECT AVG(hanbai_tanka) FROM Shohin); hi 标量子查询

36 5-2 子查询 155 执行结果 shohin_bunrui avg 衣服 厨房用具 该查询的含义是想要选取出按照商品种类计算出的销售单价高于全部 商品的平均销售单价的商品种类 如果在 SELECT 语句中不使用 HAVING 子句的话, 那么平均销售单价为 300 日元的办公用品也会被选取出来 但是, 由于全部商品的平均销售单价是 日元, 因此低于该平均值的办公用 品会被 HAVING 子句中的条件排除在外 使用标量子查询时的注意事项 最后我们来介绍一下使用标量子查询时的注意事项 那就是该子查询绝对不能返回多行结果 也就是说如果子查询返回了多行结果, 那么它就不再是标量子查询, 而仅仅是一个普通的子查询了 因此不能被用在 = 或者 <> 等需要单一输入值的运算符当中, 也不能用在 SELECT 等子句当中 例如, 如下的 SELECT 子查询会发生错误 -- 由于不是标量子查询, 因此不能在 SELECT 子句中使用 SELECT shohin_id, shohin_mei, hanbai_tanka, (SELECT AVG(hanbai_tanka) FROM Shohin GROUP BY shohin_bunrui) AS avg_tanka FROM Shohin; 子查询 发生错误的原因很简单, 就是因为会返回如下多行结果 avg 注 A 例如, 使用 PostgreSQL 时会返回如下错误 ERROR : 副查询中使用了返回多行结果的表达式 在 1 行 SELECT 子句之中当然不可能使用 3 行数据 因此, 上述 SELECT 语句会返回 因为子查询返回了多行数据所以不能执行 这样的 错误信息 A

37 156 第 5 章复杂查询 5-3 第 5 章复杂查询 关联子查询 学习重点 关联子查询会在细分的组内进行比较时使用 关联子查询和 GROUP BY 子句一样, 也可以对表中的数据进行切分 关联子查询的结合条件如果未出现在子查询之中就会发生错误 普通的子查询和关联子查询的区别 按此前所学, 使用子查询就能选取出销售单价 (hanbai_tanka) 高于全部商品平均销售单价的商品 这次我们稍稍改变一下条件, 选取出各商品分类中高于该分类平均销售单价的商品 按照商品分类与平均销售单价进行比较只通过语言描述可能难以理解, 还是让我们来看看具体示例吧 我们以厨房用具中的商品为例, 该分组中包含了表 5-1 所示的 4 种商品 表 5-1 厨房用具中的商品 商品名称 销售单价 菜刀 3000 高压锅 6800 叉子 500 擦菜板 880 因此, 计算上述 4 种商品的平均价格的算术表达式如下所示 ( ) / 4=2795 ( 日元 ) 这样我们就能得知该分组内高于平均价格的商品是菜刀和高压锅了, 这两种商品就是我们要选取的对象 我们可以对余下的分组继续使用同样的方法 衣服分组的平均销售单价是 :

38 5-3 关联子查询 157 ( ) / 2=2500 ( 日元 ) 因此运动 T 恤就是要选取的对象 办公用品分组的平均销售单价是 : ( ) / 2=300 ( 日元 ) 因此打孔器就是我们要选取的对象 这样大家就能明白该进行什么样的操作了吧 我们并不是要以全部商 品为基础, 而是要以细分的组为基础, 对组内商品的平均价格和各商品的 销售单价进行比较 按照商品种类计算平均价格并不是什么难事 计算份额方法我们已经 学习过了, 只需按照代码清单 5-15 那样, 使用 GROUP BY 子句就可以了 代码清单 5-15 按照商品分类计算平均价格 SELECT AVG(hanbai_tanka) FROM Shohin GROUP BY shohin_bunrui; 但是, 如果我们使用前一节 ( 标量子查询 ) 的方法, 直接把上述 SELECT 语句使用到 WHERE 子句当中的话, 就会发生错误 -- 发生错误的子查询 SELECT shohin_id, shohinmei, hanbai_tanka FROM Shohin WHERE hanbai_tanka > (SELECT AVG(hanbai_tanka) FROM Shohin GROUP BY shohin_bunrui); 出错原因前一节已经讲过了, 该子查询会返回 3 行结果 ( ), 并不是标量子查询 在 WHERE 子句中使用子查询时, 该子查询的结果必须是单一的 但是, 如果以商品种类分组为单位, 对销售单价和平均单价进行比较, 除此之外似乎也没有什么办法了 到底应该怎么办才好呢? KEYWORD 关联子查询 使用关联子查询的解决方案这时就轮到我们的好帮手 关联子查询登场了 只需要在刚才的 SELECT 语句中追加一行, 就能得到我们想要的结果了 事实胜于雄辩, 还是让我们先来看看修改之后的 SELECT 语句吧 ( 代

39 158 第 5 章复杂查询 码清单 5-16) 代码清单 5-16 通过关联子查询按照商品种类对平均销售单价进行比较 SQL Server DB2 PostgreSQL MySQL SELECT shohin_id, shohin_mei, hanbai_tanka FROM Shohin AS S1 1 WHERE hanbai_tanka > (SELECT AVG(hanbai_tanka) FROM Shohin AS S2 2 该条件就是成功的关键! WHERE S1.shohin_bunrui = S2.shohin_bunrui GROUP BY shohin_bunrui); 特定的 SQL Oracle 中不能使用 AS( 会发生错误 ) 因此, 在 Oracle 中执行代码清单 5-16 时, 请大家把 1 中的 FROM Shohin AS S1 变为 FROM Shohin S1, 把 2 中的 FROM Shohin AS S2 变为 FROM Shohin S2 执行结果 shohin_id shohin_mei hanbai_tanka 办公用品 打孔器 500 衣服 运动 T 恤 4000 厨房用具 菜刀 3000 厨房用具 高压锅 6800 这样我们就能选取出办公用品 衣服和厨房用具三类商品中高于该类商品平均销售单价的商品了 这里起到关键作用的就是在子查询中添加的 WHERE 子句的条件 该条件的意思就是, 在同一商品种类中对各商品的销售单价和平均单价进行比较 这次由于作为比较对象的都是同一张 Shohin 表, 因此为了进行区别, 分别使用了 S1 和 S2 两个别名 在使用关联子查询时, 需要在表所对应的列名之前加上表的别名, 以 < 表名 >.< 列名 > 的形式记述 在对表中某一部分记录的集合进行比较时, 就可以使用关联子查询 因此, 使用关联子查询时, 通常会使用 限定 ( 绑定 ) 或者 限制 这样的语言 例如本次示例就是对 限定商品种类 的平均单价进行比较 法则 5-8 在细分的组内进行比较时, 需要使用关联子查询

40 5-3 关联子查询 159 关联子查询也是用来对集合进行切分的 换个角度来看, 其实关联子查询也和 GROUP BY 子句一样, 可以对集合进行进行切分 大家还记得我们用来说明 GROUP BY 子句的图 ( 图 5-6) 吗? 图 5-6 根据商品种类对表进行切分的图示 厨房用具 (4 条 ) 菜刀高压锅叉子擦菜板 衣服 (2 条 ) T 恤衫运动 T 恤 办公用品 (2 条 ) 打孔器圆珠笔 上图显示了作为记录集合的表是如何按照商品种类被切分的 使用关 联子查询进行切分的图示也基本相同 ( 图 5-7) 图 5-7 根据关联子查询进行切分的图示 厨房用具平均单价 =2975 衣服平均单价 =2500 办公用品平均单价 =300 我们首先需要计算各个商品分类中商品的平均销售单价, 由于该单价会用来和商品表中的各条记录进行比较, 因此关联子查询实际只能返回 1 行结果 这也是关联子查询不出错的关键 关联子查询执行时,DBMS 内部的执行结果图示请参见图 5-8

41 160 第 5 章复杂查询 图 5-8 关联子查询执行时 DBMS 的内部动作图 SELECT 衣服, T 恤衫, 1000 FROM Shohin WHERE 1000 > 2500; SELECT 衣服, 运动 T 恤, 4000 FROM Shohin WHERE 4000 > 2500; SELECT 厨房用具, 菜刀, 3000 FROM Shohin WHERE 3000 > 2795; SELECT 厨房用具, 高压锅, 6800 FROM Shohin WHERE 6800 > 2795; SELECT 厨房用具, 叉子, 500 FROM Shohin WHERE 500 > 2795; SELECT 厨房用具, 擦菜板, 880 FROM Shohin WHERE 880 > 2795; SELECT 办公用品, 圆珠笔, 100 FROM Shohin WHERE 100 > 300; SELECT 办公用品, 打孔器, 500 FROM Shohin WHERE 500 > 300; 如果商品种类发生了变化, 那么用来进行比较的平均单价也会发生变 化 这样就可以将各种商品的销售单价和平均单价进行比较了 关联子查 询的内部执行结果对于初学者来说是比较难以理解的, 但是像上图这样将 其内部执行结果可视化之后, 理解起来就变得非常容易了吧 满足条件 结合条件一定要写在子查询中 下面给大家介绍一下 SQL 初学者在使用关联子查询时经常犯的一个错误, 那就是将关联条件写在子查询之外的外层查询之中 请大家看一下下面这条 SELECT 语句 -- 错误的关联子查询书写方法 SELECT shohin_bunrui, shohin_mei, hanbai_tanka FROM Shohin AS S1 将关联条件移到子 WHERE S1.shohin_bunrui = S2.shohin_bunrui 查询之外 AND hanbai_tanka > (SELECT AVG(hanbai_tanka) FROM Shohin AS S2 GROUP BY shohin_bunrui); KEYWORD 关联名称 作用域 上述 SELECT 语句只是将子查询中的关联条件移到了外层查询之中, 其他并未加任何更改 但是, 该 SELECT 语句会发生错误, 不能正确执行 允许存在这样的书写方法可能并不奇怪, 但是 SQL 的规则禁止这样的书写方法 该书写方法究竟违反了什么规则呢? 那就是关联名称的作用域 虽然这一术语有些晦涩难懂, 但是一解释大家就明白了 关联名称就是像 S1 S2 这样作为表别名的名称, 作用域 (scope) 就是生存作用域 ( 有

42 5-3 关联子查询 161 注 A 当然, 消失的其实只是 S2 这个名称而已,Shohin 表以及其中的数据还是存在的 效作用域 ) 也就是说, 关联名称存在一个有效作用域的限制 具体来讲, 子查询内部设定的关联名称, 只能在该子查询内部使用 ( 图 5-9) 换句话说, 就是 内部可以看到外部, 而外部看不到内部 请大家一定不要忘记关联名称具有一定的有效作用域 如前所述, SQL 是按照先内层子查询后外层查询的顺序来执行的 这样, 子查询执行结束时只会留下执行结果, 作为抽出源的 S2 表其实已经不存在了 A 因此, 在执行外层查询时, 由于 S2 表已经不存在了, 就会返回 不存在使用该名称的表 这样的错误 图 5-9 子查询内的关联名称的有效作用域 SELECT shohin_bunrui, shohin_mei, hanbai_tanka FROM Shohin AS S1 WHERE hanbai_tanka>(select AVG(hanbai_tanka) FROM Shohin AS S2 WHERE S1.shohin_bunrui=S2.shohin_bunrui GROUP BY shohin_bunrui); ( 仅在子查询中有效 ) S2 S1 练习题 5.1 创建出满足下述三个条件的视图 ( 视图名称为 ViewRenshu5_1) 使用 Shohin( 商品 ) 表作为参照表, 假设表中包含初始状态的 8 行数据 条件 1 : 销售单价大于等于 1000 日元 条件 2 : 登记日期是 2009 年 9 月 20 日 条件 3 : 包含商品名称 销售单价和登记日期三列 对该视图执行 SELECT 语句的结果如下所示 SELECT * FROM ViewRenshu5_1; 执行结果 shohin_mei hanbai_tanka torokubi T 恤衫 菜刀

43 162 第 5 章复杂查询 5.2 向习题 5.1 中创建的视图 ViewRenshu5_1 中插入如下数据, 会得到什么 样的结果呢? INSERT INTO ViewRenshu5_1 VALUES (' 刀子 ', 300, ' '); 提示 : 使用 PostgreSQL 时, 由于视图会被初始设定为只读, 所以在执行 INSERT 语 句之前一定要将视图设置为可以更新 5.3 请根据如下结果编写 SELECT 语句 其中 hanbai_tanka_all 列为全部 商品的平均销售单价 执行结果 shohin_id shohin_mei shohin_bunrui hanbai_tanka hanbai_tanka_all T 恤衫 衣服 打孔器 办公用品 运动 T 恤 衣服 菜刀 厨房用具 高压锅 厨房用具 叉子 厨房用具 擦菜板 厨房用具 圆珠笔 办公用品 请根据习题 5.1 中的条件编写一条 SQL 语句, 创建一幅包含如下数据的视图 ( 名称为 AvgTankaByBunrui) 执行结果 shohin_id shohin_mei shohin_bunrui hanbai_tanka avg_hanbai_tanka T 恤衫 衣服 打孔器 办公用品 运动 T 恤 衣服 菜刀 厨房用具 高压锅 厨房用具 叉子 厨房用具 擦菜板 厨房用具 圆珠笔 办公用品 提示 : 其中的关键是 avg_hanbai_tanka 列 与习题 5.3 不同, 这里需要计算出的是各商品种类的平均销售单价 这与 5-3 节中使用关联子查询所得到的结果相同 也就是说, 该列可以使用关联子查询进行创建 问题就是应该在什么地方使用这个关联子查询