MySQL内置函数举例详解

一、数学函数

(1)绝对值函数：ABS(x)
返回x的绝对值

mysql> select abs(2),abs(-2),abs(0),abs(null);
+--------+---------+--------+-----------+
| abs(2) | abs(-2) | abs(0) | abs(null) |
+--------+---------+--------+-----------+
|      2 |       2 |      0 |      NULL |
+--------+---------+--------+-----------+

(2)返回圆周率的函数：PI()
返回圆周率值

mysql> select pi();
+----------+
| pi()     |
+----------+
| 3.141593 |
+----------+

(3)平方根函数：SQRT(x)
返回非负数x的二次方根

mysql> select sqrt(9),sqrt(-9),sqrt(null),sqrt(0);
+---------+----------+------------+---------+
| sqrt(9) | sqrt(-9) | sqrt(null) | sqrt(0) |
+---------+----------+------------+---------+
|       3 |     NULL |       NULL |       0 |
+---------+----------+------------+---------+

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

mysql> select 9 mod 3,mod(10,3);
+---------+-----------+
| 9 mod 3 | mod(10,3) |
+---------+-----------+
|       0 |         1 |
+---------+-----------+

(5)获取整数的函数：CEIL(x)、CEILING(x)、FLOOR(x)
CEIL(x)用于返回不小于x的最小整数

mysql> select ceil(3.1),ceil(-3.1),ceil(null);
+-----------+------------+------------+
| ceil(3.1) | ceil(-3.1) | ceil(null) |
+-----------+------------+------------+
|         4 |         -3 |       NULL |
+-----------+------------+------------+

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

mysql> select ceiling(3.1),ceiling(-3.1),ceiling(null);
+--------------+---------------+---------------+
| ceiling(3.1) | ceiling(-3.1) | ceiling(null) |
+--------------+---------------+---------------+
|            4 |            -3 |          NULL |
+--------------+---------------+---------------+

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

mysql> select floor(3.1),floor(-3.1),floor(null);
+------------+-------------+-------------+
| floor(3.1) | floor(-3.1) | floor(null) |
+------------+-------------+-------------+
|          3 |          -4 |        NULL |
+------------+-------------+-------------+

(6)获取随机数的函数：RAND()、RAND(x)
RAND()用于返回一个随机浮点值，范围在0~1之间。

mysql> select rand(),rand(),rand();
+--------------------+--------------------+-------------------+
| rand()             | rand()             | rand()            |
+--------------------+--------------------+-------------------+
| 0.8037649568205373 | 0.6277366146703591 | 0.727388233623829 |
+--------------------+--------------------+-------------------+

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 |
+---------------------+---------------------+---------------------+

(7)四舍五入的函数：ROUND(x)、ROUND(x,y)
ROUND(x)用于对x进行四舍五入，不保留小数位。

mysql> select round(1.5),round(1.4);
+------------+------------+
| round(1.5) | round(1.4) |
+------------+------------+
|          2 |          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 |
+---------------+---------------+------------------+------------------+

(8)截取数值的函数：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 |
+------------------+------------------+------------------+

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

mysql> select sign(12),sign(-12),sign(0);
+----------+-----------+---------+
| sign(12) | sign(-12) | sign(0) |
+----------+-----------+---------+
|        1 |        -1 |       0 |
+----------+-----------+---------+

(10)幂运算函数：POW(x,y)、POWER(x,y)、EXP(x)
POW(x,y)用于返回x的y次方的结果

mysql> select pow(3,2),power(2,-2);
+----------+-------------+
| pow(3,2) | power(2,-2) |
+----------+-------------+
|        9 |        0.25 |
+----------+-------------+

EXP(X)是以e为底数的幂函数

mysql> select exp(2),exp(0);
+------------------+--------+
| exp(2)           | exp(0) |
+------------------+--------+
| 7.38905609893065 |      1 |
+------------------+--------+

(11)对数运算函数：LOG(x)、LOG10(x)
LOG(x)返回x的自然对数，以e为底数的对数函数

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        |
+--------------------+------------+-------------+

(12)角度与弧度相互转换的函数：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              |
+---------------+-----------------+

(13)正弦函数和反正弦函数：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    |
+--------------------------+---------+

(14)余弦函数和反余弦函数：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                               |
+---------+--------------------+---------------------------------+

(15)正切函数、反正切函数、余切函数：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 |
+--------------------+--------------------+--------------------+

二、字符串函数

(1)计算字符串长度的函数：CHAR_LENGTH(str)、LENGTH(str)
CHAR_LENGTH(str)用于统计str的字符个数。

mysql> select char_length('你'),char_length('asd');
+--------------------+--------------------+
| char_length('你')  | char_length('asd') |
+--------------------+--------------------+
|                  1 |                  3 |
+--------------------+--------------------+

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

mysql> select length('你'),length('asd');
+---------------+---------------+
| length('你')  | length('asd') |
+---------------+---------------+
|             3 |             3 |
+---------------+---------------+

(2)合并字符串的函数：CONCAT(s1,s2,…)、CONCAT_WS(x,s1,s2,…)
CONCAT(s1,s2,…)用于合并字符串s1,s2,…，如果有一个参数为NULL，则结果为NULL。

mysql> select concat('my','god',null),concat('my','god');
+-------------------------+--------------------+
| concat('my','god',null) | concat('my','god') |
+-------------------------+--------------------+
| NULL                    | mygod              |
+-------------------------+--------------------+

CONCAT_WS(x,s1,s2,…)以x作为分隔符，将s1,s2,…合并，如果分隔符为NULL，则结果为NULL。

mysql> select concat_ws('-','my','god',null),concat_ws(null,'my','god');
+--------------------------------+----------------------------+
| concat_ws('-','my','god',null) | concat_ws(null,'my','god') |
+--------------------------------+----------------------------+
| my-god                         | NULL                       |
+--------------------------------+----------------------------+

(3)替换字符串的函数：INSERT(str,pos,len,newstr)
INSERT(str,pos,len,newstr)用于返回字符串str，其子字符串起始于pos位置和被字符串newstr取代的len字符，如果pos超过字符串长度，则返回值为原始字符串，如果len的长度大于其他字符串的长度则从位置pos开始替换，如果任何一个参数为NULL，则返回值为NULL。

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

(4)转换大小写的函数：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          |
+----------------+----------------+

(5)获取指定长度的字符串的函数：LEFT(s,n)、RIGHT(s,n)
LEFT(s,n)用于返回字符串s开始的最左边n个字符

mysql> select left('test',2), left('test',5);
+----------------+----------------+
| left('test',2) | left('test',5) |
+----------------+----------------+
| te             | test           |
+----------------+----------------+

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

mysql> select right('test',2), right('test',5);
+-----------------+-----------------+
| right('test',2) | right('test',5) |
+-----------------+-----------------+
| st              | test            |
+-----------------+-----------------+

(6)填充字符串的函数：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',7,'*');
+---------------------+---------------------+
| lpad('hello',4,'*') | lpad('hello',7,'*') |
+---------------------+---------------------+
| hell                | **hello             |
+---------------------+---------------------+

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

mysql> select rpad('hello',4,'*'),rpad('hello',7,'*');
+---------------------+---------------------+
| rpad('hello',4,'*') | rpad('hello',7,'*') |
+---------------------+---------------------+
| hell                | hello**             |
+---------------------+---------------------+

(7)删除空格的函数：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               |
+--------------------+

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

mysql> select trim('xy' from 'xyxynixyhaoxy');
+---------------------------------+
| trim('xy' from 'xyxynixyhaoxy') |
+---------------------------------+
| nixyhao                         |
+---------------------------------+

(9)重复生成字符串的函数：REPEAT(s,n)
REPEAT(s,n)用于重复字符串s，n表示重复多少次。

mysql> select repeat('test',4);
+------------------+
| repeat('test',4) |
+------------------+
| testtesttesttest |
+------------------+

(10)空格函数：SPACE(n)
SPACE(n)用于返回n个空格

mysql> select space(10),space(20);
+------------+----------------------+
| space(10)  | space(20)            |
+------------+----------------------+
|            |                      |
+------------+----------------------+

(11)替换函数：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                      |
+------------------------------------+

(12)比较字符串大小的函数：STRCMP(s1,s2)
STRCMP(s1,s2)用于比较字符串s1和s2的大小，若所有字符串相同则返回0，若第一个字符串大于第二个字符串则返回1，若第一个字符串小于第二个字符串则返回-1。(数字小于字母，且字母不分大小写)。

mysql> select strcmp('test','test'),strcmp('ts','t'),strcmp('ts','ts1');
+-----------------------+------------------+--------------------+
| strcmp('test','test') | strcmp('ts','t') | strcmp('ts','ts1') |
+-----------------------+------------------+--------------------+
|                     0 |                1 |                 -1 |
+-----------------------+------------------+--------------------+

(13)获取子字符串的函数：SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len),SUBSTRING(str FROM pos FOR len)、MID(str,pos,len)
用于获取指定位置的子字符串

mysql> select substring('mbgms',4),substring('mbgms' from 4);
+----------------------+---------------------------+
| substring('mbgms',4) | substring('mbgms' from 4) |
+----------------------+---------------------------+
| ms                   | ms                        |
+----------------------+---------------------------+

mysql> select substring('mbgms',-4),substring('mbgms' from -4);
+-----------------------+----------------------------+
| substring('mbgms',-4) | substring('mbgms' from -4) |
+-----------------------+----------------------------+
| bgms                  | bgms                       |
+-----------------------+----------------------------+

mysql> select substring('mbgms',4,2),substring('mbgms' from 4 for 2);
+------------------------+---------------------------------+
| substring('mbgms',4,2) | substring('mbgms' from 4 for 2) |
+------------------------+---------------------------------+
| ms                     | ms                              |
+------------------------+---------------------------------+

mysql> select substring('mbgms',-4,2),substring('mbgms' from -4 for 2);
+-------------------------+----------------------------------+
| substring('mbgms',-4,2) | substring('mbgms' from -4 for 2) |
+-------------------------+----------------------------------+
| bg                      | bg                               |
+-------------------------+----------------------------------+

MID(str,pos,len)用于获取指定位置的子字符串

mysql> select mid('mbgms',4,2),mid('mbgms',-4,2),mid('mbgms' from 4);
+------------------+-------------------+---------------------+
| mid('mbgms',4,2) | mid('mbgms',-4,2) | mid('mbgms' from 4) |
+------------------+-------------------+---------------------+
| ms               | bg                | ms                  |
+------------------+-------------------+---------------------+

(14)匹配子字符串开始位置的函数：LOCATE(substr,str), LOCATE(substr,str,pos)
、POSITION(str1 IN str)、INSTR(str, str1)
LOCATE(str1,str)用于返回字符串str1在字符串str中的开始位置

mysql> select locate('ms','mbgms'),locate('ms','mbgmsms',5);
+----------------------+--------------------------+
| locate('ms','mbgms') | locate('ms','mbgmsms',5) |
+----------------------+--------------------------+
|                    4 |                        6 |
+----------------------+--------------------------+

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

mysql> select position('ms' in 'mbgms');
+---------------------------+
| position('ms' in 'mbgms') |
+---------------------------+
|                         4 |
+---------------------------+

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

mysql> select instr('mbgms','ms');
+---------------------+
| instr('mbgms','ms') |
+---------------------+
|                   4 |
+---------------------+

(15)反转字符串的函数：REVERSE(s)
REVERSE(s)用于将字符串s反转

mysql> select reverse('test');
+-----------------+
| reverse('test') |
+-----------------+
| tset            |
+-----------------+

(16)返回指定位置的字符串的函数：ELT(n, s1, s2, s3, …)
ELT(n, s1, s2, s3, …)用于返回第n个字符串，如果n超出范围则返回NULL。

mysql> select elt(2,'a','b','c'),elt(5,'a','b','c');
+--------------------+--------------------+
| elt(2,'a','b','c') | elt(5,'a','b','c') |
+--------------------+--------------------+
| b                  | NULL               |
+--------------------+--------------------+

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

mysql> select field('ni','hihi','ni'),field(null,'hi'),field('ni','hi','o');
+-------------------------+------------------+----------------------+
| field('ni','hihi','ni') | field(null,'hi') | field('ni','hi','o') |
+-------------------------+------------------+----------------------+
|                       2 |                0 |                    0 |
+-------------------------+------------------+----------------------+

(18)返回子字符串位置的函数：FIND_IN_SET(s1, s2)
FIND_IN_SET(s1, s2)用于返回字符串s1在字符串列表s2中的位置

mysql> SELECT FIND_IN_SET('b','a,c,d'),FIND_IN_SET('b','a,b,c');
+--------------------------+--------------------------+
| FIND_IN_SET('b','a,c,d') | FIND_IN_SET('b','a,b,c') |
+--------------------------+--------------------------+
|                        0 |                        2 |
+--------------------------+--------------------------+

三、日期和时间函数

(1)获取当前日期的函数：CURDATE()、CURRENT_DATE()
CURDATE()用于获取系统当前日期

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2020-12-05 |
+------------+

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

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2020-12-05     |
+----------------+

(2)获取当前时间的函数：CURTIME()、CURRENT_TIME()

mysql> select curtime(),current_time();
+-----------+----------------+
| curtime() | current_time() |
+-----------+----------------+
| 16:26:05  | 16:26:05       |
+-----------+----------------+

(3)获取当前日期和时间的函数：CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()
CURRENT_TIMESTAMP()用于获取系统当前日期和时间

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2020-12-05 16:27:27 |
+---------------------+

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

mysql> select localtime();
+---------------------+
| localtime()         |
+---------------------+
| 2020-12-05 16:28:14 |
+---------------------+

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

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-12-05 16:29:18 |
+---------------------+

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

mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2020-12-05 16:29:45 |
+---------------------+

(4)获取时间戳的函数：UNIX_TIMESTAMP()
UNIX_TIMESTAMP()用于获取UNIX格式的时间戳

mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|       1607157038 |
+------------------+

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

mysql> select from_unixtime('1607157038');
+-----------------------------+
| from_unixtime('1607157038') |
+-----------------------------+
| 2020-12-05 16:30:38.000000  |
+-----------------------------+

(6)获取UTC日期的函数：UTC_DATE()
UTC_DATE()用于获取当前UTC(世界标准时间)日期值

mysql> select utc_date();
+------------+
| utc_date() |
+------------+
| 2020-12-05 |
+------------+

(7)获取UTC时间的函数：UTC_TIME()
UTC_TIME()用于获取当前UTC(世界标准时间)时间值

mysql> select utc_time();
+------------+
| utc_time() |
+------------+
| 08:33:06   |
+------------+

(8)获取月份的函数：MONTH(date)、MONTHNAME(date)
MONTH(date)用于返回date对应的月份

mysql> select month('2020-12-05');
+---------------------+
| month('2020-12-05') |
+---------------------+
|                  12 |
+---------------------+

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

mysql> select monthname('2020-12-05');
+-------------------------+
| monthname('2020-12-05') |
+-------------------------+
| December                |
+-------------------------+

(9)获取星期的函数：DAYNAME(date)、DAYOFWEEK(date)、WEEKDAY(date)、WEEK(date)、WEEKOFYEAR(date)
DAYNAME(date)用于返回date对应的工作日的英文名称

mysql> select dayname('2020-12-05');
+-----------------------+
| dayname('2020-12-05') |
+-----------------------+
| Saturday              |
+-----------------------+

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

mysql> select dayofweek('2020-12-05');
+-------------------------+
| dayofweek('2020-12-05') |
+-------------------------+
|                       7 |
+-------------------------+

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

mysql> select weekday('2020-12-05');
+-----------------------+
| weekday('2020-12-05') |
+-----------------------+
|                     5 |
+-----------------------+

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

mysql> select week('2020-12-05');
+--------------------+
| week('2020-12-05') |
+--------------------+
|                 48 |
+--------------------+

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

mysql> select weekofyear('2020-12-05');
+--------------------------+
| weekofyear('2020-12-05') |
+--------------------------+
|                       49 |
+--------------------------+

(10)获取天数的函数：DAYOFYEAR(date)、DAYOFMONTH(date)
DAYOFYEAR(date)用于返回date是一年中的第几天，一年有365天

mysql> select dayofyear('2020-12-05');
+-------------------------+
| dayofyear('2020-12-05') |
+-------------------------+
|                     340 |
+-------------------------+

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

mysql> select dayofmonth('2020-12-05');
+--------------------------+
| dayofmonth('2020-12-05') |
+--------------------------+
|                        5 |
+--------------------------+

(11)获取年份的函数：YEAR(date)
YEAR(date)返回date对应的年份

mysql> select year('11-02-03'),year('98-02-03'),year(now());
+------------------+------------------+-------------+
| year('11-02-03') | year('98-02-03') | year(now()) |
+------------------+------------------+-------------+
|             2011 |             1998 |        2020 |
+------------------+------------------+-------------+

(12)获取季度的函数：QUARTER(date)
QUARTER(date)返回date对应的一年中的季度值

mysql> select quarter('2020-02-03'),quarter(now());
+-----------------------+----------------+
| quarter('2020-02-03') | quarter(now()) |
+-----------------------+----------------+
|                     1 |              4 |
+-----------------------+----------------+

(13)获取分钟的函数：MINUTE(time)
MINUTE(time)返回time对应的分钟值

mysql> select minute('2020-02-03 10:20:10');
+-------------------------------+
| minute('2020-02-03 10:20:10') |
+-------------------------------+
|                            20 |
+-------------------------------+

(14)获取秒钟的函数：SECOND(time)
SECOND(time)返回time对应的秒数

mysql> select second('2020-02-03 10:20:10');
+-------------------------------+
| second('2020-02-03 10:20:10') |
+-------------------------------+
|                            10 |
+-------------------------------+

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

1.当type为YEAR时，只返回年值;

2.当type为YEAR_MONTH时，返回年与月;

3.当type为DAY_MINUTE时，返回日、时、分;

mysql> SELECT EXTRACT(YEAR FROM '2016-07-02') AS col1,
    -> EXTRACT(YEAR_MONTH FROM '2016-07-02 01:02:03') AS col2,
    -> EXTRACT(DAY_MINUTE FROM '2016-07-02 01:02:03') AS col3;
+------+--------+-------+
| col1 | col2   | col3  |
+------+--------+-------+
| 2016 | 201607 | 20102 |
+------+--------+-------+

(16)时间和秒钟转换的函数：TIME_TO_SEC(time)、SEC_TO_TIME(time) TIME_TO_SEC(time)用于将time转换为秒钟，公式为"小时3600+分钟60+秒"

mysql> select time_to_sec('12:01:20');
+-------------------------+
| time_to_sec('12:01:20') |
+-------------------------+
|                   43280 |
+-------------------------+

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

mysql> select sec_to_time('43280');
+----------------------+
| sec_to_time('43280') |
+----------------------+
| 12:01:20.000000      |
+----------------------+

(17)计算日期和时间的函数：DATE_ADD()、ADDDATE()、DATE_SUB()、SUBDATE()、ADDTIME()、SUBTIME()、DATEDIFF()
DATE_ADD()用于对日期进行加运算，格式为DATE_ADD(date, INTERVAL expr type)，expr与type的关系

mysql> select date_add('2020-12-08 12:05:30',interval 12 day);
+-------------------------------------------------+
| date_add('2020-12-08 12:05:30',interval 12 day) |
+-------------------------------------------------+
| 2020-12-20 12:05:30                             |
+-------------------------------------------------+

mysql> select date_add('2020-12-08 12:05:30',interval '1:1' minute_second);
+--------------------------------------------------------------+
| date_add('2020-12-08 12:05:30',interval '1:1' minute_second) |
+--------------------------------------------------------------+
| 2020-12-08 12:06:31                                          |
+--------------------------------------------------------------+

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

mysql> select date_sub('2020-12-08 12:05:30',interval 2 day);
+------------------------------------------------+
| date_sub('2020-12-08 12:05:30',interval 2 day) |
+------------------------------------------------+
| 2020-12-06 12:05:30                            |
+------------------------------------------------+

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

mysql> select subdate('2020-12-08 12:05:30',interval 2 day);
+-----------------------------------------------+
| subdate('2020-12-08 12:05:30',interval 2 day) |
+-----------------------------------------------+
| 2020-12-06 12:05:30                           |
+-----------------------------------------------+

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

mysql> select addtime('12:20:20','1:20:10');
+-------------------------------+
| addtime('12:20:20','1:20:10') |
+-------------------------------+
| 13:40:30                      |
+-------------------------------+

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

mysql> select subtime('12:20:20','1:20:10');
+-------------------------------+
| subtime('12:20:20','1:20:10') |
+-------------------------------+
| 11:00:10                      |
+-------------------------------+

DATEDIFF()用于计算两个日期之间的间隔天数(前面的日期减去后面的日期)

mysql> select datediff('2020-12-08','2020-12-01');
+-------------------------------------+
| datediff('2020-12-08','2020-12-01') |
+-------------------------------------+
|                                   7 |
+-------------------------------------+

(18)将日期和时间格式化的函数：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('2020-12-08 12:30:20','%W %M %Y');
+-----------------------------------------------+
| date_format('2020-12-08 12:30:20','%W %M %Y') |
+-----------------------------------------------+
| Tuesday December 2020                         |
+-----------------------------------------------+

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

mysql> select time_format('13:30:20','%H %k %I');
+------------------------------------+
| time_format('13:30:20','%H %k %I') |
+------------------------------------+
| 13 13 01                           |
+------------------------------------+

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

mysql> select date_format('2020-12-08 12:30:30',get_format(date,'usa'));
+-----------------------------------------------------------+
| date_format('2020-12-08 12:30:30',get_format(date,'usa')) |
+-----------------------------------------------------------+
| 12.08.2020                                                |
+-----------------------------------------------------------+

日期时间参数

%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位数形式表示年份;

%%:“%”文字字符。

四、条件判断函数

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

mysql> select if(true,2,3),if(false,2,3),if(2>3,2,3),if(2<3,2,3);
+--------------+---------------+-------------+-------------+
| if(true,2,3) | if(false,2,3) | if(2>3,2,3) | if(2<3,2,3) |
+--------------+---------------+-------------+-------------+
|            2 |             3 |           3 |           2 |
+--------------+---------------+-------------+-------------+

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

mysql> select ifnull(1,2),ifnull(null,2);
+-------------+----------------+
| ifnull(1,2) | ifnull(null,2) |
+-------------+----------------+
|           1 |              2 |
+-------------+----------------+

(3)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 'none' end; 
+------------------------------------------------------------+
| case 2 when 1 then 'one' when 2 then 'two' else 'none' end |
+------------------------------------------------------------+
| two                                                        |
+------------------------------------------------------------+

mysql> select case 3 when 1 then 'one' when 2 then 'two' else 'none' end;
+------------------------------------------------------------+
| case 3 when 1 then 'one' when 2 then 'two' else 'none' end |
+------------------------------------------------------------+
| none                                                       |
+------------------------------------------------------------+

五、系统信息函数

(1)获取MySQL版本号的函数：VERSION()
VERSION()用于获取MySQL版本号

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.32    |
+-----------+

(2)查看当前用户的连接数的ID函数：CONNECTION_ID()
CONNECTION_ID()用于查看当前用户的连接数

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               2 |
+-----------------+

mysql> show processlist;  #查看当前用户的连接信息
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
|  2 | root | localhost | NULL | Query   |    0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+

参数解释

1.Id：用户登录MySQL时，系统分配的连接id;

2.User：当前连接的用户;

3.Host：显示这个语句是从哪个IP的哪个端口上发出的，可以用来追踪出现问题语句的用户;

4.db：显示这个进程目前连接的是哪个数据库;

5.Command：显示当前连接执行的命令，一般取值为休眠(Sleep)、查询(Query)、连接(Connect);

6.Time：显示这个状态持续的时间，单位是秒;

7.State：显示使用当前连接的SQL语句的状态;

8.Info：显示这个SQL语句;

(3)查看当前使用的数据库的函数：DATABASE()、SCHEMA()
DATABASE()用于查看当前使用的数据库

mysql> use mysql;
Database changed

mysql> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+

SCHEMA()用于查看当前使用的数据库

mysql> select schema();
+----------+
| schema() |
+----------+
| mysql    |
+----------+

(4)查看当前登录的用户名的函数：USER()、CURRENT_USER()、SYSTEM_USER()
USER()返回当前登录的用户及主机名

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+

CURRENT_USER()用于返回当前登录的用户及主机名

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+

SYSTEM_USER()用于返回当前登录的用户及主机名

mysql> select system_user();
+----------------+
| system_user()  |
+----------------+
| root@localhost |
+----------------+

(5)查看指定字符串的字符集的函数：CHARSET(str)
CHARSET(str)用于查看字符串str的字符集

mysql> select charset('asd');
+----------------+
| charset('asd') |
+----------------+
| utf8           |
+----------------+

(6)查看指定字符串的排列方式的函数：COLLATION(str)
COLLATION(str)用于查看字符串str的字符排列方式

mysql> select collation('asd');
+------------------+
| collation('asd') |
+------------------+
| utf8_general_ci  |
+------------------+

(7)获取最后一个自动生成的ID值的函数：LAST_INSERT_ID()
LAST_INSERT_ID()用于获取最后一个自动生成的ID值

mysql> create table worker
    -> (
    -> id int auto_increment primary key,
    -> name varchar(20)
    -> );

mysql> insert into worker(name) values('snow');

mysql> insert into worker(name) values('tom');

mysql> select * from worker;
+----+------+
| id | name |
+----+------+
| 1 | snow  |
| 2 | tom   |
+----+------+

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 2                |
+------------------+

mysql> insert into worker(name) values('joy'),('kana');

mysql> select * from worker;
+----+--------+
| id | name   | 
+----+--------+
| 1 | snow    |
| 2 | tom     | 
| 3 | joy     |
| 4 | kana    |
+----+--------+

mysql> select last_insert_id();
+------------------+
| last_insert_id() | 
+------------------+
| 3                |
+------------------+

六、加/解密函数

(1)加密函数：PASSWORD(str)、MD5(str)、ENCODE(str, pswd_str)
PASSWORD(str)从明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。

mysql> select password('newpwd');
+-------------------------------------------+
| password('newpwd')                        |
+-------------------------------------------+
| *1FA85AA204CC12B39B20E8F1E839D11B3F9E6AA4 |
+-------------------------------------------+

MD5(str)为字符串str算出一个MD5 128比特校验和

mysql> select md5('newpwd');
+----------------------------------+
| md5('newpwd')                    |
+----------------------------------+
| a5e3094ce553e08de5ba237525b106d5 |
+----------------------------------+

ENCODE(str, pswd_str)使用pswd_str作为密码，加密str。

mysql> select encode('secret','newpwd');
+---------------------------+
| encode('secret','newpwd') |
+---------------------------+
| ��E�E                       |
+---------------------------+

(2)解密函数：DECODE(crypt_str, pswd_str)
DECODE(crypt_str, pswd_str)使用pswd_str作为密码，解密加密字符串crypt_str。

mysql> select decode(encode('secret','cry'),'cry');
+--------------------------------------+
| decode(encode('secret','cry'),'cry') |
+--------------------------------------+
| secret                               |
+--------------------------------------+

七、其他函数

(1)格式化函数：FORMAT(x, n)
FORMAT(x, n)将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串的形式返回。

mysql> select format(1.23456,4),format(1.2,4),format(1.234,0);
+-------------------+---------------+-----------------+
| format(1.23456,4) | format(1.2,4) | format(1.234,0) |
+-------------------+---------------+-----------------+
| 1.2346            | 1.2000        | 1               |
+-------------------+---------------+-----------------+

(2)不同进制的数字进行转换的函数：CONV(N,from_base,to_base)
CONV(N,from_base,to_base)用于不同进制数之间的转换

mysql> select conv('a',16,2),conv(15,10,2),conv(15,10,8),conv(15,10,16);
+----------------+---------------+---------------+----------------+
| conv('a',16,2) | conv(15,10,2) | conv(15,10,8) | conv(15,10,16) |
+----------------+---------------+---------------+----------------+
| 1010           | 1111          | 17            | F              |
+----------------+---------------+---------------+----------------+

(3)IP地址与数字互相转换的函数：INET_ATON(expr)、INET_NTOA(expr)
INET_ATON(expr)用于将网络地址转换为一个代表该地址数值的整数。

mysql> select inet_aton('192.168.229.220');
+------------------------------+
| inet_aton('192.168.229.220') |
+------------------------------+
|                   3232294364 |
+------------------------------+

INET_NTOA(expr)用于将地址数值转换为网络地址。

mysql> select inet_ntoa(3232294364);
+-----------------------+
| inet_ntoa(3232294364) |
+-----------------------+
| 192.168.229.220       |
+-----------------------+

(4)加锁函数和解锁函数：GET_LOCK(str, timeout)、RELEASE_LOCK(str)、IS_FREE_LOCK(str)、IS_USED_LOCK(str)
GET_LOCK(str, timeout)使用字符串str来得到一个锁，持续时间timeout秒。
1.若成功得到锁，则返回1;
2.若操作超时，则返回0;
3.若发生错误，则返回NULL;

mysql> select get_lock('lock',10);
+---------------------+
| get_lock('lock',10) |
+---------------------+
|                   1 |
+---------------------+

RELEASE_LOCK(str)用于解开被GET_LOCK()获取的，用字符串str所命名的锁。
1.若锁被解开，则返回1;
2.若该线程尚未创建锁，则返回0;
3.若命名的锁不存在，则返回NULL;
4.若该锁从未被GET_LOCK()的调用获取，或锁已经被提前解开，则该锁不存在。

mysql> select release_lock('lock');
+----------------------+
| release_lock('lock') |
+----------------------+
|                    1 |
+----------------------+

IS_FREE_LOCK(str)检查名为str的锁是否可以使用
1.若锁可以使用，则返回1;
2.若锁正在被使用，则返回0;
3.若出现错误，则返回NULL;

mysql> select is_free_lock('lock');
+----------------------+
| is_free_lock('lock') |
+----------------------+
|                    0 |
+----------------------+

IS_USED_LOCK(str)用于检查名为str的锁是否正在被使用，若被封锁，则返回使用该锁的客户端的连接标识符，否则返回NULL。

mysql> select is_used_lock('lock');
+----------------------+
| is_used_lock('lock') |
+----------------------+
|                    2 |
+----------------------+
#返回结果为当前连接ID，表示名称为'lock'的锁正在被使用;

(5)重复执行指定操作的函数：BENCHMARK(count, expr)
BENCHMARK(count, expr)用于重复count次执行表达式expr
1.可以用于计算MySQL处理表达式的速度;
2.可以在MySQL客户端内部报告语句执行的时间;

mysql> select password('newpwd');
+-------------------------------------------+
| password('newpwd')                        |
+-------------------------------------------+
| *1FA85AA204CC12B39B20E8F1E839D11B3F9E6AA4 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
#执行1次加密操作花费了0.00秒;

mysql> select benchmark(500000,password('newpwd'));
+--------------------------------------+
| benchmark(500000,password('newpwd')) |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set, 1 warning (0.08 sec)
#执行500000次加密操作花费了0.08秒;

(6)改变字符集的函数：CONVERT(expr USING transcoding_name)
CONVERT(expr USING transcoding_name)用于改变字符串的默认字符集

mysql> select charset('asd');
+----------------+
| charset('asd') |
+----------------+
| utf8           |
+----------------+
#默认是utf8字符集;

mysql> select charset(convert('asd' using latin1));
+--------------------------------------+
| charset(convert('asd' using latin1)) |
+--------------------------------------+
| latin1                               |
+--------------------------------------+
#转换成latin1字符集;

(7)改变数据类型的函数：CAST(expr AS type)、CONVERT(expr,type)

CAST(expr AS type)用于将一个数据类型的值转换为另一个数据类型的值。

型，结果为'10'
mysql> select cast(100 as char(2));
+----------------------+
| cast(100 as char(2)) |
+----------------------+
| 10                   |
+----------------------+
#将整数类型100转换为带有两个显示宽度的字符串类;

CONVERT(expr,type)用于将一个数据类型的值转换为另一个数据类型的值。

mysql> select convert(100,char(2));
+----------------------+
| convert(100,char(2)) |
+----------------------+
| 10                   |
+----------------------+
#将整数类型100转换为带有两个显示宽度的字符串类;