My Github

《MSSQL2008技术内幕:T-SQL语言基础》读书笔记（下）

索引：

一、SQL Server的体系结构

二、查询

三、表表达式

四、集合运算

五、透视、逆透视及分组

六、数据修改

七、事务和并发

八、可编程对象

五、透视、逆透视及分组

5.1 透视

　　所谓透视（Pivoting）就是把数据从行的状态旋转为列的状态的处理。其处理步骤为：

　　相信很多人在笔试或面试的时候被问到如何通过SQL实现行转列或列转行的问题，可能很多人当时懵逼了，没关系，下面我们通过例子来理解。

　　（1）准备数据

--1.0准备数据
USE tempdb;

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

CREATE TABLE dbo.Orders
(
  orderid   INT        NOT NULL,
  orderdate DATE       NOT NULL, -- prior to SQL Server 2008 use DATETIME
  empid     INT        NOT NULL,
  custid    VARCHAR(5) NOT NULL,
  qty       INT        NOT NULL,
  CONSTRAINT PK_Orders PRIMARY KEY(orderid)
);

INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES
  (30001, '20070802', 3, 'A', 10),
  (10001, '20071224', 2, 'A', 12),
  (10005, '20071224', 1, 'B', 20),
  (40001, '20080109', 2, 'A', 40),
  (10006, '20080118', 1, 'C', 14),
  (20001, '20080212', 2, 'B', 12),
  (40005, '20090212', 3, 'A', 10),
  (20002, '20090216', 1, 'C', 20),
  (30003, '20090418', 2, 'B', 15),
  (30004, '20070418', 3, 'C', 22),
  (30007, '20090907', 3, 'D', 30);

SELECT * FROM dbo.Orders;
View Code

　　这里使用了MS SQL2008的VALUES子句格式语法，这时2008版本的新特性。如果你使用的是2005及以下版本，你需要多个INSERT语句。最后的执行结果如下图所示：

　  　

　　（2）需求说明

　　假设我们要生成一个报表，包含每个员工和客户组合之间的总订货量。用以下简单的分组查询可以解决这个问题：

select empid,custid,SUM(qty) as sumqty 
from dbo.Orders
group by empid,custid;

　　该查询的执行结果如下：

　　

　　不过，假设现在要求要按下表所示的的格式来生成输出结果：

　　

　　这时，我们就需要进行透视转换了！

　　（3）使用标准SQL进行透视转换

　　Step1.分组：GROUP BY empid;

　　Step2.扩展：CASE WHEN custid='A' THEN qty END;

　　Step3.聚合：SUM(CASE WHEN custid='A' THEN qty END);

--1.1标准SQL透视转换
select empid,
    SUM(case when custid='A' then qty end) as A,
    SUM(case when custid='B' then qty end) as B,
    SUM(case when custid='C' then qty end) as C,
    SUM(case when custid='D' then qty end) as D
from dbo.Orders
group by empid;

　　执行结果如下图所示：

　　

　　（4）使用T-SQL PIVOT运算符进行透视转换

　　自SQL Server 2005开始引入了一个T-SQL独有的表运算符-PIVOT，它可以对某个源表或表表达式进行操作、透视数据，再返回一个结果表。

　　PIVOT运算符同样涉及前面介绍的三个逻辑处理阶段（分组、扩展和聚合）以及同样的透视转换元素，但使用的是不同的、SQL Server原生的语法。

　　下面是使用PIVOT运算符实现上面一样的效果：

select empid,A,B,C,D
from (select empid,custid,qty
      from dbo.Orders) as D
  pivot (sum(qty) for custid in (A,B,C,D)) as P;

　　其中，PIVOT运算符的圆括号内要指定聚合函数（本例中SUM）、聚合元素（本例中的qty）、扩展元素（custid）以及目标列名称的列表（本例中的A、B、C、D）。在PIVOT运算符的圆括号后面，可以为结果表制定一个别名。

Tip：使用PIVOT运算符一般不直接把它应用到源表（本例中的Orders表），而是将其应用到一个表表达式（该表表达式只包含透视转换需要的3种元素，不包含其他属性。）此外，不需要为它显式地指定分组元素，也就不需要再查询中使用GROUP BY子句。

5.2 逆透视

　　所谓逆透视（Unpivoting）转换是一种把数据从列的状态旋转为行的状态的技术，它将来自单个记录中多个列的值扩展为单个列中具有相同值得多个记录。换句话说，将透视表中的每个源行潜在地转换成多个行，每行代表源透视表的一个指定的列值。

　　还是通过一个栗子来理解：

　　（1）首先还是准备一下数据：

USE tempdb;

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

SELECT empid, A, B, C, D
INTO dbo.EmpCustOrders
FROM (SELECT empid, custid, qty
      FROM dbo.Orders) AS D
  PIVOT(SUM(qty) FOR custid IN(A, B, C, D)) AS P;

SELECT * FROM dbo.EmpCustOrders;
View Code

　　下面是对这个表EmpCustOrders的查询结果：

　　

　　（2）需求说明

　　要求执行你透视转换，为每个员工和客户组合返回一行记录，其中包含这一组合的订货量。期望的输出结果如下图所示：

　　

　　（3）标准SQL进行逆透视转换

　　Step1.生成副本：CROSS JOIN 交叉联接生成多个副本

　　Step2.提取元素：通过CASE语句生成qty数据列

　　Step3.删除不相关的交叉：过滤掉NULL值

select *
from (select empid, custid,
        case custid
            when 'A' then A
            when 'B' then B
            when 'C' then C
            when 'D' then D
        end as qty
      from dbo.EmpCustOrders
        cross join (VALUES('A'),('B'),('C'),('D')) as Custs(custid)) as D
where qty is not null;

　　执行结果如下图所示：

　 　

　　（4）T-SQL UNPIVOT运算符进行逆透视转换

　　和PIVOT类似，在SQL Server 2005引入了一个UNPIVOT运算符，它的作用刚好和PIVOT运算符相反，即我们可以拿来做逆透视转换工作。UNPIVOT同样会经历我们上面提到的三个阶段。继续上面的栗子，我们使用UNPIVOT来进行逆透视转换：

select empid, custid, qty
from dbo.EmpCustOrders
  unpivot (qty for custid in (A,B,C,D)) as U;

　　其中，UNPIVOT运算符后边的括号内包括：用于保存源表列值的目标列明（这里是qty），用于保存源表列名的目标列名（这里是custid），以及源表列名列表（A、B、C、D）。同样，在UNPIVOT括号后面也可以跟一个别名。

Tip：对经过透视转换所得的表再进行逆透视转换，并不能得到原来的表。因为你透视转换只是把经过透视转换的值再旋转岛另一种新的格式。

5.3 分组

　　首先了解一下分组集：分组集就是分组（GROUP BY子句）使用的一组属性（或列名）。在传统SQL中，一个聚合查询只能定义一个分组集。为了灵活而有效地处理分组集，SQL Server 2008引入了几个重要的新功能（他们都是GROUP BY的从属子句，需要依赖于GROUP BY子句）：

　　（1）GROUPING SETS从属子句

　　使用该子句，可以方便地在同一个查询中定义多个分组集。例如下面，我们定义了4个分组集：(empid,custid),(empid),(custid)和()：

--3.1GROUPING SETS从属子句
select empid,custid,SUM(qty) as sumqty
from dbo.Orders
group by 
  GROUPING SETS
  (
    (empid,custid),
    (empid),
    (custid),
    ()
   );

　　这个查询相当于执行了四个group by查询的并集。

　　（2）CUBE从属子句

　　CUBE子句为定义多个分组集提供了一种更简略的方法，可以把CUBE子句看作是用于生成分组的幂集。例如：CUBE(a,b,c)等价于GROUPING SETS[(a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),()]。下面我们用CUBE来实现上面的例子：

--3.2CUEE从属子句
select empid,custid,SUM(qty) as sumqty
from dbo.Orders
group by cube(empid,custid);

　　（3）ROLLUP从属子句

　　ROLLUP子句也是一种简略的方法，只不过它与CUBE不同，它强调输入成员之间存在一定的层次关系，从而生成让这种层次关系有意义的所有分组集。例如：CUBE(a,b,c)会生成8个可能的分组集，而ROLLUP则认为3个输入成员存在a>b>c的层次关系，所以只会生成4个分组集：(a,b,c),(a,b),(a),()。

　　下面我们假设想要按时间层次关系：订单年份>订单月份>订单日，以这样的关系来定义所有分组集，并未每个分组集返回其总订货量。可能我们用GROUPING SETS需要4行，然后使用ROLLUP却只需要一行：group by rollup(YEAR(orderdate),MONTH(orderdate),DAY(orderdate));

　　完整SQL查询如下：

--3.3ROLLUP从属子句
select
  YEAR(orderdate) as orderyear,
  MONTH(orderdate) as ordermonth,
  DAY(orderdate) as orderday,
  SUM(qty) as sumqty
from dbo.Orders
group by rollup(YEAR(orderdate),MONTH(orderdate),DAY(orderdate));

　　执行结果如下图所示：

　　 

　　（4）GROUPING_ID函数

　　如果一个查询定义了多个分组集，还想把结果行和分组集关联起来，也就是说，为每个结果行标注它是和哪个分组集关联的。SQL Server 2008中引入了一个GROUPING_ID函数，简化了关联结果行和分组集的处理，可以容易地计算出每一行和哪个分组集相关联。

　　例如，继续上面的例子，我们想要将empid,custid作为输入：

select 
  grouping_id(empid,custid) as groupingset,
  empid, custid, SUM(qty) as sumqty
from dbo.Orders
group by cube(empid,custid);
View Code

　　执行结果中会出现groupingset为0,1,2,3，分别代表了empid,custid的4个可能的分组集（(empid,custid),(empid),(custid),()）。

六、数据修改

6.1 插入与删除数据

6.1.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');
View Code

　　② 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'
    );

6.1.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');

6.2 更新与合并数据

6.2.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;

6.2.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;
View Code

　　执行结果如下图所示：

　　

　　现在我们想要增加还不存在的客户，并更新已经存在的客户。源表：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中的大多数其他语句来说是可选的。但是，推荐遵循最佳实践，以分号结束。

6.3 高级数据更新方法

　　① 通过表表达式修改数据

-- 基于联接的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;

6.4 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操作生成的。

　　

七、事务和并发

　　考虑到这一章的内容比较多而且十分重要，故将其单独整理成一篇文章，赶紧点击这里：《Microsoft SQL Server中的事务与并发》

　　另外，园子里还有一些十分不错的博文，请园友们自行前往学习浏览：

　　（1）Jackson，《30分钟全面解析-SQL事务+隔离级别+阻塞+死锁》

　　（2）BIWork，《SQL Server 中的事务与事务隔离级别以及如何理解脏读, 未提交读,不可重复读和幻读产生的过程和原因》

八、可编程对象

8.1 变量与批处理

　　（1）变量：DECLARE+SET/SELECT

　　DECLARE语句可以声明一个或多个变量，然后使用SET/SELECT语句可以把一个变量设置成指定的值。

　　① SET语句每次只能针对一个变量进行操作

--set方式
declare @i as int
set @i=10;

--SQL Server 2008可以在同一语句同时声明和初始化变量
declare @i as int = 10;

　　② SELECT语句允许从同一行中获得的多个值分配给多个变量。

--select方式
declare @firstname as nvarchar(20), @lastname as nvarchar(40);

select
  @firstname = firstname,
  @lastname = lastname
from hr.Employees
where empid=3;

select @firstname as firstname, @lastname as lastname;

　　SET语句比复制SELECT语句更加安全，因为它要求使用标量子查询来从表中提取数据。如果在运行时，标量子查询返回了多个值，则查询会失败。例如下面的代码在运行时会报错：

--set比select语句更安全
declare @empname as nvarchar(61);

set @empname = (select firstname + N' '+ lastname
                from hr.Employees
                where mgrid=2);
                
select @empname as empname;

　　

　　（2）批处理：客户端应用程序发送到SQL Server的一组单条或多条T-SQL语句，SQL Server将批处理语句作为单个可执行的单元。

　　下面是一个批处理的示例，但要注意的是如果批处理中存在语法错误，整个批处理是不会提交到SQL Server执行的。

-- A Batch as a Unit of Parsing
-- Valid batch
PRINT 'First batch';
USE TSQLFundamentals2008;
GO
-- Invalid batch
PRINT 'Second batch';
SELECT custid FROM Sales.Customers;
SELECT orderid FOM Sales.Orders; -- 这一句有语法错误，故整个批处理不能提交到SQL Server执行
GO
-- Valid batch
PRINT 'Third batch';
SELECT empid FROM HR.Employees;
GO

Tip：批处理和事务不同，事务是工作的原子工作单元，而一个批处理可以包含多个事务，一个事务也可以在多个批处理中的某些部分提交。当事务在执行中被取消或者回滚时，SQL Server会撤销自事务开始以来的部分活动，而不考虑批处理是从哪里开始的。

8.2 流程控制

　　（1）IF...ELSE

　　这个大家应该都知道，但是需要注意的是：T-SQL使用的是三值逻辑，当条件取值为FALSE或UNKNOWN时，都可以激活ELSE语句块。如果条件取值可能为FALSE或UNKNOWN（例如，涉及到NULL值），而且对每种情况需要进行不同的处理时，必须用IS NULL谓词对NULL值进行显式地测试。

　　下面的IF-ELSE代码演示了：如果今天是一个月的第一天，则对数据库进行完整备份；如果今天是一个月的最后一天，则对数据库进行差异备份（所谓差异备份，就是指只保存上一次完整备份以来做过的更新）。

IF DAY(CURRENT_TIMESTAMP) = 1
BEGIN
  PRINT 'Today is the first day of the month.';
  PRINT 'Starting a full database backup.';
  BACKUP DATABASE TSQLFundamentals2008
    TO DISK = 'C:\Temp\TSQLFundamentals2008_Full.BAK' WITH INIT;
  PRINT 'Finished full database backup.';
END
ELSE
BEGIN
  PRINT 'Today is not the first day of the month.'
  PRINT 'Starting a differential database backup.';
  BACKUP DATABASE TSQLFundamentals2008
    TO DISK = 'C:\Temp\TSQLFundamentals2008_Diff.BAK' WITH INIT;
  PRINT 'Finished differential database backup.';
END
GO

　　这里假设备份的文件路径目录C:Temp已经存在。

　　（2）WHILE：不解释了，各位应该都懂。

DECLARE @i AS INT;
SET @i = 1;
WHILE @i <= 10
BEGIN
  PRINT @i;
  SET @i = @i + 1;
END;
GO

8.3 游标

　　T-SQL中支持一种叫做游标的对象，可以用它来处理查询返回的结果集中的各行，以指定的顺序一次只处理一行。这种处理方式与使用基于集合的查询相反，普通的查询是把集合作为一个整体来处理，不依赖任何顺序。

　　换句话说，使用游标，就像是用鱼竿钓鱼，一次只能勾到一条鱼一样。而使用集合，就像用渔网捕鱼，一次能捕到整整一网鱼。因此，使用游标的场景我们应该多多斟酌。一般来说，如果按固定顺序一次处理一行的游标方式涉及到的数据访问要比基于集合的方式少得多，则使用游标会更加有效，前一篇提到的连续聚合就是这样的一个例子。

　　如何使用游标呢？

　    　

　　下面来看看一个实例，它使用游标来计算CustOrders视图中每个客户每个月的连续总订货量（连续聚合案例）：

-- Example: Running Aggregations
SET NOCOUNT ON;
USE TSQLFundamentals2008;

DECLARE @Result TABLE
(
  custid     INT,
  ordermonth DATETIME,
  qty        INT, 
  runqty     INT,
  PRIMARY KEY(custid, ordermonth)
);

DECLARE
  @custid     AS INT,
  @prvcustid  AS INT,
  @ordermonth DATETIME,
  @qty        AS INT,
  @runqty     AS INT;

DECLARE C CURSOR FAST_FORWARD /* read only, forward only */ FOR
  SELECT custid, ordermonth, qty
  FROM Sales.CustOrders
  ORDER BY custid, ordermonth;

OPEN C

FETCH NEXT FROM C INTO @custid, @ordermonth, @qty;

SELECT @prvcustid = @custid, @runqty = 0;

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @custid <> @prvcustid
    SELECT @prvcustid = @custid, @runqty = 0;

  SET @runqty = @runqty + @qty;

  INSERT INTO @Result VALUES(@custid, @ordermonth, @qty, @runqty);
  
  FETCH NEXT FROM C INTO @custid, @ordermonth, @qty;
END

CLOSE C;

DEALLOCATE C;

SELECT 
  custid,
  CONVERT(VARCHAR(7), ordermonth, 121) AS ordermonth,
  qty,
  runqty
FROM @Result
ORDER BY custid, ordermonth;
GO

　　执行结果如下图所示：

　　

8.4 临时表

　　有时需要把数据临时保存到表中，而且在有些情况下，我们可能不太想要使用永久性的表。在这种情况下，使用临时表可能会更方便。

　　（1）局部临时表：只对创建它的会话在创建级和对调用对战的内部级（内部的过程、函数、触发器等）是可见的，当创建会话从SQL Server实例断开时才会自动删除它。

　　创建临时局部表，只需要在命名时以单个#号作为前缀：

IF OBJECT_ID('tempdb.dbo.#MyOrderTotalsByYear') IS NOT NULL
  DROP TABLE dbo.#MyOrderTotalsByYear;
GO

SELECT
  YEAR(O.orderdate) AS orderyear,
  SUM(OD.qty) AS qty
INTO dbo.#MyOrderTotalsByYear
FROM Sales.Orders AS O
  JOIN Sales.OrderDetails AS OD
    ON OD.orderid = O.orderid
GROUP BY YEAR(orderdate);

SELECT Cur.orderyear, Cur.qty AS curyearqty, Prv.qty AS prvyearqty
FROM dbo.#MyOrderTotalsByYear AS Cur
  LEFT OUTER JOIN dbo.#MyOrderTotalsByYear AS Prv
    ON Cur.orderyear = Prv.orderyear + 1;
GO
View Code

　　（2）全局临时表：可以对其他所有会话都可见，当创建临时表的会话断开数据库的连接，而且也没有活动在引用全局临时表时，SQL Server才会自动删除相应的全局临时表。

　　创建全局局部表，只需要在命名时以两个#号作为前缀：

-- Global Temporary Tables
CREATE TABLE dbo.##Globals
(
  id  sysname     NOT NULL PRIMARY KEY,
  val SQL_VARIANT NOT NULL
);
View Code

8.5 动态SQL

　　SQL Server允许用字符串来动态构造T-SQL代码地一个批处理，接着再执行这个批处理，这种功能叫做动态SQL（Daynamic SQL）。

　　（1）使用EXEC（EXECUTE的缩写）命令

-- Simple example of EXEC
DECLARE @sql AS VARCHAR(100);
SET @sql = 'PRINT ''This message was printed by a dynamic SQL batch.'';';
EXEC(@sql);
GO
View Code

　　（2）使用sp_executesql存储过程

　　sp_executesql存储过程有两个输入参数和一个参数赋值部分：第一个参数需要指定包含想要运行的批处理代码地Unicode字符串，第二个参数是一个Unicode字符串，包含第一个参数中所有输入和输出参数的生命。接着为输入和输出参数指定取值，各参数之间用逗号分隔。

-- Simple example using sp_executesql
DECLARE @sql AS NVARCHAR(100);

SET @sql = N'SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderid = @orderid;';

EXEC sp_executesql
  @stmt = @sql,
  @params = N'@orderid AS INT',
  @orderid = 10248;
GO
View Code

Tips:

①sp_executesql存储过程在执行性能上比EXEC要好，因为它的参数化有助于重用缓存过的执行计划。

②sp_executesql存储过程在安全上也比EXEC要好，它的参数化也可以不必受SQL注入的困扰。

8.6 例程：用户定义函数、存储过程与触发器

　　（1）用户定义函数：封装计算的逻辑处理，有可能需要基于输入的参数，并返回结果。

　　下面的示例创建了一个用户定义函数dbo.fn_age，对于给定出生日期和事件日期，这个函数可以返回某个人在时间日期当时的年龄：

IF OBJECT_ID('dbo.fn_age') IS NOT NULL DROP FUNCTION dbo.fn_age;
GO

CREATE FUNCTION dbo.fn_age
(
  @birthdate AS DATETIME,
  @eventdate AS DATETIME
)
RETURNS INT
AS
BEGIN
  RETURN
    DATEDIFF(year, @birthdate, @eventdate)
    - CASE WHEN 100 * MONTH(@eventdate) + DAY(@eventdate)
              < 100 * MONTH(@birthdate) + DAY(@birthdate)
           THEN 1 ELSE 0
      END
END
GO
View Code

　　（2）存储过程：封装T-SQL代码地服务器端例程，可以有输入和输出参数，可以返回多个查询的结果集。

　　下面的示例创建了一个存储过程usp_GetCustomerOrders，它接受一个客户ID和日期范围作为输入参数，返回Orders表中由指定客户在指定日期范围内所下的订单组成的结果集，同时也将受查询影响的行为作为输出参数。

IF OBJECT_ID('Sales.usp_GetCustomerOrders', 'P') IS NOT NULL
  DROP PROC Sales.usp_GetCustomerOrders;
GO

CREATE PROC Sales.usp_GetCustomerOrders
  @custid   AS INT,
  @fromdate AS DATETIME = '19000101',
  @todate   AS DATETIME = '99991231',
  @numrows  AS INT OUTPUT
AS
SET NOCOUNT ON;

SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE custid = @custid
  AND orderdate >= @fromdate
  AND orderdate < @todate;

SET @numrows = @@rowcount;
GO

DECLARE @rc AS INT;

EXEC Sales.usp_GetCustomerOrders
  @custid   = 1, -- Also try with 100
  @fromdate = '20070101',
  @todate   = '20080101',
  @numrows  = @rc OUTPUT;

SELECT @rc AS numrows;
GO
View Code

Tips:存储过程可以封装业务逻辑处理，更好地控制安全性（有助于避免SQL注入），提高执行性能（减少网络通信流量）。

　　（3）触发器：一种特殊的存储过程，只要特定事件发生，就会调用触发器，运行它的代码。SQL Server支持两种类型相关的触发器，分别是：DML触发器和DDL触发器。

　　下面的示例演示了一个简单的DML触发器，对插入到表的数据进行审核（插入到Audit审核表）。

CREATE TRIGGER trg_T1_insert_audit ON dbo.T1 AFTER INSERT
AS
SET NOCOUNT ON;

INSERT INTO dbo.T1_Audit(keycol, datacol)
  SELECT keycol, datacol FROM inserted;
GO
View Code

8.7 错误处理

　　T-SQL代码中提供了一种成为TRY...CATCH的结构，在SQL Server 2005中引入的。

BEGIN TRY
  PRINT 10/2;
  PRINT 'No error';
END TRY
BEGIN CATCH
  PRINT 'Error';
END CATCH
GO

　　对于错误处理代码，在实际开发中，可以封装创建一个存储过程来重用错误代码。

参考资料

TSQLFundenmantals

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

考虑到很多人买了这本书，却下载不了这本书的配套源代码和示例数据库，特意上传到了百度云盘中，点此下载

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

 

posted @ 2016-11-30 00:34  EdisonZhou  阅读(3077)  评论(5)  编辑  收藏  举报