next up previous contents
Next: 實機操作練習題 Up: Duplicates 重複紀錄 Previous: 不重複輸出重複紀錄   Contents   DYWANG_HOME

刪除重複紀錄

  1. 查詢資料表 animals,其中 cat 重複兩筆。
    mariadb root@localhost:dyw> select * from animals;                              
    +----+---------+------+
    | id | name    | foot |
    +----+---------+------+
    | 1  | dog     | 4    |
    | 2  | cat     | 4    |
    | 3  | chicken | 2    |
    | 15 | bird    | 2    |
    | 16 | cat     | 4    |
    +----+---------+------+
    
    5 rows in set
    Time: 0.031s
    
  2. 產生一個名為 tmp 的暫時資料表,其欄位格式來自資料表 animals。
    mariadb root@localhost:dyw> create table tmp like animals;                      
    Query OK, 0 rows affected
    Time: 0.053s
    
  3. 由資料表 animals 取得 name,foot 不重複的紀錄,寫入 tmp 資料表。
    mariadb root@localhost:dyw> insert into tmp(id,name,foot) select id,name,foot fr
                                om animals group by name,foot;                      
    Query OK, 4 rows affected
    Time: 0.024s
    
  4. 查看 tmp 資料表紀錄,name,foot 沒有重複的紀錄。
    mariadb root@localhost:dyw> select * from tmp;                                  
    +----+---------+------+
    | id | name    | foot |
    +----+---------+------+
    | 1  | dog     | 4    |
    | 2  | cat     | 4    |
    | 3  | chicken | 2    |
    | 15 | bird    | 2    |
    +----+---------+------+
    
    4 rows in set
    Time: 0.024s
    
  5. 查看 tmp 資料表結構,與 animals 一樣。
    mariadb root@localhost:dyw> show create table tmp\G                             
    ***************************[ 1. row ]***************************
    Table        | tmp
    Create Table | CREATE TABLE `tmp` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(20) NOT NULL,
      `foot` tinyint(3) unsigned NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8
    
    1 row in set
    Time: 0.018s
    
  6. 刪除原始資料表 animals。
    mariadb root@localhost:dyw> drop table animals;                                 
    You're about to run a destructive command.
    Do you want to proceed? (y/n): y
    Your call!
    Query OK, 0 rows affected
    Time: 0.014s
    
  7. 重新名命資料表 tmp 為 animals。
    mariadb root@localhost:dyw> alter table tmp rename to animals;                  
    You're about to run a destructive command.
    Do you want to proceed? (y/n): y
    Your call!
    Query OK, 0 rows affected
    Time: 0.011s
    
  8. 查詢新的資料表 animals 已沒有重複紀錄。
    mariadb root@localhost:dyw> select * from animals;                              
    +----+---------+------+
    | id | name    | foot |
    +----+---------+------+
    | 1  | dog     | 4    |
    | 2  | cat     | 4    |
    | 3  | chicken | 2    |
    | 15 | bird    | 2    |
    +----+---------+------+
    
    4 rows in set
    Time: 0.026s
    



De-Yu Wang 2020-04-07