常用mysql语句

本文仅仅记录，日常开发中常用sql。

GROUP_CONCAT 函数

期望

select product_attribute.name as name, GROUP_CONCAT(product_attribute_value.value) as value
from product_attribute,
     product_attribute_value
where product_attribute.id = product_attribute_value.product_attribute_id
  and product_attribute.product_id = '1414481474832461824'
group by product_attribute.name;

要点：inner join 以及 GROUP_CONCAT函数使用

找最大值

# 案例 sub_rule生成规则是：sub前缀+六位数字（取最大+1），例子：sub000011 下一次生成是sub000012
SELECT max(RIGHT(sub_rule,CHAR_LENGTH(sub_rule) - 3)) +0 FROM user;

字符串操作函数

## 字符串拼接相关
# concat函数。注意：任意连接的字符串为NULL
select concat(id,name) from user where id ='14249292325131';
# 入参，分隔符，以及多个变量。注意：分隔符为null时，则返回的结果也为NULL
select concat_ws('_',id,name,email) from user where id ='';
# 全参数是这样。默认要是不分组，所有的结果就是一组。然后就行拼接。
# GROUP_CONCAT( [DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]] [SEPARATOR str_val] )
select id,group_concat(name,id SEPARATOR '**' ) from user group by name;


## 字符串截取
# SUBStr(SUbString|Mid) 四种入参方式.三种函数目的一样,真不知道，为啥冗余那么多
# 和 SUBSTR(sub_rule FROM 4) 一样
select SUBSTR(sub_rule,4) from user where id=2;
# 和subStr(sub_rule FROM 2 FOR 3)
select subStr(sub_rule,2,3) from user where id=3;
# [4,字符串最大] 例子 sub000012 结果：000012
select SUBSTR(sub_rule FROM 4) from user where id=2;
# 从第几位开始，要几个 例子 sub000012 结果：ub0
select subStr(sub_rule FROM 2 FOR 3) from user where id=3;
# 从右边开始right(字段，要几位)
select right(sub_rule,4) from user where id=2;
select mid(sub_rule,4) from user where id=2;


# 计算字符串长度
select char_length(sub_rule) from user where id=2;
select length(sub_rule) from user where id=2;

# 替换
# 参数：source,从几个开始pos，替换几个len，newStr。1-若pos不在字符串长度范围内，返回原字符串，2-若len不在范围内，则从pos开始替换，替换内容为newStr，3-若任意参数为null，返null
select insert(sub_rule,333,2,'wwwwwwwww')from user where id=3;

# 字符串大小写转换
select lower(sub_rule)from user where id=3;

# 删除空格 trim,Ltrim,Rtrim
select trim(sub_rule)from user where id=3;
# 去除指定字符
# 全参 [Both|Leading|trailing] [remStr] From var 。例子：
select trim(BOTH 'x' from sub_rule)from user where id=3;

select charset(sub_rule) from user where id=2;

统计各个状态值

  SELECT sum(case when meter_category = 0 then 1 else 0 end) AS masterNum,
                sum(case when meter_category = 1 then 1 else 0 end) AS salveNum
                from table_name