SQL语言与MySQL数据库（六）

一、事务

1.事务的基本介绍

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

             操作：

                      开启事务 start transaction；

                      回滚 rollback；

                      提交 commit。

  案例：

-- 事务管理

START TRANSACTION;

UPDATE account SET balance=balance-500 WHERE NAME='zhangsan';

-- 出错了

UPDATE account SET balance=balance+500 WHERE NAME='lisi';

COMMIT;

ROLLBACK;

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

          事务提交的两种方式：

                自动提交：一条DML（增删改）语句会自动提交一次事务

                手动提交：需要先开启事务，再提交；Oracle数据库是手动提交事务

         修改事务提交的默认方式：

                查看： select @@autocommit; -- 1自动提交 0手动提交

                修改：set @@autocommit=0;

2.事务的四大特征

               · 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败

               · 持久性：当事务提交或回滚后，数据库会持久化的保存数据

               · 隔离性：多个事务之间，相互独立

               · 一致性：当事务操作前后，数据的总量保持一致

3.事务的隔离级别

              概念：多个事务之间是隔离的、相互独立的。但是如果多个事务操作同一批数据，则就会引发一些问题，设置不同的隔离级别就可以解决这些问题。

              存在问题：

                      · 脏读：一个事务，读取到另一个事务中没有提交的数据

                      · 不可重复读（虚读）：在同一个事务中，两次读到不同的数据

                      · 幻读：一个事务操作（DML）数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改

               隔离级别：

                       read uncommitted：读未提交 产生问题：脏读、不可重复读、幻读

                       read committed：读已提交(Oracle) 产生问题：不可重复读、幻读

                       repeatable read：可重复读(MySQL默认) 产生问题：幻读

                       serializable：串行化 可解决所有问题

             注意：隔离级别从小到大安全性越来越高，但效率越来越低

             修改数据库隔离级别：

                    查询隔离级别  select @@tx_isolation; 新版本MySQL SELECT @@transaction_isolation;

                    设置隔离级别（设置后刷新数据库）  set global transaction isolation level 隔离级别字符串；

案例：转账操作（事务管理）

START TRANSACTION;     -- 开启事务

UPDATE account SET balance=balance-500 WHERE NAME='zhangsan';

UPDATE account SET balance=balance+500 WHERE NAME='lisi';

COMMIT;                 -- 提交事务

ROLLBACK;               -- 回滚 

二、用户管理和权限管理

DCL：管理用户，授权

 1.管理用户

    查询用户：

-- 切换到mysql数据库

use mysql;

-- 查询user表

Select * from user;

   添加用户：

create user ‘用户名’@’主机名’ identified by ‘密码’;

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

--  %表示可以在任意主机使用用户登录数据库

 删除用户 ：

drop user ‘用户名’@’主机名’ ;

  修改用户密码：

use mysql;

update user set password = password(‘新密码’) where user=’用户名’;

update user set password = password(‘12345’) where user=’root’;

set password for ‘用户名’@’主机名’=password(‘新密码’);

set password for ‘root’@’localhost’=password(‘987’); 

忘记root用户密码：

       1.管理员运行cmd -->执行net stop mysql 停止mysql服务

       2.使用无验证方式启动mysql服务 -->mysql --skip-grant-tables;

       3.打开新的cmd -->mysql; 回车,无验证登录成功

             Use mysql；

             update user set password = password(‘新密码’) where user=’用户名’;

       4.关闭两个窗口

       5.打开任务管理器，手动结束mysqld.exe的进程

       6.启动mysql服务 net start mysql

       7.使用新密码登录

 

2.授权

查询权限

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

SHOW GRANTS FOR 'root'@'localhost';

授予权限

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

GRANT SELECT,DELETE,UPDATE ON demo1.account TO 'rootdemo'@'%';

GRANT ALL ON *.* TO 'rootdemo'@'%';   -- 通配，授予root用户的权限

撤销权限

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

REVOKE SELECT,DELETE ON demo1.account FROM  'rootdemo'@'%';