在前面的文章中对 T-SQL 的查询做了基本总结, 接下来我们看下 SQL 中的另外一个常用操作 -- 数据的修改.
INSERT
INSERT
向数据表中插入数据的基本语句, 句式:
- INSERT INTO table_name(field1[,field2]...)
- VALUES(value1[,value2]...);
T-SQL 对 VALUES 进行了增强, 可以一条语句插入多条数据:
- INSERT INTTO table_name(field1[,field2]...)
- VALUES(value1[,value2]...)[,(value3,[value4]...)];
示例如下:
USE WJChi;
-- 插入一条数据
- INSERT INTO dbo.UserInfo(Id,Name,Age,IdentifyId,UAddressId)
- VALUES
- (NEWID(),N'xfh',26,NEWID(),NEWID());
-- 插入多条数据
- INSERT INTO dbo.UserInfo(Id,Name,Age,IdentifyId,UAddressId)
- VALUES
- (NEWID(),N'xfh',26,NEWID(),NEWID()),
- (NEWID(),N'雪飞鸿',28,NEWID(),NEWID());
- BULK INSERT
该语句用于将文件中的数据插入到一个现有表中, 句式:
- BULK INSERT target_table FROM `data_file_path`
- WITH
- (
- DATAFILETYPE = '',
- FIELDTERMINATOR = '',
- ROWTERMINATOR = ''
- )
INSERT...SELECT...
该语句向已存在的目标表中插入有 SELECT 查询返回的结果集, 句式:
- INSERT INTO target_table(field1[,field2])
- SELECT column1[,column2]
- FROM raw_table
- WHERE...
SELECT...INTO...
SELECT...INTO... 不是标准 SQL, 该语句会创建新表并将查询结果集数据插入到表中, 句式:
- SELECT field1[,field2]
- INTO target_table
- FROM raw_table;
目标表的结构和数据是基于源表的, 但, 不会从源表复制: 约束, 索引, 触发器和权限.
INSERT...EXEC...
该语句可将存储过程或动态 SQL 处理的结果集插入到目标表中, 句式:
- INSERT INTO target_table(field1[,field2])
- EXEC stored_procedure;
- UPDATE
- UPDATE
UPDATE 是标准 SQL 语句, 用于更行表中的行, 句式:
- UPDATE target_table
- SET field1[,field2]
- WHERE ...
UPDATE 操作不是幂等的, 我们可以借助事务来防止误操作:
- BEGIN TRAN
- UPDATE ...
- ROLLBACK -- or COMMIT
SQL 中有 all-at-once operations(同时操作)的概念, 即出现在同一逻辑处理阶段的所有表达式在同一时间进行逻辑计算. 基于同时操作的概念, 注意以下语句的运算结果:
交换两个字段的值
- UPDATE dbo.T1
- SET col1 = col2, col2 = col1;
如果字段 col1=100,col2=200
- UPDATE dbo.T1
- SET col1 = col1 + 10,col2 = col1 + 10;
执行以上语句后, col1 和 col2 的值均为 110.
基于联接的 UPDATE
基于联接的 UPDATE 操作是非标准的 SQL.
- UPDATE OD
- SET discount += 0.5
- FROM dbo.OrderDetails AS OD
- JOIN dbo.Orders AS O
- ON OD.orderid = O.orderid
- WHERE O.custid = 1;
对上面 SQL 的理解, 应按照 SQL 的执行顺序来, 即: 先 FROM, 后 WHERE, 最后再看 UPDATE 语句. 该语句用于更新 OD 表中存在于查询结果集 (FROM 语句) 中的数据.
DELETE
T-SQL 提供了两种删除数据的方式: DELETE 与 TRUNCATE, 删除操作也是非幂等的, 与 UPDATE 一样, 我们可以借助事务防止误操作.
- DELETE
- DELETE FROM target_table WHERE...
基于联接的 DELETE
基于联接的 DELETE 操作是非标准的 SQL.
示例如下:
- DELETE FROM Sales.SalesPersonQuotaHistory
- FROM Sales.SalesPersonQuotaHistory AS spqh
- INNER JOIN Sales.SalesPerson AS sp
- ON spqh.BusinessEntityID = sp.BusinessEntityID
- WHERE sp.SalesYTD> 2500000.00;
上述语句换一种写法, 如下:
- DELETE spqh
- FROM
- Sales.SalesPersonQuotaHistory AS spqh
- INNER JOIN Sales.SalesPerson AS sp
- ON spqh.BusinessEntityID = sp.BusinessEntityID
- WHERE sp.SalesYTD> 2500000.00;
上面两条 SQL 的理解与 UPDATE...FROM... 类似: 先看 FROM, 其次是 WHERE, 最后是 DELETE. 该语句用于删除 spqh 表中存在于查询结果集 (FROM 语句) 中的数据.
删除符合条件的部分数据, 如前 20 行:
- DELETE TOP (20)
- FROM Purchasing.PurchaseOrderDetail
- WHERE DueDate < '20020701';
- TRUNCATE
- TRUNCATE TABLE target_table;
TRUNCATE 会删除表中的所有数据并重置表结构, 相当于删掉表然后重建. 与 DELETE 相比, TRUNCATE 使用最小日志记录方式而非完全日志记录方式, 所以在性能上会有优势.
但, 当目标表被外键约束引用时, 即使引用表 (父表) 为空甚至外键被禁用, 都不允许使用 TRUNCATE 操作. 我们可以通过创建一个虚拟表, 带有指向生产表的外键(甚至可以禁止外键以防影响性能), 依此来避免 TRUNCATE 误操作.
MERGE
MERGE 是标准的 SQL 语句, T-SQL 对其进行了一定的扩展. MERGE 语句实现的功能通常可以转换为几个其他 DML 语句 (INSERT,UPDATE,DELETE) 的组合, 使用 MERGE 语句可以使代码更为简洁.
- MERGE target_table
- USING raw_table
- ON...
- WHEN MATCHED THEN
- // do something
- WHEN NOT MATCHED THEN
- // do something
- ;
注意, 必须以分号来结束 MERGE 语句. 示例代码如下:
- MERGE INTO dbo.Customers AS CT
- USING dbo.CustomersStage AS CS
- ON CT.Custid = CS.Custid
-- 源表中的数据与目标表相匹配
- WHEN MATCHED THEN
- UPDATE SET CT.CompanyName = CS.CompanyName
-- 源表中的数据与目标表不匹配
- WHEN NOT MATCHED THEN
- INSERT(Custid,CompanyName) VALUES(CS.Custid,CS.CompanyName)
-- 目标表中的数据不被源表匹配
- WHEN NOT MATCHED BY SOURCE THEN
- DELETE;
以上各个分支子句, 如: WHEN MATCHED THEN 可以附带额外的判断条件, 即 WHEN MATCHED AND... THEN. 详细内容, 可参考 MERGE (Transact-SQL).
通过表表达式修改数据
对较为复杂的语句进行故障排除时, 首先看哪些行会被修改, 而不是实际的修改它们. 选择之一是将代码修改为 SELECT 语句, 并在排除代码故障后, 将其改回 UPDATE 语句.
可以使用表表达式来简化上述解决问题的思路, 示例如下:
- WITH Temp AS
- (
- SELECT custid,OD.orderid,discount,discount+1 AS newDiscount
- FROM dbo.OrderDetails AS OD
- JOIN dbo.Orders AS O
- ON OD.orderid = O.orderid
- WHERE O.cusstid = 1
- )
- UPDATE Temp SET discount = newDiscount;
或者:
- UPDATE Temp SET discount = newDiscount FROM
- (
- SELECT custid,OD.orderid,discount,discount+1 AS newDiscount
- FROM dbo.OrderDetails AS OD
- JOIN dbo.Orders AS O
- ON OD.orderid = O.orderid
- WHERE O.cusstid = 1
- ) AS Temp;
表表达式不仅可用于 SELECT, 也可用于其它 DML 语句(INSERT,UPDATE,DELETE 和 MERGE)
TOP & OFFSET-FETCH
与 SELECT 语句一样, T-SQL 也支持在 INSERT,UPDATE,DELETE,MERGE 语句中直接使用 TOP 选项用于修改部分数据. 但, 与 SELECT 不同的是, DML 中使用 TOP 时无法使用 ORDER BY 子句, 所以无法保证能按预期修改数据. 但可以使用表表达式来避免这个问题:
- WITH Temp AS
- (
- SELECT TOP(50) * FROM dbo.Orders ORDER BY orderid DESC
- )
- UPDATE Temp SET freight += 10.00;
此外, 可以使用 OFFSET-FETCH 来替代 TOP:
- WITH Temp AS
- (
- SELECT * FROM dbo.Orders ORDER BY orderid DESC
- OFFSET 0 ROWS FETCH FIRST 50 ROWS ONLY;
- )
- UPDATE Temp SET freight += 10.00;
- OUTPUT
顾名思义, OUTPUT 用于输出, 句式:
- INSERT[DELETE|UPDATE|MERGE]
- OUTPUT
-- 输出修改前的数据
DELETED
-- 输出修改后的数据
- INSERTED
- WHERE...
示例代码如下:
- USE WJChi;
- INSERT INTO dbo.UAddress
- (
- Id,
- ShortAddress,
- LongAddress
- )
- OUTPUT Inserted.Id,Inserted.ShortAddress,Inserted.LongAddress
- VALUES
- ( NEWID(), -- Id - uniqueidentifier
- N'临时地址', -- ShortAddress - nvarchar(50)
- N'上海市, 临时地址' -- LongAddress - nvarchar(200)
- );
- USE WJChi;
- DELETE TOP (1) FROM dbo.UserInfo
-- 输出被删除的行
OUTPUT DELETED.NAME,DELETED.Age;
- USE WJChi;
- UPDATE dbo.UserInfo SET Age=30
-- 输出修改前的行
OUTPUT Deleted.Name AS OLD_AGE,Deleted.Age AS OLD_AGE,
-- 输出修改后的行
- INSERTED.NAME AS NEW_NAME,INSERTED.Age AS NEW_AGE
- WHERE Name='雪飞鸿';
小结
增删改相比于查询较为简单, 通常语句本身也不会存在性能问题, 一般所说的优化多指查询性能优化. 但, 我们需要注意增删改与查询间排它锁与共享锁问题.
SQL Server Management 插件 --SQL Prompt 在执行 DELETE,UPDATE 但没有带 WHERE 子句的 T-SQL 时会弹出提示框, 可以防止误删, 误更新操作.
来源: https://www.cnblogs.com/Cwj-XFH/p/10105519.html