今天在项目中需要清理某个表的垃圾数据,通过delete from table where field in(子查询)失败,特来研究下删除下in和not in的问题
(1).普通in/not in正确
DELETE FROM member_extend WHERE uid IN ( 4, 5 ) DELETE FROM member_extend WHERE uid NOT IN ( 4, 5 )
(2).子查询in/not中没有包含where所属的表名,正确
DELETE FROM member_extend WHERE uid IN( SELECT id FROM member ) DELETE FROM member_extend WHERE uid NOT IN( SELECT id FROM member )
(3).子查询in中包含where所属的表名,错误:You can't specify target table 'member_extend' for update in FROM clause
DELETE FROM member_extend WHERE uid IN( SELECT uid FROM member_extend ) DELETE FROM member_extend WHERE uid NOT IN( SELECT uid FROM member_extend ) DELETE FROM member_extend WHERE uid NOT IN( SELECT b.uid FROM member a LEFT JOIN member_extend b on a.id=b.uid )
通过上面的(3)实例我们可以看出来,在delete where 子查询中不能直接包含where所属的表名,例如我们要删除的是member_extend表的数据,子查询中也直接出现member_extend表的数据,我们只需要再包装一层,并加上别名即可。
上面(3)实例中的正确代码修正后的方式:
DELETE FROM member_extend WHERE uid IN( SELECT uid FROM (SELECT uid FROM member_extend) a ) DELETE FROM member_extend WHERE uid NOT IN( SELECT uid FROM (SELECT uid FROM member_extend) a ) DELETE FROM member_extend WHERE uid NOT IN( SELECT uid FROM (SELECT b.uid FROM member a LEFT JOIN member_extend b on a.id=b.uid) AS b )
where与having非常类似.都能筛选数据.表达式完全一致. 但是职责的确不同.where负责对表中的字段进行筛选,having负责对where筛选后的结果集再次筛选。这也就是where不能使用别名字段来筛选的原因,因为数据中没有这个字段。&n...
Left join:即左连接,是以左表为基础,根据ON后给出的两表的条件将两表连接起来。结果会将左表所有的查询信息列出,而右表只列出ON后条件与左表满足的部分。左连接全称为左外连接,是外连接的一种。Right join:即右连接,是以右表为基础,根据ON后给出的两表的条件将两表连接起来。结果会将右表...
1.查看所有表,包括视图表,show tables;2.查看表结果,包括视图表,desc 表名3.查看建表过程,show create table 表名;4.查看建视图过程,show create view...
1.很多人认为count查询非常快,但是在加上筛选条件那就是未必的了!测试:user表中4000w数据(1).SELECT count(*) from user; 用时0.00s (2).SELECT...
1.查看歌曲表结构(主要是给name字段添加全文索引)(mysql5.7才支持全中文索引)desc music; +---------+-------------+------+-----+---------+----------------+ | Fie...
在项目中发现大量的form连接表,就开始质疑inner join 和 form a,b的性能问题。找到一份有价值的资料,特别记录:ANSI SQL规范首选INNER JOIN语法。此外,尽管使用WHERE子句定义联结的确比较简单,但是使用明确的联结语法能够确保不会忘记联结条件,有时候这样做也能影响性...