JAVA / MySql 编程——第五章 事务、视图、索引、备份和恢复
1、事务（Transaction）：
|
事务是将一系列数据操作绑成一个整体进行统一管理。 如果一事务执行成功，则咋子该事务中进行的所有数据更改均会提交，称为数据库中的永久成部分。 如果事务执行是遇到错误且必须取消或回滚，则数据将全部恢复到操作前的状态，所有数据的更改均被清除。 |
|
定义： 事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所欲的命令作为一个整体起向系统提交侧小操作请求，即这一组数据库命令要么都执行，要么都不执行。 Eg： 转账过程就是一个整体 它需要两条UPDATE语句来完成，这两条语句是一个整体 如果其中任一条出现错误，则整个转账业务也应取消，两个账户中的余额应恢复到原来的数据，从而确保转账前和转账后的余额不变，即都是1001元 事务是作为单一逻辑工作单元执行一系列操作。一个逻辑工作单位必须有四个属性， 即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability），这些特性通常简称为ACID。 ●原子性：事务是一个完整的操作，事务的各步操作（各元素）是不可分的(原子的)，要么都执行，要么都不执行 ●一致性：当事务完成时，数据必须处于一致状态。 ●隔离性：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，他不应以任何方式依赖于或影响其他事务。 ● 持久性：事务的持久性指不管系统是否发生了故障，事务处理的结果都是永久的。 |
2、执行事务：
|
默认设置下，枚举SQL语句就是一个事务，即执行SQL语句后自动提交，为了达到将几个操作作为一个整体的目的， 需要使用BEGIN或START TRANSACTION开启一个事务，或者执行命令SETAUTOCOMMIT = 0， 来禁止当前回话的自动提交后，后面的语句作为事务的开始。 |
|
MySQL中支持事务的存储引擎有InnoDB和BDB 执行事务的语法： SQL使用下列语句来管理事务。 （1）开始事务 语法 BEGIN; //开始的意思 或者 START TRANSACTION; 这个语句显式地标记一个事务的起始点。 （2）提交事务 语法 COMMIT; 这个语句标志一个事务成功提交。 自事务开始至提交语句之间执行的所有数据更新将永久地保存在数据库数据文件中，并释放连接是占用的资源。 （3）回滚(撤销)事务 语法 ROLLBACK; 清除自事务起始点至该语句所做的所有数据更新操作，将数据状态回滚到事务开始前，并释放由事务控制的资源。 |
|
设置值自动提交关闭或开始： MySQL中默认开启自动提交模式，即为指定开启事务时， 每条SQL语句都是单独的事务执行完毕自动提交，可以关闭自动提交模式，手动提交或回滚事务。 语法 SET autocommit = 0 | 1; 值为0：关闭自动提交。 值为1：开启自动提交。 当执行SET autocommit = 0后，即关闭自动提交， 从下一条SQL语句开始则开启新事务，需使用COMMIT或ROLLBACK语句结束该事务。 |
|
编写事务的原则： 事务尽可能简短； 事务中访问的数据量尽量最少； 查询数据时尽量不要使用事务； 在事务处理过程中尽量不要出现等待用户输入的操作； |
3、视图：
|
视图是保存在数据库中的SELECT查询。因此，对查询执行的大多数操作饿可在视图上进行。 使用视图的原因有两点：一个是处于安全考虑，用户不必看到整个数据库的结构，而隐藏部分数据;另一个是符合用户日常业务逻辑，使他们更容易理解数据。 定义： 视图是另一种查看数据库中一个或多个表中数据的方法。 视图是一种虚拟表，通常是作为来自一个或多个表的行或列的子集创建的。当然，它也可以包含全部的行和列。 但是，视图并不是数据库中存储的数值的集合，它的行和列来自查询中引用的表。在执行是，他直接显示来自于表中的数据。 |
|
视图充当着查询中指定表筛选器。定义视图的查询可以基于一个或多个表，也可以基于其他视图、当前数据库或其他数据库。 n 视图是一张虚拟表 u 表示一张表的部分数据或多张表的综合数据 u 其结构和数据是建立在对表的查询基础上 n 视图中不存放数据 u 数据存放在视图所引用的原始表中 n 一个原始表，根据不同用户的不同需求，可以创建不同的视图 |
|
视图通常用来进行一下三种操作： （1）筛选表中的行。 （2）防止为经许可的用户访问敏感数据。 （3）将多个物理数据表抽象为一个逻辑数据表。 （4）降低数据库的复杂程度 |
|
对最终用户的好处： （1）结果更容易理解。创建视图是，可以将列名改为有意义的名称，使用户更容易理解列所有表的内容。在视图中修改名不会影响基表的列名。 （2）获得数据更容易。很多人对SQL不太了解，因此对他们来说，创建对多个表的复杂查询很困难，可以通过创建视图来方便用户访问多个表中的数据。 |
|
对开发人员的好处： （1）限制数据检索更容易。开发人员于是需要隐藏某些行或列中的信息。通过使用视图，用户可以灵活地访问他们的数据，同时保证同一个表或其他表中的其他数据的安全性。要实现这一个目标，可以在创建视图时将对用户保密的列排出在外。 （2）维护应用程序更方便。调试视图表调试查询更容易，跟踪视图中各个步骤的错误更为容易，这是因为所有的步骤都是视图的组成部分。 |
4、创建和使用视图：
|
使用SQL语句创建视图 语法： CREATE VIEW 视图名 AS <SELECT语句> 注：在SQL语句名命规范中，视图一般以view_xxx或v_xxx的样式来命名； |
|
与创建数据表相同，在创建视图之前，如果数据库中已存在同名视图，需要先删除在创建。 使用SQL语句删除视图 语法 DROP VIEW [IF EXISTE] 视图名; |
|
使用SQL语句看视图数据 语法 SELECT 字段1，字段2，…… FROM view_name; 使用查询语句SELECT执行视图的SQL代码，可获得数据结果集。 |
|
查看所有视图 USE information_schema; SELECT * FROM views\G; |
|
使用视图的注意事项 （1）每个视图中可以使用多个表。 （2）与查询相似，一个视图可以嵌套另一个视图，但最好不要超过三层。 （3）对视图数据进行添加、更新和删除操作直接引用表中的数据。 （4）当视图数据来自多个表时，不允许添加和删除数据。 注：使用视图修改数据会有许多限制，一般在实际开发中视图仅用作查询 |
5、索引：
|
索引提供指针有存储在表中指定列的数据值，在根据指定的排序列这些指针。 数据库使用索引的方式与使用书的目录很相似；通过搜索索引找到特定的值，在跟随指针到包含该值的行。 |
|
索引是一种有效组合数据的方式，为快速查找到指定记录 作用: 大大提高数据库的检索速度 改善数据库性能 MySQL索引按存储类型分类 B-树索引：InnoDB、MyISAM均支持 哈希索引 |
|
MySQL中，常用的索引有以下六类。 1. 普通索引：普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。 2.唯一索引：唯一索引不允许两行具有相同的索引值。如果现有数据中存在重复的键值，则一般情况下多数数据库不允许创建唯一索引。 允许有空值 3.主键索引：在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。 主键列中的每个值是非空、唯一的 4.复合索引：在创建索引是，并不是只能对其他一列创建索引，与创建主键一样，可以多个列组合作为索引，这种索引称为符合索引。 5.全文索引：全文索引的作用是在定义索引的列上支持值的全文查找，允许这些索引列中插入重复的空值和重复值。 6.空间索引：空间索引是对空间数据类型的列建立的索引，如GEOMETRY、POINT等。 |
6、使用索引：
|
创建索引：使用CREATE INDEX语句可以在以经存在的表上添加索引： 语法 CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (column_name[length]……) 其中： （1）UNIQUE | FULLTEXT | SPATIAL：分别表示唯一索引、全文索引的空间索引，为可选参数。 （2）index_name：指定索引名。 （3）column_name：指定需要创建索引的列。 （4）length：指定索引长度，可选参数，只有字符创类型才能指定索引长度。 (5) table_name：指定创建索引的表名。 |
|
|
删除索引： 语法 DROP INDEX index_name ON table_name; 注：删除表时，该表的所有索引将同时被删除。 删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。 |
|
|
n 按照下列标准选择建立索引的列 u 频繁搜索的列 u 经常用作查询选择的列 u 经常排序、分组的列 u 经常用作连接的列（主键/外键） |
n 不要使用下面的列创建索引 u 仅包含几个不同值的列 u 表中仅包含几行 |
|
使用索引时注意事项： n 查询时减少使用*返回全部列，不要返回不需要的列 n 索引应该尽量小，在字节数小的列上建立索引 n WHERE子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前 n 避免在ORDER BY子句中使用表达式 n 根据业务数据发生的频率，定期重新生成或重新组织索引，进行碎片整理； |
|
|
查看索引： 语法： SHOW INDEX FROM table_name; |
Eg：查看myschool数据库中全部索引信息 USE myschool; SHOW INDEX FROM `student`\G; |
 |
分析： Table：创建索引的表 Non_unique：索引是否非唯一 Key_name：索引的名称 Column_name：定义索引的列字段 Seq_in_index：该列在索引中的位置 Null：该列是否能为空值 Index_type：索引类型
|
7、数据库的备份和恢复
|
mysqldump是MySQL一个常用的备份命令（属于DOS命令），执行此命令将包含数据的表结构和数据内容转换成相应的CREATE语句和INSERT INTO语句，保存在文本文件中，将来如果需要还原数据，只需执行该文本文件中的SQL语句即可。 |
||||||||||||||
|
mysqldump命令格式 语法 mysqldump -u username -h host -ppassword dbname[ tbname1 [,tbname2……] ] > filename.sql 其中： （1）username表示用户名。 （2）host表示登录用户的主机名称，如本机为主机可省略。 （3）password表示登录密码。 （4）dbname为需要备份的数据库。 （5）tbname为需要备份的数据表，可指定多张表。为可选项，如备份整个数据库则此项省略。 （6）filename.sql表示备份的文件名。 注意：mysqldump是DOS系统下的命令，在使用时无须进入mysql命令行，否则将无法执行 |
||||||||||||||
|
mysqldump的常用参数：
|
||||||||||||||
|
语法： mysqldump –help |
||||||||||||||
|
备份文件包含的主要信息 备份后文件包含信息MySQL及mysqldump工具版本号 备份账户的名称 主机信息 备份的数据库名称 SQL语句注释和服务器相关注释 CREATE和INSERT语句 |
8、使用mysql命令恢复数据库：
|
对于备份数据库后生成的包含有建库、建表、插入数据等SQL语句的文本文件，可以通过mysql命令还远到心的数据库中，实现数据库的恢复。 语法 mysql –u username –p [dbname] < filename.sql 其中： （1）username表示用户名。 （2）dbname表示数据库名字。 （3）filename.sql为数据库备份后的文件。 注：1. mysql为DOS命令 2.在执行该语句之前，必须在MySQL服务器中创建新数据库，如果不存在恢复数据库过程将会出错 |
|
mysql命令是在DOS环境下的恢复数据库命令，如果已经登录了MySQL服务器，也可以使用source命令恢复数据库。 语法： source filename; //filename为数据库备份文件。 注：登录MySQL服务后使用 执行该命令前，先创建并选择恢复后的目标数据库 |
9、通过复制文件实现数据备份和恢复:
|
MySQL服务器的数据在磁盘中是以文件形式保存的，所以可以直接复制MySQL数据库的存储目录及文件进行备份。 |
10、表数据导出到文本文件：
|
通过对表数据的导出和导入，可以实现MySQL数据服务器与其他数据库服务器间移动数据。 导出操作，是指将数据从MySQL数据表复制到文本文件，数据导出的方式有很多种。 使用SELECT …… INTO OUTFILE语句导出数据。 语法： SELECT columnlist FROM tablename [WHERE contion] INTO OUTFILE ‘filename’ [OPTION]; 从上述语法中可以看出，该导出语句分成以下两部分： （1）普通的数据查询语句，主要用来实现查询所要导出到文本文件中的数据。 （2）通过参数filename指定导出数据的目标文件 |
11、文本文件导入到数据表：
|
导入操作，是指将数据从文本文件加载到MySQL数据库表里。 同样，导入数据库的方式也有多种。 使用LOAD DATA INFILE语句实现数据的导入， 语法： LOAD DATA INFILE filename INTO TABLE tablename [OPTION]; 其中： （1）filename用来指定文本文件的路径和特征。 （2）tablename用来指定导入表的名称。 注：导入数据前应确保目标表已存在！ |
12、附加：
|
事务处理的命令包括START TRANSACTION, COMMIT和ROLLBACK， 其中COMMIT用于提交事务， ROLLBACK用户回滚事务。、 DDL也会影响到事务的提交，eg：TRUNCATE TABLE （DDL(数据定义语言) 用于创建和删除数据库对象等操作 CREATE 、DROP 、ALTER） 事务开启，除了STARTTRANSACTION之外，DML语句中增加、修改、删除都会开启事务。 UPDATE：更新 DELETE：删除 INSERT：新增 SELECT：查询 |
|||
|
唯一索引，不允许具有索引值相同的行，从而禁止重复的索引或键值。 |
|||
|
mysql通过 GRANT 授予权限 ，授予root用户全局级全部权限： GRANT ALL ON *.* to 'root'@'%' IDENTFIED BY '123456'; //给root设置密码
|
|||
|
mysql的数据库事务隔离级别是： Read Uncommitted（读取未提交内容）：在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。 本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。 读取未提交的数据，也被称之为脏读（Dirty Read）。 Read Committed（读取提交内容）：这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。 它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。 这种隔离级别也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit， 所以同一select可能返回不同结果。 Repeatable Read（可重读）：这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。 不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。 简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行， 当用户再读取该范围的数据行时，会发现有新的“幻影” 行。 InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。 Serializable（可串行化）：这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。 简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。 |
|||
|
索引是关系数据库中用于存放每一条记录的一种对象，主要目的是加快数据的读取速度和完整性检查； 创建唯一索引的目的不是为了提高访问速度，而只是为了避免数据出现重复。 唯一索引可以有多个但索引列的值必须唯一，索引列的值允许有空值。 如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该使用关键字UNIQUE。 唯一性索引列允许空值，而主键列不允许为空值。 |
