1、字符串替换 例:将含有空格的替换成“-”
update info_de
set name = REPLACE(name, ' ', '-')
where name like('%aaa %')
2、大小写转换
1.将大写字母改为小写字母
update 表名 set 字段名a= Lower(字段a)
2.将小写字母转化成大写字母
update 表名 set 字段名a= upper(字段名a)
3、删除重复行(保留其中一条—id最小一条)
DELETE FROM map_info a
WHERE
(a.license_value) IN (
SELECT license_value FROM map_info GROUP BY license_value
HAVING
count(*) > 1
)
AND id NOT IN (
SELECT min(id) FROM map_info GROUP BY license_value HAVING count(*) > 1
)
4、是否为空:
is null (正确)
=null (错误)
5、mybatis面向注解开发 like 模糊查询
project_name like CONCAT('%',#{proName},'%')
6、查询重复的数据--group、having
select * from license_base_info_origin
where lj_license_key in
(select lj_license_key from license_base_info_origin GROUP BY(lj_license_key) HAVING count(lj_license_key) >1)