Mysql的事务、视图、索引、备份和恢复
事务
事务是作为单个逻辑工作单元执行的一系列操作,一个逻辑工作单元必须具备四个属性.即:原子性、一致性、隔离性、持久性，这些特性通常简称为ACID.
原子性(Atomicity)
事务是不可分割的工作逻辑单元
多个操作作为一个整体向系统提交,要么都执行,要么都不执行.
一致性(Consistency)
当事务完成时,数据处于一致状态.
隔离性(Isolation)
并发事务之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务。
持久性（Durability）
事务完成后，它对数据库的修改被永久保持。
MYSQL中支持事务的储存引擎有InnoDB 和BOB；
开始事务 BEGIN 或 START TRANSACTION; 标记一个事物的起点
提交事务 COMMIT; 该语句标识一个事务的成功提交
回滚(撤销)事务 ROLLBACK;清除自事务起始点至该语句所做的所有数据更新 的操作.
视图
视图是另一种查看数据库中一个或多个表中数据的方法.
视图是一种虚拟表
来自一个或多个表的行或列的子集创建的.(也可以是全部的行和列.)
视图充当着查询中的指定表筛选器,定义视图的查询可以基于一个或多个表,也可以基于其他视图、当前数据库或其他数据库.
好处:
对于用户:
结果更容易理解
获取数据更容易
对于开发人员:
限制数据检索更容易
维护应用程序更方便.
使用SQL语句创建视图语法:
CREATE VIEW 视图名 AS <SELECT语句>;
在SQL语句命名规范中,视图一般以view_xxx 或v_xxx的样式来命名.
使用SQL语句删除视图语法:
DROP VIEW [IF EXISTS] 视图名;
使用SQL语句查看视图数据
SELECT 字段1,字段2,字段3,......FROM 视图名.
使用视图的注意事项:
1):每个视图中可以使用多个表;
2):与查询相似,一个视图可以嵌套另一个视图,但最好不要超过三层;
3):对视图数据进行添加,更新,删除操作直接引用表中的数据;
4):当视图数据来自多个表时,不允许添加和删除数据;
索引
索引提供指针以只想储存在表中指定列的数据值,再根据指定的排序次序排列这些指针.
作用:索引的作用是通过使用索引,大大提高了数据库的检索速度,改善数据库性能.
索引的分类
1.普通索引
普通索引是MySQL中的基本索引类型,允许在定义索引的列中插入重复值和空值.
2.唯一索引
唯一索引不允许两行具有相同的索引值.
3.主键索引
在数据库关系图中为表定义一个主键将自动创建主键索引,主键索引是唯一索引的特殊类型
主键索引要求主键的每一个值是非空,唯一的,挡在查询中使用主键索引时,它还允许快速访问数据.
4.复合索引
在创建索引时,并不是只能对其中一列创建索引,与创建主键一样,可以将多个列组合作为索引,这种被称之为复合索引.
5.全文索引
MySQL 4.5数据库中,只有MyISAM储存引擎支持全文索引.主要应用于大量文本文字中搜索字符串.
6.空间索引
空间索引是对空间数据类型的列建立的索引,必须为not null 修饰的列,空间索引只能在储存引擎为MyISAM的表中创建
创建索引语法:
CREATE [UNIQUE | FULLTEXT | SPATITL] INDEX 指定索引名
ON 指定创建索引的表名 (指定需要创建索引的列 [length]);
[UNIQUE | FULLTEXT | SPATITL] 分别表示唯一索引,全文索引,空间索引,可选参数.
Length: 指定索引长度,可选参数,只有字符串类型才能指定索引长度.
经验总结:
1):查询时减少使用*返回全部列,不要返回不需要的列;
2):索引应该尽量小,在字节数小的列上建立索引;
3):WHERE 子句中有多个条件表达式时,包含索引列的表达式应置于其他条件表达式之前;
4):避免在ORDER BY子句中使用表达式.
5):根据业务数据发生频率,定期重新生成或重新组织索引,进行碎片整理.
标准建立索引的列:
1):频繁使用的列
2):经常用作查询的列;
3):经常排序,分组的列;
4):经常用作连接的列(主键/外键);
请勿使用以下的列创建索引:
1):仅包含几个不同值的列
2):表中仅包含几行.
3):为小型表创建索引可能不太实用,在检索数据所花的时间比逐条检查花费的时间更长.
删除索引
DROP INDEX 指定索引名 ON 指定创建索引的表名
删除索引注意一下几点:
删除表时,该表的所有索引将同时被删除.
删除表中的列时,如果要删除的列为索引的组成部分,则该列也会从索引中删除,如果组成索引的所有列都被删除,则整个索引将被删除.
查看索引
语法:SHOW INDEX FROM 表名
SHOW INDEX FROM student;
显示结果:
Table 创建索引的表名
Non_unique 表示索引是否是唯一.0表示唯一索引,1表示非唯一索引
Key_name 表示索引的名称
Seq_in_index 表示该列在索引的位置,如果索引是单列的该值为1,组合索引为每列在索引定义中的顺序.
Column_name 表示定义索引的列字段
Collation
Cardinality
Sub_part 表示索引的长度
Packed
Null 表示该列是否为空值
Index_type 表示索引类型
Comment
Index_comment
数据库的备份和恢复
使用mysqldump命令备份数据库
语法:mysqldump -u 数据库用户名 -h 登录用户的主机名称,本机可省略 -p 登录密码
需要备份的数据库名 [表1,表2,......] > 备份文件的名称.
mysqldump是DOS系统下的命令,在使用时无需进入MySQL命令行,否则将无法执行.
使用MySQL恢复数据库
方法一:
mysql -u 用户名 -p [数据库名] < 数据库备份的文件.
方法二:
source 数据库备份的文件.
登录mysql服务器后使用,执行命令前,先创建并选择恢复后的目标数据库.
通过复制粘贴实现数据备份和恢复
优点,操作简单;
缺点:复制数据前必须停止服务,不适合InnoDB数据库,MySQL版本不同不兼容;
表数据导入到文本文件
SELECT * FROM 表名
INTO OUTFILE 导出的目标文件
导入数据前保证目标表的存在;
