正文

3  数据库函数、谓词和CASE表达式

SQL之所以具有强大的分析表达能力，其中一个重要原因，就是它具备丰富的函数，通过这些函数的组合可以实现对数据的复杂处理，最终得到我们想要的数据。另外一方面，SQL也有丰富的谓词来对数据进行判断，匹配出符合我们需求的数据。CASE表达式是一种多条件判断表达式，可以根据不同条件返回不同的值，类似于编程语言中的IF ELSE。

3.1  聚合函数

聚合函数，又称分析函数，是将一组值通过聚合分析后得到一个值，因此得名聚合函数。使用频率最高的聚合函数有5个，如下表所示

聚合函数有一个共同的特点，即在计算过程中都会忽略掉NULL值，因为对NULL的聚合是没有任何意义的。COUNT、SUM和AVG三个函数还可以和DISTINCT配合使用，其含义为先对目标列进行去重，之后再对去重后的结果聚合。SUM和AVG只能应用于一列，且列的数据类型为数值型。MIN和MAX也是只能应用于一列，不过除了支持数值型外，还支持字符串类型和日期类型。COUNT可以应用于一列或多列，而且不限制列的类型。

3.2  算术函数

算术函数，主要用于对数值类型进行各种数学运算。SQL中除了加减乘除（+-*/）四个运算符外，还提供了一系列的算术函数，如下表所示：

这里只罗列了常用的一些函数，通过他们之间的组合，可以实现复杂的运算，如果上述表格不满足你的分析需求，可以自行Google或查看官方文档，寻找匹配的算术函数。

3.3  日期函数

日常分析工作中，经常需要对日期进行加减、格式化等处理，这就离不开强大的日期处理函数，常用的日期函数如下：

日期函数用于获取当前日期时间的函数多数是空参数函数，虽然参数为空，但是函数名后的括号不能省略不写。数据库厂商虽然也提供了部分与函数名相同的属性值，不带括号也能调用，不过笔者建议最好还是使用函数带上空括号，这样识别度更高，可读性更好。

3.4  字符串函数

字符串是信息的一个重要载体，其中包含着大量的重要信息，因此对字符串的处理非常重要，相应地字符串处理函数也是非常丰富，以下我们罗列出最常用的一些函数：

3.5  转换函数

当某些数据的类型与我们需要的类型不符时，可以使用类型转换函数，将其类型转换为我们需要的类型。常用的类型转换函数有两个，分别为CAST和CONVERT，两个函数的作用是相同的，只是语法略有不同。CAST函数的用法为CAST(字段 AS 数据类型)，而CONVERT的用法为CONVERT(字段, 数据类型)。

不过，并不是所有的类型都是可以互相转换的，而且有些转换会导致精度的损失，因此请谨慎使用。

3.6  其他函数

还有一些函数是使用在特定用途上的，本文也罗列出几个数据分析工作中常用的。

MD5函数，其作用是生成等长的信息摘要。在数据分析工作中，经常用于对敏感信息的脱敏，因为很难通过md5值反向推断加密前的内容，因此是非常安全的。其使用方法为，MD5(str)，返回对str进行md5算法计算得到的校验和字符串。

IFNULL(expr1, expr2)：如果expr1不为NULL，则返回expr1，否则返回expr2。通常用于对某个字段的NULL值填补，也叫缺失值填补。

IF(expr1, expr2, expr3)：如果expr1不等于0或者不为NULL，则返回expr2的值，否则返回expr3的值。相当于编程语言中的IF ELSE条件判断语句

3.7  谓词

简单来说，谓词就是用于真假判断的关键字，用来判定两个对象间关系论断的真假，返回值只有真或假。这么说可能还是有点抽象。我们来举一些谓词的例子大家就明白了。

例如，我们前面讲到的比较运算符，就都属于谓词的范畴。还有一些其他谓词如下表所示：

3.8  CASE表达式

SQL语句中的CASE表达式，对应着编程语言中的条件分支，起到多条件判断返回多种值的作用。其语法形式为：

CASE

WHEN <求值表达式1> THEN <表达式1>

WHEN <求值表达式2> THEN <表达式2>

WHEN <求值表达式3> THEN <表达式3>

......

ELSE <表达式> END

其执行过程为，按照书写顺序，依次判断WHEN后面求值表达式返回的值为真或假，如果返回值为假，则继续向下搜索；如果返回值为真时，执行THEN后面对应的表达式，将执行后的值返回，CASE表达式退出；如果所有WHEN子句都不满足时，则执行ELSE后面的表达式，返回执行后得到的值，CASE表达式退出。

了解了执行过程，那么在书写CASE表达式时，就一定要注意顺序问题。这里需要注意一点的是，如果执行到第二个THEN的时候，实际生效的条件为<求值表达式1>的值为假，与此同时<求值表达式2>的值为真；如果执行到第三个THEN的时候，实际生效的条件为<求值表达式1>和<求值表达式2>的值都为假，与此同时<求值表达式3>的值为真，往后以此类推。

3.9  NULL值判断

NULL值的判断必须使用谓词IS，因为NULL和其他任何值（包括NULL值）比较结果都为NULL，也就对应着假。这一点很好理解，你可以把NULL值理解为未知。未知和任何值比较结果还是未知，未知和未知比较，结果也只能是未知。

4  关联查询与子查询

拥有了前面3部分的知识基础，那么我们就可以开始学习SQL的复杂查询。本文要讲的复杂查询有两个，一个是关联查询，一个是子查询。首先，我们先来看下他们的理论基础，集合运算。

4.1  集合运算

在第1部分，我们提到过，在数据库领域，集合是指一组记录的总和，它可以指代表，也可以指代视图、查询执行的结果。所以，表和查询执行的结果都是集合，那么就都可以参与集合运算。也就是说，可以把查询执行的结果看做是一张中间表或临时表，继续参与运算，这就是子查询的理论基础。

集合运算主要包含四种，并集、交集、差集和笛卡尔积。

并集，是求两个集合合并后的集合。在MySQL中使用关键字UNION或UNION ALL实现，两者的区别是，UNION会剔除掉合并后集合中的多余重复值，只保留一份；而UNION ALL，不会剔除重复值。因此，UNION操作，运行结束后，可能会导致记录数的减少。

交集，是求两个集合都共同拥有的元素的集合。在MySQL中没有提供专门的关键字，而是通过内关联实现的，下一小结会详细介绍。

差集，是求在一个集合中存在而在另一个集合中不存在的元素的集合。差集计算具有方向性，同样的，MySQL也没有提供差集计算的关键字，而是需要通过左/右关联然后再过滤出未关联成功的记录而得到。

笛卡尔积，是将两个集合中记录两两组合，相当于集合的乘法。它是关联查询的数学理论基础。你可以简单理解为，关联查询的过程就是，先做笛卡尔积，然后再通过on条件过滤出符合条件的记录。当然，实际的执行过程，不会这么简单，但是是在这个流程基础上去做优化，减少计算量的。

在进行集合的并集、交集和差集运算时，需要注意的是：

参与运算的两个集合记录的列数必须相同

参与运算的两个集合对应位置的列的类型必须一致

如果使用ORDER BY子句，必须写在最后

4.2  表关联类型

常见的表关联类型有四种，内连接(INNER JOIN)、左外连接(LEFT OUTER JOIN)、右外连接(RIGHT OUTER JOIN)、全外连接(FULL OUTER JOIN)。

关联的语法比较简单，拿内连接举例，书写为，A INNER JOIN B ON expr。其中，A和B表示两个表的名称，也可以是子查询。ON后面跟的expr表示关联条件，通常是由表A和表B关联字段组成的表达式。

内连接(INNER JOIN)，通常可以省略掉INNER不写，它的含义是左右两个集合相乘后，只保留满足ON后面关联条件的记录。所以，可以利用内连接计算两个集合的交集，只需要把集合元素的字段都写在ON后面的关联条件里即可。

左外连接(LEFT OUTER JOIN)，OUTER通常可以省略不写，它的含义是，左右两个集合相乘后，保留满足ON后面关联条件的记录加上左表中原有的但未关联成功的记录。因此，左外连接，可以用来计算集合的差集，只需要过滤掉关联成功的记录，留下左表中原有的但未关联成功的记录，就是我们要的差集。

右外连接(RIGHT OUTER JOIN)，与左外连接含义相同，只是方向不同而已，通常也是省略OUTER不写。

全外连接(FULL OUTER JOIN)，含义是，左右两个集合相乘后，保留满足ON后面关联条件的记录加上左表和右表中原有的但未关联成功的记录。

4种JOIN方式的示意图

4.3  多表关联

多表关联的本质，还是两两关联。例如，表A内关联表B再内关联表C，实际上就可以等价于表A内关联表B，运行后的结果作为一张中间表，然后再与表C内关联。所以，执行过程仍然是两两关联。

4.4  表关联注意事项

表关联是比较复杂的查询方式，在书写时，大家要在脑海中构建关联后的集合的样子，对应去选择需要使用的连接方法。下面是根据实际工作经验总结的容易出错的点，希望大家注意。

a. 使用UNION可能会导致记录数的减少，在使用聚合函数时，可能会导致计算出现偏差

b. 在使用1对多或多对多关系的表进行关联时，记录数可能会增多，也可能会导致计算出现偏差

c. 左外连接和右外连接都有连接方向的问题，表放的位置对结果是有影响的，尤其是多表关联时，一定要关注书写的顺序，尽可能先做内连接再做左/右外连接。

d. 尽量避免使用交叉连接

4.5  子查询

子查询，就是指被括号嵌套起来的查询SQL语句，通常是一条完整的SELECT语句。

子查询放在不同的位置，起到的作用也是不同的。它经常出现在3个位置上，分别是SELECT后面、FROM/JOIN后面，还有WHERE/HAVING后面。

当子查询出现在SELECT后面时，其作用通常是要为结果添加一列。不过，这里要注意的是，在SELECT后使用的子查询语句只能返回单个列，且要保证满足条件时子查询语句只会返回单行结果。企图检索多个列或返回多行结果将引发错误。

子查询出现在FROM/JOIN后面，是我们最常用的方式，就是将子查询的结果作为中间表，继续基于这个表做分析。

当子查询出现在WHERE/HAVING后面时，则表示要使用子查询返回的结果做过滤。这里根据子查询返回的结果数量，分三种情况，即1行1列、N行1列、N行N列。

当返回结果为1行1列时，实际上就是返回了一个具体值，这种子查询又叫标量子查询。标量子查询的结果，可以直接用比较运算符来进行计算。

当返回结果是N行1列时，实际上就是返回了一个相同类型数值的集合。因此可以使用IN谓词判断，同时也可以配合ANY、SOME、ALL等关键字使用。

当返回结果是N行N列时，实际上就是返回一个临时表，这时就不能进行值的比较了，而是使用EXISTS谓词判断返回的集合是否为空。