大学课设之Mysql图书管理设计

需求分析

1. 改图书管理涉及书本的信息，读者的信息，图书馆管理员的信息管理，要求可以对图书的信息进行修改，删除，添加的管理。
2. 读者可以凭借自己的身份证明借阅书籍，例如学校的一卡通，这是为学生提供方便，不需要另外办理一张借阅卡之类的证明。
3. 借阅书籍时，系统自动的添加学生的信息，学生可以查阅自己的信息，例如借了什么书籍，什么时候借阅的，什么时候该还阅等等。
4. 当学生借阅书籍时，修改图书馆保存的书籍的信息，同时记录该书籍是否还可借。
5. 学生归还书籍时，系统可以修改学生的借阅信息。
6. 当学生借书时，可以查看借书的信息。

概念结构设计-总E-R图

逻辑结构设计

①　一个学生只有一个学号，通过学号可以查到姓名，姓名可以相同。
②　一个书有一个编号，每一本书的编号不一样。
③　书名可以相同，通过编号可以查询书名。
④　Borrow表通过学号与编号，可以查询哪个学生借了哪一本书。
⑤　书可分为不同的类别，放在不同的楼层。
⑥　还书时，可以查询哪本书借了，有无欠费。

Student(学号，姓名，性别，班级，出生，年级)；
Book（编号，书名，作者，出版社，数量，类型）；
Borrow（学号，编号，借的时间，应该还书的时间）；
Grenre（类型，类别，楼层）；
Money（学号，编号，超过时间）。

数据库的实现

Student 表

触发器重点说明

1. 创建触发器student_borrow当一个学生借书，在borrow表里应该插入一条记录，而且该书的数量应该应该减一；如果所借的书的数量为0，则不能借，并提示‘该书已经被借完’。

CREATE trigger student_borrow
after insert on borrow for EACH row
BEGIN
declare shuliang int;
SELECT bnumber into shuliang from book where bno=new.bno;
if shuliang=0
then 
SIGNAL SQLSTATE 'HY000'set MESSAGE_TEXT='该书已经被借完';
ELSEIF shuliang>0
then
update book set bnumber=bnumber-1 
WHERE book.bno=new.bno;
end IF;
END

2. 创建一个触发器return_book当一个学生想还书时，把该书的数量在book上加1,前提是这个学生借的这本书没有逾期。

create trigger return_book
after DELETE on borrow for each row
BEGIN
update book set bnumber=bnumber+1 where book.bno=old.bno;
End

3. 创建一个存储过程student_check，给定一个学号，能查看学生的借书情况，输出名字，借书时间，应该还书时间。

create procedure student_check(in xh char(10))
SELECT sname as 姓名,borrow.bno as 编号,bname as 书名,
borrow_time as 借书时间,return_time as 还书时间
from borrow,book,student
WHERE book.bno=borrow.bno and student.sno=borrow.sno and borrow.sno=xh;
CALL student_check('2016070101');

4. 创建你一个存储过程student_money，输入学生的学号，显示学生的欠费信息。

 create procedure student_money(in xh char(10))
SELECT sname,bname,overtime*2 as 欠费
from student,book,money
where student.sno=money.sno and book.bno=money.bno and money.sno=xh;
call student_money('2016070101');

5. 创建一个存储过程book_check,输入一个楼层，看看有哪些书籍。

CREATE procedure book_check(in loucheng int)
SELECT floor,bname from book,grenre
where book.kind=grenre.kind and floor=loucheng;
drop PROCEDURE book_check;

完整的Mysql设计代码

/*创建数据库，zhsy（综合实验）*/
create database zhsy;


/*创建学生表*/
create table student(
sno char(10) primary key,
sname char(10) not null,
ssex enum('男','女'),
sclass char(8) not null,
sbrith date,
grade smallint
)ENGINE=INNODB default charset=utf8 collate=utf8_bin;

DESC student;


/*创建book表*/
create table book(
bno char(10) primary key,
bname char(10) not NULL,
bauthor char(10) not null,
bhouse  char(10) not null,
bnumber int not null,
kind char(5)
)ENGINE=INNODB default charset=utf8 collate=utf8_bin;

desc book;


/*创建genre表,这是把书分类，图书类，计算机类，在哪个楼层*/
create table grenre(
kind char(5) not null,
type char(10) not null,
floor int
)ENGINE=INNODB default charset=utf8 collate=utf8_bin;

desc grenre;


/*建立借书表borrow，看根据学号和bno可以确定学生借了多少本书*/
CREATE table borrow(
sno char(10),
bno char(10),
borrow_time datetime not null,
return_time datetime not NULL,
FOREIGN key(sno) REFERENCES student(sno)
)ENGINE=INNODB default charset=utf8 collate=utf8_bin;

desc borrow;

/*添加borrow主键：*/
ALTER table borrow add PRIMARY key(sno,bno);


/*学生逾期还书，要收费，momey*/
create table money(
sno char(10),
bno char(10),
overtime int not NUll,
primary key(sno,bno)
)ENGINE=INNODB default charset=utf8 collate=utf8_bin;
alter table money add CONSTRAINT foreign KEY(sno,bno) REFERENCES borrow(sno,bno);



/*为student表添加数据*/
insert into student
VALUES('2016070101','小陈','男','计科1','1995-1-20','2016'),
('2016070202','小许','女','管理1','1996-10-17','2016'),
('2016070103','小湖','男','计科2','1997-12-27','2016'),
('2015070301','小明','男','安全1','1995-11-27','2015'),
('2016070122','汪洋','男','计科2','1997-12-07','2016'),
('2015070221','李丽','女','管理2','1997-1-27','2015'),
('2016070203','张放','男','管理2','1997-9-27','2016'),
('2018070301','郑明','男','安全1','1999-11-27','2018'),
('2018070333','乔云萍','女','安全2','1998-11-27','2018'),
('2017070301','谢生','男','安全2','1997-11-27','2017'),
('2018070109','刘晨','男','计科','1996-8-27','2018'),
('2016070305','严欣婷','女','安全2','1998-11-27','2016'),
('2018070328','海雯','女','安全2','1999-07-27','2018'),
('2018070330','陈欧','女','安全2','2000-11-2','2018');


insert into book VALUES
('B001','高等数学A','林益','北京大学','3','I类'),
('B002','高等数学B','李伶','北京大学','4','I类'),
('B003','概率论','隋亚莉','清华大学','2','I类'),

('B004','C语言设计','谭浩强','清华大学','5','II类'),
('B005','C++语言设计','李昌兵','电子工业','6','II类'),
('B006','java语言设计','张跃平','电子工业','2','II类'),

('B007','十万个为什么','韩启德','少年儿童','10','III类'),
('B008','哈利·波特','J·K·罗琳','教育','2','III类'),
('B009','哈利·波特','J·K·罗琳','教育','2','III类'),

('B010','青年杂志','张楠','中国青年','12','IV类'),
('B011','呐喊','鲁迅','中国青年','4','IV类'),
('B012','朝花夕拾','鲁迅','中国青年','5','IV类'),
('B013','阿Q正传','鲁迅','中国青年','4','IV类'),




INSERT into borrow VALUES
('2016070101','B004','2017-02-20 07:30:00','2017-04-20 07:30:00');

('2016070101','B001','2018-10-1 8:00:00','2018-11-1 00:00:00'),
('2016070101','B002','2018-11-1 09:30:00','2018-12-1 09:30:00'),
('2016070202','B003','2017-03-05 11:00:00','2018-05-05 11:00:00'),
('2016070101','B003','2018-11-1 09:30:00','2018-12-1 09:30:00'),
('2015070301','B004','2017-02-20 07:30:00','2017-04-20 07:30:00'),
('2016070203','B005','2017-08-01 08:00:05','2017-10-01 08:00:05'),
('2018070333','B008','2018-10-09 09:10:00','2018-11-09 09:10:00'),
('2016070305','B008','2017-09-12 10:30:00','2017-10-12 10:30:00'),
('2018070330','B013','2018-12-01 18:00:00','2019-01-01 18:00:00'),
('2018070330','B002','2018-10-09 17:00:00','2018-12-09 17:00:00'),
('2018070328','B013','2018-09-01 12:00:00','2018-11-01 12:00:00'),
('2018070328','B008','2018-09-01 12:00:00','2018-11-01 12:00:00');


insert into money VALUES
('2016070101','B001',10),
('2016070101','B002',15),
('2015070301','B004',11),
('2016070101','B004',9),
('2018070333','B008',12),
('2016070305','B008',4),
('2018070330','B013',30);



insert into grenre VALUES
('I类','数学类','2'),
('II类','计算机类','2'),
('III类','图书类','2'),
('IV类','小说文学类','3');


/*为表创建索引，以提高速度查询*/


/*为student表中的sname创建索引*/
/*
CREATE index sno_index on student(sno);
drop index sno_index on student;
*/
CREATE index sno_index on student(sname);

/*为book表中的bname创建索引*/
create index bno_index on book(bname);

/*create index bno_index on book(bno);

drop index bno_index on book;
*/


/*为borrow表中的sno与bno创建索引*/

/*
create index sno_bno_index on borrow(sno,bno);
create index sno_index on borrow(sno);
*/

/*为money表中的sno创建索引*/

/*create index sno_index on money(sno);
drop index sno_index on money;
*/

/*为grenre表建立索引kind_index,*/
create INDEX kind_index on grenre(kind);





/*创建视图V_borrow统计一下。借不同的书的人数，输出书的编号，书名，数量*/
CREATE  VIEW v_borrow(bno,bname,数量)
as
SELECT borrow.bno,bname,count(*)
from borrow,book
WHERE book.bno=borrow.bno
GROUP BY bno;

/*定义一个视图V1，作用是看看有哪些同学借了书，没有超过期限的学号和姓名*/
create VIEW v1(学号,姓名)
AS
SELECT sno,sname
FROM student
where sno in (
SELECT  DISTINCT sno
from borrow
where sno not in(SELECT sno from money)
)

/*创建视图v2_money，作用是,假定逾期一天就惩罚2元，输出罚钱的学号*/
CREATE VIEW v2_money(学号,姓名,书名,应该交钱)
AS
SELECT student.sno,student.sname,bname,overtime*2
from money,student,book
where money.sno=student.sno and money.bno=book.bno;

/*创建视图v3_check,作用是用来查看每个楼层有哪些书*/
create view v3_check(书名,类别,楼层)
AS 
SELECT
bname,type,floor
from book,grenre
where book.kind=grenre.kind and floor in
(SELECT floor from grenre);



创建用户

/*1.创建book_teacher用户*/
CREATE USER 'book_teacher'@'localhost' identified by'123';
/*把select、UPDATE、insert,DELETE权限给用户book_teacher;*/
SELECT * from mysql.user
grant SELECT,insert,UPDATE,DELETE on zhsy.book to 'book_teacher'@'localhost';


/*2.创建学生用户*/
insert mysql.user(host,user,authentication_string,ssl_cipher,x509_issuer,x509_subject)
values('localhost','student',password(123),'','','');
flush privileges 
SELECT * from mysql.user
grant SELECT on zhsy.book to'student'@'localhost';
grant SELECT on zhsy.borrow to'student'@'localhost';
grant SELECT on zhsy.grenre to'student'@'localhost';
grant SELECT on zhsy.money to'student'@'localhost';

/*3.创建teacher_money用户，密码123*/
create user 'teacher_money'@'localhost'identified by'123';
grant SELECT on zhsy.book to'teacher_money'@'localhost';
grant SELECT,update on zhsy.money to'teacher_money'@'localhost';


SELECT * from mysql.user


/*触发器*/

CREATE trigger student_borrow
after insert on borrow for EACH row
BEGIN
declare shuliang int;
SELECT bnumber into shuliang from book where bno=new.bno;
if shuliang=0
then 
SIGNAL SQLSTATE 'HY000'set MESSAGE_TEXT='该书已经被借完';
ELSEIF shuliang>0
then
update book set bnumber=bnumber-1 
WHERE book.bno=new.bno;
end IF;
END

/*drop trigger student_borrow;show triggers;*/

insert into borrow VALUES
('2016070202','B007','2018-12-1 12:00:00','2018-01-01 12:00:00');
insert into book values
('B015','大学英语','某某某','人民教育','0','I类');
insert into borrow VALUES
('2018070301','B015','2018-12-1 12:00:00','2018-01-01 12:00:00');



/*
CREATE trigger kong
AFTER insert on borrow for each ROW
BEGIN
declare shuliang int;
SELECT bnumber into shuliang from book where bno=new.bno;
if shuliang=0
then 
SIGNAL SQLSTATE 'HY000'
set MESSAGE_TEXT='该书已经被借完';
end if;
END
*/
/*show triggers;drop trigger kong*/


/*创建一个触发器return_book*/
create trigger return_book
after DELETE on borrow for each row
BEGIN
update book set bnumber=bnumber+1 where book.bno=old.bno;
end

/*DROP trigger return_book;*/

create procedure student_check(in xh char(10))
SELECT sname as 姓名,borrow.bno as 编号,bname as 书名,
borrow_time as 借书时间,return_time as 还书时间
from borrow,book,student
WHERE book.bno=borrow.bno and student.sno=borrow.sno and borrow.sno=xh;

/*DROP PROCEDURE student_check;*/
CALL student_check('2016070101');

create procedure student_money(in xh char(10))
SELECT sname,bname,overtime*2 as 欠费
from student,book,money
where student.sno=money.sno and book.bno=money.bno and money.sno=xh;

call student_money('2016070101');


CREATE procedure book_check(in loucheng int)
SELECT floor,bname from book,grenre
where book.kind=grenre.kind and floor=loucheng;

drop PROCEDURE book_check;

call book_check(3);

/*ALTER  table student add INDEX in_index student(sno(5));*/