触发器&分支结构&函数 - 《SQL》

触发器:
触发器: tigger,事先为某张表绑定好一段代码，当表中的某些内容发生改变的时候(增删改)系统会自动触发代码，执行。

触发器:事件类型,触发时间,触发对象
事件类型：增删改，三种类型insert，delete，update
触发时间：前后，before，after
触发对象：表中的每一条记录(行)

一张表中只能拥有一种触发时间的一种类型的触发器，一张表最后6个触发器。

创建触发器:
在mysql高级结构中：没有大括号，都用对应的字符符号代替
触发器基本语法：
— 临时修改语句结束符
Delimiter 自定义符号：后续代码中只有遇到这个符号才会结束
Create trigger触发器名字触发时间事件类型 on 表名for each row
Begin — 开始
— 里面为触发器的内容，每行内容必须使用语句结束符;
End — 结束
— 语句结束符
自定义符号

—临时修改修正回来
Delimiter ;

delimiter
create trigger after_order AFTER INSERT ON my_order for EACH ROW
BEGIN — 开始
— 触发器内容开始
UPDATE my_goods set inv = inv-? where id =? ;
END
— 结束触发器
— 修正临时语句结束符
delimiter ;

查看触发器：
查看所有触发器或者模糊匹配
Show triggers(like ‘pattern’);

可以查看触发器创建语句
show create trigger 触发器名称
所有触发器都存在一张表中information_schema.triggers

使用触发器：
触发器：不需要手动调用，而是当某种情况发生时，自动触发

修改触发器&删除触发器

触发器不能修改，只能先删除后新增
drop trigger 触发器名称

触发器记录
触发器记录：不管触发器是否触发，只要当某种操作准备执行，系统就会将当前要操作的记录的当前状态和即将执行之后的新的状态分别保留下来，供触发器使用。其中要操作当前状态保存在old中，操作之后的可能形态保存在new中。

old代表的是旧记录，new代表的是新纪录。
删除的时候没有new，插入的时候没有old。

old和new都是代表记录本身，任何一条记录除了有数据，还有字段名
使用放松：old.字段名 new.字段名(new代表的是假设发生之后的结果)

生成订单
delimiter
create trigger after_order AFTER INSERT ON my_order for EACH ROW
BEGIN — 开始
— 触发器内容开始 old没有 new新订单的库存
UPDATE my_goods set inv = inv-new.g_number where id = new.g_id;
END
— 结束触发器
— 修正临时语句结束符
delimiter ;

代码执行结构:
代码执行结构有三种，顺序结构，分支结构，循环结构

分支结构
分支结构:实现准备多个代码块,按照条件选择性执行某段代码.

在mysql中只有if分支

基本语法
if 条件判断 then
— 满足条件要执行的代码
else
— 不满足条件执行的代码
End if;

判断库存

触发器，判断库存是否足够，够则生成，不够不生成。

— 触发器：订单生成之前要判断库存是否满足
— 修改语句结束符
delimiter %%
create trigger before_order before INSERT on my_order for each ROW
BEGIN
— 判断库存是否足够

    -- 获取商品库存<br />     SELECT inv from my_goods where id = new.g_id into @inv;
    -- 比较库存<br />        if @inv < new.g_number THEN<br />                -- 库存不够 触发器没有提供事件发生的能力，只能暴力报错<br />             insert into xxx VALUES(xxx);<br />        end if;<br />END<br />%%<br />delimiter ;<br />-- 插入多余订单<br />INSERT into my_order values(null,1,200);

循环结构：

While(没有for循环)

While 条件判断 do
— 满足条件要执行的代码
— 变更循环条件
End while;

循环控制：在循环内部进行循环判断和控制
MySQL 中没有对应continue和break但有其他
iterate:迭代，类似 continue,后面的代码不执行，循环重新来过
leave:离开,类似break整个循环接收

使用方式：iterate/leave 循环名字;

— 定义循环名字
循环名字：while 条件 do
— 循环体
— 循环控制
leave/iterate 循环名字;
end while;

函数
函数:将-段代码块封装到一个结构中，在需要执行代码块的时候,调用结构执行即可.(代码复用)
函数分为两类:系统函数和自定义函数

系统函数
系统定义好的函数,直接调用即可.
任何函数都有返回值，因此函数的调用是通过select调用.

MySQL中，字符串的基本操作单位(最常见的是字符)
SET @cn = ‘时间你好’; SET @en = ‘hello times’;
substring:
MySQL下标从1开始，字符为单位，没有中文乱码问题从1开始选1位
select SUBSTRing(@cn,1,1);select SUBSTRing(@en,1,1);
char_length ，字符长度 length，字节长度
select CHAR_LENGTH(@cn),LENGTH(@cn),CHAR_LENGTH(@en),LENGTH(@en);

instr：判断字符串是否在某个具体的字符串中存在,存在返回位置
select instr(@cn,’间’),instr(@cn,’不’),
instr(@en,’ti’),instr(@cn,’bu’);
0代表未找到

lpad：左填充，将字符串按照某个指定的填充方式填充到指定长度(字符单位)
select lpad(@cn,20,’欢迎’),lpad(@en,20,’hello’)
触发器&分支结构&函数 - 图1
一共20个字符。填充和原字符一共20个。

Insert：替换，找到目标位置，指定长度的字符串,替换成目标字符串
select INSERT(@en,3,3,’y’),@en; 从那里开始a，多少位b，替换为c
触发器&分支结构&函数 - 图2

strcmp：compare字符串比较
set @f = ‘hello’; set @s = ‘hey’; set @t = ‘HEY’;
SELECT STRCMP(@f,@s),STRCMP(@s,@f),STRCMP(@s,@t);
触发器&分支结构&函数 - 图3
前者小返回-1，大返回1，等于返回0。不区分大小写

自定义函数

函数要素:函数名,参数列表(形参和实参),返回值,函数体(作用域)

创建函数

创建语法：
Delimiter
create function 函数名(形参列表) returns 数据类型
— 规定要返回的参数类型
begin
— 函数体
— 返回值：return 类型(指定数据类型)
end

delimiter ;

create function display1() RETURNS INT
BEGIN — 只有一条语句可以省略begin end delimiter
return 100;
end

自定义函数与系统函数的调用方式是一样的 select 函数名(参数);

查看函数
查看所有函数：show function status(like‘pattern’);

函数属于数据库，只有在指定的数据库下才可调用。

查看函数的创建语句： show create function 函数名称;

修改函数&删除函数
先删除后新增，不能修改。
drop function 函数名;

函数参数

参数分为两种：定义时的参数叫形参,调用时的参数叫实参(实参可以是数值也可以是变量)
形参：要求必须指定数据类型。

@变量定义的为全局变量，没有的可以理解为局部变量
任何变量要修改必须用set

select @sum,@i;
在函数外部也可以使用。外部定义的内部也能直接使用。
declare i int default 1;
在插件中不需要声明delimiter

— 创建函数：计算1-指定数之间的和
delimiter
create function display2(int_1 int) returns INT
BEGIN
set @i =1; — @变量定义的为全局变量，没有的可以理解为局部变量
set @sum = 0;
while @i<=int_1 DO
— 任何变量要修改必须用set
set @sum = @sum + @i;
— 修改循环变量
set @i = @i +1;
end while;
return @sum;
end

delimiter ;

select display2(11);

作用域
Mysq1中的作用域与js中的作用域完全一样
全局变量可以在任何地方使用;局部变量只能在函数内部使用.

全局变量:使用set关键字定义，使用@符号标志
局部变量:使用declare关键字声明,没有@符号。所有的局部变量的声明，必须在函数体开始之前。

declare i int default 1;

— 求和 1- 指定书之间的和，要求五的倍数不加 1
delimiter
create function display3(int_1 int) returns INT
BEGIN
— 声明变量：循环变量，结果变量
declare i int default 1;
declare sum int default 0; — 定义局部变量可以有属性
while i<= int_1 DO
if i%5!=0 then
set sum = sum + i;
end IF;
SET i = i + 1;
end WHILE;
return sum;
END

delimiter ;

— 求和 1- 指定书之间的和，要求五的倍数不加2
delimiter $$
create function display4(int_1 int) returns INT
BEGIN
— 声明变量：循环变量，结果变量
declare i int default 1;
declare sum int default 0; — 定义局部变量可以有属性
mywhile:while i<= int_1 DO

if i%5 =0 then
SET i = i + 1;
ITERATE mywhile;
end IF;

set sum = sum + i;
SET i = i + 1;
end WHILE;

return sum;

END
$$
delimiter ;