mysql-高级功能(触发器、存储过程、视图、事务)

目录

• 触发器-created trigger
  • 1.触发器分为六种情况
  • 2.语法结构
  • 3.创建触发器
  • 4.查询/删除触发器
• 存储过程-created procedure
  • 1.创建存储过程
  • 2.使用存储过程
  • 3.查看存储过程
  • 4.修改存储过程
  • 5.删除存储过程
• 视图-created view
  • 1.视图命名/说明
  • 2.视图创建/使用
• 事务-start transaction
  • 1.事务四大特性
  • 2.开启事务-语法结构
  • 3.事务的undo redo机制

触发器-created trigger

触发器就是当达到某种条件自动触发
当你在对数据进行增删改的情况下会自动触发触发器，执行代码

1.触发器分为六种情况

之前：before 之后：after

增加前、增加后 before insert 、 after insert

删除前、删除后 before delete 、after delete

修改前、修改后 before update 、after updata

2.语法结构

delimiter $$
created trigger trigger_name
{before | after} {insert | update | delete}
ON table_name
[FOR EACH ROW]
when (condition)
begin
    -- 触发器执行的操作
end $$
delimiter ;

解释：
delimiter $$ //将mysql默认的结束符由;换成$$,因为中间需要用到分号；
delimiter ; //结束之后记得再改回来，不然后面结束符就都是$$了
trigger_name:是触发器的名称
table_name:是要操作的表的名称
BEFORE或after，insert、update、delete:事件
for each row ：行级触发器（sql影响一行j就触发一次）
for each statement : 语句级触发器（默认，一条sql触发一次）
old:行级，表示触发器执行之前字段,old.age
new:行级，表示触发器执行之后字段,new.age
old_table:语句级，更新前，old_table.age
new_table:语句级，更新后，new_table.age

3.创建触发器

insert-新增触发

假设我们有一个名为orders的表，其中包含订单信息，我们想要在每次向该表插入数据时自动向另一个名为order_log的表中插入一条日志记录。
首先，我们需要创建order_log表：
created table order_log (
    id INT primary key,
    order_id INT,
    action VARCHAR(50),
    createdd_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
创建触发器：每当向orders表插入一条数据时，就会自动向order_log表中插入一条日志记录
--当某个表数据新增时触发
created trigger insert_order_log
after insert on orders
for each row
begin
    insert INTO order_log (order_id, action)
    VALUES (NEW.id, 'Order createdd');
end;

update-更新触发

--当某个字段被更新前触发
created trigger orders
after update of 字段 ON orders
FOR EACH ROW
begin
    insert INTO order_log (order_id, action)
    VALUES (NEW.id, 'Order createdd');
end;

--创建一个触发器:库存数量>0表示正常，库存数量<=0表示库存不足通知补货
created trigger orders
after update of 库存余额字段 ON orders
FOR EACH ROW
when new.库存余额字段<0 and old.库存余额字段 >=0
begin
    insert into 补货表;
end;

4.查询/删除触发器

drop trigger trigger_name; //删除
SHOW triggerS; //查询

//在 MySQL 中，所有触发器的信息都存在 information_schema 数据库的 triggers 表中
SELECT * FROM information_schema.triggers where trigger_name= '触发器名';

由运行结果可以看到触发器的详细信息。对以上显示信息的说明如下：
trigger_SCHEMA 表示触发器所在的数据库；
trigger_NAME 表示触发器的名称；
EVENT_OBJECT_TABLE 表示在哪个数据表上触发；
ACTION_STATEMENT 表示触发器触发的时候执行的具体操作；
ACTION_ORIENTATION 的值为 ROW，表示在每条记录上都触发；
ACTION_TIMING 表示触发的时刻是 after；

存储过程-created procedure

内部封装了操作数据库的sql语句,后续想要实现相应的操作 只需要调用存储过程即可

1.创建存储过程

#创建两数之和存储过程
delimiter $$
created procedure test2 (IN num1 INT, IN num2 INT, OUT sum INT)
begin
  SET sum = num1 + num2;
end $$
delimiter ;


解释：
delimiter $$ :修改mysql的结束';'这个符号，因为下面会用到分号
delimiter ;  :在把mysql结束符号改回来。
in :表示这个参数必须只能是传入不能被返回出去
out:表示这个参数可以被返回出去
inout:表示即可以传入也可以被返回出去

2.使用存储过程

set @num1 = 5;
set @num2 = 10;
call sum_two_numbers(@num1, @num2, @sum);
selecte @sum;

3.查看存储过程

show procedure status; //查看所有
show procedure status where db='jeff'; //查看某个库的的存储过程
show created procedure sum_two_numbers; //查看指定名字的存储过程

4.修改存储过程

ALTER PROCEDURE procedure_name [characteristics...]
begin
  -- 存储过程主体
end;

5.删除存储过程

drop procedure if exists procedure_name; //删除存储过程

视图-created view

1.视图命名/说明

新建的视图名：view_表1_表2 （一般根据命名规范）

注：
1.视图只有表结构,视图中的数据还是来源于原来的表
2.不要改动视图表中的数据
3.一般情况下不会频繁的使用视图来写业务逻辑

1.什么是视图？
一个查询语句的结果是一张虚拟表,将这种虚拟表保存下来，它就变成了一个视图

2.为什么要使用视图？
当平凡需要使用到多张表的连表结果，你就可以事先生成好视图之后直接调用即可，避免反复写连表操作的sql语句。

2.视图创建/使用

created view 新建的视图名 as 连表

eg:
created view view_user_bike as select * from user inner join bike on bike_id = user.bike_id

使用：
select * from view_user_bike

事务-start transaction

事务包含一大堆sql语句，这些sql语句要么同时成功，要么一个也别想成功

事务的作用：保证了数据操作的安全系

案例：用交行的银行卡操作建行的ATM机给工行的账户转账1000万

当交行的银行卡钱扣除1000万，建行的ATM机告诉工行给转账账号加1000万。在过程中，如果建行的ATM机突然故障，失去了消息，那么钱扣除了却没有到账。

1.事务四大特性

A.原子性 C.一致性 I：隔离性 D：持久性

原子性（atomicity）:一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
一致性（consistency）:事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。 （总量不变）
隔离性（isolation）:一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（durability）:持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

2.开启事务-语法结构

1.# 修改数据之前先开启事务操作
start transaction;
2.# 修改操作
sql语句
3.# 回滚到上一个状态,未保存。数据在内存中
rollback;
4.# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit;  # 相当于保存，数据刷到硬盘

注：
rollback 在没有永久性更改之前，都可以回滚。就跟没有保存之前的撤回一样。
commit 就更保存数据一样

3.事务的undo redo机制

MySQL中的Undo机制是通过事务日志实现的，也称为Undo日志。每个事务在开始时都会创建一个Undo日志，该日志记录了所有更改的逆操作，以便在需要时可以回滚事务。

当一个事务被回滚时，MySQL使用Undo日志来撤消该事务中所有的更改，并将数据库恢复到事务开始的状态。

Redo机制则通过重做日志来实现。每个事务完成时，MySQL将事务的所有更改记录在重做日志中。如果需要重做某个事务，MySQL可以使用重做日志来恢复事务中的所有更改。

在MySQL中，可以使用以下命令开启或关闭事务日志：
SET GLOBAL innodb_undo_log_truncate=ON; -- 开启事务日志
SET GLOBAL innodb_undo_log_truncate=OFF; -- 关闭事务日志
需要注意的是，开启事务日志可能会影响性能，因此应该谨慎使用。另外，事务日志和重做日志可能会占用大量的磁盘空间，因此应该定期清理。

undo:事务日志，也称为undo日志，记录了事务中所有的更改操作，可以使用事务日志来恢复丢失的数据。可以使用 mysqlbinlog 工具来解析事务日志文件，并将其中的更改操作应用到数据库中。

redo:重做事务