事务，隔离，存储，视图，用户管理相关笔记

学习视频:_韩顺平MySQL_事务有什么用_哔哩哔哩_bilibili

目录

什么是事务

事务细节 

四种隔离

隔离级别演示

​编辑 将右边换成读已提交的隔离级别  Read commited

隔离级别修改为可重复读

隔离级别设置为可串行化

设置隔离

 mysql事务ACID

mysql表类型和存储引擎

如何选择表的存储引擎

 修改存储引擎

视图 

视图的基本使用

Mysql管理 

Mysql权限管理

给用户授权

---

什么是事务

• 事务用于保证数据的一致性，它由一组相关的dml语句组成,该组的dml语句要么全部成功，要么全部失败。如：转账就要用事务来处理,用以保证数据的一致性。
• 事务和锁

当执行事务操作时(dml语句),mysql会在表上加锁，防止其他用户改表的数据，这对用户来讲是非常重要的。

• mysql数据库控制台事务的几个重要操作(基本操作  transaction.sql)

1.   start     transaction -- 开始一个事务
2. savepoint  保存点-- 设置保存点
3. rollback  to  保存点名-- 回退事务
4. rollback -- 回退全部事务
5. commit -- 提交事务，所有的操作生效，不能回退

 

回退到b点后可以回退到a点,但是直接回退到a点后不可以回退到b点 

-- 1.创建测试表
CREATE TABLE t24(
	id INT,
	`name` VARCHAR(32));
-- 2.开始事务
START TRANSACTION
-- 3.设置保存点
SAVEPOINT a
-- 执行dml操作
INSERT INTO t24 VALUES(100,'tom');
SELECT * FROM t24;

-- 设置保存点 b
SAVEPOINT b
-- 执行dml操作
INSERT INTO t24 VALUES(200,'jack');

-- 回退到b
ROLLBACK TO b
-- 继续回退 到a 
ROLLBACK TO a

-- 如果直接写 rollback 表示直接回退到事务开始的状态
ROLLBACK

如果确定提交-- commit ,则不能回退

• 回退事务

保存点是事务中的点，用于取消部分事务，当结束事务时，会自动的删除该事务所定义的所有保存点。当执行回退事务时，通过指定保存点可以回退到指定的点。

• 提交事务

使用commit语句可以提交事务。当执行了commit语句后,会确认事务的变化、结束事务、删除保存点、释放锁、数据生效。当使用commit语句结束事务子后，其他会话[其他连接]将可以查看到事务变化后的新数据[所有数据就正式生效]

---

事务细节 

1. 如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚

   INSERT INTO t24 VALUES(300,'milan');
   
   SELECT * FROM t24
   
   ROLLBACK
   

   

2. 如果开始一个事务，你没有创建保存点，你可以执行 rollback ，默认就是回退到你事务开始的状态

INSERT INTO t24 VALUES(300,'milan');

SELECT * FROM t24

ROLLBACK

START TRANSACTION
INSERT INTO t24 VALUES(400,'king');
INSERT INTO t24 VALUES(500,'bxc');
-- 表示直接回退到事务开始的状态
ROLLBACK 

 这时候会回退到

3.mysql的事务机制需要innodb的存储引擎还可以使用,myisam不好使。

4.开始一个事务  start transaction ,set autocommit=off;

---

四种隔离

•  事务隔离级别介绍

1. 多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。
2. 如果不考虑隔离性，可能会引发如下问题：脏读、不可重复读、幻读

• 脏读:当一个事务读取另一个事务尚未提交的修改时，产生脏读
• 不可重复读:同一查询在同一事务中多次进行,由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。
• 幻读:同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。

 c2统计10点以前的数据时可能在这个时候读到c1在10点以后的数据，这个时候c2就发生了不可重复读/幻读(具体看是修改或删除还是插入操作)

---

• 事务隔离级别

概念:Mysql隔离级别定义了事务与事务之间的隔离程度

---

隔离级别演示

-- 演示mysql的事务隔离级别

-- 1.  开了2个mysql的控制台
-- 2.  查看当前mysql的隔离级别
SELECT @@tx_isolation;

 左边添加用户但是没有提交，右边照样还是可以访问到添加了用户以后的数据(脏读)

左边提交了,右边能看到相应的数据->不可重复读和幻读

---

 将右边换成读已提交的隔离级别  Read commited

这时候左边添加数据，右边查询的时候并么有显示(没有发生脏读) 

这个时候发生了不可重复读和幻读

---

隔离级别修改为可重复读

 现在就算提交，右边也看不到(没有发生不可重复读和幻读)

---

隔离级别设置为可串行化

 

 左边添加/修改数据后右边查询表格时会卡住,光标一直闪烁但是不走。

现在左边提交了右边会显示超时。

 但是查询的时候直接提交就不超时了

---

设置隔离

1.查看当前会话隔离级别   set_transaction.sql

select  @@tx_isolation;

2.查看系统当前隔离级别

select @@global.tx_isolation;

3.设置当前会话隔离级别

set  session transaction isolation level [];

4.设置系统当前隔离级别

set global transaction isolation level [];

5.mysql 默认的事务隔离级别是 repeatable read,一般情况下,没有特殊要求,没有必要修改(因为该级别可以满足绝大部分项目需求)

全局修改:修改my.ini配置文件,在最后加上修改代码

---

 mysql事务ACID

• 事务的acid特性

1. 原子性(Atomicity)

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

      2.一致性(Consistency)

事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

      3.隔离性(lsolation)

事务的隔离是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

       4.持久性(Durability)

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

---

mysql表类型和存储引擎

• 基本介绍

1.MySQL的表类型由存储引擎(Storage Engines)决定，主要包括MyISAM、innoDB、Memory等。

2.MySQL 数据表主要支持六种类型，分别是:CSV、Memory、ARCHIVE、MRG_MYISAM、

MYISAM、InnoBDB。

3.这六种又分为两类，一类是"事务安全型"（transaction-safe），比如:InnoDB;其余都属于第二类,称为"非事务安全型"(non-transaction-safe)[mysiam和memory]。

 

重点介绍三种：MyISAM、InnoDB、MEMORY

1.MyISAM不支持事务、也不支持外键、但其访问速度快、对事务完整性没有要求

2.InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎,InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

3.MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一但服务关闭，表中的数据就会丢失掉，表的结构还在。

• 如何选择表的存储引擎

 1.如果你的应用不需要事务,处理的只是基本的CRUD操作，那么MyISAM是不二选择，速度快

  2.如果需要支持事务，选择InnoDB

  3.Memory 存储引擎就是将数据存储在内存中，由于没有磁盘I./O的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。(经典用法  用户的在线状态())

-- myisam 存储引擎
CREATE TABLE t27
(
	id INT,
	`name` VARCHAR(32) )ENGINE MYISAM
-- 1.添加速度快 2.不支持外键和事务 3.支持表级锁

START TRANSACTION;
SAVEPOINT t1
INSERT INTO t27 VALUES(1,'jack');
SELECT * FROM t27;

ROLLBACK TO t1;

这时候回退到t1,发生警告,表的信息不变(没有发生回退)

-- memory 存储引擎
-- 1. 数据存储在内存中[关闭了Mysql服务，数据丢失，但是表结构还在]
-- 2.执行速度很快(没有IO读写) 3.默认支持索引(hash表)

CREATE TABLE t29(
	 id INT,
	 `name` VARCHAR(32) ) ENGINE MEMORY

INSERT INTO t29 VALUES(1,'tom'),(2,'jack'),(3,'hsp');
SELECT * FROM t29;

	

 

重启Mysql

 数据丢失，但是表结构还在。

---

---

 修改存储引擎

-- 指令修改存储引擎
ALTER TABLE `t29` ENGINE=INNODB
	

---

视图 

视图原理

需求:a表的列信息很多，有些信息是个人比较重要的信息，如果我们希望某个用户只能查询a表的一部分信息，有什么办法->视图

1. 视图是一个虚拟表,其内容由查询定义，同真实的表一样，视图包含列，其数据来自对应的真实表。
2. 视图和基表的关系

视图的基本使用

1.  create view 视图名 as select 语句
2. alter view 视图名 as select 语句
3. SHOW CREATE VIEW 视图名
4. drop view 视图名1，视图名2

-- 创建视图

CREATE VIEW empp_view01
	AS 
	SELECT ename,job,sal FROM empp;
-- 查看视图
DESC empp_view01;

SELECT * FROM empp_view01;
SELECT ename,job FROM empp_view01;

-- 查看创建视图的指令
SHOW CREATE VIEW empp_view01;
-- 删除视图
DROP VIEW empp_view01;

---

• 视图细节讨论

1. 创建视图后，到数据库去查看，对应视图只有一个视图结构文件(形式:视图名.frm)
2. 视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete]
3. 视图中可以再使用视图

---

• 视图最佳实践 

1.安全。一些数据表有着重要的信息，有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。

2.性能。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接(JOIN)。 这样做不但麻烦，效率也相对比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。

3.灵活。如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。

视图练习:使用三张表创建一个视图

---

Mysql管理 

• Mysql用户

mysql中的用户，都存储在系统数据库mysql中 user 表中

其中user表的重要字段说明:

1.host :    允许登录的"位置"，localhost表示该用户只允许本机登录，也可以指定ip地址，比如:

192.168.1.100

2. user :           用户名;

3.authentication_string: 密码,是通过mysql的password()函数加密之后的密码。

• 创建用户

 create uesr  '用户名'  @' 允许登录的位置' identified by    '密码'    

说明：创建用户，同时指定密码

• 删除用户

drop user  '用户名'    @' 允许登录的位置' ;

-- Mysql用户的管理
-- 原因: 当我们做项目开发时，可以根据不同的开发人员，赋给他相应的Mysql操作权限
-- 所以，Mysql数据库管理人员(root)，根据需要创建不同的用户，赋给相应的权限供开发人员使用

-- 创建新的用户
-- 解读(1) 'hsp_edu' @ 'localhost' 表示用户的完整信息，'hsp_edu' 用户名 'localhost' 登录的ip
-- (2) 123456 密码，但是注意创建成功以后存放到Mysql的user表中的密码是passward('123456')加密后的密码
CREATE  USER 'hsp_edu'@'localhost' IDENTIFIED BY '123456'

SELECT * FROM 
	mysql.`user`
-- 删除用户
DROP USER 'hsp_edu'@'localhost'

---

• 用户修改密码

修改自己的密码:

set password =password('密码xxx');

修改他人的密码(需要有修改用户密码权限)

set password for '用户名'@'登录位置' =password('密码');

比如在hsp_edu修改root密码不行,但是在root修改hsp_edu可以。

---

Mysql权限管理

• 给用户授权

 基本语法：

grant 权限列表  on  库.对象名 to '用户名'  @ '登录位置'  [identified by  '密码'  ]

说明:

1.权限列表，多个权限用逗号隔开

grant select on ......

grant select,delete ,create on ......

grant all [privileges]  on   ...... //表示赋予该用户在对象上的所有权限

2.特别说明

*.*:代表本系统中的所有数据库的所有对象(表，视图，存储过程)

库.*:表示某个数据库中的所有数据对象(表，视图，存储过程等）

3.identified  by可以省略,也可以写出

(1)如果用户存在，就是修改该用户的密码。

(2)如果该用户不存在，就是创建该用户。

• 回收用户授权

基本语法：

revoke  权限列表  on   库.对象名   from '用户名'@'登录位置';

• 权限生效指令

如果权限没有生效,可以执行下面命令。

基本语法:

FLUSH  PRIVILEGES;

---

练习:

-- 使用 root 用户创建 testdb ,表news
CREATE DATABASE testdb
CREATE TABLE news(
	id INT,
	content VARCHAR(32));
-- 添加测试数据
INSERT INTO news VALUES(100,'北京新闻');
SELECT * FROM news;

-- 给baixiaochun用户 分配查看 news表和 添加news的权限
GRANT SELECT,INSERT
	ON testdb.`news`
	TO 'baixiaochun'@'localhost'

 

---

Mysql用户管理细节说明 manage_detail.sql

1.在创建用户的时候，如果不指定Host,则为%,%表示所有IP都有连接权限  create user xxx;

2.你也可以这样指定

create user 'xxx'@'192.168.1.%' 表示  xxx用户在 192.168.1.*的ip可以登录mysql

3.在删除用户的时候，如果host不是% ,需要明确指定 ’用户'@‘host值'

 学习如逆水行舟，不进则退。和小吴一起加油！