第10篇：mysql常用函数汇总-Java充电社【公众号：Java充电社】

打算提升sql技能的，可以加我微信itsoku，带你成为sql高手。

这是Mysql系列第10篇。

环境：mysql5.7.25，cmd命令中进行演示。

## 目录
[TOC]

## MySQL 数值型函数

| 函数名称        | 作 用                                                      |
| --------------- | ---------------------------------------------------------- |
| abs             | 求绝对值                                                   |
| sqrt            | 求二次方根                                                 |
| mod             | 求余数                                                     |
| ceil 和 ceiling | 两个函数功能相同，都是返回不小于参数的最小整数，即向上取整 |
| floor           | 向下取整，返回值转化为一个BIGINT                           |
| rand            | 生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列  |
| round           | 对所传参数进行四舍五入                                     |
| sign            | 返回参数的符号                                             |
| pow 和 power    | 两个函数的功能相同，都是所传参数的次方的结果值             |
| sin             | 求正弦值                                                   |
| asin            | 求反正弦值，与函数 SIN 互为反函数                          |
| cos             | 求余弦值                                                   |
| acos            | 求反余弦值，与函数 COS 互为反函数                          |
| tan             | 求正切值                                                   |
| atan            | 求反正切值，与函数 TAN 互为反函数                          |
| cot             | 求余切值                                                   |

### abs:求绝对值

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

```java
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.00 sec)
```

### sqrt:求二次方跟（开方）

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

### mod:求余数

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

```java
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.00 sec)
```

### ceil和ceiling:向上取整

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

```java
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 的最大整数值。

```java
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:生成一个随机数

> 生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列

```java
mysql> select rand(), rand(), rand();
+--------------------+--------------------+--------------------+
| rand()             | rand()             | rand()             |
+--------------------+--------------------+--------------------+
| 0.5224735778965741 | 0.3678060549942833 | 0.2716095720153391 |
+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)

mysql> select rand(1),rand(2),rand(1);
+---------------------+--------------------+---------------------+
| rand(1)             | rand(2)            | rand(1)             |
+---------------------+--------------------+---------------------+
| 0.40540353712197724 | 0.6555866465490187 | 0.40540353712197724 |
+---------------------+--------------------+---------------------+
1 row in set (0.00 sec)

### round:四舍五入函数

> 返回最接近于参数 x 的整数；ROUND(x,y) 函数对参数x进行四舍五入的操作，返回值保留小数点后面指定的y位。

```java
mysql> select round(-6.6),round(-8.44),round(3.44);
+-------------+--------------+-------------+
| round(-6.6) | round(-8.44) | round(3.44) |
+-------------+--------------+-------------+
|          -7 |           -8 |           3 |
+-------------+--------------+-------------+
1 row in set (0.00 sec)

mysql> select round(-6.66,1),round(3.33,3),round(88.66,-1),round(88.46,-2);
+----------------+---------------+-----------------+-----------------+
| round(-6.66,1) | round(3.33,3) | round(88.66,-1) | round(88.46,-2) |
+----------------+---------------+-----------------+-----------------+
|           -6.7 |         3.330 |              90 |             100 |
+----------------+---------------+-----------------+-----------------+
1 row in set (0.00 sec)
```

### sign:返回参数的符号

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

```java
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 次方。

```java
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 为弧度值。

```java
mysql> select sin(1),sin(0.5*pi()),pi();
+--------------------+---------------+----------+
| sin(1)             | sin(0.5*pi()) | pi()     |
+--------------------+---------------+----------+
| 0.8414709848078965 |             1 | 3.141593 |
+--------------------+---------------+----------+
1 row in set (0.00 sec)
```

> 注：PI() 函数返回圆周率（3.141593）

**其他几个三角函数在此就不说了，有兴趣的可以自己去练习一下。**

## MySQL 字符串函数

| 函数名称            | 作 用                                                        |
| ------------------- | ------------------------------------------------------------ |
| length              | 计算字符串长度函数，返回字符串的字节长度                     |
| concat              | 合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个 |
| insert              | 替换字符串函数                                               |
| lower               | 将字符串中的字母转换为小写                                   |
| upper               | 将字符串中的字母转换为大写                                   |
| left                | 从左侧字截取符串，返回字符串左边的若干个字符                 |
| right               | 从右侧字截取符串，返回字符串右边的若干个字符                 |
| trim                | 删除字符串左右两侧的空格                                     |
| replace             | 字符串替换函数，返回替换后的新字符串                         |
| substr 和 substring | 截取字符串，返回从指定位置开始的指定长度的字符换             |
| reverse             | 字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串     |

### length:返回字符串直接长度

> 返回值为字符串的字节长度，使用 uft8（UNICODE 的一种变长字符编码，又称万国码）编码字符集时，一个汉字是 3 个字节，一个数字或字母是一个字节。

```java
mysql> select length('javacode2018'),length('路人甲Java'),length('路人');
+------------------------+-------------------------+------------------+
| length('javacode2018') | length('路人甲Java')    | length('路人')   |
+------------------------+-------------------------+------------------+
|                     12 |                      13 |                6 |
+------------------------+-------------------------+------------------+
1 row in set (0.00 sec)
```

### concat:合并字符串

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

```java
mysql> select concat('路人甲','java'),concat('路人甲',null,'java');
+----------------------------+---------------------------------+
| concat('路人甲','java')    | concat('路人甲',null,'java')    |
+----------------------------+---------------------------------+
| 路人甲java                 | NULL                            |
+----------------------------+---------------------------------+
1 row in set (0.00 sec)
```

### insert:替换字符串

> INSERT(s1，x，len，s2) 返回字符串 s1，子字符串起始于 x 位置，并且用 len 个字符长的字符串代替 s2。
>
> x的值从1开始，第一个字符的x=1，若 x 超过字符串长度，则返回值为原始字符串。
>
> 假如 len 的长度大于其他字符串的长度，则从位置 x 开始替换。
>
> 若任何一个参数为 NULL，则返回值为 NULL。

```java
mysql> select
    ->   insert('路人甲Java', 2, 4, '**') AS col1,
    ->   insert('路人甲Java', -1, 4,'**') AS col2,
    ->   insert('路人甲Java', 3, 20,'**') AS col3;
+---------+---------------+----------+
| col1    | col2          | col3     |
+---------+---------------+----------+
| 路**va  | 路人甲Java    | 路人**   |
+---------+---------------+----------+
1 row in set (0.00 sec)
```

### lower:将字母转换成小写

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

```java
mysql> select lower('路人甲JAVA');
+------------------------+
| lower('路人甲JAVA')    |
+------------------------+
| 路人甲java             |
+------------------------+
1 row in set (0.00 sec)
```

### upper:将字母转换成大写

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

```java
mysql> select upper('路人甲java');
+------------------------+
| upper('路人甲java')    |
+------------------------+
| 路人甲JAVA             |
+------------------------+
1 row in set (0.00 sec)
```

### left:从左侧截取字符串

> LEFT(s，n) 函数返回字符串 s 最左边的 n 个字符，s=1表示第一个字符。

```java
mysql> select left('路人甲JAVA',2),left('路人甲JAVA',10),left('路人甲JAVA',-1);
+-------------------------+--------------------------+--------------------------+
| left('路人甲JAVA',2)    | left('路人甲JAVA',10)    | left('路人甲JAVA',-1)    |
+-------------------------+--------------------------+--------------------------+
| 路人                    | 路人甲JAVA               |                          |
+-------------------------+--------------------------+--------------------------+
1 row in set (0.00 sec)
```

### right:从右侧截取字符串

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

```java
mysql> select right('路人甲JAVA',1),right('路人甲JAVA',10),right('路人甲JAVA',-1);
+--------------------------+---------------------------+---------------------------+
| right('路人甲JAVA',1)    | right('路人甲JAVA',10)    | right('路人甲JAVA',-1)    |
+--------------------------+---------------------------+---------------------------+
| A                        | 路人甲JAVA                |                           |
+--------------------------+---------------------------+---------------------------+
1 row in set (0.00 sec)
```

### trim:删除字符串两侧空格

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

```java
mysql> select '[   路人甲Java   ]',concat('[',trim('   路人甲Java   '),']');
+-----------------------+---------------------------------------------+
| [   路人甲Java   ]    | concat('[',trim('   路人甲Java   '),']')    |
+-----------------------+---------------------------------------------+
| [   路人甲Java   ]    | [路人甲Java]                                |
+-----------------------+---------------------------------------------+
1 row in set (0.00 sec)
```

### replace:字符串替换

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

### substr 和 substring:截取字符串

> substr(str,pos)
>
> substr(str from pos)
>
> substr(str,pos,len)
>
> substr(str from pos for len)
>
> substr()是substring()的同义词。
>
> 没有len参数的形式是字符串str从位置pos开始返回一个子字符串。
>
> 带有len参数的形式是字符串str从位置pos开始返回长度为len的子字符串。 
>
> 使用FROM的形式是标准的SQL语法。 
>
> 也可以对pos使用负值，在这种情况下，子字符串的开头是字符串末尾的pos字符，而不是开头。 在这个函数的任何形式中pos可以使用负值。 
>
> 对于所有形式的substring()，从中提取子串的字符串中第一个字符的位置被认为是1。

```java
/** 第三个字符之后的子字符串：inese **/
SELECT substring('chinese', 3);
/** 倒数第三个字符之后的子字符串：ese **/
SELECT substring('chinese', -3);
/** 第三个字符之后的两个字符：in **/
SELECT substring('chinese', 3, 2);
/** 倒数第三个字符之后的两个字符：es **/
SELECT substring('chinese', -3, 2);
/** 第三个字符之后的子字符串：inese **/
SELECT substring('chinese' FROM 3);
/** 倒数第三个字符之后的子字符串：ese **/
SELECT substring('chinese' FROM -3);
/** 第三个字符之后的两个字符：in **/
SELECT substring('chinese' FROM 3 FOR 2);
/** 倒数第三个字符之后的两个字符：es **/
SELECT substring('chinese' FROM -3 FOR 2);
```

### reverse:反转字符串

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

```java
mysql> select reverse('路人甲Java');
+--------------------------+
| reverse('路人甲Java')    |
+--------------------------+
| avaJ甲人路               |
+--------------------------+
1 row in set (0.00 sec)
```

## MySQL 日期和时间函数

| 函数名称                | 作 用                                                        |
| ----------------------- | ------------------------------------------------------------ |
| curdate 和 current_date | 两个函数作用相同，返回当前系统的日期值                       |
| curtime 和 current_time | 两个函数作用相同，返回当前系统的时间值                       |
| now 和 sysdate          | 两个函数作用相同，返回当前系统的日期和时间值                 |
| unix_timestamp          | 获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数 |
| from_unixtime           | 将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数     |
| month                   | 获取指定日期中的月份                                         |
| monthname               | 获取指定日期中的月份英文名称                                 |
| dayname                 | 获取指定曰期对应的星期几的英文名称                           |
| dayofweek               | 获取指定日期是一周中是第几天，返回值范围是1~7,1=周日 |
| week                    | 获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53 |
| dayofyear               | 获取指定曰期是一年中的第几天，返回值范围是1~366              |
| dayofmonth              | 获取指定日期是一个月中是第几天，返回值范围是1~31             |
| year                    | 获取年份，返回值范围是 1970〜2069                            |
| time_to_sec             | 将时间参数转换为秒数                                         |
| sec_to_time             | 将秒数转换为时间，与TIME_TO_SEC 互为反函数                   |
| date_add 和 adddate     | 两个函数功能相同，都是向日期添加指定的时间间隔               |
| date_sub 和 subdate     | 两个函数功能相同，都是向日期减去指定的时间间隔               |
| addtime                 | 时间加法运算，在原始时间上添加指定的时间                     |
| subtime                 | 时间减法运算，在原始时间上减去指定的时间                     |
| datediff                | 获取两个日期之间间隔，返回参数 1 减去参数 2 的值             |
| date_format             | 格式化指定的日期，根据参数返回指定格式的值                   |
| weekday                 | 获取指定日期在一周内的对应的工作日索引                       |

### curdate 和 current_date:两个函数作用相同，返回当前系统的日期值

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

```java
mysql> select curdate(),current_date(),current_date()+1;
+------------+----------------+------------------+
| curdate()  | current_date() | current_date()+1 |
+------------+----------------+------------------+
| 2019-09-17 | 2019-09-17     |         20190918 |
+------------+----------------+------------------+
1 row in set (0.00 sec)
```

### curtime 和 current_time:获取系统当前时间

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

```java
mysql> select curtime(),current_time(),current_time()+1;
+-----------+----------------+------------------+
| curtime() | current_time() | current_time()+1 |
+-----------+----------------+------------------+
| 16:11:25  | 16:11:25       |           161126 |
+-----------+----------------+------------------+
1 row in set (0.00 sec)
```

### now 和 sysdate:获取当前时间日期

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

```java
mysql> select now(),sysdate();
+---------------------+---------------------+
| now()               | sysdate()           |
+---------------------+---------------------+
| 2019-09-17 16:13:28 | 2019-09-17 16:13:28 |
+---------------------+---------------------+
1 row in set (0.00 sec)
```

### unix_timestamp:获取UNIX时间戳

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

```java
mysql> select unix_timestamp(),unix_timestamp(now()),now(),unix_timestamp('2019-09-17 12:00:00');
+------------------+-----------------------+---------------------+---------------------------------------+
| unix_timestamp() | unix_timestamp(now()) | now()               | unix_timestamp('2019-09-17 12:00:00') |
+------------------+-----------------------+---------------------+---------------------------------------+
|       1568710893 |            1568710893 | 2019-09-17 17:01:33 |                            1568692800 |
+------------------+-----------------------+---------------------+---------------------------------------+
1 row in set (0.00 sec)
```

### from_unixtime:时间戳转日期

> FROM_UNIXTIME(unix_timestamp[,format]) 函数把 UNIX 时间戳转换为普通格式的日期时间值，与 `UNIX_TIMESTAMP ()` 函数互为反函数。
>
> 有2个参数：
>
> unix_timestamp：时间戳（秒）
>
> format：要转化的格式 比如“”%Y-%m-%d“” 这样格式化之后的时间就是 2017-11-30
>
> 可以有的形式：

| 格式          | 说明                                         |
| ------------- | -------------------------------------------- |
| %M            | 月名字(January～December)                    |
| %W            | 星期名字(Sunday～Saturday)                   |
| %D            | 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等） |
| %Y            | 年, 数字, 4 位                               |
| %y            | 年, 数字, 2 位                               |
| %a            | 缩写的星期名字(Sun～Sat)                     |
| %d            | 月份中的天数, 数字(00～31)                   |
| %e            | 月份中的天数, 数字(0～31)                    |
| %m            | 月, 数字(01～12)                             |
| %c            | 月, 数字(1～12)                              |
| %b            | 缩写的月份名字(Jan～Dec)                     |
| %j            | 一年中的天数(001～366)                       |
| %H            | 小时(00～23)                                 |
| %k            | 小时(0～23)                                  |
| %h            | 小时(01～12)                                 |
| %I（i的大写） | 小时(01～12)                                 |
| %l（L的小写） | 小时(1～12)                                  |
| %i            | 分钟, 数字(00～59)                           |
| %r            | 时间,12 小时(hh:mm:ss [AP]M)                 |
| %T            | 时间,24 小时(hh:mm:ss)                       |
| %S            | 秒(00～59)                                   |
| %s            | 秒(00～59)                                   |
| %p            | AM或PM                                       |
| %W            | 一个星期中的天数英文名称(Sunday~Saturday）   |
| %w            | 一个星期中的天数(0=Sunday ～6=Saturday）     |
| %U            | 星期(0～52), 这里星期天是星期的第一天        |
| %u            | 星期(0～52), 这里星期一是星期的第一天        |
| %%            | 输出%                                        |

```java
mysql> select from_unixtime(1568710866),from_unixtime(1568710866,'%Y-%m-%d %H:%h:%s');
+---------------------------+-----------------------------------------------+
| from_unixtime(1568710866) | from_unixtime(1568710866,'%Y-%m-%d %H:%h:%s') |
+---------------------------+-----------------------------------------------+
| 2019-09-17 17:01:06       | 2019-09-17 17:05:06                           |
+---------------------------+-----------------------------------------------+
1 row in set (0.00 sec)
```

### month:获取指定日期的月份

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

```java
mysql> select month('2017-12-15'),month(now());
+---------------------+--------------+
| month('2017-12-15') | month(now()) |
+---------------------+--------------+
|                  12 |            9 |
+---------------------+--------------+
1 row in set (0.00 sec)
```

### monthname:获取指定日期月份的英文名称

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

### dayname:获取指定日期的星期名称

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

```java
mysql> select now(),dayname(now());
+---------------------+----------------+
| now()               | dayname(now()) |
+---------------------+----------------+
| 2019-09-17 17:13:08 | Tuesday        |
+---------------------+----------------+
1 row in set (0.00 sec)
```

### dayofweek:获取日期对应的周索引

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

```java
mysql> select now(),dayofweek(now());
+---------------------+------------------+
| now()               | dayofweek(now()) |
+---------------------+------------------+
| 2019-09-17 17:14:21 |                3 |
+---------------------+------------------+
1 row in set (0.00 sec)
```

### week:获取指定日期是一年中的第几周

> WEEK(date[,mode]) 函数计算日期 date 是一年中的第几周。WEEK(date,mode) 函数允许指定星期是否起始于周日或周一，以及返回值的范围是否为 0～52 或 1～53。
>
> WEEK函数接受两个参数：
>
> - `date`是要获取周数的日期。
> - `mode`是一个可选参数，用于确定周数计算的逻辑。它允许您指定本周是从星期一还是星期日开始，返回的周数应在`0`到`52`之间或`0`到`53`之间。
>
> 如果忽略`mode`参数，默认情况下`WEEK`函数将使用`default_week_format`系统变量的值。
>
> 要获取`default_week_format`变量的当前值，请使用`SHOW VARIABLES`语句如下：

```java
mysql> SHOW VARIABLES LIKE 'default_week_format';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| default_week_format | 0     |
+---------------------+-------+
1 row in set, 1 warning (0.00 sec)
```
在我们的服务器中，`default_week_format`的默认值为`0`，下表格说明了`mode`参数如何影响`WEEK`函数：

| 模式 | 一周的第一天 | 范围 |
| :--- | :----------- | :--- |
| 0    | 星期日       | 0-53 |
| 1    | 星期一       | 0-53 |
| 2    | 星期日       | 1-53 |
| 3    | 星期一       | 1-53 |
| 4    | 星期日       | 0-53 |
| 5    | 星期一       | 0-53 |
| 6    | 星期日       | 1-53 |
| 7    | 星期一       | 1-53 |

上表中“今年有4天以上”表示：

- 如果星期包含1月1日，并且在新的一年中有`4`天或更多天，那么这周是第`1`周。
 - 否则，这一周的数字是前一年的最后一周，下周是第1周。

```java
mysql> select now(),week(now());
+---------------------+-------------+
| now()               | week(now()) |
+---------------------+-------------+
| 2019-09-17 17:20:28 |          37 |
+---------------------+-------------+
1 row in set (0.00 sec)
```

### dayofyear:获取指定日期在一年中的位置

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

```java
mysql> select now(),dayofyear(now()),dayofyear('2019-01-01');
+---------------------+------------------+-------------------------+
| now()               | dayofyear(now()) | dayofyear('2019-01-01') |
+---------------------+------------------+-------------------------+
| 2019-09-17 17:22:00 |              260 |                       1 |
+---------------------+------------------+-------------------------+
1 row in set (0.00 sec)
```

### dayofmonth:获取指定日期在一个月的位置

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

```java
mysql> select now(),dayofmonth(now()),dayofmonth('2019-01-01');
+---------------------+-------------------+--------------------------+
| now()               | dayofmonth(now()) | dayofmonth('2019-01-01') |
+---------------------+-------------------+--------------------------+
| 2019-09-17 17:23:09 |                17 |                        1 |
+---------------------+-------------------+--------------------------+
1 row in set (0.00 sec)
```

### year:获取年份

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

```java
mysql> select now(),year(now()),year('2019-01-02');
+---------------------+-------------+--------------------+
| now()               | year(now()) | year('2019-01-02') |
+---------------------+-------------+--------------------+
| 2019-09-17 17:28:10 |        2019 |               2019 |
+---------------------+-------------+--------------------+
1 row in set (0.00 sec)
```

### time_to_sec:将时间转换为秒值

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

```java
mysql> select time_to_sec('15:15:15'),now(),time_to_sec(now());
+-------------------------+---------------------+--------------------+
| time_to_sec('15:15:15') | now()               | time_to_sec(now()) |
+-------------------------+---------------------+--------------------+
|                   54915 | 2019-09-17 17:30:44 |              63044 |
+-------------------------+---------------------+--------------------+
1 row in set (0.00 sec)
```

### sec_to_time:将秒值转换为时间格式

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

```java
mysql> select sec_to_time(100),sec_to_time(10000);
+------------------+--------------------+
| sec_to_time(100) | sec_to_time(10000) |
+------------------+--------------------+
| 00:01:40         | 02:46:40           |
+------------------+--------------------+
1 row in set (0.00 sec)
```

### date_add和adddate:向日期添加指定时间间隔

> DATE_ADD(date,INTERVAL expr type)
>
> date：参数是合法的日期表达式。*expr* 参数是您希望添加的时间间隔。
>
> type：参数可以是下列值

```java
mysql> select date_add('2019-01-01',INTERVAL 10 day),adddate('2019-01-01 16:00:00',interval 100 SECOND);
+----------------------------------------+----------------------------------------------------+
| date_add('2019-01-01',INTERVAL 10 day) | adddate('2019-01-01 16:00:00',interval 100 SECOND) |
+----------------------------------------+----------------------------------------------------+
| 2019-01-11                             | 2019-01-01 16:01:40                                |
+----------------------------------------+----------------------------------------------------+
1 row in set (0.00 sec)
    
mysql> select date_add('2019-01-01',INTERVAL -10 day),adddate('2019-01-01 16:00:00',interval -100 SECOND);
+-----------------------------------------+-----------------------------------------------------+
| date_add('2019-01-01',INTERVAL -10 day) | adddate('2019-01-01 16:00:00',interval -100 SECOND) |
+-----------------------------------------+-----------------------------------------------------+
| 2018-12-22                              | 2019-01-01 15:58:20                                 |
+-----------------------------------------+-----------------------------------------------------+
1 row in set (0.00 sec)
```

### date_sub和subdate:日期减法运算

> DATE_SUB(date,INTERVAL expr type)
>
> date：参数是合法的日期表达式。*expr* 参数是您希望添加的时间间隔。
>
> type的类型和date_add中的type一样。

```java
mysql> select date_sub('2019-01-01',INTERVAL 10 day),subdate('2019-01-01 16:00:00',interval 100 SECOND);
+----------------------------------------+----------------------------------------------------+
| date_sub('2019-01-01',INTERVAL 10 day) | subdate('2019-01-01 16:00:00',interval 100 SECOND) |
+----------------------------------------+----------------------------------------------------+
| 2018-12-22                             | 2019-01-01 15:58:20                                |
+----------------------------------------+----------------------------------------------------+
1 row in set (0.00 sec)
    
mysql> select date_sub('2019-01-01',INTERVAL -10 day),subdate('2019-01-01 16:00:00',interval -100 SECOND);
+-----------------------------------------+-----------------------------------------------------+
| date_sub('2019-01-01',INTERVAL -10 day) | subdate('2019-01-01 16:00:00',interval -100 SECOND) |
+-----------------------------------------+-----------------------------------------------------+
| 2019-01-11                              | 2019-01-01 16:01:40                                 |
+-----------------------------------------+-----------------------------------------------------+
1 row in set (0.00 sec)
```

### addtime:时间加法运算

> ADDTIME(time,expr) 函数用于执行时间的加法运算。添加 expr 到 time 并返回结果。
>
> 其中：time 是一个时间或日期时间表达式，expr 是一个时间表达式。

```java
mysql> select addtime('2019-09-18 23:59:59','0:1:1'), addtime('10:30:59','5:10:37');
+----------------------------------------+-------------------------------+
| addtime('2019-09-18 23:59:59','0:1:1') | addtime('10:30:59','5:10:37') |
+----------------------------------------+-------------------------------+
| 2019-09-19 00:01:00                    | 15:41:36                      |
+----------------------------------------+-------------------------------+
1 row in set (0.00 sec)
```

### subtime:时间减法运算

>SUBTIME(time,expr) 函数用于执行时间的减法运算。
>
>函数返回 time。expr 表示的值和格式 time 相同。time 是一个时间或日期时间表达式， expr 是一个时间。

```java
mysql> select subtime('2019-09-18 23:59:59','0:1:1'),subtime('10:30:59','5:12:37');
+----------------------------------------+-------------------------------+
| subtime('2019-09-18 23:59:59','0:1:1') | subtime('10:30:59','5:12:37') |
+----------------------------------------+-------------------------------+
| 2019-09-18 23:58:58                    | 05:18:22                      |
+----------------------------------------+-------------------------------+
1 row in set (0.00 sec)
```

### datediff:获取两个日期的时间间隔

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

```java
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)
```

### date_format:格式化指定的日期

> DATE_FORMAT(date，format) 函数是根据 format 指定的格式显示 date 值。
>
> DATE_FORMAT() 函数接受两个参数：
>
> date：是要格式化的有效日期值format：是由预定义的说明符组成的格式字符串，每个说明符前面都有一个百分比字符(%)。
>
> format：格式和上面的函数`from_unixtime`中的format一样，可以参考上面的。

```java
mysql> select date_format('2017-11-30','%Y%m%d') as col0,now() as col1, date_format(now(),'%Y%m%d%H%i%s') as col2;
+----------+---------------------+----------------+
| col0     | col1                | col2           |
+----------+---------------------+----------------+
| 20171130 | 2019-09-17 17:56:12 | 20190917175612 |
+----------+---------------------+----------------+
1 row in set (0.00 sec)
```

### weekday:获取指定日期在一周内的索引位置

> WEEKDAY(date) 返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。

```java
mysql> select now(),weekday(now());
+---------------------+----------------+
| now()               | weekday(now()) |
+---------------------+----------------+
| 2019-09-17 18:01:34 |              1 |
+---------------------+----------------+
1 row in set (0.00 sec)

mysql> select now(),dayofweek(now());
+---------------------+------------------+
| now()               | dayofweek(now()) |
+---------------------+------------------+
| 2019-09-17 18:01:34 |                3 |
+---------------------+------------------+
1 row in set (0.00 sec)
```

## MySQL 聚合函数

| 函数名称 | 作用                             |
| -------- | -------------------------------- |
| max      | 查询指定列的最大值               |
| min      | 查询指定列的最小值               |
| count    | 统计查询结果的行数               |
| sum      | 求和，返回指定列的总和           |
| avg      | 求平均值，返回指定列数据的平均值 |

## MySQL 流程控制函数
| 函数名称 | 作用           |
| -------- | -------------- |
| if       | 判断，流程控制 |
| ifnull   | 判断是否为空   |
| case     | 搜索语句       |

### if:判断

> IF(expr,v1,v2)
>
> 当 expr 为真是返回 v1 的值，否则返回 v2

```java
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 (0.00 sec)
```

### ifnull:判断是否为空

> IFNULL(v1,v2)：v1为空返回v2，否则返回v1。

```java
mysql> select ifnull(null,'路人甲Java'),ifnull('非空','为空');
+------------------------------+---------------------------+
| ifnull(null,'路人甲Java')    | ifnull('非空','为空')     |
+------------------------------+---------------------------+
| 路人甲Java                   | 非空                      |
+------------------------------+---------------------------+
1 row in set (0.00 sec)
```

### case:搜索语句,类似于java中的if..else if..else

> 类似于java中的if..else if..else

**有2种写法**

**方式1：**

```
CASE  <表达式>
   WHEN <值1> THEN <操作>
   WHEN <值2> THEN <操作>
   ...
   ELSE <操作>
END CASE;
```

**方式2：**

```
CASE
    WHEN <条件1> THEN <命令>
    WHEN <条件2> THEN <命令>
    ...
    ELSE commands
END CASE;
```

示例：

准备数据：

```java
CREATE TABLE t_stu (
  id   INT AUTO_INCREMENT  COMMENT '编号',
  name VARCHAR(10) COMMENT '姓名',
  sex  TINYINT COMMENT '性别,0:未知,1:男,2:女',
  PRIMARY KEY (id)
) COMMENT '学生表';

insert into t_stu (name,sex) VALUES 
  ('张学友',1),
  ('刘德华',1),
  ('郭富城',1),
  ('蔡依林',2),
  ('xxx',0);
```

```java
mysql> select * from t_stu;
+----+-----------+------+
| id | name      | sex  |
+----+-----------+------+
|  1 | 张学友    |    1 |
|  2 | 刘德华    |    1 |
|  3 | 郭富城    |    1 |
|  4 | 蔡依林    |    2 |
|  5 | xxx       |    0 |
+----+-----------+------+
5 rows in set (0.00 sec)
```

需求：查询所有学生信息，输出：姓名，性别（男、女、未知），如下：

```java
mysql> SELECT
      t.name 姓名,
      (CASE t.sex
       WHEN 1
         THEN '男'
       WHEN 2
         THEN '女'
       ELSE '未知' END) 性别
    FROM t_stu t;
+-----------+--------+
| 姓名      | 性别   |
+-----------+--------+
| 张学友    | 男     |
| 刘德华    | 男     |
| 郭富城    | 男     |
| 蔡依林    | 女     |
| xxx       | 未知   |
+-----------+--------+
5 rows in set (0.00 sec)

mysql> SELECT
    t.name          姓名,
    (CASE
    WHEN t.sex = 1
    THEN '男'
    WHEN t.sex = 2
    THEN '女'
    ELSE '未知' END) 性别
    FROM t_stu t;
+-----------+--------+
| 姓名      | 性别   |
+-----------+--------+
| 张学友    | 男     |
| 刘德华    | 男     |
| 郭富城    | 男     |
| 蔡依林    | 女     |
| xxx       | 未知   |
+-----------+--------+
5 rows in set (0.00 sec)
```

## 其他函数

| 函数名称 | 作用                |
| -------- | ------------------- |
| version  | 数据库版本号        |
| database | 当前的数据库        |
| user     | 当前连接用户        |
| password | 返回字符串密码形式  |
| md5      | 返回字符串的md5数据 |

```java
mysql> SELECT version();
+------------+
| version()  |
+------------+
| 5.7.25-log |
+------------+
1 row in set (0.00 sec)

mysql> SELECT database();
+--------------+
| database()   |
+--------------+
| javacode2018 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT  password('123456');
+-------------------------------------------+
| password('123456')                        |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT md5('123456');
+----------------------------------+
| md5('123456')                    |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+
1 row in set (0.00 sec)
```

**今天列的函数比较多，大家搜藏一下，慢慢消化，喜欢的帮忙转发一下，谢谢。**

<a style="display:none" target="_blank" href="https://mp.weixin.qq.com/s/_S1DD2JADnXvpexxaBwLLg" style="color:red; font-size:20px; font-weight:bold">继续收门徒，亲手带，月薪 4W 以下的可以来找我</a>

## 最新资料

1. <a href="https://mp.weixin.qq.com/s?__biz=MzkzOTI3Nzc0Mg==&mid=2247484964&idx=2&sn=c81bce2f26015ee0f9632ddc6c67df03&scene=21#wechat_redirect" target="_blank">尚硅谷 Java 学科全套教程（总 207.77GB）</a>
2. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484192&idx=1&sn=505f2faaa4cc911f553850667749bcbb&scene=21#wechat_redirect" target="_blank">2021 最新版 Java 微服务学习线路图 + 视频</a>
3. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484573&idx=1&sn=7f3d83892186c16c57bc0b99f03f1ffd&scene=21#wechat_redirect" target="_blank">阿里技术大佬整理的《Spring 学习笔记.pdf》</a>
4. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484544&idx=2&sn=c1dfe907cfaa5b9ae8e66fc247ccbe84&scene=21#wechat_redirect" target="_blank">阿里大佬的《MySQL 学习笔记高清.pdf》</a>
5. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247485167&idx=1&sn=48d75c8e93e748235a3547f34921dfb7&scene=21#wechat_redirect" target="_blank">2021 版 java 高并发常见面试题汇总.pdf</a>
6. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247485664&idx=1&sn=435f9f515a8f881642820d7790ad20ce&scene=21#wechat_redirect" target="_blank">Idea 快捷键大全.pdf</a>

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/1/2883e86e-3eff-404a-8943-0066e5e2b454.png)