MySql--sql语句与常用函数

发表于 2022-10-26 更新于 2024-04-03 分类于技术， mysql

阅读次数：本文字数： 9.1k 阅读时长 ≈ 33 分钟

摘要

sql语句与常用函数介绍
本文基于mysql-8.0.30，https://dev.mysql.com/doc/refman/8.0/en/

Mysql关键字执行顺序

Mysql关键字语法顺序	Mysql关键字执行顺序
select[distinct]	from <left_table>
from	on <join_condition>
join(left join)	<join_type> join <right_table>
on	where <where_condition>
where	group by <grout_by_list> [with rollup]
group by[with rollup]	having <having_condition>
having	select[distinct] <select_list>
union(union all)	union(union all)
order by	order by <order_by_list> [asc
limit	limit <limit_number>

FROM：组装来自不同数据源的记录
WHERE：根据指定的条件过滤上一步检索出的记录
GROUP BY：对上面过滤后的记录按指定条件分组
SUM/AVG/COUNT：使用聚合函数进行计算
HAVING：对所有分组根据指定条件进行过滤
SELECT：从上一步过滤后的各个分组记录中提取指定查询的字段列表（包括聚合字段、计算字段、表达式字段等）
ORDER BY：对上一步查询得到的结果集按照排序字段列表进行排序，并输出排序结果
DISTINCT和GROUP BY：都可以实现去重，DISTINCT是筛选去重，GROUP BY是分组去重
WHERE与HAVING的区别：WHERE是对记录进行筛选；而HAVING是对按GROUP BY进行分组后的组进行筛选。HAVING只有在使用GROUP BY 后才能使用。

distinct:去重

distinct关键字用于去重，distinct必须写在所有要查询字段的前面
distinct后面有几个字段，就代表修饰几个字段，而不是紧随distinct的字段（类似于联合唯一索引），例如以下两个语句效果相同

1
2
3

# name+age去重
select distinct name,age from employees
select distinct (name),age from employees

distinct + order by 是无法保证排序结果的，此时要使用 group by + order by
- distinct执行顺序先于order by，而distinct去重时不确定其选取的是哪条记录，而且distinct本身的排序结果也是乱序，所以其根本无法保证基于其它字段的排序顺序
- group by也可以达到去重的目的，并可以基于聚合函数实现排序的目的

# 错误方式
SELECT DISTINCT(t.search) FROM tbl_search_history t where t.user_id = :userId ORDER BY t.id desc

# 正确方式
SELECT t.search FROM tbl_search_history t WHERE t.user_id = :userId GROUP BY t.search order by MAX(t.id) desc

with rollup:分组后聚合

with rollup出现在group by后面，用于对分组结果中的聚合数据再次进行汇总
比如下面这个sql用于统计各个部门员工的年龄平均值、最大值和最小值

select depName,avg(age),max(age),min(age) from tbl_employees group by depName with rollup
+---------+----------+----------+----------+
| depName | avg(age) | max(age) | min(age) |
+---------+----------+----------+----------+
| 技术部  | 30.2500  | 38       | 22       |
| 运维部  | 30.5000  | 38       | 24       |
| 销售部  | 38.0000  | 46       | 24       |
| <null>  | 32.2500  | 46       | 22       |
+---------+----------+----------+----------+

select depName,groupName,avg(age),max(age),min(age) from tbl_employees group by depName,groupName with rollup
+---------+-----------+----------+----------+----------+
| depName | groupName | avg(age) | max(age) | min(age) |
+---------+-----------+----------+----------+----------+
| 技术部  | 前端      | 25.0000  | 28       | 22       |
| 技术部  | 后端      | 35.5000  | 38       | 34       |
| 技术部  | <null>    | 30.2500  | 38       | 22       |
| 运维部  | 1组       | 29.0000  | 34       | 24       |
| 运维部  | 2组       | 32.0000  | 38       | 26       |
| 运维部  | <null>    | 30.5000  | 38       | 24       |
| 销售部  | 1组       | 34.0000  | 44       | 24       |
| 销售部  | 2组       | 42.0000  | 46       | 38       |
| 销售部  | <null>    | 38.0000  | 46       | 24       |
| <null>  | <null>    | 32.2500  | 46       | 22       |
+---------+-----------+----------+----------+----------+

with rollup会为分组字段自动填充null值，我们可以使用GROUPING函数判断是否为with rollup自动填充的来设置想要的内容，比如

select CASE WHEN GROUPING(depName)=1 THEN '总计' ELSE depName END depName,avg(age),max(age),min(age)
from tbl_employees group by depName with rollup
+---------+----------+----------+----------+
| depName | avg(age) | max(age) | min(age) |
+---------+----------+----------+----------+
| 技术部  | 30.2500  | 38       | 22       |
| 运维部  | 30.5000  | 38       | 24       |
| 销售部  | 38.0000  | 46       | 24       |
| 总计    | 32.2500  | 46       | 22       |
+---------+----------+----------+----------+

select CASE WHEN GROUPING(depName)=1 and GROUPING(groupName)=1 THEN '总计' ELSE depName END depName,
CASE WHEN GROUPING(depName)=0 and GROUPING(groupName)=1 THEN '小计'
WHEN GROUPING(depName)=1 and GROUPING(groupName)=1 THEN '总计' ELSE groupName END groupName,
avg(age),max(age),min(age) from tbl_employees group by depName,groupName with rollup
+---------+-----------+----------+----------+----------+
| depName | groupName | avg(age) | max(age) | min(age) |
+---------+-----------+----------+----------+----------+
| 技术部  | 前端      | 25.0000  | 28       | 22       |
| 技术部  | 后端      | 35.5000  | 38       | 34       |
| 技术部  | 小计      | 30.2500  | 38       | 22       |
| 运维部  | 1组       | 29.0000  | 34       | 24       |
| 运维部  | 2组       | 32.0000  | 38       | 26       |
| 运维部  | 小计      | 30.5000  | 38       | 24       |
| 销售部  | 1组       | 34.0000  | 44       | 24       |
| 销售部  | 2组       | 42.0000  | 46       | 38       |
| 销售部  | 小计      | 38.0000  | 46       | 24       |
| 总计    | 总计      | 32.2500  | 46       | 22       |
+---------+-----------+----------+----------+----------+

group_concat(): 分组连接，将分组下的全部column用分隔符连接

语法：group_concat([DISTINCT] column [Order BY column ASC/DESC] [SEPARATOR '分隔符'])
默认逗号连接，不去重，不排序

select depName,group_concat(age) from tbl_employees group by depName
+---------+-------------------------+
| depName | group_concat(age)       |
+---------+-------------------------+
| 技术部  | 22,24,26,28,34,34,36,38 |
| 运维部  | 34,24,26,38             |
| 销售部  | 44,24,46,38             |
+---------+-------------------------+

select depName,group_concat(age) from tbl_employees group by depName with rollup
+---------+-------------------------------------------------+
| depName | group_concat(age)                               |
+---------+-------------------------------------------------+
| 技术部  | 22,24,26,28,34,34,36,38                         |
| 运维部  | 34,24,26,38                                     |
| 销售部  | 44,24,46,38                                     |
| <null>  | 22,24,26,28,34,34,36,38,34,24,26,38,44,24,46,38 |
+---------+-------------------------------------------------+

默认连接符为",“，如果要使用”:"连接，可以使用SEPARATOR ':'

select depName,group_concat(age SEPARATOR ':') from tbl_employees group by depName
+---------+---------------------------------+
| depName | group_concat(age SEPARATOR ':') |
+---------+---------------------------------+
| 技术部  | 22:24:26:28:34:34:36:38         |
| 运维部  | 34:24:26:38                     |
| 销售部  | 44:24:46:38                     |
+---------+---------------------------------+

select depName,group_concat(age SEPARATOR ':') from tbl_employees group by depName with rollup
+---------+-------------------------------------------------+
| depName | group_concat(age SEPARATOR ':')                 |
+---------+-------------------------------------------------+
| 技术部  | 22:24:26:28:34:34:36:38                         |
| 运维部  | 34:24:26:38                                     |
| 销售部  | 44:24:46:38                                     |
| <null>  | 22:24:26:28:34:34:36:38:34:24:26:38:44:24:46:38 |
+---------+-------------------------------------------------+

默认连接全部数据，如果要去掉重复的数据，可以使用DISTINCT关键字，其结果是排好序的

select depName,group_concat(DISTINCT age SEPARATOR ':') from tbl_employees group by depName
+---------+------------------------------------------+
| depName | group_concat(DISTINCT age SEPARATOR ':') |
+---------+------------------------------------------+
| 技术部  | 22:24:26:28:34:36:38                     |
| 运维部  | 24:26:34:38                              |
| 销售部  | 24:38:44:46                              |
+---------+------------------------------------------+

select depName,group_concat(DISTINCT age SEPARATOR ':') from tbl_employees group by depName with rollup
+---------+------------------------------------------+
| depName | group_concat(DISTINCT age SEPARATOR ':') |
+---------+------------------------------------------+
| 技术部  | 22:24:26:28:34:36:38                     |
| 运维部  | 24:26:34:38                              |
| 销售部  | 24:38:44:46                              |
| <null>  | 22:24:26:28:34:36:38:44:46               |
+---------+------------------------------------------+

排序Order BY columnName ASC/DESC

select depName,group_concat(DISTINCT age Order BY age DESC SEPARATOR ':') from tbl_employees group by depName with rollup
+---------+------------------------------------------------------------+
| depName | group_concat(DISTINCT age Order BY age DESC SEPARATOR ':') |
+---------+------------------------------------------------------------+
| 技术部  | 38:36:34:28:26:24:22                                       |
| 运维部  | 38:34:26:24                                                |
| 销售部  | 46:44:38:24                                                |
| <null>  | 46:44:38:36:34:28:26:24:22                                 |
+---------+------------------------------------------------------------+

over(): 开窗函数

语法：over（partition by columnname1 order by columnname2）
含义：按columname1指定的字段进行分组排序，按字段columnname2的值进行排序
over函数只能出现在select中，不能出现在过滤条件中
over()意思就是所有的数据都在窗口中，不能单独使用，要和分析函数如：sum(),avg(),row_number()等等一块使用，这么说貌似不好理解，直接看示例吧
将年龄的平均值显示在每行

select `depName`,`groupName`,age,avg(age)over() as avgAge from tbl_employees
+---------+-----------+-----+---------+
| depName | groupName | age | avgAge  |
+---------+-----------+-----+---------+
| 技术部  | 前端      | 22  | 32.2500 |
| 技术部  | 前端      | 24  | 32.2500 |
| 技术部  | 前端      | 26  | 32.2500 |
| 技术部  | 前端      | 28  | 32.2500 |
| 技术部  | 后端      | 34  | 32.2500 |
| 技术部  | 后端      | 34  | 32.2500 |
| 技术部  | 后端      | 36  | 32.2500 |
| 技术部  | 后端      | 38  | 32.2500 |
| 运维部  | 1组       | 34  | 32.2500 |
| 运维部  | 1组       | 24  | 32.2500 |
| 运维部  | 2组       | 26  | 32.2500 |
| 运维部  | 2组       | 38  | 32.2500 |
| 销售部  | 1组       | 44  | 32.2500 |
| 销售部  | 1组       | 24  | 32.2500 |
| 销售部  | 2组       | 46  | 32.2500 |
| 销售部  | 2组       | 38  | 32.2500 |
+---------+-----------+-----+---------+

将每个部门的年龄平均值显示在每行

select `depName`,`groupName`,age,avg(age)over(partition by depName) as avgAge from tbl_employees
+---------+-----------+-----+---------+
| depName | groupName | age | avgAge  |
+---------+-----------+-----+---------+
| 技术部  | 前端      | 22  | 30.2500 |
| 技术部  | 前端      | 24  | 30.2500 |
| 技术部  | 前端      | 26  | 30.2500 |
| 技术部  | 前端      | 28  | 30.2500 |
| 技术部  | 后端      | 34  | 30.2500 |
| 技术部  | 后端      | 34  | 30.2500 |
| 技术部  | 后端      | 36  | 30.2500 |
| 技术部  | 后端      | 38  | 30.2500 |
| 运维部  | 1组       | 34  | 30.5000 |
| 运维部  | 1组       | 24  | 30.5000 |
| 运维部  | 2组       | 26  | 30.5000 |
| 运维部  | 2组       | 38  | 30.5000 |
| 销售部  | 1组       | 44  | 38.0000 |
| 销售部  | 1组       | 24  | 38.0000 |
| 销售部  | 2组       | 46  | 38.0000 |
| 销售部  | 2组       | 38  | 38.0000 |
+---------+-----------+-----+---------+

将每个部门下每个分组的年龄平均值显示在每行

select `depName`,`groupName`,age,avg(age)over(partition by depName,groupName) as avgAge from tbl_employees
+---------+-----------+-----+---------+
| depName | groupName | age | avgAge  |
+---------+-----------+-----+---------+
| 技术部  | 前端      | 22  | 25.0000 |
| 技术部  | 前端      | 24  | 25.0000 |
| 技术部  | 前端      | 26  | 25.0000 |
| 技术部  | 前端      | 28  | 25.0000 |
| 技术部  | 后端      | 34  | 35.5000 |
| 技术部  | 后端      | 34  | 35.5000 |
| 技术部  | 后端      | 36  | 35.5000 |
| 技术部  | 后端      | 38  | 35.5000 |
| 运维部  | 1组       | 34  | 29.0000 |
| 运维部  | 1组       | 24  | 29.0000 |
| 运维部  | 2组       | 26  | 32.0000 |
| 运维部  | 2组       | 38  | 32.0000 |
| 销售部  | 1组       | 44  | 34.0000 |
| 销售部  | 1组       | 24  | 34.0000 |
| 销售部  | 2组       | 46  | 42.0000 |
| 销售部  | 2组       | 38  | 42.0000 |
+---------+-----------+-----+---------+

每个部门每个分组下按年龄倒序并显示每组的平均年龄

select `depName`,`groupName`,age,avg(age)over(partition by depName,groupName order by age desc) as avgAge from tbl_employees
+---------+-----------+-----+---------+
| depName | groupName | age | avgAge  |
+---------+-----------+-----+---------+
| 技术部  | 前端      | 28  | 28.0000 |
| 技术部  | 前端      | 26  | 27.0000 |
| 技术部  | 前端      | 24  | 26.0000 |
| 技术部  | 前端      | 22  | 25.0000 |
| 技术部  | 后端      | 38  | 38.0000 |
| 技术部  | 后端      | 36  | 37.0000 |
| 技术部  | 后端      | 34  | 35.5000 |
| 技术部  | 后端      | 34  | 35.5000 |
| 运维部  | 1组       | 34  | 34.0000 |
| 运维部  | 1组       | 24  | 29.0000 |
| 运维部  | 2组       | 38  | 38.0000 |
| 运维部  | 2组       | 26  | 32.0000 |
| 销售部  | 1组       | 44  | 44.0000 |
| 销售部  | 1组       | 24  | 34.0000 |
| 销售部  | 2组       | 46  | 46.0000 |
| 销售部  | 2组       | 38  | 42.0000 |
+---------+-----------+-----+---------+
注意，这里是先分组，然后逐行求平均值的，比如前4条记录是一组，第一行数据和自己比，平均值就是28，第二行数据和第一行数据比，平均值是(26+28)/2，第三行数据和前两行比，平均值就是(28+26+24)/3，以此类推，第四行就是(28+26+24+22)/4，第五行是另一个分组了，依旧是按行计算。这里需要说一下第7行和第8行，因为这两行是同一个分组，且年龄相同，都是34，其平均值计算方法是相同年龄行相加后一起求平均值，所以我们看到这两行的平均值是一样的，所以over按哪个字段排序，如果其值相同，调用分析函数时要一起计算。每个分组的最后一条记录的显示的平均值才是该组的真实平均值

不分组直接排序，这里按年龄倒序

select `depName`,`groupName`,age,avg(age)over(order by age desc) as avgAge from tbl_employees
+---------+-----------+-----+---------+
| depName | groupName | age | avgAge  |
+---------+-----------+-----+---------+
| 销售部  | 2组       | 46  | 46.0000 |
| 销售部  | 1组       | 44  | 45.0000 |
| 技术部  | 后端      | 38  | 40.8000 |
| 运维部  | 2组       | 38  | 40.8000 |
| 销售部  | 2组       | 38  | 40.8000 |
| 技术部  | 后端      | 36  | 40.0000 |
| 技术部  | 后端      | 34  | 38.0000 |
| 技术部  | 后端      | 34  | 38.0000 |
| 运维部  | 1组       | 34  | 38.0000 |
| 技术部  | 前端      | 28  | 37.0000 |
| 技术部  | 前端      | 26  | 35.1667 |
| 运维部  | 2组       | 26  | 35.1667 |
| 技术部  | 前端      | 24  | 32.9333 |
| 运维部  | 1组       | 24  | 32.9333 |
| 销售部  | 1组       | 24  | 32.9333 |
| 技术部  | 前端      | 22  | 32.2500 |
+---------+-----------+-----+---------+
这里的计算方法与上面分组的情况类似，按行计算平均值，遇到排序字段值相同时，要一起计算。最后一条记录显示的平均值就是所有记录的平均值。

查询每个员工年龄与全体平均年龄的差值

select depName,groupName,age,avg(age)over() as avgAge,age-avg(age)over() from tbl_employees
+---------+-----------+-----+---------+--------------------+
| depName | groupName | age | avgAge  | age-avg(age)over() |
+---------+-----------+-----+---------+--------------------+
| 技术部  | 前端      | 22  | 32.2500 | -10.2500           |
| 技术部  | 前端      | 24  | 32.2500 | -8.2500            |
| 技术部  | 前端      | 26  | 32.2500 | -6.2500            |
| 技术部  | 前端      | 28  | 32.2500 | -4.2500            |
| 技术部  | 后端      | 34  | 32.2500 | 1.7500             |
| 技术部  | 后端      | 34  | 32.2500 | 1.7500             |
| 技术部  | 后端      | 36  | 32.2500 | 3.7500             |
| 技术部  | 后端      | 38  | 32.2500 | 5.7500             |
| 运维部  | 1组       | 34  | 32.2500 | 1.7500             |
| 运维部  | 1组       | 24  | 32.2500 | -8.2500            |
| 运维部  | 2组       | 26  | 32.2500 | -6.2500            |
| 运维部  | 2组       | 38  | 32.2500 | 5.7500             |
| 销售部  | 1组       | 44  | 32.2500 | 11.7500            |
| 销售部  | 1组       | 24  | 32.2500 | -8.2500            |
| 销售部  | 2组       | 46  | 32.2500 | 13.7500            |
| 销售部  | 2组       | 38  | 32.2500 | 5.7500             |
+---------+-----------+-----+---------+--------------------+

查询行号

mysql没有提供可供计算行号的方法，不过我们可以通过两种方式来实现
第一种方法，自定义行号变量@rownum

 SELECT @rownum:=@rownum+1 AS rownum, t.depName,t.groupName,t.age  FROM (SELECT @rownum:=0) r, tbl_employees t
+--------+---------+-----------+-----+
| rownum | depName | groupName | age |
+--------+---------+-----------+-----+
| 1      | 技术部  | 前端      | 22  |
| 2      | 技术部  | 前端      | 24  |
| 3      | 技术部  | 前端      | 26  |
| 4      | 技术部  | 前端      | 28  |
| 5      | 技术部  | 后端      | 34  |
| 6      | 技术部  | 后端      | 34  |
| 7      | 技术部  | 后端      | 36  |
| 8      | 技术部  | 后端      | 38  |
| 9      | 运维部  | 1组       | 34  |
| 10     | 运维部  | 1组       | 24  |
| 11     | 运维部  | 2组       | 26  |
| 12     | 运维部  | 2组       | 38  |
| 13     | 销售部  | 1组       | 44  |
| 14     | 销售部  | 1组       | 24  |
| 15     | 销售部  | 2组       | 46  |
| 16     | 销售部  | 2组       | 38  |
+--------+---------+-----------+-----+
# 年龄倒序显示
SELECT @rownum:=@rownum+1 AS rownum, t.depName,t.groupName,t.age  FROM (SELECT @rownum:=0) r, tbl_employees t order by age desc
+--------+---------+-----------+-----+
| rownum | depName | groupName | age |
+--------+---------+-----------+-----+
| 1      | 销售部  | 2组       | 46  |
| 2      | 销售部  | 1组       | 44  |
| 3      | 技术部  | 后端      | 38  |
| 4      | 运维部  | 2组       | 38  |
| 5      | 销售部  | 2组       | 38  |
| 6      | 技术部  | 后端      | 36  |
| 7      | 技术部  | 后端      | 34  |
| 8      | 技术部  | 后端      | 34  |
| 9      | 运维部  | 1组       | 34  |
| 10     | 技术部  | 前端      | 28  |
| 11     | 技术部  | 前端      | 26  |
| 12     | 运维部  | 2组       | 26  |
| 13     | 技术部  | 前端      | 24  |
| 14     | 运维部  | 1组       | 24  |
| 15     | 销售部  | 1组       | 24  |
| 16     | 技术部  | 前端      | 22  |
+--------+---------+-----------+-----+

第二种方法，使用row_number()over()函数

select row_number()over() as rownum,depName,groupName,age from tbl_employees
+--------+---------+-----------+-----+
| rownum | depName | groupName | age |
+--------+---------+-----------+-----+
| 1      | 技术部  | 前端      | 22  |
| 2      | 技术部  | 前端      | 24  |
| 3      | 技术部  | 前端      | 26  |
| 4      | 技术部  | 前端      | 28  |
| 5      | 技术部  | 后端      | 34  |
| 6      | 技术部  | 后端      | 34  |
| 7      | 技术部  | 后端      | 36  |
| 8      | 技术部  | 后端      | 38  |
| 9      | 运维部  | 1组       | 34  |
| 10     | 运维部  | 1组       | 24  |
| 11     | 运维部  | 2组       | 26  |
| 12     | 运维部  | 2组       | 38  |
| 13     | 销售部  | 1组       | 44  |
| 14     | 销售部  | 1组       | 24  |
| 15     | 销售部  | 2组       | 46  |
| 16     | 销售部  | 2组       | 38  |
+--------+---------+-----------+-----+
此时如果要按年龄排序，则排序规则可以写在最后，也可以写在over函数内，效果是一样的
select row_number()over() as rownum,depName,groupName,age from tbl_employees order by age desc
+--------+---------+-----------+-----+
| rownum | depName | groupName | age |
+--------+---------+-----------+-----+
| 1      | 销售部  | 2组       | 46  |
| 2      | 销售部  | 1组       | 44  |
| 3      | 技术部  | 后端      | 38  |
| 4      | 运维部  | 2组       | 38  |
| 5      | 销售部  | 2组       | 38  |
| 6      | 技术部  | 后端      | 36  |
| 7      | 技术部  | 后端      | 34  |
| 8      | 技术部  | 后端      | 34  |
| 9      | 运维部  | 1组       | 34  |
| 10     | 技术部  | 前端      | 28  |
| 11     | 技术部  | 前端      | 26  |
| 12     | 运维部  | 2组       | 26  |
| 13     | 技术部  | 前端      | 24  |
| 14     | 运维部  | 1组       | 24  |
| 15     | 销售部  | 1组       | 24  |
| 16     | 技术部  | 前端      | 22  |
+--------+---------+-----------+-----+
select row_number()over(order by age desc) as rownum,depName,groupName,age from tbl_employees
+--------+---------+-----------+-----+
| rownum | depName | groupName | age |
+--------+---------+-----------+-----+
| 1      | 销售部  | 2组       | 46  |
| 2      | 销售部  | 1组       | 44  |
| 3      | 技术部  | 后端      | 38  |
| 4      | 运维部  | 2组       | 38  |
| 5      | 销售部  | 2组       | 38  |
| 6      | 技术部  | 后端      | 36  |
| 7      | 技术部  | 后端      | 34  |
| 8      | 技术部  | 后端      | 34  |
| 9      | 运维部  | 1组       | 34  |
| 10     | 技术部  | 前端      | 28  |
| 11     | 技术部  | 前端      | 26  |
| 12     | 运维部  | 2组       | 26  |
| 13     | 技术部  | 前端      | 24  |
| 14     | 运维部  | 1组       | 24  |
| 15     | 销售部  | 1组       | 24  |
| 16     | 技术部  | 前端      | 22  |
+--------+---------+-----------+-----+

Mysql常用函数

数学函数

函数名称	功能
ABS(x)	返回x的绝对值
BIN(x)	返回x的二进制（OCT(x)返回八进制，HEX(x)返回十六进制）
CEILING(x)	返回>=x的最小整数值，可以简写为 ceil(x)
FLOOR(x)	返回<=x的最大整数值

select ABS(10),ABS(-10),BIN(10),OCT(10),HEX(10),CEILING(10.2),EXP(1),FLOOR(10.2)
+---------+----------+---------+---------+---------+---------------+-------------------+-------------+
| ABS(10) | ABS(-10) | BIN(10) | OCT(10) | HEX(10) | CEILING(10.2) | EXP(1)            | FLOOR(10.2) |
+---------+----------+---------+---------+---------+---------------+-------------------+-------------+
| 10      | 10       | 1010    | 12      | A       | 11            | 2.718281828459045 | 10          |
+---------+----------+---------+---------+---------+---------------+-------------------+-------------+

函数名称	功能
GREATEST(x1,x2,…,xn)	返回集合中最大的值
LEAST(x1,x2,…,xn)	返回集合中最小的值

# 按照数字顺序，字符顺序，首字母顺序比较
select GREATEST(1,2,3,4),LEAST(1,2,3,4),GREATEST('A','B','C','D'),LEAST('A','B','C','D'),GREATEST('MySQL','Java','Redis'),LEAST('MySQL','Java','Redis')
+-------------------+----------------+---------------------------+------------------------+----------------------------------+-------------------------------+
| GREATEST(1,2,3,4) | LEAST(1,2,3,4) | GREATEST('A','B','C','D') | LEAST('A','B','C','D') | GREATEST('MySQL','Java','Redis') | LEAST('MySQL','Java','Redis') |
+-------------------+----------------+---------------------------+------------------------+----------------------------------+-------------------------------+
| 4                 | 1              | D                         | A                      | Redis                            | Java                          |
+-------------------+----------------+---------------------------+------------------------+----------------------------------+-------------------------------+

# 这里要注意，只要集合中含有NULL，返回值都是null
select GREATEST(10, 20, NULL),LEAST(10, 20, NULL)
+------------------------+---------------------+
| GREATEST(10, 20, NULL) | LEAST(10, 20, NULL) |
+------------------------+---------------------+
| <null>                 | <null>              |
+------------------------+---------------------+

函数名称	功能
EXP(x)	返回值e（自然对数的底）的x次方，自然对数e其值约等于2.71828
LN(x)	返回x的自然对数
LOG(x,y)	返回x的以y为底的对数
MOD(x,y)	返回x/y的模（余数）,当被除数为正数，结果就是正数，当被除数为负数，结果就是负数。
PI()	返回pi的值（圆周率）

select EXP(1),LN(2.718281828459045),LOG(2,16),MOD(10,3),MOD(-10,3),PI()
+-------------------+-----------------------+-----------+-----------+------------+----------+
| EXP(1)            | LN(2.718281828459045) | LOG(2,16) | MOD(10,3) | MOD(-10,3) | PI()     |
+-------------------+-----------------------+-----------+-----------+------------+----------+
| 2.718281828459045 | 1.0                   | 4.0       | 1         | -1         | 3.141593 |
+-------------------+-----------------------+-----------+-----------+------------+----------+

函数名称	功能
RAND()	返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。

# 相同的种子，返回值相同
select RAND(),RAND(2),RAND(2)
+---------------------+--------------------+--------------------+
| RAND()              | RAND(2)            | RAND(2)            |
+---------------------+--------------------+--------------------+
| 0.10167000452212059 | 0.6555866465490187 | 0.6555866465490187 |
+---------------------+--------------------+--------------------+

# 获得介于[i，j)之间的随机值，FLOOR(i + RAND() *(j − i))，比如[1,10)
SELECT FLOOR(1 + RAND()*(10 - 1)) AS Random_Number;
+---------------+
| Random_Number |
+---------------+
| 6.0           |
+---------------+

# 获得介于[i，j]之间的随机值，FLOOR(i + RAND() *(j − i + 1))，比如[1,10]
SELECT FLOOR(1 + RAND()*(10 - 1 + 1)) AS Random_Number;
+---------------+
| Random_Number |
+---------------+
| 10.0          |
+---------------+

函数名称	功能
ROUND(x,y)	返回参数x的四舍五入的有y位小数的值
SIGN(x)	返回代表数字x的符号的值
SQRT(x)	返回一个数的平方根
POW(x,y)	指数函数，x为底，y为指数
TRUNCATE(x,y)	返回数字x截短为y位小数的结果，y为负数时整数部分替换为0

select ROUND(3.147,2),SIGN(5),SIGN(-5),SQRT(16),POW(4,2),TRUNCATE(3.147,2),TRUNCATE(3.147,0),TRUNCATE(123.147,-2)
+----------------+---------+----------+----------+----------+-------------------+-------------------+----------------------+
| ROUND(3.147,2) | SIGN(5) | SIGN(-5) | SQRT(16) | POW(4,2) | TRUNCATE(3.147,2) | TRUNCATE(3.147,0) | TRUNCATE(123.147,-2) |
+----------------+---------+----------+----------+----------+-------------------+-------------------+----------------------+
| 3.15           | 1       | -1       | 4.0      | 16.0     | 3.14              | 3                 | 100                  |
+----------------+---------+----------+----------+----------+-------------------+-------------------+----------------------+

函数名称	功能
FORMAT(N,D,locale)	将数字N格式化为格式，如"#,###,###.##"，舍入到D位小数。它返回一个值作为字符串。

mysql支持的locale请查看https://dev.mysql.com/doc/refman/8.0/en/locale-support.html

# 不指定locale时默认en_US，de_DE语言环境使用点(.)来分隔千位和逗号(，)来分隔小数点
select format(1450028.123,2),format(1450028.123,2,'en_US'),format(1450028.123,2,'zh_CN'),format(1450028.123,2,'de_DE');
+-----------------------+-------------------------------+-------------------------------+-------------------------------+
| format(1450028.123,2) | format(1450028.123,2,'en_US') | format(1450028.123,2,'zh_CN') | format(1450028.123,2,'de_DE') |
+-----------------------+-------------------------------+-------------------------------+-------------------------------+
| 1,450,028.12          | 1,450,028.12                  | 1,450,028.12                  | 1.450.028,12                  |
+-----------------------+-------------------------------+-------------------------------+-------------------------------+

聚合函数(常用于GROUP BY从句的SELECT查询中)

函数名称	功能
AVG(col)	返回指定列的平均值
COUNT(col)	返回指定列中非NULL值的个数，注意count(*)不会跳过NULL值行
MIN(col)	返回指定列的最小值
MAX(col)	返回指定列的最大值
SUM(col)	返回指定列的所有值之和
GROUP_CONCAT(col)	返回由属于一组的列值连接组合而成的结果

select depName,count(*),avg(age),max(age),min(age),sum(age),group_concat(age) from tbl_employees group by depName
+---------+----------+----------+----------+----------+----------+-------------------------+
| depName | count(*) | avg(age) | max(age) | min(age) | sum(age) | group_concat(age)       |
+---------+----------+----------+----------+----------+----------+-------------------------+
| 技术部  | 8        | 30.2500  | 38       | 22       | 242      | 22,24,26,28,34,34,36,38 |
| 运维部  | 4        | 30.5000  | 38       | 24       | 122      | 34,24,26,38             |
| 销售部  | 4        | 38.0000  | 46       | 24       | 152      | 44,24,46,38             |
+---------+----------+----------+----------+----------+----------+-------------------------+

字符串函数

函数名称	功能
ASCII(char)	返回字符的ASCII码值
LENGTH(s)	返回字符串str中的字符数
BIT_LENGTH(str)	返回字符串的比特长度
CONCAT(s1,s2…,sn)	将s1,s2…,sn连接成字符串
CONCAT_WS(sep,s1,s2…,sn)	将s1,s2…,sn连接成字符串，并用sep字符间隔
INSERT(str,x,y,instr)	将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
REPLACE(str,substr,otherstr)	在str中查找substr，并使用otherstr替换
FIND_IN_SET(str,list)	分析逗号分隔的list列表，如果发现str，返回str在list中的位置，位置从1开始计算
REPEAT(str,x)	返回字符串str重复x次的结果
REVERSE(str)	返回颠倒字符串str的结果
STRCMP(s1,s2)	比较字符串s1和s2,如果两个字符串相同，则返回0，如果根据定义的顺序第一个参数小于第二个参数，则返回-1，而当第二个参数小于第一个参数时，返回1。
LEFT(str,x)	返回字符串str中最左边的x个字符
RIGHT(str,x)	返回字符串str中最右边的x个字符
SUBSTRING(str,x,y)或者SUBSTR(str,x,y)	返回字符串str从第x个字符开始，截取y个字符长度，y不填写则表示截取到字符串末尾
TRIM(str)	去除字符串首部和尾部的所有空格
RTRIM(str)	返回字符串str尾部的空格
LTRIM(str)	从字符串str中切掉开头的空格
UCASE(str)或UPPER(str)	返回将字符串str中所有字符转变为大写后的结果
LCASE(str)或LOWER(str)	返回将字符串str中所有字符改变为小写后的结果
LPAD(str,x,y)	将str长度为设置为x，不足部分用y在前面补齐
RPAD(str,x,y)	将str长度为设置为x，不足部分用y在后面补齐
SPACE(x)	返回指定数量的空格，x必须大于0

# 字符串替换
select INSERT('abcdefgh',3,5,'ufo'),REVERSE('abcdefgh'),REPEAT('MySQL', 3),REPLACE('abcdefgh','de','ufo')
+------------------------------+---------------------+--------------------+--------------------------------+
| INSERT('abcdefgh',3,5,'ufo') | REVERSE('abcdefgh') | REPEAT('MySQL', 3) | REPLACE('abcdefgh','de','ufo') |
+------------------------------+---------------------+--------------------+--------------------------------+
| abufoh                       | hgfedcba            | MySQLMySQLMySQL    | abcufofgh                      |
+------------------------------+---------------------+--------------------+--------------------------------+

# 计算字符串长度，中文占3个字符，每个字符8个字节
select ASCII('A'),LENGTH('中华人民共和国'),BIT_LENGTH('中华人民共和国')
+------------+--------------------------+------------------------------+
| ASCII('A') | LENGTH('中华人民共和国') | BIT_LENGTH('中华人民共和国') |
+------------+--------------------------+------------------------------+
| 65         | 21                       | 168                          |
+------------+--------------------------+------------------------------+

# 字符串连接
select CONCAT('中华','人民','共和国'),CONCAT_WS('_','中华','人民','共和国')
+--------------------------------+---------------------------------------+
| CONCAT('中华','人民','共和国') | CONCAT_WS('_','中华','人民','共和国') |
+--------------------------------+---------------------------------------+
| 中华人民共和国                 | 中华_人民_共和国                      |
+--------------------------------+---------------------------------------+

# 字符串截取
select LEFT('中华人民共和国',5),RIGHT('中华人民共和国',5),SUBSTRING('中华人民共和国',3),SUBSTRING('中华人民共和国',3,2)
+--------------------------+---------------------------+-------------------------------+---------------------------------+
| LEFT('中华人民共和国',5) | RIGHT('中华人民共和国',5) | SUBSTRING('中华人民共和国',3) | SUBSTRING('中华人民共和国',3,2) |
+--------------------------+---------------------------+-------------------------------+---------------------------------+
| 中华人民共               | 人民共和国                | 人民共和国                    | 人民                            |
+--------------------------+---------------------------+-------------------------------+---------------------------------+

# 字符串补齐
select LPAD("hello",7,'a'),LPAD("hello",7,0),RPAD("hello",7,'a'),RPAD("hello",7,0)
+---------------------+-------------------+---------------------+-------------------+
| LPAD("hello",7,'a') | LPAD("hello",7,0) | RPAD("hello",7,'a') | RPAD("hello",7,0) |
+---------------------+-------------------+---------------------+-------------------+
| aahello             | 00hello           | helloaa             | hello00           |
+---------------------+-------------------+---------------------+-------------------+

# 列表中查找指定的字符串
SELECT FIND_IN_SET('b', 'a,b,c,d'),FIND_IN_SET('bb', 'ab,bb,cb,db')
+-----------------------------+----------------------------------+
| FIND_IN_SET('b', 'a,b,c,d') | FIND_IN_SET('bb', 'ab,bb,cb,db') |
+-----------------------------+----------------------------------+
| 2                           | 2                                |
+-----------------------------+----------------------------------+

# 字符串比较，按字母顺序逐个比较
Select STRCMP('Geeks', 'Geeks'),STRCMP('Geeks', 'Geek'),STRCMP('Geek', 'Geeks'),STRCMP('Geek', NULL)
+--------------------------+-------------------------+-------------------------+----------------------+
| STRCMP('Geeks', 'Geeks') | STRCMP('Geeks', 'Geek') | STRCMP('Geek', 'Geeks') | STRCMP('Geek', NULL) |
+--------------------------+-------------------------+-------------------------+----------------------+
| 0                        | 1                       | -1                      | <null>               |
+--------------------------+-------------------------+-------------------------+----------------------+
select STRCMP('ae','abcd')
+---------------------+
| STRCMP('ae','abcd') |
+---------------------+
| 1                   |
+---------------------+

# 大小写转换
select UCASE('abc'),UPPER('abc'),LCASE('ABC'),LOWER('ABC')
+--------------+--------------+--------------+--------------+
| UCASE('abc') | UPPER('abc') | LCASE('ABC') | LOWER('ABC') |
+--------------+--------------+--------------+--------------+
| ABC          | ABC          | abc          | abc          |
+--------------+--------------+--------------+--------------+

# 去除左右空格
select LENGTH(TRIM('  abc  ')),LENGTH(LTRIM('  abc  ')),LENGTH(RTRIM('  abc  '))
+-------------------------+--------------------------+--------------------------+
| LENGTH(TRIM('  abc  ')) | LENGTH(LTRIM('  abc  ')) | LENGTH(RTRIM('  abc  ')) |
+-------------------------+--------------------------+--------------------------+
| 3                       | 5                        | 5                        |
+-------------------------+--------------------------+--------------------------+

# 返回指定数量的空格
select SPACE(100)
+------------------------------------------------------------------------------------------------------+
| SPACE(100)                                                                                           |
+------------------------------------------------------------------------------------------------------+
|                                                                                                      |
+------------------------------------------------------------------------------------------------------+

日期和时间函数

时间格式符含义

序号	格式符	含义
1	%Y	四位的年份
2	%y	2位的年份
3	%m	月份（01,02，…11,12）
4	%c	月份（1,2,3…11,12）
5	%d	日（01,02，…）
6	%H	小时（24小时）
7	%h	小时（12小时）
8	%i	分钟（00,01，…59）
9	%s	秒（00,01，…59）

获取当前日期和时间

函数名称	功能
CURDATE()或CURRENT_DATE()或CURRENT_DATE	返回当前的日期
CURTIME()或CURRENT_TIME()或CURTIME	返回当前的时间
NOW()	返回当前的日期和时间,%Y-%m-%d %H:%i:%s
current_timestamp()或current_timestamp或localtime或localtimestamp	返回当前的日期和时间,%Y-%m-%d %H:%i:%s
sysdate()	返回当前的日期和时间,%Y-%m-%d %H:%i:%s

select CURDATE(),CURRENT_DATE(),CURTIME(),CURRENT_TIME(),NOW(),current_timestamp()
+------------+----------------+-----------+----------------+---------------------+---------------------+
| CURDATE()  | CURRENT_DATE() | CURTIME() | CURRENT_TIME() | NOW()               | current_timestamp() |
+------------+----------------+-----------+----------------+---------------------+---------------------+
| 2022-10-26 | 2022-10-26     | 6:44:49   | 6:44:49        | 2022-10-26 06:44:49 | 2022-10-26 06:44:49 |
+------------+----------------+-----------+----------------+---------------------+---------------------+

# 也可以使用名称获取时间
select current_timestamp,CURRENT_DATE,CURRENT_TIME,localtime,localtimestamp
+---------------------+--------------+--------------+---------------------+---------------------+
| current_timestamp   | CURRENT_DATE | CURRENT_TIME | localtime           | localtimestamp      |
+---------------------+--------------+--------------+---------------------+---------------------+
| 2022-10-26 07:11:54 | 2022-10-26   | 7:11:54      | 2022-10-26 07:11:54 | 2022-10-26 07:11:54 |
+---------------------+--------------+--------------+---------------------+---------------------+

# 获得当前 UTC 日期时间函数，now()调用的是系统本地时间，因为我国位于东八时区，所以本地时间 = UTC 时间 + 8 小时
select utc_timestamp(), utc_date(), utc_time(),now();
+---------------------+------------+------------+---------------------+
| utc_timestamp()     | utc_date() | utc_time() | now()               |
+---------------------+------------+------------+---------------------+
| 2022-10-26 07:16:43 | 2022-10-26 | 7:16:43    | 2022-10-26 15:16:43 |
+---------------------+------------+------------+---------------------+

日期增加或减少

函数名称	功能
DATE_ADD(date,INTERVAL int keyword)	返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);
DATE_SUB(date,INTERVAL int keyword)	返回日期date减去间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);

keyword: YEAR,MONTH,DAY,HOUR,MINUTE,SECOND,WEEK,QUARTER,MICROSECOND

SELECT DATE_ADD(CURRENT_DATE,INTERVAL 6 YEAR),DATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH),DATE_ADD(CURRENT_DATE,INTERVAL 6 DAY),DATE_SUB(CURRENT_DATE,INTERVAL 6 YEAR),DATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH),DATE_SUB(CURRENT_DATE,INTERVAL 6 DAY)
+----------------------------------------+-----------------------------------------+---------------------------------------+----------------------------------------+-----------------------------------------+---------------------------------------+
| DATE_ADD(CURRENT_DATE,INTERVAL 6 YEAR) | DATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH) | DATE_ADD(CURRENT_DATE,INTERVAL 6 DAY) | DATE_SUB(CURRENT_DATE,INTERVAL 6 YEAR) | DATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH) | DATE_SUB(CURRENT_DATE,INTERVAL 6 DAY) |
+----------------------------------------+-----------------------------------------+---------------------------------------+----------------------------------------+-----------------------------------------+---------------------------------------+
| 2028-10-26                             | 2023-04-26                              | 2022-11-01                            | 2016-10-26                             | 2022-04-26                              | 2022-10-20                            |
+----------------------------------------+-----------------------------------------+---------------------------------------+----------------------------------------+-----------------------------------------+---------------------------------------+

两个日期比较

函数名称	功能
datediff(date1,date2)	两个日期相减
timediff(time1,time2)	两个时间相减
timestampdiff(unit,datetime1,datetime2)	两个时间相差的unit声明的间隔

unit: year,month,day,hour,minute,second,week,QUARTER

# 比较日期，时间的差值
select datediff('2022-08-08', '2022-08-01'),timediff('2022-08-08 08:08:0', '2022-08-08 00:00:00'),timediff('2022-08-08 08:08:0', '2022-08-07 00:00:00'),timediff('08:08:0', '00:00:00')
+--------------------------------------+-------------------------------------------------------+-------------------------------------------------------+---------------------------------+
| datediff('2022-08-08', '2022-08-01') | timediff('2022-08-08 08:08:0', '2022-08-08 00:00:00') | timediff('2022-08-08 08:08:0', '2022-08-07 00:00:00') | timediff('08:08:0', '00:00:00') |
+--------------------------------------+-------------------------------------------------------+-------------------------------------------------------+---------------------------------+
| 7                                    | 8:08:00                                               | 1 day, 8:08:00                                        | 8:08:00                         |
+--------------------------------------+-------------------------------------------------------+-------------------------------------------------------+---------------------------------+

# 比较日期的相差的年，月，日数
select timestampdiff(year,'2022-05-01','2021-05-01'),timestampdiff(month,'2022-05-01','2021-05-01'),timestampdiff(day,'2022-05-01','2021-05-01')
+-----------------------------------------------+------------------------------------------------+----------------------------------------------+
| timestampdiff(year,'2022-05-01','2021-05-01') | timestampdiff(month,'2022-05-01','2021-05-01') | timestampdiff(day,'2022-05-01','2021-05-01') |
+-----------------------------------------------+------------------------------------------------+----------------------------------------------+
| -1                                            | -12                                            | -365                                         |
+-----------------------------------------------+------------------------------------------------+----------------------------------------------+

# 比较日期的相差的小时，分钟，秒数
select timestampdiff(hour,'2022-08-08 08:08:00', '2022-08-08 00:00:00'),timestampdiff(minute,'2022-08-08 08:08:0', '2022-08-08 00:00:00'),timestampdiff(second,'2022-08-08 08:08:00', '2022-08-08 00:00:00')
+-----------------------------------------------------------------+-------------------------------------------------------------------+-------------------------------------------------------------------+
| timestampdiff(hour,'2022-08-08 08:08:00', '2022-08-08 00:00:00') | timestampdiff(minute,'2022-08-08 08:08:00', '2022-08-08 00:00:00') | timestampdiff(second,'2022-08-08 08:08:00', '2022-08-08 00:00:00') |
+-----------------------------------------------------------------+-------------------------------------------------------------------+-------------------------------------------------------------------+
| -8                                                              | -488                                                              | -29280                                                            |
+-----------------------------------------------------------------+-------------------------------------------------------------------+-------------------------------------------------------------------+

# 比较日期的相差的周，季度数
select timestampdiff(week,'2022-08-08 08:08:00', '2022-08-01 00:00:00'),timestampdiff(QUARTER,'2022-08-08 08:08:00', '2022-05-01 00:00:00')
+-----------------------------------------------------------------+--------------------------------------------------------------------+
| timestampdiff(week,'2022-08-08 08:08:00', '2022-08-01 00:00:00') | timestampdiff(QUARTER,'2022-08-08 08:08:00', '2022-05-01 00:00:00') |
+-----------------------------------------------------------------+--------------------------------------------------------------------+
| -1                                                              | -1                                                                 |
+-----------------------------------------------------------------+--------------------------------------------------------------------+

日期或时间拆分

函数名称	功能
DAYOFWEEK(date)	返回date所代表的一星期中的第几天(1~7)
DAYOFMONTH(date)或者DAY(date)	返回date是一个月的第几天(1~31)
DAYOFYEAR(date)	返回date是一年的第几天(1~366)
DAYNAME(date)	返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);
MONTHNAME(date)	返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);
QUARTER(date)	返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);

SELECT CURRENT_DATE(),DAYOFWEEK(CURRENT_DATE),DAYOFMONTH(CURRENT_DATE),DAYOFYEAR(CURRENT_DATE),DAYNAME(CURRENT_DATE),QUARTER(CURRENT_DATE)
+----------------+-------------------------+--------------------------+-------------------------+-----------------------+-----------------------+
| CURRENT_DATE() | DAYOFWEEK(CURRENT_DATE) | DAYOFMONTH(CURRENT_DATE) | DAYOFYEAR(CURRENT_DATE) | DAYNAME(CURRENT_DATE) | QUARTER(CURRENT_DATE) |
+----------------+-------------------------+--------------------------+-------------------------+-----------------------+-----------------------+
| 2022-10-26     | 4                       | 26                       | 299                     | Wednesday             | 4                     |
+----------------+-------------------------+--------------------------+-------------------------+-----------------------+-----------------------+

函数名称	功能
WEEK(date)或weekofyear(date)	返回日期date为一年中第几周(0~53)
YEAR(date)	返回日期date的年份(1000~9999)
MONTH(date)	返回date的月份值(1~12)
DAY(date)	返回date是一个月的第几天(1~31)
dayofweek(date)	返回date是一周的第几天(1~7)，（1 = Sunday, 2 = Monday, …, 7 = Saturday）
weekday()	返回date是一周的第几天(0~6)，(0 = Monday, 1 = Tuesday, …, 6 = Sunday)
HOUR(time)	返回time的小时值(0~23)
MINUTE(time)	返回time的分钟值(0~59)
SECOND(time)	返回time的秒数值(0~59)
microsecond(time)	返回微秒
LAST_DAY(date)	返回date所属月份的最后一天

select now(),WEEK(CURRENT_DATE),YEAR(CURRENT_DATE),MONTH(CURRENT_DATE),DAY(CURRENT_DATE),HOUR(CURRENT_TIME),MINUTE(CURRENT_TIME),SECOND(CURRENT_TIME)
+---------------------+--------------------+--------------------+---------------------+-------------------+--------------------+----------------------+----------------------+
| now()               | WEEK(CURRENT_DATE) | YEAR(CURRENT_DATE) | MONTH(CURRENT_DATE) | DAY(CURRENT_DATE) | HOUR(CURRENT_TIME) | MINUTE(CURRENT_TIME) | SECOND(CURRENT_TIME) |
+---------------------+--------------------+--------------------+---------------------+-------------------+--------------------+----------------------+----------------------+
| 2022-10-26 07:31:03 | 43                 | 2022               | 10                  | 26                | 7                  | 31                   | 3                    |
+---------------------+--------------------+--------------------+---------------------+-------------------+--------------------+----------------------+----------------------+

select microsecond('2022-10-22 07:15:30.123456')
+-------------------------------------------+
| microsecond('2022-10-22 07:15:30.123456') |
+-------------------------------------------+
| 123456                                    |
+-------------------------------------------+

SELECT MONTHNAME(CURRENT_DATE),DAYNAME(CURRENT_DATE),LAST_DAY(CURRENT_DATE),LAST_DAY('2022-02-01')
+-------------------------+-----------------------+------------------------+------------------------+
| MONTHNAME(CURRENT_DATE) | DAYNAME(CURRENT_DATE) | LAST_DAY(CURRENT_DATE) | LAST_DAY('2022-02-01') |
+-------------------------+-----------------------+------------------------+------------------------+
| October                 | Wednesday             | 2022-10-31             | 2022-02-28             |
+-------------------------+-----------------------+------------------------+------------------------+

# 返回当前月份有多少天
select now(), day(last_day(now())) as days;
+---------------------+------+
| now()               | days |
+---------------------+------+
| 2022-10-26 07:34:22 | 31   |
+---------------------+------+

# 返回当天是一周中的第几天，注意dayofweek与weekday的区别
select now(),dayofweek(now()),weekday(now()),DAYNAME(now())
+---------------------+------------------+----------------+----------------+
| now()               | dayofweek(now()) | weekday(now()) | DAYNAME(now()) |
+---------------------+------------------+----------------+----------------+
| 2022-10-26 07:48:48 | 4                | 2              | Wednesday      |
+---------------------+------------------+----------------+----------------+

extract(unit from date)函数，也可是实现日期、时间拆分的目的

unit支持的内容：
year,quarter,month,week,day,hour,minute,second,microsecond,day_hour,day_minute,day_second,day_microsecond,hour_minute,hour_second,hour_microsecond,minute_second,minute_microsecond,second_microsecond

select extract(year from now()),extract(month from now()),extract(day from now()),extract(hour from now()),extract(minute from now()),extract(second from now())
+--------------------------+---------------------------+-------------------------+--------------------------+----------------------------+----------------------------+
| extract(year from now()) | extract(month from now()) | extract(day from now()) | extract(hour from now()) | extract(minute from now()) | extract(second from now()) |
+--------------------------+---------------------------+-------------------------+--------------------------+----------------------------+----------------------------+
| 2022                     | 10                        | 27                      | 4                        | 7                          | 24                         |
+--------------------------+---------------------------+-------------------------+--------------------------+----------------------------+----------------------------+

日期和时间的格式化

函数名称	功能
DATE_FORMAT(date,fmt)	依照指定的fmt格式格式化日期date值
TIME_FORMAT(time,fmt)	依照字符串fmt格式化时间time值
STR_TO_DATE(str, fmt)	将某种格式的日期字符串转换为标准格式 %Y-%m-%d %H:%i:%s
unix_timestamp()	获取当前日期的unix时间戳
unix_timestamp(date)	获取指定日期的unix时间戳
FROM_UNIXTIME(ts)	使用标准格式 %Y-%m-%d %H:%i:%s格式化UNIX时间戳ts
FROM_UNIXTIME(ts,fmt)	根据指定的fmt格式，格式化UNIX时间戳ts
GET_FORMAT({DATE	TIME

select DATE_FORMAT(now(),'%Y/%m/%d %H:%i:%s'),DATE_FORMAT('2022-10-22','%Y年%m月%d日'),TIME_FORMAT('12:23:24','%H_%i_%s')
+----------------------------------------+------------------------------------------+------------------------------------+
| DATE_FORMAT(now(),'%Y/%m/%d %H:%i:%s') | DATE_FORMAT('2022-10-22','%Y年%m月%d日') | TIME_FORMAT('12:23:24','%H_%i_%s') |
+----------------------------------------+------------------------------------------+------------------------------------+
| 2022/10/26 10:03:00                    | 2022年10月22日                           | 12_23_24                           |
+----------------------------------------+------------------------------------------+------------------------------------+

select STR_TO_DATE('08/09/2022', '%m/%d/%Y'),str_to_date('08.09.2022 08:09:30', '%m.%d.%Y %h:%i:%s')
+---------------------------------------+---------------------------------------------------------+
| STR_TO_DATE('08/09/2022', '%m/%d/%Y') | str_to_date('08.09.2022 08:09:30', '%m.%d.%Y %h:%i:%s') |
+---------------------------------------+---------------------------------------------------------+
| 2022-08-09                            | 2022-08-09 08:09:30                                     |
+---------------------------------------+---------------------------------------------------------+

select unix_timestamp(),unix_timestamp(now()),unix_timestamp('2022-08-09 08:09:30')
+------------------+-----------------------+---------------------------------------+
| unix_timestamp() | unix_timestamp(now()) | unix_timestamp('2022-08-09 08:09:30') |
+------------------+-----------------------+---------------------------------------+
| 1666773064       | 1666773064            | 1660032570                            |
+------------------+-----------------------+---------------------------------------+

select FROM_UNIXTIME(1666773064),FROM_UNIXTIME(1666773064,'%m.%d.%Y %h:%i:%s')
+---------------------------+-----------------------------------------------+
| FROM_UNIXTIME(1666773064) | FROM_UNIXTIME(1666773064,'%m.%d.%Y %h:%i:%s') |
+---------------------------+-----------------------------------------------+
| 2022-10-26 08:31:04       | 10.26.2022 08:31:04                           |
+---------------------------+-----------------------------------------------+

函数名称	功能
DATE(date)	输出格式：2022-11-23
TIMESTAMP(datetime)	输出格式：2022-11-23 07:44:48

select date('2022-11-23 07:44:48'),date(now()),timestamp('2022-11-23 07:44:48'),timestamp(now())
+-----------------------------+-------------+----------------------------------+---------------------+
| date('2022-11-23 07:44:48') | date(now()) | timestamp('2022-11-23 07:44:48') | timestamp(now())    |
+-----------------------------+-------------+----------------------------------+---------------------+
| 2022-11-23                  | 2022-11-23  | 2022-11-23 07:44:48              | 2022-11-23 07:57:11 |
+-----------------------------+-------------+----------------------------------+---------------------+

日期、时间转换函数

函数名称	功能
time_to_sec(time)	时间转换为秒
sec_to_time(seconds)	秒转换为时间
to_days(date)	日期转换为天数，距离0000-00-00到现在的天数，建议使用4位年份
from_days(days)	天数转换为日期

# 年份不是2位时，以实际年份值为准
select to_days('0000-00-00'),to_days('0000-01-01'),to_days('1-01-01'),to_days('100-01-01')
+-----------------------+-----------------------+--------------------+----------------------+
| to_days('0000-00-00') | to_days('0000-01-01') | to_days('1-01-01') | to_days('100-01-01') |
+-----------------------+-----------------------+--------------------+----------------------+
| <null>                | 1                     | 366                | 36525                |
+-----------------------+-----------------------+--------------------+----------------------+

# 年份是2位时，默认前面加上20，建议使用4位年份
select to_days('00-01-01'),to_days('10-01-01'),to_days('2010-01-01')
+---------------------+---------------------+-----------------------+
| to_days('00-01-01') | to_days('10-01-01') | to_days('2010-01-01') |
+---------------------+---------------------+-----------------------+
| 730485              | 734138              | 734138                |
+---------------------+---------------------+-----------------------+

# 时间与秒相互转
select time_to_sec('01:00:05'),sec_to_time(3605)
+-------------------------+-------------------+
| time_to_sec('01:00:05') | sec_to_time(3605) |
+-------------------------+-------------------+
| 3605                    | 1:00:05           |
+-------------------------+-------------------+

时区转换

函数名称	功能
convert_tz(date,from_timezone,to_timezone)	将date从from_timezone转换为to_timezone

select convert_tz('2022-08-08 12:00:00', '+08:00', '+00:00')
+-------------------------------------------------------+
| convert_tz('2022-08-08 12:00:00', '+08:00', '+00:00') |
+-------------------------------------------------------+
| 2022-08-08 04:00:00                                   |
+-------------------------------------------------------+

拼凑日期、时间函数

函数名称	功能
makdedate(year,dayofyear)	拼接日期，year年份，dayofyear一年中的第几天
maketime(hour,minute,second)	拼接时间，hour小时,minute分钟,second秒

select makedate(2022,31),maketime(12,15,30)
+-------------------+--------------------+
| makedate(2022,31) | maketime(12,15,30) |
+-------------------+--------------------+
| 2022-01-31        | 12:15:30           |
+-------------------+--------------------+

获得国家地区的时间格式字符串

函数名称	功能
GET_FORMAT({DATE	TIME

select GET_FORMAT(DATE,'USA'),GET_FORMAT(DATETIME,'USA'),GET_FORMAT(TIME,'USA')
+------------------------+----------------------------+------------------------+
| GET_FORMAT(DATE,'USA') | GET_FORMAT(DATETIME,'USA') | GET_FORMAT(TIME,'USA') |
+------------------------+----------------------------+------------------------+
| %m.%d.%Y               | %Y-%m-%d %H.%i.%s          | %h:%i:%s %p            |
+------------------------+----------------------------+------------------------+

函数调用	结果
GET_FORMAT(DATE,‘USA’)	‘%m.%d.%Y’
GET_FORMAT(DATE,‘JIS’)	‘%Y-%m-%d’
GET_FORMAT(DATE,‘ISO’)	‘%Y-%m-%d’
GET_FORMAT(DATE,‘EUR’)	‘%d.%m.%Y’
GET_FORMAT(DATE,‘INTERNAL’)	‘%Y%m%d’
GET_FORMAT(DATETIME,‘USA’)	‘%Y-%m-%d %H.%i.%s’
GET_FORMAT(DATETIME,‘JIS’)	‘%Y-%m-%d %H:%i:%s’
GET_FORMAT(DATETIME,‘ISO’)	‘%Y-%m-%d %H:%i:%s’
GET_FORMAT(DATETIME,‘EUR’)	‘%Y-%m-%d %H.%i.%s’
GET_FORMAT(DATETIME,‘INTERNAL’)	‘%Y%m%d%H%i%s’
GET_FORMAT(TIME,‘USA’)	‘%h:%i:%s %p’
GET_FORMAT(TIME,‘JIS’)	‘%H:%i:%s’
GET_FORMAT(TIME,‘ISO’)	‘%H:%i:%s’
GET_FORMAT(TIME,‘EUR’)	‘%H.%i.%s’
GET_FORMAT(TIME,‘INTERNAL’)	‘%H%i%s’

加密函数

函数名称	功能
AES_ENCRYPT(str,key)	使用AES加密,返回用密钥key对字符串str利用高级加密标准算法加密后的结果，调用AES_ENCRYPT的结果是一个二进制字符串，以BLOB类型存储
AES_DECRYPT(str,key)	使用AES解密,返回用密钥key对字符串str利用高级加密标准算法解密后的结果
MD5(str)	计算字符串str的MD5校验和
SHA(str),SHA1(str)	计算字符串str的安全散列算法(SHA)校验和
SHA2(str)	计算字符串str的安全散列算法(SHA2)校验和

select AES_ENCRYPT('123456','abc'),AES_DECRYPT(AES_ENCRYPT('123456','abc'),'abc')
+------------------------------------+------------------------------------------------+
| AES_ENCRYPT('123456','abc')        | AES_DECRYPT(AES_ENCRYPT('123456','abc'),'abc') |
+------------------------------------+------------------------------------------------+
| 0xe46cef3cb055471402ea4dfb7249cb32 | 123456                                         |
+------------------------------------+------------------------------------------------+

# 不可逆
select MD5('123456'),SHA('123456')
+----------------------------------+------------------------------------------+
| MD5('123456')                    | SHA('123456')                            |
+----------------------------------+------------------------------------------+
| e10adc3949ba59abbe56e057f20f883e | 7c4a8d09ca3762af61e59520943dc26494f8941b |
+----------------------------------+------------------------------------------+

信息函数

名称	功能
USER(),SESSION_USER(),SYSTEM_USER()	客户端提供的用户名和主机名
CURRENT_USER(),CURRENT_USER	经过身份验证的用户名和主机名
DATABASE(),SCHEMA()	返回默认（当前）数据库名称
VERSION()	返回指示MySQL服务器版本的字符串
ICU_VERSION()	Unicode国际组件（ICU）库的版本,用于支持正则表达式操作
CONNECTION_ID()	返回连接的连接ID（线程ID）
CHARSET(x)	查询x的存储字符集
COLLATION(x)	查询x的排序字符集

select user(),SESSION_USER(),SYSTEM_USER(),CURRENT_USER(),CURRENT_USER
+-------------------+-------------------+-------------------+----------------+--------------+
| user()            | SESSION_USER()    | SYSTEM_USER()     | CURRENT_USER() | CURRENT_USER |
+-------------------+-------------------+-------------------+----------------+--------------+
| root@1.119.151.30 | root@1.119.151.30 | root@1.119.151.30 | root@%         | root@%       |
+-------------------+-------------------+-------------------+----------------+--------------+

select version(),database(),schema(),ICU_VERSION(),CONNECTION_ID()
+-----------+------------+----------+---------------+-----------------+
| version() | database() | schema() | ICU_VERSION() | CONNECTION_ID() |
+-----------+------------+----------+---------------+-----------------+
| 8.0.30    | test       | test     | 69.1          | 138             |
+-----------+------------+----------+---------------+-----------------+

SELECT CHARSET('abc'),CHARSET(1010),CHARSET(0x1010),COLLATION('abc'),COLLATION(1010),COLLATION(0x1010)
+----------------+---------------+-----------------+--------------------+-----------------+-------------------+
| CHARSET('abc') | CHARSET(1010) | CHARSET(0x1010) | COLLATION('abc')   | COLLATION(1010) | COLLATION(0x1010) |
+----------------+---------------+-----------------+--------------------+-----------------+-------------------+
| utf8mb4        | binary        | binary          | utf8mb4_general_ci | binary          | binary            |
+----------------+---------------+-----------------+--------------------+-----------------+-------------------+

其它函数

函数名称	功能
INET_ATON(ip)	返回IP地址的数字表示
INET_NTOA(num)	返回数字所代表的IP地址
IS_IPV4(str)	参数是否为IPv4地址
INET6_ATON(ip)	返回IPv6地址的数字值
INET6_NTOA(num)	从数字值返回IPv6地址
IS_IPV6(str)	参数是否为IPv6地址
UUID()	返回通用唯一标识符（UUID）
UUID_SHORT()	返回整数值通用标识符
IS_UUID(str)	参数是否为有效的UUID
ANY_VALUE(column)	抑制ONLY_FULL_GROUP_BY值拒绝，返回分组内任意数据
GROUPING(column)	将超聚合的ROLLUP行与常规行区分开来，返回值是0或1

SELECT INET_ATON('10.122.89.47'),INET_NTOA(175790383),IS_IPV4('10.122.89.47')
+---------------------------+----------------------+-------------------------+
| INET_ATON('10.122.89.47') | INET_NTOA(175790383) | IS_IPV4('10.122.89.47') |
+---------------------------+----------------------+-------------------------+
| 175790383                 | 10.122.89.47         | 1                       |
+---------------------------+----------------------+-------------------------+

select INET6_ATON('fdfe::5a55:caff:fefa:9089'),INET6_NTOA(0xfdfe0000000000005a55cafffefa9089),IS_IPV6('fdfe::5a55:caff:fefa:9089')
+-----------------------------------------+------------------------------------------------+--------------------------------------+
| INET6_ATON('fdfe::5a55:caff:fefa:9089') | INET6_NTOA(0xfdfe0000000000005a55cafffefa9089) | IS_IPV6('fdfe::5a55:caff:fefa:9089') |
+-----------------------------------------+------------------------------------------------+--------------------------------------+
| 0xfdfe0000000000005a55cafffefa9089      | fdfe::5a55:caff:fefa:9089                      | 1                                    |
+-----------------------------------------+------------------------------------------------+--------------------------------------+

select UUID(),UUID_SHORT(),IS_UUID('872192b2-55b3-11ed-8890-0ad5455f39d8')
+--------------------------------------+----------------------+-------------------------------------------------+
| UUID()                               | UUID_SHORT()         | IS_UUID('872192b2-55b3-11ed-8890-0ad5455f39d8') |
+--------------------------------------+----------------------+-------------------------------------------------+
| 9761d30a-55b3-11ed-8890-0ad5455f39d8 | 16889433509511299076 | 1                                               |
+--------------------------------------+----------------------+-------------------------------------------------+

流程控制

控制函数

函数名称	功能
if(expr,v1,v2)	实现if-else的效果，如果expr是true，返回v1。如果expr是false，返回v2
ifnull(v1,v2)	判断v1值是否为null，如果v1不为NULL，返回v2，否则返回v1
nullif(v1,v2)	如果v1=v2则返回null,否则返回v1

select if(1>2,'1>2','1<=2'),ifnull(1,2),ifnull(null,2),nullif(1,1),nullif(1,2)
+----------------------+-------------+----------------+-------------+-------------+
| if(1>2,'1>2','1<=2') | ifnull(1,2) | ifnull(null,2) | nullif(1,1) | nullif(1,2) |
+----------------------+-------------+----------------+-------------+-------------+
| 1<=2                 | 1           | 2              | <null>      | 1           |
+----------------------+-------------+----------------+-------------+-------------+

# 统计每个部门年龄等于34的人数
select depName,sum(if(age=34,1,0)),group_concat(age) from tbl_employees group by depName
+---------+---------------------+-------------------------+
| depName | sum(if(age=34,1,0)) | group_concat(age)       |
+---------+---------------------+-------------------------+
| 技术部  | 2                   | 22,24,26,28,34,34,36,38 |
| 运维部  | 1                   | 34,24,26,38             |
| 销售部  | 0                   | 44,24,46,38             |
+---------+---------------------+-------------------------+

控制语句`case…when`

case…when比if函数功能更加强大，支持多个条件分支判断

case…when语句支持两种判断类型

1.表达式与常量比较

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
...
else 缺省要显示的值n或语句n
end

SELECT CASE 'green' WHEN 'red' THEN 'stop' WHEN 'green' THEN 'go' ELSE 'null' END as result;
+--------+
| result |
+--------+
| go     |
+--------+

# 统计每个部门年龄等于34的人数
select depName,sum(case age when 34 then 1 else 0 end),group_concat(age) from tbl_employees group by depName
+---------+------------------------------------------+-------------------------+
| depName | sum(case age when 34 then 1 else 0 end ) | group_concat(age)       |
+---------+------------------------------------------+-------------------------+
| 技术部  | 2                                        | 22,24,26,28,34,34,36,38 |
| 运维部  | 1                                        | 34,24,26,38             |
| 销售部  | 0                                        | 44,24,46,38             |
+---------+------------------------------------------+-------------------------+

2.分支条件判断

case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 缺省要显示的值n或语句n
end

SELECT CASE WHEN 1>2 THEN 'stop' WHEN 1<2 THEN 'go' ELSE 'null' END as result;
+--------+
| result |
+--------+
| go     |
+--------+

# 统计每个部门年龄等于34的人数
select depName,sum(case when age=34 then 1 else 0 end),group_concat(age) from tbl_employees group by depName
+---------+------------------------------------------+-------------------------+
| depName | sum(case when age=34 then 1 else 0 end ) | group_concat(age)       |
+---------+------------------------------------------+-------------------------+
| 技术部  | 2                                        | 22,24,26,28,34,34,36,38 |
| 运维部  | 1                                        | 34,24,26,38             |
| 销售部  | 0                                        | 44,24,46,38             |
+---------+------------------------------------------+-------------------------+

算术运算符

名称	功能
+	加法
-	减法
*	乘法
/	除法
div	整除，只取整数部分
%，mod	模运算符，取余数

# 除法
select 10/4,10 div 4
+--------+----------+
| 10/4   | 10 div 4 |
+--------+----------+
| 2.5000 | 2        |
+--------+----------+

# 取余数
select 10 mod 3,mod(10,3),10 % 3
+----------+-----------+--------+
| 10 mod 3 | mod(10,3) | 10 % 3 |
+----------+-----------+--------+
| 1        | 1         | 1      |
+----------+-----------+--------+

摘要