第二章单表查询 T-SQL语言基础(1)

单表查询(1)

本章:逻辑查询处理,特定的SELECT查询生成正确的结果集而要经历的一系列逻辑阶段;单表查询的其他方面,包括:运算符,空值(NULL),字符的处理和临时数据,分级(ranking),CASE表达式,以及查询元数据.

2.1 SELECT语句的元素

SELECT语句的目的是对表进行查询,应用一定的逻辑处理,并返回结果.

"逻辑查询处理"是指ANSI SQL定义的概念模型,规定了如何处理一个查询,以及如何取得最终的结果.

Microsoft SQL Server引擎并不教条地严格遵守逻辑查询处理;相反,在物理地处理一个查询时,它可以自由地调整处理阶段的顺序,只要最终的结果能够和逻辑查询处理的规定保持一致.

示例查询:

-- Listing 2-1: Sample Query
USE TSQLFundamentals2008;

SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)
HAVING COUNT(*) > 1
ORDER BY empid, orderyear;

逻辑查询处理的顺序:

1.FROM

2.WHERE

3.GROUP BY

4.HAVING

5.SELECT

6.ORDER BY

逻辑处理阶段的表述:

1.从Sales.Orders表中查询数据行.

2.对订单数据进行过滤,只保留客户ID等于71的记录.

3.按雇员ID和订单订单年份对订单数据进行分组.

4.对分组数据(雇员ID和订单年份)进行过滤,只保留具有多个订单的分组.

5.选择(返回)每个分组的雇员ID,订单年份,以及订单数量

6.按照雇员ID和订单年份对输出结果进行排序

**使用分号 ; 来结束SQL语句,这是一种标准,可以提高代码的可读性.

2.1.1 FROM 子句

FROM子句是在逻辑处理阶段第一个要处理的查询子句,用于指定要查询的表名,以及对这些表进行操作的表运算符.

**填写表名,应该总是用数据库架构来限定代码中的对象名称.通过显示指定架构名称,可以保证得到的对象的确是你原来想要的,而且不必付出任何额外的代价.

例如:FROM Sales.Orders

SELECT orderid, custid, empid, orderdate, freight
FROM Sales.Orders;

*以上的输出看起来是以特定的顺序返回(以orderid的升序排列)的,但不能保证绝对这样.因为这个阶段返回的是结果集,无序的结果集

*分隔标识符名称,对于符合常规标识符格式要求的标识符,分隔是可选的.

2.1.2 WHERE 子句

在WHERE子句中,可以指定一个谓词或逻辑表达式,从而过滤由FROM阶段返回的行.

只有能让逻辑表达式结果为TRUE的行(为false和unknown的行过滤掉),才能由WHERE阶段返回给候选的逻辑查询处理阶段.

SELECT orderid, empid, orderdate, freight
FROM Sales.Orders
WHERE custid = 71;

**索引对于查询的重要性:

WHERE子句对查询性能有重要影响.在过滤表达式的基础上,SQL Server会计算使用什么索引来访问请求的数据.与扫面整个表相比,通过使用索引,有时可以大幅减少SQL Server在获取请求的数据时付出的代价.

如果向调用者返回所有可能的行,让他们在客户端进行过滤,这样会产生大量的网络传输流量,而通过查询过滤器就可以减少这种不必要的网络传输.

2.1.3 GROUP BY 子句(分组)

GROUP BY阶段可以将前面逻辑查询处理阶段返回的行按"组"进行组合.每个组由在GRUOP BY子句中指定的各元素决定.

SELECT empid, YEAR(orderdate) AS orderyear
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate);

以上,根据每个雇员ID和订单年份值的唯一组合.

**如果查询涉及到分组,那么GROUP BY阶段之后的所有阶段(包括HAVING,SELECT,ORDERBY)的操作对象将是组,而不是单独的行.每个组最终也表示为查询结果集中的一行.这意味着在GROUP BY阶段之后处理的子句中指定的所有表达式务必保证为每个组只返回一个标量(单值).

**在每个组中各GROUP BY元素只唯一出现一次

注:如果有GROUP BY,在每个组中各GROUP BY元素只唯一出现一次!并且每个组返回的值时唯一的!

**所以不在GROUP BY列表中的函数要使用聚合函数,因为聚合函数只为每个组返回一个值,所以一个元素如果不再GROUP BY列表中出现,就只能作为聚合函数(COUNT,SUM,AVG,MIN,以及MAX)的输入.

SELECT
  empid,
  YEAR(orderdate) AS orderyear,
  SUM(freight) AS totalfreight,
  COUNT(*) AS numorders
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate);

以上: SUM(freight) 返回每组中所有运费值的总和,而函数COUNT(*)返回每组中行的个数(这里代表订单的数量)

**如果试图引用不在GROUP BY列表中出现的属性(如freight),而且也没有将其作为GROUP BY子句之后处理的任何子句中聚合函数的输入,SQL Server引擎就会报错(原因:这种情况下,无法保证表达式为每个组返回的值是唯一地).

/*
SELECT empid, YEAR(orderdate) AS orderyear, freight
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate);
*/

以上,错误的代码,freight 列在select阶段中会导致,每个组不一定会返回唯一地值

***所有的聚合函数都会忽略NULL值,但COUNT(*)不会,例如:假设一个组有5行,其qty列的值分别为30,10,NULL,10,10,表达式COUNT(*)返回5,因为这个组有5行;COUNT(qty)返回4,因为只有4行已知的值.不希望重复的值,可以通过DISTINCT, COUNT(DISTINCT qty)返回2.

以下返回每个雇员每年受理过的不重复(不同)的客户数量

SELECT 
  empid, 
  YEAR(orderdate) AS orderyear, 
  COUNT(DISTINCT custid) AS numcusts
FROM Sales.Orders
GROUP BY empid, YEAR(orderdate);

2.1.4 HAVING 子句

组过滤,HAVING与GROUP BY结合使用.

HAVING子句用于指定对组进行过滤的谓词或逻辑表达式,这与WHERE阶段对单独的行进行过滤相对应.

只有能让HAVING子句中的逻辑表达式为TRUE的组,HAVING阶段才会把这些组返回到下一个逻辑查询阶段.逻辑表达式计算结果为FALSE或UNKNOWN的组将被过滤掉.

*HAVING子句在对行进行分组之后处理的,可以引用聚合函数

SELECT empid, YEAR(orderdate) AS orderyear
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)
HAVING COUNT(*) > 1;

--COUNT(*)>1 意味着保留多行的组

2.1.5 SELECT 子句

SELECT子句用于指定需要在查询返回的结果集中包含的属性(列).

注:如果之前的逻辑阶段有GROUP BY,那么SELECT语句中列应该是GROUP BY分组中的列,或者是其他列的聚合函数.

**as 语句,用作别名,没有列名的根据标准应尽量取个别名

**别名的表达式:1.<表达式> as <别名>; 2.<别名> = <表达式> (别名等于表达式); 3.<表达式> <别名> (表达式空格别名).尽量用as语句,规范化

注:SELECT 语句的两个列一定要用逗号隔开,不然后面的会被当做是个别名

例如:

SELECT orderid orderdate
FROM Sales.Orders;

--以上,orderdate被当作是orderid的别名来对待,但用户应该想列出两个列,orderid和orderdate,所以之间应该加个逗号.

注意:SELECT子句是在FROM,WHERE,GROUP BY,以及HAVING子句后处理的.这意味着对于SELECT子句之前处理的那些子句,在SELECT子句中为表达式分配的别名并不存在.

例如:

/*
SELECT orderid, YEAR(orderdate) AS orderyear
FROM Sales.Orders
WHERE orderyear > 2006;
*/
--这个会报错,因为WHERE查询阶段,找不到orderyear列

以上,解决这个问题的一种方法就是在WHERE子句和SELECT子句中重复使用表达式:YEAR(orderdate)

SELECT orderid, YEAR(orderdate) AS orderyear
FROM Sales.Orders
WHERE YEAR(orderdate) > 2006;

注意:SQL Server能够标识在查询中重复使用的同一表达式(YEAR(orderdate)),所以,对于SQL Server来说,只需要计算一次表达式.

****在关系模型中,所有操作和关系都基于关系代数和关系(集合)中的结果.在SQL中,情况略有不同,因SELECT查询并不保证返回一个真正的集合(即,由唯一行组成的无序集合).首先,SQL不要求表必须符合集合的条件.SQL表可以没有键,行也不一定具有唯一性,在这些情况下表都不是集合,而是多集(multiset)或包(bag);其次,即使正在查询的表具有主键,也符合集合的条件,针对这个表的SELECT查询仍然可能返回包含重复的结果.在描述SELECT查询的输出时,经常会使用结果集(result set)这个术语.

**DISTINCT 子句用来删除重复的行

****SQL支持在SELECT列表中用(*)来选择查询表中的所有列,但是,注意在绝大多数情况下,不要用*,这是一个不好的习惯.查询所有的列,可以显示地指定它们的列名.

注意:不管分配别名的表达式是在试图引用它的表达式的左边还是右边,在SELECT子句内部也仍然不能够引用同一SELECT子句中创建的别名列.

2.1.6 ORDER BY 子句

ORDER BY子句用于展示数据时对输出结果中的行进行排序.

没有ORDER BY的情况? 理解SQL最重要的一点就是明白表不保证是有序的,因为表为了代表一个集合(如果有重复项,则是多集),而集合是无序的.这意味着,如果在查询表时不指定一个ORDER BY子句,那么虽然查询可以返回一个结果表,但SQL Server可以自由地按任意顺序对结果中的行进行排序.也就是说,每次查询的结果的排序都可能不一样! 所以,为了确保结果中的行按照一定的顺序进行排序,唯一的方法就是显示地指定一个ORDER BY子句.

返回游标? 如果指定了ORDER BY子句,查询结果将不符合表的要求,因为这时结果中的行将具有一定的顺序.带有ORDER BY子句的查询会生成一种ANSI称之为游标(cursor)的结果(一种非关系结果,其中的行具有固定的顺序).

查询返回表结果和游标,之间的区别? 在SQL中的某些语言元素和运算预期只对查询的表结果进行处理,而不能处理游标,例如表表达式和集合运算...

**升序 ASC,降序 DESC

** T-SQL 支持在ORDER BY子句中指定没有在SELECT子句中出现过的元素

注:当指定了DISTINCT以后,ORDER BY子句就被限制为只能选取在SELECT列表中出现的元素.原因:当指定DISTINCT时,一个结果行可能代表多个原始行;因此,可能无法清楚地知道应该使用ORDER BY列表值中对个可能值中的哪一个!

---------------------------------------------------------------------
-- The ORDER BY Clause
---------------------------------------------------------------------

-- Listing 2-4: Query Demonstrating the ORDER BY Clause
SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)
HAVING COUNT(*) > 1
ORDER BY empid, orderyear;

SELECT empid, firstname, lastname, country
FROM HR.Employees
ORDER BY hiredate;

/*
SELECT DISTINCT country
FROM HR.Employees
ORDER BY empid;
*/

2.1.7 TOP 选项 (结合ORDER BY)

TOP选项是T-SQL特有的,用于限制查询返回的行数或百分比.

当在查询中指定了ORDER BY子句时,TOP将依赖该子句来定义行的逻辑优先顺序.

-- Listing 2-5: Query Demonstrating the TOP Option
SELECT TOP (5) orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC;

--如果要从Orders表返回最近的5个订单,则可以在SELECT子句中指定TOP(5),并在ORDER BY子句中指定orderdate DESC

***从逻辑查询处理的角度来看,TOP选项是作为SELECT阶段的一部分而处理的,紧接着DISTINCT子句处理之后(如果存在DISTINCT).注意,当在查询中指定了TOP以后,ORDER BY子句就会起到双重作用.

首先,作为SELECT处理阶段一部分的TOP选项要依靠ORDER BY子句先为各个行定义它们的逻辑优先顺序,在这种优先顺序的基础上再去过滤其他请求.

其次,作为SELECT处理阶段之后的OPRDER BY阶段,与为了展示数据而对行进行排序的ORDER BY子句完全一样.

TOP阶段返回的是一个表结果还是游标??应该是游标,TOP阶段是依靠ORDER BY阶段排序后再进行处理的,ORDER BY阶段返回游标,所有TOP野应该返回游标?

当使用TOP时,同一ORDER BY子句既担当了为TOP决定行的逻辑优先顺序的角色,同时也担当了它的常规角色(展示数据),只是最终生成的结果由表变成了具有固定顺序的游标.

**在TOP选项中可以使用PERCENT关键字,在这种情况下,SQL Server会按照百分比来计算该返回的满足条件的行数(向上取整).

SELECT TOP (1) PERCENT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC;

注:查询的不确定性,可能是order by指定的列不具有唯一性,不是主键或唯一约束,会有多个重复的值,导致了不确定性!这时由物理方式决定,物理上最先访问到哪行,就选择相应的行.

可以为order by列表增加一个附加属性,确定返回的行具有唯一性!

SELECT TOP (5) orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC;

--以上, ORDER BY列的取值不是唯一的,因为在orderdate列上并没有定义主键或唯一性约束.多个行可能具有相同的订单日期.在没有指定附加属性的情况下,行(具有相同订单日期的行)之间的优先关系是没有定义的.这一事实让查询具有一定的不确定性,多个查询结果都可以认为是正确的,这时,SQL SERVER只是根据物理上最先访问到了哪行,就选择相应的行.

--附加属性是SQL查询中的一个非常重要的概念,附加属性是一个允许唯一地排列元素的属性或属性列表,它不一定是单个属性.

--order by 列表增加附加属性,orderid DESC,使返回的行具有唯一性.

SELECT TOP (5) orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC, orderid DESC;

**order by列表中增加附加属性,还可以请求返回所有具有相同结果的行.

通过增加一个WITH TIES 选项来定义,不过是返回TOP n行最后一行相同的其他所有行

SELECT TOP (5) WITH TIES orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC;

--以上,查询返回的5行,还能够请求返回与TOP n行中最后一行的排序值相同的其他所有行,

--增加 WITH TIES 选项

2.1.8 OVER 子句

OVER 子句用于为行定义一个窗口(这里的窗口是用户指定的一组行),以便进行特定的运算.可以把行的窗口简单地认为是运算将要操作的一个行的集合.

**聚合函数和排名函数都是可以支持OVER子句的运算类型.由于OVER子句为这些函数提供了一个行的窗口,所以这些函数也称之为开窗函数.

注:聚合函数的要点就是要对一组值进行聚合,聚合函数传统上一直以GROUP BY查询作为操作的上下文.

聚合开窗函数使用OVER子句提供窗口作为上下文,对窗口中的一组值进行操作,而不是使用GROUP BY子句提供的上下文.这样就不必对数据进行分组,还能够在同一行中同时返回基础行的列和聚合列.

**带有空的圆括号的OVER子句会提供所有行进行计算,所有行是指在FROM,WHERE,GROUP BY,以及HAVING处理阶段完成后仍然可用的那些行.注意,只有在SELECT和ORDER BY处理阶段才允许使用OVER子句.

**如果想对行进行限制或分区,则可以使用PARTITION BY子句.

SELECT orderid, custid, val,
  SUM(val) OVER() AS totalvalue,
  SUM(val) OVER(PARTITION BY custid) AS custtotalvalue
FROM Sales.OrderValues;

-- SUM(val) OVER() 是指对SELECT阶段操作的所有行计算其总价格
-- OVER() 返回的所有行,SUM(val) 对价格行计算其总价格(所有val行求和)
 
--SUM(val) OVER(PARTITION BY custid) 是指返回当前客户(和当前行具有相同custid的所有行)的总价格.

--结果行中totalvalue列表示所有行的价格总数. custtotalvalue列表示所有行中与当前行具有相同custid值的那些行的价格总数

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

SELECT orderid, custid, val,
  100. * val / SUM(val) OVER() AS pctall,
  100. * val / SUM(val) OVER(PARTITION BY custid) AS pctcust
FROM Sales.OrderValues;

--

**OVER子句支持四种排名函数:ROW_NUMBER(行号),RANK(排名),DENSE_RANK(密集排名),以及NTILE

SELECT orderid, custid, val,
  ROW_NUMBER() OVER(ORDER BY val) AS rownum,
  RANK()       OVER(ORDER BY val) AS rank,
  DENSE_RANK() OVER(ORDER BY val) AS dense_rank,
  NTILE(10)   OVER(ORDER BY val) AS ntile
FROM Sales.OrderValues
ORDER BY val;

--ROW_NUMBER 函数用于为查询的结果集中的各行分配递增的序列号,其逻辑顺序通过OVER子句中的ORDER BY语句进行指定.将生成唯一的行号值.
--order by如果不能确定行的唯一性,则可能出现多种正确的结果;如果可以确定唯一行,则计算结果就成为确定的.


--RANK和DENSE_RANK,与ROW_NUMBER类似,但它们为具有相同逻辑排序值的所有行生成同样的排名.
--RANK:表示之前有多少行具有更低的排序值
--DENSE_RANK:表示之前有多少个更低的排序值
--区别理解:比如:1,2,3,3,5  
--        ROW_NUMBER行号为1,2,3,4,5
--        RANK的行号为:1,2,3,3,5
--        DENSE_RANK行号为:1,2,3,3,4


--NTILE函数可以把结果中的行关联到组(tile,相当于由行组成的指定书目的组),并未每一行分配一个所属的组的编号.
--NTILE函数接受一个表示组的数量的输入参数,并要在OVER子句中指定逻辑顺序.
--注:NTILE函数在逻辑上需要依赖于ROW_NUMBER函数
--如果组数无法整除表的行数,余数中的每一行会被分配到最前面的每个组.例如,假设有102行,请求5组,那么前两组将有21行而不是20行.

***排名函数也支持在OVER子句中使用PARTITION BY语句.

SELECT orderid, custid, val,
  ROW_NUMBER() OVER(PARTITION BY custid
                    ORDER BY val) AS rownum
FROM Sales.OrderValues
ORDER BY custid, val;

--行号是为每个客户独立计算的

注意:OVER子句中指定的ORDER BY逻辑与数据展示没什么关系,并不会改变查询结果表最终的任何内容.

如果在SELECT处理阶段指定了开窗函数,开窗计算会在DISTINCT子句(如果存在)之前进行处理.

SELECT DISTINCT val, ROW_NUMBER() OVER(ORDER BY val) AS rownum
FROM Sales.OrderValues;
--ROW_NUMBER函数是在DISTINCT子句之前处理的.
--首先,为OrderValues视图中的830行分配唯一的行号.
--接着再处理DISTINCT子句,所以这时不会有任何重复的行要删除
--注:同一SELECT子句中不同时指定DISTINCT和ROW_NUMBER是一条最佳实践原则,因为DISTINCT子句在这种情况下不起任何作用.


SELECT val, ROW_NUMBER() OVER(ORDER BY val) AS rownum
FROM Sales.OrderValues
GROUP BY val;
--这个就可以删除VAL重复的值了,因为分了组

目前的逻辑处理顺序: