数据库命令

# 插入数据，来源于另一张表的查询

mysql> insert into dealer_copy1 select * from dealer_copy;

 # 查询不包含的数据

SELECT * FROM smallshop WHERE business_scope NOT LIKE '%食品%' AND business_scope NOT LIKE '%酒%' AND business_scope NOT LIKE '%饮料%' AND business_scope NOT LIKE '%乳制品%'
AND business_scope NOT LIKE '%运输%' AND business_scope NOT LIKE '%同城配送%'

 

 # 删除不包含的数据

DELETE FROM dealer WHERE business_scope NOT LIKE '%食品%' AND business_scope NOT LIKE '%酒%' AND business_scope NOT LIKE '%饮料%' AND business_scope NOT LIKE '%乳制品%'
AND business_scope NOT LIKE '%运输%' AND business_scope NOT LIKE '%同城配送%'

 

# 查询包含的数据

SELECT business_scope FROM smallshop WHERE business_scope LIKE '%食品%' OR business_scope  LIKE '%酒%' OR business_scope  LIKE '%饮料%' OR business_scope LIKE '%乳制品%'
OR business_scope  LIKE '%运输%' OR business_scope  LIKE '%同城配送%'

 

# 合并2个表的数据

UPDATE smallshop SET id=id+117412
SELECT COUNT(1) FROM smallshop

INSERT INTO search_system.smallshop
SELECT * FROM smallshop

# 数据去重

SELECT * FROM search_system.logistics WHERE company_name in (SELECT company_name from search_system.smallshop)

#去重

SELECT count(1) from search_system.smallshop GROUP BY company_name

SELECT company_name from search_system.smallshop WHERE company_name LIKE '%亳州市%'

SELECT * from search_system.smallshop WHERE company_name LIKE '%亳州市%' GROUP BY company_name

#去重后插入新表

INSERT INTO search_system.smallshop_copy SELECT * from search_system.smallshop WHERE company_name LIKE '%亳州市%' GROUP BY company_name

 

 

 

# 操作记录

SELECT * FROM search_system.logistics WHERE company_name not in (SELECT company_name from search_system.smallshop)

SELECT company_name from search_system.smallshop GROUP BY company_name

INSERT INTO search_system.smallshop_copy SELECT * from search_system.smallshop WHERE company_name LIKE '%亳州市%' GROUP BY company_name

SELECT * from search_system.smallshop_copy WHERE company_name= '亳州市谯城区如意酒水商行' GROUP BY company_name
SELECT * from search_system.smallshop_copy GROUP BY company_name