深入理解 DML 和 DQL：SQL 數據操作與查詢全解析

在數據庫管理中，SQL（結構化查詢語言）是操作和查詢數據的核心工具。其中，DML（Data Manipulation Language，數據操作語言） 和 DQL（Data Query Language，數據查詢語言） 是最常用的子集。DML 負責數據的插入、更新和刪除，而 DQL 專注于數據查詢。本文將詳細講解 DML 和 DQL 的核心操作，包括 UPDATE、DELETE、TRUNCATE、SELECT，以及相關函數和子句，結合示例幫助你快速上手。

一、DML：數據操作語言

DML 用于操作數據庫中的數據，主要包括以下操作：

插入（INSERT）：向表中添加新記錄。
更新（UPDATE）：修改表中已有記錄。
刪除（DELETE）：移除表中記錄。

以下重點講解 UPDATE 和 DELETE，并深入分析 TRUNCATE 與 DELETE 的區別。

1. UPDATE：修改數據

UPDATE 用于修改表中符合條件的記錄，語法如下：

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

作用：更新指定列的值。
注意：WHERE 子句指定更新的范圍，未指定會導致全表更新。

示例

假設有一個 student 表：

id	name	age
1	Alice	20
2	Bob	22
3	Charlie	20

需求：將年齡為 20 的學生年齡改為 21。

UPDATE student
SET age = 21
WHERE age = 20;

結果：

id	name	age
1	Alice	21
2	Bob	22
3	Charlie	21

注意：

沒有 WHERE 子句時，UPDATE student SET age = 21; 會將所有記錄的 age 改為 21。
建議先用 SELECT 查詢確認受影響的記錄。

2. DELETE：刪除數據

DELETE 用于刪除表中符合條件的記錄，語法如下：

DELETE FROM table_name
WHERE condition;

作用：移除滿足條件的記錄。
注意：不帶 WHERE 會刪除表中所有記錄。

示例

需求：刪除年齡小于 22 的學生。

DELETE FROM student
WHERE age < 22;

結果：

id	name	age
2	Bob	22

注意：

刪除后，表結構和索引保留，數據可通過事務回滾（如果在事務中）。
自增列計數器行為因存儲引擎不同而異（詳見下文）。

3. TRUNCATE：清空表

TRUNCATE 用于完全清空表中的數據，語法如下：

TRUNCATE TABLE table_name;

作用：刪除表中所有記錄，重置表到初始狀態。
注意：無法指定條件，總是清空整個表。

示例

需求：清空 student 表。

TRUNCATE TABLE student;

結果：

表變為空，結構保留。
自增列計數器重置為 1。

二、TRUNCATE 與 DELETE 的區別

TRUNCATE 和 DELETE 都可以刪除數據，但有顯著差異。以下是詳細對比：

特性	DELETE	TRUNCATE
刪除范圍	可通過 `WHERE` 刪除部分記錄	刪除整個表，無條件
速度	較慢，逐行刪除，記錄日志	更快，直接重建表結構
事務支持	支持事務，可回滾	不影響事務，無法回滾
自增列計數器	不重置，保留上次值	重置為 1
觸發器	觸發 `DELETE` 觸發器	不觸發觸發器
外鍵約束	支持（受外鍵限制）	不支持（表有外鍵時無法使用）
日志記錄	記錄每行操作，占用日志空間	僅記錄表結構變更，日志少

TRUNCATE 的優勢

速度快：TRUNCATE 直接重建表結構，效率高于逐行刪除的 DELETE。
重置自增列：適合需要重置主鍵計數器的場景（如測試環境清空數據）。
不影響事務：執行后不記錄逐行日志，節省日志空間。
低資源占用：適合快速清空大表。

DELETE 刪除后的行為（重啟數據庫）

DELETE 刪除數據后，自增列計數器的行為因存儲引擎不同而異：

InnoDB

：

自增計數器存儲在內存中。
重啟數據庫后，計數器從 1 重新開始。

示例：

CREATE TABLE test (id INT AUTO_INCREMENT PRIMARY KEY);
INSERT INTO test VALUES (1), (2), (3);
DELETE FROM test;
INSERT INTO test VALUES (NULL); -- id = 1（重啟后）

MyISAM

：
- 自增計數器存儲在文件中，持久化。
- 重啟數據庫后，從上一個最大值繼續。
- 示例：
```
DELETE FROM test;
INSERT INTO test VALUES (NULL); -- id = 4（繼續上一個值）
```

示例：DELETE vs TRUNCATE

-- 創建表
CREATE TABLE student (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50),age INT
);
INSERT INTO student (name, age) VALUES ('Alice', 20), ('Bob', 22);-- DELETE
DELETE FROM student;
INSERT INTO student (name, age) VALUES ('Charlie', 21);
-- InnoDB 重啟后：id = 1；MyISAM：id = 3-- TRUNCATE
TRUNCATE TABLE student;
INSERT INTO student (name, age) VALUES ('Charlie', 21);
-- 無論引擎：id = 1

三、DQL：數據查詢語言

DQL 用于從數據庫中檢索數據，主要通過 SELECT 語句實現。以下講解 SELECT 的核心用法，包括簡單查詢、特定字段查詢、別名、去重和條件查詢。

1. 簡單單表查詢

查詢整個表

SELECT * FROM student;

作用：返回 student 表的所有列和記錄。

示例輸出

（假設表如上）：

id | name    | age
1  | Alice   | 20
2  | Bob     | 22

注意：* 適合快速查看，但生產環境中建議明確指定列以提高性能。

查詢特定字段

SELECT name, age FROM student;

作用：只返回 name 和 age 列。

示例輸出

：

name    | age
Alice   | 20
Bob     | 22

給結果起別名

使用 AS 關鍵字為列或表達式指定別名：

SELECT name AS student_name, age AS student_age FROM student;

輸出

：

student_name | student_age
Alice        | 20
Bob          | 22

別名提高可讀性，常用在復雜查詢或報表生成中。

2. 函數：CONCAT

CONCAT 函數用于拼接字符串，語法如下：

SELECT CONCAT(column1, ' ', column2) AS result FROM table_name;

示例

需求：將學生的姓名和年齡拼接為一個字符串。

SELECT CONCAT(name, ' is ', age, ' years old') AS info FROM student;

輸出：

info
Alice is 20 years old
Bob is 22 years old

注意：

不同數據庫對 CONCAT 的支持略有差異（如 MySQL 支持多參數，SQL Server 使用 +）。
可結合其他函數（如 CAST）處理非字符串類型。

3. 去重：DISTINCT

DISTINCT 用于去除查詢結果中的重復行，語法如下：

SELECT DISTINCT column1, column2 FROM table_name;

示例

需求：查詢所有不同的年齡。

SELECT DISTINCT age FROM student;

假設數據：

id	name	age
1	Alice	20
2	Bob	22
3	Charlie	20

輸出：

age
20
22

注意：

DISTINCT 作用于整行（多列時考慮組合）。
對性能有一定影響，盡量在必要時使用。

4. WHERE 條件子句

WHERE 用于過濾滿足條件的記錄，語法如下：

SELECT column1, column2
FROM table_name
WHERE condition;

示例

需求：查詢年齡大于 20 的學生。

SELECT name, age
FROM student
WHERE age > 20;

輸出：

name | age
Bob  | 22

常見條件：

比較：=, >, <, >=, <=, !=。
邏輯：AND, OR, NOT。
范圍：BETWEEN ... AND ...。
集合：IN (value1, value2)。
模糊匹配：LIKE '%pattern%'。

綜合示例

需求：查詢年齡為 20 或 22 的學生，拼接姓名和年齡，去重后顯示。

SELECT DISTINCT CONCAT(name, ' is ', age) AS info
FROM student
WHERE age IN (20, 22);

輸出：

info
Alice is 20
Bob is 22
Charlie is 20

四、實際應用場景

數據清理：
- 使用 DELETE 移除無效記錄（如 WHERE created_date < '2020-01-01'）。
- 使用 TRUNCATE 重置測試環境數據。
數據更新：
- UPDATE 批量修改用戶信息（如 SET status = 'active' WHERE last_login > '2023-01-01'）。
報表生成：
- SELECT 結合 CONCAT 和 DISTINCT 生成用戶統計報表。
- 使用 WHERE 過濾特定條件的數據。

五、注意事項與優化技巧

DML 操作：
- 事務管理：DELETE 和 UPDATE 應在事務中執行，確保可回滾。
- 日志監控：DELETE 操作可能導致日志文件過大，定期清理。
- 備份：執行 TRUNCATE 前備份數據，因無法回滾。
DQL 查詢：
- 索引優化：為 WHERE 條件中的列建立索引，提高查詢效率。
- 避免 SELECT \*：明確指定列，減少不必要的數據傳輸。
- 去重性能：DISTINCT 可能影響性能，優先考慮業務邏輯去重。
存儲引擎選擇：
- InnoDB：適合事務密集場景，自增列需注意重啟行為。
- MyISAM：適合讀多寫少場景，自增列更穩定。

六、總結

DML 和 DQL 是數據庫操作的核心組成部分：

DML（UPDATE, DELETE, TRUNCATE）用于修改和刪除數據，TRUNCATE 適合快速清空表，DELETE 提供更靈活的條件刪除。
DQL（SELECT）通過 WHERE、DISTINCT、CONCAT 等功能實現精確查詢，滿足多樣化需求。