数据库原理及应用实验二.docx

1、数据库原理及应用实验二 实验2.1 数据查询一、实验目的1掌握利用T-SQL语言对数据库数据进行查询的方法，包括简单查询、多表连接查询以及嵌套查询。2理解在数据库表中对暑假的UNLL值的处理方式。3掌握T-SQL集合运算。二、实验内容对提供的数据库LibraryLib，按以下要求进行查询操作：1查询图书价格在1530元之间的所有图书的书名、书号、出版日期、价格和库存册书，且结果按书号进行升序排序。2查询图书名称中包含“计算机”的所有图书信息。3查询借书应归还日期在2009年8月1日前的用户账户。4找出还没有联系电话的出版社信息。5查询所有图书的平均价格。6查询各个出版社的图书的平均价格。7查询

2、已经归还且超期图书数至少超过2本德借书证号及其超期图书总数。8查询库存册数低于10的图书书名、库存数和其出版该图书的出版社名称。9查询一年相同月份里出版的不同图书的书名、出版日期和出版社标识。10查询用户信息和该用户的借出图书标识、应归还日期。如果该用户没有借阅图书，只显示该用户信息。11查询借阅了“清华大学出版社”出版的图书且尚未归还的用户总人数。12查询借阅了书名包含“Delphi”和“编程”的图书且还未归还这些图书的用户姓名、图书名、应归还日期。13查询借阅图书应归还日期为“2009年8月1日”前且库存册数小于10的图书的用户姓名、所在院系、联系电话、借阅图书的出版社名称、书名。14找出

3、和书号为“730200899X”的图书有相同购书数量的图书名称、书号、出版社标识及库存数。15查询图书价格高于出版该图书的出版社的所有图书平均价格的图书名称、出版社标识和图书价格。16查询在书库中的从来没有被读者借阅过的图书信息。17查询有图书价格大于或等于标识为“2”的出版社的所有图书价格的出版社标识。18查询每一本图书的书名、图书书号、出版社标识、图书价格及该图书书号的前一个书号。19利用集合运算，查询图书号为“5084-3567-7”或“7050191836”的图书名称、书号和库存册数。20利用集合运算，查询同时借阅了图书标识为“1”和“2”图书的读者的借书证号。21实现集合减运算查询借

4、阅了图书标识为“1”的图书而没有解压图书标识为“5”的图书的读者借书证号。三、实验步骤 打开SQL Sever 2000的SSMS把老师给定的数据库附加进去，然后打开T-SQL开始做实验。1查询图书价格在1530元之间的所有图书的书名、书号、出版日期、价格和库存册书，且结果按书号进行升序排序。根据要求，查询指定图书价格条件的图书信息，只需对“Book”表做单表查询。SQL语句如下：select BookName,BookISBN,BookPublishDate,BookPrice,BookCurNumfrom Bookwhere BookPrice between 15 and 30order

5、 by BookISBN asc执行结果如下：2查询图书名称中包含“计算机”的所有图书信息。 SQL语句如下：select *from Bookwhere BookName like %计算机%;执行结果如下图：3查询借书应归还日期在2009年8月1日前的用户账户。 SQL语句如下：select UserIDFrom Borrowwhere BorrowEndDate =2执行结果：8查询库存册数低于10的图书书名、库存数和其出版该图书的出版社名称。用连接查询来实现，SQL语句如下：SELECT BookName,BookCurNum,PublishNameFROM Book,Publishw

6、here Book.BookPublishID=Publish.PublishID and BookCurNum10;执行结果：9查询一年相同月份里出版的不同图书的书名、出版日期和出版社标识。 用自身连接查询，SQL语句如下：select B1.BookName,B1.BookCurNum,B1.BookPublishIDfrom Book B1,Book B2where year(B1.BookPublishDate)=year(B2.BookPublishDate) and month(B1.BookPublishDate)=month(B2.BookPublishDate) and B1

7、.BookName!=B2.BookName执行结果为：10查询用户信息和该用户的借出图书标识、应归还日期。如果该用户没有借阅图书，只显示该用户信息。 用外连接查询：select Users.UserID,Borrow.BookID,Borrow.BorrowEndDatefrom Users left outer join Borrow on (Users.UserID=Borrow.UserID)执行结果为：11查询借阅了“清华大学出版社”出版的图书且尚未归还的用户总人数。 用连接查询：select count(distinct UserID) as 未归还总人数from Borrow,B

8、ook,Publishwhere PublishName=清华大学出版社 and Borrow.BookID=Book.BookID and Book.BookPublishID=Publish.PublishID 用嵌套查询：select count(distinct UserID)as 未归还总人数from Borrowwhere BookID in(select BookID from Book,Publish where PublishName=清华大学出版社 and Book.BookPublishID=Publish.PublishID)对嵌套查询进一步分解：select coun

9、t(distinct UserID)as 未归还总人数from Borrowwhere BookID in(select BookID from Book where BookPublishID in (select PublishID from Publish where PublishName=清华大学出版社)执行结果：12查询借阅了书名包含“Delphi”和“编程”的图书且还未归还这些图书的用户姓名、图书名、应归还日期。SQL语句如下：用连接查询：select UserName,BookName,BorrowEndDatefrom Users,Borrow,Bookwhere Users

10、.UserID=Borrow.UserID and Borrow.BookID=Book.BookID and (Book.BookName like %Delphi%编程% or Book.BookName like %编程%Delphi% )用嵌套查询：select UserName,BookName,BorrowEndDatefrom Borrow,Users,Bookwhere Users.UserID=Borrow.UserID and Borrow.BookID=Book.BookID and Borrow.BookID in( select BookID from Book wh

11、ere BookName like %Delphi%编程% or BookName like %编程%Delphi%) 执行结果：13查询借阅图书应归还日期为“2009年8月1日”前且库存册数小于10的图书的用户姓名、所在院系、联系电话、借阅图书的出版社名称、书名。 用连接查询：select UserName,UserDepart,UserTelephone,PublishName,BookNamefrom Users,Publish,Book,Borrowwhere Users.UserID = Borrow.UserID and Borrow.BookID = Book.BookID an

12、d Publish.PublishID= Book.BookPublishID and Borrow.BorrowEndDate 2009-08-01 and Book.BookCurNum 10用嵌套查询：select UserName,UserDepart,UserTelephone,PublishName,BookNamefrom Users,Publish,Book,Borrowwhere Users.UserID = Borrow.UserID and Borrow.BookID = Book.BookID and Publish.PublishID= Book.BookPublis

13、hID and Users.UserID in(select UserIDfrom Borrowwhere BookID in(select BookIDfrom Bookwhere BookCurNum 10 )and UserID in(select UserIDfrom Borrowwhere BorrowEndDate (select avg(BookPrice) from Book B2 where B1.BookPublishID = B2.BookPublishID)执行结果为;16查询在书库中的从来没有被读者借阅过的图书信息。 利用not exists语句查询：select *

14、from Bookwhere not exists (select * from BorrowHistory where BookID=Book.BookID )执行结果：17查询有图书价格大于或等于标识为“2”的出版社的所有图书价格的出版社标识。 用谓词all：select distinct BookPublishIDfrom Bookwhere BookPrice =all( select BookPrice from Book where BookPublishID=2)执行结果为：18查询每一本图书的书名、图书书号、出版社标识、图书价格及该图书书号的前一个书号。 SQL查询如下：sel

15、ect BookName,BookISBN,BookPublishID,BookPrice, (select max(B2.BookISBN) from Book B2 where B2.BookISBN30执行结果：(3)查询已经超期一个月意思的还未归还图书的用户名称、联系方式以及超期图书名。select UserName,UserTelephone,BookNamefrom Users,Borrow,Bookwhere Users.UserID=Borrow.UserID and Book.BookID=Borrow.BookID and Borrow.UserID in (select

16、UserID from Borrowwhere getdate()-BorrowBeginDate30)执行结果：(4)查询在同一天借阅了不同图书的用户借书证号select B1.UserIDfrom Borrow B1,Borrow B2where B1.BorrowBeginDate=B2.BorrowBeginDate and B1.BookID !=B2.BookID查询结果为空：(5)查询借书从来没有被超期罚款的用户信息。select *from Userswhere UserID != all(select UserIDfrom ExtraDateFee)执行结果：(6)检索有图书

17、所购册书大于或等于图书价格超过50的图书任一所购册书的图书的价格。select BookPrice,BookNamefrom Book where BookNum= any(select BookNumfrom Book where BookPrice50)执行结果：索引练习(1)为“借出图书信息表Borrow”创建按“应归还日期”降序排列的索引。create index I_BorrowEndDate on Borrow(BorrowEndDate desc)(2)为“图书信息表Book”创建按“书名”升序排列的唯一索引。create unique index I_BookName on Book(BookName asc)