MySQL事务，视图，用户管理学习笔记【事务概念 | 事务隔离级别 | 设置级别 | 视图

博客主页：花果山~程序猿-CSDN博客

文章分栏：MySQL之旅_花果山~程序猿的博客-CSDN博客

关注我一起学习，一起进步，一起探索编程的无限可能吧！让我们一起努力，一起成长！

在这里插入图片描述

一，事务初识

二，支持事务的引擎

三，事务的提交方式

自动提交

手动提交

四，事务隔离级别

读未提交

读已提交

可重复读

穿行化

查看并设置隔离级别

设置隔离级别

五，视图

创建视图

其他规则

删除视图

嗨！收到一张超美的图，愿你每天都能顺心！

一，事务初识

在MySQL中，事务（Transaction）是一个非常重要的概念，它用于保证数据的一致性和完整性。事务可以看作是一组操作的集合，这些操作要么全部成功执行，要么全部不执行，是一个不可分割的工作单位。这四个特性通常被总结为ACID原则：

1. 原子性（Atomicity）：事务中的所有操作被视为一个整体，不可分割。事务的所有改变要么全部完成，要么全部不起作用。
2. 一致性（Consistency）：事务执行前后，数据库从一个一致状态转换到另一个一致状态。也就是说，事务应该保证数据库的完整性约束不被破坏。
3. 隔离性（Isolation）：多个事务并发执行时，彼此之间不应互相影响。一个事务内部的操作对其他事务是隔离的，直到该事务完成并提交。
4. 持久性（Durability）：一旦事务提交，其结果就是永久性的，即使系统发生故障也不会丢失。

### 例子

假设有一个银行转账的场景，张三需要向李四转账100元。这个过程可以分为两个关键的数据库操作：

1. 从张三的账户中减去100元。
2. 给李四的账户加上100元。

如果不使用事务，这两个操作可能会因为某种原因（比如系统崩溃、网络问题）而只完成其中一个，导致数据不一致（比如张三的钱减少了，但李四的钱没有增加）。

使用事务处理这个问题的方式如下：

start transaction;  --也可以使用 begin

savepoint s1; --创建保存点s1
-- 操作1: 从张三账户减去100元
UPDATE accounts SET balance = balance - 100 WHERE user = '张三';

savepoint s2; --创建保存点2;
-- 操作2: 给李四账户加上100元
UPDATE accounts SET balance = balance + 100 WHERE user = '李四';
commit;

在这个例子中，start transaction / begin 开始一个新的事务，然后执行两个更新操作，最后用 commit 提交事务。

如果在执行这两个操作的过程中发生了错误，可以通过rollback命令回滚事务，

rollback to s1;

撤销所有已经执行的操作，确保数据的一致性。

二，支持事务的引擎

mysql并不是所有引擎都支持事务，可以通过下面指令来查看引擎信息：

show engines;

三，事务的提交方式

自动提交

我们可以通过下面指令来查看事务提交方式状态

show variables like 'autocommit';

当然我们也可以手动关闭：set autocommit=0; --打开=1

手动提交

情况一：当我们autocommit关闭后，每次完成一条mysql语句的输入，这时需要我们手动commit，否则一旦进程退出，或者网络崩溃数据就会回滚。

情况二：一旦手动使用 start transaction / begine ，就必须手动使用commit手动提交。

我们需要知道曾经我们在mysql指令上本质上就是一条条开了自动提交的事务。

四，事务隔离级别

mysql服务端会面临同一份数据库，多个客户端并发的读写这时，我们就需要考虑数据如何安全的处理。在MySQL中，事务的隔离级别决定了一个事务在处理数据时能够看到其他事务修改数据的程度，这直接影响到事务的并发性能和数据的一致性。事务的隔离级别主要有四种：读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）、串行化（Serializable）。下面通过例子来解释这些概念：

读未提交

在这个隔离级别下，一个事务可以读取另一个事务尚未提交的数据。这种情况下容易出现“脏读”现象。

例子：

事务A开始，并更新了一条记录但未提交。
事务B开始，并读取了事务A更新但未提交的记录。
事务A决定回滚，之前更新的内容被撤销。
这时，事务B读取的就是无效的数据，即所谓的“脏读”。

读已提交

在此级别，事务只能读取已经提交的变更。避免了脏读，但可能出现不可重复读。

例子：

事务A读取了一条记录。
事务B开始，更新了这条记录并提交。
在事务A中，再次读取同一条记录，会发现数据已经改变，即使它自己没有做任何修改。这就是不可重复读。

可重复读

这是MySQL的默认事务隔离级别。在这个级别下，同一个事务内多次读取同一数据的结果是一致的，不会受到其他事务的影响。但可能会遇到幻读现象。

例子：

事务A开始，查询出所有状态为 'available' 的商品。
事务B开始，将其中一个商品的状态改为 'sold' 并提交。
事务A（在一个事务中）再次查询相同条件的商品列表，结果集仍然包含那个已被B修改状态的商品，仿佛B的修改从未发生过，实现了重复读的特性。但是，如果A尝试根据ID单独查询那个商品的状态（或者结束A事务后查询商品时），会发现状态已经变为'sold'，这种差异可能引起幻读。

穿行化

这是最严格的隔离级别（公司一般都不会使用该级别，效率太低了），通过完全序列化事务的执行来避免所有的并发问题。实际上，这意味着事务会按照一定的顺序依次执行，而不是并发执行。

例子：

假设有两个事务T1和T2，都试图修改同一条记录。
在串行化隔离级别下，T1先开始执行，T2必须等待T1完成（包括提交或回滚）后才能开始执行。
这样彻底避免了脏读、不可重复读和幻读的问题，但牺牲了并发性能。

查看并设置隔离级别

查看：

select @@global.tx_isolation;  --查看全局隔离级别，同时也是客户端的默认级别
select @@session.tx_isolation; --当前mysql会话(登录)级别
select @@tx_isolation;         --当前会话级别

区别：

tx_isolation 或 session_tx_isolation 控制的是单个数据库连接（会话）的事务隔离行为，而 global_tx_isolation 则影响服务器层面，控制新创建会话的默认事务隔离级别。前者是局部作用域，后者是全局作用域。

设置隔离级别

语法：

set [SESSION | GLOBAL] transaction isolation level  {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

如以下例子：

set session transaction isolation level serializable;

五，视图

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。视图的数据变化会影响到基表，基表的数据变化也会影响到视图。（学习目标是认识一下，一般的大型公司对于数据库的操作有着严格的限制，一般不会开放）

知道基表，与虚拟表什么意思即可。

基表：原始的数据表

虚拟表：创建产生的视图

创建视图

create view view_name as select ....；

案例：

那基表与虚拟表是否有联系呢？

答：通过上面这张图，我们可以知道视图（虚拟表），只有表结构文件（.frm），并没有数据文件(.ibd)我们不难猜测，基表与虚拟表共享数据，事实也是如此。如果我们修改内容，基表与虚拟表信息也将改变。

其他规则

与表一样，必须唯一命名（不能出现同名视图或表名）

创建视图数目无限制，但要考虑复杂查询创建为视图之后的性能影响（as 后面的select 效率需要考虑）

视图不能添加索引，也不能有关联的触发器或者默认值

视图可以提高安全性，必须具有足够的访问权限（但是一般公司不开放）

order by 可以用在视图中，但是如果从该视图检索数据 select 中也含有 order by , 那么该视图中的order by 将被覆盖（就近原则）

视图可以和表一起使用(支持内连接，多表查询）

删除视图

drop view 视图名;

六，用户管理

mysql是一款网络服务，有客户端，服务端，是一对多的情况，每一个客户有着自己的资源，也不希望别的客户对自己的资源进行随意的访问，修改，删除那么作为服务端就要对用户进行权限的管理。

我们知道曾经是通过免密进入mysql，那mysql的账号是在哪儿？

在root下，进入mysql库，后找到user表即可，这里我们需要先了解这三个数据：user，host(登录的主机)，authentication_string（经过哈希加密的密码）

创建新用户

create user '用户名'@'登录主机' identified by '密码'；

create user 'afeng_test'@'localhost' identified by '210764';

（注：1. 登录主机，localhost等价于127.0.0.1，外网想连接的化一般指定主机IP或者是极不推荐的'%'表示任意IP，因为数据库都是公司内网用的不会端口暴露给外网。

2.我们可以知道user本质上是表结构，完全是可通过表的insert into 进行创建，只是填参数填的有些费劲。

3. 如果设置了免密登录，我们是无法创建用户的，一般需要我们通过root登录，才能创建新用户）

删除用户

drop user '用户名'@'主机IP';

修改密码

用户修改自己密码

set password=password('新的密码');

root修改用户密码

方法一：set password for ' 用户名 ' @ ' 主机名 ' =password( ' 新的密码 ' ) ；

方法二：update user set authentication_string=password('密码'）where user='用户名';

(修改完后别忘记重启)

对用户权限管理

下面是mysql提供的权限列表：

增加权限（同时创个号）

语法：

grant 权限名1，权限名2... on 库.对象名 to '用户名'@'登陆位置' [identified by '密码']；

identified by 可选。如果用户存在，赋予权限的同时修改密码 , 如果该用户不存在，就是创建用户 。

如：

grant select on ...
grant select, delete, update ...
grant all privileges on --权限全部添加
grant ... on '*' ...  --代表本系统中的所有数据库 + 所有对象
grant ... on '库.*' ... --代表本系统中某个库的所有对象

回收权限

revoke 权限1，权限2 on 库 . 对象名 from ' 用户名 '@' 登陆位置 ' ；

如：

revoke all privileges on ...  --回收全部权限

最后刷新权限

flush privileges;

如何设置mysqld初始密码

首先我们是通过设置了免密登录的，查看mysql配置文件.

vim  /etc/my.cnf

我们会看到我们安装时，添加的免密登录配置：

然后进入 mysql库中，找到里面的user表，然后修改root用户的密码。

updata user set authentication_string=password('密码') where user='root';

成功后退出mysql，然后回到配置文件中，将免密登录代码关闭，接着重启mysqld。

systemctl restart mysqld;

用我们刚才设置的密码登录后，我们可以使用一下指令 show databases;

如果出现这样的情况：

通过生成的临时密码登录后操作mysql时会报如下错误

解决方法：

1.alter user 'root'@'localhost' identified by '123'; 
2.或set password for 'root'@'localhost'=password('123');
3.旧sql版本: set password=password('密码');

如果密码过于简单，会报以下错误：

修改好后，我们退出重新登录mysql即可

结语

本小节就到这里了，感谢小伙伴的浏览，如果有什么建议，欢迎在评论区评论，如果给小伙伴带来一些收获，请动动你发财的小手点个免费的赞，你的点赞和关注永远是博主创作的动力源泉。