文章目录
字符串操作
字符串连接 (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
使用场景:
-
和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
-
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