Mysql数据库表常用SQL语句

Outshine 6月前 ⋅ 527 阅读

最近一段时间经常会处理一些数据库表格,下面是一些常用的数据库处理语句 表更新 update alibaba inner join alibaba_tmp on alibaba.memberid=alibaba_tmp.memberid set alibaba.is_cxt=alibaba_tmp.is_cxt;

update alibaba inner join alibaba_tmp on alibaba.memberid=alibaba_tmp.memberid set alibaba.is_cxt=alibaba_tmp.is_cxt;

update alibaba inner join alibaba_tmp on alibaba.memberid=alibaba_tmp.memberid set alibaba.company_url=alibaba_tmp.company_url;

update alibaba_two inner join alibaba_tmp on alibaba_two.memberid=alibaba_tmp.memberid set alibaba_two.is_cxt=alibaba_tmp.is_cxt;

update alibaba_two inner join alibaba_tmp on alibaba_two.memberid=alibaba_tmp.memberid set alibaba_two.company_url=alibaba_tmp.company_url;

临时多表去重复 delete from alibaba_tmp where memberid in (select memberid from alibaba) OR memberid in (select memberid from alibaba_two) OR memberid in (select memberid from alibaba_nophone)

临时表去重复 DELETE FROM alibaba_tmp WHERE memberid IN (SELECT * FROM (SELECT memberid FROM alibaba_tmp GROUP BY memberid HAVING COUNT(memberid) > 1) t1) AND id NOT IN (SELECT * FROM (SELECT MIN(id) FROM alibaba_tmp GROUP BY memberid HAVING COUNT(memberid) > 1) t2)

删除无效数据 insert into alibaba_delete(company_name,company_url,memberid,is_cxt,contactor,phone,mobile,fax,address,product,business_scope,business_model,legal_person,reg_no,reg_date,company_type,update_on,remark) SELECT company_name,company_url,memberid,is_cxt,contactor,phone,mobile,fax,address,product,business_scope,business_model,legal_person,reg_no,reg_date,company_type,update_on,remark FROM alibaba where remark='nophone' OR remark='delete';

DELETE FROM alibaba WHERE remark='nophone' OR remark='delete'

新表去重复 DELETE FROM alibaba_new WHERE memberid IN (SELECT * FROM (SELECT memberid FROM alibaba_new GROUP BY memberid HAVING COUNT(memberid) > 1) t1) AND id NOT IN (SELECT * FROM (SELECT MIN(id) FROM alibaba_new GROUP BY memberid HAVING COUNT(memberid) > 1) t2)