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

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

《数据库原理及应用实验二.docx》由会员分享，可在线阅读，更多相关《数据库原理及应用实验二.docx（24页珍藏版）》请在冰豆网上搜索。

数据库原理及应用实验二

实验2.1数据查询

一、实验目的

1．掌握利用T-SQL语言对数据库数据进行查询的方法，包括简单查询、多表连接查询以及嵌套查询。

2．理解在数据库表中对暑假的UNLL值的处理方式。

3．掌握T-SQL集合运算。

二、实验内容

对提供的数据库LibraryLib，按以下要求进行查询操作：

1．查询图书价格在15~30元之间的所有图书的书名、书号、出版日期、价格和库存册书，且结果按书号进行升序排序。

2．查询图书名称中包含“计算机”的所有图书信息。

3．查询借书应归还日期在2009年8月1日前的用户账户。

4．找出还没有联系电话的出版社信息。

5．查询所有图书的平均价格。

6．查询各个出版社的图书的平均价格。

7．查询已经归还且超期图书数至少超过2本德借书证号及其超期图书总数。

8．查询库存册数低于10的图书书名、库存数和其出版该图书的出版社名称。

9．查询一年相同月份里出版的不同图书的书名、出版日期和出版社标识。

10．查询用户信息和该用户的借出图书标识、应归还日期。

如果该用户没有借阅图书，只显示该用户信息。

11．查询借阅了“清华大学出版社”出版的图书且尚未归还的用户总人数。

12．查询借阅了书名包含“Delphi”和“编程”的图书且还未归还这些图书的用户姓名、图书名、应归还日期。

13．查询借阅图书应归还日期为“2009年8月1日”前且库存册数小于10的图书的用户姓名、所在院系、联系电话、借阅图书的出版社名称、书名。

14．找出和书号为“730200899X”的图书有相同购书数量的图书名称、书号、出版社标识及库存数。

15．查询图书价格高于出版该图书的出版社的所有图书平均价格的图书名称、出版社标识和图书价格。

16．查询在书库中的从来没有被读者借阅过的图书信息。

17．查询有图书价格大于或等于标识为“2”的出版社的所有图书价格的出版社标识。

18．查询每一本图书的书名、图书书号、出版社标识、图书价格及该图书书号的前一个书号。

19．利用集合运算，查询图书号为“5084-3567-7”或“7050191836”的图书名称、书号和库存册数。

20．利用集合运算，查询同时借阅了图书标识为“1”和“2”图书的读者的借书证号。

21．实现集合减运算查询借阅了图书标识为“1”的图书而没有解压图书标识为“5”的图书的读者借书证号。

三、实验步骤

打开SQLSever2000的SSMS把老师给定的数据库附加进去，然后打开T-SQL开始做实验。

1．查询图书价格在15~30元之间的所有图书的书名、书号、出版日期、价格和库存册书，且结果按书号进行升序排序。

根据要求，查询指定图书价格条件的图书信息，只需对“Book”表做单表查询。

SQL语句如下：

selectBookName,BookISBN,BookPublishDate,BookPrice,BookCurNum

fromBook

whereBookPricebetween15and30

orderbyBookISBNasc

执行结果如下：

2．查询图书名称中包含“计算机”的所有图书信息。

SQL语句如下：

select*

fromBook

whereBookNamelike'%计算机%';

执行结果如下图：

3．查询借书应归还日期在2009年8月1日前的用户账户。

SQL语句如下：

selectUserID

FromBorrow

whereBorrowEndDate<'2009-8-1';

执行结果如下图：

4．找出还没有联系电话的出版社信息。

SQL语句如下：

select*

fromPublish

wherePublishTelephoneisnull;

执行结果：

5．查询所有图书的平均价格。

SQL语句如下：

selectavg（allBookPrice）as'所有图书的平均价值'

fromBook;

执行结果：

6．查询各个出版社的图书的平均价格。

SQL语句如下：

selectavg（allBookPrice）as'各出版社平均价值'

fromBook

groupbyBookPublishID;

执行结果为：

7．查询已经归还且超期图书数至少超过2本德借书证号及其超期图书总数。

SQL语句如下：

selectUserID,count（*）as'超期图书总数'

fromExtraDateFee

groupbyUserID

havingcount（*）>=2

执行结果：

8．查询库存册数低于10的图书书名、库存数和其出版该图书的出版社名称。

用连接查询来实现，SQL语句如下：

SELECTBookName,BookCurNum,PublishName

FROMBook,Publish

whereBook.BookPublishID=Publish.PublishIDandBookCurNum<10;

执行结果：

9．查询一年相同月份里出版的不同图书的书名、出版日期和出版社标识。

用自身连接查询，SQL语句如下：

selectB1.BookName,B1.BookCurNum,B1.BookPublishID

fromBookB1,BookB2

whereyear（B1.BookPublishDate）=year（B2.BookPublishDate）

andmonth（B1.BookPublishDate）=month（B2.BookPublishDate）

andB1.BookName!

=B2.BookName

执行结果为：

10．查询用户信息和该用户的借出图书标识、应归还日期。

如果该用户没有借阅图书，只显示该用户信息。

用外连接查询：

selectUsers.UserID,Borrow.BookID,Borrow.BorrowEndDate

fromUsersleftouterjoinBorrowon（Users.UserID=Borrow.UserID）

执行结果为：

11．查询借阅了“清华大学出版社”出版的图书且尚未归还的用户总人数。

用连接查询：

selectcount（distinctUserID）as'未归还总人数'

fromBorrow,Book,Publish

wherePublishName='清华大学出版社'

andBorrow.BookID=Book.BookID

andBook.BookPublishID=Publish.PublishID

用嵌套查询：

selectcount（distinctUserID）as'未归还总人数'

fromBorrow

whereBookIDin（

selectBookIDfromBook,Publish

wherePublishName='清华大学出版社'

andBook.BookPublishID=Publish.PublishID）

对嵌套查询进一步分解：

selectcount（distinctUserID）as'未归还总人数'

fromBorrow

whereBookIDin（

selectBookID

fromBook

whereBookPublishIDin（

selectPublishID

fromPublish

wherePublishName='清华大学出版社'））

执行结果：

12．查询借阅了书名包含“Delphi”和“编程”的图书且还未归还这些图书的用户姓名、图书名、应归还日期。

SQL语句如下：

用连接查询：

selectUserName,BookName,BorrowEndDate

fromUsers,Borrow,Book

whereUsers.UserID=Borrow.UserID

andBorrow.BookID=Book.BookID

and（Book.BookNamelike'%Delphi%编程%'

orBook.BookNamelike'%编程%Delphi%'

）

用嵌套查询：

selectUserName,BookName,BorrowEndDate

fromBorrow,Users,Book

whereUsers.UserID=Borrow.UserID

andBorrow.BookID=Book.BookID

andBorrow.BookIDin（

selectBookID

fromBook

where

BookNamelike'%Delphi%编程%'

orBookNamelike'%编程%Delphi%'

）

执行结果：

13．查询借阅图书应归还日期为“2009年8月1日”前且库存册数小于10的图书的用户姓名、所在院系、联系电话、借阅图书的出版社名称、书名。

用连接查询：

selectUserName,UserDepart,UserTelephone,PublishName,BookName

fromUsers,Publish,Book,Borrow

whereUsers.UserID=Borrow.UserID

andBorrow.BookID=Book.BookID

andPublish.PublishID=Book.BookPublishID

andBorrow.BorrowEndDate<'2009-08-01'

andBook.BookCurNum<10

用嵌套查询：

selectUserName,UserDepart,UserTelephone,PublishName,BookName

fromUsers,Publish,Book,Borrow

whereUsers.UserID=Borrow.UserID

andBorrow.BookID=Book.BookID

andPublish.PublishID=Book.BookPublishID

andUsers.UserIDin（

selectUserID

fromBorrow

whereBookIDin（

selectBookID

fromBook

whereBookCurNum<10）

andUserIDin（

selectUserID

fromBorrow

whereBorrowEndDate<'2009-08-01'））

执行结果为：

14．找出和书号为“730200899X”的图书有相同购书数量的图书名称、书号、出版社标识及库存数。

用嵌套查询：

selectBookName,BookISBN,BookPublishID,BookCurNum

fromBook

whereBook.BookNum=（

selectBookNum

fromBook

whereBookISBN='730200899x'

）

用自身连接查询：

selectB1.BookName,B1.BookISBN,B1.BookPublishID,B1.BookCurNum

fromBookB1,BookB2

whereB1.BookNum=B2.BookNum

andB2.BookISBN='730200899x'

执行结果：

15．查询图书价格高于出版该图书的出版社的所有图书平均价格的图书名称、出版社标识和图书价格。

SQL语言：

selectBookName,BookPublishID,BookPrice

fromBookB1

whereBookPrice>

（selectavg（BookPrice）

fromBookB2

whereB1.BookPublishID=B2.BookPublishID

）

执行结果为;

16．查询在书库中的从来没有被读者借阅过的图书信息。

利用notexists语句查询：

select*

fromBook

wherenotexists

（select*

fromBorrowHistory

whereBookID=Book.BookID

）

执行结果：

17．查询有图书价格大于或等于标识为“2”的出版社的所有图书价格的出版社标识。

用谓词all：

selectdistinctBookPublishID

fromBook

whereBookPrice>=all（

selectBookPrice

fromBook

whereBookPublishID=2）

执行结果为：

18．查询每一本图书的书名、图书书号、出版社标识、图书价格及该图书书号的前一个书号。

SQL查询如下：

selectBookName,BookISBN,BookPublishID,BookPrice,

（selectmax（B2.BookISBN）

fromBookB2

whereB2.BookISBN

fromBookB1

orderbyBookISBN

执行结果为：

19．利用集合运算，查询图书号为“5084-3567-7”或“7050191836”的图书名称、书号和库存册数。

采用集合运算符号UNION实现查询：

（selectBookName,BookISBN,BookCurNum

fromBook

whereBook.BookISBN='5084-3587-7'）

union

（selectBookName,BookISBN,BookCurNum

fromBook

whereBook.BookISBN='7050191836'

）

执行结果：

20．利用集合运算，查询同时借阅了图书标识为“1”和“2”图书的读者的借书证号。

由于在SQLServer2000不支持直接使用保留字intersect进行交运算，所以用and逻辑与运算符来实现：

selectdistinctB1.UserID

fromBorrowB1,BorrowB2

where（B1.UserIDin（

selectUserID

fromBorrow

whereBorrow.BookID=1）

andB2.UserIDin（

selectUserID

fromBorrow

whereBorrow.BookID=2））

andB1.UserID=B2.UserID

执行结果为：

21．实现集合减运算查询借阅了图书标识为“1”的图书而没有解压图书标识为“5”的图书的读者借书证号。

由于在SQLServer2000不支持直接使用保留字except运算符，所以用and逻辑与运算符来实现：

selectdistinctB1.UserID

fromBorrowB1,BorrowB2

where（B1.UserIDin（

selectUserID

fromBorrow

whereBorrow.BookID=1）

andB2.UserIDnotin（

selectUserID

fromBorrow

whereBorrow.BookID=5））

andB1.UserID=B2.UserID

执行结果为：

四、实验心得

本次试验的主要内容是对数据库中数据查询的练习，通过本次实验掌握利用T-SQL语言对数据库数据进行查询的方法，包括简单查询，多表连接查询以及嵌套查询，并理解了在数据库表中队数据的UNLL值的处理方式和T-SQL的集合运算。

实验的主要难点是多表查询和嵌套查询。

多表查询的时候一般可以使用两种查询方法，一种是连接，连接查询也分为表连接、自身连接和外连接，另一种是用嵌套查询，嵌套查询也分为IN谓词的子查询、带有比较运算符的子查询、带有ANY或ALL谓词的子查询、带有EXISTS谓词的子查询，还有结合使用聚集函数的查询。

其中嵌套查询都可以用连接运算来代替，但并非所有的嵌套查询均可以用连接运算来表示。

实验2.4索引

一、实验目的

1．熟悉利用SSMS中的设计工具来创建索引的方法。

2．熟悉使用T-SQL语句来创建、取消索引的方法。

二、实验内容

对数据库LibraryLib进行如下索引操作：

1．采用两种方法，即T-SQL语句和SSMS中的设计工具为“还书信息表BorrowHistory”创建按“实际归还日期”降序排列的索引I_BHReturnDate。

2．用T-SQL语句为“借出图书信息表Borrow”创建按“借书证号”升序排列的索引I_UserID。

3．创建表“图书信息表Book”的按“书号”升序排列的唯一索引I_BookISBN。

4．取消“借出图书信息表Borrow”的“借书证号”升序索引。

三、实验步骤

1．采用两种方法，即T-SQL语句和SSMS中的设计工具为“还书信息表BorrowHistory”创建按“实际归还日期”降序排列的索引I_BHReturnDate。

创建索引I_BHReturnDate的T-SQL语句如下：

createindexI_BHReturnDateonBorrowHistory（BorrowReturnDatedesc）

利用SSMS设计工具，按如下步骤。

在SSMS中选择指定的数据库LibraryLib，展开“表”节点。

右击要为其创建索引的表BorrowHistory，然后从弹出的快捷键菜单中选择“设计”命令，右击菜单栏中选择“索引”如下图。

在打开的属性窗口中创建索引

2．用T-SQL语句为“借出图书信息表Borrow”创建按“借书证号”升序排列的索引I_UserID。

createindexI_UserIDonBorrow（UserID）

3．创建表“图书信息表Book”的按“书号”升序排列的唯一索引I_BookISBN。

createuniqueindexI_BookISBNonBook（BookIDasc）

4．取消“借出图书信息表Borrow”的“借书证号”升序索引。

dropindexBorrow.I_UserID

四、实验心得

本次实验相对比较简单，通过本次实验可以熟悉利用SSMS中的设计工具来创建索引，并掌握使用T-SQL语句来创建、取消索引的方法。

但是对索引在检索数据库的应用的理解还不够，因为本次实验中数据库中的数据比较少，所以还没有很好的理解利用索引来加快查询速度的优点。

思考与练习

1．数据查询练习

（1）查询各个超期罚款用户及其罚款总数，结果按罚款总数降序排列。

selectUserID,count（*）as'罚款总数'

fromExtraDateFee

groupbyUserID

orderby'罚款总数'desc

执行结果如下：

（2）查询已经超期一个月以上的还未归还图书的用户借书编号。

selectdistinctUserID

fromBorrow

wheregetdate（）-BorrowBeginDate>30

执行结果：

（3）查询已经超期一个月意思的还未归还图书的用户名称、联系方式以及超期图书名。

selectUserName,UserTelephone,BookName

fromUsers,Borrow,Book

whereUsers.UserID=Borrow.UserID

andBook.BookID=Borrow.BookID

andBorrow.UserIDin（

selectUserID

fromBorrow

wheregetdate（）-BorrowBeginDate>30）

执行结果：

（4）查询在同一天借阅了不同图书的用户借书证号

selectB1.UserID

fromBorrowB1,BorrowB2

whereB1.BorrowBeginDate=B2.BorrowBeginDate

andB1.BookID!

=B2.BookID

查询结果为空

：

（5）查询借书从来没有被超期罚款的用户信息。

select*

fromUsers

whereUserID!

=all（

selectUserID

fromExtraDateFee

）

执行结果：

（6）检索有图书所购册书大于或等于图书价格超过50的图书任一所购册书的图书的价格。

selectBookPrice,BookName

fromBook

whereBookNum>=any（

selectBookNum

fromBook

whereBookPrice>50

）

执行结果：

索引练习

（1）为“借出图书信息表Borrow”创建按“应归还日期”降序排列的索引。

createindexI_BorrowEndDateonBorrow（BorrowEndDatedesc）

（2）为“图书信息表Book”创建按“书名”升序排列的唯一索引。

createuniqueindexI_BookNameonBook（BookNameasc）