likes
comments
collection
share

通俗易懂-MySQL常用函数解析、开窗函数示例

作者站长头像
站长
· 阅读数 38

数学函数

count()

count()count(field) 统计某个结果集中的总数量,field为要统计的字段。

SELECT
	count( 1 ) 
FROM
	db1.users

sum()

sum(field) 统计某个字段累计的总和,field为要统计的字段。

SELECT
	sum( price ) 
FROM
	order_info

avg()

avg(field) 统计某个字段的平均数,field为要统计的字段。

SELECT
	avg( price ) 
FROM
	order_info

round()

round(number,x)返回某个数字按指定位数取整后的数字,number被操作的数字,x为精确的位数

select round(123.4567,2)  # 返回 123.4500

abs()

abs(number) 取绝对值

select abs(-500) # 返回500

rand()

rand() 返回0-1.0之间的浮点随机数

select rand() -- 可能返回 0.566122031

类型转换

cast()

cast(data as type) 将一种类型的数据转换成另一种类型。data为数据, type为类型。必须是数据库支持的数据类型。

select cast('123' as int) # 将字符的123转换成数字的123

查看数据库支持的数据类型

SELECT DATA_TYPE FROM information_schema.COLUMNS GROUP BY DATA_TYPE;

字符串函数

concat()

concat(data1,data2,data3,datan...)拼接字符串。如果其中一个数据为null则返回null

select concat(1,2,3,4) # 返回 1234
select concat(1,2,3,null) # 返回 null

concat_ws()

concat_ws(separator,data1,data2,data3,datan...) 根据指定的分隔符(separator)拼接字符串,存在null则拼接空白。

select concat_ws(',',1,2,3,4) # 返回 1,2,3,4
select concat_ws('-',1,2,3,4) # 返回 1-2-3-4
select concat_ws('-',1,2,3,null) # 返回 1-2-3

group_concat()

group_concat() 根据某个字段分组然后汇总数据。

# 假设有个表(id,name) 数据有5条(1,张三)(2,李四)(3,王五)(4,狗蛋)(5,狗der)
# 获取name字段的汇总
select group_concat(name) from test -- 返回 张三,李四,王五,狗蛋,狗der
# 获取name字段的汇总,根据id进行降序排序
select group_concat(name order by id desc) from test -- 返回 狗der,狗蛋,王五,李四,张三

date_format()

date_format(dateStr,pattern)格式化时间函数。dataStr为要格式化的时间,pattern为格式。

SELECT DATE_FORMAT('2011-09-20 08:30:45',   '%Y-%m-%d');  # 返回 2011-09-20

可以使用的格式有:

格式描述
%a缩写星期名
%b缩写月名
%c月,数值
%D带有英文前缀的月中的天
%d月的天,数值(00-31)
%e月的天,数值(0-31)
%f微秒
%H小时 (00-23)
%h小时 (01-12)
%I小时 (01-12)
%i分钟,数值(00-59)
%j年的天 (001-366)
%k小时 (0-23)
%l小时 (1-12)
%M月名
%m月,数值(00-12)
%pAM 或 PM
%r时间,12-小时(hh:mm:ss AM 或 PM)
%S秒(00-59)
%s秒(00-59)
%T时间, 24-小时 (hh:mm:ss)
%U周 (00-53) 星期日是一周的第一天
%u周 (00-53) 星期一是一周的第一天
%V周 (01-53) 星期日是一周的第一天,与 %X 使用
%v周 (01-53) 星期一是一周的第一天,与 %x 使用
%W星期名
%w周的天 (0=星期日, 6=星期六)
%X年,其中的星期日是周的第一天,4 位,与 %V 使用
%x年,其中的星期一是周的第一天,4 位,与 %v 使用
%Y年,4 位
%y年,2 位

length()

length()返回字符串的长度。

select length('123') # 返回3 

replace()

replace()替换指定字符串中的指定字符串为指定字符串

select replace('1234abcd','1234','aaaaa-') # 返回 aaaaa-abcd

find_in_set()

find_in_set() 查找指定的字符串在不在字符串列表中

select find_in_set('狗蛋','狗蛋,狗der,大宝贝') -- 返回1,因为找到了
select find_in_set('狗蛋1','狗蛋,狗der,大宝贝') -- 返回0 没找到

locate()

locate() 返回字符串首次出现的位置,下标从1开始

select locate('2','123') -- 返回2
select locate('1','123') -- 返回1

日期函数

curdate()

curdate() 返回今天的日期

select CURDATE() -- 返回 2022-11-08

curtime()

curtime() 返回今天的时间

select curtime() -- 返回 11:28:07

now()

now() 返回日期和时间

select now() -- 返回2022-11-08 11:28:25

逻辑函数

if()

if() 判断表达式是否成立,格式if(表达式,成立返回,不成立返回),注意成立和不成立的返回必须是一个类型的,要是字符串必须都是字符串,不能一个是字符串一个是数字。

if(1=1,'成立','不成立') -- 返回 成立
if(1 is not null,'成立','不成立') -- 返回 成立
if(1=2,'成立','不成立') -- 返回 不成立
if(1 is not null and 1=1,'成立','不成立') -- 返回 成立
if(1 is not null and 1=2,'成立','不成立') -- 返回 不成立

case

case 多表达式判断,返回最先成立表达式的结果,格式CASE WHEN 表达式1 THEN 表达式1成立的结果 WHEN 表达式2 THEN 表达式2成立的结果 ELSE 都不成立的结果 END

select case when 1=1 then '1=1' when 2=2 then '2=2' else '都不成立' end -- 返回 1=1
select case when 1=2 then '1=1' when 2=2 then '2=2' else '都不成立' end -- 返回 2=2
select case when 1=2 then '1=1' when 2=3 then '2=2' else '都不成立' end -- 返回 都不成立

ifnull()

ifnull() 如果传入的值为null,就返回指定的值,不为null,就返回传入的值

select ifnull(1,'111') -- 返回 1
select ifnull(null,'111') -- 返回 111

开窗函数

MySQL 8.0之后才支持!

示例数据

idnamebirthdayweight
1大宝贝2002-12-1275KG
2狗蛋2001-10-0950KG
3狗der2002-10-0950KG

row_number() 根据某个字段分组,根据根据某个字段排序,给出序号

示例1 不分组,单纯获取序号,根据birthday降序

select name ,row_number() over (order by birthday desc) as rank_sort from test_table

返回结果

namebirthdayrank_sort
大宝贝2002-12-121
狗der2002-10-092
狗蛋2001-10-093

示例2 根据体重weight分组,根据birthday降序

select name, weight ,row_number() over (partition by weight order by birthday desc) as rank_sort from test_table

返回结果

nameweightrank_sort备注(这个是自己加上去,写备注的)
大宝贝75KG1因为75KG只有一条数据,所以rank_sort就是1
狗der50KG1因为50KG只有两条数据,狗der的birthday第一大,所以rank_sort就是1
狗蛋50KG2因为50KG只有两条数据,狗蛋的birthday第二大,所以rank_sort就是2