1. MySQL函数
1.1. 分类
1.1.1. 聚合函数
| 函数名 | 描述 | 备注 |
|---|---|---|
| MAX(expression) | 返回字段expression中的最大值 | |
| MIN(expression) | 返回字段expression中的最小值 | |
| MOD(x,y) | 返回x除以y以后的余数 | x,y任意一个为Null结果都为NULL |
| PI() | 返回圆周率(3.141593） | |
| POW(x,y) | 返回x的y次方 | |
| COUNT([DISTINCT] expr1,expr2,…) | 统计参数不为Null的个数 | |
| SUM([DISTINCT] expr) | 对参数求和 | 忽略NULL值 |
| COUNT([DISTINCT] expr1,expr2,…) | 统计参数不为Null的个数 | |
| SUM([DISTINCT] expr) | 对参数求和 | 忽略NULL值 |
| GROUP_CONCAT([DISTINCT] expr ) | 将分组结果的某列或表达式以字符串形式返回 | 额外参数：ORDER BY、SEPARATOR |
1.1.2. 数学函数
| 函数名 | 描述 | 备注 |
|---|---|---|
| MAX(expression) | 返回字段 expression 中的最大值 | |
| MIN(expression) | 返回字段 expression 中的最小值 | |
| PI() | 返回圆周率(3.141593） | |
| POW(x,y) | 返回 x 的 y 次方 | |
| RAND() | 返回 0 到 1 的随机数 | |
| ROUND(x) | 返回离 x 最近的整数（遵循四舍五入） | |
| ROUND(x,y) | 返回指定位数的小数（遵循四舍五入） | |
| TRUNCATE(x,y) | 返回指定位数的小数（截断，不会四舍五入） | |
| ABS(x) | 返回 x 的绝对值 | |
| CEIL(x) | 返回大于或等于 x 的最小整数 | |
| FLOOR(x) | 返回小于或等于 x 的最大整数 | |
| GREATEST(expr1, expr2, expr3, …) | 返回列表中的最大值 | |
| LEAST(expr1, expr2, expr3, …) | 返回列表中的最小值 |
1.1.3. 字符串函数
| 函数 | 描述 | 备注 |
|---|---|---|
| CHAR_LENGTH(s) | 返回字符串 s 的字符数 | |
| CHARACTER_LENGTH(s) | 返回字符串 s 的字符数 | |
| CONCAT(s1,s2…sn) | 字符串 s1,s2 等多个字符串合并为一个字符串 | |
| CONCAT_WS(x, s1,s2…sn) | 同 CONCAT(s1,s2,…) 函数，但是每个字符串之间要加上 x | |
| FIELD(s,s1,s2…) | 返回第一个字符串 s 在字符串列表(s1,s2…)中的位置 | |
| LTRIM(s) | 去掉字符串s左边的空格 | |
| MID(s,n,len) | 从字符串 s 的 n 位置截取长度为 len 的子字符串，同 SUBSTRING(s,n,len) | |
| POSITION(s1 IN s) | 从字符串 s 中获取 s1 的开始位置 | |
| REPLACE(s,s1,s2) | 将字符串 s2 替代字符串 s 中的字符串 s1 | |
| REVERSE(s) | 将字符串s的顺序反过来 | |
| RIGHT(s,n) | 返回字符串 s 的后 n 个字符 | |
| RTRIM(s) | 去掉字符串s右边的空格 | |
| STRCMP(s1,s2) | 比较字符串 s1 和 s2 | |
| SUBSTR(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 | |
| SUBSTRING(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 | |
| TRIM(s) | 去掉字符串 s 开始和结尾处的空格 | |
| UCASE(s) | 将字符串转换为大写 | |
| UPPER(s) | 将字符串转换为大写 | |
| LCASE(s) | 将字符串 s 的所有字母变成小写字母 | |
| LOWER(s) | 将字符串 s 的所有字母变成小写字母 |
1.1.4. 日期函数
| 函数名 | 描述 |
|---|---|
| UNIX_TIMESTAMP() | 返回从1970-01-01 00:00:00到当前毫秒值 |
| UNIX_TIMESTAMP(DATE_STRING) | 将制定日期转为毫秒值时间戳 |
| FROM_UNIXTIME(BIGINT UNIXTIME[, STRING FORMAT]) | 将毫秒值时间戳转为指定格式日期 |
| CURDATE() | 返回当前日期 |
| CURRENT_DATE() | 返回当前日期 |
| CURRENT_TIME | 返回当前时间 |
| CURTIME() | 返回当前时间 |
| CURRENT_TIMESTAMP() | 返回当前日期和时间 |
| DATE() | 从日期或日期时间表达式中提取日期值 |
| DATEDIFF(d1,d2) | 计算日期 d1->d2 之间相隔的天数 |
| TIMEDIFF(time1, time2) | 计算时间差值 |
| DATE_FORMAT(d,f) | 按表达式 f的要求显示日期 d |
| STR_TO_DATE(string, format_mask) | 将字符串转变为日期 |
| DATE_SUB(date,INTERVAL expr type) | 函数从日期减去指定的时间间隔。 |
| DATE_ADD(d，INTERVAL expr type) | 计算起始日期 d 加上一个时间段后的日期 |
| EXTRACT(type FROM d) | 从日期 d 中获取指定的值，type 指定返回的值。 |
| LAST_DAY(d) | 返回给给定日期的那一月份的最后一天 |
| MAKEDATE(year, day-of-year) | 基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期 |
| YEAR(d) | 返回年份 |
| MONTH(d) | 返回日期d中的月份值，1 到 12 |
| DAY(d) | 返回日期值 d 的日期部分 |
| HOUR(t) | 返回 t 中的小时值 |
| MINUTE(t) | 返回 t 中的分钟值 |
| SECOND(t) | 返回 t 中的秒钟值 |
| QUARTER(d) | 返回日期d是第几季节，返回 1 到 4 |
| MONTHNAME(d) | 返回日期当中的月份名称，如 November |
| MONTH(d) | 返回日期d中的月份值，1 到 12 |
| DAYNAME(d) | 返回日期 d 是星期几，如 Monday,Tuesday |
| DAYOFMONTH(d) | 计算日期 d 是本月的第几天 |
| DAYOFWEEK(d) | 日期 d 今天是星期几，1 星期日，2 星期一，以此类推 |
| DAYOFYEAR(d) | 计算日期 d 是本年的第几天 |
| WEEK(d) | 计算日期 d 是本年的第几个星期，范围是 0 到 53 |
| WEEKDAY(d) | 日期 d 是星期几，0 表示星期一，1 表示星期二 |
| WEEKOFYEAR(d) | 计算日期 d 是本年的第几个星期，范围是 0 到 53 |
| YEARWEEK(date, mode) | 返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推 |
| NOW() | 返回当前日期和时间 |
1.1.5. 控制流函数
| 函数名 | 描述 |
|---|---|
| IF(expr,v1,v2) | 如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。 |
| IFNULL(v1,v2) | 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。 |
| ISNULL(expression) | 判断表达式是否为 NULL |
| NULLIF(expr1, expr2) | 比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1 |
| CASE… WHEN | 多条件判断语句 |
1.1.6. 窗口函数（MySQL 8.0以上）
1.1.6.1. 介绍
窗口函数对一组查询行执行类似聚合的操作。然而，聚合操作将查询行分组到单个结果行，而窗口函数将为每个查询行生成结果:
- 执行函数求值的行称为当前行。
- 与发生函数求值的当前行相关的查询行组成了当前行的窗口。
窗口函数分为聚合窗口函数和非聚合窗口函数，事实上，聚合函数加上OVER子句就是聚合窗口函数。相比单纯的聚合函数，窗口操作不会将查询行组折叠为单个输出行。相反，它们为每一行产生结果。
https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html
1.1.6.2. 用法
window_function ( expr ) OVER (
PARTITION BY ...
ORDER BY ...
frame_clause
)
解释：
PARTITION BY指定分区字段，分区字段的值相同的行被分到同一个“Window”ORDER BY指定分区内按某个字段的值排序。- frame_clause：Frame 是当前分区的子集，frame 子句指定如何定义子集。帧是根据当前行确定的，这使得帧能够根据当前行在其分区中的位置在分区中移动。（更多关于frame的内容，请参考https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html）
下面的查询演示了如何使用移动帧计算每组时间序列级别值中的运行总计，以及从当前行和前后行计算的滚动平均值
SELECT time, subject, val, SUM(val) OVER (PARTITION BY subject ORDER BY time ROWS UNBOUNDED PRECEDING) AS running_total, AVG(val) OVER (PARTITION BY subject ORDER BY time ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS running_average FROM observations;+----------+---------+------+---------------+-----------------+ | time | subject | val | running_total | running_average | +----------+---------+------+---------------+-----------------+ | 07:00:00 | st113 | 10 | 10 | 9.5000 | | 07:15:00 | st113 | 9 | 19 | 14.6667 | | 07:30:00 | st113 | 25 | 44 | 18.0000 | | 07:45:00 | st113 | 20 | 64 | 22.5000 | | 07:00:00 | xh458 | 0 | 0 | 5.0000 | | 07:15:00 | xh458 | 10 | 10 | 5.0000 | | 07:30:00 | xh458 | 5 | 15 | 15.0000 | | 07:45:00 | xh458 | 30 | 45 | 20.0000 | | 08:00:00 | xh458 | 25 | 70 | 27.5000 | +----------+---------+------+---------------+-----------------+
仅在select子句和 orderby 子句中允许使用窗口函数。 查询结果行由 FROM 子句在 WHERE、 GROUP BY 和 HAVING 处理之后确定，窗口执行在 ORDER BY、 LIMIT 和 SELECT DISTINCT 之前进行。
1.1.6.3. 函数列表
| 函数名 | 描述 |
|---|---|
| CUME_DIST() | Cumulative distribution value |
| DENSE_RANK() | Rank of current row within its partition, without gaps |
| FIRST_VALUE() | Value of argument from first row of window frame |
| LAG() | Value of argument from row lagging current row within partition |
| LAST_VALUE() | Value of argument from last row of window frame |
| LEAD() | Value of argument from row leading current row within partition |
| NTH_VALUE() | Value of argument from N-th row of window frame |
| NTILE() | Bucket number of current row within its partition. |
| PERCENT_RANK() | Percentage rank value |
| RANK() | Rank of current row within its partition, with gaps |
| ROW_NUMBER() | Number of current row within its partition |
1.1.6.3.1. 排名函数示例
数据文件：function.sql
SELECT
dname,
ename,
salary,
row_number() over ( PARTITION BY dname ORDER BY salary DESC ) AS row_num ,
rank() over ( PARTITION BY dname ORDER BY salary DESC ) AS rk ,
dense_rank() over ( PARTITION BY dname ORDER BY salary DESC ) AS dense_rk
FROM
employee;
结果：
+-----------+--------+--------+---------+----+----------+
| dname | ename | salary | row_num | rk | dense_rk |
+-----------+--------+--------+---------+----+----------+
| 研发部 | 张飞 | 7000 | 1 | 1 | 1 |
| 研发部 | 赵云 | 7000 | 2 | 1 | 1 |
| 研发部 | 张飞 | 7000 | 3 | 1 | 1 |
| 研发部 | 赵云 | 7000 | 4 | 1 | 1 |
| 研发部 | 关羽 | 5000 | 5 | 5 | 2 |
| 研发部 | 关羽 | 5000 | 6 | 5 | 2 |
| 研发部 | 马超 | 4000 | 7 | 7 | 3 |
| 研发部 | 黄忠 | 4000 | 8 | 7 | 3 |
| 研发部 | 马超 | 4000 | 9 | 7 | 3 |
| 研发部 | 黄忠 | 4000 | 10 | 7 | 3 |
| 研发部 | 刘备 | 3000 | 11 | 11 | 4 |
| 研发部 | 刘备 | 3000 | 12 | 11 | 4 |
| 销售部 | 徐晃 | 9000 | 1 | 1 | 1 |
| 销售部 | 张辽 | 6000 | 2 | 2 | 2 |
| 销售部 | 曹洪 | 6000 | 3 | 2 | 2 |
| 销售部 | 典韦 | 5000 | 4 | 4 | 3 |
| 销售部 | 许褚 | 3000 | 5 | 5 | 4 |
| 销售部 | 曹操 | 2000 | 6 | 6 | 5 |
+-----------+--------+--------+---------+----+----------+
1.1.6.3.2. 前后函数：lag()和lead()示例
lag()和lead()主要就是以当前行为基准，往前数or往后数n行，将那个行的某个字段拼接到结果行。 应用场景：计算环比等，比如计算某月和上个月的销售额差。
-- LAG()和lEAD()，前后函数，应用场景：计算环比
SELECT
dname,
ename,
salary,
hiredate,
lag(hiredate,1,'2022-01-11') over (PARTITION BY dname ORDER BY hiredate ) AS lag1,
lag(hiredate,2) over ( PARTITION BY dname ORDER BY hiredate ) AS lag2
-- lag第三个参数默认为NULL
FROM
employee;
SELECT
dname,
ename,
salary,
hiredate,
lead(hiredate,1,'2022-01-11') over (PARTITION BY dname ORDER BY hiredate ) AS lag1,
lead(hiredate,2) over ( PARTITION BY dname ORDER BY hiredate ) AS lag2
-- lag第三个参数默认为NULL
FROM
employee;
结果：
+-----------+--------+--------+------------+------------+------------+
| dname | ename | salary | hiredate | lag1 | lag2 |
+-----------+--------+--------+------------+------------+------------+
| 研发部 | 刘备 | 3000 | 2021-11-01 | 2022-01-11 | NULL |
| 研发部 | 关羽 | 5000 | 2021-11-02 | 2021-11-01 | NULL |
| 研发部 | 张飞 | 7000 | 2021-11-03 | 2021-11-02 | 2021-11-01 |
| 研发部 | 赵云 | 7000 | 2021-11-04 | 2021-11-03 | 2021-11-02 |
| 研发部 | 马超 | 4000 | 2021-11-05 | 2021-11-04 | 2021-11-03 |
| 研发部 | 黄忠 | 4000 | 2021-11-06 | 2021-11-05 | 2021-11-04 |
| 销售部 | 曹操 | 2000 | 2021-11-01 | 2022-01-11 | NULL |
| 销售部 | 许褚 | 3000 | 2021-11-02 | 2021-11-01 | NULL |
| 销售部 | 典韦 | 5000 | 2021-11-03 | 2021-11-02 | 2021-11-01 |
| 销售部 | 张辽 | 6000 | 2021-11-04 | 2021-11-03 | 2021-11-02 |
| 销售部 | 徐晃 | 9000 | 2021-11-05 | 2021-11-04 | 2021-11-03 |
| 销售部 | 曹洪 | 6000 | 2021-11-06 | 2021-11-05 | 2021-11-04 |
+-----------+--------+--------+------------+------------+------------+
+-----------+--------+--------+------------+------------+------------+
| dname | ename | salary | hiredate | lag1 | lag2 |
+-----------+--------+--------+------------+------------+------------+
| 研发部 | 刘备 | 3000 | 2021-11-01 | 2021-11-02 | 2021-11-03 |
| 研发部 | 关羽 | 5000 | 2021-11-02 | 2021-11-03 | 2021-11-04 |
| 研发部 | 张飞 | 7000 | 2021-11-03 | 2021-11-04 | 2021-11-05 |
| 研发部 | 赵云 | 7000 | 2021-11-04 | 2021-11-05 | 2021-11-06 |
| 研发部 | 马超 | 4000 | 2021-11-05 | 2021-11-06 | NULL |
| 研发部 | 黄忠 | 4000 | 2021-11-06 | 2022-01-11 | NULL |
| 销售部 | 曹操 | 2000 | 2021-11-01 | 2021-11-02 | 2021-11-03 |
| 销售部 | 许褚 | 3000 | 2021-11-02 | 2021-11-03 | 2021-11-04 |
| 销售部 | 典韦 | 5000 | 2021-11-03 | 2021-11-04 | 2021-11-05 |
| 销售部 | 张辽 | 6000 | 2021-11-04 | 2021-11-05 | 2021-11-06 |
| 销售部 | 徐晃 | 9000 | 2021-11-05 | 2021-11-06 | NULL |
| 销售部 | 曹洪 | 6000 | 2021-11-06 | 2022-01-11 | NULL |
+-----------+--------+--------+------------+------------+------------+
1.1.6.3.3. 头尾函数：FIRST_VALUE(expr)和LAST_VALUE(expr)
将分区排序后，first_value将本分区的第一行的expr值拼接到本结果行，last_value将本行的expr拼接到结果行（它的语义是到当前行为止的最后一个expr值，很显然就是它自己的expr值）
-- first_value和last_value
SELECT
dname,
ename,
salary,
hiredate,
FIRST_VALUE(salary) over (PARTITION BY dname ORDER BY hiredate ) AS first_val,
last_value(salary) over ( PARTITION BY dname ORDER BY hiredate ) AS last_val
FROM
employee;
结果：
+-----------+--------+--------+------------+-----------+----------+
| dname | ename | salary | hiredate | first_val | last_val |
+-----------+--------+--------+------------+-----------+----------+
| 研发部 | 刘备 | 3000 | 2021-11-01 | 3000 | 3000 |
| 研发部 | 关羽 | 5000 | 2021-11-02 | 3000 | 5000 |
| 研发部 | 张飞 | 7000 | 2021-11-03 | 3000 | 7000 |
| 研发部 | 赵云 | 7000 | 2021-11-04 | 3000 | 7000 |
| 研发部 | 马超 | 4000 | 2021-11-05 | 3000 | 4000 |
| 研发部 | 黄忠 | 4000 | 2021-11-06 | 3000 | 4000 |
| 销售部 | 曹操 | 2000 | 2021-11-01 | 2000 | 2000 |
| 销售部 | 许褚 | 3000 | 2021-11-02 | 2000 | 3000 |
| 销售部 | 典韦 | 5000 | 2021-11-03 | 2000 | 5000 |
| 销售部 | 张辽 | 6000 | 2021-11-04 | 2000 | 6000 |
| 销售部 | 徐晃 | 9000 | 2021-11-05 | 2000 | 9000 |
| 销售部 | 曹洪 | 6000 | 2021-11-06 | 2000 | 6000 |
+-----------+--------+--------+------------+-----------+----------+