sql delete语句_SQL Delete语句概述-CSDN博客

本文全面解析 SQL DELETE 语句的使用方法，包括不带 WHERE 子句、使用 WHERE 子句、使用 TOP 删除、删除重复行、使用子查询、使用联接以及从远程表删除行等操作。同时，文章提供了 SQL Server Management Studio (SSMS) 中删除行的步骤，以及使用 DELETE 语句时的重要注意事项。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

sql delete语句

This article on the SQL Delete is a part of the SQL essential series on key statements, functions and operations in SQL Server.

有关SQL Delete的这篇文章是有关SQL Server中关键语句，函数和操作SQL基本系列的一部分。

To remove a row from a table is accomplished through a Data Manipulation Language, aka DML statement, using the delete keyword. The SQL delete operation is by far the simplest of all the DML commands. On execution of the delete command, we don’t have to worry about getting any form of data from the table, and we don’t have to worry about working with any data that we get back from the table(s). We just simply tell the database to delete a specific record, and it either does or it doesn’t. It’s that simple.

从表中删除行是通过数据操作语言（又称DML语句）使用delete关键字完成的。到目前为止，SQL删除操作是所有DML命令中最简单的操作。在执行delete命令时，我们不必担心从表中获取任何形式的数据，也不必担心处理从表中获取的任何数据。我们只是简单地告诉数据库删除特定记录，它要么执行，要么不执行。就这么简单。

First, let’s quickly review what an SQL delete statement looks like. We need to tell the database and table from where it should delete the data. It’s a good idea to add a condition clause to set the scope of data deletion. Otherwise, it will delete everything in the table.

首先，让我们快速回顾一下SQL delete语句的外观。我们需要告诉数据库和表应从何处删除数据。添加条件子句以设置数据删除的范围是一个好主意。否则，它将删除表中的所有内容。

Let’s take a look at our table and removing some records.

让我们看一下表并删除一些记录。

如何删除不带where子句的行 (How to delete rows with no where clause)

The following example deletes all rows from the Person.Person the table in the AdventureWorks2014 database. There is no restriction enforced on the SQL delete statement using a WHERE clause.

下面的示例从AdventureWorks2014数据库中的Person.Person表中删除所有行 。使用WHERE子句SQL delete语句没有任何限制。

USE Adventureworks2014;
GO
DELETE FROM [Person].[Person];

如何使用where子句删除行 (How to delete rows with where clause)

The following example deletes rows from the [Person].[Person] table in the AdventureWorks2014 database in which the value in the businessEntityID column is greater than 30,000

以下示例从AdventureWorks2014数据库的[Person]。[Person]表中删除行，其中businessEntityID列中的值大于30,000

USE Adventureworks2014;
GO
DELETE FROM [Person].[Person]
WHERE businessEntityID > 30000;

Note: An unfortunate mistake that may occur is to accidently run a SQL Delete with no Where clause and inadvertently delete all of your data. To prevent this from happening consider using the Execution guard feature in ApexSQL Complete, to warn against such potentially damaging actions, before you execute them. Learn more: Execution alerts

注意：可能发生的不幸错误是意外地运行了不带Where子句SQL Delete并无意间删除了所有数据。 为防止这种情况发生，请在执行之前考虑使用ApexSQL Complete中的Execution保护功能，以警告此类可能有害的操作。 了解更多：执行警报

如何使用带有where子句的Top删除行 (How to delete rows using Top with where clause)

The following example deletes 50 random rows from the Person.Person table in the AdventureWorks2014 database. The value in the BusinessEntityID must be in between 30,000 and 40,000

下面的示例从AdventureWorks2014数据库的Person.Person表中删除50个随机行。 BusinessEntityID中的值必须介于30,000和40,000之间

USE Adventureworks2014;
GO
DELETE TOP(50) FROM [Person].[Person]
WHERE BusinessEntityID between 30000 and 40000

Note: The when the TOP (n) clause is used with the SQL Delete statement and any DML statement (i.e. Select, Insert, Delete and Update), the operation is performed on a random selection of a number of rows specified in the Top clause.

注意：当TOP（n）子句与SQL Delete语句和任何DML语句（即Select，Insert，Delete和Update）一起使用时，将对Top子句中指定的许多行的随机选择执行操作。

如何删除重复的行 (How to delete duplicate rows)

In the real-world, we tend to gather data from different sources; it’s not uncommon to have duplicate records. One approach to the duplicate problem is first to identify where the duplicates have occurred. And run a select query on those columns.

在现实世界中，我们倾向于从不同的来源收集数据。有重复的记录并不少见。解决重复问题的一种方法是首先确定重复发生在哪里。并对这些列运行选择查询。

EATE TABLE tb_spaceused
(database_name       NVARCHAR(128), 
 database_size       VARCHAR(18), 
 [unallocated space] VARCHAR(18), 
 reserved            VARCHAR(18), 
 data                VARCHAR(18), 
 index_size          VARCHAR(18), 
 unused              VARCHAR(18)
);
INSERT INTO tb_spaceused
EXEC sp_msforeachdb 
     @command1 = "use ? exec sp_spaceused  @oneresultset = 1";
 
SELECT *
FROM tb_spaceused
order by database_name

The following example uses the PARTITION BY argument to partition the query result set by all the columns of tb_spaceused table. The Row_Number (), a window function, which means it operates over an ordered set. The ORDER BY clause specified in the OVER clause orders the rows in each partition by the entire columns tb_spaceused table.

以下示例使用PARTITION BY参数将查询结果集按tb_spaceused表的所有列进行分区。 Row_Number（）是一个窗口函数，这意味着它将对有序集进行操作。 OVER子句中指定的ORDER BY子句按tb_spaceused表的整个列对每个分区中的行进行排序。

WITH CTE
     AS (SELECT *, 
                ROW_NUMBER() OVER(PARTITION BY database_name, 
                                               database_size, 
                                               [unallocated space], 
                                               reserved, 
                                               data, 
                                               index_size, 
                                               unused
                ORDER BY database_name
                        ) AS Row_Num
         FROM tb_spaceused)
     SELECT *
     FROM CTE
     WHERE Row_Num <> 1;

The following example uses the PARTITION BY argument to partition the query result set by all the columns of tb_spaceused table.

Replacing the Select statement with a Delete removes all the duplicates of the table.

用Delete替换Select语句将删除表的所有重复项。

WITH CTE
     AS (SELECT *, 
                ROW_NUMBER() OVER(PARTITION BY database_name, 
                                               database_size, 
                                               [unallocated space], 
                                               reserved, 
                                               data, 
                                               index_size, 
                                               unused
                ORDER BY database_name
                        ) AS Row_Num
         FROM tb_spaceused)
     --SELECT *
     --FROM CTE
     --WHERE Row_Num <> 1;
	 DELETE FROM CTE
     WHERE Row_Num <> 1;

Replacing the Select statement with a Delete removes all the duplicates of the table.

如何使用SQL子查询删除行 (How to delete rows using SQL sub-queries)

In the following example, the rows in one table are deleted based on data in another table. In the examples, the rows from the SalesPersonQuotaHistory table are deleted based on the SalesYTD column of the SalesPerson table.

在以下示例中，一个表中的行基于另一表中的数据被删除。在示例中，根据SalesPerson表的SalesYTD列删除SalesPersonQuotaHistory表中的行。

DELETE FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID IN
(
    SELECT BusinessEntityID
    FROM Sales.SalesPerson
    WHERE SalesYTD > 4900000.00
); 
GO

如何使用SQL联接删除行 (How to delete rows using SQL Joins )

In this section, we will use the SQL Delete statement to delete the data from the Adeventureworks2014 database. Deleting data, at first sight, sound trivial, but once we get into a large database design things might not be same and easy anymore.

在本节中，我们将使用SQL Delete语句从Adeventureworks2014数据库中删除数据。乍一看，删除数据似乎微不足道，但是一旦我们进入大型数据库设计，事情可能就不再那么简单了。

In many cases, the tables are related via a primary and foreign key relationship. In the following example, we can see a use of joins to delete the data from the Sales.SalesPersonQuotaHistory.

在许多情况下，这些表通过主键和外键关系进行关联。在以下示例中，我们可以看到使用联接从Sales.SalesPersonQuotaHistory中删除数据。

DELETE sq
FROM Sales.SalesPersonQuotaHistory sq
     INNER JOIN Sales.SalesPerson sp ON sq.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 4500000.00;  
GO

如何使用链接的服务器和OpenQuery从远程表中删除行 (How to delete rows from a remote table using linked servers and OpenQuery)

The following example uses the SQL delete statement to delete rows from a remote table using the linked server named, hqdbt01. Then query the remote table using four-part object naming convention to delete the rows from the remote table

下面的示例使用SQL delete语句使用名为hqdbt01的链接服务器从远程表中删除行。然后使用四部分对象命名约定查询远程表以从远程表中删除行

DELETE
FROM [hqdbt01].AdventureWorks2014.[HumanResources].[Shift]
WHERE ShiftID = 2;

The following example, the remote table is queried by specifying the OPENQUERY rowset function along with the delete command.

在以下示例中，通过指定OPENQUERY行集函数以及delete命令来查询远程表。

DELETE OPENQUERY (hqdbt01, 'SELECT *
FROM AdventureWorks2014.HumanResources.Department  
WHERE DepartmentID = 18');

如何使用SSMS删除行 (How to delete rows using SSMS)

Using the SQL Server Management Studio (SSMS), Graphical User Interface (GUI) to delete rows involves a manual search. In reality, it will be much easier and quicker to delete records with a SQL query.

使用SQL Server Management Studio（SSMS），图形用户界面（GUI）删除行涉及手动搜索。实际上，使用SQL查询删除记录将更加容易和快捷。

Let’s go ahead and locate the table to use a SQL delete statement, in this case, table dbo.cities is selected. Now, right-click and choose Edit Top 200 rows. This option opens up a query designer window. Next, right-click the window and select Execute SQL and write a new query that will delete rows from the dbo.cities table.

让我们继续查找表以使用SQL delete语句，在这种情况下，将选择表dbo.cities 。现在，右键单击并选择编辑前200行 。此选项将打开查询设计器窗口。接下来，右键单击该窗口并选择Execute SQL并编写一个新查询，该查询将从dbo.cities表中删除行。

In the result pane, make sure that SELECT Statement is pulling up the correct targeted records before start deleting rows. Select the rows and right-click the rows and choose Delete to remove the rows from the table.

在结果窗格中，在开始删除行之前，请确保SELECT语句提取正确的目标记录。选择行并右键单击行，然后选择“ 删除”以从表中删除行。

SQL delete - Select the rows and right-click the rows and choose Delete to remove the rows from the table.

摘要 (Summary)

Thus far, we’ve seen many different ways use the SQL delete statement to remove data. But, there is a list of the consideration to be followed while using the delete statement, and it as follows:

到目前为止，我们已经看到使用SQL delete语句删除数据的许多不同方式。但是，在使用delete语句时，有一个注意事项列表，如下所示：

It is always recommended to start with a SELECT statement before you delete anything to make sure that you’re targeting the correct records. So the delete statement is used to delete rows from a table using the where clause to select only the rows to be deleted
始终建议从SELECT语句开始，然后再删除任何内容，以确保定位的是正确的记录。因此，delete语句用于使用where子句从表中删除行，以仅选择要删除的行
Always use a unique identifier to locate the rows that you need to delete
始终使用唯一标识符查找需要删除的行
To delete all the rows in a table, always use TRUNCATE TABLE. TRUNCATE TABLE which is faster than a SQL delete statement and it uses fewer system and transaction-log resources
要删除表中的所有行，请始终使用TRUNCATE TABLE。 TRUNCATE TABLE比SQL delete语句快，并且使用较少的系统和事务日志资源
By default, DELETE statements induce an exclusive (X) lock on the table, and it holds the lock until the transaction completes
默认情况下，DELETE语句在表上引入互斥（X）锁，并持有该锁，直到事务完成为止
NOLOCK hint or read uncommitted NOLOCK提示或读取未提交的isolation level 隔离级别允许读取操作
It is recommended to specify the TABLOCK hint in the delete statement. This process allows page de-allocation and associated space available for reuse by other objects in the database
建议在delete语句中指定TABLOCK提示。此过程允许页面解除分配以及相关的空间可用于数据库中的其他对象重用
It is good practice to create a clustered index on the heap table before executing a delete statement
优良作法是在执行删除语句之前在堆表上创建聚簇索引
Although very simple and very powerful, and the result of a Delete statement is destructive. Deleted rows cannot be easily recovered
尽管非常简单且非常强大，但Delete语句的结果却具有破坏性。删除的行无法轻易恢复