《SQL必知必会》个人笔记（一）
引言
虽然以前看过第一遍，但是后面看过一遍之后听说出了第五版之后，又找到相关资源看了一遍。
评价
这本书可以说是能让技术人员用最快速度入门SQL的一本书，对于很多一上来不知道SQL语法觉得概念很抽象的人来说也能对与SQL领域有一个大致的了解，当然作为一个过来人当初觉得SQL真的挺神奇的。
这本书当然只适合新手，对于老手来说更多是快速回顾和查漏补缺，所以这一篇读书笔记将会简单提炼一些忽视的部分记录，以及工作实践之后对于书中一些建议的补充和解释。
个人已经看过非常老的小绿本的版本，在看到出到第五本之后再看看这本书还挺感慨的。
最后千万不要认为SQL很简单，其实越是看似简单的东西越是复杂。魔鬼常在细节中.
什么是SQL
SQL在表面上看是编程语言，实际上它掩盖了包含数据库，操作系统，甚至各种底层编程语言的，可以说SQL是现今互联网或者说WEB应用的核心，看似简单的SQL语言其实承载的前人智慧的结晶和精华，再过去很难想象一条SQL可以让人从身无分文到亿万富翁，操作失误也可以瞬间让6万人电话无法拨打。
从程序眼的角度来说，编写数据库的技术人员对于技术的要求和门槛是最高的，同时也是对于综合硬实力的一个硬核考核指标，写出优秀的框架代码虽然可能受人欢迎，但是会优化数据库能把系统效率直线提升的可以发现确实很少。
查询
查询语句使用的是SELECT语句，在查询当中可以通过*号查询所有的列，可以指定列值，几乎所有的情况都不建议使用 * 作为列值，一方面是增删某些字段导致一些实体映射出问题，另一方面星号最大的问题是对于查询性能的影响，通常来说查询的列越多，数据越多，性能越差。
如果想要限制返回的结果，不同的数据库厂商实现不同： 再Mysql、MariaDB和Postgresql 中实现比较简单，可以通过 limit关键字进行过滤，但是需要注意“深分页”问题。
另一种是使用嵌套查询的 方式，比如Oracle 数据库的分页模板如下：
内层查询主要的作用是获取行号，通过（当前页*每页数量）定位到“结尾”，再通过一层嵌套“过滤”掉不符合页码的行，注意这种查询和Limit一样都是是不保证排序的。
其实本质上就是limit 的 复杂写法。limit 可以看作是语法糖，过去在SQL规范中无此实现建议，所以并不是所有数据库都支持。
/*Oracle分页模板*/ SELECT * FROM ( SELECT TMP_PAGE.*, ROWNUM ROW_ID FROM ( #查询语句 SELECT * FROM TABLE )TMP_PAGE WHERE ROWNUM < ((#{pageNum} + 1) * #{pageSize} + 1) ) WHERE ROW_ID > ((#{pageNum} + 1) * #{pageSize} - #{pageSize})
具体的案例如下：
-- 案例 SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM <= 40 ) WHERE RN >= 21
最后是个别数据库的特殊写法，比如SQL SEVER的TOP函数： SELECT TOP 5 prod_name FROM Products;
注释使用： 上面的的两个案例把三种常用的注释方式介绍了一下，特别数据在一些ORM框架的XML文件中有一些注释是冲突的，比如 #这样的写法在其中就是不支持的，另外也不是所有的数据库都支持这样的写法。
最后需要指出的编写SQL的时候尽量编写符合SQL规范的语句，目的是有可能切换库的时候少一些无聊的工作。
排序
在通常的情况下使用排序建议尽量和查询的列对应，最理想的状态是只包含order by 列的查询，当然这比较天方夜谭，但是依然不能忽视order by 带来的性能问题和影响。
order by 排序还有一个比较值得注意的特点是：多列排序只对出现相同的值进行排序也就是说再多个行的值相同的情况下，数据库才会对后面指定对排序列进行排序，比如多列查询前面对列都是唯一的值，是不能保证后面的内容是有序的。
这里举一个例子，order by a,b,c，如果a都是唯一值进行排序，才会对于b进行排序，而如果因为b的排序而影响列a的顺序，显然这是不合逻辑的， 同理列c也是如此。
另外，处理按照列排序之外，还可以指定列位置进行排序，当然基本没人会用，这里了解有这个用法即可。
排序方向是asc升序和desc降序，需要注意部分数据库只支持降序索引（比如Mysql），所以排序的方向还是十分重要的，另外避免使用一个列升序一个列降序的方式进行查询，不仅导致大量的内存文件排序或者磁盘临时表排序，本身还会直接导致索引没法帮忙优化直接走全表扫描。
过滤数据
有关where 条件的操作符如下：
上面几个操作符号最容易踩坑的可能是 <>和 !=，还有is null。 !=和<>通常可以互换。但是，并非所有 DBMS 都支持这两种不等于操作符，另外!=和<>是 不会包括值为N ULL的数据的，这个细节从侧面映证了NULL在数据库当中有其特殊的含义。
比较 != null 和 is null
首先我们来看下<>和!= 两种写法的查询结果的区别：
<>在数据库中表示 不等于，虽然和!=的作用等价，但是有些数据库不支持!=的写法，比如sqlserver，所以使用<>会比较规范一些。
select id,username,password, gender from admin where gender <> null; select id,username,password, gender from admin where gender != null; select id,username,password, gender from admin where gender is not null; select id,username,password, gender from admin where gender is null; -- 如果使用case when 会有比较有意思的结果： select id,username,password, case when gender != null then gender end as gender from admin 复制代码
下面是运行结果：
实验使用的是mysql的数据库，版本为5.7。
下面是运行结果： 第一条：没有记录 第二条：没有记录 第三条： 1 小红 111 2 3 小黄 NULl 1 第四条： 2 小蓝 222 NULL 第五条： 1 小红 111 NULL 2 小蓝 222 NULL 3 小黄 NULL NULL 复制代码
这样的sql语句是初学数据库的同学有可能犯错的点，尤其是!= null，is not null这两条语句经常被弄混，他们在表面的含义似乎都是“不为空”，但是实际上他们含义是完全不同的，强烈建议在进行判断数据库字段内容是否为null的时候用not null来表示不为空。
关于is null和更多的更多细节，可以参考下面的内容了解：
[[盘点数据库中的一些坑（一）]]：
所以为了让你查询的时候不陷入各种困惑和烦恼，设计数据库的时候使用not null，当然这种设计甚至可以是一些不成文的规定，因为null不仅影响结果判断，还影响索引扫描导致索引失效。
NULL 无值（no value），它与字段包含 0、空字符串或仅仅包含空格不同。
同样在书中提供了相关的注意事项提示用户：
注意：NULL 和非匹配 通过过滤选择不包含指定值的所有行时，你可能希望返回含 NULL 值的 行。但是这做不到。因为 NULL 比较特殊，所以在进行匹配过滤或非匹 配过滤时，不会返回这些结果。
多条件过滤
OR操作执行顺序问题 多条件过滤比较值得注意的是 or操作，or操作通常在条件表达式的两边加上括号，这是因为逻辑运算符的优先级or要低于and，所以如果不管清不清楚连接顺序，都应该在or查询增加括号提醒其他的开发人员连接条件，但是需要注意or操作常常会导致索引失效，所以如果or条件两边又一边没有索引就需要谨慎的测试性能之后考虑是否改写。
IN操作
in操作在日常的开发中用的不少，但是通常使用，
不建议使用多个条件or查询，这里可以看看下面这个例子：
select * from A where a1 in (1, 2) and a2 in (1,2,3)
这种形式的组合其实也比较常见，比如我们在电商搜索商品的时候会选择指定的电器种类，然后选几个固定的区间，在加上“包邮”，“免运费”等等Tag之后，基本能出现类似上面的查询效果（当然现实情况肯定不是这样搜索的）。
当搜索完成的时候
让人摸不着头脑的not in
not in 不仅容易导致索引失效，还会出现很多“意料”之外的查询结果
select id,username,password,gender from admin where gender in (select gender from admin);
读者可以根据上面的SQL先猜想最终的查询结果
-- 运行结果：select gender from admin -- gender -- 2 -- NUll -- 1 -- 最终结果： -- id,username,password,gender -- 1 小红 111 2 -- 3 小黄 1
答案并不难，in查询会过滤掉所有值为null的行，换一种说法如果 in (null)是不会返回任何结果的。
通常我们使用 in 的查询如果在子查询的结果里面有null会被排除，因为这里的in只会拿出结果为true的数据，所以最终结果是对的也是正常的（如非必要尽量避免使用子查询，此处仅仅做展示），现在我们换一种写法对上面的sql改写为not in看下会有什么样的效果：
select id,username,password,gender from admin where gender not in (select gender from admin where id = 2);
-- 运行结果： -- 无记录!!!
可能会有读者认为上面的结果是除开id为2的其他两条记录，结果大相径庭，对于这个结果答案是 unkdown 的问题，我们可以把上面的sql语句看作是下面的写法：
select id,username,password,gender from admin where gender <> null;
这样写是不是就好理解多了呢？如果读者依旧无法理解，只要牢记下面的话即可，避免not in 中的结果存在null值：
NOT INreturns 0 records when compared against an unknown valueSince
NULLis an unknown, aNOT INquery containing aNULLorNULLs in the list of possible values will always return0records since there is no way to be sure that theNULLvalue is not the value being tested.
NOT IN与未知值比较时返回 0 条记录由于“NULL”是未知的，因此在可能值列表中包含“NULL”或“NULL”的“NOT IN”查询将始终返回“0”记录，因为无法确定“NULL” value 不是被测试的值。
另外对于存在not in 语句最理想的解决办法是尽量避免使用NOT IN，除非必须使用并且可以保证结果绝对有默认值， 更好的建议是使用 left join 连接查询进行替代，或者可以使用not exists 语句进行改写：
改写方式1：
-- not exists 进行改写 SELECT id, username, PASSWORD, gender FROM admin a WHERE NOT EXISTS ( SELECT 1 FROM admin b WHERE a.gender = b.gender );
改写方式2：
-- 使用left join进行改写 SELECT a.id, a.username, a.PASSWORD, a.gender FROM admin a left join admin b on a.gender = b.gender where b.gender is null;
通配符
通配符 %
通配符搜索只能用于文本字段（字符串），非文本数据类型字段不能使用 通配符搜索。
只要做过业务开发基本都用过%xxxx%这样的写法，或者使用concat('%,', xxx, ',%')，通常在进行模糊搜索的时候会经常使用like操作，比如一些订单的模糊搜索查询，但是需要注意的是这种双边加百分号的方式容易导致 索引失效，同时like的操作本身就是比较吃性能的（所以通常针对由索引的字段模糊搜索）。
模糊搜索能够使用索引的场景通常是根据最左匹配原则，比如 "xxx%"这样的方式是可以模糊搜索的，而“%xxx%”哪怕存在索引也是会 索引失效的，这一点其实了解一点点Btree的数据结构就能明白：
Btree 索引默认是排好序的，如果使用前缀通配符的那么在搜索的时候索引无法执行顺序扫描（或者直接说二分查找），所以会非常干脆的放弃索引扫描转为全表一个个匹配出值之后再返回结果，效率也可想而知。
最后书中提到 NULL对于通配符的影响，所以建议在设计表的时候建议所有的列非空，或者存储一些默认值来防止部分需要搜索数据的时候编写麻烦的SQL。
请注意 NULL 通配符%看起来像是可以匹配任何东西，但有个例外，这就是 NULL。 子句 WHERE prod_name LIKE '%'不会匹配产品名称为 NULL 的行。
通配符 下短划线 和百分号匹配的方式不同，它使用的是单字符匹配的方式。
下划线通配符在DB2的数据库中不被支持，所以在使用之前建议写一个简单案例尝试一下能否使用。
通配符 方括号 方括号是单字符匹配的方式，也就是说[ab]会匹配a或者b其中一个内容
