最近在做題庫系統，由于在題庫中添加了重復的試題，所以需要查詢出重復的試題，并且洗掉掉重復的試題只保留其中1條，以保證考試的時候抽不到重復的題，

關于MySQL的知識點總結了一個思維導圖，希望對大家所有幫助！

首先寫了一個小的例子：

一、單個欄位的操作

這是資料庫中的表：

分組介紹：

Select 重復欄位 From 表 Group By 重復欄位 Having Count(*)>1

查看是否有重復的資料：

GROUP BY <列名序列>

HAVING <組條件運算式>

查詢出：根據dname分組，同時滿足having字句中組條件運算式（重復次數大于1）的那些組

count(*)與count(1) 其實沒有什么差別，用哪個都可以

count(*)與count（列名）的區別：

count(*)將回傳表格中所有存在的行的總數包括值為null的行，然而count(列名)將回傳表格中除去null以外的所有行的總數(有默認值的列也會被計入）

1. 查詢全部重復的資料：

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不支持這種更新查詢同一張表的操作

解決辦法：把要更新的幾列資料查詢出來做為一個第三方表，然后篩選更新，

3. 查詢表中多余重復試題（根據depno來判斷，除了rowid最小的一個）

a. 第一種方法：

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 ，

關注我的公眾號：程式員白楠楠，獲取上述資料，

標籤：其他

上一篇：用三國殺講分布式演算法，舒適了吧？

下一篇：2020年度十大高薪崗位出爐，程式員霸榜