Mysql事务、引擎和视图，用户管理与授权

什么是事务

事务用于保证数据的一致性，它由一组相关的dml（增删改）语句组成，改组的dml语句要么全部成功，要么全部失败。

事务和锁

当执行事务操作时，mysql会在表上加锁，防止其他用户改表的数据，这对用户来讲是十分重要的。

mysql数据库控制台事务的几个重要操作

1.start transcation       开始一个事务

2.savepoint        保存点名，设置保存点

3.rollback to       保存点名，回退事务

4.rollback       回退全部事务

5.commit   提交事务，所有的操作生效，不能回退

保存点：

保存点是事务中的点，用于取消部分事务，当结束事务时，会自动的删除该事务所定义的所有保存点。

回退事务：

当执行回退事务时，通过指定保存点可以回退到指定的点。

提交事务：

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

注意：

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

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

3.你也可以在这个事务中（还没提交时），创建多个保存点。

4.你可以在事务没有提交前，选择回退到哪个保存点。

5.mysql的事务机制需要innodb的存储引擎才可以使用。

6.开始一个事务语句：start transaction       或者 set autocommit=off

事务隔离级别

多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。

如果不考虑隔离性，可能会引发 脏读、不可重复读、幻读。

脏读：当一个事务读取另一个事务尚未提交的修改时，产生脏读。

不可重复读：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。

幻读：同一查询在同一事务中多次进行，由于其他事务提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。

事务隔离级别

隔离级别	脏读	不可重复读	幻读	加锁读
读未提交	√	√	√	不加锁
读已提交	×	√	√	不加锁
可重复读	×	×	×	不加锁
可串行化	×	×	×	加锁

在可重复读级别下，查询者在start transaction 下使用updata语句也能看到修改者commit后的数据，也会发生幻读现象。

查看、设置隔离级别语句

设置隔离级别

5,8可以用
当前会话:set session transaction isolation level repeatable read

系统：set global transaction isolation level repeatable read

查看隔离级别

5
系统:select @@global.tx_isolation

当前会话：select @@tx_isolation

8.0
系统：select @@global.transaction_isolation 

当前会话：select @@transaction_isolation 或者 show variables like '%transaction_isolation%'

开始事务

start transaction;

事务的ACID特性

原子性（Atomicity）

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

一致性（Consistency）

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

隔离性（Isolation）

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

持久性（Durability）

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

表类型和存储引擎

1.Mysql的表类型由存储引擎决定，主要包括MyISAM,InnoDB，Memory

2.Mysql数据表主要支持六大类型，分别是：CSV,Memory,ARCHIVE,MRG_MYISAM,MYISAM,InnoBD

3.这六种又分为两类，一类“事务安全型”，InnoDB；其余都属于第二类，称为“非事务安全型”。

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

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

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

如何选择表的存储引擎

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

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

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

视图

视图是一个虚拟表，其内容有查询定义，同真实的表一样，视图包含列，其数据来自对应的真实表。通过视图可以修改基表的数据，基表的改变也会影响到视图的数据。

基本使用

1.create view 视图名 as select语句

2.alter view 视图名 as select语句

3.show create view 视图名

4.drop view 视图名1，视图名2

视图优点

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

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

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

用户管理

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

重要字段说明

1.host ：允许登录的“位置”，localhost表示该用户只允许本机登录也可以指定ip地址。

2.user：  用户名

3.authentication_string ： 密码，是通过mysql的password（）函数加密之后的密码。

创建用户

 create user ‘用户名’ @‘允许登录位置’ identified by ‘密码’；

删除用户

 drop user ‘用户名’ @‘允许登录位置’；

修改密码

自己：set password = password（‘密码’）；

他人（需要权限）：set password for ‘用户名’@‘登录位置’ = password（‘密码’）；

不同的数据库用户，登录到DBMS后，根据相应的权限，可以操作的数据库和数据对象（表，视图，触发器）都不一样。

授予权限

语法

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

注意：

*.* : 代表本系统中的所有数据库的所有对象

库.* ：表示某个数据库中的所有数据对象

回收用户权限

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

权限生效指令

flush privileges;

注意：

1.在创建用户的时候，如果不指定host，则为%，%表示所有ip都有连接权限

create user xxx；

2.也可以这样指定

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

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