SQL DELETE 语句

SQL DELETE 语句用来从数据表中删除一条或多条记录。

SQL DELETE 语句语法

DELETE FROM TableName
WHERE conditions

注意:如果没有WHERE子句,数据表中所有记录都会被删除!

SQL DELETE 语句范例

数据表: Employees

EmployeeId FirstName LastName Department Salary
203 Mazojys Fxoj Finance 78000
204 Jozzh Lnanyo Finance 45800
205 Syllauu Dfaafk Finance 57000
206 Gecrrcc Srlkrt Finance 62000
302 Jssme Bdnaa Development 75000
303 Dnnaao Errllov Development 55000
304 Tyoysww Osk Development 49000

例1
删除EmployeeID是206的记录:

DELETE FROM Employees
WHERE EmployeeId=206

删除后的 Employees 表将会类似于:

EmployeeId FirstName LastName Department Salary
203 Mazojys Fxoj Finance 78000
204 Jozzh Lnanyo Finance 45800
205 Syllauu Dfaafk Finance 57000
302 Jssme Bdnaa Development 75000
303 Dnnaao Errllov Development 55000
304 Tyoysww Osk Development 49000

例2
删除所有Salary大于70000的记录:

DELETE FROM Employees
WHERE Salary > 70000

现在,数据表 Employees 将会类似于:

EmployeeId FirstName LastName Department Salary
204 Jozzh Lnanyo Finance 45800
205 Syllauu Dfaafk Finance 57000
303 Dnnaao Errllov Development 55000
304 Tyoysww Osk Development 49000

例3
删除数据表 Employees 中所有的记录:

DELETE FROM Employees

或者:

DELETE * FROM Employees

我们也可以用TRUNCATE来删除表中所有的记录:

TRUNCATE TABLE Employees

警告

使用DELETE语句时请务必小心,因为一旦删除将无法恢复。

有时,不用DELETE语句物理上删除记录,而是用UPDATE语句将一条记录标志为删除。

例如,我们可以在表Employees中增加一个列:Status:

EmployeeId FirstName LastName Department Salary Status
203 Mazojys Fxoj Finance 78000 Active
204 Jozzh Lnanyo Finance 45800 Active
205 Syllauu Dfaafk Finance 57000 Active
206 Gecrrcc Srlkrt Finance 62000 Active
302 Jssme Bdnaa Development 75000 Active
303 Dnnaao Errllov Development 55000 Active
304 Tyoysww Osk Development 49000 Active

如果你想删除EmployeeID为206的记录,可以使用UPDATE语句将它的Status标志为”Deleted”:

UPDATE Employees
SET Status='Deleted'
WHERE EmployeeId=206

现在表Employees类似于:

EmployeeId FirstName LastName Department Salary Status
203 Mazojys Fxoj Finance 78000 Active
204 Jozzh Lnanyo Finance 45800 Active
205 Syllauu Dfaafk Finance 57000 Active
206 Gecrrcc Srlkrt Finance 62000 Deleted
302 Jssme Bdnaa Development 75000 Active
303 Dnnaao Errllov Development 55000 Active
304 Tyoysww Osk Development 49000 Active

当我们查询表时,可以使用下面的语句:

SELECT *
FROM Employees
WHERE Status <> 'Deleted'

这样,那些Status标志为”Deleted”的记录将不会出现在结果集中,对终端用户来说,好像被删除了一样。如果你想将这些数据恢复,将会很容易:将Status更改为”Active”即可。