MySQL-函数

一、数学函数

绝对值函数

​ ABS(x)

​ ABS(x) 用于返回 x 的绝对值

mysql> SELECT ABS(2), ABS(-2.3), ABS(-33);
+--------+-----------+----------+
| ABS(2) | ABS(-2.3) | ABS(-33) |
+--------+-----------+----------+
| 2 | 2.3 | 33 |
+--------+-----------+----------+

返回圆周率的函数

​ PI()

​ PI() 返回圆周率值

mysql> SELECT PI();
+----------+
| PI() |
+----------+
| 3.141593 |
+----------+

平方根函数

​ SQRT(x)

​ SQRT(x) 返回非负数 x 的二次方根

mysql> SELECT SQRT(9), SQRT(40), SQRT(-49);
+---------+-------------------+-----------+
| SQRT(9) | SQRT(40) | SQRT(-49) |
+---------+-------------------+-----------+
| 3 | 6.324555320336759 | NULL |
+---------+-------------------+-----------+

求余函数

​ MOD(x,y)

​ MOD(x,y) 返回 x 被 y 除后的余数，MOD(x,y) 对于带有小数部分的数值也起作用，它返回除法运算后的精确余数

mysql> SELECT MOD(31,8), MOD(234,10), MOD(45.5,6);
+-----------+-------------+-------------+
| MOD(31,8) | MOD(234,10) | MOD(45.5,6) |
+-----------+-------------+-------------+
| 7 | 4 | 3.5 |
+-----------+-------------+-------------+

获取整数的函数

​ CEIL(x) 、CEILING(x) 、FLOOR(x)

​ CEIL(x) 用于返回不小于 x 的最小整数值

mysql> SELECT CEIL(-3.35), CEIL(3.35);
+-------------+------------+
| CEIL(-3.35) | CEIL(3.35) |
+-------------+------------+
| -3 | 4 |
+-------------+------------+

​ CEILING(x) 用于返回不小于 x 的最小整数

mysql> SELECT CEILING(-3.35), CEILING(3.35);
+----------------+---------------+
| CEILING(-3.35) | CEILING(3.35) |
+----------------+---------------+
| -3 
 | 4 |
+----------------+---------------+

​ FLOOR(x) 返回不大于 x 的最大整数值

mysql> SELECT FLOOR(-3.35), FLOOR(3.35);
+--------------+-------------+
| FLOOR(-3.35) | FLOOR(3.35) |
+--------------+-------------+
| -4 | 3 |
+--------------+-------------+

获取随机数的函数

​ RAND() 、RAND(x)

​ RAND() 用于返回一个随机浮点值，范围在 0 ~ 1 之间

mysql> SELECT RAND(), RAND(), RAND();
+--------------------+--------------------+---------------------+
| RAND() | RAND() | RAND() |
+--------------------+--------------------+---------------------+
| 0.7393965169222994 | 0.5185907432051289 | 0.37476419599239175 |
+--------------------+--------------------+---------------------+

​ RAND(x) 用于返回一个随机浮点值，范围在 0~1 之间，x 被用作种子值，用来产生重复序列

mysql> SELECT RAND(5), RAND(5), RAND(8);
+---------------------+---------------------+---------------------+
| RAND(5) | RAND(5) | RAND(8) |
+---------------------+---------------------+---------------------+
| 0.40613597483014313 | 0.40613597483014313 | 0.15668530311126755 |
+---------------------+---------------------+---------------------+

四舍五入的函数

​ ROUND(x) 、ROUND(x,y)

​ ROUND(x) 用于对 x 进行四舍五入

mysql> SELECT ROUND(-1.14), ROUND(1.14);
+--------------+-------------+
| ROUND(-1.14) | ROUND(1.14) |
+--------------+-------------+
| -1 | 1 |
+--------------+-------------+

​ ROUND(x,y) 用于对 x 进行四舍五入，并保留小数点后 y 位

mysql> SELECT ROUND(1.38,1), ROUND(1.38,0), ROUND(232.38,-1), ROUND(232.38,-2);
+---------------+---------------+------------------+------------------+
| ROUND(1.38,1) | ROUND(1.38,0) | ROUND(232.38,-1) | ROUND(232.38,-2) |
+---------------+---------------+------------------+------------------+
| 1.4 | 1 | 230 | 200 |
+---------------+---------------+------------------+------------------+

截取数值的函数

​ TRUNCATE(x,y)

​ TRUNCATE(x,y) 用于对 x 进行截取，结果保留小数点后 y 位

mysql> SELECT TRUNCATE(1.31,1), TRUNCATE(1.99,1), TRUNCATE(1.99,0);
+------------------+------------------+------------------+
| TRUNCATE(1.31,1) | TRUNCATE(1.99,1) | TRUNCATE(1.99,0) |
+------------------+------------------+------------------+
| 1.3 | 1.9 | 1 |
+------------------+------------------+------------------+

符号函数

​ SIGN(x)

​ SIGN(x) 用于返回参数 x 的符号，当 x 为负数时返回 -1 ，当 x 为正数时返回 1 ， 当 x 为零时返回 0

mysql> SELECT SIGN(-21), SIGN(0), SIGN(21);
+-----------+---------+----------+
| SIGN(-21) | SIGN(0) | SIGN(21) |
+-----------+---------+----------+
| -1 | 0 | 1 |
+-----------+---------+----------+

幂运算函数

​ POW(x,y) 、POWER(x,y) 、EXP(x)

​ POW(x,y) 用于返回 x 的 y 次方的结果

mysql> SELECT POW(2,4), POW(2,-4);
+----------+-----------+
| POW(2,4) | POW(2,-4) |
+----------+-----------+
| 16 | 0.0625 |
+----------+-----------+

对数运算函数

​ LOG(x) 、LOG10(x)

​ LOG(x) 返回 x 的自然对数

mysql> SELECT LOG(3), LOG(-3);
+--------------------+---------+
| LOG(3) | LOG(-3) |
+--------------------+---------+
| 1.0986122886681098 | NULL |
+--------------------+---------+

​ 使用 LOG10(x) 计算以 10 为基数的对数

mysql> SELECT LOG10(2), LOG10(100), LOG10(-100);
+--------------------+------------+-------------+
| LOG10(2) | LOG10(100) | LOG10(-100) |
+--------------------+------------+-------------+
| 0.3010299956639812 | 2 | NULL |
+--------------------+------------+-------------+

角度与弧度相互转换的函数

​ RADIANS(x) 、DEGREES(x)

​ RADIANS(x) 用于将参数 x 由角度转化为弧度

mysql> SELECT RADIANS(90), RADIANS(180);
+--------------------+-------------------+
| RADIANS(90) | RADIANS(180) |
+--------------------+-------------------+
| 1.5707963267948966 | 3.141592653589793 |
+--------------------+-------------------+

​ DEGREES(x) 用于将参数 x 由弧度转化为角度

mysql> SELECT DEGREES(PI()), DEGREES(PI()/2);
+---------------+-----------------+
| DEGREES(PI()) | DEGREES(PI()/2) |
+---------------+-----------------+
| 180 | 90 |
+---------------+-----------------+

正弦函数和反正弦函数

​ SIN(x) 、ASIN(x)

​ SIN(x) 用于返回 x 的正弦值，其中 x 为弧度值

mysql> SELECT SIN(1), ROUND(SIN(PI()));
+--------------------+------------------+
| SIN(1) | ROUND(SIN(PI())) |
+--------------------+------------------+
| 0.8414709848078965 | 0 |
+--------------------+------------------+

​ ASIN(x) 用于返回 x 的反正弦，即正弦为 x 的值

mysql> SELECT ASIN(0.8414709848078965), ASIN(3);
+--------------------------+---------+
| ASIN(0.8414709848078965) | ASIN(3) |
+--------------------------+---------+
| 1 | NULL |
+--------------------------+---------+

余弦函数和反余弦函数

​ COS(x) 、ACOS(x)

​ COS(x) 用于返回 x 的余弦，其中 x 为弧度值

mysql> SELECT COS(0), COS(PI()), COS(1);
+--------+-----------+--------------------+
| COS(0) | COS(PI()) | COS(1) |
+--------+-----------+--------------------+
| 1 | -1 | 0.5403023058681397 |
+--------+-----------+--------------------+

​ ACOS(x) 用于返回 x 的反余弦，即余弦是 x 的值

mysql> SELECT ACOS(1), ACOS(0), ROUND(ACOS(0.5403023058681397));
+---------+--------------------+---------------------------------+
| ACOS(1) | ACOS(0) | ROUND(ACOS(0.5403023058681397)) |
+---------+--------------------+---------------------------------+
| 0 | 1.5707963267948966 | 1 |
+---------+--------------------+---------------------------------+

正切函数、反正切函数、余切函数

​ TAN(x) 、ATAN(x) 、COT(x)

​ TAN(x) 返回 x 的正切，其中 x 为给定的弧度值

mysql> SELECT TAN(0.3), ROUND(TAN(PI()/4));
+---------------------+--------------------+
| TAN(0.3) | ROUND(TAN(PI()/4)) |
+---------------------+--------------------+
| 0.30933624960962325 | 1 |
+---------------------+--------------------+

​ ATAN(x) 用于返回 x 的反正切，即正切为 x 的值

mysql> SELECT ATAN(0.30933624960962325), ATAN(1);
+---------------------------+--------------------+
| ATAN(0.30933624960962325) | ATAN(1) |
+---------------------------+--------------------+
| 0.3 | 0.7853981633974483 |
+---------------------------+--------------------+

​ COT(x) 用于返回 x 的余切

mysql> SELECT COT(0.3), 1/TAN(0.3), COT(PI()/4);
+--------------------+--------------------+--------------------+
| COT(0.3) | 1/TAN(0.3) | COT(PI()/4) |
+--------------------+--------------------+--------------------+
| 3.2327281437658275 | 3.2327281437658275 | 1.0000000000000002 |
+--------------------+--------------------+--------------------+

二、 字符串函数

计算字符串长度的函数

​ CHAR_LENGTH(str) 、LENGTH(str)

​ CHAR_LENGTH(str) 用于统计str 的字符个数

mysql> SELECT CHAR_LENGTH('date'), CHAR_LENGTH('egg');
+---------------------+--------------------+
| CHAR_LENGTH('date') | CHAR_LENGTH('egg') |
+---------------------+--------------------+
| 4 | 3 |
+---------------------+--------------------+

​ LENGTH(str) 用于统计 str 的字节长度，使用 utf8 编码字符集时，一个汉字是3个字节，一个数字或字母是1个字节

mysql> SELECT LENGTH('date'), LENGTH('egg');
+----------------+---------------+
| LENGTH('date') | LENGTH('egg') |
+----------------+---------------+
| 4 | 3 |
+----------------+---------------+

合并字符串的函数

​ CONCAT(s1,s2,…) 、CONCAT_WS(x,s1,s2,…)

​ CONCAT(s1,s2,…) 用于合并字符串 s1, s2, …，如果有一个参数为 NULL，则结果为 NULL CONCAT_WS(x,s1,s2,…) 以 x 作为分隔符，将 s1, s2, … 合并，如果分隔符为 NULL ，则结果为 NULL

mysql> SELECT CONCAT_WS('-','1st','2nd','3rd'), CONCAT_WS(NULL,'1st','2nd','3rd');
+----------------------------------+-----------------------------------+
| CONCAT_WS('-','1st','2nd','3rd') | CONCAT_WS(NULL,'1st','2nd','3rd') |
+----------------------------------+-----------------------------------+]
| 1st-2nd-3rd | NULL |
+----------------------------------+-----------------------------------+
mysql> SELECT CONCAT('My','SQL','5.7'), CONCAT('My',NULL,'SQL');
+--------------------------+-------------------------+
| CONCAT('My','SQL','5.7') | CONCAT('My',NULL,'SQL') |
+--------------------------+-------------------------+
| MySQL5.7 | NULL |
+--------------------------+-------------------------+

替换字符串的函数

​ INSERT(s1,x,len,s2)

​ (1)INSERT(s1,x,len,s2) 用于返回字符串 s1，其子字符串起始于 x 位置和被字符串 s2 取代的 len 字符

​ (2) 如果 x 超过字符串长度，则返回值为原始字符串，如果 len 的长度大于其他字符串的长度则从位置 x 开始替换

​ (3) 如果任何一个参数为 NULL ，则返回值为 NULL

mysql> SELECT INSERT('Quest', 2, 4, 'What') AS col1,
 -> INSERT('Quest', -1, 4, 'What') AS col2,
 -> INSERT('Quest', 3, 100, 'Wh') AS col3;
+-------+-------+------+
| col1 | col2 | col3 |
+-------+-------+------+
| QWhat | Quest | QuWh |
+-------+-------+------+

转换大小写的函数

​ LOWER(str) 、LCASE(str) 、UPPER(str) 、UCASE(str)

​ LOWER(str) 用于将字符串 str 中的字母字符全部转换成小写字母

mysql> SELECT LOWER('BEAUTIFUL'), LOWER('Well');
+--------------------+---------------+
| LOWER('BEAUTIFUL') | LOWER('Well') |
+--------------------+---------------+
| beautiful | well |
+--------------------+---------------+

​ LCASE(str) 用于将字符串 str 中的字母字符全部转换成小写字母

mysql> SELECT LCASE('BEAUTIFUL'), LCASE('Well');
+--------------------+---------------+
| LCASE('BEAUTIFUL') | LCASE('Well') |
+--------------------+---------------+
| beautiful | well |
+--------------------+---------------+

​ UPPER(str) 用于将 str 中的小写字母转换成大写字母

mysql> SELECT UPPER('black'), UPPER('BLacK');
+----------------+----------------+| UPPER('black') | UPPER('BLacK') |
+----------------+----------------+
| BLACK | BLACK |
+----------------+----------------+

​ UCASE(str) 用于将 str 中的小写字母转换成大写字母

mysql> SELECT UCASE('black'), UCASE('BLacK');
+----------------+----------------+
| UCASE('black') | UCASE('BLacK') |
+----------------+----------------+
| BLACK | BLACK |
+----------------+----------------+

获取指定长度的字符串的函数

​ LEFT(s,n) 、RIGHT(s,n)

​ LEFT(s,n) 用于返回字符串 s 开始的最左边 n 个字符

mysql> SELECT LEFT('football', 5);
+---------------------+
| LEFT('football', 5) |
+---------------------+
| footb |
+---------------------+

​ RIGHT(s,n) 用于返回字符串 s 中右边 n 个字符

mysql> SELECT RIGHT('football',4);
+---------------------+
| RIGHT('football',4) |
+---------------------+
| ball |
+---------------------+

填充字符串的函数

​ LPAD(s1,len,s2) 、RPAD(s1,len,s2)

​ LPAD(s1,len,s2) 返回字符串 s1 ，其左边由字符串 s2填充到 len 字符长度，如果 s1 的长度大于 len ，则返回值被缩短至 len 长度

mysql> SELECT LPAD('hello',4,'??'), LPAD('hello',10,'??');
+----------------------+-----------------------+
| LPAD('hello',4,'??') | LPAD('hello',10,'??') | # 字符串'hello'长度大于4,不需要填充，只被缩短
+----------------------+-----------------------+
| hell | ?????hello | # 字符串'hello'长度小于10,因此被填充
+----------------------+-----------------------+

​ RPAD(s1,len,s2) 返回字符串 s1 ，其右边由字符串 s2 填充到 len 字符长度，如果 s1 的长度大于 len ，则返回值被缩短至 len 长度

mysql> SELECT RPAD('hello',4,'?'), RPAD('hello',10,'?');
+---------------------+----------------------+
| RPAD('hello',4,'?') | RPAD('hello',10,'?') | # 字符串'hello'长度大于4,不需要被填充，因此被缩短至4
+---------------------+----------------------+
| hell | hello????? | # 字符串'hello'长度小于10,因此被填充
+---------------------+----------------------+

删除空格的函数

​ LTRIM(s) 、RTRIM(s) 、TRIM(s)

​ LTRIM(s) 用于删除字符串 s 左侧的空格

mysql> SELECT LTRIM(' book ');
+-----------------+
| LTRIM(' book ') |
+-----------------+
| book |
+-----------------+

​ RTRIM(s) 用于删除字符串 s 右侧的空格

mysql> SELECT RTRIM(' book ');
+-----------------+
| RTRIM(' book ') |
+-----------------+
| book |
+-----------------+

​ TRIM(s) 用于删除字符串 s 两侧的空格

mysql> SELECT TRIM(' book ');
+----------------+
| TRIM(' book ') |
+----------------+
| book |
+----------------+

删除指定字符串的函数

​ TRIM(s1 FROM s)

​ TRIM(s1 FROM s) 用于删除字符串 s 中两端所有的子字符串 s1 ，如果没有指定 s1 ，则默认删除字符串 s 两侧的空格

mysql> SELECT TRIM('xy' FROM 'xyxyabcxy');
+-----------------------------+
| TRIM('xy' FROM 'xyxyabcxy') |
+-----------------------------+
| abc |
+-----------------------------+

重复生成字符串的函数

​ REPEAT(s,n)

​ REPEAT(s,n) 用于重复字符串 s ，n 表示重复多少次

mysql> SELECT REPEAT('mysql',3);
+-------------------+
| REPEAT('mysql',3) |
+-------------------+
| mysqlmysqlmysql |
+-------------------+

空格函数

​ SPACE(n)

​ SPACE(n) 用于返回 n 个空格

mysql> SELECT SPACE(20);
+----------------------+
| SPACE(20) |
+----------------------+
| |
+----------------------+

替换函数

​ REPLACE(s,s1,s2)

​ REPLACE(s,s1,s2) 表示使用字符串 s2 替换字符串 s 中所有的字符串 s1

mysql> SELECT REPLACE('xxx.mysql.com', 'x', 'w');
+------------------------------------+
| REPLACE('xxx.mysql.com', 'x', 'w') |
+------------------------------------+
| www.mysql.com |
+------------------------------------+

比较字符串大小的函数

​ STRCMP(s1,s2)

​ STRCMP(s1,s2) 用于比较字符串 s1 和 s2 的大小，若所有字符串相同则返回 0 ，若第一个字符串大于第二个字符串则返回 1 ，若第一个字符串小于第二个字符串则返回 -1

mysql> SELECT STRCMP('txt', 'txt2'), STRCMP('txt', 'txt');
+-----------------------+----------------------+
| STRCMP('txt', 'txt2') | STRCMP('txt', 'txt') |
+-----------------------+----------------------+
| -1 | 0 |
+-----------------------+----------------------+

获取子字符串的函数

​ SUBSTRING(s,n,len) 、MID(s,n,len)

​ SUBSTRING(s,n,len) 用于获取指定位置的子字符串

mysql> SELECT SUBSTRING('breakfast',5) AS col1, # 从第5个字符串开始获取
 -> SUBSTRING('breakfast',5,3) AS col2, # 从第5个字符串开始，获取3个
 -> SUBSTRING('breakfast',-5) AS col3, # (倒向)从第5个字符串开始获取
 -> SUBSTRING('breakfast',-5,3) AS col4; # (倒向)从第5个字符串开始获取，获取3个
+-------+------+-------+------+
| col1 | col2 | col3 | col4 |
+-------+------+-------+------+
| kfast | kfa | kfast | kfa |
+-------+------+-------+------+

​ MID(s,n,len) 用于获取指定位置的子字符串

mysql> SELECT MID('breakfast',5) AS col1, # 从第5个字符串开始获取
 -> MID('breakfast',5,3) AS col2, # 从第5个字符串开始，获取3个
 -> MID('breakfast',-5) AS col3, # (倒向)从第5个字符串开始获取
 -> MID('breakfast',-5,3) AS col4; # (倒向)从第5个字符串开始获取，获取3个
+-------+------+-------+------+
| col1 | col2 | col3 | col4 |
+-------+------+-------+------+
| kfast | kfa | kfast | kfa |
+-------+------+-------+------+

匹配子字符串开始位置的函数

​ LOCATE(str1,str) 、POSITION(str1 IN str) 、INSTR(str, str1)

​ LOCATE(str1,str) 用于返回字符串 str1 在字符串 str 中的开始位置

mysql> SELECT LOCATE('ball', 'football');
+----------------------------+
| LOCATE('ball', 'football') |
+----------------------------+
| 5 |
+----------------------------+

​ POSITION(str1 IN str) 用于返回字符串 str1 在字符串 str 中的开始位置

mysql> SELECT POSITION('ball' IN 'football');
+--------------------------------+
| POSITION('ball' IN 'football') |
+--------------------------------+
| 5 |
+--------------------------------+

​ INSTR(str, str1) 用于返回子字符串 str1 在字符串 str 中的开始位置

mysql> SELECT INSTR('football', 'ball');
+---------------------------+
| INSTR('football', 'ball') |
+---------------------------+
| 5 |
+---------------------------+

反转字符串的函数

​ REVERSE(s)

​ REVERSE(s) 用于将字符串 s 反转

mysql> SELECT REVERSE('abcd');
+-----------------+
| REVERSE('abcd') |
+-----------------+
| dcba |
+-----------------+

返回指定位置的字符串的函数

​ ELT(n, s1, s2, s3, …)

​ ELT(n, s1, s2, s3, …) 用于返回第 n 个字符串，如果 n超出范围则返回 NULL

mysql> SELECT ELT(3, 'a', 'b', 'c', 'd'), ELT(5, 'a', 'b', 'c', 'd');
+----------------------------+----------------------------+
| ELT(3, 'a', 'b', 'c', 'd') | ELT(5, 'a', 'b', 'c', 'd') |
+----------------------------+----------------------------+
| c | NULL |
+----------------------------+----------------------------+

返回指定字符串位置的函数

​ FIELD(s, s1, s2, …)

​ FIELD(s, s1, s2, …) 用于返回字符串 s 在列表 s1, s2, … 中的位置，如果不存在字符串 s 则返回 0 ，如果字符串 s 是 NULL 也返回 0

mysql> SELECT FIELD('hi', 'hihi', 'hey', 'hi', 'bas');
+-----------------------------------------+
| FIELD('hi', 'hihi', 'hey', 'hi', 'bas') |
+-----------------------------------------+
| 3 |
+-----------------------------------------+

返回子字符串位置的函数

​ FIND_IN_SET(s1, s2)

​ FIND_IN_SET(s1, s2) 用于返回字符串 s1 在字符串列表 s2中的位置

mysql> SELECT FIND_IN_SET('hi', 'hihi,hey,hi,bas'); # 注意s2是一个列表
+--------------------------------------+
| FIND_IN_SET('hi', 'hihi,hey,hi,bas') |
+--------------------------------------+
| 3 |
+--------------------------------------+

三、日期和时间函数

获取当前日期的函数

CURDATE() 、CURRENT_DATE()

CURDATE() 用于获取系统当前日期

mysql> SELECT CURDATE();
+------------+
| CURDATE() |
+------------+
| 2017-05-23 |
+------------+

CURRENT_DATE() 用于系统获取当前日期

mysql> SELECT CURRENT_DATE();
+----------------+
| CURRENT_DATE() |
+----------------+
| 2017-05-23 |
+----------------+

获取当前时间的函数

CURTIME() 、CURRENT_TIME()

CURTIME() 用于获取系统当前时间

mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 20:25:23 |
+-----------+

获取当前日期和时间的函数

CURRENT_TIMESTAMP() 、LOCALTIME() 、NOW() 、SYSDATE()

CURRENT_TIMESTAMP() 用于获取系统当前日期和时间

mysql> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2017-05-23 20:26:48 |
+---------------------+

LOCALTIME() 用于获取系统当前日期和时间

mysql> SELECT LOCALTIME();
+---------------------+
| LOCALTIME() |
+---------------------+
| 2017-05-23 20:27:29 |
+---------------------+

NOW() 用于获取系统当前日期和时间

mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2017-05-23 20:28:10 |
+---------------------+

SYSDATE() 用于获取系统当前日期和时间

mysql> SELECT SYSDATE();
+---------------------+
| SYSDATE() |
+---------------------+
| 2017-05-23 20:29:02 |
+---------------------+

获取时间戳的函数

UNIX_TIMESTAMP()

UNIX_TIMESTAMP() 用于获取 UNIX 格式的时间戳

mysql> SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
| 1495542689 |
+------------------+

转换时间戳的函数

FROM_UNIXTIME()

FROM_UNIXTIME() 用于将 UNIX 格式的时间戳转换为普通格式的时间

mysql> SELECT FROM_UNIXTIME('1495542689');
+-----------------------------+
| FROM_UNIXTIME('1495542689') |
+-----------------------------+
| 2017-05-23 20:31:29 |
+-----------------------------+

获取 UTC 日期的函数

UTC_DATE()

UTC_DATE() 用于获取当前 UTC (世界标准时间) 日期值

mysql> SELECT UTC_DATE();
+------------+
| UTC_DATE() |
+------------+
| 2017-05-23 |
+------------+

获取 UTC 时间的函数

UTC_TIME()

UTC_TIME() 用于获取当前 UTC (世界标准时间) 时间值

mysql> SELECT UTC_TIME();
+------------+
| UTC_TIME() |
+------------+
| 12:36:29 |
+------------+

获取月份的函数

MONTH(date) 、MONTHNAME(date)

MONTH(date) 用于返回 date 对应的月份

mysql> SELECT MONTH('2017-02-23');
+---------------------+
| MONTH('2017-02-23') |
+---------------------+
| 2 |
+---------------------+

MONTHNAME(date) 用于返回 date 对应月份的英文全名

mysql> SELECT MONTHNAME('2017-02-23');
+-------------------------+
| MONTHNAME('2017-02-23') |
+-------------------------+
| February |
+-------------------------+

获取星期的函数

DAYNAME(date) 、DAYOFWEEK(date) 、WEEKDAY(date) 、WEEK(date) 、

WEEKOFYEAR(date) DAYNAME(date) 用于返回 date 对应的工作日的英文名称

mysql> SELECT DAYNAME('2017-02-23');
+-----------------------+
| DAYNAME('2017-02-23') |
+-----------------------+
| Thursday |
+-----------------------+

DAYOFWEEK(date) 用于返回 date 对应的一周中的索引，1 表示周日，2 表示周一，… ，7 表示周六

mysql> SELECT DAYOFWEEK('2017-02-23');
+-------------------------+
| DAYOFWEEK('2017-02-23') |
+-------------------------+
| 5 |
+-------------------------+

WEEKDAY(date) 用于返回日期对应的工作日索引，0 表示周一，1 表示周二，… ，6 表示周日

mysql> SELECT WEEKDAY('2017-05-23');
+-----------------------+
| WEEKDAY('2017-05-23') |
+-----------------------+
| 1 |
+-----------------------+

WEEK(date) 用于计算 date 是一年中的第几周，一年有 53 周

mysql> SELECT WEEK('2017-05-23');
+--------------------+
| WEEK('2017-05-23') |
+--------------------+
| 21 |
+--------------------+

WEEKOFYEAR(date) 用于计算日期 date 是一年中的第几周，一年有 53 周

mysql> SELECT WEEKOFYEAR('2017-05-23');
+--------------------------+
| WEEKOFYEAR('2017-05-23') |
+--------------------------+
| 21 |
+--------------------------+

获取天数的函数

DAYOFYEAR(date) 、DAYOFMONTH(date)

DAYOFYEAR(date) 用于返回 date 是一年中的第几天，一年有 365 天

mysql> SELECT DAYOFYEAR('2017-05-23');
+-------------------------+
| DAYOFYEAR('2017-05-23') |
+-------------------------+
| 143 |
+-------------------------+

DAYOFMONTH(date) 用于计算 date 是一个月中的第几天

mysql> SELECT DAYOFMONTH('2017-05-23');
+--------------------------+
| DAYOFMONTH('2017-05-23') |
+--------------------------+
| 23 |
+--------------------------+

获取年份的函数

YEAR(date)

YEAR(date) 返回 date 对应的年份

mysql> SELECT YEAR('11-02-03'), YEAR('98-02-03');
+------------------+------------------+
| YEAR('11-02-03') | YEAR('98-02-03') |
+------------------+------------------+
| 2011 | 1998 |
+------------------+------------------+

获取季度的函数

QUARTER(date)

QUARTER(date) 返回 date 对应的一年中的季度值

mysql> SELECT QUARTER('17-05-23');
+---------------------+
| QUARTER('17-05-23') |
+---------------------+
| 2 |
+---------------------+

获取分钟的函数

MINUTE(time)

MINUTE(time) 返回 time 对应的分钟值

mysql> SELECT MINUTE('17-02-03 10:10:03');
+-----------------------------+
| MINUTE('17-02-03 10:10:03') |
+-----------------------------+
| 10 |
+-----------------------------+

获取秒钟的函数

SECOND(time)

SECOND(time) 返回 time 对应的秒数

mysql> SELECT SECOND('10:05:03');
+--------------------+
| SECOND('10:05:03') |
+--------------------+
| 3 |
+--------------------+

获取日期的指定值的函数

EXTRACT(type FROM date)

EXTRACT(type FROM date) 用于获取指定的日期值

mysql> SELECT EXTRACT(YEAR FROM '2016-07-02') AS col1, # 当type为YEAR时，只返回年值
 -> EXTRACT(YEAR_MONTH FROM '2016-07-02 01:02:03') AS col2, # 当type为YEAR_MONTH时，返回年与月
 -> EXTRACT(DAY_MINUTE FROM '2016-07-02 01:02:03') AS col3; # 当type为DAY_MINUTE时，返回日、小时、分钟
+------+--------+-------+
| col1 | col2 | col3 |
+------+--------+-------+
| 2016 | 201607 | 20102 |
+------+--------+-------+

时间和秒钟转换的函数

TIME_TO_SEC(time) 、SEC_TO_TIME(time)

TIME_TO_SEC(time) 用于将 time 转换为秒钟，公式为 " 小时3600 + 分钟60 + 秒 "

mysql> SELECT TIME_TO_SEC('23:23:00');
+-------------------------+
| TIME_TO_SEC('23:23:00') |
+-------------------------+
| 84180 |
+-------------------------+

SEC_TO_TIME(time) 用于将秒值转换为时间格式

mysql> SELECT SEC_TO_TIME('84180');
+----------------------+
| SEC_TO_TIME('84180') |
+----------------------+
| 23:23:00 |
+----------------------+

计算日期和时间的函数

DATE_ADD() 、ADDDATE() 、DATE_SUB() 、SUBDATE() 、ADDTIME() 、SUBTIME() 、DATEDIFF()

DATE_ADD() 用于对日期进行加运算，格式为 DATE_ADD(date, INTERVAL expr type)，expr 与 type 的关系

mysql> SELECT DATE_ADD('2010-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND); #对指定的日期增加1分1秒
+---------------------------------------------------------------+
| DATE_ADD('2010-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND) |
+---------------------------------------------------------------+
| 2011-01-01 00:01:00 |
+---------------------------------------------------------------+

DATE_SUB() 用于对日期进行减运算，格式为 DATE_SUB(date, INTERVAL expr type) ，expr 与 type 的关系

mysql> SELECT DATE_SUB('2011-01-02', INTERVAL 31 DAY); # 给指定的日期减去31天
+-----------------------------------------+
| DATE_SUB('2011-01-02', INTERVAL 31 DAY) |
+-----------------------------------------+
| 2010-12-02 |
+-----------------------------------------+

SUBDATE() 用于对日期进行减运算，格式为 SUBDATE(date, INTERVAL expr type) ，expr 与 type 的关系

mysql> SELECT SUBDATE('2011-01-02', INTERVAL 31 DAY); # 对指定的日期减去31天
+----------------------------------------+
| SUBDATE('2011-01-02', INTERVAL 31 DAY) |
+----------------------------------------+
| 2010-12-02 |
+----------------------------------------+

ADDTIME() 用于对日期进行加运算，格式为 ADDTIME(date, expr)

mysql> SELECT ADDTIME('2000-12-31 23:59:59', '1:1:1'); # 给指定的日期增加1时1分1秒
+-----------------------------------------+
| ADDTIME('2000-12-31 23:59:59', '1:1:1') |
+-----------------------------------------+
| 2001-01-01 01:01:00 |
+-----------------------------------------+

SUBTIME() 用于对日期进行减运算，格式为 SUBTIME(date, expr)

mysql> SELECT SUBTIME('2000-12-31 23:59:59', '1:1:1'); # 给指定的日期减去1时1分1秒
+-----------------------------------------+
| SUBTIME('2000-12-31 23:59:59', '1:1:1') |
+-----------------------------------------+
| 2000-12-31 22:58:58 |
+-----------------------------------------+

DATEDIFF() 用于计算两个日期之间的间隔天数

mysql> SELECT DATEDIFF('2017-12-31', '2010-12-31');
+--------------------------------------+
| DATEDIFF('2017-12-31', '2010-12-31') |
+--------------------------------------+
| 2557 |
+--------------------------------------+

将日期和时间格式化的函数

DATE_FORMAT(date, format) 、TIME_FORMAT(time, format) 、GET_FORMAT(val_type, format_type)

DATE_FORMAT(date, format) 用于格式化日期，即根据 format 指定的格式显示 date 值，format 格式

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
+------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y') |
+------------------------------------------------+
| Saturday October 1997 |
+------------------------------------------------+ 

TIME_FORMAT(time, format) 用于格式化时间，即根据 format 指定的格式显示 time 值，format 格式

mysql> SELECT TIME_FORMAT('16:00:00', '%H %k %I');
+-------------------------------------+
| TIME_FORMAT('16:00:00', '%H %k %I') |
+-------------------------------------+
| 16 16 04 |
+-------------------------------------+

GET_FORMAT() ，我们指定值类型和格式化类型，然后会显示成格式字符串

mysql> SELECT DATE_FORMAT('2000-10-05 22:23:00', GET_FORMAT(DATE,'USA'));
+------------------------------------------------------------+
| DATE_FORMAT('2000-10-05 22:23:00', GET_FORMAT(DATE,'USA')) |
+------------------------------------------------------------+
| 10.05.2000 |
+------------------------------------------------------------+

Specifier	Description
%d	该月日期，数字形式（00…31）
%e	该月日期，数字形式（0…31）
%f	微秒（000000…999999）
%H	以2位数表示24小时（00…23）
%h,%I	以2位数表示12小时（01…12）
%i	分钟，数字形式（00-59）
%j	一年中的天数（001-366）
%k	以24小时（0-23）
%l	以12小时（0…12）
%M	月份名称（january…December）
%m	月份数字形式（00…12）
%p	上午（AM）或下午（PM）
%r	时间，12小时制（小时hh：分钟mm：秒钟ss后面加AM或PM）
%S,%s	以2位数形式表示秒（00…59）
%T	时间，24小时制（小时hh：分钟mm：秒数ss）
%U	周（00…53），其中周日为每周的第一天 %u周（00…53），其中周一为每周的第一天
%V	周（01…53），其中周日为每周的第一天，和%X一起使用
%v	周（01…53），其中周一为每周的第一天，和%x一起使用
%W	工作日名称（周日…周六）
%w	一周中的每日（0=周日…6=周六）
%X	该周的年份，其中周日为每周的第一天；数字形式4位数，和%V同时使用
%x	该周的年份，其中周一为每周的第一天；数字形式4位数，和%v同时使用
%Y	4位数形式表示年份
%y	2位数形式表示年份
%%	“%”文字字符

四、条件判断函数

IF()

IF(expr, v1, v2) 如果表达式 expr 为 TRUE ，则返回值为 v1 ，否则返回 v2

mysql> SELECT IF(1>2, 2, 3);
+---------------+
| IF(1>2, 2, 3) |
+---------------+
| 3 |
+---------------+

IFNULL()

IFNULL(v1, v2) ，如果 v1 不为 NULL ，则返回值为 v1 ；如果 v1 为 NULL ，则返回值为 v2

mysql> SELECT IFNULL(1,2), IFNULL(NULL,10);
+-------------+-----------------+
| IFNULL(1,2) | IFNULL(NULL,10) |
+-------------+-----------------+
| 1 | 10 |
+-------------+-----------------+

CASE

CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn] END 含义：如果 expr 等于某个 vn，则返回对应位置 THEN 后面的结果，如果与所有值都不相等，则返回 ELSE 后面的 rn

mysql> SELECT CASE 2 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;
+------------------------------------------------------------+
| CASE 2 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END |
+------------------------------------------------------------+
| two |
+------------------------------------------------------------+