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 等。

减少io

比如说一份好的 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 子查询效率高。

比如最大生命值大于 7000 的法师英雄都有谁，那么你会怎么做呢？

SELECT * FROM heros WHERE hp_max >= 7000 AND role = '法师'

想要看下 7 天内的新增用户数有多少？首先我们需要获取现在的时间，使用 NOW() 函数即可，然后把它转化成天数，与用户的注册时间进行对比，小于 7 天的时间即是我们的筛选条件，最后就可以得到想要的数据了：

SELECT COUNT(*) as num FROM new_user WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7

这个专栏可以帮助你彻底理解 SQL，从而开启你的数据之旅，比如如何用 SQL 检索数据、过滤数据，如何使用 SQL 函数、子查询……

接触到不同的关系型数据库管理系统， SQL 在不同的数据库管理系统中是如何使用的。

数据是 SQL 的生命力，你不用担心面对干巴巴的 SQL 语言而感到无从下手，为了避免纸上谈兵，让你在工作中更容易、更熟练地使用 SQL，我特意为专栏建了一个王者荣耀数据库和 NBA 球员数据库。

1. 基础篇SQL 的语法非常简单，就像英语一样，但它的功能却很强大，可以帮助我们对数据实现索引、排序、分组等功能。但是这些命令在不同的数据库管理系统中的使用却有所差异，因此在专栏中，我不仅会重点讲解 SQL 本身的语法，还会讲解这些语法在类似 MySQL、Oracle、SQL Server 等不同的数据库管理系统中是如何使用的。
2. 进阶篇很多人在写 SQL 的时候都会遇到这样的问题：“同样都是用 SQL 查询数据，为什么我写的语句要比别人的慢？”事实上，就是因为 SQL 语法简单，导致很多人写的时候不拘小节，比如搞混了关键词的顺序，这就在无意中降低了 SQL 的执行效率。在这一部分，我会讲解在实际工作中，使用 SQL 经常会遇到的问题，以及如何使用工具进行分析，快速定位性能问题及解决方案。
3. 高级篇在大数据的时代，诞生了很多适用于不同场景的数据库管理系统，既有基于 SQL 的关系型数据库，比如 Oracle、MySQL、SQL Server、Access、WebSQL、SQLite 等，也有 NoSQL 非关系型数据库，比如 MongoDB、Redis 等。在这一部分中，我会讲一讲各种主流数据库管理系统的使用
4. 实战篇以上几个部分是在帮你梳理 SQL 的知识体系，但只有学会用 SQL 系统地进行项目实战，你才能真正地学以致用，让 SQL 为你的工作助力。在这一部分中，我会结合数据分析的项目，为你讲解 SQL 的实战案例，比如如何用 SQL 做数据清洗、数据集成等。

基础篇

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

sql的写法

1. 表名、表别名、字段名、字段别名等都小写；
2. SQL 保留字、函数名、绑定变量等都大写。

比如我想找主要角色定位是战士的英雄，以及他们的英雄名和最大生命值，就可以输入下面的语言：

SELECT name, hp_max FROM heros WHERE role_main = '战士'

Oracle 中的 SQL 是如何执行的

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 该怎么被执行，这样就可以在执行器中执行语句了。

共享池是 Oracle 中的术语，包括了库缓存，数据字典缓冲区等。我们上面已经讲到了库缓存区，它主要缓存 SQL 语句和执行计划。而数据字典缓冲区存储的是 Oracle 中的对象定义，比如表、视图、索引等对象。当对 SQL 语句进行解析的时候，如果需要相关的数据，会从数据字典缓冲区中提取。

因为在 SQL 的执行过程中，创建解析树，生成执行计划是很消耗资源的。

使用绑定变量，通过不同的变量取值来改变 SQL 的执行结果。这样做的好处是能提升软解析的可能性，不足之处在于可能会导致生成的执行计划不够优化，因此是否需要绑定变量还需要视情况而定。

比如下面的用player_id代替具体的数字，进行绑定变量

SQL> select * from player where player_id = :player_id;

MySQL 中的 SQL 是如何执行的

MySQL 是典型的 C/S 架构，即 Client/Server 架构，服务器端程序使用的 mysqld。整体的 MySQL 流程如下图所示：

MySQL 由三层组成：

1. 连接层：客户端和服务器端建立连接，客户端发送 SQL 至服务器端;
2. SQL 层：对 SQL 语句进行查询处理；
3. 存储引擎层：与数据库文件打交道，负责数据的存储和读取。

sql层的结构：

1. 查询缓存：Server 如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没有，就进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在 MySQL8.0 之后就抛弃了这个功能。
2. 解析器：在解析器中对 SQL 语句进行语法分析、语义分析。
3. 优化器：在优化器中会确定 SQL 语句的执行路径，比如是根据全表检索，还是根据索引来检索等。
4. 执行器：在执行之前需要判断该用户是否具备权限，如果具备权限就执行 SQL 查询并返回结果。在 MySQL8.0 以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。

MySQL中查看sql执行时间

看下 profiling 是否开启，profiling=0 代表关闭，profiling=1 打开;开启它可以让 MySQL 收集在 SQL 执行时所使用的资源情况，命令如下：

mysql> select @@profiling;
mysql> set profiling=1;

然后可以执行select查询语句，再用show profiles;或者show profiles for query x;查看结果

✳DDL

1.对数据库进行定义

CREATE DATABASE nba; // 创建一个名为nba的数据库DROP DATABASE nba; // 删除一个名为nba的数据库

2.对数据表进行定义

创建表结构的语法是这样的：

DROP TABLE IF EXISTS `player`;
CREATE TABLE `player`  (
    `player_id` int(11) NOT NULL AUTO_INCREMENT,
    `team_id` int(11) NOT NULL,
    `player_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `height` float(3, 2) NULL DEFAULT 0.00,
    PRIMARY KEY (`player_id`) USING BTREE,
    UNIQUE INDEX `player_name`(`player_name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

语句最后以分号（;）作为结束符，最后一个字段的定义结束后没有逗号。排序规则是utf8_general_ci，代表对大小写不敏感，如果设置为utf8_bin，代表对大小写敏感。

修改表结构：

ALTER TABLE player ADD (age int(11));  
ALTER TABLE player RENAME COLUMN age to player_age;
ALTER TABLE player MODIFY (player_age float(3,1));
ALTER TABLE player DROP COLUMN player_age;

数据表的常见约束

对字段进行约束，约束的目的在于数据的准确性和一致性。

主键约束

主键起的作用是唯一标识一条记录，不能重复，不能为空，即 UNIQUE+NOT NULL。一个数据表的主键只能有一个。主键可以是一个字段，也可以由多个字段复合组成。在上面的例子中，我们就把 player_id 设置为了主键。

外键约束

外键确保了表与表之间引用的完整性。一个表中的外键对应另一张表的主键。外键可以是重复的，也可以为空。比如 player_id 在 player 表中是主键，如果你想设置一个球员比分表即 player_score，就可以在 player_score 中设置 player_id 为外键，关联到 player 表中。

字段约束

唯一性约束。

唯一性约束表明了字段在表中的数值是唯一的，即使我们已经有了主键，还可以对其他字段进行唯一性约束。比如我们在 player 表中给 player_name 设置唯一性约束，就表明任何两个球员的姓名不能相同。需要注意的是，唯一性约束和普通索引（NORMAL INDEX）之间是有区别的。唯一性约束相当于创建了一个约束和普通索引，目的是保证字段的正确性，而普通索引只是提升数据检索的速度，并不对字段的唯一性进行约束。

NOT NULL 约束

对字段定义了 NOT NULL，即表明该字段不应为空，必须有取值。

DEFAULT，表明了字段的默认值。若这个字段在插入数据时没有取值，就设置为默认值。比如我们将身高 height 字段的取值默认设置为 0.00，即DEFAULT 0.00。

CHECK 约束，用来检查特定字段取值范围的有效性，CHECK 约束的结果不能为 FALSE，比如我们可以对身高 height 的数值进行 CHECK 约束，必须≥0，且＜3，即CHECK(height>=0 AND height<3)。

✳SELECT用法

对多个列进行检索，比如

SELECT name, hp_max, mp_max, attack_max, defense_max FROM heros

但多个数据探索的时候，有SELECT *就不需要写很长的 SELECT 语句了。但是避免在生产上使用SELECT *

SELECT name AS n, hp_max AS hm, mp_max AS mm, attack_max AS am, defense_max AS dm FROM heros

进阶篇

高级篇

实战篇