Mysql 简明教程

MySQL - Delete Join

MySQL 中的简单删除操作可以在表的单个项或多个项上执行。但是，如果此删除操作要对多个表的多个项执行怎么办？这是 Joins 的用处所在。

Simple deletion operation in MySQL can be performed on a single entity or multiple entities of a table. But what if this deletion operation is to be performed on multiple entities of multiple tables? This is where Joins come into picture.

MySQL DELETE…​ JOIN

正如我们在本教程中之前讨论的，联接用于通过基于通用字段组合这些表的列从两个或更多个表中检索记录。此合并的数据可以删除，并且所有更改都会反映在原始表中。

As we have discussed in this tutorial previously, Joins are used to retrieve records from two or more tables, by combining columns of these tables based on the common fields. This merged data can be deleted with all the changes reflected in original tables.

Syntax

以下是 MySQL 中 DELETE…​ JOIN 语句的基本语法：

Following is the basic syntax of DELETE…​ JOIN statement in MySQL −

DELETE table(s)
FROM table1 JOIN table2
ON table1.common_field = table2.common_field;

我们可以在执行删除时使用任何联接子句（INNER JOIN、LEFT JOIN、RIGHT JOIN 等）。

We can use any join clause (INNER JOIN, LEFT JOIN, RIGHT JOIN etc.) while performing deletion.

Example

在这个例子中, 我们首先创建了一个名为 CUSTOMERS 的表格, 其中包含客户的个人详细信息, 包括他们的姓名、年龄、地址和薪水等。

In this example, we first create a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc.

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 −

让我们创建另一个名为 ORDERS 的表，其中包含所做的订单和下单日期的详细信息。

Let us create another table ORDERS, containing the details of orders made and the date they are made on.

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 is displayed as follows −

通过对这些表格应用 DELETE…​ JOIN 查询来执行删除操作。

The delete operation is performed by applying the DELETE…​ JOIN query on these tables.

DELETE a
FROM CUSTOMERS AS a INNER JOIN ORDERS AS b
ON a.ID = b.CUSTOMER_ID;

Verification

要验证更改是否反映在表格中, 我们可以使用 SELECT 语句打印表格。

To verify if the changes are reflected in the tables, we can use SELECT statement to print the tables.

DELETE…​ JOIN with WHERE Clause

DELETE…​ JOIN 查询中的 ON 子句用于对记录应用约束。除此之外, 我们还可以使用 WHERE 子句使筛选更加严格。观察下面的查询; 在这里, 我们尝试删除 CUSTOMERS 表格中薪水低于 2000.00 卢比的客户的记录。

The ON clause in DELETE…​ JOIN query is used to apply constraints on the records. In addition to it, we can also use WHERE clause to make the filtration stricter. Observe the query below; here, we are trying to delete the records of customers, in the CUSTOMERS table, whose salary is lower than Rs. 2000.00.

DELETE a
FROM CUSTOMERS AS a INNER JOIN ORDERS AS b
ON a.ID = b.CUSTOMER_ID
WHERE a.SALARY < 2000.00;

Verification

要验证更改是否反映在原始表格中, 我们将使用 SELECT 语句。

To verify whether the changes are reflected in the original tables or not, we will use the SELECT statement.

删除后的 CUSTOMERS 表如下所示 −

The CUSTOMERS table after deletion is as follows −

Delete Join Using Client Program

除了使用 MySQL 查询连接两个或两个以上的表格之外, 我们还可以使用客户端程序执行 Delete Join 操作。

In addition to joining two or more than two tables using the MySQL query, we can also perform the Delete Join operation using a client program.

Syntax

Example

以下是这些程序 −

Following are the programs −