六、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_xxx 或 v_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 文件,单机 “开始”,即可开始导入数据