专业 网站设计公司价格,小程序游戏开发平台,网上产品免费推广平台,微信怎么建立公众号最近把SQL Server进阶教程重新读了一遍#xff0c;顺便整理了一下书本中的知识点
1.关键知识点
CASE WHEN ❑ 高手使用select做分支#xff0c;新手用where和having做分支 ❑ 要写ELSE#xff0c;要写END#xff0c;避免未匹配上得到NULL ❑ check到底怎…最近把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 返回的是某个字段值的集合然后根据集合去匹配可能会得到truefalse和unknownnot 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”指的代码行你可以把他当作一个固定字段1count(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.a1,应该用not esixt有A.a1 ❑ 学习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等
