数据定义DDL决定数据库长什么样而数据操作DML决定数据库每天在做什么。对于绝大多数业务系统来说真正运行最频繁的不是 CREATE TABLE而是 INSERT、UPDATE、DELETE 等 DML 操作。本文将系统介绍 SQL Server 中最常用的 DMLData Manipulation Language数据操作语言语句包括INSERT、UPDATE、DELETE、MERGE、OUTPUT的使用方法、典型业务场景、性能优化技巧以及生产环境中的注意事项。一、什么是 DMLDMLData Manipulation Language即数据操作语言主要负责对表中的数据进行新增、修改、删除和合并。SQL Server 中最核心的 DML 语句包括INSERT新增数据UPDATE修改数据DELETE删除数据MERGE同步、合并数据UPSERTOUTPUT返回受影响的数据可以把数据库比作一本账本INSERT 就是在账本中新写一条记录UPDATE 是修改已有记录DELETE 是划掉记录MERGE 则像对照两本账本进行同步OUTPUT 则是操作时自动生成一份变更清单。二、INSERT——新增数据INSERT 用于向表中写入新数据。基本语法INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...);场景一插入单条用户数据INSERT INTO Users ( UserName, Email, CreateTime ) VALUES ( Tom, tomtest.com, GETDATE() );适用于用户注册创建订单新增商品场景二一次插入多条数据INSERT INTO Users ( UserName, Email ) VALUES (Alice,alicetest.com), (Bob,bobtest.com), (Jack,jacktest.com);SQL Server 2008 起支持这种写法。相比循环 INSERT效率明显更高。场景三从查询结果插入例如归档历史订单。INSERT INTO OrderHistory ( OrderID, UserID, Amount ) SELECT OrderID, UserID, Amount FROM Orders WHERE StatusCompleted;这种方式通常比程序循环导入快得多。场景四使用 DEFAULTINSERT INTO Users ( UserName, Status ) VALUES ( Jerry, DEFAULT );要求字段定义了默认值。例如Status INT DEFAULT 1性能与注意事项建议指定列名不建议使用INSERT INTO Table VALUES(...)批量导入优先使用多值 INSERT、BCP、BULK INSERT大批量插入可考虑关闭非聚集索引后重建三、UPDATE——修改数据UPDATE 用于修改已有记录。基本语法UPDATE 表名 SET 列值 WHERE 条件;场景一修改用户手机号UPDATE Users SET Phone13800001111 WHERE UserID1001;场景二订单批量修改状态UPDATE Orders SET StatusCompleted WHERE PayStatusPaid;典型应用支付成功后更新订单状态。场景三基于 JOIN 更新例如同步会员等级。UPDATE U SET U.LevelNameL.LevelName FROM Users U INNER JOIN UserLevel L ON U.LevelIDL.LevelID;这种 UPDATE 是 SQL Server 非常实用的扩展。UPDATE 注意事项务必带 WHERE 条件。建议先执行SELECT * FROM Orders WHERE StatusPending;确认影响范围后再UPDATE Orders SET StatusProcessing WHERE StatusPending;这是 DBA 最基本的操作规范。四、DELETE——删除数据DELETE 删除的是数据而不是表。基本语法DELETE FROM 表名 WHERE 条件;场景一删除单个用户DELETE FROM Users WHERE UserID1001;场景二删除测试数据DELETE FROM Orders WHERE UserID-1;很多开发环境都会保留这种测试账号。场景三删除过期日志DELETE FROM SystemLog WHERE CreateTime DATEADD(MONTH,-6,GETDATE());这是日志清理最常见的方式。DELETE 与 TRUNCATE 的区别对比项DELETETRUNCATE删除方式按行删除整表快速清空WHERE支持不支持日志较多较少Identity不重置重置触发器会触发不触发 DELETE Trigger一般来说清空整张表→ TRUNCATE删除部分数据→ DELETE如果存在外键引用TRUNCATE 通常无法执行。五、MERGE——同步数据UPSERTMERGE 可以一次完成存在则更新不存在则插入可选删除目标中多余数据因此也称UPSERT。基本语法MERGE Target AS T USING Source AS S ON T.IDS.ID WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ...;场景一同步用户信息MERGE Users AS T USING TempUsers AS S ON T.UserIDS.UserID WHEN MATCHED THEN UPDATE SET T.UserNameS.UserName, T.EmailS.Email WHEN NOT MATCHED THEN INSERT ( UserID, UserName, Email ) VALUES ( S.UserID, S.UserName, S.Email );非常适合数据同步ETL数据仓库场景二同步商品库存每天 ERP 导入库存MERGE ProductStock AS T USING ImportStock AS S ON T.ProductIDS.ProductID WHEN MATCHED THEN UPDATE SET StockS.Stock WHEN NOT MATCHED THEN INSERT(ProductID,Stock) VALUES(S.ProductID,S.Stock);MERGE 注意事项SQL Server 多个版本曾修复过 MERGE 的边界 Bug生产环境建议保持最新累计更新CU并发较高场景可考虑拆分为 UPDATEINSERT 两步实现大批量同步建议结合事务与索引优化六、OUTPUT——获取受影响的数据很多人不知道SQL Server 可以直接返回本次 DML 操作的数据。INSERT OUTPUTINSERT INTO Users ( UserName ) OUTPUT INSERTED.UserID, INSERTED.UserName VALUES ( Lucy );返回UserID UserName无需再次查询。UPDATE OUTPUTUPDATE Orders SET AmountAmount100 OUTPUT DELETED.Amount AS OldAmount, INSERTED.Amount AS NewAmount WHERE OrderID10;其中INSERTED修改后数据DELETED修改前数据非常适合审计日志数据追踪数据回滚记录DELETE OUTPUTDELETE FROM Users OUTPUT DELETED.* WHERE UserID100;删除前的数据可以直接保存到日志表。七、事务管理保证数据一致性多个 DML 通常需要作为一个整体执行。BEGIN TRAN; UPDATE Account SET BalanceBalance-100 WHERE UserID1; UPDATE Account SET BalanceBalance100 WHERE UserID2; COMMIT;发生异常ROLLBACK;最佳实践一个业务一个事务事务尽量短不要在事务中等待用户输入及时 COMMIT 或 ROLLBACK八、DML 最佳实践1、先 SELECT再 UPDATE/DELETESELECT * FROM Orders WHERE StatusPending;确认无误后再执行修改。2、避免锁表对于百万级数据不要DELETE FROM Orders;建议WHILE 11 BEGIN DELETE TOP (5000) FROM Orders WHERE CreateTime2023-01-01; IF ROWCOUNT0 BREAK; END批量删除能够有效减少锁竞争与事务日志压力。3、合理建立索引WHERE 条件字段建议建立索引。否则UPDATE、DELETE 很容易全表扫描。4、批量导入优化对于海量数据使用 BULK INSERT使用 SqlBulkCopy.NET分批提交事务导入完成后更新统计信息九、常见陷阱1、忘记 WHEREUPDATE Users SET Status0;整个用户表都会被修改。这是数据库事故中最常见的问题之一。2、隐式类型转换例如WHERE UserID100如果 UserID 为 INTSQL Server 可能发生隐式转换影响索引使用导致性能下降。建议保持参数类型与字段类型一致。3、NULL 判断错误错误写法WHERE EmailNULL正确写法WHERE Email IS NULL同样IS NOT NULL而不是! NULL4、外键约束例如Orders引用Users删除用户DELETE FROM Users WHERE UserID1;如果订单仍存在将提示外键冲突。应先删除子表或配置级联删除CASCADE或重新设计业务逻辑十、综合案例订单同步与归档假设每天凌晨需要同步外部订单并归档已完成订单。第一步同步新增和更新订单MERGE Orders AS T USING ImportOrders AS S ON T.OrderID S.OrderID WHEN MATCHED THEN UPDATE SET T.Amount S.Amount, T.Status S.Status WHEN NOT MATCHED THEN INSERT (OrderID, UserID, Amount, Status) VALUES (S.OrderID, S.UserID, S.Amount, S.Status);第二步记录变更日志UPDATE Orders SET Status Archived OUTPUT INSERTED.OrderID, DELETED.Status, INSERTED.Status, GETDATE() INTO OrderChangeLog WHERE Status Completed;第三步归档历史数据INSERT INTO OrderHistory SELECT * FROM Orders WHERE StatusArchived; DELETE FROM Orders WHERE StatusArchived;整个流程建议放入事务中执行并结合适当索引确保同步、日志记录和归档的一致性。十一、SQL Server 版本差异不同版本对 DML 能力持续增强SQL Server 2008支持多行VALUES插入、MERGE语句。SQL Server 2012增强OFFSET/FETCH等分页能力便于与 DML 配合处理批量数据。SQL Server 2016在 JSON、Temporal Table 等特性上有明显增强可配合OUTPUT构建审计方案同时对批量操作和查询优化器进行了持续改进。SQL Server 2019/2022智能查询处理Intelligent Query Processing进一步优化部分 DML 相关执行计划但MERGE在高并发场景仍建议充分测试后再投入生产。总结DML 是数据库开发中使用频率最高的一组 SQL 语句也是最容易因为误操作而引发生产事故的部分。掌握INSERT、UPDATE、DELETE、MERGE 与 OUTPUT的正确使用方式不仅能够完成日常的数据维护工作更能编写出安全、高效、易维护的数据处理程序。最后牢记几条经验法则任何 UPDATE、DELETE 都应先用 SELECT 验证影响范围。涉及多步修改时使用事务确保数据一致性。批量操作采用分批提交减少锁竞争和事务日志压力。充分利用 OUTPUT 实现数据审计与变更追踪。MERGE 虽然功能强大但在高并发业务中应结合版本特性和实际测试谨慎使用。
