SQL必知必会学习笔记(一)
学极客时间的 SQL必知必会专栏，做记录
初步了解
我们可以把 SQL 语言按照功能划分成以下的 4 个部分：
- DDL，英文叫做 Data Definition Language，也就是数据定义语言，它用来定义我们的数据库对象，包括数据库、数据表和列。通过使用 DDL，我们可以创建，删除和修改数据库和表结构。
- DML，英文叫做 Data Manipulation Language，数据操作语言，我们用它操作和数据库相关的记录，比如增加、删除、修改数据表中的记录。
- DCL，英文叫做 Data Control Language，数据控制语言，我们用它来定义访问权限和安全级别。
- DQL，英文叫做 Data Query Language，数据查询语言，我们用它查询想要的记录，它是 SQL 语言的重中之重。在实际的业务中，我们绝大多数情况下都是在和查询打交道，因此学会编写正确且高效的查询语句，是学习的重点。
SQL 语言定义了我们的需求，而不同的 DBMS（数据库管理系统）则会按照指定的 SQL 帮我们提取想要的结果。采用ER图（Entity Relationship Diagram / 实体 - 关系图）来设计数据库，ER 图评审通过后，我们再用 SQL 语句或者可视化管理工具（如 Navicat）创建数据表。ER图是我们用来描述现实世界的概念模型，在这个模型中有 3 个要素：实体、属性、关系，实体就是我们要管理的对象，属性是标识每个实体的属性，关系则是对象之间的关系，实体之间可以是一对一、一对多或多对多的关系。
SQL 书写规范：
- 表名、表别名、字段名、字段别名等都小写；
- SQL 保留字、函数名、绑定变量等都大写。
-- 例子
SELECT name, hp_max FROM heros WHERE role_main = '战士'底下留言中有在争执数据库系统大小写的规范，我们使用大小写的规范是为了使代码可读性提高，即使数据库系统本身不区分大小写，也不影响我们的书写规范，通常情况下，我们的「代码写出来都是给人读的，附带在机器上运行」。
MySQL在Windows下都不区分大小写。 Oracle中，SQL语句是不区分大小写，如果查询中有字符，是区分大小写的 比如 SELECT * FROM heros WHERE name = 'guanyu' 和 SELECT * FROM heros WHERE name = 'GUANYU' 在Oracle中会认为是不同的查询，而在MySQL中是相同的查询 同时，我们可以通过修改系统参数来进行配置，比如在MySQL可以通过参数lower_case_table_names来配置 数据库和数据表的大小写敏感性
了解 DBMS
比如 MySQL、SQL Server、PostgreSQL、DB2 和 MongoDB 等，这些都是我们比较熟知的 DBMS ，我们使用 SQL 和这些 DBMS 沟通进行数据的存取。
- DBMS 的英文全称是 DataBase Management System，数据库管理系统，实际上它可以对多个数据库进行管理，所以你可以理解为 DBMS = 多个数据库（DB） + 管理程序。
- DB 的英文是 DataBase，也就是数据库。数据库是存储数据的集合，你可以把它理解为多个数据表。
- DBA 的英文是 DataBase Administrator，数据库管理员。一般企业里的 DBA 主要负责数据库的权限控制、维护及一些偏底层的事情，像表设计查询优化这种业务相关的通常是业务上的后端处理。
- DBS 的英文是 DataBase System，数据库系统。它是更大的概念，包括了数据库、数据库管理系统以及数据库管理人员 DBA。
数据库类型
- 关系型数据库（RDBMS）就是建立在关系模型基础上的数据库，SQL 就是关系型数据库的查询语言。
- 键值型数据库通过 Key-Value 键值的方式来存储数据，其中 Key 和 Value 可以是简单的对象，也可以是复杂的对象。Key 作为唯一的标识符，优点是查找速度快，在这方面明显优于关系型数据库，同时缺点也很明显，它无法像关系型数据库一样自由使用条件过滤（比如 WHERE），如果你不知道去哪里找数据，就要遍历所有的键，这就会消耗大量的计算。键值型数据库典型的使用场景是作为内容缓存。Redis 是最流行的键值型数据库。
- 文档型数据库用来管理文档，在数据库中文档作为处理信息的基本单位，一个文档就相当于一条记录，MongoDB 是最流行的文档型数据库。
- 搜索引擎也是数据库检索中的重要应用，常见的全文搜索引擎有 Elasticsearch、Splunk 和 Solr。虽然关系型数据库采用了索引提升检索效率，但是针对全文索引效率却较低。搜索引擎的优势在于采用了全文搜索的技术，核心原理是“倒排索引”。
- 列式数据库是相对于行式存储的数据库，Oracle、MySQL、SQL Server 等数据库都是采用的行式存储（Row-based），而列式数据库是将数据按照列存储到数据库中，这样做的好处是可以大量降低系统的 I/O（行式存储是把一行的数据都串起来进行存储，然后再存储下一行。同样，列式存储是把一列的数据都串起来进行存储，然后再存储下一列。这样做的好处，就是相邻的数据的数据类型是一样的，因此也更容易压缩。压缩之后就自然降低了IO），适合于分布式文件系统，不足在于功能相对有限。
- 图形数据库，利用了图这种数据结构存储了实体（对象）之间的关系。最典型的例子就是社交网络中人与人的关系，数据模型主要是以节点和边（关系）来实现，特点在于能高效地解决复杂的关系问题。
相比于 SQL，NoSQL 泛指非关系型数据库，包括了上述的键值型数据库、文档型数据库、搜索引擎、列存储和图形数据库。
SQL 执行
Oracle
SQL 在 Oracle 中的执行过程：
从上面这张图中可以看出，SQL 语句在 Oracle 中经历了以下的几个步骤。
- 语法检查：检查 SQL 拼写是否正确，如果不正确，Oracle 会报语法错误。
- 语义检查：检查 SQL 中的访问对象是否存在。比如我们在写 SELECT 语句的时候，列名写错了，系统就会提示错误。语法检查和语义检查的作用是保证 SQL 语句没有错误。
- 权限检查：看用户是否具备访问该数据的权限。
- 共享池检查：共享池（Shared Pool）是一块内存池，最主要的作用是缓存 SQL 语句和该语句的执行计划。Oracle 通过检查共享池是否存在 SQL 语句的执行计划，来判断进行软解析，还是硬解析。那软解析和硬解析又该怎么理解呢？在共享池中，Oracle 首先对 SQL 语句进行 Hash 运算，然后根据 Hash 值在库缓存（Library Cache）中查找，如果存在 SQL 语句的执行计划，就直接拿来执行，直接进入“执行器”的环节，这就是软解析。如果没有找到 SQL 语句和执行计划，Oracle 就需要创建解析树进行解析，生成执行计划，进入“优化器”这个步骤，这就是硬解析。
- 优化器：优化器中就是要进行硬解析，也就是决定怎么做，比如创建解析树，生成执行计划。
- 执行器：当有了解析树和执行计划之后，就知道了 SQL 该怎么被执行，这样就可以在执行器中执行语句了。
共享池是 Oracle 中的术语，包括了库缓存，数据字典缓冲区等。库缓存区，它主要缓存 SQL 语句和执行计划。而数据字典缓冲区存储的是 Oracle 中的对象定义，比如表、视图、索引等对象。当对 SQL 语句进行解析的时候，如果需要相关的数据，会从数据字典缓冲区中提取。
库缓存这一个步骤，决定了 SQL 语句是否需要进行硬解析。为了提升 SQL 的执行效率，我们应该尽量避免硬解析，因为在 SQL 的执行过程中，创建解析树，生成执行计划是很消耗资源的。通过绑定变量来减少硬解析，例如 SQL> select * from player where player_id = :player_id;，缺点是使用动态 SQL 的方式，因为参数不同，会导致 SQL 的执行效率不同，同时 SQL 优化也会比较困难。
MySQL
可以看到 MySQL 由三层构成：
- 连接层：客户端和服务器端建立连接，客户端发送 SQL 至服务器端；
- SQL 层：对 SQL 语句进行查询处理；
- 存储引擎层：与数据库文件打交道，负责数据的存储和读取。
能看到 SQL 语句在 MySQL 中的流程是：SQL 语句→缓存查询→解析器→优化器→执行器，但是 8.0 以后的版本不再支持查询缓存，而是直接执行解析器→优化器→执行器的流程
与 Oracle 不同的是，MySQL 的存储引擎采用了插件的形式，每个存储引擎都面向一种特定的数据库应用环境，下面是一些常见的存储引擎：
- InnoDB 存储引擎：它是 MySQL 5.5 版本之后默认的存储引擎，最大的特点是支持事务、行级锁定、外键约束等。
- MyISAM 存储引擎：在 MySQL 5.5 版本之前是默认的存储引擎，不支持事务，也不支持外键，最大的特点是速度快，占用资源少。
- Memory 存储引擎：使用系统内存作为存储介质，以便得到更快的响应速度。不过如果 mysqld 进程崩溃，则会导致所有的数据丢失，因此我们只有当数据是临时的情况下才使用 Memory 存储引擎。
- NDB 存储引擎：也叫做 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群环境，类似于 Oracle 的 RAC 集群。
- Archive 存储引擎：它有很好的压缩机制，用于文件归档，在请求写入时会进行压缩，所以也经常用来做仓库。
在 MySQL 中每个表的设计都可以采用不同的存储引擎，我们可以根据实际的数据处理需要来选择存储引擎，这也是 MySQL 的强大之处。
SQL 执行分析-MySQL
-- 查看 profiling 是否开启
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set, 1 warning (0.00 sec)
-- 输出为 0 表示未开启
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
-- 查询表数据
mysql> select * from student;
-- 查看当前会话产生的所有 profiles
mysql> show profiles;
+----------+------------+---------+
| Query_ID | Duration | Query |
+----------+------------+---------+
...
-- 获取上一次操作的执行时间
mysql> show profile;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000594 |
| Executing hook on transaction | 0.000046 |
| starting | 0.000020 |
| checking permissions | 0.000018 | -- 权限检查
| Opening tables | 0.019565 | -- 打开表
| init | 0.000033 | -- 初始化
| System lock | 0.000032 | -- 锁系统
| optimizing | 0.000017 | -- 优化查询
| statistics | 0.000027 |
| preparing | 0.000029 | -- 准备
| executing | 0.000045 | -- 执行
| end | 0.000016 |
| query end | 0.000016 |
| waiting for handler commit | 0.000031 |
| closing tables | 0.000022 |
| freeing items | 0.000075 |
| cleaning up | 0.000048 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)
-- 查询指定操作 id 的 profiles
mysql> show profile for query 2;