MySql--sql语句与常用函数

摘要

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也可以达到去重的目的,并可以基于聚合函数实现排序的目的
1
2
3
4
5
# 错误方式
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用于统计各个部门员工的年龄平均值、最大值和最小值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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自动填充的来设置想要的内容,比如

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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 '分隔符'])

  • 默认逗号连接,不去重,不排序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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 ':'

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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关键字,其结果是排好序的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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

1
2
3
4
5
6
7
8
9
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()等等一块使用,这么说貌似不好理解,直接看示例吧

  • 将年龄的平均值显示在每行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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 |
+---------+-----------+-----+---------+
  • 将每个部门的年龄平均值显示在每行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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 |
+---------+-----------+-----+---------+
  • 将每个部门下每个分组的年龄平均值显示在每行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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 |
+---------+-----------+-----+---------+
  • 每个部门每个分组下按年龄倒序并显示每组的平均年龄

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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按哪个字段排序,如果其值相同,调用分析函数时要一起计算。每个分组的最后一条记录的显示的平均值才是该组的真实平均值
  • 不分组直接排序,这里按年龄倒序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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 |
+---------+-----------+-----+---------+
这里的计算方法与上面分组的情况类似,按行计算平均值,遇到排序字段值相同时,要一起计算。最后一条记录显示的平均值就是所有记录的平均值。
  • 查询每个员工年龄与全体平均年龄的差值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 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()函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
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的最大整数值
1
2
3
4
5
6
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) 返回集合中最小的值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 按照数字顺序,字符顺序,首字母顺序比较
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的值(圆周率)
1
2
3
4
5
6
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() 返回0到1内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 相同的种子,返回值相同
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
1
2
3
4
5
6
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

1
2
3
4
5
6
7
# 不指定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) 返回由属于一组的列值连接组合而成的结果
1
2
3
4
5
6
7
8
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
# 字符串替换
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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

1
2
3
4
5
6
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
# 比较日期,时间的差值
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);
1
2
3
4
5
6
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所属月份的最后一天
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
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

1
2
3
4
5
6
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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 | 20221022| 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
1
2
3
4
5
6
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) 天数转换为日期
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 年份不是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
1
2
3
4
5
6
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秒
1
2
3
4
5
6
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
1
2
3
4
5
6
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)校验和
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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的排序字符集
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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…whenif函数功能更加强大,支持多个条件分支判断

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

    • 1.表达式与常量比较
    1
    2
    3
    4
    5
    6
    case 要判断的字段或表达式
    when 常量1 then 要显示的值1或语句1
    when 常量2 then 要显示的值2或语句2
    ...
    else 缺省要显示的值n或语句n
    end
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    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.分支条件判断

    1
    2
    3
    4
    5
    6
    case
    when 条件1 then 要显示的值1或语句1
    when 条件2 then 要显示的值2或语句2
    ...
    else 缺省要显示的值n或语句n
    end
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    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 模运算符,取余数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 除法
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 |
+----------+-----------+--------+