MySQL - Delete Join 删除连接
在 MySQL 中，简单的删除操作可以针对单个实体或表中的多个实体执行。但如果要针对多个表的多个实体执行此删除操作，该怎么办?这时就需要使用连接了。
MySQL DELETE... JOIN
正如我们之前在本教程中讨论过的，连接用于从两个或多个表中检索记录，方法是根据公共字段合并这些表的列。合并后的数据可以被删除，所有更改都会反映在原始表中。
语法
以下是 MySQL 中 DELETE... JOIN 语句的基本语法 -
DELETE table(s) FROM table1 JOIN table2 ON table1.common_field = table2.common_field;
执行删除操作时，我们可以使用任何连接子句(INNER JOIN、LEFT JOIN、RIGHT JOIN 等)。
示例
在此示例中，我们首先创建一个名为 CUSTOMERS 的表，其中包含客户的个人信息，包括姓名、年龄、地址和工资等。
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 语句将值插入到该表中，如下所示 -
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);
该表将创建为 −
| 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，其中包含已下订单的详细信息及其下单日期。
CREATE TABLE ORDERS (
OID INT NOT NULL,
DATE VARCHAR (20) NOT NULL,
CUSTOMER_ID INT NOT NULL,
AMOUNT DECIMAL (18, 2),
);
使用 INSERT 语句，将值插入此表，如下所示 -
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);
表显示如下 −
| 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 查询来执行。
DELETE a FROM CUSTOMERS AS a INNER JOIN ORDERS AS b ON a.ID = b.CUSTOMER_ID;
验证
为了验证更改是否反映在表中，我们可以使用 SELECT 语句打印表。
| 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 |
使用 WHERE 子句的 DELETE... JOIN
DELETE... JOIN 查询中的 ON 子句用于对记录施加约束。除此之外，我们还可以使用 WHERE 子句来使过滤更加严格。观察下面的查询;这里，我们尝试删除 CUSTOMERS 表中工资低于 2000.00 卢比的客户记录。
DELETE a FROM CUSTOMERS AS a INNER JOIN ORDERS AS b ON a.ID = b.CUSTOMER_ID WHERE a.SALARY < 2000.00;
验证
为了验证更改是否反映在原始表中，我们将使用 SELECT 语句。
删除后的 CUSTOMERS 表如下所示 -
| 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 |
使用客户端程序删除连接
除了使用 MySQL 查询连接两个或两个以上的表之外，我们还可以使用客户端程序执行删除连接操作。
语法
要通过 PHP 程序执行 Delete Join，我们需要使用 mysqli 函数 query() 执行带有 JOIN 子句的 DELETE 语句，如下所示 -
$sql = 'DELETE tutorials_tbl, tcount_tbl FROM tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author'; $mysqli->query($sql);
要通过 JavaScript 程序执行 Delete Join 操作，我们需要使用 mysql2 库的 query() 函数执行带有 JOIN 子句的 DELETE 语句，如下所示 -
sql = "DELETE tutorials_tbl, tcount_tbl FROM tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author"; con.query(sql);
要通过 Java 程序执行 Delete Join，我们需要使用 JDBC 函数 executeUpdate() 执行带有 JOIN 子句的 DELETE 语句，如下所示 -
String sql = "DELETE tutorials_tbl, tcount_tbl FROM tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author"; statement.executeUpdate(sql);
要通过 Python 程序执行 Delete Join，我们需要使用 MySQL Connector/Python 的 execute() 函数执行带有 JOIN 子句的 DELETE 语句，如下所示 -
delete_join_query = "DELETE a FROM CUSTOMERS AS a INNER JOIN ORDERS AS b ON a.ID = b.CUST_ID" cursorObj.execute(delete_join_query)
示例
以下是程序 -
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
printf("Connect failed: %s
", $mysqli->connect_error);
exit();
}
// printf('Connected successfully.
');
$sql = 'DELETE tutorials_tbl, tcount_tbl FROM tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author';
if ($mysqli->query($sql)) {
printf("Join deleted successfully!.
");
}
if ($mysqli->errno) {
printf("Join could not be deleted !.
", $mysqli->error);
}
$mysqli->close();
输出
获得的输出如下 -
Join deleted successfully!.
var mysql = require("mysql2");
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "password",
}); //连接到 MySQL
con.connect(function (err) {
if (err) throw err;
// console.log("Connected successfully...!");
// console.log("--------------------------");
sql = "USE TUTORIALS";
con.query(sql);
//Delete Join
sql = "DELETE tutorials_tbl, tcount_tbl FROM tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author";
con.query(sql, function (err, result) {
if (err) throw err;
console.log(result);
});
});
输出
生成的输出如下 -
ResultSetHeader {
fieldCount: 0,
affectedRows: 2,
insertId: 0,
info: '',
serverStatus: 34,
warningStatus: 0,
changedRows: 0
}
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class DeleteJoin {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/TUTORIALS";
String username = "root";
String password = "password";
try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
System.out.println("Connected successfully...!");
//MySQL Delete JOIN...!;
String sql = "DELETE tutorials_tbl, tcount_tbl FROM tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author";
statement.executeUpdate(sql);
System.out.println("JOIN Deleted successfully...!");
connection.close();
} catch (Exception e) {
System.out.println(e);
}
}
}
输出
获得的输出如下所示 -
Connected successfully...! JOIN Deleted successfully...!
import mysql.connector
#建立连接
connection = mysql.connector.connect(
host='localhost',
user='root',
password='password',
database='tut'
)
cursorObj = connection.cursor()
delete_join_query = f"""DELETE a FROM CUSTOMERS AS a INNER JOIN ORDERS AS b ON a.ID = b.CUST_ID"""
cursorObj.execute(delete_join_query)
connection.commit()
print("deleted succesfully")
cursorObj.close()
connection.close()
输出
以下是上述代码的输出 -
deleted successfully
