六、MySQL 06 事务、视图、索引、备份和恢复

六、MySQL 事务、视图、索引、备份和恢复

6.1 事务

6.1.1 事务介绍

  • 事务 (Transaction) 将一些列数据操作捆绑成为一个整体统一管理的机制
  • 多个操作作为一个整体向系统提交,要么都执行、要么都不执行
  • 不可分割的工作逻辑单元

6.1.2 为什么需要事务

  • 事务包含了一组操作,这些操作可以是一条SQL语句、一组SQL语句或整个程序。
  • 如果其中一个操作不成功,这些操作都不会执行,前面执行的操作也会回滚原状态,用来保证数据的一致性和完整性。
  • 例如,就像银行转账,张三给李四转账,只有当张三的钱转走了,并且李四账户的钱收到了之后才会事务提交,否则事务会回滚到转账前的状态,保证数据的一致性,保证数据不会出错。

6.1.3 事务的四大特性

事务是最哦为单个逻辑工作单元执行的一系列操作,必须遵循四个特性

原子性、一致性、隔离性、持久性

  • 原子性
    • 事务是一个完整的操作
    • 事务的各步操作是不可分的(原子的)
    • 要么一起执行完毕,要么都不执行
  • 一致性
    • 事务完成前,数据必须处于一致状态
  • 隔离性
    • 一个事务在完成之前对其他事务是不可见的
    • 它不可以任何方式依赖或影响其他事务
  • 持久性
    • 一旦事务提交,不论系统是否故障,事务处理结果都是永久的
    • 事务成功完成之后对于数据库的改变是永久性的

6.1.4 在 MySQL 中使用事务

  • 默认设置下,每个 SQL 语句就是一个事务,即执行 SQL 语句后自动提交

  • 实现事务主要有两种方式

    • 使用 BEGIN 或 START TRANSACTION 开启一个事务
    • 执行 SET autocommit=0 禁止当前会话自动提交

两种实现事务的语法格式如下

1、执行事务的 SQL 语句语法格式

  • 使用 BEGIN 或 START TRANSACTION 开启一个事务
  • 提交事务 语法
#开启事务
BEGIN ; 或 START TRANSACTION;
 #一组作为事务运行的 SQL 语句
 #提交事务
 COMMIT;
  • BEGIN 或 START TRANSACTION 标志着事务的起止点,其后的 SQL 语句不会被提交生效

  • 当运行到 COMMIT 语句后,标志着一个事务被提交成功

  • 自事务开始至提交语句之间执行的所有数据更新将被永久的保存在数据库文件中,并释放连接时占用的资源

  • 回滚(撤销)事务 语法

#开启事务
BEGIN;或 START TRANSACTION;
 #一组作为事务运行的 SQL 语句
 #回滚事务
 ROLLBACK;
  • 事务可以被重新提交,也可能因为执行某些语句不成功或其他异常情况而终止
  • 为保证事务的一致性,要是有 ROLLBACK 语句清除自事务起始点至该语句所做的所有数据更新操作

2、设置自动提交关闭或开启

  • MySQL 设置默认每条 SQL 语句就是一个事务,每执行一个 SQL 语句就会默认提交
  • MySQL 允许修改默认设置 autocommit 的值,及一条 SQL 语句不会被默认提交
  • 设置自动提交关闭或开启的语法
SET autocommit=0|1
  • autocommit 的值为0时,表示关闭自动提交
  • autocommit 的值为1时,表示开启自动提交

6.2 索引

6.2.1 索引简介

  • 使有序的内容在查找时消耗的时间更少,效率更高
  • 索引是对数据表中的一列值或多列值进行排列的一种结构
  • 索引是创建在数据表对象上的,有一个字段或多个字段生产的键组成

6.2.2 索引的分类

  • MySQL 中常用的索引
    • 普通索引(INDEX)
    • 唯一索引(UNIQUE)
    • 主键索引(PRIMARY KEY)
    • 全文索引(FULLTEXT)
  • 普通索引
    • MySQL 中的基本索引类型
    • 该类索引对索引的数据没有任何限制
    • 允许定义重复值或空值
    • 唯一任务是加快对数据的访问速度
  • 唯一索引
    • 唯一索引不允许出现两行具有相同的索引值
    • 现有数据中如果有重复的键值,则大多数数据库不允许创建唯一索引
  • 主键索引
    • 一种特殊的唯一索引
    • 不允许有空值
    • 创建数据表时,如果指定了主键,则会自动创建主键索引
  • 全文索引
    • 全文索引的作用是在定义索引的列上支持值的全文查找
    • 允许全文索引列中有重复值和空值
    • 全文索引只能在 CHAR、VARCHAR、TEXT 类型的列上创建

6.2.3 在 MySQL 中使用索引

1、创建索引

  • 使用 CREATE INDEX 可以在已经存在的表上添加索引
  • 添加索引语法
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名称 ON 表名(列名)
  • UNIQUE、FULLTEXT、SPATIAL:分别表示唯一索引、全文索引、空间索引,为可选参数

2、查看索引

  • 使用 SHOW INDEX 关键字可以查看已创建的索引
  • 查看索引 语法
SHOW INDEX FORM 表名

3、删除索引

  • 删除索引 语法
DROP INDEX 索引名 ON 表名
  • 删除表时,该列的所有索引将同时被删除
  • 删除表中的列时,若要删除的列为索引的组成部分,则该列也会从索引中被删除
  • 如果组成索引的所有列被删除,则整个索引被删除

6.3 视图

6.3.1 需要视图的原因

  • 不同身份的用户所查询的数据范围不同,只将用户需要的某些列展示给用户,不展示多余的列
  • 使用视图,可以定制用户数据,聚焦特定的数据
  • 也可以借助视图,对代码进行封装保存

6.3.2 视图的概念

1、视图是一张虚拟表

  • 表示一张表的部分数据或多张表的综合数据
  • 其结构和数据是建立在对表的查询基础上

2、视图中不存放数据

  • 数据存放在视图所引用的原始表中

3、一个原始表,根据不同用户的不同需求,可以创建不同的视图

6.3.3 视图的好处

1、给开发人员带来好处

  • 限制数据检索更容易
  • 维护应用程序更方便

2、给数据库的最终用户带来的好处

  • 结果更容易理解
  • 获得数据更容易

6.3.4 在 MySQL 中使用视图

1、创建视图

  • 创建视图 语法
CREATE VIEW 视图名 AS <SELECT 语句>;
  • 视图名一般以 view_xxxv_xxx 来命名

2、查询视图中的数据

  • 查询已创建的视图中的数据 语法
SELECT * FROM 视图名

3、删除视图

  • 与数据表一样,若将要创建的视图在数据库中已经存在,则先删除视图才能创建成功
  • 删除视图 语法
DROP VIEW [IF EXISTS] 视图名;

4、使用视图的注意事项

  • 每个视图可以使用多张表
  • 与查询相识,一个视图可以嵌套另一个视图,但尽量不要超过 3 层
  • 对视图数据进行添加、更新、删除操作实际上是直接操作引用表中的数据

6.4 数据库备份和恢复

6.4.1 数据库备份

1、使用 mysqldump 备份数据库

  • 通过 mysqldump 命令可以将指定的数据库和数据表导出为 SQL 脚本
  • 使用 mysqldump 备份数据库是在命令行中实现的 命令如下
mysqldump [options] database [table1.table2,...] > [path]/filename.sql
  • options:连接数据库的参数,主要内容如下
    • -u username:用户名
    • -h host:登录用户的主机名称。若为本机,则省略
    • -p password:登录密码
  • database:需要备份的数据库
  • table:需要备份的数据表,可指定多张表。若备份整个数据库,则省略
  • filename.sql:备份文件的名称
  • mysqldump 是 DOS 命令,无需进入 mysql 命令行

2、使用 Navicat 备份数据库

  • 使用 Navicat 也可以导出数据库的备份脚本
  • 选择要备份的数据库,右键之后选择 “转储 SQL 文件” --> “结构和数据…” 命令 即可导出

6.4.2 数据库恢复

1、使用 MySQL 命令导入数据

  • 使用导出的 SQL 备份脚本,在需要恢复时通过 MySQL 命令对其进行导入操作
  • 导入SQL 备份脚本 命令如下
mysql -u username -p dbname < filename.sql
  • username:登录数据库系统的用户名
  • dbname:导入目标数据库的数据库名
  • filename.sql:数据库备份后的文件地址

2、使用 source 命令恢复数据

  • 可以在已连接数据库的状态下导入数据
  • 使用 source 命令 语法如下
source filename;
  • filename 为 SQL备份后的文件地址

3、使用 Navicat 的数据导入功能导入数据

  • 也可以使用 Navicat 直接导入数据
  • 右键选择要导入数据的数据库,执行 “运行 SQL 文件…” 命令,之后在打开的导入窗口,选择要运行的 SQL 文件,单机 “开始”,即可开始导入数据
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
好的,我为您提供一个简单的教务系统案例,包括数据库设计、表设计、数据操纵、视图索引、存储过程、存储函数、触发器、事件、事务数据库用户及权限分配、恢复备份。 1. 数据库设计 教务系统的数据库设计包括以下几个表: - 学生表(student):存储学生的姓名、学号、性别、出生日期、班级等信息。 - 教师表(teacher):存储教师的姓名、工号、性别、职称等信息。 - 课程表(course):存储课程的名称、课程编号、学分、教师编号等信息。 - 成绩表(score):存储学生的成绩信息,包括学生编号、课程编号、成绩等信息。 - 班级表(class):存储班级的名称、班级编号、专业等信息。 2. 表设计 根据上述设计,我们可以创建以下表: - 学生表 ``` CREATE TABLE student ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, gender ENUM('男', '女'), birthday DATE, class_id INT UNSIGNED NOT NULL, FOREIGN KEY (class_id) REFERENCES class(id) ); ``` - 教师表 ``` CREATE TABLE teacher ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, gender ENUM('男', '女'), title VARCHAR(20) ); ``` - 课程表 ``` CREATE TABLE course ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, credit FLOAT UNSIGNED, teacher_id INT UNSIGNED NOT NULL, FOREIGN KEY (teacher_id) REFERENCES teacher(id) ); ``` - 成绩表 ``` CREATE TABLE score ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, student_id INT UNSIGNED NOT NULL, course_id INT UNSIGNED NOT NULL, score FLOAT UNSIGNED, FOREIGN KEY (student_id) REFERENCES student(id), FOREIGN KEY (course_id) REFERENCES course(id) ); ``` - 班级表 ``` CREATE TABLE class ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, major VARCHAR(50) ); ``` 3. 数据操纵 插入数据: ``` INSERT INTO student (name, gender, birthday, class_id) VALUES ('张三', '男', '1999-01-01', 1); INSERT INTO teacher (name, gender, title) VALUES ('李四', '男', '教授'); INSERT INTO course (name, credit, teacher_id) VALUES ('数据库原理', 3.0, 1); INSERT INTO score (student_id, course_id, score) VALUES (1, 1, 90.0); INSERT INTO class (name, major) VALUES ('计算机科学与技术1班', '计算机科学与技术'); ``` 查询数据: ``` -- 查询学生 SELECT * FROM student; -- 查询教师 SELECT * FROM teacher; -- 查询课程 SELECT * FROM course; -- 查询成绩 SELECT * FROM score; -- 查询班级 SELECT * FROM class; ``` 更新数据: ``` -- 更新学生信息 UPDATE student SET name = '李四', gender = '女' WHERE id = 1; -- 更新教师信息 UPDATE teacher SET name = '王五' WHERE id = 1; -- 更新课程信息 UPDATE course SET credit = 4.0 WHERE id = 1; -- 更新成绩信息 UPDATE score SET score = 80.0 WHERE id = 1; -- 更新班级信息 UPDATE class SET name = '计算机科学与技术2班' WHERE id = 1; ``` 删除数据: ``` -- 删除学生 DELETE FROM student WHERE id = 1; -- 删除教师 DELETE FROM teacher WHERE id = 1; -- 删除课程 DELETE FROM course WHERE id = 1; -- 删除成绩 DELETE FROM score WHERE id = 1; -- 删除班级 DELETE FROM class WHERE id = 1; ``` 4. 视图 创建视图: ``` CREATE VIEW student_view AS SELECT s.id, s.name, s.gender, s.birthday, c.name AS class_name FROM student s JOIN class c ON s.class_id = c.id; ``` 查询视图: ``` SELECT * FROM student_view; ``` 5. 索引 为表创建索引: ``` CREATE INDEX idx_student_name ON student (name); ``` 删除索引: ``` DROP INDEX idx_student_name ON student; ``` 6. 存储过程 创建存储过程: ``` DELIMITER // CREATE PROCEDURE get_student(IN student_id INT) BEGIN SELECT * FROM student WHERE id = student_id; END // DELIMITER ; ``` 调用存储过程: ``` CALL get_student(1); ``` 7. 存储函数 创建存储函数: ``` DELIMITER // CREATE FUNCTION get_student_name(IN student_id INT) RETURNS VARCHAR(20) BEGIN DECLARE student_name VARCHAR(20); SELECT name INTO student_name FROM student WHERE id = student_id; RETURN student_name; END // DELIMITER ; ``` 调用存储函数: ``` SELECT get_student_name(1); ``` 8. 触发器 创建触发器: ``` CREATE TRIGGER update_student_score AFTER UPDATE ON score FOR EACH ROW BEGIN UPDATE student SET score = NEW.score WHERE id = NEW.student_id; END; ``` 删除触发器: ``` DROP TRIGGER update_student_score; ``` 9. 事件 创建事件: ``` CREATE EVENT backup_database ON SCHEDULE EVERY 1 WEEK DO BEGIN -- 备份数据库 mysqldump -u root -p mydatabase > mydatabase_backup.sql; END; ``` 删除事件: ``` DROP EVENT backup_database; ``` 10. 事务 开启事务: ``` START TRANSACTION; ``` 提交事务: ``` COMMIT; ``` 回滚事务: ``` ROLLBACK; ``` 11. 数据库用户及权限分配 创建用户: ``` CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword'; ``` 授权: ``` GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase.* TO 'myuser'@'localhost'; ``` 撤销权限: ``` REVOKE SELECT, INSERT, UPDATE, DELETE ON mydatabase.* FROM 'myuser'@'localhost'; ``` 删除用户: ``` DROP USER 'myuser'@'localhost'; ``` 12. 恢复备份 备份数据库: ``` mysqldump -u root -p mydatabase > mydatabase_backup.sql; ``` 恢复数据库: ``` mysql -u root -p mydatabase < mydatabase_backup.sql; ``` 以上是一个简单的教务系统案例,包括数据库设计、表设计、数据操纵、视图索引、存储过程、存储函数、触发器、事件、事务数据库用户及权限分配、恢复备份

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值