MySQL常用函数整理，建议收藏！
常见函数
- 字符串函数
- 数字函数
- 日期函数
- 聚合函数
- 流程控制函数
一、字符串函数
concat(s1,s2...,sn)
--将s1,s2...,sn连接成字符串，如果该函数中的任何参数为 null，返回结果为 null
concat_ws(sep,s1,s2...,sn)
--将s1,s2...,sn连接成字符串,并用sep字符间隔
substr(str,start,len)
--从start位置开始截取字符串，len表示要截取的长度；
lcase(str)或lower(str)
--将字符串str转换为小写形式
upper(str)和ucase(str)
--将字符串转换为大写形式
length(s) --返回字符串str中的字符数
trim(str) --去除字符串首部和尾部的所有空格
left(str,x) --返回字符串str中最左边的x个字符
right(str,x) --返回字符串str中最右边的x个字符
strcmp(s1,s2) --比较字符串s1和s2是否相同 相同返回0，否则-1
下面对个别举例
1、concat函数
select concat('mysql', 'mongodb', 'es') as str;
+----------------+
| str |
+----------------+
| mysqlmongodbes |
+----------------+
如果该函数中的任何参数为 null，返回结果为 null
select concat('mysql', null, 'es') as str;
+------+
| str |
+------+
| null |
+------+
2、concat_ws函数
select concat_ws('-', 'mysql', 'mongodb', 'es') as str1;
+------------------+
| str1 |
+------------------+
| mysql-mongodb-es |
+------------------+
如果该函数中的存在参数为 null，则会过滤null
select concat_ws('-', 'mysql', null, 'es') as str1;
+----------+
| str1 |
+----------+
| mysql-es |
+----------+
3、substr函数
没有指定len长度：表示从start开始起，截取到字符串末尾。
select substr('今天天气真不错',3) as str;
+---------+
| str |
+---------+
|天气真不错 |
+---------+
指定了len长度：表示从start开始起，截取len个长度。
select substr('今天天气真不错',3,2) as str;
+------+
| str |
+------+
| 天气 |
+------+
4、trim函数
select trim(' 今天杭州天气40度 ') as str1;
+--------------+
| str1 |
+--------------+
|今天杭州天气40度|
+--------------+
默认是空格，我们可以指定子串
select trim('-' from '---今天杭州天气40度---') as str1;
+--------------+
| str1 |
+--------------+
|今天杭州天气40度|
+--------------+
二、数字函数
round(x,y) --四舍五入,y表示保留小数位(y非必填)
ceil(x) --向上取整，返回>=该参数的最小整数。
floor(x) --向下取整，返回<=该参数的最大整数。
mod(x,y) --返回x/y的模（余数）
greatest(x1,x2,...,xn)
--返回集合中最大的值
least(x1,x2,...,xn)
--返回集合中最小的值
rand() --返回０到１内的随机值,可以通过提供一个参数(种子)使rand()随机数生成器生成一个指定的值。
truncate(x,y) --返回数字x截短为y位小数的结果
示例
1、round函数
round(x);四舍五入
round(x,y)对x进行保留y位
select round(3.47), round(3.47,1);
+-------------+---------------+
| round(3.47) | round(3.47,1) |
+-------------+---------------+
| 3 | 3.5 |
+-------------+---------------+
三、日期函数
在讲时间之前我们先来理解下 一些基本概念
（图2）
now() --返回当前的日期和时间
curdate()或current_date()
--返回当前的日期
curtime()或current_time()
--返回当前的时间
year(date) --获取年份
month(date) --获取月份
day(date) --获取日
hour(date) --获取小时
minute(date) --获取分钟
second(date) --获取秒数
str_to_date(str,format) --将日期格式的字符串，转换成指定格式的日期
date_format(date,format)
--将日期转换为对应的字符串格式
date_add(date,interval expr unit)
--增加日期时间
date_sub(date,interval expr unit)
--减少日期时间
dayofweek(date) --返回date所代表的一星期中的第几天(1~7)
dayofmonth(date) --返回date是一个月的第几天(1~31)
dayofyear(date) --返回date是一年的第几天(1~366)
quarter(date) --返回date在一年中的季度(1~4)
unix_timestamp(date)
--将当前时间转为时间戳 如:select unix_timestamp(now())
from_unixtime(tr) --将时间戳转为时间 如:select from_unixtime(unix_timestamp());
select period_diff(200302,199802);
--返回两个日期值之间的差值(月数)
示例
1、str_to_date(str,format)
srt:要格式化为日期的字符串(输入字符串)
format:要使用的格式字符串
如果不能按照format解析str，str_to_date函数将返回null
-- 2017-01-06 10:20:30
select str_to_date('2017-01-06 10:20:30','%Y-%m-%d %H:%i:%s');
-- 2022-07-06
select str_to_date('2022-07-06 10:20:30','%Y-%m-%d');
-- 2022-05-25
select str_to_date('25,5,2022','%d,%m,%Y');
-- 2022-05-25 11:30:00
select str_to_date('20220525 1130','%Y%m%d %h%i');
-- 2022-05-27 10:40:10
select str_to_date('2022,5,27 10,40,10', '%Y,%m,%d %h,%i,%s');
-- 2022-05-25
select str_to_date('25,5,2022 extra characters','%d,%m,%Y');
2、date_format(date,format)
使用场景：开始结束时间的查询条件，根据年月日星期分组的查询
SELECT
id,
DATE_FORMAT(create_time, '%Y-%c-%d')
FROM order
WHERE DATE_FORMAT(create_time, '%Y-%m-%d') >= '2022-07-15';
使用场景：计算年龄
-- 计算年龄方式一
SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(Birthday, '%Y') as age from employee
--计算年龄方式二
SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(create_time)),'%Y')+0 AS age FROM employee;
3、date_add(date,interval expr unit)
作用: 增加日期时间
应用场景：当前时间的前一天，前几分钟。 常用于数据统计。
例子：
select date_add(now(),interval 1 day) as day;
+---------------------+
| day |
+---------------------+
| 2022-07-16 02:23:15 |
+---------------------+
其中 Date 表示日期格式，其中就包括: 如 2017-12-27，now() 等格式。
expr：表示数量。
unit：表示单位，支持毫秒(microsecond)，秒(second)，小时(hour)，天(day)，周(week)，月(month),年(year)等。
四、聚合函数
avg(x) --返回指定列的平均值
count(x) --返回指定列中非null值的个数
min(x) --返回指定列的最小值
max(x) --返回指定列的最大值
sum(x) --返回指定列的所有值之和
group_concat(x) --返回由属于一组的列值连接组合而成的结果，非常有用
group_concat(x)
group_concat函数在开发中确实很有用，group_concat需要和group by联合使用，用于将某一列的值按指定的分隔符进行拼接，mysql默认的分隔符是逗号。
示例一
对一张表使用group_concat函数
-- 1、好比这有个部门表，我想看下每个部门一共有多少人，我们可以这样写。
select dept, count(*) from employee group by dept;
+-------+----------+
| dept | count(*) |
+-------+----------+
| 部门a | 5 |
| 部门c | 3 |
| 部门b | 5 |
+-------+----------+
--2、如果我们想看下每个部门的具体人名，我们就可以用group_concat函数
select dept,group_concat(name) from employee group by dept;
+-------+------------------------------+
| dept | group_concat(name) |
+-------+------------------------------+
| 部门a | 小王,小王,小高,小高,小秦 |
| 部门b | 小红,小红,小红,小秦,小马 |
| 部门c | 小马,小朱,小朱
————————————————
-- 3、我们还可以对人名排序
select dept,group_concat(name order by name desc) from employee group by dept;
-- 4、我们还可以对人名进行去重
select dept,group_concat(distinct name) from employee group by dept;
+-------+------------------------------+
| dept | group_concat(name) |
+-------+------------------------------+
| 部门a | 小王,小高,小秦 |
| 部门b | 小红,小秦,小马 |
| 部门c | 小马,小朱
————————————————
示例2
对两张及以上表使用group_concat函数
--一张文章表，一张文章关联标签表，它们是一对多的关系，现在我想一次性查出该文章下所有标签信息
select se.id, se.article_title, group_concat(ca.name) as tagnames
from article se
left join article_tag ca on se.`id` = ca.`article_id`
group by se.id
+-------+------------------------------+
| id | article_title | tagnames |
+-------+------------------------------+
| 1 | 杰森斯坦森蜕变! |好莱坞,八卦,娱乐 |
| 2 | 郭德纲助理被开除! |德云社,郭德纲,相声|
————————————————
五、流程控制图
1、if函数
语法
if(test,t,f) --如果test是真，返回t；否则返回f
示例
select name,age,if(age>60,'老年','不老') as remark from user
+------+------+--------+
| name | age | remark |
+------+------+--------+
| 张三 | 10 | 不老 |
| 李四 | 69 | 老年 |
| 王五 | 72 | 老年 |
| 赵六 | 30 | 不老 |
+------+------+--------+
2、ifnull函数
判断值是否为null，是null用指定值填充；
语法
ifnull(arg1,arg2) --如果arg1不是空，返回arg1，否则返回arg2
nullif(arg1,arg2) --如果arg1=arg2返回null；否则返回arg1
示例
select ifnull(1,2) r1, ifnull(null,5) r2, ifnull(2*null,'false') r3;
+----+----+-------+
| r1 | r2 | r3 |
+----+----+-------+
| 1 | 5 | false |
+----+----+-------+
select nullif(1,1) as r1,nullif('a','b') as r2,nullif(2+3,4+1) as r2;
+------+------+------+
| r1 | r2 | r2 |
+------+------+------+
| null | a | null |
+------+------+------+
3、case...when函数
case when 有两种写法：
- 搜索case when，好处是每一次假设都可以指定不同的列
其实只会这一种方式就可以，下面是不同写法而已，功能都能实现
case
when <求值表达式> then <表达式1>
when <求值表达式> then <表达式2>
else <表达式>
end
<求值表达式> ：一般为字段 【=、>、<、in、等】如 字段 = "1"
<表达式1> : 一般为字段或者字符串或者数值等。
2）简单case表达式
case <表达式>
when <表达式> then <表达式>
when <表达式> then <表达式>
else <表达式>
end
注意
- else 可以不写，默认返回null
- end 不可以忘记
- 当一个case子句中有多个判断逻辑时、字段类型需要一致
- 当一个case子句中有多个判断逻辑时、第一个为真的结果会被输出
这里举个完整的示例,我们先看原始数据
需求一:统计下每个人的分数在什么阶段。
select id,name,score,
case
when score>=90 then '优秀'
when score>=80 and score <90 then '良好'
when score>=60 and score<80 then '合格'
when score<60 then '不合格'
end as '阶段' from t_score;
运行结果
需求2:统计优秀、良好、合格、不合格人数。
select
case
when score>=90 then '优秀'
when score>=80 and score <90 then '良好'
when score>=60 and score<80 then '合格'
when score<60 then '不合格'
end as '阶段',count(*) as '人数' from t_score group by case
when score>=90 then '优秀'
when score>=80 and score <90 then '良好'
when score>=60 and score<80 then '合格'
when score<60 then '不合格'
end;
-- 上面sql我们也可以使用别名
select
case
when score>=90 then '优秀'
when score>=80 and score <90 then '良好'
when score>=60 and score<80 then '合格'
when score<60 then '不合格'
end as type,count(*) as '人数' from t_score group by type;
运行结果
需求3：不使用group by 的聚合来统计优秀、良好、合格、不合格人数。
需求2返回的是4条数据，如果我们想变成一条数据，这样是不是更加方便。
select
sum(case when score>=90 then 1 else 0 end ) as '优秀' ,
sum(case when score>=80 and score <90 then 1 else 0 end ) as '良好' ,
sum(case when score>=60 and score<80 then 1 else 0 end ) as '合格' ,
sum(case when score<60 then 1 else 0 end ) as '不合格'
from t_score;
运行结果
声明: 公众号如需转载该篇文章,发表文章的头部一定要 告知是转至公众号: 后端元宇宙。同时也可以问本人要markdown原稿和原图片。其它情况一律禁止转载！
