MySQL数据库二 Li.059

视图

视图介绍
- 视图：是一种虛拟存在的数据表，这个虚拟表并不在数据库中实际存在。
- 作用：将一些较为复系的查询语句的结果，封装到-个虚拟表中，后期再有相同需求时，直接道询该虚拟表即可

# 准备数据
create database mydb5;
use mydb5;
create table country(
    id int primary key auto_increment,
    NAME varchar(30)
);
insert into country values (null,'中国'),(null,'美国'),(null,'俄罗斯');
create table city(
    id int primary key auto_increment,
    NAME varchar(30),
    cid int,
    constraint cc_fk1 foreign key (cid) references country(id)
);
insert into city values (null,'北京',1),(null,'上海',1),(null,'纽约',2),(null,"莫斯科",3);

视图的创建和查询

创建视图语法: create view 视图名称 [(列名列表)] as 查询语句;
查询视图语法: select * from 视图名称;

## 创建视图
create view city_country (city_id,city_name,country_name) as
select c1.id,c1.NAME,c2.NAME
from city c1,
     country c2
where c1.cid = c2.id;
select * from city_country;

修改视图数据语法: update 视图名称 set 列名=值 where 条件; 修改视图的数据,原表的数据也会修改

## 修改视图数据
update city_country set city_name ='深圳' where city_name='北京';

修改视图结构语法: alter view 视图名称 (列名列表) as 查询语句;
删除视图语法: drop view [if exists] 视图名称;

## 修改视图结构
alter view city_country (city_id,city_name,NAME) as
   select c1.id,c1.NAME,c2.NAME
from city c1,
     country c2
where c1.cid = c2.id;
## 删除视图
drop view if exists city_country;

数据库备份和恢复

备份-命令行方式
- 登录到MySQL服务器, mysqldump -uroot -p 数据库名称>文件保存路径(sql结尾文件)
恢复
- 登录MySQL数据库
- 删除已备份的数据库
- 重新创建名称相同的数据库
- 使用该数据库
- 导入文件执行: source 备份文件全路径

# 备份-命令行操作
mysqldump -uroot -p backupdatabase > /root/backupdatabase.sql

# 恢复-命令行操作
mysqldump -uroot -p

show databases;
drop backupdatabase;
create database backupdatabase;
use backupdatabase;
source /root/backupdatabase.sql;

备份-使用Datagrip
- Export with ‘mysqldump’
恢复
- 使用Datagrip
- 删除已备份的数据库
- 重新创建名称相同的数据库
- 在数据库右键 Run SQL Script, 选择导出的文件.sql

存储过程和函数介绍

存储过程和函数是事先经过编译并存储在数据库的一段SQL说一句的集合
存储过程和函数的好处
- 提高代码的复用性
- 减少数据在数据库和应用服务器之间的传输, 提高效率
- 减少代码层面的业务处理
存储过程和函数的区别
- 存储函数必须有返回值
- 存储过程可以没有返回值

存储过程的创建和调用

创建存储过程

# 修改结束分隔符
delimiter $

# 创建存储过程
create procedure 存储过程名称(参数列表)
BEGIN
    SQL语句列表;
END$

# 修改结束分隔符
DELIMITER ;

调用存储过程
- CALL 存储过程名称(实际参数);

## 存储过程数据准备
create database mydb6;
use mydb6;
create table student(
    id int primary key auto_increment,
    NAME varchar(20),
    age int,
    gender varchar(5),
    score  int
);
insert into student values
(null,'张三',23,'男',95),
(null,'李四',24,'男',98),
(null,'王五',25,'女',100),
(null,'赵六',26,'女',90);

# 按照性别进行分级, 查询每组学生的总成绩. 按钮总成绩的升序排序
select gender,SUM(score) as getSum from student group by gender order by getSum asc;

delimiter $
create procedure stu_group()
BEGIN
    select gender,SUM(score) as getSum from student group by gender order by getSum asc;
END $
delimiter ;

call stu_group();

存储过程的查看和删除

查看数据库中所有的存储过程
- select * from mysql.proc where db=‘数据库名称’;
删除存储过程
- drop procedure [if exists] 存储过程名称;

# 查看存储过程
select * from mysql.proc where db='mydb6';
# 删除存储过程
drop procedure if exists stu_group;

存储过程语法 - 变量

定义变量: declare 变量名数据类型 [default 默认值];
变量赋值方式一: set 变量名=变量值;
变量赋值二: select 列名 into 变量名 from 表名 [where 条件];

# 按照性别进行分级, 查询每组学生的总成绩. 按钮总成绩的升序排序
select gender,SUM(score) as getSum from student group by gender order by getSum asc;

delimiter $
create procedure stu_group()
BEGIN
    select gender,SUM(score) as getSum from student group by gender order by getSum asc;
END $
delimiter ;

call stu_group();

# 查看存储过程
select * from mysql.proc where db='mydb6';
# 删除存储过程
drop procedure if exists stu_group;

delimiter $
create procedure pro_test1()
begin
    declare  num int default 10;
    select num;
end $
delimiter ;
call pro_test1();

delimiter $
create procedure pro_test2()
begin
    declare NAME varchar(10);
    set NAME='存储过程';
    select NAME;
end $
delimiter ;

call pro_test2();

delimiter $
create procedure pro_test3()
begin
    declare woman_sum_score,man_sum_score int;
    select sum(score) into woman_sum_score from student where gender='女';
    select sum(score) into man_sum_score from student where gender='男';
    select woman_sum_score,man_sum_score;
end $
delimiter ;

call pro_test3();

存储过程语法 - if语句

if语句

if 判断条件1 then 执行的SQL语句1;
    [elseif 判断条件2 then 执行的SQL语句2]
    ...
    [else 执行的SQL语句n]
end if;

# 定义变量int 存储班级总成绩
# 定义vachar变量量, 用于存储分数描述
# 根据总成绩判断: 380分以上 学习优秀, 320~380 学习不错, 320以下 学习一般
delimiter $
create procedure pro_test4()
begin
    declare all_sum_score int;
    declare info varchar(30);
    select sum(score) into all_sum_score from student;
    if all_sum_score>380 then set info='学习优秀';
    elseif (all_sum_score<=380 && all_sum_score >=320) then set info='学习不错';
    else set info='学习一般';
    end if;
    select all_sum_score,info;
end $
delimiter ;

call pro_test4();

存储过程语法 - 参数传递

存储过程的参数和返回值

delimiter $
create procedure 存储过程名称 ([IN|OUT|INOUT] 参数名 数据类型)
begin
    SQL语句;
end$
delimiter ;

参数
- IN: 代表输入参数,需要由调用者传递实际数据(默认)
- OUT: 代表输入参数, 该参数可以作为返回值
- INOUT: 代表既可以作为输入参数, 也可以作为输入参数

delimiter $
create procedure pro_test5(IN all_sum_score int,OUT info varchar(30))
begin
    if all_sum_score>380 then set info='学习优秀';
    elseif (all_sum_score<=380 && all_sum_score >=320) then set info='学习不错';
    else set info='学习一般';
    end if;
end $
delimiter ;

call pro_test5(320,@info);
call pro_test5((select sum(score) from student),@info);
select @info;

存储过程语法 - while循环

while循环语法

初始化语句;
while 条件判断语句 do
    循环体语句;
    条件控制语句;
end while;

delimiter $
create procedure pro_test6()
begin
    declare result int default 0;
    declare i int default 1 ;
    while i < 101 do
        if i%2=0 then
       set result = result + i;
       end if;
        set i = i + 1;
    end while;
    select result;
end $
delimiter ;
call pro_test6;

存储函数

存储函数和存储过程是非常相似的, 区别在于存储函数必须有返回值
创建存储函数

delimiter $
create function 函数名称(参数列表)
returns 返回值类型
begin
    SQL语句列表;
    RETURN结果;
end$
delimiter ;

调用存储函数
- select 函数名称(实际参数);
删除存储函数
- drop function 函数名称;

delimiter $
create function fun_test1()
RETURNS int
begin
    declare num int;
    select count(*) into num from student where score>95;
    return num;
end $
delimiter ;

select fun_test1();

drop function fun_test1;

MySQL 触发器

触发器是与表有关的数据库对象, 可以在insert,update,delete之前或之后触发并执行触发器中定义的SQL语句.
这种特性可以协助应用系统在数据库端确保数据的完整性,日志记录,数据校验等操作.
使用别名NEW和OLD来引用触发器中发生变化的内容记录
触发器分类

触发器类型     OLD                           NEW
INSERT型触发器 无(插入前无数据)              NEW表示将要或者已经新增的数据
UPDATE型触发器 OLD表示修改之前的数据         NEW表示将要或已经修改后的数据
DELETE型触发器 OLD表示将要或者已经删除的数据 无(删除删除后状态无数据)

触发器的操作

创建触发器

delimiter $
create trigger 触发器名称
before|after insert|update|delete
on 表名
fro each row
begin
    触发器要执行的功能;
end $
delimiter ;

# 数据准备
create database mydb7;
use mydb7;

create table account(
    id int primary key auto_increment,
    NAME varchar(20),
    money double
);
insert into account values (null,'张三',1000),(null,'李四',1000);
create table account_log(
    id int primary key auto_increment,
    operation varchar(20),
    operation_time datetime,
    operation_id int,
    operation_params varchar(200)
);

# 插入触发器
delimiter $
create trigger account_insert
    after INSERT
    on account
    for each row
    begin
        insert into account_log values (null,'INSERT',now(),new.id,
                                        CONCAT('插入后{id=',new.id,',name=',new.NAME,',money=',new.money,'}'));
    end $
delimiter ;
insert into account values (null,'王五',2000);
select * from account;
select * from account_log;

# 更新触发器
delimiter $
create trigger account_update
    after UPDATE
    on account
    for each row
    begin
        insert into account_log values (null,'UPDATE',now(),new.id,
                                        CONCAT('更新前{id=',old.id,',name=',old.NAME,',money=',old.money,'}',
                                            '更新后{id=',new.id,',name=',new.NAME,',money=',new.money,'}'));
    end $
delimiter ;
select * from account;
update account set money=2000 where id=2;
select * from account_log;

# 删除触发器
delimiter $
create trigger account_delete
    after DELETE
    on account
    for each row
    begin
        insert into account_log values (null,'DELETE',now(),old.id,
                                        CONCAT('删除前{id=',old.id,',name=',old.NAME,',money=',old.money,'}'));
    end $
delimiter ;
select * from account;
delete from account where id=3;
select * from account_log;

触发器的操作

查看触发器: show triggers;
删除触发器: drop trigger 触发器名称;

# 查看触发器
show triggers ;
# 删除触发器
drop trigger account_delete;

MySQL 事务

事务: 一条或多条SQL语句组成一个执行单元, 其特点是这个单元要么同时成功要么同时失败
单元中的每条SQL语句都相互依赖, 形成一个整体
如果某条SQL语句执行失败或者出现错误, 那么整个单元就会撤回到事务最初的状态.
如果单元中所有的SQL语句都执行成功, 则事务就顺利执行.

事务的操作

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

# 准备数据
create database db8;
use db8;
create table account(
    id int primary key auto_increment,
    NAME varchar(20),
    money double
);
insert into account values (null,'张三',1000),(null,'李四',1000);
select * from account;


# 开启事务
start transaction ;
update account set money=money-500 where NAME='张三';
update account set money=money+500 where NAME='李四';
# 回滚事务
rollback ;
# 提交事务
commit ;

事务的提交方式

事务提交方式的分类
- 自动提交(MySQL默认)
- 手动提交
查看事务提交方式
- select @@autocommit;
修改事务提交方式
- set @@autocommit=数字;

# 查询事务的提交方式
select @@autocommit;
# 修改事务的提交方式
# 默认是1, 不可修改
set @@autocommit=0;

事务的四大特征

原子性(Atomicity)
- 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。
- 因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。
一致性(Consistency)
- 一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态。
- 也就是说一个事务执行之前和执行之后都必须处于一致性状态。
隔离性(isolcation)
- 隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务。
- 不能被其他事务的操作所千扰，多个并发事务之问要相互隔离。卜
持久性(durability)
- 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的。
- 即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

事务的隔离级别

事务的隔离级别
- 多个客户端操作时，各个客户端的事务之间应该是隔离的，相互独立的，不受影响的。
- 而如果多个事务操作同一批数据时，就会产生不同的问题，我们需要设置不同的隔离级别来解决这些问题。

隔离级别分类	隔离级别	会引发的问题
read uncommitted	读末提交	脏读、不可重复读、幻读
read committed	读已提交	不可重复读、幻读
repeatable read	可重复读	幻读
serializable	串行化	无

引发的问题

问题	现象
脏读	在一个事务处理过程中读取到了另一个未提交事务中的数据,导致2次查询结果不一致
不可重复读	在一个事务处理过程中读取到了另一个事务中修改并已提交的数据,导致2次查询结果不一对我
幻读	查询某数据不存在,准备插入此记录,但执行插入时发现此记录已存在,无法插入.或查询数据不存在执行删除操作,却发现删除成功

事务的隔离级别

查询数据库隔离级别
- select @@tx_isolation;
修改数据库隔离级别(需要重新连接)
- set global transaction isolation level 级别字符串;

# 查询事务隔离级别
select @@tx_isolation;
# 修改事务隔离级别(修改后需要重新连接)
set global transaction isolation level read committed ;

隔离级别问题演示
- 脏读问题演示

set global transaction isolation level read uncommitted ;
start transaction ;
update account set money=money-500 where NAME='张三';
update account set money=money+500 where NAME='李四';
# 提交事务
select * from account;
rollback ;

# 同时开启, 在上一用户transaction执行期间查询, 把transcation未commit的数据读取出来了.
start transaction ;
select * from account;
COMMIT;

解决脏读设置为: set global transaction isolation level read committed ;

不可重复读问题演示和解决
- 不可重复读: 一个事务中读取到其他事务已提交的数据

set global transaction isolation level read committed;
start transaction ;
update account set money=money-500 where NAME='张三';
update account set money=money+500 where NAME='李四';
# 提交事务
select * from account;
COMMIT;

# 同时开启, 在上一用户transaction执行期间查询, 把transcation未commit的数据读取出来了.
# 第2次读, 前面commit;完成后读, 数据变化了.
start transaction ;
select * from account;
COMMIT;

解决不可重复读解决: set global transaction isolation level repeatable read ; 在事务2中读多次,还是事务1提交前的数据.

幻读的问题演示
- 查询某记录是否存在, 不存在
- 准备插入此记录, 但执行插入时发现此记录
- 或某记录不存在执行删除, 却发现删除成功

start transaction ;
insert into account values (3,'王五',2000);
select * from account;
commit ;

# 同时事务, 插入操作, 事务2执行失败
start transaction ;

select * from account;
insert into account values (3,'王五',2000);
COMMIT;

解决幻读: set global transaction isolation level serializable;

事务的隔离级别

序号	隔离级别	名称	脏读	不可重复读	幻读	数据库默认隔离级别
1	read uncommitted	读未提交	是	是	是
2	read committed	读已提交	否	是	是	Oracle
3	repeatable read	可重复读	否	否	是	MySQL
4	serializable	串行化	否	否	否

隔离级别从小到大安全性越来越高, 但是效率越来越低, 不建议修改数据库默认的隔离级别

MySQL 体系结构-存储引擎

客户端连接
- 支持接口：支持的客户端连接，例如 C、Java、PHP等语言来连接MySQL数据库。
第一层：网络连接层
- 连接池：管理、缓冲用户的连接，线程处理等需要缓存的需求。
第二层：核心服务层
- 管理服务和工具：系统的管理和控制工具，例如备份恢复、复制、集群等。
- SQL 接口：接受 SQL 命令，并且返回查询结果。
- 查询解析器：验证和解析 SQL 命令，例如过滤条件、语法结构等。
- 查询优化器：在执行查询之前，使用默认的一套优化机制进行优化sql语句。
- 缓存：如果缓存当中有想查询的数据，则直接将缓存中的数据返回。没有的话再重新查询。
第三层：存储擎层
- 插件式存储引(擎：管理和操作数据的一种机制，包括(存储数据、如何更新、查询数据等
第四层：系统文件层
- 文件系统：配置文件、数据文件、日志文件、错误文件、二进制文件等等的保存。

存储引擎介绍

MysQL 数据库使用不同的机制存取表文件，包括存储方式、索引技巧、锁定水平等不同的功能。这些不同的技术以及配套的功能称为存储引擎。
Oracle、SalServer 等数据库只有一种存储引擎。而MySQL针对不同的需求,配置不同的存储引1擎,就会让数据库采取不同处理数据的方式和扩展功能。
MysQL 支持的存储引l擎有很多，常用的有三种：InnoDB、 MyISAM、MEMORY。
特性对比
- MyISAM 存储引擎：访问快，不支持事务和外键操作。
- InnoDB 存储引擎：支持事务和外键操作，支持并发控制，占用磁盘空间大。(MySQL5.5版本后默认
- MEMORY 存储引!擎：内存存储，速度快，不安全。适合小量快速访问的数据。

特性	MyISAM	InnoDB	MEMORY
存储限制	有(平台对文件系统大小的限制)	64TB	有(平台的内存限制)
事务安全	不支持	支持	不支持
锁机制	表锁	表锁/行锁	表锁
B+Tree索引	支持	支持	支持
哈希索引	不支持	不支持	支持
全文索引	支持	支持	不支持
集群索引	不支持	支持	不支持
数据索引	不支持	支持	支持
数据缓存	不支持	支持	N/A
索引缓存	支持	支持	N/A
数据可压缩	支持	不支持	不支持
空间使用	低	高	N/A
内存使用	低	高	中等
批量插入速度	高	低	高
外键	不支持	支持	不支持

存储引擎的操作

查询数据库支持的存储引擎: show engines;
查询某个数据库中的所有数据表的存储引擎: show table status from 数据库名称;
查询某个数据库中某个数据表的存储引擎: show table status from 数据库名称 where name=‘数据表名称’;
创建数据表,指定存储引擎

create table 表名(
    列名, 数据类型,
    ...
)ENGINE = 引擎名称;

修改数据表的存储引擎: alter table 表名 ENGINE=引擎名称;

# 查询数据库支持的存储引擎:
show engines;
# 查询某个数据库中的所有数据表的存储引擎:
show table status from mydb4;
# 查询某个数据库中某个数据表的存储引擎:
show table status from mydb4 where name='USER';
# 创建数据表,指定存储引擎
use db8;
create table test_engine(
    id int
)engine = MyISAM;
show table status from db8;
# 修改数据表的存储引擎
alter table test_engine engine = INNODB;

存储引擎的选择

MyISAM
- 特点: 不支持事务和外键. 读取速度快, 节约资源
- 使用场景: 以查询操作为主, 只有很少的更新和删除操作, 并且对事务的完整性,并发性要求不是很高
InnoDB
- 特点: MySQL的默认存储引擎
- 使用场景: 对事务的完整性有比较高的要求, 在并发条件下要求数据的一致性, 读写频繁的操作
MEMORY
- 特点: 将所有数据保存在内存中, 在需要快速定位记录和其他类似数据环境下, 可以提供更快的访问.
- 使用场景: 通常用于更新不太频繁的小表, 用来快速得到访问的结果

如果不确定,则使用数据库默认的存储引擎

MySQL 的索引

索引介绍

MySQL索引: 是帮助MySQL高效获取数据的一种数据结构. 所以, 索引的本质就是数据结构
在表数据之外, 数据库系统还维护着满足特写查找算法的数据结构, 这些数据结构以某种方式指向数据, 这样就可以在这些数据结构上实现高级查找算法, 这种数据结构就是索引

索引的操作

按照功能分类
- 普通索引：最基本的索引，没有任何限制。
- 唯一索引：索引列的值必须唯—，但允许有空值。如果是组合素引，则列值组合必须唯一
- 主键索引：一种特殊的唯一索引，不允许有空值。在建表时有主键列同时创建主键奈引。
- 联合素引：顾名思义，就是将单列索引进行组合。
- 外键索引：只有InnoDB 引李支持外键索引，用来保证数据的一致性、完整性和实现级联操作。
- 全文素引：快速匹配全部文档的方式。InnoDB引擎 5.6版本后才支持全文素引。MEMORY 引擎不支持。
按照结构分类
- BTree 索引：MySQL使用最频繁的一个素引（数据结构，是lInnoDB 和 MyISAM 存储引草默认的索号类型，底层基于 B+Tree 数据结构。
- Hash 奈引：MySQL 中 Memory 存储引擎默认支持的奈引类型。
创建索引

# 无索引名称,则是默认BTree
create [unique|fulltext] index 索引名称 [using 索引类型]
on 表名(列名...);

查看索引: show index from 表名;

# 数据准备
create database db9;
use db9;
create table student(
    id int primary key auto_increment,
    NAME varchar(20),
    age int,
    score int
);
insert into student values
(null,'张三',23,98),
(null,'李四',24,95),
(null,'王五',25,96),
(null,'赵六',26,94),
(null,'周七',27,99);
select * from student;

# 为student表的name列创建一个普通索引
create index idx_name on student(NAME);
# 为student表的age列创建一个唯一索引
create unique index idx_age on student(age);
# 查询student索引 (主键列自带主键索引)
show index from student;
# 查询mydb4数据库的product表(外键列自带外键索引)
show index from mydb4.product;

添加索引
添加索引
- 普通索引：ALTER TABLE 表名 ADD INDEX索引名称（列名);
- 组合索引：ALTER TABLE 表名 ADD INDEX索引名称(列名1,列名2….);
- 主键索引：ALTER TABLE 表名 ADD PRIMARY KEY(主键列名);
- 外键索引：ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY （本表外键列名)REFERENCES 主表名(主键列名);
- 唯一索引：ALTER TABLE 表名 ADD UNIQUE 索引名称(列名);
- 全文索引：ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名);
删除索引
- drop index 索引名 on 表名;

# 添加index
alter table student add unique idx_score(score);
# 删除index
alter table student drop index idx_name1;
drop index idx_name1 on student;

索引的原理

BTree数据结构
- 每个节点中不仅包含key值,还有数据. 会增加查询数据时磁盘的IO次数
B+Tree数据红塔区
- 非叶子节点只存储key值
- 所有数据存储在叶子节点
- 所有叶子节点之间都有连接指针
B+Tree好处
- 提高查询速度
- 减少磁盘的IO次数
- 树形结构较小

索引的设计原则

创建索引遵循的原则
- 对查询频次较高, 且数据量比较大的表建立索引
- 使用唯一索引,区分度越高,使用索引的效率越高
- 索引字段的选择,最佳做眉毛列应当从where子句的条件中提取
- 索引虽然可以有效的提升查询数据的效率, 也并不是多多益善
最左匹配原则(适合组合索引)
例如: 为user中的name,address,phone列添加组合索引
- alter table user add index idx_three(name,address,phone);
此时,组合索引idx_three实际建立了(name),(name,address),(name,address,phone)三个索引
下面的三个SQL语句都可以命中索引

select * from user where address='北京' and phone='12345' and name='张三';
select * from user where name='张三' and address='北京';
select * from user where name='张三';

这三条SQL语句在检索时分别会使用以下索引进行数据匹配
- (name,address,phone)
- (name,address)
- (name)
索引字段出现的顺序可以是任意的, MySQL优化器会帮我们自动的调整where条件中的顺序
如果组合索引中最左边的列不在查询条件中, 则不会命中索引
- select * from user where address=‘北京’;

MySQL 锁机制

锁机制: 数据库为了保证数据的一致性, 在共享的资源被管他访问时变得案例所设计的一种规则
锁机制类似多线程的同步, 作用可以保证数据的一致性和案例性
按操作分类
- 共享锁: 也叫读锁. 针对同一份数据, 多个事务读取操作可以同时加锁而互相不影响, 但是不能修改数据
- 揍他锁: 也叫写锁. 当前的操作没有完成前, 会阻断其他操作的读取和写入.
按粒度分类
- 表级锁: 会锁定整个表. 开销小, 加锁快. 锁定力度大, 发生锁冲突概率高, 并发低. 不会出现死锁情况.
- 行级锁: 会锁定当前行. 开销大, 加锁慢. 锁定粒度小, 发生锁冲突概率低, 并发度高. 会出现死锁情况.
按使用方式分类
- 悲观锁: 第次查询数据时都认为别人会修改, 很悲观, 所有查询时加锁
- 乐观锁: 第次查询数据时都认为别人不会修改, 很乐观, 但是更新时会判断一下在此期间别人有没有去更新这个数据.
不同存储引擎支持的锁

存储引擎	表锁	行锁
InnoDB
MyISAM
MEMORY

InnoDB 共享锁

共享锁特点
- 数据可以被多个事务查询, 但是不能修改
创建共享锁格式
- select 语句 lock in share mode;

InnoDB带索引的列是行锁

start transaction ;
select * from student where id = 1 lock in share mode ;
commit ;

# 同时开启事务, 更新这行数据
start transaction ;
update student set NAME='张三三' where id =1;
update student set NAME='李四四' where id =2;
commit;

InnoDB非索引列,加的就表锁

start transaction ;
select * from student where score=98 lock in share mode ;
commit ;

# 同时开启事务, 更新这行数据
start transaction ;
update student set NAME='王五五' where id =3;
commit;

InnoDB 排他锁

揍他锁特点
- 加锁的数据, 不能被其他事务加锁查询或修改
- 普通查询则可以
创建排他锁格式
- select语句 for update;

start transaction ;
select * from student where id=1 for update;
commit ;

# 同时开启事务
start transaction;
# 普通查询没有问题
select * from student where id=1 ;
# 查询id为1的数据, 并加入共享锁, 不能
select * from student where id=1 lock in share mode ;
# 查询id为1的数据, 并加入排他锁, 不能
select * from student where id=1 for update;
# 修改数据也是, 不能
update student set NAME='张三' where id=1;
commit;

MyISAM 读锁

读锁特点
- 所有的连接只能查询数据,不能修改
读锁语法格式
- 加锁: lock table 表名 read;
- 解锁: unlocak tables;

# 准备数据
create table product(
    id int primary key auto_increment,
    NAME varchar(20),
    price int
) engine = MYISAM;
insert into product values
(null,'华为手机',4999),
(null,'苹果',8999),
(null,'中兴',1999);
select * from product;
show table status from db10 where name='product';

# 窗口1, 增加读锁
lock tables product read;
select * from product;
unlock tables ;


# 窗口2, 可查询
select * from product;
# 不可修改, 只有窗口1解锁后才能修改
update product set price=5999 where id=1;

MyISAM 写锁

写锁特点
- 其他连接不能查询和修改数据
写锁语法格式
- 加锁: lock table 表名 write;
- 解锁: unlocak tables;

# 窗口1, 增加写锁
lock tables product write ;
# 可在窗口1查询
select * from product;
# 可在窗口1更新
update product set price=5999 where id=1;
unlock tables ;

# 窗口2, 不可查询, 只有窗口1解锁才能查询
select * from product;
# 不可修改, 只有窗口1解锁后才能修改
update product set price=4999 where id=1;

悲观锁和乐观锁

悲观锁
- 就是很悲观, 它对于数据被外界修改的操作持保守感谢感谢, 认为数据随时会修改
- 整个数据处理中需要将数据加锁. 悲观锁一般都是依靠关系型数据库提供的锁机制
- 之前所学的锁机制都是悲观锁
乐观锁
- 就是很乐观, 第次自己操作数据的时候认为没有人会来修改它, 所有不去加锁
- 但是在更新的时候会去判断在此期间数据有没有被修改
- 需要用户自己去实现, 不会发生并发抢占资源, 只有在提交操作的时候检查是否违反数据完整性.

乐观锁的解决方式

方式一
- 给数据表中添加一个version 列，每次更新后都将这个列的值加1.
- 读取数据时，将版本号读取出来，在执行更新的时候，比较版本号。
- 如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。
- 用户自行根据这个通知来决定怎么处理，比如重新开始一遍，或者放弃本次更新。
方式二
- 和版本号方式基本一样，给数据表中添加一个列，名称无所谓，数据类型需要是timestamp。
- 每次更新后都将最新时间插入到此列。
- 读取数据时，将时问读取出来，在执行更新的时候，比较时间。
- 如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。

# 准备数据
create table city(
    id int primary key auto_increment,
    NAME varchar(20),
    VERSION int
);
insert into city values
(null,'北京',1),
(null,'上海',1),
(null,'广州',1),
(null,'深圳',1) ;
select * from city;

select VERSION from city where NAME='北京';
update city set NAME='北京市',VERSION=VERSION+1 where NAME='北京' and VERSION=1;

视图#

视图的创建和查询#

数据库备份和恢复#

存储过程和函数介绍#

存储过程的创建和调用#

存储过程的查看和删除#

存储过程语法 - 变量#

存储过程语法 - if语句#

存储过程语法 - 参数传递#

存储过程语法 - while循环#

存储函数#

MySQL 触发器#

触发器的操作#

触发器的操作#

MySQL 事务#

事务的操作#

事务的提交方式#

事务的四大特征#

事务的隔离级别#

事务的隔离级别#

事务的隔离级别#

MySQL 体系结构-存储引擎#

存储引擎介绍#

存储引擎的操作#

存储引擎的选择#

MySQL 的索引#

索引介绍#

索引的操作#

索引的原理#

索引的设计原则#

MySQL 锁机制#

InnoDB 共享锁#

InnoDB 排他锁#

MyISAM 读锁#

MyISAM 写锁#

悲观锁和乐观锁#

乐观锁的解决方式#

视图

视图的创建和查询

数据库备份和恢复

存储过程和函数介绍

存储过程的创建和调用

存储过程的查看和删除

存储过程语法 - 变量

存储过程语法 - if语句

存储过程语法 - 参数传递

存储过程语法 - while循环

存储函数

MySQL 触发器

触发器的操作

触发器的操作

MySQL 事务

事务的操作

事务的提交方式

事务的四大特征

事务的隔离级别

事务的隔离级别

事务的隔离级别

MySQL 体系结构-存储引擎

存储引擎介绍

存储引擎的操作

存储引擎的选择

MySQL 的索引

索引介绍

索引的操作

索引的原理

索引的设计原则

MySQL 锁机制

InnoDB 共享锁

InnoDB 排他锁

MyISAM 读锁

MyISAM 写锁

悲观锁和乐观锁

乐观锁的解决方式