不仅 SQL，对所有的编程语言来说，函数都起着至关重要的作用。函数就像是编程语言的“道具箱”，每种编程语言都准备了非常多的函数。
使用函数，我们可以实现计算、字符串操作、日期计算等各种各样的运算。
一、函数的种类
前几篇和大家一起学习了 SQL 的语法结构等必须要遵守的规则。本文将会进行一点改变，来学习一些 SQL 自带的便利工具——函数。
我们在 SQL 如何对表进行聚合和分组查询并对查询结果进行排序 中已经学习了函数的概念，这里再回顾一下。所谓函数，就是输入某一值得到相应输出结果的功能，输入值称为参数（parameter），输出值称为返回值。
函数大致可以分为以下几种。
算术函数（用来进行数值计算的函数）
字符串函数（用来进行字符串操作的函数）
日期函数（用来进行日期操作的函数）
转换函数（用来转换数据类型和值的函数）
聚合函数（用来进行数据聚合的函数）
我们已经在 SQL 如何对表进行聚合和分组查询并对查询结果进行排序 中学习了聚合函数的相关内容，大家应该对函数有初步的了解了吧。
聚合函数基本上只包含 COUNT、SUM、AVG、MAX、MIN 这 5 种，而其他种类的函数总数则超过 200 种。
可能大家会觉得怎么会有那么多函数啊，但其实并不需要担心，虽然数量众多，但常用函数只有 30 ~ 50 个。不熟悉的函数大家可以查阅参考文档（词典）来了解 。
本文我们将学习一些具有代表性的函数。大家并不需要一次全部记住，只需要知道有这样的函数就可以了，实际应用时可以查阅参考文档。
接下来，让我们来详细地看一看这些函数。
二、算术函数
算术函数是最基本的函数，其实之前我们已经学习过了，可能有些读者已经想起来了。没错，就是 算术运算符和比较运算符 介绍的加减乘除四则运算。
由于这些算术运算符具有“根据输入值返回相应输出结果”的功能，因此它们是出色的算术函数。在此我们将会给大家介绍除此之外的具有代表性的函数。
为了学习算术函数，我们首先根据代码清单 1 创建一张示例用表（SampleMath）。
NUMERIC 是大多数 DBMS 都支持的一种数据类型，通过 NUMBERIC ( 全体位数, 小数位数 ) 的形式来指定数值的大小。
接下来，将会给大家介绍常用的算术函数——ROUND 函数，由于 PostgreSQL 中的 ROUND 函数只能使用 NUMERIC 类型的数据，因此我们在示例中也使用了该数据类型。
代码清单 1 创建 SampleMath 表
1
2
3
4
5
| -- DDL ：创建表
CREATE TABLE SampleMath
(m NUMERIC (10,3),
n INTEGER,
p INTEGER);
|
SQL Server PostgreSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| -- DML ：插入数据
BEGIN TRANSACTION; -----①
INSERT INTO SampleMath(m, n, p) VALUES (500, 0, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (-180, 0, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, NULL, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 7, 3);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 5, 2);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 4, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (8, NULL, 3);
INSERT INTO SampleMath(m, n, p) VALUES (2.27, 1, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (5.555,2, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 1, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (8.76, NULL, NULL);
COMMIT;
|
下面让我们来确认一下创建好的表中的内容，其中应该包含了 m、n、p 三列。
1
| SELECT * FROM SampleMath;
|
执行结果：
1
2
3
4
5
6
7
8
9
10
11
12
13
| m | n | p
---------+---+--
500.000 | 0 |
-180.000 | 0 |
| |
| 7 | 3
| 5 | 2
| 4 |
8.000 | | 3
2.270 | 1 |
5.555 | 2 |
| 1 |
8.760 | |
|
2.1 ABS——绝对值
语法 1 ABS 函数
ABS 是计算绝对值的函数。绝对值（absolute value）不考虑数值的符号，表示一个数到原点的距离。
简单来讲，绝对值的计算方法就是：0 和正数的绝对值就是其本身，负数的绝对值就是去掉符号后的结果。
代码清单 2 计算数值的绝对值
1
2
3
| SELECT m,
ABS(m) AS abs_col
FROM SampleMath;
|
执行结果：
右侧的 abs_col 列就是通过 ABS 函数计算出的 m 列的绝对值。请大家注意，-180 的绝对值就是去掉符号后的结果 180。
通过上述结果我们可以发现，ABS 函数的参数为 NULL 时，结果也是 NULL。并非只有 ABS 函数如此，其实绝大多数函数对于 NULL 都返回 NULL 。
2.2 MOD——求余
语法 2 MOD 函数
MOD 是计算除法余数（求余）的函数，是 modulo 的缩写。例如，7/3 的余数是 1，因此 MOD(7, 3) 的结果也是 1（代码清单 3）。
因为小数计算中并没有余数的概念，所以只能对整数类型的列使用 MOD 函数。
代码清单 3 计算除法（n ÷ p）的余数
Oracle DB2 PostgreSQL MySQL
1
2
3
| SELECT n, p,
MOD(n, p) AS mod_col
FROM SampleMath;
|
执行结果：
1
2
3
4
5
6
7
8
9
10
11
12
13
| n | p | mod_col
---+---+--------
0 | |
0 | |
| |
7 | 3 | 1
5 | 2 | 1
4 | |
| 3 |
1 | |
2 | |
1 | |
| |
|
这里有一点需要大家注意：主流的 DBMS 都支持 MOD 函数，只有 SQL Server 不支持该函数。
2.3 ROUND——四舍五入
语法 3 ROUND 函数
ROUND 函数用来进行四舍五入操作。四舍五入在英语中称为 round。
如果指定四舍五入的位数为 1，那么就会对小数点第 2 位进行四舍五入处理。如果指定位数为 2，那么就会对第 3 位进行四舍五入处理（代码清单 4）。
代码清单 4 对 m 列的数值进行 n 列位数的四舍五入处理
1
2
3
| SELECT m, n,
ROUND(m, n) AS round_col
FROM SampleMath;
|
执行结果：
1
2
3
4
5
6
7
8
9
10
11
12
13
| m | n | round_col
---------+---+----------
500.000 | 0 | 500
-180.000 | 0 | -180
| |
| 7 |
| 5 |
| 4 |
8.000 | |
2.270 | 1 | 2.3
5.555 | 2 | 5.56
| 1 |
8.760 | |
|
三、字符串函数
截至目前，我们介绍的函数都是主要针对数值的算术函数，但其实算术函数只是 SQL（其他编程语言通常也是如此）自带的函数中的一部分。
虽然算术函数是我们经常使用的函数，但是字符串函数也同样经常被使用。
在日常生活中，我们经常会像使用数字那样，对字符串进行替换、截取、简化等操作，因此 SQL 也为我们提供了很多操作字符串的功能。
为了学习字符串函数，我们再来创建一张表（SampleStr），参见代码清单 5。
代码清单 5 创建 SampleStr 表
1
2
3
4
5
| -- DDL ：创建表
CREATE TABLE SampleStr
(str1 VARCHAR(40),
str2 VARCHAR(40),
str3 VARCHAR(40));
|
SQL Server PostgreSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| -- DML ：插入数据
BEGIN TRANSACTION; -------------①
INSERT INTO SampleStr (str1, str2, str3) VALUES ('opx' ,'rt',NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc' ,'def' ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('山田' ,'太郎' ,'是我');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('aaa' ,NULL ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES (NULL ,'xyz',NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('@!#$%' ,NULL ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('ABC' ,NULL ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('aBC' ,NULL ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc太郎' ,'abc' ,'ABC');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abcdefabc' ,'abc' ,'ABC');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('micmic' ,'i' ,'I');
COMMIT;
|
下面让我们来确认一下创建好的表中的内容，其中应该包含了 str1、str2、str3 三列。
1
| SELECT * FROM SampleStr;
|
执行结果：
1
2
3
4
5
6
7
8
9
10
11
12
13
| str1 | str2 | str3
-----------+------+-----
opx | rt |
abc | def |
山田 | 太郎 | 是我
aaa | |
| xyz |
@!#$% | |
ABC | |
aBC | |
abc太郎 | abc | ABC
abcdefabc | abc | ABC
micmic | i | I
|
3.1 ||——拼接
语法 4 || 函数
在实际业务中，我们经常会碰到 abc + de = abcde 这样希望将字符串进行拼接的情况。在 SQL 中，可以通过由两条并列的竖线变换而成的“||”函数来实现（代码清单 6）。
代码清单 6 拼接两个字符串（str1+str2）
Oracle DB2 PostgreSQL
1
2
3
| SELECT str1, str2,
str1 || str2 AS str_concat
FROM SampleStr;
|
执行结果：
1
2
3
4
5
6
7
8
9
10
11
12
13
| str1 | str2 | str_concat
-----------+------+------------
opx | rt | opxrt
abc | def | abcdef
山田 | 太郎 | 山田太郎
aaa | |
| xyz |
@!#$% | |
ABC | |
aBC | |
abc太郎 | abc | abc太郎abc
abcdefabc | abc | abcdefabcabc
micmic | i | micmaci
|
进行字符串拼接时，如果其中包含 NULL，那么得到的结果也是 NULL。这是因为“||”也是变了形的函数。当然，三个以上的字符串也可以进行拼接（代码清单 7）。
代码清单 7 拼接三个字符串（str1+str2+str3）
Oracle DB2 PostgreSQL
1
2
3
4
| SELECT str1, str2, str3,
str1 || str2 || str3 AS str_concat
FROM SampleStr
WHERE str1 = '山田';
|
执行结果：
1
2
3
| str1 | str2 | str3 | str_concat
------+------+------+-----------
山田 | 太郎 | 是我 | 山田太郎是我
|
这里也有一点需要大家注意，|| 函数在 SQL Server 和 MySQL 中无法使用。
3.2 LENGTH——字符串长度
语法 5 LENGTH 函数
想要知道字符串中包含多少个字符时，可以使用 LENGTH（长度）函数（代码清单 8）。
代码清单 8 计算字符串长度
Oracle DB2 PostgreSQL MySQL
1
2
3
| SELECT str1,
LENGTH(str1) AS len_str
FROM SampleStr;
|
执行结果：
1
2
3
4
5
6
7
8
9
10
11
12
13
| str1 | len_str
-----------+--------
opx | 3
abc | 3
山田 | 2
aaa | 3
|
@!#$% | 5
ABC | 3
aBC | 3
abc太郎 | 5
abcdefabc | 9
micmic | 6
|
需要注意的是，该函数也无法在 SQL Server 中使用。
我想大家应该逐渐明白“SQL 中有很多特定的用法”这句话的含义了吧。
3.3 LOWER——小写转换
语法 6 LOWER 函数
LOWER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写（代码清单 9）。
因此，该函数并不适用于英文字母以外的场合。此外，该函数并不影响原本就是小写的字符。
代码清单 9 大写转换为小写
1
2
3
4
| SELECT str1,
LOWER(str1) AS low_str
FROM SampleStr
WHERE str1 IN ('ABC', 'aBC', 'abc', '山田');
|
执行结果：
1
2
3
4
5
6
| str1 | low_str
------+--------
abc | abc
山田 | 山田
ABC | abc
aBC | abc
|
既然存在小写转换函数，那么肯定也有大写转换函数，UPPER 就是大写转换函数。
3.4 REPLACE——字符串的替换
语法 7 REPLACE 函数
1
| REPLACE(对象字符串，替换前的字符串，替换后的字符串)
|
使用 REPLACE 函数，可以将字符串的一部分替换为其他的字符串（代码清单 10）。
代码清单 10 替换字符串的一部分
1
2
3
| SELECT str1, str2, str3,
REPLACE(str1, str2, str3) AS rep_str
FROM SampleStr;
|
执行结果：
1
2
3
4
5
6
7
8
9
10
11
12
13
| str1 | str2 | str3 | rep_str
-----------+------+------+---------
opx | rt | |
abc | def | |
山田 | 太郎 | 是我 | 山田
aaa | | |
| xyz | |
@!#$% | | |
ABC | | |
aBC | | |
abc太郎 | abc | ABC | ABC太郎
abcdefabc | abc | ABC | ABCdefABC
micmic | i | I | mIcmIc
|
3.5 SUBSTRING——字符串的截取
语法 8 SUBSTRING 函数（PostgreSQL/MySQL 专用语法）
1
| SUBSTRING（对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
|
使用 SUBSTRING 函数可以截取出字符串中的一部分字符串（代码清单 11）。截取的起始位置从字符串最左侧开始计算 。
代码清单 11 截取出字符串中第 3 位和第 4 位的字符
PostgreSQL MySQL
1
2
3
| SELECT str1,
SUBSTRING(str1 FROM 3 FOR 2) AS sub_str
FROM SampleStr;
|
执行结果：
1
2
3
4
5
6
7
8
9
10
11
12
13
| str1 | sub_str
-----------+--------
opx | x
abc | c
山田 |
aaa | a
|
@!#$% | #$
ABC | C
aBC | C
abc太郎 | c太
abcdefabc | cd
micmic | cm
|
虽然上述 SUBSTRING 函数的语法是标准 SQL 承认的正式语法，但是现在只有 PostgreSQL 和 MySQL 支持该语法。
3.6 UPPER——大写转换
语法 9 UPPER 函数
UPPER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为大写（代码清单 12）。
因此，该函数并不适用于英文字母以外的情况。此外，该函数并不影响原本就是大写的字符。
代码清单 12 将小写转换为大写
1
2
3
4
| SELECT str1,
UPPER(str1) AS up_str
FROM SampleStr
WHERE str1 IN ('ABC', 'aBC', 'abc', '山田');
|
执行结果：
1
2
3
4
5
6
| str1 | up_str
------+--------
abc | ABC
山田 | 山田
ABC | ABC
aBC | ABC
|
与之相对，进行小写转换的是 LOWER 函数。
四、日期函数
虽然 SQL 中有很多日期函数，但是其中大部分都依存于各自的 DBMS，因此无法统一说明 。本节将会介绍那些被标准 SQL 承认的可以应用于绝大多数 DBMS 的函数。
4.1 CURRENT_DATE——当前日期
语法 10 CURRENT_DATE 函数
CURRENT_DATE 函数能够返回 SQL 执行的日期，也就是该函数执行时的日期。由于没有参数，因此无需使用括号。
执行日期不同，CURRENT_DATE 函数的返回值也不同。如果在 2009 年 12 月 13 日执行该函数，会得到返回值“2009-12-13”。如果在 2010 年 1 月 1 日执行，就会得到返回值“2010-01-01”（代码清单 13）。
代码清单 13 获得当前日期
执行结果：
1
2
3
| date
------------
2016-05-25
|
该函数无法在 SQL Server 中执行。此外，Oracle 和 DB2 中的语法略有不同。
4.2 CURRENT_TIME——当前时间
语法 11 CURRENT_TIME 函数
CURRENT_TIME 函数能够取得 SQL 执行的时间，也就是该函数执行时的时间（代码清单 14）。由于该函数也没有参数，因此同样无需使用括号。
代码清单 14 取得当前时间
PostgreSQL MySQL
执行结果：
1
2
3
| timetz
-----------------
17:26:50.995+09
|
该函数同样无法在 SQL Server 中执行，在 Oracle 和 DB2 中的语法同样略有不同。
4.3 CURRENT_TIMESTAMP——当前日期和时间
语法 12 CURRENT_TIMESTAMP 函数
CURRENT_TIMESTAMP 函数具有 CURRENT_DATE + CURRENT_TIME 的功能。使用该函数可以同时得到当前的日期和时间，当然也可以从结果中截取日期或者时间。
代码清单 15 取得当前日期和时间
SQL Server PostgreSQL MySQL
1
| SELECT CURRENT_TIMESTAMP;
|
执行结果：
1
2
3
| now
---------------------------
2016-04-25 18:31:03.704+09
|
该函数可以在 SQL Server 等各个主要的 DBMS 中使用 。但是，与之前的 CURRENT_DATE 和 CURRENT_TIME 一样，在 Oracle 和 DB2 中该函数的语法略有不同。
语法 13 EXTRACT 函数
使用 EXTRACT 函数可以截取出日期数据中的一部分，例如“年”“月”，或者“小时”“秒”等（代码清单 16）。该函数的返回值并不是日期类型而是数值类型。
代码清单 16 截取日期元素
PostgreSQL MySQL
1
2
3
4
5
6
7
| SELECT CURRENT_TIMESTAMP,
EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year,
EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month,
EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day,
EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;
|
执行结果：
1
2
3
| now | year | month | day | hour | minute | second
---------------------------+------+-------+-----+------+--------+-------
2010-04-25 19:07:33.987+09 | 2010 | 4 | 25 | 19 | 7 | 33.987
|
需要注意的是 SQL Server 也无法使用该函数。
五、转换函数
最后将要给大家介绍一类比较特殊的函数——转换函数。虽说有些特殊，但是由于这些函数的语法和之前介绍的函数类似，数量也比较少，因此很容易记忆。
“转换”这个词的含义非常广泛，在 SQL 中主要有两层意思：一是数据类型的转换，简称为类型转换，在英语中称为 cast ；另一层意思是值的转换。
5.1 CAST——类型转换
语法 14 CAST 函数
1
| CAST（转换前的值 AS 想要转换的数据类型）
|
进行类型转换需要使用 CAST 函数。
之所以需要进行类型转换，是因为可能会插入与表中数据类型不匹配的数据，或者在进行运算时由于数据类型不一致发生了错误，又或者是进行自动类型转换会造成处理速度低下。
这些时候都需要事前进行数据类型转换（代码清单 17、代码清单 18）。
代码清单 17 将字符串类型转换为数值类型
SQL Server PostgreSQL
1
| SELECT CAST('0001' AS INTEGER) AS int_col;
|
MySQL
1
| SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
|
Oracle
1
2
| SELECT CAST('0001' AS INTEGER) AS int_col
FROM DUAL;
|
DB2
1
2
| SELECT CAST('0001' AS INTEGER) AS int_col
FROM SYSIBM.SYSDUMMY1;
|
执行结果：
1
2
3
| int_col
---------
1
|
代码清单 18 将字符串类型转换为日期类型
SQL Server PostgreSQL MySQL
1
| SELECT CAST('2009-12-14' AS DATE) AS date_col;
|
Oracle
1
2
| SELECT CAST('2009-12-14' AS DATE) AS date_col
FROM DUAL;
|
DB2
1
2
| SELECT CAST('2009-12-14' AS DATE) AS date_col
FROM SYSIBM.SYSDUMMY1;
|
执行结果：
1
2
3
| date_col
------------
2009-12-14
|
从上述结果可以看到，将字符串类型转换为整数类型时，前面的“000”消失了，能够切实感到发生了转换。
但是，将字符串转换为日期类型时，从结果上并不能看出数据发生了什么变化，理解起来也比较困难。
从这一点我们也可以看出，类型转换其实并不是为了方便用户使用而开发的功能，而是为了方便 DBMS 内部处理而开发的功能。
5.2 COALESCE——将 NULL 转换为其他值
语法 15 COALESCE 函数
1
| COALESCE(数据1，数据2，数据3……)
|
COALESCE 是 SQL 特有的函数。该函数会返回可变参数 中左侧开始第 1 个不是 NULL 的值。参数个数是可变的，因此可以根据需要无限增加。
其实转换函数的使用还是非常频繁的。在 SQL 语句中将 NULL 转换为其他值时就会用到转换函数（代码清单 19、代码清单 20）。
就像之前我们学习的那样，运算或者函数中含有 NULL 时，结果全都会变为 NULL。能够避免这种结果的函数就是 COALESCE。
代码清单 19 将 NULL 转换为其他值
SQL Server PostgreSQL MySQL
1
2
3
| SELECT COALESCE(NULL, 1) AS col_1,
COALESCE(NULL, 'test', NULL) AS col_2,
COALESCE(NULL, NULL, '2009-11-01') AS col_3;
|
Oracle
1
2
3
4
| SELECT COALESCE(NULL, 1) AS col_1,
COALESCE(NULL, 'test', NULL) AS col_2,
COALESCE(NULL, NULL, '2009-11-01') AS col_3
FROM DUAL;
|
DB2
1
2
3
4
| SELECT COALESCE(NULL, 1) AS col_1,
COALESCE(NULL, 'test', NULL) AS col_2,
COALESCE(NULL, NULL, '2009-11-01') AS col_3
FROM SYSIBM.SYSDUMMY1;
|
执行结果：
1
2
3
| col_1 | col_2 | col_3
-------+-------+-----------
1 | test | 2009-11-01
|
代码清单 20 使用 SampleStr 表中的列作为例子
1
2
| SELECT COALESCE(str2, 'NULL')
FROM SampleStr;
|
执行结果：
1
2
3
4
5
6
7
8
9
10
11
12
13
| coalesce
----------
rt
def
太郎
'NULL'
xyz
'NULL'
'NULL'
'NULL'
abc
abc
i
|
这样，即使包含 NULL 的列，也可以通过 COALESCE 函数转换为其他值之后再应用到函数或者运算当中，这样结果就不再是 NULL 了。
此外，多数 DBMS 中都提供了特有的 COALESCE 的简化版函数（如 Oracle 中的 NVL 等），但由于这些函数都依存于各自的 DBMS，因此还是推荐大家使用通用的 COALESCE 函数。
（完）