mysql 触发器 new.much_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。

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

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

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

触发器四要素：

监视地点

监视事件

触发时间

触发事件

创建触发器的语法:

create triggertriggerName

after/before(触发时间) insert/update/delete(监视事件) on表名(监视地点)foreach row #(行触发器，写死的)这句话是固定的beginsql语句 #一句或多句，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#触发时间：aftercreate triggertg2

afterinsert onoforeach rowbegin

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

afterdelete onoforeach rowbegin

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

afterupdate onoforeach rowbegin

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 triggertg5

beforeinsert onoforeach rowbegin

ifnew.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型，速度最快。

事务

事务的四大特性：

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

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

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

持久性(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行数据，如何快速导入？

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

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

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

索引的创建原则：

不要过度索引。

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

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

索引类型：

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

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

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

全文索引：fulltext index。

查看一张表上所有索引

show index from表名;

showindex 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 procedurestatus;

showprocedure status \G 横着显示

删除存储过程：

drop procedure 存储过程的名字

调用存储过程：

call 存储过程名字();

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

create procedurep1()begin

select * fromg;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

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 doset s = s +i;set i = i + 1;end while;selects;end$