SQL学习指南（第3版） (Alan Beaulieu) (Z-Library)

Learning SQL-3e-ptp出片.indd 1 2022/1/29 下午10:20

版权声明 Copyright © 2020 Alan Beaulieu. All rights reserved. Simplified Chinese Edition, jointly published by O’Reilly Media, Inc. and Posts & Telecom Press, 2022. Authorized translation of the English edition, 2020 O’Reilly Media, Inc., the owner of all rights to publish and sell the same. All rights reserved including the rights of reproduction in whole or in part in any form. 本书中文简体字版由 O’Reilly Media, Inc.授权人民邮电出版社有限公司出版。未经出版者书面许可， 对本书的任何部分不得以任何方式复制或抄袭。 版权所有，侵权必究。 ♦ 著 ［美］艾伦·博利厄（Alan Beaulieu） 译 杨 云 责任编辑 傅道坤 责任印制 王 郁 胡 南 ♦ 人民邮电出版社出版发行 北京市丰台区成寿寺路 11 号 邮编 100164 电子邮件 315@ptpress.com.cn 网址 https://www.ptpress.com.cn 三河市中晟雅豪印务有限公司印刷 ♦ 开本：787×1000 1/16 印张：20.75 2022 年 4 月第 3 版 字数：424 千字 2022 年 4 月河北第 1 次印刷 著作权合同登记号 图字：01-2020-7182 号 定价：89.90 元 读者服务热线：(010)81055410 印装质量热线：(010)81055316 反盗版热线：(010)81055315 广告经营许可证：京东市监广登字 20170147 号

事务 iii 章 内容提要 本书介绍了 SQL 语言的基础知识以及高级特性，包括 SQL 基本查询、过滤、多 数据表查询、集合、数据操作、分组和聚合、子查询、连接、条件逻辑、事务、 索引和约束、视图等内容。同时，为了适应近年来数据库领域的发展变化，本 书针对大数据、SQL 跨平台数据库服务和数据分析等领域的需求，增加了处理 大型数据库的实现策略和扩展技术，以及报表和分析工具等内容。 本书内容循序渐进，每章的主题相对独立，并提供了丰富、可扩展的示例，同 时还配备精选练习，有利于读者有效学习和快速掌握 SQL 语言。本书适合作为 数据库应用开发者和数据库管理员的必备入门书，也可供 SQL 相关从业者查阅 和参考。

iv 第 12 章

v 章 资源与支持 本书由异步社区出品，社区（https://www.epubit.com/）为您提供相关资源和后续服务。 提交勘误 作者和编辑尽最大努力来确保书中内容的准确性，但难免会存在疏漏。欢迎您将发现 的问题反馈给我们，帮助我们提升图书的质量。  当您发现错误时，请登录异步社区，按书名搜索，进入本书页面，单击“提交勘误”，输 入勘误信息，单击“提交”按钮即可。本书的作者和编辑会对您提交的勘误进行审核，确 认并接受后，您将获赠异步社区的 100 积分。积分可用于在异步社区兑换优惠券、样书或 奖品。 扫码关注本书 扫描下方二维码，您将会在异步社区微信服务号中看到本书信息及相关的服务提示。

vi 资源与支持 与我们联系 我们的联系邮箱是 contact@epubit.com.cn。  如果您对本书有任何疑问或建议，请您发邮件给我们，并请在邮件标题中注明本书书 名，以便我们更高效地做出反馈。  如果您有兴趣出版图书、录制教学视频，或者参与图书审校等工作，可以发邮件给本 书的责任编辑（fudaokun@ptpress.com.cn）。  如果您来自学校、培训机构或企业，想批量购买本书或异步社区出版的其他图书，也 可以发邮件给我们。  如果您在网上发现有针对异步社区出品图书的各种形式的盗版行为，包括对图书全部 或部分内容的非授权传播，请您将怀疑有侵权行为的链接发邮件给我们。您的这一举 动是对作者权益的保护，也是我们持续为您提供有价值的内容的动力之源。 关于异步社区和异步图书 “异步社区”是人民邮电出版社旗下 IT 专业图书社区，致力于出版精品 IT 图书和相关 学习产品，为作译者提供优质出版服务。异步社区创办于 2015 年 8 月，提供大量精品 IT 技术图书和电子书，以及高品质技术文章和视频课程。更多详情请访问异步社区官 网 https://www.epubit.com。  “异步图书”是由异步社区编辑团队策划出版的精品 IT 专业图书的品牌，依托于人民 邮电出版社近 40 年的计算机图书出版积累和专业编辑团队，相关图书在封面上印有异 步图书的 LOGO。异步图书的出版领域包括软件开发、大数据、AI、测试、前端、网 络技术等。 异步社区 微信服务号

目录 vii 章 目录 前言................................................................................................................................xv 第 1 章 背景知识 ......................................................................................................1 1.1 数据库简介 ......................................................................................................1 1.1.1 非关系型数据库系统 ..........................................................................2 1.1.2 关系模型 ..............................................................................................4 1.1.3 术语 ......................................................................................................6 1.2 什么是 SQL......................................................................................................6 1.2.1 SQL 语句分类 ......................................................................................7 1.2.2 SQL：一种非过程化语言 ...................................................................8 1.2.3 SQL 示例 ..............................................................................................9 1.3 什么是 MySQL .............................................................................................. 11 1.4 跨平台 SQL....................................................................................................12 1.5 内容前瞻 ........................................................................................................12 第 2 章 创建和填充数据库 ..................................................................................13 2.1 创建 MySQL 数据库 .....................................................................................13 2.2 使用命令行工具 mysql..................................................................................14 2.3 MySQL 数据类型 ..........................................................................................16 2.3.1 字符型数据 ........................................................................................16 2.3.2 数值型数据 ........................................................................................19 2.3.3 时间型数据 ........................................................................................20

viii 目录 2.4 创建数据表.....................................................................................................22 2.4.1 第 1 步：设计.....................................................................................22 2.4.2 第 2 步：改进.....................................................................................23 2.4.3 第 3 步：构建 SQL 模式语句 ...........................................................24 2.5 填充和修改数据表.........................................................................................27 2.5.1 插入数据.............................................................................................27 2.5.2 更新数据.............................................................................................32 2.5.3 删除数据.............................................................................................32 2.6 常见错误及响应.............................................................................................32 2.6.1 非唯一的主键.....................................................................................33 2.6.2 不存在的外键.....................................................................................33 2.6.3 列值违规.............................................................................................33 2.6.4 无效的日期转换.................................................................................34 2.7 Sakila 数据库..................................................................................................35 第 3 章 查询入门 ....................................................................................................38 3.1 查询机制.........................................................................................................38 3.2 查询子句.........................................................................................................40 3.3 select 子句 ......................................................................................................40 3.3.1 列的别名.............................................................................................42 3.3.2 移除重复数据.....................................................................................43 3.4 from 子句........................................................................................................45 3.4.1 数据表.................................................................................................45 3.4.2 数据表链接.........................................................................................48 3.4.3 定义数据表别名.................................................................................49 3.5 where 子句......................................................................................................49 3.6 group by 和 having 子句 ................................................................................52 3.7 order by 子句 ..................................................................................................53 3.7.1 升序排序和降序排序.........................................................................55 3.7.2 通过数字占位符进行排序 .................................................................56 3.8 练习 ................................................................................................................57 第 4 章 过滤 .............................................................................................................58 4.1 条件评估.........................................................................................................58

目录 ix 4.1.1 使用括号 ............................................................................................59 4.1.2 使用 not 运算符 .................................................................................60 4.2 构建条件 ........................................................................................................60 4.3 条件类型 ........................................................................................................61 4.3.1 相等条件 ............................................................................................61 4.3.2 范围条件 ............................................................................................63 4.3.3 成员条件 ............................................................................................67 4.3.4 匹配条件 ............................................................................................69 4.4 null：4 个字母的单词 ...................................................................................72 4.5 练习 ................................................................................................................75 第 5 章 多数据表查询 ...........................................................................................77 5.1 什么是连接 ....................................................................................................77 5.1.1 笛卡儿积 ............................................................................................78 5.1.2 内连接 ................................................................................................79 5.1.3 ANSI 连接语法 ..................................................................................80 5.2 连接 3 个或以上的数据表 ............................................................................82 5.2.1 使用子查询作为数据表.....................................................................85 5.2.2 使用同一数据表两次 ........................................................................86 5.3 自连接 ............................................................................................................87 5.4 练习 ................................................................................................................88 第 6 章 使用集合 ....................................................................................................90 6.1 集合论入门 ....................................................................................................90 6.2 集合论实践 ....................................................................................................92 6.3 集合运算符 ....................................................................................................94 6.3.1 union 运算符 ......................................................................................94 6.3.2 intersect 运算符 ..................................................................................96 6.3.3 except 运算符 .....................................................................................97 6.4 集合运算规则 ................................................................................................99 6.4.1 对符合查询结果排序 ........................................................................99 6.4.2 集合运算的优先级 ..........................................................................100 6.5 练习 ..............................................................................................................102

x 目录 第 7 章 数据生成、操作和转换 .......................................................................103 7.1 处理字符串数据...........................................................................................103 7.1.1 生成字符串.......................................................................................104 7.1.2 操作字符串.......................................................................................108 7.2 处理数值型数据...........................................................................................115 7.2.1 执行算术函数...................................................................................116 7.2.2 控制数值精度...................................................................................117 7.2.3 使用有符号数...................................................................................119 7.3 处理时间型数据...........................................................................................120 7.3.1 处理时区...........................................................................................120 7.3.2 生成时间型数据...............................................................................122 7.3.3 操作时间型数据...............................................................................125 7.4 转换函数.......................................................................................................129 7.5 练习 ..............................................................................................................130 第 8 章 分组和聚合..............................................................................................131 8.1 分组的概念...................................................................................................131 8.2 聚合函数.......................................................................................................134 8.2.1 隐式分组与显式分组.......................................................................135 8.2.2 统计不同的值...................................................................................136 8.2.3 使用表达式.......................................................................................136 8.2.4 处理 null ...........................................................................................137 8.3 生成分组.......................................................................................................138 8.3.1 单列分组...........................................................................................139 8.3.2 多列分组...........................................................................................139 8.3.3 通过表达式分组...............................................................................140 8.3.4 生成汇总...........................................................................................140 8.4 分组过滤条件...............................................................................................142 8.5 练习 ..............................................................................................................143 第 9 章 子查询 .......................................................................................................144 9.1 什么是子查询...............................................................................................144 9.2 子查询类型...................................................................................................145

目录 xi 9.3 非关联子查询 ..............................................................................................145 9.3.1 多行单列子查询 ..............................................................................147 9.3.2 多列子查询 ......................................................................................151 9.4 关联子查询 ..................................................................................................153 9.4.1 exists 运算符 ....................................................................................154 9.4.2 使用关联子查询操作数据...............................................................156 9.5 何时使用子查询 ..........................................................................................157 9.5.1 子查询作为数据源 ..........................................................................157 9.5.2 子查询作为表达式生成器...............................................................163 9.6 子查询小结 ..................................................................................................165 9.7 练习 ..............................................................................................................166 第 10 章 再谈连接................................................................................................167 10.1 外连接 ........................................................................................................167 10.1.1 左外连接与右外连接.....................................................................170 10.1.2 三路外连接 ....................................................................................171 10.2 交叉连接 ....................................................................................................172 10.3 自然连接 ....................................................................................................178 10.4 练习 ............................................................................................................179 第 11 章 条件逻辑 ................................................................................................181 11.1 什么是条件逻辑 ........................................................................................181 11.2 case 表达式.................................................................................................182 11.2.1 搜索型 case 表达式........................................................................182 11.2.2 简单的 case 表达式........................................................................184 11.3 case 表达式示例.........................................................................................184 11.3.1 结果集变换.....................................................................................184 11.3.2 检查存在性.....................................................................................186 11.3.3 除零错误.........................................................................................188 11.3.4 条件更新.........................................................................................189 11.3.5 处理 null 值 ....................................................................................189 11.4 练习 ............................................................................................................190 第 12 章 事务.........................................................................................................192 12.1 多用户数据库 ............................................................................................192

xii 目录 12.1.1 锁定.................................................................................................192 12.1.2 锁的粒度.........................................................................................193 12.2 什么是事务.................................................................................................194 12.2.1 启动事务.........................................................................................195 12.2.2 结束事务.........................................................................................196 12.2.3 事务保存点.....................................................................................197 12.3 练习 ............................................................................................................199 第 13 章 索引和约束 ...........................................................................................201 13.1 索引 ............................................................................................................201 13.1.1 创建索引.........................................................................................202 13.1.2 索引类型.........................................................................................206 13.1.3 如何使用索引.................................................................................208 13.1.4 索引的不足.....................................................................................209 13.2 约束 ............................................................................................................210 13.3 练习 ............................................................................................................213 第 14 章 视图 .........................................................................................................214 14.1 什么是视图.................................................................................................214 14.2 为什么要使用视图.....................................................................................216 14.2.1 数据安全.........................................................................................217 14.2.2 数据聚合.........................................................................................217 14.2.3 隐藏复杂性.....................................................................................218 14.2.4 连接分区数据.................................................................................219 14.3 可更新视图.................................................................................................219 14.3.1 更新简单视图.................................................................................220 14.3.2 更新复杂视图.................................................................................221 14.4 练习 ............................................................................................................223 第 15 章 元数据 ....................................................................................................225 15.1 关于数据的数据.........................................................................................225 15.2 information_schema....................................................................................226 15.3 使用元数据.................................................................................................231 15.3.1 模式生成脚本.................................................................................231

目录 xiii 15.3.2 验证部署 ........................................................................................234 15.3.3 动态 SQL 生成 ...............................................................................235 15.4 练习 ............................................................................................................239 第 16 章 分析函数................................................................................................240 16.1 分析函数的概念 ........................................................................................240 16.1.1 数据窗口 ........................................................................................240 16.1.2 本地化排序 ....................................................................................242 16.2 排名 ............................................................................................................243 16.2.1 排名函数 ........................................................................................243 16.2.2 生成多个排名 ................................................................................246 16.3 报表函数 ....................................................................................................249 16.3.1 窗口框架 ........................................................................................251 16.3.2 lag 和 lead.......................................................................................254 16.3.3 列值拼接 ........................................................................................255 16.4 练习 ............................................................................................................256 第 17 章 处理大型数据库 ..................................................................................258 17.1 分区 ............................................................................................................258 17.1.1 分区的概念 ....................................................................................259 17.1.2 数据表分区 ....................................................................................259 17.1.3 索引分区 ........................................................................................259 17.1.4 分区方法 ........................................................................................260 17.1.5 分区的优势 ....................................................................................267 17.2 集群 ............................................................................................................268 17.3 分片 ............................................................................................................268 17.4 大数据 ........................................................................................................269 17.4.1 Hadoop............................................................................................270 17.4.2 NoSQL 和文档数据库 ...................................................................270 17.4.3 云计算 ............................................................................................270 17.4.4 小结 ................................................................................................271 第 18 章 SQL 和大数据 .....................................................................................272 18.1 Apache Drill 简介.......................................................................................272

xiv 目录 18.2 使用 Drill 查询文件 ...................................................................................273 18.3 使用 Drill 查询 MySQL.............................................................................275 18.4 使用 Drill 查询 MongoDB .........................................................................278 18.5 使用 Drill 处理多个数据源 .......................................................................284 18.6 SQL 的未来 ................................................................................................286 附录 A 示例数据库的 ER 图.............................................................................287 附录 B 练习答案 ...................................................................................................289

xv 章 前言 编程语言一直在不断地发展变化，如今所使用的编程语言很少有十年以上的历史，其 中为数不多的例子包括：COBOL，目前仍大量应用于大型机环境中；Java，诞生于 20 世纪 90 年代中期，已成为最流行的编程语言之一；C，在操作系统和服务器开发以及 嵌入式系统中依然颇受欢迎。在数据库领域，SQL 的根源可以追溯到 20 世纪 70 年代。 SQL 最初是一种为生成、操作和检索关系型数据库的数据而创建的语言，而关系型数 据库已经有 40 多年的历史。但在过去的十年间，Hadoop、Spark 和 NoSQL 等数据平台 已经获得了众多的关注，吞食了关系型数据库的市场。然而，正如本书最后几章所讨 论的那样，SQL 语言也在不断演变，以便从各种平台检索数据，无论这些数据是存储 在数据表、文档还是平面文件中。 为什么要学习 SQL 无论是否会用到关系型数据库，只要你从事数据科学、商业智能或其他数据分析领域 的工作，都可能需要了解 SQL 以及 Python 和 R 等语言/平台。数据无处不在，铺天盖 地，通常以迅猛之势出现在你的面前，能够从这些数据中提取出有意义信息的人才在 市场上供不应求。 为什么选择本书 有很多书会把读者当作新手或者门外汉，这类书的内容往往流于表面。另一种书则属 于参考指南，会事无巨细地描述语言中每个语句的各种写法，如果你非常清楚自己要 干什么，但是需要了解明确的语法，那么这类指南可以助你一臂之力。本书则另辟蹊 径，努力寻找这两者的平衡点，从 SQL 语言的背景开始，逐步学习基础知识，然后进 阶探索一些能让读者一鸣惊人的高级特性。此外，本书最后一章展示了如何在非关系 型数据库中查询数据，这是入门书中很少涉及的话题。

xvi 前言 本书的组织结构 本书共分为 18 章和 2 个附录，具体内容如下。 第 1 章：背景知识，探讨计算机化数据库的发展历史，包括关系模型和 SQL 语言的兴起。 第 2 章：创建和填充数据库，演示如何创建 MySQL 数据库、生成本书中示例所需的数 据表并向其中填充数据。 第 3 章：查询入门，介绍 select 语句并进一步演示最常见的子句（select、from、where 等）。 第 4 章：过滤，演示可以在 select、update 或 delete 语句的 where 子句中使用的各种条件。 第 5 章：多数据表查询，介绍查询如何通过数据表连接使用多个数据表。 第 6 章：使用集合，专门介绍集合以及如何在查询中运用集合。 第 7 章：数据生成、操作和转换，演示一些用于操作或转换数据的内建函数。 第 8 章：分组和聚合，介绍如何聚合数据。 第 9 章：子查询，介绍子查询（作者个人的偏爱）并展示了其用法以及应用场景。 第 10 章：再谈连接，进一步探讨各种类型数据表的连接。 第 11 章：条件逻辑，探讨如何在 select、insert、update 和 delete 语句中使用条件逻辑 （即 if-then-else）。 第 12 章：事务，介绍事务并展示其用法。 第 13 章：索引和约束，探讨索引和约束相关的内容。 第 14 章：视图，介绍如何构建可以使用户远离数据复杂性的界面。 第 15 章：元数据，演示数据字典的效用。 第 16 章：分析函数，涵盖各种分析函数，用于生成排名、小计以及其他在报表生成和 分析中经常使用的值。 第 17 章：处理大型数据库，演示各种用于简化大型数据库管理和遍历的技术。 第 18 章：SQL 和大数据，探讨 SQL 语言的变化，以便从非关系型数据平台中检索数据。 附录 A：示例数据库的 ER 图，介绍用于本书中所有示例的数据库模式。 附录 B：练习答案，提供每章练习的答案。 致谢 感谢我的编辑 Jeff Bleiel，正是在你的帮助下，本书才顺利付梓，同时感谢 Thomas Nield、 Ann White-Watkins 和 Charles Givre，感谢你们帮我审阅书稿。还要感谢 Deb Baker、Jess Haberman 以及参与本书出版事务的 O’Reilly Media 公司的其他同仁。最后，感谢我的 妻子 Nancy、女儿 Michelle 和 Nicole，感谢你们的激励与鼓舞。

背景知识 1 第 1 章 背景知识 在着手干活之前，为了更好地理解关系型数据库和 SQL 语言的演进历程，还是有必要 研究一下数据库技术的历史。因此，我将先介绍一些基本的数据库概念，回顾计算机 化数据存储和检索的历史。 如果有读者急着想开始编写 SQL 查询，可以直接跳到第 3 章，不过 我建议随后再回头阅读前两章，以便更好地了解 SQL 语言的历史和 功用性。 1.1 数据库简介 数据库就是一组相关信息。例如，电话簿就是一个数据库，其中包含了生活在某一地 区内所有人的姓名、电话号码和地址。尽管电话簿肯定是最为普及且常用的数据库， 但它也存在以下问题。 • 查找电话号码的时候费时间，尤其是电话簿里包含大量联系人的时候。 • 电话簿仅使用姓/名作为索引，所以要想查找居住在特定地址的联系人时就不实用 了，尽管理论上是可行的。 • 从电话簿被印刷好的那一刻起，随着地区居民的流动、个人电话号码的更改、住址 的更换，电话簿中的信息会变得越来越不准确。 电话簿的种种缺点同样体现在所有的手工数据存储系统上，比如存储在文件柜中的患 者记录。由于纸质数据库使用不便，一些早先开发的计算机应用程序就是数据库系统， 采用了计算机化的数据存储和检索机制。由于数据库系统使用电子化代替纸张存储数 据，因此能够更快地检索数据、以多种方式编制索引，并为用户群提供即时信息。

2 第 1 章 早期的数据库系统用磁带存储被管理的数据。磁带的数量通常远多于磁带机，因此在 请求特定数据时，需要由技术人员手动装卸磁带。由于那个时代的计算机内存很小， 对同一数据的多次请求往往需要多次读取磁带。尽管这些数据库系统比起纸质数据库 有了很大的改进，但与如今的数据库技术相差甚远（现代数据库系统能够管理 PB 级的 数据，利用服务器集群进行访问，每个服务器在高速内存中缓存的数据可达数十 GB， 不过我讲的可能有点超前了）。 1.1.1 非关系型数据库系统 本节介绍了关系型数据库出现之前的一些背景信息，如果读者急于学 习 SQL，可以直接跳到下一节。 在计算机化数据库系统发展的最初几十年里，数据以各种形式存储并展现给用户。例 如，在层次数据库系统中，以一个或多个树形结构来表示数据。图 1-1 展示了以树形结 构表示的 George Blake 和 Sue Smith 的银行账户数据。 图 1-1 账户数据的层次化视图 George 和 Sue 的数据树都包含了各自的账户以及交易信息。层次数据库系统提供了定 位特定客户信息树的工具，并能够遍历该树找到所需的账户和/或交易。树中的每个 节点都具有 0 个或 1 个父节点，以及 0 个、1 个或多个子节点。这种配置称为单根层 次结构。 另一种数据管理方式是网状数据库系统，它表现为多个记录以及定义不同记录之间关 系的多个链接。图 1-2 展示了 George 和 Sue 的账户在此系统中的视图。

背景知识 3 图 1-2 账户数据的网状视图 为了查找 Sue 的 MoneyMkt 账户交易信息，需要执行以下步骤： 1．查找 Sue Smith 的客户记录； 2．沿着 Sue Smith 的客户记录链接找到其账户列表； 3．遍历账户列表直至找到 MoneyMkt 账户； 4．沿着 MoneyMkt 账户记录链接找到其交易列表。 网状数据库系统的一个值得注意的特性是图 1-2 最右侧的一组 product 记录。注意，每 个 product 记录（Checking、Savings 等）都指向一个 account 记录列表，以指定这些账 户记录的产品类型。因此 account 记录可以通过多个入口（customer 记录或 product 记 录）进行访问，这使得网状数据库具有多根层次的特点。 层次数据库和网状数据库依然活跃在今天，不过通常只能在大型机世界中找到。另外， 层次数据库已在目录服务领域中重获新生，比如 Microsoft 的 Active Directory 和开源的 Apache Directory Server。然而，在 20 世纪 70 年代初，一种表示数据的全新方式开始 生根，这种方式更为严谨，且易于理解和实现。