MySQL常用函数汇总

数据库 / MySQL
字数统计: 7.3k   |   阅读时长≈ 32 分钟

MySQL 内部函数可以帮助用户更加方便地处理表中的数据。

SELECT、INSERT、UPDATE和DELETE语句及其子句（例如WHERE、ORDER BY、HAVING等）中都可以使用 MySQL 函数。

MySQL 函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数和加密函数等。

数值型函数

ABS函数：求绝对值

绝对值函数ABS(x)返回x的绝对值。正数的绝对值是其本身，负数的绝对值为其相反数，0 的绝对值是 0。

1
2
3
4
5
6
7
mysql> SELECT ABS(5), ABS(-2.4), ABS(-24), ABS(0);
+--------+-----------+----------+--------+
| ABS(5) | ABS(-2.4) | ABS(-24) | ABS(0) |
+--------+-----------+----------+--------+
|      5 |       2.4 |       24 |      0 |
+--------+-----------+----------+--------+
1 row in set (0.10 sec)

SQRT函数：求二次方根

平方根函数SQRT(x)返回非负数x的二次方根。负数没有平方根，返回NULL。

1
2
3
4
5
6
7
mysql> SELECT SQRT(25), SQRT(120), SQRT(-9);
+----------+--------------------+----------+
| SQRT(25) | SQRT(120)          | SQRT(-9) |
+----------+--------------------+----------+
|        5 | 10.954451150103322 |     NULL |
+----------+--------------------+----------+
1 row in set (0.06 sec)

MOD函数：求余数

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

1
2
3
4
5
6
7
mysql> SELECT MOD(63,8), MOD(120,10), MOD(15.5,3);
+-----------+-------------+-------------+
| MOD(63,8) | MOD(120,10) | MOD(15.5,3) |
+-----------+-------------+-------------+
|         7 |           0 |         0.5 |
+-----------+-------------+-------------+
1 row in set (0.03 sec)

CEIL和CELING函数：向上取整

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

1
2
3
4
5
6
7
mysql> SELECT CEIL(-2.5), CEILING(2.5);
+------------+--------------+
| CEIL(-2.5) | CEILING(2.5) |
+------------+--------------+
|         -2 |            3 |
+------------+--------------+
1 row in set (0.00 sec)

FLOOR函数：向下取整

FLOOR(x)函数返回小于x的最大整数值。

1
2
3
4
5
6
7
mysql> SELECT FLOOR(5), FLOOR(5.66), FLOOR(-4), FLOOR(-4.66);
+----------+-------------+-----------+--------------+
| FLOOR(5) | FLOOR(5.66) | FLOOR(-4) | FLOOR(-4.66) |
+----------+-------------+-----------+--------------+
|        5 |           5 |        -4 |           -5 |
+----------+-------------+-----------+--------------+
1 row in set (0.00 sec)

RAND函数：生成随机数

RAND()函数可以产生一个在 0 和 1 之间的随机数。

1
2
3
4
5
6
7
mysql>  SELECT RAND(), RAND(), RAND();
+------------------+-----------------+------------------+
| RAND()           | RAND()          | RAND()           |
+------------------+-----------------+------------------+
| 0.45464584925645 | 0.1824410643265 | 0.54826780459682 |
+------------------+-----------------+------------------+
1 row in set (0.00 sec)

当使用整数作为参数调用时，RAND()使用该值作为随机数的种子发生器。每次种子使用给定值生成，RAND()将产生一个可重复的系列数字：

1
2
3
4
5
6
7
mysql> SELECT RAND(1), RAND(), RAND(1);
+---------------------+--------------------+---------------------+
| RAND(1)             | RAND()             | RAND(1)             |
+---------------------+--------------------+---------------------+
| 0.40540353712197724 | 0.7901452330780637 | 0.40540353712197724 |
+---------------------+--------------------+---------------------+
1 row in set (0.00 sec)

由运行结果可知，当向RAND()函数中传入一个整数作为参数是，RAND()函数产生的随机数可以重复。

SIGN函数：返回参数的符号

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

1
2
3
4
5
6
7
mysql> SELECT SIGN(-6), SIGN(0), SIGN(34);
+----------+---------+----------+
| SIGN(-6) | SIGN(0) | SIGN(34) |
+----------+---------+----------+
|       -1 |       0 |        1 |
+----------+---------+----------+
1 row in set (0.00 sec)

POW和POWER函数：求次方

POW(x,y)函数和POWER(x,y)函数用于计算x的y次方。

1
2
3
4
5
6
7
mysql> SELECT POW(5,-2), POW(10,3), POW(100,0), POWER(4,3), POWER(6,-3);
+-----------+-----------+------------+------------+----------------------+
| POW(5,-2) | POW(10,3) | POW(100,0) | POWER(4,3) | POWER(6,-3)          |
+-----------+-----------+------------+------------+----------------------+
|      0.04 |      1000 |          1 |         64 | 0.004629629629629629 |
+-----------+-----------+------------+------------+----------------------+
1 row in set (0.00 sec)

SIN函数：求正弦值

正弦函数SIN(x)返回x的正弦值，其中x为弧度值。

1
2
3
4
5
6
7
mysql> SELECT SIN(1), SIN(0.5*PI());
+--------------------+---------------+
| SIN(1)             | SIN(0.5*PI()) |
+--------------------+---------------+
| 0.8414709848078965 |             1 |
+--------------------+---------------+
1 row in set (0.15 sec)

提示：PI()函数返回圆周率（3.141593）。

ASIN函数：求反正弦值

反正弦函数ASIN(x)返回x的反正弦值，若x不在 -1 到 1 的范围之内，则返回NULL。

1
2
3
4
5
6
7
mysql> SELECT ASIN(0.8414709848078965), ASIN(2);
+--------------------------+---------+
| ASIN(0.8414709848078965) | ASIN(2) |
+--------------------------+---------+
|                        1 |    NULL |
+--------------------------+---------+
1 row in set (0.03 sec)

COS函数：求余弦值

余弦函数COS(x)返回x的余弦值，x为弧度值。

1
2
3
4
5
6
7
mysql> SELECT COS(1), COS(0), COS(PI());
+--------------------+--------+-----------+
| COS(1)             | COS(0) | COS(PI()) |
+--------------------+--------+-----------+
| 0.5403023058681398 |      1 |        -1 |
+--------------------+--------+-----------+
1 row in set (0.03 sec)

ACOS函数：求反余弦值

反余弦函数ACOS(x)。x值的范围必须在 -1 和 1 之间，否则返回NULL。

1
2
3
4
5
6
7
mysql> SELECT ACOS(2), ACOS(1), ACOS(-1);
+---------+---------+-------------------+
| ACOS(2) | ACOS(1) | ACOS(-1)          |
+---------+---------+-------------------+
|    NULL |       0 | 3.141592653589793 |
+---------+---------+-------------------+
1 row in set (0.01 sec)

TAN函数：求正切值

正切函数TAN(x)返回x的正切值，x为给定的弧度值。

1
2
3
4
5
6
7
mysql> SELECT TAN(1), TAN(0);
+--------------------+--------+
| TAN(1)             | TAN(0) |
+--------------------+--------+
| 1.5574077246549023 |      0 |
+--------------------+--------+
1 row in set (0.03 sec)

ATAN函数：求反正切值

反正切ATAN(x)返回x的反正切值，正切为x的值。

1
2
3
4
5
6
7
mysql> SELECT ATAN(1.5574077246549023), ATAN(0);
+--------------------------+---------+
| ATAN(1.5574077246549023) | ATAN(0) |
+--------------------------+---------+
|                        1 |       0 |
+--------------------------+---------+
1 row in set (0.05 sec)

COT函数：求余切值

余切函数COT(x)返回x的余切值，x是给定的弧度值。

1
2
3
4
5
6
7
mysql> SELECT COT(1);
+--------------------+
| COT(1)             |
+--------------------+
| 0.6420926159343306 |
+--------------------+
1 row in set (0.00 sec)

字符串函数

LENGTH函数：获取字符串长度

LENGTH(str)函数的返回值为字符串的字节长度，使用uft8编码字符集时，一个汉字是 3 个字节，一个数字或字母是一个字节。

1
2
3
4
5
6
7
mysql> SELECT LENGTH('name'), LENGTH('数据库');
+----------------+---------------------+
|LENGTH('name')  | LENGTH('数据库')    |
+----------------+---------------------+
|              4 |                   9 |
+----------------+---------------------+
1 row in set (0.04 sec)

CONCAT函数：字符串拼接

CONCAT(sl, s2, ...)函数返回结果为连接参数产生的字符串，或许有一个或多个参数。

若有任何一个参数为NULL，则返回值为NULL。若所有参数均为非二进制字符串，则结果为非二进制字符串。若自变量中含有任一二进制字符串，则结果为一个二进制字符串。

1
2
3
4
5
6
7
mysql> SELECT CONCAT('MySQL','5.7'), CONCAT('MySQL',NULL);
+-----------------------+----------------------+
| CONCAT('MySQL','5.7') | CONCAT('MySQL',NULL) |
+-----------------------+----------------------+
| MySQL5.7              | NULL                 |
+-----------------------+----------------------+
1 row in set (0.03 sec)

INSERT函数：替换字符串

替换字符串函数INSERT(s1，x，len，s2)返回字符串s1，子字符串起始于x位置，并且用len个字符长的字符串代替s2。

若x超过字符串长度，则返回值为原始字符串。假如len的长度大于其他字符串的长度，则从位置x开始替换。若任何一个参数为NULL，则返回值为NULL。

1
2
3
4
5
6
7
8
9
mysql> SELECT INSERT('Football',2,4,'Play') AS col1,
    -> INSERT('Football',-1,4,'Play') AS col2,
    -> INSERT('Football',3,20,'Play') AS col3;
+----------+----------+--------+
| col1     | col2     | col3   |
+----------+----------+--------+
| FPlayall | Football | FoPlay |
+----------+----------+--------+
1 row in set (0.04 sec)

由执行结果可知：

  • 第一个函数INSERT('Football'，2，4，'Play')将Football从第 2 个字符开始长度为 4 的字符串替换为Play，结果为FPlayall；
  • 第二个函数('Football'，-1，4，'Play')中的起始位置 -1 超出了字符串长度，直接返回原字符串；
  • 第三个函数INSERT('Football'，3，20，'Play')替换长度超出了原字符串长度，则从第 3 个字符开始，截取后面所有的字符，并替换为指定字符Play，结果为FoPlay。

LOWER函数：将字母转换成小写

字母小写转换函数LOWER(str)可以将字符串str中的字母字符全部转换成小写。

1
2
3
4
5
6
7
mysql> SELECT LOWER('BLUE'), LOWER('Blue');
+---------------+---------------+
| LOWER('BLUE') | LOWER('Blue') |
+---------------+---------------+
| blue          | blue          |
+---------------+---------------+
1 row in set (0.03 sec)

UPPER函数：将字母转换成大写

字母大写转换函数UPPER(str)可以将字符串str中的字母字符全部转换成大写。

1
2
3
4
5
6
7
mysql> SELECT UPPER('green'), UPPER('Green');
+----------------+----------------+
| UPPER('green') | UPPER('Green') |
+----------------+----------------+
| GREEN          | GREEN          |
+----------------+----------------+
1 row in set (0.03 sec)

LEFT函数：从左侧截取字符串

LEFT(s, n)函数返回字符串s最左边的n个字符。

1
2
3
4
5
6
7
mysql> SELECT LEFT('MySQL',2);
+-----------------+
| LEFT('MySQL',2) |
+-----------------+
| My              |
+-----------------+
1 row in set (0.04 sec)

由运行结果可知，返回字符串MySQL左边开始的长度为 2 的子字符串，结果为My。

RIGHT函数：从右侧截取字符串

RIGHT(s, n)函数返回字符串s最右边的n个字符。

1
2
3
4
5
6
7
mysql> SELECT RIGHT('MySQL',3);
+------------------+
| RIGHT('MySQL',3) |
+------------------+
| SQL              |
+------------------+
1 row in set (0.00 sec)

TRIM函数：删除空格

删除空格函数TRIM(s)删除字符串s两侧的空格。

1
2
3
4
5
6
7
mysql> SELECT '[   mobile   ]', CONCAT('[',TRIM('   mobile   '),']');
+----------------+--------------------------------------+
| [   mobile   ] | CONCAT('[',TRIM('   mobile   '),']') |
+----------------+--------------------------------------+
| [   mobile   ] | [mobile]                             |
+----------------+--------------------------------------+
1 row in set (0.07 sec)

REPLACE函数：字符串替换

替换函数REPLACE(s，s1，s2)使用字符串s2替换字符串s中所有的字符串s1。

1
2
3
4
5
6
7
mysql> SELECT REPLACE('aaa.mysql.com','a','w');
+----------------------------------+
| REPLACE('aaa.mysql.com','a','w') |
+----------------------------------+
| www.mysql.com                    |
+----------------------------------+
1 row in set (0.00 sec)

SUBSTRING函数：截取字符串

获取子串函数SUBSTRING(s，n，len)带有len参数的格式，从字符串s返回一个长度同len字符相同的子字符串，起始于位置n。

也可能对n使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的第n个字符，即倒数第n个字符，而不是字符串的开头位置。

1
2
3
4
5
6
7
8
9
10
mysql> SELECT SUBSTRING('computer',3) AS col1,
    -> SUBSTRING('computer',3,4) AS col2,
    -> SUBSTRING('computer',-3) AS col3,
    -> SUBSTRING('computer',-5,3) AS col4;
+--------+------+------+------+
| col1   | col2 | col3 | col4 |
+--------+------+------+------+
| mputer | mput | ter  | put  |
+--------+------+------+------+
1 row in set (0.00 sec)

REVERSE函数：反转字符串

字符串逆序函数REVERSE(s)可以将字符串s反转，返回的字符串的顺序和s字符串的顺序相反。

1
2
3
4
5
6
7
mysql> SELECT REVERSE('hello');
+------------------+
| REVERSE('hello') |
+------------------+
| olleh            |
+------------------+
1 row in set (0.00 sec)

聚合函数

MAX函数：查询指定列的最大值

MAX()函数是用来返回指定列中的最大值。

为了方便理解，首先创建一个学生成绩表tb_students_score。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> use test_db;
Database changed
mysql> SELECT * FROM tb_students_score;
+--------------+---------------+
| student_name | student_score |
+--------------+---------------+
| Dany         |            90 |
| Green        |            99 |
| Henry        |            95 |
| Jane         |            98 |
| Jim          |            88 |
| John         |            94 |
| Lily         |           100 |
| Susan        |            96 |
| Thomas       |            93 |
| Tom          |            89 |
+--------------+---------------+
10 rows in set (0.13 sec)

在tb_students_score表中查找最高的成绩。

1
2
3
4
5
6
7
mysql> SELECT MAX(student_score) AS max_score FROM tb_students_score;
+-----------+
| max_score |
+-----------+
|       100 |
+-----------+
1 row in set (0.06 sec)

MAX()函数不仅适用于查找数值类型，也可应用于字符类型。

在tb_students_score表中查找student_name的最大值。

1
2
3
4
5
6
7
mysql> SELECT MAX(student_name) AS max_name FROM tb_students_score;
+----------+
| max_name |
+----------+
| Tom      |
+----------+
1 row in set (0.03 sec)

注意：MAX()函数还可以返回任意列中的最大值，包括返回字符类型的最大值。在对字符类型的数据进行比较时，按照字符的 ASCII 码值大小进行比较，从a～z，a的 ASCII 码最小，z的最大。在比较时，先比较第一个字符，如果相等，继续比较下一个字符，一直到两个字符不相等或者字符结束为止。例如，b与t比较时，t为最大值；bcd与bca比较时，bcd为最大值。

MIN函数：查询指定列的最小值

MIN()函数是用来返回查询列中的最小值。

1
2
3
4
5
6
7
mysql> SELECT MIN(student_score) AS min_score FROM tb_students_score;
+-----------+
| min_score |
+-----------+
|        88 |
+-----------+
1 row in set (0.00 sec)

提示：MIN()函数与MAX()函数类似，不仅适用于查找数值类型，也可应用于字符类型。

COUNT函数：统计查询结果的行数

COUNT()函数统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数，使用方法有以下两种：

  • COUNT(*)计算表中总的行数，无论某列有数值或者为空值。
  • COUNT(字段名)计算指定列下总的行数，计算时将忽略空值的行。
1
2
3
4
5
6
7
mysql> SELECT COUNT(student_name) AS students_number FROM tb_students_score;
+-----------------+
| students_number |
+-----------------+
|              10 |
+-----------------+
1 row in set (0.03 sec)

提示：在计算总数的时候对待NULL值的方式是，指定列的值为空的行被COUNT()函数忽略，但若不指定列，而在COUNT()函数中使用星号*，则所有记录都不忽略。

SUM函数：求和

SUM()是一个求总和的函数，返回指定列值的总和。

如果在没有返回匹配行SELECT语句中使用SUM函数，则SUM函数返回NULL，而不是 0。

SUM函数忽略计算中的NULL值。

1
2
3
4
5
6
7
mysql> SELECT SUM(student_score) AS score_sum FROM tb_students_score;
+-----------+
| score_sum |
+-----------+
|       942 |
+-----------+
1 row in set (0.00 sec)

提示：SUM()函数在计算时，忽略列值为NULL的行。

AVG函数：求平均值

AVG()函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。

在tb_students_score表中，查询所有学生成绩的平均值。

1
2
3
4
5
6
7
mysql> SELECT AVG(student_score) AS score_avg FROM tb_students_score;
+-----------+
| score_avg |
+-----------+
|   94.2000 |
+-----------+
1 row in set (0.03 sec)

提示：使用AVG()函数时，参数为要计算的列名称，若要得到多个列的平均值，则需要在每一列都使用AVG()函数。

日期和时间函数

CURDATE和CURRENT_DATE函数：获取系统当前日期

CURDATE()和CURRENT_DATE()函数的作用相同，将当前日期按照YYYY-MM-DD或YYYYMMDD格式的值返回，具体格式根据函数用在字符串或数字语境中而定。

1
2
3
4
5
6
7
mysql> SELECT CURDATE(), CURRENT_DATE(), CURRENT_DATE()+0;
+------------+----------------+------------------+
| CURDATE()  | CURRENT_DATE() | CURRENT_DATE()+0 |
+------------+----------------+------------------+
| 2017-04-01 | 2017-04-01     |         20170401 |
+------------+----------------+------------------+
1 row in set (0.03 sec)

由运行结果可以看到，两个函数的作用相同，返回了相同的系统当前日期，CURDATE()+0将当前日期值转换为数值型的。

CURTIME和CURRENT_TIME函数：获取系统当前时间

CURTIME()和CURRENT_TIME()函数的作用相同，将当前时间以HH:MM:SS或HHMMSS格式返回，具体格式根据函数用在字符串或数字语境中而定。

1
2
3
4
5
6
7
mysql> SELECT CURTIME(),CURRENT_TIME(),CURRENT_TIME()+0;
+-----------+----------------+------------------+
| CURTIME() | CURRENT_TIME() | CURRENT_TIME()+0 |
+-----------+----------------+------------------+
| 19:39:51  | 19:39:51       |           193951 |
+-----------+----------------+------------------+
1 row in set (0.04 sec)

由运行结果可以看出，两个函数返回的结果相同，都返回了当前的系统时间。CURRENT_TIME()+0是将当前日期值转换为数值型的。

NOW和SYSDATE函数：获取当前时间日期

NOW()和SYSDATE()函数的作用相同，都是返回当前日期和时间值，格式为YYYY-MM-DD HH:MM:SS或YYYYMMDDHHMMSS，具体格式根据函数用在字符串或数字语境中而定。

1
2
3
4
5
6
7
mysql> SELECT NOW(),SYSDATE();
+---------------------+---------------------+
| NOW()               | SYSDATE()           |
+---------------------+---------------------+
| 2017-04-01 19:36:52 | 2017-04-01 19:36:52 |
+---------------------+---------------------+
1 row in set (0.04 sec)

虽然在 MySQL 中NOW()和SYSDATE()都表示当前时间，但是NOW()取的是语句开始执行的时间，而SYSDATE()取的是语句执行过程中动态的实时时间。

先查询了NOW()和SYSDATE()，然后sleep了 3 秒，再查询NOW()和SYSDATE()，结果如下：

1
2
3
4
5
6
7
mysql> select now(), sysdate(), sleep(3), now(), sysdate();
+-----------------------+------------------------+-------------+-----------------------+---------------------+
| now()                 | sysdate()              | sleep(3)    | now()                 | sysdate()           |
+-----------------------+------------------------+-------------+------------------- ---+---------------------+
| 2019-02-27 10:59:39   | 2019-02-27 10:59:39    |        0    | 2019-02-27 10:59:39   | 2019-02-27 10:59:42 |
+-----------------------+------------------------+-------------+-----------------------+---------------------+
1 row in set (3.00 sec)

由运行结果可以看出，NOW()函数始终获取的是 SQL 语句开始执行的时间，而SYSDATE()函数则是动态获取的实时时间。

UNIX_TIMESTAMP函数：获取UNIX时间戳

UNIX_TIMESTAMP(date)若无参数调用，返回一个无符号整数类型的UNIX时间戳（1970-01-01 00:00:00 GMT之后的秒数）。

若用date来调用UNIX_TIMESTAMP()，它会将参数值以1970-01-01 00:00:00 GMT后的秒数的形式返回。

1
2
3
4
5
6
7
mysql> SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW()), NOW();
+------------------+-----------------------+---------------------+
| UNIX_TIMESTAMP() | UNIX_TIMESTAMP(NOW()) | NOW()               |
+------------------+-----------------------+---------------------+
|       1551251270 |            1551251270 | 2019-02-27 15:07:50 |
+------------------+-----------------------+---------------------+
1 row in set (0.00 sec)

FROM_UNIXTIME函数：时间戳转日期

FROM_UNIXTIME(date)函数把UNIX时间戳转换为普通格式的日期时间值，与UNIX_TIMESTAMP()函数互为反函数。

1
2
3
4
5
6
7
mysql> SELECT FROM_UNIXTIME(1150051270);
+---------------------------+
| FROM_UNIXTIME(1150051270) |
+---------------------------+
| 2006-06-12 02:41:10       |
+---------------------------+
1 row in set (0.00 sec)

MONTH函数：获取指定日期的月份

MONTH(date)函数返回指定date对应的月份，范围为 1～12。

1
2
3
4
5
6
7
mysql> SELECT MONTH('2017-12-15');
+---------------------+
| MONTH('2017-12-15') |
+---------------------+
|                  12 |
+---------------------+
1 row in set (0.00 sec)

MONTHNAME函数：获取指定日期月份的英文名称

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

1
2
3
4
5
6
7
mysql> SELECT MONTHNAME('2017-12-15');
+-------------------------+
| MONTHNAME('2017-12-15') |
+-------------------------+
| December                |
+-------------------------+
1 row in set (0.00 sec)

DAYNAME函数：获取指定日期的星期名称

DAYNAME(date)函数返回date对应的工作日英文名称，例如Sunday、Monday等。

1
2
3
4
5
6
7
mysql> SELECT DAYNAME('2006-06-12');
+-----------------------+
| DAYNAME('2006-06-12') |
+-----------------------+
| Monday                |
+-----------------------+
1 row in set (0.00 sec)

DAYOFWEEK函数：获取日期对应的周索引

DAYOFWEEK(d)函数返回d对应的一周中的索引（位置）。1 表示周日，2 表示周一，……，7 表示周六。这些索引值对应于 ODBC 标准。

1
2
3
4
5
6
7
mysql> SELECT DAYOFWEEK('2017-12-15');
+-------------------------+
| DAYOFWEEK('2017-12-15') |
+-------------------------+
|                       6 |
+-------------------------+
1 row in set (0.04 sec)

由运行结果可知，2017 年 12 月 15 日为周五，因此返回其对应的索引值为 6。

WEEK函数：获取指定日期是一年中的第几周

WEEK()函数计算日期date是一年中的第几周。WEEK(date,mode)函数允许指定星期是否起始于周日或周一，以及返回值的范围是否为 052 或 153。

WEEK函数接受两个参数：

  • date是要获取周数的日期。
  • mode是一个可选参数，用于确定周数计算的逻辑。

如果忽略mode参数，默认情况下WEEK函数将使用default_week_format系统变量的值。要获取default_week_format变量的当前值，请使用SHOW VARIABLES语句：

1
2
3
4
5
6
7
mysql> SHOW VARIABLES LIKE 'default_week_format';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| default_week_format | 0     |
+---------------------+-------+
1 row in set
1
2
3
4
5
6
7
mysql> SELECT WEEK('2018-10-25',1);
+----------------------+
| WEEK('2018-10-25',1) |
+----------------------+
|                   43 |
+----------------------+
1 row in set (0.00 sec)

由运行结果可知，2018-10-25 是一年中的第 43 周。

DAYOFYEAR函数：获取指定日期在一年中的位置

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

1
2
3
4
5
6
7
mysql> SELECT DAYOFYEAR('2017-12-15');
+-------------------------+
| DAYOFYEAR('2017-12-15') |
+-------------------------+
|                     349 |
+-------------------------+
1 row in set (0.00 sec)

DAYOFMONTH函数：获取指定日期在一个月的位置

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

1
2
3
4
5
6
7
mysql> SELECT DAYOFMONTH('2017-12-15');
+--------------------------+
| DAYOFMONTH('2017-12-15') |
+--------------------------+
|                       15 |
+--------------------------+
1 row in set (0.02 sec)

YEAR函数：获取年份

YEAR()函数可以从指定日期值中来获取年份值。

YEAR()函数需要接受date参数，并返回日期的年份。

1
YEAR(date);

YEAR()函数返回的年份值范围为 1000 到 9999，如果日期为零，YEAR()函数返回 0。

1
2
3
4
5
6
7
mysql> SELECT YEAR(NOW());
+-------------+
| YEAR(NOW()) |
+-------------+
|        2019 |
+-------------+
1 row in set (0.00 sec)

TIME_TO_SEC函数：将时间转换为秒值

TIME_TO_SEC(time)函数返回将参数time转换为秒数的时间值，转换公式为“小时 ×3600+ 分钟 ×60+ 秒”。

1
2
3
4
5
6
7
mysql> SELECT TIME_TO_SEC('15:15:15');
+-------------------------+
| TIME_TO_SEC('15:15:15') |
+-------------------------+
|                   54915 |
+-------------------------+
1 row in set (0.00 sec)

SEC_TO_TIME函数：将秒值转换为时间格式

SEC_TO_TIME(seconds)函数返回将参数seconds转换为小时、分钟和秒数的时间值。

1
2
3
4
5
6
7
mysql> SELECT SEC_TO_TIME('54925');
+----------------------+
| SEC_TO_TIME('54925') |
+----------------------+
| 15:15:25             |
+----------------------+
1 row in set (0.00 sec)

DATE_ADD和ADDDATE函数：向日期添加指定时间间隔

DATE_ADD(date,INTERVAL expr type)和ADDDATE(date,INTERVAL expr type)两个函数的作用相同，都是用于执行日期的加运算。

DATE_ADD()和ADDDATE()函数有两个参数：

  • date是DATE或DATETIME的起始值。
  • INTERVAL expr type是要添加到起始日期值的间隔值。
1
2
3
4
5
6
7
8
9
mysql> SELECT DATE_ADD('2018-10-31 23:59:59',INTERVAL 1 SECOND) AS C1,
    -> DATE_ADD('2018-10-31 23:59:59',INTERVAL '1:1' MINUTE_SECOND) AS C2,
    -> ADDDATE('2018-10-31 23:59:59',INTERVAL 1 SECOND) AS C3;
+---------------------+---------------------+---------------------+
| C1                  | C2                  | C3                  |
+---------------------+---------------------+---------------------+
| 2018-11-01 00:00:00 | 2018-11-01 00:01:00 | 2018-11-01 00:00:00 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
mysql> SELECT ADDDATE('2017-11-30 23:59:59', INTERVAL 1 SECOND) AS col1,
    -> ADDDATE('2017-11-30 23:59:59',INTERVAL '1:1' MINUTE_SECOND) AS col2;
+---------------------+---------------------+
| col1                | col2                |
+---------------------+---------------------+
| 2017-12-01 00:00:00 | 2017-12-01 00:01:00 |
+---------------------+---------------------+
1 row in set (0.02 sec)

DATE_SUB和SUBDATE函数：日期减法运算

DATE_SUB(date,INTERVAL expr type)和SUBDATE(date,INTERVAL expr type)两个函数作用相同，都是执行日期的减法运算。

DATE_SUB()和SUBDATE()函数接受两个参数：

  • date是DATE或DATETIME的起始值。
  • expr是一个字符串，用于确定从起始日期减去的间隔值。type是expr可解析的间隔单位，例如DAY，HOUR等。
1
2
3
4
5
6
7
8
9
mysql> SELECT DATE_SUB('2018-01-02',INTERVAL 31 DAY) AS C1,
    -> SUBDATE('2018-01-02',INTERVAL 31 DAY) AS C2,
    -> DATE_SUB('2018-01-01 00:01:00',INTERVAL '0 0:1:1' DAY_SECOND) AS C3;
+------------+------------+---------------------+
| C1         | C2         | C3                  |
+------------+------------+---------------------+
| 2017-12-02 | 2017-12-02 | 2017-12-31 23:59:59 |
+------------+------------+---------------------+
1 row in set (0.00 sec)

提示：DATE_ADD(date,INTERVAL expr type)和DATE_SUB(date,INTERVAL expr type)函数在指定加减的时间段时也可以指定负值，加法的负值即返回原始时间之前的日期和时间，减法的负值即返回原始时间之后的日期和时间。

ADDTIME函数：时间加法运算

ADDTIME(time,expr)函数用于执行时间的加法运算。添加expr到time并返回结果。

其中：time是一个时间或日期时间表达式，expr是一个时间表达式。

1
2
3
4
5
6
7
mysql> SELECT ADDTIME('2018-10-31 23:59:59','0:1:1'), ADDTIME('10:30:59','5:10:37');
+----------------------------------------+-------------------------------+
| ADDTIME('2018-10-31 23:59:59','0:1:1') | ADDTIME('10:30:59','5:10:37') |
+----------------------------------------+-------------------------------+
| 2018-11-01 00:01:00                    | 15:41:36                      |
+----------------------------------------+-------------------------------+
1 row in set (0.00 sec)

SUBTIME函数：时间减法运算

SUBTIME(time,expr)函数用于执行时间的减法运算。

其中：函数返回time。expr表示的值和格式time相同。time是一个时间或日期时间表达式，expr是一个时间。

1
2
3
4
5
6
7
mysql> SELECT SUBTIME('2018-10-31 23:59:59','0:1:1'), SUBTIME('10:30:59','5:12:37');
+----------------------------------------+-------------------------------+
| SUBTIME('2018-10-31 23:59:59','0:1:1') | SUBTIME('10:30:59','5:12:37') |
+----------------------------------------+-------------------------------+
| 2018-10-31 23:58:58                    | 05:18:22                      |
+----------------------------------------+-------------------------------+
1 row in set (0.00 sec)

DATEDIFF函数：获取两个日期的时间间隔

DATEDIFF(date1，date2)返回起始时间date1和结束时间date2之间的天数。date1和date2为日期或date-and-time表达式。计算时只用到这些值的日期部分。

1
2
3
4
5
6
7
mysql> SELECT DATEDIFF('2017-11-30','2017-11-29') AS COL1, DATEDIFF('2017-11-30','2017-12-15') AS col2;
+------+------+
| COL1 | col2 |
+------+------+
|    1 |  -15 |
+------+------+
1 row in set (0.00 sec)

由运行结果可知，DATEDIFF()函数返回date1-date2后的值，因此DATEDIFF('2017-11-30'，'2017-11-29')的返回值为 1，DATEDIFF('2017-11-30 23：59：59'，'2017-12-15')的返回值为 -15。

DATE_FORMAT函数：格式化指定的日期

DATE_FORMAT(date，format)函数是根据format指定的格式显示date值。

DATE_FORMAT()函数接受两个参数：

  • date：是要格式化的有效日期值
  • format：是由预定义的说明符组成的格式字符串，每个说明符前面都有一个百分比字符(%)。

主要的format格式如下表所示。

说明符 说明
%a 工作日的缩写名称(Sun~Sat)
%b 月份的缩写名称(Jan…Dec)
%c 月份，数字形式(0~12)
%D 带有英语后缀的该月日期(0th, 2st, 3nd,…）
%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小时(1~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
2
3
4
5
6
7
8
mysql> SELECT DATE_FORMAT('2017-11-15 21:45:00','%W %M %D %Y') AS col1,
    -> DATE_FORMAT('2017-11-15 21:45:00','%h:i% %p %M %D %Y') AS col2;
+------------------------------+----------------------------+
| col1                         | col2                       |
+------------------------------+----------------------------+
| Wednesday November 15th 2017 | 09:i PM November 15th 2017 |
+------------------------------+----------------------------+
1 row in set (0.03 sec)

WEEKDAY函数：获取指定日期在一周内的索引位置

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

1
2
3
4
5
6
7
mysql> SELECT WEEKDAY('2017-12-15');
+-----------------------+
| WEEKDAY('2017-12-15') |
+-----------------------+
|                     4 |
+-----------------------+
1 row in set (0.00 sec)

由运行结果可以看出，WEEKDAY()和DAYOFWEEK()函数都是返回指定日期在某一周内的位置，只是索引编号不同。

流程控制函数

IF函数：判断

IF语句允许您根据表达式的某个条件或值结果来执行一组 SQL 语句。

要在 MySQL 中形成一个表达式，可以结合文字，变量，运算符，甚至函数来组合。表达式可以返回TRUE,FALSE或NULL，这三个值之一。

1
IF(expr,v1,v2)

其中：表达式expr得到不同的结果，当expr为真是返回v1的值，否则返回v2。

使用IF(expr,v1,v2)函数根据expr表达式结果返回相应值。

1
2
3
4
5
6
7
mysql> SELECT IF(1<2,1,0) c1, IF(1>5,'√','×') c2, IF(STRCMP('abc','ab'),'yes','no') c3;
+----+----+-----+
| c1 | c2 | c3  |
+----+----+-----+
|  1 | × | yes |
+----+----+-----+
1 row in set, 2 warnings (0.00 sec)

在c3中，先用STRCMP(s1,s2)函数比较两个字符串的大小，字符串'abc'和'ab'比较结果的返回值为 1，也就是表达式expr的返回结果不等于 0 且不等于NULL，则返回值为v1，即字符串'yes'。

IFNULL函数：判断是否为空

IFNULL函数是 MySQL 控制流函数之一，它接受两个参数，如果不是NULL，则返回第一个参数。 否则，IFNULL函数返回第二个参数。两个参数可以是文字值或表达式。

1
IFNULL(v1,v2);

其中：如果v1不为NULL，则IFNULL函数返回v1; 否则返回v2的结果。

1
2
3
4
5
6
7
mysql> SELECT IFNULL(5,8), IFNULL(NULL,'OK'), IFNULL(SQRT(-8),'FALSE'), SQRT(-8);
+-------------+-------------------+--------------------------+----------+
| IFNULL(5,8) | IFNULL(NULL,'OK') | IFNULL(SQRT(-8),'FALSE') | SQRT(-8) |
+-------------+-------------------+--------------------------+----------+
|           5 | OK                | FALSE                    |     NULL |
+-------------+-------------------+--------------------------+----------+
1 row in set (0.00 sec)

CASE函数：搜索语句

除了IF函数，MySQL 还提供了一个替代的条件语句CASE。CASE语句有两种形式：简单的和可搜索CASE语句。

简单的 CASE 语句

简单的CASE语句就是指使用简单CASE语句来检查表达式的值与一组唯一值的匹配。

1
2
3
4
5
6
CASE  <表达式>
   WHEN <值1> THEN <操作>
   WHEN <值2> THEN <操作>
   ...
   ELSE <操作>
END CASE;

其中：<表达式> 可以是任何有效的表达式。我们将 <表达式> 的值与每个WHEN子句中的值进行比较，例如 <值1>，<值2> 等。如果 <表达式> 和 <值n> 的值相等，则执行相应的WHEN分支中的命令 <操作>。如果WHEN子句中的 <值n> 没有与 <表达式> 的值匹配，则ELSE子句中的命令将被执行。ELSE子句是可选的。 如果省略ELSE子句，并且找不到匹配项，MySQL 将引发错误。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT CASE WEEKDAY(NOW())
    WHEN 0 THEN '星期一'
    WHEN 1 THEN '星期二'
    WHEN 2 THEN '星期三'
    WHEN 3 THEN '星期四'
    WHEN 4 THEN '星期五'
    WHEN 5 THEN '星期六'
    ELSE '星期天'
    END AS COLUMN1,NOW(), WEEKDAY(NOW()), DAYNAME(NOW());
+---------+---------------------+----------------+----------------+
| COLUMN1 | NOW()               | WEEKDAY(NOW()) | DAYNAME(NOW()) |
+---------+---------------------+----------------+----------------+
| 星期四  | 2019-02-28 13:45:43 |              3 | Thursday       |
+---------+---------------------+----------------+----------------+
1 row in set, 7 warnings (0.00 sec)

由执行结果可以看出，NOW()函数得到当前系统时间是 2019 年 2 月 28 日，DAYNAME(NOW())得到当天是'Thursday'，WEEKDAY(NOW())函数返回当前时间的工作日索引是 3，即对应的是星期四。

可搜索的 CASE 语句

简单CASE语句仅允许将表达式的值与一组不同的值进行匹配。 为了执行更复杂的匹配，如范围，则可以使用可搜索CASE语句。 可搜索CASE语句等同于IF语句，但是它的构造更加可读。

1
2
3
4
5
6
CASE
    WHEN <条件1> THEN <命令>
    WHEN <条件2> THEN <命令>
    ...
    ELSE commands
END CASE;

MySQL 分别计算WHEN子句中的每个条件，直到找到一个值为TRUE的条件，然后执行THEN子句中的相应 <命令>。如果没有一个条件为TRUE，则执行ELSE子句中的 <命令>。如果不指定ELSE子句，并且没有一个条件为TRUE，MySQL 将发出错误消息。MySQL 不允许在THEN或ELSE子句中使用空的命令。 如果您不想处理ELSE子句中的逻辑，同时又要防止 MySQL 引发错误，则可以在ELSE子句中放置一个空的BEGIN END块。

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT CASE WHEN WEEKDAY(NOW())=0 THEN '星期一' WHEN WEEKDAY(NOW())=1 THE
N '星期二'  WHEN WEEKDAY(NOW())=2 THEN '星期三' WHEN WEEKDAY(NOW())=3 THEN '星期
四' WHEN WEEKDAY(NOW())=4 THEN '星期五' WHEN WEEKDAY(NOW())=5 THEN '星期六' WHEN
WEEKDAY(NOW())=6 THEN '星期天' END AS COLUMN1,NOW(),WEEKDAY(NOW()),DAYNAME(NOW(
));
+---------+---------------------+----------------+----------------+
| COLUMN1 | NOW()               | WEEKDAY(NOW()) | DAYNAME(NOW()) |
+---------+---------------------+----------------+----------------+
| 星期四  | 2019-02-28 14:08:00 |              3 | Thursday       |
+---------+---------------------+----------------+----------------+
1 row in set, 7 warnings (0.00 sec)

此例跟上例的返回结果一样，只是使用了CASE函数的不同写法，WHEN后面为表达式，当表达式的返回结果为TRUE时取THEN后面的值，如果都不是，则返回ELSE后面的值。

打赏
  • Copyrights © 2017-2023 WSQ
  • 访问人数: | 浏览次数:

请我喝杯咖啡吧~

支付宝
微信