MySQL （五）创建删除触发器、用户、事务

关注

已于 2024-03-18 15:27:25 修改 · 1.3k 阅读

原创 ·

本内容遵循 CC 4.0 BY-SA 版权协议

· GEO检测

触发器编程-创建（CREATE TRIGGER）、删除（DROP TRIGGER） 2、一个触发器只适用于一个表，每个表最多只能有3个触发器，分别是INSERT触发器、UPDATE触发器和DELETE触发器。（3）INSTEAD OF：当对触发器作用的表执行INSTEAD OF后面指定的操作时，仅执行触发器程序。2、对S表创建一个级联删除的触发器，要求删除s表中记录时要把SC表中相应的选课记录也删除。当对定义了触发器的关系表中做数据更新操作时，系统会自动执行相应的触发器，不需要人为执行。由于INSERT或UPDATE语句的执行而导致要加到该触发器作用的表中去的所有新行。阅读详情

五、触发器

指在insert、update、delete之前或是之后，触发并执行触发器中的SQL语句

old引用原来的数据内容，new引用新的数据内容

1.创建触发器

代码：

CREATE DEFINER=`root`@`localhost` TRIGGER `test`.`total_AFTER_DELETE` #触发器名 total_AFTER_DELETE
AFTER DELETE ON `total` #列total发生删除之后
FOR EACH ROW #行级触发器
BEGIN
	delete from test.section where id = old.id; #函数体
END

另一个文件中运行：

delete from test.total where id = "2"

a.insert

#数据准备
create database test;
use test;
create table total  (
`id` int primary key, 
`dept` int,
`age` int,
`height` float ,
`university` varchar(20)
)engine = InnoDB charset = utf8mb4 collate = utf8mb4_bin;

insert into `total` values(1,3500,18,182,"CUMT");
insert into `total` values(2,3522,23,167,"JNU");

use test;
create table userlogs( #日志表
	id int(11) primary key auto_increment,
	operation varchar(20),
    old_id int(11), 
    new_id int(11) 
)engine=innodb default charset = utf8;

依据上面截图的步骤写到对应的位置，在MySQL workbench里直接写运行总是报错

至少我是这样，如果有办法直接在代码区写，请戳我修改

create trigger to_user_insert #触发器名称为to_user_insert
after insert on `total` #表total插入时触发
for each row 
begin 
	insert into userlogs values(null,'insert',null,new.id);
end;

#插入数据
use test;
insert into `total` values(7,3513,25,198,"CUMT");
insert into `total` values(8,3514,23,168,"CUMT");

日志表userlogs结果：列id表示进行两次插入，列new_id表示两次插入的数据的id分别为7和8

（7和8对应表total中新插入数据的id）

只有new_id，没有old.id

表total的结果：

b.update

create trigger to_user_update 
after update on `total` #表total更新时触发
for each row 
begin 
	insert into userlogs values(null,'update',old.id,new.id);
end;

#更新数据
use test;
update total set age = 9 where id = 7; #更新年龄
update total set id = 10 where id = 8; #更新id

日志表userlogs结果：最后一行，对id数据进行更新，更新前为8，更新后为10

同时拥有old.id和new_id

c.delete

create trigger to_user_delete
after delete on `total` #表total删除时触发
for each row 
begin 
	insert into userlogs values(null,'delete',old.id,null);
end;

#删除数据
use test;
delete from total where id = 7;
delete from total where id = 10;

日志表userlogs结果：有old.id，无new_id

2.删除触发器

drop trigger total_AFTER_DELETE; #触发器名

六、用户

1.创建用户

create user 'boxieyu'@'localhost' #用户名@主机名 仅当前主机访问
identified by '12346'; #密码

create user 'boxieyu'@'%' #任意主机访问
identified by '12346'; #密码

2.修改用户

#修改用户名
rename user 'boxieyu'@'localhost' to 'boxiansi'@'localhost' #原用户名 to 新用户名

#修改用户密码
alter user 'boxiansi'@'localhost' identified by '654321'

3.删除用户

drop user 'boxiansi'@'localhost'

4.查询权限

show grants for 'boxieyu'@'%'

5.授予权限

grant all on test.* to 'boxieyu'@'%' 
#grant 权限名称 on 库名.表名 to 用户名@主机名

权限名称如下：

6.撤销权限

revoke all on test.* from 'boxieyu'@'%'

七、事务

一组操作，要成功都成功，要失败都失败

1.查看、修改事务提交方式

#查看事务提交方式，1表示自动提交，0表示手动提交
select @@autocommit;
#修改事务提交方式
set @@autocommit = 0;

2.提交事务

#数据准备
create database test;
use test;
create table money  ( 
`name` varchar(20) primary key, 
`cny` int
)engine = InnoDB charset = utf8mb4 collate = utf8mb4_bin;

insert into `money` values('boxiansi',2000);
insert into `money` values('boxieyu',2000);

初始数据：

start transaction; #开启事务
select * from money where name = 'boxiansi';
update money set cny = cny - 100 where name = 'boxiansi';
update money set cny = cny + 100 where name = 'boxieyu';
commit #提交事务

运行结果：

3.回滚事务

start transaction; #开启事务
select * from money where name = 'boxiansi';
update money set cny = cny - 100 where name = 'boxiansi';
update money set cny = cny + 100 where name = 'boxieyu';
rollback #回滚事务，回到事务起点
commit; #提交事务

运行结果：

4.设置停止点

start transaction; #开启事务
select * from money where name = 'boxiansi';
update money set cny = cny - 100 where name = 'boxiansi';
savepoint stop1; #设置停止点
update money set cny = cny + 100 where name = 'boxieyu';
rollback to stop1; #回滚事务，回滚到停止点stop1
commit; #提交事务，不提交事务原数据表不会产生任何变化

运行结果：回滚到停止点stop1，即仅执行第一步将'boxiansi'账户减少100

其他：并发事务与事务隔离级别

脏读、不可重复读和幻读的概念具体参照：（文字真的不好描述）

55. 基础-事务-并发事务演示及隔离级别_哔哩哔哩_bilibili

#查看隔离级别
select @@transaction_isolation;
#修改当前窗口隔离级别
set session transaction isolation level read committed; #read committed是新的隔离级别

标签

#mysql #数据库

MySQL触发器基本用法浅谈：创建、查看、删除等本文结合实例形式分析了mysql触发器的基本创建、查看、删除等相关使用方法与注意事项；讲述了MySQL触发器基本用法。阅读详情