MySQL講義第8講——資料更新之 DELETE

MySQL 資料庫和其它的關係型資料庫一樣，支援資料的增（插入：insert）、刪（刪除：delete）、改（更新：update）、查（查詢：select）操作。

如果要刪除表中的部分資料，使用 delete 命令，如果要刪除表中的全部資料，可以使用 delete 命令或者 truncate命令。

一、使用 delete 命令刪除表中的資料

格式如下：

DELETE FROM 表名
    [WHERE 條件]
    [ORDER BY ...]
    [LIMIT row_count]
    
說明：
（1） ORDER BY：按照指定的順序對行刪除操作。
（2） LIMIT：限制可刪除的行數。

舉例：

（1）從 certificate 表刪除 phone 為 133 開頭的記錄

mysql> select * from certificate;
+------+--------+-------------+-----------------+
| s_no | s_name | phone       | certificate     |
+------+--------+-------------+-----------------+
|    1 | Jack   | 13703735566 | 英語四級        |
|    2 | Mark   | 13783735566 | 英語四級        |
|    3 | Rose   | 13783735522 | 英語六級        |
|    4 | John   | 18503735214 | 計算機二級      |
|    5 | Jerry  | 13303735266 | 英語四級        |
+------+--------+-------------+-----------------+
5 rows in set (0.00 sec)

mysql> delete from certificate where phone like '133%';
Query OK, 1 row affected (0.02 sec)

mysql> select * from certificate;
+------+--------+-------------+-----------------+
| s_no | s_name | phone       | certificate     |
+------+--------+-------------+-----------------+
|    1 | Jack   | 13703735566 | 英語四級        |
|    2 | Mark   | 13783735566 | 英語四級        |
|    3 | Rose   | 13783735522 | 英語六級        |
|    4 | John   | 18503735214 | 計算機二級      |
+------+--------+-------------+-----------------+
4 rows in set (0.00 sec)

（2）刪除時限制刪除的行數：從 certificate 表刪除 phone 為 137 開頭的記錄，但一次最多刪除兩行。並且按學號降序的順序刪除。

mysql> select * from certificate;
+------+--------+-------------+-----------------+
| s_no | s_name | phone       | certificate     |
+------+--------+-------------+-----------------+
|    1 | Jack   | 13703735566 | 英語四級        |
|    2 | Mark   | 13783735566 | 英語四級        |
|    3 | Rose   | 13783735522 | 英語六級        |
|    4 | John   | 18503735214 | 計算機二級      |
+------+--------+-------------+-----------------+
4 rows in set (0.00 sec)

mysql> delete from certificate where phone like '137%'
    -> order by s_no desc
    -> limit 2;
Query OK, 2 rows affected (0.07 sec)

mysql> select * from certificate;
+------+--------+-------------+-----------------+
| s_no | s_name | phone       | certificate     |
+------+--------+-------------+-----------------+
|    1 | Jack   | 13703735566 | 英語四級        |
|    4 | John   | 18503735214 | 計算機二級      |
+------+--------+-------------+-----------------+
2 rows in set (0.01 sec)

二、使用 truncate 命令刪除表中的資料

使用 truncate 命令可以刪除表中的所有資料，和不帶條件的 delete 命令結果相同。兩個命令的區別有兩點：
（1）delete 命令在刪除時逐行判斷和刪除，效率較低；truncate 命令是直接刪除表，然後重建表，因此刪除的效率很高。
（2）當表中有自增欄位時，如果使用 delete 命令刪除全部記錄，當重新插入記錄時，自增欄位的值從刪除之前的編號的最大值開始增加。如果使用 truncate 命令刪除全部記錄，重新插入記錄時，自增欄位的值將重新開始編號。

truncate 命令的語法如下：

truncate [TABLE] 表名;

舉例：

（1）建立表 t1，id 為自增欄位，初始值為 1，然後插入資料。根據 t1 表生成 t2，t2 表的結構和資料與 t1 表完全相同。

結果如下：

mysql> desc t1;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | zhang |
|  2 | wang  |
|  3 | li    |
|  4 | zhao  |
|  5 | liu   |
+----+-------+
5 rows in set (0.00 sec)

mysql> create table t2 like t1;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t2 select * from t1;
Query OK, 5 rows affected (0.08 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> desc t2;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> select * from t2;
+----+-------+
| id | name  |
+----+-------+
|  1 | zhang |
|  2 | wang  |
|  3 | li    |
|  4 | zhao  |
|  5 | liu   |
+----+-------+
5 rows in set (0.00 sec)

（2）使用 delete 命令刪除 t1 表中的所有資料，然後插入新記錄

可以看到，新插入的記錄 id 從 6 開始編號。

mysql> delete from t1;
Query OK, 5 rows affected (0.02 sec)

mysql> insert into t1(name) values('Tom'),('Jack'),('Jerry');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  6 | Tom   |
|  7 | Jack  |
|  8 | Jerry |
+----+-------+
3 rows in set (0.00 sec)

（3）使用 truncate 命令刪除 t2 表中的所有資料，然後插入新記錄

可以看到，新插入的記錄 id 從 1 重新開始編號。

mysql> truncate table t2;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t2(name) values('Tom'),('Jack'),('Jerry');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t2;
+----+-------+
| id | name  |
+----+-------+
|  1 | Tom   |
|  2 | Jack  |
|  3 | Jerry |
+----+-------+
3 rows in set (0.00 sec)