MySQL-常用函数大全

MySQL函数大全

MySQL-verison：5.7.17

MySQL常用函数分类

• 数学函数
• 聚合函数
• 字符串函数
• 日期时间函数

数学函数

-- 绝对值
select abs(-123)		=>	123 
-- 平方根
select sqrt(4)			=>	2
-- 取余数
select mod(5,3)			=>	2
-- 向上取整
select ceil(1.111)		=>	2
-- 向下取整
selct floor(1.999)		=>	1
-- 四舍五入
select round(1.345)		=>	1		默认保留整数
select round(1.345,2)	=>	1.35	2为精度
-- 返回参数的符号
select sign(-10)		=>	-1
select sign(10)			=>	1
-- 幂运算
select pow(3,2)			=>	9
-- 随机数
select rand();			=>	0.9057697559760601	(0~1之间)
-- 截取指定小数位数
select truncate(2.783,2)=> 2.78
-- 返回集合中的最大的值
select greatest(10，20，30，40)	=>	40
-- 返回集合中的最小的值
select least(10，20，30，40)		=>	10

聚合函数

 - sum(FIELD)		求和		应用于数值类型/日期	忽略NULL
 - avg(FIELD)		求平均值	应用于数值类型/日期	忽略NULL
 - max(FIELD)		求最大值	应用于数值类型/日期	忽略NULL
 - min(FIELD)		求最小值	应用于数值类型/日期	忽略NULL
 - count(FIELD)		计数		应用于一切类型		忽略NULL
 	count(1)				是由一列
 	count(*)		所有行	有主键，多列
 	count(FIELD) 	排除NULL行

字符串函数

-- 字符串字节长度
select length('a我c')；		=>	5		utf8mb4 三字节汉字
-- 字符串字符长度
select character_length('a我c'); => 3
-- 位置替换
select insert('FootBall',1,4,'Basket'); => BasktBall -- 1和4为我位置，从1开始
-- 内容替换
select replace('123abc123','123','456'); => 456abc456
-- 左截取
select left('henrychen',5);	=>	henry	-- 5表示数量
-- 右截取
select right('henrychen',4)	=>	chen	-- 4表示数量
-- 截取
select mid('henrychen',4);	=>	chen	-- 6表示位置，从1开始，4表示数量
-- 转小写
select lower('ABC');	=>	abc
-- 转大写
select upper('abc');	=> ABC
-- 去两端空格
select trim('    abc    ');	=>	abc
-- 字符串反转
select reverse('abc');	=>	cba

日期时间函数

mysql中符合日期格式的字符串等同于日期

-- 创建日期
select date('2021-1-1');	=>	2021-1-1
-- 获取系统当前时间
select now();				=>	2021-05-13 11:20:21
-- 获取系统当前日期
select current_date();		=>	2021-05-13
-- 获取系统当前时间
select current_time();		=>	11:23:56
-- 获取当前系统时间戳
select unix_timestamp();		=> 1620876322
-- 格式化时间戳
select from_nixtime(1620876322);	=>	2021-05-13 11:25:22
-- 获取年份，返回值范围是1970~2069
select year (now());		=> 2021
-- 季
select quarter(now());		=> 2
-- 月
select month(now());		=> 5
-- 年周
select weekofyear(now());	=>	19
select week(now());
-- 月周
-- 年日
select dayofyear(now());	=>	123
--月日
select day(now());			=>	13
select dayofmonth(now());
--周几
select weekday(date('2021-5-9'));	=>	6	-- 周一~周日 0~6
select dayofweek(date('2021-5-9'));	=>	1	-- 周日~周六	1~7
select dayname(date('2021-5-9'));	=>	Sunday

流程控制

-- if(LOGIC_EXPRESSION,VALUE1,VALUE2)函数
select if(true,1,2);
-- 空值判断
select ifnull(NULL,1);		=>	1
select ifnull(2,1);			=>	2
-- 多分支
	-- switch..case
select (case v when 1 then v1 when 2 then v2 else v3 end) ALIAS;
	-- if...else
selec (case when v<=1 then v1 when v<=10 then v2 else v3 end) A;

MYSQL高阶函数

字符串函数扩展

字符串拼接

-- 行内拼接字符串
concat(F1,...,FN)

-- 行内拼接：指定分隔符拼接
concat_ws(sep,F1,...FN)

-- 分组Pinjie：组内拼接字符串，支持组内排序
group_concat(FIELD [order by FIELD ASC/DESC])	--	默认分隔符为，
group_concat(FIELD separator '_')	-- 指定分隔符

字符符串截取

-- 获取指定位置处的子字符串
substring(String,v1) 	-- 返回从v1开始到结尾的字符串，如果v1为负数，表示从倒数第|v1|个位置开始
substring(String,v1,v2)	-- 返回v1~v2之间的字符串

-- 根据分隔符提取字符串

 - delimiter 分隔符  count 第几个分隔符  count > 0 前几个分隔符  count < 0 后几个分隔符

substring_index(String,delimiter,count)

-- 提取第几个
substring_index(substring_index(String,delimiter,n),delimiter,-1)

字符串查找

-- 定位子字符串在父字符串中的位置
-- 从startPos(incluse)开始在string中找到substr首次出现的位置
- 如果 substr==null || string == null return null
- 如果 substr not in string retrun 0
- 否则返回1-char_length(String)
locate(substr,String[,startPos])

-- 返回substr在String中第一次出现的首字符的位置
instr(String,substr)

字符串函数应用：列转行，行转列
-列转行

列转行：聚合
select
	group_field,...,
	group_concat(FIELD/concat(FIELD,'SEP',...)) alias
where
group by
	group_field
having ...

-行转列

行转列：将一列拆成多行
select FIELD_A from tabA
union all
select FIELD_B from tabA

limit分页查询

-- limit offset,size;
select ... limit(pageNo-1)*pageSize,pageSize;

集合函数

-- 集合包含
find_in_set(FIELD/CONST,'v1,v2,...')

-- 分组：分组收集函数
collect_list(FIELD)	-- 将该列所有行收集成一个集合
collect_set(FIELD)	-- 将该列所有行收集成一个去重后的集合

日期函数扩展

– 以date为基础，增加指定unit(单位)指定num(数量)之后的日期
date_add/adddate(date,interval ±num unit)
select date_sub(current_date(), INTERVAL 1 DAY)
adddate(current_date(), 1)

– 以date为基础，减去指定的时间间隔
date_sub/subdate(date,interval ±num unit)
subdate(current_date(), 1)

– 时间加法运算，在原始时间上添加指定的时间
addtime(‘2018-10-31 23:59:59’,‘0:1:1’)

– 时间减法运算，在原始时间上减去指定的时间
subtime(‘2018-10-31 23:59:59’,‘0:1:1’)

– 获取两个日期之间间隔，返回参数1减去参数2的值
datediff(‘2017-11-30’,‘2017-11-29’)

– 格式化指定的日期，根据参数返回指定格式的值
date_format(date,format)
date_format(‘2017-11-15 21:45:00’,’%W %M %D %Y’)