MySQL学习笔记

1SHOW DATABASES; -- 查询所有数据库
2SELECT DATABASE(); -- 查询当前数据库
3CREATE DATABASE IF NOT EXISTS learndatabase DEFAULT CHARSET utf8mb4; -- 创建数据库
4USE learndatabase; -- 使用数据库
5DROP DATABASE IF EXISTS learndatabase; -- 删除数据库

 1SHOW TABLES; -- 查询数据库所有的表
 2DESC tableName; -- 查询表结构
 3SHOW CREATE TABLE tableName; -- 查询表的建表语句
 4
 5CREATE TABLE tableName( -- 创建表
 6                          id INT COMMENT '编号',
 7                          name VARCHAR(50) COMMENT '姓名',
 8                          age INT COMMENT '年龄',
 9                          gender VARCHAR(1) COMMENT '性别'
10) COMMENT '用户表';
11
12ALTER TABLE tableName ADD nickname VARCHAR(20) COMMENT '昵称'; -- 修改表,添加字段
13alter table tb add date DATE comment '入职日期';
14ALTER TABLE tableName CHANGE nickname username VARCHAR(30) COMMENT '用户名'; -- 修改字段名,同时修改字段类型
15ALTER TABLE tableName MODIFY username CHAR(30); -- 修改数据类型
16ALTER TABLE tableName DROP username; -- 删除字段username
17
18ALTER TABLE tableName RENAME TO tb; -- 修改表名
19
20DROP TABLE IF EXISTS tableName; -- 删除表
21TRUNCATE TABLE tableName; -- 删除表,重新创建该表;其实就是清空数据

1-- 选择字段插入
2INSERT INTO tb(id, name, age, gender, username, date) VALUES (1, '赵四', 13, '男', '四娘', '2001-05-28');
3-- 全部字段插入
4insert into tb values (1, '张三', 15, '女', '三爹', '2005-05-05');
5-- 插入多条数据
6insert into tb values (3, '王五', 29, '女', '五爹', '1998-12-15'),(4, '李四', 30, '男', '四爹', '1970-01-30');

1--  修改
2update tb set date='2010-09-01' where name='赵四';
3update tb set id=2, date='2001-05-28' where username='三爹';
4-- 没有加where, 改所有数据
5update tb set date='2000-01-01'

1--  删除
2delete from tb where id=1 and name='赵四';
3-- 删除索引
4delete from tb;

1-- 查询多个字段
2select id, name from tb;
3-- 查询所有, 写*效率不高,最好写出所有字段,清晰明了
4select * from tb;
5-- 设置别名
6select id as '爱迪', name as '姓名' from tb;
7-- 查询去重
8select distinct  * from tb;

 1-- and or not, && || !
 2select * from tb where age = 13;
 3select * from tb where age < 20;
 4select * from tb where age <= 20;
 5select * from tb where username is null ; -- 查询没有username字段的数据
 6select * from tb where username is not null ; -- 查询有username字段的数据
 7select * from tb where age != 13;
 8select * from tb where age <> 13;
 9select * from tb where age <= 20 && age >= 10;
10select * from tb where age <= 20 and age >= 10;
11select * from tb where age between 10 and 20; -- between and 包括最大值和最小值
12select * from tb where age=10 or age=13 or age=20;
13select * from tb where age in(10,13,20); -- age在in里面的数其一即可
14select * from tb where name like '__'; -- 查询姓名只有两个字符的数据
15select * from tb where username like  '%x'; -- 查询username最后一位是x的数据

1-- 聚合函数不计算null值
2select sum(age) from tb;
3select count(1) from tb;
4select count(*) from tb;
5select avg(age) from tb;
6select max(age) from tb;
7select sum(age) from tb where id=1;

1select gender, count(*) from tb group by gender; -- 根据性别分组,分组后统计数量,相当于对每个组分别处理
2select gender, avg(age) from tb group by gender; -- 性别分组,统计男女的平均年龄
3
4-- where在分组前进行过滤, having在分组后过滤,having可以对聚合函数过滤,where不行.
5-- 虽然组合在一起的sql语句很麻烦,但是一步一步地加条件来看,就不麻烦了,sql就是在简单查询结果基础上一步一步处理的.
6-- 先where得到有条件的表,然后group by得到两行分组后的数据,最后对这两行数据进行having筛选.
7select gender, count(*) from tb where age<= 30 and age >= 10 group by gender having count(*) > 2;
8-- 给聚合起别名
9select gender, count(*) as gender_count from tb where age<= 30 and age >= 10 group by gender having gender_count > 2;

1select * from tb order by age asc;
2select * from tb order by age desc;
3select * from tb order by date desc;
4-- 先按照age升序,age相同再按照date降序排序
5select * from tb order by age asc ,date desc;

1-- 分页查询 limit 起始索引,查询记录数
2-- 起始索引从0开始, 起始索引=(查询页码-1)*每页记录数
3-- 如果是第一页数据,可以省略
4select * from tb limit 0, 2;
5select * from tb limit 2, 3;

 1-- 查询用户
 2use mysql;
 3select * from user;
 4-- 创建用户 用户名@主机名 后面是密码,创建的用户没有权限
 5-- 创建用户bnaod,只能在当前主机访问,密码password
 6create user 'bnaod'@'localhost' identified by 'password';
 7-- 创建用户,在任意主机访问
 8create user 'bnaod'@'%' identified by 'password';
 9-- 修改用户密码
10alter user 'bnaod'@'localhost' identified with mysql_native_password by 'newpassword';
11-- 删除用户
12drop user 'bnaod'@'localhost';

1-- 查询权限
2show grants for 'bnaod'@'localhost';
3-- 授予权限 on后接数据库名.表名 *代替全部 如*.*表示所有数据库的所有表 后面是用户
4grant all on learndatabase.* to 'bnaod'@'localhost';
5-- 撤销权限
6revoke all on learndatabase.* from 'bnaod'@'localhost';

1select concat('hello', ' mysql');
2select lower('HEllo');
3select upper('HEllo');
4select lpad('01', 5, '@');
5select rpad('01', 5, '@');
6select trim(' HELLO  MYSQL ');
7select substring('HELLO,MYSQL', 1, 5); -- 这个不是字符串下标,就是单纯的第一个
8-- 将编号补成5位数,如1->00001
9select lpad(id, 5, '0') from tb;

1select ceil(1.1);
2select floor(1.1);
3select mod(3,4);
4select mod(4,4);
5select rand();
6select round(pi(), 7);
7-- 生成6位随机验证码
8select lpad(1000000 * round(rand(),6), 6, '0');

 1select curdate();
 2select curtime();
 3select now();
 4select year(now());
 5select year(date) from tb;
 6select month(date) from tb;
 7select day(date) from tb;
 8select date_add(now(), INTERVAL 70 DAY );
 9select date_add(now(), INTERVAL 70 MONTH );
10select date_add(now(), INTERVAL 70 YEAR );
11select datediff(now(), '2001-05-28');
12select datediff(now(), '1992-08-17');
13-- 员工入职天数降序排列
14select datediff(now(), date) as jointime from tb order by jointime desc ;

 1select if(true, 'ok', 'error');
 2select if(false, 'ok', 'error');
 3
 4select ifnull('ok', 'default');
 5select ifnull('', 'default');
 6select ifnull(null, 'default');
 7
 8select case when true then 'res1' else 'default' end;
 9
10-- 查询员工年龄,如果大于50就显示老毕登, 如果大于15就显示中壁灯, 如果小于15就显示小壁灯
11select id,
12       name,
13       (case when age>=50 then '老毕登' when age>=15 then'中壁灯' else '小壁灯' end) as nickname
14from tb ;

 1create table user(
 2    id int primary key auto_increment, -- 主键,且自动增长
 3    name varchar(10) not null unique , -- 不为null,唯一
 4    age int check ( age > 0 and age <= 120 ), -- 年龄在0到120之间
 5    status char(1) default '1', -- 默认为1
 6    gender char(1)
 7) comment 'user table'
 8
 9desc user;
10
11insert into user(name, age, status, gender) values ('tom1', 19, '1', '男'), ('tom2', 31, '0', '男');
12insert into user(name, age, status, gender) values (null, 20,'1','女');
13insert into user(name, age, status, gender) values ('tom1', 20,'1','女');
14insert into user(name, age, status, gender) values ('tom3', -1,'1','女');
15insert into user(name, age, gender) values ('tom3', 23,'女');

 1-- 准备
 2create table dept(
 3    id int auto_increment primary key ,
 4    name varchar(50) not null
 5);
 6insert into dept(id, name) VALUES (1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办');
 7alter table user add dept_id int;
 8alter table user add manager_id int;
 9insert into user(id, name, age, status, gender, dept_id) values (null, 'jerry2', 73, '0', '女', null);
10
11-- 添加外键.在创建表时额外添加一行, 也可以额外修改
12-- constraint [外键名称] foreign key (外键字段名) references 主表(主表列名)
13alter table user add constraint fk_user_dept_id foreign key (dept_id) references dept(id);
14-- 这样父表的字段不能直接删除,因为有子表外键关联
15-- 删除外键
16-- alter table 表名 drop foreign key 外键名称;
17alter table user drop foreign key fk_user_dept_id;

1-- constraint [外键名称] foreign key (外键字段名) references 主表(主表列名) on update cascade on delete cascade
2-- on update在更新时要进行的行为,on delete在删除时要进行的行为
3alter table user add constraint fk_user_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade;
4alter table user add constraint fk_user_dept_id foreign key (dept_id) references dept(id) on update set null on delete set null ;

1-- 进行笛卡尔积, 组合的所有情况
2select * from user,dept;
3-- 消除笛卡尔积, 加一个条件, 这里没有外键也可以
4select * from user,dept where dept_id = dept.id;

1-- 联合查询 union union all,将多次查询的结果合并起来, 列数必须保持一致
2-- select from union [all] select from
3-- 将年龄小于50岁 和 状态为1 的员工查询出来
4select * from user where age < 50 union all select * from user where status = 1; -- 结果有重复,直接将查询结果合并
5select * from user where age < 50 union select * from user where status = 1; -- 结果没重复,合并后去重

1select * from t1 where column1=(select column1 from t2) 外部可以是select/update/insert/delete

 1create table account(
 2    id int auto_increment primary key ,
 3    name varchar(50) ,
 4    money int
 5);
 6insert into account(id, name, money) VALUES (null, 'lisan', 2000);
 7insert into account(id, name, money) VALUES (null, 'wangsan', 2000);
 8-- 恢复数据
 9update account set money = 2000 where name = 'lisan' or name = 'wangsan';
10-- 查询lisan余额
11select * from account where name='lisan';
12-- 转账操作
13update account set money = money - 1000 where name = 'lisan';
14update account set money = money + 1000 where name = 'wangsan';

 1-- 查看事务的提交方式
 2select @@autocommit;
 3-- 设置为手动提交, 0为手动,1为自动
 4set @@autocommit = 0; -- 设置为手动
 5set @@autocommit = 1; -- 设置为自动
 6-- 操作
 7update account set money = money - 1000 where name = 'lisan';
 8update account set money = money + 1000 where name = 'wangsan';
 9-- 提交事务
10commit;
11-- 回滚事务, 如果出错了就回滚
12rollback;

1-- 开启事务
2start transaction;
3begin;
4-- 提交事务
5commit;
6-- 回滚
7rollback;

1-- 查看事务隔离级别
2select @@transaction_isolation;
3-- 设置事务隔离级别
4-- set [session|global] transaction isolation level {read uncommitted|read committed|repeatable read|serializable};
5set session transaction isolation level serializable ;
6set session transaction isolation level repeatable read ;

1-- 模拟脏读
2-- 窗口1
3set session transaction isolation level read uncommitted ;
4start transaction ;
5select * from account; -- 这时候可以看到lisan减了1000块,就是因为可以读到未提交的(read uncommitted)
6-- 窗口2
7start transaction ;
8update account set money = money - 1000 where name='lisan';

 1-- 模拟不可重复读
 2-- 窗口1
 3set session transaction isolation level read committed ;
 4start transaction ;
 5select * from account; -- 在commit之前查询
 6select * from account; -- 在commit之后查询, 可以查询到变更的.
 7commit ;
 8-- 窗口2
 9start transaction ;
10update account set money = money + 1000 where name='lisan';
11commit ;

 1-- 模拟幻读
 2-- 窗口1
 3set session transaction isolation level repeatable read ;
 4start transaction ;
 5select * from account where id=3; -- 1查不到
 6insert into account(id, name, money) VALUES (null, 'zangsan', 3000); -- 4这边后执行,发现插入不了
 7select * from account where id=3; -- 5再次查询,还是没有, 这是因为已经解决了不可重复读了.
 8-- 窗口2
 9start transaction ;
10insert into account(id, name, money) VALUES (null, 'zangsan', 3000); -- 2这边先insert
11commit ; -- 3先提交,这时已经有这一行了.

 1-- 查询建表语句,默认innodb
 2show create table account;
 3# CREATE TABLE `account` (
 4#                            `id` int NOT NULL AUTO_INCREMENT,
 5#                            `name` varchar(50) DEFAULT NULL,
 6#                            `money` int DEFAULT NULL,
 7#                            PRIMARY KEY (`id`)
 8# ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
 9
10-- 查询当前数据库支持的存储引擎
11show engines;
12-- 创建表,指定引擎为MyISAM
13create table my_myisam(
14    id int auto_increment primary key,
15    name varchar(50)
16)engine = MyISAM;
17-- 创建表,指定引擎为MEMORY
18create table my_memory(
19    id int auto_increment primary key ,
20    name varchar(50)
21)engine = Momory;

 1-- 创建索引 create [unique|fulltext] index index_name on table_name (index_col_name,..)
 2-- 查看索引 show index from table_name;
 3show index from learndatabase.user;
 4-- 删除索引 drop index index_name on table_name;
 5-- 为name字段创建索引,该字段可能重复(常规索引)
 6create index idx_user_name on user(name);
 7-- 为phone创建唯一索引(唯一索引, 单列索引)
 8create unique index idx_user_phone on user(phone);
 9-- 为多个字段创建索引(联合索引),使用最频繁的字段放左侧(最左前缀原则)
10create index idx_user_name_phone_age on user(name, phone, age);

1-- show [session|global] status;
2-- 查看select, update, delete, insert等执行次数
3show global status like 'Com_______';

1-- 查看是否开启
2show variables like 'slow_query_log';

 1select @@have_profiling;
 2-- 查看是否开启
 3select @@profiling;
 4-- 设置开启
 5set profiling = 1;
 6-- 查看耗时情况
 7show profiles;
 8-- 查看指定query_id的sql语句每个阶段的耗时情况
 9-- show profile for query [query_id];
10show profile for query 3;

1explain select * from learndatabase.user where age < 50;

1-- 验证索引效率:先执行一条没有索引的sql,再创建索引再执行
2select * from tb_sku where sn='100001';
3create index idx_sku_sn on tb_sku(sn);
4select * from tb_sku where sn='100001';

1-- 假设创建了联合索引且顺序为profession,age,status,那么
2explain select * from user where profession='软件工程' and age=31 and status='0'; -- 会用到索引,所有字段都走索引
3explain select * from user where profession='软件工程' and age=31; -- 会用到索引,所有字段都走索引
4explain select * from user where profession='软件工程' -- 会用到索引,所有字段都走索引
5explain select * from user where age=31 and status='0'; -- 不会用到索引
6explain select * from user where profession='软件工程' and status='0'; -- 只有profession会用到索引
7explain select * from user where age=31 and profession='软件工程' and status='0'; -- 会用到索引,这个sql语句的顺序无关,只需要存在

1explain select * from user where profession='软件工程' and age<31 and status='0'; -- profession和age走索引,status不走
2explain select * from user where profession='软件工程' and age<=31 and status='0'; -- 都走,其实只有=那里都走

 1-- 索引列运算操作, 会导致索引失效
 2explain select * from user where substring(phone, 10, 2) = '15'; -- 没用索引
 3-- 字符串没加引号, 会导致索引失效
 4explain select * from user where name = lisan;
 5explain select * from user where profession='软件工程' and age=31 and status=0; -- status没有走索引
 6-- 模糊查询, 如果仅仅是尾部模糊, 索引不会失效; 头部模糊匹配会失效
 7explain select * from user where profession like '软件%'; -- 走索引
 8explain select * from user where profession like '%软件'; -- 不走
 9explain select * from user where profession like '%件%'; -- 不走
10-- or连接的条件: 前面列有索引,后面列没索引,那么索引都不会用到; 只有都有索引才会用到
11explain select * from user where phone='17712312310' or age=13; -- age没索引,所以没有用任何索引
12-- 数据分布影响: 如果MySQL评估使用索引比全表更慢,则不使用索引
13explain select * from user where phone>'17700000000'; -- 绝大部分数据都满足,这时就没用索引
14explain select * from user where phone is not null; -- 绝大部分都满足,没走索引
15explain select * from user where phone is null; -- 绝大部分都不满足,走索引

1-- 比如既有单个索引,又有联合索引的情况.
2-- use index: 建议用哪个索引 ignore index: 不用哪个索引 force index:必须用哪个索引
3explain select * from user use index(idx_user_pro) where profession='软件工程';
4explain select * from user ignore index(idx_user_pro) where profession='软件工程';
5explain select * from user force index(idx_user_pro) where profession='软件工程';

1-- 如现有索引profession, age, status的联合索引
2explain select id, profession, age, status from user where profession='软件工程' and age=31 and stauts='0'; -- 效率高
3explain select id, profession, age, status, name from user where profession='软件工程' and age=31 and stauts='0'; -- 效率低

1create index idx_xxxx on table_name(column(n));

1select count(distinct email) / count(*) from tb_user; -- 全部字段的选择性
2select count(distinct substring(email, 1, 5)) / count(*) from tv_user; -- email字段截取前5个的选择性

 1explain select id, age, phone from tb_user order by age, phone -- 没有索引时, using filesort
 2
 3create index idx_user_age_phone_aa on tb_user(age, phone); -- 创建两个升序索引
 4explain select id, age, phone from tb_user order by age, phone; -- 有索引时, using index
 5
 6explain select * from tb_user order by age, phone; -- 没用索引, using filesort, 这时不是覆盖查询了, 回表了.
 7
 8explain select id, age, phone from tb_user order by age desc, phone desc; -- 反向利用索引, using index
 9
10explain select id, age, phone from tb_user order by age asc, phone desc; -- 没有用到索引 using filesort
11
12create index idx_user_age_phone_ad on tb_user(age asc, phone desc); -- 创建一个升序,一个降序索引
13explain select id, age, phone from tb_user order by age asc, phone desc; -- 用到索引 using index

1explain select profession, count(*) from tb_user group by profession; -- 没有查询的时候, 用临时表
2create index idx_user_pro_age_sta on tb_user(profession, age, status); -- 创建联合索引
3explain select profession, count(*) from tb_user group by profession; -- 用了联合索引
4explain select age, count(*) from tb_user group by age; -- 违反最左前缀法则, 用到临时表
5explain select profession, age, count(*) from tb_user group by profession, age; -- 用联合索引
6explain select age, count(*) from tb_user where profession='软件工程' group by age; -- 用到联合索引, 最左前缀

1select * from tb_sku limit 1000000, 10;  -- 19s
2select t.* from tb_sku t, (select  id from tb_sku order by id limit 1000000, 10) a where t.id = a.id; -- 11s
3

1-- 使用主键索引
2update student set no='20010101' where id=1; -- 此时事务加的行锁, 对其他并发事务修改其他行没有影响
3update student set no='20010101' where name='韦一笑'; -- 当name字段没有索引时,此时加的是表锁, 对表的其他行均不能使用update变更

 1-- 视图
 2-- 介绍: 视图是一种虚拟存在的表. 视图中的数据并不在数据库中实际存在, 行和列数据来定义自定义视图的查询中使用的表, 并且是在使用视图时动态生成的
 3-- 作用: 1.简单:简化用户对数据的理解, 也可以简化他们的操作. 那些被经常使用的查询可以被定义为视图
 4--      2.安全:数据库可以授权,但不能授权到数据库特定的行和列上. 通过视图用户只能查询和修改他们所能见到的数据
 5--      3.数据独立: 视图可帮助用户屏蔽真实表结构带来的影响.
 6
 7-- 创建: create [or replace] view 视图名称[(列名列表)] as select语句 [with [cascaded|local] check option]
 8create or replace view stu_v_1 as select id,name from student where id<=10;
 9-- 查询: 查看视图创建语句 show create view 视图名称;
10--      查看视图数据 select * from 视图名称....;
11show create view stu_v_1;
12select * from stu_v_1;
13select * from stu_v_1 where id<3;
14-- 修改: 方式一:create [or replace] view 视图名称[(列名列表)] as select语句 [with [cascaded|local] check option]
15--      方式二:alter view 视图名称[(列名列表)] as select语句 [with [cascaded|local] check option]
16create or replace view stu_v_1 as select id,name,no from student where id<=10;
17alter view stu_v_1 as select id,name from student where id<=10;
18-- 删除: drop view [if exist] 视图名称 [,视图名称]...
19drop view if exists stu_v_1;
20
21
22-- 检查选项 with [cascaded|local] check option
23-- 当使用with check option子句创建视图, mysql会通过视图检查正在更改的每个行, 如插入,删除,更新, 使其符合试图定义
24-- mysql允许基于另一个视图创建视图, 它还会检查依赖视图中的规则以保持一致性. 为了确定检查范围提供了两个选项cascaded和local, 默认cascaded
25-- 没有check的视图, 可以随便插入数据
26create or replace view stu_v_1 as select id, name from student where id <= 10;
27insert into stu_v_1 values (30, 'tom'); -- 可以插入
28-- 普通的with check option的作用:
29create or replace view stu_v_1 as select id, name from student where id <= 10 with local check option ;
30insert into stu_v_1 values (6,'tom'); -- 可以插入,满足要求
31insert into stu_v_1 values (13,'tom'); -- 不能插入, 不满足<=10的要求
32-- cascaded选项, 级联的意思, 就是向下的视图不管有没有check选项都要检查
33create or replace view stu_v_1 as select id, name from student where id <= 20; -- 视图1, 没有check
34create or replace view stu_v_2 as select id, name from stu_v_1 where id >= 10 with cascaded check option ; -- 视图2, 级联检查
35insert into stu_v_2 values (7,'tom'); -- 不行, 不满足视图2的要求
36insert into stu_v_2 values (30,'tom'); -- 不行, 不满足视图1的要求
37insert into stu_v_2 values (15,'tom'); -- 可以
38-- 再在基础上新增一个视图3, 这时cascaded不会影响上层视图
39create or replace view stu_v_3 as select id, name from stu_v_2 where id >= 15;
40insert into stu_v_3 values (11,'tom'); -- 满足视图1和2的要求, 可以. 视图3的要求没有check所以条件无所谓
41insert into stu_v_3 values (17,'tom'); -- 满足要求, 可以
42insert into stu_v_3 values (28,'tom'); -- 不满足
43-- LOCAL选项: 它只影响本视图, 不会影响上下.
44
45-- 视图的更新: 要使视图更新, 视图中的行与基础表的行之间必须存在一对一的关系
46-- 视图包含以下任一项 视图不可更新
47-- 1.聚合函数或窗口函数(sum, min, max count等)
48-- 2.distinct
49-- 3.group by
50-- 4.having
51-- 5.union或union all
52
53
54-- 视图案例
55-- 1.为了保证数据库安全, 开发人员在操作tb_user表时, 只能看到用户的基本字段,屏蔽手机号和邮箱两个字段
56create view tb_user_view as select id, name, profession, gender, status, create_time from tb_user;
57select * from tb_user_view;
58-- 2.查询学生选修的课程(三表联查),为了简化操作,定义视图
59create view tb_stu_cource_view as select s.name student_name, s.no student_no, c.name course_name from student s, student_course sc, course c where s.id=sc.studentid and sc.courseid=c.id;

  1-- 存储过程: 事先经过编译并存储在数据库中的一段SQL语句的集合, 调用存储过程可以简化应用开发人员的很多工作,
  2-- 减少数据在数据库和应用服务器之间的传输,对提高数据处理的效率有好处
  3-- 存储过程的思想很简单, 就是数据库SQL语言层面的代码封装与重用
  4-- 特点: 封装复用; 可以接收参数, 返回数据; 减少网络交互提升效率
  5
  6
  7-- 创建存储过程
  8# create procedure 存储过程名称([参数列表])
  9# begin
 10#  -- sql语句
 11# end;
 12create procedure p1()
 13begin
 14    select count(*) from user;
 15end;
 16-- 如果在命令行中创建存储过程, 需要指定sql语句的结束符, 通过delimiter
 17-- delimiter $$   设置结束以$$结束
 18# create procedure p1()
 19# begin
 20#     select count(*) from user;
 21# end$$
 22
 23-- 调用
 24-- call 名称([参数]);
 25call p1();
 26-- 查看存储过程
 27-- select * from information_schema.ROUTINES where ROUTINE_SCHEMA='数据库名字'; -- 查询指定数据库存储过程及状态信息
 28-- show create procedure 存储过程名称; -- 查询某个存储过程定义
 29select * from information_schema.ROUTINES where ROUTINE_SCHEMA='bbs';
 30show create procedure p1;
 31-- 删除
 32-- drop procedure if exists 存储过程名称;
 33drop procedure if exists p1;
 34
 35
 36-- 变量
 37
 38-- 系统变量是是MySQL服务器提供的, 不是用户定义的, 属于服务器层面. 分为全局变量(global)和会话变量(session)
 39-- 全局变量在所有MySQL会话都有效, 会话变量只在当前会话有效
 40-- 查看系统变量, 默认都是session
 41-- 查看所有系统变量 show [global|session] variables;
 42-- 通过模糊匹配查看变量 show [global|session] variables like '....';
 43-- 查看指定变量 select @@[global|session]系统变量名;
 44show variables ;
 45show session variables ;
 46show global variables ;
 47show variables like 'auto%';
 48show global variables like 'auto%';
 49select @@autocommit;
 50select @@session.autocommit;
 51select @@global.autocommit;
 52
 53-- 设置系统变量, MySQL服务重启后, 所设置的全局变量会失效, 要想不失效,可以在/etc/my.cnf中配置
 54-- set [session|global] 系统变量名=值;
 55-- set @@[session|global] 系统变量名=值;
 56set session autocommit = 0;
 57set global autocommit = 0;
 58
 59-- 用户定义变量: 不用提前声明, 用的时候直接用@变量名
 60-- 作用域是当前会话, 其他会话用不了.
 61-- 赋值, 推荐使用:=, 因为=可以作为比较运算符
 62-- set @var_name =expr [,@var_name=expr]...;
 63-- set @var_name :=expr [,@var_name:=expr]...;
 64-- select @var_name :=expr [,@var_name :=expr] ...;
 65-- select 字段名 into @var_name from 表名;
 66set @caonima='cnm';
 67set @mygender:='男', @myhobby:='java';
 68select @cnm:='red';
 69select count(*) into @usercount from learndatabase.user;
 70-- 使用
 71-- select @var_name;
 72select @caonima;
 73select @mygender, @myhobby, @usercount;
 74select @abc; -- 没声明赋值,结果是null
 75
 76-- 局部变量: 需要定义的在局部生效的变量, 访问需要declare声明. 可用作存储过程的局部变量和输入参数
 77-- 局部变量的范围是在其内声明的begin and块.
 78-- 声明, 没有@符号了
 79-- declare 变量名 变量类型 [default...];
 80-- 变量类型: int, bigint, char, varchar, date, time等
 81-- 赋值, 没有@符号.
 82-- set 变量名=值;
 83-- set 变量名:=值;
 84-- select 字段 into 变量名 from 表名;
 85create procedure p2()
 86begin
 87    declare stu_count int default 0;
 88    select count(*) into stu_count from learndatabase.user;
 89    select stu_count;
 90end;
 91call p2();
 92
 93
 94-- 流程控制结构
 95-- if语法
 96# if 条件1 then
 97#     ...
 98# elseif 条件2 then
 99#     ...
100# else
101#     ...
102# end if;
103create procedure p3()
104begin
105    declare score int default 59;
106    declare result varchar(10);
107
108    if score >= 85 then
109        set result:='优秀';
110    elseif score >=60 then
111        set result:='及格';
112    else
113        set result:='不及格';
114    end if;
115
116    select result;
117end;
118
119call p3();
120
121-- 参数
122-- 类型: IN 作为输入的参数, 需要调用时传值, 此为默认
123--      OUT 作为输出的参数, 该参数可以作为返回值
124--      INOUT 既可以作为输入, 也可以作为输出的参数
125# create procedure 存储过程名称([in/out/inout 参数名 参数类型])
126# begin
127#     -- sql
128# end;
129-- 判断成绩的存储过程
130create procedure p4(in score int, out result varchar(10))
131begin
132    if score >= 85 then
133        set result:='优秀';
134    elseif score >=60 then
135        set result:='及格';
136    else
137        set result:='不及格';
138    end if;
139end;
140call p4(90, @result);
141select @result;
142
143-- 将传入的200分制的分数, 换算成100分制的分数
144create procedure p5(inout score double)
145begin
146    set score:=score * 0.5;
147end;
148set @score:=199.8;
149call p5(@score);
150select @score;
151
152-- case
153-- 语法1
154# case case_value
155#     when when_value1 then statement_list1 -- 当case_value=value1时执行when之后的sql语句
156#     [when when_value2 then statement_list2]...
157#     [else statement_list] -- 都不满足时执行的sql语句
158# end case;
159-- 语法2
160# case
161#     when search_condition1 then statement_list1 -- 当search_condition1为true时执行
162#     [when search_condition2 then statement_list2]...
163#     [else statement_list] -- 都不满足时执行的sql语句
164# end case;
165-- 根据传入的月份,判断月份所属的季节
166create procedure p6(in month int)
167begin
168    declare result varchar(10);
169    case
170        when month >= 1 and month <= 3 then set result:='第一季度';
171        when month >= 4 and month <= 6 then set result:='第二季度';
172        when month >= 7 and month <= 9 then set result:='第三季度';
173        when month >= 10 and month <= 12 then set result:='第四季度';
174        else set result:='错误输入!';
175    end case;
176    select concat('输入的月份为', month, ', 它所属的季度为: ', result);
177end;
178call p6(4);
179
180-- while循环: 有条件的循环, 满足条件才循环
181# while 条件 DO
182#     sql...;
183# end while;
184-- 计算1累加到n
185create procedure p7(in count int)
186begin
187    declare i int default 1;
188    declare res int default 0;
189    while i <= count do
190        set res := res + i;
191        set i := i + 1;
192    end while;
193    select res;
194end;
195
196create procedure p7(in count int)
197begin
198    declare res int default 0;
199    while count > 0 do
200            set res := res + count;
201            set count := count - 1;
202        end while;
203    select res;
204end;
205drop procedure p7;
206call p7(100);
207
208-- repeat循环: 满足循环退出
209# repeat
210#     sql...;
211#     until 条件; -- 当条件为真,退出循环
212# end repeat;
213-- 1加到n
214create procedure p8(in count int)
215begin
216    declare res int default 0;
217    repeat
218        set res:=res+count;
219        set count:=count-1;
220    until count <0 end repeat;
221    select res;
222end;
223call p8(10);
224
225-- loop循环: 简单循环, 如果不增加退出循环条件那么就是死循环, 它配合leave和iterate使用
226-- leave: 退出循环
227-- iterate: 必须用在循环中, 作用是跳过当前循环剩下语句进入下循环
228# [begin_label:] loop -- 指定标识label
229#     sql...;
230#     leave label; -- 退出循环
231#     iterate label; -- 跳到下次循环
232# end loop [end_label];
233-- 实现1累加n
234create procedure p9(in count int)
235begin
236    declare res int default 0;
237    loop_label: loop
238        set res := res + count;
239        set count := count - 1;
240        if count <= 0 then
241            leave loop_label;
242        end if;
243    end loop loop_label;
244    select res;
245end;
246call p9(100);
247-- 计算1-n的偶数累加
248create procedure p10(in count int)
249begin
250    declare res int default 0;
251    if count % 2 = 1 then
252        set count:= count -1;
253    end if;
254    odd_sum:loop
255        set res := res + count;
256        set count := count - 2;
257        if count <= 0 then
258            leave odd_sum;
259        end if;
260    end loop odd_sum;
261    select res;
262end;
263
264create procedure p10(in count int)
265begin
266    declare res int default 0;
267    odd_sum: loop
268        if count <= 0 then
269            leave odd_sum;
270        end if;
271        if count % 2 = 1 then
272            set count := count - 1;
273            iterate odd_sum;
274        end if;
275        set res := res + count;
276        set count := count - 1;
277    end loop odd_sum;
278    select res;
279end;
280
281drop procedure p10;
282call p10(10);
283
284
285-- 游标: 存储查询结果集的数据类型, 在存储过程和函数中可以使用游标对结果集进行循环处理
286-- 声明游标: declare 游标名称 cursor for 查询语句;
287-- 打开游标: open 游标名称;
288-- 获取游标记录: fetch 游标名称 into 变量[,变量];
289-- 关闭游标: close 游标名称;
290-- 案例: 根据传入的参数uage, 查询用户表中所有年龄小于uage的用户姓名name和专业profession, 然后将name和profession插入新表
291create procedure p11(in uage int)
292begin
293    declare uname varchar(100);
294    declare ugender char(1);
295    declare u_cursor cursor for select name, gender from learndatabase.user where age <= uage;
296
297    drop table if exists tb_user_gender;
298    create table if not exists tb_user_gender(
299        id int primary key auto_increment,
300        name varchar(100),
301        gender char(1)
302    );
303    open u_cursor;
304    while true do -- 这里不知道什么时候结束
305        fetch u_cursor into uname, ugender;
306        insert into tb_user_gender values (null, uname, ugender);
307    end while;
308    close u_cursor;
309
310end;
311
312-- 条件处理程序(异常): 定义在流程控制结构执行过程中遇到的问题时相应的处理步骤
313-- declare handler_action handler for condition_value[,condition_value] ... statement;
314-- handler_action: continue:继续执行当前程序, exit:终止执行当前程序
315-- condition_value: sqlstate sqlstate_value: 状态码, 如02000
316--                  sqlwarning: 所有以01开头的sqlstate代码的简写
317--                  not found: 所有以02开头的sqlstate代码的简写
318--                  sqlexception: 所有没有被sqlwarning和not found捕获的sqlstate代码的简写
319create procedure p11(in uage int)
320begin
321    declare uname varchar(100);
322    declare ugender char(1);
323    declare u_cursor cursor for select name, gender from learndatabase.user where age <= uage;
324    declare exit handler for sqlstate '02000' close u_cursor; -- 满足状态码为02000触发退出, 退出时将游标关闭
325    -- declare exit handler for not found close u_cursor;
326
327    drop table if exists tb_user_gender;
328    create table if not exists tb_user_gender(
329        id int primary key auto_increment,
330        name varchar(100),
331        gender char(1)
332    );
333    open u_cursor;
334    while true do -- 现在最后一次循环后, 下面的语句会报错, 被handler抓取退出.
335    fetch u_cursor into uname, ugender;
336    insert into tb_user_gender values (null, uname, ugender);
337        end while;
338    close u_cursor;
339
340end;
341call p11(30);
342
343
344-- 存储函数: 有返回值的存储过程, 参数只能是in类型的.
345# create function 函数名称([参数列表])
346# returns type [characteristic...]
347# begin
348#     --sql
349#     return ... ;
350# end;
351-- characteristic说明:
352-- determinstic:相同的输入总是产生相同的结果
353-- no sql:不包含sql语句
354-- reads sql data:包含读取sql的语句, 不包含写入数据的语句
355-- 案例: 1累加到n
356create function fun1(n int)
357returns int deterministic
358begin
359    declare res int default 0;
360    while n>0 do
361        set res:=res+n;
362        set n:=n-1;
363    end while;
364    return res;
365end;
366
367
368-- 触发器: 在insert/update/delete之前或之后, 触发并执行触发器中定义的sql语句集合
369-- 确保数据完整性, 日志记录, 数据校验等操作.
370-- 使用别名old和new来引用触发器中发生变化的记录内容
371-- insert触发器:new表示将要或已经新增的数据
372-- update触发器:old表示修改之前的数据, new表示将要或已经修改后的数据
373-- delete触发器:old表示将要或已经删除掉的数据
374-- 现在只支持行级触发器(每行触发一次), 不支持语句级触发器(不管影响多少行,只执行一次)
375
376-- 创建触发器
377# create trigger trigger_name
378# before/after insert/update/delete
379# on tbl_name for each row -- 行级触发器
380# begin
381#     trigger_statement;
382# end;
383-- 查看
384show triggers ;
385-- 删除
386# drop trigger [schama_name.]trigger_name; -- 没有指定数据库名默认为当前数据库
387
388-- 需求, 记录tb_user表数据变更日志, 插入到日志表user_logs中, 包含增加,修改,删除
389create table user_logs(
390    id int(11) not null auto_increment,
391    operation varchar(20) not null ,
392    operation_time datetime not null ,
393    operation_id int(11) not null ,
394    operation_params varchar(500) ,
395    primary key (`id`)
396)engine =innodb default charset =utf8;
397
398-- 定义三个触发器
399--insert触发器
400create trigger insert_trigger
401    after insert on learndatabase.user for each row
402begin
403    insert into user_logs(id, operation, operation_time, operation_id, operation_params) values
404        (null, 'insert', now(), new.id, concat('插入的数据内容: ', new.id, new.name, new.gender, new.gender));
405end;
406show triggers ;
407drop trigger insert_trigger;
408insert into learndatabase.user values (null, 'rurushu', 38, 1, '男', 3, 5);
409-- update触发器
410create trigger update_trigger
411    after insert on learndatabase.user for each row
412begin
413    insert into user_logs(id, operation, operation_time, operation_id, operation_params) values
414        (null, 'update', now(), new.id, concat('更新前数据: ', old.id, old.name, old.gender, old.gender,
415                                               '更新后数据: ', new.id, new.name, new.gender, new.gender));
416end;
417-- delete触发器
418create trigger delete_trigger
419    after insert on learndatabase.user for each row
420begin
421    insert into user_logs(id, operation, operation_time, operation_id, operation_params) values
422        (null, 'delete', now(), old.id, concat('删除前数据: ', old.id, old.name, old.gender, old.gender));
423end;

1flush tables with read lock ;
2mysqldump -uroot -proot learndatabase > learndatabase.sql
3unlock tables ;

1mysqldump --single-transaction -uroot -proot learndatabase > learndatabase.sql

1-- 加锁 
2lock tables 表名... read/write
3-- 释放锁 
4unlock tables 或者 客户端断开连接

1-- 查看元数据锁
2select object_type, object_schema, object_name, lock_type, lock_duration from performance_schema.metadata_locks;

1-- 例子:
2select * from learndatabase.user lock in share mode ; -- 这句话加上行锁共享锁和意向共享锁
3lock tables learndatabase.user read; -- 这时可以加表共享读锁, 因为这两个锁兼容; 都可以读嘛
4lock tables learndatabase.user write; -- 这时不能加表独占写锁, 互斥的; 因为如果允许的话, 别个就可以改数据了,读的东西不一致.

1begin ; -- 客户端1开启事务
2update learndatabase.user set gender='女' where id=1; -- 加上行锁和意向排它锁
3lock tables learndatabase.user read; -- 客户端2加锁, 不行
4lock tables learndatabase.user write; -- 客户端2加锁, 不行

1-- 查看意向锁及行锁的加锁情况
2select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;

1-- insert, update, delete 获得排它锁, 系统自动加锁不需要控制
2-- select 不加任何锁
3-- select ... lock in share mode 共享锁
4-- select ... for update 排它锁
5select * from learndatabase.user where id=1; -- id=1这一行的共享锁, 那么其他事务对这一行数据不能获得排它锁
6update stu set name='lei' where id=1; -- 排它锁

1update stu set name ='lei' where name='lily'; -- 事务1,第8行数据. 加的表锁.因为此时name没有索引,没有走索引
2update stu set name='lei' where id=1; -- 事务2, 即便是第一行数据也不能更改, 因为这时已经是表锁了.

1-- 查询行锁
2select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;

 1-- 1. 索引上给等值查询(唯一索引), 给不存在的记录加锁, 优化为间隙锁
 2update stu set age = 10 where id = 5; -- 事务1 表中只有3和8,之间没有数据, 这时加间隙锁, 锁住(3.8]这个空隙
 3insert into stu values (7,'lili', 8); -- 事务2 这时再往里插入, 插入不了被锁住了.
 4
 5-- 2. 索引上的等值查询(普通索引), 向右遍历时最后一个值不满足查询条件时, next-key lock退化为间隙
 6-- 这里age字段就不是唯一索引可能有多个相同的值, 那么它会加11及后面17之间的锁(11后就是17)
 7create index index_idx_stu_age on stu(age);
 8select * from stu where age = 11 lock in share mode ; -- 事务1, 首先对3记录加共享行锁S,其次还要对17加临键锁,锁住本身和其间隙, 防止幻读
 9
10-- 3. 索引上的范围查询(唯一索引), 会访问到不满足条件的第一个值为止.
11select * form stu where id > 30; -- 唯一索引范围查询. 假设查出来的有31 50 ,那么会给31加S锁, 还会给50加临键锁, 还会将50到正无穷也加S锁
12-- 间隙锁的目的是防止其他事务插入间隙, 间隙锁可以共存, 一个事务采用的间隙锁不会阻止另一个事务在同一间隙采用间隙锁.

  1-- Innodb引擎
  2-- 逻辑存储结构
  3-- 表空间tablespace: ibd文件, 一个mysql实例可以对应多个表空间, 用于存储记录, 索引等数据.
  4-- 段segment: 分为数据段, 索引段, 回滚段. Innodb是索引组织表, 数据段就是B+Tree的叶子结点, 索引段即为B+Tree的非叶子节点. 段用来管理多个Extent区
  5-- 区extent: 表空间的单元结构, 每个区的大小为1m, 默认情况下, Innodb存储引擎页大小为16k, 即一个区中一共有64个连续的页.
  6-- 页page: 是Innodb存储引擎磁盘管理的最小单元, 每个页的大小默认是16kb. 为了保证页的连续性, Innodb存储引擎每次从磁盘申请4-5个区
  7-- 行row: Innodb存储引擎数据是按行进行存放的. 行的数据结构:
  8--      Trx_id: 每次对每条记录进行改动时, 都会把对应的事务id赋值给trx_id隐藏列
  9--      Roll_pointer: 每次对每条记录进行改动时, 都会把旧的版本写入到undo日志中, 然后这个隐藏列就相当于一个指针, 可以用它找到该记录修改前的信息
 10--      col1, col2, ...
 11
 12
 13-- 架构
 14-- 内存架构
 15-- 1.buffer pool: 缓冲池是主内存的一个区域, 里面可以缓存磁盘上经常操作的真实数据, 在执行增删改查操作时, 先操作缓冲池中的数据(没有数据就从磁盘加载并缓存)
 16--              然后再以一定频率刷新到磁盘, 从而减少磁盘io, 加快处理速度.
 17-- 缓冲池以页page为单位, 底层采用链表数据结构管理page, page分为三类:
 18--      free page: 空闲page, 没有使用过
 19--      clean page: 被使用page, 数据没有被修改过
 20--      dirty page: 脏页, 被使用page, 数据被修改过, 页中数据与磁盘数据产生了不一致
 21-- 2.change buffer: 更改缓冲区(针对非唯一二级索引页), 在执行DML语句时, 如果这些数据page没有在buffer pool中, 不会直接操作磁盘, 而会将数据变更
 22--                存在更改缓冲区change buffer中, 在未来数据被读取时, 再将数据合并恢复到buffer pool中, 然后再将合并后的数据刷新到磁盘中
 23-- 好处: 与聚集索引不同, 二级索引通常是非唯一的, 并且以相对随机的顺序插入二级索引. 同样删除和更新可能会影响索引树种不相邻的二级索引页, 如果每次都
 24--      操作磁盘, 会造成大量磁盘io, 有了change buffer之后, 我们可以在缓冲池中进行合并处理, 减少磁盘io
 25-- 3.adaptive hash index: 自适应哈希索引, 用于优化对buffer pool数据的查询. Innodb存储引擎会监控对表上各索引页的查询, 如果观察到hash索引可以
 26--                      提升速度, 则建立hash索引, 称之为自适应hash索引.
 27-- 自适应哈希索引, 无需人工干预, 是系统根据情况自动完成
 28-- 参数adaptive_hash_index
 29-- 4.log buffer: 日志缓冲区, 用来保存要写入到磁盘中的log日志数据(redo log, undo log), 默认大小16MB, 日志缓存区的日志会定期刷新到磁盘中. 如果
 30--             需要更新,插入,删除许多行的事务, 增加日志缓冲区的大小可以节省磁盘io
 31-- 参数innodb_log_buffer_size: 缓冲区大小, innodb_flush_log_at_trx_commit: 日志刷新到磁盘的时机(0:每秒将日志写入并刷新到磁盘; 1:日志在每次事务提交时写入并刷新到磁盘; 2:日志在每次事务提交后写入,并每秒刷新到磁盘一次)
 32
 33
 34-- 磁盘架构
 35-- 这些文件系统里面都可以找得到.
 36-- System tablespace: 系统表空间是更改缓冲区的存储区域, 如果表是在系统表空间而不是每一个表文件或通用表空间中创建的, 它也可能包含表和索引数据
 37-- 参数:innodb_data_file_path
 38-- File-per-table tablespace: 每个表的文件表空间包含单个Innodb表的数据和索引, 并存储在文件系统上的单个数据文件中.
 39-- 参数:innodb_file_per_table
 40-- general tablespace: 通用表空间, 需要通过create tablespace语法创建通用表空间, 在创建表时, 可以指定该表空间.
 41create tablespace ts_name add datafile 'file_name.ibd' engine =innodb;
 42create table a(id int primary key auto_increment, name varchar(10))engine =innodb tablespace ts_name;
 43-- undo tablespace: 撤销表空间, mysql实例在初始化时会自动创建两个默认的undo表空间(undo_001,undo_002)初始大小为16M, 用于存储undo log日志.
 44-- temporary tablespace: 临时表空间, Innodb使用会话临时表空间和全局临时表空间,存储用户创建的临时表等数据.
 45-- doublewirte buffer files: 双写缓冲区, Innodb将数据页从buffer pool刷新到磁盘前, 为了保证数据安全, 先将数据写入双写缓冲区文件中, 便于系统异常时恢复数据.
 46-- redo log: 重做日志, 用来实现事务持久性. 该日志由两部分组成:重做日志缓冲(redo log buffer, 内存中), 重做日志文件(redo log, 磁盘中, ib_logfile0, ib_logfile1),
 47-- 当事务提交之后会把所有修改信息都会存到该日志中, 用于在刷新脏页到磁盘时发生错误进行数据恢复. 这文件循环写, 事务提交里面的数据没有意义了
 48
 49
 50-- 后台线程
 51-- 1. master thread: 核心后台线程, 负责调度其他线程, 还负责将缓冲池中的数据异步刷新到磁盘中, 保持数据一致性, 还包括脏页的刷新, 合并插入缓存, undo页的回收
 52-- 2. io thread: Innodb引擎中大量使用了aio来处理io请求, 可以极大提升数据库性能, 而io thread主要负责这些io请求的回调.
 53--      read thread: 默认4个, 负责读操作
 54--      write thread: 默认4个, 负责写操作
 55--      log thread: 默认1个, 负责将日志缓冲区刷新到磁盘
 56--      insert buffer thread: 默认1个, 负责将写缓冲区的内容刷新到磁盘
 57-- 查看Innodb引擎的状态信息, 里面有线程信息
 58show engine innodb status ;
 59-- 3. purge thread: 用于回收事务已经提交的undo log. 事务提交后undo log可能不用了,用它回收.
 60-- 4. page cleaner thread: 协助master thread刷新脏页到磁盘的线程, 减轻master thread的压力, 减少阻塞.
 61
 62
 63-- 事务原理
 64-- 事务的原子性, 一致性, 持久性由redo log和undo log保证
 65-- 事务的隔离性由锁和MVCC(多版本并发控制)保证
 66
 67-- redo log(保证持久性)
 68-- redo log: 重做日志, 记录的是事务提交时数据页的物理修改, 用来实现事务持久性. 该日志由两部分组成:重做日志缓冲(redo log buffer, 内存中), 重做日志文件(redo log, 磁盘中),
 69-- 当事务提交之后会把所有修改信息都会存到该日志中, 用于在刷新脏页到磁盘时发生错误进行数据恢复. 这文件循环写, 事务提交, 里面的数据没有意义了
 70-- 流程: 对缓冲区buffer pool数据进行增删改, 这时页面变成脏页, 同时将增删改的数据记录在redolog buffer记录数据页物理变化, 事务提交时将log buffer刷新到磁盘文件中ib_logfile0/1
 71--      之后脏页刷新到磁盘. 如果脏页刷新到磁盘失败了, 可以通过redo log恢复
 72
 73-- undo log(保证原子性)
 74-- 回滚日志, 用于记录数据被修改前的信息, 作用包含两个:提供回滚和MVCC
 75-- undo log和redo log记录物理日志不一样, 它是逻辑日志. 可以认为当delete一条记录时, undo log中会记录一条对应的insert记录, 反制一番, 当update一条记录时,
 76-- 它记录一条对应相反的update记录. 当执行rollback时, 就可以从undo log中逻辑记录读取到相应的内容并进行回滚
 77-- undo log销毁: undo log在事务执行时产生, 事务提交时, 并不会立即删除undo log, 因为这些日志可能还用于mvcc
 78-- undo log存储: undo log采用段的方式进行管理和记录, 存放在rollback segment回滚段中, 内部包含1024个undo log segment
 79
 80
 81
 82-- MVCC
 83-- 基本概念
 84-- 当前读: 读取的是记录的最新版本, 读取时还要保证其他并发事务不能修改当前记录, 会对读取的记录进行加锁.
 85-- 如select ... lock in share mode(共享锁), select ... for update , update, insert, delete(排它锁)都是一种当前读
 86begin;
 87select * from learndatabase.user; -- 在别的事务commit之前和之后去读到的都是同样的数据, 可重复读
 88select * from learndatabase.user lock in share mode ; -- 别的事务commit之后可以读取到最新的数据.
 89-- 快照读: 简单的select(不加锁)就是快照读, 快照读, 读取的是记录数据的可见版本, 有可能是历史数据, 不加锁, 是非阻塞读
 90begin;
 91select * from learndatabase.user; -- 在别的事务commit之前去读,生成一个快照
 92select * from learndatabase.user; -- 在别的事务commit之后读, 依旧读的是之前的快照
 93-- read committed: 每次select都生成一个快照读
 94-- repeatable read: 开启事务后第一个select语句才是快照读的地方.
 95-- serializable: 快照读退化成当前读, 每次读取都会加锁.
 96-- MVCC: 维护一个数据的多个版本, 使得读写操作没有冲突, 快照读为mysql实现MVCC提供了一个非阻塞读功能, 还需要依赖与数据库记录中的三个隐式字段, undo log日志, readview.
 97
 98
 99-- 实现原理
100-- 记录中的隐藏字段
101-- 1.DB_TRX_ID: 最近修改事务ID. 记录插入这条记录或最后一次修改该记录的事务ID(事务ID是从1自增的)
102-- 2.DB_ROLL_PTR: 回滚指针. 指向这条记录的上一个版本, 用于配合undo log, 指向上个版本.
103-- 3.DB_ROW_ID: 隐藏主键. 当表结构没有指定主键时会生成该隐藏字段.
104-- 查看隐藏字段
105ibd2sdi user.ibd
106
107-- uodo log: 回滚日志, 在insert, update, delete的时候产生的便于数据回滚的日志
108-- 当insert时, 产生的undo log只在回滚时需要, 在事务提交后便被立即删除
109-- 当update, delete时, undo log不止在回滚时需要, 在快照读时也需要, 不会被立刻删除
110
111-- undo log版本链
112-- 不同事务或相同事务对同一条记录进行修改, 会导致该记录的undo log生成一条记录版本链表, 头部是最新的旧记录, 尾部是最早的旧记录.
113-- undo log记得是旧记录的所有东西, 里面就包括了DB_ROLL_PTR指针, 所以旧记录的该字段指向的是下一条旧记录
114
115-- readview: 读视图. 是快照读sql执行的MVCC提取数据的依据, 记录并维护系统当前活跃的事务(未提交的)id
116-- 包含四个核心字段:
117-- 1.m_ids:当前活跃的事物ID集合(没有commit的事务id)
118-- 2.min_trx_id: 最小活跃事物ID(m_ids中的最小值)
119-- 3.max_trx_id: 预分配事务ID, 当前最大事务ID+1(事务是自增的, 下一个就是+1)
120-- 4.creator_trx_id: readview创建者的事务ID.
121-- 版本链数据访问规则: (trx_id代表当前这条记录对应的事务的id, 就是db_trx_id)
122-- 1. trx_id == creatr_trx_id时 可以访问该版本. (说明数据是当前事务更改的)
123-- 2. trx_id < min_trx_id时 可以访问该版本 (说明事务已经提交了)
124-- 3. trx_id > max_trx_id时 不可以访问该版本. (说明该事务是在readview生成后才开启, 这里感觉可以取等)
125-- 4. min_trx_id <= trx_id <= max_trx_id且trx_id不在m_ids中 可以访问该版本 (说明数据已经提交)
126-- 不同的隔离级别, 生成readview的时机不同:
127-- read committed: 在事务中每次执行快照读时生成readview
128-- repeatable read: 仅在事务第一次执行快照读时生成readview, 后续复用该readview
129
130-- 原理分析(rc级别)
131-- read committed: 在事务中每次执行快照读时生成readview
132-- 简单来说就沿着undo log日志, 从新往旧每一条记录的db_trx_id去和规则作对比, 如果都不能访问则沿着版本链往下继续找直到满足条件为止.
133-- 原理分析(rr级别)
134-- 与rr唯一不同就是两次查询的readview不同罢了. 所以rc两次查询可能不同, rr都是一样的. 可重复读嘛