表的設(shè)計(jì)及字段說(shuō)明
lib_user
| 字段 | 數(shù)據(jù)類型 | 作用 |
|---|---|---|
| userid | varchar(50) | 主鍵,學(xué)號(hào),可用于登錄 |
| userpassword | varchar(50) | 密碼 |
| username | varchar(50) | 用戶姓名 |
| userredp | varchar(50) | 院系 |
其他自己擴(kuò)展
lib_book
$E = mc^2$
| 字段 | 數(shù)據(jù)類型 | 作用 |
|---|---|---|
| bookid | int | 自增主鍵 |
| bookname | varchar(50) | xx |
| bookname | varchar(50) | xx |
| bookname | varchar(50) | xx |
| bookname | varchar(50) | xx |
| bookname | varchar(50) | xx |
其他自己擴(kuò)展
lib_book_borrow
| 字段 | 數(shù)據(jù)類型 | 作用 |
|---|---|---|
| borid | int | 自增主鍵 |
| bookid | int | lib_book外鍵 |
| userid | varchar(50) | lib_user外鍵 |
| bordate | date | 借閱日期 |
| retdate | date | 還書(shū)日期 |
| borstate | int | 借閱狀態(tài)(正在借閱,已經(jīng)還書(shū),續(xù)借,已經(jīng)過(guò)期) |
bookbag
| 字段 | 數(shù)據(jù)類型 | 作用 |
|---|---|---|
| bookid | int | lib_book外鍵 |
| usreid | varchar(50) | lib_user外鍵 |
bookbag建表SQL語(yǔ)句
其他類似,暫列一個(gè)
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);
)
用戶登錄驗(yàn)證SQL語(yǔ)句
select * from lib_user where(userid=@userid, userpassword=@password)
用戶密碼變更SQL語(yǔ)句
update lib_user set userpassword=@password
圖書(shū)檢索SQL語(yǔ)句
暫且列一個(gè)以圖書(shū)名搜索的,其他類似
select * from lib_user where(bookname=@bookname);
添加到書(shū)包SQL語(yǔ)句
use library;
insert into bookbag(userid, bookid) values (@userid, @bookid);
書(shū)包查詢SQL語(yǔ)句
use library;
select * from lib_book where
(bookid in (select bookid from bookbag where userid=@userid));
從書(shū)包刪除
use library;
delete from bookbag(userid, bookid) values (@userid, @bookid);
借閱圖書(shū)
因?yàn)闀?shū)可能是一起借,但是不一定同時(shí)還啊,所以必須一本書(shū),對(duì)應(yīng)一個(gè)borid.
use library;
insert into lib_borrow(borid,bookid,userid,bordate,retdate,borstate)
values (@borid, @bookid,@userid,@bordate,@retdate,@borstate);
每借閱一本圖書(shū),lib_book里面數(shù)量減一的觸發(fā)器
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;
每借閱一本圖書(shū),相應(yīng)的從bookbag里面刪除的觸發(fā)器
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);
還書(shū)SQL語(yǔ)句
use library;
update lib_borrow set retdate=@date, borstate='1' where borid=@borid;
lib_book表加一的觸發(fā)器
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;
當(dāng)前借閱信息查詢SQL語(yǔ)句
多表查詢
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語(yǔ)句
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;
圖書(shū)借閱排行榜
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
用戶圖書(shū)借閱排行榜
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 ;
