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

关于 SQL 大小写的问题，总结了下面两点：

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

eg:

SELECT name, hp_max FROM heros WHERE role_main = ‘战士’

SQL如何被执行

在Oracle中

  1. 语法检查：检查 SQL 拼写是否正确，如果不正确，Oracle 会报语法错误。

  2. 语义检查：检查 SQL 中的访问对象是否存在。比如我们在写 SELECT 语句的时候，列名写错了，系统就会提示错误。语法检查和语义 检查的作用是保证 SQL 语句没有错误。

  3. 权限检查：看用户是否具备访问该数据的权限。

  4. 共享池检查：共享池（Shared Pool）是一块内存池，最主要的作用是缓存 SQL 语句和该语句的执行计划。Oracle 通过检查共享池是否存在 SQL 语句的执行计划，来判断进行软解析，还是硬解析。

  5. 在共享池中，Oracle 首先对 SQL 语句进行 Hash 运算，然后根据 Hash 值在库缓存（Library Cache）中查找，如果存在 SQL 语句的执行计划，就直接拿来执行，直接进入“执行器”的环节，这就是软解析。

  6. 如果没有找到 SQL 语句和执行计划，Oracle 就需要创建解析树进行解析，生成执行计划，进入“优化器”这个步骤，这就是硬解析。

共享池是 Oracle 中的术语，包括了库缓存，数据字典缓冲区等。

  1. 库缓存区，它主要缓存 SQL 语句和执行计划。

  2. 数据字典缓冲区存储的是 Oracle 中的对象定义，比如表、视图、索引等对象。

    当对 SQL 语句进行解析的时候，如果需要相关的数据，会从数据字典缓冲区中提取。

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

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

    eg:

    select * from player where player_id = 10001;

    绑定变量的形式就是 sql select * from player where player_id = :player_id;

    这种方式意味着在查询player_id = 10001之后，还会查询10002、10003之类的数据，软解析的方式会在第一次查询之后，在共享池中存在这类查询的执行计划。虽然减少了Oracle的解析工作量，但是使用动态SQL的方式，因为参数不同，会导致SQL的执行效率不同，同时SQL的优化也比较困难。

  3. 优化器：优化器中就是要进行硬解析，也就是决定怎么做，比如创建解析树，生成执行计划。

  4. 执行器：当有了解析树和执行计划之后，就知道了 SQL该怎么被执行，这样就可以在执行器中执行语句了。

在MySQL中

MySQL是典型的C/S架构。mysqld是服务端程序。

  1. 查询缓存：Server 如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没有，就进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在 MySQL8.0 之后就抛弃了这个功能。
  2. 一旦数据表有更新，缓存都将清空，因此只有数据表是静态的时候，或者数据表很少发生变化时，使用缓存查询才有价值，否则如果数据表经常更新，反而增加了SQL 的查询时间。
  3. 解析器：在解析器中对 SQL 语句进行语法分析、语义分析。
  4. 优化器：在优化器中会确定 SQL 语句的执行路径，比如是根据全表检索，还是根据索引来检索等。
  5. 执行器：在执行之前需要判断该用户是否具备权限，如果具备权限就执行 SQL 查询并返回结果。在 MySQL8.0 以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。

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

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

开启profiling能够查看MySQL在执行SQL语句过程中的所使用的资源情况

mysql> select @@profiling;

mysql> set profiling=1;

mysql> show profiles;

myslq> show profile for query num_x;

DDL创建数据库&数据表

DDL 的英文全称是 Data Definition Language

CREATE/DROP DATABASE nba;

CREATE TABLE player (

​ player_id int(11) NOT NULL AUTO_INCREMENT,

​ player_name varchar(255) NOT NULL

);

数据类型中 int(11) 代表整数类型，显示长度为 11 位，括号中的参数 11 代表的是最大有效显示长度，与类型包含的数值范围大小无关。 varchar(255)代表的是最大长度为 255 的可变字符串类型。NOT NULL表明整个字段不能是空值，是一种数据约束。AUTO_INCREMENT代表主键自动增长。

使用Navicat设计好表然后转存SQL，可以得到

TABLE

定义TABLE

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，代表对大小写敏感

player_id 设置为了主键，因此在 DDL 中使用PRIMARY KEY进行规定，同时索引方法采用 BTREE

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

对 player_name 字段进行索引，在设置字段索引时，我们可以设置为UNIQUE INDEX（唯一索引），也可以设置为其他索引方式，比如NORMAL INDEX（普通索引）；唯一索引和普通索引的区别在于它对字段进行了唯一性的约束。在索引方式上，你可以选择BTREE或者HASH，这里采用了BTREE方法进行索引

修改TABLE

  1. 添加一个 age 字段，类型为int(11)

    ALTER TABLE player ADD (age int(11));

  2. 修改字段名，将 age 字段改成player_age

    ALTER TABLE player RENAME COLUMN age to player_age;

  3. 修改字段的数据类型，将player_age的数据类型设置为float(3,1)

    ALTER TABLE palyer MODIFY (player_age float(3,1));

  4. 删除刚才添加的player_age字段

    ALTER TABLE player DROP COLUMN player_age;

常见约束

  1. 主键约束：

    主键起的作用是唯一标识一条记录，不能重复，不能为空，即 UNIQUE+NOT NULL。一个数据表的主键只能有一个。主键可以是一个字段，也可以由多个字段复合组成。

  2. 外键约束：

    外键确保了表与表之间引用的完整性。一个表中的外键对应另一张表的主键。外键可以是重复的，也可以为空。

    外键本身是为了强一致性，强调 正确性>性能，在数据库层面保证了数据的完整性和一致性。不过也可以不使用外键，可以在业务层进行实现。但这样会让业务逻辑与数据有一定的耦合性。也就是业务逻辑和数据必须同时修改。

  3. 唯一性约束、NOT NULL 约束、DEFAULT等

  4. CHECK 约束

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

SELECT

查询语法

1.查询列

  1. 查询列

    SELECT name FROM heros

  2. 查询多个列

    SELECT name, hp_max, mp_max, attack_max FROM heros

  3. 检索出所有列

    SELECT * FROM heros

2.起别名(AS)

起别名(AS)

SELECT name AS n, hp_max AS hm, mp_max AS mm, attack_m AS am FROM heros

也可以对表取别名，在多表查询的时候会用到

AS在大多数情况下也可以省略不写，例如

SELECT deptno, name AS deptname from dept; （这样显示的列名由dname变成了deptname）

查询常数

对 heros 数据表中的英雄名进行查询，同时增加一列字段platform，这个字段固定值为“王者荣耀”，可以这样写：

SQL：SELECT ‘王者荣耀’ as platform, name FROM heros

在这个 SQL 语句中，我们虚构了一个platform字段，并且把它设置为固定值“王者荣耀”

需要说明的是，如果常数是个字符串，那么使用单引号（‘’）就非常重要了，比如‘王者荣耀’。单引号说明引号中的字符串是个常数，否则 SQL 会把王者荣耀当成列名进行查询，但实际上数据表里没有这个列名，就会引起错误。如果常数是英文字母，比如'WZRY'也需要加引号。如果常数是个数字，就可以直接写数字，不需要单引号，比如：

SQL：SELECT 123 as platform, name FROM heros

3.去除重复行(DISTINCT)

关于单个表的 SELECT 查询，还有一个非常实用的操作，就是从结果中去掉重复的行。使用的关键字是 DISTINCT。

SQL：SELECT DISTINCT attack_range FROM heros

加上name的去重查询

SELECT DISTINCT attack_range, name FROM heros

  • DISTINCT 需要放在所有查询列之前
  • DISTINCT 其实是对后面所有列名的组合进行去重

排序检索数据(ORDER BY)

ORDER BY

  1. 排序的列名：ORDER BY后面可以有一个或多个列名，如果是多个列名进行排序，会按照后面第一个列先进行排序，当第一列值相同的时候，再按照第二列进行排序，以此类推
  2. 排序的顺序：ORDER BY后面可以注明排序规则，ASC代表递增排序，DESC代表递减排序，默认是按照ASC顺序
  3. 非选择排序：即使在 SELECT 后面没有这个列名，你同样可以放到 ORDER BY 后面进行排序
  4. 位置：ORDER BY通常位于SELECT语句的最后一条子句

eg：

显示英雄名称及最大生命值，按照最大生命值从高到低的方式进行排序：

SQL：SELECT name, hp_max FROM heros ORDER BY hp_max DESC

想要显示英雄名称及最大生命值，按照第一排序最大法力从低到高，当最大法力值相等的时候则按照第二排序进行，即最大生命值从高到低的方式进行排序：

SQL：SELECT name, hp_max FROM heros ORDER BY mp_max, hp_max DESC

约束返回结果的数量(LIMIT)

SQL：SELECT name, hp_max FROM heros ORDER BY hp_max DESC LIMIT 5

注意：

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

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

LIMIT更多内容

  • 检索记录行6-15

    SELECT * FROM table_name LIMIT 5,10

  • 为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定rows参数为-1

    例如：检索记录行 20 到末尾

    SELECT * FROM table_name LIMIT 19,-1

  • 检索前5条记录

    SELECT * FROM table_name LIMIT 5

SELECT的执行顺序

  1. 关键字的顺序不能颠倒

SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY …

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

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

eg:

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语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。（执行过程对用户不可见）

详细分析：

首先进行条件筛选部分

  1. SELECT先执行FROM这一步，在这个阶段，如果是多张表联查，还会经历

    • 先通过CROSS JOIN求笛卡尔积，相当于得到虚拟表vt（virtual table）1-1
    • 通过ON进行筛选，在虚拟表vt1-1的基础上进行筛选，得到虚拟表vt1-2
    • 添加外部行。如果使用的是左连接、右连接或者全连接，就会涉及到外部行，也就是在虚拟表vt1-2的基础上增加外部行，得到虚拟表vt1-3

    如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止，这个过程得到是我们的原始数据

  2. 拿到了查询数据表的原始数据，也就是最终的虚拟表vt1，就可以在此基础上再进行WHERE阶段，在这个阶段中，会根据vt1表的结果进行筛选过滤，得到虚拟表vt2

  3. 然后进入第三步和第四步，也就是GROUP和HAVING阶段，在这个阶段中，实际上是在虚拟表vt2的基础上进行分组和分组过滤，得到中间的虚拟表vt3和vt4

当完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到SELECT和DISTINCT阶段

  1. 首先在SELECT阶段会提取想要的字段，然后在DISTINCT阶段过滤掉重复的行，分别得到vt5-1和vt5-2
  2. 当提取了想要的字段数据后，就可以按照指定的字段进行排序，也就是ORDER BY阶段，得到虚拟表vt6
  3. 最后在vt6的基础上，取出指定行的记录，也就是LIMIT阶段，得到最终的结果，对应的是虚拟表vt7

过滤（WHERE）

1.比较运算符

取决于DBMS是否支持，如Access 不支持（!=），不等于应该使用（<>）。在 MySQL 中，不支持（!>）（!<）等

eg：

SQL：SELECT name, hp_max FROM heros WHERE hp_max BETWEEN 5399 AND 6811

空值检查

SQL：SELECT name, hp_max FROM heros WHERE hp_max IS NULL

2.逻辑运算符

eg:

  • AND
  1. 筛选最大生命值大于 6000，最大法力大于 1700 的英雄，然后按照最大生命值和最大法力值之和从高到低进行排序
SQL：SELECT name, hp_max, mp_max FROM heros WHERE hp_max > 6000 AND mp_max > 1700 ORDER BY (hp_max+mp_max) DESC
  • AND + OR
  1. 如果 AND 和 OR 同时存在 WHERE 子句中会是怎样的呢？假设我们想要查询最大生命值加最大法力值大于 8000 的英雄，或者最大生命值大于 6000 并且最大法力值大于 1700 的英雄。
SQL：SELECT name, hp_max, mp_max FROM heros WHERE (hp_max+mp_max) > 8000 OR hp_max > 6000 AND mp_max > 1700 ORDER BY (hp_max+mp_max) DESC

结果发现，这次的查询多了一些，因为放宽了条件，允许最大生命值+最大法力值大于8000的英雄显示出来，⚠️，当WHERE子句中同时存在OR和AND的时候，AND执行的优先级会更高，也就是说SQL会优先处理AND操作符，然后再处理OR操作符

  • AND + OR + （）
  1. 如果对这条查询语句OR两边的条件增加一个括号
SQL：SELECT name, hp_max, mp_max FROM heros WHERE ((hp_max+mp_max) > 8000 OR hp_max > 6000) AND mp_max > 1700 ORDER BY (hp_max+mp_max) DESC

当WHERE子句中同时出现AND和OR操作符，执行的先后顺序为：一般来说（）优先级最高，其次是AND，然后是OR

  • AND + OR + IN + NOT
  1. 查询主要定位或者次要定位是法师或者射手的英雄，同时英雄的上线时间不再2016-01-01到2017-01-01之间
SQL：
SELECT name, role_main, role_assist, hp_max, mp_max, birthdate
FROM heros 
WHERE (role_main IN ('法师', '射手') OR role_assist IN ('法师', '射手')) 
AND DATE(birthdate) NOT BETWEEN '2016-01-01' AND '2017-01-01'
ORDER BY (hp_max + mp_max) DESC

3.通配符（LIKE）

通配符就是我们用来匹配值的一部分的特殊字符。这里我们需要使用到 LIKE 操作符。

如果我们想要匹配任意字符串出现的任意次数，需要使用（%）通配符。比如我们想要查找英雄名中包含“太”字的英雄都有哪些：

SQL：SELECT name FROM heros WHERE name LIKE ‘% 太 %’

可以查询到有两条数据：东皇太一和太乙真人

⚠️不同DBMS对通配符的定义不同，在Access中使用的是（*）而不是（%）。另外关于字符串的搜索可能是需要区分大小写的，比如‘liu%’就不能匹配‘LIUBEI'。具体是否区分大小写还需要考虑不同的DBMS以及它们的配置。

如果想要匹配单个字符，就需要使用下划线（_）通配符。（%）和（ _ ）区别在于，前者代表一个或多个字符，后者只代表一个字符，比如想要查找英雄名除了第一个字以外，包含“太”字的英雄有哪些

SQL：SELECT name FROM heros WHERE name LIKE ‘_% 太 %’

因为太乙真人的太是第一个字符，而_%太%中的太不是在第一个字符，所以匹配不到“太乙真人”，只可以匹配上“东皇太一”。

同样需要说明的是，在 Access 中使用（?）来代替（_），而且在 DB2 中是不支持通配符（_）的，因此你需要在使用的时候查阅相关的 DBMS 文档。

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

练习

编写 SQL 语句，对英雄名称、主要定位、次要定位、最大生命和最大法力进行查询，筛选条件为：主要定位是坦克或者战士，并且次要定位不为空，同时满足最大生命值大于 8000 或者最大法力小于 1500 的英雄，并且按照最大生命和最大法力之和从高到底的顺序进行排序。

SELECT name, role_main, role_asist, heap_max, mp_max FROM heros WHERE role_main IN ('坦克' , '战士') AND role_asist IS NOT NULL AND (heap_max > 8000 OR mp_max < 1500) ORDER BY (heap_max + mp_max) DESC.

SQL函数

1. 算数函数

2.字符串函数

3.日期函数

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

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

4.转换函数

这两个函数不像其他函数，看一眼函数名就知道代表什么、如何使用。下面举了这两个函数的例子，你需要自己运行下：

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。

示例：

首先显示英雄以及他的物攻成长，对应的字段为attack_growth, 我们让这个字段精确到小数点后一位，需要使用的是算术函数里的 ROUND 函数。

SELECT name, ROUND(attack_growth,1) FROM heros

代码中，ROUND(attack_growth,1)中的attack_growth代表想要处理的数据，“1”代表四舍五入的位数，也就是我们这里需要精确到的位数。

运行结果为：

假设想要显示英雄最大生命值的最大值，就需要用到MAX函数。

SQL：
SELECT MAX(hp_max) FROM heros

假如我们想要知道最大生命值最大的是哪个英雄，以及对应的数值，就需要分成两个步骤来处理：首先找到英雄的最大生命值的最大值，即SELECT MAX(hp_max) FROM heros，然后再筛选最大生命值等于这个最大值的英雄，如下所示。

SQL：
SELECT name, hp_max FROM heros WHERE hp_max = (SELECT MAX(hp_max) FROM heros)

假如想要提取英雄上线日期（对应字段 birthdate）的年份，只显示有上线日期的英雄即可（有些英雄没有上线日期的数据，不需要显示），这里我们需要使用 EXTRACT 函数，提取某一个时间元素。所以我们需要筛选上线日期不为空的英雄，即WHERE birthdate is not null，然后再显示他们的名字和上线日期的年份，即：

SQL：
SELECT name, EXTRACT(YEAR FROM birthdate) AS birthdate FROM heros WHERE birthdate is NOT NULL

或者使用如下形式：

SQL:
SELECT name, YEAR(birthdate) AS birthdate FROM heros WHERE birthdate is NOT NULL

假设我们需要找出在 2016 年 10 月 1 日之后上线的所有英雄。这里我们可以采用 DATE 函数来判断 birthdate 的日期是否大于 2016-10-01，即WHERE DATE(birthdate)>'2016-10-01'，然后再显示符合要求的全部字段信息，即：

SELECT * FROM heros WHERE DATE(birthdate)>'2016-10-01'

需要注意的是下面这种写法是不安全的：

SELECT * FROM heros WHERE birthdate>'2016-10-01'

因为很多时候你无法确认 birthdate 的数据类型是字符串，还是 datetime 类型，如果你想对日期部分进行比较，那么使用DATE(birthdate)来进行比较是更安全的。

假设我们需要知道在 2016 年 10 月 1 日之后上线英雄的平均最大生命值、平均最大法力和最高物攻最大值。同样我们需要先筛选日期条件，即WHERE DATE(birthdate)>'2016-10-01'，然后再选择AVG(hp_max), AVG(mp_max), MAX(attack_max)字段进行显示。

SQL： SELECT AVG(hp_max), AVG(mp_max), MAX(attack_max) FROM heros WHERE DATE(birthdate)>'2016-10-01'

规范

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

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

练习

计算英雄的最大生命平均值；

SELECT AVG(max_heap) AS avg_max FROM heros;

显示出所有在 2017 年之前上线的英雄，如果英雄没有统计上线日期则不显示。

SELECT name FROM heros WHERE YEAR(birthday) < 2017 AND birthday IS NOT NULL;

5.聚集函数(COUNT、MAX、MIN、SUM、AVG)

SQL中的聚集函数一共有5个：COUNT()、MAX()、MIN()、SUM()、AVG()

Eg:

  1. 查询最大生命值大于 6000 的英雄数量

    SELECT COUNT(*) FROM heros WHERE hp_max > 6000

  2. 查询最大生命值大于 6000，且有次要定位的英雄数量，需要使用 COUNT 函数

    SELECT COUNT(role_assist) FROM heros WHERE hp_max > 6000

    ⚠️有些英雄没有次要定位，即 role_assist 为 NULL，这时COUNT(role_assist)会忽略值为 NULL 的数据行，而 COUNT(*) 只是统计数据行数，不管某个字段是否为 NULL。

  3. 查询射手（主要定位或者次要定位是射手）的最大生命值的最大值是多少，需要使用 MAX 函数

    SELECT MAX(hp_max) FROM heros WHERE role_main = '射手' or role_assist = '射手'

  4. 想知道射手（主要定位或者次要定位是射手）的英雄数、平均最大生命值、法力最大值的最大值、攻击最大值的最小值，以及这些英雄总的防御最大值等汇总数据

    SELECT COUNT(*), AVG(hp_max), MAX(mp_max), MIN(attack_max), SUM(defense_max) FROM heros WHERE role_main = '射手' or role_assist = '射手'

    ⚠️需要说明的是 AVG、MAX、MIN 等聚集函数会自动忽略值为 NULL 的数据行，MAX 和 MIN 函数也可以用于字符串类型数据的统计，如果是英文字母，则按照 A—Z 的顺序排列，越往后，数值越大。如果是汉字则按照全拼拼音进行排列

  5. 查询不同的生命最大值的英雄数量是多少

    SELECT COUNT(DISTINCT hp_max) FROM heros

  6. 统计不同生命最大值英雄的平均生命最大值，保留小数点后两位。

    首先需要取不同生命最大值，即DISTINCT hp_max，然后针对它们取平均值，即AVG(DISTINCT hp_max)，最后再针对这个值保留小数点两位，也就是ROUND(AVG(DISTINCT hp_max), 2)

    SELECT ROUND(AVG(DISTINCT hp_max), 2) FROM heros

分组(GROUP BY)

在做统计的时候，可能需要先对数据按照不同的数值进行分组，然后对这些分好的组进行聚集统计。对数据进行分组，需要使用 GROUP BY 子句。

比如我们想按照英雄的主要定位进行分组，并统计每组的英雄数量。

SELECT COUNT(*), role_main FROM heros GROUP BY role_main

运行结果：

比如我们想要对英雄按照次要定位进行分组，并统计每组英雄的数量

SELECT COUNT(*), role_assist FROM heros GROUP BY role_assist

运行结果：（6 条记录）

你能看出如果字段为 NULL，也会被列为一个分组。在这个查询统计中，次要定位为 NULL，即只有一个主要定位的英雄是 40 个。

我们也可以使用多个字段进行分组，这就相当于把这些字段可能出现的所有的取值情况都进行分组。

比如，我们想要按照英雄的主要定位、次要定位进行分组，查看这些英雄的数量，并按照这些分组的英雄数量从高到低进行排序。

SELECT COUNT(*) as num, role_main, role_assist FROM heros GROUP BY role_main, role_assist ORDER BY num DESC

运行结果：（19 条记录）

Tips: 可以发现：在执行顺序上，SELECT字段在GROUP BY和HAVING之后，不过在SELECT字段之前，已经计算了聚集函数，也就是COUNT(*) as num。聚集函数的计算在GROUP BY之后，HAVING之前。

过滤分组(HAVING)

当我们创建出很多分组的时候，有时候就需要对分组进行过滤。你可能首先会想到 WHERE 子句，实际上过滤分组我们使用的是 HAVING。HAVING 的作用和 WHERE 一样，都是起到过滤的作用，只不过 WHERE 是用于数据行，而 HAVING 则作用于分组。

比如我们想要按照英雄的主要定位、次要定位进行分组，并且筛选分组中英雄数量大于 5 的组，最后按照分组中的英雄数量从高到低进行排序。

首先我们需要获取的是英雄的数量、主要定位和次要定位，即SELECT COUNT(*) as num, role_main, role_assist。然后按照英雄的主要定位和次要定位进行分组，即GROUP BY role_main, role_assist，同时我们要对分组中的英雄数量进行筛选，选择大于 5 的分组，即HAVING num > 5，然后按照英雄数量从高到低进行排序，即ORDER BY num DESC。

SELECT COUNT(*) as num, role_main, role_assist FROM heros GROUP BY role_main, role_assist HAVING num > 5 ORDER BY num DESC

运行结果：（4 条记录）

如果把 HAVING 替换成了 WHERE，SQL 则会报错。对于分组的筛选，我们一定要用 HAVING，而不是 WHERE。另外你需要知道的是，HAVING 支持所有 WHERE 的操作，因此所有需要 WHERE 子句实现的功能，你都可以使用 HAVING 对分组进行筛选。

Eg:

筛选最大生命值大于 6000 的英雄，按照主要定位、次要定位进行分组，并且显示分组中英雄数量大于 5 的分组，按照数量从高到低进行排序。

SELECT COUNT(*) as num, role_main, role_assist FROM heros WHERE hp_max > 6000 GROUP BY role_main, role_assist HAVING num > 5 ORDER BY num DESC

运行结果：（2 条记录）

要记住，在 SELECT 查询中，关键字的顺序是不能颠倒的，它们的顺序是：

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

练习

  1. 筛选最大生命值大于 6000 的英雄，按照主要定位进行分组，选择分组英雄数量大于 5 的分组，按照分组英雄数从高到低进行排序，并显示每个分组的英雄数量、主要定位和平均最大生命值。

    分析：最后一句“显示…“是查询目的，所以先写这个，再按照条件一句句写下来即可

    SELECT COUNT(*) AS num, role_main, AVG(hp_max) FROM heros WHERE hp_max > 6000 GROUP BY role_main HAVING num > 5 ORDER BY num DESC;

  2. 筛选最大生命值与最大法力值之和大于 7000 的英雄，按照攻击范围来进行分组，显示分组的英雄数量，以及分组英雄的最大生命值与法力值之和的平均值、最大值和最小值，并按照分组英雄数从高到低进行排序，其中聚集函数的结果包括小数点后两位。

    SELECT COUNT(*) AS num, ROUND(AVG(hp_max + mp_max),2), ROUND(MAX(hp_max + mp_max),2), ROUND(MIN(hp_max + mp_max),2) WHERE (hp_max + mp_max) > 7000 GROUP BY attack_range ORDER BY num DESC

子查询

非关联子查询

eg：

以 NBA 球员数据表为例，假设我们想要知道哪个球员的身高最高，最高身高是多少，就可以采用子查询的方式：

SELECT player_name, height FROM player WHERE height = (SELECT MAX(height) FROM player)

关联子查询

子查询的执行依赖于外部查询

eg:

查找每个球队中大于平均身高的球员有哪些，并显示他们的球员姓名、身高以及所在球队 ID

首先先统计球队的平均身高，即

SELECT AVG(height) FROM player AS b WHERE E a.team_id = b.team_id

然后筛选身高大于这个数值的球员姓名、身高和球队ID，即整合结果为

SELECT player_name, height, team_id FROM player AS a WHERE height > (SELECT AVG(height) FROM player AS b WHERE a.team_id = b.team_id)

可以发现：

非关联子查询与主查询的执行无关，只需要执行一次即可，而关联子查询，则需要将主查询的字段值传入子查询中进行关联查询。

EXISTS子查询

关联子查询通常也会和 EXISTS 一起来使用，EXISTS 子查询用来判断条件是否满足，满足的话为 True，不满足为 False。

eg：

想要看出场过的球员都有哪些，并且显示他们的姓名、球员 ID 和球队 ID。在这个统计中，是否出场是通过 player_score 这张表中的球员出场表现来统计的，如果某个球员在 player_score 中有出场记录则代表他出场过，这里就使用到了 EXISTS 子查询，即EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)，然后将它作为筛选的条件，实际上也是关联子查询，即：

SELECT player_id, team_id, player_name FROM player WHERE EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)

同样，我们也可以通过NOT EXISTS查询不存在于表中的信息。

eg：

查询不存在于player_score表中的球员信息，比如主表中的player_id不在子表player_score中，判断语句为NOT EXISTS

SELECT player_id, team_id, player_name FROM player WHERE NOT EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)

集合比较子查询(IN、ANY、ALL、SOME)

集合比较子查询的作用是与另一个查询结果集进行比较，我们可以在子查询中使用 IN、ANY、ALL 和 SOME 操作符，它们的含义和英文意义一样：

还是通过上面那个例子，假设我们想要看出场过的球员都有哪些，可以采用 IN 子查询来进行操作：

SELECT player_id, team_id, player_name FROM player WHERE player_id IN (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)

你会发现运行结果和上面的是一样的，那么问题来了，既然 IN 和 EXISTS 都可以得到相同的结果，那么我们该使用 IN 还是 EXISTS 呢？

我们可以把这个模式抽象为：

SELECT * FROM A WHERE cc IN (SELECT cc FROM B)

SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc)

IN表是外边和内表进行hash连接，是先执行子查询；

EXISTS是外表进行循环，然后在内表进行查询。

因此如果外表数据量大，则用IN，如果外表数据量小，则用EXISTS。

IN有一个缺陷是不能判断NULL，因此如果字段存在NULL值，则会出现返回，因此最好使用NOT EXISTS。

了解了 IN 查询后，我们来看下 ANY 和 ALL 子查询。刚才讲到了 ANY 和 ALL 都需要使用比较符，比较符包括了（>）（=）（<）（>=）（<=）和（<>）等。

如果我们想要查询球员表中，比印第安纳步行者（对应的 team_id 为 1002）中任何一个球员身高高的球员的信息，并且输出他们的球员 ID、球员姓名和球员身高，该怎么写呢？首先我们需要找出所有印第安纳步行者队中的球员身高，即SELECT height FROM player WHERE team_id = 1002，然后使用 ANY 子查询即：

SELECT player_id, player_name, height FROM player WHERE height > ANY (SELECT height FROM player WHERE team_id = 1002)

同样，如果我们想要知道比印第安纳步行者（对应的 team_id 为 1002）中所有球员身高都高的球员的信息，并且输出球员 ID、球员姓名和球员身高，该怎么写呢？

SELECT player_id, player_name, height FROM player WHERE height > ALL (SELECT height FROM player WHERE team_id = 1002)

需要强调的是 ANY、ALL 关键字必须与一个比较操作符一起使用。因为如果你不使用比较操作符，就起不到集合比较的作用，那么使用 ANY 和 ALL 就没有任何意义。

将子查询作为计算字段

实际上子查询也可以作为主查询的计算字段。

eg:

查询每个球队的球员数，也就是对应 team 这张表，我需要查询相同的 team_id 在 player 这张表中所有的球员数量是多少。

SELECT team_name, (SELECT count(*) FROM player WHERE player.team_id = team.team_id) AS player_num FROM team

运行结果：（3 条记录）

你能看到，在 player 表中只有底特律活塞和印第安纳步行者的球员数据，所以它们的 player_num 不为 0，而亚特兰大老鹰的 player_num 等于 0。在查询的时候，我将子查询SELECT count(*) FROM player WHERE player.team_id = team.team_id作为了计算字段，通常我们需要给这个计算字段起一个别名，这里我用的是 player_num，因为子查询的语句比较长，使用别名更容易理解。

练习

编写 SQL 语句，得到场均得分大于 20 的球员。场均得分从 player_score 表中获取，同时你需要输出球员的 ID、球员姓名以及所在球队的 ID 信息。

我写的：

SELECT player_id, player_name, team_id
FROM player
WHERE player_id IN (SELECT player_id FROM player_score WHERE score > 20)

评论区有人写的：

  1. 用EXISTS
SELECT player_name, player_id, team_id 
FROM player 
WHERE EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id AND score > 20)
  1. 用IN
SELECT player_id, player_name, team_id
FROM player
AS a WHERE a.player_id IN (SELECT player_id FROM player_score AS b WHERE a.player_id=b.player_id AND AVG(b.score) > 20)

显然，不管怎么说我都没有做判断id一致这个操作

连接

SQL92

5 种连接方式，它们分别是笛卡尔积、等值连接、非等值连接、外连接（左连接、右连接）和自连接

笛卡尔积

笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。

两张表的笛卡尔积的结果，这是笛卡尔积的调用方式：

SELECT * FROM player, team

笛卡尔积也称为交叉连接，英文是 CROSS JOIN，它的作用就是可以把任意表进行连接，即使这两张表不相关。但我们通常进行连接还是需要筛选的，因此你需要在连接后面加上 WHERE 子句，也就是作为过滤条件对连接数据进行筛选。比如后面要讲到的等值连接。

等值连接

两张表的等值连接就是用两张表中都存在的列进行连接。我们也可以对多张表进行等值连接。

针对 player 表和 team 表都存在 team_id 这一列，我们可以用等值连接进行查询。

SELECT player_id, player.team_id, player_name, height, team_name FROM player, team WHERE player.team_id = team.team_id

在进行等值连接的时候，可以使用表的别名，这样会让 SQL 语句更简洁：

SELECT player_id, a.team_id, player_name, height, team_name FROM player AS a, team AS b WHERE a.team_id = b.team_id

需要注意的是，如果我们使用了表的别名，在查询字段中就只能使用别名进行代替，不能使用原有的表名，比如下面的 SQL 查询就会报错：

SELECT player_id, player.team_id, player_name, height, team_name FROM player AS a, team AS b WHERE a.team_id = b.team_id

非等值连接

当我们进行多表查询的时候，如果连接多个表的条件是等号时，就是等值连接，其他的运算符连接就是非等值查询。

如果想要知道每个球员的身高的级别，可以采用非等值连接查询。

SELECT p.player_name, p.height, h.height_level
FROM player AS p, height_grades AS h
WHERE p.height BETWEEN h.height_lowest AND h.height_highest

外连接

除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。两张表的外连接，会有一张是主表，另一张是从表。如果是多张表的外连接，那么第一张表是主表，即显示全部的行，而第剩下的表则显示对应连接的信息。在 SQL92 中采用（+）代表从表所在的位置，而且在 SQL92 中，只有左外连接和右外连接，没有全外连接。

  1. 左外连接，就是指左边的表是主表，需要显示左边表的全部行，而右侧的表是从表，（+）表示哪个是从表。
SELECT * FROM player, team where player.team_id = team.team_id(+)

相当于 SQL99 中的：

SELECT * FROM player LEFT JOIN team on player.team_id = team.team_id
  1. 右外连接，指的就是右边的表是主表，需要显示右边表的全部行，而左侧的表是从表。
SELECT * FROM player, team where player.team_id(+) = team.team_id

相当于 SQL99 中的：

SELECT * FROM player RIGHT JOIN team on player.team_id = team.team_id

需要注意的是，LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的标准中，在 SQL92 中不存在，只能用（+）表示。

自连接

自连接可以对多个表进行操作，也可以对同一个表进行操作。也就是说查询条件使用了当前表的字段。

比如我们想要查看比布雷克·格里芬高的球员都有谁，以及他们的对应身高：

SELECT b.player_name, b.height FROM player as a , player as b WHERE a.player_name = '布雷克 - 格里芬' and a.height < b.height

如果不用自连接的话，需要采用两次 SQL 查询。首先需要查询布雷克·格里芬的身高。

SELECT height FROM player WHERE player_name = '布雷克 - 格里芬'

运行结果为 2.08。

然后再查询比 2.08 高的球员都有谁，以及他们的对应身高：

SELECT player_name, height FROM player WHERE height > 2.08

练习

现有team表格的sql语句：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for team
-- ----------------------------
DROP TABLE IF EXISTS `team`;
CREATE TABLE `team`  (
  `team_id` int(11) NOT NULL COMMENT '球队ID',
  `team_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '球队名称',
  PRIMARY KEY (`team_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of team
-- ----------------------------
INSERT INTO `team` VALUES (1001, '底特律活塞');
INSERT INTO `team` VALUES (1002, '印第安纳步行者');
INSERT INTO `team` VALUES (1003, '亚特兰大老鹰');

SET FOREIGN_KEY_CHECKS = 1;

即：

要求：表格中一共有 3 支球队，现在这 3 支球队需要进行比赛，请用一条 SQL 语句显示出所有可能的比赛组合。

  1. 区分主客场

    SELECT t1.team_name, t2.team_name FROM team AS t1 LEFT JOIN team AS t2 ON t1.team_id != t2.team_id

  2. 不重复的两个球队的比赛

    SELECT t1.team_name, t2.team_name FROM team AS t1, team AS t2 WHERE t1.team_id < t2.team_id

SQL99

现有三张数据表：player、team、height_grades

  • player

  • team

  • height_grades

交叉连接(CROSS JOIN)

交叉连接实际上就是 SQL92 中的笛卡尔乘积，只是这里我们采用的是 CROSS JOIN。

SELECT * FROM player CROSS JOIN team

运行结果（一共 37*3=111 条记录）：

如果多张表进行交叉连接，比如表 t1，表 t2，表 t3 进行交叉连接，可以写成下面这样：

SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3

自然连接

自然连接即为 SQL92 中的等值连接。它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。

如果我们想把 player 表和 team 表进行等值连接，相同的字段是 team_id。还记得在 SQL92 标准中，是如何编写的么？

SELECT player_id, a.team_id, player_name, height, team_name FROM player as a, team as b WHERE a.team_id = b.team_id

在 SQL99 中你可以写成：

SELECT player_id, team_id, player_name, height, team_name FROM player NATURAL JOIN team

实际上，在 SQL99 中用 NATURAL JOIN 替代了 WHERE player.team_id = team.team_id。

ON连接

ON 连接用来指定我们想要的连接条件，针对上面的例子，它同样可以帮助我们实现自然连接的功能：

SELECT player_id, player.team_id, player_name, height, team_name FROM player JOIN team ON player.team_id = team.team_id

这里我们指定了连接条件是ON player.team_id = team.team_id，相当于是用 ON 进行了 team_id 字段的等值连接。

也可以 ON 连接进行非等值连接，比如我们想要查询球员的身高等级，需要用 player 和 height_grades 两张表：

SELECT p.player_name, p.height, h.height_level
FROM player AS p JOIN height_grades AS h
ON height BETWEEN h.height_lowest AND h.height_highest

这个语句的运行结果和我们之前采用 SQL92 标准的查询结果一样。

SELECT p.player_name, p.height, h.height_level
FROM player AS p, height_grades AS h
WHERE p.height BETWEEN h.height_lowest AND h.height_highest

一般来说在 SQL99 中，我们需要连接的表会采用 JOIN 进行连接，ON 指定了连接条件，后面可以是等值连接，也可以采用非等值连接。

USING 连接

当我们进行连接的时候，可以用 USING 指定数据表里的同名字段进行等值连接。比如：

SELECT player_id, team_id, player_name, height, team_name FROM player JOIN team USING(team_id)

你能看出与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，你需要在 USING 的括号 () 中填入要指定的同名字段。同时使用 JOIN USING 可以简化 JOIN ON 的等值连接，它与下面的 SQL 查询结果是相同的：

SELECT player_id, player.team_id, player_name, height, team_name FROM player JOIN team ON player.team_id = team.team_id

外连接

  1. 左外连接

    LEFT JOIN 或 LEFT OUTER JOIN

    eg:

    SELECT * FROM player LEFT JOIN team ON player.team_id = team.team_id

  2. 右外连接

    RIGHT JOIN 或 RIGHT OUTER JOIN

    eg:

    SELECT * FROM player RIGHT JOIN team ON player.team_id = team.team_id

  3. 全外连接

    FULL JOIN 或 FULL OUTER JOIN

    eg:

    SELECT * FROM player FULL JOIN team ON player.team_id = team.team_id

⚠️需要注意的是 MySQL 不支持全外连接，否则的话全外连接会返回左表和右表中的所有行。当表之间有匹配的行，会显示内连接的结果。当某行在另一个表中没有匹配时，那么会把另一个表中选择的列显示为空值。也就是说，全外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。

自连接

自连接的原理在 SQL92 和 SQL99 中都是一样的，只是表述方式不同。

比如我们想要查看比布雷克·格里芬身高高的球员都有哪些，在两个 SQL 标准下的查询如下。

SQL92

SELECT b.player_name, b.height FROM player as a , player as b WHERE a.player_name = '布雷克 - 格里芬' and a.height < b.height

SQL99

SELECT b.player_name, b.height FROM player as a JOIN player as b ON a.player_name = '布雷克 - 格里芬' and a.height < b.height

运行结果（6 条记录）：

区别

至此我们讲解完了 SQL92 和 SQL99 标准下的连接查询，它们都对连接进行了定义，只是操作的方式略有不同。我们再来回顾下，这些连接操作基本上可以分成三种情况：

  1. 内连接：将多个表之间满足连接条件的数据行查询出来。它包括了等值连接、非等值连接和自连接。
  2. 外连接：会返回一个表中的所有记录，以及另一个表中匹配的行。它包括了左外连接、右外连接和全连接。
  3. 交叉连接：也称为笛卡尔积，返回左表中每一行与右表中每一行的组合。在 SQL99 中使用的 CROSS JOIN。

不过 SQL92 在这三种连接操作中，和 SQL99 还存在着明显的区别。

首先我们看下 SQL92 中的 WHERE 和 SQL99 中的 JOIN。

你能看出在 SQL92 中进行查询时，会把所有需要连接的表都放到 FROM 之后，然后在 WHERE 中写明连接的条件。而 SQL99 在这方面更灵活，它不需要一次性把所有需要连接的表都放到 FROM 之后，而是采用 JOIN 的方式，每次连接一张表，可以多次使用 JOIN 进行连接。

另外，我建议多表连接使用 SQL99 标准，因为层次性更强，可读性更强，比如：

SELECT ...
FROM table1
    JOIN table2 ON table1 和 table2 的连接条件
        JOIN table3 ON table2 和 table3 的连接条件

它的嵌套逻辑类似我们使用的 FOR 循环：

for t1 in table1:
    for t2 in table2:
       if condition1:
           for t3 in table3:
              if condition2:
                  output t1 + t2 + t3

SQL99 采用的这种嵌套结构非常清爽，即使再多的表进行连接也都清晰可见。如果你采用 SQL92，可读性就会大打折扣。

最后一点就是，SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 和 JOIN USING。它们在实际中是比较常用的，省略了 ON 后面的等值条件判断，让 SQL 语句更加简洁。

不同 DBMS 中使用连接需要注意的地方

SQL 连接具有通用性，但是不同的 DBMS 在使用规范上会存在差异，在标准支持上也存在不同。在实际工作中，你需要参考你正在使用的 DBMS 文档，这里我整理了一些需要注意的常见的问题。

1. 不是所有的 DBMS 都支持全外连接

虽然 SQL99 标准提供了全外连接，但不是所有的 DBMS 都支持。不仅 MySQL 不支持，Access、SQLite、MariaDB 等数据库软件也不支持。不过在 Oracle、DB2、SQL Server 中是支持的。

2.Oracle 没有表别名 AS

为了让 SQL 查询语句更简洁，我们经常会使用表别名 AS，不过在 Oracle 中是不存在 AS 的，使用表别名的时候，直接在表名后面写上表别名即可，比如 player p，而不是 player AS p。

3.SQLite 的外连接只有左连接

SQLite 是一款轻量级的数据库软件，在外连接上只支持左连接，不支持右连接，不过如果你想使用右连接的方式，比如table1 RIGHT JOIN table2，在 SQLite 你可以写成table2 LEFT JOIN table1，这样就可以得到相同的效果。

除了一些常见的语法问题，还有一些关于连接的性能问题需要你注意：

1. 控制连接表的数量

多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。

2. 在连接时不要忘记 WHERE 语句

多表连接的目的不是为了做笛卡尔积，而是筛选符合条件的数据行，因此在多表连接的时候不要忘记了 WHERE 语句，这样可以过滤掉不必要的数据行返回。

3. 使用自连接而不是子查询

我们在查看比布雷克·格里芬高的球员都有谁的时候，可以使用子查询，也可以使用自连接。一般情况建议你使用自连接，因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。你可以这样理解：子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表进行条件判断，因此在大部分 DBMS 中都对自连接处理进行了优化。

总结

连接可以说是 SQL 中的核心操作，通过两篇文章的学习，你已经从多个维度对连接进行了了解。同时，我们对 SQL 的两个重要标准 SQL92 和 SQL99 进行了学习，在我们需要进行外连接的时候，建议采用 SQL99 标准，这样更适合阅读。

此外我还想强调一下，我们在进行连接的时候，使用的关系型数据库管理系统，之所以存在关系是因为各种数据表之间存在关联，它们并不是孤立存在的。在实际工作中，尤其是做业务报表的时候，我们会用到 SQL 中的连接操作（JOIN），因此我们需要理解和熟练掌握 SQL 标准中连接的使用，以及不同 DBMS 中对连接的语法规范。剩下要做的，就是通过做练习和实战来增强你的经验了，做的练习多了，也就自然有感觉了。

练习

编写 SQL 查询语句，查询不同身高级别（对应 height_grades 表）对应的球员数量（对应 player 表）

参考：

SELECT height_level,COUNT(height_level)
FROM player JOIN height_grades
ON player.height BETWEEN height_grades.height_lowest AND height_grades.height_highest
GROUP BY height_level;

SELECT height_level, COUNT(player_name) AS player_num 
FROM player AS p LEFT JOIN height_grades AS h ON p.height BETWEEN h.height_lowest AND h.height_highest 
GROUP BY height_level;

答疑里：

完整的SELECT语句内部执行顺序是：

  1. FROM子句组装数据（包括通过ON进行连接）
  2. WHERE子句进行条件筛选
  3. GROUP BY分组
  4. 使用聚集函数进行计算；
  5. HAVING筛选分组；
  6. 计算所有的表达式；
  7. SELECT 的字段；
  8. ORDER BY排序
  9. LIMIT筛选