写在前面 - MySQL学习

1、针对开发工程师、DBA、运维

  • mysql服务器的安装配置
  • SQL编程(自定义函数、存储过程、触发器、定时任务)
  • 数据库索引建立
  • SQL语句优化
  • 数据库内部结构和原理
  • 数据库的性能监控分析与系统优化
  • 各种参数常量设定
  • 数据库建模优化
  • 主从复制
  • 分布式架构搭建、垂直切割和水平切割
  • MyCat
  • 数据迁移
  • 容灾备份和恢复
  • 对开源数据库进行二次开发

2、技术骨干、架构师则往往需要对底层原理吃透，数据库事务 ACID 是如何实现的？何时命中索引、何时不能，为什么？分布式场景下数据库怎么优化才能保持高性能

3、精通数据库。精通意味着：

第一形成知识网，更灵活地应对突发问题；

第二底层原理要懂，懂了才能更自由地应对复杂多变的业务场景。

4、对于知识，是需要 认真 和 讲究逻辑 的。希望这份认真、严谨在每个细节都能体会到。希望能感受到这种思维方式的美。具备优秀的思维能力 才是在未来可以迁移的能力，如果只是学习一些命令，则很快会过时， 思维能力 和 学习能力 的提升才是不会变的东西。

数据库概述

为什么要使用数据库

持久化(persistence)：把数据保存到可掉电式存储设备中以供之后使用。大多数情况下，特别是企业级应用，数据持久化意味着将内存中的数据保存到硬盘上加以”固化”，而持久化的实现过程大多 通过各种关系数据库来完成。

  • 持久化的主要作用是将内存中的数据存储在关系型数据库中，当然也可以存储在磁盘文件、XML数据文件中。

image-20220419121935522

DB & DBMS & SQL

DB：数据库（Database）即存储数据的“仓库”，其本质是一个文件系统。它保存了一系列有组织的数据。

DBMS：数据库管理系统（Database Management System）是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制。用户通过数据库管理系统访问数据库中表内的数据。

SQL：结构化查询语言（Structured Query Language）专门用来与数据库通信的语言。

数据库管理系统(DBMS)可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体用户的数据。

DBMS - rank

image-20220419122104302

RDBMS & 非RDBMS

NOSQL：

键值型数据库

文档型数据库

搜索引擎数据库

列式数据库

图形数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
+ RDBMS
	+ 关系模型上建立的数据库
        + 关系型数据库
    + 优势
        + 复杂查询
        + 事务支持
+ NOSQL
    + 更高的性能
    + 分类
        + kv
        + 文档型
        + 搜索引擎型
            + 倒排索引
        + 列式
            + 降低io次数
        + 图形
    + nosql

关系型数据库设计规则

1、表、记录、字段

E-R（entity-relationship，实体-联系）模型中有三个主要概念是： 实体集 、 属性 、 联系集 。 一个实体集（class）对应于数据库中的一个表（table），一个实体（instance）则对应于数据库表 中的一行（row），也称为一条记录（record）。一个属性（attribute）对应于数据库表中的一列 （column），也称为一个字段（field）。 

1
2
3
4
5
6
+ ORM（对象关系映射）思想
    + 数据库中的一个表 —— java一个类
    + 数据 —— 对象
    + 列 —— 字段、属性
+ E-R模型
    + 实体-联系

2、表的关联关系

表与表之间的数据记录有关系(relationship)。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。

四种：一对一关联、一对多关联、多对多关联、自我引用

1
2
3
4
5
6
7
8
9
10
11
12
+ 表的关联关系
    + 一对一关联
        + 可合为一张表
    + 一对多关联
        + 主表的主键作为从表的字段
        + 属性可重复
    + 多对多关联	
        + 必须创建第三个表
            + 字段：包含两个表的主键
    + 自我引用
        + 字段引用其他字段
            + 体现层级关系

基本的SELECT语句

SQL概述

SQL 有两个重要的标准，分别是 SQL92 和 SQL99，它们分别代表了 92 年和 99 年颁布的 SQL 标准，我们今天使用的 SQL 语言依然遵循这些标准。

SQL语言在功能上主要分为如下3大类：

  • DDL（Data Definition Languages、数据定义语言），这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。主要的语句关键字包括 CREATE 、 DROP 、 ALTER 等。

  • DML（Data Manipulation Language、数据操作语言），用于添加、删除、更新和查询数据库记录，并检查数据完整性。主要的语句关键字包括 INSERT 、 DELETE 、 UPDATE 、 SELECT 等。 SELECT是SQL语言的基础，最为重要。

  • DCL（Data Control Language、数据控制语言），用于定义数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等。

因为查询语句使用的非常的频繁，所以很多人把查询语句单拎出来一类：DQL（数据查询语言）。

还有单独将 COMMIT 、 ROLLBACK 取出来称为TCL （Transaction Control Language，事务控制语言）。 

1
2
3
4
5
6
7
8
9
10
+ sql分类
    + DDL数据定义语言
        + 创建、删除、修改等
    + DML数据操作语言
        + 增、删、改、查
    + DCL数据控制语言
        + 访问、安全
+ SELECT重中之重
+ 大处着眼，小处着手
+ 眼界很重要，也不能眼高手低

SQL语言的规则与规范

MySQL 在 Windows 环境下是大小写不敏感的

MySQL 在 Linux 环境下是大小写敏感的

  • 数据库名、表名、表的别名、变量名是严格区分大小写的

  • 关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。

推荐采用统一的书写规范：

  • 数据库名、表名、表别名、字段名、字段别名等都小写

  • SQL 关键字、函数名、绑定变量等都大写

基本的SELECT语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
+ 列的别名
+ 去除重复行
    + distinct
+ 空值
    + NULL
    + 不参与运算	
        + IFNULL解决
+ 着重号
    + ``飘
    + 解决name（字段、表名）和函数冲突问题
+ 查询常数
    + 加入常数列
+ 显示表结构
    + describe、desc
    + 表字段信息
+ WHERE

显示表结构

使用DESCRIBE 或 DESC 命令，表示表结构。

1
2
3
DESCRIBE employees; 
#或
DESC employees;

运算符

算术运算符

算术运算符主要用于数学运算，其可以连接运算符前后的两个数值或表达式，对数值或表达式进行加（+）、减（-）、乘（*）、除（/）和取模（%）运算。

比较运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回NULL。

比较运算符经常被用来作为SELECT查询语句的条件来使用，返回符合条件的结果记录。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
+ 符号类型运算符
    + 安全等于
        + 解决跟null比较的问题
        + 两个null为1
    + 不等于：<>、!=
    + 是否为NULL
        + IS NULL、ISNULL
    + 最小的、最大的
        + LEAST()、GREATEST
    + 区间
        + BETWEEN...AND...
    + 在离散集合中
        + IN
    + 模糊查询
        + LIKE
            + % 不确定个数字符
            + _代表一个不确定的字符
                + 有几个下划线代表几个字符
                + 查询带_的字符：加上\转义字符
                    + 自定义转移字符：ESCAPE
    + 正则表达式
        + REGEXP
        + RLIKE

逻辑运算符

逻辑运算符主要用来判断表达式的真假，在MySQL中，逻辑运算符的返回结果为1、0或者NULL。

逻辑非、逻辑与、逻辑或、逻辑异或。

1
2
3
4
5
6
7
8
9
10
+ NOT
+ AND
+ OR
+ XOR
    + 异或：两边不一样为真
+ and优先级比or高
    + 先算and
+ 运算符的优先级
    + 遇事不决，小括号
+ 位运算符

排序与分页

排序

1、使用 ORDER BY 子句排序

ASC（ascend）: 升序

DESC（descend）:降序

2、可以使用不在SELECT列表中的列排序。

在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。

1
2
3
4
5
+ 排序
    + 默认为数据库先后添加顺序
+ ORDER BY
    + 可以使用别名
+ 二级排序

分页

1、LIMIT [位置偏移量,] 行数

第一个位置偏移量参数指示MySQL从哪一行开始显示，是一个可选参数，如果不指定位置偏移量，将会从表中的第一条记录开始（第一条记录的位置偏移量是0，第二条记录的位置偏移量是1，以此类推）；

第二个参数“行数”指示返回的记录条数。

2、约束返回结果的数量可以减少数据表的网络传输量，也可以提升查询效率。

如果我们知道返回结果只有1 条，就可以使用 LIMIT 1 ，告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回。

1
2
3
4
5
6
7
8
9
10
11
+ 偏移量，分页数
+ 声明顺序
    + WHERE
    + ORDER BY
    + LIMIT
+ 8.0新特性
    + LIMIT ... OFFSET ...
    + LIMIT 条目 OFFSET 偏移量
+ LIMIT 1
    + 取第一条数据
+ 其他数据库异同

多表查询

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。

前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。

写在前面

1
2
3
4
5
6
7
8
9
10
11
12
13
+ 为什么需要多表查询？
    + 分开查询太麻烦
    + 合成一张表
    + 数据冗余
    + IO次数变多
    + 事务问题
+ 笛卡尔积的的错误
    + 没有进行连接
+ 多表查询
    + 加入连接条件
    + 指明表的字段
+ 设置表的别名
+ n个表至少n-1个连接条件

多表查询分类

等值连接 vs 非等值连接、自连接 vs 非自连接 

1
2
3
4
5
6
7
8
9
+ 多表查询的分类
    + 等值连接
    + 自连接
    + 内连接
+ 等值连接和非等值连接
    + 判断工资等级
+ 判断字段处于另一个表的区间
    + 自连接和非自连接
    + 树形结构查询

内连接 vs 外连接

  • 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行

  • 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的 行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。

如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表 。 

1
2
3
4
5
6
7
8
9
10
11
12
+ 外连接
    + 左外连接
    + 右外连接
    + 全外连接
+ 常用sql标准
+ SQL92外连接
    + Mysql不支持
+ SQL99语法：JOIN ... ON
    + 内连接 INNER
    + 外连接	 LEFT、RIGHT OUTER
        + 满外连接
            + FULL OUTER Mysql不支持

7种SQL JOINS的实现

image-20220425143537469

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#中图：内连接 
A∩B SELECT employee_id,last_name,department_name FROM employees e JOIN departments d ON e.`department_id` = d.`department_id`; 
#左上图：左外连接 
SELECT employee_id,last_name,department_name FROM employees e LEFT JOIN departments d ON e.`department_id` = d.`department_id`; 
#右上图：右外连接 
SELECT employee_id,last_name,department_name FROM employees e RIGHT JOIN departments d ON e.`department_id` = d.`department_id`; 
#左中图：A - A∩B 
SELECT employee_id,last_name,department_name FROM employees e LEFT JOIN departments d ON e.`department_id` = d.`department_id` WHERE d.`department_id` IS NULL 
#右中图：B - A∩B 
SELECT employee_id,last_name,department_name FROM employees e RIGHT JOIN departments d ON e.`department_id` = d.`department_id` WHERE e.`department_id` IS NULL
#左下图：满外连接 
#左中图 + 右上图 A∪B 
SELECT employee_id,last_name,department_name FROM employees e LEFT JOIN departments d ON e.`department_id` = d.`department_id` WHERE d.`department_id` IS NULL UNION ALL #没有去重操作，效率高 
SELECT employee_id,last_name,department_name FROM employees e RIGHT JOIN departments d ON e.`department_id` = d.`department_id`; 
#右下图 
#左中图 + 右中图 A∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B） 
SELECT employee_id,last_name,department_name FROM employees e LEFT JOIN departments d ON e.`department_id` = d.`department_id` WHERE d.`department_id` IS NULL UNION ALL SELECT employee_id,last_name,department_name FROM employees e RIGHT JOIN departments d ON e.`department_id` = d.`department_id` WHERE e.`department_id` IS NULL
1
2
3
4
5
6
7
8
9
10
11
12
+ UNION的使用
    + 能用UNION ALL就不用UNION
+ 7种JOIN的实现
    + 中图：内连接
    + 左上：左外连接
    + 右上：右外连接
    + 左中：左外连接 + 右为NULL
    + 右中：右外连接 + 左为NULL
    + 左下（全外）
    + 左上 UNION ALL 右中
    + 右上 UNION ALL 左中
    + 右下：左中 UNION ALL 右中

NATURAL JOIN 与 USING 的使用

SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中所有相同的字段 ，然后进行 等值 连接 。

当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的 同名字段进行等值连接。但是只能配合JOIN一起使用。

1
2
3
4
5
6
7
8
+ 自然连接
    + 自动查找相同字段进行等值连接
+ USING连接
    + 字段名称一样时简写
+ 小结
    + WHERE
    + ON 推荐
    + USING

单行函数

分类

MySQL提供的内置函数从 实现的功能角度 可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里，内置函数再分为两类： 单行函数 、 聚合函数。

  • 单行函数：操作数据对象、接受参数返回一个结果、只对一行进行变换、每行返回一个结果、可以嵌套参数、可以是一列或一个值
1
2
3
4
5
6
+ 什么是函数
    + 提高效率和维护性
    + 内置函数和自定义函数
+ 不同DBMS有差异
+ MYSQL内置函数
    + 单行函数、聚合函数

数值&字符串函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
+ 数值函数
    + 基本函数
    + 角度与弧度换算
    + 三角函数
    + 指数和对数
    + 进制间的转换
+ 字符串函数
    + askii码
    + 字符数CHAR_LENGTH、字节数
    + 连接 CONCAT，连接 + 分隔
    + 插入替换 INSERT，全部替换 REPLACE
    + 索引从1开始
    + 大写、小写 UPPER、LOWER
    + 取左边、右边 LEFT RIGHT
    + 左对齐，右对齐 LPAD、RPAD，以xx补齐特定长度
    + 去除首、尾的空格、字符 TRIM
    + 重复 REPEAT
    + 比较 STRCMP
    + 截取 SUBSTR
    + 位置 LOCATE
    + 取后面数组的字符 ELT、FIELD，首次位置 FIND_IN_SET
    + 翻转 REVERSE
    + 判断相等 NULLIF

日期和时间函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
+ 日期时间函数
    + 获取当前时间
    + 年份、季度、月份、星期、天
    + 时、分、秒
    + 日期与时间戳转换
    + 获取时间戳
    + 时间戳转换、还原
    + 日期与秒数转换
    + 计算日期和时间
    + 时间加减
    + 时间间隔
    + 日期格式化与解析
    + 格式化：日期——>字符串
    + 解析：字符串——>日期

流程控制函数

流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。

1
2
3
4
5
6
7
8
+ 流程控制函数
    + IF(case,value,value)
    + IFNULL
    + CASE WHEN
    + if else
    + CASE xx WHEN
    + switch
    + 自带循环

加密解密&信息&其他函数

加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。这些函数在保证数据库安全时非常有用。

MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
+ 加密函数
    + MD5
    + SHA
    + ENCODE
    + DECODE
+ mysql信息函数
    + 版本号
    + 连接数
    + 用户
    + 字符集
+ 其他函数
    + 四舍五入
    + 进制转换
    + IP转换
    + 测试时间
    + 修改字符编码

聚合函数

写在前面

聚合函数作用于一组数据，并对一组数据返回一个值。

聚合函数不能嵌套调用。比如不能出现类似“AVG(SUM(字段名称))”形式的调用。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
+ 聚合函数
    + 输入一堆值，返回一个值
+ 常用聚合函数
    + AVG、SUM
        + 平均数、和
    + MAX、MIN
        + 最大、最小值
        + 字符串也可以比较
    + COUNT
        + 作用：计算指定字段在查询结构中出现的个数，不计算NULL
        + 统计记录个数，建议使用COUNT(*)和COUNT(1)
        + 注意NULL情况
    + 其他
        + 方差、标准差、中位数
+ 认知能力
    + 对下层降维打击
    + 提升层次
    + 看到更广阔的领域
+ 人与人的差别太大：各个方面

GROUP BY

image-20220427134341809

1
2
3
4
5
6
7
8
9
10
+ 分组操作
    + 根据某一字段分组
+ 使用多个列分组
    + 前后顺序不同，效果一样
    + SELECT中非组函数的字段必须出现在GROUP中
        + 有组函数必须有GROUP BY
    + GROUP BY声明在FROM和WHERE后面、ORDER BY和LIMIT前面
    + 新特性：WITH ROLLUP
        + 又计算了整体的平均值
        + 不能和ORDER BY同时使用

HAVING

1、过滤分组：HAVING子句

1、行已经被分组。

2、使用了聚合函数。

3、满足HAVING 子句中条件的分组将被显示。

4、HAVING 不能单独使用，必须要跟 GROUP BY 一起使用

2、非法使用聚合函数 ： 不能在 WHERE 子句中使用聚合函数。

image-20220427134404009

1
2
3
4
5
6
7
8
9
10
 
+ HAVING：过滤分组（分组后）
    + 基本使用
        + 一旦过滤条件出现组函数，过滤条件必须用HAVING替换
        + HAVING必须声明在GROUP BY后面
        + 开发中，使用HAVING的前提是有GROUP BY
        + 普通过滤条件也可以写在HAVING
            + 推荐WHERE，执行效率高
            + 当过滤条件中有聚合函数，此过滤条件必须声明在HAVING中
            + 当过滤条件中没有聚合函数，此过滤条件声明在WHERE中更好

WHERE与HAVING的对比

1、区别1：WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；

HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。

这决定了，在需要对数据进行分组统计的时候，HAVING 可以完成 WHERE 不能完成的任务。这是因为，在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 WHERE 无法完成的。另外，WHERE排除的记录不再包括在分组中。

区别2：如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。

这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING 则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。

开发中的选择：

WHERE 和 HAVING 也不是互相排斥的，我们可以在一个查询里面同时使用 WHERE 和 HAVING。包含分组统计函数的条件用 HAVING，普通条件用 WHERE。这样，我们就既利用了 WHERE 条件的高效快速，又发挥了 HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很大的差别。

1
2
3
+ HAVING和WHERE对比	
    + HAVING范围更广
    + 如果过滤条件没有聚合函数，WHERE执行效率高

SQL底层执行&原理

SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：

1、首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1。

2、通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2。

3、添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。

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

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

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

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

首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表vt5-1 和 vt5-2 。

当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是 ORDER BY 阶段 ，得到虚拟表vt6 。

最后在 vt6 的基础上，取出指定行的记录，也就是 LIMIT 阶段 ，得到最终的结果，对应的是虚拟表vt7 。

当然我们在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。

同时因为 SQL 是一门类似英语的结构化查询语言，所以我们在写 SELECT 语句的时候，还要注意相应的关键字顺序，所谓底层运行的原理，就是我们刚才讲到的执行顺序。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
+ SQL底层执行原理
    + SELECT语句的完整结构
        + SQL92
            + SELECT ...（存在聚合函数）
            + FROM	 ...
            + WHERE ...（不包含聚合函数的过滤条件）
            + GROUP BY ...
            + HAVING ...（包含聚合函数的过滤条件）
            + ORDER BY ...
            + LIMIT ...
        + SQL99
            + SELECT ...（存在聚合函数）
            + FROM	 ...（LEFT/RIGHT）JOIN...ON...（多表的连接条件）
            + WHERE ...（不包含聚合函数的过滤条件）
            + GROUP BY ...
            + HAVING ...（包含聚合函数的过滤条件）
            + ORDER BY ...
            + LIMIT ...
        + SQL语句执行过程
            + FROM ... -> ON ... 连接条件 -> LEFT/RIGTH JOIN -> WHERE ... -> GROUP BY -> HAVING  -> （行数限制）
            + SELECT 选择字段 -> DISTINCT -> （列数限制）
            + ORDER BY -> LIMIT
        + HAVING在WHERE 和 OROUP BY 后面，所以效率慢
        + SELECT的别名只能在ORDER BY用

子查询

写在前面

1、子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。

SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

2、我们按内查询的结果返回一条还是多条记录，将子查询分为单行子查询 、 多行子查询 。

3、我们按内查询是否被执行多次，将子查询划分为 相关(或关联)子查询 和 不相关(或非关联)子查询 。

子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做不相关子查询。

同样，如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为相关子查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
+ 重点关注关联查询和子查询
+ 子查询
    + 子查询是指一个查询语句嵌套在另一个查询与内部的查询
    + 需要从一个表中获取数据结果，再根据结果进行比较
+ 方式1：多次查询
+ 方式2：自连接
+ 子查询
+ 使用
    + 先于主查询完成
    + 包括在括号内
    + 子查询放在比较条件的右侧
+ 子查询分类
    + 单行子查询 - 多行子查询
        + 子查询结果为单行&多行
    + 相关子查询 - 不相关子查询
        + 内查询是否被执行多次
        + 相关子查询
            + 内查询需要外查询条件

单行子查询

image-20220419180546105

1
2
3
4
5
6
7
8
9
10
11
12
13
+ 单行子查询
    + 单行比较操作符
        + 题目
            + 方法一：不成对比较
            + 方法二：成对查询（了解）
+ 编写技巧
    + 从里往外
    + 从外往里
+ HAVING中的子查询
+ CASE中的子查询
    + CASE xxx WHEN (SELECT)
+ 不确定的地方的都可以使用子查询
+ 非法使用子查询

多行子查询

也称为集合比较子查询

内查询返回多行

使用多行比较操作符

image-20220419180613665

1
2
3
4
5
6
7
8
9
10
11
+ 多行子查询
    + 内查询返回多行数据
+ 多行比较操作符
    + IN ANY ALL SOME(ANY)
+ IN
+ ALL/ANY
+ Mysql聚合函数不能嵌套
    + 可以替换表
    + 可以用ALL、ANY替换
+ 多行查询结果作为表
+ 空值问题

相关子查询

1、相关子查询执行流程

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询 。

相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询

image-20220427134445704

2、from型的子查询：子查询是作为from的一部分，子查询要用()引起来，并且要给这个子查询取别名， 把它当成一张“临时的虚拟的表”来使用。

3、EXISTS 与 NOT EXISTS关键字

关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。

如果在子查询中不存在满足条件的行：

  • 条件返回 FALSE
  • 继续在子查询中查找

如果在子查询中存在满足条件的行：

  • 不在子查询中继续查找
  • 条件返回 TRUE

NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

4、题目中可以使用子查询，也可以使用自连接。一般情况建议你使用自连接，因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。

可以这样理解：子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表进行条件判断，因此在大部分 DBMS 中都对自连接处理进行了优化。

1
2
3
4
5
6
7
8
9
10
11
+ 相关子查询
    + 内查询需要外查询的列
+ 内部使用外部表数据
+ 方式二：FROM使用子查询
    + 作为表进行连接
+ ORDER BY中的子查询
+ EXISTS、NOT EXISTS
    + 找到为TRUE的记录就停止查找
+ NOT EXISTS
    + 始终没有找到，返回TRUE
+ 更新删除操作，运用子查询