《SQL必知必会》读书笔记

原创

阿东

发布于 2022-04-29 09:44:53

7720

发布于 2022-04-29 09:44:53

文章被收录于专栏：公众号：懒时小窝

引言

因为之前看过《Mysql必知必会》，所以看到这本书的名字之后挺感兴趣的，但是内容比较基础和入门所以大多是跳读的，本次笔记更多是结合过去所学内容。

个人评价

这本书可以说是能让初学SQL的技术人员用最快速度入门的一本书，对于很多一上来不知道SQL语法觉得概念很抽象的人来说也能对SQL领域有一个大致的了解，作为一个过来人当初觉得SQL真的挺神奇的。

这本书当然只适合新手，对于老手来说更多是快速回顾和查漏补缺，所以这一篇读书笔记将会简单提炼一些忽视的部分记录，以及工作实践之后对于书中一些知识点内容进行建议的补充和解释。

个人已经看过非常老的小绿本的《Mysql必知必会》版本，看到出到这本书的名字之后想再看看这本书。

最后千万不要认为SQL很简单，其实越是看似简单的东西越是复杂。魔鬼常在细节中。

笔记内容很多，建议按需阅读。

笔记索引

重要章节索引

很多读者可能不爱看附录的内容，因为很多时候里面都是参考资料，但是这本书比较特别，作者把自己认为重要和常用SQL语法列了出来并且标记了对应的章节，相信你已经明白这是什么意思了，所以这篇读书笔记直接把这一块内容迁移过来了：

不知道是不是出于让读者能看下去考虑，很多比较重要的内容都往后面的章节编排。另外下面标注的内容多是CRUD的内容，因为工作过程中用的最多。

ALTER TABLE ALTER TABLE 用来更新现存表的模式。可以用 CREATE TABLE 来创建一个新表。详情可参见第 17课。

COMMIT COMMIT 用来将事务写入数据库。详情可参见第 20课。

CREATE INDEX CREATE INDEX 用来为一列或多列创建索引。详情可参见第 22课。

CREATE TABLE CREATE TABLE 用来创建新的数据库表。可以用 ALTER TABLE 来更新一个现存表的模式。详情可参见第 17课。

CREATE VIEW CREATE VIEW 用来创建一个或多个表的视图。详情可参见第 18课。

DELETE DELETE 用来从表中删除一行或多行。详情可参见第 16课。

DROP DROP 用来永久性地删除数据库对象（表、视图和索引等）。详情可参见常用 SQL 语句速查第 17课和第 18课。

INSERT INSERT 用来对表添加一个新行。详情可参见第 15课。

INSERT SELECT INSERT SELECT 用来将 SELECT 的结果插入到表中。详情可参见第 15课。 ROLLBACK ROLLBACK 用来撤销事务块。详情可参见第 20课。

SELECT SELECT 用来从一个或多个表（或视图）中检索数据。详情可参见第 2课、第 3课和第 4课（第 2课到第 14课从不同方面涉及了 SELECT ）。

UPDATE UPDATE 用来对表中的一行或多行进行更新。详情可参见第 16课。

其他索引

个人笔记部分拆分为多个模块，具体的模块如下：

复杂查询：复杂查询包含子查询，join连接查询，组合查询union和数据分组group，之所以叫复杂查询也是因为日常工作中编写的大量复杂SQL基本都有他们的身影，所以要想编写高效SQL需要对于这些内容有较好的掌握。

函数操作：指的是函数操作建议使用官方提供的函数，不建议使用自己编写的函数，不仅难以维护而且随着业务的拓展很有可能导致函数的不可阅读，之后介绍了关于存储过程的内容。

多条件过滤：多条件过滤包含like和通配符的使用，like语句对于性能的影响还是比较大的，同时本身能使用上索引的场景也不多，最后哪怕使用上索引对扫描效率也是比较低的索引扫描方式。

什么是SQL

SQL在表面上看是编程语言，实际上它掩盖了包含数据库，操作系统，甚至各种底层编程语言的，可以说SQL是现今互联网或者说WEB应用的核心，看似简单的SQL语言其实承载的前人智慧的结晶和精华，在过去很难想象一条SQL可以让人从身无分文到亿万富翁，操作失误也可以瞬间让6万人电话无法拨打。

从程序眼的角度来说，编写数据库的技术人员对于技术的要求和门槛是最高的，同时也是对于综合硬实力的一个硬核考核指标，写出优秀的框架代码虽然可能受人欢迎，但是会优化数据库能把系统效率直线提升的可以发现确实很少。

重要章节笔记

下面的内容对应上面提到的章节索引内容。

查询（第 2课）

查询语句使用的是SELECT语句，在查询当中可以通过 * 号查询所有的列，可以指定列值，几乎所有的情况都不建议使用 * 作为列值，一方面是增删某些字段导致一些实体映射出问题，另一方面星号最大的问题是对于查询性能的影响，查询的列越多性能越差。

如果想要限制返回的结果，不同的数据库厂商实现不同：

Mysql、MariaDB和Postgresql 中实现比较简单，可以通过 limit关键字进行过滤，但是需要注意“深分页”问题。

深分页比较常见的解决方案是 延迟关联，这种处理方式也比较通用。

对于不支持limit函数的数据库使用的是使用嵌套查询的方式，比如Oracle 数据库的分页模板如下：

/*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})

内层查询主要的作用是获取行号，通过（当前页 * 每页数量）定位到“结尾”记录行，再通过一层嵌套“过滤”掉不符合页码的行，注意这种查询和limit一样都是不保证排序的。

其实本质上就是 limit 的 SQL实现写法。limit 也可以看作是语法糖，过去在SQL规范中无此要求，所以并不是所有数据库都支持。

嵌套查询分页的实践案例如下：

-- 案例
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规范的语句，目的是有可能迁移库的时候少一些无聊的工作。

排序（第3课）

建议使用排序的时候尽量让排序字段和查询的列对应，最理想的状态是只包含order by 列的查询让他可以使用覆盖索引的查询方式，不能忽视order by带来的性能问题和影响。

order by 排序还有一个比较值得注意的特点是：多列排序只对出现相同的值进行排序，也就是说多个行的值相同的情况下，数据库才会对后面指定对排序列进行排序，如果多列查询前面对列都是唯一的值是不能保证后面的内容是有序的。

这里举一个例子order by a,b,c，如果a都是唯一值进行排序，才会对于b进行排序，而如果因为b的排序而影响列a的顺序，显然这是不合逻辑的，同理列c也是如此。

除了按照列排序之外，还可以指定列位置进行排序，当然基本没人会用，这里了解有这个用法即可。

-- 列位置排序
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY 2, 3;

排序方向包含asc升序和desc降序，需要注意部分数据库只支持降序索引（比如Mysql）所以指定排序方向还是十分重要的。

另外避免使用一个列升序一个列降序的方式进行查询，不仅导致大量的内存文件排序或者磁盘临时表排序，本身还会直接导致索引失效走全表扫描。

过滤数据（第4课）

有关where 条件的操作符如下：

上面几个操作符号最容易踩坑的可能是 <>和 !=，还有is null。 !=和<>通常可以互换。但是并非所有 DBMS 都支持这两种不等于操作符，另外!=和<>是 不会包括值为NULL的数据的，这个细节从侧面映证了NULL在数据库当中有其特殊的含义。

<> 在数据库中表示 不等于，虽然和 !=的作用等价，但是有些数据库不支持!=的写法，比如sqlserver，所以使用<> 会比较规范一些。

比较 != null 和 is null

首先准备一份案例数据：

CREATE TABLE `admin` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键，自增',
  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户名',
  `password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '密码',
  `gender` int DEFAULT NULL COMMENT '1为男，2为女',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;

INSERT INTO `admin` (`id`, `username`, `password`, `gender`) VALUES (1, '小红', '111', 2);
INSERT INTO `admin` (`id`, `username`, `password`, `gender`) VALUES (2, '小蓝', '222', NULL);
INSERT INTO `admin` (`id`, `username`, `password`, `gender`) VALUES (0, '小黄', NULL, 1);

首先我们来看下<>和!= 两种写法的查询结果的区别：

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 值的行。但是这做不到。因为 NULL 比较特殊，所以在进行匹配过滤或非匹配过滤时，不会返回这些结果。

插入数据（第 15课）

插入语句的写法是insert into table values (xxx,xxx)或者insert into table (xxx,xx) values (xxx,xxx)，在编写插入语句的时候建议指定插入列，因为一旦新增字段如果插入列的SQL没有更新会直接导致业务报错。

INSERT INTO Customers(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
SELECT cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM CustNew;

另外插入操作一个很常见的问题是 批量插入问题，对于大数据量处理，个人在过去的文章也做过总结，这里就不再啰嗦了：

# 一次大数据文件处理日记

更新和删除数据（第 16课）

对于更新和删除的动作记住一条铁律：做任何更新和删除操作之前先查询一遍确认操作的结果是否符合预期的效果，这里特别强调需要注意where条件中对于 NULL 值的列数据匹配问题。

另外通常数据库管理比较严格的公司或者项目一般都不会给更新或者删除权限，而是需要经过运维或者DBA的审查之后进行特殊环境的操作才能完成整个操作，所以需要在更新操作之前确保是否具备足够的用户权限。

部分数据库支持通过查询的结果进行更新，比如Postgresql就支持下面的写法：

update tables
set tables.name = tmp.name
from 
(select id,name from User where name = 'xxx')
tmp
where tmp.id = tables.id;

这样的写法并不是所有的数据库支持，如果不需要关注数据库移植问题可以放心大胆的使用，体验一条SQL包罗万象的感觉。

删除数据

在使用 DELETE 时一定要细心不要漏了WHERE 条件，一旦缺少这个条件......可不是等着被请去喝茶那么简单。

如果想从表中删除所有行不建议使用 DELETE 。可使用 TRUNCATE TABLE。这个指令不会记录相关日志直接删除数据，代价是一旦删错后果自负。

另外删除数据在BTree结构的底层并不是真的删除，以MYSQL为例在删除的时候只是把当前记录的行标示位标记“已删除”，然后后续在后台进程定时回收或者复用不可用页数据。

更新和删除原则

除非确实打算更新和删除每一行，否则绝对不要使用不带 WHERE 子句的 UPDATE 或 DELETE 语句。
保证每一个表都有主键。
先SELECT后DELETE或者UPDATE 永远是一个好习惯。
对于ORM框架来说建议编写插件或者拦截方式WHERE条件的UPDATE或者DELETE执行。

创建表（17课）

大多数时候我们使用 navicat 这样的工具设计表结构，因为使用原生SQL的方式根据不同的数据库实现会有不同，差别点在数据类型和语法的差别上，利用可视化软件也算是帮我们省去了细节的不同差别。

但是不能脱离工具之后忘记建表语句怎么写，下面来看看创建表的最简单案例：

CREATE TABLE Products
(
	prod_id CHAR(10) NOT NULL,
	vend_id CHAR(10) NOT NULL,
	prod_name CHAR(254) NOT NULL,
	prod_price DECIMAL(8,2) NOT NULL,
	prod_desc VARCHAR(1000) NULL
);

NULL 值和空字符串注意这两者有本质的区别，NULL值在数据库中是一个特殊值，在聚合函数统计的时候常常会被排除在统计范围内（除了count()函数较为特别之外）。而空字符串是一个具体的值，并不是没有值。

和前文提到的一样，更多情况下使用defualt默认值代替null或者not null的情况，原因是过多的not null不是很好造数据测试，还有一种方式是放弃数据库做not null限制，而是在业务代码中通过设置默认值防止null值出现，这也是一种思路。

注意事项

不要在表中包含数据时对其进行更新，数据量较小的时候可能看不出问题，但是一旦数据量上百万级别，修改一个字段的时候数据库通常会加上元数据锁，同时由于需要调整底层数据结构，通常需要耗费非常多的时间。
设计表不建议使用"预留字段"的方式在后续扩展的时候进行兼容，这种预留字段看似很有用，但是实际使用的时候会发现给的预留字段往往无法满足业务要求还需要修改预留字段的数据类型，或者需要另外加新的字段。
多数 DBMS允许重新命名表中的列。
所有的 DBMS都允许给现有的表增加列，不过对所增加列的数据类型（以及 NULL 和 DEFAULT 的使用）有所限制。

大数据量表修改表结构通常通过备份，新建表，迁移数据，删除旧表的方式。

大表新增字段：

对于复杂的表结构更改一般需要手动删除过程提出以下步骤：

(1) 用新的列布局创建一个新表；
(2) 使用 INSERT SELECT 语句（关于这条语句的详细介绍，请参阅第 15课）从旧表复制数据到新表。有必要的话，可以使用转换函数和计算字段；
(3) 检验包含所需数据的新表；
(4) 重命名旧表（如果确定，可以删除它）；
(5) 用旧表原来的名字重命名新表；
(6) 根据需要，重新创建触发器、存储过程、索引和外键

经验技巧：对于快速了解表及业务，可以通过在本地重命名这张表观察业务会出现什么变化，这样就能快速的掌握一张表具体干了什么事情，这对于字段特别特别多的表来说是非常好用的一条规则，当然 仅仅本地能这么干。

使用 ALTER TABLE 要极为小心，应该在进行改动前做完整的备份（表结构和数据的备份）。

因为数据库表的更改不能撤销，如果增加了不需要的列可能无关紧要，但是删除某一列之后会导致当前数据库的当前列数据丢失并且无法找回。

删除表

DROP TABLE CustCopy; 这条语句的使用也要极为小心，删除之前先查询几遍反复确认没有删除错误，另外不要在精神不好的时候特别是熬夜干活的时候干这种事情。

重命名

所有重命名操作的基本语法都要求指定旧表名和新表名。

视图（第 18课）

虽然创建视图的语法类似，但是很可惜视图不仅在不同的数据库实现细节差别很大，在同一个数据库的不同版本也可能会有很大差别，建议根据自己所用的数据库官方文档确认具体有哪些限制和操作方法。

视图通常分为两种：逻辑视图和物化视图。逻辑视图通常指的是数据库根据优化器的优化查询树生成一张虚拟表，这张表不占用实际的存储空间，而物化视图则会占用实际的存储空间。

下面是创建视图的一个案例：

CREATE VIEW OrderItemsExpanded AS
SELECT order_num,
prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM OrderItems

为什么使用视图？

重用SQL：这一点很关键，视图主要目的就是为了简化大量重复操作。
对于经常只读的数据使用视图可以简化大量重复操作，同时可以简化复杂的SQL语句。
保护原表数据同时视图可以自由定义返回数据的格式，不需要受到底层数据表的字段数据类型限制。
可以只使用表的一部分而不是整个表的数据。

视图性能问题因为视图使用的是根据查询优化树去查询数据，所以其实视图并不包含数据，所以每次使用视图时，都必须处理查询执行时。

视图限制

对于视图的限制不同数据库供应商具体实现差别较大，所以下面的条例并不是完全适用所有数据库。

视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造视图。
一些数据库实现不允许ORDER BY。
如果列是计算字段通常需要重命名。
部分数据库实现的视图只是一个只读列表，不能通过修改视图修改底层数据。
视图不能索引，也不能有关联的触发器或默认值。
与表一样，视图必须唯一命名。
视图的创建需要遵循SELECT的限制和规则。

如果在视图中加入where条件，则会自动合并where条件然后返回结果，如果难以理解可以认为是下面的情况： select (view info) where xx = xxx

事务（第 20课）

关于事务的相关理论这里不做过多的解释，我们只需要关注事务的4个常见术语：

事务（transaction）指一组 SQL语句；
回退（rollback）指撤销指定 SQL语句的过程；
提交（commit）指将未存储的 SQL语句结果写入数据库表；
保留点（savepoint）指事务处理中设置的临时占位符（placeholder），可以对它发布回退（与回退整个事务处理不同）。

对于事务的开启通常可以使用语法begin transaction;或者begin，如果想要撤销操作使用rollback回滚当前的所有事务或者使用保存点的方式回滚到指定保存点的位置，最后通过commit或者commit trainsaction;提交事务。

当然这些原生的事务操作接触机会比较少，更多情况下我们在框架中使用事务，这里可以参考过去过去写的文章进行了解：

Spring 事务机制的个人总结

游标

说实话对于这种几乎用不到的东西没啥学的价值，这里就直接跳过了，因为真的碰不到使用场景。

高级特性（第22课）

我认为高级特性里面都是比较重要的内容，但是不知道作者为什么要安排到最后一章节。

高级特性包含下面几点：

约束
索引
触发器
数据库安全

约束和索引

约束指的是针对某个字段的限制，而索引则是约束的具体实现。多数时候可能比较模糊的是唯一索引和唯一约束区别，主键索引和唯一索引的区别。

唯一索引和唯一约束的不同点：

唯一约束通过唯一索引实现。
删除唯一约束可以删除约束但是对应的索引结构不会一并删除，所以对应列在删除约束之后依然不能删除，但是如果约束和索引一起删除就可以插入重复值。
有些数据库供应商会针对构建的唯一约束创建唯一索引，但是如果单纯的创建唯一索引不会创建唯一约束的。也就是说虽然实现的效果都是不能插入重复值，但是本质还是有差别的。

主键约束和唯一约束的区别：

表可包含多个唯一约束，但每个表只允许一个主键。
唯一约束列可包含 NULL 值。
唯一约束列可修改或更新。
唯一约束列的值可重复使用，主键值则不可以二次使用。
与主键不一样，唯一约束不能用来定义外键。

检查约束

检查约束比较经典的使用场景是通过乐观锁和检查约束限制交易金额不能为负数，这样可以从数据库层面保证数据的安全和完整性。

创建检查约束的案例如下：

CREATE TABLE OrderItems
(
	order_num INTEGER NOT NULL,
	order_item INTEGER NOT NULL,
	prod_id CHAR(10) NOT NULL,
	quantity INTEGER NOT NULL CHECK (quantity > 0),
	item_price MONEY NOT NULL
);

索引

索引和其他的高级特性不同，索引使用非常灵活并且没有一套非常固定的规则，在不同的业务中使用索引的方式不同，但是在创建索引的时候依然可以遵循下面的规则：

尽量保证索引的可选择性：可选择性指的这里举两个简单例子，比如性别就不适合做索引，他只有1/2的选择性，而流水订单号就很适合做索引因为他在定义上就不允许重复。
查多写少的表比较适合加索引：维护索引需要维护底层的Btree树，这对于性能影响是比较大的，同时索引的直接影响是插入和更新以及删除的性能影响
一张表的索引不建议超过5个。
谨慎对待varchar或者char类型的索引，字符长度越长创建索引的开销和代价越大
索引应该在设计表的一开始就考虑好，而不是等业务跑了很久之后突然添加索引。

虽然编写效率高的SQL非常重要，但是在SQL优化能力较弱的时候用对索引更为重要。

关于索引的使用经验和案例可以查看下面的内容：

# 三高Mysql - Mysql索引和查询优化（偏实战部分）

# 三高Mysql - Mysql索引和查询优化讲解（偏理论部分）

数据库安全

数据库安全通常指下面的内容：

对数据库管理功能（创建表、更改或删除已存在的表等）的访问；
对特定数据库或表的访问；
访问的类型（只读、对特定列的访问等）；
仅通过视图或存储过程对表进行访问；
创建多层次的安全措施，从而允许多种基于登录的访问和控制；
限制管理用户账号的能力。

复杂查询

子查询

子查询通常和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 中没有 AS Oracle不支持 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 语句速查：这个不用多说，其实相当于作者告诉你哪些章节重要了。

写在最后

回过来再看这本书发现这本书的知识密度其实一般，所以不建议作为工具书，快速掌握知识点之后就可以抛在一边挑一个主流数据库去用就行了。

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

数据库

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

数据库

#SQL

#SQL必知必会