mysql 核心内容-上

1、SQL语句执行流程

MySQL大体上可分为Server层和存储引擎层两部分。

Server层：

连接器：TCP握手后服务器来验证登陆用户身份，A用户创建连接后，管理员对A用户权限修改了也不会影响到已经创建的链接权限，必须重新登陆。

查询缓存：查询后的结果存储位置，MySQL8.0版本以后已经取消，因为查询缓存失效太频繁，得不偿失。

分析器：根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。

优化器：多种执行策略可实现目标，系统自动选择最优进行执行。

执行器：判断是否有权限，将最终任务提交到存储引擎。

存储引擎层

负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB，它从MySQL 5.5.5版本开始成为了默认存储引擎(经常用的也是这个)。

SQL执行顺序

2、BinLog、RedoLog、UndoLog

BinLog

BinLog是记录所有数据库表结构变更（例如create、alter table）以及表数据修改(insert、update、delete)的二进制日志，主从数据库同步用到的都是BinLog文件。BinLog日志文件有三种模式。

STATEMENT 模式

内容：binlog 记录可能引起数据变更的 sql 语句

优势：该模式下，因为没有记录实际的数据，所以日志量很少 IO 都消耗很低，性能是最优的

劣势：但有些 *** 作并不是确定的，比如 uuid() 函数会随机产生唯一标识，当依赖 binlog 回放时，该 *** 作生成的数据与原数据必然是不同的，此时可能造成无法预料的后果。

ROW 模式

内容：在该模式下，binlog 会记录每次 *** 作的源数据与修改后的目标数据，StreamSets就要求该模式。

优势：可以绝对精准的还原，从而保证了数据的安全与可靠，并且复制和数据恢复过程可以是并发进行的

劣势：缺点在于 binlog 体积会非常大，同时，对于修改记录多、字段长度大的 *** 作来说，记录时性能消耗会很严重。阅读的时候也需要特殊指令来进行读取数据。

MIXED 模式

内容：是对上述STATEMENT 跟 ROW 两种模式的混合使用。

细节：对于绝大部分 *** 作，都是使用 STATEMENT 来进行 binlog 没有记录，只有以下 *** 作使用 ROW 来实现：表的存储引擎为 NDB，使用了uuid() 等不确定函数，使用了 insert delay 语句，使用了临时表

主从同步流程：

1、主节点必须启用二进制日志，记录任何修改了数据库数据的事件。

2、从节点开启一个线程（I/O Thread)把自己扮演成 mysql 的客户端，通过 mysql 协议，请求主节点的二进制日志文件中的事件 。

3、主节点启动一个线程（dump Thread），检查自己二进制日志中的事件，跟对方请求的位置对比，如果不带请求位置参数，则主节点就会从第一个日志文件中的第一个事件一个一个发送给从节点。

4、从节点接收到主节点发送过来的数据把它放置到中继日志（Relay log）文件中。并记录该次请求到主节点的具体哪一个二进制日志文件内部的哪一个位置（主节点中的二进制文件会有多个）。

5、从节点启动另外一个线程（sql Thread ），把 Relay log 中的事件读取出来，并在本地再执行一次。

mysql默认的复制方式是异步的，并且复制的时候是有并行复制能力的。主库把日志发送给从库后不管了，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个概念。

全同步复制

主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。

半同步复制

半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写 *** 作完成。

还可以延伸到由于主从配置不一样、主库大事务、从库压力过大、网络震荡等造成主备延迟，如何避免这个问题？主备切换的时候用可靠性优先原则还是可用性优先原则？如何判断主库Crash了？互为主备的情况下如何避免主备循环复制？被删库跑路了如何正确恢复？( o )… 感觉越来越扯到DBA的活儿上去了。

RedoLog

可以先通过下面demo理解：

饭点记账可以把账单写在账本上也可以写在粉板上。有人赊账或者还账的话，一般有两种做法：

1、直接把账本翻出来，把这次赊的账加上去或者扣除掉。

2、先在粉板上记下这次的账，等打烊以后再把账本翻出来核算。

生意忙时选后者，因为前者太麻烦了。得在密密麻麻的记录中找到这个人的赊账总额信息，找到之后再拿出算盘计算，最后再将结果写回到账本上。

同样在MySQL中如果每一次的更新 *** 作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程IO成本、查找成本都很高。而粉板和账本配合的整个过程就是MySQL用到的是Write-Ahead Logging 技术，它的关键点就是先写日志，再写磁盘。此时账本 = BinLog，粉板 = RedoLog。

1、 记录更新时，InnoDB引擎就会先把记录写到RedoLog（粉板）里面，并更新内存。同时，InnoDB引擎会在空闲时将这个 *** 作记录更新到磁盘里面。

2、 如果更新太多RedoLog处理不了的时候，需先将RedoLog部分数据写到磁盘，然后擦除RedoLog部分数据。RedoLog类似转盘。

RedoLog有write pos 跟checkpoint

write pos ：是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。

check point：是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

write pos和check point之间的是粉板上还空着的部分，可以用来记录新的 *** 作。如果write pos追上checkpoint，表示粉板满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint推进一下。

有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。 redolog两阶段提交：为了让binlog跟redolog两份日志之间的逻辑一致。提交流程大致如下：

1 prepare阶段 -->2 写binlog -->3 commit

当在2之前崩溃时，重启恢复后发现没有commit，回滚。备份恢复：没有binlog 。一致

当在3之前崩溃时，重启恢复发现虽没有commit，但满足prepare和binlog完整，所以重启后会自动commit。备份：有binlog. 一致

binlog跟redolog区别：

redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。

redo log是物理日志，记录的是在某个数据页上做了什么修改；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如给ID=2这一行的c字段加1。

redo log是循环写的，空间固定会用完；binlog是可以追加写入的。追加写是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

UndoLog

UndoLog 一般是逻辑日志，主要分为两种：

insert undo log

代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃

update undo log

事务在进行update或delete时产生的undo log不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

3、MySQL中的索引

索引的常见模型有哈希表、有序数组和搜索树。

哈希表：一种以KV存储数据的结构，只适合等值查询，不适合范围查询。

有序数组：只适用于静态存储引擎，涉及到插入的时候比较麻烦。可以参考Java中的ArrayList。

搜索树：按照数据结构中的二叉树来存储数据，不过此时是N叉树(B+树)。广泛应用在存储引擎层中。

B+树比B树优势在于：

B+ 树非叶子节点存储的只是索引，可以存储的更多。B+树比B树更加矮胖，IO次数更少。

B+ 树叶子节点前后管理，更加方便范围查询。同时结果都在叶子节点，查询效率稳定。

B+树中更有利于对数据扫描，可以避免B树的回溯扫描。

索引的优点：

1、唯一索引可以保证每一行数据的唯一性

2、提高查询速度

3、加速表与表的连接

4、显著的减少查询中分组和排序的时间

5、通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点：

1、创建跟维护都需要耗时

2、创建索引时，需要对表加锁，在锁表的同时，可能会影响到其他的数据 *** 作

3、 索引需要磁盘的空间进行存储，磁盘占用也很快。

4、当对表中的数据进行CRUD的时，也会触发索引的维护，而维护索引需要时间，可能会降低数据 *** 作性能

索引设计的原则不应该：

1、索引不是越多越好。索引太多，维护索引需要时间跟空间。

2、 频繁更新的数据，不宜建索引。

3、数据量小的表没必要建立索引。

应该：

1、重复率小的列建议生成索引。因为重复数据少，索引树查询更有效率，等价基数越大越好。

2、数据具有唯一性，建议生成唯一性索引。在数据库的层面，保证数据正确性

3、频繁group by、order by的列建议生成索引。可以大幅提高分组和排序效率

4、经常用于查询条件的字段建议生成索引。通过索引查询，速度更快

索引失效的场景

1、模糊搜索：左模糊或全模糊都会导致索引失效，比如'%a'和'%a%'。但是右模糊是可以利用索引的，比如'a%' 。

2、隐式类型转换：比如select * from t where name = xxx , name是字符串类型，但是没有加引号，所以是由MySQL隐式转换的，所以会让索引失效 3、当语句中带有or的时候：比如select * from t where name=‘sw’ or age=14

4、不符合联合索引的最左前缀匹配：(A,B,C)的联合索引，你只where了C或B或只有B,C

关于索引的知识点：

主键索引：主键索引的叶子节点存的是整行数据信息。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。主键自增是无法保证完全自增的哦，遇到唯一键冲突、事务回滚等都可能导致不连续。

唯一索引：以唯一列生成的索引，该列不允许有重复值，但允许有空值(NULL)

普通索引跟唯一索引查询性能：InnoDB的数据是按数据页为单位来读写的，默认每页16KB，因此这两种索引查询数据性能差别微乎其微。

change buffer：普通索引用在更新过程的加速，更新的字段如果在缓存中，如果是普通索引则直接更新即可。如果是唯一索引需要将所有数据读入内存来确保不违背唯一性，所以尽量用普通索引。

非主键索引：非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）

回表：先通过数据库索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树。

覆盖索引：如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为覆盖索引。

联合索引：相对单列索引，组合索引是用多个列组合构建的索引，一次性最多联合16个。

最左前缀原则：对多个字段同时建立的组合索引(有顺序，ABC，ACB是完全不同的两种联合索引) 以联合索引(a,b,c)为例，建立这样的索引相当于建立了索引a、ab、abc三个索引。另外组合索引实际还是一个索引，并非真的创建了多个索引，只是产生的效果等价于产生多个索引。

索引下推：MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。

索引维护：B+树为了维护索引有序性涉及到页分裂跟页合并。增删数据时需考虑页空间利用率。

自增主键：一般会建立与业务无关的自增主键，不会触发叶子节点分裂。

延迟关联：通过使用覆盖索引查询返回需要的主键，再根据主键关联原表获得需要的数据。

InnoDB存储: * .frm文件是一份定义文件，也就是定义数据库表是一张怎么样的表。*.ibd文件则是该表的索引，数据存储文件，既该表的所有索引树，所有行记录数据都存储在该文件中。

MyISAM存储：* .frm文件是一份定义文件，也就是定义数据库表是一张怎么样的表。* .MYD文件是MyISAM存储引擎表的所有行数据的文件。* .MYI文件存放的是MyISAM存储引擎表的索引相关数据的文件。MyISAM引擎下，表数据和表索引数据是分开存储的。

MyISAM查询：在MyISAM下，主键索引和辅助键索引都属于非聚簇索引。查询不管是走主键索引，还是非主键索引，在叶子结点得到的都是目的数据的地址，还需要通过该地址，才能在数据文件中找到目的数据。

PS：InnoDB支持聚簇索引，MyISAM不支持聚簇索引

4、SQL事务隔离级别

ACID的四个特性

原子性（Atomicity）：把多个 *** 作放到一个事务中，保证这些 *** 作要么都成功，要么都不成功

一致性（Consistency）：理解成一串对数据进行 *** 作的程序执行下来，不会对数据产生不好的影响，比如凭空产生，或消失

隔离性（Isolation，又称独立性）：隔离性的意思就是多个事务之间互相不干扰，即使是并发事务的情况下，他们只是两个并发执行没有交集，互不影响的东西；当然实现中，也不一定需要这么完整隔离性，即不一定需要这么的互不干扰，有时候还是允许有部分干扰的。所以MySQL可以支持4种事务隔离性

持久性（Durability）：当某个 *** 作 *** 作完毕了，那么结果就是这样了，并且这个 *** 作会持久化到日志记录中

PS：ACID中C与CAP定理中C的区别

ACID的C着重强调单数据库事务 *** 作时，要保证数据的完整和正确性，数据不会凭空消失跟增加。CAP 理论中的C指的是对一个数据多个备份的读写一致性

事务 *** 作可能会出现的数据问题

1、脏读(dirty read)：B事务更改数据还未提交，A事务已经看到并且用了。B事务如果回滚，则A事务做错了

2、 不可重复读(non-repeatable read)：不可重复读的重点是修改: 同样的条件, 你读取过的数据, 再次读取出来发现值不一样了，只需要锁住满足条件的记录

3、 幻读(phantom read)：事务A先修改了某个表的所有纪录的状态字段为已处理，未提交；事务B也在此时新增了一条未处理的记录，并提交了；事务A随后查询记录，却发现有一条记录是未处理的造成幻读现象，幻读仅专指新插入的行。幻读会造成语义上的问题跟数据一致性问题。

4、 在可重复读RR隔离级别下，普通查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在当前读下才会出现。要用间隙锁解决此问题。

在说隔离级别之前，你首先要知道，你隔离得越严实，效率就会越低。因此很多时候，我们都要在二者之间寻找一个平衡点。SQL标准的事务隔离级别由低到高如下： 上图从上到下的模式会导致系统的并行性能依次降低，安全性依次提高。

读未提交：别人改数据的事务尚未提交，我在我的事务中也能读到。

读已提交(Oracle默认)：别人改数据的事务已经提交，我在我的事务中才能读到。

可重复读(MySQL默认)：别人改数据的事务已经提交，我在我的事务中也不去读，以此保证重复读一致性。

串行：我的事务尚未提交，别人就别想改数据。

标准跟实现：上面都是关于事务的标准，但是每一种数据库都有不同的实现，比如MySQL InnDB 默认为RR级别，但是不会出现幻读。因为当事务A更新了所有记录的某个字段，此时事务A会获得对这个表的表锁，因为事务A还没有提交，所以事务A获得的锁没有释放，此时事务B在该表插入新记录，会因为无法获得该表的锁，则导致插入 *** 作被阻塞。只有事务A提交了事务后，释放了锁，事务B才能进行接下去的 *** 作。所以可以说 MySQL的RR级别的隔离是已经实现解决了脏读，不可重复读和幻读的。

5、MySQL中的锁

无论是Java的并发编程还是数据库的并发 *** 作都会涉及到锁，研发人员引入了悲观锁跟乐观锁这样一种锁的设计思想。

悲观锁：

优点：适合在写多读少的并发环境中使用，虽然无法维持非常高的性能，但是在乐观锁无法提更好的性能前提下，可以做到数据的安全性

缺点：加锁会增加系统开销，虽然能保证数据的安全，但数据处理吞吐量低，不适合在读书写少的场合下使用

乐观锁：

优点：在读多写少的并发场景下，可以避免数据库加锁的开销，提高DAO层的响应性能，很多情况下ORM工具都有带有乐观锁的实现，所以这些方法不一定需要我们人为的去实现。

缺点：在写多读少的并发场景下，即在写 *** 作竞争激烈的情况下，会导致CAS多次重试，冲突频率过高，导致开销比悲观锁更高。

实现：数据库层面的乐观锁其实跟CAS思想类似， 通数据版本号或者时间戳也可以实现。

数据库并发场景主要有三种：

读-读：不存在任何问题，也不需要并发控制

读-写：有隔离性问题，可能遇到脏读，幻读，不可重复读

写-写：可能存更新丢失问题，比如第一类更新丢失，第二类更新丢失

两类更新丢失问题：

第一类更新丢失：事务A的事务回滚覆盖了事务B已提交的结果 第二类更新丢失：事务A的提交覆盖了事务B已提交的结果

为了合理贯彻落实锁的思想，MySQL中引入了杂七杂八的各种锁：

锁分类

MySQL支持三种层级的锁定，分别为

表级锁定

MySQL中锁定粒度最大的一种锁，最常使用的MYISAM与INNODB都支持表级锁定。

页级锁定

是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁，表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。

行级锁定

Mysql中锁定粒度最细的一种锁，表示只针对当前 *** 作的行进行加锁。行级锁能大大减少数据库 *** 作的冲突。其加锁粒度最小，但加锁的开销也最大行级锁不一定比表级锁要好：锁的粒度越细，代价越高，相比表级锁在表的头部直接加锁，行级锁还要扫描找到对应的行对其上锁，这样的代价其实是比较高的，所以表锁和行锁各有所长。

MyISAM中的锁

虽然MySQL支持表，页，行三级锁定，但MyISAM存储引擎只支持表锁。所以MyISAM的加锁相对比较开销低，但数据 *** 作的并发性能相对就不高。但如果写 *** 作都是尾插入，那还是可以支持一定程度的读写并发

从MyISAM所支持的锁中也可以看出，MyISAM是一个支持读读并发，但不支持通用读写并发，写写并发的数据库引擎，所以它更适合用于读多写少的应用场合，一般工程中也用的较少。

InnoDB中的锁

该模式下支持的锁实在是太多了，具体如下：

共享锁和排他锁 (Shared and Exclusive Locks)

意向锁（Intention Locks）

记录锁（Record Locks）

间隙锁（Gap Locks）

临键锁 （Next-Key Locks）

插入意向锁（Insert Intention Locks）

主键自增锁 (AUTO-INC Locks)

空间索引断言锁（Predicate Locks for Spatial Indexes）

举个栗子，比如行锁里的共享锁跟排它锁：lock in share modle 共享读锁：

为了确保自己查到的数据没有被其他的事务正在修改，也就是说确保查到的数据是最新的数据，并且不允许其他人来修改数据。但是自己不一定能够修改数据，因为有可能其他的事务也对这些数据使用了 in share mode 的方式上了S 锁。如果不及时的commit 或者rollback 也可能会造成大量的事务等待。

for update排它写锁:

为了让自己查到的数据确保是最新数据，并且查到后的数据只允许自己来修改的时候，需要用到for update。相当于一个 update 语句。在业务繁忙的情况下，如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待，从而影响数据库的并发使用效率。

Gap Lock间隙锁：

1、行锁只能锁住行，如果在记录之间的间隙插入数据就无法解决了，因此MySQL引入了间隙锁(Gap Lock)。间隙锁是左右开区间。间隙锁之间不会冲突。

2、间隙锁和行锁合称NextKeyLock，每个NextKeyLock是前开后闭区间。

间隙锁加锁原则(学完忘那种)：

1、加锁的基本单位是 NextKeyLock，是前开后闭区间。

2、查找过程中访问到的对象才会加锁。

3、索引上的等值查询，给唯一索引加锁的时候，NextKeyLock退化为行锁。

4、索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，NextKeyLock退化为间隙锁。

5、唯一索引上的范围查询会访问到不满足条件的第一个值为止。

只要字段值还可以继续拆分，就不满足第一范式。

范式设计得越详细，对某些实际 *** 作可能会更好，但并非都有好处，需要对项目的实际情况进行设定。

在满足第一范式的前提下，其他列都必须完全依赖于主键列。 如果出现不完全依赖，只可能发生在联合主键的情况下：

实际上，在这张订单表中，product_name 只依赖于 product_id ，customer_name 只依赖于 customer_id。也就是说，product_name 和 customer_id 是没用关系的，customer_name 和 product_id 也是没有关系的。

这就不满足第二范式：其他列都必须完全依赖于主键列！

拆分之后，myorder 表中的 product_id 和 customer_id 完全依赖于 order_id 主键，而 product 和 customer 表中的其他字段又完全依赖于主键。满足了第二范式的设计！

在满足第二范式的前提下，除了主键列之外，其他列之间不能有传递依赖关系。

表中的 customer_phone 有可能依赖于 order_id 、 customer_id 两列，也就不满足了第三范式的设计：其他列之间不能有传递依赖关系。

修改后就不存在其他列之间的传递依赖关系，其他列都只依赖于主键列，满足了第三范式的设计！

查询每门课的平均成绩。

查询 score 表中至少有 2 名学生选修，并以 3 开头的课程的平均分数。

分析表发现，至少有 2 名学生选修的课程是 3-105 、3-245 、6-166 ，以 3 开头的课程是 3-105 、3-245。也就是说，我们要查询所有 3-105 和 3-245 的 degree 平均分。

查询所有学生的 name，以及该学生在 score 表中对应的 c_no 和 degree 。

通过分析可以发现，只要把 score 表中的 s_no 字段值替换成 student 表中对应的 name 字段值就可以了，如何做呢？

查询所有学生的 no 、课程名称 ( course 表中的 name ) 和成绩 ( score 表中的 degree ) 列。

只有 score 关联学生的 no ，因此只要查询 score 表，就能找出所有和学生相关的 no 和 degree ：

然后查询 course 表：

只要把 score 表中的 c_no 替换成 course 表中对应的 name 字段值就可以了。

查询所有学生的 name 、课程名 ( course 表中的 name ) 和 degree 。

只有 score 表中关联学生的学号和课堂号，我们只要围绕着 score 这张表查询就好了。

只要把 s_no 和 c_no 替换成 student 和 srouse 表中对应的 name 字段值就好了。

首先把 s_no 替换成 student 表中的 name 字段：

再把 c_no 替换成 course 表中的 name 字段：

查询 95031 班学生每门课程的平均成绩。

在 score 表中根据 student 表的学生编号筛选出学生的课堂号和成绩：

这时只要将 c_no 分组一下就能得出 95031 班学生每门课的平均成绩：

查询在 3-105 课程中，所有成绩高于 109 号同学的记录。

首先筛选出课堂号为 3-105 ，在找出所有成绩高于 109 号同学的的行。

查询所有成绩高于 109 号同学的 3-105 课程成绩记录。

查询所有和 101 、108 号学生同年出生的 no 、name 、birthday 列。

查询 '张旭' 教师任课的学生成绩表。

首先找到教师编号：

通过 sourse 表找到该教师课程号：

通过筛选出的课程号查询成绩表：

查询某选修课程多于5个同学的教师姓名。

首先在 teacher 表中，根据 no 字段来判断该教师的同一门课程是否有至少5名学员选修：

查看和教师编号有有关的表的信息：

我们已经找到和教师编号有关的字段就在 course 表中，但是还无法知道哪门课程至少有5名学生选修，所以还需要根据 score 表来查询：

根据筛选出来的课程号，找出在某课程中，拥有至少5名学员的教师编号：

在 teacher 表中，根据筛选出来的教师编号找到教师姓名：

查询 “计算机系” 课程的成绩表。

思路是，先找出 course 表中所有 计算机系 课程的编号，然后根据这个编号查询 score 表。

查询 计算机系 与 电子工程系 中的不同职称的教师。

查询课程 3-105 且成绩 至少 高于 3-245 的 score 表。

查询课程 3-105 且成绩高于 3-245 的 score 表。

查询某课程成绩比该课程平均成绩低的 score 表。

查询所有任课 ( 在 course 表里有课程 ) 教师的 name 和 department 。

查询 student 表中至少有 2 名男生的 class 。

查询 student 表中不姓 "王" 的同学记录。

查询 student 表中每个学生的姓名和年龄。

查询 student 表中最大和最小的 birthday 值。

以 class 和 birthday 从大到小的顺序查询 student 表。

查询 "男" 教师及其所上的课程。

查询最高分同学的 score 表。

查询和 "李军" 同性别的所有同学 name 。

查询和 "李军" 同性别且同班的同学 name 。

查询所有选修 "计算机导论" 课程的 "男" 同学成绩表。

需要的 "计算机导论" 和性别为 "男" 的编号可以在 course 和 student 表中找到。

建立一个 grade 表代表学生的成绩等级，并插入数据：

查询所有学生的 s_no 、c_no 和 grade 列。

思路是，使用区间 ( BETWEEN ) 查询，判断学生的成绩 ( degree ) 在 grade 表的 low 和 upp 之间。

准备用于测试连接查询的数据：

分析两张表发现，person 表并没有为 cardId 字段设置一个在 card 表中对应的 id 外键。如果设置了的话，person 中 cardId 字段值为 6 的行就插不进去，因为该 cardId 值在 card 表中并没有。

要查询这两张表中有关系的数据，可以使用 INNER JOIN ( 内连接 ) 将它们连接在一起。

完整显示左边的表 ( person ) ，右边的表如果符合条件就显示，不符合则补 NULL 。

完整显示右边的表 ( card ) ，左边的表如果符合条件就显示，不符合则补 NULL 。

完整显示两张表的全部数据。

在 MySQL 中，事务其实是一个最小的不可分割的工作单元。事务能够 保证一个业务的完整性 。

比如我们的银行转账：

在实际项目中，假设只有一条 SQL 语句执行成功，而另外一条执行失败了，就会出现数据前后不一致。

因此，在执行多条有关联 SQL 语句时， 事务 可能会要求这些 SQL 语句要么同时执行成功，要么就都执行失败。

在 MySQL 中，事务的 自动提交 状态默认是开启的。

自动提交的作用 ：当我们执行一条 SQL 语句的时候，其产生的效果就会立即体现出来，且不能 回滚 。

什么是回滚？举个例子：

可以看到，在执行插入语句后数据立刻生效，原因是 MySQL 中的事务自动将它 提交 到了数据库中。那么所谓 回滚 的意思就是，撤销执行过的所有 SQL 语句，使其回滚到 最后一次提交 数据时的状态。

在 MySQL 中使用 ROLLBACK 执行回滚：

由于所有执行过的 SQL 语句都已经被提交过了，所以数据并没有发生回滚。那如何让数据可以发生回滚？

将自动提交关闭后，测试数据回滚：

那如何将虚拟的数据真正提交到数据库中？使用 COMMIT :

事务的实际应用 ，让我们再回到银行转账项目：

这时假设在转账时发生了意外，就可以使用 ROLLBACK 回滚到最后一次提交的状态：

这时我们又回到了发生意外之前的状态，也就是说，事务给我们提供了一个可以反悔的机会。假设数据没有发生意外，这时可以手动将数据真正提交到数据表中：COMMIT 。

事务的默认提交被开启 ( @@AUTOCOMMIT = 1 ) 后，此时就不能使用事务回滚了。但是我们还可以手动开启一个事务处理事件，使其可以发生回滚：

仍然使用 COMMIT 提交数据，提交后无法再发生本次事务的回滚。

事务的四大特征：

事务的隔离性可分为四种 ( 性能从低到高 ) ：

查看当前数据库的默认隔离级别：

修改隔离级别：

测试 READ UNCOMMITTED ( 读取未提交 ) 的隔离性：

由于小明的转账是在新开启的事务上进行 *** 作的，而该 *** 作的结果是可以被其他事务（另一方的淘宝店）看见的，因此淘宝店的查询结果是正确的，淘宝店确认到账。但就在这时，如果小明在它所处的事务上又执行了 ROLLBACK 命令，会发生什么？

这就是所谓的 脏读 ，一个事务读取到另外一个事务还未提交的数据。这在实际开发中是不允许出现的。

把隔离级别设置为 READ COMMITTED ：

这样，再有新的事务连接进来时，它们就只能查询到已经提交过的事务数据了。但是对于当前事务来说，它们看到的还是未提交的数据，例如：

但是这样还有问题，那就是假设一个事务在 *** 作数据时，其他事务干扰了这个事务的数据。例如：

虽然 READ COMMITTED 让我们只能读取到其他事务已经提交的数据，但还是会出现问题，就是 在读取同一个表的数据时，可能会发生前后不一致的情况。* 这被称为* 不可重复读现象 ( READ COMMITTED ) 。

将隔离级别设置为 REPEATABLE READ ( 可被重复读取 ) :

测试 REPEATABLE READ ，假设在两个不同的连接上分别执行 START TRANSACTION :

当前事务开启后，没提交之前，查询不到，提交后可以被查询到。但是，在提交之前其他事务被开启了，那么在这条事务线上，就不会查询到当前有 *** 作事务的连接。相当于开辟出一条单独的线程。

无论小张是否执行过 COMMIT ，在小王这边，都不会查询到小张的事务记录，而是只会查询到自己所处事务的记录：

这是 因为小王在此之前开启了一个新的事务 ( START TRANSACTION ) * ，那么* 在他的这条新事务的线上，跟其他事务是没有联系的 ，也就是说，此时如果其他事务正在 *** 作数据，它是不知道的。

然而事实是，在真实的数据表中，小张已经插入了一条数据。但是小王此时并不知道，也插入了同一条数据，会发生什么呢？

报错了， *** 作被告知已存在主键为 6 的字段。这种现象也被称为 幻读，一个事务提交的数据，不能被其他事务读取到 。

顾名思义，就是所有事务的 写入 *** 作 全都是串行化的。什么意思？把隔离级别修改成 SERIALIZABLE :

还是拿小张和小王来举例：

此时会发生什么呢？由于现在的隔离级别是 SERIALIZABLE ( 串行化 ) ，串行化的意思就是：假设把所有的事务都放在一个串行的队列中，那么所有的事务都会按照 固定顺序执行 ，执行完一个事务后再继续执行下一个事务的 写入 *** 作 ( 这意味着队列中同时只能执行一个事务的写入 *** 作 ) 。

根据这个解释，小王在插入数据时，会出现等待状态，直到小张执行 COMMIT 结束它所处的事务，或者出现等待超时。

转载： https://github.com/baa-god/sql_node/blob/master/mysql/

Mysql是目前互联网使用最广的关系数据库，关系数据库的本质是将问题分解为多个分类然后通过关系来查询。 一个经典的问题是用户借书，三张表，一个用户，一个书，一个借书的关系表。当需要查询某个用户借书情况或者是书被那些人借了，就用关系查询来实现。

关系数据库范式

来自英文Normal form，简称NF。要想设计—个好的关系，必须使关系满足一定的约束条件，满足这些规范的数据库是简洁的、结构明晰的，同时，不会发生插入(insert)、删除(delete)和更新(update) *** 作异常。总共有六种范式：第一范式（1NF）、第二范式（2NF）、 第三范式 （3NF）、巴斯-科德范式（BCNF）、 第四范式 (4NF）和 第五范式 （5NF，又称完美范式）。

1NF是指数据库表的每一列都是不可分割的原子数据项。2NF必须满足1NF，要求数据库表中的每行记录必须可以被唯一地区分。3NF在2NF基础上，任何非主 属性 不依赖于其它非主属性（在2NF基础上消除传递依赖）。BCNF是在3NF基础上，任何非主属性不能对主键子集依赖（在3NF基础上消除对主码子集的依赖）， 满足BCNF不再会有任何由于函数依赖导致的异常，但是我们还可能会遇到由于多值依赖导致的异常。4NF的定义很简单：已经是BC范式，并且不包含多值依赖关系。5NF处理的是无损连接问题，这个范式基本没有实际意义，因为无损连接很少出现，而且难以察觉。而域键范式试图定义一个终极范式，该范式考虑所有的依赖和约束类型，但是实用价值也是最小的，只存在理论研究中。

Catalog和Schema

是数据库对象命名空间中的层次，主要用来解决命名冲突的问题。从概念上说，一个数据库系统包含多个Catalog，每个Catalog又包含多个Schema，而每个Schema又包含多个数据库对象（表、视图、字段等）。但是Mysql的数据库名就是Schema，不支持Catalog。

Mysql的数据库引擎主要有两种MyISAM和InnoDB，MyISAM支持全文检索，InnoDB支持事务。

SQL中的通配符‘%’代表任意字符出现任意次数。‘_’代表任意字符出现一次。SQL与正则表达式结合查询一般用在WHERE table_name REGEXP '^12.34'。子查询是从里到外执行。

数据库联结（join）涉及到外键，外键是指一个表的列是另一个表的主键，那么它就是外键。笛卡尔积联结（不指定联结条件时）生成的记录条目是单纯的第一个表的行乘以第二个表的列数。用得最多的是等值联结也叫内部联结。

高级联结还有自连接，是指查询中的两张表是同一张表，它通常作为外部语句用来代替从相同表中检索数据时使用的子查询。自然联结使每个列只返回一次。外部联结是指联结包含了那些在相关表中没有关联行的行。例如列出所有产品及其订购数量，包括没有人订购的产品。LEFT OUTER JOIN指选择左边表的所有行。

组合查询是指采用UNION等将两个查询结果取并集。

视图是查看存储在别处的数据的一种工具，它本身并不包含数据，因此表的数据修改了，视图返回的数据也将随之修改，因此如果使用了复杂或嵌套视图会对性能有较大的影响。视图的作用之一是隐藏复杂的SQL通常会涉及到联结查询。

存储过程类似于批处理，包含了一条或多条SQL语句。语法：

CREATE PROCEDURE name（）

BEGIN

SQL

END

-------------------------

CALL name（）//来调用存储过程

游标有DECLARE定义，游标与存储过程是绑定的，存储过程处理完成，游标就会消失。游标被打开后可以使用FETCH语句访问每一行。

触发器是在某个时间发生时自动执行某条SQL语句。语法：

CREATE TRIGGER name AFTER INSERT ON talbe_name FOR EACH ROW

事务处理可以维护数据库的完整性，保证批量的 *** 作要么完全执行，要么完全不执行。包括事务、回退、提交、保留点几个关键术语。ROLLBACK只能在一个事务处理内使用。他不能回退CREATE和DROP *** 作。使用COMMIT保证事务提交。复杂的事务处理需要部分提交或回退，因此我们需要使用保留点SAVEPOINT。可以使用ROLLBACK TO savepoint_name。保留点越多越好。保留点在事务执行完成后自动释放。

---