今天我們來聊一下表的增刪。對于數(shù)據(jù)表來說，數(shù)據(jù)是非常重要的，所以關于數(shù)據(jù)的增刪也是非常重要的。

1.增

1.1 create

下面這張圖就是create的使用方式。

1.2 全列插入

下面這張圖就是全列插入，意思就是說給這張表里面的每一個數(shù)據(jù)都插入值。

1.3 指定列插入

下面這張圖就是指定列插入，我們在這里給a1和a2插入了值。那么我們在查看的時候就會發(fā)現(xiàn)在這張表的第二行的a3位置就是NULL。

1.4插入否則更新（duplicate）

具體語法：

INSERT ... ON DUPLICATE KEY UPDATE column = value [, column = value] ...

我們在插入值的時候如果給主鍵或唯一鍵插入了一樣的值那么就會報錯。就像下面這樣。

這個時候我們就可以使用這個指令在發(fā)生沖突的時候來進行更新，通過額外添加的 on duplicate key update a2=15,a3=150;我們就可以實現(xiàn)修改其已被主鍵占用的那一行值。

1.5替換（replace）

主鍵 或者 唯一鍵 沒有沖突，則直接插入；

主鍵 或者 唯一鍵 如果沖突，則刪除后再插入

當主鍵或者唯一鍵沒用沖突的時候它就會直接插入數(shù)據(jù)。

我們看下面這張圖，我們使用replace那么就可以直接更改主鍵的那一行值。

我們也可以根據(jù)指令執(zhí)行完后的那一行語句來確定是直接插入還是刪除后插入。

-- 1 row affected: 表中沒有沖突數(shù)據(jù)，數(shù)據(jù)被插入

-- 2 row affected: 表中有沖突數(shù)據(jù)，刪除后重新插入

1.7 deplicate和replace的區(qū)別

1.7.1核心操作邏輯不同

• REPLACE：當插入的數(shù)據(jù)與表中現(xiàn)有數(shù)據(jù)發(fā)生唯一鍵沖突時，會先刪除表中已存在的沖突行，然后插入新行。本質(zhì)上等價于執(zhí)行了 DELETE + INSERT 兩個操作。
• ON DUPLICATE KEY UPDATE：當插入的數(shù)據(jù)發(fā)生唯一鍵沖突時，不會刪除原有行，而是直接更新原有行中指定的字段。本質(zhì)上等價于執(zhí)行了 UPDATE 操作（僅針對沖突行）。

1.7.2對自增主鍵的影響不同

• REPLACE：由于會先刪除沖突行再插入新行，若表使用自增主鍵，新插入的行會生成新的自增 ID（原有 ID 被廢棄，不會重復使用）。例如：原有行 id=1 沖突，REPLACE 后新行可能是 id=2（自增 ID 遞增）。
• ON DUPLICATE KEY UPDATE：僅更新原有行，不會刪除數(shù)據(jù)，因此自增主鍵的值保持不變。例如：原有行 id=1 沖突，更新后仍為 id=1。

1.7.3對未指定字段的處理不同

• REPLACE：插入新行時，若新數(shù)據(jù)中未明確指定某些字段，這些字段會使用表定義的默認值（或 NULL），覆蓋原有行的值。例如：原有行有 age=20，但 REPLACE 語句未指定 age，則新行的 age 會變?yōu)槟J值（如 NULL）。
• ON DUPLICATE KEY UPDATE：僅更新 UPDATE 子句中明確指定的字段，未指定的字段保持原有值不變。例如：原有行 age=20，UPDATE 僅指定更新 name，則 age 仍為 20。

1.7.4示例對比

假設有表 student 結構如下：

CREATE TABLE student (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) UNIQUE, -- 唯一索引，可能沖突
  score INT
);

已有數(shù)據(jù)：(id=1, name='Tom', score=80)

場景：插入 name='Tom' 的新數(shù)據(jù)（沖突）

使用 REPLACE：

REPLACE INTO student (name, score) VALUES ('Tom', 90);

執(zhí)行后結果：

原有行 (1, 'Tom', 80) 被刪除。

插入新行 (2, 'Tom', 90)（id 變?yōu)?2，自增 ID 遞增）。

使用 ON DUPLICATE KEY UPDATE：

INSERT INTO student (name, score) VALUES ('Tom', 90)
ON DUPLICATE KEY UPDATE score = VALUES(score);

執(zhí)行后結果：

原有行 (1, 'Tom', 80) 被更新為 (1, 'Tom', 90)（id 保持 1，僅更新 score）。

1.7.5適用場景

• REPLACE：適合需要完全替換沖突行（包括未指定字段使用默認值）的場景，但需注意自增 ID 變化的影響（可能導致 ID 不連續(xù)）。
• ON DUPLICATE KEY UPDATE：適合僅更新部分字段、保留其他原有數(shù)據(jù)的場景，效率更高（無需刪除操作），且不會改變自增 ID。

總結

兩者的核心區(qū)別在于：REPLACE 是 “刪舊插新”，會改變行的存在性和自增 ID；ON DUPLICATE KEY UPDATE 是 “原地更新”，僅修改指定字段，保留原有行的其他屬性。選擇時需根據(jù)是否需要保留原有數(shù)據(jù)、自增 ID 是否需不變等需求決定。

2. 刪

2.1 刪除表（delete）

語法：

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

我們來看下面這行代碼，我們可以通過delete這個指令來刪除表中的一行。

如果我們把WHERE name = '孫悟空';這句話去掉的話，那么delete就會直接刪除掉整張表。

-- 查看原數(shù)據(jù) SELECT * FROM exam_result WHERE name = '孫悟空';
 +----+-----------+-------+--------+--------+ 
| id | name | chinese | math | english |
 +----+-----------+-------+--------+--------+ 
| 2 | 孫悟空 | 174 | 80 | 77 | +
----+-----------+-------+--------+--------+ 
1 row in set (0.00 sec) 
-- 刪除數(shù)據(jù) 
DELETE FROM exam_result WHERE name = '孫悟空';
Query OK, 1 row affected (0.17 sec) 
-- 查看刪除結果 
SELECT * FROM exam_result WHERE name = '孫悟空'; 
Empty set (0.00 sec)

PS:delete是不會讓那個auto_increment重新開始計算的，也就是說我們刪除一張表之后如果再往里面插入數(shù)據(jù)的話，

2.2 截斷表（truncate）

語法：

TRUNCATE [TABLE] table_name

注意：這個操作慎用

1. 只能對整表操作，不能像 DELETE 一樣針對部分數(shù)據(jù)操作；

2. 實際上 MySQL 不對數(shù)據(jù)操作，所以比 DELETE 更快，但是TRUNCATE在刪除數(shù)據(jù)的時候，并不經(jīng)過真正的事物，所以無法回滾

3. 會重置 AUTO_INCREMENT 項

所以說這個的話就僅做了解即可，因為他不支持數(shù)據(jù)回滾就意味著無法通過一些常規(guī)手段來對數(shù)據(jù)表進行復原。但是他刪除數(shù)據(jù)的速度很快，在一些已經(jīng)確定要刪除且數(shù)據(jù)很大的表中很好用。

2.3 刪除的注意點

DELETE 和 TRUNCATE 都只刪除表中的數(shù)據(jù)，而表的結構（包括列定義、數(shù)據(jù)類型、索引、約束、主鍵等）會被完整保留，不會被刪除。

具體來說：

• delete：僅刪除表中符合條件的行（或全表行），表的結構、索引、約束等元數(shù)據(jù)完全不變。
• truncate：同樣只刪除數(shù)據(jù)，表的結構、索引、約束等依然保留（相當于 “清空內(nèi)容但保留容器”）。

到此這篇關于SQL中表的增刪的文章就介紹到這了,更多相關sql表增刪內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論