sqldelete语句_SQLDelete语句概述
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:
注意：可能发⽣的不幸错误是意外地运⾏了不带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],
sql中delete用法reserved,
data,
index_size,
unused
ORDER BY database_name
) AS Row_Num
FROM tb_spaceused)
SELECT *
FROM CTE
WHERE Row_Num <> 1;
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;
如何使⽤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
如何使⽤删除⾏ (How to delete rows using )
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 rowset function along with the delete command.
在以下⽰例中，通过指定⾏集函数以及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语句提取正确的⽬标记录。 选择⾏并右键单击⾏，然后选择“ 删除”以从表中删除⾏。
摘要 (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语句时，有⼀个注意事项列表，如下所⽰：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。