万字掌握MYSQL常用函数

hello，大家好，第一次写博客，写的不好或有错误的，可以私信我哟，希望大家留下一个关注，多多支持，大家一起努力！！！这篇博客主要介绍MYSQL中我们会经常用到的函数。

文章目录

• • 一、字符串函数
  • • 1、替换
    • 2、合并
    • 3、截取
    • 4、字符串长度
    • 5、字符串重复
    • 6、匹配
    • 7、去除字符串空格
  • 二、数据运算函数
  • • 1、数学运算
    • 2、比较运算
    • 3、逻辑运算
    • 4、聚合运算
  • 三、控制函数
  • • 1、if(条件,满足条件返回值,不满足条件返回值) 函数
    • 2、case when 函数
  • 四、json类型列解析
  • • 1、查看字典的键、值
    • 2、将score列拆分
    • 3、将2的结果行转列
    • 4、将3的结果再变回原数据格式
  • 五、日期时间函数
  • • 1、获取当前时间
    • 2、日期和时间格式转换
    • 3、日期时间运算
  • 六、数据分组和数据透视
  • • 1、数据分组 并 汇总
    • 2、数据透视
  • 七、窗口函数
  • • 1、聚合函数 + over()函数
    • 2、partition by 子句
    • 3、order by 子句
    • 4、排序函数
    • 5、分组函数
    • 6、头尾函数
    • 7、向前和向后移动函数
    • 8、移动窗口函数

一、字符串函数

1、替换

# replace('字符串','需要替换的字符串','替换后的字符串')select replace('abc123efd','123','aaaa')  

2、合并

# concat(a,b)  # concat_ws('分隔符',a,b)# group_concat(a,separator ';')select concat('123','abc');  # 123abcselect concat_ws('-','123','abc'); #123-abcselect name      ,concat('{',group_concat(concat('"',科目,'":',score) order by score desc separator ';'),'}') as scorefrom t2group by name;  # 小乔 {"英语":88;"数学":71;"语文":38}

3、截取

# left(str,n) 从左侧截取n个字符select left('123456',3); # 123# right(str,n) 从右侧截取n个字符select right('123456',3); # 456# substring(str,m,n) 截取字符串str从m位置开始的长度为n的字符select substring('123456',2,4); # 2345   

4、字符串长度

# length(str) 字节长度 一个文字utf-8编码环境下占3个字节,gbk下占2个字节select length('abcd我'); # 字节长度 7 select char_length('abcd我'); #字符长度 5

5、字符串重复

select REPEAT('d',6); # dddddd

6、匹配

# like/not like '正则表达式'select '女娲娘娘' like '女%';  # 1  是否是以女字开头的字符select '女娲娘娘' like '女_娘';# 0  是否以女开头,娘结尾,字符长度3的字符串

7、去除字符串空格

ltrim/rtrim/trim(str) 去除左/右/左右空白格select ltrim(' 我一个人 '); # '我一个人 'select rtrim(' 我一个人 '); # ' 我一个人'select trim(' 我一个人 ');  # '我一个人'

二、数据运算函数

1、数学运算

select 4+5; # 9 加select 4-5; # -1 减select 4*5; # 20 乘select 4/5; # 0.8 除select 6%4; # 2 取余数select abs(-9); # 9 绝对值select ceil(5.8); # 6 大于此数的最小整数值select floor(7.9); # 7 小于此数的最大整数值select sign(-9); # 判断正负数 负数输出-1select sign(4);  # 正数输出1# 数学运算函数还有很多很多，就不一一举例了

2、比较运算

• 大于,=等于,>=大于等于,<=小于等于,!=不等于,不等于,is null 为空,is not null不为空, between a and b 在 a和b 之间

select 1>3; # 0select 5!=0; # 1select 1 is null; # 0select 6 between 1 and 7; # 1

3、逻辑运算

select 1=3 and 3=4;  # 0  and 全部满足才可以select 2=3 or 3<4;   # 1  or 满足其一就可以

4、聚合运算

count();#计数max();#最大值min();#最小值avg();#平均值sum();#求和

三、控制函数

1、if(条件,满足条件返回值,不满足条件返回值) 函数

select if(3+4=7,1,0);  # 输出1select if(3+4=8,1,0);  # 输出0

2、case when 函数

• 写法一：case when 条件1 then 返回值1 when 条件2 then 返回值2 …… else 返回值n end
• 写法二：case 需要判断的式子 when 条件1 then 返回值1 when 条件2 then 返回值2 …… else 返回值n end
• 例子：分数小于60不及格,60—80良好,90以上优秀；把班级一班二班三班转换成class1 class2 class3；原数据如图
  

select *    , case when score < 60 then '不及格'   when score < 80 and score >=60 then '良好'   else '优秀'  endas res, case class when '一班' then 'class1'  when '二班' then 'class2'  else 'class3' endas class_resfrom stu_info

四、json类型列解析

数据集如下：表score，字段name姓名和score各科分数

1、查看字典的键、值

select name,json_keys(score) as 科目from score

2、将score列拆分

• json_extract(列名, ‘$.键名’) 提取键相对应的值

select name,JSON_EXTRACT(score, '$.数学') as '数学',JSON_EXTRACT(score, '$.语文') as '语文',JSON_EXTRACT(score, '$.英语') as '英语'from score

3、将2的结果行转列

# 将2的结果记为表t1select name,'数学' as 科目, 数学 as score from t1union allselect name,'语文' as 科目, 语文 as score from t1union allselect name,'英语' as 科目, 英语 as score from t1order by name

4、将3的结果再变回原数据格式

• 将3的结果记为t2 concat()和group_concat()函数参考字符串函数

select name      ,concat('{',group_concat(concat('"',科目,'":',score)),'}') as scorefrom t2group by name

五、日期时间函数

1、获取当前时间

# 日期函数 curdate()只输出日期 now()日期时间都输出select curdate(),now(),date(now()),year(now()),month(now()),day(now());# 时间函数 curtime()只输出时间 select curtime(),time(now()),hour(now()),minute(now()),second(now()); # 全年中的第几周select weekofyear(now());  # 15 值的是一年中的第15周  # 一周中的第几天 在SQL中周日到周六按1—7计算select dayofweek(now()); # 2 一周中的第二天即周一 # 季度函数select quarter(curdate()); # 2 第二季度

2、日期和时间格式转换

• extract(unit from 日期时间) ：提取日期时间里的具体哪项 unit 可以是 (year,month,day,hour,minute,second,week)
• date_format(日期时间,格式)：将日期时间转化成需要的指定格式
  • 年： %Y四位数年份 ， %y两位数年份
  • 月： %M月份全英文， %b月份英文缩写，%m（01-12）月， %c （1-12）月
  • 日： %D天数的英文缩写th结尾， %d（01-31）日， %e （1-31）日，%j（001-366）一年中的第几天
  • 周：%a 星期几的英文缩写，%W英文全称
  • 时：%H（00-23），%h（01-12）
  • 分：%i（00-59）
  • 秒：%S（00-59） ，
  • 时分秒：%T （hh:mm:ss）

select DATE_FORMAT('2022-04-09','%Y%m%d')     # 20220409      ,DATE_FORMAT('2022-04-09','%Y-%M-%D')   # 2022-April-9th  ,DATE_FORMAT('2022-04-09','%Y-%c-%e')   # 2022-4-9  ,DATE_FORMAT('2022-04-09','%Y-%m-%d');  # 2022-04-09select EXTRACT(day from '2022-04-09'); # 9

3、日期时间运算

• date_add(date, interval num day/month/year) ：往后移几天/月/年
• date_sub(date, interval num day/month/year) ：往前移几天/月/年
• datediff(end，start)：日期天数差
• timestampdiff(day/hour/minute/secod, start_time, end_time) ：时间差（天/小时/分/秒）
• unix_timestamp(日期)：日期转时间戳
• from_unixtime(时间戳) ：时间戳转日期时间

select '2020-02-01' ,DATE_ADD('2020-02-01',interval 10 day) # 2020-02-11,DATE_ADD('2020-02-01',interval 3 month)# 2020-05-01,DATE_ADD('2020-02-01',interval 2 year);# 2022-02-01select '2020-02-01 02:34:20',,DATE_SUB('2020-02-01 02:34:20',interval 10 hour)   # 2020-01-31 16:34:20,DATE_SUB('2020-02-01 02:34:20',interval 3 minute)  # 2020-02-01 02:31:20,DATE_SUB('2020-02-01 02:34:20',interval 2 second);# 2020-02-01 02:34:18select datediff('2022-02-01','2020-02-01'); # 731 两个日期天数相差731天select timestampdiff(month,'2022-02-01 02:00:00','2022-03-01 03:00:00'); # 1 相差一个月select timestampdiff(second,'2022-02-01 02:00:00','2022-03-01 03:00:00');# 2422800 相差多少秒

六、数据分组和数据透视

1、数据分组 并 汇总

• group by with rollup ：不同维度的汇总
• 计算各个城市/各个省份/全部省份的总销售额及店铺数量；原数据如下：
  

select province, city, sum(sales) as sum_sales, count(shop) as shop_numfrom shop_saleGROUP BY province,city with ROLLUP

2、数据透视

• 计算每天每区的订单量，数据如下：
  

select deal_date    ,sum(case when area = 'A区' then 1 else 0 end) as 'A区',sum(case when area = 'B区' then 1 else 0 end) as 'B区',sum(case when area = 'C区' then 1 else 0 end) as 'C区'from order_info GROUP BY deal_date

七、窗口函数

• 以下数据表order_info为例，展示窗口函数的厉害之处
  

1、聚合函数 + over()函数

• 例子：每个店铺日销量和全部销量的均值作比较，放在一张表里

# 不使用  聚合函数+over()select *    , (select avg(sales) from chapter11) as sale_avgfrom order_info;# 使用 聚合函数+over()  over()将所有数据当作一个区，计算平均值select *, avg(sales) over() as sale_avg from order_info;

2、partition by 子句

• over(partition by 列名) 按照某列相同的值进行分区，后面的操作在各自区里进行计算。
  -例子： 每个店铺日销售量和自己店铺的销量均值作比较

select *      , avg(sales) over(PARTITION by shopname) as sales_avgfrom order_info"""解释：按照shopname分区，计算各店铺的平均值（例如：shopnameA的销售量为1，7，4 那么平均值为4，以此类推），partition by与group by不同，前者输出所有数据，后者对分组字段进行去重输出（例如：A有三条数据，那么partition by会输出3条，而group by只会输出1条A）"""

3、order by 子句

• over(order by 列名)： 对所有数据按照某列进行排序
• over(partition by 列名 order by 列名) ：对分区后的各自内部数据进行排序，在排序后的数据进行聚合时，输出的是截至当前行的聚合结果
• 例子：每个店铺日销售量与截至当天该店铺所有销售量的均值和总销售量作比较

# 如果select * ,avg(sales) over(PARTITION by shopname ORDER BY sale_date) as sale_avg #每个区截止到当前日期的销售额均值 ,avg(sales) over(PARTITION by shopname) as sale_avg2 #每个区的所有销售额均值   ,sum(sales) over(PARTITION by shopname ORDER BY sale_date) as sale_cum_sum #每个区截止到当前日期的销售额之和   ,sum(sales) over(PARTITION by shopname) as sale_cum_sum2 #每个区的所有销售额之和  from order_info

4、排序函数

• row_number()：1，2，3，4，5
• dense_rank()：1，2，2，3，4
• rank() ：1，2，2，4，5
• 例子：按照店铺名称shopname进行排序

select *, ROW_NUMBER() over(ORDER BY shopname) as r1, rank() over(ORDER BY shopname) as r2, DENSE_RANK() over(ORDER BY shopname) as r3from order_info 

5、分组函数

• ntile(n)：对分区后的数据进行切片分组
• 例子：按照销售额降序排序，分成3组

# 一共9条数据，分成3组，一组3条数据select *, ntile(3) over(ORDER BY sales desc) as cut_group from order_info

6、头尾函数

• first_value()：截至当前行的第一个值
• last_value()：截至当前行的最后一个值
• 例子：获取每个店铺的最早销售日期和截至当前行最后一次销售日期

select shopname     , sale_date     , first_value(sale_date) over(partition by shopname order by sale_date asc) first_date      , last_value(sale_date) over(partition by shopname order by sale_date asc) last_datefrom order_info

7、向前和向后移动函数

• lead(列名，n)：向前移动n行
• lag(列名，n)：向后移动n行
• 例子：计算各店铺的日环比。日环比 = (当天销售额/昨天销售额-1)*100%

select *lag(sales,1) over(PARTITION by shopname ORDER BY sale_date) as date_lag,   lead(sales,1) over(PARTITION by shopname ORDER BY sale_date) as date_lead, concat(round((sales/lag(sales,1) over(PARTITION by shopname ORDER BY sale_date)-1)*100,2),'%') as 环比from order_info

8、移动窗口函数

• over(rows between a and b)
  • a：current row（当前行）或者n preceding（向前n行）或者unbounded preceding（从第一行开始）
  • b：current row（当前行）或者n following （向后n行）或者unbounded following（到最后一行结束）

# 这里自己创建了一些数据，1月上半月每日的销售额create table sales (     date date,     sale int      );insert into sales values('2022-01-01',456),('2022-01-02',567)    ,('2022-01-03',678),('2022-01-04',231),('2022-01-05',245)    ,('2022-01-06',273),('2022-01-07',283),('2022-01-08',323)    ,('2022-01-09',333),('2022-01-10',375);select date     , sale     , sum(sale) over(order by date asc rows between 2 preceding and current row) as three_sale_sum # 近三天的销售额之和     , sum(sale) over(order by date asc rows between unbounded preceding and current row) as sale_sum_cum# 截至当前日期的销售额之和 , avg(sale) over(order by date asc rows between 1 preceding and 1 following) as avg__three_sale# 昨天今天明天三天的销售额平均值from sales

我们的MYSQL函数理论知识就到这里了，接下来我会找几道相关题目实战演练。
谢谢大家看完我的第一篇博客，留下个关注再走呗。