在SQL中,删除表中的行使用DELETE
语句。以下是详细的使用方法和注意事项:
DELETE FROM 表名
WHERE 条件;
删除特定条件的行:
-- 删除员工表中部门为'HR'的所有员工
DELETE FROM employees
WHERE department = 'HR';
删除所有行(清空表):
-- 删除orders表中的所有记录
DELETE FROM orders;
⚠️ 注意:这会删除表中所有数据,但保留表结构
使用子查询删除:
-- 删除30天内没有活动的用户
DELETE FROM users
WHERE user_id IN (
SELECT user_id
FROM user_activity
WHERE last_active_date < DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
);
多表关联删除(不同数据库语法可能不同):
sql
DELETE t1 FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
WHERE t2.status = 'inactive';
sql
DELETE FROM table1
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id
WHERE t2.status = 'inactive';
限制删除行数:
-- MySQL: 限制删除100行
DELETE FROM log_entries
WHERE created_at < '2020-01-01'
LIMIT 100;
-- SQL Server:
DELETE TOP (100) FROM log_entries
WHERE created_at < '2020-01-01';
sql
BEGIN TRANSACTION;
DELETE FROM large_table WHERE condition;
-- 验证无误后
COMMIT;
-- 或出错时
ROLLBACK;
对于清空整个表,TRUNCATE TABLE
通常比DELETE更快:
TRUNCATE TABLE table_name;
区别: - TRUNCATE是DDL操作,DELETE是DML操作 - TRUNCATE不能带WHERE条件 - TRUNCATE会重置自增计数器