DataBase Experiment HW08

1

查询每个银行内的存款总额（不包括贷款、保险）、所有贷款的总额、存款最多的存款金额、贷款总额与存款总额之比；
输出：branch_id，存款总额，贷款总额，存款最多的存款金额，贷款总额:存款总额
示例：
±---------------±---------------±-------------±--------------±-----------+
| OPEN_BRANCH_ID | saving | loans | maxs | precent |
±---------------±---------------±-------------±--------------±-----------+
| 2 | 231201772.6167 | 0.0000 | 99021770.1967 | 0.00000000 |
±---------------±---------------±-------------±--------------±-----------+

注：该示例不是标准答案

SELECT t.*, t.loans/t.saving as ratio from(
SELECT a.OPEN_BRANCH_ID,sum(if(pp.name='存款',a.AVAIL_BALANCE,0)) as deposits,
sum(if(pp.name='贷款',a.AVAIL_BALANCE,0)) as loans,
max(if(pp.name='存款',a.AVAIL_BALANCE,0)) as max_deposit
from account a 
JOIN PRODUCT p on p.PRODUCT_CD = a.PRODUCT_CD 
join product_type pp on pp.product_type_cd=p.product_type_cd
GROUP BY OPEN_BRANCH_ID
) as t 

select branch_id, saving, maxs, coalesce(loan, 0) loan, ( ifnull(sec.loan, 0) / fir.saving) as percent from 
(
	(select BRANCH_ID, sum(avail_balance) saving, max(avail_balance) maxs
	from (branch b, account a, product p) 
	where b.branch_id = a.open_branch_id
	and a.product_cd = p.product_cd
	and p.product_type_cd = 'ACCOUNT'
	group by BRANCH_ID) as fir
left outer join 
	(
	select BRANCH_ID, IFNULL(sum(avail_balance), 0) loan
	from (branch b, account a, product p) 
	where b.branch_id = a.open_branch_id
	and a.product_cd = p.product_cd
	and p.product_type_cd = 'LOAN'
	group by BRANCH_ID
	) as sec
using(branch_id)
);

2

请使用with rescusive 递归查询每个员工的上级领导。输出员工id、上级领导｜该员工ID。
答案示例：
±-------±------------+
| emp_id | res |
±-------±------------+
| 1 | 1 |
| 2 | 1|2 |
| 3 | 1|3 |
| 20 | 1|2|20 |
| 4 | 1|3|4 |
±-------±------------+

注：示例是标准答案部分数据

with recursive t(emp_id,res) as( 
(select emp_id ,cast(emp_id as char ) from employee where superior_emp_id is null  )
union all (select e.emp_id,CONCAT(t.res,'|',e.emp_id)as res from t,employee  e where t.emp_id = e.superior_emp_id  )) 
select * from t;

select * from employee;
select emp_id, cast(coalesce(concat(superior_emp_id, '|', emp_id), emp_id) as char(30)) as res
    from employee;
WITH recursive cte(emp_id, res) as
(
	select e.emp_id , coalesce(concat(superior_emp_id, '|', emp_id), emp_id) as res
    from employee e
    where e.superior_emp_id is NULL
    union
    select e.emp_id , concat(res, '|', e.emp_id) 
    from employee e inner join cte
    where e.superior_emp_id = cte.emp_id
)
select * from cte;

3

利用游标修改account表的AVAIL_BALANCE；如果该金额是属于存款，则按利息每年利息0.00001增加，以last_activity_date开始计算。
注：先更新一下account表
update account set last_activity_date=‘2022-11-11’ where last_activity_date is null;
提示：使用函数 timestampdiff

CREATE DEFINER = `root` @`localhost` PROCEDURE `proc_cursor2` () DETERMINISTIC BEGIN
DECLARE done INT DEFAULT ( 0 );
DECLARE accountid INT;
DECLARE  n INT;
DECLARE  i INT;
DECLARE ans DECIMAL ( 12, 4 );

DECLARE cur_1 CURSOR FOR SELECT
ACCOUNT_ID ,AVAIL_BALANCE FROM account;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' 
SET done = 1;

OPEN cur_1;

REPEAT
	set i = 0;
	FETCH cur_1 INTO accountid,ans;
	
	SELECT timestampdiff(YEAR,last_activity_date,now()) into n
	FROM account  WHERE ACCOUNT_ID = accountid;
	
	WHILE i < n DO
		set ans = ans * 0.00001;
		set i = i+1;
	END WHILE;
	
	UPDATE account 
	SET AVAIL_BALANCE = ans 
	WHERE
	account.ACCOUNT_ID = accountid;
	UNTIL done 
END REPEAT;
CLOSE cur_1;
END 

SET SQL_SAFE_UPDATES = 0;
update account set last_activity_date='2022-11-11' where last_activity_date is null;

select * from account;
update account set avail_balance = 10000 where avail_balance is null;
desc account;


drop procedure  if exists updateAB;
delimiter $$
create procedure updateAB()
begin
	declare ai int;
	declare ab decimal(12, 4);
    declare lad date;
    declare dif int;
	
    declare cur cursor for
		select account_id, avail_balance, last_activity_date
        from account;
	DECLARE exit HANDLER FOR NOT FOUND CLOSE cur;
    
    open cur;
    repeat 
		fetch cur into ai, ab, lad;
		set dif = timestampdiff(year, lad, NOW());
        
			while(dif > 0) do
				set ab = ab * 1.00001;
				set dif = dif - 1;
			end while;
	-- select dif;
         update account set avail_balance = ab
         where account_id = ai;
    until null 
    end repeat;
    close cur;
end $$
delimiter ;

4

4
新建日志表

CREATE TABLE `logs` (
 `Id` int(11) NOT NULL AUTO_INCREMENT,
 `log` varchar(255) DEFAULT NULL COMMENT '日志说明',
 PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='日志表';

在players表(第七次作业中已建好的表)中新建update触发器：

1. 更新了players表数据就要添加一条日志数据信息。如果有数据被更改，则在logs表中增加一条元组，具体格式为：“列名” + “该列旧数据” + “ is updated to ” + “该列新数据;”；如果该列没有被更新，则无需添加说明。
   注：每一次触发只能有增加一条元组。
2. 如果某个player转会，其team_id会做相应更改，在team表中的nums（球队人数）也要需要更新。

写好触发器后，请依次运行如下SQL语句，检查触发器运行结果：

 SELECT * from teams;
 UPDATE players set salary = 10*salary,team_id=3 WHERE `name`="姚明";
 SELECT * from teams;
 SELECT * from logs;

DROP TRIGGER IF EXISTS `player_log`;
delimiter;;
CREATE TRIGGER `player_log` AFTER UPDATE ON `players` FOR EACH ROW
BEGIN 
DECLARE s1 VARCHAR ( 1000 ) CHARACTER 
SET utf8;
DECLARE
	s2 VARCHAR ( 20 ) CHARACTER 
	SET utf8;# 为避免中文字符编码出现乱码，这里需要设置字符集
DECLARE
	res VARCHAR ( 40 ) CHARACTER 
	SET utf8;
SET s2 = " is updated to ";
SET s1 = "";# 依次检测一条update操作是否对player的三个属性进行变更（id除外）
IF
	old.NAME <> new.NAME THEN	
		SET s1 = CONCAT( s1, "name ", old.NAME, s2, new.NAME, "; " );
END IF;
IF
	old.salary <> new.salary THEN		
		SET s1 = CONCAT( s1, "salary ", old.salary, s2, new.salary, "; " );
END IF;
IF
	old.team_id <> new.team_id THEN		
		SET s1 = CONCAT( s1, "teams_id ", old.team_id, s2, new.team_id, "; " );
	UPDATE teams 
	SET nums = nums - 1 
	WHERE
		teams.id = old.team_id;
	UPDATE teams 
	SET nums = nums + 1 
	WHERE
		teams.id = new.team_id;
END IF;
INSERT INTO LOGS(log) VALUES(s1);
END;
;;
delimiter;

drop trigger if exists update_player ;
delimiter $$
create trigger update_player
after update
on players
for each row 
begin
	set @temp = '';
    if old.id != new.id then
		set @temp = concat( @temp, "id ", old.id, " is update to ", new.id, '; ');
    end if;
	if old.name != new.name then
		set @temp = concat( @temp, " name ", old.name, " is update to ", new.name, '; ');
    end if;
    if old.salary != new.salary  then
		set @temp = concat( @temp, " salary  ", old.salary , " is update to ", new.salary , '; ');
    end if;
	if old.team_id != new.team_id  then
		set @temp = concat( @temp, " team_id  ", old.team_id , " is update to ", new.team_id , '; ');
        update teams set teams.nums = teams.nums + 1 where teams.id = new.team_id;
		update teams set teams.nums = teams.nums - 1 where teams.id = old.team_id;
    end if;
    
    if @temp != "" then
		insert into logs(log) values(@temp);
    end if;
    
end $$
delimiter ;