procedure sp_name ([参数,参数..]) begin 执行体 end delimiter ;

其中，create procedure 为用来创建存储过程的关键字
sp_name为存储过程的名称
begin...end为存储过程执行代码的开始和结束关键字,里面的执行体可以写多条sql
例如:

mysql> call p1();
ERROR 1146 (42S02): Table ‘la.goods‘ doesn‘t exist
mysql> delimiter $
mysql> create procedure p1()
    -> begin
    -> select * from blog_category;
    -> end $
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

存储过程创建完毕

使用存储过程

语法：call 存储过程名称();
例如:call p1();

存储过程参数

存储过程与函数非常相似，因此可以设置参数
语法：
create procedure 名称(参数)
begin
...
end

参数的设置语法：
create procedure 名称([in|out|inout] 参数名称 type...)
示例：create procedure pp(in name varchar(32),out age int, inout city varchar(32));
type表示参数的数据类型,mysql全部数据类型都可以使用
in：
输入参数，可以是常量，也可以是变量
可以向存储过程传递信息，存储过程内部可以接收实参信息
如果是变量，在存储过程中做了修改，则外部感觉不到，仍然保持原值【值传递】
out：
输出参数，必须是变量，
在存储过程内部，该参数初始值为null，忽略调用者传递进来的信息。并且存储内部对该变量做了修改，外部也能访问到，类似【引用传递】
inout：
输入输出参数，必须是变量，
可以向存储过程内部传递信息，如果在存储过程内部值被改变，则可从外部可以感知到
该参数与out类似，都可以从存储过程内部传值给外部

in类型参数设置：
drop procedure if exists p7;
delimiter $
create procedure p7(in a int)
begin
select a; //输出传递进来的实参信息
set a=100; //对参数进行修改
select a; //输出修改后的参数信息
end $
delimiter ;

调用p7,并传递“常量”参数的效果：
mysql> call p7(50);
+------+
| a |
+------+
| 50 |
+------+
1 row in set (0.00 sec)

+------+
| a |
+------+
| 100 |
+------+
1 row in set (0.01 sec)

调用p7,并传递“变量”参数：
mysql> call p7(@num);
+------+
| a |
+------+
| 20 |
+------+
1 row in set (0.00 sec)

+------+
| a |
+------+
| 100 |
+------+
1 row in set (0.02 sec)

变量在存储过程中被修改了，但是外边仍然访问到原值：
设置变量：
mysql> set @num=20;
Query OK, 0 rows affected (0.00 sec)

调用存储过程：
mysql> call p7(@num);
+------+
| a |
+------+
| 20 |
+------+
1 row in set (0.00 sec)
+------+
| a |
+------+
| 100 |
+------+
1 row in set (0.02 sec)

外部访问变量，发现没有被存储过程影响修改：
mysql> select @num;
+------+
| @num |
+------+
| 20 |
+------+
1 row in set (0.00 sec)

out类型参数设置：
drop procedure if exists p8;
delimiter $
create procedure p8(out a int)
begin
select a;
set a=100;
select a;
end $
delimiter ;

设置变量：
mysql> set @num=20;
Query OK, 0 rows affected (0.00 sec)

调用存储过程，并传递变量参数(发现在存储过程内部变量的值已经变为null)：
mysql> call p8(@num);
+------+
| a |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
+------+
| a |
+------+
| 100 |
+------+
1 row in set (0.01 sec)

在存储过程外部，访问变量，发现其已经被存储过程修改了：
mysql> select @num;
+------+
| @num |
+------+
| 100 |
+------+
1 row in set (0.00 sec)

inout类型参数设置：
存储过程参数设置为inout类型：
drop procedure if exists p9;
delimiter $
create procedure p9(inout a int)
begin
select a;
set a=100;
select a;
end $
delimiter ;

设置变量：
mysql> set @num=20;
Query OK, 0 rows affected (0.00 sec)

调用存储过程传递参数变量，发现存储过程内部可以获得参数信息(20)：
mysql> call p9(@num);
+------+
| a |
+------+
| 20 |
+------+
1 row in set (0.00 sec)
+------+
| a |
+------+
| 100 |
+------+
1 row in set (0.01 sec)

在存储过程外部，访问变量，发现其已经被存储过程修改(100)：
mysql> select @num;
+------+
| @num |
+------+
| 100 |
+------+
1 row in set (0.00 sec)

out和inout参数都要求传递变量，如果传递常量则mysql要报错，具体如下：
mysql> call p9(1000);
ERROR 1414 (42000): OUT or INOUT argument 1 for routine advance.p9 is not a vari
able or NEW pseudo-variable in BEFORE trigger

变量使用

局部变量

注意：局部变量可以在子程序（函数、存储过程）中声明并使用，这些变量的作用范围是在BEGIN…END程序中。

1.声明变量语法：
declare var_name [, var_name]... data_type [ default value ];
declare 为声明变量的关键字，var_name为局部变量的名称，data_type为变量的数据类型(mysql的全部数据类型都可以使用)，default value给变量提供一个默认值，否则为null
示例：
declare myparam int default 100;

2.变量赋值-1
set为变量赋值
语法1：
set var_name=expr [, var_name=expr]...;
expr为变量的值，也可以是一个表达式
例如:
set a=10;
set b=c+d;

示例1：
声明4个局部变量，并赋值：

delimiter $
create procedure p3()
begin
    declare a int default 0;
    declare b int default 0;
    declare c int default 0;
    declare d int default 0;
    set a=10,b=20,c=30;
    set d=b+c;
    select a,b,c,d;
end $
delimiter ;

使用:call p3();

3.变量赋值-2
SELECT INTO为变量赋值
语法2：
select col_name[,...] into var_name[,...] table_expr [WHERE...];
select ... into 为该语法的关键字
col_name为数据表字段名称，var_name为局部变量的名称，var_name变量在之前需要声明好
示例2：
–查询出数据表的一条记录的一(或多个)个字段内容并为变量赋值

delimiter $
create procedure p4()
begin
    declare mingzi varchar(32) default ‘‘;
    declare jiage decimal(10,2) default 0;

    select name,price into mingzi,jiage from goods where id=3;
    select mingzi,jiage;
end $
delimiter ;

全局变量

全局变量的作用域要比局部变量要广，全局变量可以作用于当前整个连接，在子程序的内部/外部都可以声明使用，但是当当前连接断开后，其所定义的全局变量都会消失。

局部变量：只能在存储过程(或函数)内部声明使用

全局变量：在存储过程(或函数)内部、外部都可以声明使用
在存储过程内部声明的，外部也可以访问，不推荐
在存储过程外部声明的，内部也可以访问，不推荐
推荐的是，内部声明就内部访问，外部声明就外部访问
创建用户变量
语法1： set @name:=value;
@name为用户变量名称，在名称前边必须要设置@符号
示例1： set @v1 := 3; 或 set @v1 = 3; //直接赋值
set @v1 := @v1*4+5; 或 set @v1 = @v1*4+5; //表达式赋值

语法2： select @name:=value,@name:=value....;
示例2： select @age:= 25,@height:=180;

对用户变量赋值有两种方式，一种是直接用”=”号，另一种是用”:=”号
其区别在于使用set命令对用户变量进行赋值时，两种方式都可以使用
当使用select语句对用户变量进行赋值时，只能使用”:=”方式
因为在select语句中，”=”号被看作是比较操作符

示例应用：

mysql> set @city=‘beijing‘;
Query OK, 0 rows affected (0.00 sec)

mysql> set @people=‘2000‘;
Query OK, 0 rows affected (0.00 sec)

mysql> select @city,@people;
+---------+---------+
| @city   | @people |
+---------+---------+
| beijing | 2000    |
+---------+---------+
1 row in set (0.00 sec)

在存储过程外部，可以直接使用内部声明的全局变量(不推荐)：
声明一个存储过程，使用外部声明好的全局变量(不推荐)
创建一个存储过程，在内部声明全局变量

drop procedure if exists p6;     //判断并删除存在的p6存储过程
delimiter $
create procedure p6()
begin
    select @v1:=10,@v2:=20;
end $
delimiter ;

光标

什么是光标？
一条sql语句的查询结果可以是多个字段、多条记录信息，这些数据如果需要在存储过程(函数)中使用的，就需要使用“光标”，光标可以逐条读取查询结果集中的记录信息。
光标可以存储、遍历sql语句查询出来的多条记录信息。

声明：
declare 光标名称 cursor for sql语句
(cursor:英文为指针、游标之意)
示例：
declare cursor_goods cursor for select name,price from goods
上边示例中，光标名称为cursor_goods，select语句从goods表中查询name、price字段的多条记录出来

声明光标数据获取完毕的处理语句：
declare exit[/continue] handler for not found 设置条件;
对于exit处理程序，当前begin…end复合语句的执行被终止，
对一个continue 处理程序，当前子程序的执行在执行处理程序语句之后继续。
示例：
declare exit handler for not found set done=false;
上边示例中，当光标数据获得完毕就设置done的值为false。

打开：
open 光标名称;
示例：
open cursor_goods;
使用：
获得光标中的具体数据信息，把信息存储到具体变量中
fetch 光标名称 into name1,name2,name3…
name系列参数表示将光标中的select语句查询出来的字段信息存入该参数中，name系列参数必须在声明光标之前就定义好
示例：
fetch cursor_goods into goods_name,goods_price;
关闭：
close光标名称;
示例：
close cursor_goods;

综合应用示例：
利用光标把goods表的数据复制粘贴到goods1表中
–创建一个goods1数据表，字段与goods表完全一致

CREATE TABLE `goods1` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘主键‘,
  `name` varchar(32) NOT NULL DEFAULT ‘‘ COMMENT ‘名称‘,
  `price` decimal(10,2) NOT NULL DEFAULT ‘0.00‘ COMMENT ‘价格‘,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

–创建存储过程
–通过光标把goods表的记录读取出来存储入另外一个数据表goods1里边

drop procedure if exists p14;
delimiter $
create procedure p14()
begin
    //声明光标赋值变量
    declare nm varchar(32) default ‘‘;
    declare pr decimal(10,2) default 0;

    //声明一个光标变量，如果光标数据读取完毕，要修改变量值【在前】
    declare done int default true;
    //声明光标，名称为cur_goods【在后】
    declare cur_goods cursor for select name,price from goods;
    //光标数据读取完毕设置条件变化,即done=false了
    declare exit handler for not found set done=false;

    //使用光标
    open cur_goods;
        //利用循环把光标中的各个记录信息进行获取操作
        while  done  do
            //获得光标中的具体数据信息,并存储给nm和pr变量
            fetch  cur_goods  into nm,pr;
            //把nm、pr变量信息写入到goods1表中
            insert into goods1 values (null,nm,pr);
        end while;
    //关闭光标
    close cur_goods;
end $
delimiter ;

创建光标存储过程：

确认goods1表数据：
mysql> select * from goods1;
Empty set (0.00 sec)

调用p14存储过程：
mysql> call p14();
Query OK, 0 rows affected (0.01 sec)

再次确认goods1表数据(数据已经复制过来了)：

mysql> select * from goods1;
+----+---------+----------+
| id | name    | price    |
+----+---------+----------+
|  4 | nokia   |  1200.00 |
|  5 | htc     |  1300.00 |
|  6 | samsung |  1400.00 |
|  7 | xiaomi  |  1500.00 |
|  8 | huawei  | 10000.00 |
+----+---------+----------+
5 rows in set (0.00 sec)

光标也可以同时声明多个，并且嵌套使用、并列使用均可以：
嵌套使用示意：

begin
declare nm,nm1 varchar(32);
declare pr,pr1 decimal(10,2);
DECLARE done int default true;
DECLARE cur CURSOR FOR SELECT name,price FROM goods;
DECLARE exit handler for not found set done=false;
open cur;
while done do
    fetch cur into into nm,pr;
    begin
        DECLARE done1 int default true;
        DECLARE cur1 CURSOR FOR SELECT name,price FROM goods1;
        DECLARE exit handler for not found set done1=false;
        open cur1;
        while done1 do
            fetch cur1 into nm1,pr1;
            select nm,pr,nm1,pr1;
        end while;
        close cur1;
end;
end while;
close cur;
end $

注意：
① 内部的cur1部分，必须使用begin...end构造
② 在同一个begin...end里边不能出现两次DECLARE exit handler ...语句，
嵌套时各层级有自己的可以
③ 在各自层级的begin...end里边，声明光标变量、光标、结束条件、循环。

并列使用示意：
declare nm,nm1 varchar(32);
declare pr,pr1 decimal(10,2);
DECLARE done int default true;
DECLARE cur CURSOR FOR SELECT name,price FROM goods limit 1;
DECLARE cur1 CURSOR FOR SELECT name,price FROM goods1 limit 1;
DECLARE exit handler for not found set done=false;
open cur;
close cur;

open cur1;
set done = true; //恢复条件
while ..
close cu
注意：
① 可以在同一个begin…end里边同时声明多个光标
② 并列打开，自己使用自己的
③ declare exit handler…语句只能声明一个

查看存储过程

① show procedure status like ‘存储过程名称’\G //查看存储过程的状态
② show create procedure 存储过程名称\G //查看创建存储过程的信息
\G表示以一种更友好的、清楚的方式显示信息
③select * from mysql.proc; //存储过程存储在”mysql数据库”的proc数据表中

Db:数据库名称
Name:存储过程名称
Type: Procedure/function
definer:创建者
Modified:修改时间
Createed:创建事件
查看存储过程如何创建：

删除存储过程

drop procedure [if exists] 名字;

存储过程好处

①　存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
②　复杂操作简单化，当对数据库进行复杂操作时（如对多个表进行Update、Insert、Query、Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
③　存储过程可以重复使用，可减少数据库开发人员的工作量。
④　安全性高，可设定只有某此用户才具有对指定存储过程的使用权。

流程控制

流程控制：单路分支、双路分支、多路分支、循环等
流程控制结构，都只能在“编程范围(存储过程/存储函数/触发器)”中使用。

if分支

语法：
if 判断表达式
then 表述;
elseif判断表达式
then 表述;
else
表述;
end if;
其中 if elseif else then end if 都是关键字
判断表达式：判断是否为真true
分支“表述”语句可以是一条语句，如果是多条语句，可以使用begin…end进行构造
在else分支里边，不需要设置then关键字
例如：
if 判断表达式
then begin表述1;表述2;表述3;end
elseif 判断表达式
then begin表述1;表述2;表述3; end
else
begin表述1;表述2;表述3; end
end if;
if语句都要使用end if来结束。

示例：
利用if else判断价格是否贵、便宜：

drop procedure if exists p10;
delimiter $
create procedure p10()
begin
    declare jiage decimal(10,2);
    select price into jiage from goods where id=5;
    if jiage<1000
        then  select ‘the price is cheap‘;
    else
        select ‘the price is expensive‘;
    end if;
end $
delimiter ;

使用效果：

mysql> call p10();
+------------------------+
| the price is expensive |
+------------------------+
| the price is expensive |
+------------------------+
1 row in set (0.00 sec)

设计多路分支语句：

drop procedure if exists p11;
delimiter $
create procedure p11()
begin
    declare jiage decimal(10,2);
    select price into jiage from goods where id=5;
    if jiage<1000
        then  select ‘the price is cheap‘;
    elseif jiage>=1000 and jiage<2000
        then select ‘the price is expensive‘;
    else
        select ‘the price is costly‘;
    end if;
end $
delimiter ;

使用效果：

mysql> call p11();
+------------------------+
| the price is expensive |
+------------------------+
| the price is expensive |
+------------------------+
1 row in set (0.00 sec)

case分支

第一种格式：
语法1：
case 表达式
when 值 then表述
when值 then表述
else 表述
end case
其中 表达式 与 值 判断相等，会决定执行哪个 when 的 表述 被执行
case when else end case 都是关键字，在else分支里边不需要设置then

示例1：

case val
when 1 then select ‘val is 1’;
when 2 then select ‘val is 2’;
else select ‘val is not 1 or 2’;
end case;

–创建p12存储过程
–根据传递的实参判断星期几，case用法

drop procedure if exists p12;
delimiter $
create procedure p12(in w int)
begin
    case w
        when 1 then select ‘Monday‘;
        when 2 then select ‘Tuesday‘;
        when 3 then select ‘Wednesday‘;
        when 4 then select ‘Tuesday‘;
        when 5 then select ‘Friday‘;
        when 6 then select ‘Saturday‘;
        when 7 then select ‘Sunday‘;
        else select ‘no week‘;
    end case;
end $
delimiter ;

使用效果：

mysql> call p12(3);
+-----------+
| Wednesday |
+-----------+
| Wednesday |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call p12(5);
+--------+
| Friday |
+--------+
| Friday |
+--------+
1 row in set (0.00 sec)

第二种格式：
语法2：
case
when判断表达式 then 表述
when 判断表达式 then 表述
else 表述
end case
case会逐个判断每个when的表达式是否为真，为真true则执行对应的“表述”，都不真就执行else的表述
判断表达式：例如a=b、a>b、a<b、a>=b、 a>b and c>d 等等

示例2：

case
when val is null then select ‘val is null’;
when val<0       then select ‘val is less then 0’;
when val>0       then select ‘val is greater than 0’;
else select ‘val is 0’;
end case;

while循环

while语句创建一个带条件判断的循环过程
语法：
while 表达式 do
循环体
end while
如果表达式为真，则执行循环体，否则退出循环

示例：
通过while循环变量进行累加。

drop procedure if exists p13;
delimiter $
create procedure p13()
begin
    declare a int default 0;
    while a<10 do
        set a=a+1;
    end while;
    select a;
end $
delimiter ;

使用效果(a被累加到10)：

mysql> call p13();
+------+
| a    |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

除了while 还有loop、leave、iterate、repeat等循环

存储函数

mysql函数分为两种：系统函数、自定义函数
系统函数：例如sum()、count()、avg()、max()、min()、unix_timestamp()获取时间戳、from_unixtime()时间戳变为格式化时间等等
自定义函数：用户可以根据自身需要给系统定义函数(存储函数)，定义好的函数在使用上与系统函数的格式完全一致

创建

语法：

create function 函数名称(参数)
returns 数量类型
begin
函数执行体
return 被返回的信息
end
create function： 为用来创建存储函数的关键字；
参数：设置形式与存储过程类似 ，参数名称  类型  参数都是in输入型的
returns 数据类型：表示函数返回数据的类型，是必须要设置的
begin...end：为构造复合语句的标记
函数执行体：与存储过程相同，里边可以有变量、流程结构等
return  ：该函数返回的具体信息，数据类型要求与returns一致

示例：
–调用函数传递数字参数，根据数字参数获得指定goods表的name名字信息

drop function if exists f1;
delimiter $
create function f1(i int)
returns varchar(32)
begin
    declare nm varchar(32) default ‘‘;
    select name into nm from goods where id=i;
    return nm;
end $
delimiter ;

使用效果：

mysql> select f1(5);
+--------+
| f1(5)  |
+--------+
| huawei |
+--------+
1 row in set (0.00 sec)

mysql> select f1(4);
+--------+
| f1(4)  |
+--------+
| xiaomi |
+--------+
1 row in set (0.00 sec)

查看

① show create function 名称\G
② select * from mysql.proc;

删除

drop function [if exists] 名称;
存储过程与存储函数的不同点：
①　存储函数参数没有in out inout 标志，默认都是in的
②　存储函数使用“select 函数()” 方式调用，可以作为sql语句的一部分进行调用；存储过程使用“call 存储过程名()” 方式调用，其作为独立的部分执行。
③　函数可以通过return返回信息，而存储过程不允许使用return，但是其可以通过out参数返回多个值

其他注意事项：
①　存储过程内部可以调用其他存储过程，通过call调用
②　存储过程/函数参数名称要与数据表的字段名称区别开来，否则出现异常错误

触发器

触发器（trigger）有的人说它是一种特殊的”存储过程”,它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则制定等。

进行数据库应用软件的开发时，我们有时会碰到表中的某些数据改变，希望同时引起其他相关数据改变的需求，利用触发器就能满足这样的需求。

创建触发器

语法：
create trigger触发器名称 触发时间 监听事件
on 表名for each row
begin
要执行的代码 或称作 触发事件；
end;
create trigger ... on ... for each row begin ... end是创建触发器的关键字
触发器名称：触发器本身有名称
触发时间：可以指定为before或after
监听事件：可以是insert、update、delete
表名：对哪个数据表建立触发器
触发事件：被触发执行的事件，可以是具体的insert、update、delete语句

示例1：

创建一个触发器，当订单表形成订单之后的时候，自动做商品库存扣除操作
(时间：after，事件：insert)
给goods表增加number库存字段
–给goods表增加number库存字段及默认值
alter table goods add number int not null default 100 comment ‘库存‘;
创建订单表

create table indent(
    indent_id int  auto_increment primary key comment ‘主键id‘,
    gid int comment ‘商品id，是外键‘,
    num smallint comment ‘购买数量‘
)engine=Innodb charset=utf8;

设计触发器
mysql> use advance; //触发器需要针对具体数据库做设置

delimiter $
create trigger indent_ai_tri
after insert
on indent for each row
begin
    update goods set number=number-new.num where id=new.gid;
end $
delimiter ;
indent_ai_tri:触发其名称，indent_ai(after insert)_tri(trigger)
after insert: 给订单表insert写入数据之后要触发事件执行
indent:给该indent表设计的触发器
update...语句：触发事件，当给indent表写入数据之后就要执行该update语句事件

确认商品表信息

mysql> select * from goods;
+----+---------+----------+--------+
| id | name    | price    | number |
+----+---------+----------+--------+
|  1 | nokia   |  1200.00 |    100 |
|  2 | htc     |  1300.00 |    100 |
|  3 | samsung |  1400.00 |    100 |
|  4 | xiaomi  |  1500.00 |    100 |
|  5 | huawei  | 10000.00 |    100 |
+----+---------+----------+--------+
5 rows in set (0.00 sec)

购买商品形成订单(xiaomi手机买了5个)

mysql> insert into indent values (null,4,5);
Query OK, 1 row affected (0.01 sec)

再次确认商品表信息(由于有触发器存在，发现xiaomi库存自动做扣除)：

mysql> select * from goods;
+----+---------+----------+--------+
| id | name    | price    | number |
+----+---------+----------+--------+
|  1 | nokia   |  1200.00 |    100 |
|  2 | htc     |  1300.00 |    100 |
|  3 | samsung |  1400.00 |    100 |
|  4 | xiaomi  |  1500.00 |     95 |
|  5 | huawei  | 10000.00 |    100 |
+----+---------+----------+--------+
5 rows in set (0.00 sec)

触发器new和old关键字
new关键字，该关键字代表给indent表写入的新记录的引用。
当然，除了new，还有old关键字，其代表的是之前旧记录的引用。
对insert事件而言，new是合法的
对delete事件而言，old是合法的
对update事件而言，new和old可以同时使用
如下图:

触发器关键字after和before：
要根据业务规则的实际流向，选择使用不同的关键字，
after:代表在某个事件完成之后要触发另一个事情
例如用户下订单后就要做商品库存扣除操作，就要使用after
before:代表在某个事件准备做之前就要做另一个事件
例如用户下订单之前就要判断购买的商品数量是否合法，就要使用before

示例2

创建一个触发器，订单取消(删除)了，要把对应商品的库存还原回来
(时间：after，事件：delete)
(对old关键字进行使用)

delimiter $
create trigger indent_ad_tri
after delete
on indent for each row
begin
    update goods set number=number+old.num where id=old.gid;
end $
delimiter ;

确认 商品表和订单表 数据：

mysql> select * from goods;
+----+---------+----------+--------+
| id | name    | price    | number |
+----+---------+----------+--------+
|  1 | nokia   |  1200.00 |    100 |
|  2 | htc     |  1300.00 |    100 |
|  3 | samsung |  1400.00 |    100 |
|  4 | xiaomi  |  1500.00 |     95 |
|  5 | huawei  | 10000.00 |    100 |
+----+---------+----------+--------+
5 rows in set (0.00 sec)

mysql> select * from indent;
+-----------+------+------+
| indent_id | gid  | num  |
+-----------+------+------+
|         1 |    4 |    5 |
+-----------+------+------+
1 row in set (0.00 sec)

把indent_id=1的订单给取消了(删除)：
mysql> delete from indent where indent_id=1;
Query OK, 1 row affected (0.00 sec)

再次确认商品表数据(由于有触发器存在，库存自动还原)：
mysql> select * from goods;
+----+---------+----------+--------+
| id | name    | price    | number |
+----+---------+----------+--------+
|  1 | nokia   |  1200.00 |    100 |
|  2 | htc     |  1300.00 |    100 |
|  3 | samsung |  1400.00 |    100 |
|  4 | xiaomi  |  1500.00 |    100 |
|  5 | huawei  | 10000.00 |    100 |
+----+---------+----------+--------+
5 rows in set (0.00 sec)

示例3

创建触发器实现
–订单商品的购买数量发生变化，要更新商品表对应的库存信息
–最终的商品库存 = 当前库存+修改前(old)的订单库存-修改后(new)的订单库存
(时间：after，事件：update)
(会同时使用new和old关键字)

设计触发器

delimiter $
create trigger indent_au_tri
after update
on indent for each row
begin
    update goods set number=number+old.num-new.num where id=new.gid;
end $
delimiter ;

给订单表添加购买信息(samsung被购买了8个)
mysql> insert into indent values (null,3,8);
Query OK, 1 row affected (0.01 sec)

确认 商品表和 订单表数据：
mysql> select * from goods;
+----+---------+----------+--------+
| id | name    | price    | number |
+----+---------+----------+--------+
|  1 | nokia   |  1200.00 |    100 |
|  2 | htc     |  1300.00 |    100 |
|  3 | samsung |  1400.00 |     92 |
|  4 | xiaomi  |  1500.00 |    100 |
|  5 | huawei  | 10000.00 |    100 |
+----+---------+----------+--------+
5 rows in set (0.00 sec)

mysql> select * from indent;
+-----------+------+------+
| indent_id | gid  | num  |
+-----------+------+------+
|         2 |    3 |    8 |
+-----------+------+------+
1 row in set (0.00 sec)

把订单表中的samsung购买数量由8改为16个：
mysql> update indent set num=16 where indent_id=2;
Query OK, 1 row affected (0.00 sec)

确认商品表，由于触发器存在，库存也自动做更新：
mysql> select * from goods;
+----+---------+----------+--------+
| id | name    | price    | number |
+----+---------+----------+--------+
|  1 | nokia   |  1200.00 |    100 |
|  2 | htc     |  1300.00 |    100 |
|  3 | samsung |  1400.00 |     84 |
|  4 | xiaomi  |  1500.00 |    100 |
|  5 | huawei  | 10000.00 |    100 |
+----+---------+----------+--------+
5 rows in set (0.00 sec)

示例4

创建触发器实现
–单个商品购买数量不能超过5个，例如10,12等等都是非法的
–如果购买的数量超过5个，则强制设置购买5个
(时间：before，事件：insert)

delimiter $
create trigger indent_bi_tri
before insert
on indent for each row
begin
    if new.num>5 then
        set new.num = 5;
    end if;
end $
delimiter ;

购买30个htc手机
mysql> insert into indent values (null,2,30);
Query OK, 1 row affected (0.01 sec)

由于触发器存在，发现最后只买了5个：
mysql> select * from indent;
+-----------+------+------+
| indent_id | gid  | num  |
+-----------+------+------+
|         2 |    3 |   16 |
|         3 |    2 |    5 |
+-----------+------+------+
2 rows in set (0.00 sec)

mysql> select * from goods;
+----+---------+----------+--------+
| id | name    | price    | number |
+----+---------+----------+--------+
|  1 | nokia   |  1200.00 |    100 |
|  2 | htc     |  1300.00 |     95 |
|  3 | samsung |  1400.00 |     84 |
|  4 | xiaomi  |  1500.00 |    100 |
|  5 | huawei  | 10000.00 |    100 |
+----+---------+----------+--------+
5 rows in set (0.00 sec)

查看和删除

查看：
① show triggers\G;
② information_schema 数据库的triggers数据表存储的都是触发器

注意事项

1. 相同的数据表，相同时间、相同事件的触发器只能创建一个，例如对表account创建一个before insert触发器，那么如果对表account再次创建一个before insert触发器，mysql将会报错，此时只可以在表account上创建after insert或before update类型的触发器。
2. 及时删除不需要的触发器，如果需求发生变化，而触发器并没有相应的改变或删除，其仍会执行，从而影响数据的完整性
3. 删除数据表时，mysql会同时删除该数据表的触发器
4. 适当使用，使用过多会造成数据库及应用程序的维护困难

MySQL编程基础

标签：判断   ...   程序   ons   推荐   -o   工作量   存在   好的