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

什么是事务
事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作
多个操作作为一个整体向系统提交，要么都执行、要么都不执行 
事务是一个不可分割的工作逻辑单元 

转账过程就是一个整体
它需要两条UPDATE语句来完成，这两条语句是一个整体
如果其中任一条出现错误，则整个转账业务也应取消，两个账户中的余额应恢复到原来的数据，从而确保转账前和转账后的余额不变，即都是1001元

事务的特性
事务必须具备以下四个属性，简称ACID 属性
原子性（Atomicity）
    事务是一个完整的操作，事务的各步操作是不可分的(原子的)，要么都执行，要么都不执行
一致性（Consistency）
    当事务完成时，数据必须处于一致状态
隔离性（Isolation）
    并发事务之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务
持久性（Durability）
    事务完成后,它对数据库的修改被永久保持

如何创建事务
MySQL中支持事务的存储引擎有InnoDB和BDB

开始事务
BEGIN ;
或
START TRANSACTION;
提交事务
COMMIT ;
回滚（撤销）事务
ROLLBACK ;

自动关闭和开启事务
默认情况下，每条单独的SQL语句视为一个事务
关闭默认提交状态后，可手动开启、关闭事务

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

关闭自动提交后，从下一条SQL语句开始则开启新事务，需使用COMMIT或ROLLBACK语句结束该事务

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

视图的用途
    筛选表中的行
    防止未经许可的用户访问敏感数据
    降低数据库的复杂程度
    将多个物理数据库抽象为一个逻辑数据库

如何创建视图
使用SQL语句创建视图
CREATE VIEW view_name  
   AS
    <SELECT 语句>;

使用SQL语句删除视图
DROP VIEW [IF EXISTS] view_name;

使用SQL语句查看视图
SELECT 字段1, 字段2, …… FROM view_name;

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

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

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

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

MySQL索引按存储类型分类
    B-树索引：InnoDB、MyISAM均支持
    哈希索引

常用索引类型

普通索引    基本索引类型    允许在定义索引的列中插入重复值和空值
唯一索引    索引列数据不重复   允许有空值
主键索引    主键列中的每个值是非空、唯一的一个主键将自动创建主键索引
复合索引    将多个列组合作为索引
全文索引    支持值的全文查找允许重复值和空值
空间索引    对空间数据类型的列建立的索引

如何创建/删除索引
创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
       ON table_name (column_name[length]…);

删除索引
DROP  INDEX index_name ON table_name;
删除表时，该表的所有索引同时会被删除 

在student表的studentName列创建普通索引
USE myschool;
CREATE  INDEX `index_student_studentName`
   ON `student`(`studentName`);

创建索引的指导原则
按照下列标准选择建立索引的列
    频繁搜索的列
    经常用作查询选择的列
    经常排序、分组的列
    经常用作连接的列（主键/外键）
请不要使用下面的列创建索引
    仅包含几个不同值的列
    表中仅包含几行

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

查看索引 
SHOW INDEX FROM table_name;

查看myschool数据库中全部索引信息 
USE myschool;
SHOW INDEX FROM `student`\G;

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

为什么进行数据库备份  
数据库故障
突然断电
病毒入侵
误操作导致数据丢失

使用mysqldump命令备份数据库
mysqldump命令——MySQL一个常用的备份工具
将CREATE和INSERT INTO语句保存到文本文件
属于DOS命令
    选项参数    
mysqldump [options] –u username –h host –ppassword 
dbname[tbname1[,tbname2……]]>filename.sql
需备份的数据库名

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

使用root账户登录到MySQL服务器，备份myschool数据库下的student表
mysqldump –u root –p myschool student > d:\backup\myschool_20160808.sql

为保证账户密码安全，命令中可不写密码，但参数“-p”必须有，回车后根据提示写密码

常用参数选项
-add-drop-table            在每个CREATE TABLE语句前添加DROP TABLE语句，默认是打开的，可以用-skip-add-drop-table来取消
--add-locks            该选项会在INSERT 语句中捆绑一个LOCK TABLE 和UNLOCK TABLE 语句
                好处：防止记录被再次导入时，其他用户对表进行的操作
-t或-no-create-info        只导出数据，而不添加CREATE TABLE语句
-c或--complete-insert        在每个INSERT语句的列上加上列名，在数据导入另一个数据库时有用
-d或--no-data            不写表的任何行信息，只转储表的结构

备份文件包含的主要信息
    备份后文件包含信息MySQL及mysqldump工具版本号
    备份账户的名称
    主机信息
    备份的数据库名称
    SQL语句注释和服务器相关注释
    CREATE和INSERT语句

恢复数据库
方法一：使用mysql命令恢复数据库
mysql –u username –p [dbname] < filename.sql

使用备份文件将myschool数据库中student表信息恢复到schoolDB数据库中
mysql –u root –p schoolDB < d:\backup\myschool_20160808.sql

方法二：使用source命令恢复数据库
source filename;

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

CREATE DATABASE myschoolDB2;  #创建数据库
USE myschoolDB2;  #选择数据库
source d:\backup\myschool_20160808.sql; #恢复数据库

表数据导出到文本文件
导出/导入数据——可实现数据库服务器间移动数据

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

将成绩表中“Logic Java”课程的成绩信息导出到文本文件
SELECT * FROM `result`
WHERE `subjectNo` = 
(SELECT `subjectNo` FROM `subject` 
WHERE `subjectName` = 'Logic Java')
INTO OUTFILE 'd:/backup/result_Java.txt';

文本文件导入到数据表
 LOAD DATA INFILE filename INTO TABLE tablename [OPTION]

将数据从文本文件导入到myschoolDB的result表
LOAD DATA INFILE 'd:/back/result_Java.txt' INTO TABLE result;