實驗2——SQL語言

1、北 華 航 天 工 業(yè) 學 院數(shù)據(jù)庫原理與應用實驗報告 報告題目： SQL語言 作者所在院系：計算機與遙感信息技術學院 作者所在專業(yè)： 軟件工程 作 者 學 號 ： 2015405A607 作 者 姓 名 ： 武俊其 指導教師姓名： 張春娥 完 成 時 間 ： 2016.11.2 北華航天工業(yè)學院教務處制SQL語言一、 實驗目的1、 理解數(shù)據(jù)庫以及數(shù)據(jù)表的設計；2、 熟悉SQL Server2008中的數(shù)據(jù)類型；3、 熟悉使用SQL語句創(chuàng)建和刪除模式和索引；4、 掌握使用SQL語句創(chuàng)建、修改和刪除數(shù)據(jù)表；5、 掌握使用SQL語句查詢表中的數(shù)據(jù)；6、 掌握使用SQL語句插入、修改和刪除數(shù)據(jù)表中的

2、數(shù)據(jù)；7、 掌握使用SQL語句創(chuàng)建、刪除、查詢和更新視圖。二、 實驗內(nèi)容（一）創(chuàng)建數(shù)據(jù)庫和模式1、通過SQL語句創(chuàng)建圖書信息管理數(shù)據(jù)庫，命名為“db_Library”，數(shù)據(jù)文件和日志文件放在D盤下以自己學號和姓名命名的文件夾中，數(shù)據(jù)文件的邏輯名為db_Library_data，數(shù)據(jù)文件的物理名為db_Library_data.mdf，文件初始大小為10MB，最大可增加至300MB，增幅為10%；日志文件的邏輯名為db_Library_log，日志文件的物理名為db_Library_data.ldf，文件初始大小為5MB，最大可增加至200MB，增幅為2MB。（參照SQL Server 2008

3、聯(lián)機叢書）2、通過SQL語句在該數(shù)據(jù)庫中創(chuàng)建模式L_C。（二）創(chuàng)建和管理數(shù)據(jù)表要求為各數(shù)據(jù)表的字段選擇合適的數(shù)據(jù)類型及名稱；為各數(shù)據(jù)表設置相應的完整性約束條件。1、通過SQL語句將以下數(shù)據(jù)表創(chuàng)建在L_C模式下：課程信息表（tb_course）課程編號、課程名、先修課、學分2、通過SQL語句將以下數(shù)據(jù)表創(chuàng)建在該數(shù)據(jù)庫的默認模式dbo下：圖書類別信息表（tb_booktype)類別編號、類別名稱圖書信息表（tb_book）圖書編號、類別編號、書名、作者、出版社、定價、庫存數(shù)讀者信息表（tb_reader）讀者編號、姓名、性別、學號、班級、系部借閱信息表（tb_borrow)圖書編號、讀者編號、借閱

4、日期、歸還日期3、通過SQL語句對讀者信息表進行修改：刪除系部字段、添加所在系字段。4、通過SQL語句對圖書信息表進行修改：將定價的數(shù)據(jù)類型改為REAL。5、通過SQL語句刪除課程信息表。（三）創(chuàng)建和刪除索引1、使用SQL語句在圖書信息表上創(chuàng)建一個非聚簇索引IX_S_QUANTITY，要求按照該表中庫存數(shù)字段的降序創(chuàng)建。2、使用SQL語句在讀者信息表上創(chuàng)建一個唯一的非聚簇索引IX_S_NAME，要求按照該表中的姓名字段的升序創(chuàng)建。3、使用SQL語句刪除之前創(chuàng)建的兩個索引。（四）數(shù)據(jù)庫及數(shù)據(jù)表設計根據(jù)周圍的實際應用情況，自選一個小型的數(shù)據(jù)庫應用項目進行研究，完成該系統(tǒng)的設計。通過需求分析，列出系

5、統(tǒng)的主要功能，并完成該系統(tǒng)數(shù)據(jù)庫的邏輯結構設計。例如可選擇學籍管理系統(tǒng)、企業(yè)進銷存管理系統(tǒng)、人事管理系統(tǒng)或在線考試系統(tǒng)等。（五）數(shù)據(jù)查詢通過SSMS向各數(shù)據(jù)表中添加以下記錄。（1）圖書類別信息表類別編號類別名稱類別編號類別名稱類別編號類別名稱1數(shù)學4文學7建筑2英語5藝術8化學3計算機6電子信息9物理（2）圖書信息表圖書編號類別編號書名作者出版社定價庫存數(shù)100013數(shù)據(jù)庫管理王珊高等教育出版社35.5010100023軟件測試賀平機械工業(yè)出版社24.605100033C+程序設計譚浩強清華大學出版社30.008100044紅樓夢曹雪芹人民文學出版社70.005100054西游記羅貫中人民文學

6、出版社60.008100064紅與黑司湯達人民文學出版社50.005100071高等數(shù)學李翼清華大學出版社28.004100088有機化學張翔高等教育出版社29.005100092大學英語王琳高等教育出版社25.0010100102英語教程王琳高等教育出版社25.005（3）讀者信息表讀者編號姓名性別學號班級所在系R10001張小航男135110113511計算機系R10002王文廣女135110213511計算機系R10003李理女135110313511計算機系R10004李彥宏男135120113512計算機系R10005張麗霞女135120213512計算機系R10006王強男1221

7、10412211電子系R10007張寶田男122120412212電子系R10008宋文霞女126110412611建工系R10009劉芳菲女138110413811外語系R10010常江寧男138120413812外語系（4）借閱信息表圖書編號讀者編號借閱日期歸還日期10002R100032014-9-202014-10-2010003R100032014-9-202014-10-2010004R100032014-9-302014-10-3010009R100032014-9-302014-10-3010009R100072014-5-202014-6-2010010R100072014-

8、5-202014-6-2010009R100092014-5-302014-6-3010010R100092014-5-222014-6-2210002R100092014-5-222014-6-2210003R100092014-5-302014-6-30對以上數(shù)據(jù)表，完成以下操作：（1）查詢每本圖書的所有信息；（2）查詢每個讀者的讀者編號、姓名和班級；（3）查詢每條借閱記錄的借閱天數(shù)（函數(shù)DATEDIFF獲取兩個日期的差）；（4）查詢被借閱過的圖書的圖書編號；（5）查詢圖書編號為“10006”的書名和作者；（6）查詢庫存數(shù)在5到10本之間的圖書的圖書編號和書名；（7）查詢計算機系或電子系姓

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

10、；（17）統(tǒng)計借閱了2本以上圖書的讀者信息；（18）查詢借閱了“大學英語”一書或者借閱了“C+程序設計”一書的讀者信息；（用集合查詢完成）（19）查詢既借閱了“大學英語”一書又借閱了“C+程序設計”一書的讀者信息；（用集合查詢完成）（20）查詢計算機系中比其他系所有讀者借書數(shù)量都多的讀者的信息；（21）在讀者信息表中插入一條新的記錄（讀者編號：R10011；姓名：張三；所在系：電子系）；（22）定義一個表tb_bknew，包含圖書編號、書名和類別名稱字段，要求將類別編號為“3”的圖書的圖書編號、書名和類別名稱插入到tb_bknew表中；（23）將類別編號為“3”的所有圖書的庫存數(shù)增加5；（24

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

12、eate database d_libraryon(name=db_library_data, filename='d:2015405A607武俊其db_library_data.mdf',size=10,maxsize=300,filegrowth=1)log on(name=db_library_log,filename='d:2015405A607武俊其db_library_data.ldf',size=5,maxsize=200,filegrowth=2) /創(chuàng)建數(shù)據(jù)庫create schema"L_C"AUTHORIZATION W

13、U /創(chuàng)建模式CREATE TABLE tb_course( 課程編號 int, 課程名 char(20), 先修課 char(20), 學分 int);CREATE TABLE tb_booktype( 類別編號 int, 類別名稱 char(20);CREATE TABLE tb_book( 圖書編號 int, 類別編號 int, 書名 char(20), 作者 char(20), 出版社 char(20), 定價 float, 庫存數(shù) int );CREATE TABLE tb_reader( 讀者編號 char(20), 姓名 char(20), 性別 char(2), 學號 int,

14、 班級 char(10), 系部 char(20) );CREATE TABLE tb_borrow( 圖書編號 int, 讀者編號 char(20), 借閱日期 char(20), 歸還日期 char(20),);goALTER TABLE tb_reader DROP COLUMN 系部 ; /刪除系部ALTER TABLE tb_reader ADD 所在系 CHAR ; /添加所在系 LTER TABLE tb_book ALTER COLUMN 定價 REAL; /定價的數(shù)據(jù)類型改為REALDROP TABLE tb_course CASCADE; /刪除課程信息表CREATE UN

15、IQUE INDEX IX_S_QUANTITY ON tb_book(庫存數(shù)); CREATE UNIQUE INDEX IX_S_NAME ON tb_reader(姓名); /創(chuàng)建索引DROP INDEX IX_S_QUANTITY ON tb_book;DROP INDEX IX_S_NAME ON tb_reader; /刪除索引1.查詢每本圖書的所有信息 select * from tb_book; 2.查詢每個讀者的讀者編號,姓名和班級 select 讀者編號,姓名,班級 from tb_reader ;3.查詢每條借閱記錄的借閱天數(shù)(函數(shù) DATEDIFF 獲取兩個日期的差)

16、select datediff(DAY,借閱日期,歸還日期)借閱天數(shù) from tb_borrow4.查詢被借閱過的圖書的圖書編號 select distinct 圖書編號 from tb_borrow; 5.查詢圖書編號為"10006"的書名和作者 select 書名,作者 from tb_book where 圖書編號='10006'6.查詢庫存數(shù)在到本之間的圖書的圖書編號和書名 select 圖書編號,書名 from tb_book where 庫存數(shù) between 5 and 10; 7.查詢計算機系或電子系姓張的讀者信息 select * fro

17、m tb_reader where 姓名 like '張%'and(所在系='計算機系'or 所在系='電子系'); 8.查詢書名包括"英語"的圖書信息 select * from tb_book where 書名 like '%英語' 9.統(tǒng)計男讀者,女讀者的人數(shù) select 性別, COUNT(*)人數(shù) from tb_reader group by 性別; 10.統(tǒng)計各類圖書的類別編號,平均定價以及庫存總數(shù) select 類別編號,AVG(定價)平均定價,sum(庫存數(shù))庫存總數(shù) from tb_boo

18、k group by 類別編號; 11.統(tǒng)計每本書籍借閱的人數(shù)要求輸出圖書編號和所借人數(shù)查詢結果 按人數(shù)降序排列 select 圖書編號,COUNT(*)所借人數(shù) from tb_borrow group by 圖書編號 order by COUNT(*) desc; 12.查詢有庫存的各類別圖書的類別編號,類別名稱和借閱數(shù)量 select tb_book.類別編號,類別名稱,COUNT(*)借閱數(shù)量 from tb_book,tb_booktype,tb_borrow where tb_book.類別編號 =tb_booktype.類別編號 and tb_book.圖書編號=tb_borro

19、w.圖書編號 group by tb_book.類別編號 ,tb_booktype.類別名稱; 13.查詢借閱了大學英語一書的讀者,輸出讀者姓名,性別,系部 select 姓名 ,性別 ,所在系 from tb_reader where 讀者編號 in ( select 讀者編號 from tb_borrow where 圖書編號 in ( select 圖書編號 from tb_book where 書名 ='大學英語') ) 14.查詢每個讀者的讀者編號,姓名,所借圖書編號及所借閱日期 select tb_reader.讀者編號 ,姓名 ,借閱日期 from tb_read

20、er left outer join tb_borrow on tb_reader.讀者編號 =tb_borrow.讀者編號; 15.查詢現(xiàn)有圖書中價格最高的圖書,輸出書名,作者,定價 select 書名,作者,定價 from tb_book where 定價= (select MAX(定價 ) from tb_book ); 16.查詢借閱了大學英語但沒有借閱C+程序設計的讀者輸出讀者姓名,性別,系部 select 姓名 ,性別, 所在系 from tb_reader where 姓名 in (select 姓名 from tb_borrow where 圖書編號 in (select 圖書

21、編號 from tb_book where 書名 ='大學英語') and 姓名 not in ( select 姓名 from tb_borrow where 圖書編號 in (select 圖書編號 from tb_book where 書名='C+程序設計') );17.統(tǒng)計借閱了本以上圖書的讀者信息 select * from tb_reader where 讀者編號 in ( select 讀者編號 from tb_borrow group by 讀者編號 having COUNT(*)>2);18.查詢計算機系中比其他系所有讀者借書數(shù)量都多的讀

22、者的信息 select * from tb_reader tb where 所在系='計算機系' and 讀者編號 in ( select tb_reader. 讀者編號 from tb_reader,tb_borrow where tb_reader.讀者編號=tb_borrow.讀者編號 and tb_reader.讀者編號=tb.讀者編號 group by tb_reader.讀者編號 having count(圖書編號)>any ( select count(圖書編號) from tb_reader,tb_borrow where tb_reader.讀者編號 =

23、tb_borrow.讀者編號 and 所在系 <>'計算機系' group by tb_reader.讀者編號 ) );19.查詢借閱了大學英語一書或者借閱了C+程序設計一書的讀者信息用集合查詢完成 select * from tb_reader where 讀者編號 in (select tb_borrow.圖書編號 from tb_borrow,tb_book where tb_borrow.圖書編號=tb_book.圖書編號 and 書名 ='大學英語') union select * from tb_reader where 讀者編號 in

24、( select 讀者編號 from tb_borrow,tb_book where tb_borrow.圖書編號=tb_book.圖書編號 and 書名='C+程序設計')20.查詢既借閱了大學英語一書又借閱了C+程序設計一書的讀者信息用集合查詢完成 select * from tb_reader where 讀者編號 in (select 讀者編號 from tb_borrow,tb_book where tb_borrow.圖書編號 =tb_book.圖書編號 and 書名='大學英語') intersect select * from tb_reader

25、 where 讀者編號 in (select 讀者編號 from tb_borrow,tb_book where tb_borrow.圖書編號=tb_book.圖書編號 and 書名='C+程序設計')21.在讀者信息表中插入一條新的記錄 insert into tb_reader(讀者編號,姓名,所在系) values ('R10011','張三','電子系'); 22.定義一個表 tb_booknew,要求將類別編號為"3"的圖書的圖書編號,書名和類別名稱插入到 tb_bknew 表中 CREATE TABLE tb_booknew ( 圖書編號 int, 書名 char(10), 類別名稱 char(10), ); insert into tb_booknew select 圖書編號,書名,類別名稱 from tb_book,tb_booktype where tb_booktype.類別編號=tb_book.類別編號 and tb_book.類別編號='3' 23. 將類別編號為的所有圖書的庫存數(shù)增加 update tb_book set 庫存數(shù)=庫存數(shù)+5 where 類別編號='3' 24.將"C+程序設計"這本書的歸還日