Mysql删除多表及多表记录sql语句
批量删除多表
1、删除所有pre_前缀的表
代码如下:SELECT CONCAT( 'drop table ',table_name,'; ') FROM information_schema.tables where
information_schema.tables.TABLE_NAME LIKE 'pre_%' ;
2、删除所有pre_前缀的表 并且 不删除pre_uc前缀的表SELECT CONCAT( 'drop table ',table_name,'; ') FROM information_schema.tables WHERE
information_schema.tables.TABLE_NAME LIKE 'pre_%' AND information_schema.tables.TABLE_NAME NOT LIKE
'pre_uc%';将得到的结果复制下来,再重新执行DELETE t1 FROM t1 LEFT JOIN T2 ON t1.id=t2.id WHERE t2.id IS NULL 或
DELETE FROM t1,USING t1 LEFT JOIN T2 ON t1.id=t2.id WHERE t2.id IS NULL
3、从两个表中找出相同记录的数据并把两个表中的数据都删除掉
DELETE t1,t2 from t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t1.id=25
(注意此处的delete t1,t2 from 中的t1,t2不能是别名)如:
delete t1,t2 from table_name as t1 left join table2_name as t2 on t1.id=t2.id where table_name.id=25
在数据里面执行是错误的(MYSQL 版本不小于5.0在5.0中是可以的)上述语句改写成
delete table_name,table2_name from table_name as t1 left join table2_name as t2 on t1.id=t2.id where table_name.id=25
删除表中多余的重复记录,只留有rowid最小的记录(单字段)
Delete From 表
Where 字段1 In (Select 字段1 From 表 Group By 字段1 Having Count(字段1) > 1) And
Rowid Not In (Select Min(Rowid) From 表 Group By 字段1 Having Count(字段1) > 1)
删除表中多余的重复记录,只留有rowid最小的记录(多个字段)
Delete From 表 a
Where (a.字段1, a.字段2) In (Select 字段1, 字段2 From 表 Group By 字段1, 字段2 Having Count(*) > 1) And
Rowid Not In (Select Min(Rowid) From 表 Group By 字段1, 字段2 Having Count(*) > 1)
删除多于的重复记录(单个字段,多个字段)
delete from table where id not in ( select min(id) from table group by name)
或者
delete from table where id not in ( select min(id) from table group by 字段1,字段2)
6.删除多余的重复记录(单个字段,多个字段)
delete from table where id in ( select max(id) from table group by name having count(*)>1)