MySQL高阶—事务、触发器、存储过程
一、数据库事务
(1)数据库中的事务有四大特性:ACID
- 原子性:每个事务都是不可分割的执行,要么全部成功,要么全部失败。
- 一致性:事务执行之前和执行之后的状态保持一致。比如A有1000元,B有1000元,A给B转账500元之后,A与B的钱数之和应该还是2000元。
- 隔离性:多个事务同时操作,每个事务之间相互独立,互不影响。
- 持久性:事务提交成功后对数据库的改变是永久的。
(2)数据库三范式
数据库的范式(规范的数据表示公式):按照什么方式在数据库中表示(存储)是完全合理是做不到的,只能在数据库设计过程中尽量靠近三范式约束。
-
1NF:字段不可分割
数据库中的每一列数据,是不能再拆分的。
-
2NF:有主键,非主键字段依赖主键。
数据库中的每一条数据都是唯一的,主键作为数据唯一的描述符。
-
3NF:非关键字的任何字段属性,不能产生相互的依赖条件
不是主键的任何其他字段,不能产生相互的依赖关系。
(3)事务的隔离级别
事务的隔离级别,指多个事务同时操作数据库时,不同事务之间应该怎么定义他们的操作。
-
读未提交:read uncommitted
一个事务中,读取了另一个事务中没有提交的数据,两个事务之间造成了影响。
-
读已提交:read committed
一个事务中,读取了另一个事务中提交的数据。
-
可重复读:repeat read
在一次完整的事务中,每次读取的数据都是一致的,不会发生变化,所有提交的更新的数据都会在下一个事务中读取到。数据库默认的隔离级别
-
串行化/序列化:serializerable
所有的事务操作全部排队,依次执行
二、触发器
数据库中提供了特殊的处理方法:自动化操作,本质上是当数据库中发生了一些行为之后,导致一些其他的行为自动触发,类似python开发中的事件驱动开发。
数据库中提供了一种数据库高级对象:触发器;描述的是数据表上一个条件被触发执行的后续行为操作。
① 触发器语法:
create trigger trigger_name trigger_time trigger_event on table_name for each row trigger_stmt end;
描述:在某张表上,发生了一个触发事件,在触发事件发生之前|之后(触发时机),执行触发器中定义的要执行的程序。
trigger_time:触发时机,before|after
trigger_event:触发事件,insert|update|delete
②触发器案例:自动下单功能
-
创建测试数据表
create table goods( gid int auto_increment primary key comment '商品主键', gname varchar(20) not null comment '商品名称', gprice double not null comment '商品单价', gstock int not null comment '商品库存', ); create table goods_order( goid int auto_increment primary key comment '订单编号', goname varchar(20) comment '购买商品名称', goprice double comment '成交单价', gocount int comment '购买数量', subtotal double comment '小计金额' );
-
创建触发器
delimiter $$ -- 创建一个触发器 create trigger goods_sale_auto -- 在goods表格修改之后执行触发器 after update on goods for each row -- 要执行的程序开始操作 begin -- 声明两个变量 declare buycount int; declare subtotal double; -- 判断库存是否更新:更新前old,更新后new if new.gstock<old.gstock then --获取购买的数量 set buycount = old.gstock - new.gstock; set subtotal = buycount * old,gprice; insert into goods_order(gname,goprice,gcount,subtotal) values(old.gname,old,gprice,buycount,subtotal); end if; -- 要执行的程序完结操作 end; $$ delimiter; --触发器创建完成后,修改结束符为默认的分号
三、存储过程
触发器是数据库中根据发生的条件(某张表上发生了INSERT/UPDATE/DELETE操作)自动执行的数据库程序,当我们需要自定义程序,并且手工调用时要怎么去做?
数据库提供了另外一种高级对象:存储程序,一般称为存储过程,就是一个用户按照规范语法编写的程序代码,可以将项目中的业务逻辑封装在程序中,通过固定的语法方式直接调用执行,类似python中的函数。
(1)基本语法结构
创建存储过程
CREATE PROCEDURE proc_name ([proc_parameters]) routing_body
create procedure 固定语法:创建存储过程
proc_name:自定义存储过程名称
proc_parameters:存储过程执行需要的参数
routing_body:存储程序要执行的程序代码
(2)案例
delimiter $$
create procedure employee_avg()
begin
-- 模拟一行或多行代码
select AVG(salary) from ex01.employee
end;
$$
-- 调用存储过程
call employee_avg();
(3)带有返回值的存储过程
delimiter $$
create procedure my_employee2(out res double)
begin
-- 查询数据,并将数据保存到变量中
select AVG(salary) into res from ex01.employee
end;
$$
-- 调用存储过程
call my_employee2(@res);
--查看返回值的数据
select @res;
返回值声明在存储过程名称后面的括号:
**out:**返回数据
**in:**输入数据
**input:**既是输入数据,同时也能返回数据
调用时,需要使用变量接受数据,为了跟其他变量区分,添加@符号:call mey_employee(@res);