快捷導(dǎo)航

如何在SQL中實(shí)現(xiàn)表的增刪

更新時(shí)間：2025年10月28日 10:05:06 作者：啊吧怪不啊吧

本文介紹SQL增刪操作,增含建表、全列 / 指定列插入及沖突處理,刪有 DELETE 和 TRUNCATE,二者各有邏輯、適用場(chǎng)景,且均保留表結(jié)構(gòu),本文結(jié)合實(shí)例代碼給大家介紹的非常詳細(xì),感興趣的朋友跟隨小編一起看看吧

1.增

1.1 create

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

1.2 全列插入

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

1.3 指定列插入

下面這張圖就是指定列插入，我們?cè)谶@里給a1和a2插入了值。那么我們?cè)诓榭吹臅r(shí)候就會(huì)發(fā)現(xiàn)在這張表的第二行的a3位置就是NULL。

1.4插入否則更新（duplicate）

具體語法：

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

我們?cè)诓迦胫档臅r(shí)候如果給主鍵或唯一鍵插入了一樣的值那么就會(huì)報(bào)錯(cuò)。就像下面這樣。

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

1.5替換（replace）

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

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

當(dāng)主鍵或者唯一鍵沒用沖突的時(shí)候它就會(huì)直接插入數(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：當(dāng)插入的數(shù)據(jù)與表中現(xiàn)有數(shù)據(jù)發(fā)生唯一鍵沖突時(shí)，會(huì)先刪除表中已存在的沖突行，然后插入新行。本質(zhì)上等價(jià)于執(zhí)行了 DELETE + INSERT 兩個(gè)操作。
ON DUPLICATE KEY UPDATE：當(dāng)插入的數(shù)據(jù)發(fā)生唯一鍵沖突時(shí)，不會(huì)刪除原有行，而是直接更新原有行中指定的字段。本質(zhì)上等價(jià)于執(zhí)行了 UPDATE 操作（僅針對(duì)沖突行）。

1.7.2對(duì)自增主鍵的影響不同

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

1.7.3對(duì)未指定字段的處理不同

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

1.7.4示例對(duì)比

假設(shè)有表 student 結(jié)構(gòu)如下：

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

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

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

使用 REPLACE：

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

執(zhí)行后結(jié)果：

原有行 (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í)行后結(jié)果：

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

1.7.5適用場(chǎng)景

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

總結(jié)

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

2. 刪

2.1 刪除表（delete）

語法：

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

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

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

-- 查看原數(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) 
-- 查看刪除結(jié)果 
SELECT * FROM exam_result WHERE name = '孫悟空'; 
Empty set (0.00 sec)

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

2.2 截?cái)啾恚╰runcate）

語法：

TRUNCATE [TABLE] table_name

注意：這個(gè)操作慎用

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

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

3. 會(huì)重置 AUTO_INCREMENT 項(xiàng)

所以說這個(gè)的話就僅做了解即可，因?yàn)樗恢С謹(jǐn)?shù)據(jù)回滾就意味著無法通過一些常規(guī)手段來對(duì)數(shù)據(jù)表進(jìn)行復(fù)原。但是他刪除數(shù)據(jù)的速度很快，在一些已經(jīng)確定要?jiǎng)h除且數(shù)據(jù)很大的表中很好用。