mysql之触发器

   触发器

   trigger——枪击、扳击、引线的意思

   作用：监视某种情况并触发某种操作。

   观察以下场景：

   一个电子商城

   商品表，g

主键	商品名	库存
1	电脑	28
2	自行车	12

   订单表，o

订单主键	商品外键(gid)	购买数量

   完成下单与减少库存的逻辑(逻辑是紧密联系的)

insert into o(gid,num) values(2,3);   //插入语句
update g set goods_num=goods_num-3 where id=2;    //更新过程

   这两个逻辑可以看成一个整体，或者说insert→引发update。

   用触发器可以解决上述问题，我们可以监视某表的变化，当发生某种变化时，触发某个操作。

   触发器能监视：增、删、改。

   能触发的操作：增、删、改。

   

   触发器四要素：

1. 监视地点
2. 监视事件
3. 触发时间
4. 触发事件

   创建触发器的语法:

create trigger triggerName
after/before(触发时间) insert/update/delete(监视事件) on 表名(监视地点)
for each row #(行触发器，写死的)这句话是固定的
begin
sql语句  #一句或多句，insert/update/delete范围内(触发事件)
end;

   注意：sql语句默认以;为结束符。修改结束符：

delimiter $

   删除触发器的语法：

drop trigger 触发器名;  

   查看触发器:

 show triggers;

   如何在触发器中引用行的值？

   对于insert而言，新增的行用new来表示，行中的每一列的值，用new.列名来表示。

   对于delete来说，原本有一行，后来被删除，想引用被删除的这一行，用old来表示。old.列名就可以引用被删除行中的值。

   对于update来说，被修改的行，修改前的数据用old来表示，old.列名引用被修改之前行中的值。修改后的数据用new来表示，new.列名引用被修改之后行中的值。

   如下例：

   商品表，g

主键	商品名	库存
1	猪	22
2	羊	19
3	狗	12
4	猫	8

   订单表，o

订单主键	商品外键	购买数量

   1、添加订单，库存减少。

#监视地点：o表
#监视操作：insert
#触发操作：update
#触发时间：after

create trigger tg2
after insert on o
for each row
begin
update g set num=num-new.much where id=new.gid;
end$

insert into o values (1,4,1)$
insert into o values (2,3,3)$

   2、删除一个订单，库存相应增加。

#监视地点：o表
#监视操作：delete
#触发操作：update
#触发时间：after

create trigger tg3
after delete on o
for each row
begin
update g set num = num + old.much where id = old.gid;
end$

delete from o where oid = 2$

   3、修改订单的数量时，库存相应改变。

#监视地点：o表
#监视操作：update
#触发操作：update
#触发时间：after

create trigger tg4
after update on o
for each row
begin
update g set num = num + old.much - new.much where id = old.gid;
end$

update o set much = 1 where oid = 1$

   触发器里before和after的区别

   after是先完成数据的增、删、改再触发，触发的语句晚于监视的增、删、改,无法影响前面的增删改动作。

   before是先完成触发，再增、删、改，触发的语句先于监视的增、删、改发生，我们有机会判断、修改即将发生的操作。

   before的典型案例：

   对于所下订单进行判断，如果订单的数量>5，就认为是恶意订单，强制把订单的商品数量改成5。

#监视地点：o表
#监视事件：insert
#触发事件：update
#触发时间：before

#目的：触发事件先于监视事件发生，并判断监视事件的数据
create trigger tg5
before insert on o
for each row
begin
    if new.much > 5 then
        set new.much = 5;
    end if;(sql还有控制语句啊！！！)
    update g set num = num - new.much where id = new.gid; 
end$

    

   存储引擎

   数据库对同样的数据，有着不同的存储方式和管理方式，在mysql中称为存储引擎。

   例如，一部电影，有格式如MP4、wmv、avi、rmvb、flv等，同样的一部电影，在硬盘上有不同的存储格式，所占空间与清晰程度也不一样。

   表里的数据存储在硬盘上，具体如何存储的？

   存储的方式方法也有多种。对于用户来说，同样一张表的数据，无论用什么引擎来存储，用户看到的数据是一样的，但是对于服务器来说，有区别。

   常用的表的引擎(存储引擎与其特点)

   myisam：批量插入速度快，不支持事务，锁表(?)。

   innodb：批量插入相对较慢，支持事务，锁行(?)。

   全文索引：目前5.5版本，myisam，innodb都已经支持。

   应用场景：

   文章、新闻等安全性要求不高的，选myisam。

   订单、资金、账单、火车票等对安全性要求高的，可以选用innodb。

   对于临时中转表,可以用memory型，速度最快。

   

   事务

   事务的四大特性：

1. 原子性(Atomicity)：原子意为最小的粒子，或者说不能再分的事物。数据库事物的不可再分的原则即为原子性。要么全部执行，要么全部撤销。

2. 一致性(Consistency)：指数据的规则，在事物前/后应保持一致。

3. 隔离性(Isolation)：简单点说，某个事务的操作对其他事务不可见。

4. 持久性(Durability)：当事务完成后，其影响应该保留下来，不能撤销。

   通俗的说事务：指一组操作，要么都成功执行，要么都不执行。→原子性

   在所有的操作没有执行完毕之前，其他会话(?)不能够看到中间改变的过程。→隔离性

   事务发生前和发生后，数据的总额依然匹配。→一致性

   事务产生的影响不能够撤销→持久性

   如果出了错误，事务也不允许撤销，只能通过”补偿性事务”才能撤销。

   例子，转账。

   李三→支出500，李三 -500

   赵四→收到500，赵四 +500

   1、关于事务的引擎：选用innodb/bdb

   2、开启事务：

start transaction;
sql.....
sql.....

   3、commit提交或rollback回滚。

   注意:当一个事务commit或者rollback就结束了。

   注意:有一些语句会造成事务的隐式的提交，比如 start transaction;

   

   备份与恢复

   系统运行中，有增量备份与整体备份。

   例: 每周日整体备份一次，周一到周六备份当天(增量备份)。如果周五出了问题，可以用周日的整体 + 周一、二、三、四来恢复。

   备份的工具： 有第3方的收费备份工具。目前我们所学的是系统自带的备份功能，mysqldump。

   mysqldump可以导出库、表。

   例1：导出mugua库下面的account表。

mysqldump -u 用户名 -p 密码 库名 表1 表2 表3 > 地址/备份文件名称
或
mysqldump -u 用户名 -p 库名 表1 表2 表3 > 地址/备份文件名称
Enter password:密码

   导出的是建表语句及insert语句。

   例2：如何导出一个库下面的所有表？

mysqldump -u 用户名 -p 密码 库名 > 地址/备份文件名称

   例3：如何导出以库为单位导出？

mysqldump -u 用户名 -p 密码 -B 库1 库2 库3 > 地址/备份文件名称

   例4：如何导出所有库？

mysqldump -u 用户名 -p 密码 -A > 地址/备份文件名称

   恢复：

   1、登陆mysql命令行

   对于库级的备份文件

mysql> source 备份文件地址(source < d:/xxx.sql)

   对于表级的备份文件

mysql>use 库名
mysql>source 备份文件地址(source d:/xxx.sql)

   2、不登陆到mysql命令行

   针对库级的备份文件

mysql -u 用户名 -p 密码 < 备份文件地址(分隔符：/)

   针对表级的备份文件

mysql -u 用户名 -p 密码 库名< 表级备份文件地址(分隔符：/)

 

   索引

   索引：是针对数据所建立的目录。

   作用：可以加速查询速度。

   负面影响：降低了增删改的速度，增大了表的文件大小(索引文件甚至可能比数据文件还大)。

   案例(换服务器导入数据)：

   设有新闻表15列，10列上有索引，共500W行数据，如何快速导入？

1. 把空表(xxx.frm——表结构文件，列及列类型)的索引全部删除。

2. 导入数据(xxx.ibd——数据文件)。

3. 数据导入完毕后，集中建索引。

   索引的创建原则：

1. 不要过度索引。

2. 在where条件最频繁的列上加。

3. 尽量索引散列(?)值，过于集中的值加索引意义不大。

   索引类型：

1. 普通索引：index→仅仅是加快查询速度。

2. 唯一索引：unique index→行上的值不能重复。

3. 主键索引：primary key→主键不能重复(主键索引加在元数据上)。主键必唯一，但是唯一索引不一定是主键，一张表上，只能有一个主键，但是可以有一个或多个唯一索引。

4. 全文索引：fulltext index。

   查看一张表上所有索引

show index from 表名;
show index from 表名 \G(横着显示)

   建立索引

alter table 表名 add index/unique/fulltext [索引名(默认使用列名)](列名);
alter table 表名 add primary key(列名);//不要加索引名,因为主键只有一个

   删除索引

   删除非主键索引：

alter table 表名 drop index 索引名;

   删除主键：

alter table 表名 drop primary key;

   查看匹配度：

select id,email,match (intro) against (‘health’) from member;

   关于全文索引的用法：

match (全文索引名) against (‘keyword’);

   例：

select * from member where match (intro) against (‘health’);

   关于全文索引的停止词

   全文索引不针对非常频繁的词做索引，如 this、is、you、my等等。

   全文索引：在mysql的默认情况下，对于中文意义不大。

   原因：因为英文有空格、标点符号来拆分单词，进而对单词进行索引。而对中文，没有空格来隔开单词，mysql无法识别每个中文词(中文分词是一项很大的项目)。

 

   存储过程：procedure

   概念类似于函数，就是把一段代码封装起来，当要执行这一段代码的时候，可以通过调用该存储过程来实现。在封装的语句体里面，可以用if/else、case、while等控制结构可以进行sql编程。

   查看现有的存储过程：

show procedure status;     
show procedure status \G 横着显示

   删除存储过程：

drop procedure 存储过程的名字

   调用存储过程：

call 存储过程名字();

   第1个存储过程，体会”封装sql”。

create procedure p1()
begin
   select * from g;
end$

调用：call() p1()$

   第2个存储过程，体会”参数与控制结构”。

create procedure p2(n int)
begin
    select * from g where num > n;
end$
call p2(10)$

create procedure p3(n int,j char(1))
begin
    if j = 'h' then
        select * from g where num > n;
    else
        select * from g where num < n;
    end if;
end$
call p3(10,'h')$

   练习：计算1---n的和(体会循环)

create procedure p4(n smallint)
begin
    declare i int;
    declare s int;
    set i = 1;
    set s = 0;
    while i <= n do
        set s = s + i;
        set i = i + 1;
    end while;
    select s;
end$