Mysql中的Delete操作

MySQL 中的Delete 操作

1 Delete操作初识

delete操作一般用于删除数据表中的某一行，常见的语法如下：

delete from 数据表名称 where 字段名称=特定值

如果我们不在这条语句后面添加where筛选条件，则视为删除数据表的所有行，这里我们只对这种简单的使用方式加以回顾，并不举例说明。

2 Delete高级操作

如果在特定的场景中，需要使用sql语句删除重复的行，那我们应该如何操作呢。这里给出一个具体的例子，例如Leetcode 196 删除重复的邮箱中需要我们使用delete命令删除重复的电子邮箱。

2.1 自联结方式

首先，需要使用自连接语句筛选出重复的电子邮箱id。

select p1.* from person as p1 inner join person as p2 
on p1.email = p2.email and p1.id > p2.id;

此时，我们将重复的电子邮箱查询出来。

+----+------------------+
| Id | Email            |
+----+------------------+
|  3 | john@example.com |
+----+------------------+
1 row in set (0.00 sec)

然后，就需要使用delete语句，此时涉及到的是一个多表删除的语句，应该写成如下格式：

delete p1 from person as p1 inner join person as p2 
on p1.email = p2.email and p1.id > p2.id;

我们发现在delete和from之间加入了一个p1，这代表只删除p1表中满足筛选条件的行，而p1代表person，最终就完成了对person表的delete操作。

2.2 窗口函数

首先，我们仍然需要筛选出重复的电子邮箱的id。

select p.id
from (select id, email, 
      dense_rank() over(partition by email order by id asc) as counting 
      from person) as p
where counting > 1;

然后，在person删除对应上述的id。

delete from person where id in
(select p.id
from (select id, email, 
      dense_rank() over(partition by email order by id asc) as counting 
      from person) as p
where counting > 1);

3 真题实战（新浪2021数据分析师笔试）

有一个计费表jifei，其中包含的字段有：phone（8位电话号码)、month（月份）、expense（月消费，费用为0表明该月没有产生费用），请你删除jifei表中所有10月份出现的两条相同记录的其中一条记录。

此题目中需要多个字段重复即删除，所以第一步仍然需要筛选出需要删除的行。

select j.id, j.phone, j.month, j.expense from 
(select id, phone, month, expense, 
dense_rank() over(partition by phone, month, expense order by id asc) as counting
from jifei) as j
where month(j.month) = 10 and j.counting > 1;

然后使用delete删除重复的行。

delete from jifei
where id in (
select j.id from 
(select id, phone, month, expense, 
dense_rank() over(partition by phone, month, expense order by id asc) as counting
from jifei) as j
where month(j.month) = 10 and j.counting > 1
);

上述两个步骤实际上删除的所有重复出现的行，但是题目需要删除10月份重复出现两次的记录，所以还需要内联结一个对月份记录的字段。

select j.id, j.phone, j.month, j.expense, j.counting, jc.same_count from 
(select id, phone, month, expense, dense_rank() over(partition by phone, month, expense order by id asc) as counting from jifei) as j 
inner join 
(select phone, month, expense, count(id) as same_count from jifei group by phone, month, expense) as jc
on j.phone = jc.phone and j.month = jc.month and j.expense = jc.expense
where month(j.month) = 10 and j.counting > 1 and jc.same_count = 2;