Sql 简明教程
SQL - DELETE JOIN
SQL 中的简单删除操作可以在表的一个或多个记录上执行。要从多个表中删除记录，最直接的方法是一次从一个表中删除记录。
Simple deletion operation in SQL can be performed on a single record or multiple records of a table. And to delete records from multiple tables, the most straightforward approach would be to delete records from one table at a time.
但是，SQL 通过允许同时针对多个表执行删除操作使其变得更加容易。这是通过使用联接实现的。
However, SQL makes it easier by allowing the deletion operation to be performed on multiple tables simultaneously. This is achieved using Joins.
The SQL DELETE… JOIN Clause
SQL 中联接的目的是基于公共列/字段将两张或多张表的记录组合在一起。表联接后，对获得的结果集执行删除操作将一次从所有原始表中删除记录。
The purpose of Joins in SQL is to combine records of two or more tables based on common columns/fields. Once the tables are joined, performing the deletion operation on the obtained result-set will delete records from all the original tables at a time.
例如，考虑一个教育机构的数据库。它包括各种表：部门、学生详细信息、图书馆通行证、实验室通行证等。当一组学生毕业时，需要从组织表中删除他们的所有详细信息，因为它们是不需要的。但是，从多个表中分别删除详细信息可能会很麻烦。
For example, consider a database of an educational institution. It consists of various tables: Departments, StudentDetails, LibraryPasses, LaboratoryPasses etc. When a set of students are graduated, all their details from the organizational tables need to be removed, as they are unwanted. However, removing the details separately from multiple tables can be cumbersome.
为了更简单，我们会首先使用 Joins 从所有表中检索所有毕业生的组合数据；然后，再使用 DELETE 语句从所有表中删除该连接数据。上述完整流程可以通过一个单一查询完成。
To make it simpler, we will first retrieve the combined data of all graduated students from all the tables using Joins; then, this joined data is deleted from all the tables using DELETE statement. This entire process can be done in one single query.
Syntax
以下为 SQL DELETE… JOIN 语句的基本语法 −
Following is the basic syntax of the SQL DELETE… JOIN statement −
DELETE table(s)
FROM table1 JOIN table2
ON table1.common_field = table2.common_field;这里我们所说的 JOIN，可以使用任意类型的连接：常规连接、自然连接、内部连接、外部连接、左连接、右连接、完全连接等。
When we say JOIN here, we can use any type of Join: Regular Join, Natural Join, Inner Join, Outer Join, Left Join, Right Join, Full Join etc.
Example
为了演示该删除操作，我们必须先创建表并在其中插入值。我们可以创建表，方法是使用如下所示的 CREATE TABLE 查询。
To demonstrate this deletion operation, we must first create tables and insert values into them. We can create these tables using CREATE TABLE queries as shown below.
创建一个名为 CUSTOMERS 的表，其中包含客户的个人详细信息，包括他们的姓名、年龄、地址和工资等。使用以下查询 −
Create a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc. Using the following query −
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 |
让我们创建另一个名为 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 −
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 |
下列 DELETE… JOIN query 同时从这些表中删除记录 −
Following DELETE… JOIN query removes records from these tables at once −
DELETE a
FROM CUSTOMERS AS a INNER JOIN ORDERS AS b
ON a.ID = b.CUSTOMER_ID;Output
输出将在 SQL 中如下显示 −
The output will be displayed in SQL as follows −
Query OK, 3 rows affected (0.01 sec)Verification
我们可以使用如下所示的 SELECT 语句来检索表的内容，从而验证表中是否反映了更改 −
We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement as follows −
SELECT * FROM CUSTOMERS;该表显示如下：
The table is displayed as follows −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ramesh |
32 |
Ahmedabad |
2000.00 |
5 |
Hardik |
27 |
Bhopal |
8500.00 |
6 |
Komal |
22 |
Hyderabad |
4500.00 |
7 |
Muffy |
24 |
Indore |
10000.00 |
既然我们只删除了 CUSTOMERS 表中的记录，则更改不会反映在 ORDERS 表中。我们可以使用以下查询进行验证。
Since, we only deleted records from CUSTOMERS table, the changes will not be reflected in the ORDERS table. We can verify it using the following query.
SELECT * FROM ORDERS;ORDERS 表显示为 −
The ORDERS table is displayed as −
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 |
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 the WHERE clause to make the filtration stricter. Observe the query below. Here, we are deleting 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;Output
执行查询后，将显示以下输出。
On executing the query, following output is displayed.
Query OK, 1 row affected (0.01 sec)Verification
我们可以使用如下所示的 SELECT 语句来检索表的内容，从而验证表中是否反映了更改 −
We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement as follows −
SELECT * FROM CUSTOMERS;删除后的 CUSTOMERS 表如下所示 −
The CUSTOMERS table after deletion is as follows −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ramesh |
32 |
Ahmedabad |
2000.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 |
既然我们只删除了 CUSTOMERS 表中的记录，则更改不会反映在 ORDERS 表中。我们可以使用以下查询进行验证 −
Since we only deleted records from the CUSTOMERS table, the changes will not be reflected in the ORDERS table. We can verify it using the following query −
SELECT * FROM ORDERS;ORDERS 表显示为 −
The ORDERS table is displayed as −
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 |