Mysql第三次上机

上机3
1、 基于第一次上机创建的银行数据库，创建一个函数，为所有存款账户增加1%的利息。
2、 创建一个新表branch_total，用于存储各个支行的存款总额（表中有branch_name和total_balance两个属性）。然后在这个表上，创建一个触发器，实现当有用户存款变动（包括增加、删除和更新）时，brach_total表中的存款总额跟着自动更新。

上机代码分享

/*3-1*/
use BANK337;

select * from account337;

delimiter $   /*将结束符设为$, 下面sql语句部分可能会用到; 以免冲突*/

/*创建函数*/
create function add_one_percent() returns varchar(255)
begin
update account337 set balance = balance * 1.01;
return "add successfully!";
end $
/*创建完成*/
delimiter ;

select add_one_percent();
select * from account337;

/*3-2*/
create table branch_total(
branch_name varchar(50) primary key,
balance_total numeric(10,2),
foreign key(branch_name) references branch337(branch_name)
);

/*从account中向branch_total插入数据*/
insert into branch_total(branch_name, balance_total)
(select b.branch_name, sum(b.balance)
	from account337 b 
	group by b.branch_name
);

select * from branch_total;

/*给branch_total中branch_name设置unique索引*/
alter table branch_total add unique(branch_name);

/*创建insert触发器并做测试*/
delimiter $
select * from account337$
select * from branch_total$
create trigger branch_total_insert_trigger
after insert
on account337
for each row
begin
insert into branch_total(branch_name, balance_total)
values (new.branch_name, new.balance)
on duplicate key /*如果存在则更新，不存在则插入*/
update balance_total = balance_total + new.balance;
end $

insert into account337 values('A-301','Downtown',100)$
select * from account337$
select * from branch_total$
insert into account337 values('A-400','Pownal',300)$
select * from account337$
select * from branch_total$
/*insert触发器创建及测试完成*/

/*创建update触发器并测试*/
create trigger branch_total_update_trigger
after update
on account337
for each row
begin
update branch_total 
	set balance_total = balance_total - old.balance + new.balance
	where branch_name = old.branch_name;
end $

update account337 set balance = 100 where account_number = 'A-400'$
select * from account337$
select * from branch_total$
/*update完成*/

/*创建delete触发器并测试*/
create trigger branch_total_delete_trigger
after delete
on account337
for each row
begin
update branch_total set balance_total = balance_total - old.balance
	where branch_name = old.branch_name;
end $

delete from account337 where account = 'A-301'$
select * from account337$
select * from branch_total$
/*delete完成*/

delimiter ;

上机代码含注释