SQL进阶

1、为了提高sql代码的可移植性，避免使用oracel中的decode和mysql中的if函数，而使用标准的case函数，与decode相比，case函数中可以使用in、like、< >等谓词，表达能力较强

2、使用case函数时，when子句要注意条件的排他性

3、case函数中的else是可选的，如果不写，其结果为else null，但是最好养成习惯写上

4、group by中可以使用select子句中简单case函数指定的别名，但是只能在postgresql和mysql中使用，在oracle、db2、sql server中会报错，因为group by的执行顺序早于select，而postgresql和mysql在执行查询语句前，会先对select子句里的列表进行扫描，并对列进行计算，但是违反sql标准，不建议使用

5、新手用where子句进行条件分支，高手在select子句中用case when进行条件分支，也就是where中的过滤条件可以放到select中用case when实现

6、case表达式用在select子句里时，既可以写在聚合函数内部，也可以写在聚合函数外部，高度自由

7、在聚合函数中使用case表达式可以进行行转列

8、自连接经常和非等值连接结合起来使用

9、应把表看作行的集合，用面向集合的方法来思考

10、自连接的性能开销很大，应尽量给用于连接的列建立索引

11、在以前的sql标准里，having子句必须和group by子句一起使用，但是新的标准里，having子句是可以单独使用的

12、sql是通过不断生成子集来求得目标集合，不像面向过程语言那样通过画流程图来思考问题，而是通过画集合的关系图来思考

13、使用union all可以将列转行，将多个重复项汇总于一列

14、可以将两张表进行全连接，通过判断连接后的相关字段是否为Null来求得差集

15、生成嵌套式表侧栏时，如果先生成主表的笛卡尔积再进行连接，很容易就可以完成

16、从行数来看，表连接可以看成乘法，因此，当表之间是一对多的关系时，连接后的行数不会增加
17、集合论是sql语言的根基，只有从集合的角度思考，才能明白sql的强大威力

18、关联子查询是为了使sql能够实现类似面向过程语言中的循环功能而引入的

19、谓词是一种特殊的函数，返回值是真值，true、false、unknown

20、exists的子查询中，select子句的列表可以有下面三种写法：
(1) 通配符 select *
(2) 常量 select 123
(3) 列名 select col
无论子查询中选择什么样的列，对于exists来说都是一样的

21、between这种输入值为一行的谓词叫作一阶谓词，像exists这样输入值为行的集合的谓词叫作二阶谓词

22、exists和having都是以集合而不是个体为单位来操作数据

23、exists主要进行行方向的量化

24、生成序列视图(包含0-999)
create view sequence (seq)
as select d1.digit+(d2.digit10)+(d3.digit100)
from digit d1 cross join digit d2 cross join digit d3
(其中digit是单列表，值为0-9)

从序列视图中获取1-100
select seq
from sequence where seq between 1 and 100 order by seq

25、数学中通过group by生成的子集中有一个对应的名字，叫作划分(partition)

26、用于调查集合性质的常用条件及其用途

条件表达式	用途
count(distinct clo)=count(col)	col列没有重复的值
count(*)=coun(col)	col列不存在null
count(*)=max(col)	col列是连续的编号(起始值是1)
count(*)=max(col)-min(col)+1	col列是连续的编号(起始值是任意整数)
min(col)=max(col)	col列都是相同值，或者是null
min(col) * max(col) >0	col列全是正数或全是负数
min(col) * max(col) <0	col列的最大值是正数，最小值是负数
min(abx(col))=0	col列最少有一个是0
min(col -常量)=-max(col-常量)	col列的最大值和最小值与指定常量等距

27、在sql中指定搜索条件时，最重要的是搞清楚搜索的实体是集合还是集合中的元素，如果一个实体对应着一行数据那就是元素，使用where子句，如果一个实体对应着多行数据就是集合，使用having子句

28、尽量不使用依赖各种数据库实现的函数和运算符，比如decode(oracle)、if(mysql)、nvl(oracle)、stuff(sql server)，请使用case、coalesce、nullif等标准函数代替，否则会导致代码的可移植性变差

29、连接操作使用标准语法，用inner或cross等表明连接类型的关键字，连接条件可以使用on子句分开写，这样一眼就能看明白连接的类型和条件，代码可读性好

30、从from开始写，不要直接从select开始写，因为select子句是sql语句执行中最后执行的部分，写的时候根本没有必要在意，sql中各部分的执行顺序是：
from ->where ->group by->having ->select ->order by

因此如果需要写很复杂的sql语句，可以考虑按照执行顺序从from子句开始写，这样添加逻辑时更加自然，如果把从select子句开始写的方法称为自顶向下法，那么从from子句开始写的方法就可以称为自底向上法

31、从面向过程思维向面向集合思维转变的7个关键点
(1) 用case表达式替代if语句和case语句，sql更像一种函数式语言
(2) 用group by和关联子查询代替循环
(3) 表中的行没有顺序
(4) 将表看成集合
(5) 理解exists谓词和量化概念
(6) 学习having子句的真正价值
(7) 不要画长方形，去画圆

32、目前能够准确描述静态数据模型的标准工具是文氏图，即圆，通过在文氏图中画嵌套子集，可以很大程度地加深对sql的理解，这是因为，嵌套子集的用法是sql中非常重要的技巧之一

33、oracle不区别空字符串和null

34、sql函数在参数为null时都会返回null，这种现象被称为null的传播

35、消除null场景和方法：
(1) 编号
使用异常编号，比如性别中除了1-男，2-女，还有0-未知，9-不适用，编号要使用字符串类型而不是数值类型，因为如果是008这种以0开头的，数值类型会将前面的0去掉

(2) 名字
使用无名氏

(3) 数值
用0代替，对于数值型的列，最好的方法是一开始就将null转换为0，再存储到数据库中

(4) 日期
用最大值或最小值代替，如 0000-01-01、9999-12-31

总结：
1、首先分析能不能设置默认值
2、仅在无论如何都无法设置默认值时允许使用null