SQL进阶教程(txt+pdf+epub+mobi电子书下载)

作者：MICK

出版社：人民邮电出版社

格式: AZW3, DOCX, EPUB, MOBI, PDF, TXT

SQL进阶教程试读：

前言

几年过去，我由于个人原因回国了，工作中也不再使用日语，便想着借着业余时间翻译一些优秀的日语技术书。当图灵公司的老师问我是否有意向翻译这本书时，我立刻就答应了。当初回国时为了缩减行李，我只保留了几本日语原版的技术书，这本就是其中之一。这样一本多年前结缘、至今仍躺在我书架上的好书，当有机会将它翻译成中文版时，我实在没有什么理由放弃掉。

这本书，我认为是作者的用心之作。书中大部分内容都来自作者记录自己实践总结和日常思考的个人博客，最大的特点是理论与实践相结合，除了讲述应该怎么做，还解释了其背后的原理。全书包含两部分内容，第一部分介绍了 SQL 在使用方面的一些技巧，第二部分介绍了关系数据库相关的内容。第一部分在介绍 SQL 的技巧时，作者并没有上来就展示各种酷炫的招式，而是先以简单的问题或者例题引出将要讨论的内容，在讲解之后进一步扩展，由点及面地引出更深的话题或者背后的原理。这种由浅入深的讲述方式，符合一般的学习习惯，读者能在轻松愉悦的阅读过程中，跟着作者一起思考，自然而然地掌握相应的思考方式。第二部分在介绍关系数据库时，作者先介绍了关系数据库诞生的历史背景及其解决的问题。关系数据库已经诞生了几十年，为了让现在的读者理解当初的问题和背景，作者大量引用了关系数据库之父 E.F. Codd 和关系数据库领域权威专家 C.J. Date 的文献和言论，并按自己的理解给出了分析与解释，力图使读者体会到伟大人物们在革新技术之际的心路历程。除此之外，第二部分中作者还从逻辑学和集合论的角度讲述了 SQL 和关系模型的理论基础。该部分内容作者充分发挥了自己在相关领域的深厚积累，以深入浅出的方式进行了阐述，我认为非常精彩。

书中引用了许多经典的图书和文献，都在脚注和书末参考文献中给出了详细的出处，方便有需要的读者进一步研读。更加可贵的是，在大多数小节的末尾作者都提出了两三个精心设计的小问题，这些问题是正文内容的扩展和延伸，非常利于读者巩固相应的知识点。而且，针对这些问题，作者也给出了详细的解答，并指出了读者容易犯的错误。

本书推荐数据库工程师、经常需要和数据库打交道的软件工程师，以及所有希望提升 SQL 水平的读者阅读。在翻译过程中，我尽力表达出原著的意图，但是由于水平有限，难免存在问题，欢迎读者批评指正。读者在阅读中有任何问题，都可以通过电子邮件和我取得联系（ensho_go@hotmail.com）。2017 年 9 月于北京前言

编写本书的目的在于架起两座桥梁：一是让数据库工程师从初级向中级进阶的桥梁，旨在帮助初级工程师提升自己；二是理论（原理）和实践之间的桥梁。这里所说的“初级”，具体是指已经掌握了 SQL 的基础知识和技能，具有半年到一年左右的使用经验这种水平。

我们来做一个测试，帮助大家了解一下自己处于何种水平。下面有 10 个问题，请回答 Yes 或 No。01. 没有在聚合函数中使用过 CASE 表达式。02. 想象不出自连接是如何工作的。03. 感觉 HAVING 子句不是很常用。04. 感觉 IN 比 EXISTS 好用，所以更喜欢用 IN。05. 听到布尔类型，脑海里浮现出的只有 true 和 false。06. 设计表的时候不加 NOT NULL 的约束。07. SQL 全部用大写字母或全部用小写字母来写。08. 不能用一句话说出 GROUP BY 和 PARTITION BY 的区别。09. 不知道 SQL 里的高阶函数的名字。110. 试着读过 Joe Celko 的《SQL 权威指南》和《SQL 解惑（第 2 2

版）》，但是感觉太难而没能读完（或者压根儿没有读过）。原书名为 Joe Celko's SQL for Smarties: Advanced SQL 1Programming，本书共有五版。国内引进了第 4 版，书名为《SQL 权威指南（第 4 版）》，朱巍等译，人民邮电出版社，2013 年。——编者注

米全喜译，人民邮电出版社，2008 年 4 月。——编者注2

大家的回答如何呢？如果全部都回答了 No，那很好，不要担心什么，请合上本书，立刻踏上成为一名高级工程师的道路吧（也许只有本书 3-2 节“参考文献”值得略读一下）。相反，如果一半以上都回答了 Yes，那么本书将照亮大家的前进之路——这正是编写本书的目的，相信大家读后一定会有收获。

但是，接下来要说的内容可能会让大家觉得有点前后矛盾。因为，这本书即将介绍的技术绝不是多么新潮的东西，而是遵循标准 SQL 的非常普通的技术。关于这一点，相信扫一眼目录你就会明白。CASE 表达式、自连接、HAVING 子句、外连接、关联子查询、EXISTS……这些都是数据库工程师日常工作中经常用到的技术。

编写本书的目的就是从新的角度把光照向这些“并没有什么特别的、谁都知道的技术”，照亮它们迄今都没有被看到的一面。相信大家读完本书时，会从那个一直以来都被认为平淡无奇的关系数据库的世界里，看到一些不一样的光辉。

下面，就让我们立刻前往博大精深的关系数据库的世界，开始探险之旅吧。

声明

※本书中的URL等信息可能会有变化。

※本书出版之际，我们力求准确阐述，但是翔泳社、原书作者、人民邮电出版社和译者均不对内容作任何保证，对于由本书内容和示例代码造成的一切后果，不承担任何责任。

※本书中的示例代码和脚本，以及执行结果页面都是基于特定环境的参考示例。

※本书中的公司名、商品名分别是相关公司的商标或注册商标。

阅读本书时的注意事项● 本书中出现的SQL语句都是尽可能按照标准

SQL（SQL-92/99/2003）来写的，对于依赖具体数据库实现的地

方，本书会有明确的说明。● 按照标准SQL的要求，指定表的别名的关键字AS也应该写上，

但本书省略了。这是为了避免SQL程序在Oracle数据库中出错（其他数据库里也一样，省略了就不会出错）。● RANK、ROW_NUMBER这样的窗口函数（OLAP函数）目前还

不能在MySQL数据库中运行。● 正文里的代码在以下数据库中测试运行过。● Oracle 10g● SQL Server 2005● DB2 9.1● PostgreSQL 9.6● MySQL 5.0● 正文里提到Oracle、MySQL等数据库而未指定版本时，请参照上

述版本。● 关于用于创建示例用表的SQL语句和示例代码等，请参考如

下网站。http://www.ituring.com.cn/book/1813（请点击“随书下载”

下载中文版相关资料）http://www.geocities.jp/mickindex/database/

db_support_sinan.html（作者MICK的日文网站）第 1 章神奇的SQL

1-1 … CASE表达式

1-2 … 自连接的用法

1-3 … 三值逻辑和NULL

1-4 … HAVING子句的力量

1-5 … 外连接的用法

1-6 … 用关联子查询比较行与行

1-7 …用SQL进行集合运算

1-8 … EXISTS谓词的用法

1-9 … 用SQL处理数列

1-10 … HAVING子句又回来了

1-11 …让SQL飞起来

1-12 … SQL编程方法1-1　CASE 表达式▶在 SQL 里表达条件分支CASE 表达式是 SQL 里非常重要而且使用起来非常便利的技术，我们应该学会用它来描述条件分支。本节将通过行列转换、已有数据重分组（分类）、与约束的结合使用、针对聚合结果的条件分支等例题，来介绍 CASE 表达式的用法。写在前面

CASE 表达式是从 SQL-92 标准开始被引入的。可能因为它是相对较新的技术，所以尽管使用起来非常便利，但其真正的价值却并不怎么为人所知。很多人不用它，或者用它的简略版函数，例如 DECODE（Oracle）、IF（MySQL）等。然而，正如 Joe Celko 所说，CASE 表达式也许是 SQL-92 标准里加入的最有用的特性。如果能用好它，那么 SQL 能解决的问题就会更广泛，写法也会更加漂亮。而且，因为 CASE 表达式是不依赖于具体数据库的技术，所以可以提高 SQL 代码的可移植性。这里强烈推荐大家改用 CASE 表达式，特1别是使用 DECODE 函数的 Oracle 用户 。

DECODE 是 Oracle 用户很熟悉的函数，它有以下四个不如 1CASE 表达式的地方。• 它是 Oracle 独有的函数，所以不具有可移植性。• 分支数最大支持 127 个（参数上限 255 个，一个分支需要 2 个参数）。• 如果分支数增加，代码会变得非常难读。• 表达能力较弱。具体来说，参数里不能使用谓词，也不能嵌套子查询。

本节，我们将通过具体的例题来学习优点众多的 CASE 表达式。CASE 表达式概述

首先我们来学习一下基本的写法，CASE 表达式有简单 CASE 表达式（simple case expression）和搜索 CASE 表达式（searched case expression）两种写法，它们分别如下所示。

■ CASE 表达式的写法-- 简单CASE 表达式CASE sex WHEN '1' THEN '男' WHEN '2' THEN '女'ELSE '其他' END-- 搜索CASE 表达式CASE WHEN sex = '1' THEN '男' WHEN sex = '2' THEN '女'ELSE '其他' END

这两种写法的执行结果是相同的，“sex”列（字段）如果是 '1'，那么结果为男；如果是 '2'，那么结果为女。简单 CASE 表达式正如其名，写法简单，但能实现的事情比较有限。简单 CASE 表达式能写的条件，搜索 CASE 表达式也能写，所以本书基本上采用搜索 CASE 表达式的写法。

我们在编写 SQL 语句的时候需要注意，在发现为真的 WHEN 子句时，CASE 表达式的真假值判断就会中止，而剩余的 WHEN 子句会被忽略。为了避免引起不必要的混乱，使用 WHEN 子句时要注意条件的排他性。

■剩余的　WHEN 子句被忽略的写法示例-- 例如，这样写的话，结果里不会出现“第二”CASE WHEN col_1 IN ('a', 'b') THEN '第一' WHEN col_1 IN ('a') THEN '第二'ELSE '其他' END

此外，使用 CASE 表达式的时候，还需要注意以下几点。

注意事项 1：统一各分支返回的数据类型

虽然这一点无需多言，但这里还是要强调一下：一定要注意 CASE 表达式里各个分支返回的数据类型是否一致。某个分支返回字符型，而其他分支返回数值型的写法是不正确的。

注意事项 2：不要忘了写 END

使用 CASE 表达式的时候，最容易出现的语法错误是忘记写 END。虽然忘记写时程序会返回比较容易理解的错误消息，不算多么致命的错误。但是，感觉自己写得没问题，而执行时却出错的情况大多是由这个原因引起的，所以请一定注意一下。

注意事项 3：养成写 ELSE 子句的习惯

与 END 不同，ELSE 子句是可选的，不写也不会出错。不写 ELSE 子句时，CASE 表达式的执行结果是 NULL。但是不写可能会造成“语法没有错误，结果却不对”这种不易追查原因的麻烦，所以最好明确地写上 ELSE 子句（即便是在结果可以为 NULL 的情况下）。养成这样的习惯后，我们从代码上就可以清楚地看到这种条件下会生成 NULL，而且将来代码有修改时也能减少失误。将已有编号方式转换为新的方式并统计

在进行非定制化统计时，我们经常会遇到将已有编号方式转换为另外一种便于分析的方式并进行统计的需求。

例如，现在有一张按照“‘1：北海道’、‘2：青森’、……、‘47：2冲绳’”这种编号方式来统计都道府县 人口的表，我们需要以东北、关东、九州等地区为单位来分组，并统计人口数量。具体来说，就是统计下表 PopTbl 中的内容，得出如右表“统计结果”所示的结果。日本的省级行政单位有都、道、府、县，包含一都（东京都）、2二府（京都府和大阪府）、一道（北海道）和诸多的县，统称都道府县。多个较近的县被划归到一个地区，如关东地区、九州地区等，类似我国的华北地区、华南地区等概念。——译者注3在“统计结果”这张表中，“四国”对应的是表 PopTbl 中的“德岛、香川、爱媛、高知 ”，“九 州”对应的是表 PopTbl 中的“福冈、佐贺、长崎”。——编者注

大家会怎么实现呢？定义一个包含“地区编号”列的视图是一种做法，但是这样一来，需要添加的列的数量将等同于统计对象的编号个数，而且很难动态地修改。

而如果使用 CASE 表达式，则用如下所示的一条 SQL 语句就可以完成。为了便于理解，这里用县名（pref_name）代替编号作为 GROUP BY 的列。-- 把县编号转换成地区编号(1)SELECT CASE pref_name WHEN '德岛' THEN '四国' WHEN '香川' THEN '四国' WHEN '爱媛' THEN '四国' WHEN '高知' THEN '四国' WHEN '福冈' THEN '九州' WHEN '佐贺' THEN '九州' WHEN '长崎' THEN '九州' ELSE '其他' END AS district, SUM(population) FROM PopTbl GROUP BY CASE pref_name WHEN '德岛' THEN '四国' WHEN '香川' THEN '四国' WHEN '爱媛' THEN '四国' WHEN '高知' THEN '四国' WHEN '福冈' THEN '九州' WHEN '佐贺' THEN '九州' WHEN '长崎' THEN '九州' ELSE '其他' END;

这里的关键在于将 SELECT 子句里的 CASE 表达式复制到 GROUP BY 子句里。需要注意的是，如果对转换前的列“pref_name”进行 GROUP BY，就得不到正确的结果（因为这并不会引起语法错误，所以容易被忽视）。

同样地，也可以将数值按照适当的级别进行分类统计。例如，要按人口数量等级（pop_class）查询都道府县个数的时候，就可以像下面这样写 SQL 语句。-- 按人口数量等级划分都道府县SELECT CASE WHEN population < 100 THEN '01' WHEN population >= 100 AND population < 200 THEN '02' WHEN population >= 200 AND population < 300 THEN '03' WHEN population >= 300 THEN '04' ELSE NULL END AS pop_class, COUNT(*) AS cnt FROM PopTbl GROUP BY CASE WHEN population < 100 THEN '01' WHEN population >= 100 AND population < 200 THEN '02' WHEN population >= 200 AND population < 300 THEN '03' WHEN population >= 300 THEN '04' ELSE NULL END;pop_class cnt--------- ----01 102 303 304 2

这个技巧非常好用。不过，必须在 SELECT 子句和 GROUP BY 子句这两处写一样的 CASE 表达式，这有点儿麻烦。后期需要修改的时候，很容易发生只改了这一处而忘掉改另一处的失误。

所以，如果我们可以像下面这样写，那就方便多了。-- 把县编号转换成地区编号(2) ：将CASE 表达式归纳到一处SELECT CASE pref_name WHEN '德岛' THEN '四国' WHEN '香川' THEN '四国' WHEN '爱媛' THEN '四国' WHEN '高知' THEN '四国' WHEN '福冈' THEN '九州' WHEN '佐贺' THEN '九州' WHEN '长崎' THEN '九州' ELSE '其他' END AS district, SUM(population)FROM PopTblGROUP BY district; ←-------GROUP BY 子句里引用了SELECT 子句中定义的别名

没错，这里的 GROUP BY 子句使用的正是 SELECT 子句里定义的列的别称——district。但是严格来说，这种写法是违反标准 SQL 的规则的。因为 GROUP BY 子句比 SELECT 语句先执行，所以在 GROUP BY 子句中引用在 SELECT 子句里定义的别称是不被允许的。事实上，在 Oracle、DB2、SQL Server 等数据库里采用这种写法时就会出错。

不过也有支持这种 SQL 语句的数据库，例如在 PostgreSQL 和 MySQL 中，这个查询语句就可以顺利执行。这是因为，这些数据库在执行查询语句时，会先对 SELECT 子句里的列表进行扫描，并对列进行计算。不过因为这是违反标准的写法，所以这里不强烈推荐大家使用。但是，这样写出来的 SQL 语句确实非常简洁，而且可读性也很好。用一条 SQL 语句进行不同条件的统计

进行不同条件的统计是 CASE 表达式的著名用法之一。例如，我们需要往存储各县人口数量的表 PopTbl 里添加上“性别”列，然后求按性别、县名汇总的人数。具体来说，就是统计表 PopTbl2 中的数据，然后求出如表“统计结果”所示的结果。

通常的做法是像下面这样，通过在 WHERE 子句里分别写上不同的条件，然后执行两条 SQL 语句来查询。

■示例代码 3-- 男性人口SELECT pref_name, SUM(population) FROM PopTbl2 WHERE sex = '1' GROUP BY pref_name;-- 女性人口SELECT pref_name, SUM(population) FROM PopTbl2 WHERE sex = '2' GROUP BY pref_name;

最后需要通过宿主语言或者应用程序将查询结果按列展开。如果使用 UNION，只用一条 SQL 语句就可以实现，但使用这种做法时，工作量并没有减少，SQL 语句也会变得很长。而如果使用 CASE 表达式，下面这一条简单的 SQL 语句就可以搞定。SELECT pref_name, -- 男性人口 SUM( CASE WHEN sex = '1' THEN population ELSE 0 END) AS cnt_m, -- 女性人口 SUM( CASE WHEN sex = '2' THEN population ELSE 0 END) AS cnt_f FROM PopTbl2 GROUP BY pref_name;

上面这段代码所做的是，分别统计每个县的“男性”（即 '1'）人数和“女性”（即 '2'）人数。也就是说，这里是将“行结构”的数据转换成了“列结构”的数据。除了 SUM，COUNT、AVG 等聚合函数也都可以用于将行结构的数据转换成列结构的数据。

这个技巧可贵的地方在于，它能将 SQL 的查询结果转换为二维表的格式。如果只是简单地用 GROUP BY 进行聚合，那么查询后必须通过宿主语言或者 Excel 等应用程序将结果的格式转换一下，才能使之成为交叉表。看上面的执行结果会发现，此时输出的已经是侧栏为县名、表头为性别的交叉表了。在制作统计表时，这个功能非常方便。如果用一句话来形容这个技巧，可以这样说：

新手用 WHERE 子句进行条件分支，高手用 SELECT 子句进行条件分支。

如此好的技巧，请大家多使用。用 CHECK 约束定义多个列的条件关系

其实，CASE 表达式和 CHECK 约束是很般配的一对组合。也许有很多数据库工程师不怎么用 CHECK 约束，但是一旦他们了解了 CHECK 约束和 CASE 表达式结合使用之后的强大威力，就一定会跃跃欲试的。

假设某公司规定“女性员工的工资必须在 20 万日元以下”，而在这个公司的人事表中，这条无理的规定是使用 CHECK 约束来描述的，代码如下所示。CONSTRAINT check_salary CHECK ( CASE WHEN sex = '2' THEN CASE WHEN salary <= 200000 THEN 1 ELSE 0 END ELSE 1 END = 1 )

在这段代码里，CASE 表达式被嵌入到 CHECK 约束里，描述了“如果是女性员工，则工资是 20 万日元以下”这个命题。在命题逻辑中，该命题是叫作蕴含式（conditional）的逻辑表达式，记作 P → Q。

这里需要重点理解的是蕴含式和逻辑与（logical product）的区别。逻辑与也是一个逻辑表达式，意思是“P 且 Q”，记作 P ∧ Q。用逻辑与改写的 CHECK 约束如下所示。CONSTRAINT check_salary CHECK ( sex = '2' AND salary <= 200000 )

当然，这两个约束的程序行为不一样。究竟哪里不一样呢？请先思考一下，再看下面的答案和解释。答案如果在 CHECK 约束里使用逻辑与，该公司将不能雇佣男性员工。而如果使用蕴含式，男性也可以在这里工作。解释要想让逻辑与 P ∧ Q 为真，需要命题 P 和命题 Q 均为真，或者一个为真且另一个无法判定真假。也就是说，能在这家公司工作的是“性别为女且工资在 20 万日元以下”的员工，以及性别或者工资无法确定的员工（如果一个条件为假，那么即使另一个条件无法确定真假，也不能在这里工作）。而要想让蕴含式 P → Q 为真，需要命题 P 和命题 Q 均为真，或者 P 为假，或者 P 无法判定真假。也就是说如果不满足“是女性”这个前提条件，则无需考虑工资约束。

请参考下面这个关于逻辑与和蕴含式的真值表。U 是 SQL 中三值逻辑的特有值 unknown 的缩写（关于三值逻辑，1-3 节将详细介绍）。

■逻辑与和蕴含式逻辑与蕴含式PQP ∧ QPQP → QTTTTTTTFFTFFTUUTUFFTFFTTFFFFFTFUFFUTUTUUTTUFFUFTUUUUUT

如上表所示，蕴含式在员工性别不是女性（或者无法确定性别）的时候为真，可以说相比逻辑与约束更加宽松。在 UPDATE 语句里进行条件分支

下面思考一下这样一种需求：以某数值型的列的当前值为判断对象，将其更新成别的值。这里的问题是，此时 UPDATE 操作的条件会有多个分支。例如，我们通过下面这样一张公司人事部的员工工资信息表 Salaries 来看一下这种情况。

■Salariesnamesalary相田300 000神崎270 000220 000木村齐藤290 000

假设现在需要根据以下条件对该表的数据进行更新。01. 对当前工资为 30 万日元以上的员工，降薪 10%。02. 对当前工资为 25 万日元以上且不满 28 万日元的员工，加薪

20%。

按照这些要求更新完的数据应该如下表所示。

乍一看，分别执行下面两个 UPDATE 操作好像就可以做到，但这样的结果却是不正确的。-- 条件1UPDATE Salaries SET salary = salary * 0.9 WHERE salary >= 300000;-- 条件2UPDATE Salaries SET salary = salary * 1.2 WHERE salary >= 250000 AND salary < 280000;

我们来分析一下不正确的原因。例如这里有一个员工，当前工资是 30 万日元，按“条件 1”执行 UPDATE 操作后，工资会被更新为 27 万日元，但继续按“条件 2”执行 UPDATE 操作后，工资又会被更新为 32.4 万日元。这样，本来应该被降薪的员工却被加薪了 2.4 万日元。

这样的结果当然并非人事部所愿。员工相田的工资必须被准确地降为 27 万日元。问题在于，第一次的 UPDATE 操作执行后，“当前工资”发生了变化，如果还拿它当作第二次 UPDATE 的判定条件，结果就会不准确。然而，即使将两条 SQL 语句的执行顺序颠倒一下，当前工资为 27 万日元的员工，其工资的更新结果也会出现问题。为了避免这些问题，准确地表达出可恶的人事部长的意图，可以像下面这样用 CASE 表达式来写 SQL。-- 用CASE 表达式写正确的更新操作UPDATE Salaries SET salary = CASE WHEN salary >= 300000 THEN salary * 0.9 WHEN salary >= 250000 AND salary < 280000 THEN salary * 1.2 ELSE salary END;

这条 SQL 语句不仅执行结果正确，而且因为只需执行一次，所以速度也更快。这样的话，人事部长就会满意了吧？

需要注意的是，SQL 语句最后一行的 ELSE salary 非常重要，必须写上。因为如果没有它，条件 1 和条件 2 都不满足的员工的工资就会被更新成 NULL。这一点与 CASE 表达式的设计有关，在刚开始介绍 CASE 表达式的时候我们就已经了解到，如果 CASE 表达式里没有明确指定 ELSE 子句，执行结果会被默认地处理成 ELSE NULL。现在大家明白笔者最开始强调使用 CASE 表达式时要习惯性地写上 ELSE 子句的理由了吧？

这个技巧的应用范围很广。例如，可以用它简单地完成主键值调换这种繁重的工作。通常，当我们想调换主键值 a 和 b 时，需要将主键值临时转换成某个中间值。使用这种方法时需要执行 3 次 UPDATE 操作，但是如果使用 CASE 表达式，1 次就可以做到。

SomeTablep_key（主键）col_1（第 1 列）col_2（第 2 列）a1一b2二c3三

如果在调换上表的主键值 a 和 b 时不用 CASE 表达式，则需要像下面这样写 3 条 SQL 语句。--1. 将a 转换为中间值dUPDATE SomeTable SET p_key = 'd' WHERE p_key = 'a';--2. 将b 调换为aUPDATE SomeTable SET p_key = 'a' WHERE p_key = 'b';--3. 将d 调换为bUPDATE SomeTable SET p_key = 'b' WHERE p_key = 'd';

像上面这样做，结果确实没有问题。只是，这里没有必要执行 3 次 UPDATE 操作，而且中间值 d 是否总能使用也是问题。而如果使用 CASE 表达式，就不必担心这些，1 次就可以完成调换。-- 用CASE 表达式调换主键值UPDATE SomeTable SET p_key = CASE WHEN p_key = 'a' THEN 'b' WHEN p_key = 'b' THEN 'a' ELSE p_key END WHERE p_key IN ('a', 'b');

显而易见，这条 SQL 语句按照“如果是 a 则更新为 b，如果是 b 则更新为 a”这样的条件分支进行了 UPDATE 操作。不只是主键，唯一键的调换也可以用同样的方法进行。本例的关键点和上一例的加薪与降薪一样，即用 CASE 表达式的条件分支进行的更新操作是一4气呵成的，因此可以避免出现主键重复所导致的错误 。

如果在 PostgreSQL 和 MySQL 数据库执行这条 SQL 语句，会因4主键重复而出现错误。但是，约束的检查本来就发生在更新完成后，因此更新途中主键一时出现重复也没有问题。事实上，在 Oracle、DB2、SQL Server 数据库执行都没有问题。

但是，一般来说需要进行这样的调换大多是因为表的设计出现了问题，所以请先重新审视一下表的设计，去掉不必要的约束。表之间的数据匹配

与 DECODE 函数等相比，CASE 表达式的一大优势在于能够判断表达式。也就是说，在 CASE 表达式里，我们可以使用 BETWEEN、LIKE 和 <、> 等便利的谓词组合，以及能嵌套子查询的 IN 和 EXISTS 谓词。因此，CASE 表达式具有非常强大的表达能力。

如下所示，这里有一张资格培训学校的课程一览表和一张管理每个月所设课程的表。

■课程一览 CourseMastercourse_idcourse_name1会计入门2财务知识3簿记考试4税务师

■开设的课程 OpenCoursesmonthcourse_id200706120070632007064200707420070822007084

我们要用这两张表来生成下面这样的交叉表，以便于一目了然地知道每个月开设的课程。course_name 6 月 7 月 8 月----------- ---- ---- ----会计入门 ○ × ×财务知识 × × ○簿记考试 ○ × ×税务师 ○ ○ ○

我们需要做的是，检查表 OpenCourses 中的各月里有表 CourseMaster 中的哪些课程。这个匹配条件可以用 CASE 表达式来写。-- 表的匹配：使用IN 谓词SELECT course_name, CASE WHEN course_id IN (SELECT course_id FROM OpenCourses WHERE month = 200706) THEN '○' ELSE '×' END AS "6 月", CASE WHEN course_id IN (SELECT course_id FROM OpenCourses WHERE month = 200707) THEN '○' ELSE '×' END AS "7 月", CASE WHEN course_id IN (SELECT course_id FROM OpenCourses WHERE month = 200708) THEN '○' ELSE '×' END AS "8 月" FROM CourseMaster;-- 表的匹配：使用EXISTS 谓词SELECT CM.course_name, CASE WHEN EXISTS (SELECT course_id FROM OpenCourses OC WHERE month = 200706 AND OC.course_id = CM.course_id) THEN '○' ELSE '×' END AS "6 月", CASE WHEN EXISTS (SELECT course_id FROM OpenCourses OC WHERE month = 200707 AND OC.course_id = CM.course_id) THEN '○' ELSE '×' END AS "7 月", CASE WHEN EXISTS (SELECT course_id FROM OpenCourses OC WHERE month = 200708 AND OC.course_id = CM.course_id) THEN '○' ELSE '×' END AS "8 月" FROM CourseMaster CM;

这样的查询没有进行聚合，因此也不需要排序，月份增加的时候仅修改 SELECT 子句就可以了，扩展性比较好。

无论使用 IN 还是 EXISTS，得到的结果是一样的，但从性能方面来说， EXISTS 更好。通过 EXISTS 进行的子查询能够用到“month, course_id”这样的主键索引，因此尤其是当表 OpenCourses 里数据比较多的时候更有优势。在 CASE 表达式中使用聚合函数

接下来介绍一下稍微高级的用法。这个用法乍一看可能让人觉得像是语法错误，实际上却并非如此。我们来看一道例题，假设这里有一张显示了学生及其加入的社团的一览表。如表 StudentClub 所示，这张表的主键是“学号、社团 ID”，存储了学生和社团之间多对多的关系。

■StudentClubstd_id(学号)club_id(社团 ID)club_name(社团main_club_flg (主社团标志)名)1001Y棒球1002管弦乐N2002管弦乐N2003Y羽毛球2004足球N3004足球N4005游泳N5006围棋N

有的学生同时加入了多个社团（如学号为 100、200 的学生），有的学生只加入了某一个社团（如学号为 300、400、500 的学生）。对于加入了多个社团的学生，我们通过将其“主社团标志”列设置为 Y 或者 N 来表明哪一个社团是他的主社团；对于只加入了一个社团的学生，我们将其“主社团标志”列设置为 N。

接下来，我们按照下面的条件查询这张表里的数据。01. 获取只加入了一个社团的学生的社团 ID。02. 获取加入了多个社团的学生的主社团 ID。

很容易想到的办法是，针对两个条件分别写 SQL 语句来查询。要想知道学生“是否加入了多个社团”，我们需要用 HAVING 子句对聚合结果进行判断。

■条件 1 的 SQL-- 条件1 ：选择只加入了一个社团的学生SELECT std_id, MAX(club_id) AS main_club FROM StudentClub GROUP BY std_idHAVING COUNT(*) = 1;

■执行结果 1std_id main_club------ ----------300 4400 5500 6

■条件 2 的 SQL-- 条件2 ：选择加入了多个社团的学生SELECT std_id, club_id AS main_club FROM StudentClub WHERE main_club_flg = 'Y' ;

■执行结果 2std_id main_club------ ----------100 1200 3

这样做也能得到正确的结果，但需要写多条 SQL 语句。而如果使用 CASE 表达式，下面这一条 SQL 语句就可以了。SELECT std_id, CASE WHEN COUNT(*) = 1 -- 只加入了一个社团的学生 THEN MAX(club_id) ELSE MAX(CASE WHEN main_club_flg = 'Y' THEN club_id ELSE NULL END) END AS main_club FROM StudentClub GROUP BY std_id;std_id main_club------ ----------100 1200 3300 4400 5500 6

这条 SQL 语句在 CASE 表达式里使用了聚合函数，又在聚合函数里使用了 CASE 表达式。这种嵌套的写法让人有点眼花缭乱，其主要目的是用 CASE WHEN COUNT(*) = 1 …… ELSE ……. 这样的 CASE 表达式来表示“只加入了一个社团还是加入了多个社团”这样的条件分支。我们在初学 SQL 的时候，都学过对聚合结果进行条件判断时要用 HAVING 子句，但从这道例题可以看到，在 SELECT 语句里使用 CASE 表达式也可以完成同样的工作，这种写法比较新颖。如果用一句话来形容这个技巧，可以这样说：

新手用 HAVING 子句进行条件分支，高手用 SELECT 子句进行条件分支。

通过这道例题我们可以明白：CASE 表达式用在 SELECT 子句里时，既可以写在聚合函数内部，也可以写在聚合函数外部。这种高度自由的写法正是 CASE 表达式的魅力所在。本节小结

本节，我们一起领略了 CASE 表达式的灵活和强大的表达能力。CASE 表达式是支撑 SQL 声明式编程的根基之一，也是灵活运用 SQL 时不可或缺的基础技能，请一定要学会它。在本书的后半部分，几乎没有哪一节不用到 CASE 表达式的，这也是把它放在本书开头来介绍的原因。

最后说一点细节的东西。CASE 表达式经常会因为同 VB 和 C 语言里的 CASE“语句”混淆而被叫作 CASE 语句。但是准确来说，它并不是语句，而是和 1+1 或者 a/b 一样属于表达式的范畴。结束符 END 确实看起来像是在标记一连串处理过程的终结，所以初次接触 CASE 表达式的人容易对这一点感到困惑。“表达式”和“语句”的名称区别恰恰反映了两者在功能处理方面的差异。

作为表达式，CASE 表达式在执行时会被判定为一个固定值，因此它可以写在聚合函数内部；也正因为它是表达式，所以还可以写在 SELECE 子句、GROUP BY 子句、WHERE 子句、ORDER BY 子句里。简单点说，在能写列名和常量的地方，通常都可以写 CASE 表达式。从这个意义上来说，与 CASE 表达式最接近的不是面向过程语言里的 CASE 语句，而是 Lisp 和 Scheme 等函数式语言里的 case 和 cond 这样的条件表达式。关于 SQL 和函数式语言的对比，第 2 章会进行介绍。

下面是本节要点。01. 在 GROUP BY 子句里使用 CASE 表达式，可以灵活地选择作为

聚合的单位的编号或等级。这一点在进行非定制化统计时能发挥

巨大的威力。02. 在聚合函数中使用 CASE 表达式，可以轻松地将行结构的数据

转换成列结构的数据。03. 相反，聚合函数也可以嵌套进 CASE 表达式里使用。04. 相比依赖于具体数据库的函数，CASE 表达式有更强大的表达能

力和更好的可移植性。05. 正因为 CASE 表达式是一种表达式而不是语句，才有了这诸多

优点。

如果想了解更多关于 CASE 表达式的内容，请参考下面的文献资料。01. Joe Celko，《SQL 权威指南（第 4 版）》（人民邮电出版

社，2013 年）请参考 15.3.5 节“在 UPDATE 中使用 CASE 表达式”和

18.1 节“CASE 表达式”等。从 CASE 表达式的详细用法到具

体事例，这两节都有广泛的介绍。02. Joe Celko，《SQL 解惑（第 2 版）》（人民邮电出版社，

2008 年）关于在 CASE 表达式中嵌入聚合函数，请参考“谜题 13 教

师”“谜题 36 双重职务”“谜题 43 毕业”。另外，“谜题 44 成对

的款式”运用了在 UPDATE 里进行条件分支的技巧，“谜题 45

辣味香肠比萨饼”用 CASE 表达式巧妙地将行结构的数据转换

成了列结构的数据。练习题

●练习题1-1-1 ：多列数据的最大值

用 SQL 从多行数据里选出最大值或最小值很容易——通过 GROUP BY 子句对合适的列进行聚合操作，并使用 MAX 或 MIN 聚合函数就可以求出。那么，从多列数据里选出最大值该怎么做呢？

样本数据如下表所示。

GreatestskeyxyzA123B552C471D338

先思考一下从表里选出 x 和 y 二者中较大的值的情况。此时求得的结果应该如下所示。key greatest----- ---------A 2B 5C 7D 3

Oracle 和 MySQL 数据库直接提供了可以实现这个需求的 GREATEST 函数，但是这里请不要用这些函数，而用标准 SQL 的方法来实现。

求出 x 和 y 二者中较大的值后，再试着将列数扩展到 3 列以上吧。这次求的是 x、y 和 z 三者中的最大值，因此结果应该如下所示。key greatest----- ---------A 3B 5C 7D 85

●练习题1-1-2 ：转换行列——在表头里加入汇总和再揭常用于表示再次使用前述内容，这里指的是在表格中以合计值5的形式再次体现德岛、香川、爱媛和高知这 4 个县的数据。 ——译者注

使用正文中的表 PopTbl2 作为样本数据，练习一下把行结构的数据转换为列结构的数据吧。这次请生成下面这样的表头里带有汇总和再揭的二维表。性别 全国 德岛 香川 爱媛 高知 四国（再揭）---- ------ ----- ------ ------ ------- ----------男 855 60 100 100 100 360女 845 40 100 50 100 290“全国”列里是表 PopTbl2 中的所有都道府县（限于篇幅，还有一些都道府县未列出）人口的合计值。另外，最右边的一列“四国（再揭）”是四国地区 4 个县的合计值。

●练习题1-1-3 ：用 ORDER BY 生成“排序”列

最后这个练习题用到的是比较小众的技巧，但有时又必须使用它，所以我们也来看一下。

对练习题 1-1-1 里用过的表 Greatests 正常执行 SELECT key FROM Greatests ORDER BY key; 这个查询后，结果会按照 key 这一列值的字母表顺序显示出来。

那么，请思考一个查询语句，使得结果按照 B-A-D-C 这样的指定顺序进行排列。这个顺序并没有什么具体的意义，大家也可以在实现完上述需求后，试着实现让结果按照其他顺序排列。1-2　自连接的用法▶面向集合语言SQLSQL通常在不同的表或者视图间进行连接运算，但是也可以对相同的表进行“自连接”运算。自连接的处理过程不太容易想象，因此人们常常对其敬而远之。但是，如果能熟练掌握，就会发现它是非常方便的技术。本节，我们将一起学习自连接的用法。写在前面

SQL 的连接运算根据其特征的不同，有着不同的名称，如内连接、外连接、交叉连接等。一般来说，这些连接大都是以不同的表或视图为对象进行的，但针对相同的表或相同的视图的连接也并没有被禁止。针对相同的表进行的连接被称为“自连接”（self join）。一旦熟练掌握自连接技术，我们便能快速地解决很多问题。但是，其处理过程不太容易想象，以至于常常被人们敬而远之。因此在本节里，我们将通过例题体会一下自连接的便利性，并理解一下它的处理过程。

理解自连接不仅可以让我们学会实际工作中能用到的技能，还能增进我们对“面向集合”这一 SQL 语言重要特征的理解。面向对象语言以对象的方式来描述世界，而面向集合语言 SQL 以集合的方式来描述世界。自连接技术充分体现了 SQL 面向集合的特性，相信大家在读完本节后再看二维表状的表格时，就会觉得这种表格更像是集合。可重排列、排列、组合

假设这里有一张存放了商品名称及价格的表，表里有“苹果、橘子、香蕉”这 3 条记录。在生成用于查询销售额的报表等的时候，我们有时会需要获取这些商品的组合。

Productsname（商品名称）price（价格）苹果50100橘子香蕉80

这里所说的组合其实分为两种类型。一种是有顺序的有序对（ordered pair），另一种是无顺序的无序对（unordered pair）。有序对用尖括号括起来，如 <1, 2>；无序对用花括号括起来，如 {1, 2}。在有序对里，如果元素顺序相反，那就是不同的对，因此 <1, 2> ≠ <2, 1> ；而无序对与顺序无关，因此 {1, 2} ＝ {2, 1}。用学校里学到的术语来说，这两类分别对应着“排列”和“组合”。

用 SQL 生成有序对非常简单。像下面这样通过交叉连接生成笛1卡儿积（直积），就可以得到有序对 。在支持 CROSS JOIN 写法的数据库里，也可以写作 FROM 1Products P1 CROSS JOIN Products P2。如果这样写，我们从代码上就能明确看出是在进行 CROSS JOIN，但是为了和排列及组合的代码进行比较，这里采用的是旧式写法。-- 用于获取可重排列的SQL 语句SELECT P1.name AS name_1, P2.name AS name_2 FROM Products P1, Products P2;

■执行结果name_1 name_2------ ------苹果 苹果苹果 橘子苹果 香蕉橘子 苹果橘子 橘子橘子 香蕉香蕉 苹果香蕉 橘子香蕉 香蕉

执行结果里每一行（记录）都是一个有序对。因为是可重排列，2所以结果行数为 3 ＝ 9。结果里出现了（苹果, 苹果）这种由相同元素构成的对，而且（橘子, 苹果）和（苹果, 橘子）这种只是调换了元素顺序的对也被当作不同的对了。这是因为，该查询在生成结果集合时会区分顺序。

接下来，我们思考一下如何更改才能排除掉由相同元素构成的对。首先，为了去掉（苹果, 苹果）这种由相同元素构成的对，需要像下面这样加上一个条件，然后再进行连接运算。-- 用于获取排列的SQL 语句SELECT P1.name AS name_1, P2.name AS name_2 FROM Products P1, Products P2 WHERE P1.name <> P2.name;

■执行结果name_1 name_2------ ------苹果 橘子苹果 香蕉橘子 苹果橘子 香蕉香蕉 苹果香蕉 橘子

加上 WHERE P1.name <> P2.name 这个条件以后，就能排除掉由相同元素构成的对，结果行数为排列。理解这个连接的关键在于想象一下这里存在下面这样的两张表。

■不能有（苹果, 苹果）这样的组合

当然，无论是 P1 还是 P2，实际上数据都来自同一张物理表 Products（见 P.22）。但是，在 SQL 里，只要被赋予了不同的名称，即便是相同的表也应该当作不同的表（集合）来对待。也就是说，P1 和 P2 可以看成是碰巧存储了相同数据的两个集合。这样的话，这个自连接的处理结果就成了下面这样。● P1 里的“苹果”行的连接对象为 P2 里的“橘子、香蕉”这两

行● P1 里的“橘子”行的连接对象为 P2 里的“苹果、香蕉”这两

行● P1 里的“香蕉”行的连接对象为 P2 里的“苹果、橘子”这两

行

由此我们可以认为，相同的表的自连接和不同表间的普通连接并没有什么区别，自连接里的“自”这个词也没有太大的意义。

这次的处理结果依然是有序对。接下来我们进一步对（苹果, 橘子）和（橘子, 苹果）这样只是调换了元素顺序的对进行去重。请看下面的 SQL 语句。-- 用于获取组合的SQL 语句SELECT P1.name AS name_1, P2.name AS name_2 FROM Products P1, Products P2 WHERE P1.name > P2.name;

■执行结果name_1 name_2------ ------苹果 橘子香蕉 橘子香蕉 苹果

同样地，请想象这里存在 P1 和 P2 两张表。在加上“不等于”这个条件后，这条 SQL 语句所做的是，按字符顺序排列各商品，只与“字符顺序比自己靠前”的商品进行配对，结果行数为组合。到这里，我们终于得到了无序对。恐怕平时我们说到组合的时候，首先想到的就是这种类型的组合吧。

想要获取 3 个以上元素的组合时，像下面这样简单地扩展一下就可以了。这次的样本数据只有 3 行，所以结果应该只有 1 行。-- 用于获取组合的SQL 语句：扩展成3 列SELECT P1.name AS name_1, P2.name AS name_2, P3.name AS name_3 FROM Products P1, Products P2, Products P3 WHERE P1.name > P2.name AND P2.name > P3.name;

■执行结果name_1 name_2 name_3------- -------- --------香蕉 苹果 橘子

如这道例题所示，使用等号“＝”以外的比较运算符，如“<、>、<>”进行的连接称为“非等值连接”。这里将非等值连接与自连接结合使用了，因此称为“非等值自连接”。在需要获取列的组合时，我们经常需要用到这个技术，请牢记。

最后补充一点，“>”和“<”等比较运算符不仅可以用于比较数值大小，也可以用于比较字符串（比如按字典序进行比较）或者日期等。

试读结束[说明：试读内容隐藏了图片]

下载完整电子书