MySQL 常用内置函数

字符串函数

1、 LOWER(str) 将输入的字符串全部转换为小写：

SELECT LOWER('MySQL');

输出结果：

+----------------+
| LOWER('MySQL') |
+----------------+
| mysql          |
+----------------+
1 row in set (0.00 sec)

2、 CONCAT(str1, str2) 将字符串 str1 和 str2 首尾连接后返回：

SELECT CONCAT ('My', 'SQL');

输出结果：

+----------------------+
| CONCAT ('My', 'SQL') |
+----------------------+
| MySQL                |
+----------------------+
1 row in set (0.00 sec)

3、 SUBSTR(str, m, n) 获取字符串中指定的子字符串，从 m 位置开始，取 n 个字符，如果 n 被忽略，则取到字符串结尾处：

SELECT SUBSTR('MySQL', 3, 3);

输出结果：

+-----------------------+
| SUBSTR('MySQL', 3, 3) |
+-----------------------+
| SQL                   |
+-----------------------+
1 row in set (0.00 sec)

结果解析：第一个字符的起始位置是 1，不是 0。

4、 LENGTH(str) 返回字符串的长度：

SELECT LENGTH('MySQL');

输出结果：

+-----------------+
| LENGTH('MySQL') |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

5、 INSTR(str, substr) 从字符串 str 中返回子串 substr 第一次出现的位置：

SELECT INSTR('MySQLSQL', 'SQL');

输出结果：

+--------------------------+
| INSTR('MySQLSQL', 'SQL') |
+--------------------------+
|                        3 |
+--------------------------+
1 row in set (0.00 sec)

结果解析：“SQL” 在 “MySQLSQL” 中第一次出现的位置是 3。

6、 LPAD(str1, n, str2) 在字符串 str1 的左边使用字符串 str2 进行填充，直到总长度达到 n 为止：

SELECT LPAD('MySQL', 8, '*');

输出结果：

+-----------------------+
| LPAD('MySQL', 8, '*') |
+-----------------------+
| ***MySQL              |
+-----------------------+
1 row in set (0.00 sec)

结果解析：把 * 添加到 MySQL 的左边，总长度达到 8 为止。

7、 REPLACE(str, old_str, new_str) 在字符串 str 中查找所有的子串 old_str，使用 new_str 替换，并返回替换后的结果：

SELECT REPLACE('MySQL', 'SQL', 'sql');

输出结果：

+--------------------------------+
| REPLACE('MySQL', 'SQL', 'sql') |
+--------------------------------+
| Mysql                          |
+--------------------------------+
1 row in set (0.06 sec)

结果解析：把 MySQL 中的 SQL 替换为小写的 sql。

8、 REPEAT(str, count) 将字符串 str 重复 count 次，并返回重复后的结果：

SELECT REPEAT('MySQL', 2);

输出结果：

+--------------------+
| REPEAT('MySQL', 2) |
+--------------------+
| MySQLMySQL         |
+--------------------+
1 row in set (0.00 sec)

结果解析：MySQL 字符串重复输出两次。

9、 REVERSE(str) 将字符串 str 反转，返回反转后的结果：

SELECT REVERSE('MySQL');

输出结果：

+------------------+
| REVERSE('MySQL') |
+------------------+
| LQSyM            |
+------------------+
1 row in set (0.00 sec)

数值函数

1、 ABS(num) 返回 num 的绝对值：

SELECT ABS(-5);

输出结果：

+---------+
| ABS(-5) |
+---------+
|       5 |
+---------+
1 row in set (0.00 sec)

2、 CEIL(num) 返回大于 num 的最小整数值：

SELECT CEIL(5.1);

输出结果：

+-----------+
| CEIL(5.1) |
+-----------+
|         6 |
+-----------+
1 row in set (0.00 sec)

3、 FLOOR(num) 返回小于 num 的最大整数值：

SELECT FLOOR(5.1);

输出结果：

+------------+
| FLOOR(5.1) |
+------------+
|          5 |
+------------+
1 row in set (0.00 sec)

4、 MOD(num1, num2) 返回 num1 对 num2 进行模运算结果：

SELECT MOD(6, 4);

输出结果：

+-----------+
| MOD(6, 4) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

5、 RAND() 返回 0 到 1 内的随机值：

SELECT RAND();

输出结果：

+---------------------+
| RAND()              |
+---------------------+
| 0.19370275940159593 |
+---------------------+
1 row in set (0.00 sec)

6、 ROUND(num, n) 返回 num 的四舍五入的 n 位小数的值：

SELECT ROUND(5.1364, 2);

输出结果：

+------------------+
| ROUND(5.1364, 2) |
+------------------+
|             5.14 |
+------------------+
1 row in set (0.00 sec)

如果 num 中的小数位数小于 n，缺少的位数用 0 补上。

7、 TRUNCATE(num, n) 返回数字 num 截断为 n 位小数的结果：

SELECT TRUNCATE(5.1364, 2);

输出结果：

+---------------------+
| TRUNCATE(5.1364, 2) |
+---------------------+
|                5.13 |
+---------------------+
1 row in set (0.01 sec)

如果 num 中的小数位数小于 n，缺少的位数用 0 补上。

8、 SQRT(num) 返回 num 的平方根：

SELECT SQRT(16);

输出结果：

+----------+
| SQRT(16) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

时间日期函数

1、 NOW() 返回当前日期时间：

SELECT NOW();

输出结果：

+---------------------+
| NOW()               |
+---------------------+
| 2022-05-07 21:53:41 |
+---------------------+
1 row in set (0.00 sec)

2、 CURDATE() 返回当前日期：

SELECT CURDATE();

输出结果：

+------------+
| CURDATE()  |
+------------+
| 2022-05-07 |
+------------+
1 row in set (0.00 sec)

3、 CURTIME() 返回当前时间：

SELECT CURTIME();

输出结果：

+-----------+
| CURTIME() |
+-----------+
| 21:54:20  |
+-----------+
1 row in set (0.00 sec)

4、 WEEK(date) 返回 date 日期是一年中的第几周：

SELECT WEEK('2020-07-01');

输出结果：

+--------------------+
| WEEK('2020-07-01') |
+--------------------+
|                 26 |
+--------------------+
1 row in set (0.00 sec)

5、 YEAR(date)、MONTH(date)、DAY(date) 返回 date 日期中的年份、月份、日：

SELECT YEAR('2020-07-01'), MONTH('2020-07-01'), DAY('2020-07-01');

输出结果：

+--------------------+---------------------+-------------------+
| YEAR('2020-07-01') | MONTH('2020-07-01') | DAY('2020-07-01') |
+--------------------+---------------------+-------------------+
|               2020 |                   7 |                 1 |
+--------------------+---------------------+-------------------+
1 row in set (0.00 sec)

6、 DATEDIFF(date1, date2) 返回 date1 与 date2 间隔的天数：

SELECT DATEDIFF('2020-07-01', '2020-06-01');

输出结果：

+--------------------------------------+
| DATEDIFF('2020-07-01', '2020-06-01') |
+--------------------------------------+
|                                   30 |
+--------------------------------------+
1 row in set (0.00 sec)

结果说明：如果 date1 日期比 date2 日期小，输出为负值。

7、 ADDDATE(date, n) 返回 date 日期添加 n 天后的新日期：

SELECT ADDDATE('2020-07-01', 10);

输出结果：

+---------------------------+
| ADDDATE('2020-07-01', 10) |
+---------------------------+
| 2020-07-11                |
+---------------------------+
1 row in set (0.00 sec)

转换函数

DATE_FORMAT() 函数

显示今天是星期几

SELECT DATE_FORMAT(NOW(), '%W');

输出结果：

+--------------------------+
| DATE_FORMAT(NOW(), '%W') |
+--------------------------+
| Saturday                  |
+--------------------------+
1 row in set (0.000 sec)

上面例子中可以使用 %a 代替 %W ，那么输出结果则为 Sat ，英文单词的简写；也可以使用 %w 替换 %W ，输出结果为 6 ，即星期几的数值。

只显示年月

只显示年月，且年用四位数字形式来表示，年月之间使用 - 连接。

SELECT DATE_FORMAT(NOW(), '%Y-%c');

输出结果：

+-----------------------------+
| DATE_FORMAT(NOW(), '%Y-%c') |
+-----------------------------+
| 2022-5                      |
+-----------------------------+
1 row in set (0.000 sec)

年份只显示后两位，则上面例子可以使用 %y 代替 %Y ，输出结果为 21-3。

显示当前时间的分钟数和秒数

显示当前时间的分钟数和秒数，分和秒之间用 : 连接。

SELECT DATE_FORMAT(CURTIME(), '%i:%S');

输出结果：

+---------------------------------+
| DATE_FORMAT(CURTIME(), '%i:%S') |
+---------------------------------+
| 55:47                           |
+---------------------------------+
1 row in set (0.000 sec)

STR_TO_DATE() 函数

1、待转换字符串中只能出现数字，否则返回结果为 NULL ：

SELECT STR_TO_DATE('2020070a', '%Y%m%d');

输出结果：

+-----------------------------------+
| STR_TO_DATE('2020070a', '%Y%m%d') |
+-----------------------------------+
| NULL                              |
+-----------------------------------+
1 row in set, 2 warnings (0.000 sec)

2、如果格式字符串仅包含日期，则待转字符串至少需要 8 位数字：

SELECT STR_TO_DATE('202007', '%Y%m%d');

输出结果：

+---------------------------------+
| STR_TO_DATE('202007', '%Y%m%d') |
+---------------------------------+
| NULL                            |
+---------------------------------+
1 row in set, 1 warning (0.000 sec)

结果解析：字符串 202007 低于 8 位，故结果返回 NULL 。

3、转换后日期时间必须有效，否则返回结果为 NULL ：

SELECT STR_TO_DATE('20201301', '%Y%m%d');

输出结果：

+-----------------------------------+
| STR_TO_DATE('20201301', '%Y%m%d') |
+-----------------------------------+
| NULL                              |
+-----------------------------------+
1 row in set, 1 warning (0.000 sec)

结果解析：20201301 转换为日期后得到的月份是 13，超出有效范围，故结果返回 NULL 。

4、如果被转字符串超出 8 位且格式字符串中无时间格式，则自动取前 8 位转换为日期：

SELECT STR_TO_DATE('2020070110', '%Y%m%d');

输出结果：

+-------------------------------------+
| STR_TO_DATE('2020070110', '%Y%m%d') |
+-------------------------------------+
| 2020-07-01                          |
+-------------------------------------+
1 row in set, 1 warning (0.000 sec)

5、格式字符串可以包含时间格式：

SELECT STR_TO_DATE('20200701104523', '%Y%m%d%H%i%S');

输出结果：

+-----------------------------------------------+
| STR_TO_DATE('20200701104523', '%Y%m%d%H%i%S') |
+-----------------------------------------------+
| 2020-07-01 10:45:23                           |
+-----------------------------------------------+
1 row in set (0.000 sec)

CAST 函数与 CONVERT 函数

1、数字和小数点组成的字符串转换为整型：

SELECT CAST('3.12' AS SIGNED);

输出结果：

+------------------------+
| CAST('3.12' AS SIGNED) |
+------------------------+
|                      3 |
+------------------------+
1 row in set, 1 warning (0.00 sec)

使用 CONVERT() 函数则是：CONVERT('3.12', SIGNED)，得到的结果相同。

2、非数值字符串转换为整型：

SELECT CAST('30a1.12' AS SIGNED);

输出结果：

+---------------------------+
| CAST('30a1.12' AS SIGNED) |
+---------------------------+
|                        30 |
+---------------------------+
1 row in set, 1 warning (0.00 sec)

在转换为整型的时候，如果遇到无法识别的字符则停止转换，只返回能正常识别的部分。如果一开始就无法识别则返回 0。

3、把整型转换为二进制：

SELECT CAST(123 AS BINARY);

输出结果：

+---------------------+
| CAST(123 AS BINARY) |
+---------------------+
| 123                 |
+---------------------+
1 row in set (0.00 sec)

使用 BINARY 将数值或字符串进行二进制转换，在打印结果上看不出变化。此处我们可以举一个小例子。

当我们使用等号进行「等值判断」时，大小写英文字母是等值的：

mysql> SELECT 'A' = 'a';
+-----------+
| 'A' = 'a' |
+-----------+
|         1 |
+-----------+
1 row in set (0.01 sec)

如果要使得结果为「否」（也就是 0），可以在字符前面加上 BINARY 关键字将其转换成二进制字符串，这样的判断结果就是「否」了：

mysql> SELECT BINARY 'A' = BINARY 'a';
+-------------------------+
| BINARY 'A' = BINARY 'a' |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)

4、数字和小数点组成的字符串转换为浮点型：

SELECT CAST('12.34' AS DECIMAL(3, 1));

输出结果：

+------------------------------+
| CAST('12.34' AS DECIMAL(3, 1)) |
+------------------------------+
|                         12.3 |
+------------------------------+
1 row in set (0.00 sec)

结果解析：DECIMAL(m, n) ，其中 m 必须大于 n，表示总共 m 位数据，其中小数 n 位，整数 m-n 位。

通用函数

条件判断：IF() 函数

条件判断函数 IF() 的基本语法如下：

IF(expr, value1, value2)

其中 expr 是布尔类型的条件判断表达式，如果 expr 为真返回 value1，否则返回 value2。

举例说明

判断一个比较算术式：

SELECT IF(1 > 2, '是', '否');

输出结果：

+-------------------------+
| IF(1 > 2, '是', '否')   |
+-------------------------+
| 否                      |
+-------------------------+
1 row in set (0.000 sec)

条件判断：IFNULL 函数

条件判断函数 IFNULL() 的基本语法如下：

IFNULL(value1, value2)

该函数先判断 value1，如果 value1 不为 NULL，该函数返回 value1，否则返回 value2。

举例说明

判断一个数值是否为空值：

SELECT IFNULL(NULL, 1);

输出结果：

+-----------------+
| IFNULL(NULL, 1) |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.000 sec)

条件判断：CASE() 函数

条件判断函数 CASE() 的基本语法如下：

CASE expr WHEN value1 THEN result1 [WHEN value2 THEN result2 …… WHEN valuen THEN resultn] [ELSE DEFAULT] END

如果 expr 等于其中一个 value 的值，则返回对应 THEN 后的结果，如果都不等，则返回 ELSE 后面的 DEFAULT。

举例说明

1、成功匹配其中一条 WHEN 分支：

SELECT CASE 2 WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' ELSE 'D' END;

输出结果：

+---------------------------------------------------------------------+
| CASE 2 WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' ELSE 'D' END |
+---------------------------------------------------------------------+
| B                                                                   |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

2、所有 WHEN 分支匹配失败，进入 ELSE 默认分支：

SELECT CASE 5 WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' ELSE 'D' END;

输出结果：

+---------------------------------------------------------------------+
| CASE 5 WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' ELSE 'D' END |
+---------------------------------------------------------------------+
| D                                                                   |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

系统信息函数

1、查看当前 MySQL 数据库版本号：

SELECT VERSION();

2、查看当前使用的数据库：

SELECT DATABASE();

3、查看当前服务器连接次数：

SELECT CONNECTION_ID();

两个不同的加密函数

使用 MD5 进行加密

MD5 是一种被广泛使用的加密方法，它是一个密码散列函数，可以产生一个 128 位的散列值，这些值按一定规则进行排序。当你的保护对象发生变化后，其 MD5 的值也会不一样，所以 MD5 经常用来验证数据有没有被篡改。使用方式如下：

SELECT MD5(123);

输出结果：

+----------------------------------+
| MD5(123)                         |
+----------------------------------+
| 202cb962ac59075b964b07152d234b70 |
+----------------------------------+
1 row in set (0.00 sec)

使用 SHA 进行加密

SHA 和 MD5 一样，也是一个密码散列函数，是 FIFS 认证的安全散列算法，比 MD5 更安全。使用方式如下：

SELECT SHA(123);

输出结果：

+------------------------------------------+
| SHA(123)                                 |
+------------------------------------------+
| 40bd001563085fc35165329ea1ff5c5ecbdbbeef |
+------------------------------------------+
1 row in set (0.00 sec)

sha更加长

格式化函数

接下来给大家介绍的格式化函数是 FORMAT() 函数。使用该函数可得到 ##,###.#### 这种格式的输出。其基本语法如下：

FORMAT(value, n)

其中 value 是被格式化的数据，n 表示保留的小数位数。

1、对整数进行格式化，保留 2 位小数：

SELECT FORMAT(1000, 2);

输出结果：

+-----------------+
| FORMAT(1000, 2) |
+-----------------+
| 1,000.00        |
+-----------------+
1 row in set (0.000 sec)

2、对浮点数进行格式化，保留 2 位小数：

SELECT FORMAT(1000.125, 2);

输出结果：

+---------------------+
| FORMAT(1000.125, 2) |
+---------------------+
| 1,000.13            |
+---------------------+
1 row in set (0.000 sec)

结果解析：可以看到该函数具有四舍五入的功能。

多行函数

国家人口的平均值 avg(国家人口)

国家人口的总数 sum(国家人口)

人口最多和最少国家的人口数量 max(国家人口)，min(国家人口)

总共有多少国家 count(国家名字)

分组函数

使用 GROUP BY 进行分组时要注意：

GROUP BY 一般和多行函数一起使用；
使用分组统计时，SELECT 子句后要么是多行函数，要么是 GROUP BY 子句中用于分组的字段；换句话说，分组后，只能查询组的值或组的统计值；
对 GROUP BY 的结果进行筛选，使用 HAVING 关键字。

MySql不难，我们一起进步之函数篇

MySQL 常用内置函数

字符串函数

数值函数

时间日期函数

转换函数

DATE_FORMAT() 函数

显示今天是星期几

只显示年月

显示当前时间的分钟数和秒数

STR_TO_DATE() 函数

CAST 函数与 CONVERT 函数

通用函数

条件判断：IF() 函数

举例说明

条件判断：IFNULL 函数

举例说明

条件判断：CASE() 函数

举例说明

系统信息函数

两个不同的加密函数

使用 MD5 进行加密

使用 SHA 进行加密

格式化函数

多行函数

国家人口的平均值 avg(国家人口)

国家人口的总数 sum(国家人口)

人口最多和最少国家的人口数量 max(国家人口)，min(国家人口)

总共有多少国家 count(国家名字)

分组函数