mysql多表操作随手记

UPDATE bd a 
LEFT JOIN
(SELECT bd.id,img.img_url from bd bd 
inner join bd_imgs img on bd.id%10 = img.sign 
where bd.bd_type=1 and (bd.img_url='' or bd.img_url is NULL) and img.bd_type=1
order by bd.id desc) as b on a.id=b.id
set a.img_url=b.img_url;

-- 添加索引
-- ALTER TABLE `aa` ADD INDEX areaid (`area_id`);

-- 查询索引
show index from aa;
-- 删除索引
-- DROP INDEX rds_idx_0 ON `aa`;

***********************************************************************
-- 查询 area_id=0
SELECT count(*) from fcxlt_bd where area_id=0;

-- 联合查询 area_id=0
SELECT count(*) from aa a
inner join bb b on a.id=b.id
inner JOIN cc c on a.id=c.id
inner JOIN dd d on a.id=d.id
where area_id=0;


-- 删除area_id=0

DELETE a,b,c,d from aa a
inner join bb b on a.id=b.id
inner JOIN cc c on a.id=c.id
inner JOIN dd d on a.id=d.id
where area_id=0;

--删除area_id=0 
delete a from fcxlt_bd a where a.area_id=0

***********************************************************************
-- 删除2018-10-1之前的

DELETE a,b,c,d from aa a
inner join bb b on a.id=b.id
inner JOIN cc c on a.id=c.id
inner JOIN dd d on a.id=d.id
where a.date < '2018-10-01';

***********************************************************************