SQL Server DML 操作的项目实战_MsSqlserver

数据定义（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 &lt; 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&lt;'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 操作内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！

SQL Server DML 操作的项目实战

一、什么是 dml？

二、insert——新增数据

基本语法

场景一：插入单条用户数据

场景二：一次插入多条数据

场景三：从查询结果插入

场景四：使用 default

性能与注意事项

三、update——修改数据

基本语法

场景一：修改用户手机号

场景二：订单批量修改状态

场景三：基于 join 更新

update 注意事项

四、delete——删除数据

基本语法

场景一：删除单个用户

场景二：删除测试数据

场景三：删除过期日志

delete 与 truncate 的区别

五、merge——同步数据（upsert）

基本语法

场景一：同步用户信息

场景二：同步商品库存

merge 注意事项

六、output——获取受影响的数据

insert output

update output

delete output

七、事务管理：保证数据一致性

八、dml 最佳实践

1、先 select，再 update/delete

2、避免锁表

3、合理建立索引

4、批量导入优化

九、常见陷阱

1、忘记 where

2、隐式类型转换

3、null 判断错误

4、外键约束

十、综合案例：订单同步与归档

十一、sql server 版本差异

总结

推荐阅读

SQL中优化IN的几种方法

SQL PARTITION BY用法小结

SQL image转汉字乱码处理的问题解决

你以为迁移完事了?其实这些 SQL 逻辑陷阱正悄悄等着你呢(七大逻辑陷阱及修复方案)

Android利用SQLite实现简单的记事本(附源码)

SQL Server表被锁了怎么办？快速查看和解锁的实用方法

猜你喜欢

发表评论