大厂高频面试题:如何实现 MySQL 删除重复记录并且只保留一条?

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 最近在做题库系统,由于在题库中添加了重复的试题,所以需要查询出重复的试题,并且删除掉重复的试题只保留其中1条,以保证考试的时候抽不到重复的题。

最近在做题库系统,由于在题库中添加了重复的试题,所以需要查询出重复的试题,并且删除掉重复的试题只保留其中1条,以保证考试的时候抽不到重复的题。

关于MySQL的知识点总结了一个思维导图,希望对大家所有帮助!

MySQL知识点总结.jpg

首先写了一个小的例子:

一、单个字段的操作

这是数据库中的表:

image.png

分组介绍:

image.png

Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1

查看是否有重复的数据:

GROUP BY <列名序列>

HAVING <组条件表达式>

查询出:根据dname分组,同时满足having字句中组条件表达式(重复次数大于1)的那些组

count(*)与count(1) 其实没有什么差别,用哪个都可以

count(*)与count(列名)的区别:

count(*)将返回表格中所有存在的行的总数包括值为null的行,然而count(列名)将返回表格中除去null以外的所有行的总数(有默认值的列也会被计入)

1. 查询全部重复的数据:

image.png

Select * From 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1)

2. 删除全部重复试题:

将上面的查询select改为delete(这样会出错的)

DELETE
FROM
 dept
WHERE
 dname IN (
  SELECT
   dname
  FROM
   dept
  GROUP BY
   dname
  HAVING
   count(1) > 1
 )

会出现如下错误:[Err] 1093 - You can't specify target table 'dept' for update in FROM clause

原因是:更新这个表的同时又查询了这个表,查询这个表的同时又去更新了这个表,可以理解为死锁。mysql不支持这种更新查询同一张表的操作

解决办法:把要更新的几列数据查询出来做为一个第三方表,然后筛选更新。

image.png

3. 查询表中多余重复试题(根据depno来判断,除了rowid最小的一个)

a. 第一种方法:

image.png

SELECT
 *
FROM
 dept
WHERE
 dname IN (
  SELECT
   dname
  FROM
   dept
  GROUP BY
   dname
  HAVING
   COUNT(1) > 1
 )
AND deptno NOT IN (
 SELECT
  MIN(deptno)
 FROM
  dept
 GROUP BY
  dname
 HAVING
  COUNT(1) > 1
)

上面这种写法正确,但是查询的速度太慢,可以试一下下面这种方法:

b. 第二种方法:

☆根据dname分组,查找出deptno最小的。然后再查找deptno不包含刚才查出来的。这样就查询出了所有的重复数据(除了deptno最小的那行)

SELECT *
FROM
 dept
WHERE
 deptno NOT IN (
  SELECT
   dt.minno
  FROM
   (
    SELECT
     MIN(deptno) AS minno
    FROM
     dept
    GROUP BY
     dname
   ) dt
 )

c. 补充第三种方法(根据评论区给的删除总结出来的):

SELECT
 * 
FROM
 table_name AS ta 
WHERE
 ta.唯一键 <> ( SELECT max( tb.唯一键 ) FROM table_name AS tb WHERE ta.判断重复的列 = tb.判断重复的列 );

4. 删除表中多余重复试题并且只留1条:

a. 第一种方法:

DELETE
FROM
 dept
WHERE
 dname IN (
  SELECT
   t.dname
  FROM
   (
    SELECT
     dname
    FROM
     dept
    GROUP BY
     dname
    HAVING
     count(1) > 1
   ) t
 )
AND deptno NOT IN (
SELECT
 dt.mindeptno
FROM
 (
  SELECT
   min(deptno) AS mindeptno
  FROM
   dept
  GROUP BY
   dname
  HAVING
   count(1) > 1
 ) dt
)

b. ☆第二种方法(与上面查询的第二种方法对应,只是将select改为delete):

DELETE
FROM
 dept
WHERE
 deptno NOT IN (
  SELECT
   dt.minno
  FROM
   (
    SELECT
     MIN(deptno) AS minno
    FROM
     dept
    GROUP BY
     dname
   ) dt
 )

c. 补充第三种方法(评论区推荐的一种方法):

DELETE 
FROM
 table_name AS ta 
WHERE
 ta.唯一键 <> (
SELECT
 t.maxid 
FROM
 ( SELECT max( tb.唯一键 ) AS maxid FROM table_name AS tb WHERE ta.判断重复的列 = tb.判断重复的列 ) t 
 );

二、多个字段的操作:

单个字段的如果会了,多个字段也非常简单。就是将group by 的字段增加为你想要的即可。

此处只写一个,其他方法请仿照一个字段的写即可。

DELETE
FROM
 dept
WHERE
 (dname, db_source) IN (
  SELECT
   t.dname,
   t.db_source
  FROM
   (
    SELECT
     dname,
     db_source
    FROM
     dept
    GROUP BY
     dname,
     db_source
    HAVING
     count(1) > 1
   ) t
 )
AND deptno NOT IN (
 SELECT
  dt.mindeptno
 FROM
  (
   SELECT
    min(deptno) AS mindeptno
   FROM
    dept
   GROUP BY
    dname,
    db_source
   HAVING
    count(1) > 1
  ) dt
)

总结:

其实上面的方法还有很多需要优化的地方,如果数据量太大的话,执行起来很慢,可以考虑加优化一下:

  • 在经常查询的字段上加上索引
  • 将*改为你需要查询出来的字段,不要全部查询出来
  • 小表驱动大表用IN,大表驱动小表用EXISTS。IN适合的情况是外表数据量小的情况,而不是外表数据大的情况,因为IN会遍历外表的全部数据,假设a表100条,b表10000条那么遍历次数就是100*10000次,而exists则是执行100次去判断a表中的数据是否在b表中存在,它只执行了a.length次数。至于哪一个效率高是要看情况的,因为in是在内存中比较的,而exists则是进行数据库查询操作的

小编总结了2020面试题,这份面试题的包含的模块分为19个模块,分别是: Java 基础、容器、多线程、反射、对象拷贝、Java Web 、异常、网络、设计模式、Spring/Spring MVC、Spring Boot/Spring Cloud、Hibernate、MyBatis、RabbitMQ、Kafka、Zookeeper、MySQL、Redis、JVM 。

关注我的公众号:程序员白楠楠,获取上述资料。

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
相关文章
|
4月前
|
缓存 NoSQL 关系型数据库
美团面试:MySQL有1000w数据,redis只存20w的数据,如何做 缓存 设计?
美团面试:MySQL有1000w数据,redis只存20w的数据,如何做 缓存 设计?
美团面试:MySQL有1000w数据,redis只存20w的数据,如何做 缓存 设计?
|
4月前
|
存储 关系型数据库 MySQL
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
|
2月前
|
关系型数据库 MySQL Java
字节面试: MySQL 百万级 导入发生的 “死锁” 难题如何解决?“2序4拆”,彻底攻克
字节面试: MySQL 百万级 导入发生的 “死锁” 难题如何解决?“2序4拆”,彻底攻克
字节面试: MySQL 百万级 导入发生的 “死锁” 难题如何解决?“2序4拆”,彻底攻克
|
4月前
|
存储 SQL 关系型数据库
京东面试:mysql深度分页 严重影响性能?根本原因是什么?如何优化?
京东面试:mysql深度分页 严重影响性能?根本原因是什么?如何优化?
京东面试:mysql深度分页 严重影响性能?根本原因是什么?如何优化?
|
9月前
|
存储 SQL 关系型数据库
MySQL进阶突击系列(03) MySQL架构原理solo九魂17环连问 | 给大厂面试官的一封信
本文介绍了MySQL架构原理、存储引擎和索引的相关知识点,涵盖查询和更新SQL的执行过程、MySQL各组件的作用、存储引擎的类型及特性、索引的建立和使用原则,以及二叉树、平衡二叉树和B树的区别。通过这些内容,帮助读者深入了解MySQL的工作机制,提高数据库管理和优化能力。
|
4月前
|
SQL 存储 关系型数据库
滴滴面试:明明 mysql 加的是 行锁,怎么就变 表锁 了?
滴滴面试:明明 mysql 加的是 行锁,怎么就变 表锁 了?
|
8月前
|
存储 关系型数据库 MySQL
美团面试:MySQL为什么 不用 Docker部署?
45岁老架构师尼恩在读者交流群中分享了关于“MySQL为什么不推荐使用Docker部署”的深入分析。通过系统化的梳理,尼恩帮助读者理解为何大型MySQL数据库通常不使用Docker部署,主要涉及性能、管理复杂度和稳定性等方面的考量。文章详细解释了有状态容器的特点、Docker的资源隔离问题以及磁盘IO性能损耗,并提供了小型MySQL使用Docker的最佳实践。此外,尼恩还介绍了Share Nothing架构的优势及其应用场景,强调了配置管理和数据持久化的挑战。最后,尼恩建议读者参考《尼恩Java面试宝典PDF》以提升技术能力,更好地应对面试中的难题。
|
6月前
|
消息中间件 NoSQL 关系型数据库
去哪面试:1Wtps高并发,MySQL 热点行 问题, 怎么解决?
去哪面试:1Wtps高并发,MySQL 热点行 问题, 怎么解决?
去哪面试:1Wtps高并发,MySQL 热点行 问题, 怎么解决?
|
7月前
|
SQL 关系型数据库 MySQL
京东面试:MySQL MVCC是如何实现的?如何通过MVCC实现读已提交、可重复读隔离级别的?
1.请解释什么是MVCC,它在数据库中的作用是什么? 2.在MySQL中,MVCC是如何实现的?请简述其工作原理。 3.MVCC是如何解决读-写和写-写冲突的? 4.在并发环境中,当多个事务同时读取同一行数据时,MVCC是如何保证每个事务看到的数据版本是一致的? 5.MVCC如何帮助提高数据库的并发性能?
京东面试:MySQL MVCC是如何实现的?如何通过MVCC实现读已提交、可重复读隔离级别的?
|
8月前
|
存储 SQL 关系型数据库
MySQL 面试题
MySQL 的一些基础面试题

推荐镜像

更多