事务，视图，索引，备份和恢复

事务（Transaction）

事务是指将一系列数据操作捆绑成为一个整体进行统一管理，如果某一事务执行成功，则在该事务中进行的所有事务更改均会提交成为是数据库中的永久组成部分。如果事务提交时遇到错误切必须取消或者回滚，则数据将全部恢复到操作前的状态，所有的数据更改均被清除

什么的事务？

• 事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作
• 事务是一种机制，一个操作序列，包含一组数据库操作命令，并且把所有的命令作为一个整体
• 多个操作作为一个整体向系统提交，要么都执行、要么都不执行
• 事务是一个不可分割的工作逻辑单元 。一个逻辑工作单元必须有四个属性，即原子性（Atomicity），一致性（Consistency），隔离性（Isolation），及持久性（Durability）这些事务的特性通常简称为ACID。

1. 原子性
   事务是一个完整的操作，事务的各步操作是不可分的(原子的)，要么都执行，要么都不执行

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

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

4. 持久性
   事务完成后,它对数据库的修改被永久保持

1.执行事务的语法

（1） 开事事务

BEGIN ;
或
START TRANSACTION;

标志事务的一个起始点
（2） 提交事务

COMMIT ;

标志事务是否成功提交，提交后所进行的数据将永久保存到数据库中
（3）回滚（撤销）事务

ROLLBACK ;

将数据状态回滚到事务开始前

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

MySql中默认开启自动提交模式 ，默每句SQL语句都是一个事务，执行SQL语句后自动提交，可以执行SETAUTOCOMMIT=0禁止当前会话自动提交，后面的语句作为事务的开始, 需使用COMMIT或ROLLBACK语句结束该事务。SETAUTOCOMMIT=1开启自动提交。

语法：

关闭/开启自动提交状态
SET autocommit = 0|1;
值为0：关闭自动提交
值为1：开启自动提交

什么是视图?

• 视图是一张虚拟表
• 表示结构和数据是建立在对表的查询基础上
• 视图中不存放数据
• 一张表的部分数据或多张表的综合数据
• 其数据存放在视图所引用的原始表中
• 一个原始表，根据不同用户的不同需求，可以创建不同的视图

为什么需要视图？

• 数据库管理系统提供的视图这类数据库对象，可以提高表操作的安全性。

使用视图的原因：

1. 出于安全考虑，用户不必看到整个数据库表的结构，而隐藏部分数
2. 符合用户日常业务逻辑，使他们更容易理解数据

视图通常用来进行三种查操作？

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

对用户的好处

（1） 结果更容易理解
（2） 获得数据更容易

对开发人员的好处

（1） 限制数据检索更容易
（2） 维护应用程序更方便

创建使用视图

1. 创建视图

CREATE VIEW view_name  
   AS
  <SELECT 语句>;

2. 使用SQL语句删除视图

DROP VIEW [IF EXISTS] view_name;

3. 使用SQL语句查看视图

SELECT 字段1, 字段2, …… FROM view_name;

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

5. 查看所有视图

USE information_schema;
SELECT * FROM views\G;

经验: 使用视图修改数据会有许多限制，一般在实际开发中视图仅用作查询

什么是索引

• 索引是一种有效组合数据的方式，为快速查找到指定记录

作用

• 大大提高数据库的检索速度
• 改善数据库性能

MySQL索引按存储类型分类

• B-树索引：InnoDB、MyISAM均支持
• 哈希索引
• 常用索引分类

1. 普通索引
   基本索引类型
   允许在定义索引的列中插入重复值和空值

2. 唯一索引
   索引列数据不重复
   允许有空值

3. 主键索引
   主键列中的每个值是非空、唯一的
   一个主键将自动创建主键索引

4. 复合索引
   将多个列组合作为索引

5. 全文索引
   支持值的全文查找
   允许重复值和空值

6. 空间索引
   对空间数据类型的列建立的索引

创建索引

语法：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
       ON table_name (column_name[length]…);

删除索引
语法：

	DROP  INDEX index_name ON table_name;

经验：删除表时，该表的所有索引同时会被删除

创建索引原则

• 按照下列标准选择建立索引的列
• 频繁搜索的列
• 经常用作查询选择的列
• 经常排序、分组的列
• 经常用作连接的列（主键/外键）

请不要使用下面的列创建索引

• 仅包含几个不同值的列
• 表中仅包含几行

使用索引注意

• 查询时减少使用*返回全部列，不要返回不需要的列
• 索引应该尽量小，在字节数小的列上建立索引
• WHERE子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前
• 避免在ORDER BY子句中使用表达式

查看索引

SHOW INDEX FROM table_name;

查询索引的参数

• Table：创建索引的表
• Non_unique：索引是否非唯一
• Key_name：索引的名称
• Column_name：定义索引的列字段
• Seq_in_index：该列在索引中的位置
• Null：该列是否能为空值
• Index_type：索引类型

数据库备份和恢复

使用mysqldump命令备份数据库

• mysqldump命令——MySQL一个常用的备份工具将CREATE和INSERT INTO语句保存到文本文件。
• 属于DOS命令

语法：

Mysqldump -u username -h host -ppassword dbname [tbname1[tbname2]]>filename.sql

其中：

• username表示用户名
• hsot登录主机名称
• password登录密码
• dbname备份的数据库名称
• tbname需要备份的数据库，可指定多张表，为可选项，如备份整个数据库，省略此项
• filename.sql备份文件的名称

注意：mysqldump是DOS系统下的命令在使用时无须进入mysql命令行，否则将无法执行

Mysqldump的常用参数

Mysqldump - - help查看所有参数

第一种方法 使用mysqldump恢复数据库

mysql –u username –p [dbname] < filename.sql

• username表是用户名
• dbname表示数据库名
• ilename.sql备份后的文件

注意：

1. mysql为DOS命令
2. 在执行该语句之前，必须在MySQL服务器中创建新数据库，如果不存在恢复数据库过程将会出错。

第二中方法 使用source命令恢复数据库

source filename;

• 登录MySQL服务后使用
• 执行该命令前，先创建并选择恢复后的目标数据库

第三中方法 表数据导入文本文件

导出/导入数据——可实现数据库服务器间移动数据

SELECT  *  FROM tablename 
         [WHERE contion]
         INTO OUTFILE 'filename' [OPTION]

第四种方法 文本文件导入数据库

LOAD DATA INFILE filename INTO TABLE tablename [OPTION]

• filename文本文件的路径特征
• tablename导入表的名称

总结