MySQL(事务、权限管理、视图)
事务
一、事务的概念
事务是一个原子操作。是一个最小执行单元。可以由一个或多个SQL语句组成,在同一个事务中,所有的SQL语句都成功执行时,整个事务成功!有一个SQL语句执行失败,整个事务都执行失败!
二、事务的边界
-
开始:连接到数据库,执行一条DML语句。 上一个事务结束后,又输入了一条DML语句,即事务的开始
-
结束:
- 提交:
- 显示提交:COMMIT;
- 隐式提交:一条DML语句。正常退出(客户端退出链接)
- 回滚:
- 显示回滚:ROLLBACK;
- 隐式回滚:非正常退出(断电、死机),执行了创建、删除的语句,但是失败了!会为这个无效的SQL语句执行回滚。
- 提交:
三、事务的原理
数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段),一个事务中所有的增删改语句的执行结果都会缓存在回滚段中,只有当事务中所有的SQL语句均正常结束(COMMIT),才会将回滚段中的数据同步到数据库。否则无论因为任何原因失败了,则整个事务回滚(ROLLBACK);
四、事务的特性
Atomicity(原子性)
表示的是一个事务内的所有操作是一个整体,要么全部成功,要么全部失败。
- 对于一个事务来说,不可能只执行其中的一个部分操作,这就是事物的原子性。
Consistency(一致性)
表示一个事务内有一个操作失败时,所有的更改过得数据都必须回滚到修改前状态。
- 数据库总是从一个一致性的状态转换到另一个一致性的状态。
Isolation(隔离性)
事务查看数据操作时数据所处的状态,要么是另一个并发事务修改数据之前的状态,要么是另一个并发事务修改它之后的状态。事务不会查看中间状态的数据
- 通常来说,一个事务所做的修改在最终提交以前,对其他事物是不可见的。
Durability(持久性)
事务完成之后,对于数据库的影响是永久性的。
五、事务的应用
应用环境:基于增删改语句的操作结果(均返回操作后受影响的行数),可通过程序逻辑手动控制事务的提交或回滚
#开启事务
START TRANSACTION;#SET autoCommit = 0;#方式2 设置自动提交为0 关闭自动提交 | 1 开启自动提交
#1账户扣钱
UPDATE account SET money = money - 1000 WHERE id = 1;
#2账户加钱
UPDATE account SET money = money + 1000 WHERE id = 2;
#执行提交 ---成功
COMMIT;
#执行回滚 ---失败
ROLLBACK;
权限管理
一、创建用户
语法:CREATE USER 用户名 IDENTIFIED BY 密码
#创建用户
CREATE USER 'zhangsan' IDENTIFIED BY '123';
二、授权
语法:GRANT ALL ON 数据库.表名 TO 用户名;
#将companydb数据里的grade表授权给zhangsan
GRANT ALL ON companydb.`grade` TO 'zhangsan';
#将companydb数据库里的所有表授权给zhangsan
GRANT ALL ON companydb.* TO 'zhangsan';
三、撤销权限
语法:REVOKE ALL ON 数据库.表名 FROM 用户名
REVOKE ALL ON companydb.grade FROM 'zhangsan';
四、删除用户
语法:DROP USER 用户名;
DROP USER 'zhangsan';
视图
一、概念
视图,虚拟表,从一个表中或多个表中查询出来的结果表,作用和真实表一样,包含一系列的带有行和列的数据。视图中,可以使用SELECT语句查询数据,也可以使用INSERT、UPDATE、DELETE修改记录,视图可以使用户操作方便,并保障了数据库系统安全。
二、视图特点
- 优点
- 简单化,数据所见即所得
- 安全性,只能查询或修改视图中锁能见到的数据
- 逻辑独立性,可以屏蔽真实表结构变化带来的影响。
- 缺点
- 性能相对较差,简单的查询会稍微复杂
- 修改不方便,当视图的数据时复杂的聚合视图时,无法修改。
三、视图的创建
语法:CREATE VIEW 视图名 AS 查询数据源表的语句;
创建视图
#创建一个t_empinfo视图,该视图的数据是员工姓名,邮箱,手机号码
CREATE VIEW t_empinfo
AS
SELECT FIRST_NAME,LAST_NAME,email,PHONE_NUMBER FROM t_employees;
使用视图
#使用视图
#查询,所见即所得
SELECT * FROM t_empinfo WHERE FIRST_NAME='Steven' AND LAST_NAME='King';
#修改。只能修改得到的
UPDATE t_empinfo SET email = 'Kings' WHERE FIRST_NAME='Steven' AND LAST_NAME='King';
四、视图的修改
- 方式一:CREATE OR REPLACE VIEW 视图名 AS 查询源表的语句;
存在就替换数据,不存在就新建
CREATE OR REPLACE VIEW t_empinfo
AS
SELECT employee_id,FIRST_NAME,LAST_NAME,email,PHONE_NUMBER FROM t_employees;
- 方式二:ALTER VIEW 视图名 AS查询源表的语句;
ALTER VIEW t_empinfo
AS
SELECT FIRST_NAME,LAST_NAME,email,PHONE_NUMBER FROM t_employees;
五、视图的删除
语法:DROP VIEW 视图名
DROP VIEW t_empinfo;
注意:删除视图不会影响原表的数据
六、视图的注意事项
- 视图不会独立存储数据,原表发生改变,视图的数据也发生改变。没有优化查询的性能
- 如果视图包含以下结构中的一种,则视图不可更新
- 聚合函数的结果
- GROUP BY分组后的结果
- HAVING筛选过滤后的结果
- UNION、UNION ALL联合后的结果