关于 mysql 爬虫自用 数据库常用的一些操作

查询与主表关联不上的数据

select *  from a where  ID not in ( select c.ID from demo c  );

查询重复数据

select count(*) from t_branch where 
ID in (
select ID FROM (
select  min(b.ID) ID, count(1)
from t_branch b 
group by b.COMPANY_BASE_ID,b.BRANCH_OFFICE_NAME ,b.BRANCH_OFFICE_NUMBER
having count(*) > 1)TTT
);

查询主表id 不在详情表

SELECT * FROM t_headlines_video_main WHERE id not in (
SELECT main_id from t_headlines_video_detail 
)

通过字段的长度截取字段

# substring(字段名，起始位置下标，截至位置下标)
UPDATE t_kuai_video set VIDEO_FILE_NAME = substring(VIDEO_FILE_NAME,31 ) 
WHERE DATA_ORIGIN = 1 and VIDEO_FILE_NAME like '%/%'

查询重复数据

select count(*) from t_company_branch where 
ID in (
select ID FROM (
select  min(b.ID) ID, count(1)
from t_company_branch b 
group by b.COMPANY_BASE_ID,b.BRANCH_OFFICE_NAME ,b.BRANCH_OFFICE_NUMBER
having count(*) > 1)TTT
);

删除重复数据

delete from t_company_branch where 
ID not in (
select ID FROM (
select  min(b.ID) ID, count(1)
from t_company_branch b 
group by b.COMPANY_BASE_ID,b.BRANCH_OFFICE_NAME 
having count(*) > 1)TTT
);

replace()字符串替换函数

把数据库表article中的所有title字段里的w3cschool字符串替换成hello。

update `article` set title=replace(title,'w3cschool','hello');

replace函数定义

replace(original-string，search-string，replace-string)

original-string： 被搜索的字符串。可为任意长度。
search-string： 要搜索并被 replace-string 替换的字符串。该字符串的长度不应超过 255 个字节。如果 search-string 是空字符串，则按原样返回原始字符串。
replace-string： 该字符串用于替换 search-string。可为任意长度。如果 replace-string 是空字符串，则删除出现的所有 search-string。

CONCAT(s1,s2…,sn)将s1,s2…,sn连接成字符串

# str1 , str2 为需要拼接的字段
UPDATE demo set detail = CONCAT(str1,str2,...)