整理数据需要准备的SQL语句

整理数据需要准备的SQL语句

删除某些字段数据:

UPDATE table set mobile=NULL,phone=NULL,contactor=NULL,legal_person=NULL

删除没有电话的数据:

DELETE FROM alibaba WHERE LENGTH(mobile)<=10 AND LENGTH(phone)<=6

添加字段:

alter table MTO_POSTS add KEYWORDS varchar(50) after STATUS;

根据某个字段,删除单表重复数据:

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)

MYSQL把一张表的数据批量复制到另外一张表:
insert into alibaba_new_one select * from alibaba_new where company_url not LIKE 'https://www.1688.com%'

删除2个表的重复数据

delete from alibaba_new where memberid in (select memberid from alibaba)

新建表:

CREATE TABLE `alibaba_new_one` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_name` varchar(100) DEFAULT NULL,
  `company_url` varchar(150) NOT NULL,
  `memberid` varchar(50) DEFAULT NULL,
  `is_cxt` varchar(20) DEFAULT NULL,
  `contactor` varchar(50) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `mobile` varchar(30) DEFAULT NULL,
  `fax` varchar(30) DEFAULT NULL,
  `address` varchar(150) DEFAULT NULL,
  `product` varchar(255) DEFAULT NULL,
  `business_scope` varchar(255) DEFAULT NULL,
  `business_model` varchar(50) DEFAULT NULL,
  `legal_person` varchar(50) DEFAULT NULL,
  `reg_no` varchar(30) DEFAULT NULL,
  `reg_date` varchar(30) DEFAULT NULL,
  `company_type` varchar(20) DEFAULT NULL,
  `update_on` datetime DEFAULT NULL,
  `remark` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_url` (`company_url`) USING BTREE,
  KEY `index_remark` (`remark`) USING BTREE,
  KEY `index_memberid` (`memberid`) USING BTREE,
  KEY `index_address` (`address`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

把新表按注册日期排序插入新建的表:

insert into alibaba_new_bak(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_new ORDER BY reg_date DESC

把新表按网址类型插入新建的表:

insert into alibaba_new_one(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_new
where company_url not LIKE 'https://www.1688.com%'

注意：本文归作者所有，未经作者允许，不得转载