每天10分钟学习T-SQL语言基础(Part 4)

【数据库技术 作者  / Edison Zhou

这是 恰童鞋骚年 的第 171 篇原创文章

Microsoft SQL Server 2008技术内幕: T-SQL语言基础 》是一本关于T-SQL方面的好书,可能现在我们在京东上都买不到了,我也是在2014年在淘宝上淘的。看完之后,我总结了一些精华笔记,现将其分成一个系列的笔记文章分享与你,每篇预计阅读时间为 10分钟 左右。 上一篇 介绍了SQL Server的 透视、逆透视、分组 ,本篇会介绍数据修改。

插入与删除数据

1 看我花式插入数据

① INSERT VALUES语句 : 这个语句恐怕我们再熟悉不过了把,在任何一本数据库的书上面都可以看到这个语句的身影。

INSERT INTO dbo.Orders(orderid, orderdate, empid, custid)

VALUES(10001, '20090212', 3, 'A');

需要了解的是,前面也提到过,SQL Server 2008增强了VALUES语句的功能,允许在一条语句中指定由逗号分隔开的多行记录。 例如下面的语句向Orders中插入了4行数据:

INSERT INTO dbo.Orders

(orderid, orderdate, empid, custid)

VALUES

(10003, '20090213', 4, 'B'),

(10004, '20090214', 1, 'A'),

(10005, '20090213', 1, 'C'),

(10006, '20090215', 3, 'C');

② INSERT SELECT语句 : 将一组由SELECT查询返回的结果行插入到目标表中。

INSERT INTO dbo.Orders(orderid, orderdate, empid, custid)

SELECT orderid, orderdate, empid, custid

FROM TSQLFundamentals2008.Sales.Orders

WHERE shipcountry = 'UK';

③ INSERT EXEC语句: 将存储过过程或动态SQL批处理返回的结果集插入目标表。

下面的示例演示了如何执行存储过程usp_getorders并将结果插入到Orders表中:

INSERT INTO dbo.Orders(orderid, orderdate, empid, custid)

EXEC TSQLFundamentals2008.Sales.usp_getorders @country = 'France';

④ SELECT INTO语句:它会创建一个目标表,并用查询返回的结果来填充它。需要注意的是: 它不是一个标准的SQL语句(即不是ANSI SQL标准的一部分) ,不能用这个语句向已经存在的表中插入数据。

--保证目标表不存在

IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;


SELECT orderid, orderdate, empid, custid

INTO dbo.Orders

FROM TSQLFundamentals2008.Sales.Orders;

⑤ BULK INSERT语句: 用于将文件中的数据导入一个已经存在的表,需要制定目标表、源文件以及一些其他的选项。

下面的栗子演示了如何将文件"C:\testdata\orders.txt"中的数据容量插入(bulk insert)到Orders表,同时还指定了文件类型为字符格式,字段终止符为逗号,行终止符为换行符(\t):

BULK INSERT dbo.Orders FROM 'C:\testdata\orders.txt'

WITH

(

DATAFILETYPE = 'char',

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n'

);

2 看我花式删除数据

① DELETE语句: 标准SQL语句,大家最常见的用法。  

DELETE FROM dbo.Orders

WHERE orderdate < '20070101';

② TRUNCATE语句: 不是标准的SQL语句,永于删除表中的所有行,不需要过滤条件。

Tip TRUNCATE与DELETE在性能上差异巨大,对一个百万行级记录的表,TRUNCATE几秒内就可以解决,而DELETE可能需要几分钟。 因为TRUNCATE会以最小模式记录日志,而DELETE则以完整模式记录日志。 所以,各位,谨慎使用TRUNCATE。 因此,我们可以创建一个虚拟表(Dummy Table),让虚拟表包含一个指向产品表的外键,这样就可以保护产品表了。

③ 基于联接的DELETE: 也不是标准SQL语句,可以根据另一个表中相关行的属性定义的过滤器来删除表中的数据行。

例如,下面语句用以删除美国客户下的订单:

DELETE FROM O

FROM dbo.Orders AS O

JOIN dbo.Customers AS C

ON O.custid = C.custid

WHERE C.country = N'USA';

当然,如果要使用标准SQL语句,也可以采用下面的方式:

DELETE FROM dbo.Orders

WHERE EXISTS

(SELECT *

FROM dbo.Customers AS C

WHERE Orders.custid = C.custid

AND C.country = N'USA');

更新与合并数据

1 看我花式更新数据

① UPDATE语句: 不解释了,大家都在用

下面来看两个不一样的栗子,第一个是关于同时操作的性质。 看看下面的UPDATE语句:

UPDATE dbo.T1

SET col1 = col1 + 10, col2 = col1 + 10;

假设T1表中的col1列为100,col2列为200。 在计算后是多少呢?

答案揭晓: col=110,col=110。

再来看一个栗子,假设我们要实现两个数的交换该怎么做? 我们可能迫不及待的说出临时变量。 然而,在SQL中所有赋值表达式好像都是同时计算的,解决这个问题就不需要临时变量了。

UPDATE dbo.T1

SET col1 = col2, col2 = col1;

② 基于联接的UPDATE语句: 同样不是SQL标准语法,联接在此与基于联接的DELETE一样是起到过滤作用。

UPDATE OD

SET discount = discount + 0.05

FROM dbo.OrderDetails AS OD

JOIN dbo.Orders AS O

ON OD.orderid = O.orderid

WHERE custid = 1;

同样,要使用标准SQL语法的话,可以用子查询替代联接:

UPDATE dbo.OrderDetails

SET discount = discount + 0.05

WHERE EXISTS

(SELECT * FROM dbo.Orders AS O

WHERE O.orderid = OrderDetails.orderid

AND custid = 1);

③ 赋值UPDATE: 这是T-SQL特有的语法,可以对表中的数据进行更新的同时为变量赋值。 你不需要使用单独的UPDATE和SELECT语句,就能完成同样的任务。

假设我们有一个表Sequence,它只有一列val,全是序号数字。 我们可以通过赋值UPDATE得到一个新的序列值:

DECLARE @nextval AS INT;

UPDATE Sequence SET @nextval = val = val + 1;

SELECT @nextval;

2 新玩法:合并数据

SQL Server 2008引入了一个叫做 MERGE 的语句,它能在一条语句中根据逻辑条件对数据进行不同的修改操作(INSERT/UPDATE/DELETE)。 MERGE语句是SQL标准的一部分,而T-SQL版本的MERGE语句也增加了一些非标准的扩展。

下面我们看看如何合并,首先我们准备两张表Customers和CustomersStage:

--merge data

USE tempdb;


IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL DROP TABLE dbo.Customers;

GO


CREATE TABLE dbo.Customers

(

custid INT NOT NULL,

companyname VARCHAR(25) NOT NULL,

phone VARCHAR(20) NOT NULL,

address VARCHAR(50) NOT NULL,

CONSTRAINT PK_Customers PRIMARY KEY(custid)

);


INSERT INTO dbo.Customers(custid, companyname, phone, address)

VALUES

(1, 'cust 1', '(111) 111-1111', 'address 1'),

(2, 'cust 2', '(222) 222-2222', 'address 2'),

(3, 'cust 3', '(333) 333-3333', 'address 3'),

(4, 'cust 4', '(444) 444-4444', 'address 4'),

(5, 'cust 5', '(555) 555-5555', 'address 5');


IF OBJECT_ID('dbo.CustomersStage', 'U') IS NOT NULL DROP TABLE dbo.CustomersStage;

GO


CREATE TABLE dbo.CustomersStage

(

custid INT NOT NULL,

companyname VARCHAR(25) NOT NULL,

phone VARCHAR(20) NOT NULL,

address VARCHAR(50) NOT NULL,

CONSTRAINT PK_CustomersStage PRIMARY KEY(custid)

);


INSERT INTO dbo.CustomersStage(custid, companyname, phone, address)

VALUES

(2, 'AAAAA', '(222) 222-2222', 'address 2'),

(3, 'cust 3', '(333) 333-3333', 'address 3'),

(5, 'BBBBB', 'CCCCC', 'DDDDD'),

(6, 'cust 6 (new)', '(666) 666-6666', 'address 6'),

(7, 'cust 7 (new)', '(777) 777-7777', 'address 7');


-- Query tables

SELECT * FROM dbo.Customers;


SELECT * FROM dbo.CustomersStage;

执行结果如下图所示:

现在我们想要增加还不存在的客户,并更新已经存在的客户。 源表: CustomersStage,目标表: Customers。

MERGE INTO dbo.Customers AS TGT

USING dbo.CustomersStage AS SRC

ON TGT.custid = SRC.custid

WHEN MATCHED THEN

UPDATE SET

TGT.companyname = SRC.companyname,

TGT.phone = SRC.phone,

TGT.address = SRC.address

WHEN NOT MATCHED THEN

INSERT (custid, companyname, phone, address)

VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address);

谓词条件: TGT.custid=SRC.custid用于定义什么样的数据是匹配的,什么样的数据是不匹配的。

Tips MERGE语句必须以分号结束 而对于T-SQL中的大多数其他语句来说是可选的。但是,推荐遵循最佳实践,以分号结束。

高级数据更新方法

① 通过表表达式修改数据

-- 基于联接的UPDATE

UPDATE OD

SET discount = discount + 0.05

FROM dbo.OrderDetails AS OD

JOIN dbo.Orders AS O

ON OD.orderid = O.orderid

WHERE custid = 1;

-- 基于表表达式(这里是CTE)的UPDATE

WITH C AS

(

SELECT custid, OD.orderid,

productid, discount, discount + 0.05 AS newdiscount

FROM dbo.OrderDetails AS OD

JOIN dbo.Orders AS O

ON OD.orderid = O.orderid

WHERE custid = 1

)

UPDATE C

SET discount = newdiscount;

② 带有TOP选项的数据更新

-- 删除前50行

DELETE TOP(50) FROM dbo.Orders;

-- 更新前50行

UPDATE TOP(50) dbo.Orders

SET freight = freight + 10.00;

-- 基于CTE删除前50行

WITH C AS

(

SELECT TOP(50) *

FROM dbo.Orders

ORDER BY orderid

)

DELETE FROM C;

-- 基于CTE更新前50行

WITH C AS

(

SELECT TOP(50) *

FROM dbo.Orders

ORDER BY orderid DESC

)

UPDATE C

SET freight = freight + 10.00;

OUTPUT子句

在某些场景中,我们希望能够从修改过的行中返回数据,这时就可以使用OUTPUT子句。 SQL Server 2005引入了OUTPUT子句,通过在修改语句中添加OUTPUT子句,就可以实现从修改语句中返回数据的功能。

① 带有OUTPUT的INSERT语句

INSERT INTO dbo.T1(datacol)

OUTPUT inserted.keycol, inserted.datacol

SELECT lastname

FROM TSQLFundamentals2008.HR.Employees

WHERE country = N'USA';

② 带有OUTPUT的DELETE语句

DELETE FROM dbo.Orders

OUTPUT

deleted.orderid,

deleted.orderdate,

deleted.empid,

deleted.custid

WHERE orderdate < '20080101';

③ 带有OUTPUT的UPDATE语句

UPDATE dbo.OrderDetails

SET discount = discount + 0.05

OUTPUT

inserted.productid,

deleted.discount AS olddiscount,

inserted.discount AS newdiscount

WHERE productid = 51;

④ 带有OUTPUT的MERGE语句

MERGE INTO dbo.Customers AS TGT

USING dbo.CustomersStage AS SRC

ON TGT.custid = SRC.custid

WHEN MATCHED THEN

UPDATE SET

TGT.companyname = SRC.companyname,

TGT.phone = SRC.phone,

TGT.address = SRC.address

WHEN NOT MATCHED THEN

INSERT (custid, companyname, phone, address)

VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address)

OUTPUT $action, inserted.custid,

deleted.companyname AS oldcompanyname,

inserted.companyname AS newcompanyname,

deleted.phone AS oldphone,

inserted.phone AS newphone,

deleted.address AS oldaddress,

inserted.address AS newaddress;

以上MERGE语句使用OUTPUT子句返回被修改过的行的新旧版本的值。 对于INSERT操作不存在旧版本的值,因此所有deleted列的值都返回NULL。 $action函数会告诉我们输出行是UPDATE还是由INSERT操作生成的。

小结

本文介绍了MS SQL Server 2008的数据修改的多种方式,下一篇会介绍可编程对象。

参考资料

[美] Itzik Ben-Gan 著,成保栋 译,《Microsoft SQL Server 2008技术内幕: T-SQL语言基础》

考虑到很多人买了这本书,却下载不了这本书的配套源代码和示例数据库,特意上传到了百度云盘中 下载链接 :https://pan.baidu.com/s/1jIryBUA

强烈建议大家阅读完每一章节后, 练习一下课后习题 ,相信或多或少都会有一些收获。

The End

「  码字不易 ,也希望各位看官看完觉得还行就在本文右下方顺手点个“ 在看 ”,那就是对我 最大的鼓励! 如果觉得很好,也可以 转发 给你的朋友,让更多人看到,独乐乐不如众乐乐,是吧?

往期 精彩 回顾

恰童鞋骚年,风华也许不再正茂,但却仍想挥斥方遒

本公众号会长期关注和分享.NET Core,Microservice, Cloud Native ,DevOps、企业数字化转型等技术内容文章,还会与你分享个人生活成长的点滴及各类好书的读书笔记,希望能对你有所帮助,一起成长!

长按订阅更多精彩▼

点个 在看】 如何?

我来评几句
登录后评论

已发表评论数()

相关站点

+订阅
热门文章