mysql字段拼接、查询重复数据、删除重复数据、还原字段默认值
1.查询条件判断:
case when 条件 then 结果1(条件成立) else 结果2
2.查询分组排序:
row_number() over(),执行晚于 where 、group by
row_number() over(partition by 分组列 order by 排序列 desc)
group by 和 order by 同时使用:
order by 必须包含在 group by 中
3.字段拼接:name前面拼接一个1
update student set name = CONCAT('1',name)
4.还原字段默认值
update member set member_nick_name = DEFAULT(member_nick_name), member_true_name = DEFAULT("张三") where id = 1;
5.查询表中重复数据:
SELECT
name
FROM
student
WHERE
age = 15
GROUP BY
name
HAVING
count(name)> 1;
6.删除表中重复数据:
DELETE
FROM
student
WHERE
id NOT IN ( SELECT id FROM ( SELECT min( id ) FROM student WHERE name = '张三' GROUP BY age ) A );
7.多个单条件查询
choose-when-ortherwise,相当于switch-case-default
<select id="selByCondition2" resultMap="rm">
select *
from mybatis where
<choose>
<when test="status !=null">
STATUS=#{STATUS}
</when>
<when test="companyName !=null and companyName !=''">
company_name like #{companyName}
</when>
<when test="bracdName !=null and bracdName !=''">
bracd_name like #{bracdName}
</when>
<otherwise>1=1</otherwise>
</choose>
</select