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

事务

事务是一种机制，一个操作序列，它包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求。要么都执行，要么都不执行。事务是作为最小的控制单元使用的，特别使用与多用户同时操作的数据库系统。

那么为什么需要事务呢？

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

事务的属性：

事务是作为单个工作逻辑单元执行的一系列操作，一个逻辑工作单元必须要有四个属性：

原子性（Atomicity）。事务是一个完整的操作，事务的各个元素是不可分的（原子性）。

一致性（consistency）。当事务完成时，数据必须处于一致状态。

隔离性（lsolation）。对数据库修改的所有并发事务是彼此隔离的。

持久性（durability）。事务的持久性是指不管系统是否发生了故障，事务处理的结果都是永久的。

那么是如何执行事务的呢？

MySQL提供了多种存储引擎支持事务，支持事务的存储引擎有InnoDB和BDB。InnoDB存储引擎事务主要时通过UNDO日志和REDO日志，MyISAM存储引擎不支持事务。UNDO日志：复制事务执行前的数据，用于事务在发生异常时回滚数据。REDO日志：记录事务在执行中，每条对数据库进行更新的操作，当事务被提交时，该内容将被刷新到磁盘。

执行事务的语法：

开始事务：begin;或者start  transaction;

 /*开始事务*/向成绩表插入一批学生成绩

BEGIN;

INSERT INTO `result` VALUES

(100000,2,NOW(),60),

(100001,1,NOW(),99),

(100002,2,NOW(),80);

SELECT * FROM `result`;#如果用SQLyog会显示已经在表里面更新了数据,实际上数据库并没有更新数据

提交事务：commit;

COMMIT;#提交

SELECT * FROM `result`;#提交过后数据库里面会更新数据

回滚（撤销）事务：rollback;

BEGIN;

INSERT INTO `result` VALUES

(100003,2,NOW(),44),

(100004,1,NOW(),77);

ROLLBACK;#回滚

SELECT * FROM `result`;#执行回滚后查询数据没有任何变化

使用事务所要遵守的原则：

事务尽可能简短；事务中访问的数据量尽量最少；查询数据时尽量不要使用事务；在事务处理过程中尽量不要出现等待用户输入的操作。

视图

视图是另一种查看数据库中一个或多个表中的数据的方法，视图是一种虚拟表，通常作为一个或多个表的行或列的子集创建的。

那么为什么需要视图呢？

在实际工作中，不同身份的用户所关注的数据库数据可能不同，不同职位的人对数据库中查询的数据范围权限也不同。数据库管理系统提供的视图这类数据库对象，可以把重复使用的复杂的查询结果保存成视图，也可以设定不同访问范围的视图，不同岗位的员工根据自己的权限来查看相关的数据，以保证数据访问的安全性。

视图通常用来进行的四项操作：

筛选表中的行

防止未经许可的用户访问敏感的数据表

将多个物理数据表抽象为一个逻辑数据表

降低数据的复杂程度

视图对用户的好处：

结果更加容易理解。

获得数据更加容易。

视图对开发人员的好处：

限制数据检索更容易。可以有效的隐藏某些敏感数据。

维护应用程序更方便。调试视图比调试查询更容易。

视图的使用

使用SQL语句创建视图：

语法：CREATE  VIEW  视图名   AS  <SELECT  语句>;

在SQL语句命名规范中，视图一般是以view_xxx或v_xxx的样式来命名的。

使用SQL语句删除视图：

 

语法： DROP VIEW [IF EXISTS] 视图名；

 

使用SQL语句查看视图数据：

语法：SELECT 字段1，字段2…..FROM view_name(视图名)；

使用视图的注意事项：

 每个视图中可以使用多个表。

 与查询相似，一个视图可以嵌套另一个视图，最好不要超过三层。

 对视图数据进行添加，更新和删除操作直接引用表中的数据。

当视图数据来自多个表时，不允许添加和删除数据，会影响表中数据的完整性。

使用视图特别注意事项：

使用视图修改数据会有许多限制和逻辑关系，一般在实际开发中视图仅用作查询。

查看所有视图的语法：SELECT * FROM views\G;

索引

索引是一种有效组合数据的方式，可以快速查找到指定的记录，类似与书籍中目录。

索引的作用：

大大提高数据库的检索速度。

改善数据库的性能。

索引分类：

索引的存储类型可以分为B-树索引（BTREE）和哈希表（HASH）。InnoDB和MyISAM存储引擎支持B-树索引。在MySQL中，常用的索引有：普通索引、唯一索引、主键索引、复合索引、全文索引、空间索引。

创建索引:

语法：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX  index_name

ON  table_name (column_name[length]…………..);

其中：UNIQUE 表示唯一索引，FULLTEXT 表示全文索引， SPATIAL表示空间索引，

为可选参数。

Index_name:表示索引名；

Table_name:表示创建索引的表名；

Column_name:指定需要创建索引的列；

Length:指定索引的长度，可选参数，只有字符串类型才能指定索引的长度。

删除索引:

语法：

DROP  INDEX  index_name  ON  table_name;

查看索引:

语法：

SHOW  INDEX  FROM  table_name;

使用索引的原则:

因为索引自身也需要维护，并占用一定的资源，因此没有必要为每个列都添加索引，

如下条件可添加索引：

频繁搜索的列；

经常用作查询的列；

经常排序，分组的列；

经常用作连接的列（主键、外键）。

请不要给下面的列创建索引：

仅包含几个不同值的列;

包中仅包含几行;

为小型表创建索引不太使用，因为索引中搜索数据所花的事件比表中逐行搜索花的时

间更长。

MySQL的备份和恢复

备份数据库：

语法：在DOS环境下运行

Mysqldump  –u username  –h  host  -ppassword

Dbname[tbname1,tabnem2….]>filename.sql

例如：

/*导出数据*/

mysqldump -u root -proot;

myschool student result>d:\bf\myschool.sql;   

恢复数据库：

语法：mysql  –u  username  -p [dbname]<filename.sql;在DOS环境下运行

或： source filename;

例如：

/*导入数据*/

CREATE DATABASE `newMySchool`;#被导入的数据库必须存在

mysql -u root -proot newMySchool<d:\bf\myschool.sql;

数据表导出到文本文件：

语法：SELECT  columnlist  FROM  tablename

                   [WHERE contion ]

                   INTO  OUTFILE  `filename`[OPTION];

例如：

#导出学生表的记录并存储为.txt文件

SELECT * FROM `student`

INTO OUTFILE `d:/beifen/student.txt`;

文本文件导入到数据表：

语法：LOAD  DATA  INFILE  filename  INTO  TABLE  tablename[OPTION];

例如：

DELETE FROM `student`;#删除学生表中的数据保证表是空的

LOAD DATA INFILE `d:/beifen/student.txt`INTO TABLE `student`;