简单的图书馆管理系统数据库设计

发表于   |  

最近.Net课程需要做一个简单的图书管理系统。
于是就把这个简单的数据库设计放了上来，用于备忘。

表的设计及字段说明

lib_user
字段 数据类型 作用
userid varchar(50) 主键,学号,可用于登录
userpassword varchar(50) 密码
username varchar(50) 用户姓名
userredp varchar(50) 院系

其他自己扩展

lib_book
字段 数据类型 作用
bookid int 自增主键
bookname varchar(50) xx
bookname varchar(50) xx
bookname varchar(50) xx
bookname varchar(50) xx
bookname varchar(50) xx

其他自己扩展

lib_book_borrow
字段 数据类型 作用
borid int 自增主键
bookid int lib_book外键
userid varchar(50) lib_user外键
bordate date 借阅日期
retdate date 还书日期
borstate int 借阅状态(正在借阅,已经还书,续借,已经过期)
bookbag
字段 数据类型 作用
bookid int lib_book外键
usreid varchar(50) lib_user外键

bookbag建表SQL语句

其他类似,暂列一个

1
2
3
4
5
6
7
8
CREATE TABLE lib_borinfo
(
userid varchar(50);
bookid int;
FOREIGN KEY (userid) REFERENCES lib_user(userid);
FOREIGN KEY (bookid) REFERENCES lib_book(bookid);
ADD CONSTRAINT unique_bor_book UNIQUE (userid,bookid);
)

用户登录验证SQL语句

1
select * from lib_user where(userid=@userid, userpassword=@password)

用户密码变更SQL语句

1
update lib_user set userpassword=@password

图书检索SQL语句

暂且列一个以图书名搜索的,其他类似

1
select * from lib_user where(bookname=@bookname);

添加到书包SQL语句

1
2
use library;
insert into bookbag(userid, bookid) values (@userid, @bookid);

书包查询SQL语句

1
2
3
use library;
select * from lib_book where
(bookid in (select bookid from bookbag where userid=@userid));

从书包删除

1
2
use library;
delete from bookbag(userid, bookid) values (@userid, @bookid);

借阅图书

因为书可能是一起借,但是不一定同时还啊,所以必须一本书,对应一个borid.

1
2
3
use library;
insert into lib_borrow(borid,bookid,userid,bordate,retdate,borstate) 
values (@borid, @bookid,@userid,@bordate,@retdate,@borstate);

每借阅一本图书,lib_book里面数量减一的触发器

1
2
3
4
5
6
7
8
create trigger BookDec1
on lib_borrow
after insert
as
update lib_book set bookres=bookres-1
from lib_book, inserted
where
lib_book.bookid=inserted.bookid;

每借阅一本图书,相应的从bookbag里面删除的触发器

1
2
3
4
5
6
7
create trigger BookbagDel
on lib_borrow
after insert 
as
delete bookbag
where bookid in (select bookid from inserted)
and userid in (select userid from inserted);

还书SQL语句

1
2
use library;
update lib_borrow set retdate=@date, borstate='1' where borid=@borid;

lib_book表加一的触发器

1
2
3
4
5
6
7
8
create trigger BookbagDel
on lib_borrow
after update
as
update lib_book set bookres=bookres+1
from lib_book, inserted
where
lib_book.bookid=inserted.bookid;

当前借阅信息查询SQL语句

多表查询

1
2
3
4
use library;
select * from lib_borrow 
left join lib_book on lib_book.bookid=lib_borrow.bookid
where lib_borrow.userid=@userid and lib_borrow.borstate=0;

历史借阅信息查询SQL语句

1
2
3
4
use library;
select * from lib_borrow 
left join lib_book on lib_book.bookid=lib_borrow.bookid
where lib_borrow.userid=@userid and lib_borrow.borstate=1;

图书借阅排行榜

1
2
3
4
5
6
select * from
(select bookid, bor_sum=COUNT(lib_borrow.bookid) 
from lib_borrow group by lib_borrow.bookid) as A
left join (select * from lib_book) as B 
on A.bookid = B.bookid 
order by A.bor_sum desc

用户图书借阅排行榜

1
2
3
4
select * from(select userid, user_sum=COUNT(lib_borrow.userid)
from lib_borrow group by lib_borrow.userid) as A
left join (select userid1=userid,username,usersex,userredp from lib_user) as B 
on A.userid = B.userid1 order by A.user_sum desc ;