H2 Database 简明教程

H2 Database - Delete

SQL DELETE 查询用于从表中删除现有记录。我们可以对 DELETE 查询使用 WHERE 子句来删除选定的记录，否则将删除所有记录。

The SQL DELETE query is used to delete the existing records from a table. We can use WHERE clause with DELETE query to delete selected records, otherwise all the records will be deleted.

Syntax

以下是 delete 命令的通用查询语法。

Following is the generic query syntax of the delete command.

DELETE [ TOP term ] FROM tableName [ WHERE expression ] [ LIMIT term ]

以上语法从表中删除行。如果指定了 TOP 或 LIMIT，则最多删除指定数量的行（如果为 null 或小于零，则无限制）。

The above syntax deletes the rows from a table. If TOP or LIMIT is specified, at most the specified number of rows are deleted (no limit if null or smaller than zero).

Example

考虑具有以下记录的 CUSTOMER 表。

Consider the CUSTOMER table having the following records.

+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

以下命令将删除 ID 为 6 的客户的详细信息。

The following command will delete the details of the customer, whose ID is 6.

DELETE FROM CUSTOMERS WHERE ID = 6;

执行以上命令后，通过执行以下命令检查 Customer 表。

After execution of the above command, check the Customer table by executing the following command.

SELECT * FROM CUSTOMERS;

以上命令生成以下输出 −

The above command produces 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 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

如果我们要从 CUSTOMERS 表中 DELETE 所有记录，则不使用 WHERE 子句。DELETE 查询如下。

If we want to DELETE all the records from CUSTOMERS table, we do not use WHERE clause. The DELETE query would be as follows.

DELETE FROM CUSTOMER;

执行以上命令后，Customer 表中将没有任何记录。

After executing the above command, no records will be available in the Customer table.