介绍

在Structured Query Language 中，通常称为SQL，DELETE语句是用户可用的最强大的操作之一。顾名思义，DELETE操作不可逆地从数据库表中删除一行或多行数据。作为数据管理的一个基本方面，SQL 用户了解DELETE语句的工作方式非常重要。

本指南将介绍如何使用 SQL 的DELETE语法从一个或多个表中删除数据。它还将解释 SQL 如何处理DELETE与外键约束冲突的操作。

先决条件

为了遵循本指南，您需要一台运行某种类型的使用 SQL 的关系数据库管理系统 (RDBMS) 的计算机。本指南中的说明和示例已使用以下环境进行验证：

• 运行 Ubuntu 20.04 的服务器，具有管理权限的非 root 用户和使用 UFW 配置的防火墙，如我们的 Ubuntu 20.04 初始服务器设置指南中所述。
• 在服务器上安装并保护 MySQL，如如何在 Ubuntu 20.04 上安装 MySQL 中所述。本指南已使用非 root MySQL 用户进行验证，该用户使用步骤 3中描述的过程创建。

注意：请注意，许多 RDBMS 使用自己独特的 SQL 实现。尽管本教程中概述的命令适用于大多数 RDBMS，但如果您在 MySQL 以外的系统上测试它们，则确切的语法或输出可能会有所不同。

您还需要一个加载了一些示例数据的数据库和表，您可以使用它们来练习删除数据。我们鼓励您阅读以下连接到 MySQL 和设置示例数据库部分，详细了解如何创建本指南将在整个示例中使用的数据库和两个表。

连接到 MySQL 并设置示例数据库

如果您的 SQL 数据库系统在远程服务器上运行，请从本地计算机通过 SSH 连接到您的服务器：

ssh sammy@your_server_ip

然后打开 MySQL 服务器提示，替换sammy为您的 MySQL 用户帐户的名称：

mysql -u sammy -p

创建一个名为 的数据库deleteDB：

CREATE DATABASE deleteDB;

如果数据库创建成功，您将收到如下输出：

Output
Query OK, 1 row affected (0.01 sec)

要选择deleteDB数据库，请运行以下USE语句：

USE deleteDB;

Output
Database changed

选择deleteDB数据库后，在其中创建几个表。例如，假设您和您的一些朋友成立了一个俱乐部，成员可以在其中共享音乐设备。为了帮助您跟踪俱乐部成员及其设备，您决定创建几个表。第一个表将包含以下四列：

• memberID：每个俱乐部会员的标识号，用int数据类型表示。此列也将作为表的主键
• name：每个成员的名字，用varchar最多30个字符的数据类型表示
• homeBorough: 此列将存储每个成员居住的行政区，再次使用varchar数据类型表示，但最多只有 15 个字符
• email：可以联系每个成员的电子邮件地址，使用varchar最多 30 个字符的数据类型表示

创建一个名为的表clubMembers，其中包含以下四列：

CREATE TABLE clubMembers (
memberID int PRIMARY KEY,
name varchar(30),
homeBorough varchar(15),
email varchar(30)
);

下一个表将包含以下列：

• equipmentID：每件设备的唯一标识符。此列中的值将属于int数据类型。与表中的memberID列一样clubMembers，此列将作为表的主键
• equipmentType：每行代表什么类型的仪器或工具（例如guitar，mixer、amplifier、 等）。这些值将使用varchar最多 30 个字符的数据类型表示
• brand：生产每台设备的品牌，再次使用varchar最多30个字符的数据类型表示
• ownerID：此栏将保存拥有该设备的俱乐部会员的 ID 号，以整数表示。

为了确保 ownerID 列只包含代表有效成员 ID 号的值，您可以创建一个引用表列的外键约束。外键约束是表达两个表之间关系的一种方式。外键通过要求它应用的列中的值必须已经存在于它引用的列中来实现这一点。在以下示例中，外键约束要求添加到该列的任何值必须已存在于该列中。clubMembermemberIDownerIDmemberID

创建一个包含这些列和名为 的约束的表clubEquipment：

CREATE TABLE clubEquipment (
equipmentID int PRIMARY KEY,
equipmentType varchar(30),
brand varchar(15),
ownerID int,
CONSTRAINT fk_ownerID
FOREIGN KEY (ownerID) REFERENCES clubMembers(memberID)
);

请注意，此示例提供了外键约束的名称fk_ownerID. MySQL 会自动为您添加的任何约束生成一个名称，但是当我们稍后需要引用此约束时，在此处定义一个名称会很有用。

接下来，运行以下INSERT INTO语句以加载clubMembers具有六行示例数据的表：

INSERT INTO clubMembers
VALUES
(1, 'Rosetta', 'Manhattan', '[email protected]'),
(2, 'Linda', 'Staten Island', '[email protected]'),
(3, 'Labi', 'Brooklyn', '[email protected]'),
(4, 'Bettye', 'Queens', '[email protected]'),
(5, 'Phoebe', 'Bronx', '[email protected]'),
(6, 'Mariya', 'Brooklyn', '[email protected]');

然后运行另一个INSERT INTO语句来加载clubEquipment包含 20 行示例数据的表：

INSERT INTO clubEquipment
VALUES
(1, 'electric guitar', 'Gilled', 6),
(2, 'trumpet', 'Yemehe', 5),
(3, 'drum kit', 'Purl', 3),
(4, 'mixer', 'Bearinger', 3),
(5, 'microphone', 'Sure', 1),
(6, 'bass guitar', 'Fandar', 4),
(7, 'acoustic guitar', 'Marten', 6),
(8, 'synthesizer', 'Korgi', 4),
(9, 'guitar amplifier', 'Vax', 4),
(10, 'keytar', 'Poland', 3),
(11, 'acoustic/electric bass', 'Pepiphone', 2),
(12, 'trombone', 'Cann', 2),
(13, 'mandolin', 'Rouge', 1),
(14, 'electric guitar', 'Vax', 6),
(15, 'accordion', 'Nonher', 5),
(16, 'electric organ', 'Spammond', 1),
(17, 'bass guitar', 'Peabey', 1),
(18, 'guitar amplifier', 'Fandar', 3),
(19, 'cello', 'Yemehe', 2),
(20, 'PA system', 'Mockville', 5);

这样，您就可以按照指南的其余部分并开始学习如何使用 SQL 删除数据。

从单个表中删除数据

在 SQL 中删除数据的一般语法如下所示：

DELETE FROM table_name
WHERE conditions_apply;

警告：此语法的重要部分是WHERE子句，因为它允许您准确指定应删除哪些数据行。没有它，像这样的命令会正确执行，但它会从表中删除每一行数据。DELETE FROM table_name;

请注意，成功的DELETE手术是不可逆转的。如果您在不确切知道它将删除哪些数据的情况下运行它，您就有可能不小心删除错误的记录。帮助确保您不会意外删除错误数据的一种方法是首先发出SELECT查询以查看DELETE操作WHERE子句将返回哪些数据。

例如，假设您要删除与 Korgi 品牌制作的音乐设备相关的所有记录。为了安全起见，不过，你决定先写一个查询，看看到底是什么设备记录列表Korgi中的brand列。

要查找表中的哪些乐器是由 Korg 制作的，您可以运行以下查询。请注意，与SELECT查询或INSERT INTO操作不同，DELETE操作不允许您指定单个列，因为它们旨在删除整行数据。为了模仿这种行为，这个查询在SELECT关键字后面加上一个星号 ( *)，它是 SQL 的简写，代表“每一列”：

SELECT * FROM clubEquipment
WHERE brand = 'Korgi';

此查询返回表中的每一列clubEquipment，但只返回brand列包含值的行Korgi：

Output
+-------------+---------------+-------+---------+
| equipmentID | equipmentType | brand | ownerID |
+-------------+---------------+-------+---------+
|           8 | synthesizer   | Korgi |       4 |
+-------------+---------------+-------+---------+
1 row in set (0.00 sec)

要删除此行，您将运行一个DELETE具有FROM与WHERE前一条语句相同的和子句的操作SELECT：

DELETE FROM clubEquipment
WHERE brand = 'Korgi';

Output
Query OK, 1 row affected (0.01 sec)

此输出表明该DELETE操作仅影响单行。但是，您可以使用WHERE返回多行的任何子句删除多行数据。

以下SELECT查询返回clubEquipment表中equipmentType列包含单词的每条记录electric：

SELECT * FROM clubEquipment
WHERE equipmentType LIKE '%electric%';

Output
+-------------+------------------------+-----------+---------+
| equipmentID | equipmentType          | brand     | ownerID |
+-------------+------------------------+-----------+---------+
|           1 | electric guitar        | Gilled    |       6 |
|          11 | acoustic/electric bass | Pepiphone |       2 |
|          14 | electric guitar        | Vax       |       6 |
|          16 | electric organ         | Spammond  |       1 |
+-------------+------------------------+-----------+---------+
4 rows in set (0.00 sec)

同样，要删除这四个记录，请重写此查询操作，但替换SELECT *为DELETE：

DELETE FROM clubEquipment
WHERE equipmentType LIKE '%electric%';

Output
Query OK, 4 rows affected (0.00 sec)

您还可以使用子查询来返回和删除更细粒度的结果集。子查询是一个完整的查询操作——意思是，一个以SELECT一个FROM子句开头并包含一个子句的 SQL 语句——嵌入在另一个操作中，跟在周围操作自己的FROM子句之后。

例如，假设您要删除clubEquipment名称以字母“L”开头的任何成员拥有的表中列出的任何设备。您可以首先使用如下语句查询此数据：

SELECT *
FROM clubEquipment
WHERE ownerID IN
(SELECT memberID FROM clubMembers
WHERE name LIKE 'L%');

此操作返回clubEquipment表中的每一行，其ownerID列出现在从第四行开始的子查询返回的值中。此子查询返回 memberID of any record whosename` 值以“L”开头：

Output
+-------------+------------------+-----------+---------+
| equipmentID | equipmentType    | brand     | ownerID |
+-------------+------------------+-----------+---------+
|          12 | trombone         | Cann      |       2 |
|          19 | cello            | Yemehe    |       2 |
|           3 | drum kit         | Purl      |       3 |
|           4 | mixer            | Bearinger |       3 |
|          10 | keytar           | Poland    |       3 |
|          18 | guitar amplifier | Fandar    |       3 |
+-------------+------------------+-----------+---------+
6 rows in set (0.00 sec)

然后，您可以使用以下DELETE语句删除此数据：

DELETE FROM clubEquipment
WHERE ownerID IN
(SELECT memberID FROM clubMembers
WHERE name LIKE 'L%');

Output
Query OK, 6 rows affected (0.01 sec)

从多个表中删除数据

通过包含JOIN子句，您可以在一次操作中从多个表中删除数据。

JOIN子句用于将来自两个或多个表的行组合成单个查询结果。他们通过查找表之间的相关列并在输出中对结果进行适当排序来实现此目的。

DELETE包含JOIN子句的操作的语法如下所示：

DELETE table_1, table_2
FROM table_1 JOIN table_2
ON table_2.related_column = table_1.related_column
WHERE conditions_apply;

请注意，由于JOIN子句比较多个表的内容，因此此示例语法通过在列名称前面加上表名称和句点来指定从哪个表中选择每一列。这称为完全限定的列引用。您可以像这样为任何操作指定应该从哪个表中选择列，尽管像我们在前面的示例中所做的那样仅从单个表中选择时没有必要。

为了说明使用JOIN条款删除数据，假设您的俱乐部决定限制会员可以共享的音乐设备品牌。运行以下语句以创建一个名为的表prohibitedBrands，您将在其中列出俱乐部不再接受的品牌。该表只有两列，均使用varchar数据类型，用于保存每个品牌的名称及其在哪个国家/地区开展业务：

CREATE TABLE prohibitedBrands (
brandName varchar(30),
homeCountry varchar(30)
);

然后用一些示例数据加载这个新表：

INSERT INTO prohibitedBrands
VALUES 
('Fandar', 'USA'),
('Givson', 'USA'),
('Muug', 'USA'),
('Peabey', 'USA'),
('Yemehe', 'Japan');

之后，俱乐部决定从clubEquipment表中删除其品牌出现在prohibitedBrands表中且位于美国的任何设备记录。

您可以使用类似以下SELECT语句的操作来查询此数据。此操作将clubEquipment和prohibitedBrands表连接在一起，仅返回其brand和brandName列共享公共值的行。该WHERE子句通过排除homeCountry列不包含USA作为其值的任何品牌来进一步优化此结果集：

SELECT * 
FROM clubEquipment JOIN prohibitedBrands
ON clubEquipment.brand = prohibitedBrands.brandName
WHERE homeCountry = 'USA';

Output
+-------------+---------------+--------+---------+-----------+-------------+
| equipmentID | equipmentType | brand  | ownerID | brandName | homeCountry |
+-------------+---------------+--------+---------+-----------+-------------+
|           6 | bass guitar   | Fandar |       4 | Fandar    | USA         |
|          17 | bass guitar   | Peabey |       1 | Peabey    | USA         |
+-------------+---------------+--------+---------+-----------+-------------+
2 rows in set (0.00 sec)

这就是我们正在寻找的所有信息；即，prohibitedBrands表中也出现在clubEquipment表中的每个美国品牌。

要从prohbitedBrands表中删除这些品牌以及从 中删除相关设备clubEquipment，请重写前面的SELECT语句，但替换SELECT *为DELETE后跟两个表的名称：

DELETE clubEquipment, prohibitedBrands
FROM clubEquipment JOIN prohibitedBrands
ON clubEquipment.brand = prohibitedBrands.brandName
WHERE homeCountry = 'USA';

Output
Query OK, 4 rows affected (0.01 sec)

此输出表明该操作删除了四行数据：两行来自clubEquipment和两行来自prohibitedBrands。如果只想删除clubEquipment表中的记录并保留表中的所有记录，prohibitedBrands则只clubEquipment在DELETE关键字后面列出，反之亦然。

改变外键DELETE行为

默认情况下，任何DELETE会导致与外键冲突的语句都将失败。

从召回连接MySQL，建立样本数据库的前提条件一节中ownerID的列clubEquipment表的外键引用ownerID的列clubEquipmentTABL。这意味着输入到该ownerID列中的任何值都必须已经存在于该memberID列中。

如果试图从clubMembers表中删除其memberID值在ownerID列中任何位置使用的一行数据，则会导致错误：

DELETE FROM clubMembers
WHERE memberID = 6;

Output
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

您可以通过首先删除子表（clubEquipment在此示例中）中外键值存在于父表 ( clubMembers)中的任何行来避免此错误。

或者，您可以通过将现有外键约束替换为以DELETE不同方式处理操作的外键约束来更改此行为。

注意：并非每个数据库管理系统或引擎都允许您在现有表中添加或删除约束，如以下段落所述。如果您使用的是 MySQL 以外的 RDBMS，您应该查阅其官方文档以了解它在管理约束方面有哪些限制。

要替换当前约束，您必须首先使用ALTER TABLE语句将其删除。回想一下，在CREATE TABLEfor 语句中clubEquipment，我们定义fk_ownerID了表的外键约束的名称：

ALTER TABLE clubEquipment
DROP FOREIGN KEY fk_ownerID;

Output
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

然后，创建一个新的外键约束，该约束被配置为DELETE以对给定用例有意义的方式处理操作。除了禁止DELETE违反外键的语句的默认设置外，大多数 RDBMS 上还有两个其他选项可用：

• ON DELETE SET NULL：此选项将允许您从父表中删除记录，并将子表中引用它们的所有值重置为NULL.
• ON DELETE CASCADE: 当您删除父表中的一行时，此选项将导致 SQL 自动删除子表中引用它的任何记录。

就本示例而言，ON DELETE SET NULL没有意义。如果会员离开俱乐部并且他们的记录从桌子上删除，clubMembers其余会员将不再使用他们的设备，因此应从桌子上删除clubEquipment。因此，该ON DELETE CASCADE选项对我们的目的更有意义。

要添加遵循该ON DELETE CASCADE行为的外键约束，请运行以下ALTER TABLE语句。这将创建一个名为的新约束newfk_ownerID，该约束复制先前的外键定义，但包括ON DELETE CASCADE选项：

ALTER TABLE clubEquipment
ADD CONSTRAINT newfk_ownerID
FOREIGN KEY (ownerID)
REFERENCES clubMembers(memberID)
ON DELETE CASCADE;

Output
Query OK, 7 rows affected (0.07 sec)
Records: 7  Duplicates: 0  Warnings: 0

此输出表明它影响了clubEquipment表中所有剩余的七行。

注意：DELETE您可以从一开始就在CREATE TABLE语句中定义此行为，而不是更改表的定义以更改外键处理操作的方式，如下所示：

CREATE TABLE clubEquipment (
equipmentID int PRIMARY KEY,
equipmentType varchar(30),
brand varchar(15),
ownerID int,
CONSTRAINT fk_ownerID
FOREIGN KEY (ownerID) REFERENCES clubMembers(memberID)
ON DELETE CASCADE
);

之后，您将能够从clubMembers表中删除任何记录，并且clubEquipment表中引用它的任何行也将被删除：

DELETE FROM clubMembers
WHERE memberID = 6;

Output
Query OK, 1 row affected (0.00 sec)

尽管此输出表明它仅影响一行，但它也会删除clubEquipment表中将其ownerID值列为 的所有设备记录6。

结论

通过阅读本指南，您了解了如何使用该DELETE语句从一个或多个表中删除数据。您还学习了 SQL 如何处理DELETE与外键约束冲突的操作，以及如何更改默认行为。

此处概述的命令应该适用于任何使用 SQL 的数据库管理系统。请记住，每个 SQL 数据库都使用自己独特的语言实现，因此您应该查阅 DBMS 的官方文档，以DELETE获取有关它如何处理语句以及可用选项的更完整描述。

如果您想了解有关使用 SQL 的更多信息，我们鼓励您查看本系列中关于如何使用 SQL的其他教程。