基础加强—学习进阶(SQLServer2008宝典)

最佳实践：

——找到事物的最好办法就是查找，而不是先排除不是该事物的所有东西，一般来说（但不总是），将否定的WHERE条件声明为肯定的条件可改善性能。

 证明否定的命题是很难的，尤其是当空值包括在内时，因为空值的意思是“未知的”，正在搜索的列可能存在于列表中。总结：对于<> 、NOT IN 条件来说，如果IN括号列表中有空值，则每行都被判定为假，如果表中做条件的列中有空值，则空值行都被判定为假。

——使用BETWEEN搜索条件

BETWEEN搜索条件查找范围内的值，但是，该范围可能具有欺骗性，因为它是个包含两边界的。

举例：BETWEEN用来选择所有的数量大于9且小于20的工作订单。

SELECT WorkOrderID

FROM Production.WorkOrder

WHERE OrderQty BETWEEN 10 AND 19

——结果集排序

SQL可以按多列进行排序，且排序列不必是SELECT返回的列这样如何指定排序列具有很强的灵活性。

结果集排序内幕：

SELECT 语句中

1.如果未指定DISTINCT，你可以在ORDER BY 子句中指定不在最后结果集中列排序；是因为虽然Order by 子句在SELECT 语句后面但是，ANSI SQL:1999增强了对ORDER BY 的支持，允许访问SELECT 阶段的输入虚拟表和输出虚拟表

2.如果指定了DISTINCT，DISTINCT会改变了SELECT 返回的行数，在ORDER BY 子句中就只能指定返回结果集中的列排序。

3.需要注意的是，ORDER BY 这一步骤不同于其他步骤，他不返回有效的表，而是返回一个游标，所以使用了ORDER BY 子句的查询不能用作表表达式。

4.ORDER BY 步骤子句中列列表排序字段，是唯一一步可以使用SELECT 列表中的列别名的步骤。

——TOP 谓词的旧语法没有括号且不接受变量，SQLServer 2005 引入了带括号的新语法，WITH TIES 选项对于TOP ()谓词很重要，它允许最后位置包含多行。

 

标量函数最佳实践：

性能既是数据架构设计的一部分也是查询的一部分，设计通过WHERE 条件可搜索的方式存储数据，而不是依靠在查询时使用函数操作数据，虽然在结果集列的表达式中使用函数是不可避免的，但在WHERE条件中使用函数会强制每一行都计算该函数。此外，WHERE 子句中使用函数的另一个瓶颈是，Query Optimizer 无法使用索引查找——不得不使用扫描，造成更多的I/O

 

标量函数

字符串函数：

SUBSTRING(string,  starting position, length)

STUFF(string,  insertion position,  delete count, string inserted)

RIGHT(string, count)和LEFT(string, count) 返回字符串的最右边或最左边的部分

LEN(string)   返回字符串的长度

RIRIM(string) 和LTRIM(string)   删除前导或尾随空格  他们经常作为 RIRIM(LTRIM(string) )一起使用。

UPPER(string)和LOWER(string)  整个字符串转换为大写或小写

数据类型转换：

SQLServer通常自动地处理数据类型的转换（隐式转换），显示的转换需要CAST()和CONVERT()函数。还有另一个数据类型转换函数提供在文本和数字之间移动数据的快速方法

STR(number,  [length],  [decimal])   返回数字的字符串形式

 

开窗函数的使用：

聚合函数和排名函数都是可以支持OVER子句的运算类型

OVER子句支持四种排名函数：ROW_NUMBER(行号)、RANK(排名)、DENSE_RANK(密集排名)，以及NTILE.

以ROW_NUMBER(行号)函数举例：表达式ROW_NUMBER() OVER(PARTITION BY  字段名1  ORDER BY 字段名2)

OVER子句支持聚合函数：举例  SUM(字段名) OVER(PARTITION BY  字段名1 )

 

聚合函数的要点就是要对一组值进行聚合，聚合函数传统上一直以GROUP BY 查询作为操作的上下文，在对数据进行分组以后，查询为每个组只返回一行；因此，也就要限制所有的表表达式为每个组只能返回一个值。（GROUP BY 为上下文的聚合，是以分组为单位）

 

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

SELECT RoomLocation,RoomName,

SUM(RoomCapacity) OVER () totalsum,

SUM(RoomCapacity) OVER (PARTITION BY RoomLocation) oversum

FROM dbo.MR_MeetingRoom

分析讲解：带有空的圆括号的OVER子句会提供所有行进行计算，这里的“所有行”是在FROM、WHERE、GROUP BY ，以及HANVING处理阶段完成后的仍然可用的那些行。

 

总结：这里的Partition By类似于Group By，以字段名1相同值作为一个求和范围，实际是按照某列不同取值分块，然后聚合函数或者排名函数在块内操作。

 

联接查询

全差集查询：全差集查询在逻辑上是内联接的逆过程。它通过使用仅接受两个数据集任一主键中空值的WHERE限制进行全外联接，查找两个数据集中交集以外的所有行。

举例：

SELECT Thing1,Thing2

FROM One

   FULL OUTER JOIN Two

      ON One.OnePK = Two.OnePK

WHERE Two.TwoPK IS NULL

 OR One.OnePK IS NULL

自我总结：全差集查询很方便被用来清理没有关联上的垃圾数据。

子查询

创建子查询时，可能要用到5种基本形式，到底使用哪种形式取决于数据的需要和自己的喜好。

1.简单子查询：简单子查询可以使独立查询（执行顺序是a.简单子查询被执行一次 b.结果要传输到外查询 c.外查询被执行一次）。

2.公用表表达式（CTE）:CTE是简单子查询在语法上有所改动的一种变体，与视图相似，用WITH定义了的子查询，CTE的主查询内可以访问多次，就像视图或派生的表一样。

3.相关子查询：与简单子查询相似，但是最少要引用外查询中的一个列，从概念上说，外查询首先运行，相关子查询对外查询中的每一行运行一次。

4.行构造函数：一个VALUES子句或FROM子句，提供硬编码值作为一个子查询。

5.组合SQL：把数据从INSERT 、UPDATE 或者DELETE表达式的输出子句传输到外查询。

最佳实践：使用联接可以从两个表在联接之后被作为一个整体进行筛选或操作的数据源提取数据。如果数据必须在联接之前进行操作，就要使用派生表的子查询。

公用表表达式

公用表表达式(CTE)可以当作临时视图来考虑。

把子查询用作表

就像在SELECT语句的FROM子句中可以使用视图替代表一样，派生表形式的子查询也可以替代表，前提是该子查询要具有别名。这种技术非常强大，常常可以把一些非常困难的查询问题分解为容易解决的小问题。

把一个子查询用作一个派生表就是对聚合函数问题的最佳解决方案了。在您创建聚合查询的时候，每一列必须以某种方式参与到聚合函数中，即就是可以作为GROUP BY 列也可以作为聚合函数（sum()、avg()、count()、max()、或者min()）这个规定使返回附加的描述信息非常困难。不过，在子查询中执行聚合函数并把找到的行作为派生表发送到外查询，能使外查询返回任意期望的列。

举例：

SELECT p.ProductID,SUM(StandardCost) AS StandardCost

FROM Purchasing.PurchaseOrderDetail AS od

INNER JOIN Production.Product AS p

ON od.ProductID = p.ProductID

GROUP BY p.ProductID

结果包含 p.ProductID，但不包含他的名称或描述。当然，按要返回的每个列进行分组是有可能的，但是，这种方法并不是很严谨。下面的查询在子查询中执行了聚合总和，然后这个聚合总和与Product 表相联接，以便不需要再增加其他的工作量就可以使用每一列。

思考下面的SQL语句，使这样的语句在更多的场景下使用。

SELECT p.ProductID,p.ProductNumber,Sales.OrderQty

FROM Production.Product AS p

INNER JOIN (SELECT ProductID,SUM(OrderQty) AS OrderQty FROM Purchasing.PurchaseOrderDetail

GROUP BY ProductID) AS Sales

ON p.ProductID = Sales.ProductID

ORDER BY p.ProductID

行构造函数

行构造函数是在SQL Server2008中新引入的，它提供了一种方便的可以在子查询中直接提供硬编码的方法。VALUES子句是放在括号中的就像每一个硬编码行一样。行构造函数需要有一个别名和一个别名列表，也要放在括号中。

举例：  SELECT   a, b   FROM  (VALUES (1,2), (3,4), (5,6), (7,8), (9,10) ) AS Mytable(a,b)

ALL, SOME,和ANY条件

虽然没有IN那么普遍，在把一个子查询运用于一个WHERE子句时，这三个选项还是值得提到的。每一个选项都提供一个有关在子查询中的项怎样与WHERE子句的测试值相匹配的情况。ALL对于每一个值来说必须为真，SOME和ANY对于子查询中的一些值必选为真。ANY和SOME条件IN条件相似。事实上，=ANY和=SOME与IN 完全相同。ANY和SOME条件还具有测试其他条件测试（如< 、<= 、>=和<>）的功能。

ANY与SOME等价，据说搞这两个不同的词出来是为了迁就英语语法。例如，在用= ANY 的地方在（英语）语法上就应该是= SOME。