删除重复行

 user_info：

 

-- 单字段筛选重复行

SELECT *from user_info WHERE NAME in ( SELECT NAME from user_info GROUP BY NAME HAVING COUNT(NAME)>1);

 

 -- 删除重复行，保留id最小的数据

SELECT *from user_info WHERE 

NAME in ( SELECT NAME from user_info GROUP BY NAME HAVING COUNT(NAME)>1)

AND id NOT in ( SELECT min(id) from user_info GROUP BY NAME HAVING COUNT(NAME)>1);

 -- 多字段判断重复

SELECT * from user_info WHERE (name,school) in ( SELECT name,school from user_info group by name,school HAVING COUNT(*)>1);

 

 

删除字段首位末位：

user_info 

 

删除字段左边3位

update user_info set NAME=RIGHT(NAME,LENGTH(NAME)-3) where `name` like 'chu%';

删除字段右边一位

update user_info set NAME=LEFT(NAME,LENGTH(NAME)-1) where `name` ='zeng';