MySQL的基本学习（五）——事务、DCL和SQL基图

最新推荐文章于 2024-09-16 00:12:33 发布
最新推荐文章于 2024-09-16 00:12:33 发布
阅读量199 收藏
点赞数
文章标签： sql mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_46378049/article/details/114601230
版权

MySQL的基本学习（五）——事务、DCL和SQL基图

前言

前面写了一篇比较短的文章简单记录了一下多表查询的利用示例，这篇文章我们来继续学习MySQL数据库，我们这篇主要是学习事务的概念还有SQL语言的最后一类DCL。

 

 

 

 

 

事务

概念

如果一个包含多个步骤的业务操作，叫做事务管理，那么这些操作要么同时成功，要么同时失败。

操作

语法

  • 开启事务：START TRANSACTION
  • 回滚：ROLLBACK
  • 提交；COMMIT

示例

我们来用一个示例学习事务的操作：

我们有一个账户表

CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10),
balance DOUBLE
);

里面有Tom和Jack，两名账户，他们各自有一千块钱

-- 添加数据
INSERT INTO 
account (name,balance)
VALUES
('Tom',1000),
('Jack',1000);

Tom想要转账500元给Jack。

-- 1.Tom的账户减少500元
UPDATE account SET balance=balance-500 WHERE name='Tom';
-- 2.Jack的账户增加500元
UPDATE account SET balance=balance+500 WHERE name='Jack';

我们先不考虑判读余额是否大于等于500的问题，我们就先考虑转账

我们利用SQLyog等数据库图形软件去执行这两条语句，很明显这样操作没有问题，最后结果：

    id  name    balance  
------  ------  ---------
     1  Tom           500
     2  Jack         1500

可是如果我们中间出现了一些错误，例如：

-- 1.Tom的账户减少500元
UPDATE account SET balance=balance-500 WHERE name='Tom';
出错了
-- 2.Jack的账户增加500元
UPDATE account SET balance=balance+500 WHERE name='Jack';

那么再执行，第一句会被执行，执行到中间的时候会报错无法解析“出错了”，最后结果：

    id  name    balance  
------  ------  ---------
     1  Tom           500
     2  Jack         1000

很明显，这是一次失败的转账，Tom损失了钱而Jack并没有得到钱。

故我们引出事务的概念，利用一致性的特点避免这种操作中的失误。

我们使用关键字START  TRANSACTION 来开启事务。

然后我们执行：

-- 0.开启事务
START TRANSACTION;
-- 1.Tom的账户减少500元
UPDATE account SET balance=balance-500 WHERE NAME='Tom';
出错了
-- 2.Jack的账户增加500元
UPDATE account SET balance=balance+500 WHERE NAME='Jack';

然后检索全表，我们发现

    id  name    balance  
------  ------  ---------
     1  Tom           500
     2  Jack         1000

这个数据明显是错误的，但是我们在别的地方登录这个MySQL数据库，我们去查看这个表，我们会发现Tom和Jack的账户还是一千，原来开启事务之后，该端的数据库的变化就只是临时性变化，当该端窗口关闭，临时变化就会被取消。

我们发现数据不如意之后，就可以使用如下命令进行回滚：

-- 回滚事务
ROLLBACK;

执行回滚之后，数据就会恢复到执行START TRANSACTION，即开启事务的地方。

如果我们把“出错了”去掉，再执行：

-- 0.开启事务
START TRANSACTION;
-- 1.Tom的账户减少500元
UPDATE account SET balance=balance-500 WHERE NAME='Tom';
-- 2.Jack的账户增加500元
UPDATE account SET balance=balance+500 WHERE NAME='Jack';

然后查看发现数据合理，则就可以使用下面的命令进行提交，实现数据库中的持久性修改。

-- 提交事务
COMMIT;

注意，如果开启了事务不进行提交，那么在关闭该端MySQL的时候，数据会自动回滚。

 

插曲：自动提交与手动提交

MySQL的提交方式

在MySQL数据库中事务默认自动提交。

即一条DML（增删改），自动进行了事务的开启、提交，从而一条语句就进行了一次持久化的更改。

总结一下就是：

事务提交的两种方式：

  1. 自动提交：（MySQL默认自动提交）
  2. 手动提交：需要先开启事务，在进行提交命令

修改事务的默认提交方式

我们在MySQL的软件中执行：

SELECT @@autocommit;

会返回：

@@autocommit  
--------------
             1

@@autocommit这个数据是1，则代表现在事务的提交方式是自动提交；如果是0，则为手动提交。

由此，修改事务的提交方式只需要修改这个值即可：

SET @@autocommit=0;

这个值调整成0之后，你的每一次登录MySQL的操作，一定要注意最后要手动执行：

-- 提交事务
COMMIT;

Oracle数据库是手动提交事务噢，尽早养成手动提交事务的好习惯吧。

 

事务的四大特征——ACID

  1. 原子性（Atomicity）：事务是不可分割的最小操作单位，要么同时成功，要么同时失败。
  2. 持久性（Consistency）：当事务结束之后（提交或者回滚），数据库会持久化的保存数据。
  3. 隔离性（Isolation）：多个事务之间，相互独立。
  4. 一致性（Durability）：事务操作前后，数据总量不变。

一致性是事务的最终目的，原子性、隔离性、持久性都是为了实现一致性。

 

事务的隔离级别

概念

事务的四大特征之中，有一个特征叫做“隔离性”，即多个事务之间隔离的，相互独立的。但是，如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

存在问题

多个事务操作用一批数据，会引发如下问题：

  1. 脏读：一个事务，读取到另一个事务中没有提交的数据
  2. 虚读（不可重复读）：同一个事务中两次读取到的数据不一样
  3. 幻读：一个事务操作（DML）数据表中所有的记录，另一个事务添加了一条数据，则第一个事务查看不到自己的修改。

隔离级别

  • read  uncommitted：读未提交。
    • 产生的问题：脏读、虚读、幻读
  • read  committed：读已提交（Oracle默认）
    • 产生的问题：虚读、幻读
  • repeatable read：可重复读（MySQL默认）
    • 产生的问题：幻读
  • serializable：串行化（串行化类似于线程锁，是一种上锁策略）
    • 可以解决所有的问题

隔离级别从小到大安全性越来越高，但是效率越来越低。

查询隔离级别

SELECT  @@tx_isolation;

Mysql8以上，tx_isolation更名为 transaction_isolation

设置隔离级别

SET  global  transaction  isolation  level  级别字符串;

每次设置隔离级别之后，需要关闭当前连接再打开再查询，才能通过“查询隔离级别”看到隔离级别设置成功。

 

不同问题的演示

还是我们的account表，表中的Tom和Jack的账户各自有1000元。

mysql> SELECT * FROM account;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | Tom  |    1000 |
|  2 | Jack |    1000 |
+----+------+---------+
2 rows in set (0.00 sec)

我们开启一个窗口登录MySQL，然后我们设置隔离级别：读未提交

SET  global  transaction  isolation  level  read  uncommitted;

然后我们再开启一个窗口，然后两个窗口同时开启事务：

START TRANSACTION;

然后在窗口1中，给Tom减少500，给Jack增加500

UPDATE account SET balance=balance-500 WHERE name='Tom';
UPDATE account SET balance=balance+500 WHERE name='Jack';

注意，这里我们先不要提交。然后我们在两个窗口都分别查看account表，然后会发现两个表都如下：

mysql> SELECT * FROM account;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | Tom  |     500 |
|  2 | Jack |    1500 |
+----+------+---------+
2 rows in set (0.00 sec)

这就是脏读，事务并没提交，但是不同的登录端拿到的数据在同步变化。

另外这里还有虚读的问题，第二个MySQL登录端开启事务后，两次检索account表，余额发生了变化，数据不一样。

数据脏读有什么坏处呢？

例如Tom和Jack各自有1000元，Jack找Tom借500元，Tom利用数据库开启事务，给Jack转账了500，但是尚未提交，Tom让Jack去查看自己的余额，Jack用另一端登录MySQL发现自己的余额成了1500，所以就给Tom写了一张500元欠条，这时，Tom那边利用命令ROLLBACK进行了回滚，两人的余额又各自成了1000，Jack就很冤枉的欠了Tom500元。

 

数据虚读有什么坏处呢？

例如Jack的老婆要检查Jack的余额，Jack开启事务后，查看了一下自己的余额，发现是1000元，然后离开房间要去把老婆叫过来，这时Tom在另一个MySQL端给Jack转了500元，然后提交了Tom那边的事务。过了一会Jack的老婆来了，一检索余额发现有1500元，这Jack就非常冤枉。

避免脏读，我们接下来只要将事务的隔离级别升级成read  committed即可。

如果需要避免虚读，我们将事务的隔离级别升级成repeatable read即可，这种情况下，别的MySQL登录端提交了他们各自的事务，就可以看到数据的真正的变化了。

幻读问题在MySQL这里不能演示，所以我们就讲到这里了。

串行化

最高隔离级别串行化底层是加锁机制，感兴趣的可以试一下：一登录端开启事务进行操作的时候，另一登录端使用SELECT * FROM 检索表的时候会出现阻塞，因为该表已经被第一端给锁起来了，必须等待第一端结束事务（提交或回滚）。

 

DCL

SQL四大类语言中最后还有一个DCL没有讲解，这里来补上。

概念

DCL，（Data Control Language）数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户，主要就是管理用户和授权两个方面。

关键字：GRANT、REVOKE等

不同于DDL操作数据库和数据表、DML用来增删改数据表中的数据、DQL来查询表中的数据，DCL主要来管理数据库的用户。

DCL语句只需简单掌握即可，在商业应用中，会有专门的数据库管理人员来管理数据库的账号等问题。

管理用户

我们的用户信息在MySQL的数据目录（一般是C:/ProgramData/MySQL/MySQL Server 8.0/Data）中，系统自带数据库mysql，就是存放了一些用户信息。

添加用户

一般出于安全性考虑，我们不会直接在user中INSERT，我们会使用专门的语法。

CREATE  USER  '用户名'@'主机名'  IDENTIFIED BY  '密码';

示例：

创建用户Tom，在主机登录：

CREATE USER 'Tom'@'localhost' IDENTIFIED  BY '123456';

创建用户Jack，可以在任意电脑登录：

CREATE USER 'Jack'@'%' IDENTIFIED  BY '123456';

删除用户

DROP  USER '用户名'@'主机名';

示例：

删除用户Tom

DROP USER 'Tom'@'localhost';

修改用户密码

ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';

忘记了root密码怎么办：

  1. cmd（管理员权限下）==》net  stop  mysql  ：停止mysql服务
  2. 使用无验证方式启动mysql服务：mysqld  –skip-grant-tables，然后该窗口会卡顿
  3. 然后保持前一个窗口的前提下，打开一个新的mysql窗口，输入mysql直接登录
  4. 然后修改root密码
  5. 修改后关闭这两个窗口
  6. 打开任务管理器，找到mysql.exe进程，手动结束
  7. cmd（管理员权限下）==》net  start  mysql  ：启动mysql服务

另外，如果出现提示mysql服务无效，请打开services.msc，在其中查看mysql的服务名，用这个服务名即可。

 

查询用户

切换到mysql数据库，查看user表即可：

USE  mysql;
SELECT * FROM user;

注意：

  1. 密码password是经过加密的
  2. %，表示可以在任意主机上使用用户登录数据库

 

权限管理

查询权限

SHOW GRANTS FOR '用户名'@'主机名';

例如

SHOW GRANTS FOR 'Les'@'%';


Grants for Les@%                 
---------------------------------
GRANT USAGE ON *.* TO `Les`@`%`

即Les用户仅具有登录权限，使用Les去USE  db3，就会弹出“拒绝访问”。

 

授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

示例：

GRANT SELECT ON db3.account TO 'Les'@'%';

如此一来，Les就可以USE  db3，但是如果SHOW  TABLES; 只能看见account这一张表，并且只能对该表进行查询操作。

如果我们这样做：

GRANT SELECT,UPDATE,DELETE ON db3.account TO 'Les'@'%';

就可以对该account表进行多种操作了。

那么如果要给某用户授予所有权限且对于所有数据库和数据表呢？

GRANT ALL ON *.* TO 'Les'@'%';

从此Les就得到所有权限。

 

撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

示例：

REVOKE SELECT ON db3.account  FROM  'Les'@'%';

然后我们在Les端：

mysql> SELECT * FROM account;
ERROR 1142 (42000)

 

撤销也可以使用通配符，道理和上面一样。

 

 

SQL基图

 

 

 

商业转载 请联系作者获得授权，非商业转载 请标明出处，谢谢

 

 

 

 

轻舟在过
关注
MySQL —— 事务
weixin_46540606的博客
02-01 582
概念 在MySQL中的事务，是由存储引擎实现的，只有InnoDB存储引擎支持事务 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行 事务用来管理DDL、DML、DCL操作，比如insert、update、delete语句，默认是自动提交的 事务操作 MySQL的事务操作主要有以下三种： 开启事务：任何一条DML语句（insert、update、delete）执行，标志事务的开启 提交事务：成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步 回滚事务：
MySQL基础篇3——DDL、DML、DCL使用篇
无
04-02 2663
此笔记来自b站尚硅谷课程，仅做复习使用。 一、创建和管理库1、创建数据库（CREATE DATABASE）2、修改数据库（ALTER DATABASE）3、删除数据库（DROP DATABASE）二、创建和管理表1、创建表（CREATE TABLE）2、修改表（ ALTER TABLE）3、重命名表（RENAME TABLE）4、删除表（DROP TABLE）5、清空表（TRUNCATE TABLE）6. DCL 中 COMMIT 和 ROLLBACK三、数据处理之增删改1、添加(INSERT INTO)
mysql创建用户并赋予权限
BorisCao的博客
10-15 191
1.创建用户 CREATE USER 'test'@'localhost' IDENTIFIED BY '123456'; 2.赋予操作权限 GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost'; 3.赋予远程访问权限 GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' IDENTIFIED BY '123456';
MySQL 学习（三）创建用户、授权、删除
ACGkaka的博客
07-03 207
目录1. 创建用户2. 授权3. 查看授权4. 撤销授权5. 删除用户 1. 创建用户 # username 是用户名，密码为123456 # localhost 用户只能在本地登录 CREATE USER username@localhost IDENTIFIED BY '123456'; # % 可以在任何一台电脑上都可以登录 CREATE USER username@% IDENTIFIED BY '123456'; 2. 授权 # SELECT, INSERT, UPDATE 设置用户的操作权限
MySQL添加用户、为用户分配权限
灿烂星空和猫
06-22 376
登录MySQL 登录本地用户 mysql -u root -p 登录外网用户（需要注意服务器可能只允许本地登录，需要修改响应的配置文件） mysql -u zhrt -h 10.64.6.4 -p 添加用户 允许本地访问的用户（127.0.0.1） create user zhrt@localhost identified by '123456'; 允许外网IP访问的用户 create user 'zhrt'@'%' identified by '123456'; 用户分配权限 授予用户在本地服
mysql新建库与权限
04-09 648
这里写自定义目录标题欢迎使用Markdown编辑器新的改变功能快捷键合理的创建标题，有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表如何创建一个注脚注释也是必不可少的KaTeX数学公式新的甘特图功能，丰富你的文章UML 图表FLowchart流程图导出与导入导出导入 欢迎使用Markdown编辑器 你好！ 这是你第一次使用 Markdown编辑器 所展示的欢迎页。如果你想学习如何使用Mar
MySQL之数据库管理（用户和数据权限管理）
遇安的博客
08-24 5213
CREATE USER用于创建新的MySQL账户。CREATE USER会在系统本身的mysql数据库的user表中添加一个新记录。要使用CREATE USER，必须拥有mysql数据库的全局
数据库学习之MySQL (零）——纲举目张 文章导航
01-19
数据库学习之MySQL (四）——DQL DDL DML DCL 事务 到底是什么 MySQL上手 DQL DQL基础查询语法 数据库学习之MySQL (五）——select 语句 IFNULL() CONCAT()的应用 数据库学习之MySQL (六）——条件查询 条件表达式 ...
Java学习日志(二十七): MySQL基础，SQL语句操作数据库和表
12-14
在学习JavaEE时，掌握这些基本的MySQL操作和SQL语句是非常重要的，因为它们是开发中与数据库交互的基础。了解并熟练运用这些知识，能够帮助开发者高效地管理数据库，存储和检索数据，从而实现应用程序的功能。
数据库实验五 安全性语言实验
gui_bjyxszd的博客
12-11 1557
数据库实验五 安全性语言实验
mysql用户权限
adelexu1971的博客
11-22 99
一）用户权限管理 先查看用户名密码和IP是否允许连接 库级权限 mysql.db 表级权限tables_priv 字段权限columns_priv 管理权限 procs_priv 1）用户授权（grant） mysql> help grantName: 'GRANT'Description:Syntax:GRANT priv_type [(...
MySQL基础命令
孤独礼赞的博客
01-06 491
mysql基础命令 **> show databases; # 查看库 > > show tables; # 查看表 > > use 库名 # 进入库 > > select database(); # 查看当前所在库 > > select * from 表名; # 查看表内所有内容 > > selec...
mysql 赋权限语句 详细_MySQL常用语句（三）：创建用户与权限赋予
weixin_30750081的博客
01-27 582
1、create user 'test_1'@'localhost' identified by '123456';创建用户验证：退出当前登录，使用mysql -utest_1 -p123456如果能登录，则证明创建成功2、select user,host,password from mysql.user;显示当前实例所有用户的信息3、delete from mysql.user where us...
sqlalchemy JSON 字段写入时中文序列化问题
llc的博客
09-12 361
_table_args__ = ({"comment": "表名称"})...extra = Column(JSON, comment="其他属性")...
SQL(结构性查询语句）
2301_78693337的博客
09-11 480
以上就是今天要讲的内容，本文介绍了基础的SQL语法使用，而sql还提供了大量能使我们快速便捷地处理数据的函数和方法等着我们探索。
SqlDb_.cs 与 csharp_SqlDb.h
最新发布
weixin_42944928的博客
09-16 290
【代码】SqlDb_.cs 与 csharp_SqlDb.h。
经典sql题（一）求连续登录不少于三天用户
m0_58076578的博客
09-14 791
去重提取日期查询：提取每个用户的唯一登录日期。结果表table1：显示每个用户的唯一日期。为每个用户生成序列号查询：为每个用户的日期生成序号，并计算date2。结果表table2：显示用户的日期和对应的date2。步骤使用函数，按照日期顺序为每个用户的登录日期分配一个序号。例如，对于用户 ID = 1，假设他们的日期是2023-10-012023-10-02和2023-10-03将为它们生成序号 1、2 和 3。date2是通过将每个日期减去它的序号得到的。这个操作的目的是为了检测连续的日期。
学习SQL语言完整资料和MySQL的安装与使用技巧
学习SQL语言的目标是熟悉关系型数据库查询系统的SQL查询语言，通过实例来学习SQL的语法，以及安装和配置MySQL并利用MySQL测试SQL语法。 在学习SQL语言之前，我们需要了解SQL是什么以及它的用途。SQL是一种用于管理...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

轻舟在过

您的支持是我创作的最大动力~

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值