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

Photo :MS SQL Server

文 | Edison Zhou

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

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

SQL Server体系结构

数据库的物理布局

数据库在物理上由数据文件和事务日志文件组成,每个数据库必须 至少有一个数据文件和一个日志文件

(1) 数据文件用于保存数据库对象数据 数据库必须至少有一个主文件组(Primary),而用户定义的文件组则是可选的。 Primary文件组包括 主数据文件(.mdf),以及数据库的系统目录(catalog)。 可以选择性地为Primary增加多个辅助数据文件(.ndf)。 用户定义的文件组只能包含辅助数据文件。

(2) 日志文件则用于保存SQL Server为了维护事务而需要的信息 虽然SQL Server可以同时写多个数据文件,但同一时刻只能以顺序方式写一个日志文件。

有关 .mdf、.ldf和.ndf

.mdf 代表 Master Data File,.ldf 代表 Log Data File,而 .ndf 代表 Not Master Data File(非主数据文件)

架构(Schema)和对象

一个数据库包含多个架构,而每个架构又包括多个对象。 可以将架构看作是各种对象的容器,这些对象可以是表(table)、视图(view)、存储过程(stored procedure)等等。

此外,架构也是一个命名空间,用作对象名称的前缀。 例如,假设在架构Sales中有一个Orders表,架构限定的对象名称是Sales.Orders。 如果在引用对象时省略架构名称,SQL Server将采用一定的办法来分析出架构名称是什么。 如果不显示指定架构,那么在解析对象名称时,就会要付出一些没有意义的额外代价。 因此,建议都加上架构名称。

查询

单表查询

(1)关于SELECT子句: 使用*号是糟糕的习惯

SELECT * FROM Sales.Shippers;

在绝大多数情况下,使用星号是一种糟糕的编程习惯,在此还是建议大家即使需要查询表的所有列,也应该显式地指定它们。

(2)关于FROM子句: 显示指定架构名称

通过显示指定架构名称,可以保证得到的对象的确是你原来想要的,而且还不必付出任何额外的代价。

(3)关于TOP子句: T-SQL独有关键字

① 可以使用PERCENT关键字按百分比计算满足条件的行数

SELECT TOP (1) PERCENT orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY orderdate DESC;

上面这条SQL就会请求最近更新过的前1%个订单。

② 可以使用WITH TIES选项请求返回所有具有相同结果的行

SELECT TOP (5) WITH TIES orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY orderdate DESC;

上面这条SQL请求返回与TOP n行中最后一行的排序值相同的其他所有行。

(4)关于OVER子句: 为行定义一个窗口以便进行特定的运算

OVER子句的优点在于能够在返回基本列的同时,在同一行对它们进行聚合; 也可以在表达式中混合使用基本列和聚合值列。

例如,下面的查询为OrderValues的每一行计算当前价格占总价格的百分比,以及当前价格占客户总价格的百分比 。

SELECT orderid, custid, val,

100.0 * val / SUM(val) OVER() AS pctall,

100.0 * val / SUM(val) OVER(PARTITION BY custid) AS pctcust

FROM Sales.OrderValues;

(5)子句的逻辑处理顺序

(6)运算符的优先级

(7)CASE表达式

① 简单表达式: 将一个值与一组可能的取值进行比较,并返回满足第一个匹配的结果;

SELECT productid,productname,categoryid,categoryname=(

CASE categoryid

WHEN 1 THEN 'Beverages'

WHEN 2 THEN 'Condiments'

WHEN 3 THEN 'Confections'

WHEN 4 THEN 'Dairy Products'

ELSE 'Unkonw Category'

END)

FROM Production.Products;

② 搜索表达式: 将返回结果为TRUE的第一个WHEN逻辑表达式所关联的THEN子句中指定的值。 如果没有任何WHEN表达式结果为TRUE,CASE表达式则返回ELSE子句中出现的值。 (如果没有指定ELSE,则默认返回NULL);

SELECT orderid, custid, val, valuecategory=(

CASE

WHEN val < 1000.00 THEN 'Less than 1000'

WHEN val BETWEEN 1000.00 AND 3000.00 THEN 'Between 1000 and 3000'

WHEN val > 3000.00 THEN 'More than 3000'

ELSE 'Unknown'

END

)

FROM Sales.OrderValues

(8)三值谓词逻辑: TRUE、FALSE与UNKNOWN

SQL支持使用NULL表示缺少的值,它使用的是三值谓词逻辑,代表计算结果可以使TRUE、FALSE与UNKNOWN。 在SQL中,对于UNKNOWN和NULL的处理不一致,这就需要我们在编写每一条查询语句时应该明确地注意到正在使用的是三值谓词逻辑。

例如,我们要请求返回region列不等于WA的所有行,则需要在查询过滤条件中显式地增加一个队NULL值得测试:

SELECT custid, country, region, city

FROM Sales.Customers

WHERE region <> N'WA'

OR region IS NULL;

另外,T-SQL对于NULL值得处理是先输出NULL值再输出非NULL值得顺序,如果想要先输出非NULL值,则需要改变一下排序条件,例如下面的请求:

select custid, region

from sales.Customers

order by (case

when region is null then 1 else 0

end), region;

当region列为NULL时返回1,否则返回0。 非NULL值得表达式返回值为0,因此,它们会排在NULL值(表达式返回1)的前面。 如上所示的将CASE表达式作为第一个拍序列,并把region列指定为第二个拍序列。 这样,非NULL值也可以正确地参与排序,是一个完整解决方案的查询。

(9)LIKE谓词的花式用法

① %(百分号)通配符

SELECT empid, lastname

FROM HR.Employees

WHERE lastname LIKE N'D%';

② _(下划线)通配符: 下划线代表任意单个字符

下面请求返回lastname第二个字符为e的所有员工

SELECT empid, lastname

FROM HR.Employees

WHERE lastname LIKE N'_e%';

③ [<字符列>]通配符: 必须匹配指定字符中的一个字符

下面请求返回lastname以字符A、B、C开头的所有员工

SELECT empid, lastname

FROM HR.Employees

WHERE lastname LIKE N'[ABC]%';

④  [<字符-字符>]通配符: 必须匹配指定范围内中的一个字符

下面请求返回lastname以字符A到E开头的所有员工:

SELECT empid, lastname

FROM HR.Employees

WHERE lastname LIKE N'[A-E]%';

⑤ [^<字符-字符>]通配符: 不属于特定字符序列或范围内的任意单个字符

下面请求返回lastname不以A到E开头的所有员工:

SELECT empid, lastname

FROM HR.Employees

WHERE lastname LIKE N'[^A-E]%';

⑥ ESCAPE转义字符

如果搜索包含特殊通配符的字符串(例如'%','_','['、']'等),则必须使用转移字符。 下面检查lastname列是否包含下划线:

SELECT empid, lastname

FROM HR.Employees

WHERE lastname LIKE N'%!_%' ESCAPE '!';

(10)两种转换值的函数: CAST和CONVERT

CAST和CONVERT都用于转换值的数据类型。

SELECT CAST(SYSDATETIME() AS DATE);

SELECT CONVERT(CHAR(8),CURRENT_TIMESTAMP,112);

需要注意的是,CAST是ANSI标准的SQL,而CONVERT不是。 所以,除非需要使用样式值,否则推荐优先使用CAST函数,以保证代码尽可能与标准兼容。

连接查询

(1)交叉联接: 返回笛卡尔积,即m*n行的结果集

-- CROSS JOIN

select c.custid, e.empid

from sales.Customers as c

cross join HR.Employees as e;

-- INNER CROSS JOIN

select e1.empid,e1.firstname,e1.lastname,

e2.empid,e2.firstname,e2.lastname

from hr.Employees as e1

cross join hr.Employees as e2;

(2)内联接: 先笛卡尔积,然后根据指定的谓词对结果进行过滤

select e.empid,e.firstname,e.lastname,o.orderid

from hr.Employees as e

join sales.Orders as o

on e.empid=o.empid;

虽然不使用JOIN这种ANSI SQL-92标准语法也可以实现联接,但强烈推荐使用ANSI SQL-92标准,因为它用起来更加安全。比如,假如你要写一条内联接查询,如果不小心忘记了指定联接条件,如果这时候用的是ANSI SQL-92语法,那么语法分析器将会报错。

(3)外联结: 笛卡尔积→对结果过滤→添加外部行

通过例子来理解外联结: 根据客户的客户ID和订单的客户ID来对Customers表和Orders表进行联接,并返回客户和他们的订单信息。 该查询语句使用的联接类型是左外连接,所以查询结果也包括那些没有发出任何订单的客户;

--LEFT OUTER JOIN

select c.custid,c.companyname,o.orderid

from sales.Customers as c

left outer join sales.Orders as o

on c.custid=o.custid;

另外,需要注意的是在对外联结中非保留值得列值进行过滤时,不要再WHERE子句中指定错误的查询条件。

例如,下面请求返回在2007年2月12日下过订单的客户,以及他们的订单。 同时也返回在2007年2月12日没有下过订单的客户。 这是一个典型的左外连接的案例,但是我们经常会犯这样的错误:

select c.custid,c.companyname,o.orderid,o.orderdate

from sales.Customers as c

left outer join sales.Orders as o

on c.custid=o.custid

where o.orderdate='20070212';

执行结果如下:      

这是因为对于所有的外部行,因为它们在o.orderdate列上的取值都为NULL,所以WHERE子句中条件o.orderdate='20070212'的计算结果为UNKNOWN,因此WHERE子句会过滤掉所有的外部行。

我们应该将这个条件搬到on后边:

select c.custid,c.companyname,o.orderid,o.orderdate

from sales.Customers as c

left outer join sales.Orders as o

on c.custid=o.custid

and o.orderdate='20070212';

这下的执行结果如下:     

子查询

(1)独立子查询: 不依赖于它所属的外部查询

例如下面要查询Orders表中订单ID最大的订单信息,这种叫做独立标量子查询,即返回值不能超过一个。

select orderid, orderdate, empid, custid

from sales.Orders

where empid=(select MAX(o.orderid) from sales.Orders as o);

下面请求查询返回姓氏以字符D开头的员工处理过的订单的ID,这种叫做独立多值子查询,即返回值可能有多个。

select orderid

from sales.Orders

where empid in (select e.empid

from hr.Employees as e

where e.lastname like N'D%');

(2)相关子查询: 必须依赖于它所属的外部查询,不能独立地调用它

例如下面的查询会返回每个客户的订单记录中订单ID最大的记录:

select custid, orderid, orderdate, empid

from sales.Orders as o1

where orderid=(select MAX(o2.orderid)

from sales.Orders as o2

where o2.custid=o1.custid);

简单地说,对于o1表中的每一行,子查询负责返回当前客户的最大订单ID。 如果o1表中某行的订单ID和子查询返回的订单ID匹配,那么o1中的这个订单ID就是当前客户的最大订单ID,在这种情况下,查询便会返回o1表中的这个行。

(3)EXISTS谓词: 它的输入是一个查询,如果子查询能够返回任何行,则返回True,否则返回False

例如下面的查询会返回下过订单的西班牙客户:

select custid, companyname

from sales.customers as c

where c.country=N'Spain' and exists (

select * from sales.Orders as o

where o.custid=c.custid);

同样,要查询没有下过订单的西班牙客户只需要加上NOT即可:

select custid, companyname

from sales.customers as c

where c.country=N'Spain' and not exists (

select * from sales.Orders as o

where o.custid=c.custid);

对于EXISTS,它采用的是二值逻辑(TRUE和FALSE),它只关心是否存在匹配行,而不考虑SELECT列表中指定的列,并且无须处理所有满足条件的行。可以将这种处理方式看做是一种“短路”,它能够提高处理效率。

另外,由于EXISTS采用的是二值逻辑,因此相较于IN要更加安全,可以避免对NULL值得处理。

(4)高级子查询

① 如何表示前一个或后一个记录?

逻辑等式: 上一个->小于当前值的最大值; 下一个->大于当前值的最小值;

-- 上一个订单ID

select orderid, orderdate, empid, custid,

(

select MAX(o2.orderid)

from sales.Orders as o2

where o2.orderid<o1.orderid

) as prevorderid

from sales.Orders as o1;

② 如何实现连续聚合函数? 在子查询中连续计算

-- 连续聚合

select orderyear, qty,

(select SUM(o2.qty)

from sales.OrderTotalsByYear as o2

where o2.orderyear<=o1.orderyear) as runqty

from sales.OrderTotalsByYear as o1

order by orderyear;

执行结果如下图所示:      

③ 使用NOT EXISTS谓词取代NOT IN隐式排除NULL值: 当对至少返回一个NULL值的子查询使用NOT IN谓词时,外部查询总会返回一个空集。 (前面提到,EXISTS谓词采用的是二词逻辑而不是三词逻辑)

-- 隐式排除NULL值

select custid,companyname from sales.Customers as c

where not exists

(select *

from sales.Orders as o

where o.custid=c.custid);

又如以下查询请求返回每个客户在2007年下过订单而在2008年没有下过订单的客户:

select custid, companyname

from sales.Customers as c

where exists

(select * from sales.Orders as o1

where c.custid=o1.custid

and o1.orderdate>='20070101' and o1.orderdate<'20080101')

and not exists

(select * from sales.Orders as o2

where c.custid=o2.custid

and o2.orderdate>='20080101' and o2.orderdate<'20090101');

小结

本文介绍了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、企业数字化转型等技术内容文章,还会与你分享个人生活成长的点滴及各类好书的读书笔记,希望能对你有所帮助,一起成长!

长按订阅更多精彩▼

点个 在看】 如何?

我来评几句
登录后评论

已发表评论数()

相关站点

+订阅
热门文章