Mysql 简明教程
MySQL - Delete Query
MySQL DELETE Statement
如果我们希望从任何 MySQL 表中删除记录，那么我们可以使用 SQL 命令 DELETE FROM 。此语句是 SQL 中数据操作语言的一部分，因为它与 MySQL 表而不是结构中的数据进行交互。
If we want to delete a record from any MySQL table, then we can use the SQL command DELETE FROM. This statement is a part of Data Manipulation Language in SQL as it interacts with the data in a MySQL table rather than the structure.
DELETE 语句可用于删除单个表中的多行，以及跨多个表的记录。然而，为了筛选要删除的记录，我们可以将 WHERE 子句与 DELETE 语句一起使用。
DELETE statement can be used to delete multiple rows of a single table and records across multiple tables. However, in order to filter the records to be deleted, we can use the WHERE clause along with the DELETE statement.
我们可以在 mysql> 提示符以及任何脚本（如 PHP、Node.js、Java 和 Python）中使用此命令。
We can use this command at the mysql> prompt as well as in any script like PHP, Node.js, Java, and Python.
Syntax
以下是 DELETE 命令的基本 SQL 语法，可从 MySQL 表中删除数据：
Following is the basic SQL syntax of the DELETE command to delete data from a MySQL table −
DELETE FROM table_name [WHERE Clause]-
If the WHERE clause is not specified, then all the records will be deleted from the given MySQL table.
-
We can specify any condition using the WHERE clause.
-
We can delete records in a single table at a time.
当你想删除表中的选定行时，WHERE 子句非常有用。
The WHERE clause is very useful when you want to delete selected rows in a table.
Deleting Data from a MySQL Table
我们使用 MySQL DELETE 查询从数据库表中删除记录。然而，我们可以一次删除单行、多行，甚至在一个查询中从表中删除所有记录。让我们在本文档中一一用适当的示例讨论它们。
We use the MySQL DELETE query to delete records from a database table. However, we can delete single, multiple rows at once or even delete all records from a table in a single query. Let us discuss them one by one futher in this tutorial with appropriate examples.
Example
首先，让我们使用以下查询创建一个名为 CUSTOMERS 的表：
First of all, let us create a table named CUSTOMERS 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)
);以下查询在上述创建的表中插入 7 条记录 −
The following query inserts 7 records into the above created table −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 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 );执行以下 SELECT 语句以检索 CUSTOMERS 表中存在的所有记录：
Execute the below SELECT statement to retrieve all the records present in the CUSTOMERS table −
Select * From CUSTOMERS;以下为 CUSTOMERS 表 −
Following is the CUSTOMERS table −
现在，让我们使用 DELETE 语句从 CUSTOMERS 表中删除 single record (ID = 1)，如下所示：
Now, let us delete a single record (ID = 1) from the CUSTOMERS table using the DELETE statement as follows −
DELETE FROM CUSTOMERS WHERE ID = 1;Output
执行给定的查询后，输出如下：
On executing the given query, the output is displayed as follows −
Query OK, 1 row affected (0.00 sec)Verification
执行以下查询以验证上述记录是否已被删除：
Execute the following query to verify whether the above record have been deleted or not −
Select * From CUSTOMERS;正如我们在输出中看到的，ID=1 的行已被删除：
As we can see in the output, the row with ID=1 has been deleted −
Deleting Multiple Rows
Deleting Multiple Rows
我们还可以使用 DELETE 语句删除多行。为此，我们只需要在 WHERE 子句中指定多个条件，所有待删除的记录都满足这些条件。
We can also delete multiple rows using the DELETE statement. For this, we just have to specify multiple conditions in the WHERE clause that are satisfied by all the records that are supposed to be deleted.
Example
在这里，我们正在从以前创建的 CUSTOMERS 表中删除 ID 为 2 和 3 的记录：
Here, we are deleting records from previously created CUSTOMERS table whose ID is 2 and 3 −
DELETE FROM CUSTOMERS WHERE ID = 2 OR ID = 3;Output
以上程序的输出如下所示：
The output for the program above is produced as given below −
Query OK, 2 rows affected (0.01 sec)Verification
执行以下查询以验证上述记录是否已被删除：
Execute the following query to verify whether the above records have been deleted or not −
Select * From CUSTOMERS;正如我们在输出中看到的，具有 ID 值 2 和 3 的行已删除：
As we can see in the output, the row with ID values 2 and 3 are deleted −
Deleting All Rows
Deleting All Rows
如果我们想从 MySQL 表中删除所有记录，只需执行 DELETE 语句而不使用 WHERE 子句。以下是语法：
If we want to delete all records from a MySQL table, simply execute the DELETE statement without using the WHERE clause. Following is the syntax −
DELETE FROM table_name;Example
以下查询将删除 CUSTOMERS 表中的所有记录：
The following query will delete all records from the CUSTOMERS table −
DELETE FROM CUSTOMERS;Output
执行给定的程序后，输出如下所示：
On executing the given program, the output is displayed as follows −
Query OK, 4 rows affected (0.01 sec)Verification
执行以下查询以验证是否已删除 CUSTOMERS 表中的所有记录：
Execute the following query to verify whether all the records from CUSTOMERS table have been deleted or not −
Select * From CUSTOMERS;正如我们在下面看到的输出中，已返回一个空集，即所有记录都已删除。
As we can see the output below, empty set has been returned i.e all the records have been deleted.
Empty set (0.00 sec)Delete Query in MySQL Using a Client Program
除了使用 MySQL 查询删除数据库表中的记录外，我们还可以使用客户端程序来执行 DELETE 操作。
Besides deleting records of a database table with a MySQL query, we can also use a client program to perform the DELETE operation.