函数能帮助用户做很多的事情，比如说字符串的处理 、数值的运算 、日期的运算等，在这方面 MySQL 提供了多种内建函数帮助开发人员编写简单快捷的 SQL 语句，其中常用的函数有字符串函数 、日期函数和数值函数。
在 MySQL 数据库中，函数可以用在 SELECT 语句及其子句（例如 WHERE 、ORDER BY 、HAVING 等）中，也可以用在 UPDATE 、DELETE 语句及其子句中。
字符串函数
字符串函数是最常用的一种函数，在 MySQL 中，字符串函数是最丰富的一类函数。
| 序号 | 函数 | 功能 |
| 1 | ASCII(str) | 返回字符串 str 第一个字符的 ASCII 码 |
| 2 | CONCAT(S1, S2, ... Sn) | 返回 S1, S2, ... Sn 为一个字符串 |
| 3 | CONCAT_WS(x, S1, S2 ... Sn) | 返回 S1, S2, ... Sn 为一个字符串，并以分隔符 x 分隔 |
| 4 | LENGTH(str) | 返回字符串 str 的字符数 |
| 5 | INSERT(str, x, y, instr) | 将字符串 str 从第 x 位置开始，y 个字符长的子串替换为字符串 instr |
| 6 | FIELD(str, S1, S2 ... Sn) | 返回第一个字符串 str 在字符串列表 S1, S2 ... Sn 中的位置 |
| 7 | FIND_IN_SET(S1, S2) | 返回在字符串 S2 中与 S1 匹配的字符串的位置 |
| 8 | LOCATE(S1, S2) | 从字符串 S2 中获取 S1 的开始位置 |
| 9 | LOWER(str) | 将字符串 str 中所有字符变为小写 |
| 10 | UPPER(str) | 将字符串 str 中所有字符变为大写 |
| 11 | LEFT(str, x) | 返回字符串 str 最左边的 x 个字符 |
| 12 | RIGHT(str, x) | 返回字符串 str 最右边的 x 个字符 |
| 13 | LPAD(str, n, pad) | 用字符串 pad 对 str 最左边进行填充，直到长度为 n 个字符长度 |
| 14 | RPAD(str, n, pad) | 用字符串 pad 对 str 最右边进行填充，直到长度为 n 个字符长度 |
| 15 | LTRIM(str) | 去掉字符串 str 左侧的空格 |
| 16 | RTRIM(str) | 去掉字符串 str 右侧的空格 |
| 17 | TRIM(str) | 去掉字符串行尾和行首的空格 |
| 18 | POSITION(S1 IN S2) | 从字符串 S2 中获取 S1 的开始位置 |
| 19 | REPEAT(str, x) | 返回 str 重复 x 次的结果 |
| 20 | REPLACE(str, a, b) | 用字符串 b 替换字符串 str 中所有出现的字符串 a |
| 21 | STRCMP(s1, s2) | 比较字符串 s1 和 s2 |
| 22 | SUBSTR(str, x, y) | 返回字符串 str 从 x 位置起 y 个字符长度的字符串 |
| 23 | SUBSTRING(str, x, y) | 返回字符串 str 从 x 位置起 y 个字符长度的字符串 |
- ASCII(str) 函数：返回字符串 str 第一个字符的 ASCII 码
mysql> select ASCII('China'); +----------------+ | ASCII('China') | +----------------+ | 67 | +----------------+ 1 row in set (0.00 sec) mysql> select ASCII('china'); +----------------+ | ASCII('china') | +----------------+ | 99 | +----------------+ 1 row in set (0.00 sec)
- CONCAT(S1, S2, ... Sn) 函数：把传入的参数连接成为一个字符串
把 “aaa” 、“bbb” 、“ccc” 三个字符串连接成为一个新字符串 “aaabbbccc”，另外，任何字符串与 NULL 进行连接的结果都将是 NULL。
mysql> select concat('aaa', 'bbb', 'ccc'), concat('aaa', null); +-----------------------------+---------------------+ | concat('aaa', 'bbb', 'ccc') | concat('aaa', null) | +-----------------------------+---------------------+ | aaabbbccc | NULL | +-----------------------------+---------------------+ 1 row in set (0.00 sec)
- CONCAT_WS(x, S1, S2 ... Sn) 函数：把传入的参数连接成为一个字符串，并以分隔符 x 分隔
把 “aaa” 、“bbb” 、“ccc” 三个字符串连接成为一个新字符串 “aaa,bbb,ccc”，另外，任何字符串与 NULL 进行连接的结果都将是 NULL。
mysql> select concat_ws(',', 'aaa', 'bbb', 'ccc'), concat_ws('aaa', null); +-------------------------------------+------------------------+ | concat_ws(',', 'aaa', 'bbb', 'ccc') | concat_ws('aaa', null) | +-------------------------------------+------------------------+ | aaa,bbb,ccc | | +-------------------------------------+------------------------+ 1 row in set (0.00 sec)
- LENGTH(str) 函数：返回字符串 str 的字符数
mysql> select length('china'); +-----------------+ | length('china') | +-----------------+ | 5 | +-----------------+ 1 row in set (0.00 sec) mysql> select length('hello world'); +-----------------------+ | length('hello world') | +-----------------------+ | 11 | +-----------------------+ 1 row in set (0.00 sec)
- INSERT(str, x, y, instr) 函数：将字符串 str 从第 x 位置开始，y 个字符长的子串替换为字符串 instr
下面的例子把字符串“beijing2008you”中从第 12 个字符开始以后的 3 个字符替换成“me”。
mysql> select insert('beijing2008you', 12, 3, 'me'); +---------------------------------------+ | insert('beijing2008you', 12, 3, 'me') | +---------------------------------------+ | beijing2008me | +---------------------------------------+ 1 row in set (0.00 sec)
- FIELD(str, S1, S2 ... Sn) 函数：返回第一个字符串 str 在字符串列表 S1, S2 ... Sn 中的位置
下面的例子返回字符串 “c” 在字符串列表中的位置。
mysql> select field("c", "a", "b", "c", "d", "e"); +-------------------------------------+ | field("c", "a", "b", "c", "d", "e") | +-------------------------------------+ | 3 | +-------------------------------------+ 1 row in set (0.00 sec)
- FIND_IN_SET(S1, S2) 函数：返回在字符串 S2 中与 S1 匹配的字符串的位置
下面的例子返回字符串 “c” 在指定字符串中的位置。
mysql> select find_in_set('c', 'a,b,c,d,e'), find_in_set('c', 'a,b,d,e'); +-------------------------------+-----------------------------+ | find_in_set('c', 'a,b,c,d,e') | find_in_set('c', 'a,b,d,e') | +-------------------------------+-----------------------------+ | 3 | 0 | +-------------------------------+-----------------------------+ 1 row in set (0.00 sec)
- LOCATE(S1, S2) 函数：从字符串 S2 中获取 S1 的开始位置
下面的例子获取 “b” 在字符串 “abc” 中的位置。
mysql> select locate('b', 'abc'), locate('b', 'ac'); +--------------------+-------------------+ | locate('b', 'abc') | locate('b', 'ac') | +--------------------+-------------------+ | 2 | 0 | +--------------------+-------------------+ 1 row in set (0.00 sec)
- LOWER(str) 函数：把字符串转换成小写
mysql> select lower('beijing2008'); +----------------------+ | lower('beijing2008') | +----------------------+ | beijing2008 | +----------------------+ 1 row in set (0.00 sec)
- UPPER(str) 函数：把字符串转换成大写
mysql> select upper('beijing2008'); +----------------------+ | upper('beijing2008') | +----------------------+ | BEIJING2008 | +----------------------+ 1 row in set (0.00 sec)
- LEFT(str, x) 函数：返回字符串最左边的 x 个字符，如果第二个参数是 NULL，那么将不返回任何字符串
mysql> select left('beijing2008', 7), left('beijing2008', null); +------------------------+---------------------------+ | left('beijing2008', 7) | left('beijing2008', null) | +------------------------+---------------------------+ | beijing | NULL | +------------------------+---------------------------+ 1 row in set (0.00 sec)
- RIGHT(str, x) 函数：返回字符串最右边的 x 个字符，如果第二个参数是 NULL，那么将不返回任何字符串
mysql> select right('beijing2008', 4), right('beijing2008', null); +-------------------------+----------------------------+ | right('beijing2008', 4) | right('beijing2008', null) | +-------------------------+----------------------------+ | 2008 | NULL | +-------------------------+----------------------------+ 1 row in set (0.00 sec)
- LPAD(str, n, pad) 函数：用字符串 pad 对 str 最左边进行填充，直到长度为 n 个字符长度
mysql> select lpad('2008', 10, 'beijing'); +-----------------------------+ | lpad('2008', 10, 'beijing') | +-----------------------------+ | beijin2008 | +-----------------------------+ 1 row in set (0.00 sec)
- RPAD(str, n, pad) 函数：用字符串 pad 对 str 最右边进行填充，直到长度为 n 个字符长度
mysql> select rpad('beijing', 10, '2008'); +-----------------------------+ | rpad('beijing', 10, '2008') | +-----------------------------+ | beijing200 | +-----------------------------+ 1 row in set (0.00 sec)
- LTRIM(str) 函数：去掉字符串 str 左侧空格
mysql> select ltrim(' beijing'); +---------------------+ | ltrim(' beijing') | +---------------------+ | beijing | +---------------------+ 1 row in set (0.00 sec)
- RTRIM(str)函数：去掉字符串 str 右侧空格
mysql> select rtrim('beijing '); +---------------------+ | rtrim('beijing ') | +---------------------+ | beijing | +---------------------+ 1 row in set (0.00 sec)
- TRIM(str) 函数：去掉目标字符串的开头和结尾的空格
mysql> select trim(' $ beijing2008 $ '); +------------------------------+ | trim(' $ beijing2008 $ ') | +------------------------------+ | $ beijing2008 $ | +------------------------------+ 1 row in set (0.00 sec)
- POSITION(S1 IN S2) 函数：从字符串 S2 中获取 S1 的开始位置
下面的例子返回字符串 “abc” 中 “b” 的位置。
mysql> select position('b' in 'abc'); +------------------------+ | position('b' in 'abc') | +------------------------+ | 2 | +------------------------+ 1 row in set (0.00 sec)
- REPEAT(str, x) 函数：返回 str 重复 x 次的结果
mysql> select repeat('mysql', 3); +--------------------+ | repeat('mysql', 3) | +--------------------+ | mysqlmysqlmysql | +--------------------+ 1 row in set (0.00 sec)
- REPLACE(str, a, b) 函数：用字符串 b 替换字符串 str 中所有出现的字符串 a
用字符串“2008”代替字符串“beijing_2010”中的“_2010”
mysql> select replace('beijing_2010', '_2010', '2008'); +------------------------------------------+ | replace('beijing_2010', '_2010', '2008') | +------------------------------------------+ | beijing2008 | +------------------------------------------+ 1 row in set (0.00 sec)
- STRCMP(s1, s2) 函数：比较字符串 s1 和 s2 的 ASCII 码值的大小
如果 s1 比 s2 小，那么返回 -1；如果 s1 与 s2 相等，那么返回 0；如果 s1 比 s2 大，那么返回 1
mysql> select strcmp('a', 'b'), strcmp('b', 'b'), strcmp('c', 'b'); +------------------+------------------+------------------+ | strcmp('a', 'b') | strcmp('b', 'b') | strcmp('c', 'b') | +------------------+------------------+------------------+ | -1 | 0 | 1 | +------------------+------------------+------------------+ 1 row in set (0.00 sec)
- SUBSTR(str, x, y) 函数：返回从字符串 str 中的第 x 位置起 y 个字符长度的字串
此函数经常用来对给定字符串进行字串的提取
mysql> select substr('beijing2008', 8, 4), substr('beijing2008', 1, 7); +-----------------------------+-----------------------------+ | substr('beijing2008', 8, 4) | substr('beijing2008', 1, 7) | +-----------------------------+-----------------------------+ | 2008 | beijing | +-----------------------------+-----------------------------+ 1 row in set (0.00 sec)
- SUBSTRING(str, x, y) 函数：返回从字符串 str 中的第 x 位置起 y 个字符长度的字串
此函数经常用来对给定字符串进行字串的提取
mysql> select substring('beijing2008', 8, 4), substring('beijing2008', 1, 7); +--------------------------------+--------------------------------+ | substring('beijing2008', 8, 4) | substring('beijing2008', 1, 7) | +--------------------------------+--------------------------------+ | 2008 | beijing | +--------------------------------+--------------------------------+ 1 row in set (0.00 sec)
数值函数
MySQL 中另外一类很重要的函数就是数值函数，这些函数能处理很多数值方面的运算。
| 序号 | 函数 | 功能 |
| 1 | ABS(x) | 返回 x 的绝对值 |
| 2 | CEIL(x) | 返回大于 x 的最小整数值 |
| 3 | FLOOR(x) | 返回小于 x 的最小整数值 |
| 4 | MOD(x, y) | 返回 x/y 的模 |
| 5 | RAND() | 返回 0 ~ 1 内的随机数 |
| 6 | ROUND(x, y) | 返回数值 x 四舍五入 y 位小数的值 |
| 7 | TRUNCATE(x, y) | 返回数值 x 截断为 y 位小数的结果 |
| 8 | GREATEST(expr1, expr2, ... exprn) | 返回列表中的最大值 |
| 9 | LEAST(expr1, expr2, ... exprn) | 返回列表中的最小值 |
- ABS(x) 函数：返回 x 的绝对值
mysql> select abs(-0.8), abs(0.8); +-----------+----------+ | abs(-0.8) | abs(0.8) | +-----------+----------+ | 0.8 | 0.8 | +-----------+----------+ 1 row in set (0.00 sec)
- CEIL(x) 函数：返回大于 x 的最小整数
mysql> select ceil(-0.8), ceil(0.8); +------------+-----------+ | ceil(-0.8) | ceil(0.8) | +------------+-----------+ | 0 | 1 | +------------+-----------+ 1 row in set (0.00 sec)
- FLOOR(x) 函数：返回小于 x 的最大整数，和 CEIL 的用法刚好相反
mysql> select floor(-0.8), floor(0.8); +-------------+------------+ | floor(-0.8) | floor(0.8) | +-------------+------------+ | -1 | 0 | +-------------+------------+ 1 row in set (0.00 sec)
- MOD(x, y) 函数：返回 x/y 的模
和 x%y 的结果相同，模数和被摸数任何一个为 NULL 结果都为 NULL
mysql> select mod(15, 10), mod(1, 11), mod(null, 10), mod(10, null); +-------------+------------+---------------+---------------+ | mod(15, 10) | mod(1, 11) | mod(null, 10) | mod(10, null) | +-------------+------------+---------------+---------------+ | 5 | 1 | NULL | NULL | +-------------+------------+---------------+---------------+ 1 row in set (0.00 sec)
- RAND() 函数：返回 0~1 内的随机值
mysql> select rand(), rand(); +---------------------+--------------------+ | rand() | rand() | +---------------------+--------------------+ | 0.15221380549689179 | 0.6532633832220578 | +---------------------+--------------------+ 1 row in set (0.00 sec)
可以利用此函数可以取任意指定范围内的随机数，比如需要产生 0~100 内的任意随机整数，可以进行如下操作：
mysql> select ceil(100*rand()), ceil(100*rand()); +------------------+------------------+ | ceil(100*rand()) | ceil(100*rand()) | +------------------+------------------+ | 81 | 9 | +------------------+------------------+ 1 row in set (0.00 sec)
- ROUND(x, y) 函数：返回参数 x 的 四舍五入的有 y 位小数的值
如果是整数，将会保留 y 位数量的 0；如果不写 y，则默认 y 为 0，即将 x 四舍五入后取整。适合于将所有数字保留同样小数位的情况。
mysql> select round(1.1), round(1.1, 2), round(1, 2); +------------+---------------+-------------+ | round(1.1) | round(1.1, 2) | round(1, 2) | +------------+---------------+-------------+ | 1 | 1.10 | 1 | +------------+---------------+-------------+ 1 row in set (0.00 sec)
- TRUNCATE(x, y) 函数：返回数字 x 截断为 y 位小数的结果
注意 TRUNCATE 和 ROUND 的区别在于 TRUNCATE 仅仅是截断，而不进行四舍五入。
mysql> select round(1.235, 2), truncate(1.235, 2); +-----------------+--------------------+ | round(1.235, 2) | truncate(1.235, 2) | +-----------------+--------------------+ | 1.24 | 1.23 | +-----------------+--------------------+ 1 row in set (0.00 sec)
- GREATEST(expr1, expr2, ... exprn) 函数： 返回列表中的最大值
注： 如果任一参数为 NULL，则函数将立即返回 NULL，而不进行任何比较。
mysql> select greatest(3, 12, 34, 8, 25); +----------------------------+ | greatest(3, 12, 34, 8, 25) | +----------------------------+ | 34 | +----------------------------+ 1 row in set (0.00 sec) mysql> select greatest('Google', 'Baidu', 'Apple'); +--------------------------------------+ | greatest('Google', 'Baidu', 'Apple') | +--------------------------------------+ | Google | +--------------------------------------+ 1 row in set (0.00 sec) mysql> select greatest('1900-01-01', '2050-12-31'); +--------------------------------------+ | greatest('1900-01-01', '2050-12-31') | +--------------------------------------+ | 2050-12-31 | +--------------------------------------+ 1 row in set (0.00 sec) mysql> select greatest(1, 3, null); +----------------------+ | greatest(1, 3, null) | +----------------------+ | NULL | +----------------------+ 1 row in set (0.00 sec)
- LEAST(expr1, expr2, ... exprn) 函数：返回列表中的最小值
注： 如果任一参数为 NULL，则函数将立即返回 NULL，而不进行任何比较。
mysql> select least(3, 12, 34, 8, 25); +-------------------------+ | least(3, 12, 34, 8, 25) | +-------------------------+ | 3 | +-------------------------+ 1 row in set (0.00 sec) mysql> select least('Google', 'Baidu', 'Apple'); +-----------------------------------+ | least('Google', 'Baidu', 'Apple') | +-----------------------------------+ | Apple | +-----------------------------------+ 1 row in set (0.00 sec) mysql> select least('1900-01-01', '2050-12-31'); +-----------------------------------+ | least('1900-01-01', '2050-12-31') | +-----------------------------------+ | 1900-01-01 | +-----------------------------------+ 1 row in set (0.00 sec) mysql> select least(1, 3, null); +-------------------+ | least(1, 3, null) | +-------------------+ | NULL | +-------------------+ 1 row in set (0.00 sec)
日期和时间函数
有时我们会遇到这样的需求：当前时间是多少，下个月的今天是星期几，统计截止到当前日期前三天的收入总和，等等。这些需求就需要日期和时间函数来实现。
| 序号 | 函数 | 功能 |
| 1 | CURDATE() | 返回当前日期 |
| 2 | CURTIME() | 返回当前时间 |
| 3 | NOW() | 返回当前的日期和时间 |
| 4 | UNIX_TIMESTAMP(date) | 返回日期 date 的 UNIX 时间戳 |
| 5 | FROM_UNIXTIME(unixtime) | 返回 UNIX 时间戳的日期值 |
| 6 | WEEK(date) | 返回日期 date 为一年中的第几周 |
| 7 | YEAR(date) | 返回日期 date 的年份 |
| 8 | HOUR(time) | 返回 time 的小时值 |
| 9 | MINUTE(time) | 返回 time 的分钟值 |
| 10 | MONTHNAME(date) | 返回 date 的月份名 |
| 11 | DATE_FORMAT(date, fmt) | 返回按字符串 fmt 格式化日期 date 值 |
| 12 | DATE_ADD(date, INTERVAL expr type) | 返回一个日期或时间值加上一个时间间隔的时间值 |
| 13 | DATEDIFF(expr, expr2) | 返回起始时间 expr 和结束时间 expr2 之间的天数 |
- CURDATE() 函数：返回当前日期，只包含年月日
mysql> select curdate(); +------------+ | curdate() | +------------+ | 2021-11-20 | +------------+ 1 row in set (0.00 sec)
- CURTIME() 函数：返回当前时间，只包含时分秒
mysql> select curtime(); +-----------+ | curtime() | +-----------+ | 18:00:50 | +-----------+ 1 row in set (0.00 sec)
- NOW() 函数：返回当前的日期和时间，年月日时分秒全部包含
mysql> select now(); +---------------------+ | now() | +---------------------+ | 2021-11-20 18:01:29 | +---------------------+ 1 row in set (0.00 sec)
- UNIX_TIMESTAMP(date) 函数：返回日期 date 的 UNIX 时间戳
mysql> select unix_timestamp(now()); +-----------------------+ | unix_timestamp(now()) | +-----------------------+ | 1637402607 | +-----------------------+ 1 row in set (0.00 sec)
- FROM_UNIXTIME 函数：返回 UNIXTIME 时间戳的日期值，和 UNIX_TIMESTAMP(date) 互为逆操作
mysql> select from_unixtime(1184134516); +---------------------------+ | from_unixtime(1184134516) | +---------------------------+ | 2007-07-11 14:15:16 | +---------------------------+ 1 row in set (0.00 sec)
- WEEK(date) 函数：返回所给日期是一年中的第几周
mysql> select week(now()); +-------------+ | week(now()) | +-------------+ | 46 | +-------------+ 1 row in set (0.00 sec)
- YEAR(date) 函数：返回所给日期是哪一年
mysql> select year(now()); +-------------+ | year(now()) | +-------------+ | 2021 | +-------------+ 1 row in set (0.00 sec)
- HOUR(time) 函数：返回所给时间的小时
mysql> select hour(now()); +-------------+ | hour(now()) | +-------------+ | 18 | +-------------+ 1 row in set (0.00 sec)
- MINUTE(time) 函数：返回所给时间的分钟
mysql> select minute(now()); +---------------+ | minute(now()) | +---------------+ | 7 | +---------------+ 1 row in set (0.00 sec)
- MONTHNAME(date) 函数：返回 date 的英文月份名称
mysql> select monthname(now()); +------------------+ | monthname(now()) | +------------------+ | November | +------------------+ 1 row in set (0.00 sec)
- DATE_FORMAT(date, fmt) 函数：按字符串 fmt 格式化日期 date 值，此函数能按指定的格式显示日期
将当前时间显示为“月, 日, 年” 格式
mysql> select date_format(now(), '%M,%D,%Y'); +--------------------------------+ | date_format(now(), '%M,%D,%Y') | +--------------------------------+ | November,20th,2021 | +--------------------------------+ 1 row in set (0.00 sec)
- DATE_ADD(date, INTERVAL expr type) 函数：返回与所给日期 date 相差 INTERAL 时间段的日期
下面的例子中，第 1 列返回当前的日期时间，第 2 列返回距离当前日期 31 天后的日期时间，第 3 列返回距离当前日期一年两个月后的日期时间。
mysql> select now(), date_add(now(), interval 31 day), date_add(now(), interval '1_2' year_month); +---------------------+----------------------------------+--------------------------------------------+ | now() | date_add(now(), interval 31 day) | date_add(now(), interval '1_2' year_month) | +---------------------+----------------------------------+--------------------------------------------+ | 2021-11-20 18:18:33 | 2021-12-21 18:18:33 | 2023-01-20 18:18:33 | +---------------------+----------------------------------+--------------------------------------------+ 1 row in set (0.00 sec)
同样也可以用负数让它返回之前的某个日期时间，如下第 1 列返回当前的日期时间，第 2 列返回距离当前日期 31 天前的日期时间，第 3 列返回距离当前日期一年两个月前的日期时间。
mysql> select now(), date_add(now(), interval -31 day), date_add(now(), interval '-1_-2' year_month); +---------------------+-----------------------------------+----------------------------------------------+ | now() | date_add(now(), interval -31 day) | date_add(now(), interval '-1_-2' year_month) | +---------------------+-----------------------------------+----------------------------------------------+ | 2021-11-20 18:20:57 | 2021-10-20 18:20:57 | 2020-09-20 18:20:57 | +---------------------+-----------------------------------+----------------------------------------------+ 1 row in set (0.00 sec)
- DATEDIFF(expr, expr2) 函数：用来计算两个日期之间相差的天数
mysql> select datediff('2022-01-01', now()); +-------------------------------+ | datediff('2022-01-01', now()) | +-------------------------------+ | 42 | +-------------------------------+ 1 row in set (0.00 sec)
日期和时间格式
在按照字符串 fmt 格式化日期 date 值时，此函数能够按指定的格式显示日期，可以用到的格式符如下所示。
| 序号 | 格式符 | 格式说明 |
| 1 | %S 和 %s | 两位数字形式的秒（00, 01, ... 59） |
| 2 | %i | 两位数字形式的分钟（00, 01, ... 59） |
| 3 | %H | 两位数字形式的小时，24 小时制（00, 01, ... 23） |
| 4 | %h 和 %I | 两位数字形式的小时，12 小时制（00, 01, ... 12） |
| 5 | %k | 数字形式的小时，24 小时制（0, 1, ... 23） |
| 6 | %l | 数字形式的小时，12 小时制（0, 1, ... 12） |
| 7 | %T | 24 小时的时间形式（hh:mm:ss） |
| 8 | %r | 12 小时的时间形式（hh:mm:ssAM 或 hh:mm:ssPM） |
| 9 | %p | AM 或 PM |
| 10 | %W | 一周中每一天的名称（Sunday, Monday, ... Saturday） |
| 11 | %a | 一周中每一天的名称缩写（Sun, Mon, ... Sat） |
| 12 | %d | 两位数字表示月中的天数（00, 01, ... 31） |
| 13 | %e | 数字形式表示月中的天数（1, 2, ... 31） |
| 14 | %D | 英文后缀表示月中的天数（1st, 2nd, 3rd, ...） |
| 15 | %w | 以数字形式表示周中的天数（0=Sunday, 1=Monday, ... 6=Saturday） |
| 16 | %j | 以 3 位数字表示年中的天数（001, 002, ... 365） |
| 17 | %U | 周（0, 1, ... 52），其中 Sunday 为周中的第一天 |
| 18 | %u | 周（0, 1, ... 52），其中 Monday 为周中的第一天 |
| 19 | %M | 月名称（January, February, ... December） |
| 20 | %b | 缩写的月名称（January, February, ... December） |
| 21 | %m | 两位数字表示的月份（01, 02, ... 12） |
| 22 | %c | 数字表示的月份（1, 2, ... 12） |
| 23 | %Y | 4 位数字表示的年份 |
| 24 | %y | 两位数字表示的年份 |
| 25 | %% | 直接值“%” |
日期间隔类型
| 表达式类型 | 描述 | 格式 |
| HOUR | 小时 | hh |
| MINUTE | 分 | mm |
| SECOND | 秒 | ss |
| YEAR | 年 | YY |
| MONTH | 月 | MM |
| DAY | 日 | DD |
| YEAR_MONTH | 年和月 | YY-MM |
| DAY_HOUR | 日和小时 | DD hh |
| DAY_MINUTE | 日和分钟 | DD hh:mm |
| DAY_SECOND | 日和秒 | DD hh:mm:ss |
| HOUR_MINUTE | 小时和分 | hh:mm |
| HOUR_SECOND | 小时和秒 | hh:ss |
| MINUTE_SECOND | 分钟和秒 | mm:ss |
日期类型转换
- 日期类型转换为字符串类型
mysql> select now(); +---------------------+ | now() | +---------------------+ | 2021-12-29 09:06:21 | +---------------------+ 1 row in set (0.00 sec) mysql> select date_format(now(), '%Y-%m-%d'); +--------------------------------+ | date_format(now(), '%Y-%m-%d') | +--------------------------------+ | 2021-12-29 | +--------------------------------+ 1 row in set (0.00 sec) mysql> select date_format(now(), '%Y%m%d'); +------------------------------+ | date_format(now(), '%Y%m%d') | +------------------------------+ | 20211229 | +------------------------------+ 1 row in set (0.00 sec)
- 字符串类型转换为日期类型
mysql> select str_to_date('20211229', '%Y%m%d'); +-----------------------------------+ | str_to_date('20211229', '%Y%m%d') | +-----------------------------------+ | 2021-12-29 | +-----------------------------------+ 1 row in set (0.00 sec) mysql> select str_to_date('2021-12-29', '%Y-%m-%d'); +---------------------------------------+ | str_to_date('2021-12-29', '%Y-%m-%d') | +---------------------------------------+ | 2021-12-29 | +---------------------------------------+ 1 row in set (0.00 sec)
- 日期类型转换为数值类型
mysql> select cast(date_format(now(), '%Y%m%d') as signed int); +--------------------------------------------------+ | cast(date_format(now(), '%Y%m%d') as signed int) | +--------------------------------------------------+ | 20211229 | +--------------------------------------------------+ 1 row in set (0.00 sec)
- 数值类型转换为日期类型
mysql> select str_to_date(20211229, '%Y%m%d'); +---------------------------------+ | str_to_date(20211229, '%Y%m%d') | +---------------------------------+ | 2021-12-29 | +---------------------------------+ 1 row in set (0.00 sec) mysql> select str_to_date(cast(20211229 as char(10)), '%Y%m%d'); +---------------------------------------------------+ | str_to_date(cast(20211229 as char(10)), '%Y%m%d') | +---------------------------------------------------+ | 2021-12-29 | +---------------------------------------------------+ 1 row in set (0.00 sec)
- 字符串类型转换为数值类型
mysql> select cast('123' as signed int); +---------------------------+ | cast('123' as signed int) | +---------------------------+ | 123 | +---------------------------+ 1 row in set (0.00 sec)
- 数值类型转换为字符串类型
mysql> select cast(123 as char(3)); +----------------------+ | cast(123 as char(3)) | +----------------------+ | 123 | +----------------------+ 1 row in set (0.00 sec)
流程函数 / 空值处理
流程函数也是很常用的一类函数，用户可以使用这类函数在一个 SQL 语句中实现条件选择，这样做能够提高语句的效率。
| 序号 | 函数 | 功能 |
| 1 | IF(value, t, f) | 如果 value 为真， |
| 2 | IFNULL(value1, value2) | 如果 value1 不为空，返回 value1，否则返回 value2 |
| 3 | COALESCE(expr1, expr2, ... expr_n) | 返回参数中的第一个非空表达式（从左向右） |
| 4 | CASE WHEN [value1] THEN [result1] ... ELSE [default] END | 如果 value1 为真，返回 result1，否则返回 default |
| 5 | CASE [expr] WHEN [value1] THEN [result1] ... ELSE [default] END | 如果 expr 等于 value1，返回 result1，否则返回 default |
创建测试表 salary，并插入数据。
mysql> create table salary(userid int, salary decimal(9,2)); Query OK, 0 rows affected (0.02 sec) mysql> desc salary; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | userid | int(11) | YES | | NULL | | | salary | decimal(9,2) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> insert into salary values(1,1000), (2,2000), (3,3000), (4,4000), (5,5000), (1,null); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from salary; +--------+---------+ | userid | salary | +--------+---------+ | 1 | 1000.00 | | 2 | 2000.00 | | 3 | 3000.00 | | 4 | 4000.00 | | 5 | 5000.00 | | 1 | NULL | +--------+---------+ 6 rows in set (0.00 sec)
- IF(value, t, f) 函数：指定月薪在 2000 元以上的职员属于高薪，用“high”表示；而 2000 元以下的职员属于低薪，用“low”表示
mysql> select userid, salary, if(salary>2000, 'high', 'low') from salary; +--------+---------+--------------------------------+ | userid | salary | if(salary>2000, 'high', 'low') | +--------+---------+--------------------------------+ | 1 | 1000.00 | low | | 2 | 2000.00 | low | | 3 | 3000.00 | high | | 4 | 4000.00 | high | | 5 | 5000.00 | high | | 1 | NULL | low | +--------+---------+--------------------------------+ 6 rows in set (0.00 sec)
- IFNULL(value1, value2) 函数：这个函数一般用来替换 NULL 值，因为 NULL 值是不能参与数值运算的
mysql> select userid, salary, ifnull(salary,0) from salary; +--------+---------+------------------+ | userid | salary | ifnull(salary,0) | +--------+---------+------------------+ | 1 | 1000.00 | 1000.00 | | 2 | 2000.00 | 2000.00 | | 3 | 3000.00 | 3000.00 | | 4 | 4000.00 | 4000.00 | | 5 | 5000.00 | 5000.00 | | 1 | NULL | 0.00 | +--------+---------+------------------+ 6 rows in set (0.00 sec)
- COALESCE(expr1, expr2, ... expr_n)
mysql> select coalesce(null, 'baidu.com', null, 'google.com'); +-------------------------------------------------+ | coalesce(null, 'baidu.com', null, 'google.com') | +-------------------------------------------------+ | baidu.com | +-------------------------------------------------+ 1 row in set (0.00 sec)
- CASE WHEN [value1] THEN [result1] ... ELSE [default] END
mysql> select userid, salary, case when salary<=2000 then 'low' else 'high' end from salary; +--------+---------+---------------------------------------------------+ | userid | salary | case when salary<=2000 then 'low' else 'high' end | +--------+---------+---------------------------------------------------+ | 1 | 1000.00 | low | | 2 | 2000.00 | low | | 3 | 3000.00 | high | | 4 | 4000.00 | high | | 5 | 5000.00 | high | | 1 | NULL | high | +--------+---------+---------------------------------------------------+ 6 rows in set (0.00 sec)
- CASE [expr] WHEN [value1] THEN [result1] ... ELSE [default] END
mysql> select userid, salary, case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary; +--------+---------+-----------------------------------------------------------------------+ | userid | salary | case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end | +--------+---------+-----------------------------------------------------------------------+ | 1 | 1000.00 | low | | 2 | 2000.00 | mid | | 3 | 3000.00 | high | | 4 | 4000.00 | high | | 5 | 5000.00 | high | | 1 | NULL | high | +--------+---------+-----------------------------------------------------------------------+ 6 rows in set (0.00 sec)
标准偏差函数
标准差是衡量数据集中值的分布情况，标准偏差显示平均值存在多少变化。
| 序号 | 函数 | 功能 |
| 1 | STD(expression) | 返回表达式的总体标准偏差。如果没有匹配的行，则 STD 函数返回 NULL |
| 2 | STDEV_POP(expression) | 相当于 STD 函数 |
| 3 | VAR_POP(expression) | 计算表达式的总体方差 |
STD(expression)
- 查看示例数据库 yiibaidb 中的 orders 表，其表结构如下所示：
mysql> use yiibaidb Database changed mysql> desc orders; +----------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+-------------+------+-----+---------+-------+ | orderNumber | int(11) | NO | PRI | NULL | | | orderDate | date | NO | | NULL | | | requiredDate | date | NO | | NULL | | | shippedDate | date | YES | | NULL | | | status | varchar(15) | NO | | NULL | | | comments | text | YES | | NULL | | | customerNumber | int(11) | NO | MUL | NULL | | +----------------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec)
- 查询从 orders 表中返回客户编号及其订单数：
mysql> select customernumber, count(*) ordercount from orders where status = 'shipped' group by customernumber; +----------------+------------+ | customernumber | ordercount | +----------------+------------+ | 103 | 3 | | 112 | 3 | | 114 | 5 | ... ... ... | 495 | 2 | | 496 | 3 | +----------------+------------+
- 计算客户订单数量的总体标准差：
mysql> select std(ordercount) from (select customernumber, count(*) ordercount from orders group by customernumber) t; +-------------------+ | std(ordercount) | +-------------------+ | 2.831223540955185 | +-------------------+ 1 row in set (0.00 sec)
VAR_POP(expression)
- 计算客户订单数量的总体方差：
mysql> select var_pop(ordercount) from (select customernumber, count(*) ordercount from orders group by customernumber) t; +---------------------+ | var_pop(ordercount) | +---------------------+ | 8.015826738858816 | +---------------------+ 1 row in set (0.00 sec)
其他常用函数
MySQL 提供的函数很丰富，除了前面介绍的字符串函数 、数值函数 、日期函数 、流程函数以外还有很多其他函数。
| 序号 | 函数 | 功能 |
| 1 | DATABASE() | 返回当前数据库名 |
| 2 | VERSION() | 返回当前数据库版本 |
| 3 | USER() | 返回当前登录用户名 |
| 4 | INET_ATON(IP) | 返回 IP 地址的数字表示 |
| 5 | INET_NTOA(num) | 返回数字代表的 IP 地址 |
| 6 | PASSWORD(str) | 返回字符串 str 的加密版本 |
| 7 | MD5() | 返回字符串 str 的 MD5 值 |
- DATABASE()
mysql> select database(); +------------+ | database() | +------------+ | study | +------------+ 1 row in set (0.00 sec)
- VERSION()
mysql> select version(); +------------+ | version() | +------------+ | 5.7.35-log | +------------+ 1 row in set (0.00 sec)
- USER()
mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)
- INET_ATON(IP) 函数：返回 IP 地址的网络字节序表示
mysql> select inet_aton('192.168.1.1'); +--------------------------+ | inet_aton('192.168.1.1') | +--------------------------+ | 3232235777 | +--------------------------+ 1 row in set (0.00 sec)
- INET_NTOA(num) 函数：返回网络字节序代表的 IP 地址
mysql> select inet_ntoa(3232235777); +-----------------------+ | inet_ntoa(3232235777) | +-----------------------+ | 192.168.1.1 | +-----------------------+ 1 row in set (0.00 sec)
INET_ATON(IP) 和 INET_NTOA(num) 函数主要的用途是将字符串的 IP 地址转换为数字表示的网络字节序，这样可以更方便进行 IP 或者网段的比较。
- PASSWORD(str) 函数：返回字符串 str 的加密版本，一个 41 位长的字符串
此函数只用来设置系统用户的密码，但是不能用来对应用的数据加密，如果应用方面有加密的需求，可以使用 MD5 等加密函数来实现
mysql> select password('123456'); +-------------------------------------------+ | password('123456') | +-------------------------------------------+ | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +-------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
- MD5() 函数：返回字符串 str 的 MD5 值，常用来对应用中的数据进行加密
mysql> select md5('123456'); +----------------------------------+ | md5('123456') | +----------------------------------+ | e10adc3949ba59abbe56e057f20f883e | +----------------------------------+ 1 row in set (0.00 sec)
原创文章，转载请注明出处：http://www.opcoder.cn/article/51/