存储过程
存储过程概念
与函数的区别
相同点
1.存储过程和函数目的都是为了可重复的执行操作数据库的sql语句的集合
2.存储过程和函数都是一次编译,后续执行.
不同点
1.标识符不同:函数为function,过程为procedure
2.函数有返回值且必须返回,过程没有返回值
3.过程无返回值类型,不能将结果直接赋值给变量;函数有返回值类型,调用时,除在select中,必须将返回值赋给变量.
4.函数可以在select语句中直接使用,而过程不能:函数是使用select调用,过程不是.
存储过程操作
创建过程
基本语法:
delimiter 新语句结束符
create procedure 过程名字([参数列表])
begin
过程体;
end
新语句结束符
delimiter 分号
(如果过程体只有1条语句,可以省略begin和end)
查看过程
- 查看全部存储过程: show procedure status [like 'pattern'];
- 查看过程创建语句: show create procedure 过程名;
调用过程
语法: call 过程名([实参列表]);
删除过程
语法: drop procedure 过程名;
存储过程的形参类型
存储过程也允许提供参数:需要指定数据类型
存储过程对参数还有额外的要求,自己的参数分类.
in
入口参数:过程外部递交的参数.可以是变量也可以是数据.
out
出口参数 :过程内部产生的变量,输出给外部(使用)--必须是变量
如果out变量在外部有值,那么进入过程之后第一件事就是被清空设为NULL
inout
入口出口参数:数据从外部递交,在过程中处理(改变)后又输出给外部(使用)
参数使用级别语法(形参)
过程类型 变量名 数据类型; // in var_a int
调用函数查看结果:
现在我们在函数外部 select @n1,@n2,@n3;
由于int_2为out,int_3位inout类型.函数结束的时候(执行到函数的end语句)将内部的int_2(100),int_3(1000)值输出赋值给形参对应的外部变量.
而形参int_2(out类型), int_3(inout类型)对应的实参变量为@n2,@n3.因此@n2为100,@n3为1000.
@n1为'a'不变(仍旧为函数中赋的值'a')
触发器
触发器概念
基本概念
触发器是一种特殊的存储过程.触发器主要通过事件进行触发执行的.
触发器:trigger,类似js中的事件. 提前给某张表的所有记录(行)绑定一段代码.如果改行的操作满足条件(触发),这段代码就会自动执行.
作用
1.可以在写入数据表前,强制检验或转换数据(保证数据安全)
2.触发器发生错误时,异动的结果会被撤销.(如果触发器执行错误,那么前面用户已经成功执行的操作也会被撤销:事务安全)
3.部分数据库管理系统可以针对DDL数据定义语言使用触发器,称为DDL触发器.
4.可依照特定的情况,替换异动的指令(instead of).==>mysql不支持
触发器优缺点
优点
1.触发器可通过数据库中的相关表实现级联更改.(如果某张表的数据改变,可以利用触发器来实现其他表的无痕操作(用户不知道))
2.保证数据安全:进行安全校验
缺点
1.对触发器过分的依赖,势必影响数据库的结构,同时增加了维护的复杂度
2.造成数据在程序层面不可控.(PHP层)
触发器基本语法
创建触发器
create trigger 触发器名字 触发时机 触发事件 on 表 for each row
begin
end
触发对象: on 表 for each row, 触发器绑定实质是表中的所有行,因此当每一行发生指定的改变的时候,就会触发触发器.
触发时机
触发时机:每张表中对应的行都会有不同的状态,当sql指令发生的时候,都会令行中数据发生改变.每一行总有2种状态:数据操作前和操作后.
- before: 在表中数据发生改变前的状态
- after: 在表中数据已经发生改变后的状态
触发事件
触发事件:mysql中触发器针对的目标是数据发生改变,对应的操作只有写操作(增删改)
- insert:插入操作
- update:更新操作
- delete:删除操作
注意事项
一张表中,每一个触发时机绑定的触发事件对应的触发器类型只能有1个.
因此一张表中最多有6个触发器:before insert, before update, before delete, after insert, after update, after delete.
需求:有两张表,一张商品表,一张订单表(保留商品ID),每次订单生成,商品表中对应的库存就应该发生变化.
1.创建两张表:商品表和订单表
2.创建触发器:如果订单表发生数据插入,对应的商品就应该减少库存
create trigger 触发器名字 after insert on my_orders for each row
注意这是个有问题的触发器,在此仅单纯为演示:
查看触发器
- 1. 查看全部触发器: show triggers;
- 2. 查看触发器创建语句: show create trigger 触发器名字;
触发触发器
激活触发器
1.表为my_orders
2.发生插入数据操作
删除触发器
语法:drop trigger 触发器名字;
触发器实际应用
记录关键字:new, old
触发器针对的是数据表中的每条记录(每行) ,每行在数据操作前后都有对应状态.
触发器在执行之前就将对应的状态获取,将没有操作之前的状态(数据)都保存到old关键字中,而操作后的状态都放在new关键字中.
在触发器中,可以通过old和new来获取绑定表中对应的记录数据.
基本语法: 关键字.字段名
old和new并不是所有触发器都有:
insert:插入前为空,因此没有old状态
delete:删除后为空,因此没有new状态
商品自动扣除库存
验证结果,正确:
思考:如果库存数量没有商品订单中数量多怎么办?(这种情况下应该先判断,如果库存少于订单中数量,应取消本次下单操作)
操作目标:订单表
操作时机:下单前
操作事件;插入数据
总结:老师的讲座遗漏了mysql中的索引.大家可以在B站搜索下mysql讲座中的索引部分.索引非常重要.没有建立索引的搜索和建立索引的搜索完全是两个级数.
现在我们学习的只是mysql粗浅的知识.推荐继续在B站观看尚硅谷的msyql基础+高级教程(里面有讲索引).或者在B站搜索DBA,学习mysql更深层次的东西.还有mysql的一些原理,避免采坑.还要懂得它的优化之类
更高层次的还有集群之类的.我们总不可能将来建立的网站就只有1个物理主机吧?
当然也可以推后在学,我们现在也可以返回PHP讲座继续学习PHP.
