一、开篇词丨SQL可能是你掌握的最有用的技能

SQL 作为与数据直接打交道的语言，是与各种前端、后端语言进行交互的“中台”语言。
尽管技术人员或多或少地会使用 SQL，但不同的人编写出来的 SQL 的效率是不同的，比如说一份好的 SQL 执行计划就会尽量减少 I/O 操作，因为 I/O 是 DBMS 最容易出现瓶颈的地方，可以说数据库操作中有大量的时间都花在了 I/O 上。此外，你还需要考虑如何降低 CPU 的计算量，在 SQL 语句中使用 GROUP BY、ORDER BY 等这些语句会消耗大量的 CPU 计算资源，因此我们需要从全局出发，不仅需要考虑数据库的 I/O 性能，还需要考虑 CPU 计算、内存使用情况等。比如 EXISTS 查询和 IN 查询在某些情况下可以得到相同的结果，但是具体执行起来，哪个效率更高呢？假设我把这个模式抽象为下面这样：

SELECT * FROM A WHERE cc IN (SELECT cc FROM B)SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc=A.cc)
在查询过程中，我们需要判断表 A 和表 B 的大小。如果在有索引的情况下，表 A 比表 B 大，那么 IN 子查询的效率比 EXISTS 子查询效率高。
实际上用一条 SQL 语句，你就可以直接从数据表中得到答案：SELECT * FROM heros WHERE hp_max >= 7000 AND role = '法师'
SQL 语句直观到你就算没有 SQL 基础，也能凭借英语基础猜出它的大致意思。这就是 SQL 最大的特点。假如你是一名运营人员，想要看下 7 天内的新增用户数有多少，该怎么做呢？首先我们需要获取现在的时间，使用 NOW() 函数即可，然后把它转化成天数，与用户的注册时间进行对比，小于 7 天的时间即是我们的筛选条件，最后就可以得到想要的数据了：
SELECT COUNT(*) as num FROM new_user WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7
上面举的两个例子属于相对简单的 SQL 查询，SQL 还可以帮助你统计每日新增、每日活跃、次日留存的数据。
事实上除了业务之外，基于数据的各种技术中也会用到 SQL，比如 OLTP（联机事务处理过程）、OLAP（联机分析处理过程）、RDBMS（对象关系型数据库管理系统）。甚至是在 NoSQL 的阵营上，如今也在使用类似 SQL 的操作，要知道，提出 NoSQL 这个概念的初衷就是远离 SQL，但如今人们更愿意把 NoSQL 定义为 Not Only SQL（不只是 SQL）。此外在我们熟悉的 XML、JSON 等数据格式中，都存在着各种 SQL，比如用于 XML 的 SQL、用于 JSON 的 SQL 等。除此以外，还包括了用于记录地理位置信息的 SQL、用于搜索的 SQL、用于时间序列数据的 SQL、用于流的 SQL 等。

二、SQL语法基础

1、了解SQL：一门半衰期很长的语言

我们可以把 SQL 语言按照功能划分成以下的 4 个部分：

1. DDL，英文叫做 Data Definition Language，也就是数据定义语言，它用来定义我们的数据库对象，包括数据库、数据表和列。通过使用 DDL，我们可以创建，删除和修改数据库和表结构。
2. DML，英文叫做 Data Manipulation Language，数据操作语言，我们用它操作和数据库相关的记录，比如增加、删除、修改数据表中的记录。
3. DCL，英文叫做 Data Control Language，数据控制语言，我们用它来定义访问权限和安全级别。
4. DQL，英文叫做 Data Query Language，数据查询语言，我们用它查询想要的记录，它是 SQL 语言的重中之重。在实际的业务中，我们绝大多数情况下都是在和查询打交道，因此学会编写正确且高效的查询语句，是学习的重点。

SQL 是我们与 DBMS 交流的语言，我们在创建 DBMS 之前，还需要对它进行设计，对于 RDBMS 来说采用的是 ER 图（Entity Relationship Diagram），即实体 - 关系图的方式进行设计。ER 图评审通过后，我们再用 SQL 语句或者可视化管理工具（如 Navicat）创建数据表。

实体 - 关系图有什么用呢？它是我们用来描述现实世界的概念模型，在这个模型中有 3 个要素：实体、属性、关系。实体就是我们要管理的对象，属性是标识每个实体的属性，关系则是对象之间的关系。比如我们创建了“英雄”这个实体，那么它下面的属性包括了姓名、职业、最大生命值、初始生命值、最大魔法值、初始魔法值和攻击范围等。同时，我们还有“用户”这个实体，它下面的属性包括用户 ID、登录名、密码、性别和头像等。

关于 SQL 大小写的问题，我总结了下面两点：表名、表别名、字段名、字段别名等都小写；SQL 保留字、函数名、绑定变量等都大写。
SELECT name, hp_max FROM heros WHERE role_main = '战士'
你能看到 SELECT、FROM、WHERE 这些常用的 SQL 保留字都采用了大写，而 name、hp_max、role_main 这些字段名，表名都采用了小写。此外在数据表的字段名推荐采用下划线命名，比如 role_main 这种。

02、DBMS的前世今生

SEQUEL 的语言标准是开放的，但是围绕它的商业化竞争却从来没有停止过。首先因为商标之争，SEQUEL 改名为 SQL，后来有一个重量级的公司基于那篇论文研发出了商业化的数据库管理软件，这就是 Oracle。自此之后，又诞生了一堆大家熟知的 DBMS，比如 MySQL、SQL Server、PostgreSQL、DB2 和 MongoDB 等。

DB、DBS 和 DBMS 的区别是什么
DBMS 的英文全称是 DataBase Management System，数据库管理系统，实际上它可以对多个数据库进行管理，所以你可以理解为 DBMS = 多个数据库（DB） + 管理程序。
DB 的英文是 DataBase，也就是数据库。数据库是存储数据的集合，你可以把它理解为多个数据表。
DBS 的英文是 DataBase System，数据库系统。它是更大的概念，包括了数据库、数据库管理系统以及数据库管理人员 DBA。

2019 年 5 月 DB-Engines 公布的 DBMS 的排名
关系型数据库（RDBMS）就是建立在关系模型基础上的数据库，SQL 就是关系型数据库的查询语言。相比于 SQL，NoSQL 泛指非关系型数据库，包括了榜单上的键值型数据库、文档型数据库、搜索引擎和列存储等，除此以外还包括图形数据库。
键值型数据库通过 Key-Value 键值的方式来存储数据，其中 Key 和 Value 可以是简单的对象，也可以是复杂的对象。Key 作为唯一的标识符，优点是查找速度快，在这方面明显优于关系型数据库，同时缺点也很明显，它无法像关系型数据库一样自由使用条件过滤（比如 WHERE），如果你不知道去哪里找数据，就要遍历所有的键，这就会消耗大量的计算。
键值型数据库典型的使用场景是作为内容缓存。Redis 是最流行的键值型数据库。文档型数据库用来管理文档，在数据库中文档作为处理信息的基本单位，一个文档就相当于一条记录，MongoDB 是最流行的文档型数据库。搜索引擎也是数据库检索中的重要应用，常见的全文搜索引擎有 Elasticsearch、Splunk 和 Solr。虽然关系型数据库采用了索引提升检索效率，但是针对全文索引效率却较低。搜索引擎的优势在于采用了全文搜索的技术，核心原理是“倒排索引”。
列式数据库是相对于行式存储的数据库，Oracle、MySQL、SQL Server 等数据库都是采用的行式存储（Row-based），而列式数据库是将数据按照列存储到数据库中，这样做的好处是可以大量降低系统的 I/O，适合于分布式文件系统，不足在于功能相对有限。
图形数据库，利用了图这种数据结构存储了实体（对象）之间的关系。
最典型的例子就是社交网络中人与人的关系，数据模型主要是以节点和边（关系）来实现，特点在于能高效地解决复杂的关系问题。

SQL 阵营与 NoSQL 阵营
NoSQL 的分类很多，刚才提到的键值型、文档型、搜索引擎、列式存储和图形数据库等都属于 NoSQL 阵营。也只有用 NoSQL 一词才能将这些技术囊括进来。即便如此，在 DBMS 排名中，还是 SQL 阵营的比重更大，影响力前 5 的 DBMS 中有 4 个是关系型数据库，而排名前 20 的 DBMS 中也有 12 个是关系型数据库。所以说，掌握 SQL 是非常有必要的。

03、学会用数据库的方式思考SQL是如何执行的

Oracle 中的 SQL 是如何执行的

从上面这张图中可以看出，SQL 语句在 Oracle 中经历了以下的几个步骤。

1. 语法检查：检查 SQL 拼写是否正确，如果不正确，Oracle 会报语法错误。
2. 语义检查：检查 SQL 中的访问对象是否存在。比如我们在写 SELECT 语句的时候，列名写错了，系统就会提示错误。语法检查和语义检查的作用是保证 SQL 语句没有错误。
3. 权限检查：看用户是否具备访问该数据的权限。
4. 共享池检查：共享池（Shared Pool）是一块内存池，最主要的作用是缓存 SQL 语句和该语句的执行计划。Oracle 通过检查共享池是否存在 SQL 语句的执行计划，来判断进行软解析，还是硬解析。那软解析和硬解析又该怎么理解呢？在共享池中，Oracle 首先对 SQL 语句进行 Hash 运算，然后根据 Hash 值在库缓存（Library Cache）中查找，如果存在 SQL 语句的执行计划，就直接拿来执行，直接进入“执行器”的环节，这就是软解析。如果没有找到 SQL 语句和执行计划，Oracle 就需要创建解析树进行解析，生成执行计划，进入“优化器”这个步骤，这就是硬解析。
5. 优化器：优化器中就是要进行硬解析，也就是决定怎么做，比如创建解析树，生成执行计划。
6. 执行器：当有了解析树和执行计划之后，就知道了 SQL 该怎么被执行，这样就可以在执行器中执行语句了。

WX20200527-142805@2x.png

MySQL 中的 SQL 是如何执行的

MySQL 是典型的 C/S 架构，即 Client/Server 架构，服务器端程序使用的 mysqld

你能看到 MySQL 由三层组成：

1. 连接层：客户端和服务器端建立连接，客户端发送 SQL 至服务器端；

2. SQL 层：对 SQL 语句进行查询处理；

3. 存储引擎层：与数据库文件打交道，负责数据的存储和读取。

   
   SQL 层的结构

4. 查询缓存：Server 如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没有，就进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在 MySQL8.0 之后就抛弃了这个功能。

5. 解析器：在解析器中对 SQL 语句进行语法分析、语义分析。

6. 优化器：在优化器中会确定 SQL 语句的执行路径，比如是根据全表检索，还是根据索引来检索等。

7. 执行器：在执行之前需要判断该用户是否具备权限，如果具备权限就执行 SQL 查询并返回结果。在 MySQL8.0 以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。
   你能看到 SQL 语句在 MySQL 中的流程是：SQL 语句→缓存查询→解析器→优化器→执行器。在一部分中，MySQL 和 Oracle 执行 SQL 的原理是一样的。与 Oracle 不同的是，MySQL 的存储引擎采用了插件的形式，每个存储引擎都面向一种特定的数据库应用环境。同时开源的 MySQL 还允许开发人员设置自己的存储引擎，下面是一些常见的存储引擎：

8. InnoDB 存储引擎：它是 MySQL 5.5 版本之后默认的存储引擎，最大的特点是支持事务、行级锁定、外键约束等。

9. MyISAM 存储引擎：在 MySQL 5.5 版本之前是默认的存储引擎，不支持事务，也不支持外键，最大的特点是速度快，占用资源少。

10. Memory 存储引擎：使用系统内存作为存储介质，以便得到更快的响应速度。不过如果 mysqld 进程崩溃，则会导致所有的数据丢失，因此我们只有当数据是临时的情况下才使用 Memory 存储引擎。

11. NDB 存储引擎：也叫做 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群环境，类似于 Oracle 的 RAC 集群。

12. Archive 存储引擎：它有很好的压缩机制，用于文件归档，在请求写入时会进行压缩，所以也经常用来做仓库。

    
    图片.png
    图片.png

既然一条 SQL 语句会经历不同的模块，那我们就来看下，在不同的模块中，SQL 执行所使用的资源（时间）是怎样的。下面我来教你如何在 MySQL 中对一条 SQL 语句的执行时间进行分析。首先我们需要看下 profiling 是否开启，开启它可以让 MySQL 收集在 SQL 执行时所使用的资源情况，命令如下：

mysql> select @@profiling;

profiling=0 代表关闭，我们需要把 profiling 打开，即设置为 1： 图片.png 图片.png

04丨使用DDL创建数据库&数据表时需要注意什么？

DDL 的英文全称是 Data Definition Language，中文是数据定义语言。它定义了数据库的结构和数据表的结构。
在 DDL 中，我们常用的功能是增删改，分别对应的命令是 CREATE、DROP 和 ALTER。需要注意的是，在执行 DDL 的时候，不需要 COMMIT，就可以完成执行任务。

图片.png