《SQL必知必会》读书笔记（一）https://developer.aliyun.com/article/1394961

复杂查询

子查询

子查询通常和where条件绑定，下面是简单的子查询案例：

SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01');

对于子查询的建议是最好明确知道子查询的返回结果，另外虽然部分数据库优化器会对子查询的连接方式进行优化，但是子查询整体上对于SQL执行效率的影响比较大。

另外不建议使用带多个in的子查询，原因在后面的in查询中进行介绍。除了在where条件中使用，子查询还会用在列查询上：

SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;

最后碰到子查询的情况更多建议使用join查询替代，同时对于整个SQL的阅读体验也会好不少。

连接JOIN

对于大多数的join查询来说，连接的逻辑都是循环连接，类似两个for循环嵌套，数据库不建议三张表以上的连接查询规则是通用的，虽然不是数据库限制但是在编写的过程中建议一定要遵守。

有不少的数据库实现不允许超过一定数量连接表查询。

连接查询另一个十分常见的问题是 笛卡尔积，笛卡尔积简单来说就是行 * 行的结果集，很多情况下产生笛卡尔积是因为没有使用 唯一条件进行连接查询，比如join查询在没有进行关联条件on或者using限制的时候会出现很多“重复”的结果。

初次接触的时候可能会误认为只有左外连接或者右边外连接会出现笛卡尔积，其实只要是这种类似循环的连接方式就会出现笛卡尔积的结果。

SQLite支持 LEFT OUTER JOIN，但是不支持 RIGHT OUT JOIN。但是LEFT JOIN和  RIGHT JOIN 唯一的区别是连接顺序的不同。 另外MariaDB、MySQL和 SQLite不支持 FULL OUTER JOIN 语法，这和他们主要都是使用循环嵌套的连接方式有关。

另外关于Join使用需要注意join条件和where条件的区分，区别可以参考下面的案例，这里的ON限制的是 连接查询的结果，而where过滤的是连接查询之后的行结果

SELECT vend_name, prod_name, prod_price
FROM Vendors
INNER JOIN Products ON Vendors.vend_id = Products.vend_id AND Products.prod_name =Vendors.vend_name
WHERE Vendors.vend_name = 'xxxx'
;

牢记where是行级过滤器， having是组级过滤器。

表别名

表别名在多表存在相似的字段的时候建议指定，但是不建议使用 abcde 这样的别名，不仅毫无意义并且SQL复杂之后十分影响阅读。

Oracle 中没有 ASOracle不支持 AS 关键字。要在 Oracle中使用别名，可以不用 AS ，简单 地指定列名即可（因此，应该是 Customers C ，而不是 Customers AS C ）。

别名的另外一种情况是使用子查询数据库会强制用户指定别名才允许获取字段，这一点是出于查询的时候子查询结果可能出现重名字段导致解释器无法解释SQL。

小贴士：更加建议即使是单表查询也指定别名，养成良好习惯。

自连接

另一方面表别名还用在自连接方面，自连接的形式比较多，可以用于多表查询也可以使用子查询或者使用Join连接查询。

SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
FROM Customers
WHERE cust_contact = 'Jim Jones');

函数聚集连接

连接查询的一种使用技巧是使用聚集函数对于关联表的聚集统计。

SELECT Customers.cust_id,
COUNT(Orders.order_num) AS num_ord
FROM Customers
INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

注意事项

• 建议在使用连接查询的时候尽量使用内连接查询
• 对于连接方式的底层细节可以查看相关的数据库文档，比如Postgresql支持多种的底层连接方式哈希关联，嵌套关联等等，这些连接方式是优化器选择的，但是多数数据库使用的都会嵌套循环的方式进行连接。
• 只要是连接查询就有可能出现笛卡尔积，所以需要密切关注连接条件能否保证数据结构的唯一性。
• 在一个联结中可以包含多个表，甚至可以对每个联结采用不同的联结类型。

组合查询

组合查询一般指的是union查询，对于union查询我们会疑问是使用union all还是单纯的使用 union，对于这两种用法的主要区别是：

• union：会对组合的列结果自动进行去重和排序。
• unioin all：不对结果进行排序，只是简单的把结果拼在一起，但是拼在一起的结果不保证顺序。

通过以上对比发现我们需要注意union内部的操作带来的性能影响，比如Mysql就会在内部会自动去重排序，同时会产生中间表（分为内存中间表或者磁盘中间表，视中间结果集数据量而定），而中间表通常意味着 索引失效，所以如果可能的话尽量少用union。

下面是组合查询的案例：

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

使用 UNION 组合 SELECT 语句的数目，SQL没有标准限制

下面是书中记录的union的规则，建议根据具体使用的数据库查看相关的设计限制：

• UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字 UNION 分隔（因此，如果组合四条 SELECT 语句，将要使用三个 UNION 关键字）。
• UNION 中的每个查询必须包含相同的列、表达式或聚集函数（不过， 各个列不需要以相同的次序列出）。
• 列数据类型必须兼容：类型不必完全相同，但必须是 DBMS可以隐含 转换的类型（例如，不同的数值类型或不同的日期类型）。

其他类型的 UNION： 部分数据库支持一些特殊的组合查询方式，当然都可以使用连接查询替代，所以如果有可能出现换库的可能性，就不要写一些针对某款数据库的SQL，以免增加阅读理解难度和迁移难度。

下面是一些特殊的UINON操作：

• EXCEPT （有时称为 MINUS ）可用来检索只在第一个表中存在而在第二个表中不存在的行
• INTERSECT：可用来检索两个表中都存在的行

数据分组

分组常用的函数是group by，需要注意分组聚合的操作也是容易导致索引失效的，因为内部会产生中间表并且会进行内部的Sort排序，所以对于分组的字段建议加上索引。

数据分组除了经常使用的WHERE条件之外还有HAVING，这两个关键字唯一的区别是前者是过滤行记录，后者是过滤分组记录，虽然大部分where条件都可以使用HAVING替换。

WHERE 在数据分组前进行过滤， HAVING 在数据分组后进行过滤，同时因为分组前过滤的特性，WHERE 的过滤可能会影响group by聚合函数的运算结果。

下面是书中的简单案例：

SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

此SQL的作用是过滤出count( * )大于2的分组，另外针对where和having的分组前后过滤，这里提供自己试验的简单例子介绍：

数据库使用的是Mysql5.7以上的版本。

SELECT
  parent_category_id 
FROM
  help_category 
GROUP BY
  parent_category_id 
HAVING
  count(*) >= 4;
/*
0
4
23
*/
-- 增加where条件，发现结果被提前过滤
SELECT
  parent_category_id 
FROM
  help_category 
WHERE
  parent_category_id > 4 
GROUP BY
  parent_category_id 
HAVING
  count(*) >= 4;
/*
23
*/

虽然having和where没有严格的SQL规范如何使用，但是更多的时候having会和group by配合使用，所以where要比having更为常用。

SELECT 语句执行次序

函数操作

函数操作的部分包含函数和计算字段的部分，因为存储过程基本就是在调各种函数，所以这里放到一块整理。

函数

从书中给的表可以看出函数的可移植性很差，对于统计SQL如果需要迁移到其他的数据库，在重写的时候会让人十分头疼，所以尽量避免SQL做复杂的函数运算以及避开自己编写SQL函数。

SQL是不区分大小写的，所以编写SQL函数的时候保持风格一致即可，喜欢大写就用大写，小写就用小写。

另外一种需要大量函数的场景是存储过程，函数的可移植性比较差，存储过程就更差的了，可以简单找一些存储过程的案例尝试迁移就会明白这句话的意思。

大多数的函数都包含下面的特征：

• 字符串文本处理
• 数值算数运算
• 处理日期和时间
• 美观的格式化参数
• 特殊函数操作（尽量避免使用）

下面是一些比较常用的函数，简单浏览有一个印象即可。

数值处理函数：

字符串处理函数：

聚集函数

聚集函数虽然也被称之为函数但是他们的行为不针对单行而是针对所有相同列的行，聚集函数通过常见的数学运算聚合运算结果，常用的聚集函数有下面几种：

AVG() 函数

AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出，同时需要注意如果列为 NULL 会忽略当前列。

建议对于做数学聚合的列使用 numeric 或整型数字类型，虽然使用字符串可以通过函数转化之后存储结果，但是会有不必要的转化开销。

count() 函数：

count( * )函数会忽略空值列，如果是指定列count()统计会取出每一个数计算，相比较之下单列count() 性能明显不如count( * )。

建议：碰到count() 函数场景都使用 count( * )，因为官方对于星号做了内部优化，这里所说的优化可以理解为去掉空值判断。但是需要注意NULL值问题

这里其实有比较深的陷阱，count() 列和count( * )的结果有可能会不一样，这是因为count( * )会计算NULL值列，所以还是建议在设计表的时候把列尽量都设置为not null。

• 使用 COUNT(*) 对表中行的数目进行计数，不管表列中包含的是空值 （ NULL ）还是非空值。
• 使用 COUNT(column) 对特定列中具有值的行进行计数，忽略 NULL 值。

max() 函数和 min() 函数

最大值和最小值函数会忽略NULL 值，这两个函数可能会返回任意列的最大值或者最小值，同时部分数据库设计会返回随机列的最大值或者最小值，如果是文本数据则返回文本数据排序的第一条或者最后一条。

max()和min()函数对于性能影响和开销比较大，从上面文本数据的排序可以看出内部有可能出现临时表排序动作所以建议少用或者自己写排序和limit模拟函数效果。

**sum()**函数

求和函数可以对于多列的数值进行数学运算之后将结果进行合并，同样它会自动忽略NULL值。

聚集列选项

聚集列可以指定是否去重， 需要注意 DISTINCT 不能用于 COUNT( * )，如果指定列名则 DISTINCT 能用于 COUNT() 。

下面是使用注意事项：

• all：默认，对于所有的列
• distinct：只包含不同的值，需要指定 DISTINCT 参 数

不建议把DISTINCT用于max或者min函数。

对于部分数据库处理支持去重之外，支持返回指定数量的结果，比如SQL SERVER的 TOP函数。

计算字段

如何拼接字符？拼接字符的方式有两种：“||” 符号和 "+" 符号。

SELECT vend_name + '(' + vend_country + ')' FROM Vendors ORDER BY vend_name;

在部分数据库当中存在字符串拼接函数concat，使用频率也不小，但是其实和前面的符号拼接没有区别。

SELECT Concat(vend_name, ' (', vend_country, ')') FROM Vendors

另外对于字符串中有时候可能会存储一些空格内容，可以使用SQL的trim()函数对于字符内容进行过滤再返回结果。

trim()函数通常还会被细分为rtrim()和ltrim()顾名思义，就是去掉左右两边的空格，目前绝大多数的数据库都支持这样的函数。

SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')' FROM Vendors

AS 别名

别名通常在连表查询的时候如果涉及多个重名列，那么如果想要更清晰的划分列结果最好的办法是对于列进行重名。

另外的一种情况是在使用case when的语句中通常会有AS的用法，当然更多的写法是在某些列需要计算的产生的case when临时结果需要对于列进行重命名方便ORM进行映射。：

--简单case函数
case sex
  when '1' then '男'
  when '2' then '女’
  else '其他' end
  AS gender

别名还有其他用途，常见的用途包括在实际的表列名包含不合法的字符（如空格）时重新命名它， 在原来的名字含混或容易误解时扩充它。

算术运算

需要注意查询中所有的算数运算都会导致索引失效，所以不是特别建议在SQL层面完成各种复杂的函数计算。

下面是书中给的例子，并不是所有的函数操作都有函数的使用动作，对于算数运算符操作和字符串的拼接操作都可以认为是函数操作。

SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems WHERE order_num = 20008;

存储过程

存储过程这个东西让人又爱又恨，如果对于SQL十分了解就会发现存储过程可以完成许多很复杂的操作，并且执行效率非常高，但是代价是极其难以阅读并且维护难度大，对于存储过程细究可以用一本书来讲述，这里不做过多展开，对于大部分业务开发者来说存储过程通常是不建议或者不允许的使用的。

存储过程通常有下面的特点：

• 因为不需要外部保证数据的一致性，所以可以简化控制并发问题
• 存储过程保证了出错的可能性越低，因为嵌套的层级越多越容易出错
• 简化管理，所有的逻辑都可以通过存储过程了解。
• 安全，因为是操作数据库本身。

下面是执行存储过程的语句：

EXECUTE AddNewProduct('JTS01',
'Stuffed Eiffel Tower',
6.49,
'Plush stuffed toy with
➥the text La Tour Eiffel in red white and blue');

存储过程可能会因为下面的原因产生差异：

• 参数可选，具有不提供参数时的默认值。
• 不按次序给出参数，以“参数=值”的方式给出参数值。
• 输出参数，允许存储过程在正执行的应用程序中更新所用的参数。
• 用 SELECT 语句检索数据。
• 返回代码，允许存储过程返回一个值到正在执行的应用程序。

创建存储过程

创建存储过程的步骤如下：

CREATE PROCEDURE MailingListCount (
ListCount OUT INTEGER
)
IS
v_rows INTEGER;
BEGIN
SELECT COUNT(*) INTO v_rows
FROM Customers
WHERE NOT cust_email IS NULL;
ListCount := v_rows;
  END;

执行这个存储过程如下：

var ReturnValue NUMBER
EXEC MailingListCount(:ReturnValue);
SELECT ReturnValue;

多条件过滤

OR操作执行顺序问题

多条件过滤比较值得注意的是 or操作，or操作通常在条件表达式的两边加上括号，这是因为逻辑运算符的优先级or要低于and，所以不管清不清楚连接顺序，都应该在or查询增加括号提醒其他的开发人员连接条件。

但是需要注意or操作常常会导致索引失效，所以如果or条件两边有一边没有索引就需要谨慎的测试性能之后考虑是否改写。

IN操作

in操作在日常的开发中用的不少，但是通常使用不建议使用多个in，更不建议使用多个条件or查询，这里可以看看下面这个例子：

select * from A where a1 in (1, 2) and a2 in (1,2,3)

这种形式的组合其实也比较常见，比如我们在电商搜索商品的时候会选择指定的电器种类，然后选几个固定的区间，在加上“包邮”，“免运费”等等Tag之后，基本能出现类似上面的查询效果（当然现实情况肯定不是这样搜索的）。

这样的组合就是2 * 3 = 6，整个迭代会有6种组合，如果子查询越多那么组合越多，效率自然也就越低。

让人摸不着头脑的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 IN returns 0 records when compared against an unknown value

Since NULL is an unknown, a NOT IN query containing a NULL or NULLs in the list of possible values will always return 0 records since there is no way to be sure that the NULL value 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对于通配符的不会匹配的问题，建议在设计表的时候所有的列非空或存储默认值。

请注意 NULL 通配符%看起来像是可以匹配任何东西，但有个例外，这就是 NULL。子句 WHERE prod_name LIKE '%'不会匹配产品名称为 NULL 的行。

通配符 下短划线和百分号匹配的方式不同，它使用的是单字符匹配的方式。

下划线通配符在DB2的数据库中不被支持，所以在使用之前建议写一个简单案例尝试一下当前使用的数据库能否使用。

通配符 方括号

方括号是单字符匹配的方式，也就是说[ab]会匹配a或者b其中一个内容。

附录

对于初学者来说可以参考下面的附录学习，另外附录中作者提到的表连接已经失效的了，建议按照附录A的内容自己创建表（使用SQL语句创建）来当做练习了。

• 附录 B SQL 语句的语法：常见一些SQL语法速查，在自己没有编写SQL思路的时候可以参考学习。
• 附录C - SQL 数据类型：数据类型在不同的数据库实现细节不同，附录C的数据类型虽然不值得深究但是作为对比和参考是比较合适的
• 附录 - 保留字：保留字看起来多的吓人，但是实际上也是有技巧的，直接下载一个可视化的DB管理工具，
• 常用 SQL 语句速查：这个不用多说，其实相当于作者告诉你哪些章节重要了。

写在最后

对于初学者来说比较友好的一本书，十分推荐。