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转换为带有两个显示宽度的字符串类;
