MySQL 常用函数/使用技巧
一、常用函数
1. if
A. 语法:IF(expr1, expr2, expr3) ——>如果表达式expr1成立(真),返回结果expr2,否则返回结果expr3;
2. ifnull
A. 语法:IFNULL(expr1, expr2) ——>如果expr1的值不为NULL,则返回expr1,否则返回expr2;
3. case when
A. 简单函数:CASE [col_name] WHEN [value1] THEN [result1] … ELSE [default] END,应用场景:枚举这个字段所有可能的值
B. 搜索函数:CASE WHEN [expr] THEN [result1] … ELSE [default] END,应用场景:
C. 举例:
/** * 查看场所信息状态 * @return */ @Query(value = "select case l.status when 1 then '审核中' when 2 then '审核不通过' when 3 then '审核通过' when 4 then '审核同步中' end as status, t2.op_obj, l.id from operate_log l, (select op_obj, max(operate_log_id) as id from operate_log_detail where op_obj in :ids group by op_obj) t2 where l.id = t2.id", nativeQuery = true) List<Object[]> findAllOperateInfo(@Param("ids") List<Long> ids);
4. substring_index
A. 语法:substring_index (str, delimiter, number) ——> 返回从字符串str的第number个出现的分隔符delimiter之后的子串;
5. concat
A. 语法:concat(str1, str2, ...) ——> 将多个字符串连接成一个字符串;
B. 注意:如果有任何一个参数为null,则返回值为null;
C. 举例:在一个搜索框中模糊匹配name和remark两个字段数据
select category, id from app_web_info where concat(`name`, remark) like concat('%', #{keyword}, '%')
6. group_concat
A. 语法:group_concat([distinct] 要连接的字段 [order by 排序字段 asc/desc] [separator '分割符']) ——> distinct可以排除重复值,order by对结果的值进行排序,separator是一个字符串值,缺省为一个逗号,作用是将group by产生的同一个分组中的值连接起来,返回一个字符串结果;
B. 举例:
select app_type as appType, app_id as appId, count(distinct(user_id)) as activeUserNum, count(*) as accessNum, group_concat(distinct user_id) as userIds from access_log group by app_type, app_id
5和6的实例可参考:https://www.cnblogs.com/chLxq/p/11001915.html
7. max
A. 语法:max(expr):返回字段expr中最大值,是针对数字类型,若是字符串类型,请使用max(expr + 0),因为字符串类型大小是先比较首字符,然后依次往后比较;
B. 举例:value字段是text类型
SELECT max(CASE WHEN hpr.`name` = 'hostGroupId' THEN hp.VALUE END) AS hostGroupId FROM host_pro hp LEFT JOIN host_pro_ref hpr
ON hpr.id = hp.property_ref where hp.host_id = '1_1.12.109.17'; // 值是5
SELECT max(CASE WHEN hpr.`name` = 'hostGroupId' THEN hp.VALUE + 0 END) AS hostGroupId FROM host_pro hp LEFT JOIN host_pro_ref hpr
ON hpr.id = hp.property_ref where hp.host_id = '1_1.12.109.17'; // 值是48
8. date_format
A. 语法:date_format(date, format) ——> 用于以不同的格式显示日期时间数据
B. format格式:可参考https://www.w3school.com.cn/sql/func_date_format.asp
C. 举例:
select date_format(created_time, '%Y%m') as accessYearMonth, date_format(created_time, '%e') as accessDay date_format(created_time, '%k') as accessHour,
date_format(created_time, '%Y-%m-%d %H') as `time` from access_log group by `time` order by `time`
9. find_in_set
A. 语法:find_in_set(str, strlist),str代表要查询的字符串,strlist需要查询的字段,参数以逗号分割,用来查询strlist字段值是否包含str的结果
B. 举例:select * from org where FIND_IN_SET('1',replace(ancestors, '/', ',')) > 0
9. instr
A. 语法:instr(str, substr),查找一个字符串中是否包含另一个指定的子串。
可参考:MySQL 函数大全
https://blog.csdn.net/qq_30038111/article/details/79611167
二、使用技巧
1. 一个搜索框的值模糊匹配上多个字段
A. select * from 表名 where concat(ifnull('字段1', ''), ifnull( '字段2', ''), ...) like concat('%', 关键字, '%');
B. ifnull函数的作用是为了解决有任何一个字段参数为null,则该行返回值为null的情况。
2. 查询数据行转列
A. 利用max(case ... when ... then ... else ... end) as ...来实现,注意max函数若查询最大值需要给字段加0;
B. 转换前
select h.id, hp.property_ref, hp.value, hpr.id, hpr.name from host h left join host_properties hp on hp.host_id = h.id left join host_property_reference hpr on hpr.id = hp.property_ref where h.id = '1_127.0.0.0'
C. 转换后
select h.id, max(case hpr.name when 'hostName' then hp.value end) as hostName from host h left join host_properties hp on hp.host_id = h.id left join host_property_reference hpr on hpr.id = hp.property_ref where h.id = '1_127.0.0.0';
3. 运算符优先级
A. and和or联合使用带来的问题,需要用括号解决;
B. 举例:select * from user where a = 1 and b = 2 or c = 3 区别与 select * from user where a = 1 and (b = 2 or c = 3);
C. 运算符优先级。
4. in关键字查询匹配多个字段:select * from user where (id, name) in ((1, '张三'), (2, '李四'));
5. 将一张表的数据插入另外一张表
A. 如果两张表的字段一致:insert into table1 select * from table2;
B. 如果只希望导入指定字段:insert into table1 (name1, age1) select name2, age2 from table2。
6. SQL中使用 != 不等于会过滤掉为null的数据,可以使用ifnull(column, '') != 1解决。