1.插入读者信息.
insert into NIKI_READ_INFO(unum,name,pass,conn,role,sex,rdate,edate,bnum,vnum,flag) values ('s201604050003','小艾','abcd','2233465',502,'F','2008-06-10','2010-06-01',0,0,0);
2.读者信息查询.
select name as '姓名',bnum as '借书数量',vnum as '违规次数' from NIKI_READ_INFO;
3.修改读者信息.
update IKI_READ_INFO set conn='2233465' where unum='s201604050003';
4.插入图书信息.
insert into NIKI_BOOK_INFO(cnum,isbn,date,status) values ('TP0000012',501,'2008-06-01',1);
5.图书查阅.
select a.cnum as '编号',b.isbn as 'ISBN',b.bname as '书名',b.auth as '作者',b.tnum as '库存总量',b.cnum as '现存量' from NIKI_BOOK_INFO a, NIKI_BOOK_BASE b where a.isbn=b.bid and b.isbn='7-302-12266-0';
6.图书信息修改.
update NIKI_BOOK_INFO set date='2008-06-02' where cnum='TP0000012';
7.图书统计.
select SUM(cnum) as '现存总量' from NIKI_BOOK_BASE;
8.借阅图书.
insert into NIKI_BOOK_BRRW(`read`,`book`,`bdate`,`rdate`,`cnum`,`status`) values (501,501,'2008-06-11','2008-07-11',0,1);
update NIKI_READ_INFO set bnum=bnum+1 where rid=501;
update NIKI_BOOK_BASE set cnum=cnum+1 where bid=(select isbn from NIKI_BOOK_INFO where bid=501);
9.归还图书.
insert into NIKI_BOOK_RTRN(`bid`,`read`,`book`,`date`) values (1,501,501,'2008-07-15');
update NIKI_READ_INFO set bnum=bnum-1 where rid=501;
update NIKI_BOOK_BASE set cnum=cnum-1 where bid=(select isbn from NIKI_BOOK_INFO where bid=501);
update NIKI_BOOK_BRRW set status=2 where bid=1;
select rdate from NIKI_BOOK_BRRW where bid=1;
10.罚款的管理.
insert into NIKI_BOOK_FINE values (bid,book,read,date,amt,flag,comm) values(1,501,501,'2008-07-14',40,1,'图书过期');
select * from NIKI_BOOK_FINE;
update NIKI_READ_INFO set vnum=vnum+1 where rid=501;
11.征订管理.
insert into NIKI_BOOK_SOLI(isbn,num,date) values (501,2,'2008-06-01');
update NIKI_BOOK_BASE set cnum=cnum+2,tnum=tnum+2 where bid=(select isbn from NIKI_BOOK_INFO where bid=501);
insert into NIKI_BOOK_INFO(cnum,isbn,date,status) values ('TP0000013',501,'2008-06-01',1);
insert into NIKI_BOOK_INFO(cnum,isbn,date,status) values ('TP0000014',501,'2008-06-01',1);
12.丢失管理.
insert into NIKI_BOOK_LOSS(book,read,amt,date) values (501,501,50,'2008-07-14');
insert into NIKI_BOOK_FINE values (bid,book,read,date,amt,flag,comm) values(1,501,501,'2008-07-14',50,1,'图书过期');
select * from NIKI_BOOK_FINE;
update NIKI_READ_INFO set vnum=vnum+1 where rid=501;
update NIKI_BOOK_BASE set tnum=tnum-1 where bid=(select isbn from NIKI_BOOK_INFO where bid=501);
13.注销管理.
insert into NIKI_BOOK_CANC(book,date) values (501,'2008-07-14');
update NIKI_BOOK_INFO set `status`=0 where bid=501;
14.插入图书基本信息.
insert into NIKI_BOOK_BASE(isbn,type,price,public,auth,ename,bname,cnum,tnum) values ('7-302-12266-1','计算机',4800,'机械工业出版社','夏邦贵','2006年1月第1版','经典实例精讲',2,2);
15.根据ISBN查询.
select * from NIKI_BOOK_BASE where isbn='7-302-12266-4';
16.根据书名进行模糊查询.
select * from NIKI_BOOK_BASE where bname like '%电工%';
17.根据书名和版次联合查询.
select * from NIKI_BOOK_BASE where bname like '%电工%' and ename='2006年1月第1版';
18.查询书被哪些读者借阅.
select * from NIKI_BOOK_BRRW;
19.查询个人历史借书情况.
select * from NIKI_BOOK_BRRW where `read`=501;
20.个人图书罚款查询.
select * from NIKI_BOOK_FINE where `read`=501;
