如何在 SQL 中删除数据

介绍

在 Structured Query Language,更常被称为 SQL,‘DELETE’声明是用户可用的最强大的操作之一。 正如其名称所暗示的那样,‘DELETE’操作可以不可逆转地从数据库表中删除一个或多个数据行。 作为数据管理的一个基本方面,SQL用户必须了解‘DELETE’声明是如何工作的。

本指南将介绍如何使用 SQL 的DELETE语法从一个或多个表中删除数据,并解释 SQL 如何处理与外部密钥限制相冲突的DELETE操作。

前提条件

要遵循本指南,您需要运行某种类型的关系数据库管理系统(RDBMS)的计算机,该系统使用SQL。

  • 运行 Ubuntu 20.04 的服务器,有一个拥有行政权限的非根基用户和一个配置有 UFW 的防火墙,正如我们[初始的 Ubuntu 20.04 服务器设置指南(https://andsky.com/tech/tutorials/initial-server-setup-with-ubuntu-20-04)所描述的那样.
  • MySQL在服务器上安装并安全,概要载于[How To Install MySQL on Ubuntu 20.04] (https://andsky.com/tech/tutorials/how-to-install-mysql-on-ubuntu-20-04). 此指南由非根基 MySQL 用户进行校验,该用户使用 Step 3** 所描述的过程创建. .

注意:请注意,许多RDBMS使用自己的独特的SQL实现,虽然本教程中描述的命令将在大多数RDBMS上工作,但如果您在MySQL以外的系统上测试它们,精确的语法或输出可能会有所不同。

我们鼓励您阅读以下 连接到MySQL和设置示例数据库 部分,以了解如何创建数据库的详细信息,以及本指南将在整个示例中使用的两个表。

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

如果您的 SQL 数据库系统在远程服务器上运行,则从本地计算机输入 SSH 到服务器:

1[environment local]
2ssh sammy@your_server_ip

然后打开MySQL服务器提示,用您的MySQL用户帐户的名称代替sammy:

1mysql -u sammy -p

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

1CREATE DATABASE deleteDB;

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

1[secondary_label Output]
2Query OK, 1 row affected (0.01 sec)

若要選擇「deleteDB」資料庫,請執行下列「USE」聲明:

1USE deleteDB;

1[secondary_label Output]
2Database changed

选择删除DB数据库后,在其内创建一对表,例如,想象你和一些朋友开始一个俱乐部,其中成员可以彼此共享音乐设备。

  • 成员':每个俱乐部成员的身份号码,用int'数据类型表示。 本栏还将作为表上的主键.
  • 名称':每个成员的名称,使用最多30个字符的varchar'数据类型表示。
  • " homeBorough " :本栏将储存每个成员居住的区,再次使用 " varchar " 数据类型表示,但最多只有15个字符
  • " 电子邮件 " :可联系每个成员的电子邮件地址,使用最多30个字符的 " varchar " 数据类型表示

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

1CREATE TABLE clubMembers (
2memberID int PRIMARY KEY,
3name varchar(30),
4homeBorough varchar(15),
5email varchar(30)
6);

下一个表将有以下列:

  • 设备 ' :每件设备的独特标识。 本栏中的值将是int数据类型。 与成员表中的成员`一栏一样,这一栏将作为表格的主要关键。
  • 设备Type':每行代表何种类型的仪器或工具(例如guitar'、混合器'、放大器'等)。 这些值将使用最多为30个字符的 " varchar " 数据类型表示
  • " 品牌 " :生产每件设备的品牌,再次使用最多30个字符的 " varchar " 数据类型表示
  • `所有者ID':本栏以整数表示拥有设备的俱乐部成员的身份证号码。 .

为了确保所有者ID列只持有代表有效成员ID号码的值,您可以创建一个_外键_限制,引用俱乐部成员表格中的成员ID一栏。 外国的关键限制是表达两个表格之间关系的一种方式。 外国密钥这样做,要求其适用的一栏中的数值必须在其引用的一栏中已经存在。 在下面的例子中,外国的关键限制要求 " 所有人ID " 一栏中的任何增加值必须已经存在.

创建一个使用这些列和这个限制名为clubEquipment的表:

1CREATE TABLE clubEquipment (
2equipmentID int PRIMARY KEY,
3equipmentType varchar(30),
4brand varchar(15),
5ownerID int,
6CONSTRAINT fk_ownerID
7FOREIGN KEY (ownerID) REFERENCES clubMembers(memberID)
8);

请注意,此示例为外部密钥限制提供了名称, fk_ownerID. MySQL 将自动生成您添加的任何限制的名称,但在我们需要稍后参考该限制时,在这里定义一个将是有用的。

接下來,執行下列「INSERT INTO」聲明以載入「clubMembers」表,包含六行樣本資料:

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

然后运行另一个INSERT INTO语句来加载clubEquipment表,包含二十行样本数据:

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

有了这一点,您已经准备好遵循本指南的其余部分,并开始学习如何使用 SQL 删除数据。

从单个表中删除数据

SQL 中删除数据的通用语法看起来如下:

1DELETE FROM table_name
2WHERE conditions_apply;

<$>[警告] 警告 :这个语法的重要组成部分是WHERE条款,因为它允许您准确指定哪些数据行应该被删除。

请注意,成功的DELETE操作是不可逆转的。如果您要运行一个操作而不确切地知道它会删除哪些数据,您可能会意外删除错误的记录。

例如,假设您想要删除与 Korgi 品牌制作的音乐设备相关的任何记录,但为了保持安全,您首先决定写一个查询,以查看他们的品牌列表中的设备记录Korgi。

要查找您的表中的哪些工具是由 Korg 创建的,您可以运行以下查询。 请注意,与SELECT查询或INSERT INTO操作不同,DELETE操作不允许您指定单个列,因为它们旨在删除整个数据行。

1SELECT * FROM clubEquipment
2WHERE brand = 'Korgi';

此查询返回clubEquipment表中的每个列,但只返回具有品牌列含有值Korgi的行:

1[secondary_label Output]
2+-------------+---------------+-------+---------+
3| equipmentID | equipmentType | brand | ownerID |
4+-------------+---------------+-------+---------+
5|           8 | synthesizer   | Korgi |       4 |
6+-------------+---------------+-------+---------+
71 row in set (0.00 sec)

要删除此行,您将运行一个删除操作,该操作具有与之前的选择陈述相同的从和哪里条款:

1DELETE FROM clubEquipment
2WHERE brand = 'Korgi';

1[secondary_label Output]
2Query OK, 1 row affected (0.01 sec)

此输出表示删除操作只影响一个行,但是,您可以使用返回多个行的任何WHERE条款删除多个数据行。

下面的SELECT查询返回了clubEquipment表中的每个记录,其equipmentType列中包含电气一词:

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

 1[secondary_label Output]
 2+-------------+------------------------+-----------+---------+
 3| equipmentID | equipmentType          | brand     | ownerID |
 4+-------------+------------------------+-----------+---------+
 5|           1 | electric guitar        | Gilled    |       6 |
 6|          11 | acoustic/electric bass | Pepiphone |       2 |
 7|          14 | electric guitar        | Vax       |       6 |
 8|          16 | electric organ         | Spammond  |       1 |
 9+-------------+------------------------+-----------+---------+
104 rows in set (0.00 sec)

再次,要删除这四个记录,重写此查询操作,但用DELETE取代SELECT * :

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

1[secondary_label Output]
2Query OK, 4 rows affected (0.00 sec)

您还可以使用 subqueries 返回和删除更多细微的结果集。 子查询是完整的查询操作,即以SELECT开头并包含FROM条款的 SQL 语句,嵌入到另一个操作中,然后是周围操作的FROM条款。

例如,假设您想要删除任何拥有字母L的会员的clubEquipment表中列出的设备。

1SELECT *
2FROM clubEquipment
3WHERE ownerID IN
4(SELECT memberID FROM clubMembers
5WHERE name LIKE 'L%');

此操作返回来自clubEquipment表的每个行,其ownerID列出现在第四行开始的子查询返回的值中。

 1[secondary_label Output]
 2+-------------+------------------+-----------+---------+
 3| equipmentID | equipmentType    | brand     | ownerID |
 4+-------------+------------------+-----------+---------+
 5|          12 | trombone         | Cann      |       2 |
 6|          19 | cello            | Yemehe    |       2 |
 7|           3 | drum kit         | Purl      |       3 |
 8|           4 | mixer            | Bearinger |       3 |
 9|          10 | keytar           | Poland    |       3 |
10|          18 | guitar amplifier | Fandar    |       3 |
11+-------------+------------------+-----------+---------+
126 rows in set (0.00 sec)

然后,您可以使用以下DELETE声明删除这些数据:

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

1[secondary_label Output]
2Query OK, 6 rows affected (0.01 sec)

从多个表中删除数据

您可以通过添加一个JOIN条款在单个操作中从多个表中删除数据。

「JOIN」条款用于将来自两个或多个表的行组合成一个查询结果,他们通过在表之间找到相关列并在输出中对结果进行适当的排序来完成此操作。

包含JOIN条款的DELETE操作的语法看起来如下:

1DELETE table_1, table_2
2FROM table_1 JOIN table_2
3ON table_2.related_column = table_1.related_column
4WHERE conditions_apply;

请注意,因为JOIN条款比较了多个表的内容,所以本示例语法指定了从哪个表选择每个列,以表的名称和期限为前列的名称。

若要用JOIN条款说明删除数据,请假设您的俱乐部决定限制音乐设备成员的品牌可以共享的内容。 运行下面的陈述,创建一个名为prohibitedBrands的表格,在其中列出哪些品牌不再被俱乐部接受。

1CREATE TABLE prohibitedBrands (
2brandName varchar(30),
3homeCountry varchar(30)
4);

然后加载此新表格,并提供一些样本数据:

1INSERT INTO prohibitedBrands
2VALUES 
3('Fandar', 'USA'),
4('Givson', 'USA'),
5('Muug', 'USA'),
6('Peabey', 'USA'),
7('Yemehe', 'Japan');

随后,俱乐部决定从俱乐部设备表中删除任何设备记录,其品牌出现在禁止品牌表中,并位于美国。

此操作将clubEquipment和prohibitedBrands表合并在一起,只返回品牌和品牌名列共享共同值的行。

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

1[secondary_label Output]
2+-------------+---------------+--------+---------+-----------+-------------+
3| equipmentID | equipmentType | brand  | ownerID | brandName | homeCountry |
4+-------------+---------------+--------+---------+-----------+-------------+
5|           6 | bass guitar   | Fandar |       4 | Fandar    | USA         |
6|          17 | bass guitar   | Peabey |       1 | Peabey    | USA         |
7+-------------+---------------+--------+---------+-----------+-------------+
82 rows in set (0.00 sec)

这是我们正在寻找的所有信息,即每个位于美国的品牌在禁止品牌表中,这也出现在俱乐部设备表中。

若要從「prohbitedBrands」表和「clubEquipment」的相關裝置中刪除這些品牌,請重寫之前的「SELECT」聲明,但用「DELETE」取代「SELECT * 」以及兩個表的名稱:

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

1[secondary_label Output]
2Query OK, 4 rows affected (0.01 sec)

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

更改外部密钥删除行为

默认情况下,任何可能导致与外部密钥发生冲突的DELETE陈述都会失败。

从连接到MySQL和设置示例数据库的前提条件部分中回忆一下,clubEquipment表的ownerID列是指clubEquipment表的ownerID列的外部密钥,这意味着ownerID列中输入的任何值必须已经存在于memberID列中。

如果您尝试从clubMembers表中删除一个数据行,其memberID值在ownerID列中的任何地方使用,则会导致错误:

1DELETE FROM clubMembers
2WHERE memberID = 6;

1[secondary_label Output]
2ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

您可以通过首先删除子表中的任何行(本示例中的‘clubEquipment’)来避免此错误,其中母表中存在外钥值(‘clubMembers’)。

或者,您可以通过替换现有外部密钥限制,以一种以不同的方式对待DELETE操作来更改这种行为。

注:不是每个数据库管理系统或引擎允许您在以下段落中描述的现有表中添加或删除限制。

要取代当前的限制,您必须先用ALTER TABLE语句删除它,请记住,在clubEquipment的CREATE TABLE语句中,我们将fk_ownerID定义为表的外部密钥限制的名称:

1ALTER TABLE clubEquipment
2DROP FOREIGN KEY fk_ownerID;

1[secondary_label Output]
2Query OK, 0 rows affected (0.01 sec)
3Records: 0 Duplicates: 0 Warnings: 0

然后,创建一个新的外部密钥限制,该限制被配置为对待DELETE操作,以便对所述使用情况有意义。

  • ON DELETE SET NULL:此选项将允许您从母表中删除记录,并将重置将其引用为 NULL的子表中的任何值。

对于本示例而言,ON DELETE SET NULL没有意义.如果一个会员离开俱乐部并从clubMembers表中删除他们的记录,他们的设备不再可供其他会员使用,因此应该从clubEquipment表中删除。

若要添加遵循ON DELETE CASCADE行为的外钥约束,请运行以下ALTER TABLE声明,从而创建一个名为newfk_ownerID的新约束,该约束复制了以前的外钥定义,但包含了ON DELETE CASCADE选项:

1ALTER TABLE clubEquipment
2ADD CONSTRAINT newfk_ownerID
3FOREIGN KEY (ownerID)
4REFERENCES clubMembers(memberID)
5ON DELETE CASCADE;

1[secondary_label Output]
2Query OK, 7 rows affected (0.07 sec)
3Records: 7 Duplicates: 0 Warnings: 0

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

注:而不是更改表的定义以更改外部密钥处理DELETE操作的方式,您可以在CREATE TABLE声明中从一开始就定义此行为:

1CREATE TABLE clubEquipment (
2equipmentID int PRIMARY KEY,
3equipmentType varchar(30),
4brand varchar(15),
5ownerID int,
6CONSTRAINT fk_ownerID
7FOREIGN KEY (ownerID) REFERENCES clubMembers(memberID)
8ON DELETE CASCADE
9);

美元

接下来,您将能够从clubMembers表中删除任何记录,并且在clubEquipment表中引用的任何行也将被删除:

1DELETE FROM clubMembers
2WHERE memberID = 6;

1[secondary_label Output]
2Query OK, 1 row affected (0.00 sec)

虽然此输出表示它只影响了一行,但它也将删除clubEquipment表中的任何设备记录,这些设备列出其ownerID值为6。

结论

通过阅读本指南,您了解如何使用DELETE语句从一个或多个表中删除数据,您还了解了 SQL 如何处理与外部密钥限制相冲突的DELETE操作,以及如何更改此默认行为。

请记住,每个 SQL 数据库都使用其独特的语言实现,因此,您应该咨询您的 DBMS 的官方文档,以便更全面地描述如何处理DELETE陈述以及可用的选项。

如果您想了解更多有关使用SQL的工作,我们鼓励您查看本系列中的其他教程在 如何使用SQL。

Published At
Categories with 技术
Tagged with
comments powered by Disqus