mysql内置函数

字符串操作

字符串连接 （CONCAT）

语法：

CONCAT(s1,s2...sn)	

经典使用方式之一在mybatis 中进行模糊匹配。

select CONCAT('%','#{name}','%');

注意：返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。

select concat(u.id,u.email) as info from user u;

使用分隔符拼接字符串 CONCAT_WS

语法：

CONCAT_WS(seperator, s1,s2...sn)
-- WS的缩写大概是指  with seperator

第一个参数是 分隔符

select CONCAT_WS('%','1','2','3'); --输出结果  1%2%3

select CONCAT_WS('%',id,name,email) from user;

可以看到使用CONAT_WS 进行拼接 即便数据库字段为null，也不会想函数concat一样直接返回 null

分组，字符串连接 group_concat

分组后某个字段拼接，group_concat 还支持去重（distinct）。

SELECT
	GROUP_CONCAT(r.role_name) roleNames,
	GROUP_CONCAT(r.role_id) roleIds,
	r.user_id
FROM
	user_role r
GROUP BY
	user_id

默认使用 ,做分隔符，还可以添加前缀和后缀。

SELECT
	GROUP_CONCAT(r.role_name) roleNames,
	GROUP_CONCAT('_id',r.role_id) roleIds,
	r.user_id
FROM
	user_role r
GROUP BY
	user_id;

字符串截取（SUBSTRING ）、 MID(s,n,len)

mysql 提供了如下的字符截取函数。其中 substr 是 substring的缩写

SUBSTRING 函数需要三个参数，第一个要操作的字符串，第二个参数 开始截取的字符串索引（从 1开始），第三个参数是要截取的长度。

MID 函数和SUBSTRING 功能和参数一致。

select SUBSTRING('123456789',1,6);;  -- 输出结果：123456
select SUBSTR('123456789',1,6);;  -- 输出结果：123456
select SUBSTR('123456789' FROM 2 FOR 2);-- 输出结果：23

select SUBSTRING('123456789',0,6); -- 输出结果 空

所以需要注意的是 字符串截取索引要从 1 开始。

字符串转小写 LCASE(s) LOWER(s)

SELECT LCASE('Hello'); -- hello
SELECT LOWER('World');  -- world

字符串转小写

SELECT UPPER('qwe'); -- QWE
SELECT UCASE('qwe'); ---QWE

字符串去空格

去前后空格

SELECT TRIM('  123   ');  -- 输出结果  123

去前空格

SELECT LTRIM('  123   "');  -- 输出结果  123  "

去后空格

SELECT RTRIM('"  123  '); -- 输出结果  "  123 

字符串查找 FIND_IN_SET

语法：

FIND_IN_SET(str1,str2)

str2：是一个字符串集合，每个元素使用,分隔。
函数的作用是返回在集合str2中和str1匹配元素的位置。

SELECT FIND_IN_SET('3','1,2,3,4,5')
-- 返回结果 3

SELECT FIND_IN_SET('7','1,2,3,4,5')
-- 返回结果 0

SELECT FIND_IN_SET('3','12,3');
-- 返回结果 2



SELECT FIND_IN_SET(null,'12');
-- 返回 null

SELECT FIND_IN_SET('12',null);
-- 返回 null

可以看到该函数并不是单纯的字符串匹配，而是将第二个字符串按照,拆分并匹配值。

与下面程序大致相同。

 public Integer findInSet(String str1,String str2){
	if(str1==null || str2==null)
		return null;
	 String[] str2Array = str2.split(",");
	 for(int i=0;i<str2Array.length;i++){
	     if(str1.equals(str2Array[i]))
	         return i+1;
	 }
	 return 0;
 }

看到split 方法，就想到了 分隔连续的问题。

SELECT FIND_IN_SET('1','0,,1,2');
-- 返回 3

字符串定位 LOCATE

语法：

LOCATE(s1,s)

获取 字符s1 在字符串s 中的起始位置。匹配不到则返回0

select LOCATE('3','1,2,3');
-- 返回结果 5
select LOCATE('0','1,2,3');
-- 返回结果 0

上面就可以明显看到， locate方法 返回的结果是在字符串中的位置

数字格式化 FORMAT(x,n)

FORMAT(x,n)	

函数可以将数字 x 进行千分位格式化（ "#,###,###"）, 并保留 n 位小数，最后一位四舍五入。

select FORMAT(1234.123,2); 
--输出结果  1,234.12

数字函数

求和

AVG(expression)	
-- 返回一个表达式的平均值，expression 是一个字段	

对某个学生成绩求和

select sum(grade),stu_id from stu_grade group by stu_id;

求平均

SUM(expression)
-- 返回一个表达式的平均值，expression 是一个字段	

求某个学生的平均成绩

select sum(grade)/count(DISTINCT(`subject`)),stu_id from stu_grade group by stu_id;

最值

最大值：

MAX(expression)	
-- 返回字段 expression 中的最大值	

最小值：

MIN(expression)	
-- 返回字段 expression 中的最小值	

取整

向上取整

CEIL(x)	

select ceil(1.24); -- 2
select ceil(2.64); -- 3

向下取整

FLOOR(x)

select FLOOR(1.24); -- 1
select FLOOR(2.64); -- 2

高级函数

类型转化 CAST

oracle 也支持该函数

CAST(x AS type)	

SELECT CAST('12.5' AS decimal) -- 13

SELECT CAST('12.5' AS decimal(12,2)) -- 12.5

不知道为什么，字符串转数字时，只有指定类型为 才能正常执行。（mysql - 5.7.21）

if 相关

if

使用场景：

1. 和count连用
   分别统计 用户1 和用户2 的角色总数

   	SELECT
   		count(*) as total,
   		count(IF(user_id =1, 1,null)) role1,
   		count(IF(user_id = 2,1, null)) role2
   	FROM
   		user_role
   

2. sum连用

   SELECT
   	count(*) as total,
   	SUM(IF(user_id =1, 1,null)) role1,
   	SUM(IF(user_id = 2,1, null)) role2
   FROM
   	user_role
   

因为没有使用groupby 语句，上面两个的输出结果都是一样的。

total | role1 | role2
	3 |	1	  | 2

IFNULL

IFNULL(expr1,expr2)

如果expr1表达式结果不为null，则返回结果为 expr1.否则结果为 expr2，返回结果是数字或字符串。

可以用于数据统计时定义默认值，如果统计结果是null，则返回0。

select IFNULL(SUM(null),0) sum;
-- 0

IF ELSE

if实现条件判断，满足不同条件执行不同的操作，这个我们只要学编程的都知道if的作用了，下面我们来看看mysql 存储过程中的if是如何使用的吧。

IF search_condition THEN 
    statement_list  
[ELSEIF search_condition THEN]  
    statement_list ...  
[ELSE 
    statement_list]  
END IF