一、前言

在數(shù)據(jù)庫中，SQL作為一種常用的數(shù)據(jù)庫編程語言，扮演著至關(guān)重要的角色。SQL不僅可以用于創(chuàng)建、修改和查詢數(shù)據(jù)庫，還可以通過DROP、DELETE和TRUNCATE等語句來刪除數(shù)據(jù)。這些語句是SQL語言中的最常用的命令，且它們有著不同的含義和使用場景。

本文以GaussDB數(shù)據(jù)庫為平臺，將詳細(xì)介紹SQL中DROP、TRUNCATE和DELETE等語句的含義、使用場景以及注意事項(xiàng)，幫助讀者更好地理解和掌握這些常用的數(shù)據(jù)庫操作命令。

二、GaussDB的 DROP & TRUNCATE & DELETE 簡述

1、簡述

• DROP語句可以刪除整個(gè)表，包括表結(jié)構(gòu)和數(shù)據(jù)；
• TRUNCATE語句則可以快速地刪除表中的所有數(shù)據(jù)，但不刪除表結(jié)構(gòu)。
• DELETE語句可以刪除表中的數(shù)據(jù)，不包括表結(jié)構(gòu)；

2、命令比對

Tip：在GaussDB數(shù)據(jù)庫中，DROP是用于定義或修改數(shù)據(jù)庫中的對象的命令之一。對象主要包括：庫、模式、表空間、表、索引、視圖、存儲(chǔ)過程、函數(shù)、加密秘鑰等，本次只針對其對表的操作。

三、GaussDB的DROP TABLE命令及示例

1、功能描述

DROP TABLE的功能是用來刪除已存在的Table。

2、語法

DROP TABLE [IF EXISTS] [db_name.]table_name;

說明：SQL中加[IF EXISTS] ，可以防止因表不存在而導(dǎo)致執(zhí)行報(bào)錯(cuò)。

參數(shù)：db_name：Database名稱。如果未指定，將選擇當(dāng)前database。table_name：需要?jiǎng)h除的Table名稱。

3、示例

以下示例演示DROP命令的使用，依次執(zhí)行如下SQL語句：

--刪除整個(gè)表course

DROP TABLE IF EXISTS course

--創(chuàng)建course表

CREATE TABLE course(cid VARCHAR(10),cname VARCHAR(10),teid VARCHAR(10));

--初始化數(shù)據(jù)

INSERT INTO course VALUES('01' , '語文' , '02');

INSERT INTO course VALUES('02' , '數(shù)學(xué)' , '01');

INSERT INTO course VALUES('03' , '英語' , '03');

--3條記錄

SELECT count(1) FROM course;

--刪除整個(gè)表

DROP TABLE IF EXISTS course

--查看結(jié)果，表不存在（表結(jié)構(gòu)及數(shù)據(jù)不存在）

SELECT count(1) FROM course;

1）DROP TABLE，提示表不存在

2）創(chuàng)建并初始化一張實(shí)驗(yàn)表

圖片

3）DROP TABLE 執(zhí)行成功

4）查看執(zhí)行結(jié)果

四、GaussDB的TRUNCATE命令及示例

1、功能描述

從表或表分區(qū)中移除所有數(shù)據(jù)，TRUNCATE快速地從表中刪除所有行。它和在目標(biāo)表上進(jìn)行無條件的DELETE有同樣的效果，但由于TRUNCATE不做表掃描，因而快得多, 且使用的系統(tǒng)和事務(wù)日志資源少。在大表上操作效果更明顯。

TRUNCATE TABLE 刪除表中的所有行，但表結(jié)構(gòu)及其列、約束、索引等保持不變。新行標(biāo)識所用的計(jì)數(shù)值重置為該列的種子。

2、語法

TRUNCATE [TABLE] table_name;

或：

ALTER TABLE [IF EXISTS] table_name TRUNCATE PARTITION { partition_name | FOR ( partition_value [, ...] ) }

參數(shù)：table_name：需要?jiǎng)h除數(shù)據(jù)的Table名稱。partition_name：需要?jiǎng)h除的分區(qū)表的分區(qū)名稱。partition_value：需要?jiǎng)h除的分區(qū)表的分區(qū)值。

3、示例1

以下示例演示TRUNCATE命令的使用：

--創(chuàng)建course表

DROP TABLE IF EXISTS course;

CREATE TABLE course(cid VARCHAR(10),cname VARCHAR(10),teid VARCHAR(10));

--初始化數(shù)據(jù)

INSERT INTO course VALUES('01' , '語文' , '02');

INSERT INTO course VALUES('02' , '數(shù)學(xué)' , '01');

INSERT INTO course VALUES('03' , '英語' , '03');

--3條記錄

SELECT count(1) FROM course;

--清空表

TRUNCATE TABLE course;

--或

TRUNCATE course;

--0條記錄

SELECT count(1) FROM course;

1）創(chuàng)建實(shí)驗(yàn)表并初始化數(shù)據(jù)

2）TRUNCATE TABLE執(zhí)行成功

3）查看執(zhí)行結(jié)果

4、示例2

以下示例演示TRUNCATE命令的刪除分區(qū)表數(shù)據(jù)：

--創(chuàng)建列表分區(qū)（LIST）

DROP TABLE IF EXISTS orders;

CREATE TABLE orders (

id INT PRIMARY KEY,

customer_id INT,

order_date DATE,

product_id INT,

quantity INT

) PARTITION BY LIST (customer_id) (

PARTITION p1 VALUES (100),

PARTITION p2 VALUES (200),

PARTITION p3 VALUES (300),

PARTITION p4 VALUES (400),

PARTITION p5 VALUES (500)

);

--插入測試數(shù)據(jù)

INSERT INTO orders(id,customer_id,order_date,product_id,quantity)VALUES(1001,100,date'20230822',1,10);

INSERT INTO orders(id,customer_id,order_date,product_id,quantity)VALUES(1002,100,date'20230822',2,20);

INSERT INTO orders(id,customer_id,order_date,product_id,quantity)VALUES(1003,100,date'20230822',3,30);

INSERT INTO orders(id,customer_id,order_date,product_id,quantity)VALUES(1004,200,date'20230822',4,40);

--查看分區(qū)p1、p2的數(shù)據(jù)

SELECT * FROM orders WHERE customer_id IN (100,200);

--或

--根據(jù)分區(qū)名稱查詢

SELECT * FROM orders PARTITION(p2);

--清空分區(qū)p1。

ALTER TABLE orders TRUNCATE PARTITION p1;

--或者

--清空分區(qū)p2=200。

ALTER TABLE orders TRUNCATE PARTITION for (200);

--查看分區(qū)p1、p2的數(shù)據(jù)

SELECT * FROM orders WHERE customer_id IN (100,200);

1）創(chuàng)建實(shí)驗(yàn)表并初始化

2）根據(jù)分區(qū)進(jìn)行刪數(shù)據(jù)

五、GaussDB的DELETE命令及示例

1、功能描述

從指定的表里刪除滿足WHERE子句的行。如果WHERE子句不存在，將刪除表中所有行，結(jié)果只保留表結(jié)構(gòu)。

2、注意事項(xiàng)

• 不支持DELETE語句中使用LIMIT。應(yīng)使用WHERE條件明確需要更新的目標(biāo)行。
• 不支持在單條SQL語句中，對多個(gè)表進(jìn)行刪除。
• DELETE語句中必須有WHERE子句，避免全表掃描。
• DELETE語句中禁止不應(yīng)使用ORDER BY、GROUP BY子句，避免不必要的排序。
• 如果需要清空一張表，建議使用TRUNCATE，而不是DELETE。
• TRUNCATE會(huì)創(chuàng)建新的物理文件，并在事務(wù)結(jié)束時(shí)將原文件物理刪除，清空磁盤空間。而DELETE會(huì)將表中數(shù)據(jù)進(jìn)行標(biāo)記，直到VACCUUM FULL階段才會(huì)真正清理磁盤空間。
• DELETE有主鍵或索引的表，WHERE條件應(yīng)結(jié)合主鍵或索引，提高執(zhí)行效率。
• DELETE 語句每次刪除一行，并在事務(wù)日志中為所刪除的每行記錄一項(xiàng)。
• 如果想保留標(biāo)識計(jì)數(shù)值，請改用 DELETE

3、語法

DELETE FROM table_name [WHERE condition];

參數(shù)：table_name：需要?jiǎng)h除數(shù)據(jù)的Table名稱。condition：用于判斷哪些行需要被刪除。

4、示例

復(fù)用前面的實(shí)驗(yàn)表：

1）刪除orders表中customer_id <200的所有數(shù)據(jù)：

DELETE FROM orders WHERE customer_id <200;

六、應(yīng)用場景

• 需要根據(jù)一定的業(yè)務(wù)條件刪除數(shù)據(jù)時(shí)、且數(shù)據(jù)量、性能可控的情況下，可以考慮使用 DELETE。
• 需要?jiǎng)h除大批量數(shù)據(jù)時(shí)，同時(shí)要求速度快，效率高并且無需撤銷時(shí)，可以使用 TRUNCATE。
• 在企業(yè)級開發(fā)中，實(shí)際上都是進(jìn)行邏輯刪除（將數(shù)據(jù)進(jìn)行“刪除標(biāo)識”處理）、而并不進(jìn)行物理上的刪除。
• 在實(shí)際生產(chǎn)環(huán)境中，一般情況下刪除業(yè)務(wù)處理（過渡表）中的數(shù)據(jù)。
• 在實(shí)際企業(yè)開發(fā)、維護(hù)過程中，不管使用 DELETE、TRUNCATE還是DROP命令前，都要考慮數(shù)據(jù)的備份。

七、小結(jié)

在GaussDB等數(shù)據(jù)庫中，DROP、TRUNCATE和DELETE均是常用的刪除數(shù)據(jù)的命令。但在實(shí)際業(yè)務(wù)使用中，需要根據(jù)不同的需求進(jìn)行準(zhǔn)確的選擇，但無論選擇那種刪數(shù)方式，都需要考慮數(shù)據(jù)安全性——重要的事情說三遍：備份！備份！備份！