事务、视图、索引、数据备份和恢复

11111100111

于 2024-01-08 12:20:49 发布

阅读量1k

点赞数 29

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/m0_61606343/article/details/135453685

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

一、事务

1.1 模拟银行转账案例

#创建表并添加数据
CREATE TABLE IF NOT EXISTS mybank(
    cid CHAR(4), #卡号
    cname VARCHAR(30),#用户名
    balance DECIMAL(10,2)  #当前余额
);
INSERT INTO mybank(cid,cname,balance)VALUES(4001,'张三',1000);
INSERT INTO mybank(cid,cname,balance)VALUES(4002,'李四',1);

SELECT * FROM MYBANK;

#转账的理想操作（张三账户少500，李四账户加500）
UPDATE MYBANK SET BALANCE = BALANCE - 500 WHERE CID = 4001;
UPDATE MYBANK SET BALANCE = BALANCE + 500 WHERE CID = 4002;

1.2 什么是事务

事务（transaction）是一种机制，一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库操作命令要么都执行，要么都不执行。事务是一个不可分割的工作逻辑单元。

1.3 事务的特性

实物必须具备以下四个属性，简称ACID属性：

原子性（Atomicity）

事务的各部操作时不可分的（原子的），要么都执行，要么都不执行

一致性（Consistency）

当事务完成时，数据必须处于一致状态

隔离性（Lsolation）

并发事物之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务

持久性（Durability）

事务完成后，它对数据库的修改被永久保持

1.4 MySQL事务处理

MySQL中支持事务的存储引擎有InnoDB和BDB

方法一：用BEGIN,COMMIT,ROLLBACK实现

方法二：直接用SET改变MySQL的自动提交模式

1.5 使用事务解决银行转账问题

#事务

#方法一：
BEGIN;
UPDATE MYBANK SET BALANCE=BALANCE-500 WHERE CID=4001;
UPDATE MYBANK SET BALANCE=BALANCE+500 WHERE CID=4002;
SELECT * FROM MYBANK;
#COMMIT;
ROLLBACK;
SELECT * FROM MYBANK;

#方法二：
#关闭事务自动提交
SET AUTOCOMMIT = 0;
UPDATE MYBANK SET BALANCE = BALANCE + 500 WHERE CID = 4001;
UPDATE MYBANK SET BALANCE = BALANCE + 500 WHERE CID = 4002;
#手动提交事务
COMMIT;
#回滚事务（撤回）
ROLLBACK;
#开启自动提交
SET AUTOCOMMIT = 1;

二、视图

2.1 什么是视图

视图是一张虚拟表，表示一张表的部分数据或多张表的综合数据。其结构和数据是建立在对表的查询基础上。视图中不存放数据，数据存放在视图所引用的原始表中

一个原始表，根据不同用户的不同需求（权限），可以创建不同的视图

2.2 视图的用途

筛选表中的行
防止未经许可的用户访问敏感数据
将多个物理数据库表抽象为一个逻辑数据库表

2.3 管理视图

使用SQL语句创建视图

使用SQL语句查看视图

使用SQL语句删除视图

#创建一个视图，显示学生的学号、姓名、性别、年级名称、课程名称、考试时间、考试结果

#创建视图
CREATE VIEW VM_EXAMINFO AS 
#查看视图
SELECT S.STUDENTNO,STUDENTNAME,SEX,GRADENAME,SUBJECTNAME,EXAMDATE,STUDENTRESULT
FROM STUDENT S LEFT JOIN GRADE G ON S.GRADEID=G.GRADEID
LEFT JOIN RESULT R ON S.STUDENTNO=R.STUDENTNO
LEFT JOIN `SUBJECT` SU ON SU.SUBJECTNO=R.SUBJECTNO
ORDER BY S.STUDENTNO
#删除视图
DROP VIEW IF EXISTS VM_EXAMINFO;

2.4 使用视图的注意事项

视图中可以使用多个表
一个视图可以嵌套另一个视图
对视图数据进行添加、更新和删除操作会直接影响所引用表中的数据
当视图数据来自多个表时，不允许添加和删除数据

2.5 练习：查看学生各科目考试成绩的平均分

CREATE VIEW VM_AVG_STUDENTRESULT AS
SELECT STUDENTNAME,SUBJECTNAME,AVGRESULT
FROM STUDENT S LEFT JOIN (
	SELECT STUDENTNO,SUBJECTNO,AVG(STUDENTRESULT) AVGRESULT FROM RESULT GROUP BY STUDENTNO,SUBJECTNO
) T ON S.STUDENTNO=T.STUDENTNO
LEFT JOIN `SUBJECT` SU ON SU.SUBJECTNO = T.SUBJECTNO
ORDER BY S.STUDENTNO;