SQL Server数据删除 #

DELETE [FROM] table_name
[WHERE condition];

-- 或使用FROM子句
DELETE table_name
FROM table_name alias
[JOIN other_table ON condition]
[WHERE condition];

-- 创建示例表
CREATE TABLE users (
    id INT PRIMARY KEY,
    name NVARCHAR(50),
    email VARCHAR(100),
    status BIT DEFAULT 1
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    amount DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

INSERT INTO users VALUES
    (1, 'John', 'john@example.com', 1),
    (2, 'Jane', 'jane@example.com', 1),
    (3, 'Bob', 'bob@example.com', 0),
    (4, 'Alice', 'alice@example.com', 1),
    (5, 'Charlie', 'charlie@example.com', 0);

INSERT INTO orders VALUES
    (1, 1, '2024-01-01', 100.00),
    (2, 1, '2024-01-02', 200.00),
    (3, 2, '2024-01-03', 150.00);

-- 删除单行
DELETE FROM users WHERE id = 5;

-- 删除多行
DELETE FROM users WHERE status = 0;

-- 删除所有行（危险操作！）
DELETE FROM users;

-- 等于条件
DELETE FROM users WHERE id = 1;

-- 范围条件
DELETE FROM orders WHERE order_date < '2024-01-01';

-- IN条件
DELETE FROM users WHERE id IN (1, 2, 3);

-- LIKE条件
DELETE FROM users WHERE email LIKE '%@test.com';

-- NULL条件
DELETE FROM users WHERE email IS NULL;

-- 多条件组合
DELETE FROM users 
WHERE status = 0 
  AND created_at < DATEADD(YEAR, -1, GETDATE());

-- 子查询条件
DELETE FROM users 
WHERE id IN (
    SELECT user_id FROM orders 
    WHERE status = 'cancelled'
);

-- EXISTS条件
DELETE FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- 删除前N行
DELETE TOP (10) FROM users WHERE status = 0;

-- 删除前10%行
DELETE TOP (10) PERCENT FROM users;

-- 删除最早的10条记录
WITH cte AS (
    SELECT TOP 10 id
    FROM orders
    ORDER BY order_date ASC
)
DELETE FROM orders
WHERE id IN (SELECT id FROM cte);

-- 或直接使用CTE删除
WITH cte AS (
    SELECT TOP 10 *
    FROM orders
    ORDER BY order_date ASC
)
DELETE FROM cte;

-- 基于关联表删除
DELETE o
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.status = 0;

-- 删除没有订单的用户
DELETE u
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- 使用子查询删除
DELETE FROM users
WHERE id IN (
    SELECT user_id FROM orders WHERE amount > 10000
);

-- 使用相关子查询
DELETE FROM users u
WHERE (
    SELECT COUNT(*) FROM orders o 
    WHERE o.user_id = u.id
) = 0;

-- 使用CTE删除
WITH InactiveUsers AS (
    SELECT u.id
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE u.status = 0
    GROUP BY u.id
    HAVING COUNT(o.id) = 0
)
DELETE FROM users
WHERE id IN (SELECT id FROM InactiveUsers);

-- 返回删除的数据
DELETE FROM users
OUTPUT deleted.id, deleted.name, deleted.email
WHERE id = 1;

-- 结果
-- id | name | email
-- 1  | John | john@example.com

-- 创建归档表
CREATE TABLE users_archive (
    id INT,
    name NVARCHAR(50),
    email VARCHAR(100),
    deleted_at DATETIME DEFAULT GETDATE()
);

-- 删除并归档
DELETE FROM users
OUTPUT deleted.id, deleted.name, deleted.email INTO users_archive
WHERE status = 0;

-- 查看归档数据
SELECT * FROM users_archive;

-- 创建审计表
CREATE TABLE delete_audit (
    table_name NVARCHAR(50),
    record_id INT,
    old_data NVARCHAR(MAX),
    deleted_by NVARCHAR(100) DEFAULT SUSER_NAME(),
    deleted_at DATETIME DEFAULT GETDATE()
);

-- 删除并记录完整数据
DELETE FROM users
OUTPUT 
    'users',
    deleted.id,
    (SELECT * FROM deleted FOR JSON PATH),
    DEFAULT,
    DEFAULT
INTO delete_audit
WHERE id = 1;

-- 清空表
TRUNCATE TABLE users;

-- 安全写法
IF OBJECT_ID('temp_users', 'U') IS NOT NULL
    TRUNCATE TABLE temp_users;

-- TRUNCATE重置IDENTITY
CREATE TABLE test (
    id INT IDENTITY(1,1),
    name NVARCHAR(50)
);

INSERT INTO test VALUES ('A'), ('B');
SELECT * FROM test;  -- id: 1, 2

TRUNCATE TABLE test;
INSERT INTO test VALUES ('C');
SELECT * FROM test;  -- id: 1（重置了）

-- DELETE不重置IDENTITY
DELETE FROM test;
INSERT INTO test VALUES ('D');
SELECT * FROM test;  -- id: 2（未重置）

-- 有外键引用时不能TRUNCATE
TRUNCATE TABLE users;  -- 错误：被orders表引用

-- 解决方案1：先删除外键约束
ALTER TABLE orders DROP CONSTRAINT fk_orders_users;
TRUNCATE TABLE users;
ALTER TABLE orders ADD CONSTRAINT fk_orders_users 
    FOREIGN KEY (user_id) REFERENCES users(id);

-- 解决方案2：使用DELETE
DELETE FROM users;

-- 创建带级联删除的表
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name NVARCHAR(50)
);

CREATE TABLE customer_orders (
    id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) 
        REFERENCES customers(id)
        ON DELETE CASCADE  -- 级联删除
);

-- 删除客户时自动删除订单
DELETE FROM customers WHERE id = 1;
-- 同时删除customer_orders中相关记录

-- 手动删除关联数据
BEGIN TRANSACTION;

-- 先删除子表数据
DELETE FROM orders WHERE user_id = 1;

-- 再删除主表数据
DELETE FROM users WHERE id = 1;

COMMIT TRANSACTION;

-- 创建删除触发器
CREATE TRIGGER tr_users_delete
ON users
INSTEAD OF DELETE
AS
BEGIN
    -- 先删除关联订单
    DELETE FROM orders 
    WHERE user_id IN (SELECT id FROM deleted);
    
    -- 再删除用户
    DELETE FROM users 
    WHERE id IN (SELECT id FROM deleted);
END

-- 添加删除标记列
ALTER TABLE users ADD is_deleted BIT DEFAULT 0;
ALTER TABLE users ADD deleted_at DATETIME;

-- 软删除（更新标记）
UPDATE users 
SET 
    is_deleted = 1,
    deleted_at = GETDATE()
WHERE id = 1;

-- 查询时过滤已删除数据
SELECT * FROM users WHERE is_deleted = 0;

-- 恢复数据
UPDATE users 
SET is_deleted = 0, deleted_at = NULL 
WHERE id = 1;

-- 创建活动用户视图
CREATE VIEW vw_active_users AS
SELECT id, name, email, status
FROM users
WHERE is_deleted = 0;

-- 使用视图操作
SELECT * FROM vw_active_users;  -- 自动过滤已删除

-- 软删除
UPDATE users SET is_deleted = 1 WHERE id = 1;

-- 创建INSTEAD OF触发器
CREATE TRIGGER tr_users_soft_delete
ON users
INSTEAD OF DELETE
AS
BEGIN
    UPDATE users
    SET 
        is_deleted = 1,
        deleted_at = GETDATE()
    WHERE id IN (SELECT id FROM deleted);
END

-- 删除操作变成软删除
DELETE FROM users WHERE id = 1;

-- 大表分批删除
DECLARE @batchSize INT = 1000;
DECLARE @rowsAffected INT = 1;

WHILE @rowsAffected > 0
BEGIN
    DELETE TOP (@batchSize) FROM orders
    WHERE order_date < '2023-01-01';
    
    SET @rowsAffected = @@ROWCOUNT;
    
    -- 延迟减少锁争用
    WAITFOR DELAY '00:00:00.1';
END

-- 先选出要删除的ID
SELECT id INTO #to_delete
FROM orders
WHERE order_date < '2023-01-01';

-- 分批删除
DECLARE @batchSize INT = 1000;

WHILE EXISTS (SELECT 1 FROM #to_delete)
BEGIN
    DELETE TOP (@batchSize) FROM orders
    WHERE id IN (SELECT id FROM #to_delete);
    
    DELETE TOP (@batchSize) FROM #to_delete;
END

DROP TABLE #to_delete;

-- 使用事务保证原子性
BEGIN TRANSACTION;

BEGIN TRY
    DELETE FROM orders WHERE user_id = 1;
    DELETE FROM users WHERE id = 1;
    
    COMMIT TRANSACTION;
    PRINT '删除成功';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT '删除失败: ' + ERROR_MESSAGE();
END CATCH

-- 使用保存点部分回滚
BEGIN TRANSACTION;

DELETE FROM users WHERE id = 1;

SAVE TRANSACTION after_user_delete;

DELETE FROM orders WHERE user_id = 1;

-- 如果需要回滚到保存点
ROLLBACK TRANSACTION after_user_delete;

COMMIT TRANSACTION;

-- 使用@@ROWCOUNT
DELETE FROM users WHERE status = 0;

IF @@ROWCOUNT = 0
    PRINT '没有数据被删除';
ELSE
    PRINT '删除了 ' + CAST(@@ROWCOUNT AS VARCHAR) + ' 行';

-- 删除后验证
DECLARE @userId INT = 1;

BEGIN TRANSACTION;

DELETE FROM orders WHERE user_id = @userId;
DELETE FROM users WHERE id = @userId;

-- 验证删除成功
IF NOT EXISTS (SELECT 1 FROM users WHERE id = @userId)
   AND NOT EXISTS (SELECT 1 FROM orders WHERE user_id = @userId)
BEGIN
    COMMIT TRANSACTION;
    PRINT '删除成功';
END
ELSE
BEGIN
    ROLLBACK TRANSACTION;
    PRINT '删除失败';
END

-- 确保WHERE条件使用索引
CREATE INDEX ix_users_status ON users(status);

-- 使用索引列删除
DELETE FROM users WHERE status = 0;

-- 大批量删除时禁用触发器
DISABLE TRIGGER ALL ON users;

DELETE FROM users WHERE status = 0;

ENABLE TRIGGER ALL ON users;

-- 禁用约束检查
ALTER TABLE orders NOCHECK CONSTRAINT ALL;
DELETE FROM users WHERE status = 0;
ALTER TABLE orders CHECK CONSTRAINT ALL;

-- 在简单恢复模式下，TRUNCATE使用最小日志
ALTER DATABASE mydb SET RECOVERY SIMPLE;

TRUNCATE TABLE large_table;

ALTER DATABASE mydb SET RECOVERY FULL;

-- 先查询再删除
BEGIN TRANSACTION;

-- 查看要删除的数据
SELECT * FROM users WHERE status = 0;

-- 确认数量
SELECT COUNT(*) AS '将要删除的行数' 
FROM users WHERE status = 0;

-- 确认后执行删除
DELETE FROM users WHERE status = 0;

-- 检查结果
SELECT * FROM users;

-- 确认无误后提交
COMMIT TRANSACTION;
-- 或回滚
-- ROLLBACK TRANSACTION;

-- 授予DELETE权限
GRANT DELETE ON users TO user1;

-- 拒绝DELETE权限
DENY DELETE ON users TO user1;

-- 撤销权限
REVOKE DELETE ON users FROM user1;

-- 1. 外键约束
DELETE FROM users WHERE id = 1;  -- 错误：被orders引用

-- 2. 触发器阻止
-- 触发器中ROLLBACK会阻止删除

-- 3. 锁超时
-- 其他事务持有锁

-- 4. 权限不足
-- 需要DELETE权限

-- 使用WITH (ROWLOCK)提示
DELETE FROM users WITH (ROWLOCK) WHERE id = 1;

-- 使用事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
DELETE FROM users WHERE id = 1;
COMMIT TRANSACTION;