Version: Next

SQL

什么是SQL

Structured Query Language:结构化查询语言定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”

SQL的分类

DDL(Data Definition Language)数据定义语言用来定义数据库对象，数据库，表，列。关键字：create drop alter
DML(Data Manipulation Language)数据操作语言用来对数据库中表的数据进行增删改关键字：insert, delete, update
DQL(Data Query Language)数据查询语言用来查询数据库表中的记录（数据）关键字：select, where
DCL(Data Control Language)数据控制语言(了解)

DDL：操作数据库、表

操作数据库：CRUD

C(Create)创建：
/*创建数据库*/
create database if not exists dbname;
create database dbname character set gbk;
R(Retrieve)查询：
/*查询建表语句*/
show create database mysql;
U(Update)修改：
/*修改数据库字符集*/
alert database dbname character set gbk；
D(Delete)删除：
/*删除数据库字符集*/
drop database dbname：
drop database if exists dbname;

使用数据库：
/*查询正在使用的数据库*/
select database();

/*使用数据库*/
use dbname;

操作表

C(Create)创建：
1. 语法：
  create table 表名(
  列明1 数据类型1,
  列明2 数据类型2，
  ...
  列明n 数据类型n
  );
  /*double*/
  create table 表名(
  列名1 double(5, 2) /*一共5位，小数占2位*/
  );
  /*data
  日期，只包含年月日，yyyy-MM-dd
  */
  /*
  datatime:年月日时分秒
  yyyy-MM-dd HH:mm:ss
  */
  /*
  timestamp:时间戳年月日时分秒
  yyyy-MM-dd HH:mm:ss
  */
  将来不给这个字段赋值，或赋值为null则默认使用当前的系统时间自动赋值
  /*
  字符串：
  name varchar(20)
  */
2. 创建表：
  create table(
  id int,
  name varchar(32),
  age int,
  score double(4, 1),
  birthday date,
  insert_time timestamp
  );
3. 复制表：
  create table 表名 like 被复制表;
R(Retrieve)查询：
/*查询数据库中所有表名称*/
show tables;

/*查询表结构*/
desc 表名;

U(Update)修改：
- 修改表名
  alter table tablename rename to newtablename;
- 修改表的字符集
  alter table tablename character set 字符集;
- 添加一列
  alter table tablename add listname 数据类型;
- 修改列名称、类型
  alter table tablename change 列名新列名新数据类型;
  alter table tablename modify 列名新数据类型;
- 删除列
  alter table 表名 drop 列名;
D(Delete)删除：
drop table if exists 表名;
使用数据库：

DML：表的增删改

添加数据

注意：

列名和值要一一对应
如果表名后不定义列名，默认给所有列添加值
除了数字类型，其他类型需要引号引起来，单双皆可

insert into 表名(列名1，列名2,...,列名n) values(值1,值2,...,值n);
insert into 表名 values(value1,value2...);

删除数据

注意：

如果没加条件，会删除表中所有记录

delete from 表名 [where 条件];

删除表中所有记录（效率）

truncate table 表名;  -- 删除表，然后创建一个一摸一样的空表

修改数据

update 表名 set 列名1 = 值1, 列名2 = 值2 ... [where 条件];

DQL：数据查询语言

select * from 表明;

语法

select
    字段列表
from
    表名,表名
where
    条件列表
group by
    分组字段
having
    分组之后的条件
order by
    排序
limit
    分页

基础查询

多个字段查询

SELECT NAME,age FROM student;

SELECT 
    NAME,   -- 姓名
    age     -- 年龄
FROM 
    student;

去除重复

SELECT DISTINCT
    address -- 姓名
FROM 
    student;

计算列

-- 计算math和english分数之和
SELECT 
    NAME,           -- 姓名
    math,           -- 数学
    english,        -- 英语
    math+english    -- 数学加英语
FROM 
    student;

-- IFNULL(列，替换值)
SELECT 
    NAME,                       -- 姓名
    math,                       -- 数学
    english,                    -- 英语
    math+IFNULL(english, 0)     -- 数学加英语
FROM 
    student;

起别名

SELECT  
    NAME AS 姓名,                         -- 姓名
    math AS 数学,                         -- 数学
    english AS 英语,                      -- 英语
    math+IFNULL(english, 0) AS 总分       -- 数学加英语
FROM 
    student;

条件查询

where子句后跟条件
运算符返回boolean
- 比大小
- Between ... and
- IN(集合)
- Like '张%' 模糊查询
  - _ : 单个任意字符
  - %: 多个任意字符
- IS NULL 不能写=NULL
- and &&
- or ||
- not !

-- 查询年龄大于20岁
SELECT * FROM student WHERE age>=20;    

-- 查询年龄不等于20岁的
SELECT * FROM student WHERE age<>20;
SELECT * FROM student WHERE age!=20;

-- 查询年龄大于等于20 <=30
SELECT * FROM student WHERE age>=20 AND age<=30;
SELECT * FROM student WHERE age>=20 && age<=30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30;

-- 查询年龄18，22，25岁的
SELECT * FROM student WHERE age = 18 OR age = 22 OR age = 25;
SELECT * FROM student WHERE age IN (22,18,25);

-- null
SELECT * FROM student WHERE english IS NULL;
SELECT * FROM student WHERE english IS NOT NULL;

-- 模糊查询 - 查询姓马的
SELECT * FROM student WHERE NAME LIKE "马%";

-- 姓名中第二个字是化的人
SELECT * FROM student WHERE NAME LIKE "_化%";

排序查询

语法：order by 子句

order by 排序字段1 排序方式1，排序字段2 排序方式2...

默认升序 ASC -> 降序DESC

SELECT * FROM student ORDER BY math;
SELECT * FROM student ORDER BY math DESC;

-- 按照数学成绩排名，如果数学成绩一样，则按照英语成绩排名
SELECT * FROM student ORDER BY math DESC, english DESC;
SELECT * FROM student ORDER BY math , english ;

聚合函数

将一列数据作为一个整体，进行纵向的计算。例如:平均分

count：计算列上个数
max：计算最大值
min：计算最小值
sum：求和
avg：平均值

注意：聚合函数的计算会排除null数据

SELECT COUNT(NAME) FROM student;
SELECT AVG(math) FROM student;

SELECT COUNT(english) FROM student;  -- 排除了null

--IFNULL(列，替换值)
SELECT COUNT(IFNULL(english, 0)) FROM student;

分组查询

语法：

group by 分组字段;
注意：
- 分组之后，查询的字段：分组字段、聚合函数（写其他的没有意义）
- !! Where和Having的区别？
  - 位置： where在分组之前进行限定，如果不满足则不参与分组 having在分组之后进行限定，如果不满足则不会被查询出来
  - where后不可跟聚合函数,having可以链接聚合函数的判断

-- 按照男女分组，统计两组人数，分别查询两组平均分
SELECT sex,AVG(math),COUNT(id) FROM student GROUP BY sex;

-- 分数低于70分的人不参与分组
SELECT sex,AVG(math),COUNT(id) FROM student WHERE math>70 GROUP BY sex;

-- 分数低于70分的人不参与分组,分组后人数要大于两人
SELECT sex,AVG(math),COUNT(id) FROM student WHERE math>70 GROUP BY sex HAVING COUNT(id)>2;

-- 给Count(id)起别名
SELECT sex,AVG(math),COUNT(id) AS 人数 FROM student WHERE math>70 GROUP BY sex HAVING 人数>2;

分页查询

语法：

limit 开始索引，每页查询条数

-- 每页显示3条记录
-- 公式：开始的索引=（当前的页码 - 1） * 每页显示的条数
SELECT * FROM student LIMIT 0 , 3;  -- 第一页
SELECT * FROM student LIMIT 3 , 3;  -- 第二页

注意

limit是mysql“方言”

limit -> mysql

约束

概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性分类：

主键约束：primary key
非空约束：not null
唯一约束：unique
外键约束：foreign key

非空约束

创建表时添加约束
CREATE TABLE stu(
id INT,
NAME VARCHAR(20)NOT NULL
);
-- 删除非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);

-- 添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

唯一约束

值不能重复

-- 创建表时添加唯一约束 在mysql中null可以重复
CREATE TABLE stu(
    id INT,
    phone_number VARCHAR(20) UNIQUE
);

主键约束

注意：

含义：非空且唯一
一张表只能有一个主键字段
一张表中记录的唯一标识

-- 创建表时添加主键约束
CREATE TABLE stu(
    id INT PRIMARY KEY,
    NAME VARCHAR(20)
);

-- 删除主键
ALTER TABLE stu DROP PRIMARY KEY;

自动增长如果某一列是数值类型的，使用auto_increment完成值的自动增长
CREATE TABLE stu(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);

外键约束

-- 建表
CREATE TABLE emp (
    id INT PRIMARY KEY AUTO_INCREMENT,  
    NAME VARCHAR(30),  
    age INT,  
    dep_name VARCHAR(30),  
    dep_location VARCHAR(30) 
); 

 INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州'); 
 INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州'); 
 INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州'); 
 INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳'); 
 INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳'); 
 INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳'); 
 
-- 舍弃

 -- 解决方案：分成2张表 
 -- 创建部门表(id,dep_name,dep_location) 
 -- 一方，主表 
CREATE TABLE department(  
    id INT PRIMARY KEY AUTO_INCREMENT,  
    dep_name VARCHAR(20),  
    dep_location VARCHAR(20) 
); 
 
-- 创建员工表(id,name,age,dep_id) 
-- 多方，从表 
CREATE TABLE employee(  
    id INT PRIMARY KEY AUTO_INCREMENT,  
    NAME VARCHAR(20),  
    age INT,  
    dep_id INT  -- 外键对应主表的主键 
); 
 
-- 添加2个部门 
INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳'); 
SELECT * FROM department; 
 
-- 添加员工,dep_id表示员工所在的部门 
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1); 
 
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1); 
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1); 
 
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2); 
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2); 
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2); 

SELECT * FROM employee;

研发部门和开发部地址存在大量重复，称为数据冗余 -> 进行表拆分

员工表 <-> 部门表

员工表的dep_id 外键关联部门表的主键id

/* 创建时添加外键
语法：
     create table 名(
        外键列
        constraint 外键名称 foreign key 外键列名称 references 主表名         称(主表主键名称)
     );
*/
CREATE TABLE employee(  
    id INT PRIMARY KEY AUTO_INCREMENT,  
    NAME VARCHAR(20),  
    age INT,  
    dep_id INT,  -- 外键对应主表的主键 
    CONSTRAINT key1 FOREIGN KEY (dep_id) REFERENCES department(id)
);

删除外键
alter table employee drop foreign key 外键名：
添加外键
alter table employee ADD CONSTRAINT key1 FOREIGN KEY (dep_id) references department(id);
级联操作在添加外键时设置级联更新
ALTER TABLE employee ADD CONSTRAINT key1 FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE
设置级联删除
ALTER TABLE employee ADD CONSTRAINT key1 FOREIGN KEY (dep_id) REFERENCES department(id) ON DELETE CASCADE ON UPDATE CASCADE

数据库设计

多表关系

一对一：
- 人和身份证
一对多（多对一）√：
- 部门和员工一个部门有多个员工，一个员工隶属于一个部门
多对多：
- 学生和课程一个学生有多门课程，一门课程有多名学生
实现关系：
- 一对多（多对一）：在多的一方建立外键，指向一的一方的主键
- 多对多：需要借助第三张中间表。至少包含两个字段，作为外键分别指向两个表的主键
- 一对一：在任意一方添加外键指向另一方的主键，外键必须唯一

范式

概念：在设计数据库时，需要遵循的一些规范。

分类：

第一范式：每一列都是不可分割的原子数据项
第二范式：在第一的基础上，非码属性必须完全依赖于候选码（消除非主属性对主码的部分函数依赖）
1. 函数依赖：A->B，如果通过A属性(属性组)的值，可以确定唯一B属性的值。学号->姓名 (学号，课程名称) ->分数
2. 完全函数依赖：A->B，如果A时一个属性组，则B属性值确定需要依赖A属性组中所有的属性值 (学号，课程名称) -> 分数
3. 部分函数依赖：A->B，如果A时一个属性组，则B属性值确定需要依赖A属性组中的一部分属性值 (学号，课程名称) -> 姓名
4. 传递函数依赖：A->B , B->C 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性(属性组)的值可以确定唯一C属性的值，则称C传递依赖于A 学号->系名, 系名->系主任
5. 码:如果在一张表中，一个属性或属性组，被其他所有属性完全依赖，则称这个属性为该表的码 (学号，课程名称):
  - 主属性：码属性中的所有属性
  - 非主属性：除过码属性组的是户型
第三范式：在第二的基础上，任何非主属性不依赖于其他非主属性

三大范式

第一范式
第二范式

第三范式

数据库的备份和还原

命令行备份：mysqldump -u用户名 -p密码数据库名> 保存路径还原
1. 登录数据库
2. 创建数据库
3. 使用数据库
4. 执行文件 -> source 文件路径
图形化工具还原->执行sql脚本

多表查询

# 创建部门表 
CREATE TABLE dept(   
    id INT PRIMARY KEY AUTO_INCREMENT,   
    NAME VARCHAR(20) 
);
 
INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');  
 
# 创建员工表 
CREATE TABLE emp (   
    id INT PRIMARY KEY AUTO_INCREMENT,   
    NAME VARCHAR(10),   
    gender CHAR(1),   -- 性别   
    salary DOUBLE,   -- 工资   
    join_date DATE,  -- 入职日期   
    dept_id INT,   
    FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键，关联部门表(部门表的主键) 
);   
 
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男 ',7200,'2013-02-24',1); 
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男 ',3600,'2010-12-02',2); 

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'200808-08',2); 
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女 ',5000,'2015-10-07',3); 
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女 ',4500,'2011-03-14',1)

-- 笛卡尔积
-- 集合A,B，取两个集合的所有排列组合，要完成多表查询需要消除无用的数据
select * from emp,dept;

多表查询的分类

内连接查询

隐式内连接：使用where条件消除无用数据
-- 查询所有员工信息和对应的部门信息
SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;

-- 查询员工表的名称性别以及部门表的名称
SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.dept_id = dept.id;

-- !!!!!!!!!!!!!!!!!!!!!!! 别名法
SELECT
t1.name,t1.gender,t2.name
FROM
emp t1,dept t2
WHERE
t1.dept_id = t2.id;
显式内连接：语法：select 字段列表 from 表名1 inner join 表名2 on 条件
SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id
SELECT * FROM emp JOIN dept ON emp.dept_id = dept.id

SELECT
t1.name,t1.gender,t2.name
FROM
emp t1
JOIN
dept t2
ON
t1.dept_id = t2.id
注意事项：
- 从哪些表中查询数据
- 确定查询条件

外连接查询

左外链接：
语法：select 字段列表 from 表1 left [outer] join 表2 on 条件
-- 查询所有员工信息，如果员工有部门则查询部门名称，没有部门则不显示部门名称
SELECT
t1.*,t2.name
FROM
emp t1, dept t2
WHERE
t1.dept_id = t2.id

-- 新加入的没有部门的员工无法显示！
使用左外链接
-- 左外连接
SELECT
t1.*,t2.name
FROM
emp t1
LEFT JOIN
dept t2
ON
t1.dept_id = t2.id

-- 可以显示没有部门的新员工
查询的是左表所有数据及其交集

右外连接：
-- 右外连接
SELECT
t1.*,t2.name
FROM
emp t1
RIGHT JOIN
dept t2
ON
t1.dept_id = t2.id
查询的是左表所有数据及其交集

子查询

概念：查询中欠条查询，称嵌套查询为子查询

-- 查询工资最高的员工信息
-- 1.查询最高工资是多少
SELECT MAX(salary) FROM emp;
-- 2.查询员工信息并且工资等于最高工资
SELECT * FROM emp WHERE salary = 9000;

-- 合并成一条
SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);

子查询的不同情况
1. 子查询的结果是单行单列的: 子查询可以作为条件，使用运算符运算
  -- 员工工资小于平均工资的人
  -- ↓其结果是单行单列的
  SELECT AVG(salary) FROM emp
  
  -- 可以用运算符
  SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);

子查询的结果是多行单列的（单行多列）：子查询可以作为条件，使用运算符in来判断
-- 查询财务部和市场部所有员工信息
-- 1.查询财务部id
-- 2.查询dept_id是财务部id的员工
SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
SELECT * FROM emp WHERE dept_id IN (3,2)

-- 最终
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部')

子查询的结果是多行多列的：子查询可以作为一张虚拟表进行多表查询
-- 查询员工入职日期是2011年11月11号之后的员工信息和部门信息
-- 1.员工入职日期在日期之后的员工信息
SELECT * FROM emp WHERE join_date > '2011-11-11'
-- 2.将查询结果作为一张表与部门关联
SELECT
t1.*, t2.*
FROM
dept t1 ,(SELECT * FROM emp WHERE join_date > '2011-11-11') t2
WHERE
t1.id = t2.dept_id

-- 使用普通内连接
SELECT
t1.*,t2.*
FROM
emp t1, dept t2
WHERE
t1.dept_id = t2.id AND t1.join_date > '2011-11-11'

事务的基本介绍

概念

如果一个包含多个步骤的业务操作被事务管理，那么这些操作要么同时成功，要么同时失败

操作

开启事务
start transaction
回滚
rollback
提交事务
commit

MySQL中事务默认自动提交：一条(DML)增删改语句自动提交一次事务

修改事物的默认提交方式：

select @@autocommit;

-- 1 自动提交
-- 0 手动提交

-- 修改
set @@autocommit = 0;

事务的四大特征 ACID

原子性 Atomicity

Undo Log

不可分割性，要么同时成功，要么同时失败

转账：一方资金转出、一方资金转入，两个步骤必须同时成功或者同时失败

一致性 Consistency

事务执行前后，数据保持一致，多个事务对同一个数据读取的结果是相同的

转账：转账前和转账后，账目对的上

隔离性 Isolation

并发访问数据库时，一个用户的事务不会被其他事务所干扰，各个并发事务之间是独立的

持久性 Durability

事务一旦提交/回滚，将永久更新数据库表

Buffer Pool 、刷脏、Redo Log

事务 ACID 如何保证

原子性A

由 undo log 日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的 SQL

一致性C

由其他三个特征保证、程序代码要保证业务上的一致性

隔离性I

写-写操作：锁机制
读-写操作：由 MVCC 保证

持久性D

保证 Redo Log 与 Bin Log 一致
由 内存 + Redo Log 保证，MySQL 修改数据的同时在内存和 Redo Log 中记录这次操作，宕机时可以从 Redo Log 回复
InnoDB Redo Log 写盘， InnoDB 事务进入 Prepare 状态
如果前面 Prepare 成功， binlog 写盘，再继续将事务日志持久化到 binlog，如果持久化成功，那么 InnoDB 事务则进入 commit 状态（在 redo log 中写一个 commit 记录）
Redo Log 的刷盘会在系统空闲时进行

为了降低 IO 成本，在内存设置 Buffer Pool 来缓存数据，用 Redo Log 记录，确保 Redo Log 或磁盘上的表文件得到持久化更新，否则数据修改只发生在内存中的 buffer pool 中，一旦宕机，内存数据丢失，提交过的数据就不能确保持久性了

同样是写磁盘，为什么 Redo Log 比写数据文件快
B+ 树索引叶子节点 16KB 保证 IO 吞吐量，但在修改数据很少的情况下反而增加了 IO 成本；Redo Log 可以采用追加写入的方式，对于记录这种小修改就很快

事务的隔离级别

概念：多个事务之间是相互独立的，但如果多个事务操作同一批数据，则会引发一些问题。设置不同的隔离级别解决问题

并发事务存在的问题

脏读 Dirty Read

一个事务读取到另一个事务中没有提交的数据

丢失修改

当一个事务正在修改数据时，另一个事务也进入对数据进行了修改，导致第一个事务的修改被干掉了，那么第一个事务的修改就丢失了

不可重复读 Unrepeatabled Read

在同一个事务中，两次读到的数据不一样（虚读）

幻读

一个事务读取表中的某些数据，中间另一个事务进入对数据作了修改，原本的事务查到了一些原本不存在的记录，就像发生了幻觉一样

隔离级别

SQL标准定义了四个隔离级别：

READ-UNCOMMITTED（读取未提交）：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读
READ-COMMITTED(读取已提交)：允许读取并发事务已提交的数据，可以组织脏读，但是幻读或不可重复读扔可能发生
REPEATABLE-READ(可重复读)：对同一字段的多次读取结果是一致的，除非数据本身是被事务自己修改的，可以阻止不可重复读和脏读，但幻读仍可能发生
- 为了保证可重复读，在第一次读取后，事务结束前，新来的数据并不会被理会，不会被查出来。但在进行范围查找时，如果新数据在范围内，就可能被一起查出来
SERIALIZABLE(序列化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间完全不可能产生干扰，也就是说，该界别可以防止脏读幻读和不可重复读

隔离界别	脏读	不可重复读	幻读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	X	√	√
REPEATABLE-READ	X	X	√
SERIALIZABLE	X	X	X

info

MySQL InnoDB默认的隔离级别为REPEATABLE-READ，可以通过 SELECT @@tx_iso lation;命令查看

注意：隔离级别从小到大，安全性越来越高，但是效率越来越低

MVCC 多版本并发控制

ReadView + 版本链

多版本并发控制
读取数据时，通过一种类似快照的方式将数据保存下来，这样读锁和写锁不冲突了，不同的事务 session 会看到自己特定版本的数据，版本链
MVCC 只在 READ COMMITTED 和 REPETABLE READ 两个隔离级别下工作。其他两个隔离级别与 MVCC 不兼容，因为 READ UNCONMMITED 总是读取最新的数据行，而不是符合当前事务版本的数据行；而 SERIALIZABLE 则会对所有读取的行都加锁

聚簇索引记录中必有两个必要的隐藏列
trx_id：用来存储每次对某条聚簇索引记录进行修改时的事务ID，自增
roll_pointer：每次对哪条聚簇索引记录有修改时，都会把老版本写入 undo log。这个 roll_pointer 存储的是一个指针，指向这条聚簇索引记录的上一个版本的位置，通过它来获得上一个版本的信息（注意：插入操作的 undo log 没有这个属性，因为它没有老版本）

读已提交与可重复度的区别

Read Committed 与 Repeatable Read 的区别在于，它们生成 ReadView 的策略不同

ReadView

可以看成快照，并且指定查询版本链中的哪一条数据
m_ids 表示在生成 ReadView 时当前系统中活跃的读写事务的事务ID列表（就是未提交）
min_trx_id 表示在生成 ReadView 时当前系统中活跃的读写事务中最小的事务ID，也就是 m_ids 中的最小值
max_trx_id 表示生成 ReadView 时系统中应该分配给下一个事务的事务ID
creator_trx_id 表示生成该 ReadView 的事务的事务ID
开始事务时，创建一个 ReadView，ReadView 维护当前活动的事务 ID，即未提交的事务ID，排序生成一个 数组（ReadView 中维护了一个未提交事务的ID组成的数组 m_ids）访问数据，获取数据中的事务ID（获取的是事务ID最大的记录），对比 ReadView：
如果在 ReadView 的左边：都比 ReadView 小，可以访问（在左边意味着事务已经提交过）
如果在 ReadView 的右边：比 ReadView 大，或者就在 ReadView 中，不可以访问，获取 roll_pointer，取上一版本重新对比（在右边意味着，该事务在 ReadView 生成之后出现，在 ReadView 中意味着该事务还没提交）
trx_id == creator_trx_id 可以访问这个版本
trx_id < min_trx_id 可以访问这个版本
trx_id > max_trx_id 不可以访问这个版本
min_tex_id <= trx_id <= max_tex_id：
如果 trx_id 在 m_ids 中是不能访问这个版本的
还可能：在这个范围内，但是 ReadView m_ids 数组中不包含这个 trx_id （未提交的事务ID未必是连续的），那么说明这个 trx_id 对应的事务已经提交过了，就可以访问
已提交读 隔离级别下的事务在 每次查询 的开始都会生成一个独立的 ReadView，而 可重复读 隔离级别则在 第一次读 时生成一个 ReadView，之后的读都复用之前的 ReadView
这就是 MySQL 的 MVCC，通过版本链实现多版本，可并发读写、写读，通过 ReadView 生成策略的不同实现不同的隔离级别

m_ids	表示在生成 ReadView 时当前系统中活跃的读写事务的事务ID列表（就是未提交）
min_trx_id	表示在生成 ReadView 时当前系统中活跃的读写事务中最小的事务ID，也就是 m_ids 中的最小值
max_trx_id	表示生成 ReadView 时系统中应该分配给下一个事务的事务ID
creator_trx_id	表示生成该 ReadView 的事务的事务ID

SQL#

什么是SQL#

SQL的分类#

DDL：操作数据库、表#

操作数据库：CRUD#

操作表#

DML：表的增删改#

添加数据#

删除数据#

修改数据#

DQL：数据查询语言#

语法#

基础查询#

多个字段查询#

去除重复#

计算列#

起别名#

条件查询#

排序查询#

聚合函数#

分组查询#

分页查询#

注意

约束#

非空约束#

唯一约束#

主键约束#

外键约束#

数据库设计#

多表关系#

范式#

数据库的备份和还原#

多表查询#

多表查询的分类#

内连接查询#

外连接查询#

子查询#

事务的基本介绍#

概念#

操作#

事务的四大特征 ACID#

原子性 Atomicity#

一致性 Consistency#

隔离性 Isolation#

持久性 Durability#

事务 ACID 如何保证#

原子性A#

一致性C#

隔离性I#

持久性D#

事务的隔离级别#

并发事务存在的问题#

脏读 Dirty Read#

丢失修改#

不可重复读 Unrepeatabled Read#

幻读#

隔离级别#

info

MVCC 多版本并发控制#

读已提交与可重复度的区别#

ReadView#

SQL

什么是SQL

SQL的分类

DDL：操作数据库、表

操作数据库：CRUD

操作表

DML：表的增删改

添加数据

删除数据

修改数据

DQL：数据查询语言

语法

基础查询

多个字段查询

去除重复

计算列

起别名

条件查询

排序查询

聚合函数

分组查询

分页查询

约束

非空约束

唯一约束

主键约束

外键约束

数据库设计

多表关系

范式

数据库的备份和还原

多表查询

多表查询的分类

内连接查询

外连接查询

子查询

事务的基本介绍

概念

操作

事务的四大特征 ACID

原子性 Atomicity

一致性 Consistency

隔离性 Isolation

持久性 Durability

事务 ACID 如何保证

原子性A

一致性C

隔离性I

持久性D

事务的隔离级别

并发事务存在的问题

脏读 Dirty Read

丢失修改

不可重复读 Unrepeatabled Read

幻读

隔离级别

MVCC 多版本并发控制

读已提交与可重复度的区别

ReadView