实验二 数据查询与索引

实验二数据查询与索引实验

2.1数据查询

一、实验目的掌握利用语言对数据库数据进行查询的方法，包括简单查询、多表连接

1.T-SQL查询以及嵌套查询.理解在数据库表中对数据的值的处理方式2NULL.掌握集合运算3T-SQL

二、实验内容对提供的数据库按以下要求进行查询操作LibraryLib,.查询图书价格在元之间的所有图书的书名、书号、出版日期、价格和库115〜30存册书，且结果按书号进行升序罗列.查询图书名称中包含“计算机”的所有图书信息2查询借书应归还日期在年月日前的用户帐户

3.

202281.找出还没有联系电话的出版社信息

4.查询所有图书的平均价格

5.查询各个出版社的图书的平均价格

6.查询已经归还且超期图书数至少超过本的借书证号及其超期图书总数查询

728.库存册数低于的图书书名、库存数和其出版该图书的出版社名称10查询一年相同月份里出版的不同图书的书名、出版日期和出版社标识

9.查询用户信息和该用户的借出图书标识、应归还日期如果该用户没有借阅图

10.书，只显示该用户信息.查询借阅了“清华大学出版社”出版的图书且尚未归还的用户总人数

11.查询借阅了书名包含和“编程”的图书且还未归还这些图书的用12“Delphi”户姓名、图书名、应归还日期.查询借阅图书应归还日期为年月日”前且库存册数小于的13“20228110图书的用户姓名、所在的院系、联系电话、借阅图书的出版社名称、书名.找出和书号为的图书有相同购书数量的图书名称、书号、出14“730202299X”SQLQuery

20....rator522SELECT UserID FROM BorrowWHERE BookID=l INTERSECTSELECT UserIDFROM BorrowWHERE BookID=21二]结尾重消用■BusedD—j1rZ ha ng QF].实现集合减运算，查询借阅了图书标识为的图书而没有借阅图书标识为21“1”“5”的图书的读者借书证号实现操作语句如下SQL|SQUhMiy

21....rator_Q52]2SELECT UserIDFROM Borrow WHERE BookID=l EXCEPTSELECT UserIDFROMBorrowWHERE BookID=5□结果消息|UserID-i1rZhangQF实验

2.4索弓

一、实验目的.熟悉利用中的设计工具来创建索引的方法1SSMS熟悉使用语句来创建、取销索引的方法

2.T-SQL

二、实验内容对数据库进行如下索引操作LibraryLib采用两种方法，即语句和中的设计工具为“还书信息表

1.T-SQL SSMS创建按“实际归还日期”降序罗列的索引BorrowHistor”y I_BHReturnDateo用语句为“借出图书信息表创建按“借书证号班序罗列的

2.T-SQL Borrow”索引I_UserIDo创建表“图书信息表的按“书号听序罗列的惟一性索引

3.Book”LBooklSBN取销“借出图书信息表的“借书证号”升序索引

4.Borrow”

三、实验步骤创建表形的按“实际归还日期”降序排列的索引

1.orrowHistoiy”采用如下两种方法LBHRetumDate创建索引的语句如下1LBHRetumDate T-SQL利用的设计工具，按如下步骤操作在2SQL ServeManagement Studio1SSMS中，选定数据库展开“表”节点，右击表然后从弹“LibraryLib”，“BorrowHistory”,出的快捷菜单中选择“设计”命令点击工具栏选项“管理索引和键打开“索弓键”对话框，如下图所示:21/单击“常规”项中的“列”文本框，然后单击“列”文本框右边按钮，打开如下4图所示的“索引列”对话框）在列名下，选择列选择排序顺序为“降序”单击“确定”按5BorrowEndDate,钮关闭该对话框）其他选项的配置设为默认，然后关闭对话框，保存文件6为“借出图书信息表表创建按“借书证号升序罗列的索引

2.Borrow”实现操作如下图所示LUserlD,Q0V三三-禺LibraryLib▼I_UserID.sq...rator52□CREATELINDEX I-UserlD ONBorrow UserID为“图书信息表创建按“书号听序罗列的惟一性索引

3.Book”LBooklSBN,实现操作如下所示行量）/三室与-也LibraryLib▼CREATE I_Bo...trator53I BooklSBN....rator

52.取销“借出图书信息表的“借书证号听序索引，实现操作如下所4Borrow”示亍QO V三与,出LibraryLib||I BooklSBI....rator62fl一DROP INDEXBorrow.I UserID|卿———――消息命令已成功完成思量与练习数据查询练习

1.查询各个超期罚款用户及其罚款总数，结果按罚款总数降序罗列操作语1句如下所示:三厚与亏力QC Vj LibraryLibsikaol.sql...rator52□SELECT UserID,COUNTFineMoney ASTotal-Money FROMExtraDateFeeGROUP BYUserID ORDER BY COUNTFineMoneyDESC通结果」消息jUserID Total_MoneyrZhariQF1112ZouY1II■查询已经超期一个月以上的还未归还图书的用户借书证编号操作语句如2下所示戈V厚厚ta--也LibraryLib▼执行燮:I/sikao

4.sql...rator532SELECT Bl.UserIDFROM Borrow Bl,BorrowB2WHERE Bl.BorrouBeginDate=B

2.BorrowBeginDateL ANDBl.BookID!=B

2.BookID国结果」消息PUserID I）查询借书从来没有被超期罚款的用户信息操作语句如下所示:（5sikao

5.sql...rator52□SELECT拿FROM UsersWHEREUserID INSELECTUserIDFROM BorrowHistoryWHEREBorrowReturnDate=BorrowEndDate□结果消息3UserID U...User...User...Us...Use...UserTete...UserEMail Used...Use；大…1ZhangQF:13张庆…

00310658...zhangqf@

16...NULL2邹月营…2ZouY

1400235674...zouyue@

16...NULL0）检索有图书所购册书大于或者等于图书价格超过的图书任一所购册书的图（650书的价格操作语句如下所示sikao

6.sql...rator53□SELECT BookName,BookPrice FROM Book WHEREBookNum=ANYSELECT BookNumFROM Book WHERE BookPrice50目结果鱼消息BookName BookPrice!版主答疑高级编程技巧1Qelph

47.00大学英语词汇记忆点津与考点要览

216.00英语网上文摘

35.00理序设计语言特别版4O

55.67数据库系统概论

533.80操作系统及应用

636.507多媒体应用技术基础

45.90操作系统课程设计

821.009数据库原理及应用

32.00理序设计案例集锦10SQL Serve/

48.

00.索引练习4为“借出图书信息表创建按“应归还日期”降序罗列的索引利1Borrow”用语句，具体实现操作如下图所示T-SQL*

①■/隼m B7M LibraryLib▼，执行里》I_BorrovEnd...trator52h CREATEPINDEX I_BorrowEndDate ONBorrow BorrouEndDate DESC您，川i一］■■■■、•怎消息命令已成功完成.为“图书信息表创建按“书名”升序罗列的惟一性索引，如果有书名相同，2Book”看看对建立索引有没有影响利用语句，具体实现操作如下图所示T-SQLQC/后厚*B-业LibraryLib▼I^Booklaae....rator53I hCREATE UNIQUE|INDEX I BookName ONBook iBookName一U3消息命令已成功完成.说明当创建一个惟一索引时，索引的键值不能重复，在列包含重复值时，不能建惟一索引，如要使用此选项，应确定索引所包含的列均不允许值，否则NULL在使用时会时常出错

四、实验心得通过本次实验，我熟练掌握了利用语言对数据库进行查询、创建和取消索引的方T-SQL法，并能够灵便地进行单表查询、多表连接查询和嵌套查询实验过程中，学习到了几个新的语言提供的各类函数，如日期函数和,SQL yearmonth分别用来求得指定日期的年份和月份，并懂得了利用简单的函数表达式作为查询条件进行查询的规则，如思量与练习中的第、题123对于索引的创建及取销，内容比较简单，基本上没有什么问题，关键要掌握创建惟一索引时要注意的问题，即索引的键值不重复，列不包含重复值固然，在做实验的过程中，也遇到了些问题，如运行语句时，会提示“对象无效”Book等信息，但查询语句明显无错误，重新运行几次就能显示正确查询结果总之，经过此次实验，非但体味到了学以致用的成就感，而且提高了我的实际操作能力版社标识和图书价格、查询图书价格高于出版该图书的出版社的所有图书平均价格的图书名称、出15版社标识和图书价格.查询在书库中的从来没有被读者借阅过的图书信息

16.查询有图书价格大于或者等于标识为的出版社的所有图书价格的出版社标17“2”识.查询每一本图书的书名、图书书号、出版社标识、图书价格及该图书书号的前一个书号

18.利用集合运算，查询图书号为或者的图书名称、书号和库存册19“5084-3587-7”“7050191836”数利用集合运算，查询同时借阅了图书标识为和图书的读者的借书

20.“1”“2”证号.实现集合减运算，查询借阅了图书标识为的图书而没有借阅图书标识为21“1”的图书的读者借书证号“5”

三、实验步骤.查询图书价格在元之间的所有图书的书名、书号、出版日期、价格和115〜30库存册书，且结果按书号进行升序罗列实现操作语句如下SQL丁q■受场苫阳LibraryLib，工执行Q0►V器目国］SQLQueryl s...trtor52日SELECT BookName,BooklSBN,BookPublishDate,BookPrice,BookCurNumI FROM BookWHERE BookPriceBETWEEN15AND30「ORDERBY BooklSBN国结果|将消息IBookName BooklSBN BookPublishDateBookPrice BookCurNum操作系统课程设计17-111-16821-62005-08-0100:00:00,

00021.0019|…天学黄运舌忙活区百海可每金妻死…2781067224X2004-08-0100:00:00,

00016.008•查询图书名称中包含“计算机”的所有图书信息实现操作语句如下2SQLLibrtfyLib-f执行QDVSQLQ««ry

2.B«..trater52jSELECT.FROMBook-WHEREBookNaoneLIKE，计算机二]结果为消息BooklD BookNameBookJSBN三三与G3LibraryLib.疏通叫、需㈤厩--»fh・■・——■■■■■if,T IS1「5计算机网络7115101620谢希仁r20034501OOOO.OQOOO计置机网络X-・・・・••»查询借书应归还日期在年月日前的用户帐户操作语句如下:

3.202281SQL执行量y三三与7@LibraryLibh SELECT DISTINCT UserIDIFROMBorrow1-WHERE BorrouEndDate,2009-08-01二]结果消息3I UserID-I:五；1ang6F.找出还没有联系电话的出版社信息实现操作语句如下:4SQL*-执行邕，厚厚BLibraryLib▼1执行Q0»SQLQuery4^s...trator52t]SELECT*FROM Publish WHERE PublishTelephoneIS NULL□结果消息JjPublishlD PublishNamePublishAddre$$Publish!elephone PublishEmailPublishPostCode.查询所有图书的平均价格实现操作语句如下5SQL执行Q0V三厚*0--疝LibraryLib.z^QlJQvery

33.

41.

96663421.

00.查询已经归还且超期图书数至少超过本的借书证号及其超期图书总数实现操作语72SQL句如下执行a Vmm*B-椽LibraryLib，:SQLQueryT.s...trator52^SELECTUserID,COUNT*Total_Extra；；1FROM ExtraDateFeeSGROUPBYUserIDHAVING COUNT*=24K u-^明M更结果且消息11|UserID£Total_Extra j.查询库存册数低于的图书书名、库存数和其出版该图书的出版社名称实810现操作语句如下SQL执行QD£二一二匕空总瑞@LibraryLib.口执行X►SQLQueryS.s...trator523SELECT BookName,BookCurNum,PublishNameFROM Book,PublishLUHERE BookCurNuin10AND Book.BookPt iblishID=Publish.PublishID结jr］急消息E BookNameBookCurNumPublishName版主答疑高级编程技巧f11・Delph|7清华大学出版社大学英语词汇记忆点津与考点要览28清华大学出版社英语网上文摘清华大学出版社39曜序设计语言（特别版）清华大学出版社4O9清华大学出版社计算机网络57清华大学出版社操作系统及应用68查询一年相同月份里出版的不同图书的书名操、出版日期和出版社标识实现

9.作语句如下SQL执行QO/1三三*Q-也；LibraryLib▼，执行矍》P/^^UweryS.s...trator523SELECT Bl.BookName,Bl.BookPublishDate,Bl.BookPublishlDFROM Book Bl.Book B2WHERE YEARBl.BookPublishDate=YEARB

2.BookPublishDate ANDMONTHBl.BookPub1ishDate=YEARB

2.BookPublishDateL ANDBl.BookName I=B

2.BookName口结果鱼消息B ookNameBookPubli$hD ateB ookPublishlD查询用户信息和该用户的借出图书标识、应归还日期如果该用户没有借阅

10.图书，只显示该用户信息实现操作语句如下SQL执行QP■，受三*0--也LibraryLib▼，执行Q0VSQLQuerylO....rator53j|fc SELECTUsers.UserID Borrow.BookID BorroiJ.BorrowEndDateZ z口j FROMUsers LEFTOUTER JOINBorrow ONUsers.UserID=Borrow.UserID典结果匾消用UserID BookIDBorrowEndDate111NULL NULL2LuoHH NULLNULL3ShiYD NULLNULL4YanDS NULLNULL5ZhangQF12009-07-1515:42:41,0006ZhangQF22009-07-1515:42:48,0007ZouY NULLNULL.查询借阅了“清华大学出版社出版的图书且尚未归还的用户总人数实现11操作语句如下:SQL执行QD■/手弄^0-也LibraryLibL SQLQueryl

1....rator52|_____________________________________________if hSELECTCOUNT DISTINCTUserlDP ASTotal_UserFROM BorrowBook,Publish9叶青华大学出版社，WHERE PubJLishNawAND Borrow.BookID-Book.BookIDL AND Book.BookPublishlD=Publish.PublishlDTotaLUserl-

1.查询借阅了书名包含和“编程”的图书且还未归还这些图书的用12“Delphi”户姓名、图书名、应归还日期实现操作语句如下:SQL执行益-出QD V-W m LibraryLib▼SQLQuery12777rat or523SELECT UserNarne,BookNawe,BorrowEndDateFROM Users,Book,BorrowWHERE Users.UserID=Borrow.UserID ANDBook.BookID=Borrou.BookID AND.彳与编程Book.BookName LIKEDelphi*编程与与L ORBook.BookName LIKEDeJLphi口结果消点J IUserNameBookName BonowEndDate.查询借阅图书应归还日期为年月日”前且库存册数小于的图书的13“20228110用户姓名、所在的院系、联系电话、借阅图书的出版社名称、书名实现操作语句如下:SQL执行二=由营与-也Q0,LibraryLibSQLQueryl

3....rator522SELECT UserNaine,UserDepart,UserTelephone,BookName,PublishNawe FROMUsers,Borrow,Book,PublishWHEREUsers.UserID=Borrou.UserIDAND Borrow.BookID=Book.BookIDAND Book.BookPublishlD=Publish.PublishlD1AND Borrow.BorroxirEndDate2009-08-01ANDBook.BookCurNum106J1U____________目结果鱼消息U$erNameU$eDepart LserT elephoneB ookName PublishName族误夸大名二中版主答疑高级编程技巧潘华大学出版社1T I03106585423Delph谯庆君―大名二中大学英语词汇记忆点津与考点要览清华大学出版社

203106585423.找出和书号为的图书有相同购书数量的图书名称、书号、出14“730202299X”版社标识和图书价格实现操作语句如下:SQL执行量）SQLQueryl

4....rator523SELECT BookName,BooklSBN,BookPublishlD,BookCurNum FROM Book WHEREBookNuroINSELECT BookNumFROM Book1版主答疑Delph高级编程技巧730200899X172大学英语词汇记忆点津与考点要览781067224X183英语网上文摘7800048381194锯序设计语言（特别版）704010095919C+57040195835110数据库系统概论6705019183618操作系统及应用11L WHEREBookISBN=730200899X、查询图书价格高于出版该图书的出版社的所有图书平均价格的图书名称、出15版社标识和图书价格实现操作语句如下:SQL执行Q0V:三;毛与-出LibraryLib ySQLQueryl

5....rator53HSELECT BookName,BookPublishlD,BookPrice IFROMBookBlWHERE BookPriceSELECT AVGBookPriceFROMBookB2二］结果展消息BookName BookPublishlD BookPrice版主答疑盾级编程技巧-Delph j

147.00推序设计语言（特别版）C♦

155.67计算机网络

139.00数据库系统概论

133.80操作系统及应用

136.50多媒体应用技术基础

245.90理序设计案例集锦SQL Sew/

200248.00WHERE Bl.BookPublishlD=B

2.BookPLiblishlD.查询在书库中的从来没有被读者借阅过的图书信息实现操作语句如下:16SQL1234567SQLQueryl

6....rator52j^|__________________________EJ SELECT・FROMBookWHERE NOTEXISTS SELECT*FROM BorrouHistoryWHEREBookID=Book.BookID1二结果消息BookID BookNa...Bookl...Book...BookP...Book...Book..Boo...Book,...Boo...Bo...B...B...Borro...数据库

17040.王珊

2000...数据…

33.80420101013NULL27操作系…7050…孙仲…2005-...操作…

36.5037EL这..10813NULL多媒体…刘甘…多媒…普

395084...2002%..

45.9046・・内..121723NULL滦作系…4107-

111...罗宇…2005-...Linux...

21.00192201943NULL数据库…高..513978-

7...石玉…2009-...数据…

32.00311NU...204523NULL周睡…614SQLS...

9787...2010-...数据…

48.00372计…NU...401023NULL.查询有图书价格大于或者等于标识为的出版社的所有图书价格的出版社标识17“2”实现操作语句如下SQL山V苣运mL^raryLibSQLQuerylT....rator522SELECTDISTINCTBookPublishlD FROMBookWHEREBookPrice=ALLSELECT BookPriceFROM BookWHEREBookPublishID=2二］结果消息UBookPublishlD-

1122.查询每一本图书的书名、图书书号、出版社标识、图书价格及该图书书号的前一个18书号实现操作语句如下SQL行

⑥■.二三三%-出LibraryLib▼执行“盼...一-患1---一一一一SQLQueryl

8....rator52□SELECT BookName,BooklSBN,BookPublishlD,BookPrice,；SELECT MAXB

2.BooklSBN FROMBookB2•WHEREB

2.BooklSBN B

1.BooklSBN ASRormer_ISBNFROM BookBlL ORDERBYBooklSBN便结果消息JBookName BooklSBNBookPublishlDBookPriceRormerJSBN|多媒体应用技术基础]5084-3587-

7245.90NULL1•/2C♦布序设计语言（特别版）

7040100959155.675084-358773数据库系统概论

7040195835133.8070401009594操作系统及应用

7050191836136.5070401958355镰作系统课程设计7-111-16821-

6421.0070501918366计算机网络

7115101620139.007-111-16821-67版主答疑Qelph高级编程技巧73020089SX

147.0071151016208英语网上文摘

780004838115.00730200899X大学英语词汇记忆点津与考点要览9781067224X

116.00780004838110数据库原理及应用978-7-5084-6589-

0232.00781067224X.利用就遛翳娥睛蜜禺幽御掇者嘲得书用箫则眼19“5027050⑼83665书号和库存册数实现操作语句如下:SQLSQLQuer]fl

9....rator_52|3SELECT BookNaxneBookISBN BookCurNuwFROMBookWHEREz z1BookISBN=5084-3587-7UNIONSELECT BookName,BooklSBN,BookCurNum FROMBook果」m lBookNameBooklSBN BookCurNum藻柞素或友应由11I70501918368宝谟而豆甬强探融“2S5084-3587717利用集合运算，查询同时借阅了图书标识为和图书的读者的借书证号实现

20.“1”“2”操作语句如下SQL。