SQL删除查询

SQL删除查询 (SQL Delete Query)

SQL Delete Query is used to remove rows from table in a database. In a database the storage and retrieval of data is the most important aspect. But, there are cases when we have insert some incorrect data by mistake and we have to remove it. Or the data is obsolete now and we can delete it, such as logging information that can be purged after few days.

SQL Delete Query用于从数据库的表中删除行。 在数据库中，数据的存储和检索是最重要的方面。 但是，在某些情况下，我们会错误地插入一些不正确的数据，因此必须将其删除。 或数据现在已过时，我们可以将其删除，例如几天后可以清除的日志信息。

Deletion of data is very important aspect of database maintenance. Deletion of unnecessary data is important in order to maintain a clean database with only valuable information stored as part of the table values.

数据删除是数据库维护中非常重要的方面。 为了维护干净的数据库，其中仅将有价值的信息存储为表值的一部分，删除不必要的数据很重要。

SQL删除语法 (SQL Delete Syntax)

If we want to delete specific rows, then we need to provide delete statement with where clause.

如果要删除特定行，则需要提供带where子句的delete语句。

DELETE From table_name WHERE condition;

In the syntax above the deletion happens based on the condition that is specified in the WHERE clause.

在上面的语法中，删除是根据WHERE子句中指定的条件发生的。

SQL删除行 (SQL Delete Row)

Let’s try to understand the DELETE command through some example. Let’s consider the following Customer Table to understand DELETE command.

让我们尝试通过一些示例来了解DELETE命令。 让我们考虑以下客户表以了解DELETE命令。

CustomerId	CustomerName	CustomerAge	CustomerGender
1	James	32	M
2	Diana	26	M
3	Annie	35	F

顾客ID	顾客姓名	客户年龄	客户性别
1个	詹姆士	32	中号
2	戴安娜	26	中号
3	安妮	35	F

We want to delete rows with CustomerGender as Female. The delete statement will be;

我们要删除CustomerGender为Female的行。 delete语句将是；

DELETE FROM Customer WHERE CustomerGender = 'F';

SQL删除单行 (SQL Delete Single Row)

For above table, result will be 1 record deleted. If you want to make sure that your SQL delete query will delete maximum one row, then always use primary key in the where clause.

对于上表，结果将被1 record deleted 。 如果要确保SQL删除查询最多删除一行，请始终在where子句中使用主键。

DELETE FROM Customer WHERE CustomerID = 1;

Above query will delete only one row or no rows if there is no customer with customer id as 1.

如果没有客户ID为1的客户，则上述查询将仅删除一行或不删除任何行。

SQL删除所有行 (SQL Delete All Rows)

If you will not provide where clause with delete statement, then whole table data will be deleted. Use this very carefully to avoid any unwanted data loss.

如果您不提供带delete语句的where子句，则整个表数据将被删除。 请非常小心地使用它，以免造成任何不必要的数据丢失。

Delete From Customer;

In the syntax above the deletion happens without any condition and will delete all the records of the table.

在上面的语法中，删除没有任何条件，并且将删除表的所有记录。

SQL删除表 (SQL Delete Table)

If you want to delete the table itself, then you can use DROP statement like below.

如果要删除表本身，则可以使用如下所示的DROP语句。

Drop table Customer;

Above command will delete Customer table with all it’s data.

上面的命令将删除客户表及其所有数据。

删除声明要点 (Delete Statement Important Points)

Delete Query in SQL should always be executed with the WHERE clause to avoid unwanted data loss. Delete statement without WHERE clause will delete all the records of the table and without proper rollback mechanism, your data could be lost forever.

SQL中的删除查询应始终与WHERE子句一起执行，以避免不必要的数据丢失。 没有WHERE子句的Delete语句将删除表的所有记录，并且如果没有适当的回滚机制，则数据可能会永远丢失。

Reference: For more information about DELETE please refer to Oracle Documentation.

参考：有关DELETE的更多信息，请参考Oracle文档 。

翻译自: https://www.journaldev.com/18404/sql-delete-query