MySQL - Delete Join 删除连接

MySQL DELETE... JOIN
DELETE... JOIN 使用 WHERE 子句
使用客户端程序删除连接

在 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 NodeJS Java Python

要通过 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)

示例

以下是程序 -

PHP NodeJS Java Python

$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

MySQL 8 教程

MySQL 8 数据库

MySQL 8 用户

MySQL 8 表

MySQL 8 查询

MySQL 8 视图

MySQL 8 索引

MySQL 运算符和子句

MySQL 连接

MySQL 键

MySQL 触发器

MySQL 8 数据类型

MySQL 正则表达式

MySQL 全文搜索

MySQL8 函数和运算符

MySQL 8 其他概念

MySQL 8 实用资源

MySQL - Delete Join 删除连接