MySQL简易图书管理系统实践

Aki_Francis

于 2023-03-09 20:41:00 发布

阅读量2.8k

点赞数 2

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/Aki_Francis/article/details/129429872

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

创建数据库Book_Management

create database Book_Management;
use Book_Management;

创建图书信息表

其中包含了自增长的主键book_id，以及其他相关图书信息

create table Book_Info(
    book_id int primary key auto_increment,
    book_name varchar(50) not null ,
    book_author varchar(20) not null ,
    book_price decimal(10,2) not null ,
    book_inventory int not null ,
    book_note varchar(200)
);

创建学生信息表

其中包含了学生学号等学生信息

create table Student_Info(
    student_id char(8) primary key,
    student_name varchar(20) not null ,
    student_gender char(2) not null ,
    student_age int not null
);

添加图书信息

这里我添加了三本图书，分别是《百年孤独》、《时间简史》、《活着》

insert into Book_Info(book_name, book_author, book_price, book_inventory, book_note)
values('百年孤独','海明威',40,20,'是个人都该看');
insert into Book_Info(book_name, book_author, book_price, book_inventory, book_note)
values('时间简史','霍金',62.88,12,'霍金已经死了');
insert into Book_Info(book_name, book_author, book_price, book_inventory, book_note)
values('活着','余华',52.2,9,'余华的著作');

查看图书信息表可以看到图书信息

添加学生信息

这里我添加了四个学生，分别是明明、小黑、红兰、艾热

insert into Student_Info(student_id, student_name, student_gender, student_age)
values ('20230001','明明','男',21);
insert into Student_Info(student_id, student_name, student_gender, student_age)
values ('20230002','小黑','男',21);
insert into Student_Info(student_id, student_name, student_gender, student_age)
values ('20230003','红兰','女',22);
insert into Student_Info(student_id, student_name, student_gender, student_age)
values ('20230004','艾热','男',21);

查看学生信息表可以看到学生信息

创建借书记录表

表中有记录号，自增长；借阅者的id和name、图书的id和name，借阅数量、是否归还、借阅时间，并且将相关数据进行关联

create table Borrow_Record(
    record_id int primary key auto_increment,
    borrower_id char(8) not null ,
    borrower_name varchar(20) not null ,
    borrowed_book_id int not null ,
    borrowed_book_name varchar(50) not null ,
    borrow_number int not null ,
    if_returned int not null ,
    borrow_datetime datetime not null,
    constraint FK_RECORD_STUDENT foreign key (borrower_id) references student_info(student_id),
    constraint FK_RECORD_BOOK foreign key (borrowed_book_id) references book_info(book_id)
);

实现借书过程

共有四个参数，三个输入a：学号，b：图书编号，n：借阅数量；一个输出state：输出借阅状态（1.成功 2.学生不存在 3.图书不存在 4.库存不足）

create procedure proc_borrow_book(in a char(8),in b int,in n int,out state int)
begin
    -- 判断学生是否存在
    declare i_student int default 0;
    declare i_book int default 0;
    declare i_storage int default 0;
    declare i_student_name varchar(20) default '';
    declare i_book_name varchar(50) default '';
    select count(student_id) into i_student from student_info where student_id=a;
    if i_student > 0 then-- 学生存在
        select count(book_id) into i_book from book_info where book_id=b;
        if i_book > 0 then-- 图书存在
            select book_inventory into i_storage from book_info where book_id=b;
            if i_storage >= n then-- 库存充足
                select student_name into i_student_name from student_info where student_id=a;
                select book_name into i_book_name from book_info where book_id=b;
                insert into Borrow_Record(borrower_id, borrower_name, borrowed_book_id, borrowed_book_name, borrow_number, if_returned, borrow_datetime)
                values(a,i_student_name,b,i_book_name,n,0,now());
                update book_info set book_inventory=i_storage-n where book_id=b;
            else-- 库存不足
                set state = 4;
            end if;
        else-- 图书不存在
            set state = 3;
        end if;
    else-- 学生不存在
        set state = 2;
    end if;
end;

测试借书过程

首先测试借阅人学生不存在

set @test_state = 0;
call proc_borrow_book('20202020','1',1,@test_state);
select @test_state from dual;

输出state=2

测试借的图书不存在

call proc_borrow_book('20230001','5',1,@test_state);
select @test_state from dual;

输出state=3

测试借书太多，库存不够

call proc_borrow_book('20230001','5',1,@test_state);
select @test_state from dual;

输出state=4

测试成功借阅图书，20230001学生借阅10本编号为1（百年孤独）

call proc_borrow_book('20230001','1',10,@test_state);
select @test_state from dual;

输出state=1，借阅成功

再查看图书信息表发现book_inventory被借了10本后剩余10本（20-10=10）

最后附上整体代码

create database Book_Management;
use Book_Management;

create table Book_Info(
    book_id int primary key auto_increment,
    book_name varchar(50) not null ,
    book_author varchar(20) not null ,
    book_price decimal(10,2) not null ,
    book_inventory int not null ,
    book_note varchar(200)
);

create table Student_Info(
    student_id char(8) primary key,
    student_name varchar(20) not null ,
    student_gender char(2) not null ,
    student_age int not null
);

insert into Book_Info(book_name, book_author, book_price, book_inventory, book_note)
values('百年孤独','海明威',40,20,'是个人都该看');
insert into Book_Info(book_name, book_author, book_price, book_inventory, book_note)
values('时间简史','霍金',62.88,12,'霍金已经死了');
insert into Book_Info(book_name, book_author, book_price, book_inventory, book_note)
values('活着','余华',52.2,9,'余华的著作');

insert into Student_Info(student_id, student_name, student_gender, student_age)
values ('20230001','明明','男',21);
insert into Student_Info(student_id, student_name, student_gender, student_age)
values ('20230002','小黑','男',21);
insert into Student_Info(student_id, student_name, student_gender, student_age)
values ('20230003','红兰','女',22);
insert into Student_Info(student_id, student_name, student_gender, student_age)
values ('20230004','艾热','男',21);

select * from student_info;
select * from Book_Info;


create table Borrow_Record(
    record_id int primary key auto_increment,
    borrower_id char(8) not null ,
    borrower_name varchar(20) not null ,
    borrowed_book_id int not null ,
    borrowed_book_name varchar(50) not null ,
    borrow_number int not null ,
    if_returned int not null ,
    borrow_datetime datetime not null,
    constraint FK_RECORD_STUDENT foreign key (borrower_id) references student_info(student_id),
    constraint FK_RECORD_BOOK foreign key (borrowed_book_id) references book_info(book_id)
);

-- a：学号
-- b：图书编号
-- n：借阅数量
-- state：输出借阅状态（1.成功 2.学生不存在 3.图书不存在 4.库存不足）

create procedure proc_borrow_book(in a char(8),in b int,in n int,out state int)
begin
    -- 判断学生是否存在
    declare i_student int default 0;
    declare i_book int default 0;
    declare i_storage int default 0;
    declare i_student_name varchar(20) default '';
    declare i_book_name varchar(50) default '';
    select count(student_id) into i_student from student_info where student_id=a;
    if i_student > 0 then-- 学生存在
        select count(book_id) into i_book from book_info where book_id=b;
        if i_book > 0 then-- 图书存在
            select book_inventory into i_storage from book_info where book_id=b;
            if i_storage >= n then-- 库存充足
                select student_name into i_student_name from student_info where student_id=a;
                select book_name into i_book_name from book_info where book_id=b;
                insert into Borrow_Record(borrower_id, borrower_name, borrowed_book_id, borrowed_book_name, borrow_number, if_returned, borrow_datetime)
                values(a,i_student_name,b,i_book_name,n,0,now());
                update book_info set book_inventory=i_storage-n where book_id=b;
                set state = 1;
            else-- 库存不足
                set state = 4;
            end if;
        else-- 图书不存在
            set state = 3;
        end if;
    else-- 学生不存在
        set state = 2;
    end if;
end;

set @test_state = 0;
call proc_borrow_book('20202020','1',1,@test_state);
select @test_state from dual;


call proc_borrow_book('20230001','5',1,@test_state);
select @test_state from dual;

call proc_borrow_book('20230001','1',100,@test_state);
select @test_state from dual;

call proc_borrow_book('20230001','1',10,@test_state);
select @test_state from dual;