MySQL函数

dongsq007

已于 2023-05-04 14:46:22 修改

阅读量271

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 sql

于 2023-05-02 11:03:09 首次发布

本文链接：https://blog.csdn.net/weixin_38441454/article/details/130452086

版权

MySQL 专栏收录该内容

5 篇文章 1 订阅

订阅专栏

概述

MySQL提供了众多功能强大、方便易用的函数，包括数学函数、字符串函数、日期时间函数、条件判断函数、系统信息函数和加密函数等其他函数。

MySQL函数示例

1 数学函数

数学函数主要用来处理数值数据，主要的数学函数有绝对值函数、三角函数（包括正弦函数、余弦函数、正切函数、余切函数）、对数函数、随机数函数，在有错误产生时，数学函数将会返回空值NULL。

1）绝对值函数ABS(x)和返回圆周率的函数PI()

PI()函数默认返回小数点后6位。

mysql>  select abs(2),abs(-5.5),abs(100),pi();
+--------+-----------+----------+----------+
| abs(2) | abs(-5.5) | abs(100) | pi()     |
+--------+-----------+----------+----------+
|      2 |       5.5 |      100 | 3.141593 |
+--------+-----------+----------+----------+
1 row in set (0.00 sec)

2）平方根函数SQRT(x) 和求余函数MOD(x,y)

MOD(x,y)返回x被y除后的余数，MOD()对于带小数部分的数值也起作用

求9、40、-49的二次平方根，负数没有平方根，返回NULL

mysql> select sqrt(9),sqrt(40),sqrt(-49);
+---------+-------------------+-----------+
| sqrt(9) | sqrt(40)          | sqrt(-49) |
+---------+-------------------+-----------+
|       3 | 6.324555320336759 |      NULL |
+---------+-------------------+-----------+
1 row in set (0.00 sec)

对（31,8）、（234,10）、（45.5,6）进行求余运算

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 |
+-----------+-------------+-------------+
1 row in set (0.00 sec)

3）获取整数的函数CEIL(x)、CEILING(x)和FLOOR(x)

CEIL(x)和CEILING(x)意义相同，返回不小于x的最小整数值，返回值转化为一个BIGINT，FLOOR(x)返回不大于x的最大整数值，返回值转换为一个BIGINT

mysql> select CEIL(-3.35),CEILING(3.35),FLOOR(-3.35),FLOOR(3.35);
+-------------+---------------+--------------+-------------+
| CEIL(-3.35) | CEILING(3.35) | FLOOR(-3.35) | FLOOR(3.35) |
+-------------+---------------+--------------+-------------+
|          -3 |             4 |           -4 |           3 |
+-------------+---------------+--------------+-------------+
1 row in set (0.00 sec)

4）获取随机数的函数RAND()和RAND(x)

RAND(x)返回一个随机浮点数v，范围在0到1之间，不带参数的RAND()每次产生的随机数是不同的。

使用RAND()函数产生随机数

mysql> select rand(),rand(),rand();
+----------------------+--------------------+--------------------+
| rand()               | rand()             | rand()             |
+----------------------+--------------------+--------------------+
| 0.026402477199586553 | 0.8923876433562372 | 0.3827308159160715 |
+----------------------+--------------------+--------------------+
1 row in set (0.00 sec)

使用RAND(x)产生随机数，当RAND(x)参数相同时，将产生相同的随机数，不同的x产生的随机数不同

mysql> select rand(10),rand(10),rand(11);
+--------------------+--------------------+-------------------+
| rand(10)           | rand(10)           | rand(11)          |
+--------------------+--------------------+-------------------+
| 0.6570515219653505 | 0.6570515219653505 | 0.907234631392392 |
+--------------------+--------------------+-------------------+
1 row in set (0.01 sec)

5）函数ROUND(x)、ROUND(x,y)、TRUNCATE(x,y)

ROUND(x)返回最接近于参数x的整数，对x值进行四舍五入；使用ROUND(x,y)函数对操作数进行四舍五入操作，结果保留小数点后面指定y位；TRUNCATE(x,y)返回被舍去至小数点后y位的数字x，若y的值为0，则结果不带有小数点或不带有小数部分，若y为负数，则截去x小数点左起第y位开始后面所有低位的值。

使用ROUND(x)，ROUND(x,y)进行四舍五入操作

mysql> select round(-1.14),round(1.14), round(1.14,1),round(1.14,0);
+--------------+-------------+---------------+---------------+
| round(-1.14) | round(1.14) | round(1.14,1) | round(1.14,0) |
+--------------+-------------+---------------+---------------+
|           -1 |           1 |           1.1 |             1 |
+--------------+-------------+---------------+---------------+
1 row in set (0.00 sec)

使用TRUNCATE(x,y)对操作数进行截取

mysql> select truncate(1.31,1),truncate(1.99,1),truncate(1.99,0),truncate(19.99,-1);
+------------------+------------------+------------------+--------------------+
| truncate(1.31,1) | truncate(1.99,1) | truncate(1.99,0) | truncate(19.99,-1) |
+------------------+------------------+------------------+--------------------+
|              1.3 |              1.9 |                1 |                 10 |
+------------------+------------------+------------------+--------------------+
1 row in set (0.00 sec)

6）符号函数SIGN(x)

SIGN(x)返回参数的符号，x的值为负、零或正时返回结果一次为-1、0、1

mysql> select sign(-21), sign(0),sign(21);
+-----------+---------+----------+
| sign(-21) | sign(0) | sign(21) |
+-----------+---------+----------+
|        -1 |       0 |        1 |
+-----------+---------+----------+
1 row in set (0.00 sec)

7）幂运算函数POW(x,y)、POWER(x,y)和EXP(x)

POW(x,y)和 POWER(x,y)返回x的y次方的结果值；EXP(x)返回e的x乘方后的值

mysql> select pow(2,2),power(2,3),exp(3),exp(-3),exp(0);
+----------+------------+--------------------+----------------------+--------+
| pow(2,2) | power(2,3) | exp(3)             | exp(-3)              | exp(0) |
+----------+------------+--------------------+----------------------+--------+
|        4 |          8 | 20.085536923187668 | 0.049787068367863944 |      1 |
+----------+------------+--------------------+----------------------+--------+
1 row in set (0.00 sec)

8）对数运算函数LOG(x)和LOG10(x)

mysql> select log(3),log(-3),log10(2),log10(100),log10(-100);
+--------------------+---------+--------------------+------------+-------------+
| log(3)             | log(-3) | log10(2)           | log10(100) | log10(-100) |
+--------------------+---------+--------------------+------------+-------------+
| 1.0986122886681098 |    NULL | 0.3010299956639812 |          2 |        NULL |
+--------------------+---------+--------------------+------------+-------------+
1 row in set, 2 warnings (0.00 sec)

9）角度与弧度相互转换的函数RADIANS(x)和DEGREES(x)

RADIANS(x)将参数x由角度转换为弧度，DEGREES(x)将参数x由弧度转换为角度

mysql> select radians(90),radians(180),degrees(PI()),degrees(PI()/2);
+--------------------+-------------------+---------------+-----------------+
| radians(90)        | radians(180)      | degrees(PI()) | degrees(PI()/2) |
+--------------------+-------------------+---------------+-----------------+
| 1.5707963267948966 | 3.141592653589793 |           180 |              90 |
+--------------------+-------------------+---------------+-----------------+
1 row in set (0.00 sec)

10）正弦函数SIN(x)和反正弦函数ASIN(x)

mysql> select sin(1),round(sin(pi())),asin(0.8414709848078965),asin(3);
+--------------------+------------------+--------------------------+---------+
| sin(1)             | round(sin(pi())) | asin(0.8414709848078965) | asin(3) |
+--------------------+------------------+--------------------------+---------+
| 0.8414709848078965 |                0 |                        1 |    NULL |
+--------------------+------------------+--------------------------+---------+
1 row in set (0.00 sec)

11）余弦函数COS(x)和反余弦函数ACOS(x)

mysql> select cos(0),cos(1),acos(1),acos(0);
+--------+--------------------+---------+--------------------+
| cos(0) | cos(1)             | acos(1) | acos(0)            |
+--------+--------------------+---------+--------------------+
|      1 | 0.5403023058681398 |       0 | 1.5707963267948966 |
+--------+--------------------+---------+--------------------+
1 row in set (0.00 sec)

12）正切函数TAN(x)、反正切函数ATAN(x)、余切函数COT(x)

mysql> select tan(0.3),round(tan(pi()/4)),atan(1),cot(0.3);
+---------------------+--------------------+--------------------+--------------------+
| tan(0.3)            | round(tan(pi()/4)) | atan(1)            | cot(0.3)           |
+---------------------+--------------------+--------------------+--------------------+
| 0.30933624960962325 |                  1 | 0.7853981633974483 | 3.2327281437658275 |
+---------------------+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)

2 字符串函数

字符串函数主要用来处理数据库中的字符串数据。MySQL中的字符串函数有计算字符串长度函数、字符串合并函数、字符串替换函数、字符串比较函数、查找指定字符串位置函数等。

1）计算字符串字符数的函数和字符串长度的函数

CHAR_LENGTH(str)返回值为字符串str所包含的字符个数。

mysql> select char_length('date');
+---------------------+
| char_length('date') |
+---------------------+
|                   4 |
+---------------------+
1 row in set (0.00 sec)

2）合并字符串函数CONCAT(s1,s2,...)、CONCAT_WS(x,s1,s2,...)

CONCAT(s1,s2,...)返回结果为连接参数产生的字符串，或许有一个或多个参数。如果有任何一个参数为NULL，则返回值为NULL。如果所有参数均为非二进制字符串，则结果为非二进制字符串。如果变量中含有任一二进制字符串，则结果为一个二进制字符串。

mysql> select concat('MySQL','8.0'),concat('My',NULL,'SQL');
+-----------------------+-------------------------+
| concat('MySQL','8.0') | concat('My',NULL,'SQL') |
+-----------------------+-------------------------+
| MySQL8.0              | NULL                    |
+-----------------------+-------------------------+
1 row in set (0.00 sec)

在CONCAT_WS(x,s1,s2,...)中，CONCAT_WS是CONCAT()的一种特殊形式，第一个参数x是其他参数的分隔符，分隔符的位置放在要连接的两个字符串之间，分割的字符串可以是一个字符串，也可以是其他参数，如果分隔符为NULL，则结果为NULL，函数会忽略任何分隔符参数后的NULL值。

mysql> select concat_ws('-','lst','2nd', '3rd'), concat_ws('*','lst', NULL,'3rd');
+-----------------------------------+----------------------------------+
| concat_ws('-','lst','2nd', '3rd') | concat_ws('*','lst', NULL,'3rd') |
+-----------------------------------+----------------------------------+
| lst-2nd-3rd                       | lst*3rd                          |
+-----------------------------------+----------------------------------+
1 row in set (0.01 sec)

3）替换字符串的函数INSERT(s1,x,len,s2)

INSERT(s1,x,len,s2)返回字符串s1,其子字符串起始于x位置和被字符串s2取代的len字符。如果x超过字符串长度，则返回值为原始字符串。假如len的长度大于其他字符串的长度，则从位置x开始替换。若任何一个参数为NULL，则返回值为NULL。

mysql> select insert('Quest',2,4,'What') as coll, insert('Quest',-1,4,'What') as col2, insert('Quest',3,100,'Wh') as col3;
+-------+-------+------+
| coll  | col2  | col3 |
+-------+-------+------+
| QWhat | Quest | QuWh |
+-------+-------+------+
1 row in set (0.00 sec)

4）字母大小写转换函数

LOWER(str)或者LCASE(str) 可以将字符串str中的字母字符全部转换为小写字母，UPPER(str)或者UCASE(str)可以将字符串str中的字母字符全部转换成大写字母。

mysql> select lower('BEAUTIFUL'),lcase('Well'),upper('black'),ucase('BLack');
+--------------------+---------------+----------------+----------------+
| lower('BEAUTIFUL') | lcase('Well') | upper('black') | ucase('BLack') |
+--------------------+---------------+----------------+----------------+
| beautiful          | well          | BLACK          | BLACK          |
+--------------------+---------------+----------------+----------------+
1 row in set (0.03 sec)

5）获取指定长度的字符串的函数LEFT(s,n)和RIGHT(s,n)

LEFT(s,n)返回字符串s开始的最左边n个字符。RIGHT(s,n)相反

mysql> select left('football',5), right('football',4);
+--------------------+---------------------+
| left('football',5) | right('football',4) |
+--------------------+---------------------+
| footb              | ball                |
+--------------------+---------------------+
1 row in set (0.00 sec)

6）填充字符串函数LPAD(s1,len,s2)和RPAD(s1,len,s2)

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

mysql> select lpad('hello',4, '??'),lpad('hello',10,'??'),rpad('hello',4,'?'),rpad('hello',10,'?');
+-----------------------+-----------------------+---------------------+----------------------+
| lpad('hello',4, '??') | lpad('hello',10,'??') | rpad('hello',4,'?') | rpad('hello',10,'?') |
+-----------------------+-----------------------+---------------------+----------------------+
| hell                  | ?????hello            | hell                | hello?????           |
+-----------------------+-----------------------+---------------------+----------------------+
1 row in set (0.00 sec)

7）删除空格函数LTRIM(s)、RTRIM(s)和TRIM(s)

LTRIM(s)只删除左边的空格，RTRIM(s)只删除右边的空格，TRIM(s)删除两边的空格；

8）删除指定字符串的函数TRIM(s1 FROM s)

TRIM(s1 FROM s) 删除字符串s中两端所有的子字符串s1。s1为可选项，在未指定情况下删除空格。

mysql> select trim('xy' from 'xyxboxyokxxyxy');
+----------------------------------+
| trim('xy' from 'xyxboxyokxxyxy') |
+----------------------------------+
| xboxyokx                         |
+----------------------------------+
1 row in set (0.00 sec)

9）重复生成字符串的函数REPEAT(s,n)

REPEAT(s,n) 返回一个由重复的字符串s组成的字符串，字符串s的数目等于n。若n<=0，则返回一个空字符串，若s或n为NULL，则返回NULL。

mysql> select repeat('mysql',3);
+-------------------+
| repeat('mysql',3) |
+-------------------+
| mysqlmysqlmysql   |
+-------------------+
1 row in set (0.01 sec)

10）空格函数SPACE(n)和替换函数REPLACE(s,s1,s2)

SPACE(n)返回一个由n个空格组成的字符串，REPLACE(s,s1,s2)使用字符串s2替代字符串s中所有的字符串s1。

mysql> select concat('(',space(6), ')'), replace('xxx.mysql.com', 'x', 'w');
+---------------------------+------------------------------------+
| concat('(',space(6), ')') | replace('xxx.mysql.com', 'x', 'w') |
+---------------------------+------------------------------------+
| (      )                  | www.mysql.com                      |
+---------------------------+------------------------------------+
1 row in set (0.00 sec)

11）比较字符串大小的函数STRCMP(s1,s2)

STRCMP(s1,s2) ，若所有的字符串均相同，则返回0，若根据当前分类次序，第一个参数小于第二个，则返回-1，其他情况返回1。

12）获取子串的函数SUBSTRING(s,n,len)和MID(s,n,len)

SUBSTRING(s,n,len) 带有len参数的格式，从字符串s返回一个长度与len字符相同的子字符串，起始于位置n，也可能对n使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的n字符，即倒数第n个字符，而不是字符串的开头位置。MID(s,n,len)作用与SUBSTRING(s,n,len)的作用相同。

mysql> select substring('breakfast',5) as col1,
    -> substring('breakfast',5,3) as col2,
    -> substring('lunch',-3) as col3,
    -> substring('lunch', -5,3) as col4;
+-------+------+------+------+
| col1  | col2 | col3 | col4 |
+-------+------+------+------+
| kfast | kfa  | nch  | lun  |
+-------+------+------+------+
1 row in set (0.00 sec)

13）匹配子串开始位置的函数

LOCATE(str1,str)、POSITION(str1 IN str) 和INSTR(str, str1)3个函数的作用相同，返回子字符串str1在字符串str中开始位置。

14）字符串逆序的函数REVERSE(s)

REVERSE(s)返回的字符串顺序和s字符串顺序相反。

3 日期和时间函数

一般的日期函数除了使用DATE类型的参数外，也可以使用DATETIME或者TIMESTAMP类型的参数，但会忽略这些值的时间部分。相同的，以TIME类型值为参数的函数，可以接受TIMESTAMP类型的参数，但会忽略日期部分。

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

CURDATE()和CURRENT_DATE()作用相同，返回当前日期

CURTIME()和CURRENT_TIME()作用相同，返回当前时间

CURRENT_TIMIESTAMP()、LOCALTIME()、NOW()、SYSDATE()作用相同，返回当前日期时间

mysql> select curdate(), current_date(),
    -> curtime(), current_time(),
    -> current_timestamp(),localtime(),now(),sysdate();
+------------+----------------+-----------+----------------+---------------------+---------------------+---------------------+---------------------+
| curdate()  | current_date() | curtime() | current_time() | current_timestamp() | localtime()         | now()               | sysdate()           |
+------------+----------------+-----------+----------------+---------------------+---------------------+---------------------+---------------------+
| 2023-05-02 | 2023-05-02     | 09:33:15  | 09:33:15       | 2023-05-02 09:33:15 | 2023-05-02 09:33:15 | 2023-05-02 09:33:15 | 2023-05-02 09:33:15 |
+------------+----------------+-----------+----------------+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

2）返回UTC日期的函数和返回UTC时间的函数

UTC_DATE()函数返回当前UTC（世界标准时间）的日期值，UTC_TIME()返回当前UTC时间值

mysql> select utc_date(), utc_time();
+------------+------------+
| utc_date() | utc_time() |
+------------+------------+
| 2023-05-02 | 01:38:36   |
+------------+------------+
1 row in set (0.00 sec)

3）获取月份函数MONTH(date) 和MONTHNAME(date)

MONTH(date)返回date对应的月份，范围值为1~12

MONTHNAME(date) 返回日期date对应月份英文全名

mysql> select month('2023-05-02'), monthname('2023-05-02');
+---------------------+-------------------------+
| month('2023-05-02') | monthname('2023-05-02') |
+---------------------+-------------------------+
|                   5 | May                     |
+---------------------+-------------------------+
1 row in set (0.00 sec)

4）获取星期的函数DAYNAME(d)、DAOFWEEK(d) 和WEEKDAY(d)

DAYNAME(d) 返回d对应的日期的英文名称，如Sunday

DAYOFWEEK(d) 返回日期对应的周索引，1表示周日，2表示周一...

WEEKDAY(d)返回日期对应的工作日索引， 0表示周一，1表示周二...

mysql> select dayofweek('2023-05-02'), weekday('2023-05-02');
+-------------------------+-----------------------+
| dayofweek('2023-05-02') | weekday('2023-05-02') |
+-------------------------+-----------------------+
|                       3 |                     1 |
+-------------------------+-----------------------+
1 row in set (0.00 sec)

5）获取星期数WEEK(d)和WEEKOFYEAR(d)

WEEK(d)计算日期d是一年中的第几周

WEEKOFYEAR(d)计算某天位于一年中的第几周，范围是1~53，两个函数作用相同

mysql> select week('2023-05-02'), weekofyear('2023-05-02');
+--------------------+--------------------------+
| week('2023-05-02') | weekofyear('2023-05-02') |
+--------------------+--------------------------+
|                 18 |                       18 |
+--------------------+--------------------------+
1 row in set (0.00 sec)

6）获取天数DAYOFYEAR(d)和DAYOFMONTH(d)

DAYOFYEAR(d)函数返回d是一年中的第几天，范围是1~366

DAYOFMONTH(d)返回d是一个月中的第几天，范围是1~31

7）获取年份、季度、小时、分钟和秒钟的函数

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

QUARTER(date)返回date对应的一年中的季度值，范围是1~4

MINUTE(time)返回time对应的分钟数，范围是0~59

SECOND(time)返回time对应的秒数，范围是0~59

mysql> select year('23-05-02'),quarter('23-05-02'),MINUTE('23-05-02 10:02:30'), second('10:02:30');
+------------------+---------------------+-----------------------------+--------------------+
| year('23-05-02') | quarter('23-05-02') | MINUTE('23-05-02 10:02:30') | second('10:02:30') |
+------------------+---------------------+-----------------------------+--------------------+
|             2023 |                   2 |                           2 |                 30 |
+------------------+---------------------+-----------------------------+--------------------+
1 row in set (0.00 sec)

8）获取日期的指定值的函数

EXTRACT(type FROM date) 函数所使用的时间间隔类型说明符与DATE_ADD()或DATE_SUB()相同，但它是日期中提取一部分，不是执行日期计算。

mysql> select extract(YEAR from '2023-05-02') as col1, extract(YEAR_MONTH from '2023-05-02 10:07:30') as col2,
    -> extract(DAY_MINUTE from '2023-05-02 10:07:30');
+------+--------+------------------------------------------------+
| col1 | col2   | extract(DAY_MINUTE from '2023-05-02 10:07:30') |
+------+--------+------------------------------------------------+
| 2023 | 202305 |                                          21007 |
+------+--------+------------------------------------------------+
1 row in set (0.00 sec)

9）时间和秒钟转换的函数

TIME_TO_SEC(time) 返回已转换为秒的time参数，使用TIME_TO_SEC将时间值转换为秒值

mysql> select time_to_sec('10:10:30');
+-------------------------+
| time_to_sec('10:10:30') |
+-------------------------+
|                   36630 |
+-------------------------+
1 row in set (0.00 sec)

10）计算日期和时间的函数

DATE_ADD(date, INTERVAL expr type) 和DATE_SUB(date, INTERVAL expr type)中，date是一个DATETIME或DATE值，用来指定起始时间。expr是一个表达式，用来指定从起始时间添加或减去的时间间隔值。对于负值的时间间隔，expr可以以一个负号‘-’开头。type为关键词，指示了表达式被解释的方式。

除了这两个，计算日期和时间的函数还有ADDDATE()、SUBDATE()、ADDTIME()、SUBTIME()、DATE_DIFF()，SUBDATE()和DATE_SUB()用来执行日期的减操作。

mysql> select date_add('2023-04-30', INTERVAl 1 DAY) as col1,
    -> adddate('2023-05-01 10:20:30', INTERVAL 1 MINUTE);
+------------+---------------------------------------------------+
| col1       | adddate('2023-05-01 10:20:30', INTERVAL 1 MINUTE) |
+------------+---------------------------------------------------+
| 2023-05-01 | 2023-05-01 10:21:30                               |
+------------+---------------------------------------------------+
1 row in set (0.00 sec)

说明：type和expr参数的关系

type值	预期的expr格式
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEAR
SECOND_MICROSECOND	'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND	'MINUTES.MICROSECONDS'
MINUTE_SECOND	'MINUTES:SECONDS'
HOUR_MICROSECOND	'HOURS.MICROSECONDS'
HOUR_SECOND	'HOURS:MINUTES:SECONDS'
YEAR_MONTH	'YEARS-MONTHS'
HOUR_MINUTE	'HOURS:MINUTES'
DAY_MICROSECOND	'DAYS.MICROSECONDS'
DAY_SECOND	'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE	'DAYS HOURS:MINUTES'
DAY_HOUR	'DAYS HOURS'

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

DATE_FORMAT(date,format)根据 format 指定的格式显示date值

mysql> select date_format('1997-10-04 22:30:33', '%W %M %Y'),
    -> date_format('1997-10-09 22:30:33', '%D %y %a %d %m %b %j') as col2;
+------------------------------------------------+--------------------------+
| date_format('1997-10-04 22:30:33', '%W %M %Y') | col2                     |
+------------------------------------------------+--------------------------+
| Saturday October 1997                          | 9th 97 Thu 09 10 Oct 282 |
+------------------------------------------------+--------------------------+
1 row in set (0.00 sec)

说明：DATE_FORMAT时间日期格式

说明符	说明
%a	工作日的缩写名称
%b	月份的缩写名称
%c	月份，数字形式
%D	以英文后缀表示月中几号
%d	该月日期，数字形式
%e	该月日期，数字形式
%f	微秒
%H	以2位数表示24小时
%h, %I	以2位数表示12小时
%i	分钟，数字形式
%j	一年中的天数
%k	以24小时表示时间
%l	以12小时表示时间
%M	月份名称
%m	月份，数字形式
%p	上午，下午
%r	时间，12小时制
%S, %s	以2位数表示秒
%T	时间，24小时制
%U	周，其中周日为每周第一天
%u	周，其中周一为每周第一天
%V	周，其中周日为每周第一天，和%X同时使用
%v	周，其中周一为每周第一天，和%x同时使用
%W	工作日名称
%w	一周中的每日
%X	该周的年份和%V同时使用
%x	该周的年份和%v同时使用
%Y	4位数形式表示年份
%y	2位数表示年份
%%	标识符%

4 条件判断函数

条件判断函数也称为流程函数，根据满足不同条件，执行相应的流程，MySQL中进行条件判断的函数有IF、IFNULL和CASE。

1）IF(expr,v1,v2)函数

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

mysql> select if(1>2,2,3),if(1<2,'yes ', 'no'),
    -> if(strcmp('test', 'test1'), 'no', 'yes');
+-------------+----------------------+------------------------------------------+
| if(1>2,2,3) | if(1<2,'yes ', 'no') | if(strcmp('test', 'test1'), 'no', 'yes') |
+-------------+----------------------+------------------------------------------+
|           3 | yes                  | no                                       |
+-------------+----------------------+------------------------------------------+
1 row in set (0.00 sec)

2）IFNULL(v1,v2)函数

假如v1不为NULL,则IFNULL()的返回值为v1；否则返回值为v2。

5 系统信息函数

MySQL中的系统信息函数有数据库的版本号、当前用户名和连接数、系统字符集、最后一个自动生成的ID值等。

1）获取MySQL版本号、连接数和数据库名的函数

VERSION()返回MySQL版本号

CONNECTION_ID()返回MySQL服务器当前连接的次数

SHOW PROCESSLIST 输出当前用户的连接信息

DATEBASE()和SCHEMA()返回使用的数据库

mysql> select version(), connection_id(), database(), schema();
+-----------+-----------------+------------+----------+
| version() | connection_id() | database() | schema() |
+-----------+-----------------+------------+----------+
| 8.0.30    |              11 | test       | test     |
+-----------+-----------------+------------+----------+
1 row in set (0.00 sec)


mysql> show processlist;
+----+-----------------+-----------+-------+---------+--------+------------------------+------------------+
| Id | User            | Host      | db    | Command | Time   | State                  | Info             |
+----+-----------------+-----------+-------+---------+--------+------------------------+------------------+
|  5 | event_scheduler | localhost | NULL  | Daemon  | 133898 | Waiting on empty queue | NULL             |
| 10 | root            | localhost | mysql | Sleep   |  31592 |                        | NULL             |
| 11 | root            | localhost | NULL  | Query   |      0 | init                   | show processlist |
+----+-----------------+-----------+-------+---------+--------+------------------------+------------------+
3 rows in set (0.00 sec)

2）获取用户名的函数

USER()、CURRENT_USER、CURRENT_USER()、SYSTEM_USER()和SESSION_USER()这几个函数返回当前被MySQL服务验证的用户名和主机名组合。

3）获取字符串的字符集和排序方式的函数

CHARSET(str) 返回字符串str 自变量的字符集

mysql> select charset('abc'), charset(convert('abc' using  latin1));
+----------------+---------------------------------------+
| charset('abc') | charset(convert('abc' using  latin1)) |
+----------------+---------------------------------------+
| utf8mb4        | latin1                                |
+----------------+---------------------------------------+
1 row in set (0.00 sec)

4）获取最后一个自动生成的ID值的函数

LAST_INSERT_ID()函数返回最后生成的AUTO_INCREMENT值。

6 加密函数

加密函数是MySQL:8.0的新特性，主要用来对数据进行加密和界面处理，以保证某些重要数据不被别人获取。

1）加密函数MD5(str)

MD5(str)为字符串算出一个MD5 128比特校验和，该值以32位16进制数字的二进制字符串形式返回，若参数为NULL,则会返回NULL。

2）加密函数SHA(str)

SHA(str)从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL,SHA加密算法比MD5更加安全。

3）加密函数SHA2(str, hash_length)

SHA2(str, hash_length) 使用hash_length作为长度，加密str，hash_length支持的值为：224、 256、 384、512和0， 0等同于256。

mysql> select sha2('tomcat', 0) A, sha2('tomcat',256) B \G
*************************** 1. row ***************************
A: 51b912f34ae18b4e5ad349f50bc6fdd8d9a605d09bab4f302a09c7f790854296
B: 51b912f34ae18b4e5ad349f50bc6fdd8d9a605d09bab4f302a09c7f790854296
1 row in set (0.00 sec)

7 其他函数

有些函数不能笼统的分为哪一类，例如重复指定操作函数、改变字符集函数、IP地址与数字转换函数等。

1）格式化函数FORMAT(x,n)

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

2）不同进制的数字进行转换函数

CONV(N, from_base, to_base) 函数进行不同进制数的转换。返回位数值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              |
+-----------------+----------------+---------------+----------------+
1 row in set (0.00 sec)

3）IP地址与数字相互转换的函数

INET_ATON(expr) 给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数，地址可以是4或8 bit地址。

INET_NTOA(expr)给定一个数字网络地址，返回作为字符串该地址的点地址表示。

mysql> select inet_aton('192.168.0.105'), inet_ntoa(3232235625);
+----------------------------+-----------------------+
| inet_aton('192.168.0.105') | inet_ntoa(3232235625) |
+----------------------------+-----------------------+
|                 3232235625 | 192.168.0.105         |
+----------------------------+-----------------------+
1 row in set (0.00 sec)

4）加锁函数和解锁函数

GET_LOCK(str, timeout) 设法使用字符串str给定的名字得到一个锁，超时为tomeout秒，若成功得到锁，则返回1，否则返回0，若发生错误返回NULL。

RELEASE_LOCK(str) 解开被GET_LOCK(str)获取的，若锁被解开，则返回1，若该线程未创建锁，则返回0，若命名的锁不存在，则返回NULL，若该锁从未被GET_LOCK()的调用获取，或锁已经被提前解开，则该锁不存在。

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

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

mysql> select get_lock('lock1', 10) as getlock,
    -> is_used_lock('lock1') as isusedlock,
    -> is_free_lock('lock1') as isfreelock,
    -> release_lock('lock1') as releaselock;
+---------+------------+------------+-------------+
| getlock | isusedlock | isfreelock | releaselock |
+---------+------------+------------+-------------+
|       1 |         10 |          0 |           1 |
+---------+------------+------------+-------------+
1 row in set (0.00 sec)

5）重复执行指定操作的函数

BENCHMARK(count, expr) 函数重复count次执行表达式expr，它可以用于计算MySQL处理表达式的速度，结果值通常为0（表示速度很快，没有消费时间），另一个作用是可以在MySQL客户端内部报告语句执行的时间。

mysql> select benchmark(50000, sha('newpwd'));
+---------------------------------+
| benchmark(50000, sha('newpwd')) |
+---------------------------------+
|                               0 |
+---------------------------------+
1 row in set (0.01 sec)

6）改变字符集的函数

CONVERT(...USING...) 用来在不同字符集之间转换

mysql> select charset('string'), charset(convert('string' using latin1));
+-------------------+-----------------------------------------+
| charset('string') | charset(convert('string' using latin1)) |
+-------------------+-----------------------------------------+
| utf8mb4           | latin1                                  |
+-------------------+-----------------------------------------+
1 row in set (0.00 sec)

7）改变数据类型的函数

CAST(x, AS type) 和CONVERT(x, type) 将一个类型的值转换为另一个类型的值，可以转换的type有BINARY、CHAR(n)、DATE、TIME、DATETIME、DECIMAL、 SIGNED、UNSIGNED。

mysql> select cast(100 as char(2)), convert('2023-05-02 00:15:30', TIME);
+----------------------+--------------------------------------+
| cast(100 as char(2)) | convert('2023-05-02 00:15:30', TIME) |
+----------------------+--------------------------------------+
| 10                   | 00:15:30                             |
+----------------------+--------------------------------------+
1 row in set, 1 warning (0.01 sec)

8 窗口函数

在MySQL:8.0之前，没有排名函数，所以当需要在查询当中实现排名时，必须手写@变量。在MySQL:8.0版本中，新增了一个窗口函数，用它可以实现很多新的查询方式，窗口函数类似于SUM()、COUNT()那样的集合函数，但它并不会将多行查询结果合并为一行，而是将结果放回多行当中。也就是说，窗口函数是不需要GROUP BY的。

创建公司部门表branch,包含部门的名称和部门人数两个字段

mysql> create table branch(name char(255) not null,brcount int(11) not null);
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> insert into branch(name,brcount)values('branch1',5),('branch2',10),('branch3',8), ('branch4',20), ('branch5', 9);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0
mysql> select * from branch;
+---------+---------+
| name    | brcount |
+---------+---------+
| branch1 |       5 |
| branch2 |      10 |
| branch3 |       8 |
| branch4 |      20 |
| branch5 |       9 |
+---------+---------+
5 rows in set (0.00 sec)

对公司部分人数按小到大进行排名，可以利用窗口函数实现：

mysql> select *, rank() OVER w1 as 'rank' from branch window w1 as (order by brcount);
+---------+---------+------+
| name    | brcount | rank |
+---------+---------+------+
| branch1 |       5 |    1 |
| branch3 |       8 |    2 |
| branch5 |       9 |    3 |
| branch2 |      10 |    4 |
| branch4 |      20 |    5 |
+---------+---------+------+
5 rows in set (0.00 sec)

这里创建了名称为w1的窗口函数，规定了对brcount字段进行排序，然后在select子句中对窗口函数w1执行rank()方法，将结果输出位rank字段。