SQL Server进阶教程读书笔记

最近把SQL Server进阶教程重新读了一遍，顺便整理了一下书本中的知识点

1.关键知识点

CASE WHEN

        ❑ 高手使用select做分支，新手用where和having做分支
        ❑ 要写ELSE，要写END，避免未匹配上得到NULL
        ❑ check到底怎么用？ 
                在SQL Server中，select中无法使用该关键字，只能在表字段里面设置，对某个（或者N个）字段进行条件约束
        ❑ 作为表达式，CASE表达式在执行时会被判定为一个固定值，在能写列名和常量的地方，通常都可以写CASE表达式

NULL

        ❑ NULL不是值，判断NULL只能写成IS NULL，不能写成=NULL
        ❑ 因为NULL不是值，所以不能对其使用谓词。
                --以下的式子都会被判为 unknown
                1 = NULL
                1 > NULL
                1 < NULL
                1<> NULL
                NULL = NULL
        ❑ 对NULL使用谓词后的结果是unknown。
        ❑ unknown参与到逻辑运算时，SQL的运行会和预想的不一样。
        ❑ 按步骤追踪SQL的执行过程能有效应对4中的情况。
        ❑ 尽量对字段进行NOT NULL限制

EXIST和IN

        ❑ exist和in可以等价替换，not exist和not in却不行（not in 返回的是某个字段值的集合，然后根据集合去匹配，可能会得到true，false和unknown，not exist返回的是true或者false，这个可以参考1.3，有点难理解）

HAVING

        ❑ having子句是可以单独使用的（以前是要跟GROUP BY一起使用），不过这种情况下，就不能在SELECT子句里引用原来的表里的列了，要么就得像示例里一样使用常量，要么就得像SELECT COUNT(＊)这样使用聚合函数。
        ❑ 如果实体对应的是表中的一行数据，那么该实体应该被看作集合中的元素，因此指定查询条件时应该使用WHERE子句。如果实体对应的是表中的多行数据，那么该实体应该被看作集合，因此指定查询条件时应该使用HAVING子句。

WITH

        ❑用于树状排序

2.常用函数(或者运算符)

        ❑COUNT

                 count(*)、count(1)和count(列名)区别
                count(*)：包含所有列，相当于行数，你就把他看作统计所有的行和列，统计结果包含列值为null
                count(1)：会去统计表中的所有的记录数，可以理解为计算一共有多少符合条件的行，统计结果也包含列值为null
                count(列名)的区别：只展现对应列名的值，会自动屏蔽列值为null的值
所以说count(*)和count(1)本质区别不大，count(1)，这个“1”，指的代码行，你可以把他当作一个固定字段1，count(1)，就是计算一共有多少个1。

                执行效率来看：
                        若列名为主键，count(列名)会比count(1)快
                        反之列名不为主键，count(1)会比count(列名)快
                        若表多个列且没有主键，则 count(1) 的执行效率优于 count（*）
                        若表有主键，则 select count（主键）的执行效率是最优
                        若表只有一个字段，则 select count（*）最优

        ❑ALL

                其实就是子查询结果AND起来的表达式之和

        ❑ANY

                相当于OR表达式之和之和

        ❑OVER

                按顺序累加或者逐年累加
                参见GROUP BY和PARTITION BY
                    --求移动累计值(1)：使用窗口函数
                    SELECT prc_date, prc_amt,
                          SUM(prc_amt) OVER (ORDER BY prc_date
                            ROWS 2 PRECEDING) AS onhand_amt
                      FROM Accounts;

        ❑COALESCE

                函数将会返回包括expression在内的所有参数中的第一个非空表达式

        ❑UNION和UNION ALL

                UNION 合并两个集合，去掉重复项
                UNION ALL 合并两个集合，保留重复项

        ❑EXCEPT

                获取两个集合的差集；两个结果用EXCEPT链接，返回第一个结果集不在第二个结果集中的数据。

        ❑INTERSECT

                获取两个集合的交集；两个结果用INTERSECT链接，返回两个结果集中的相同部分

        ❑GROUP BY和PARTITION BY

                GROUP BY：对集合进行分组和统计，根据分组条件，每个条件只有一行统计值
                PARTITION BY：对集合进行分组和统计，统计结果行数跟原来分组前一样，如果搭配OVER使用，可以呈现逐条累加的效果
                        RANK() OVER(PARTITION BY name ORDER BY price desc)
                        DENSE_RANK() OVER(PARTITION BY name ORDER BY price desc)

        ❑MERGE

                对两个数据源进行合并

3.注意事项

        ❑ 用CASE表达式代替IF语句和CASE语句
        ❑ 用GROUP BY和关联子查询代替循环
        ❑ 表中的行没有顺序
        ❑ 将表看成集合（对于集合的运算和处理，可以考虑用集合函数（UNION、UNION ALL、EXCEPT、INTERSECT））
        ❑ 理解EXISTS谓词和“量化”的概念（遇见全称量化需要转换为双重否定，就是不能用exist所有的A.a=1,应该用not esixt有A.a<>1）
        ❑ 学习HAVING子句的真正价值
        ❑ 不要画长方形，去画圆(用圆去绘制集合之间的关系，不要用面向对象的思维去看待SQL)
        ❑ 消除NULL值
                (1)首先分析能不能设置默认值。
                (2)仅在无论如何都无法设置默认值时允许使用NULL。
        ❑ 理解SQL的执行顺序FROM→WHERE→GROUP BY→HAVING→SELECT(→ORDER BY)

4.性能优化方面

        ❑ 连接查询替代子查询
                多表连接通常比子查询执行速度更快，因为在多表连接中，查询可以同时考虑多个表的关联，而子查询则需要先执行子查询，然后将结果作为临时表与其他表进行连接，这样会增加查询的开销。但是，在某些情况下，子查询可能更适合。例如，在查询结果作为子查询时，如果结果集非常小，则使用子查询可能会更快，因为查询结果集不会被重复扫描多次。
        ❑ 使用高效的查询
                参数是子查询时，使用EXISTS代替IN  
                参数是子查询时，使用连接代替IN （如果连接字段不是索引的话，那EXISTS性能更快）
                对于数据量大的表，应该使用 WITH NOLOCK防止锁表
        ❑ 避免排序
                以下会进行排序
                        ● GROUP BY子句
                        ● ORDER BY子句
                        ● 聚合函数（SUM、COUNT、AVG、MAX、MIN）
                        ● DISTINCT
                        ● 集合运算符（UNION、INTERSECT、EXCEPT）
                        ● 窗口函数（RANK、ROW_NUMBER等）
                灵活使用集合运算符的ALL可选项（如果不在乎结果是否存在重复项，则可以带上ALL）
UNION ALL
                使用EXISTS代替DISTINCT
                在极值函数中使用索引（MAX/MIN）
                能写在WHERE子句里的条件不要写在HAVING子句里
                在GROUP BY子句和ORDER BY子句中使用索引

        ❑ 索引失效
                使用索引时，条件表达式的左侧应该是原始字段；
                在索引字段上进行运算，会使索引失效，导致全表扫描，例如：WHERE col_1 ＊ 1.1 > 100，改成 WHERE col_1 > 100 / 1.1 就可以正常使用索引
                使用函数也会导致全表扫描例如： WHERE SUBSTR(col_1, 1, 1) = 'a'
                使用IS NULL谓词（索引字段是不能为NULL的，对于索引字段不要使用IS (NOT) NULL，会导致索引失效）
                使用否定形式（对于索引字段不要使用<>、 !=、 NOT IN，会导致索引失效）
                使用OR
                使用联合索引时，列的顺序错误
                使用LIKE谓词进行后方一致或中间一致的匹配（%a%、%a）
                进行默认的类型转换
        ❑ 减少中间表
                灵活使用HAVING子句
                需要对多个字段使用IN谓词时，将它们汇总到一处（可以用EXISTS代替）
                先进行连接再进行聚合
                合理地使用视图(在视图中使用以下运算，会降低性能)
                       a. 聚合函数（AVG、COUNT、SUM、MIN、MAX）
                        b.集合运算符（UNION、INTERSECT、EXCEPT等）