【常用SQL语句】MySQL删除和替换语句。

 #统计重复链接个数 当count=1统计非重复的个数
select link,count(*) as count from tableName group by link having count>1;

Select * From 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1);

#根据link判断 列出重复的记录
SELECT id,name FROM tableName a WHERE ((SELECT COUNT(*) FROM tableName WHERE link = a.link) > 1) ORDER BY link DESC

#根据link和name判断 列出重复的记录
SELECT id,name,link FROM tableName a WHERE ((SELECT COUNT(*) FROM tableName WHERE name=a.name and link = a.link) > 1) ORDER BY link DESC

#删除重复链接 保留id最大的
DELETE FROM tableName WHERE id NOT IN ( SELECT dt.minno FROM (
  SELECT MAX(id) AS minno FROM tableName GROUP BY link ) dt );
  
#根据link和name判断 删除重复的记录保留id最大的记录 [比其他语句快多了]
DELETE  FROM  tableName  WHERE id NOT IN ( SELECT  dt.maxon 
FROM  ( SELECT MAX(id) AS maxon FROM tableName GROUP BY name, link) dt)
  
#替换指定字符 
UPDATE tableName SET name=replace(name, '被代替字符', '代替字符')

#删除不包含 【xxx】 的记录 可加模糊查询%xxx%
delete FROM tableName where link NOT LIKE ''xxx"

#删除包含 【xxx】 的记录 可加模糊查询%xxx%
delete FROM tableName where tableName_link  LIKE 'xxx'

#根据link和name判断 删除重复的记录保留id最大的记录 [比其他语句快多了]
DELETE  FROM  tableName  WHERE id NOT IN ( SELECT  dt.maxon 
FROM  ( SELECT MAX(id) AS maxon FROM tableName GROUP BY name, link) dt)