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

Harvey 3月前 ⋅ 123 阅读

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

删除某些字段数据:

UPDATE table set is_cxt=NULL,mobile=NULL,phone=NULL,contactor=NULL,legal_person=NULL,business_scope=NULL,business_model=NULL,reg_no=NULL,reg_date=NULL,company_type=NULL,update_on=NULL,remark=NULL

添加字段:

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


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

全部评论: 0

    我有话说: