1. Dotnet9首页
  2. 更多分享
  3. 学习资料

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

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

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

表表达式 

表表达式是一种命名的查询表达式,代表一个有效的关系表。可以像其他表一样,在数据处理中使用表表达式。MSSQL中支持4种类型的表表达式:

派生表

派生表(也称为表子查询)是在外部查询的FROM子句中定义的,只要外部查询一结束,派生表也就不存在了。

例如下面代码定义了一个名为USACusts的派生表,它是一个返回所有美国客户的查询。外部查询则选择了派生表的所有行。

select *
from (select custid, companyname
      from sales.Customers
      where country='USA') as USACusts;

公用表表达式

公用表达式(简称CTE,Common Table Expression)是和派生表很相似的另一种形式的表表达式,是ANSI SQL(1999及以后版本)标准的一部分。

举个栗子,下面的代码定义了一个名为USACusts的CTE,它的内部查询返回所有来自美国的客户,外部查询则选择了CTE中的所有行:

WITH USACusts AS
(
    select custid, companyname
    from sales.Customers 
    where country=N'USA'
)
select * from USACusts;

和派生表一样,一旦外部查询完成,CTE的生命周期也就结束了。

视图

派生表和CTE都是不可重用的,而视图和内联表值函数却是可重用,它们的定义存储在一个数据库对象中,一旦创建,这些对象就是数据库的永久部分。只有用删除语句显式地删除,它们才会从数据库中移除。

下面仍然继续上面的例子,创建一个视图:

IF OBJECT_ID('Sales.USACusts') IS NOT NULL
   DROP VIEW Sales.USACusts;
GO
CREATE VIEW Sales.USACusts
AS
SELECT 
    custid, companyname, contactname, contacttitle, address,
    city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country=N'USA';
GO

使用该视图:

 SELECT * FROM Sales.USACusts; 

执行结果如下: 

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

内联表值函数

内联表值函数能够支持输入参数,其他方面就与视图类似了。

下面演示如何创建函数:

IF OBJECT_ID('dbo.fn_GetCustOrders') IS NOT NULL
   DROP FUNCTION dbo.fn_GetCustOrders;
GO
CREATE FUNCTION dbo.fn_GetCustOrders
    (@cid AS INT) RETURNS TABLE
AS
RETURN 
    SELECT
        orderid, custid, empid, orderdate, requireddate,
        shippeddate, shipperid, freight, shipname, shipaddress, shipcity,
        shipregion, shippostalcode, shipcountry
    FROM Sales.Orders
    WHERE custid=@cid;
GO

如何使用函数:

SELECT orderid, custid
FROM dbo.fn_GetCustOrders(1) AS CO;

执行结果如下:

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

总结

借助表表达式可以简化代码,提高代码的可维护性,还可以封装查询逻辑。

当需要使用表表达式,而且不计划重用它们的定义时,可以使用派生表或CTE,与派生表相比,CTE更加模块化,更容易维护。

当需要定义可重用的表表达式时,可以使用视图或内联表值函数。如果不需要支持输入,则使用视图;反之,则使用内联表值函数。

集合运算 

UNION并集运算

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

在T-SQL中。UNION集合运算可以将两个输入查询的结果组合成一个结果集。需要注意的是:如果一个行在任何一个输入集合中出现,它也会在UNION运算的结果中出现。T-SQL支持以下两种选项:

(1)UNION ALL:不会删除重复行

-- union all
select country, region, city from hr.Employees
union all
select country, region, city from sales.Customers;

结果得到100行: 

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

(2)UNION:会删除重复行

-- union
select country, region from hr.Employees
union
select country, region from sales.Customers;

结果得到34行:   

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

INTERSECT交集运算

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

在T-SQL中,INTERSECT集合运算对两个输入查询的结果取其交集,只返回在两个查询结果集中都出现的行。

INTERSECT集合运算在逻辑上会首先删除两个输入集中的重复行,然后返回只在两个集合中中都出现的行。换句话说:如果一个行在两个输入集中都至少出现一次,那么交集返回的结果中将包含这一行。

例如,下面返回既是雇员地址,又是客户地址的不同地址:

-- intersect
select country, region, city from hr.Employees
intersect
select country, region, city from sales.Customers;

执行结果如下图所示:

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

EXCEPT差集运算

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

在T-SQL中,集合之差使用EXCEPT集合运算实现的。它对两个输入查询的结果集进行操作,返回出现在第一个结果集中,但不出现在第二个结果集中的所有行。

EXCEPT结合运算在逻辑上首先删除两个输入集中的重复行,然后返回只在第一个集合中出现,在第二个结果集中不出现的所有行。换句话说:一个行能够被返回,仅当这个行在第一个输入的集合中至少出现过一次,而且在第二个集合中一次也没出现过。

此外,相比UNION和INTERSECT,两个输入集合的顺序是会影响到最后返回结果的。

例如,借助EXCEPT运算,我们可以方便地实现属于A但不属于B的场景,下面返回属于员工地址,但不属于客户地址的地址记录:

-- except
select country, region, city from hr.Employees
except
select country, region, city from sales.Customers;

执行结果如下图所示:

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

集合运算的优先级

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

SQL定义了集合运算之间的优先级:INTERSECT最高,UNION和EXCEPT相等。

换句话说:首先会计算INTERSECT,然后按照从左至右的出现顺序依次处理优先级相同的运算。

-- 集合运算的优先级
select country, region, city from Production.Suppliers
except
select country, region, city from hr.Employees
intersect
select country, region, city from sales.Customers;

上面这段SQL代码,因为INTERSECT优先级比EXCEPT高,所以首先进行INTERSECT交集运算。因此,这个查询的含义是:返回没有出现在员工地址和客户地址交集中的供应商地址。

使用表表达式避开不支持的逻辑查询处理

集合运算查询本身并不支持除ORDER BY以外的其他逻辑查询处理阶段,但可以通过表表达式来避开这一限制。

解决方案就是:首先根据包含集合运算的查询定义一个表表达式,然后在外部查询中对表表达式应用任何需要的逻辑查询处理。

(1)例如,下面的查询返回每个国家中不同的员工地址或客户地址的数量:

select country, COUNT(*) as numlocations
from (select country, region, city from hr.Employees
      union
      select country, region, city from sales.Customers) as U
group by country;

(2)例如,下面的查询返回由员工地址为3或5的员工最近处理过的两个订单:

select empid,orderid,orderdate
from (select top (2) empid,orderid,orderdate
    from sales.Orders
    where empid=3
    order by orderdate desc,orderid desc) as D1
union all
select empid,orderid,orderdate
from (select top (2) empid,orderid,orderdate
    from sales.Orders
    where empid=5
    order by orderdate desc,orderid desc) as D2;

小结 

本文介绍了MS SQL Server 2008的表表达式及集合运算,下一篇会介绍透视、逆透视 及 分组。

参考资料  

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

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

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

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

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

.NET Core on K8S学习与实践系列文章索引目录

.NET Core 微服务学习与实践系列文章索引目录

【资料】2019 .NET China Conf 大会资料下载

【视频】2019 .NET China Conf 大会视频发布

2019 .NET China Conf 路一直都在,社区会更好

基于Jenkins的开发测试全流程持续集成实践

基于Jenkins Pipeline的.NET Core持续集成实践

.NET单元测试的艺术系列文章索引目录

【推荐】.NET Core开发实战视频课程 ★★★

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

原文出处:微信公众号【 恰童鞋骚年】,作者【Edison Zhou】

原文链接:https://mp.weixin.qq.com/s?__biz=MzA4NzQzNTg4Ng==&mid=2651730088&idx=1&sn=d65e0acb52e5d18ce7f92530df4817c8&chksm=8bc3efa9bcb466bf3c26acecba853522e240f07835b580215573e110c3e97a3e03d812681234&mpshare=1&scene=1&srcid=&sharer_sharetime=1579046596660&sharer_shareid=cc609ea2d56165426b8c4ccd3a28d316&key=039e41916f0c5b31b65349e2f2253c7f602f91e8db644894aee3f8cf0605a4df1def8cc29c7d390406ad0464dc0b9515f4fd154a9605dc48f1af6d48f1eea319d0c5d2afa079a2b81bda1b2f675bab14&ascene=1&uin=Mjk4MzIyNjQxOQ%3D%3D&devicetype=Windows+10&version=62070158&lang=zh_CN&exportkey=A7IC9hjVRp59MA2DWsXNW68%3D&pass_ticket=6lhklhr2tGPLwcce7pdG0jWIFaBtUrcuIXWH%2B6l3BsdppFwbcnNNrnfbFlkM5JaJ

本文观点不代表Dotnet9立场,转载请联系原作者。

发表评论

登录后才能评论