數(shù)據(jù)庫課程設(shè)計(jì)之圖書管理系統(tǒng)-閱讀頁
2025-01-31 16:09本頁面
【正文】 (bookid) REFERENCES 圖書(bookid))CREATE TABLE 借書(readerid varchar(9) NOT NULL,bookid varchar(9) NOT NULL,bookname varchar(40) NOT NULL,booknum SMALLINT CHECK(booknum=0 AND booknum=5),outputdate datetime NULL,shoulddate datetime NULL,FOREIGN KEY(readerid) REFERENCES 讀者(readerid),FOREIGN KEY(bookid) REFERENCES 圖書(bookid))/*******通過存儲(chǔ)過程向讀者表中插入數(shù)據(jù)************/USE bookmgGO CREATE PROCEDURE reader_insertelem1 varchar(9),elem2 varchar(20),elem3 varchar(10),elem4 smallintASBEGIN insert into 讀者(readerid,readername,readersex,borrownum)values (elem1,elem2,elem3,elem4)END/********讀者表中插入實(shí)例**********/USE bookmgGOEXEC reader_insert 39。,39。,39。,39。/*向圖書表中插入數(shù)據(jù)*/USE bookmgGOINSERT 圖書VALUES (39。,39。,39。,39。,39。,39。,39。,39。,39。,’0’)GOINSERT 圖書VALUES (39。,39。,39。,39。,39。,39。,39。,39。,39。,’0’)GOINSERT 圖書VALUES (39。,39。,39。,39。,39。,39。,39。,39。,39。,’0’)GOINSERT 圖書VALUES (39。,39。,39。,39。,39。,39。,39。,39。,39。,’0’)GOINSERT 圖書VALUES (39。,39。,39。,39。,39。,39。,39。,39。,39。,’0’)GOINSERT 圖書VALUES (39。,39。,39。,39。,39。,39。,39。,39。,39。,’0’)/*通過存儲(chǔ)過程向圖書表中插入數(shù)據(jù)*/USE bookmgGO CREATE PROCEDURE book_insertelem1 varchar(9),elem2 varchar(40),elem3 varchar(20),elem4 varchar(20),elem5 varchar(20),elem6 varchar(20),elem7 varchar(50),elem8 varchar(10),elem9 smallint,elem10 smallintASBEGIN insert into 圖書(bookid,bookname,booktype,bookauther,booktrslator,bookpress,bookcontent,bookprice,noborrow,haveborrow)values (elem1,elem2,elem3,elem4,elem5,elem6,elem7,elem8,elem9,elem10)END/********************************創(chuàng)建還書存儲(chǔ)*******************************/USE bookmgGOCREATE PROCEDURE return_insertelem1 varchar(9),elem2 varchar(9),elem3 smallintASINSERT INTO 還書(readerid,bookid,booknum)values (elem1,elem2,elem3)BEGINUPDATE 還書SET inputdate=GETDATE()WHERE readerid=elem1。UPDATE 圖書SET haveborrow=haveborrowelem3WHERE bookid=elem2。還書成功39。11100713139。54054190339。借書成功39。UPDATE 借書SET shoulddate=DATEADD(day,30,getdate())WHERE readerid=elem1 AND bookid=elem2。UPDATE 圖書SET noborrow=noborrowelem3WHERE bookid=elem2。UPDATE 讀者SET borrownum=borrownum+elem3WHERE readerid=elem1。借書失敗，有超期未還圖書39。11100713139。54054190339。DECLARE booknum intSET booknum=(select sum(noborrow+haveborrow)FROM 圖書WHERE booktype=booktype)SELECT booktype AS 39。 ,booknum AS 39。 END/****圖書分類查詢實(shí)例*********/USE bookmgGO EXEC type_select 39。 GO/*******************圖書按書名查詢和統(tǒng)計(jì)******************/USE bookmgGOCREATE PROCEDURE bookname_selectbookname varchar(40)ASBEGINSELECT *FROM 圖書WHERE bookname=bookname。書名39。本39。子不語壹39。DECLARE booknum intSET booknum=(select sum(noborrow+haveborrow)FROM 圖書WHERE bookauthor=bookauthor)SELECT bookauthor AS 39。 ,booknum AS 39。 END/***圖書作者查詢實(shí)例******/USE bookmgGO EXEC bookauthor_select 39。GO25
環(huán)評(píng)公示相關(guān)推薦
鄂ICP備17016276號(hào)-1