MySQL图书管理系统设计

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;

注：借书存储过程运行之后，若用的是Navi cat的函数选项有创建的存储过程名，若是终端则使用语句：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）项目扩展

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

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