我要评论数据定义（ddl）决定数据库长什么样，而数据操作（dml）决定数据库每天在做什么。 对于绝大多数业务系统来说，真正运行最频繁的不是 create table，而是 insert、update、delete 等 dml 操作。
本文将系统介绍 sql server 中最常用的 dml（data manipulation language，数据操作语言）语句，包括 insert、update、delete、merge、output 的使用方法、典型业务场景、性能优化技巧以及生产环境中的注意事项。
一、什么是 dml？
dml（data manipulation language）即数据操作语言，主要负责对表中的数据进行新增、修改、删除和合并。
sql server 中最核心的 dml 语句包括：
- insert：新增数据
- update：修改数据
- delete：删除数据
- merge：同步、合并数据（upsert）
- output：返回受影响的数据
可以把数据库比作一本账本：
- insert 就是在账本中新写一条记录；
- update 是修改已有记录；
- delete 是划掉记录；
- merge 则像对照两本账本进行同步；
- output 则是操作时自动生成一份变更清单。
二、insert——新增数据
insert 用于向表中写入新数据。
基本语法
insert into 表名 (列1, 列2, ...) values (值1, 值2, ...);
场景一：插入单条用户数据
insert into users
(
username,
email,
createtime
)
values
(
'tom',
'tom@test.com',
getdate()
);
适用于：
- 用户注册
- 创建订单
- 新增商品
场景二：一次插入多条数据
insert into users
(
username,
email
)
values
('alice','alice@test.com'),
('bob','bob@test.com'),
('jack','jack@test.com');
sql server 2008 起支持这种写法。
相比循环 insert，效率明显更高。
场景三：从查询结果插入
例如归档历史订单。
insert into orderhistory
(
orderid,
userid,
amount
)
select
orderid,
userid,
amount
from orders
where status='completed';
这种方式通常比程序循环导入快得多。
场景四：使用 default
insert 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 phone='13800001111' where userid=1001;
场景二：订单批量修改状态
update orders set status='completed' where paystatus='paid';
典型应用：
支付成功后更新订单状态。
场景三：基于 join 更新
例如同步会员等级。
update u set u.levelname=l.levelname from users u inner join userlevel l on u.levelid=l.levelid;
这种 update 是 sql server 非常实用的扩展。
update 注意事项
务必带 where 条件。
建议先执行：
select * from orders where status='pending';
确认影响范围后再：
update orders set status='processing' where status='pending';
这是 dba 最基本的操作规范。
四、delete——删除数据
delete 删除的是数据，而不是表。
基本语法
delete from 表名 where 条件;
场景一：删除单个用户
delete from users where userid=1001;
场景二：删除测试数据
delete from orders where userid=-1;
很多开发环境都会保留这种测试账号。
场景三：删除过期日志
delete from systemlog where createtime < dateadd(month,-6,getdate());
这是日志清理最常见的方式。
delete 与 truncate 的区别
| 对比项 | delete | truncate |
|---|---|---|
| 删除方式 | 按行删除 | 整表快速清空 |
| where | 支持 | 不支持 |
| 日志 | 较多 | 较少 |
| identity | 不重置 | 重置 |
| 触发器 | 会触发 | 不触发 delete trigger |
一般来说：
- 清空整张表 → truncate
- 删除部分数据 → delete
如果存在外键引用，truncate 通常无法执行。
五、merge——同步数据（upsert）
merge 可以一次完成：
- 存在则更新
- 不存在则插入
- 可选删除目标中多余数据
因此也称 upsert。
基本语法
merge target as t
using source as s
on t.id=s.id
when matched then
update ...
when not matched then
insert ...;
场景一：同步用户信息
merge users as t
using tempusers as s
on t.userid=s.userid
when matched then
update set
t.username=s.username,
t.email=s.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.productid=s.productid when matched then update set stock=s.stock when not matched then insert(productid,stock) values(s.productid,s.stock);
merge 注意事项
sql server 多个版本曾修复过 merge 的边界 bug，生产环境建议：
- 保持最新累计更新（cu）
- 并发较高场景可考虑拆分为 update+insert 两步实现
- 大批量同步建议结合事务与索引优化
六、output——获取受影响的数据
很多人不知道，sql server 可以直接返回本次 dml 操作的数据。
insert output
insert into users
(
username
)
output inserted.userid,
inserted.username
values
(
'lucy'
);
返回：
userid username
无需再次查询。
update output
update orders set amount=amount+100 output deleted.amount as oldamount, inserted.amount as newamount where orderid=10;
其中：
- inserted：修改后数据
- deleted：修改前数据
非常适合：
- 审计日志
- 数据追踪
- 数据回滚记录
delete output
delete from users output deleted.* where userid=100;
删除前的数据可以直接保存到日志表。
七、事务管理：保证数据一致性
多个 dml 通常需要作为一个整体执行。
begin tran; update account set balance=balance-100 where userid=1; update account set balance=balance+100 where userid=2; commit;
发生异常：
rollback;
最佳实践：
- 一个业务一个事务
- 事务尽量短
- 不要在事务中等待用户输入
- 及时 commit 或 rollback
八、dml 最佳实践
1、先 select，再 update/delete
select * from orders where status='pending';
确认无误后再执行修改。
2、避免锁表
对于百万级数据：
不要：
delete from orders;
建议：
while 1=1
begin
delete top (5000)
from orders
where createtime<'2023-01-01';
if @@rowcount=0 break;
end
批量删除能够有效减少锁竞争与事务日志压力。
3、合理建立索引
where 条件字段建议建立索引。
否则：
update、delete 很容易全表扫描。
4、批量导入优化
对于海量数据：
- 使用 bulk insert
- 使用 sqlbulkcopy（.net）
- 分批提交事务
- 导入完成后更新统计信息
九、常见陷阱
1、忘记 where
update users set status=0;
整个用户表都会被修改。
这是数据库事故中最常见的问题之一。
2、隐式类型转换
例如：
where userid='100'
如果 userid 为 int，sql server 可能发生隐式转换，影响索引使用，导致性能下降。
建议保持参数类型与字段类型一致。
3、null 判断错误
错误写法：
where email=null
正确写法：
where email is null
同样：
is not null
而不是：
!= null
4、外键约束
例如：
orders
引用
users
删除用户：
delete from users where userid=1;
如果订单仍存在，将提示外键冲突。
应：
- 先删除子表
- 或配置级联删除（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 status='archived'; delete from orders where status='archived';
整个流程建议放入事务中执行，并结合适当索引，确保同步、日志记录和归档的一致性。
十一、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 虽然功能强大，但在高并发业务中应结合版本特性和实际测试谨慎使用。
到此这篇关于sql server dml 操作的项目实战的文章就介绍到这了,更多相关sqlserver dml 操作内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论