编辑代码

create database test;
use test;

create table t_press(
    press_name varchar(50) primary key,
    order_num varchar(50),
    press_call varchar(50),
    zip_code varchar(50) ,
    address varchar(50)
);

create table t_books_p(
    b_code varchar(50) primary key,  
    type varchar(50),
    b_name varchar(50),
    location varchar(50),
    b_number int,
    pub_date date,
    press_name varchar(50) not null,
    foreign key (press_name) references t_press(press_name)
);

create table t_reader(
    card_num varchar(50) primary key,    
    r_name varchar(50),  
    r_call varchar(50),       
    unit varchar(50),         
    have_num int
);

create table t_op(
    b_code varchar(50) not null,
    card_num varchar(50) not null,
    date date,
    operate enum('借','还'),
    primary key(b_code,card_num,operate,date),
    foreign key(b_code) references t_books_p(b_code),
    foreign key(card_num) references t_reader(card_num)
);

delimiter ##

create trigger book_op
after insert on t_op
for each row
begin
    if new.operate='借' then
        update t_books_p set b_number=b_number-1 where b_code=new.b_code;
        update t_reader set have_num=have_num+1 where card_num=new.card_num;
    else
        update t_books_p set b_number=b_number+1 where b_code=new.b_code;
        update t_reader set have_num=have_num-1 where card_num=new.card_num;
    end if;
end##

create procedure unreturned()
begin  
    select datediff(now(), op.date)-30 as over_days, op.b_code, op.card_num, r_name, r_call 
    from t_op op, t_reader
    where op.card_num=t_reader.card_num and op.operate = '借' and not exists (
        select * 
        from t_op r 
        where r.b_code = op.b_code and r.card_num = op.card_num and r.operate = '还'
    );
end##

create procedure show_num(in type varchar(50))
begin  
    set @rank := 0;
    select t_op.b_code, count(t_op.b_code) as borrow_times, @rank := @rank + 1 as rank
    from t_op, t_books_p
    where t_op.b_code = t_books_p.b_code and t_books_p.type = type and operate='借'
    group by t_op.b_code
    order by borrow_times desc;
end##

create procedure borrow_situation(in b_code varchar(50))
begin  
    select t_op.card_num, r_name, r_call, date, operate
    from t_reader, t_op
    where t_op.b_code = b_code and t_op.card_num = t_reader.card_num
    order by date;
end##

create procedure show_book(in type varchar(50))
begin  
    select b_code, b_name, location, b_number, pub_date
    from t_books_p
    where t_books_p.type = type;
end##

create procedure reader_unreturned(in card_num varchar(50))
begin  
    select datediff(now(), t.date) as borrowed_days, t.b_code,
        case when datediff(now(), t.date) > 30 
            then (datediff(now(), t.date)-30)*0.2 
            else 0 
        end as cost_RMB
    from t_op t
    where t.card_num=card_num and t.operate = '借' and not exists (
        select * 
        from t_op r 
        where r.b_code = t.b_code and r.card_num = t.card_num and r.operate = '还'
    );
end##

delimiter ;

insert into t_press values('出版社1','001','12301','10024','5B324');
insert into t_books_p values('book01','奇幻','书1','1排2架','3','2022-3-1','出版社1');
insert into t_books_p values('book02','未来','书2','2排3架','5','2022-1-1','出版社1');
insert into t_books_p values('book03','奇幻','书3','1排3架','5','2022-2-1','出版社1');
insert into t_reader values('card01','lisa','32101','5B101','0');
insert into t_reader values('card02','tom','32102','5B102','0');
insert into t_op values('book01','card01','2023-4-12','借');
insert into t_op values('book02','card01','2023-4-12','借');
insert into t_op values('book01','card01','2023-5-10','还');
insert into t_op values('book01','card02','2023-5-12','借');
insert into t_op values('book03','card02','2023-5-12','借');
select * from t_press;
select * from t_books_p;
select * from t_reader;
select * from t_op;