数据库———事物，视图，索引，触发器

一.事物

事务是由一组DML语句组成，完成一个相对完整的功能,这一组DML语句要么全部执行，要么全部放弃执行。

数据库开启事务的命令如下：
start transaction 或者 begin 开启事务
commit 提交事务，提交未存储的事务
rollback 回滚事务,即撤销指定的sql语句(回退insert delete update语句)
savepoint 保留点 ，事务处理中设置的临时占位符 你可以对它发布回退。

例如：zs给ls转账

start TRANSACTION;
update account set money=money-100 where name ='zs';
update account set money = money+100 where name = 'ls';
COMMIT;

创建事物滚动：

start TRANSACTION;
insert into t5(name,gender) values('张飞','man');
insert into t5(name,gender) values('关羽','man');
select * from t5;
roolback;                                        --若尚未中没有设置回滚点，则回滚到事物开始的位置
select * from t5;

设置回滚点：

   start transaction;
    insert into t5(name,gender) values('张飞','man');
    savepoint s1;                                                        --设置回滚点s1
    insert into t5(name,gender) values('关羽','man');
    savepoint s2;														--设置回滚点s2
    insert into t5(name,gender) values('刘备','man');
    savepoint s3;														--设置回滚点s3
    insert into t5(name,gender) values('赵云','man');
    select * from t5;
    roolbavck to s2;												  --返回回滚点s2
    select * from t5;

事物特性：

事务具有四个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持续性（Durability）。这四个特性也简称ACID性。

（1）原子性：是指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。就如原子是自然界最小颗粒，具有不可再分的特征一样。
（2）一致性：事务执行的结果，必须使数据库从一个一致性状态，变到另一个一致性状态。当数据库中只包含事务成功提交的结果时，数据库处于一致性状态。一致性是通过原子性来保证的。（双方账户的总和不能少也不能多）
（3）隔离性：各个事务的执行互不干扰，任意一个事务的内部操作对其他并发的事务，都是隔离的。（简单的理解 就是你做你的，我做我的！）
（4）持续性：持续性也称为持久性，指事务一旦提交，对数据所做的任何改变，都要记录到永久存储器中，通常是保存进物理数据库。（转账记录成功了，不能过一会变成了出错了）

二.索引

索引在Mysql中是存储引擎用于快速找到记录的一种数据结构（BTree，平衡树），索引对于数据库的良好性能是非常关键的，尤其是当表中的数据量越来越大的时候，索引对于性能的影响愈发重要。
索引相当于字典的目录，如果查找某个字的时候可以通过目录，提高查询速度，否则需要翻阅字典的每一页。
使用索引的目的：提高查询速度。

（一）.创建索引的两种语法

语法1：创建表时，添加索引。
create table 表名(
字段名1 数据类型，
字段名2 数据类型,
...，
Index | key  [索引名称]  (字段名) 
);

语法2：创建完表后，添加索引。

create table 表名(
字段名1 数据类型，
字段名2 数据类型,
...， 
);
Alter table 表名 add {unique | primay key| index...索引种类} 索引名(字段名) 

（二）、索引分类
索引大体分为四类：普通索引，唯一索引，主键索引，多列索引
1.普通索引

alter table teacher ADD INDEX name_index(column); 

2.唯一索引

ALTER TABLE teacher ADD UNIQUE(column); # 在创建的同时添加唯一索引
ALTER TABLE teacher ADD column type UNIQUE; # 在创建之后追加唯一索引

3.主键索引

   `id` int(11) NOT NULL DEFAULT '0' primary key； # 在创建的同时添加主键索引。
    alter table t2 add PRIMARY key(id);# 在创建之后追加主键索引

4.多列索引

alter table teacheradd index(column1,column2,column3); 
create table `t2` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(20) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `chinese` int(11) DEFAULT NULL,
  `math` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `phone_index` (`phone`),
  KEY `chinese` (`chinese`,`math`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

（三）删除索引

Drop index 索引名 on 表名；
drop index phone_index on t2;
show create table t2;

使用索引：
1、少量数据不使用数据
2、查询次数少不使用索引
3、查询要携带索引字段，通常放在select之后第一个
4、索引提高了查询的效率，占有跟多的资源。
注：索引不是越多越好

三.视图

视图就是一条SELECT语句执行后返回的结果集,
视图是一个虚拟表，同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表。

视图的作用：
（1）简化用户的操作
关键信息来源于多个复杂关联表，可以创建视图提取我们需要的信息，
简化操作；
（2）对机密数据提供保护作用
不希望用户访问表中某些含敏感信息的列，

操作视图：
（1）创建视图:
Create view 视图名称 as DQL语句(select …);
（2）删除视图:
Drop view 视图名称;

练习1: 创建一个员工基本信息的视图。
– 查看表格中的所有数据

select * from t_employee;-- 查看表格中的所有数据
select name,gender,age,home,marry,hobby from t_employee;-- 查看表格中员工的基本信息。
create view v_employee as select name,gender,age,home,marry,hobby from t_employee;-- 创建视图。
select * from v_employee;-- 查看视图中的内容

练习2：将员工所有的信息做成一个视图。

select * from t_employee as t1 LEFT JOIN t_employee_detail as t2 ON t1.id=t2.id;  -- 查询出所有员工信息
select t1.*,t2.pos,t2.experence from t_employee as t1 LEFT JOIN t_employee_detail as t2 ON t1.id=t2.id;    -- 去掉 重复的id
create view v_employee_detail as select t1.*,t2.pos,t2.experence from t_employee as t1 LEFT JOIN t_employee_detail as t2 ON t1.id=t2.id;    -- 创建视图
select * from v_employee_detail; -- 显示所有数据
select name,gender,age from v_employee_detail;

更新视图
在MySQL中，更新视图是指通过视图来插入（INSERT）、更新（UPDATE）和删除（DELETE）表中的数据。因为视图是一个虚拟表，其中没有数据，所以通过视图更新时，都是转换到基本表来更新。
更新视图时，只能更新权限范围内的数据。超出了范围，就不能更新。

不可更新的视图：
某些视图是可更新的。也就是说，可以在诸如UPDATE、DELETE或INSERT等语句中使用它们，以更新基表的内容。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构，这类结构会使得视图不可更新。更具体地讲，如果视图包含下述结构中的任何一种，那么它就是不可更新的：
· 聚合函数（SUM(), MIN(), MAX(), COUNT()等）。
· DISTINCT
· GROUP BY
· HAVING
· UNION
· 位于选择列表中的子查询
· Join
· FROM子句中的不可更新视图
· WHERE子句中的子查询，引用FROM子句中的表。

四.触发器

触发器（trigger）：监视某种情况，并触发某种操作，它的执行是由事件来触发的，例如当对一个表进行操作（ insert，delete， update）时就会激活它执行。
触发器经常用于加强数据的完整性约束和业务规则等。 触发器创建语法四要素：
1.监视地点(table)
2.监视事件(insert/update/delete)
3.触发时间(after/before)
4.触发事件(insert/update/delete)

触发器基本语法：

Create trigger trigger_name trigger_time trigger_event on tb_name for each row trigger_stmt;    --创建触发器：
trigger_time ：    --触发器的触发事件，可以为before（在检查约束前触发）或after（在检查约束后触发）
trigger_event：    --触发器的触发事件，包括insert、update和delete；
tb_name：    --表示建立触发器的表名，就是在哪张表上建立触发器
trigger_stmt： --触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句；
FOR EACH ROW：---表示任何一条记录上的操作满足触发事件都会触发该触发器。
Drop trigger 触发器名称。  --    删除触发器：

练习：

delimiter $       -- 修改结束符
create TRIGGER tg1 after insert on o for each row      -- 创建触发器
begin 
update g set num = num-3 where id = 1;
end $
delimiter ;
insert into o(gid,much) VALUES(1,3);

结果：苹果的数量变为7了，说明在我们插入一条订单的时候，触发器自动帮我们做了更新操作。