java基础巩固-宇宙第一AiYWM：为了维持生计，MySQL基础Part2(MVCC、存储过程、触发器、编码方式及字符集、多表连接，内外连接，子查询、自联结、视图、设计DB、三范式)~整起-CSDN博客

本文链接：https://blog.csdn.net/m0_52436398/article/details/123632103

PART1:🕴MVCC（Multiversion Concurrency Control,多版本并发控制，一个行记录可能不止有一个快照数据（快照数据就是当前数据之前的历史版本），所以由此引发的并发控制叫做MVCC）：可以视为行级锁的一个变种),但是不同的是MVCC在很多时候避免了加锁操作（就是因为额外保存了开始和结束这两个系统版本号）)，所以通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）

无论何时，只要有多个查询需要在同一时刻修改数据，都会产生并发控制的问题。MVCC是现代数据库(包括 MySQL、Oracle、PostgreSQL 等)引擎实现中常用的处理读写冲突的手段，目的在于提高数据库高并发场景下的吞吐性能。
- 在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
  - 由于 MVCC 的存在，对于一般的 SELECT 语句，InnoDB 不会加任何锁
- 同时MVCC还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题。
  - InnoDB存储引擎在 RR 级别下【为什么要单独说这呢，因为RR是InnoDB是MySQL的默认隔离级别，而这个默认隔离级别已经把脏读和不可重复读解决了，但是幻读只有在串行化这种分布式下或者说隔离级别最高的情境中采用，InnoDB不用他，所以InnoDB得想其他办法解决幻读，MVCC+Next-Key Lock这不就是方法嘛】通过 MVCC和 Next-key Lock 来解决幻读问题：
    - 执行普通 select，此时会以 MVCC 快照读的方式读取数据
      - 在快照读的情况下，RR 隔离级别只会在事务开启后的第一次查询生成 Read View ，并使用至事务提交。所以在生成 Read View 之后其它事务所做的更新、插入记录版本对当前事务并不可见，实现了可重复读和防止快照读下的 “幻读”
    - 执行 select…for update/lock in share mode、insert、update、delete 等当前读
      - 在当前读下，读取的都是最新的数据，如果其它事务有插入新的记录，并且刚好在当前事务查询范围内，就会产生幻读！InnoDB 使用 Next-key Lock 来防止这种情况。当执行当前读时，会锁定读取到的记录的同时，锁定它们的间隙，防止其它事务在查询范围内插入数据。只要我不让你插入，就不会发生幻读
- MVCC只在读取已提交和可重复读两种隔离级别下有作用
  - 在事务隔离级别 RC 和 RR （InnoDB 存储引擎的默认事务隔离级别）下，InnoDB 存储引擎使用 MVCC（非锁定一致性读），但它们生成 Read View 的时机却不同：
    - 在 RC 隔离级别下的 每次select 查询前 都生成一个Read View (m_ids 列表)并设置新的 Read View，所以导致不可重复读
    - 在 RR 隔离级别下 只在事务开始后第一次select 查询或者说读取数据前生成 一个Read View（m_ids 列表）
MVCC特点：
- 所以开销很低.MVCC 最大的好处是读不加锁，读写不冲突，极大地增加了 MySQL 的并发性
  - 什么意思呢，说具体点就是，最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度【在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能】。
    - MVCC能解决读写、写写之间的并发线程安全问题，数据库并发场景有三种，分别为:
      - 读-读:不存在任何问题，也不需要并发控制
      - 读-写:有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读
      - 写-写:有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失
- 通过 MVCC，保证了事务 ACID 中的 I（隔离性）特性。
  - MVCC只在REPEATABLE READ和READ COMMITTED两个隔离级别下工作（相当于在事务隔离级别READ COMMITTED和REPEATABLE READ (InnoDB存储引擎的默认事务隔离级别)下，InnoDB存储引擎使用非锁定的一致性读。），其余两个隔离级别都和MVCC不兼容
    - InnoDB和XtraDB存储引擎通过MVCC解决幻读问题
- 每开始一个事务，系统版本号就会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较
MVCC实现原理，以InnoDB中的MVCC为例【MVCC没有一个统一的实现标准，Oracle、PostgreSQL、Mysql等数据库都实现了MVCC,虽然各自实现机制不同，但大都是非阻塞的读操作，写操作也只锁定必要的行（通过保存数据在某个时间点的快照来实现的（意思就是不管需要执行多长时间，同一时间每个事务看到的数据都是一样的。因为事务开始后随着开始时间不同，每个事务对同一张表在同一时刻看到的数据可能是不一样的，所以需要保存一下数据在某个时间点的快照））】
- MVCC实现原理大致是:InnoDB 每一行数据都有一个隐藏的回滚指针，用于指向该行修改前的最后一个历史版本，这个历史版本存放在 undo log 中。如果要执行更新操作，会将原记录放入 undo log 中，并通过隐藏的回滚指针指向 undo log 中的原记录。其它事务此时需要查询时，就是查询 undo log 中这行数据的最后一个历史版本
- MVCC 的实现依赖于：隐藏字段、Read View、undo log。
  - 在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性，如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改
    - 在 InnoDB 存储引擎中，创建一个新事务后，执行每个 select 语句前，都会创建一个快照（Read View），快照中保存了当前数据库系统中正处于活跃（没有 commit）的事务的 ID 号。其实简单的说保存的是系统中当前不应该被本事务看到的其他事务 ID 列表（即 m_ids）。当用户在这个事务中要读取某个记录行的时候，InnoDB 会将该记录行的 DB_TRX_ID 与 Read View 中的一些变量及当前事务 ID 进行比较，判断是否满足可见性条件
  - undo log
    - undo log主要有两个作用：
      - 当事务回滚时用于将数据恢复到修改前的样子
      - 另一个作用是 MVCC ，当读取记录时，若该记录被其他事务占用或当前版本对该事务不可见，则可以通过 undo log 读取之前的版本数据，以此实现非锁定读
    - 在 InnoDB 存储引擎中 undo log 分为两种： insert undo log 和 update undo log：
      - insert undo log ：指在 insert 操作中产生的 undo log。因为 insert 操作的记录只对事务本身可见，对其他事务不可见，故该 undo log 可以在事务提交后直接删除。不需要进行 purge 操作
      - update undo log ：update 或 delete 操作中产生的 undo log。该 undo log可能需要提供 MVCC 机制，因此不能在事务提交时就进行删除。提交时放入 undo log 链表【不同事务或者相同事务的对同一记录行的修改，会使该记录行的 undo log 成为一条链表，链首就是最新的记录，链尾就是最早的旧记录】，等待 purge线程进行最后的删除
  - Read View：Read View用来实现了四种隔离级别中的读提交和可重复读，点这里去看
```
class ReadView {
  /* ... */
private:
  trx_id_t m_low_limit_id;      /* 大于等于这个 ID 的事务均不可见 */
  trx_id_t m_up_limit_id;       /* 小于这个 ID 的事务均可见 */
  trx_id_t m_creator_trx_id;    /* 创建该 Read View 的事务ID */
  trx_id_t m_low_limit_no;      /* 事务 Number, 小于该 Number 的 Undo Logs 均可以被 Purge */
  
  ids_t m_ids;                  /* 创建 Read View 时的活跃事务列表 */
  m_closed;                     /* 标记 Read View 是否 close */
}
```
    - Read View主要是用来做可见性判断，里面保存了 “当前对本事务不可见的其他活跃事务”
    - Read View 有四个重要的字段：
      - m_ids ：指的是在创建 Read View 时，当前数据库中活跃事务的事务 id 列表，注意是一个列表，“活跃事务”指的就是，启动了但还没提交的事务。
        创建 Read View时，将当前未提交事务 ID 记录下来，后续即使它们修改了记录行的值，对于当前事务也是不可见的。m_ids 不包括当前事务自己和已提交的事务（正在内存中）
      - min_trx_id ：指的是在创建 Read View 时，当前数据库中活跃事务中事务 id 最小的事务，也就是 m_ids 的最小值。
        m_up_limit_id：活跃事务列表 m_ids 中最小的事务 ID，如果 m_ids 为空，则 m_up_limit_id 为 m_low_limit_id。小于这个 ID 的数据版本均可见
      - max_trx_id ：这个并不是 m_ids 的最大值，而是创建 Read View 时当前数据库中应该给下一个事务的 id 值，也就是全局事务中最大的事务 id 值 + 1；
        m_low_limit_id：目前出现过的最大的事务 ID+1，即下一个将被分配的事务 ID。大于等于这个 ID 的数据版本均不可见
      - creator_trx_id ：指的是创建该 Read View 的事务的事务 id。
  - 对于 InnoDB ，聚簇索引记录中包含 3 个隐藏的列【或者说 InnoDB存储引擎在数据库每行数据的后面添加了三个字段】
    - ROW ID：隐藏的自增 ID，如果表没有主键且该表没有唯一非空索引时，InnoDB 会自动按 ROW ID 产生一个聚集索引树。
      - 在 MVCC 协议下，每个读操作会看到一个一致性的快照，这个快照是基于整个库的，并且可以实现非阻塞的读，用于支持读提交和可重复读隔离级别的实现，MVCC 允许数据具有多个版本，这个版本可以是时间戳或者是全局递增的事务 ID，在同一个时间点，不同的事务看到的数据是不同的，这个修改的数据是记录在 undolog 中的。
      - 一个列保存行的创建时间（不是实际的时间值而是系统的版本号）
        每开始一个事务，系统版本号就会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较
    - 事务 ID：记录最后一次修改该记录的事务 ID【trx_id，当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里】
      - DB_TRX_ID（6字节）表示最后一次插入或更新该行的事务 id。此外，delete 操作在内部被视为更新，只不过会在记录头 Record header 中的 deleted_flag 字段将其标记为已删除
      - 一个列保存行的过期时间（或者删除时间）（不是实际的时间值而是系统的版本号）
        每开始一个事务，系统版本号就会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较
      - 一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几种情况：
        如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。
        如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。
        如果记录的 trx_id 值在 Read View 的 min_trx_id 和 max_trx_id 之间，需要判断 trx_id 是否在 m_ids 列表中：
        如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。
        如果记录的 trx_id 不在 m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。
    - 回滚指针：指向这条记录的上一个版本【roll_pointer，每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中，然后这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录。】
      - DB_ROLL_PTR（7字节）即回滚指针，指向该行的 undo log 。如果该行未被更新，则为空
      - InnoDB 每一行数据都有一个隐藏的回滚指针，用于指向该行修改前的最后一个历史版本，这个历史版本存放在 undo log 中。如果要执行更新操作，会将原记录放入 undo log 中，并通过隐藏的回滚指针指向 undo log 中的原记录。其它事务此时需要查询时，就是查询 undo log 中这行数据的最后一个历史版本。
- 举个例子，session2 查询的结果是 session1 修改之前的记录，这个记录就是来自 undolog 中
- InnoDB四个操作，的实现：
一致性非锁定读：一致性的非锁定读(consistent nonlocking read)是指InnoDB存储引擎通过行多版本控制( multi versioning)MVCC的方式来读取当前执行时间数据库中行的数据。
- 对于一致性非锁定读（Consistent Nonlocking Reads）的实现，通常做法是加一个版本号或者时间戳字段，在更新数据的同时版本号 + 1 或者更新时间戳。查询时，将当前可见的版本号与对应记录的版本号进行比对，如果记录的版本小于可见版本，则表示该记录可见
  - 在 InnoDB 存储引擎中，多版本控制 (multi versioning) 就是对非锁定读的实现
    - 【如果读取的行正在执行 DELETE 或 UPDATE 操作，这时读取操作不会去等待行上锁的释放。相反地，InnoDB 存储引擎会去读取行的一个快照数据，对于这种读取历史数据的方式，我们叫它快照读 (snapshot read)】
      - 这个实现是通过undo段来完成。而undo用来在事务中回滚数据，因此快照数据本身是没有额外的开销。此外，读取快照数据是不需要上锁的，因为没有事务需要对历史的数据进行修改操作。
        相当于在事务隔离级别READ COMMITTED和REPEATABLE READ (InnoDB存储引擎的默认事务隔离级别)下，InnoDB存储引擎使用非锁定的一致性读。
        但是，要注意，在READ COMMITTED事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。
        而在REPEATABLE READ事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。
    - 在 Repeatable Read 和 Read Committed 两个隔离级别下，如果是执行普通的 select 语句（不包括 select ... lock in share mode ,select ... for update）则会使用一致性非锁定读（MVCC）。并且在 Repeatable Read 下 MVCC 实现了可重复读和防止部分幻读
- 非锁定读：意思就是不需要等待访问的行上X锁的释放（排他锁（是一种行级锁，X Lock），InnoDB存储引擎的两种行级锁中的一种）
  - 非锁定读机制极大地提高了数据库的并发性。在InnoDB存储引擎的默认设置下，这是默认的读取方式，即读取不会占用和等待表上的锁。但是在不同事务隔离级别下，读取的方式不同，并不是在每个事务隔离级别下都是采用非锁定的一致性读。此外，即使都是使用非锁定的一致性读，但是对于快照数据的定义也各不相同。
  - 锁定读：在某些情况下，用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性。而这要求数据库支持加锁语句，即使是对于SELECT的只读操作（意思就是按理来说，一般读操作可以并发，不需要加锁来维持安全性等，但是有时候，有意外咱也没办法）。【InnoDB存储引擎对于SELECT语句支持几种一致性的锁定读(locking read)操作:必须在一个事务中，当事务提交了,锁也就释放了。因此在使用下面几句SELECT锁定语句时，务必加上BEGIN, START TRANSACTION或者SET AUTOCOMMIT=0。】。或者说，如果执行的是下列语句，就是锁定读（Locking Reads）
    - SELECT…FOR UPDATE:
      - SELECT…FOR UPDATE对读取的行记录加一个X锁，其他事务不能对已锁定的行加上任何锁。
        这是拿来和一致性非锁定读作比较的。**在一致性非锁定读下，即使读取的记录已被其它事务加上 X 锁，这时记录也是可以被读取的，即读取的快照数据。**上面说了，在 Repeatable Read 下 MVCC 防止了部分幻读，这边的 “部分” 是指在一致性非锁定读情况下，只能读取到第一次查询之前所插入的数据（根据 Read View 判断数据可见性，Read View 在第一次查询时生成）。但是！如果是当前读，每次读取的都是最新数据，这时如果两次查询中间有其它事务插入数据，就会产生幻读。所以， InnoDB 在实现Repeatable Read 时，如果执行的是当前读，则会对读取的记录使用 Next-key Lock ，来防止其它事务在间隙间插入数据
    - SELECT…LOCK IN SHARE MODE
      - SELECT…LOCK IN SHARE MODE对读取的行记录加一个S锁，其他事务可以向被锁定的行加S锁，但是如果加X锁，则会被阻塞。对于一致性非锁定读，即使读取的行已被执行了SELECT…FOR UPDATE,也是可以进行读取的。
    - insert、update、delete 操作
      - 和SELECT…FOR UPDATE一样也是对读取的行记录加一个X锁，其他事务不能对已锁定的行加上任何锁。

PART2:存储结构或者存储过程(简单来说，就是为以后的使用而封装出来一个或者说保存的一条或多条MySQL语句的集合，是一些预编译的 SQL 语句。存储过程可以说是一个记录集，存储结构或者存储过程是由一些 T-SQL 语句组成的代码块，这些 T-SQL 语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。存储过程是一个预编译的代码块，执行效率比较高,一个存储过程替代大量 T_SQL 语句，可以降低网络通信量，提高通信速率,可以一定程度上确保数据安全)。but，重点来了
在这里插入图片描述

我们可以把存储过程看成是一些 SQL 语句的集合，中间加了点逻辑控制语句。存储过程在业务比较复杂的时候是非常实用的，比如很多时候我们完成一个操作可能需要写一大串 SQL 语句，这时候我们就可以写有一个存储过程，这样也方便了我们下一次的调用。存储过程一旦调试完成通过后就能稳定运行，另外，使用存储过程比单纯 SQL 语句执行要快，因为存储过程是预编译过的。【阿里巴巴 Java 开发手册里要求禁止使用存储过程，是因为存储过程难以调试和扩展，而且没有移植性，还会消耗数据库资源。】
自己框图中翻翻即可：

再就是存储过程的优缺点：

PART3:触发器：
在这里插入图片描述
PART4:编码方式、多表连接、内外连接、子查询、自联结

表之间的关系
编码方式
- 字符集：一系列字符的集合【因为计算机只认识或者说只能存储二进制的数据，所以存在编解码。字符对应二进制数据的过程称为"字符编码"，反之，二进制数据解析成字符的过程称为“字符解码”。】
  - 不同的字符集的主要区别在于：可以表示的字符范围以及编码方式。【常见的字符集有 ASCII、GB2312、GBK、UTF-8…。】
    - ASCII 字符集：
      - 至今为止共定义了 128 个字符，其中有 33 个控制字符（比如回车、删除）无法显示。
      - 一个 ASCII 码长度是一个字节也就是 8 个 bit，比如“a”对应的 ASCII 码是“01100001”。不过，ASCII 字符集最高位是 0 仅仅作为校验位，其余 7 位使用 0 和 1 进行组合，所以，ASCII 字符集可以定义 128（2^7）个字符。后来，人们对其进行了扩展得到了 ASCII 扩展字符集。ASCII 扩展字符集使用 8 位（bits）表示一个字符，所以，ASCII 扩展字符集可以定义 256（2^8）个字符。
    - GB2312
      - GB2312 字符集是一种对汉字比较友好的字符集，共收录 6700 多个汉字，基本涵盖了绝大部分常用汉字。不过，GB2312 字符集不支持绝大部分的生僻字和繁体字。
      - 对于英语字符，GB2312 编码和 ASCII 码是相同的，1 字节编码即可。对于非英字符，需要 2 字节编码。
    - GBK
      - GBK 字符集可以看作是 GB2312 字符集的扩展，兼容 GB2312 字符集，共收录了 20000 多个汉字，GBK 中 K 是汉语拼音 Kuo Zhan（扩展）中的“Kuo”的首字母。
    - GB18030：GB18030 完全兼容 GB2312 和 GBK 字符集，纳入中国国内少数民族的文字，且收录了日韩汉字，是目前为止最全面的汉字字符集，共收录汉字 70000 多个。
    - BIG5：BIG5 主要针对的是繁体中文，收录了 13000 多个汉字。
    - Unicode & UTF-8编码：
      - 不同的字符集可以表示的字符范围以及编码规则存在差异，这就导致了一个非常严重的问题：使用错误的编码方式查看一个包含字符的文件就会产生乱码现象【乱码的本质：编码和解码时用了不同或者不兼容的字符集】。
      - Unicode：将世界上所有的字符都纳入其中的一种很牛B的字符集
        Unicode 字符集中包含了世界上几乎所有已知的字符。不过，Unicode 字符集只是包含了世界上几乎所有已知的字符并没有规定如何存储这些字符（也就是如何使用二进制数据表示这些字符）。然后，就有了 UTF-8（8-bit Unicode Transformation Format），类似的还有 UTF-16、 UTF-32。你Unicode不是光包含很多没有定义如何存储这些字符嘛，我们UTF-X家族来帮你定义就行呀
        UTF-8 使用 1 到 4 个字节为每个字符编码，UTF-8 可以根据不同的符号自动选择编码的长短，像 英文字符只需要 1 个字节就够了，这一点 ASCII 字符集一样。因此，对于英语字符，UTF-8 编码和 ASCII 码是相同的。
        UTF-16 使用 2 或 4 个字节为每个字符编码，UTF-32 的规则最简单，不过缺陷也比较明显，对于英文字母这类字符消耗的空间是 UTF-8 的 4 倍之多
        UTF-32 固定位 4 个字节为每个字符编码。
  - MySQL字符集
    - MySQL 支持很多种字符编码的方式，比如 UTF-8【通常情况下建议使用 UTF-8 作为默认的字符编码方式。】、GB2312、GBK、BIG5。
    - MySQL 字符编码集中有两套 UTF-8 编码实现：utf8 和 utf8mb4
      - utf8 ： utf8编码只支持1-3个字节。 在 utf8 编码中，中文是占 3 个字节，其他数字、英文、符号占一个字节。但 emoji 符号占 4 个字节，一些较复杂的文字、繁体字也是 4 个字节。
      - utf8mb4 ： UTF-8 的完整实现，正版！最多支持使用 4 个字节表示字符，因此，可以用来存储 emoji 符号【需要存储emoji类型的数据或者一些比较复杂的文字、繁体字到 MySQL 数据库的话，数据库的编码一定要指定为utf8mb4 而不是utf8 ，要不然存储的时候就会报错了。】
    - 如果使用 utf8 的话，存储emoji 符号和一些比较复杂的汉字、繁体字就会出错。
子查询
多表查询:在数据检索查询的执行中联结（join）表。
- 为什么要有联结表以及联查的出现呢。关于这个可以看看MySQL书上给出的例子：
  - 假如有一个包含产品目录的数据库表，其中每种类别的物品占一行。对于每种物品要存储的信息包括产品描述和价格，以及生产该产品的供应商信息三个。现在假如有由同一供应商生产的多种物品，那么在何处存储同一个供应商的不同信息（如，供应商名、地址、联系方法等）呢~答案是：将这些同一个供应商的不同数据信息与供应商对应的产品信息（也就是产品描述和价格）分开存储【关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值（即关系设计中的关系（relational））互相关联。】
    - 这个例子中就可建立两个表：
      - 一个存储供应商信息，每个供应商占一行，每个供应商具有唯一的标识，也就是主键（primary key），可以是供应商ID或任何其他唯一值。
      - 另一个存储产品信息，除了存储供应商ID外不存储其他供应商信息，此时供应商信息表的主键就叫做产品信息表的外键，就是这个外键将供应商信息表和产品信息表关联起来~外键看这里
    - 分开存储，有下面几个原因**：分解数据为多个表能更有效地存储，更方便地处理，并且具有更大的可伸缩性（能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好（scale well））
      - 因为同一供应商生产的每个产品的供应商信息都是相同的，对每个产品重复此信息既浪费时间又浪费存储空间。
      - 如果供应商信息改变（例如，供应商搬家或电话号码变动），只需改动一次即可。
      - 如果有重复数据（即每种产品都存储供应商信息），很难保证每次输入该数据的方式都相同。不一致的数据在报表中很难利用。而且相同数据出现多次决不是一件好事，
- 那数据都被分开存储在多个表中了，那咱么如何用单条SELECT语句检索出数据呢？答案是使用联结（将第一个表中的每一行与第二个表中的每一行配对）
  - 联结：联结是一种机制，用来在一条SELECT语句中关联表，然后多个表返回一组输出
  - 创建联结：
    - WHERE子句作为过滤条件，它只包含那些匹配给定条件（这里是联结条件）的行，就可以实现将第一个表中的每一行与第二个表中的每一行配对。（如果没有where或者不用where则第一个表中的每个行将与第二个表中的每个行配对，而不管它们逻辑上是否可以配在一起。）
      - 应该保证所有联结都有WHERE子句，否则MySQL将返回比想要的数据多得多的数据。同理，应该保证WHERE子句的正确性。不正确的过滤条件将导致MySQL返回不正确的数据
  - 使用联结的要点：
    - （join后面写欲连接的表哦，on后面写两个表之间联接的条件哦，用join on叫联接查询，用WHERE叫等值查询）
    - 一般我们使用内部联结，但使用外部联结也是有效的。
    - 保证使用正确的联结条件，也就是提供联结条件，否则会得出笛卡儿积（由没有联结条件的表关系返回
      的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。）
    - 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单。（这和咱们Java中写代码一样，你直接哼哧哼哧写了好多代码，一侧，哎呀，咋出错了，不就蒙了，咱们应该写一个小模块，打印点中间过程信息验证一下或者打印一下日志等，进行一下单元测试啥的。咱们之前SSM那里不就是，光说后端，咱们建好数据库及其表后，是不是该写dao层，那写了dao层接口后咱们是不是应该模拟一个子实现类，进行一下单元测试测一下咱们通过dao层能不能正确的CRUD，然后dao层好了之后是不是该测service了，然后到controller层）

联结四种分类：内连接（内部联结或者叫等值联结）、自联结、自然联结和外部联结

内连接（内部联结或者叫等值联结）
- ANSI SQL规范首选INNER JOIN语法。此外，尽管使用WHERE子句定义联结的确比较简单，但是使用明确的联结语法能够确保不会忘记联结条件，有时候这样做也能影响性能
- 事实上，迄今为止我们建立的每个内部联结都是自然联结，很可能我们永远都不会用到不是自然联结的内部联结。
- SQL对一条SELECT语句中可以联结的表的数目没有限制。创建联结的基本规则也相同。首先列出所有表，然后定义表之间的关系。但是MySQL在运行时关联指定的每个表以处理联结。这种处理可能是非常耗费资源的，因此应该仔细，不要联结不必要的表。联结的表越多，性能下降越厉害。

//以上面图片中，产品信息与供应商信息为例，咱们可以相互比较看看
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

在这里插入图片描述

使用表别名：也算是一种联结呀。别名除了用于列名和计算字段外，SQL还允许给表名起别名。
- 表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户机

//我感觉不管是这个dog是个列名还是表名，反正屁股后面跟个狗子，就是这个dog的别名了
SELECT dog AS 狗子
FROM...

自联结：为什么要有这个东西，不能说光人家书上说咱就死背下就完事了，可以从需求出发看看为啥我需要这个自联结，这是个什么玩意？还拿咱们上面产品和供应商两个表说事吧。假如，我发现某个物品有质量问题，我就会有点怀疑这个物品的生产厂家(也就是这个供应商供的物品是不是都有质量问题，所以我不得找到生产ID为xxx的物品对应的供应商，然后才能找到这个供应商对应的其他物品)，有两种方法可以找：
- 使用子查询：
- 使用自联结：例子中查询中需要的两个表实际上是相同的表，因此products表在FROM子句中出现了两次。这是完全合法的，但对products的引用具有二义性，因为MySQL不知道你引用的是products表中的哪个实例。所以咱们得使用表别名令products的第一次出现为别名p1，第二次出现为别名p2。（其实咱们也是必须得用表别名，如果不这样，MySQL将返回错误，因为分别存在两个名为prod_id、prod_name的列。MySQL不知道想要的是哪一个列（即使它们事实上是同一个列）。）
  - WHERE（通过匹配p1中的vend_id和p2中的vend_id）首先联结两个表，然后按第二个表中的prod_id过滤数据，返回所需的数据
  - 自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。咱们实际中应该试一下两种方法，以确定哪一种的性能更好。
自然联结：标准的联结（前一章中介绍的内部联结）返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。
- 咱们使用自然联结时只能选择那些唯一的列，一般是通过对表使用通配符（SELECT *），对所有其他表的列使用明确的子集来完成的。
- 事实上，迄今为止我们建立的每个内部联结都是自然联结，很可能我们永远都不会用到不是自然联结的内部联结。
外部联结：联结包含了那些在相关表中没有关联行的行。（有时候会需要包含没有关联行的那些行）这种类型的联结称为外部联结。
- 存在两种基本的外部联结形式：左外部联结和右外部联结。
  - 它们之间的唯一差别是所关联的表的顺序不同。换句话说，左外部联结可通过颠倒FROM或WHERE子句中表的顺序转换为右外部联结。因此，两种类型的外部联结可互换使用，而究竟使用哪一种纯粹是根据方便而定。
- 在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）

除了上面四种联结方式，还有下面：

聚集函数用来汇总数据，但这些函数也可以与联结一起使用
组合查询：MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）。
- 组合相同表的两个查询完成的工作与具有多个WHERE子句条件的单条查询完成的工作相同。这两种技术在不同的查询中性能也不同。因此咱们用之前应该试一下这两种技术，以确定对特定的查询哪一种性能更好。
- 咱们什么时候用组合查询呢，下面两种情况用：
  - 在单个查询中从不同的表返回类似结构的数据；
  - 对单个表执行多个查询，按单个查询返回数据
- 进行并时有几条规则需要注意。
  - UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）。
  - UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
  - 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。
  - UNION从查询结果集中自动去除了重复的行（换句话说，它的行为与单条SELECT语句中使用多个WHERE子句条件一样）
    - 这是UNION的默认行为，但是如果需要，可以改变它。事实上，如果想返回所有匹配行，可使用UNION ALL而不是UNION。使用UNION ALL，MySQL不取消重复的行。如果确实需要每个条件的匹配行全部出现（包括重复行），则必须使用UNION ALL而不是WHERE。
  - SELECT语句的输出用ORDER BY子句排序。在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条ORDER BY子句
  - 使用UNION的组合查询可以应用不同的表
- 创建组合查询：给出每条SELECT语句，在各条语句之间放上关键字UNION

SELECT vent_id, prod_id, prod_price
FROM  products
WHERE prod_price <= 5;//检索价格不高于5的所有物品

SELECT vent_id, prod_id, prod_price
FROM  products
WHERE prod_price IN (1001, 1002);//用IN找出供应商1001和1002生产的所有物品

SELECT vent_id, prod_id, prod_price
FROM  products
WHERE prod_price <= 5;//检索价格不高于5的所有物品
UNION //UNION指示MySQL执行两条SELECT语句，并把输出组合成单个查询结果集
SELECT vent_id, prod_id, prod_price
FROM  products
WHERE prod_price IN (1001, 1002);//用IN找出供应商1001和1002生产的所有物品

PART5：视图：一个虚拟的表可以当表来使用，但是有普通的（持久表）来比较，视图中的数据没有实际的物理存储(不存放任何的数据)，通过SQL语句查询返回的数据是MySQL从其他表中生成的。
在这里插入图片描述
逮到一个东西，肯定是发起灵魂拷问，这是个啥，这个能用来干啥，用它有啥优缺点…然后进一步再研究高级优化呗

其实视图这货，就是一个抽象出来的中间层，这咱不熟悉嘛，为了屏蔽底层给用户带来的观感上的复杂程度，咱们经常会搞出一个中间层呀，这里视图也一样，对于一些应用程序，程序本身不关心基表的结构，所以咱们搞出一个视图，到时候只需要按照视图定义来取数据或者更新数据。
创建视图的语法：将SELECT语句的结果存放到临时表中

但是上面这创建语法存在问题，咱们创建了一个id<10的视图，但是之后向视图中插入id=20的值此插入竟然成功了。所以为了防止这种情况出现，加了一句，如图：这样MySQL数据库会对更新视图插入数据进行检查，不满足则抛出异常不允许数据更新。
- SHOW TABLES:会把视图这个虚表作为表显示出来
  - 也可以只搜索表类型为BASE TABLE的基表，就不会查出视图这种虚表了。
Oracle数据库支持物化视图，视图就变成了根据基表实际存在的实表而不是虚表

PART6:设计DB、三范式
补充框图：三大范式（但是在我们的日常开发当中，并不是所有的表一定要满足三大范式，有时候冗余几个字段可以少关联几张表，带来的查询效率的提升有可能是质变的）
- 第一范式：数据库中的字段具有原子性，属性（对应于表中的字段）不可再分，并且是单一职责
  - 也就是这个字段只能是一个值，不能再分为多个其他的字段了。1NF 是所有关系型数据库的最基本要求，也就是说关系型数据库中创建的表一定满足第一范式
- 第二范式：建立在第一范式的基础上，要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。
  - 第二范式要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主键
  - 2NF 在 1NF 的基础之上，消除了非主属性对于码的部分函数依赖。如下图所示，展示了第一范式到第二范式的过渡。第二范式在第一范式的基础上增加了一个列，这个列称为主键，非主属性都依赖于主键。
    - 函数依赖（functional dependency）：若在一张表中，在属性（或属性组）X 的值确定的情况下，必定能确定属性 Y 的值，那么就可以说 Y 函数依赖于 X，写作 X → Y
- 第三范式：建立在第一，第二范式的基础上，确保每列都和主键列直接相关，而不是间接相关不存在其他表的非主键信息，任何非主属性不依赖于其它非主属性
  - 3NF 在 2NF 的基础之上，消除了非主属性对于码的传递函数依赖。符合 3NF 要求的数据库设计，基本上解决了数据冗余过大，插入异常，修改异常，删除异常的问题。比如在关系 R(学号 , 姓名, 系名，系主任)中，学号 → 系名，系名 → 系主任，所以存在非主属性系主任对于学号的传递函数依赖，所以该表的设计，不符合 3NF 的要求
在范式化的数据库中，每个事实数据会出现并且只出现一次。相反，在反范式化的数据库中，信息是冗余的，可能会存储在多个地方。以下面这个“雇员、部门、部门领导”为例，看一下范式。

此时可能会出现下面两个问题：

如果“Jones"这一行显示部门的领导跟“Brown"这行的不一样，就没有办法知道哪个是对的。这就像是有句老话说的:“一个人有两块手表就永远不知道时间”。
此外，这个设计在没有雇员信息的情况下就无法表示一个部门–如果我们删除了所有Accounting部门的雇员，我们就失去了关于这个部门本身的所有记录。要避免这个问题，我们需要对这个表进行范式化，方式就是拆分雇员和部门项。拆分之后就可以用下面两张表分别来存储雇员表，然后这样设计的两张表就符合第二范式
- 雇员表
- 部门表
范式的优缺点：
- 优点：
  - 范式化的更新操作通常比反范式化要快。
  - 当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。
  - 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。
  - 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY 语句。
  - 还是前面的例子:在非范式化的结构中必须使用DISTINCT或者GROUP BY 才能获得一份唯一的部门列表，但是如果部门(DEPARTMENT) 是一张单独的表，则只需要简单的查询这张表就行了。
- 缺点：通常需要关联。范式化设计的schema的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的schema.上都可能需要至少一次关联，也许更多。这不但代价昂贵，也可能使一些索引策略无效。例如，范式化可能将列存放在不同的表中，而这些列如果在一个表中本可以属于同一个索引。
数据库设计通常分为哪几步?在这过程中就跟咱们写java代码是一样的有点要注意的规范：
- 数据库命令规范
- 一些常用的设计规范
  - 没有特殊要求时所有表必须使用 InnoDB 存储引擎
  - 数据库和表的字符集统一使用 UTF8。兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效，如果数据库中有存储 emoji 表情的需要，字符集需要采用 utf8mb4 字符集。
  - 使用 comment 从句添加表和列的备注，从一开始就进行数据字典的维护
  - 尽量控制单表数据量的大小，建议控制在 500 万以内
    - 【500 万并不是 MySQL 数据库的限制，过大会造成修改表结构，备份，恢复都会有很大的问题。可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小。】
  - 谨慎使用 MySQL 分区表【分区表在物理上表现为多个文件，在逻辑上表现为一个表；】
    - 谨慎选择分区键，跨分区查询效率可能更低；
  - 经常一起使用的列放到一个表中，避免更多的关联操作
  - 禁止在表中建立预留字段，预留字段的命名很难做到见名识义。预留字段无法确认存储的数据类型，所以无法选择合适的类型。对预留字段类型的修改，会对表进行锁定。
  - 禁止在数据库中存储文件（比如图片）这类大的二进制数据
    - 文件（比如图片）这类大的二进制数据通常存储于文件服务器，数据库只存储文件地址信息
  - 不要被数据库范式所束缚
    - 设计关系数据库时需要满足第三范式，但为了满足第三范式，我们可能会拆分出多张表。而在进行查询时需要对多张表进行关联查询，有时为了提高查询效率，会降低范式的要求，在表中保存一定的冗余信息，也叫做反范式。但要注意反范式一定要适度
  - 禁止从开发环境,测试环境直接连接生产环境数据库，安全隐患极大，要对生产环境抱有敬畏之心！
  - 数据库字段设计规范
    - 优先选择符合存储需要的最小的数据类型，因为优先选择符合存储需要的最小的数据类型
    - 避免使用 TEXT,BLOB 数据类型，最常见的 TEXT 类型可以存储 64k 的数据。建议把 BLOB 或是 TEXT 列分离到单独的扩展表中。TEXT 或 BLOB 类型只能使用前缀索引
    - 避免使用 ENUM 类型
    - 尽可能把所有列定义为 NOT NUL，除非有特别的原因使用 NULL 值，应该总是让字段保持 NOT NULL。
      - 因为索引 NULL 列需要额外的空间来保存，所以要占用更多的空间，并且进行比较和计算时要对 NULL 值做特别的处理。
    - 同财务相关的金额类数据必须使用 decimal 类型