引言

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

个人评价

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

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

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

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

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

笔记索引

重要章节索引

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

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

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

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

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

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

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

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

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

第 17课和第 18课。

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

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

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

UPDATEUPDATE 用来对表中的一行或多行进行更新。详情可参见第 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和更多的更多细节，可以参考下面的内容了解：

[[盘点数据库中的一些坑（一）]]：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索引和查询优化讲解（偏理论部分）

数据库安全

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

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

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