关于sql:SQL必知必会读书笔记

《SQL 必知必会》读书笔记

引言

因为之前看过《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 和更多的更多细节，能够参考上面的内容理解：

[[盘点数据库中的一些坑（一）]]：https://juejin.cn/post/704471…

所以为了让你查问的时候不陷入各种困惑和懊恼，设计数据库的时候倡议应用 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 语句速查：这个不必多说，其实相当于作者通知你哪些章节重要了。

  # 写在最初

笔记内容很多，倡议大家按需浏览。