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

1、實(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、通過(guò)SQL語(yǔ)句創(chuàng)建圖書信息管理數(shù)據(jù)庫(kù)，命名為“ db_Library ，數(shù)據(jù)文件和日志文件放在D盤下以自己學(xué)號(hào)和姓名命名的文件夾中，數(shù)據(jù)文件的邏輯名為db_Library_data ,數(shù)據(jù)文件的操作系統(tǒng)名為db_Lib

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

3、、先修課The first course 、 學(xué)分 credit2、通過(guò)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借閱信息表（

4、tb_borrow）圖書編號(hào)、讀者編號(hào)、借閱日期Borrowing date、歸還日期Return date3、通過(guò)SQL語(yǔ)句對(duì)讀者信息表進(jìn)行修改：刪除系部字段、添加所在系字段。4、通過(guò)SQL語(yǔ)句對(duì)圖書信息表進(jìn)行修改：將定價(jià)的數(shù)據(jù)類型改為REAL。5、通過(guò)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è)索引。（四）數(shù)據(jù)庫(kù)及數(shù)據(jù)表設(shè)計(jì)根

5、據(jù)周圍的實(shí)際應(yīng)用情況，自選一個(gè)小型的數(shù)據(jù)庫(kù)應(yīng)用項(xiàng)目進(jìn)行研究，完成該系統(tǒng)的設(shè)計(jì)。要求2人一組，通過(guò)需求分析，列出系統(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ù)查詢通過(guò)SSMS向各數(shù)據(jù)表中添加以下記錄。(1)圖書類別信息表類別編號(hào)類別名稱類別編號(hào)類別名稱類別編號(hào)類別名稱1數(shù)學(xué)4乂字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+程序設(shè)計(jì)

6、譚浩強(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)讀者信息表讀名編p姓名性別學(xué)號(hào)班級(jí)所在系R10001張小航男085110108511計(jì)算機(jī)系R10002王文J女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)讀名編p借閱日期歸還日期10002R100032009-9-202009-10-2010003R100032009-9-202009-10-2010004R100032009-9-302009-10-3010009R100032009-9-

8、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)查詢被借閱過(guò)的圖書的圖書編號(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)有圖書

10、中價(jià)格最高的圖書，輸出書名、作者、定價(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)

11、、書名和類別名稱字段， 要求將類編編號(hào)為“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”的視圖，要求顯示圖書

12、的借閱情況，包括圖書編號(hào)、書名、庫(kù)存數(shù)、借閱次數(shù)字段;(29)查詢借閱次數(shù)大于 2的圖書的圖書編號(hào)、書名、庫(kù)存數(shù)和借閱次數(shù);(30)刪除“圖示借閱信息 _VIEW”視圖。三、實(shí)驗(yàn)步驟use mastergocreate database db_Libraryon primary(name = Ndb_Library_data ,filename = E:SQLdb_Library_data.mdf ,size =10 MB ,maxsize = 300 MB ,filegrowth =10%)log on(name =db_Library_log ,filename = E:SQLdb_Lib

13、rary_data.ldf ,size =5 MB ,maxsize = 200 MB ,filegrowth = 2 MBgouse db_Librarygocreate 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)go

14、create table 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 -借

15、閱信息表(ISBN int,ReaderID char (20),Borrowingdate char (20),Returndate char (20)goalter table tb_reader drop column Pastern alter table tb_reader add column Pasternalter table tb_book alter column BookPrice real drop table tb_course-創(chuàng)建和刪除索引create index IX_S_QUANTITY on tb_book （Inventorynumber ） create

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

17、）values （6,電子信息）go-圖書信息表信息insert into tb_bookvalues （10001 ,3,數(shù)據(jù)庫(kù)管理,王珊,高等教育出版社,35.50,10）insert into tb_bookvalues （10002 ,3,軟件測(cè)試,賀平,機(jī)械工業(yè)出版社,24.60 ,5）insert into tb_bookvalues （10003 ,3,C+程序設(shè)計(jì),譚浩強(qiáng),清華大學(xué)出版社,30.00 ,8）insert into tb_bookvalues （10004 ,4,紅樓夢(mèng),曹雪芹,人民文學(xué)出版社,70.00 ,5）insert into tb_bookvalues

18、（10005 ,4,西游記,羅貫中,人民文學(xué)出版社,60.00 ,8）insert into tb_bookvalues （10006 ,4,紅與黑,司湯達(dá),人民文學(xué)出版社,50.00 ,5）insert 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_bookvalues （10009 ,2,大學(xué)英語(yǔ)，王琳，高等教育出版社,25.00 ,10）insert into tb_b

19、ookvalues （10010 ,2,英語(yǔ)教程,王琳,高等教育出版社,25.00 ,5）go-讀者信息表信息insert into tb_readervalues (R10001，張小航,男,0851101 ,08511,計(jì)算機(jī)系)insert into tb_reader values (R10002,王文J女,0851102 ,08511,計(jì)算機(jī)系)insert into tb_reader values (R10003,李理,女,0851103,08511,計(jì)算機(jī)系)insert into tb_readervalues (R10004,李彥宏，男,0851201,08512,計(jì)算機(jī)系

20、)女,0851202 ,08512,計(jì)算機(jī)系)insert into tb_reader values (R10005,張麗霞insert into tb_readervalues (R10006,王強(qiáng),男,0721104 ,07211,電子系)insert into tb_readervalues (R10007，張寶田男,0721204 ,07212,電子系)insert into tb_readervalues (R10008,宋文霞女,0761104 ,07611,建工系)insert into tb_readervalues (R10009，劉芳菲女,0881104 ,08811，外

21、語(yǔ)系)insert into tb_readervalues (R10010,常江寧男,0881204 ,08812,外語(yǔ)系)go-借閱信息表信息insert into tb_borrowvalues (10002 ,R10003 ,2009-9-20 ,2009-10-20)insert into tb_borrowvalues (10003 ,R10003 ,2009-9-20 ,2009-10-20)insert into tb_borrowvalues (10004 ,R10003 ,2009-9-30 ,2009-10-30)insert into tb_borrowvalues (

22、10009 ,R10003 ,2009-9-30,2009-10-30insert 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)insertintotb_borrowvalues (10009 ,R10009,2009-5-30,2009-6-30)insertintotb_borrowvalues (10010 ,R10009,2009-5-22,2009-6-22)insertintot

23、b_borrowvalues (10002 ,R10009,2009-5-22,2009-6-22)insertintotb_borrowvalues (10003 ,R10009,2009-5-30,2009-6-30)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- -

24、(4)查詢被借閱過(guò)的圖書的圖書編號(hào)；select distinct ISBNfrom tb_borrow- -(5)查詢圖書編號(hào)為“”的書名和作者；select Title ,Authorfrom tb_book where ISBN =10006- -(6)查詢庫(kù)存數(shù)在到本之間的圖書的圖書編號(hào)和書名；SELECT ISBN ,Titlefrom tb_bookwhere Inventorynumber between 5 and 10- -(7)查詢計(jì)算機(jī)系或電子系姓張的讀者信息；select *from tb_readerwhere pastern in(計(jì)算機(jī)系,電子系)- -(8)查詢

25、書名包括“英語(yǔ)”的圖書信息；select * from tb_bookwhere Title =英語(yǔ)- -(9)統(tǒng)計(jì)男讀者、女讀者的人數(shù)；select Sex COUNT (*) from 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

26、 ISBN ,COUNT (*) from tb_borrowgroup by ISBNorder by COUNT (*) desc- -(12)查詢有庫(kù)存的各類別圖書的類別編號(hào)、類別名稱和借閱數(shù)量；select tb_book .Typenumber ,Categoryname ,COUNT (*)from tb_book ,tb_booktype ,tb_borrow tb_book .ISBN = tb_borrow .ISBNwheretb_book .Typenumber=tb_booktype .Typenumberandgroup by tb_book .Typenumber

27、,tb_booktype .Categoryname- -(13)查詢借閱了 “大學(xué)英語(yǔ)” 一書的讀者，輸出讀者姓名、性別、系部；select Name ,Sex ,Pasternfrom tb_readerwhere ReaderID in(select ReaderIDfrom tb_borrowwhere ISBN in(select ISBN from tb_book where Title =大學(xué)英語(yǔ))- -(14)查詢每個(gè)讀者的讀者編號(hào)、姓名、所借圖書編號(hào)及所借閱日期；select tb_reader .ReaderID ,Name ,Borrowingdatefrom tb_re

28、ader left outer join tb_borrow on tb_reader .ReaderID = tb_borrow .ReaderID- -(15)查詢現(xiàn)有圖書中價(jià)格最高的圖書，輸出書名、作者、定價(jià)；select Title ,Author ,BookPricefrom tb_bookwhere BookPrice =(select MAX (BookPrice )from tb_book )- -(16)查詢借閱了 “大學(xué)英語(yǔ)”但沒有借閱“C+程序設(shè)計(jì)”的讀者，輸出讀者姓名、性別、系部select Name ,Sex , Pasternfrom tb_reader where

29、 Name in (select Namefrom tb_borrowwhere ISBN in (select ISBN from tb_book where Title =大學(xué)英語(yǔ))and Name not in (select Name from tb_borrowwhere 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

30、ReaderID having COUNT (*) 2)- -(18)查詢計(jì)算機(jī)系中比其他系所有讀者借書數(shù)量都多的讀者的信息； select * from tb_reader tbr1where 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 .ReaderIDhavin

31、g count (ISBN )anyselect count (ISBN ) from tb_reader ,tb_borrowwheretb reader .ReaderlD = tb borrow .ReaderlD and Pastern 計(jì)算機(jī)系groupby tb_reader .ReaderID(19)查詢借閱了 “大學(xué)英語(yǔ)” 一書或者借閱了 “C+程序設(shè)計(jì)書的讀者信息；（用集合查詢完成）select *from tbreaderwhereReaderIDin(selectReaderIDfromtb_borrow ,tb_bookwheretbborrow .ISBN=tbboo

32、k.ISBN andTitle =大學(xué)英語(yǔ)）unionselect * from tbreaderwhereReaderIDin(selectReaderIDfromtb_borrow ,tb_bookwheretbborrow .ISBN=tbbook.ISBN andTitle =C+程序設(shè)計(jì))-20 )查詢既借閱了 “大學(xué)英語(yǔ)” 一書又借閱了 “C+程序設(shè)計(jì)”書的讀者信息。（用集合查詢完成）select* from tbreaderwhereReaderID in (select ReaderID from tb_borrow ,tb_book where tb_borrow .ISBN

33、=tb_book .ISBN and Title =大學(xué)英語(yǔ))intersectselect * from tb_readerwhere ReaderID in(select ReaderID fromtb_borrow ,tb_bookwheretb_borrow .ISBN = tb_book .ISBN and Title =C+ 程序設(shè)計(jì))- -21 )在讀者信息表中插入一條新的記錄(讀者編號(hào)：R10011 ;姓名：張三；所在系：電子系)insert into tb_reader (ReaderlD ,Sex ,Pastern ) values (R10011,張三，電子系)- -(2

34、2)假設(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 .Categorynamefrom tb_book ,tb_booktypewhere tb_book .Typenumber =tb_booktype .Typenumber and tb_book .Typenumber =3- -(23)將類