數(shù)據(jù)庫(kù)-SQL語(yǔ)言的實(shí)驗(yàn)12頁(yè)

1、北 華 航 天 工 業(yè) 學(xué) 院數(shù)據(jù)庫(kù)原理與應(yīng)用實(shí)驗(yàn)報(bào)告報(bào)告題目： SQL語(yǔ)言 作者所在系部： 計(jì)算機(jī)系 作者所在專(zhuān)業(yè)： 網(wǎng)絡(luò)工程 作 者 學(xué) 號(hào) ： 20114052233 作 者 姓 名 ： 張旭剛 指導(dǎo)教師姓名： 李建義 完 成 時(shí) 間 ： 2013 4.24 北華航天工業(yè)學(xué)院教務(wù)處制SQL語(yǔ)言一、 實(shí)驗(yàn)?zāi)康?、 理解數(shù)據(jù)庫(kù)以及數(shù)據(jù)表的設(shè)計(jì)；2、 熟悉SQL Server2005中的數(shù)據(jù)類(lèi)型；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、 掌握

2、使用SQL語(yǔ)句創(chuàng)建、刪除、查詢和更新視圖。二、 實(shí)驗(yàn)內(nèi)容（一）創(chuàng)建數(shù)據(jù)庫(kù)和模式1、通過(guò)SQL語(yǔ)句創(chuàng)建圖書(shū)信息管理數(shù)據(jù)庫(kù)，命名為“db_Library”，數(shù)據(jù)文件和日志文件放在D盤(pán)下以自己學(xué)號(hào)和姓名命名的文件夾中，數(shù)據(jù)文件的邏輯名為db_Library_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、通過(guò)SQL語(yǔ)句在該數(shù)據(jù)庫(kù)中創(chuàng)建模式L-C

3、。（二）創(chuàng)建和管理數(shù)據(jù)表要求為各數(shù)據(jù)表的字段選擇合適的數(shù)據(jù)類(lèi)型及名稱(chēng)；為各數(shù)據(jù)表設(shè)置相應(yīng)的完整性約束條件。1、通過(guò)SQL語(yǔ)句將以下數(shù)據(jù)表創(chuàng)建在L-C模式下：課程信息表（tb_course）課程編號(hào)、課程名、先修課、學(xué)分2、通過(guò)SQL語(yǔ)句將以下數(shù)據(jù)表創(chuàng)建在該數(shù)據(jù)庫(kù)的默認(rèn)模式dbo下：圖書(shū)類(lèi)別信息表（tb_booktype)類(lèi)別編號(hào)、類(lèi)別名稱(chēng)圖書(shū)信息表（tb_book）圖書(shū)編號(hào)、類(lèi)別編號(hào)、書(shū)名、作者、出版社、定價(jià)、庫(kù)存數(shù)讀者信息表（tb_reader）讀者編號(hào)、姓名、性別、學(xué)號(hào)、班級(jí)、系部借閱信息表（tb_borrow)圖書(shū)編號(hào)、讀者編號(hào)、借閱日期、歸還日期3、通過(guò)SQL語(yǔ)句對(duì)讀者信息表進(jìn)行修改：

4、刪除系部字段、添加所在系字段。4、通過(guò)SQL語(yǔ)句對(duì)圖書(shū)信息表進(jìn)行修改：將定價(jià)的數(shù)據(jù)類(lèi)型改為REAL。5、通過(guò)SQL語(yǔ)句刪除課程信息表。（三）創(chuàng)建和刪除索引1、使用SQL語(yǔ)句在圖書(shū)信息表上創(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ì)根據(jù)周?chē)膶?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é)

5、構(gòu)設(shè)計(jì)。例如可選擇學(xué)籍管理系統(tǒng)、企業(yè)進(jìn)銷(xiāo)存管理系統(tǒng)、人事管理系統(tǒng)或在線考試系統(tǒng)等。（五）數(shù)據(jù)查詢通過(guò)SSMS向各數(shù)據(jù)表中添加以下記錄。（1）圖書(shū)類(lèi)別信息表類(lèi)別編號(hào)類(lèi)別名稱(chēng)類(lèi)別編號(hào)類(lèi)別名稱(chēng)類(lèi)別編號(hào)類(lèi)別名稱(chēng)1數(shù)學(xué)4文學(xué)7建筑2英語(yǔ)5藝術(shù)8化學(xué)3計(jì)算機(jī)6電子信息9物理（2）圖書(shū)信息表圖書(shū)編號(hào)類(lèi)別編號(hào)書(shū)名作者出版社定價(jià)庫(kù)存數(shù)100013數(shù)據(jù)庫(kù)管理王珊高等教育出版社35.5010100023軟件測(cè)試賀平機(jī)械工業(yè)出版社24.605100033C+程序設(shè)計(jì)譚浩強(qiáng)清華大學(xué)出版社30.008100044紅樓夢(mèng)曹雪芹人民文學(xué)出版社70.005100054西游記羅貫中人民文學(xué)出版社60.008100064紅與黑司湯

6、達(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李彥宏男085120108512計(jì)算機(jī)系R10005張麗霞女085120208512計(jì)算機(jī)系R10006王強(qiáng)男072110407211電子系R10007張寶田

7、男072120407212電子系R10008宋文霞女076110407611建工系R10009劉芳菲女088110408811外語(yǔ)系R10010常江寧男088120408812外語(yǔ)系（4）借閱信息表圖書(shū)編號(hào)讀者編號(hào)借閱日期歸還日期10002R100032009-9-202009-10-2010003R100032009-9-202009-10-2010004R100032009-9-302009-10-3010009R100032009-9-302009-10-3010009R100072009-5-202009-6-2010010R100072009-5-202009-6-2010009R1

8、00092009-5-302009-6-3010010R100092009-5-222009-6-2210002R100092009-5-222009-6-2210003R100092009-5-302009-6-30對(duì)以上數(shù)據(jù)表，完成以下操作：（1）查詢每本圖書(shū)的所有信息；（2）查詢每個(gè)讀者的讀者編號(hào)、姓名和班級(jí)；（3）查詢每條借閱記錄的借閱天數(shù)（函數(shù)DATEDIFF獲取兩個(gè)日期的差）；（4）查詢被借閱過(guò)的圖書(shū)的圖書(shū)編號(hào)；（5）查詢圖書(shū)編號(hào)為“10006”的書(shū)名和作者；（6）查詢庫(kù)存數(shù)在5到10本之間的圖書(shū)的圖書(shū)編號(hào)和書(shū)名；（7）查詢計(jì)算機(jī)系或電子系姓張的讀者信息；（8）查詢書(shū)名包括“英語(yǔ)”

9、的圖書(shū)信息；（9）統(tǒng)計(jì)男讀者、女讀者的人數(shù)；（10）統(tǒng)計(jì)各類(lèi)圖書(shū)的類(lèi)別編號(hào)、平均定價(jià)以及庫(kù)存總數(shù)；（11）統(tǒng)計(jì)每本書(shū)籍借閱的人數(shù)，要求輸出圖書(shū)編號(hào)和所借人數(shù)，查詢結(jié)果按人數(shù)降序排列；（12）查詢有庫(kù)存的各類(lèi)別圖書(shū)的類(lèi)別編號(hào)、類(lèi)別名稱(chēng)和借閱數(shù)量；（13）查詢借閱了“大學(xué)英語(yǔ)”一書(shū)的讀者，輸出讀者姓名、性別、系部；（14）查詢每個(gè)讀者的讀者編號(hào)、姓名、所借圖書(shū)編號(hào)以及所借閱日期；（LEFT OUTER JOIN）（15）查詢現(xiàn)有圖書(shū)中價(jià)格最高的圖書(shū)，輸出書(shū)名、作者、定價(jià)；（16）查詢借閱了“大學(xué)英語(yǔ)”但沒(méi)有借閱“C+程序設(shè)計(jì)”的讀者，輸出讀者姓名、性別、系部；（17）統(tǒng)計(jì)借閱了2本以上圖書(shū)的讀者信

10、息；（18）查詢借閱了“大學(xué)英語(yǔ)”一書(shū)或者借閱了“C+程序設(shè)計(jì)”一書(shū)的讀者信息；（用集合查詢完成）（19）查詢既借閱了“大學(xué)英語(yǔ)”一書(shū)又借閱了“C+程序設(shè)計(jì)”一書(shū)的讀者信息；（用集合查詢完成）（20）查詢計(jì)算機(jī)系中比其他系所有讀者借書(shū)數(shù)量都多的讀者的信息；（21）在讀者信息表中插入一條新的記錄（讀者編號(hào)：R10011；姓名：張三；所在系：電子系）；（22）定義一個(gè)表tb_booknew，包含圖書(shū)編號(hào)、書(shū)名和類(lèi)別名稱(chēng)字段，要求將類(lèi)編編號(hào)為“3”的圖書(shū)的圖書(shū)編號(hào)、書(shū)名和類(lèi)別名稱(chēng)插入到tb_bknew表中；（23）將類(lèi)別編號(hào)為“3”的所有圖書(shū)的庫(kù)存數(shù)增加5；（24）將“C+程序設(shè)計(jì)”這本書(shū)的歸還日期

11、增加一個(gè)月（函數(shù)DATEADD）。（25）刪除姓名為“張三”的讀者的信息；（26）刪除tb_bknew表中的所有數(shù)據(jù)；（27）創(chuàng)建一個(gè)名為“讀者借閱信息_VIEW”的視圖，要求顯示計(jì)算機(jī)系所有讀者的借閱信息，包括讀者編號(hào)、姓名、所在系、圖書(shū)編號(hào)、書(shū)名和借閱日期等字段，更新該視圖時(shí)要保證只有計(jì)算機(jī)系的讀者借閱信息；（28）創(chuàng)建一個(gè)名為“圖示借閱信息_VIEW”的視圖，要求顯示圖書(shū)的借閱情況，包括圖書(shū)編號(hào)、書(shū)名、庫(kù)存數(shù)、借閱次數(shù)字段；（29）查詢借閱次數(shù)大于2的圖書(shū)的圖書(shū)編號(hào)、書(shū)名、庫(kù)存數(shù)和借閱次數(shù)；（30）刪除“圖示借閱信息_VIEW”視圖。三、 實(shí)驗(yàn)步驟 （1）查詢每本圖書(shū)的所有信息； sel

12、ect * from tb_book; （2） 查詢每個(gè)讀者的讀者編號(hào)、姓名和班級(jí)； select Rnum,name,class from tb_reader; (2) (3) (4)（3） 查詢每條借閱記錄的借閱天數(shù)（函數(shù)DATEDIFF獲取兩個(gè)日期的差）； select 'Borrowday'=datediff(DD,Bodata,redata) from tb_borrow; （4）查詢被借閱過(guò)的圖書(shū)的圖書(shū)編號(hào)； select Bnum from tb_borrow;（5） 查詢圖書(shū)編號(hào)為“10006”的書(shū)名和作者； select Bname,writer from t

13、b_book where Bnum='10006'（6） 查詢庫(kù)存數(shù)在5到10本之間的圖書(shū)的圖書(shū)編號(hào)和書(shū)名； select Bnum,Bname from tb_book where Knum between 5 and 10;（7） 查詢計(jì)算機(jī)系或電子系姓張的讀者信息； select * from tb_reader where (name like '張%'and szxibu='計(jì)算機(jī)系') or( name like '張%'and szxibu='電子系'); (6) (7)（8）查詢書(shū)名包括“英語(yǔ)”的圖

14、書(shū)信息； select * from tb_book where Bname like '%英語(yǔ)%'（9）統(tǒng)計(jì)男讀者、女讀者的人數(shù)； select Rsex,'num'=count(*) from tb_reader group by Rsex; （10）統(tǒng)計(jì)各類(lèi)圖書(shū)的類(lèi)別編號(hào)、平均定價(jià)以及庫(kù)存總數(shù)； select Tynum,'Average price'=AVG(price),'Bookstore'=SUM(Knum) from tb_book group by Tynum; （11）統(tǒng)計(jì)每本書(shū)籍借閱的人數(shù)，要求輸出圖書(shū)編號(hào)和

15、所借人數(shù)，查詢結(jié)果按人數(shù)降序排列； select Bnum,'num'=COUNT(Rnum) rom tb_borrow group by Bnum order by num desc; （12）查詢有庫(kù)存的各類(lèi)別圖書(shū)的類(lèi)別編號(hào)、類(lèi)別名稱(chēng)和借閱數(shù)量； select tb_booktype.Tynum,tb_booktype.Tyname,'quantity'=COUNT(*) from tb_book,tb_booktype,tb_borrow where tb_book.Knum>0 and tb_book.Tynum=tb_booktype.Tynu

16、m and tb_book.Bnum =tb_borrow.Bnum group by tb_booktype.Tynum,tb_booktype.Tyname; （13）查詢借閱了“大學(xué)英語(yǔ)”一書(shū)的讀者，輸出讀者姓名、性別、系部； select name,Rsex,szxibu from tb_reader where Rnum in ( select Rnum from tb_borrow where Bnum= (select Bnum from tb_book where Bname='大學(xué)英語(yǔ)' ) ); （14）查詢每個(gè)讀者的讀者編號(hào)、姓名、所借圖書(shū)編號(hào)以及所借閱日

17、期；（LEFT OUTER JOIN） select tb_reader.Rnum,tb_,Bnum, Bodata from tb_reader LEFT OUTER JOIN tb_borrow on (tb_reader.Rnum=tb_borrow.Rnum); （見(jiàn)下頁(yè)）（15）查詢現(xiàn)有圖書(shū)中價(jià)格最高的圖書(shū)，輸出書(shū)名、作者、定價(jià)； select Bname,writer,price from tb_book where price= (select MAX(price) from tb_book); （16）查詢借閱了“大學(xué)英語(yǔ)”但沒(méi)有借閱“C+程序設(shè)計(jì)”的讀者

18、，輸出讀者姓名、性別、系部； (14)select name,Rsex,szxibufrom tb_readerwhere Rnum in( select Rnum from tb_borrow where Bnum = (select Bnum from tb_book where Bname='大學(xué)英語(yǔ)') ) except select name,Rsex,szxibu from tb_reader where Rnum in (select Rnum from tb_borrow where Bnum = (select Bnum from tb_book where

19、Bname='C+程序設(shè)計(jì)') ); （17） 統(tǒng)計(jì)借閱了2本以上圖書(shū)的讀者信息； select * from tb_reader where Rnum in( select Rnum from tb_borrow group by Rnum having count(*)>=2); （18）查詢借閱了“大學(xué)英語(yǔ)”一書(shū)或者借閱了“C+程序設(shè)計(jì)”一書(shū)的讀者信息；（用集合查詢完成） select * from tb_reader where Rnum in ( select Rnum from tb_borrow where Bnum in( select Bnum from

20、 tb_book where Bname in('C+程序設(shè)計(jì)','大學(xué)英語(yǔ)') ); （19）查詢既借閱了“大學(xué)英語(yǔ)”一書(shū)又借閱了“C+程序設(shè)計(jì)”一書(shū)的讀者信息；（用集合查詢完成） select * from tb_reader where Rnum in ( select Rnum from tb_borrow where tb_borrow.Bnum in (select Bnum from tb_book where Bname='大學(xué)英語(yǔ)') intersect select Rnum from tb_borrow,tb_book wh

21、ere tb_borrow.Bnum in (select Bnum from tb_book where Bname='C+程序設(shè)計(jì)' ) ); （20）查詢計(jì)算機(jī)系中比其他系所有讀者借書(shū)數(shù)量都多的讀者的信息； select * from tb_reader where szxibu ='計(jì)算機(jī)系' and Rnum in (select tb_reader.Rnumfrom tb_reader,tb_borrowwhere szxibu ='計(jì)算機(jī)系' and tb_reader.Rnum=tb_borrow.Rnumgroup by tb_

22、reader.Rnum having COUNT(*)>all (select COUNT (*)from tb_reader,tb_borrow where szxibu !='計(jì)算機(jī)系' and tb_reader.Rnum=tb_borrow.Rnumgroup by tb_reader.Rnum) （21）在讀者信息表中插入一條新的記錄（讀者編號(hào)：R10011；姓名：張三；所在系：電子系）； INSERT INTO tb_reader(Rnum,name,szxibu) VALUES('R10011','張三','電子系&#

23、39;); （22）定義一個(gè)表tb_booknew，包含圖書(shū)編號(hào)、書(shū)名和類(lèi)別名稱(chēng)字段，要求將類(lèi)編編號(hào)為“3”的圖書(shū)的圖書(shū)編號(hào)、書(shū)名和類(lèi)別名稱(chēng)插入到tb_bknew表中； create table tb_booknew (Bnum char(10) primary key, Bname char(20), Tyname char(20) insert into tb_booknew select tb_book.Bnum,tb_book.Bname,tb_booktype.Tyname from tb_book,tb_booktype where tb_book.Tynum=tb_booktyp

24、e.Tynum and tb_book.Tynum='3' （23）將類(lèi)別編號(hào)為“3”的所有圖書(shū)的庫(kù)存數(shù)增加10； update tb_book set Knum=Knum+10 where Tynum='3'（24） 將“C+程序設(shè)計(jì)”這本書(shū)的歸還日期增加二個(gè)月（函數(shù)DATEADD）。 update tb_borrow set redata=DATEADD(MONTH,1,redata) where Bnum in (select Bnum from tb_book where Bname='C+程序設(shè)計(jì)') （25） 刪除姓名為“張三”的讀者

25、的信息； delete from tb_reader where name='張三'（26） 刪除tb_bknew表中的所有數(shù)據(jù)； delete from tb_booknew（27） 創(chuàng)建一個(gè)名為“讀者借閱信息_VIEW”的視圖，要求顯示計(jì)算機(jī)系所有讀者的借閱信息，包括讀者編號(hào)、姓名、所在系、圖書(shū)編號(hào)、書(shū)名和借閱日期等字段，更新該視圖時(shí)要保證只有計(jì)算機(jī)系的讀者借閱信息； create view 讀者借閱信息_VIEW(Rnum,name,szxibu, Bname,Bodata,redata) as select tb_reader.Rnum,name,szxibu,tb_book.Bname,Bodata,redata from tb_borrow,tb_reader,tb_book where tb_borrow.Rnum=tb_reader.Rnum and tb_book.Bnum=tb_borrow.Bnum and tb_borrow.Rnum in (se