对昨天内容的总结：
总结：
范式：
键和属性
a. 超键, 候选键, 主键, 外键
b. 主属性, 非主属性
范式：
分类：1NF, 2NF, 3NF, BCNF, 4NF, 5FN
1NF：表中的属性不可再分
2NF：非主属性完全依赖于候选键
3NF：非主属性直接依赖于候选键
BCNF：主属性完全并且直接依赖于候选键
复杂查询
连接查询
a. 交叉连接 cross join
对两张表做笛卡尔乘积
b. 等值连接
1) natural join
2) using
3) on
c. 非等值连接
d. 内连接
e. 外连接
1) 左外连接 left [outer] join
2) 右外连接 right [outer] join
3) 全外连接 full [outer] join
f. 自连接
联合查询
关键字：union, union all
注意事项：
a. 结果集的列数要保持一致
b. 对应的字段，它们的数据类型和含义应该保持一致
c. union会去重, union all不会去重
子查询
a. 非关联子查询
b. 关联子查询
c. 标量子查询 （查询结果集只有一行一列，可以把结果集看成一个值）
d. 子查询往往会和一些关键字一起使用：exists, not exists, in, some(any) all
e. 子查询当作计算字段
·················································································································································································································
构成单一逻辑工作单元的操作集合，我们称为事务 (transaction)。
即使有故障，数据库系统也必须保证事务的正确执行——要么执行整个事务，要么属于该事务的操作一个也不执行。
事务的基本操作：
- START TRANSACTION / BEGIN：开启一个事务，标记事务的起点
- COMMIT：提交事务，表示事务成功被执行。
- ROLL BACK：回滚事务，回滚到初始状态或者回滚点。
- SAVEPOINT：回滚点
- RELEASE SAVEPOINT：删除回滚点
- SET TRANSACTION: 设置隔离级别
注意事项：
-
START TRANSACTION 标志事务的开始，在 MySQL 中可以用 set autocommit = 0 替代。
-
结束事务的情况只有两种：
a. COMMIT：表示事务成功被执行，结束事务。
b. 发生故障：结束事务, 不管有没有设置回滚点，都会回到事务开启前的状态。 -
ROLLBACK：不表示发生故障, 它也是一个数据库操作，属于事务的一部分。表示回滚事务，回滚到事务开启前的状态，或者中间的某个回滚点。要想 rollback 生效，必须要 commit。
·················································································································································································································
事务的性质——ACID
- 原子性（Atomicity）原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。无论是操作系统崩溃，还是计算机停止运行，这项要求都要成立。
- 一致性（Consistency）事务作为一个原子性操作，它从一个一致性的数据库状态开始运行，事务结束时，数据库的状态必须再次是一致的。
- 隔离性（Isolation）尽管多个事务可能并发执行，但系统保证，对于任何一对事务Ti和Tj ,在Ti看来， Tj要么在Ti开始之间已经完成，要么在Ti完成之后才开始执行。因此，每个事务都感觉不到系统中有其他事务在并发地执行。
- 持久性（Durability）一个事务成功完成后，它对数据库的改变必须是永久的，即使出现系统故障。
ACID 可以说是事务的四大特性，在这四个特性中，原子性是基础，隔离性是手段，一致性是约束条件，而持久性是我们的目的。
·················································································································································································································
并发执行时可能引发的问题
一、脏写
这个现象，我们称之为脏写。
两个事务并发地对同一项数据进行写操作。
脏写是指当多个事务并发写同一数据时，先执行的事务所写的数据会被后写的数据覆盖。
脏写会导致更新丢失。就好像先提交的事务根本没有执行一样。
给用户的感觉就是T2这个事务根本没有执行一样
二、脏读
你会发现 T2 计算的结果是 1900，这肯定是不正确的，数据不一致了！
这种现象我们称之为脏读。
如果一个事务A向数据库写数据，但该事务还没提交或终止，另一个事务B就看到了事务A写入数据库的数据，这个现象我们称为脏读。
即读取到了未提交的数据
三、不可重复读
不可重复读是指：一个事务有对同一个数据项的多次读取，但是在某前后两次读取之间，另一个事务更新该数据项，并且提交了。在后一次读取时，感知到了提交的更新。这个现象我们称为不可重复读。
一个事务至少读两次数据，一个事务写数据。在前后两次读取之间，感知到了另一个事务提交的更新。
可能有同学觉得不可重复读，读取的是已提交的数据，那么就是“正确的”数据，不应该是一个问题。
这时，我们回顾以下隔离性的定义，你会发现写数据的事务对读数据的事务，确实产生了影响！也就是说没有做到完全隔离。
有些情况下，不可重复读确实不是什么大的问题。但是，在有些业务场景下，我们应该避免不可重复读。
四、幻读
一个事务需要进行前后两次统计，在这两次统计期间，另一个事务插入了新的符合统计条件的记录，并且提交了。导致前后两次统计的数据不一致。这种现象，我们称之为幻读。
分清不可重复读和幻读：
不可重复读是数据项的更新过后读取数据，幻读是数据项的增加过后读取数据
从这个事务的视角来看，平白无故多了几条数据。就像产生了幻觉一样。
前后多次读取，统计的数据不一致。
既然并发执行事务可能会引发这么多问题，那么我们怎么应对呢？
数据库提供了不同的隔离级别来应对不同的问题。
·················································································································································································································
隔离级别
SQL 标准规定了四种隔离级别，分别为
读未提交（read uncommitted）
允许读取未提交的数据。
读已提交（read committed）
只允许读取已提交数据，但不要求可重复读。比如，在事务两次读取一个数据项期
间，另一个事务更新了该数据项并提交。
可重复读（repeatable read）
只允许读取已提交数据，而且在一个事务两次读取一个数据项期间，其他事务不得更新该数据。但该事务不要求与其他事务可串行化。比如，在两次统计查询中，另一个事务可以插入一些记录，当这些记录中有符合查询条件的，那么就会产生幻读。
可串行化（serializable）
看起来事务就好像是串行执行的一样。一个事务结束后，另一个事务才开始执行。
隔离性依次增高。
read uncommitted read committed repeatable read serializable
以上所有隔离级别都不允许脏写(dirty write)，即如果有一个数据项已经被另一个尚未提交或中止的事务写入，则该事务不能对该数据项执行写操作。
我们可以通过下面命令，查看和设置 MySQL 的隔离级别。
select @@[session|global.]transaction_isolation;
set [session|global] transaction isolation level read uncommitted.
注意：
MySQL 支持4种隔离级别，默认为 RR (repeatable read);
Oracle 只支持 read committed 和 serializable 两种隔离级别，默认为 read committed.
·················································································································································································································
############################### 事务 #######################################
# 事务的基本操作
# 开启事务 start transaction、begin、set autocommit=0
# 提交事务 commit
# 设置回滚点 savepoint
# 删除回滚点 release savepoint
# 回滚点 rollback(回滚到事务最初始的状态)、rollback to sp;(回滚到回滚点sp)
# 设置事务的隔离级别：set transaction
# 查看auto_commit后，发现其默认值为1，也就是说每条sql语句如insert...就是一个事务
# 每次都会默认提交
select @@autocommit;
# 当set autocommit = 0;后，会把set autocommit = 0;到commit之间的sql看成一个事务。
set autocommit = 0;
use nba;
select * from player;
commit;
# 以下三行又是一个事务
##
##
commit;
# 回滚
set autocommit = 0;
#
#
savepoint sp1;
#
#
rollback to sp1;
#
#
release savepoint sp1;
commit;
# 举个栗子：
use friend;
show tables;
select * from boys;
# 如果我们在rollback to sp1;这行执行完后，关闭了mysql（演示故障发生的情形），
# 打开mysql查看数据，发现boys表中的数据没变
# 如果我们在commit;这行执行完后，关闭了mysql（演示故障发生的情形），
# 打开mysql查看数据，发现boys表中的数据少了bid=5的记录。
# 同时bid = 3的记录的gid 并没有更新，因为rollback to sp1;
set autocommit=0;
delete from boys where bid=5;
savepoint sp1;
update boys set gid = 30 where bid = 3;
rollback to sp1;
commit;
# 验证ACID中的一致性：即事务中的操作必须满足表的约束条件
select * from boys;
show index from boys;
set autocommit=0;
update boys set gid=3 where bid = 3;
# 事务中的操作必须满足表的约束条件，以下这行sql就无法执行，违反了主键唯一的原则
# 报错：Error Code: 1062. Duplicate entry '1' for key 'PRIMARY' 0.015 sec
# 这一行执行虽然出错了，但不叫发生故障
update boys set bid=1 where bid = 3;
commit;
# 事务的特性 (ACID)
# a.原子性 b.一致性 c.隔离性 d.持久性
# 并发执行事务可能会出现的问题
# a.脏写 b.脏读 c.不可重复读 d.幻读
# 隔离级别
-- 脏写 脏读 不可重复读 幻读
-- read uncommitted × √ √ √
-- read committed × × √ √
-- repeatable read × × × √
-- serializable × × × ×
# 查看隔离级别
#当前会话（会话简单理解就是客户端连接服务器）的隔离级别，也就是这个会话中所有事务的默认隔离级别
select @@transaction_isolation;
select @@session.transaction_isolation; #当前会话的隔离级别，和上一句sql一个意思
select @@global.transaction_isolation; # 查看全局的隔离级别, 下一个会话的默认隔离级别。
# 设置隔离级别
set transaction isolation level read uncommitted; # 设置下一个事务的隔离级别
set session transaction isolation level read uncommitted; # 设置会话的隔离级别
set global transaction isolation level read uncommitted; # 设置全局的隔离级别
# read uncommitted
set session transaction isolation level read uncommitted;
select @@transaction_isolation;
# 脏写(×)
set autocommit = 0;
use friend;
select * from boys;
update boys set gid = gid + 1 where bid = 1;
commit;
# 脏读(√)
begin;
select * from boys;
commit;
# 不可重复读(√)
begin;
select * from boys where bid = 1;
select * from boys where bid = 1;
commit;
# 幻读(√)
begin;
select * from boys;
select * from boys;
commit;
# read committed
set session transaction isolation level read committed;
# 脏写(×)
# 脏读(×)
# 不可重复读(√)
begin;
select * from boys;
select * from boys where bid = 1;
select * from boys where bid = 1;
commit;
# 幻读(√)
begin;
select * from boys;
select * from boys;
commit;
# repeatable read
set session transaction isolation level repeatable read;
# 脏写(×)
# 脏读(×)
# 不可重复读(×)
begin;
select * from boys where bid = 1;
select * from boys where bid = 1;
commit;
# 幻读(MySQL RR隔离级别下不会出现幻读, 但是在其它的DBMS中可能出现幻读现象)
begin;
select * from boys;
select * from boys;
commit;
# 注：MySQL的不可重读读隔离级别比较特殊, 可以防止大多数的幻读出现。底层实现：gap lock + nextKey lock
# serializable
# 脏写(×)
# 脏读(×)
# 不可重复读(×)
# 幻读(×)
·················································································································································································································
索引
MySQL 逻辑架构
大体来说，MySQL可以分为 Server 层和存储引擎层。
Server 层包括连接器、查询缓存、解析器、优化器和执行器等，涵盖了 MySQL 大多数核心服务功能。
存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Mermory 等多个存储引擎。
接下来, 我们从一条最简单的 SQL 来看下 MySQL 是如何工作的。
SELECT * FROM t WHERE id = 1;
MySQL 是如何工作的？
连接器
当你在客户端输入 mysql –u $user –p $pwd 连接 mysql 的时候，接待你的就是连接器。连接器的作用就是和客户端建立连接（TCP三次握手）、获取权限（后面的操作都是基于这次获取的权限进行的）、维持和管理连接（如果客户端和服务器端长时间没有进行交互的话，连接就会断开）。
查询缓存
建立连接后，就可以执行select语句了。首先MySQL会去查看查询缓存，看下之前是否已经执行过这条查询语句。如果命中缓存，就直接返回。否则就扔给解析器。
解析器
MySQL需要知道你想做什么，因此我们就来到了解析器。解析器会做词法分析和语法分析。词法分析主要是分析每个词的含义，语法分析会判断你写的 SQL 语句是否满足 SQL 语法。
优化器
经过解析器，MySQL就知道你想做什么了。但是在开始执行之前，还需要经过优化器的处理。优化器会优化你的SQL语句。生成最终的执行方案 (execution plan)。然后进入执行器阶段。
执行器
执行器首先会判断你对这张表有没有相应的权限。如果没有，就报错。如果有，就调用相应的存储引擎接口，执行语句。然后将结果集返回给客户端。
·················································································································································································································
存储引擎
数据的存储和提取是由存储引擎负责的，它负责和文件系统打交道。
MySQL 的存储引擎是插件式的。不同的存储引擎支持不同的特性。
选择合适的存储引擎对应用非常重要 。
-
查看MySQL支持哪些存储引擎
SHOW ENGINES; -
查看默认存储引擎
SHOW VARIABLES LIKE ‘%storage_engine%’; -
查看某张表的存储引擎（存储引擎最小单位是表）
SELECT ENGINE FROM information_schema.TABLES
WHERE TABLE_SCHEMA=‘db’
AND TABLE_NAME=‘table’;
MySQL 5.5 之前默认的存储引擎。
·················································································································································································································
MyISAM
特点：
a. 查询速度很快
b. 支持表锁
c. 支持全文索引
d. 不支持事务
使用 MyISAM 存储表，会生成三个文件.
.frm # 存储表结构，是任何存储引擎都有的
.myd # 存放数据
.myi # 存放索引
索引和数据是分开存放的，这样的索引叫非聚集索引。
·················································································································································································································
InnoDB
MySQL 5.5 以及以后版本默认的存储引擎。没有特殊应用，Oracle官方推荐使用InnoDB 引擎。
特点：
a. 支持事务
b. 支持行锁（增加删除的时候只锁一行数据而不是锁整张表）
c. 支持MVCC
d. 支持崩溃恢复
e. 支持外键一致性约束
使用 InnoDB 存储表，会生成两个文件.
.frm # 存储表结构，是任何存储引擎都有的
.ibd # 存放数据和索引
索引和数据存放在一起，这样的索引叫聚集索引。
·················································································································································································································
Memory
特点：
a. 所有数据都存放在内存中，因此数据库重启后会丢失
b. 支持表锁（并发度不高）
c. 支持Hash和BTree索引（Hash索引可以看成HashMap）
d. 不支持Blob和Text字段（Memory数据都放在内存中，不支持过大的字段）
Memory由于数据都放在内存中，以及支持Hash索引，它的查询速度是最快的。
一般使用 Memory 存放临时表。
临时表：在单个连接中可见，当连接断开时，临时表也将不复存在。
################################### 索引 #####################################
# MySQL架构
# 如果客户端和服务器端长时间没有进行交互的话，连接就会断开
# 查看维持时间，如果8小时客户端还没动静的话就会自动断开连接
select @@wait_timeout; # 8小时
select @@query_cache_type; # 默认查询缓存时关闭
# 存储引擎
# 查看MySQL支持哪些存储引擎
show engines;
# 查看默认存储引擎
select @@default_storage_engine;
# 查看某张表的存储引擎
SELECT `ENGINE` FROM information_schema.TABLES
WHERE TABLE_SCHEMA='$db'
AND TABLE_NAME='$table';
use information_schema;
show tables; # 看看数据库information_schema里面有啥表
desc tables; # 看看tables这张表里面的信息
select `engine` from information_schema.tables
where table_schema='nba'and table_name='player';
# MyISAM
create database engine_db;
use engine_db;
create table t_MyISAM(
a int
) engine = MyISAM;
# Error Code: 1044. Access denied for user 'root'@'localhost' to database 'information_schema' 0.000 sec
# 没权限对information_schema进行操作，应当在engine_db中创建表的，use engine_db即可alter
# innodb
create table t_innodb (
a int
)engine='InnoDB';
# memory
create temporary table t_tmp (
a int
)engine='memory';
# innodb 数据页
select @@innodb_page_size; # 16KB
·················································································································································································································
磁盘IO原理
机械磁盘
六个读写头，一个磁盘上下两面都可以读写数据
组合臂的前移和后退可以保证在不同的磁道上读写数据
现在存储数据 一般用的还是机械磁盘，不是SSD。
术语：磁盘 盘组 磁道 扇区 读/写头 柱面
扇区 和 磁道
下图显示的是一个盘面，盘面中一圈圈灰色同心圆为一条条磁道，从圆心向外画直线，可以将磁道划分为若干个弧段，每个磁道上一个弧段被称之为一个扇区（图践绿色部分）。扇区是磁盘的最小组成单元，通常是512字节。（由于不断提高磁盘的大小，部分厂商设定每个扇区的大小是4096字节）
磁头 和 柱面
硬盘通常由重叠的一组盘片构成，每个盘面都被划分为数目相等的磁道，并从外缘的“0”开始编号，具有相同编号的磁道形成一个圆柱，称之为磁盘的柱面。磁盘的柱面数与一个盘面上的磁道数是相等的。由于每个盘面都有自己的磁头，因此，盘面数等于总的磁头数。 如下图
https://www.cnblogs.com/jswang/p/9071847.html
磁盘上的数据可以用一个三维地址标识: 柱面号, 盘号, 块号(磁道上的扇区)
读/写数据的步骤：
1. 移动磁头到指定的柱面号，这个过程被称为定位或查找。
由于是机械移动, 这部分耗时最高, 最大可达 0.1s.
2. 根据盘面号确定从哪个磁盘读取数据（基本不耗时）
3. 盘组开始旋转，将指定的块号移动到读/写头下
磁盘旋转的速度很快, 一般为7200rpm。旋转一圈大约需要 0.0083s.
平均情况下需要旋转半圈，即0.00415s
4. 读写数据
数据通过系统总线传送到内存。一般传输一个字节大概需要 0.02us.
读写 4KB 大约需要 80us.
一个盘块存储4kb数据，读写的基本单位是盘块
磁盘读取数据是以盘块(block)为单位的, 一般为4KB。位于同一盘块的所有数据会被一次性全部读取出来。磁盘IO的代价主要花费在第 1 步。
思考：如果我们存储的记录是乱序的，那么每一次查找都必须遍历整个表。
所以一张表中的数据最好是大小有序且连续存放，存放在一个磁道上，这样就可以避免随机存取数据。 4M
必须按照一定的格式（数据结构）将数据组织起来。
·················································································································································································································
InnoDB数据页格式
页是 InnoDB 磁盘管理的最小单位。在 InnoDB 存储引擎中, 页默认大小为16KB。
可以通过参数 innodb_page_size 将页的大小设置为 4K、8K 和 16K（因为磁盘IO中读写的基本单位是盘块，一个盘块4kb）。
InnoDB 每次至少会将 1 个页的数据从磁盘读取到内存，每次至少也会将 1 个页的数据从内存写到磁盘。
在InnoDB存储引擎中，有很多种页类型。其中最重要的是数据页，也叫 B-tree Node。（说明数据页是作为B+树的结点存在的），里面存储了索引和数据的信息。
接下来，我们就看看数据页的格式，看下在 innodb 中数据是怎么存储的。
-
File Header
主要存储表空间相关信息 -
Page Header
主要存储数据页的元信息 -
Infimum + Supremum Records（类似于链表的头结点和尾结点）
每个数据页中有两个虚拟行记录用来限定记录的边界，infimum record 是数据页上最小的记录，supremum record 是数据页上最大的记录。 -
User Records
用户数据，实际存储的行记录。 -
Free Space
空闲空间。新插入的数据可存在在这 -
Page Directory
页目录，存放了记录的相对位置。可以帮助我们快速的定位某条记录 -
File Trailer
位于数据页的最后，用来检测页是否完整地写入磁盘。
行记录是用链表形式组织的，最小最大记录相当于两个哨兵。
数据页中的记录从逻辑上是从小到大排好序的
Page Directory是一个数组，里面包含很多指向记录的指针(又叫 Slot)，S0指向最小记录的链表, Sn指向最大记录的链表。S1 ~ Sn-1 的每条链的长度范围为 [4, 8]。
Page Directory的作用就是增大数据页中的查找效率
Infimum + Supremum Records类似于链表的头结点和尾结点
User Records就是存储在整张链表中的结点
File Header 里面有两个字段：FIL_PAGE_PREV 和 FIL_PAGE_NEXT 用来表示上一个页和下一个页，因此，页与页之间是用双链表链接的。页内的记录是由单链表从大到小依次链接的。
即：页与页是有序的，页内也是有序的。
所以，我们可以认为表中的数据都是大小有序的
我们清楚了数据是怎么组织和存储的了，那么现在我们来思考这样一个问题：
Select * from t where id = 10
如何查找某一条记录呢？将每个页读入内存，依次查找吗？
这样做的话，查找效率就太慢了，为此，我们将页组织成了一个更加复杂的数据结构，这种结构可以快速定位到某条记录。
这个更加复杂的数据结构就是索引。
欲知后事如何，请听下回分解。
·················································································································································································································
作业：
# 在 student, sc, course, teacher 表中完成下列查询：
use stu_db;
# student: sid, sname, sage, sgender
# teacher: tid, tname
# course: cid, cname, tid
# sc: sid, cid, score
# a. 查询平均成绩大于等于 60 分的同学信息
# 1).查询平均成绩大于等于 60 分的sid
select sid from sc
group by sid
having avg(score) > 60;
# 2).
select * from student
where sid in(select sid from sc
group by sid
having avg(score) > 60
);
# 老师写法：
select student.*, avg(score) as avg_score
from student
join sc using(sid)
group by sid having avg_score > 60;
# b. 查询有成绩的学生信息
select * from student
join sc using(sid);
# 老师写法：
select *
from student
where sid in (
select distinct sid
from sc
where score is not null
);
# c. 查询选修「张三」老师课的同学信息，课程编号和课程分数
select * from student
where sid in(select sid from student
join sc using(sid)
join course using(cid)
join teacher using(tid)
where tname<=>'张三'
);
# 老师写法：
select *
from student
join sc using(sid)
where cid in (
select cid
from teacher
join course using(tid)
where tname='张三'
);
# d. 查询没有选修所有课程的学生信息
select *
from student
where sid in (
select sid
from sc
group by sid having count(cid) < (
select count(cid)
from course
)
);
# e. 查询 '01' 课程不及格同学的信息，并按照课程分数降序排列
select * from student
join sc using(sid)
where cid = '01' and score < 60
group by score desc;
# f. 查询各学生的年龄，按年份来算
select sid, sname, year(sage) from student;
扫一扫
181
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
