最近把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)文章来源:https://www.toymoban.com/news/detail-702039.html
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等)文章来源地址https://www.toymoban.com/news/detail-702039.html
到了这里,关于SQL Server进阶教程读书笔记的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!