当前位置：Gxlcms > 数据库问题 > SQL Server2012 T-SQL基础教程--读书笔记（5-7章）

SQL Server2012 T-SQL基础教程--读书笔记（5-7章）

时间：2021-07-01 10:21:17 帮助过：1人阅读

SELECT * FROM (SELECT * FROM Sales.Customers WHERE country = ‘USA‘) AS USACusts

有效定义的表表达式的查询必须满足3个要求：

  1. 无法保证顺序。标准SQL是不允许 ORDER BY 子句出现在定义的表表达式查询中的，除非 ORDER BY 用于展示之外的其他目的。如：使用 OFFSET-FETCH 或 TOP 筛选。

  2. 所有列必须具有名称。必须为所有列分配列别名。

  3. 所有列名必须是唯一的。

5.1.1 分配列别名

--1.内嵌方式
SELECT * FROM (SELECT YEAR(orderdate) AS orderyear,custid FROM Sales.Orders) AS D
--2.外部形式
SELECT * FROM (SELECT YEAR(orderdate),custid FROM Sales.Orders) AS D(orderyear, custid)

通常建议使用内嵌别名形式，这样调试代码时可以直接选定定义表表达式来直接运行，在结果中就可以直观的别名显示出来。如果不打算再进行任何进一步的修改的话，并且希望将其看作一个“黑匣子”时使用外部形式分配列别名更好点。

5.1.2 使用参数

5.1.3 嵌套

5.1.4 多个引用

5.2 公用表表达式

公用表表达式（CTE）是表表达式的另一种标准形式，与派生表非常相似。
语法:

;WITH CTE_NAME AS ( inner_query ) outer_query

注意，T-SQL中的 WITH 子句可以用于不同的目的，为避免报错，建议在使用CTE时，要在 WITH 前加分别（;）

5.2.1 分别列别名

CTE中也是有两种方式分配列别名

--外部方式
;WITH CTE_NAME（col1,col2） AS( inner_query ) outer_query

5.2.2 使用参数

5.2.3 定义多个CTE

;WITH c1 AS (SELECT YEAR(orderdate) AS orderyear FROM Sales.Orders)
,c2 AS ( SELECT count(*) total FROM c1 )
SELECT * FROM c2

5.2.4 CTE的多次引用

就外部查询的 FROM 子句而言， CTE在其之前已经存在了，因此可以对同一个CTE进行多次引用。

5.2.5 递归CTE

递归CTE至少由两个查询定义，至少一个查询作为定位点成员，一个查询作为递归成员。基本递归CTE的一般形式如下:

;WITH <CTE_name>[<targe_column_list>]
AS
(
    <anchor_member> 
    UNION ALL
    <recursive_member>
)
<outer_query_against_CTE>

定位点成员是一个返回有效关系结果表的查询，就像一个用于定义非递归表表达式的查询。定点成员查询仅调用一次。
递归成员是一个引用CTE名称的查询。递归成员多次调用，直到它返回一个空集合或超过某些限制为止。
在外部查询中引用CTE名称代表的是定位点成员调用和所有递归成员调用的组合结果集。

--利用递归CTE返回某个雇员和其各级下属雇员（直接或间接）

;WITH EmpsCTE AS
(
  --定位点成员
  SELECT empid, mgrid, firstname, lastname
  FROM HR.Employees 
  WHERE empid = 2
  UNION ALL
  --递归成员
  SELECT e.empid, e.mgrid, e.firstname, e.lastname 
  FROM EmpsCTE p
    INNER JOIN HR.Employees AS e ON p.empid = e.mgrid
) 
SELECT * FROM EmpsCTE

递归成员联接CTE代表的是上一个结果集。 然后从 Employees 表检索由上一个结果集中返回的直接下属。
在出现递归成员的联接谓词逻辑错误或是数据的循环结果错误，递归成员可能会调用无数次。作为一项安全措施，SQL SERVER默认情况下限制递归成员可以被调用的次数为100。可以在外部查询的尾部指定 OPTION(MAXRECURSION n) 提示来更改默认的最大递归限制，n范围为0-32767。

5.3 视图

表表达式的范围只是在单查询语句之中，视图 和 内嵌表值函数（内嵌TVF） 是两种可重复使用的表表达式类型，其定义被存储为数据库对象。只有在显式删除它们时才从数据库中移除掉。
语法：

IF OBJECT_ID(‘Sales.USACusts‘) IS NOT NULL
  DROP VIEW Sales.USACusts
GO
CREATE VIEW Sales.USACusts
  AS 
SELECT custid,companyname ROM Sales.Customers WHERE country = ‘USA‘
--查询
SELECT * FROM Sales.USACusts

注意，不建议使用 SELECT *，因为当TABLE的添加或删除列时，VIEW 的元数据并不会跟着改变，可以使用 sp_refreshview 或 sp_refreshsqlmodule 来刷新 VIEW 的元数据，但是为了避免混淆，最好是通过 ALTER VIEW 来进行显式的添加或删除 TABLE 对应的列。

5.3.1 视图和ORDER BY 子句

用于展示的 ORDER BY 子句不允许出现在定义表表达式的查询中，因为关系表的行之间没有顺序可言。试图创建一个有序的VIEW是荒谬的，因为违反了关系模型定义的基本特性。
当然你可以通过 TOP(100) 和 OFFSET 0 ROWS 与 ORDER BY 子句来创建VIEW。当查询VIEW时得到的结果可能会是有序的，但是这个结果是不确定的，这种情况是数据库优化造成的。所以，不要混淆用于定义表表达式和非定义表表达式查询的行为。

5.3.2 视图选项

当创建或更改视图时，可以指定作为视图定义一部分的视图属性和选项。在视图的头部，在 WITH 子句下面可以指定如ENCRYPTION和SCHEMABINDING属性，可以在查询的尾部指定WITH CHECK OPTION。

1.ENCRYPTION选项
ENCRYPTION 可用于创建或更改 VIEW、Stored Procedure、Trigger 或 用户定义函数（UDF user define function） 时。ENCRYPTION选项指示SQL SERVER在内部以代码混淆方式存储对象定义文本。

--由于创建视图时没有使用ENCRYPTION，可以得到创建视图的定义语句
SELECT OBJECT_DEFINITION(OBJECT_ID(‘Sales.USACusts‘))

--使用ENCRYPTION，偷懒使用了 * 创建。再使用上面这个语句得到的是NULL
CREATE VIEW Sales.USACusts WITH ENCRYPTION
AS
SELECT * FROM Sales.Customers

/*
作为OBJECT_DEFINITION函数的替代方法，可以执行下面这个存储过程来获取对象的定义，但是你发现返回的是“2 Procedure sp_helptext. The text for object ‘Sales.USACusts‘ is encrypted. SQL.sql 126 25 ” 
*/
EXECUTE sys.sp_helptext ‘Sales.USACusts‘

2.SCHEMABINDING
可对VIEW和UDF使用，它将被引用对象的架构和列绑定到引用对象的架构中。它指示不能删除被引用对象，也不能删除或修改被引用的列。

CREATE VIEW Sales.USACusts WITH SCHEMABINDING
AS
SELECT custid,companyname FROM Sales.Customers WHERE country = ‘USA‘
/*
尝试从Customers表中删除companyname，报“ALTER TABLE DROP COLUMN companyname failed because one or more objects access this column. SQL.sql 14 1 ”
*/
ALTER TABLE Sales.Customers DROP COLUMN companyname

如果使用SCHEMABINDING选项，可以避免被引用对象或列的改变或删除导致的运行时错误，其实有点像外键约束一样。
注意，使用SCHEMABINDING选项时SELECT语句不能使用星号（*）查询，否则报错。Procedure USACusts. Syntax ‘*‘ is not allowed in schema-bound objects. SQL.sql 12 8
此外，在引用对象时，必须使用架构限定的两部分名称。

3.CHECK OPTION选项
使用此选项的目的是防止出现视图修改与视图筛选的冲突。假如定义了一个视图 USACusts，用于筛选国家为‘USA‘的客户，而没有使用CHECK OPTION选项，那么其它国家的客户也是可以成功插入到此视图中。如果你想防止出现此种冲突，那么可以在定义视图查询的尾部添加WITH CHECK OPTION来实现。这与检查约束类似。

CREATE VIEW Sales.USACusts WITH SCHEMABINDING
AS
SELECT custid,companyname,country FROM Sales.Customers WHERE country = ‘USA‘
WITH CHECK OPTION
/*
插入国家为‘UK‘的数据，报“An explicit value for the identity column in table ‘Sales.USACusts‘ can only be specified when a column list is used and IDENTITY_INSERT is ON. SQL.sql 15 13 ”
*/
INSERT INTO Sales.USACusts VALUES (32,‘Customer TEST‘,‘UK‘)

5.4 内嵌表值函数(TVF)

内嵌TVF（Table-valued Functions） 是支持输入参数的可重复使用的表表达式。除了支持输入参数之外，其他方面基本与视图类似。可以看作是参数化视图
语法:

--创建TVF
CREATE FUNCTION dbo.GetCustOrders
  (@cid AS INT) RETURNS TABLE
AS
RETURN
  SELECT *
  FROM Sales.Orders
  WHERE custid = @cid
  
 --使用
 SELECT c.* FROM dbo.GetCustOrders(1) AS c

5.5 APPLY 运算符

APPLY 运算符支持 CROSS APPLY 和 OUTER APPLY，前者仅实施一个逻辑查询处理阶段，而后者实施了两个阶段。
注：标准SQL叫做LATERAL，APPLY不是标准SQL。
APLLY运算符对两个输入表进行操作，第二个表可以是一个表表达式（通常为派生表或内联TVF）。 CROSS APPLY 运算符的逻辑查询处理阶段是：它将右侧的表表达式应用到左侧表的每一行，并生成一个组合结果集的结果表。与交叉联接非常类似。

SELECT s.shipperid,e.empid
FROM Sales.Shippers s 
    CROSS JOIN HR.Employees e

SELECT s.shipperid,e.empid
FROM Sales.Shippers s 
    CROSS APPLY HR.Employees e

以上两个SQL语句运行的结果是一致的。但是，CROSS APPLY 运算符右侧的表表达式可以对来自左侧表的每一行表示一个不同的行集，这是与联接不同的。可以在右侧表（派生表或内嵌TVF）中引用（传递）左侧表的属性。

--返回每个客户的最近3个订单
SELECT c.custid, A.orderid, A.orderdate
FROM Sales.Customers c 
    CROSS APPLY 
    ( SELECT TOP 3 o.orderid, o.empid, o.orderdate, o.requireddate 
      FROM Sales.Orders o 
      WHERE o.custid = c.custid 
      ORDER BY o.orderdate DESC, o.orderid DESC) A

可以将表表达式A看作是一个相关子查询。
CROSS APPLY 运算符类似于内联接，若右侧表中没有对应的结果，则左侧的行也不会返回。如果想返回左侧的行，则可使用 OUTER APPLY 。
出于封装的目的，可以使用内嵌TVF代替派生表，这样代码更容易维护和跟踪，可读性更高。

--创建TVF
CREATE FUNCTION TopOrders
  (@cid INT, @n INT) RETURNS TABLE
AS
RETURN
  SELECT TOP (@n) orderid, empid, orderdate, requireddate
  FROM Sales.Orders 
  WHERE custid = @cid
  ORDER BY orderdate DESC, orderid DESC

--代替派生表
SELECT c.custid, A.orderid, A.orderdate
FROM Sales.Customers c
  OUTER APPLY dbo.TopOrders(c.custid, 3) A

运行结果：技术分享

练习

--1.1 返回每个雇员在orderdate列中的最大值，表：Sales.Orders
SELECT empid, MAX(orderdate) AS maxorderdate 
FROM Sales.Orders
GROUP BY empid 
--1.2 根据1.1的派生表和Orders表之间的关联查询，返回每个雇员最大订单日期的订单。
SELECT o1.empid, o1.orderdate, o1.orderid, o1.custid
FROM Sales.Orders  o1
INNER JOIN (
    SELECT empid, MAX(orderdate) AS maxorderdate 
    FROM Sales.Orders
    GROUP BY empid ) AS o2 
ON o1.empid = o2.empid AND o1.orderdate = o2.maxorderdate

--2.1 计算orderdate、orderid排序的每个订单的行号，表：orders
SELECT ROW_NUMBER() OVER (ORDER BY orderdate, orderid) AS rownum
    ,orderid, orderdate, custid, empid
FROM Sales.Orders
--2.2 返回2.1行号为11-12的行。使用CTE封装2.1的代码
;WITH fetchOrdersCTE AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY orderdate, orderid) AS rownum
        ,orderid, orderdate, custid, empid
    FROM Sales.Orders
)
SELECT * 
FROM fetchOrdersCTE 
ORDER BY 1
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

--3 使用CTE，返回Zoya Dologopyatova(empid 9)的领导管理链，表：Employees
;WITH empsCTE AS
(
    --锚点成员
    SELECT empid,mgrid,lastname,firstname FROM HR.Employees 
    WHERE firstname = ‘zoya‘
    UNION ALL
    --递归成员
    SELECT e.empid,e.mgrid,e.lastname, e.firstname
    FROM HR.Employees e
        INNER JOIN empsCTE cte ON e.empid = cte.mgrid
)
SELECT * FROM empsCTE

--4.1 创建一个视图，返回每位雇员每年的总销量，表：orders,orderdetails
CREATE VIEW Sales.VEmpOrders
AS
SELECT o.empid, YEAR(o.orderdate) AS orderyear, SUM(od.qty) AS qty
FROM Sales.Orders o
    INNER JOIN Sales.OrderDetails od ON o.orderid = od.orderid
GROUP BY o.empid, YEAR(O.orderdate)
SELECT * FROM Sales.VEmpOrders ORDER BY 1, 2

--4.2 使用4.1的视图，返回每个雇员每年的运行总销量
SELECT * ,(SELECT SUM(qty) FROM Sales.VempOrders v2 WHERE v2.orderyear <= v1.orderyear AND v2.empid = v1.empid) as runqty
FROM Sales.VEmpOrders v1
GROUP BY empid, orderyear,qty
ORDER BY 1,2

/*
5.1 创建一个TVF,参数为供应商ID（@supid AS INT）和请求的产品数量（@n AS INT）。返回指定的供应商ID供应的@n个最高单价产品。表:products 
*/
CREATE FUNCTION Production.TopProducts
  (@supid INT, @n INT) RETURNS TABLE
AS
RETURN 
  SELECT TOP (@n) productid, productname, unitprice #
  FROM Production.Products
  WHERE supplierid = @supid 
  ORDER BY unitprice
  
SELECT * FROM Production.TopProducts(5,2)

--5.2 使用CROSS APPLY运算符和5.1中的TVF，为每个供应商返回两个最贵产品。
SELECT s.supplierid, s.companyname, t.productid, t.productname, t.unitprice
FROM Production.Suppliers s
    CROSS APPLY Production.TopProducts(s.supplierid,2) t

3.技术分享 5.2技术分享

CHAPTER 06 集合运算符

集合运算符是应用于两个输入集合之间的运算符，或者说是“多元集合（multisets）”，其结果来自于两个输入查询。
T-SQL 支持UNITON、INTERSECT、EXCEPT 集合运算符。ORDER BY可以随意应用于运算符的结果中。
集合运算符涉及的两个查询必须具有相同的列数，而且对应的类型必须兼容（数据类型可以根据优先级转换） 。列名（类型）由第一个查询来确定。
标准的SQL对每个运算符支持两种行为：DISTINCT（默认）和ALL，即不加ALL的查询语句默认都是去重的
集合运算符中认为两个NULL 值是相等的。

6.1 UNION运算符（并集）

如果后面有ALL则两个查询结果的重复项都会返回到最终的结果中去。
如何确定使用哪种情况？当需要使用重复的数据时就使用ALL了，当然如果确定不会有重复的数据时，建议使用UNION ALL，这样避免数据库检查重复项所导致的开销。

6.2 INTERSECT运算符（交集）

仅返回两个查询结果中同时出现的行。 INTERSECT 运算符可以使用内部联接（INNER JOIN）和 EXISTS 谓词来替代。在这两种情况下，两个查询中的 NULL 标记的比较的结果是 UNKONW ，所以带有 NULL 的行被过滤掉。所以如果有 NULL 标记时就需要注意了。
在标准SQL中是支持 INTERSECT ALL 这个运算行为的，但是在SQL SERVER 2012中尚未实现。INTERSECT ALL 即是说R行数据在第一个查询集合中出现x次，在第二个中出现的次数为y次，则最终返回的结果应该是min(x,y)次。我们可以通过 ROW_NUMBER 函数生成每个查询生成的次数，在 PARTITION BY 子句指定所有参与的属性，并在 ORDER BY 子句中使用 SELECT <CONSTANT> 指示顺序（其实排序序在这里没有什么卵用,SQL SERVER 会进行识别优化，不会进行相应的排序，所以也不会造成相关开销）。

--INTERSECT ALL 不应该返回rownum的，可以再查询一次去年这一列
SELECT ROW_NUMBER() OVER (PARTITION BY country, region, city ORDER BY (SELECT 0)) AS rownum
    ,country, region, city
FROM HR.Employees
INTERSECT
SELECT ROW_NUMBER() OVER (PARTITION BY country, region, city ORDER BY (SELECT 0)) AS rownum
    ,country, region, city
FROM Sales.Customers

6.3 EXCEPT 运算符（差集）

返回第一个查询集合中没有出现在第二个查询集合中的结果行。
EXCEPT 集合运算符在逻辑上首先消除两个查询集合中的重复行，再进行差值运算。可以使用仅筛选外部行的外联接和NOT EXISTS 谓词来替代 EXCEPT（有NULL标记时就要注意了）。
EXCEPT ALL的定义：R行在第一个查询集合中出现x次，在第二个出现y次，并且x>y，则R在 EXCEPT ALL 后出现x-y次。T-SQL也没有实现这一功能，这个也可以通过ROW_NUMBER来实现。

SELECT ROW_NUMBER() OVER (PARTITION BY country, region,city ORDER BY (SELECT 0)) AS rownum
    ,country, region, city
FROM HR.Employees
EXCEPT 
SELECT ROW_NUMBER() OVER (PARTITION BY country, region,city ORDER BY (SELECT 0)) AS rownum
    ,country, region, city
FROM Sales.Customers

6.4 优先级

集合运算符的优先级是： INTERSECT > UNION = EXCEPT，但使用括号能够使得代码阅读性更佳。

6.5 规避不支持的逻辑阶段

用于集合运算符输入的独立查询支持除 ORDER BY 之外的所有逻辑查询处理阶段（如表运算符，WHERE, GROUP BY, HAVING）。但是，仅有 ORDER BY 阶段允许用于运算符的结果，如果需要其他逻辑运算可以通过表表达式绕过此限制。定义一个基于使用集合运算符的查询的表表达式，可以在对表表达式的外部查询中应用任何所需的逻辑查询处理阶段。

/*
此查询演示了UNION的运行结果应用GROUP BY的逻辑处理阶段。同样，其他的逻辑查询处理阶段都可以在外部查询中应用
*/
SELECT u.country, COUNT(*) AS toatl
FROM (
    SELECT country, region, city FROM HR.Employees 
    UNION
    SELECT country, region, city FROM Sales.Customers 
) u
GROUP BY u.country

/*
如果需要一个带有TOP(OFFSET-FETCH)的查询参与集合运算符中，那么需要定义一个表表达式，并指定一个对此表表达式的的外部查询参与到集合运算符中即可。
*/
SELECT *  
FROM ( 
    SELECT empid,orderid,orderdate  
    FROM Sales.Orders 
    WHERE empid = 5 
    ORDER BY orderdate DESC, orderid DESC 
    OFFSET 0 ROWS FETCH FIRST 2 ROWS ONLY  
) o1 
UNION ALL 
SELECT * FROM( 
    SELECT empid,orderid,orderdate
    FROM Sales.Orders 
    WHERE empid = 3 
    ORDER BY orderdate DESC, orderid DESC 
    OFFSET 0 ROWS FETCH FIRST 2 ROWS ONLY  
) o2

 

技术分享
1456670207290.jpg

 

练习

  1. --1. 不使用循环结构生成一个1-10范围的10个数字虚拟辅助表，不需要保证顺序。 
  2. --2. 返回订单在200801而不是100802的客户和雇员，表Orders 
  3. SELECT o.custid, o.empid 
  4. FROM Sales.Orders o 
  5. WHERE o.orderdate >= ‘20080101‘ AND o.orderdate < ‘20080201‘ 
  6. EXCEPT 

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！