SQL操作指南五（函数）

函数

• 函数的种类
  函数大致分为以下几类：
  ①算术函数（用以进行数值计算）
  ②字符串函数（用以进行字符串操作）
  ③日期函数（用以进行日期操作）
  ④转换函数（用以转换数据类型和值）
  ⑤聚合函数（用以进行数据聚合）

• 算术函数
  算术函数是最基本的函数主要是加减乘除四则运算。
  为了方便演示算术函数，我们先创建一张名称为SampleMath的表，如下（m NUMERIC(10,3)n INTEGER,p INTEGER)：

+-------+------+------+
| m     | n    | p    |
+-------+------+------+
| 500   |    0 | NULL |
| -180  |    0 | NULL |
| NULL  | NULL | NULL |
| NULL  |    7 |    3 |
| NULL  |    5 |    2 |
| NULL  |    4 | NULL |
| 8     | NULL |    3 |
| 2.27  |    1 | NULL |
| 5.555 |    2 | NULL |
| NULL  |    1 | NULL |
| 8.76  | NULL | NULL |
+-------+------+------+

ABS—绝对值
语法：ABS（数值）
示例：

SELECT m,ABS(m) AS abs_exp
FROM SampleMath;
+-------+-------+
| m     | abs_m |
+-------+-------+
| 500   | 500   |
| -180  | 180   |
| NULL  | NULL  |
| NULL  | NULL  |
| NULL  | NULL  |
| NULL  | NULL  |
| 8     | 8     |
| 2.27  | 2.27  |
| 5.555 | 5.555 |
| NULL  | NULL  |
| 8.76  | 8.76  |
+-------+-------+

MOD—求余
语法：MOD(被除数，除数)
示例：

SELECT n,p,MOD(n,p) AS mod_exp
FROM SampleMath;
+------+------+--------+
| n    | p    | mod_np |
+------+------+--------+
|    0 | NULL | NULL   |
|    0 | NULL | NULL   |
| NULL | NULL | NULL   |
|    7 |    3 |      1 |
|    5 |    2 |      1 |
|    4 | NULL | NULL   |
| NULL |    3 | NULL   |
|    1 | NULL | NULL   |
|    2 | NULL | NULL   |
|    1 | NULL | NULL   |
| NULL | NULL | NULL   |
+------+------+--------+

ROUND—四舍五入
语法：ROUND(对象数值，保留小数的位数）
示例：

SELECT m,n,ROUND(m,n) AS round_exp
FROM SampleMath;
+-------+------+-----------+
| m     | n    | round_exp |
+-------+------+-----------+
| 500   |    0 | 500.000   |
| -180  |    0 | -180.000  |
| NULL  | NULL | NULL      |
| NULL  |    7 | NULL      |
| NULL  |    5 | NULL      |
| NULL  |    4 | NULL      |
| 8     | NULL | NULL      |
| 2.27  |    1 | 2.300     |
| 5.555 |    2 | 5.560     |
| NULL  |    1 | NULL      |
| 8.76  | NULL | NULL      |
+-------+------+-----------+

• 字符串函数
  为了方便演示字符串函数，我们创建一张名为SampleStr的表，如下（str1 VARCHAR(40),str2 VARCHAR(40),str3 VARCHAR(40)):

+-----------+--------+------+
| str1      | str2   | str3 |
+-----------+--------+------+
| opx       | rt     | NULL |
| abc       | def    | NULL |
| 雨伞      | 手电筒 | 工具 |
| aaa       | NULL   | NULL |
| NULL      | xyz    | NULL |
| #$@*!     | NULL   | NULL |
| ABC       | NULL   | NULL |
| aBC       | NULL   | NULL |
| abc散记   | abc    | ABC  |
| abcdefabc | abc    | ABC  |
| micmic    | i      | I    |
+-----------+--------+------+

||—拼接
语法：字符串1||字符串2或者concat(字符串1，字符串2）
示例：

 SELECT str1,str2,concat(str1,str2) AS str_concat
 FROM SampleStr;
+-----------+--------+--------------+
| str1      | str2   | str_concat   |
+-----------+--------+--------------+
| opx       | rt     | opxrt        |
| abc       | def    | abcdef       |
| 雨伞      | 手电筒 | 雨伞手电筒   |
| aaa       | NULL   | NULL         |
| NULL      | xyz    | NULL         |
| #$@*!     | NULL   | NULL         |
| ABC       | NULL   | NULL         |
| aBC       | NULL   | NULL         |
| abc散记   | abc    | abc散记abc   |
| abcdefabc | abc    | abcdefabcabc |
| micmic    | i      | micmici      |
+-----------+--------+--------------+

需要注意的是：||的方法在SQL Server和MySQL中无法使用。

LENGTH—字符串长度
语法：LENGTH(字符串)
示例：

SELECT str1,LENGTH(str1) AS str_length
FROM SampleStr;
+-----------+------------+
| str1      | str_length |
+-----------+------------+
| opx       |          3 |
| abc       |          3 |
| 雨伞      |          6 |
| aaa       |          3 |
| NULL      | NULL       |
| #$@*!     |          5 |
| ABC       |          3 |
| aBC       |          3 |
| abc散记   |          9 |
| abcdefabc |          9 |
| micmic    |          6 |
+-----------+------------+

该函数无法在SQL Server中使用。

LOWER—小写转换
语法：LOWER(字符串)
示例：

SELECT str1,LOWER(str1)
FROM SampleStr;
+-----------+-------------+
| str1      | LOWER(str1) |
+-----------+-------------+
| opx       | opx         |
| abc       | abc         |
| 雨伞      | 雨伞        |
| aaa       | aaa         |
| NULL      | NULL        |
| #$@*!     | #$@*!       |
| ABC       | abc         |
| aBC       | abc         |
| abc散记   | abc散记     |
| abcdefabc | abcdefabc   |
| micmic    | micmic      |
+-----------+-------------+

UPPER—大写转换
语法：UPPER(字符串)
示例：

 SELECT str1,UPPER(str1) AS str_up
 FROM SampleStr;
+-----------+-----------+
| str1      | str_up    |
+-----------+-----------+
| opx       | OPX       |
| abc       | ABC       |
| 雨伞      | 雨伞      |
| aaa       | AAA       |
| NULL      | NULL      |
| #$@*!     | #$@*!     |
| ABC       | ABC       |
| aBC       | ABC       |
| abc散记   | ABC散记   |
| abcdefabc | ABCDEFABC |
| micmic    | MICMIC    |
+-----------+-----------+

REPLACE—字符串替换
语法：REPLACE(对象字符串，替换前的字符串，替换后的字符串)
使用REPLACE函数可以将对象字符串中拥有替换前字符串的片段替换成替换后的字符串的功能。
示例：

SELECT str1,str2,str3,REPLACE(str1,str2,str3) AS str_rep
FROM SampleStr;
+-----------+--------+------+-----------+
| str1      | str2   | str3 | str_rep   |
+-----------+--------+------+-----------+
| opx       | rt     | NULL | NULL      |
| abc       | def    | NULL | NULL      |
| 雨伞      | 手电筒 | 工具 | 雨伞      |
| aaa       | NULL   | NULL | NULL      |
| NULL      | xyz    | NULL | NULL      |
| #$@*!     | NULL   | NULL | NULL      |
| ABC       | NULL   | NULL | NULL      |
| aBC       | NULL   | NULL | NULL      |
| abc散记   | abc    | ABC  | ABC散记   |
| abcdefabc | abc    | ABC  | ABCdefABC |
| micmic    | i      | I    | mIcmIc    |
+-----------+--------+------+-----------+

SUBSTRING—字符串的截取
语法：SUBSTRING(对象字符串 FROM 截取的其实位置 FOR 截取的字符数)
使用SUBSTRING函数可以截取字符中的一部分字符串。截取的起始位置从字符串最左侧开始计算。
示例：

SELECT str1,SUBSTRING(str1 FROM 3 FOR 2) AS str_sub
FROM SampleStr;
+-----------+---------+
| str1      | str_sub |
+-----------+---------+
| opx       | x       |
| abc       | c       |
| 雨伞      |         |
| aaa       | a       |
| NULL      | NULL    |
| #$@*!     | @*      |
| ABC       | C       |
| aBC       | C       |
| abc散记   | c散     |
| abcdefabc | cd      |
| micmic    | cm      |
+-----------+---------+

只有PostgreSQL和MySQL支持以上用法。

• 日期函数

CURRENT_DATE函数—当前日期
语法：CURRENT_DATE
CURRENT_DATE函数能够返回SQL执行的日期，也就是该函数执行的日期。由于没有参数，所以无序使用括号。
示例：

SELECT CURRENT_DATE;
+--------------+
| CURRENT_DATE |
+--------------+
| 2018-11-10   |
+--------------+

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

CURRENT_TIME—当前时间
语法：CURRENT_TIME
CURRENT_TIME函数能够返回SQL执行的时间，也就是该函数执行的时间。由于没有参数，所以无序使用括号。
示例：

SELECT CURRENT_TIME;
+--------------+
| CURRENT_TIME |
+--------------+
| 16:00:56     |
+--------------+

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

CURRENT_TIMESTAMP—当前日期和时间
语法：CURRENT_TIMESTAMP
CURRENT_TIMESTAMP同时具有CURRENT_DATE和CURRENT_TIME的功能，使用该函数可以同时得到当前的日期和时间，当然也可以从结果中截取日期和时间。
示例：

SELECT CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP   |
+---------------------+
| 2018-11-10 16:05:19 |
+---------------------+

EXTRACT——截取日期元素
语法：EXTRACT(日期元素 FROM 日期)
使用EXTRACT函数可以截取日期数据中的一部分。该函数的返回值并不是日期类型而是数据类型。
示例：

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;
+---------------------+------+-------+-----+------+--------+--------+
| CURRENT_TIMESTAMP   | year | month | day | hour | minute | second |
+---------------------+------+-------+-----+------+--------+--------+
| 2018-11-10 16:12:54 | 2018 |    11 |  10 |   16 |     12 |     54 |
+---------------------+------+-------+-----+------+--------+--------+

SQL Server无法使用该函数。

• 转换函数
  转换函数在SQL中有两层意思，一是数据类型的转换，简称为”类型转换“，在英语中称为cast。另一层意思是值的转换。

CAST—类型转换
语法：CAST(转换前的值 AS 想要转换的数据类型)
示例（此处用MySQL举例）：

--将字符串转换成数值类型
SELECT CAST('0001' AS SIGNED INTEGER) AS int_exp;
+---------+
| int_exp |
+---------+
|       1 |
+---------+

SELECT CAST('2018-9-1' AS DATE) AS date_exp;
+------------+
| date_exp   |
+------------+
| 2018-09-01 |
+------------+

COALESCE—将NULL转换为其他值
语法：COALESCE(数据1，数据2，数据3······)
COALESCE是SQL特有的函数。该函数会返回可变参数中左侧开始第1个不是NULL的值。参数的个数是可变的，因此可以根据需求无限增加。
示例：

SELECT COALESCE(NULL,1) AS exp_1,
               COALESCE(NULL,'teat',NULL) AS exp_2,
               COALESCE(NULL,NULL,'2018-11-10') AS exp_3;
+-------+-------+------------+
| exp_1 | exp_2 | exp_3      |
+-------+-------+------------+
|     1 | teat  | 2018-11-10 |
+-------+-------+------------+

使用SampleStr表中的数据示例：

SELECT COALESCE(str2,'NULL')
FROM SampleStr;
+-----------------------+
| COALESCE(str2,'NULL') |
+-----------------------+
| rt                    |
| def                   |
| 手电筒                |
| NULL                  |
| xyz                   |
| NULL                  |
| NULL                  |
| NULL                  |
| abc                   |
| abc                   |
| i                     |
+-----------------------+