一条多表联合删除sql的优化

2019独角兽企业重金招聘Python工程师标准>>>

先创建两个表,下面是sql

--
-- 表的结构 `t1`
--
 
CREATE TABLE IF NOT EXISTS `t1` (
  `uid` int(11) NOT NULL,
  `name` varchar(5) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
 
--
-- 转存表中的数据 `t1`
--
 
INSERT INTO `t1` (`uid`, `name`) VALUES
(1, 'a'),
(2, 'b'),
(3, 'c'),
(7, 'd'),
(8, 'e');
 
-- --------------------------------------------------------
 
--
-- 表的结构 `t2`
--
 
CREATE TABLE IF NOT EXISTS `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `name` varchar(5) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=10 ;
 
--
-- 转存表中的数据 `t2`
--
 
INSERT INTO `t2` (`uid`, `name`) VALUES
(1, 'a'),
(2, 'b'),
(3, 'c'),
(4, 'd'),
(5, 'e'),
(6, 'f'),
(7, 'g');

生成表的内容是:

t1:                            t2:

 

 要求: 删除t2表里面一些错误数据：即  t2的uid不在t1中。 或者 uid 与t1 相同，名字不同的数据。如下解释:
         1:  t2中uid为 4,5,6的数据,uid在t1中不存在,需要删除;  
         2:  t2中uid为7,虽然t1中存在这条uid,但是t2表的name和t1不一样,所以也需要删除

 

方法①: 就按字面意思,你不是要删除这两种情况的数据吗?那我就先把他们找出来,然后删除,于是第一条sql就出来了

delete from t2 where t2.id in (  
    select id from (  
        SELECT distinct t2.id FROM `t2`,t1 
            where (t2.name!=t1.name and t2.uid=t1.uid) or t2.uid not in (select uid from t1 )  
    )  as tmp    -- 先把符合两种情况的数据放到一个临时表里,然后把t2中id出现在临时表里的数据删除
)

 

方法②: 和①类似,只不过转换了一下思路, 即找出t2中符合规范的数据, 组成一个临时表tmp,
            然后删除表t2中没有出现在临时表tmp中的数据

delete from t2 where uid not in (
    select uid from (
        select t1.uid from t1,t2 where t1.uid=t2.uid and t1.name = t2.name
    ) as tmp  -- 将符合规范的数据放到临时表里,然后删除t2中uid没有在tmp中出现的数据
)

 

方法③: 上面都是多表联合查询后,生成临时表, 再把符合条件的删除, 下面是 跳过临时表, 直接多表联合删除
            即删除 t2 中符合 from后面条件的记录

delete t2 from t1, t2 
    where 
( t2.uid = t1.uid and t2.name != t1.name ) 
    or 
t2.uid not in (select uid from t1)

方法④: 和方法③是同一个方向,只不过换了中语法,用 join

delete t2 from t2 left join t1 on t1.uid=t2.uid and t1.name=t2.name where t1.uid is null

自我理解: 猛一看会觉得方法③和④难以理解,我也是琢磨的好久才算
              其实我感觉方法④和⑤就是把 from 后面的语句当成一个临时表,然后删除t2表中符合临时表中条件的数据,
              如果感到方法④和⑤难以理解的话,就把这两条sql从from 开始截取出来,前面加上 select *,查询一下,你就明白了

例如方法④截取出来是

select * from t2 left join t1 on t1.uid=t2.uid and t1.name=t2.name where t1.uid is null

查询结果是:  
然后把这些结果从t2中删除.....

 

方法⑤: 和②类似,换了一种语法, sql语句中的 in 可以用于两个字段的判断,所以方法②的语句可以简化为下面这条
            感觉这种最简单, 以前用in的时候,前面一般是一个字段, 原来可以跟两个字段
            (注意:两个字段用in的时候,一定要用括号()包起来)

delete from t2 where (uid,name) not in (select uid,name from t1)

 

 追加两种方法: 因为一开始就想到用关键字 in,忽略了下面两种关键字的使用,看来我的sql功力还有待加强

方法⑥: exists的使用

delete from t2 where not exists (select * from t1 where t1.uid=t2.uid and t1.name=t2.name)

方法⑦: all 的使用

delete from t2 where uid !=all(select uid from t1 where t1.uid=t2.uid and t1.name=t2.name )

 

 

 

转载于:https://my.oschina.net/wujunwei/blog/77587