sql-delete-table

SQL - 从表中删除数据

在 SQL 中，数据管理包括删除不再需要的记录。执行此操作的主要命令是 DELETE，它是一个数据操作语言（DML）语句。需要注意的是，DELETE 只从表中删除行；它不会改变表的结构，例如其列、索引或约束。本教程将介绍如何安全有效地使用 DELETE 语句删除数据，并介绍适用于不同用例的相关命令。

理解 DELETE、TRUNCATE 和 DROP

在我们深入示例之前，区分数据库中用于删除内容的三个命令至关重要：

• DELETE：一个 DML 命令，用于从表中删除行。它可以删除所有行或根据 WHERE 子句删除特定的子集行。每次行删除都会被记录下来，因此对于大型表来说可能很慢，但它也是事务性的，可以回滚。
• TRUNCATE：一个数据定义语言（DDL）命令，可以快速删除表中的 所有 行。它不像 DELETE 那样细粒度（没有 WHERE 子句），通常是最小化日志记录的操作，因此速度快得多。它还会重置某些数据库系统中的自增列（identity columns）。
• DROP：一个 DDL 命令，它会完全删除整个表，包括其结构、数据和索引。此操作不可逆。

使用 WHERE 子句删除特定行

DELETE 最常见的用途是删除符合特定条件的行。这通过使用 WHERE 子句来实现。省略 WHERE 子句是危险的，因为它将删除表中的所有行。

语法

删除特定行的基本语法是：

DELETE FROM table_name
WHERE condition;

示例

让我们为示例设置一个现代的 employees 表。请注意，主键使用了 SERIAL 或 AUTO_INCREMENT，这是常见做法。

-- PostgreSQL/MySQL 示例表结构
CREATE TABLE employees (
    id SERIAL PRIMARY KEY, -- MySQL 请使用 AUTO_INCREMENT
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary NUMERIC(10, 2),
    is_active BOOLEAN DEFAULT TRUE
);

-- 插入示例数据
INSERT INTO employees (first_name, last_name, department, salary, is_active) VALUES
('Ramesh', 'Fadatare', 'Engineering', 75000.00, TRUE),
('Khilan', 'Patel', 'Engineering', 68000.00, TRUE),
('Kaushik', 'Das', 'Marketing', 65000.00, TRUE),
('Chaitali', 'Gupta', 'Sales', 82000.00, FALSE),
('Hardik', 'Pandya', 'Sales', 85000.00, TRUE),
('Komal', 'Sharma', 'HR', 55000.00, TRUE),
('Muffy', 'Moon', 'Marketing', 71000.00, FALSE);

假设 Hardik Pandya 已经离开了公司。我们需要从 employees 表中删除他的记录。

DELETE FROM employees
WHERE first_name = 'Hardik' AND last_name = 'Pandya';

输出

Query OK, 1 row affected.

验证

您可以通过运行 SELECT 查询来验证删除。请注意，ID 为 5 的记录已消失。

SELECT * FROM employees;

结果表中将不再包含 Hardik 的记录。

使用多个条件删除

您可以使用逻辑运算符（如 AND 和 OR）创建更复杂的删除条件。这对于数据清理很有用，例如，从销售部门中删除所有不活跃的员工。

语法

DELETE FROM table_name
WHERE condition1 AND (condition2 OR condition3);

示例

让我们删除所有被标记为不活跃的员工（is_active = FALSE）。在我们的示例数据中，这适用于 Chaitali 和 Muffy。

DELETE FROM employees
WHERE is_active = FALSE;

输出

Query OK, 2 rows affected.

验证

运行 SELECT * FROM employees; 查询现在将显示只剩下活跃员工。

从表中删除所有行

要删除表中的所有记录，您可以使用不带 WHERE 子句的 DELETE 命令。请务必谨慎使用此命令。

-- 这将删除 employees 表中的所有记录。
DELETE FROM employees;

为此目的，TRUNCATE TABLE 通常是更好的选择。

性能：DELETE vs. TRUNCATE

删除所有行时，TRUNCATE TABLE employees; 比 DELETE FROM employees; 快得多。这是因为 DELETE 逐行删除并记录每次删除，而 TRUNCATE 则通过一次性、最小化日志记录的操作来释放数据页。然而，TRUNCATE 不易回滚，也不会触发 ON DELETE 触发器。

特性	DELETE	TRUNCATE
能否使用 WHERE 子句	能	否
是否触发 DELETE 触发器	是	否
性能	较慢（逐行）	较快（释放数据页）
事务性	是（可回滚）	部分（在某些系统中不可回滚）
是否重置自增列	否	是（在大多数系统中）

安全第一：删除数据的最佳实践

意外删除数据可能会造成灾难性后果。请始终遵循以下最佳实践：

• 删除前预览： 在运行 DELETE 语句之前，编写一个具有完全相同 WHERE 子句的 SELECT 语句，以查看将要删除的行。例如：SELECT * FROM employees WHERE is_active = FALSE;
• 使用事务： 将 DELETE 语句包装在一个事务中。这允许您审查更改，并根据需要 COMMIT（提交）使其永久生效，或者在出错时 ROLLBACK（回滚）以撤销删除。
• 定期备份： 确保您有可靠的备份和恢复策略。这是您最终的安全保障。
• 使用特定条件： 在 WHERE 子句中尽可能具体。使用唯一的标识符（如 id）是定位单行最安全的方法。

以下是使用事务进行安全删除的示例：

BEGIN TRANSACTION;

-- 要测试的删除语句
DELETE FROM employees WHERE department = 'HR';

-- 此时，您可以运行 SELECT 语句以在此事务中验证更改
-- SELECT * FROM employees;

-- 如果正确，则永久提交
-- COMMIT;

-- 如果不正确，则撤销更改
-- ROLLBACK;