SQL 常用的函数：算术函数、字符串函数、日期函数和转换函数

不仅 SQL，对所有的编程语言来说，函数都起着至关重要的作用。函数就像是编程语言的“道具箱”，每种编程语言都准备了非常多的函数。

使用函数，我们可以实现计算、字符串操作、日期计算等各种各样的运算。

本文重点

根据用途，函数可以大致分为算术函数、字符串函数、日期函数、转换函聚合函数。

函数的种类很多，无需全都记住，只需要记住具有代表性的函数就可以了，其他的可以在使用时再进行查询。

一、函数的种类

前几篇和大家一起学习了 SQL 的语法结构等必须要遵守的规则。本文将会进行一点改变，来学习一些 SQL 自带的便利工具——函数。

我们在 SQL 如何对表进行聚合和分组查询并对查询结果进行排序中已经学习了函数的概念，这里再回顾一下。所谓函数，就是输入某一值得到相应输出结果的功能，输入值称为参数（parameter），输出值称为返回值。

函数大致可以分为以下几种。

算术函数（用来进行数值计算的函数）
字符串函数（用来进行字符串操作的函数）
日期函数（用来进行日期操作的函数）
转换函数（用来转换数据类型和值的函数）
聚合函数（用来进行数据聚合的函数）

我们已经在 SQL 如何对表进行聚合和分组查询并对查询结果进行排序中学习了聚合函数的相关内容，大家应该对函数有初步的了解了吧。

聚合函数基本上只包含 COUNT、SUM、AVG、MAX、MIN 这 5 种，而其他种类的函数总数则超过 200 种。

可能大家会觉得怎么会有那么多函数啊，但其实并不需要担心，虽然数量众多，但常用函数只有 30 ~ 50 个。不熟悉的函数大家可以查阅参考文档（词典）来了解 ^[1]。

本文我们将学习一些具有代表性的函数。大家并不需要一次全部记住，只需要知道有这样的函数就可以了，实际应用时可以查阅参考文档。

接下来，让我们来详细地看一看这些函数。

二、算术函数

算术函数是最基本的函数，其实之前我们已经学习过了，可能有些读者已经想起来了。没错，就是算术运算符和比较运算符介绍的加减乘除四则运算。

+（加法）
-（减法）
*（乘法）
/（除法）

由于这些算术运算符具有“根据输入值返回相应输出结果”的功能，因此它们是出色的算术函数。在此我们将会给大家介绍除此之外的具有代表性的函数。

为了学习算术函数，我们首先根据代码清单 1 创建一张示例用表（SampleMath）。

NUMERIC 是大多数 DBMS 都支持的一种数据类型，通过 NUMBERIC ( 全体位数, 小数位数 ) 的形式来指定数值的大小。

接下来，将会给大家介绍常用的算术函数——ROUND 函数，由于 PostgreSQL 中的 ROUND 函数只能使用 NUMERIC 类型的数据，因此我们在示例中也使用了该数据类型。

代码清单 1 创建 SampleMath 表

-- DDL ：创建表
CREATE TABLE SampleMath
(m  NUMERIC (10,3),
 n  INTEGER,
 p  INTEGER);

SQL Server PostgreSQL

-- 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;

特定的 SQL

不同的 DBMS 事务处理的语法也不尽相同。代码清单 1 中的 DML 语句在 MySQL 中执行时，需要将 ① 部分更改为“STARTTRANSACTION;”，在 Oracle 和 DB2 中执行时，无需用到 ① 的部分（请删除）。

详细内容请大家参考什么是 SQL 事务中的“创建事务”。

下面让我们来确认一下创建好的表中的内容，其中应该包含了 m、n、p 三列。

SELECT * FROM SampleMath;

执行结果：

    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(数值)

ABS 是计算绝对值的函数。绝对值（absolute value）不考虑数值的符号，表示一个数到原点的距离。

简单来讲，绝对值的计算方法就是：0 和正数的绝对值就是其本身，负数的绝对值就是去掉符号后的结果。

代码清单 2 计算数值的绝对值

SELECT m,
       ABS(m) AS abs_col
  FROM SampleMath;

执行结果：

执行结果

右侧的 abs_col 列就是通过 ABS 函数计算出的 m 列的绝对值。请大家注意，-180 的绝对值就是去掉符号后的结果 180。

通过上述结果我们可以发现，ABS 函数的参数为 NULL 时，结果也是 NULL。并非只有 ABS 函数如此，其实绝大多数函数对于 NULL 都返回 NULL ^[2]。

2.2 MOD——求余

语法 2 MOD 函数

MOD(被除数，除数)

MOD 是计算除法余数（求余）的函数，是 modulo 的缩写。例如，7/3 的余数是 1，因此 MOD(7, 3) 的结果也是 1（代码清单 3）。

因为小数计算中并没有余数的概念，所以只能对整数类型的列使用 MOD 函数。

代码清单 3 计算除法（n ÷ p）的余数

Oracle DB2 PostgreSQL MySQL

SELECT n, p,
       MOD(n, p) AS mod_col
  FROM SampleMath;

执行结果：

 n | p | mod_col
---+---+--------
 0 |   |
 0 |   |
   |   |
 7 | 3 |    1
 5 | 2 |    1
 4 |   |
   | 3 |
 1 |   |
 2 |   |
 1 |   |
   |   |

这里有一点需要大家注意：主流的 DBMS 都支持 MOD 函数，只有 SQL Server 不支持该函数。

特定的 SQL

SQL Server 使用特殊的运算符（函数）“%”来计算余数，使用如下的专用语法可以得到与代码清单 3 相同的结果。需要使用 SQL Server 的读者需要特别注意。

SQL Server
SELECT n, p,
      n % p AS mod_col
FROM SampleMath;

2.3 ROUND——四舍五入

语法 3 ROUND 函数

ROUND(对象数值，保留小数的位数)

ROUND 函数用来进行四舍五入操作。四舍五入在英语中称为 round。

如果指定四舍五入的位数为 1，那么就会对小数点第 2 位进行四舍五入处理。如果指定位数为 2，那么就会对第 3 位进行四舍五入处理（代码清单 4）。

代码清单 4 对 m 列的数值进行 n 列位数的四舍五入处理

SELECT m, n,
       ROUND(m, n) AS round_col
  FROM SampleMath;

执行结果：

    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 表

-- DDL ：创建表
CREATE TABLE SampleStr
(str1   VARCHAR(40),
 str2   VARCHAR(40),
 str3   VARCHAR(40));

SQL Server PostgreSQL

-- 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;

特定的 SQL

不同的 DBMS 事务处理的语法也不尽相同。代码清单 5 中的 DML 语句在 MySQL 中执行时，需要将 ① 部分更改为“START TRANSACTION;”。在 Oracle 和 DB2 中执行时，无需用到 ① 的部分（请删除）。

详细内容请大家参考什么是 SQL 事务中的“创建事务”。

下面让我们来确认一下创建好的表中的内容，其中应该包含了 str1、str2、str3 三列。

SELECT * FROM SampleStr;

执行结果：

   str1    | str2 | str3
-----------+------+-----
 opx       | rt   |
 abc       | def  |
 山田      | 太郎  | 是我
 aaa       |      |
           | xyz  |
 @!#$%     |      |
 ABC       |      |
 aBC       |      |
 abc太郎   | abc  | ABC
 abcdefabc | abc  | ABC
 micmic    | i    | I

3.1 ||——拼接

语法 4 || 函数

字符串1||字符串2

在实际业务中，我们经常会碰到 abc + de = abcde 这样希望将字符串进行拼接的情况。在 SQL 中，可以通过由两条并列的竖线变换而成的“||”函数来实现（代码清单 6）。

代码清单 6 拼接两个字符串（str1+str2）

Oracle DB2 PostgreSQL

SELECT str1, str2,
       str1 || str2 AS str_concat
  FROM SampleStr;

执行结果：

   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

SELECT str1, str2, str3,
       str1 || str2 || str3 AS str_concat
  FROM SampleStr
 WHERE str1 = '山田';

执行结果：

 str1 | str2 | str3 | str_concat
------+------+------+-----------
 山田 | 太郎 | 是我 | 山田太郎是我

这里也有一点需要大家注意，|| 函数在 SQL Server 和 MySQL 中无法使用。

特定的 SQL

SQL Server 使用“+”运算符（函数）来连接字符串。MySQL 使用 CONCAT 函数来完成字符串的拼接。

使用如下 SQL Server/MySQL 的专用语法能够得到与代码清单 7 相同的结果。另外，在 SQL Server 2012 及其之后的版本中也可以使用 CONCAT 函数。

SQL Server
SELECT str1, str2, str3,
     str1 + str2 + str3 AS str_concat
 FROM SampleStr;
MySQL SQL Server 2012 及之后
SELECT str1, str2, str3,
      CONCAT(str1, str2, str3) AS str_concat
 FROM SampleStr;

3.2 LENGTH——字符串长度

语法 5 LENGTH 函数

LENGTH(字符串)

想要知道字符串中包含多少个字符时，可以使用 LENGTH（长度）函数（代码清单 8）。

代码清单 8 计算字符串长度

Oracle DB2 PostgreSQL MySQL

SELECT str1,
       LENGTH(str1) AS len_str
  FROM SampleStr;

执行结果：

   str1    | len_str
-----------+--------
 opx       |    3
 abc       |    3
 山田      |    2
 aaa       |    3
           |
 @!#$%     |    5
 ABC       |    3
 aBC       |    3
 abc太郎   |    5
 abcdefabc |    9
 micmic    |    6

需要注意的是，该函数也无法在 SQL Server 中使用。

特定的 SQL

SQL Server 使用 LEN 函数来计算字符串的长度。使用如下 SQL Server 的专用语法能够得到与代码清单 8 相同的结果。

SQL Server
SELECT str1,
      LEN(str1) AS len_str
 FROM SampleStr;

我想大家应该逐渐明白“SQL 中有很多特定的用法”这句话的含义了吧。

专栏

对 1 个字符使用 LENGTH 函数有可能得到 2 字节以上的结果

LENGTH 函数中，还有一点需要大家特别注意，那就是该函数究竟以什么为单位来计算字符串的长度。这部分是初级以上阶段才会学习到的内容，在此先简单介绍一下。

可能有些读者已经有所了解，与半角英文字母占用 1 字节不同，汉字这样的全角字符会占用 2 个以上的字节（称为多字节字符）。

因此，使用 MySQL 中的 LENGTH 这样以字节为单位的函数进行计算时，“LENGTH(山田)”的返回结果是 4。同样是 LENGTH 函数，不同 DBMS 的执行结果也不尽相同。

3.3 LOWER——小写转换

语法 6 LOWER 函数

LOWER(字符串)

LOWER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写（代码清单 9）。

因此，该函数并不适用于英文字母以外的场合。此外，该函数并不影响原本就是小写的字符。

代码清单 9 大写转换为小写

SELECT str1,
       LOWER(str1) AS low_str
  FROM SampleStr
 WHERE str1 IN ('ABC', 'aBC', 'abc', '山田');

执行结果：

 str1 | low_str
------+--------
 abc  | abc
 山田 | 山田
 ABC  | abc
 aBC  | abc

既然存在小写转换函数，那么肯定也有大写转换函数，UPPER 就是大写转换函数。

3.4 REPLACE——字符串的替换

语法 7 REPLACE 函数

REPLACE(对象字符串，替换前的字符串，替换后的字符串)

使用 REPLACE 函数，可以将字符串的一部分替换为其他的字符串（代码清单 10）。

代码清单 10 替换字符串的一部分

SELECT str1, str2, str3,
       REPLACE(str1, str2, str3) AS rep_str
  FROM SampleStr;

执行结果：

   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 专用语法）

SUBSTRING（对象字符串 FROM 截取的起始位置 FOR 截取的字符数）

使用 SUBSTRING 函数可以截取出字符串中的一部分字符串（代码清单 11）。截取的起始位置从字符串最左侧开始计算 ^[3]。

代码清单 11 截取出字符串中第 3 位和第 4 位的字符

PostgreSQL MySQL

SELECT str1,
       SUBSTRING(str1 FROM 3 FOR 2) AS sub_str
  FROM SampleStr;

执行结果：

   str1    | sub_str
-----------+--------
 opx       | x
 abc       | c
 山田      |
 aaa       | a
           |
 @!#$%     | #$
 ABC       | C
 aBC       | C
 abc太郎   | c太
 abcdefabc | cd
 micmic    | cm

虽然上述 SUBSTRING 函数的语法是标准 SQL 承认的正式语法，但是现在只有 PostgreSQL 和 MySQL 支持该语法。

特定的 SQL

SQL Server 将语法 a 中的内容进行了简化（语法 b）。

语法 a SUBSTRING 函数（SQL Server 专用语法）
SUBSTRING(对象字符串，截取的起始位置，截取的字符数)
Oracle 和 DB2 将该语法进一步简化，得到了如下结果。

语法 b SUBSTR 函数（Oracle/DB2 专用语法）
SUBSTR(对象字符串，截取的起始位置，截取的字符数)
SQL 有这么多特定的语法，真是有些让人头疼啊。各 DBMS 中能够得到与代码清单 11 相同结果的专用语法如下所示。

SQL Server
SELECT str1,
      SUBSTRING(str1, 3, 2) AS sub_str
 FROM SampleStr;
Oracle DB2
SELECT str1,
      SUBSTR(str1, 3, 2) AS sub_str
 FROM SampleStr;

3.6 UPPER——大写转换

语法 9 UPPER 函数

UPPER(字符串)

UPPER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为大写（代码清单 12）。

因此，该函数并不适用于英文字母以外的情况。此外，该函数并不影响原本就是大写的字符。

代码清单 12 将小写转换为大写

SELECT str1,
       UPPER(str1) AS up_str
  FROM SampleStr
 WHERE str1 IN ('ABC', 'aBC', 'abc', '山田');

执行结果：

 str1 | up_str
------+--------
 abc  | ABC
 山田 | 山田
 ABC  | ABC
 aBC  | ABC

与之相对，进行小写转换的是 LOWER 函数。

四、日期函数

虽然 SQL 中有很多日期函数，但是其中大部分都依存于各自的 DBMS，因此无法统一说明 ^[4]。本节将会介绍那些被标准 SQL 承认的可以应用于绝大多数 DBMS 的函数。

4.1 CURRENT_DATE——当前日期

语法 10 CURRENT_DATE 函数

CURRENT_DATE

CURRENT_DATE 函数能够返回 SQL 执行的日期，也就是该函数执行时的日期。由于没有参数，因此无需使用括号。

执行日期不同，CURRENT_DATE 函数的返回值也不同。如果在 2009 年 12 月 13 日执行该函数，会得到返回值“2009-12-13”。如果在 2010 年 1 月 1 日执行，就会得到返回值“2010-01-01”（代码清单 13）。

代码清单 13 获得当前日期

SELECT CURRENT_DATE;

执行结果：

    date
------------
 2016-05-25

该函数无法在 SQL Server 中执行。此外，Oracle 和 DB2 中的语法略有不同。

特定的 SQL

SQL Server 使用如下的 CURRENT_TIMESTAMP（后述）函数来获得当前日期。

SQL Server
-- 使用CAST（后述）函数将CURRENT_TIMESTAMP转换为日期类型
SELECT CAST(CURRENT_TIMESTAMP AS DATE) AS CUR_DATE;
执行结果：
  CUR_DATE
  ----------
  2010-05-25
在 Oracle 中使用该函数时，需要在 FROM 子句中指定临时表（DUAL）。

而在 DB2 中使用时，需要在 CRUUENT 和 DATE 之间添加半角空格，并且还需要指定临时表 SYSIBM.SYSDUMMY1（相当于 Oracle 中的 DUAL）。

这些容易混淆的地方请大家多加注意。

Oracle
SELECT CURRENT_DATE
FROM dual;
DB2
SELECT CURRENT DATE
FROM SYSIBM.SYSDUMMY1;

4.2 CURRENT_TIME——当前时间

语法 11 CURRENT_TIME 函数

CURRENT_TIME

CURRENT_TIME 函数能够取得 SQL 执行的时间，也就是该函数执行时的时间（代码清单 14）。由于该函数也没有参数，因此同样无需使用括号。

代码清单 14 取得当前时间

PostgreSQL MySQL

SELECT CURRENT_TIME;

执行结果：

    timetz
-----------------
17:26:50.995+09

该函数同样无法在 SQL Server 中执行，在 Oracle 和 DB2 中的语法同样略有不同。

特定的 SQL

SQL Server 使用如下的 CURRENT_TIMESTAMP 函数（后述）来获得当前日期。
-- 使用CAST函数（后述）将CURRENT_TIMESTAMP转换为时间类型
SELECT CAST(CURRENT_TIMESTAMP AS TIME) AS CUR_TIME;
执行结果：
  CUR_TIME
  ----------------
  21:33:59.3400000
在 Oracle 和 DB2 中使用时的语法如下所示。需要注意的地方和 CURRENT_DATE 函数相同。在 Oracle 中使用时所得到的结果还包含日期。

Oracle
-- 指定临时表（DUAL）
SELECT CURRENT_TIMESTAMP
 FROM dual;
DB2
/* CURRENT和TIME之间使用了半角空格，指定临时表SYSIBM.SYSDUMMY1 */
SELECT CURRENT TIME
 FROM SYSIBM.SYSDUMMY1;

4.3 CURRENT_TIMESTAMP——当前日期和时间

语法 12 CURRENT_TIMESTAMP 函数

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP 函数具有 CURRENT_DATE + CURRENT_TIME 的功能。使用该函数可以同时得到当前的日期和时间，当然也可以从结果中截取日期或者时间。

代码清单 15 取得当前日期和时间

SQL Server PostgreSQL MySQL

SELECT CURRENT_TIMESTAMP;

执行结果：

           now
---------------------------
2016-04-25 18:31:03.704+09

该函数可以在 SQL Server 等各个主要的 DBMS 中使用 ^[5]。但是，与之前的 CURRENT_DATE 和 CURRENT_TIME 一样，在 Oracle 和 DB2 中该函数的语法略有不同。

特定的 SQL

Oracle 和 DB2 使用如下写法可以得到与代码清单 15 相同的结果。其中需要注意的地方与 CURRENT_DATE 时完全相同。

Oracle
-- 指定临时表（DUAL）
SELECT CURRENT_TIMESTAMP
 FROM dual;
DB2
/* CURRENT和TIME之间使用了半角空格，指定临时表SYSIBM.SYSDUMMY1 */
SELECT CURRENT TIMESTAMP
 FROM SYSIBM.SYSDUMMY1;

4.4 EXTRACT——截取日期元素

语法 13 EXTRACT 函数

EXTRACT(日期元素 FROM 日期)

使用 EXTRACT 函数可以截取出日期数据中的一部分，例如“年”“月”，或者“小时”“秒”等（代码清单 16）。该函数的返回值并不是日期类型而是数值类型。

代码清单 16 截取日期元素

PostgreSQL MySQL

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;

执行结果：

           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

SQL Server 使用如下的 DATEPART 函数会得到与代码清单 16 相同的结果。

SQL Server

SELECT CURRENT_TIMESTAMP,
      DATEPART(YEAR   , CURRENT_TIMESTAMP) AS year,
      DATEPART(MONTH  , CURRENT_TIMESTAMP) AS month,
      DATEPART(DAY    , CURRENT_TIMESTAMP) AS day,
      DATEPART(HOUR   , CURRENT_TIMESTAMP) AS hour,
      DATEPART(MINUTE , CURRENT_TIMESTAMP) AS minute,
      DATEPART(SECOND , CURRENT_TIMESTAMP) AS second;

Oracle 和 DB2 想要得到相同结果的话，需要进行如下改变。注意事项与 CURRENT_DATE 时完全相同。

Oracle

-- 在FROM子句中指定临时表（DUAL）
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
FROM DUAL;

DB2

/* CURRENT和TIME之间使用了半角空格，指定临时表SYSIBM.SYSDUMMY1 */
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
FROM SYSIBM.SYSDUMMY1;

五、转换函数

最后将要给大家介绍一类比较特殊的函数——转换函数。虽说有些特殊，但是由于这些函数的语法和之前介绍的函数类似，数量也比较少，因此很容易记忆。

“转换”这个词的含义非常广泛，在 SQL 中主要有两层意思：一是数据类型的转换，简称为类型转换，在英语中称为 cast ^[6] ；另一层意思是值的转换。

5.1 CAST——类型转换

语法 14 CAST 函数

CAST（转换前的值 AS 想要转换的数据类型）

进行类型转换需要使用 CAST 函数。

之所以需要进行类型转换，是因为可能会插入与表中数据类型不匹配的数据，或者在进行运算时由于数据类型不一致发生了错误，又或者是进行自动类型转换会造成处理速度低下。

这些时候都需要事前进行数据类型转换（代码清单 17、代码清单 18）。

代码清单 17 将字符串类型转换为数值类型

SQL Server PostgreSQL

SELECT CAST('0001' AS INTEGER) AS int_col;

MySQL

SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;

Oracle

SELECT CAST('0001' AS INTEGER) AS int_col
  FROM DUAL;

DB2

SELECT CAST('0001' AS INTEGER) AS int_col
  FROM SYSIBM.SYSDUMMY1;

执行结果：

int_col
---------
      1

代码清单 18 将字符串类型转换为日期类型

SQL Server PostgreSQL MySQL

SELECT CAST('2009-12-14' AS DATE) AS date_col;

Oracle

SELECT CAST('2009-12-14' AS DATE) AS date_col
  FROM DUAL;

DB2

SELECT CAST('2009-12-14' AS DATE) AS date_col
  FROM SYSIBM.SYSDUMMY1;

执行结果：

 date_col
------------
2009-12-14

从上述结果可以看到，将字符串类型转换为整数类型时，前面的“000”消失了，能够切实感到发生了转换。

但是，将字符串转换为日期类型时，从结果上并不能看出数据发生了什么变化，理解起来也比较困难。

从这一点我们也可以看出，类型转换其实并不是为了方便用户使用而开发的功能，而是为了方便 DBMS 内部处理而开发的功能。

5.2 COALESCE——将 NULL 转换为其他值

语法 15 COALESCE 函数

COALESCE(数据1，数据2，数据3……)

COALESCE 是 SQL 特有的函数。该函数会返回可变参数 ^[7] 中左侧开始第 1 个不是 NULL 的值。参数个数是可变的，因此可以根据需要无限增加。

其实转换函数的使用还是非常频繁的。在 SQL 语句中将 NULL 转换为其他值时就会用到转换函数（代码清单 19、代码清单 20）。

就像之前我们学习的那样，运算或者函数中含有 NULL 时，结果全都会变为 NULL。能够避免这种结果的函数就是 COALESCE。

代码清单 19 将 NULL 转换为其他值

SQL Server PostgreSQL MySQL

SELECT COALESCE(NULL, 1)                  AS col_1,
       COALESCE(NULL, 'test', NULL)       AS col_2,
       COALESCE(NULL, NULL, '2009-11-01') AS col_3;

Oracle

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

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;

执行结果：

 col_1 | col_2 |    col_3
-------+-------+-----------
     1 |  test | 2009-11-01

代码清单 20 使用 SampleStr 表中的列作为例子

SELECT COALESCE(str2, 'NULL')
  FROM SampleStr;

执行结果：

 coalesce
----------
 rt
 def
 太郎
 'NULL'
 xyz
 'NULL'
 'NULL'
 'NULL'
 abc
 abc
 i

这样，即使包含 NULL 的列，也可以通过 COALESCE 函数转换为其他值之后再应用到函数或者运算当中，这样结果就不再是 NULL 了。

此外，多数 DBMS 中都提供了特有的 COALESCE 的简化版函数（如 Oracle 中的 NVL 等），但由于这些函数都依存于各自的 DBMS，因此还是推荐大家使用通用的 COALESCE 函数。

原文链接：https://www.developerastrid.com/sql/sql-commonly-used-functions/

（完）

参考文档是 DBMS 手册的一部分。大家也可以从介绍各种函数的书籍以及 Web 网站上获取相关信息。 ↩︎
但是转换函数中的 COALESCE 函数除外。 ↩︎
需要大家注意的是，该函数也存在和 LENGTH 函数同样的多字节字符的问题。详细内容请大家参考专栏“对 1 个字符使用 LENGTH 函数有可能得到 2 字节以上的结果”。 ↩︎
如果想要了解日期函数的详细内容，目前只能查阅各个 DBMS 的手册。 ↩︎
之前我们已经介绍过，在 SQL Server 中无法使用 CURRENT_DATE 和 CURRENT_TIME 函数。可能是因为在 SQL Server 中，CURRENT_TIMESTAMP 已经涵盖了这两者的功能吧。 ↩︎
类型转换在一般的编程语言中也会使用，因此并不是 SQL 特有的功能。 ↩︎
参数的个数并不固定，可以自由设定个数的参数。 ↩︎

posted @ 2022-06-10 16:50 Vin° 阅读(1145) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部