Sql 简明教程
SQL - Delete Table
SQL DELETE 是数据操作语言 (DML) 的命令，所以它不会删除或修改表结构，但只删除表中包含的数据。因此，从表中删除数据后，其中定义的所有约束、索引或触发器仍将存在。
The SQL DELETE is a command of Data Manipulation Language (DML), so it does not delete or modify the table structure but it delete only the data contained within the table. Therefore, any constraints, indexes, or triggers defined in the table will still exist after you delete data from it.
SQL DELETE TABLE Statement
SQL DELETE TABLE 语句用于从数据库中的表删除现有记录。如果只想从表中删除特定数量的行，可以使用 WHERE 子句连同 DELETE 语句。如果省略 WHERE 子句，表中的所有行将被删除。SQL DELETE 语句一次适用于一张表。
The SQL DELETE TABLE statement is used to delete the existing records from a table in a database. If you wish to delete only the specific number of rows from the table, you can use the WHERE clause with the DELETE statement. If you omit the WHERE clause, all rows in the table will be deleted. The SQL DELETE statement operates on a single table at a time.
SQL DELETE TABLE with WHERE Clause
我们可以使用 SQL DELETE 语句基于单个条件使用 WHERE 子句从表中删除特定行。
We can use the SQL DELETE statement to delete specific rows from a table based on a single condition using the WHERE clause.
Syntax
以下是基于单个条件删除特定行的语法：
Following is the syntax for deleting specific rows based on single condition −
DELETE FROM table_name
WHERE condition;Example
假设我们已经创建了一个名为 CUSTOMERS, 的表，其中包含客户的个人详细信息，包括他们的姓名、年龄、地址和薪水等。
Assume we have creating 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 );如果你使用 SELECT * FROM CUSTOMERS 语句检索上述创建的表的内容，将得到以下输出：
If you retrieve the contents of the above created table using the SELECT * FROM CUSTOMERS statement you will get the following output −
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 |
现在，我们尝试删除所有名为“Hardik”的客户，如下所示：
Now, let’s try to delete all the customers with the name 'Hardik' as shown in the query below −
DELETE FROM CUSTOMERS WHERE NAME='Hardik';Output
我们会得到以下结果。我们可以看出删除了 1 行。
We get the following result. We can observe that 1 row has been deleted.
Query OK, 1 row affected (0.05 sec)Verification
现在，如果你使用 SELECT * FROM CUSTOMERS 命令来检索 CUSTOMERS 表的内容，将获取以下输出 −
Now if you retrieve the contents of the CUSTOMERS table using the SELECT * FROM CUSTOMERS command you will get the following output −
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 |
6 |
Komal |
22 |
Hyderabad |
4500.00 |
7 |
Muffy |
24 |
Indore |
10000.00 |
Deleting rows based on multiple conditions
我们还可以使用 SQL DELETE TABLE 语句基于 WHERE 子句中的多个条件从表中删除特定的行。当我们想要从满足特定条件的表中移除部分行时，这非常有用。
We can also use the SQL DELETE TABLE statement to delete specific rows from a table based on multiple conditions using the WHERE clause. This is useful when we want to remove a subset of rows from a table that meet a certain criterion.
在使用多个条件时，我们可以使用 AND、OR 和 NOT 比较运算符来优化条件。通过这种方式，只有满足这些条件的行才会被删除。
When using multiple conditions, we can use the comparison operators such as AND, OR, and NOT to refine our conditions. This way, only rows that satisfy the conditions will be deleted.
Syntax
以下是基于多个条件删除特定行（可使用 AND 或 OR 运算符连接）的基本语法 −
Following is the basic syntax for deleting specific rows based on multiple conditions which can be connected using either AND or OR operators −
DELETE FROM table_name
WHERE condition1 AND condition2 OR ... conditionN;此处，table_name 是我们要从中删除行的表的名称，condition1 到 conditionN 是要删除这些行必须满足的条件。AND 或 OR 运算符可用于将这些条件连接在一起。
Here, table_name is the name of the table from which we want to delete rows, and condition1 through conditionN are the conditions that must be met for the rows to be deleted. The AND or OR operators can be used to join the conditions together.
Example
在以下查询中，我们尝试删除名为“Komal”或地址为“Mumbai”的所有客户 −
In the following query we are trying to delete all the customers whose name is either 'Komal' or their address is 'Mumbai' −
DELETE FROM CUSTOMERS
WHERE NAME='Komal' OR ADDRESS='Mumbai';Output
我们得到以下结果。我们可以观察到已删除 2 行。
We get the following result. We can observe that 2 rows has been deleted.
Query OK, 2 rows affected (0.03 sec)Verification
现在，如果你使用 SELECT * FROM CUSTOMERS 命令来检索 CUSTOMERS 表的内容，将获取以下输出 −
Now if you retrieve the contents of the CUSTOMERS table using the SELECT * FROM CUSTOMERS command you will get the following output −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ramesh |
32 |
Ahmedabad |
2000.00 |
2 |
Khilan |
25 |
Delhi |
1500.00 |
3 |
Kaushik |
23 |
Kota |
2000.00 |
6 |
Komal |
22 |
Hyderabad |
4500.00 |
7 |
Muffy |
24 |
Indore |
10000.00 |
Deleting all the records in a table
我们可以在没有 WHERE 子句的情况下使用 SQL DELETE TABLE 语句以在 SQL 中删除表中的所有记录。此语句将从指定表中移除所有行，实际上会将该表重置为其原始状态（只包含结构及其约束）。
We can use the SQL DELETE TABLE statement without a WHERE clause to delete all records in a table in SQL. This statement will remove all the rows from the specified table, effectively resetting the table to its original state (containing only the structure and its constraints).
但是，请务必注意此操作无法撤消，并且表中的所有数据都将被永久删除。
However, it’s important to note that this operation cannot be undone, and all the data in the table will be permanently deleted.
Example
在此处，我们尝试从 CUSTOMERS 表中删除所有记录 −
In here, we are trying to delete all the records from the CUSTOMERS table −
DELETE FROM CUSTOMERS;