數(shù)據(jù)庫(kù)圖書信息管理數(shù)據(jù)庫(kù)SQL語(yǔ)句WORD

1、文檔可能無法思考全面，請(qǐng)瀏覽后下載! 實(shí)驗(yàn) SQL語(yǔ)言一、 實(shí)驗(yàn)?zāi)康?、 理解數(shù)據(jù)庫(kù)以及數(shù)據(jù)表的設(shè)計(jì)；2、 熟悉SQL Server2005中的數(shù)據(jù)類型；3、 熟悉使用SQL語(yǔ)句創(chuàng)建和刪除模式和索引；4、 掌握使用SQL語(yǔ)句創(chuàng)建、修改和刪除數(shù)據(jù)表；5、 掌握使用SQL語(yǔ)句查詢表中的數(shù)據(jù)；6、 掌握使用SQL語(yǔ)句插入、修改和刪除數(shù)據(jù)表中的數(shù)據(jù)；7、 掌握使用SQL語(yǔ)句創(chuàng)建、刪除、查詢和更新視圖。二、 實(shí)驗(yàn)內(nèi)容（一）創(chuàng)建數(shù)據(jù)庫(kù)和模式1、通過SQL語(yǔ)句創(chuàng)建圖書信息管理數(shù)據(jù)庫(kù)，命名為“db_Library”，數(shù)據(jù)文件和日志文件放在D盤下以自己學(xué)號(hào)和姓名命名的文件夾中，數(shù)據(jù)文件的邏輯名為db_Libra

2、ry_data，數(shù)據(jù)文件的操作系統(tǒng)名為db_Library_data.mdf，文件初始大小為10MB，最大可增加至300MB，增幅為10%；日志文件的邏輯名為db_Library_log，日志文件的操作系統(tǒng)名為db_Library_data.ldf，文件初始大小為5MB，最大可增加至200MB，增幅為2MB。2、通過SQL語(yǔ)句在該數(shù)據(jù)庫(kù)中創(chuàng)建模式L-C。（二）創(chuàng)建和管理數(shù)據(jù)表要求為各數(shù)據(jù)表的字段選擇合適的數(shù)據(jù)類型及名稱；為各數(shù)據(jù)表設(shè)置相應(yīng)的完整性約束條件。1、通過SQL語(yǔ)句將以下數(shù)據(jù)表創(chuàng)建在L-C模式下：課程信息表（tb_course）課程編號(hào)Course number 、課程名Course

3、name 、先修課The first course 、學(xué)分credit2、通過SQL語(yǔ)句將以下數(shù)據(jù)表創(chuàng)建在該數(shù)據(jù)庫(kù)的默認(rèn)模式dbo下：圖書類別信息表（tb_booktype)類別編號(hào)Type number 、類別名稱Category name 圖書信息表（tb_book）圖書編號(hào)ISBN 、類別編號(hào)Type number、書名title 、作者author、出版社BookPublic、定價(jià)BookPrice、庫(kù)存數(shù)Inventory number 讀者信息表（tb_reader）讀者編號(hào)Reader ID 、姓名、性別、學(xué)號(hào)Student ID 、班級(jí)、系部pastern 借閱信息表（tb_b

4、orrow)圖書編號(hào)、讀者編號(hào)、借閱日期Borrowing date 、歸還日期Return date 3、通過SQL語(yǔ)句對(duì)讀者信息表進(jìn)行修改：刪除系部字段、添加所在系字段。4、通過SQL語(yǔ)句對(duì)圖書信息表進(jìn)行修改：將定價(jià)的數(shù)據(jù)類型改為REAL。5、通過SQL語(yǔ)句刪除課程信息表。（三）創(chuàng)建和刪除索引1、使用SQL語(yǔ)句在圖書信息表上創(chuàng)建一個(gè)非聚簇索引IX_S_QUANTITY，要求按照該表中庫(kù)存數(shù)字段的降序創(chuàng)建。2、使用SQL語(yǔ)句在讀者信息表上創(chuàng)建一個(gè)唯一的非聚簇索引IX_S_NAME，要求按照該表中的姓名字段的升序創(chuàng)建。3、使用SQL語(yǔ)句刪除之前創(chuàng)建的兩個(gè)索引。10 / 11（四）數(shù)據(jù)庫(kù)及數(shù)據(jù)表

5、設(shè)計(jì)根據(jù)周圍的實(shí)際應(yīng)用情況，自選一個(gè)小型的數(shù)據(jù)庫(kù)應(yīng)用項(xiàng)目進(jìn)行研究，完成該系統(tǒng)的設(shè)計(jì)。要求2人一組，通過需求分析，列出系統(tǒng)的主要功能，并完成該系統(tǒng)數(shù)據(jù)庫(kù)的邏輯結(jié)構(gòu)設(shè)計(jì)。例如可選擇學(xué)籍管理系統(tǒng)、企業(yè)進(jìn)銷存管理系統(tǒng)、人事管理系統(tǒng)或在線考試系統(tǒng)等。（五）數(shù)據(jù)查詢通過SSMS向各數(shù)據(jù)表中添加以下記錄。（1）圖書類別信息表類別編號(hào)類別名稱類別編號(hào)類別名稱類別編號(hào)類別名稱1數(shù)學(xué)4文學(xué)7建筑2英語(yǔ)5藝術(shù)8化學(xué)3計(jì)算機(jī)6電子信息9物理（2）圖書信息表圖書編號(hào)類別編號(hào)書名作者出版社定價(jià)庫(kù)存數(shù)100013數(shù)據(jù)庫(kù)管理王珊高等教育出版社35.5010100023軟件測(cè)試賀平機(jī)械工業(yè)出版社24.605100033C+程序

6、設(shè)計(jì)譚浩強(qiáng)清華大學(xué)出版社30.008100044紅樓夢(mèng)曹雪芹人民文學(xué)出版社70.005100054西游記羅貫中人民文學(xué)出版社60.008100064紅與黑司湯達(dá)人民文學(xué)出版社50.005100071高等數(shù)學(xué)李翼清華大學(xué)出版社28.004100088有機(jī)化學(xué)張翔高等教育出版社29.005100092大學(xué)英語(yǔ)王琳高等教育出版社25.0010100102英語(yǔ)教程王琳高等教育出版社25.005（3）讀者信息表讀者編號(hào)姓名性別學(xué)號(hào)班級(jí)所在系R10001張小航男085110108511計(jì)算機(jī)系R10002王文廣女085110208511計(jì)算機(jī)系R10003李理女085110308511計(jì)算機(jī)系R10004

7、李彥宏男085120108512計(jì)算機(jī)系R10005張麗霞女085120208512計(jì)算機(jī)系R10006王強(qiáng)男072110407211電子系R10007張寶田男072120407212電子系R10008宋文霞女076110407611建工系R10009劉芳菲女088110408811外語(yǔ)系R10010常江寧男088120408812外語(yǔ)系（4）借閱信息表圖書編號(hào)讀者編號(hào)借閱日期歸還日期10002R100032009-9-202009-10-2010003R100032009-9-202009-10-2010004R100032009-9-302009-10-3010009R100032009-

8、9-302009-10-3010009R100072009-5-202009-6-2010010R100072009-5-202009-6-2010009R100092009-5-302009-6-3010010R100092009-5-222009-6-2210002R100092009-5-222009-6-2210003R100092009-5-302009-6-30對(duì)以上數(shù)據(jù)表，完成以下操作：（1）查詢每本圖書的所有信息；（2）查詢每個(gè)讀者的讀者編號(hào)、姓名和班級(jí)；（3）查詢每條借閱記錄的借閱天數(shù)（函數(shù)DATEDIFF獲取兩個(gè)日期的差）；（4）查詢被借閱過的圖書的圖書編號(hào)；（5）查詢圖書

9、編號(hào)為“10006”的書名和作者；（6）查詢庫(kù)存數(shù)在5到10本之間的圖書的圖書編號(hào)和書名；（7）查詢計(jì)算機(jī)系或電子系姓張的讀者信息；（8）查詢書名包括“英語(yǔ)”的圖書信息；（9）統(tǒng)計(jì)男讀者、女讀者的人數(shù)；（10）統(tǒng)計(jì)各類圖書的類別編號(hào)、平均定價(jià)以及庫(kù)存總數(shù)；（11）統(tǒng)計(jì)每本書籍借閱的人數(shù)，要求輸出圖書編號(hào)和所借人數(shù)，查詢結(jié)果按人數(shù)降序排列；（12）查詢有庫(kù)存的各類別圖書的類別編號(hào)、類別名稱和借閱數(shù)量；（13）查詢借閱了“大學(xué)英語(yǔ)”一書的讀者，輸出讀者姓名、性別、系部；（14）查詢每個(gè)讀者的讀者編號(hào)、姓名、所借圖書編號(hào)以及所借閱日期；（LEFT OUTER JOIN）（15）查詢現(xiàn)有圖書中價(jià)格最高

10、的圖書，輸出書名、作者、定價(jià)；（16）查詢借閱了“大學(xué)英語(yǔ)”但沒有借閱“C+程序設(shè)計(jì)”的讀者，輸出讀者姓名、性別、系部；（17）統(tǒng)計(jì)借閱了2本以上圖書的讀者信息；（18）查詢借閱了“大學(xué)英語(yǔ)”一書或者借閱了“C+程序設(shè)計(jì)”一書的讀者信息；（用集合查詢完成）（19）查詢既借閱了“大學(xué)英語(yǔ)”一書又借閱了“C+程序設(shè)計(jì)”一書的讀者信息；（用集合查詢完成）（20）查詢計(jì)算機(jī)系中比其他系所有讀者借書數(shù)量都多的讀者的信息；（21）在讀者信息表中插入一條新的記錄（讀者編號(hào)：R10011；姓名：張三；所在系：電子系）；（22）定義一個(gè)表tb_booknew，包含圖書編號(hào)、書名和類別名稱字段，要求將類編編號(hào)為“

11、3”的圖書的圖書編號(hào)、書名和類別名稱插入到tb_bknew表中；（23）將類別編號(hào)為“3”的所有圖書的庫(kù)存數(shù)增加5；（24）將“C+程序設(shè)計(jì)”這本書的歸還日期增加一個(gè)月（函數(shù)DATEADD）。（25）刪除姓名為“張三”的讀者的信息；（26）刪除tb_bknew表中的所有數(shù)據(jù)；（27）創(chuàng)建一個(gè)名為“讀者借閱信息_VIEW”的視圖，要求顯示計(jì)算機(jī)系所有讀者的借閱信息，包括讀者編號(hào)、姓名、所在系、圖書編號(hào)、書名和借閱日期等字段，更新該視圖時(shí)要保證只有計(jì)算機(jī)系的讀者借閱信息；（28）創(chuàng)建一個(gè)名為“圖示借閱信息_VIEW”的視圖，要求顯示圖書的借閱情況，包括圖書編號(hào)、書名、庫(kù)存數(shù)、借閱次數(shù)字段；（29）

12、查詢借閱次數(shù)大于2的圖書的圖書編號(hào)、書名、庫(kù)存數(shù)和借閱次數(shù)；（30）刪除“圖示借閱信息_VIEW”視圖。三、 實(shí)驗(yàn)步驟use mastergocreate database db_Libraryon primary(name=N'db_Library_data',filename='E:SQLdb_Library_data.mdf',size=10MB,maxsize=300MB,filegrowth=10%)log on(name='db_Library_log',filename='E:SQLdb_Library_data.ldf&#

13、39;,size=5MB,maxsize=200MB,filegrowth=2MB)gouse db_Librarygo create schema "L-C" authorization guestcreate table tb_course -課程信息表(Coursenumber int ,Coursename char(20),Thefirstcourse char(20),Credit int)gocreate table tb_booktype -圖書類別信息表(Typenumber int,Categoryname char(20) )gocreate tabl

14、e tb_book -圖書信息表(ISBN int,Typenumber int,Title char(20),Author char(20),BookPublic char(20),BookPrice float,Inventorynumber int)gocreate table tb_reader -讀者信息表( ReaderID char(20), Name char(20),Sex char(2),StudentID int ,Class char(20),Pastern char(20)gocreate table tb_borrow -借閱信息表( ISBN int, Reade

15、rID char(20),Borrowingdate char(20),Returndate char(20)goalter table tb_reader drop column Pastern alter table tb_reader add column Pastern alter table tb_book alter column BookPrice realdrop table tb_course -創(chuàng)建和刪除索引create index IX_S_QUANTITY on tb_book(Inventorynumber)create index IX_S_NAME on tb_r

16、eader(Name asc)drop index IX_S_QUANTITYdrop index IX_S_NAME-圖書類別信息表信息insert into tb_booktype values(1,'數(shù)學(xué)')insert into tb_booktype values(2,'英語(yǔ)')insert into tb_booktype values(3,'計(jì)算機(jī)')insert into tb_booktype values(4,'文學(xué)')insert into tb_booktype values(5,'藝術(shù)')

17、insert into tb_booktype values(6,'電子信息')go-圖書信息表信息insert into tb_book values(10001,3,'數(shù)據(jù)庫(kù)管理','王珊','高等教育出版社',35.50,10)insert into tb_book values(10002,3,'軟件測(cè)試','賀平','機(jī)械工業(yè)出版社',24.60,5)insert into tb_book values(10003,3,'C+程序設(shè)計(jì)','譚浩強(qiáng)&#

18、39;,'清華大學(xué)出版社',30.00,8)insert into tb_book values(10004,4,'紅樓夢(mèng)','曹雪芹','人民文學(xué)出版社',70.00,5)insert into tb_book values(10005,4,'西游記','羅貫中','人民文學(xué)出版社',60.00,8)insert into tb_book values(10006,4,'紅與黑','司湯達(dá)','人民文學(xué)出版社',50.00,5)ins

19、ert into tb_book values(10007,1,'高等數(shù)學(xué)','李翼','清華大學(xué)出版社',28.00,4)insert into tb_book values(10008,8,'有機(jī)化學(xué)','張翔','高等教育出版社',29.00,5)insert into tb_book values(10009,2,'大學(xué)英語(yǔ)','王琳','高等教育出版社',25.00,10)insert into tb_book values(10010,2,

20、'英語(yǔ)教程','王琳','高等教育出版社',25.00,5)go-讀者信息表信息insert into tb_reader values('R10001','張小航','男',0851101,'08511','計(jì)算機(jī)系')insert into tb_reader values('R10002','王文廣','女',0851102,'08511','計(jì)算機(jī)系')insert into tb

21、_reader values('R10003','李理','女',0851103,'08511','計(jì)算機(jī)系')insert into tb_reader values('R10004','李彥宏','男',0851201,'08512','計(jì)算機(jī)系')insert into tb_reader values('R10005','張麗霞','女',0851202,'08512

22、9;,'計(jì)算機(jī)系')insert into tb_reader values('R10006','王強(qiáng)','男',0721104,'07211','電子系')insert into tb_reader values('R10007','張寶田','男',0721204,'07212','電子系')insert into tb_reader values('R10008','宋文霞',

23、9;女',0761104,'07611','建工系')insert into tb_reader values('R10009','劉芳菲','女',0881104,'08811','外語(yǔ)系')insert into tb_reader values('R10010','常江寧','男',0881204,'08812','外語(yǔ)系')go-借閱信息表信息insert into tb_borrow v

24、alues(10002,'R10003','2009-9-20','2009-10-20')insert into tb_borrow values(10003,'R10003','2009-9-20','2009-10-20')insert into tb_borrow values(10004,'R10003','2009-9-30','2009-10-30')insert into tb_borrow values(10009,'R100

25、03','2009-9-30','2009-10-30')insert into tb_borrow values(10009,'R10007','2009-5-20','2009-6-20')insert into tb_borrow values(10010,'R10007','2009-5-20','2009-6-20')insert into tb_borrow values(10009,'R10009','2009-5-30&

26、#39;,'2009-6-30')insert into tb_borrow values(10010,'R10009','2009-5-22','2009-6-22')insert into tb_borrow values(10002,'R10009','2009-5-22','2009-6-22')insert into tb_borrow values(10003,'R10009','2009-5-30','2009-6-30'

27、)go-(1)查詢每本圖書的所有信息；select * from tb_book-（2）查詢每個(gè)讀者的讀者編號(hào)、姓名和班級(jí)；select ReaderID,Name,Classfrom tb_reader-（3）查詢每條借閱記錄的借閱天數(shù)；select datediff(DAY,Borrowingdate,Returndate)from tb_borrow-（4）查詢被借閱過的圖書的圖書編號(hào)；select distinct ISBN from tb_borrow-（5）查詢圖書編號(hào)為“”的書名和作者；select Title,Authorfrom tb_bookwhere ISBN='

28、10006'-（6）查詢庫(kù)存數(shù)在到本之間的圖書的圖書編號(hào)和書名；SELECT ISBN,Title from tb_bookwhere Inventorynumber between 5 and 10-（7）查詢計(jì)算機(jī)系或電子系姓張的讀者信息；select *from tb_readerwhere pastern in('計(jì)算機(jī)系','電子系')-（8）查詢書名包括“英語(yǔ)”的圖書信息；select * from tb_bookwhere Title = '英語(yǔ)'-（9）統(tǒng)計(jì)男讀者、女讀者的人數(shù)；select Sex ,COUNT(*)fr

29、om tb_readergroup by Sex -（10）統(tǒng)計(jì)各類圖書的編號(hào)、平均定價(jià)以及庫(kù)存總數(shù)；select Typenumber,AVG(BookPrice) as 平均定價(jià),SUM(Inventorynumber) as 庫(kù)存總數(shù)from tb_bookgroup by Typenumber -（11）統(tǒng)計(jì)每本書籍借閱的人數(shù)，要求輸出圖書編號(hào)和所借人數(shù)，查詢結(jié)果按人數(shù)降序排列；select ISBN ,COUNT(*)from tb_borrowgroup by ISBNorder by COUNT(*) desc-（12）查詢有庫(kù)存的各類別圖書的類別編號(hào)、類別名稱和借閱數(shù)量；sel

30、ect tb_book.Typenumber ,Categoryname ,COUNT(*)from tb_book,tb_booktype,tb_borrowwhere tb_book.Typenumber =tb_booktype.Typenumber and tb_book.ISBN=tb_borrow.ISBNgroup by tb_book.Typenumber ,tb_booktype.Categoryname -（13）查詢借閱了“大學(xué)英語(yǔ)”一書的讀者，輸出讀者姓名、性別、系部；select Name ,Sex ,Pastern from tb_readerwhere Reade

31、rID in(select ReaderID from tb_borrowwhere ISBN in(select ISBN from tb_book where Title ='大學(xué)英語(yǔ)')-（14）查詢每個(gè)讀者的讀者編號(hào)、姓名、所借圖書編號(hào)及所借閱日期；select tb_reader.ReaderID ,Name ,Borrowingdate from tb_reader left outer join tb_borrow on tb_reader.ReaderID =tb_borrow.ReaderID -（15）查詢現(xiàn)有圖書中價(jià)格最高的圖書，輸出書名、作者、定價(jià)；se

32、lect Title ,Author ,BookPrice from tb_bookwhere BookPrice =(select MAX(BookPrice )from tb_book)-（16）查詢借閱了“大學(xué)英語(yǔ)”但沒有借閱“C+程序設(shè)計(jì)”的讀者，輸出讀者姓名、性別、系部select Name ,Sex , Pastern from tb_reader where Name in (select Name from tb_borrow where ISBN in (select ISBN from tb_book where Title ='大學(xué)英語(yǔ)') and Nam

33、e not in (select Name from tb_borrow where ISBN in (select ISBN from tb_book where Title ='C+程序設(shè)計(jì)') )-（17）統(tǒng)計(jì)借閱了本以上圖書的讀者信息；select *from tb_readerwhere ReaderID in(select ReaderID from tb_borrow group by ReaderID having COUNT(*)>2)-（18）查詢計(jì)算機(jī)系中比其他系所有讀者借書數(shù)量都多的讀者的信息；select * from tb_reader tbr

34、1where Pastern ='計(jì)算機(jī)系' and ReaderID in(select tb_reader.ReaderID from tb_reader,tb_borrowwhere tb_reader.ReaderID=tb_borrow.ReaderID and tb_reader.ReaderID=tbr1.ReaderIDgroup by tb_reader.ReaderID having count(ISBN)>any(select count(ISBN) from tb_reader,tb_borrowwhere tb_reader.ReaderID =

35、tb_borrow.ReaderID and Pastern <>'計(jì)算機(jī)系'group by tb_reader.ReaderID )-（19）查詢借閱了“大學(xué)英語(yǔ)”一書或者借閱了“C+程序設(shè)計(jì)”一書的讀者信息；（用集合查詢完成）select * from tb_readerwhere ReaderID in (select ReaderID from tb_borrow,tb_book where tb_borrow.ISBN=tb_book.ISBN and Title ='大學(xué)英語(yǔ)')unionselect * from tb_readerw

36、here ReaderID in (select ReaderID from tb_borrow,tb_book where tb_borrow.ISBN=tb_book.ISBN and Title='C+程序設(shè)計(jì)')-20）查詢既借閱了“大學(xué)英語(yǔ)”一書又借閱了“C+程序設(shè)計(jì)”一書的讀者信息。（用集合查詢完成）select * from tb_readerwhere ReaderID in (select ReaderID from tb_borrow,tb_book where tb_borrow.ISBN =tb_book.ISBN and Title='大學(xué)英語(yǔ)

37、')intersectselect * from tb_readerwhere ReaderID in (select ReaderID from tb_borrow,tb_book where tb_borrow.ISBN=tb_book.ISBN and Title='C+程序設(shè)計(jì)')-21）在讀者信息表中插入一條新的記錄（讀者編號(hào)：R10011；姓名：張三；所在系：電子系）；insert into tb_reader(ReaderID,Sex ,Pastern ) values ('R10011','張三','電子系')-（22）假設(shè)存在一個(gè)表tb_booknew，包含圖書編號(hào)、書名和類別名稱字段，要求將類編編號(hào)為“”的圖書的圖書編號(hào)、書名和類別名稱插入到tb_bknew表中；create table tb_booknew(ISBN char(10),Title varchar(20),Typename varchar(20)insert into tb_booknewselect tb_book.ISBN ,tb_book.Title,tb_booktype.Categoryname from tb_b