一、事务(Transaction)是指将一系列数据操作捆绑成一个整体进行同一管理。
如果某一事务执行成功,则在该事务中进行的所有数据更改都会提交,成为数据库中的永久组成部分。
如果事务执行错误且必须取消或回滚,则数据将会全部恢复到操作前的状态,所有更改均会被清除。
为什么需要事务:可以保障两个环节的安全
什么是事务:事务是一种机制,一个操作序列,包含了一组数据库操作命令,并且把所有的命令作为一个整体向系统提交或撤销操作请求,要么都执行,要么都不执行。
事务的属性:原子性(Atomictity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)
简称:ACID
原子性:事务是一个完整的操作,其中的各元素是不可分的(原子的),所有元素为一个整体
一致性:当事务处理完成时,数据必须处于一致状态,不能使数据处于不稳定状态
隔离性:对数据进行修改的所有并发事务是彼此隔离的,它不能以任何方式依赖或影响其它事务
持久性:事务的持久性不管系统是否发生了故障,事务处理的结果都是永久的。
事务主要通过UNDO日志和REDO日志实现,MyISAM存储引擎不支持事务。
UNDO日志:复制事务执行前的数据,用于在事务发生异常时回滚数据
REDO日志:记录在事务执行中,每条语句对数据进行更新的操作,当事务提交时,该内容将被刷新到磁盘
默认设置下,每个SQL语句都是一个事务。
执行事务的语法
开始事务:BEGIN;或START TRANSACTION;显式的标记一个事务的起始点
提交事务:COMMIT;从开始到提交所有数据更新将永久保存到数据库文件中,并释放连接时占用的资源
回滚(撤销)事务:ROLLBACK;回滚到事务开始前,并释放由事务控制的资源
在MySQL中,事务的默认隔离级别时REPEATABLE-READ(可重读)
设置自动提交关闭或开启
SET autocommit=0|1;
0:关闭自动提交
1:开启自动提交
当关闭自动提交后,从吓一条SQL语句开始则开启新事务,需使用COMMIT或ROLLBACK语句结束该事务
注意:
事务尽可能短
较大的事务会占用大量资源
访问的数据量尽可能少
操作少,对操作数据争夺就越少
查询数据尽可能不要使用事务
对数据浏览不会更新数据库的数据
尽量不要出现等待用户输入的操作
如果等待用户输入数据,会长时间占用资源
DELIMITER $$
CREATE PROCEDURE mybank()
BEGIN
##设置全局错误总数
DECLARE t_error INTEGER DEFAULT 0;
##如果有SQLException就将全局错误总数设置成1
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
##关闭事务默认提交
SET autocommit=0;
##关闭事务
START TRANSACTION;
##进行真实业务操作
UPDATE bank SET currentMoney=currentMoney-1000 WHERE cid=1;
UPDATE bank SET currentMoney=currentMoney+1000 WHERE cid=2;
IF t_error!=0 THEN
##回滚事务
ROLLBACK;
ELSE
##提交
COMMIT;
END IF;
##开启事务的默认提交
SET autocommit=1;
END$$
DELIMITER;
二、视图
使用视图的原因:一是安全,用户看不到整个数据库结构,而隐藏部分数据
二是符合用户使用逻辑,使用户更容易理解
三是可以把复杂代码封装起来,只需调用并执行对应视图就可完成任务
视图是另一种查看数据库中一个或多个表中数据的方法。
视图不保存数据,保存的是SQL语句。
视图通常用来进行:筛选表中的行,防止未经许可的用户访问敏感数据,将多个物理数据表抽象为一个逻辑数据表。
对用户的好处:结果更容易理解,数据更容易获取
对开发人员的好处:限制数据检索更容易,维护更方便
创建视图
CREATE VIEW 视图名
AS
<SELECT 语句>;
删除视图
DROP VIEW 视图名;
DROP删除表,DELETE删除表中的数据
查看视图
select * from 视图名
如果对视图里的数据进行修改,也会影响数据库中的数据
注意:
每个视图中可以使用多个表
一个视图可以嵌套另一个视图,但最好不要超过三层
对视图数据进行增删改会影响数据库中的数据
当视图来自多个表时,不允许添加和删除数据
三、索引
数据和索引都是按页存在的
什么是索引
索引是一种有效组合数据的方式,是一本书的目录
作用:
大大提高数据库的检索速度
改善数据库性能
索引分类:
普通索引:基本索引类型,允许在定义索引的列中插入重复值和空值
唯一索引:不允许两行具有相同的索引值,若创建了唯一约束,就会自动创建唯一索引
主键索引:如果表中有主键,主键会自动提升成主键索引,检索速度是最快的
复合索引:可以将多个列组合作为索引,只有在查询中使用了最左边的字段,索引才会被使用
全文索引:在定义索引的列上支持值的全文查找,支持在CHARVARCHAR和TEXT类型上创建,主要用于大量文本文字中搜索只有MyISAM引擎支持
空间索引:对空间数据类型的列创建的索引,如GEOMETRY、POINT等,创建索引的列必须不为空,只能在MyISAM引擎中使用
创建索引
CREATE [索引类型 选填] INDEX 索引名
ON 索引的表名(需要创建索引的列)
不能用模糊查询,索引会失效
删除索引
DROP INDEX 索引名 ON 表名;
删除表时,该表所有的索引将同时被删除
删除表中的列时,如果删除了索引的组成部分,则该列也会从索引中删除,如果组成索引的所有列都被删除,则整个索引将被删除
频繁搜索的列
经常用作查询选择的列
经常排序、分组的列
经常用连接的列
索引应该在尽量小,字节数少的列上创建
WHERE子句中有多个条件表达式时,包含索引列的表达式应置于其他条件之前
避免在d BY子句中使用表达式
查看索引:SHOW INDEX FROM 索引名;
Table:表示创建索引的表
Non_nuique:表示是否唯一,1代表非唯一
Kye_in_index:表示在索引中的位置
Column_name:表示定义索引的列字段
Sub_part:表示索引长度
Null:表示是否能为空
Index-type:表示索引类型
备份和恢复
mysqldump -u 用户名 -p 数据库名>位置:名称
恢复:mysql -u用户名 -p 库名<位置:名称