SQL高级查询（二）

今天我们继续来看SQL语句的一些高级用法。

1 函数

之前我们已经掌握了聚合函数的用法， 实际上SQL中其他类型函数还有200多种，这里我们只介绍常用的几种就可以了。

算术函数

abs（数值）：

该函数是绝对值函数，返回括号中数值的绝对值。

mod（被除数，除数）：

该函数是求余函数。例如，4/2的余数是0，所以mod（4,2）的结果也是0.

round（数值，需保留的小数位数）：

round是四舍五入函数。例如，对于2.345我们想保留2位小数，结果就是

round（2.345，2）= 2.35

字符串函数

concat（字符串1，字符串2，...）：

该函数是字符串拼接函数，顾名思义，它是将括号中的字符串都连接起来。例如，concat（‘我’，‘爱’，‘吃鸡肉’）的结果就是‘我爱吃鸡肉’。

length（字符串）：

该函数为计算字符串长度的函数。如length（‘ABCD’）的结果是4.

lower（字符串）：

该函数为小写转换函数。例如，lower（‘ABCD’）的结果就是‘abcd’。

upper（字符串）：

该函数为大写转换函数。例如，upper（‘abc’）的结果为‘ABC’。

replace（对象，需要替换的字符串，替换之后的字符串）：

该函数为替换字符串函数。例如，replace（‘123木头人’，‘木头人’，‘456’）的结果为‘123456’。

substring（对象 from 截取的起始位置 for 截取的字符个数）：

该函数为字符串截取函数。例如，substring（‘123木头人’ from 2 for 3）的结果为‘23木’。

日期函数

current_date：

返回当前日期（年月日），注意没有括号。

current_time:

返回当前时间（时分秒），注意没有括号。

current_timestamp：

返回当前日期和时间（年月日时分秒），也是没有括号。

extract（元素 from 日期）：

该函数为日期截取函数，可以将日期中的年月日时分秒单独剥离出来。例如，extract（year from ‘2019-01-01’）的返回结果为2019。注意，返回的结果是数值类型。

转换函数

cast（对象 as 想要转换的数据类型）：

该函数可将原来的数据类型转换为另一种你所需的数据类型。例如，cast（‘2019-01-01’ as date）可将字符串‘2019-01-01’转换为日期2019-01-01.

coalesce（参数1，参数2，...）：

该函数返回参数中从左边开始第一个不为null的值。例如，coalesce（null，‘ABC’，null）的返回结果就是‘ABC’。那这个函数有什么用呢？它可以将列中的null值转换为其它值。假如我有一列名为tool的数据：

tool（列名）

pc

pad

null

telephone

可以看到，tool列中含有null值，那么使用coalesce（tool，1）的结果就是：

tool（列名）

pc

pad

1

telephone

null值被1所替换。

2 谓词

什么是谓词？谓词就是返回值为true/false/unknown的函数。像之前我们使用过的>、

like

like谓词用来进行一致性的查询，主要有3种情况。我们来看几个例子：

like ‘abc%’

%的含义是任意长度的字符串，这个like子句的含义是，以abc开头的，任意字符长度的字符串（包括0）。

like ‘%abc%’

这条子句的含义是，包含有abc的任意长度的字符串。

like ‘%abc’

这条子句的含义是，以abc结尾的任意长度的字符串。

like ‘abc_’

这条子句的含义是，以abc开头的，随后只有1个字符的字符串。'_'下划线代表一个字符。

in

in谓词的用法跟or类似。举个例子，我们有不同价格的商品，现在要筛选出价格为10,100,1000的三种商品。用or子句，可以写为：

price = 10 or price =100 or price =1000；

而用in谓词的话，可以写为：

price in （10,100,1000）

简洁明了。同样，如果想筛选除了10,100,1000之外的商品，可以用not in：

price not in （10,100,1000）

3 case表达式

case表达式也是SQL中很重要的内容，它主要是在区分情况时出现。case表达式的语法如下：

case when 返回真值的表达式 then 表达式

when返回真值的表达式 then 表达式

...

else 表达式

end

其中，返回真值，就是返回true、false、unknown这三个值。如果when值为真，则执行then后的表达式，case语句执行完毕；如果when值不为真，则执行下一行的when语句，重复上一过程；如果所有when值都不为真，则执行else后的表达式，随后case语句执行完毕。

我们还是用shell数据库中的cargo表来举例，首先来看下cargo表中的数据：

现在我们想要达到这样的效果：

A：电脑

B：键盘

C：耳机

注意到并没有哪一列含有‘ABC’，这时就可以用case表达式了：

如上图，else子句也可以省略，默认返回null。

case表达式还有一个重要的用途就是进行行列转换，如果我想得到不同类型商品的汇总价格，但是要以下的效果：

类型1 类型2 类型3

价格1 价格2 价格3

如果我们按之前的group by分组，其结果如下图：

可以看到，类型在行的位置，现在需要进行转置，将类型放在列的位置，可以用case表达式来实现：

如上图，限于篇幅，我只写了电脑和键盘类。其思路是，如果类型为电脑，则返回其价格，否则返回0，最后对case表达式返回的值进行求和，就得到了电脑类的汇总价格。对于其他类别也是一样。

好了，这就是今天的全部内容。未完待续，敬请期待。