如何使用SQL系列 之 如何删除SQL中的数据

简介

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

在结构化查询语言中，通常被称为SQL， DELETE语句是用户可用的最强大的操作之一。顾名思义，DELETE操作从数据库表中不可逆地删除一行或多行数据。作为数据管理的基本方面，对于SQL用户来说，理解DELETE语句的工作原理是很重要的。

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

前期准备

为了学习本指南，你需要一台运行某种使用SQL的关系数据库管理系统(RDBMS)的计算机。

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

你还需要一个数据库和表装载一些示例数据，您可以使用练习删除数据。

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

如果SQL数据库系统运行在远程服务器上，请从本地设备SSH到服务器:

ssh sammy@your_server_ip

然后打开MySQL服务器提示符，将==sammy==替换为你的MySQL用户账户的名称:

mysql -u sammy -p

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

CREATE DATABASE deleteDB;

如果数据库成功创建,您将收到这样的输出:

OutputQuery OK, 1 row affected (0.01 sec)

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

USE deleteDB;

OutputDatabase changed

选择deleteDB数据库后，在其中创建两个表。举个例子，假设你和你的一些朋友创办了一个俱乐部，俱乐部成员可以互相分享音乐设备。为了帮助您跟踪俱乐部成员和他们的设备，您决定创建几个表格。第一个表包含以下4列。

• 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)
);

下面创建下一张表，表名为clubeequipment，包含字段如下：

• equipmentID：每件音乐设备的唯一标识符。这列中的值将是int数据类型。根clubMembers表中的memberID列一样，作为表的主键
• equipmentType：每一行代表的乐器或工具类型(例如guitar, mixer, amplifier等)。这些值将使用不超过30个字符的varchar数据类型来表示
• brand：生产每件设备的品牌，同样使用varchar数据类型表示，最多30个字符
• ownerID：这一列将保存拥有该设备的俱乐部成员的ID号，用整数表示。

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

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语句插入6条数据到clubMembers表：

INSERT INTO clubMembers
VALUES
(1, 'Rosetta', 'Manhattan', 'hightower@example.com'),
(2, 'Linda', 'Staten Island', 'lyndell@example.com'),
(3, 'Labi', 'Brooklyn', 'siffre@example.com'),
(4, 'Bettye', 'Queens', 'lavette@example.com'),
(5, 'Phoebe', 'Bronx', 'snow@example.com'),
(6, 'Mariya', 'Brooklyn', 'takeuchi@example.com');

然后运行另一个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品牌的音乐设备有关的记录。为了安全起见，你决定先写一个查询，看看到底哪些设备记录在brand列中出了“Korgi”。

要查找表中哪些仪器是由Korgi制作的，可以运行以下查询。请注意，与SELECT查询或INSERT INTO操作不同，DELETE操作不允许指定单个列，因为它们旨在删除整行数据。为了模仿这种行为，这个查询跟在SELECT关键字后面带一个星号(*)，它是SQL的简写，代表显示所有列:

SELECT * FROM clubEquipment
WHERE brand = 'Korgi';

这个查询返回clubeequipment表中的所有列，但只返回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';

OutputQuery OK, 1 row affected (0.01 sec)

此输出表明DELETE操作只影响单行。然而，您可以使用任何WHERE条件删除多行数据,此处将返回多行。

下面的SELECT查询返回clubeequipment表中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%';

OutputQuery 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%');

这个操作返回clubeequipment表中的每一行，其ownerID列出现在从第4行开始的子查询返回的值中。这个子查询返回name值以“L”开头的任何记录的memberID:

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%');

OutputQuery OK, 6 rows affected (0.01 sec)

从多个表中删除数据

通过使用JOIN子句，你可以在一次操作删除多个表中的数据。

JOIN子句用于将两个或多个表中的行合并为单个查询结果。它们通过在表之间找到一个相关列，并在输出中对结果进行适当排序来实现这一点。

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

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语句来查询这些数据。这个操作将clubeequipment和prohibiedbrands表连接在一起，只返回brand和brandName列具有相同值的行。WHERE子句进一步细化了结果集，排除了homecount列中不包含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表中的每个美国品牌。

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

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

OutputQuery OK, 4 rows affected (0.01 sec)

这个输出表明操作删除了四行数据:两行来自clubeequipment，两行来自prohibitedBrands。如果你只想从clubeequipment表中删除记录，并维护prohibitedBrands表中的所有记录，则只需要在DELETE关键字后面列出clubeequipment，反之亦然。

更改外键DELETE的行为

默认情况下，任何会导致与外键冲突的DELETE语句都会失败。
如果你试图从clubMembers表中删除memberID值在ownerID列的任何地方都被使用的数据，将会导致错误：

DELETE FROM clubMembers
WHERE memberID = 6;

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

你可以避免这个错误,首先删除子表中的行数(在这个例子中clubEquipment)在父表中存在外键值(clubMembers)。或者，你可以通过替换现有的外键约束来改变这种行为，用不同的方式对待DELETE操作。

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

要替换当前的约束，你必须首先用ALTER TABLE语句删除它。回想一下，在clubeequipment的CREATE TABLE语句中，我们定义了fk_ownerID作为表的外键约束的名称：

ALTER TABLE clubEquipment
DROP FOREIGN KEY fk_ownerID;

OutputQuery 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;

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

此输出表明它影响了clubeequipment表中所有剩余的7行。
注意：你可以像下面这样从一开始就在CREATE table 语句中定义这种行为，而不是通过改变表的定义来改变外键如何处理DELETE操作:

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表中的任何记录，clubeequipment表中引用它的任何行也将被删除：

DELETE FROM clubMembers
WHERE memberID = 6;

OutputQuery OK, 1 row affected (0.00 sec)

虽然这个输出说它只影响一行，但它也会删除clubeequipment表中所有ownerID值为6的设备记录。

总结

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

这里列出的命令应该适用于任何使用SQL的数据库管理系统。记住，每个SQL数据库都使用自己独特的语言实现，因此你应该查阅相应DBMS的官方文档，以更完整地描述它如何处理DELETE语句以及它有哪些可用选项。