从 01 开始
2024-01-21
目录

函数

# 160.函数

不仅 SQL，对所有的编程语言来说，函数都起着至关重要的作用。函数就像是编程语言的“道具箱”，每种编程语言都准备了非常多的函数。使用函数，我们可以实现计算、字符串操作、日期计算等各种各样的运算。

‍

# 函数的种类

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

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

每种函数可能都有很多，这里仅仅介绍部分常见常用的，遇到不熟悉的参考文档即可，不用全部记住。

‍

‍

‍

# 算术函数

算术函数 是最基本的函数，其实之前我们已经学习过了一些了，也就是加减乘除。除了这几个，还有其他的，例如：

  • ABS函数：绝对值
  • MOD 函数：求余
  • ROUND函数：四舍五入

‍

为此，我们先创建一个用来测试这些函数的表：

CREATE TABLE SampleMath
(m  NUMERIC (10,3),
 n  INTEGER,
 p  INTEGER);


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);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

‍

# ABS函数：绝对值

语法：ABS(数据)。 演示：

‍

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

-- 结果：
+----------+---------+
| m        | abs_col |
+----------+---------+
|  500.000 | 500.000 |
| -180.000 | 180.000 |
-- 省略其他结果........
1
2
3
4
5
6
7
8
9
10

# MOD 函数：求余

语法：MOD(被除数，除数)。演示：

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

--结果：
+------+------+---------+
| n    | p    | mod_col |
+------+------+---------+
|    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 |
+------+------+---------+
11 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

‍

‍

特别注意：SQL Server 不支持该函数，而是改为使用“% ”来计算余数

‍

# 取整函数

主要有3个：

  • ROUND函数：四舍五入。在英文中是有大约，环绕，附近的意思。
  • CEILING函数：向上取整。英文中，是天花板的意思，有上限的意思。
  • FLOOR函数：向下取整。在英文中，是地面，地板的意思，有下面的意思

上述3个取整函数，是个通用函数，不仅在SQL中，在各种编程语言中都有相应的功能。

‍

# CEILING函数：向上取整

此函数用于查找大于或等于指定数字的最小整数值。

用法：CEILING(number)

参数的要求：

  • 必须是单个参数
  • 参数整数或浮点数据类型的数值

举例：

select CEILING(0.8);

-- 结果：
+--------------+
| CEILING(0.8) |
+--------------+
|            1 |
+--------------+
1
2
3
4
5
6
7
8

‍

‍

# FLOOR函数：向下取整

floor函数在数学中又称高斯函数。用法和CEILING类似，举例：

select floor(0.8);

-- 结果：
+------------+
| floor(0.8) |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9

‍

‍

‍

# ROUND函数：四舍五入

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

如果指定四舍五入的位数为 1 ，那么就会对小数点第 2 位进行四舍五入处理。如果指定位数为 2 ，那么就会对第 3 位进行四舍五入处理。演示：

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

--结果：
+----------+------+-----------+
| m        | n    | round_col |
+----------+------+-----------+
|  500.000 |    0 |   500.000 |
| -180.000 |    0 |  -180.000 |
|     NULL | NULL |      NULL |
|     NULL |    7 |      NULL |
|     NULL |    5 |      NULL |
|     NULL |    4 |      NULL |
|    8.000 | NULL |      NULL |
|    2.270 |    1 |     2.300 |
|    5.555 |    2 |     5.560 |
|     NULL |    1 |      NULL |
|    8.760 | NULL |      NULL |
+----------+------+-----------+
11 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

‍

‍

‍

# 字符串函数

在日常生活中，我们经常会像使用数字那样，对字符串进行替换、截取、大小写转换等操作，因此 SQL 也为我们提供了很多操作字符串的功能。

为了学习字符串函数，我们再来创建一张表（SampleStr ）：

CREATE TABLE SampleStr
(str1   VARCHAR(40),
 str2   VARCHAR(40),
 str3   VARCHAR(40)
);


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');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

‍

# 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      |
+-----------+------+--------------+
11 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

‍

注意：在Oracle DB2 PostgreSQL中没有该函数，而是使用“||”作为拼接符，SQL Server 则使用“+ ”运算符来连接字符串。

‍

# LENGTH函数：字符串长度

语法：LENGTH(字符串)。举例：

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


-- 结果：
+-----------+---------+
| str1      | len_str |
+-----------+---------+
| opx       |       3 |
| abc       |       3 |
| 山田      |       6 |
| aaa       |       3 |
| NULL      |    NULL |
| @!#$%     |       5 |
| ABC       |       3 |
| aBC       |       3 |
| abc太郎   |       9 |
| abcdefabc |       9 |
| micmic    |       6 |
+-----------+---------+
11 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

在SQL Server 使用 LEN 函数来计算字符串的长度。

‍

注意：在MySQL中length函数是按字节来计算字符串长度的，使用char_length可以按字符来计算，例如一个汉字占2 ~ 3个字节，则用length会取值2，使用char_length则取值1：

mysql> select length('你');
+--------------+
| length('你') |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

mysql> select char_length('你');
+-------------------+
| char_length('你') |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

如果不懂字节或字符的关系，可以阅读拙作：简单聊聊字符编码 (opens new window)，读完这几篇博客相信就能理解了（或者暂时跳过该知识点，等学完了SQL再来学习）

‍

‍

‍

# lower函数：小写转换

语法：LOWER(字符串)，举例：

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

--结果：
+------+---------+
| str1 | low_str |
+------+---------+
| abc  | abc     |
| 山田 | 山田    |
| ABC  | abc     |
| aBC  | abc     |
+------+---------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

‍

# upper函数：大写转换

语法：UPPER(字符串)。举例

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

-- 结果：
+------+--------+
| str1 | up_str |
+------+--------+
| abc  | ABC    |
| 山田 | 山田   |
| ABC  | ABC    |
| aBC  | ABC    |
+------+--------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

‍

‍

注意，大小写转换是针对字母的，其他的字符（例如中文）不起作用。

‍

# REPLACE函数：字符串的替换

语法：REPLACE(对象字符串，替换前的字符串，替换后的字符串)，例如：

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

-- 结果：
+-----------+------+------+-----------+
| str1      | str2 | str3 | rep_str   |
+-----------+------+------+-----------+
| 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    |
+-----------+------+------+-----------+
11 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

‍

# SUBSTRING函数：字符串的截取

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

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

-- 结果：
+-----------+---------+
| str1      | sub_str |
+-----------+---------+
| opx       | x       |
| abc       | c       |
| 山田      |         |
| aaa       | a       |
| NULL      | NULL    |
| @!#$%     | #$      |
| ABC       | C       |
| aBC       | C       |
| abc太郎   | c太     |
| abcdefabc | cd      |
| micmic    | cm      |
+-----------+---------+
11 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

‍

注意其他DBMS的写法：

  • SQL Server的语法：SUBSTRING(对象字符串，截取的起始位置，截取的字符数)
  • Oracle 和 DB2的语法：SUBSTR(对象字符串，截取的起始位置，截取的字符数)

‍

‍

# 日期函数

每个DBMS有很多的日期函数，无法统一说明，具体的请参考手册；这里介绍一些在MySQL中常用的，其他DBMS也基本都有类似功能的

‍

# CURRENT_DATE：当前日期

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

 SELECT CURRENT_DATE;
+--------------+
| CURRENT_DATE |
+--------------+
| 2023-11-14   |
+--------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

‍

‍

# CURRENT_TIME：当前时间

CURRENT_TIME 函数 能够取得 SQL 执行的时间，也就是该函数执行时的时间。由于该函数也没有参数，因此同样无需使用括号。举例

 SELECT CURRENT_TIME;
+--------------+
| CURRENT_TIME |
+--------------+
| 21:29:46     |
+--------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

‍

‍

# CURRENT_TIMESTAMP：当前日期和时间

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

‍

SELECT CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP   |
+---------------------+
| 2023-11-14 21:30:31 |
+---------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

‍

‍

# EXTRACT：截取日期元素

除了用substring，也可以用extract来截取日期数据，如“年”“月”，或者“小时”“秒”等。该函数的返回值并不是日期类型而是数值类型。举例：

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;
+---------------------+------+-------+------+------+--------+--------+
| CURRENT_TIMESTAMP   | year | month | day  | hour | minute | second |
+---------------------+------+-------+------+------+--------+--------+
| 2023-11-14 21:32:05 | 2023 |    11 |   14 |   21 |     32 |      5 |
+---------------------+------+-------+------+------+--------+--------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

‍

‍

‍

‍

# 转换函数

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

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

‍

‍

# CAST：类型转换

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

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

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

举例：将字符串类型转换为数值类型

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

-- 结果：
+---------+
| int_col |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9

‍

‍

将字符串类型转换为日期类型：

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

--结果：
+------------+
| date_col   |
+------------+
| 2009-12-14 |
+------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10

‍

从上述结果可以看到，将字符串类型转换为整数类型时，前面的“000 ”消失了，能够切实感到发生了转换。但是，将字符串转换为日期类型时，从结果上并不能看出数据发生了什么变化，理解起来也比较困难。从这一点我们也可以看出，类型转换其实并不是为了方便用户使用而开发的功能，而是为了方便 DBMS 内部处理而开发的功能。

‍

‍

# COALESCE：将 NULL 转换为其他值

语法：COALESCE(数据1，数据2，数据3……)

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

可变参数：参数的个数并不固定，可以自由设定个数的参数。

‍

‍

其实转换函数的使用还是非常频繁的。之前说过，运算或者函数中含有 NULL 时，结果全都会变为 NULL 。使用COALESCE能够避免这种结果。举例：

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

-- 结果：
+-------+-------+------------+
| col_1 | col_2 | col_3      |
+-------+-------+------------+
|     1 | test  | 2009-11-01 |
+-------+-------+------------+
1 row in set (0.00 sec
1
2
3
4
5
6
7
8
9
10
11

‍

可以主动给参数中加一个常数，这样如果遇到了null，就会使用该常数。这就相当于告诉SQL：如果遇到了null，则使用我传入的常数。例如：

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

-- 结果：
+------------------------+
| COALESCE(str2, 'NULL') |
+------------------------+
| rt                     |
| def                    |
| 太郎                   |
| NULL                   |
| xyz                    |
| NULL                   |
| NULL                   |
| NULL                   |
| abc                    |
| abc                    |
| i                      |
+------------------------+
11 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

‍

‍

‍

‍

（完）

上次更新: 2024/1/23 16:20:41
子查询
谓词

← 谓词→

Theme by Vdoing | Copyright © 2022-2024 | 粤 ICP 备 2022067627 号-1 | 粤公网安备 44011302003646 号 | 点击查看十年之约