MySQL图书管理系统设计

一、项目简介

图书管理系统是许多学校图书馆、公共图书馆所经常使用到的数据管理系统，它主要是通过存储过程“封装”数据更新功能，实现图书的借书、还书功能以及记录人员操作信息功能，本次实验的MySQL图书管理系统设计是针对后端数据库采集数据、更新数据的一个小项目。

二、项目分析

（1）需求分析

根据图书管理系统功能实现要求，本次将实现以下功能：

①图书管理功能（书籍的借出、归还）

图书管理功能是实现书籍的借出、归还，判断书籍库存情况，更新图书数据。

②信息操作功能

信息操作功能是实现借书人员的操作记录信息，记录图书租借信息。

③人员管理功能

本次实验图书馆以学校图书馆为主要目标对象，因此还需要人员管理功能，人员管理功能是实现学校学生的信息管理，它所能完成的项目是图书馆仅开放于学校的学生和老师对平通员工不做开放

（2）需求表信息分析

①图书管理需求表分析

需要实现书籍的借阅，因此对书籍信息表（books）有以下信息：

书籍序号：book_id

书名：book_name

作者名：book_author

出版社：book_press

书籍价格：book_price

书籍库存：book_stock

书籍简介：book_desc

②信息操作表信息分析

借书记录表（records）存储租借信息记录的数据表

借书记录序号：rid

租借学生编号：snum

图书编号：bid

借书数量：borrow_num

借书状态：is_return（0表示归还，1表示未归还）

租借日期：borrow_date

③人员信息表

人员信息表（personnel）存储租借人员的信息表

人员编号：per_num

人员姓名：per_name

人员性别：per_gender

人员年龄：per_age

人员职能：per_func

三、项目实施

（1）创建图书信息表

create table books(
   book_id int primary key auto_increment,  -- 书籍序号
     book_name varchar(50) not null,  -- 书名
     book_author varchar(20) not null, -- 作者名
     book_press varchar(20) not null, -- 出版社
     book_price decimal(10,2) not null, -- 书籍价格
     book_stock int not null, -- 书籍库存
     book_desc varchar(200)  -- 书籍简介
);

（2）创建人员信息表

create table personnel(
    per_num char(4) primary key, -- 人员编号
    per_name varchar(20) not null, -- 人员姓名
    per_gender char(2) not null, -- 人员性别
    per_age int not null, -- 人员年龄
    per_func varchar(20) not null -- 人员职能
);

（3）创建信息操作表

create table records(
   rid int primary key auto_increment, -- 借书记录序号
     snum char(4) not null, -- 租借学生编号
     bid int not null,  -- 图书编号
     borrow_num int not null, -- 借书数量
     is_return int not null, -- 0表示为归还   1 表示已经归还 借书状态
     borrow_date date not null, -- 租借日期
     constraint FK_RECORDS_STUDENTS foreign key(snum) references personnel(per_num),
     constraint FK_RECORDS_BOOKS foreign key(bid) REFERENCES books(book_id)
);

（4）测试数据导入

图书书籍数据导入：

insert into books(book_name,book_author,book_press,book_price,book_stock,book_desc)
values('Java程序设计','亮亮','清华出版社',38.80,12,'亮亮老师带你学Java');
insert into books(book_name,book_author,book_press,book_price,book_stock,book_desc)
values('Python王者之路','威哥','重庆大学出版社',44.40,9,'千锋威哥，Java王者领路人');

人员信息添加：

insert into personnel(per_num,per_name,per_gender,per_age,per_func) values('1001','张三','男',20,'学生');
insert into personnel(per_num,per_name,per_gender,per_age,per_func) values('1002','李四','女',20,'学生');
insert into personnel(per_num,per_name,per_gender,per_age,per_func) values('1003','王五','男',35,'讲师');
insert into personnel(per_num,per_name,per_gender,per_age,per_func) values('1004','王三','女',35,'副教授');

(5)借书存储过程及测试

①借书存储过程

-- 参数1：学号
-- 参数2：图书编号
-- 参数3：借书的数量
-- 参数4：借书状态
create procedure proc_borrow_book(IN a char(4),IN b int, IN m int,OUT state varchar(20))
begin
    declare per_count int default 0;
    declare book_count int default 0;
    declare stock int default 0;
    -- 判断学号是否存在：根据参数 a 到学生信息表查询是否有stu_num=a的记录
    select count(per_num) INTO per_count from personnel where per_num=a;
    if per_count>0 then
         -- 学号存在
         -- 判断图书ID是否存在：根据参数b 查询图书记录总数
         select count(book_id) INTO book_count from books where book_id=b;
         if book_count >0 then
            -- 图书存在
              -- 判断图书库存是否充足：查询当前图书库存，然后和参数m进行比较
                select book_stock INTO stock from books where book_id=b;
                if stock >= m then
                    -- 执行借书
                    -- 操作1：在借书记录表中添加记录
                    insert into records(snum,bid,borrow_num,is_return,borrow_date) values(a,b,m,0,sysdate());
                    -- 操作2：修改图书库存
                    update books set book_stock=stock-m where book_id=b;
                    -- 借书成功
                    set state='借书成功';
                else
                  -- 库存不足
                    set state='库存不足';
                end if;                
         else
                -- 图书不存在
                set state = '图书不存在';
         end if;
    else
       -- 不存在
         set state = '非学校人员';
    end if;
end;

注：借书存储过程运行之后，若用的是Navicat的函数选项有创建的存储过程名，若是终端则使用语句：show procedure status where db='数据库名'

②还书测试

-- 调用存储过程借书
-- 参数1：学号
-- 参数2：图书编号
-- 参数3：借书的数量
-- 参数4：借书状态
set @state='';
call proc_borrow_book('1001',2,1,@state);
-- 反应借书情况
select @state from dual;

运行后得到如下结果，则借书过程成功

同时观察借书记录中的数据

（6）还书存储过程及测试

①还书存储过程

-- 参数1：换书人学号
-- 参数2：归还图书编号
-- 参数3：还书的数量
-- 参数4：还书状态

create procedure proc_return_book(IN a char(4),IN b int, IN m int,OUT state VARCHAR(20))
begin
    declare per_count int default 0;
    declare book_count int default 0;
    declare stock int default 0;
    -- 判断学号是否存在：根据参数 a 到学生信息表查询是否有stu_num=a的记录
    select count(per_num) INTO per_count from personnel where per_num=a;
    if per_count>0 then
         -- 学号存在
         -- 判断图书ID是否存在：根据参数b 查询图书记录总数
         select count(book_id) INTO book_count from books where book_id=b;
         if book_count >0 then
            -- 图书存在
                    -- 操作1：在借书记录表中添加记录
                    insert into records(snum,bid,borrow_num,is_return,borrow_date) values(a,b,m,1,sysdate());
                    -- 操作2：修改图书库存
                    update books set book_stock=stock+m where book_id=b;
                    -- 借书成功
                    set state='还书成功';
                        
         else
                -- 书籍归还错误
                set state = '此书籍不是图书馆的书';
         end if;
    else
       -- 不存在
         set state = '还书人员信息错误';
    end if;
end;

②还书测试

-- 参数1：还书人学号
-- 参数2：归还图书编号
-- 参数3：还书的数量
-- 参数4：还书状态
set @state='';
call proc_return_book('1001',2,1,@state);
-- 反应借书情况
select @state from dual;

四、项目回顾总结

（1）项目要点

本次实验项目主要要点是对表创建的考察、存储过程创建考察、存储过程中间逻辑运算的考察，同时会有人疑惑为什么不一个一个的查或者写好固定的SQL语句更改参数？

其实这和存储过程的运用有关，存储过程的运用一是增加了SQL语句的复用性，二是增加了SQL语句的安全性，三是增加了SQL语句的便捷性。

（2）项目扩展

这里的项目扩展是作者的一个小小运用，本次的图书管理系统可以运用到生活中，我们编写一个存储过程可以用以记录一些日常事务，

例如：作者和女友有个行为信息记录表，每次的行为信息可以给女友进行加分，我们可以通过编写存储实现对日常行为的记录加分，同时可以编写存储过程实现对女友日常行为积分的兑换，设置积分兑换表，即可以完成兑换记录的添加。这就是本次实验可以运用到日常生活的地方，当然我们还你可以编写一个可视化界面，进行一个可视化的操作，用以自己的生活记录也是可以的