Sql 简明教程

SQL - DELETE Query

The SQL DELETE Statement

SQL DELETE 语句用于从现有表中删除记录。为了筛选将被删除的记录（或删除特定记录），我们需要使用 WHERE 子句和 DELETE 语句。

The SQL DELETE Statement is used to delete the records from an existing table. In order to filter the records to be deleted (or, delete particular records), we need to use the WHERE clause along with the DELETE statement.

如果在没有 WHERE 子句的情况下执行 DELETE 语句，它将从表中删除所有记录。

If you execute DELETE statement without a WHERE clause, it will delete all the records from the table.

使用 DELETE 语句，我们可以删除单张表的一行或多行，以及跨多张表的记录。

Using the DELETE statement, we can delete one or more rows of a single table and records across multiple tables.

Syntax

具有 WHERE 子句的 SQL DELETE 查询的基本语法如下：

The basic syntax of the SQL DELETE Query with the WHERE clause is as follows −

DELETE FROM table_name WHERE [condition];

可以使用 AND 或 OR 运算符组合 N 个条件。

You can combine N number of conditions using AND or OR operators.

Example

假设我们已经创建了一个名为 CUSTOMERS 的表，其中包含客户的个人详细信息，包括他们的姓名、年龄、地址和薪水等，如下所示：

Assume we have created a table named CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc. as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

现在，使用如下所示的 INSERT 语句向此表中插入值：

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

该表将被创建为：

The table will be created as −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

以下查询将删除客户 6 的记录。

The following query deletes the record of a customer, whose ID is 6.

DELETE FROM CUSTOMERS WHERE ID = 6;

Output

输出将显示为：

The output will be displayed as −

Query OK, 1 row affected (0.10 sec)

Verification

要验证是否已从表中删除记录，我们需要使用以下所示的 SELECT 查询检索已修改的表 -

To verify whether the record(s) have been deleted from the table, we need to retrieve the modified table using the SELECT query, as shown below −

SELECT * FROM CUSTOMERS;

现在，CUSTOMERS 表将拥有以下记录：

Now, the CUSTOMERS table would have the following records −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
7	Muffy	24	Indore	10000.00

Deleting Multiple Rows

要从表中删除多行，我们需要指定所有要删除的行所满足的必需条件，使用 WHERE 子句。让我们看一个示例 -

To delete multiple rows from a table, we need to specify the required condition(s), that is satisfied by all the rows to be deleted, using the WHERE clause. Let us look at an example −

Example

从相同的 Customers 表中，让我们尝试删除年龄超过 25 岁客户的记录。

From the same Customers table, let us try to delete the records of customers who are over 25 years of age.

DELETE FROM CUSTOMERS WHERE AGE > 25;

Output

输出将显示为：

The output will be displayed as −

Query OK, 2 rows affected (0.06 sec)

Verification

要验证是否已从表中删除了记录，让我们检索已修改的表。为此，使用以下 SELECT 查询 -

To verify whether the record(s) have been deleted from the table or not, let us retrieve the modified table. For that, use the SELECT query below −

SELECT * FROM CUSTOMERS;

上面的查询将生成以下表 -

The query above will produce the following table −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
7	Muffy	24	Indore	10000.00

Deleting All The Records From a Table

如果我们想使用 DELETE 查询从现有表中删除所有记录（截断它），我们只需在不使用 WHERE 子句的情况下运行它。

If we want to DELETE all the records from an existing table (truncate it) using the DELETE query, we simply need to run it without using the WHERE clause.

Example

以下 SQL 查询从 CUSTOMERS 表中删除所有记录 -

Following SQL query removes all the records from the CUSTOMERS table −

DELETE FROM CUSTOMERS;

Output

输出将显示为：

The output will be displayed as −

Query OK, 4 rows affected (0.13 sec)

Verification

要验证是否已从该表中删除所有记录，我们需要再次使用 SELECT 查询检索已修改的表 -

To verify whether all the records have been deleted from the table, we need to retrieve the modified table once again, using the SELECT query −

SELECT * FROM CUSTOMERS;

现在，CUSTOMERS 表将没有任何记录并且会显示以下输出 -

Now, the CUSTOMERS table would not have any record and will show the following output −

Empty set (0.00 sec)

Delete Records in Multiple Tables

SQL 允许我们使用 DELETE 查询从多个表中删除记录。在这里，我们将使用 JOIN 子句将来自多个表（基于公共列）的数据组合在一起。

SQL allows us to delete the records from multiple tables using the DELETE query. In here, we will use the JOIN clause to combine data from multiple tables (based on a common column).

Example

让我们再创建一个名为 ORDERS 的表，其中包含客户下达的订单详细信息。

Let us create another table with name ORDERS which contains the details of the orders made by the customers.

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

使用 INSERT 语句将值插入此表如下所示

Using the INSERT statement, insert values into this table as follows

INSERT INTO ORDERS VALUES
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

创建的表如下所示：

The table created is as shown below −

OID	DATE	CUSTOMER_ID	AMOUNT
102	2009-10-08 00:00:00	3	3000.00
100	2009-10-08 00:00:00	3	1500.00
101	2009-11-20 00:00:00	2	1560.00
103	2008-05-20 00:00:00	4	2060.00

以下 SQL 查询删除了收入超过 2000 且已下订单的客户（从 CUSTOMERS 和 ORDERS 表中）的记录 -

Following SQL query deletes the records of the customers (from the tables CUSTOMERS and ORDERS) who earn more than 2000 and have placed orders −

DELETE CUSTOMERS, ORDERS FROM CUSTOMERS
INNER JOIN ORDERS ON ORDERS.CUSTOMER_ID = CUSTOMERS.ID
WHERE CUSTOMERS.SALARY > 2000;

Output

输出将显示为：

The output will be displayed as −

Query OK, 2 rows affected (0.01 sec)

Verification

要验证是否已从表中删除记录，我们需要再次使用 SELECT 查询检索已修改的表 -

To verify whether the records have been deleted from the table, we need to retrieve the modified table once again, using the SELECT query −

SELECT * FROM CUSTOMERS;

CUSTOMERS 表将没有任何记录，其中工资大于 2000 且 ID 与 ORDERS 表中的 CUSTOMER_ID 列匹配。

The CUSTOMERS table would not have the record(s) where salary is greater than 2000 and the ID matches with the CUSTOMER_ID column in the ORDERS table.

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	MP	4500.00
7	Muffy	24	Indore	10000.00

同样，如果你验证 ORDERS 表如下所示 -

Similarly, if you verify the ORDERS table as shown below −

SELECT * FROM ORDERS;

由于工资大于 2000 且 CUSTOMER_ID 与 CUSTOMERS 表中的 ID 值匹配，ORDERS 表的最后一条记录（OID 103）将被删除 -

Since salary is greater than 2000 and the CUSTOMER_ID matches with the ID value in the CUSTOMERS table, the last record (OID 103) of the ORDERS table will be deleted −

OID	DATE	CUSTOMER_ID	AMOUNT
102	2009-10-08 00:00:00	3	3000.00
100	2009-10-08 00:00:00	3	1500.00
101	2009-11-20 00:00:00	2	1560.00