极客时间《SQL必知必会》学习笔记

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

• 尽管技术人员或多或少地会使用 SQL，但不同的人编写出来的 SQL 的效率是不同的，比如说一份好的 SQL 执行计划就会尽量减少 I/O 操作，因为 I/O 是 DBMS （数据库管理系统，Database Management System）最容易出现瓶颈的地方，可以说数据库操作中有大量的时间都花在了 I/O 上。还需要考虑如何降低 CPU 的计算量，在 SQL 语句中使用 GROUP BY、ORDER BY 等这些语句会消耗大量的 CPU 计算资源，因此我们需要从全局出发，不仅需要考虑数据库的 I/O 性能，还需要考虑 CPU 计算、内存使用情况等。

• IN和EXISTS查询举例：

 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 子查询效率高。

       总结： 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in；反之，如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。其实，区分in和exists主要是看驱动顺序的改变(这是性能变化的关键)，如果是exists，那么以外层表为驱动表，先被访问；如果是in，先执行子查询，会以驱动表的快速返回为目标，就会考虑到索引及结果集的关系了 ，另外in查询时不对null进行处理。

• 相关名词：

  • OLTP（联机事务处理过程）

  • OLAP（联机分析处理过程）

  • RDBMS（关系型数据库管理系统）

  • 基于 SQL 的关系型数据库，比如 Oracle、MySQL、SQL Server、Access、WebSQL、SQLite 等

  • 基于NoSQL 非关系型数据库，比如 MongoDB、Redis 等。

01丨了解SQL：一门半衰期很长的语言

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

  • DDL，英文叫做 Data Definition Language，也就是数据定义语言，它用来定义我们的数据库对象，包括数据库、数据表和列。通过使用 DDL，我们可以创建，删除和修改数据库和表结构。

  • DML，英文叫做 Data Manipulation Language，数据操作语言，我们用它操作和数据库相关的记录，比如增加、删除、修改数据表中的记录。

  • DCL，英文叫做 Data Control Language，数据控制语言，我们用它来定义访问权限和安全级别。

  • DQL，英文叫做 Data Query Language，数据查询语言，我们用它查询想要的记录，它是 SQL 语言的重中之重。在实际的业务中，我们绝大多数情况下都是在和查询打交道，因此学会编写正确且高效的查询语句，是学习的重点。

  • 总结：

    • 1.DDL操作：CREATE（创建表）、ALTER（表中增加字段）、DROP（删除字段）和TRUNCATE（清空表数据不删除表结构）

    • 2.DML操作：INSERT、UPDATE、DELETE 对元组操作

    • 3.DCL操作：

      • 1).创建用户 CREATE USER 用户名@地址 IDENTIFIED BY '密码';

      • 2).给用户授权 GRANT 权限1, … , 权限n ON 数据库.对象 TO 用户名;

      • 3).撤销授权 REVOKE权限1, … , 权限n ON 数据库.对象 FORM 用户名;

      • 4).查看权限 SHOW GRANTS FOR 用户名;

      • 5).删除用户 DROP USER 用户名;

    • 4.DQL操作：SELECT

• ER 图（Entity Relationship Diagram）：实体 - 关系图。它是我们用来描述现实世界的概念模型，在这个模型中有 3 个要素：实体、属性、关系。实体就是我们要管理的对象，属性是标识每个实体的属性，关系则是对象之间的关系。
• 你能看到很多 SQL 语句的大小写不统一，虽然大小写不会影响 SQL 的执行，不过我还是推荐你采用统一的书写规范，因为好的代码规范是提高效率的关键。
  • 表名、表别名、字段名、字段别名等都小写，在数据表的字段名推荐采用下划线命名，如 role_main；
  • SQL 保留字、函数名、绑定变量等都大写；
  • 总之，代码规范必须要有，但大小写影响不大，有语法高亮，附SQL Style Guide：https://github.com/mattm/sql-style-guide。

02丨DBMS的前世今生

• DB：DataBase，数据库。数据库是存储数据的集合，你可以把它理解为多个数据表。
• DBMS：DataBase Management System，数据库管理系统，实际上它可以对多个数据库进行管理，所以你可以理解为 DBMS = 多个数据库（DB） + 管理程序。
• DBS：DataBase System，数据库系统。它是更大的概念，包括了数据库、数据库管理系统以及数据库管理人员 DBA。
• 注意：虽然我们有时候把 Oracle、MySQL 等称之为数据库，但确切讲，它们应该是数据库管理系统，即 DBMS。
• 2019 年 5 月 DB-Engines 公布的 DBMS 的排名（DB-Engines Ranking - popularity ranking of database management systems）：

       从排名中我们能看出来，关系型数据库绝对是 DBMS 的主流，其中使用最多的 DBMS 分别是 Oracle、MySQL 和 SQL Server。

• 关系型数据库（RDBMS）就是建立在关系模型基础上的数据库，SQL 就是关系型数据库的查询语言。
• 相比于 SQL，NoSQL 泛指非关系型数据库，包括了榜单上的键值型数据库、文档型数据库、搜索引擎和列存储等，除此以外还包括图形数据库。

       键值型数据库：通过 Key-Value 键值的方式来存储数据，其中 Key 和 Value 可以是简单的对象，也可以是复杂的对象。Key 作为唯一的标识符，优点是查找速度快，在这方面明显优于关系型数据库，同时缺点也很明显，它无法像关系型数据库一样自由使用条件过滤（比如 WHERE），如果你不知道去哪里找数据，就要遍历所有的键，这就会消耗大量的计算。键值型数据库典型的使用场景是作为内容缓存。Redis 是最流行的键值型数据库。

       文档型数据库：用来管理文档，在数据库中文档作为处理信息的基本单位，一个文档就相当于一条记录，MongoDB 是最流行的文档型数据库。

       搜索引擎：也是数据库检索中的重要应用，常见的全文搜索引擎有 Elasticsearch、Splunk 和 Solr。虽然关系型数据库采用了索引提升检索效率，但是针对全文索引效率却较低。搜索引擎的优势在于采用了全文搜索的技术，核心原理是“倒排索引”。

       列式数据库：是相对于行式存储的数据库，Oracle、MySQL、SQL Server 等数据库都是采用的行式存储（Row-based），而列式数据库是将数据按照列存储到数据库中，这样做的好处是可以大量降低系统的 I/O，适合于分布式文件系统，不足在于功能相对有限。

       图形数据库：利用了图这种数据结构存储了实体（对象）之间的关系。最典型的例子就是社交网络中人与人的关系，数据模型主要是以节点和边（关系）来实现，特点在于能高效地解决复杂的关系问题。

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

        由于 SQL 一直称霸 DBMS，因此许多人在思考是否有一种数据库技术能远离 SQL，于是 NoSQL 诞生了，但是随着发展却发现越来越离不开 SQL。到目前为止 NoSQL 阵营中的 DBMS 都会有实现类似 SQL 的功能。下面是“NoSQL”这个名词在不同时期的诠释，从这些释义的变化中可以看出 NoSQL 功能的演变：

        1970：NoSQL = We have no SQL

        1980：NoSQL = Know SQL

        2000：NoSQL = No SQL!

        2005：NoSQL = Not only SQL

        2013：NoSQL = No, SQL!

       1979 年，Oracle 2 诞生，它是第一个商用的 RDBMS（关系型数据库管理系统），随后被卖给了军方客户。随着 Oracle 软件的名气越来越大，公司也改叫 Oracle 公司。20 世纪 90 年代，Oracle 的创始人埃里森成为继比尔·盖茨之后第二富有的人，可以说 IBM 缔造了两个帝国，一个是软件业的霸主微软，另一个是企业软件市场的霸主 Oracle。如今 Oracle 的年收入达到了 400 亿美金，足以证明商用数据库软件的价值。从这点我们也能看出，如果选择了一个大的赛道，就要尽早商业化，占据大型企业客户完全可以创建巨大的商业价值，也足以证明一个软件企业不需要靠卖硬件也可以挣到很多钱。

       MySQL 是 1995 年诞生的开源数据库管理系统，因为免费开源的特性，得到了开发者的喜爱，用户量迅速增长，成为开源数据库的 No.1。但在发展过程中，MySQL 先后两次被易手，先是在 2008 年被 SUN 收购，然后在 2010 年 SUN 被 Oracle 收购，于是 Oracle 同时拥有了 MySQL 的管理权，至此 Oracle 在数据库领域中成为绝对的领导者。从这里我们也能看到，虽然 MySQL 是免费的产品，但是使用人数多，就足以证明巨大的用户价值。一个有巨大用户价值的产品，即使没有直接的商业价值，但作为基础设施也会被商业巨头看上。

        不过在 Oracle 收购 MySQL 的同时，MySQL 的创造者担心 MySQL 有闭源的风险，因此创建了 MySQL 的分支项目 MariaDB，MariaDB 在绝大部分情况下都是与 MySQL 兼容的，并且增加了许多新的特性，比如支持更多的存储引擎类型。许多企业也由原来的 MySQL 纷纷转向了 MariaDB。

        SQL Server 是微软开发的商业数据库，诞生于 1989 年。实际上微软还推出了 Access 数据库，它是一种桌面数据库，同时具备后台存储和前台界面开发的功能，更加轻量级，适合小型的应用场景。因为后台的存储空间有限，一般只有 2G，Access 的优势在于可以在前台便捷地进行界面开发。而 SQL Server 是大型数据库，用于后台的存储和查询，不具备界面开发的功能。从这里我们也能看出，即使 SQL 语言是通用的，但是为了满足不同用户的使用场景，会存在多个 DBMS。比如 Oracle 更适合大型跨国企业的使用，因为他们对费用不敏感，但是对性能要求以及安全性有更高的要求，而 MySQL 更受到许多互联网公司，尤其是早期创业公司的青睐。

        今天我们简单梳理了 DBMS 的发展。1974 年，SEQUEL 论文发表，1979 年，第一个商用关系型数据库 Oracle 2 诞生，1995 年，MySQL 开源数据库诞生，如今，NoSQL 得到了发展，并且围绕 SQL 标准展开的 DBMS 竞赛从来没有停止过。在这段发展史中，既有 SQL 阵营，又有 NoSQL 阵营，既有商业数据库软件，又有开源产品，在不同的应用场景下，同一家公司也会有不同的 DBMS 布局。

        如果说不同的 DBMS 代表了不同公司的利益，那么作为使用者的我们更应该注重的是这些 DBMS 的使用场景。比如 Oracle 作为市场占有率最高的商用数据库软件，适合大型的跨国企业，而针对轻量级的桌面数据库，我们采用 Access 就可以了。对于免费开源的产品来说，可以选用 MySQL 或者 MariaDB。同时在 NoSQL 阵营中，我们也需要了解键值型、文档型、搜索引擎、列式数据库和图形数据库的区别。

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

3.1 SQL在Oracle中执行过程

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

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

        库缓存这一个步骤，决定了 SQL 语句是否需要进行硬解析。为了提升 SQL 的执行效率，我们应该尽量避免硬解析，因为在 SQL 的执行过程中，创建解析树，生成执行计划是很消耗资源的。

        如何避免硬解析，尽量使用软解析呢？在 Oracle 中，绑定变量是它的一大特色。绑定变量就是在 SQL 语句中使用变量，通过不同的变量取值来改变 SQL 的执行结果。这样做的好处是减少硬解析，减少 Oracle 的解析工作量，能提升软解析的可能性，不足之处在于使用动态 SQL 的方式，因为参数不同，会导致 SQL 的执行效率不同，同时 SQL 优化也会比较困难。可能会导致生成的执行计划不够优化，因此是否需要绑定变量还需要视情况而定。具体可参考：oracle绑定变量使用方法总结_oracle managedaccess 绑定变量-CSDN博客

3.2 SQL在MySQL中执行过程

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

MySQL主要由三层组成：

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

其中，SQL 层与数据库文件的存储方式无关， SQL 层的结构如下：

        注：”找到“那个箭头的朝向问题，缓存查询后，如果找到了就直接输出结果。如果没有找到就执行：解析器=>优化器=>执行器的流程，然后可以将结果存储到 缓存中方便后续进行查询。所以箭头回向指的是这这样。

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

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

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

执行器：在执行之前需要判断该用户是否具备权限，如果具备权限就执行 SQL 查询并返回结果。在 MySQL8.0 以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。

        可以看到 SQL 语句在 MySQL 中的流程是：SQL 语句→缓存查询→解析器→优化器→执行器，发现MySQL 和 Oracle 执行 SQL 的原理是一样的。

        与 Oracle 不同的是，MySQL 的存储引擎采用了插件的形式，每个存储引擎都面向一种特定的数据库应用环境。同时开源的 MySQL 还允许开发人员设置自己的存储引擎。

下面是一些常见的存储引擎：

• InnoDB 存储引擎：它是 MySQL 5.5 版本之后默认的存储引擎，最大的特点是支持事务、行级锁定、外键约束等。
• MyISAM 存储引擎：在 MySQL 5.5 版本之前是默认的存储引擎，不支持事务，也不支持外键，最大的特点是速度快，占用资源少。
• Memory 存储引擎：使用系统内存作为存储介质，以便得到更快的响应速度。不过如果 mysqld 进程崩溃，则会导致所有的数据丢失，因此我们只有当数据是临时的情况下才使用 Memory 存储引擎。
• NDB 存储引擎：也叫做 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群环境，类似于 Oracle 的 RAC 集群。
• Archive 存储引擎：它有很好的压缩机制，用于文件归档，在请求写入时会进行压缩，所以也经常用来做仓库。

        需要注意的是，数据库的设计在于表的设计，而在 MySQL 中每个表的设计都可以采用不同的存储引擎，我们可以根据实际的数据处理需要来选择存储引擎，这也是 MySQL 的强大之处。

数据库管理系统也是一种软件

        在上面初步了解了 SQL 语句在 Oracle 和 MySQL 中的执行流程，实际上完整的 Oracle 和 MySQL 结构图要复杂得多：

不同的 DBMS 的 SQL 的执行原理是相通的，只是在不同的软件中，各有各的实现路径。

在 MySQL 中对一条 SQL 语句的执行时间进行分析

-- 查询profiling，profiling=0表示关闭，=1表示开启
select @@profiling;

-- profiling=1
set profiling=1;

-- 执行一条查询SQL
select * from wucai.heros;

-- 查看当前回话所产生的所有profiles
show profiles;

-- 获取上一次查询的执行时间
show profile;

-- 指定query id查询
show profile for query 2;

-- 查询MySQL版本
select version();

注意：

        在 8.0 版本之后，MySQL 不再支持缓存的查询，原因我在上文已经说过。一旦数据表有更新，缓存都将清空，因此只有数据表是静态的时候，或者数据表很少发生变化时，使用缓存查询才有价值，否则如果数据表经常更新，反而增加了 SQL 的查询时间。

总结

        不同的 RDBMS 之间有相同的地方，也有不同的地方。相同的地方在于 Oracle 和 MySQL 都是通过解析器→优化器→执行器这样的流程来执行 SQL 的。但 Oracle 和 MySQL 在进行 SQL 的查询上面有软件实现层面的差异。Oracle 提出了共享池的概念，通过共享池来判断是进行软解析，还是硬解析。而在 MySQL 中，8.0 以后的版本不再支持查询缓存，而是直接执行解析器→优化器→执行器的流程，这一点从 MySQL 中的 show profile 里也能看到。同时 MySQL 的一大特色就是提供了各种存储引擎以供选择，不同的存储引擎有各自的使用场景，我们可以针对每张表选择适合的存储引擎。

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

建表语句

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

建表举例：

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;

注意：

• 建表语句最后以分号（;）作为结束符，最后一个字段的定义结束后没有逗号；
• 字段数据类型定义的是数据最大的有效显示长度，与类型包含的数值范围大小无关。
• 建表时，对数据表和字段名称都加上了反引号，这是为了避免它们的名称与 MySQL 保留字段相同。

• player_name 字段的字符编码是 utf8，排序规则是utf8_general_ci，代表对大小写不敏感，如果设置为utf8_bin，代表对大小写敏感。

• 在 DDL 中使用PRIMARY KEY进行规定，同时索引方法采用 BTREE。

• 唯一索引和普通索引的区别在于它对字段进行了唯一性的约束。在索引方式上，你可以选择BTREE或者HASH。

• 整个数据表的存储规则采用 InnoDB，它是 MySQL5.5 版本之后默认的存储引擎。

• 将字符编码设置为 utf8，排序规则为utf8_general_ci，行格式为Dynamic。

修改表结构

-- 添加字段
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)。

设计数据标的原则（三少一多）

1. 数据表的个数越少越好

        RDBMS 的核心在于对实体和联系的定义，也就是 E-R 图（Entity Relationship Diagram），数据表越少，证明实体和联系设计得越简洁，既方便理解又方便操作。

2. 数据表中的字段个数越少越好

        字段个数越多，数据冗余的可能性越大。设置字段个数少的前提是各个字段相互独立，而不是某个字段的取值可以由其他字段计算出来。当然字段个数少是相对的，我们通常会在数据冗余和检索效率中进行平衡。

3. 数据表中联合主键的字段个数越少越好

        设置主键是为了确定唯一性，当一个字段无法确定唯一性的时候，就需要采用联合主键的方式（也就是用多个字段来定义一个主键）。联合主键中的字段越多，占用的索引空间越大，不仅会加大理解难度，还会增加运行时间和索引空间，因此联合主键的字段个数越少越好。

4. 使用主键和外键越多越好

        数据库的设计实际上就是定义各种表，以及各种字段之间的关系。这些关系越多，证明这些实体之间的冗余度越低，利用度越高。这样做的好处在于不仅保证了数据表之间的独立性，还能提升相互之间的关联使用率。

        你应该能看出来“三少一多”原则的核心就是简单可复用。简单指的是用更少的表、更少的字段、更少的联合主键字段来完成数据表的设计。可复用则是通过主键、外键的使用来增强数据表之间的复用率。因为一个主键可以理解是一张表的代表。键设计得越多，证明它们之间的利用率越高。

05丨检索数据：你还在SELECT * 么？

查询常数

        SELECT 查询还可以对常数进行查询，就是在 SELECT 查询结果中增加一列固定的常数列。这列的取值是我们指定的，而不是从数据表中动态取出的。SQL 中的 SELECT 语法的确提供了这个功能，一般来说我们只从一个表中查询数据，通常不需要增加一个固定的常数列，但如果我们想整合不同的数据源，用常数列作为这个表的标记，就需要查询常数。

举例：

• SELECT '王者荣耀' as platform, name FROM heros
• SELECT 123 as platform, name FROM heros

注意：

• 如果常数是个字符串，需要用''括起来，单引号说明引号中的字符串是个常数；
• 如果常数是个数字，就可以直接写数字，不需要单引号

去除重复行

使用的关键字是 DISTINCT。

举例： SELECT DISTINCT attack_range FROM heros

注意：

• DISTINCT 需要放到所有列名的前面，如果写成SELECT name, DISTINCT attack_range FROM heros会报错。
• DISTINCT 其实是对后面所有列名的组合进行去重，你能看到最后的结果是 69 条，因为这 69 个英雄名称不同，都有攻击范围（attack_range）这个属性值。如果你想要看都有哪些不同的攻击范围（attack_range），只需要写DISTINCT attack_range即可，后面不需要再加其他的列名了。

如何排序检索数据

使用 ORDER BY 子句有以下几个点需要掌握：

• 排序的列名：ORDER BY 后面可以有一个或多个列名，如果是多个列名进行排序，会按照后面第一个列先进行排序，当第一列的值相同的时候，再按照第二列进行排序，以此类推。
• 排序的顺序：ORDER BY 后面可以注明排序规则，ASC 代表递增排序，DESC 代表递减排序。如果没有注明排序规则，默认情况下是按照 ASC 递增排序。我们很容易理解 ORDER BY 对数值类型字段的排序规则，但如果排序字段类型为文本数据，就需要参考数据库的设置方式了，这样才能判断 A 是在 B 之前，还是在 B 之后。比如使用 MySQL 在创建字段的时候设置为 BINARY 属性，就代表区分大小写。
• 非选择列排序：ORDER BY 可以使用非选择列进行排序，所以即使在 SELECT 后面没有这个列名，你同样可以放到 ORDER BY 后面进行排序。
• ORDER BY 的位置：ORDER BY 通常位于 SELECT 语句的最后一条子句，否则会报错。

约束返回结果的数量

        约束返回结果的数量，使用 LIMIT 关键字。

        有一点需要注意，约束返回结果的数量，在不同的 DBMS 中使用的关键字可能不同。在 MySQL、PostgreSQL、MariaDB 和 SQLite 中使用 LIMIT 关键字，而且需要放到 SELECT 语句的最后面。如果是 SQL Server 和 Access，需要使用 TOP 关键字，比如：

        SELECT TOP 5 name, hp_max FROM heros ORDER BY hp_max DESC

如果是 DB2，使用FETCH FIRST 5 ROWS ONLY这样的关键字：

SELECT name, hp_max FROM heros ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY

如果是 Oracle，你需要基于 ROWNUM 来统计行数：

SELECT name, hp_max FROM heros WHERE ROWNUM <=5 ORDER BY hp_max DESC

        需要说明的是，这条语句是先取出来前 5 条数据行，然后再按照 hp_max 从高到低的顺序进行排序。但这样产生的结果和上述方法的并不一样。我会在后面讲到子查询，你可以使用SELECT name, hp_max FROM (SELECT name, hp_max FROM heros ORDER BY hp_max) WHERE ROWNUM <=5得到与上述方法一致的结果。

        约束返回结果的数量可以减少数据表的网络传输量，也可以提升查询效率。如果我们知道返回结果只有 1 条，就可以使用LIMIT 1，告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回。

SELECT 的执行顺序

关键字的顺序是不能颠倒的：

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...

SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）：

FROM > WHERE > GROUP BY > HAVING > SELECT的字段 > DISTINCT > ORDER BY > LIMIT

比如你写了一个 SQL 语句，那么它的关键字顺序和执行顺序是下面这样的：

SELECT DISTINCT player_id, player_name, count(*) as num #顺序5
FROM player JOIN team ON player.team_id = team.team_id #顺序1
WHERE height > 1.80 #顺序2
GROUP BY player.team_id #顺序3
HAVING num > 2 #顺序4
ORDER BY num DESC #顺序6
LIMIT 2 #顺序7

        在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。

        在我们的日常工作中，很多人都可以写出 SELECT 语句，但是执行的效率却相差很大。产生这种情况的原因主要有两个，一个是习惯的培养，比如大部分初学者会经常使用SELECT *，而好的习惯则是只查询所需要的列；另一个对 SQL 查询的执行顺序及查询效率的关注，比如当你知道只有 1 条记录的时候，就可以使用LIMIT 1来进行约束，从而提升查询效率。

关于COUNT()的效率是一个很好的问题，欢迎探讨：

        在MySQL InnoDB存储引擎中，COUNT(*)和COUNT(1)都是对的所有结果进行的COUNT。如果有WHERE子句，则是对所有符合筛选条件的数据行进行统计。如果没有WHERE子句，则是对数据表的数据行数进行统计。

        因此COUNT(*)和COUNT(1)本质上没有区别，执行的复杂度都是O(N)，也就是采用全表扫描，进行循环+计数的方式进行统计。

        如果是MySQL MyISAM存储引擎，统计数据表的行数只需要O(1)复杂度，这是因为每张MyISAM的数据表都有一个meta信息有存储了row_count值。而一致性由表级锁来保证。而InnoDB支持事务，采用行级锁和MVCC机制，所以无法像MyISAM一样，只维护一个row_count变量。因此就需要采用扫描全表，进行循环+计数的方式来完成统计。

        需要注意的是，在实际执行中COUNT(*)和COUNT(1)执行时间可能略有差别，不过你还是可以把这两个在执行效率上看成是相等的。

        另外在InnoDB引擎中，如果是采用COUNT(*)和COUNT(1)来统计数据行数，要尽量采用二级索引。

        因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引（非聚簇索引）。

        对于查找具体的行来说，采用主键索引效率更高。而对于COUNT(*)和COUNT(1)这种，不需要查找具体的行，只是统计行数来说，系统会自动采用占用空间更小的二级索引来进行统计。 如果有多个二级索引的时候，会使用key_len小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计。

优化总结：

        1、一般情况下：COUNT(*) = COUNT(1) > COUNT(字段)。 所以尽量使用COUNT(*)，当然如果你要统计的是就是某个字段的非空数据行数，那另当别论。毕竟执行效率比较的前提是要结果一样才行。

        2、如果要统计COUNT(*)，尽量在数据表上建立二级索引，系统会自动采用key_len小的二级索引进行扫描，这样当我们使用SELECT COUNT(*)的时候效率就会提升，有时候提升几倍甚至更高都是有可能的。

06丨数据过滤：SQL数据过滤都有哪些方法？

比较运算符

逻辑运算符

注意：

• 查看使用的 DBMS 是否支持，不同的 DBMS 支持的运算符可能是不同的；

• 一般来说 () 优先级最高，其次优先级是 AND，然后是 OR。

使用通配符进行过滤

需要使用到 LIKE 操作符：

• 如果我们想要匹配任意字符串出现的任意次数，需要使用（%）通配符。
• 如果我们想要匹配单个字符，就需要使用下划线 (_) 通配符。（%）和（_）的区别在于，（%）代表零个或多个字符，而（_）只代表一个字符。

        不过在实际操作过程中，我还是建议你尽量少用通配符，因为它需要消耗数据库更长的时间来进行匹配。即使你对 LIKE 检索的字段进行了索引，索引的价值也可能会失效。如果要让索引生效，那么 LIKE 后面就不能以（%）开头，比如使用LIKE '%太%'或LIKE '%太'的时候就会对全表进行扫描。如果使用LIKE '太%'，同时检索的字段进行了索引的时候，则不会进行全表扫描。

总结

        比较运算符是对数值进行比较，不同的 DBMS 支持的比较运算符可能不同，你需要事先查阅相应的 DBMS 文档。逻辑运算符可以让我们同时使用多个 WHERE 子句，你需要注意的是 AND 和 OR 运算符的执行顺序。通配符可以让我们对文本类型的字段进行模糊查询，不过检索的代价也是很高的，通常都需要用到全表扫描，所以效率很低。只有当 LIKE 语句后面不用通配符，并且对字段进行索引的时候才不会对全表进行扫描。

        你可能认为学习 SQL 并不难，掌握这些语法就可以对数据进行筛选查询。但实际工作中不同人写的 SQL 语句的查询效率差别很大，保持高效率的一个很重要的原因，就是要避免全表扫描，所以我们会考虑在 WHERE 及 ORDER BY 涉及到的列上增加索引。

关于ORDER BY字段是否增加索引

        在MySQL中，支持两种排序方式：FileSort和Index排序。Index排序的效率更高， Index排序：索引可以保证数据的有序性，因此不需要再进行排序。 FileSort排序：一般在内存中进行排序，占用CPU较多。如果待排结果较大，会产生临时文件I/O到磁盘进行排序，效率较低。

        所以使用ORDER BY子句时，应该尽量使用Index排序，避免使用FileSort排序。 当然具体优化器是否采用索引进行排序，你可以使用explain来进行执行计划的查看。

优化建议：

        1、SQL中，可以在WHERE子句和ORDER BY子句中使用索引，目的是在WHERE子句中避免全表扫描，ORDER BY子句避免使用FileSort排序。 当然，某些情况下全表扫描，或者FileSort排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。 一般情况下，优化器会帮我们进行更好的选择，当然我们也需要建立合理的索引。

        2、尽量Using Index完成ORDER BY排序。 如果WHERE和ORDER BY相同列就使用单索引列；如果不同使用联合索引。

        3、无法Using Index时，对FileSort方式进行调优。

07丨什么是SQL函数？为什么使用SQL函数可能会带来问题？

来源：07丨什么是SQL函数？为什么使用SQL函数可能会带来问题？-SQL必知必会-极客时间

7.1 什么是 SQL 函数？

把经常使用的代码封装起来，需要的时候直接调用。使用函数能提高代码效率和可维护性。

7.2 常用的SQL函数

算数函数

对数值类型的字段进行算数运算。

字符串函数

常用的字符串函数操作包括了字符串拼接，大小写转换，求长度以及字符串替换和截取等。

注意：SUBSTRING('fabcd', 1, 3)截取字符串时，字符串第一个字符的下标是1，第3个参数是字符串截取的长度而不是字符串结束截取的位置。

日期函数

日期函数是对数据表中的日期进行处理，常用的函数包括：

SELECT EXTRACT(YEAR FROM '2019-04-03')，运行结果为 2019。

SELECT DATE('2019-04-01 12:00:05')，运行结果为 2019-04-01。

注意：DATE 日期格式必须是 yyyy-mm-dd 的形式。如果要进行日期比较，就要使用 DATE 函数，不要直接使用日期与字符串进行比较。

转换函数

转换函数可以转换数据之间的类型，常用的函数如下表所示：

SELECT CAST(123.123 AS INT)，运行结果会报错。

SELECT CAST(123.123 AS DECIMAL(8,2))，运行结果为 123.12。

SELECT COALESCE(null,1,2)，运行结果为 1。

        CAST 函数在转换数据类型的时候，不会四舍五入，如果原数值有小数，那么转换为整数类型的时候就会报错。不过你可以指定转化的小数类型，在 MySQL 和 SQL Server 中，你可以用DECIMAL(a,b)来指定，其中 a 代表整数部分和小数部分加起来最大的位数，b 代表小数位数，比如DECIMAL(8,2)代表的是精度为 8 位（整数加小数位数最多为 8 位），小数位数为 2 位的数据类型。所以SELECT CAST(123.123 AS DECIMAL(8,2))的转换结果为 123.12。

附王者荣耀英雄数据库：GitHub - cystanford/sql_heros_data: SQL课程-王者荣耀heros数据表

为什么使用 SQL 函数会带来问题？

        如果你学习过编程语言，就会知道语言是有不同版本的，比如 Python 会有 2.7 版本和 3.x 版本，不过它们之间的函数差异不大，也就在 10% 左右。但我们在使用 SQL 语言的时候，不是直接和这门语言打交道，而是通过它使用不同的数据库软件，即 DBMS。DBMS 之间的差异性很大，远大于同一个语言不同版本之间的差异。实际上，只有很少的函数是被 DBMS 同时支持的。比如，大多数 DBMS 使用（||）或者（+）来做拼接符，而在 MySQL 中的字符串拼接函数为Concat()。在 MySQL 和 Oracle 中都有这个函数，但是在这两个 DBMS 中作用却不一样，CONCAT函数在 MySQL 中可以连接多个字符串，而在 Oracle 中CONCAT函数只能连接两个字符串，如果要连接多个字符串就需要用（||）连字符来解决。大部分 DBMS 会有自己特定的函数，这就意味着采用 SQL 函数的代码可移植性是很差的，因此在使用函数的时候需要特别注意。

关于大小写的规范

        实际上在 SQL 中，关键字和函数名是不用区分字母大小写的，比如 SELECT、WHERE、ORDER、GROUP BY 等关键字，以及 ABS、MOD、ROUND、MAX 等函数名。

        不过在 SQL 中，你还是要确定大小写的规范，因为在 Linux 和 Windows 环境下，你可能会遇到不同的大小写问题。比如 MySQL 在 Linux 的环境下，数据库名、表名、变量名是严格区分大小写的，而字段名是忽略大小写的。而 MySQL 在 Windows 的环境下全部不区分大小写。这就意味着如果你的变量名命名规范没有统一，就可能产生错误。这里有一个有关命名规范的建议：

• 关键字和函数名称全部大写；
• 数据库名、表名、字段名称全部小写；
• SQL 语句必须以分号结尾。

        虽然关键字和函数名称在 SQL 中不区分大小写，也就是如果小写的话同样可以执行，但是数据库名、表名和字段名在 Linux MySQL 环境下是区分大小写的，因此建议你统一这些字段的命名规则，比如全部采用小写的方式。同时将关键词和函数名称全部大写，以便于区分数据库名、表名、字段名。

08丨什么是SQL的聚集函数，如何利用它们汇总表的数据？

来源：08丨什么是SQL的聚集函数，如何利用它们汇总表的数据？-SQL必知必会-极客时间

待完善