实验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”的图书的读者借书证号。
三、实验步骤
打开SQL Sever 2000的SSMS把老师给定的数据库附加进去，然后打开T-SQL开始做实验。
1．查询图书价格在15~30元之间的所有图书的书名、书号、出版日期、价格和库存册书，且结果按书号进行升序排序。
根据要求，查询指定图书价格条件的图书信息，只需对“Book”表做单表查询。SQL语句如下：
select BookName,BookISBN,BookPublishDate,BookPrice,BookCurNum
from Book
where BookPrice between 15 and 30
order by BookISBN asc
执行结果如下：
2．查询图书名称中包含“计算机”的所有图书信息。
SQL语句如下：
select *
from Book
where BookName like '%计算机%';
执行结果如下图：
3．查询借书应归还日期在2009年8月1日前的用户账户。
SQL语句如下：
select UserID
From Borrow
where BorrowEndDate < '2009-8-1';
执行结果如下图：
4．出还没有的出版社信息。
SQL语句如下：
select *
from Publish
where PublishTelephone is null;
执行结果：
5．查询所有图书的平均价格。
SQL语句如下：
select avg(all BookPrice) as  '所有图书的平均价值'
from Book;
执行结果：
6．查询各个出版社的图书的平均价格。
SQL语句如下：
select avg(all BookPrice) as  '各出版社平均价值'
from Book
group by BookPublishID;
执行结果为：
7．查询已经归还且超期图书数至少超过2本德借书证号及其超期图书总数。
SQL语句如下：
select UserID,count(*) as '超期图书总数'
from ExtraDateFee
group by UserID
having count(*)>=2
执行结果：
8．查询库存册数低于10的图书书名、库存数和其出版该图书的出版社名称。
用连接查询来实现，SQL语句如下：
SELECT BookName,BookCurNum,PublishName
FROM Book,Publish
where Book.BookPublishID=Publish.PublishID and BookCurNum<10;
执行结果：
9．查询一年相同月份里出版的不同图书的书名、出版日期和出版社标识。
用自身连接查询，SQL语句如下：
select B1.BookName,B1.BookCurNum,B1.BookPublishID
from Book B1,Book B2
where year(B1.BookPublishDate)=year(B2.BookPublishDate)
  and month(B1.BookPublishDate)=month(B2.BookPublishDate)
  and B1.BookName!=B2.BookName
执行结果为：
10．查询用户信息和该用户的借出图书标识、应归还日期。如果该用户没有借阅图书，只显示该用户信息。
用外连接查询：
select Users.UserID,Borrow.BookID,Borrow.BorrowEndDate
from Users left outer join Borrow on (Users.UserID=Borrow.UserID)
执行结果为：
11．查询借阅了“清华大学出版社”出版的图书且尚未归还的用户总人数。
用连接查询：
select count(distinct UserID) as '未归还总人数'
from Borrow,Book,Publish
where PublishName='清华大学出版社'
    and Borrow.BookID=Book.BookID
    and Book.BookPublishID=Publish.PublishID
    用嵌套查询：
select count(distinct UserID)as '未归还总人数'
from Borrow
where BookID in(
select BookID from Book,Publish
where PublishName='清华大学出版社'
and Book.BookPublishID=Publish.PublishID)
对嵌套查询进一步分解：
select count(distinct UserID)as '未归还总人数'
from Borrow
where BookID in(
select BookID
from Book
where BookPublishID in (
select PublishID
from Publish
where PublishName='清华大学出版社'))
执行结果：
12．查询借阅了书名包含“Delphi”和“编程”的图书且还未归还这些图书的用户姓名、图书名、应归还日期。
SQL语句如下：
用连接查询：
select UserName,BookName,BorrowEndDate
from Users,Borrow,Book
where Users.UserID=Borrow.UserID
      and Borrow.BookID=Book.BookID
      and (Book.BookName like '%Delphi%编程%'
      or Book.BookName like '%编程%Delphi%'
      )
用嵌套查询：
select UserName,BookName,BorrowEndDate
from Borrow,Users,Book
where Users.UserID=Borrow.UserID
      and Borrow.BookID=Book.BookID
      and Borrow.BookID in(
          select BookID
          from Book
          where
              BookName like '%Delphi%编程%'
            or BookName like '%编程%Delphi%'
)   
执行结果：
13．查询借阅图书应归还日期为“2009年8月1日”前且库存册数小于10的图书的用户姓名、所在院系、、借阅图书的出版社名称、书名。
用连接查询：
select UserName,UserDepart,UserTelephone,PublishName,BookName
from Users,Publish,Book,Borrow
where Users.UserID = Borrow.UserID
and  Borrow.BookID = Book.BookID
and  Publish.PublishID= Book.BookPublishID
and  Borrow.BorrowEndDate < '2009-08-01'
and Book.BookCurNum < 10
用嵌套查询：
select UserName,UserDepart,UserTelephone,PublishName,BookName
from Users,Publish,Book,Borrow
where Users.UserID = Borrow.UserID
and  Borrow.BookID = Book.BookID
and  Publish.PublishID= Book.BookPublishID
and Users.UserID in(
select UserID
from Borrow
where BookID in(
select BookID
from Book
where BookCurNum <10  )
and                                                                    UserID in(
select UserID
from Borrow
where BorrowEndDate < '2009-08-01') )
执行结果为：
14．出和书号为“730200899X”的图书有相同购书数量的图书名称、书号、出版社标识及库存数。
用嵌套查询：
select BookName,BookISBN,BookPublishID,BookCurNum
from Book
where  Book.BookNum =(
select BookNum
from Book
where BookISBN = '730200899x'
)
用自身连接查询：
select B1.BookName,B1.BookISBN,B1.BookPublishID,B1.BookCurNum
from Book B1,Book B2
where B1.BookNum=B2.BookNum
and B2.BookISBN='730200899x'
执行结果：
15．查询图书价格高于出版该图书的出版社的所有图书平均价格的图书名称、出版社标识和图书价格。
SQL语言：
select BookName,BookPublishID,BookPrice
from Book B1
where BookPrice >
  (select avg(BookPrice)
    from Book B2
    where B1.BookPublishID = B2.BookPublishID
)
执行结果为;
16．查询在书库中的从来没有被读者借阅过的图书信息。
利用not exists语句查询：
select *
from Book
where not exists
    (select *
    from BorrowHistory
    where BookID=Book.BookID
    )
执行结果：
17．查询有图书价格大于或等于标识为“2”的出版社的所有图书价格的出版社标识。
用谓词all：
select distinct BookPublishID
from Book
where BookPrice >=all(
          select BookPrice
          from Book
          where BookPublishID=2)
执行结果为：
distinct查询
18．查询每一本图书的书名、图书书号、出版社标识、图书价格及该图书书号的前一个书号。
SQL查询如下：
select BookName,BookISBN,BookPublishID,BookPrice,
    (select max(B2.BookISBN)
      from Book B2
      where B2.BookISBN<B1.BookISBN ) as '前一个书号'
from Book B1
order by BookISBN
执行结果为：
19．利用集合运算，查询图书号为“5084-3567-7”或“7050191836”的图书名称、书号和库存册数。
采用集合运算符号UNION实现查询：
(select BookName,BookISBN,BookCurNum
from Book
where Book.BookISBN='5084-3587-7')
union
(select BookName,BookISBN,BookCurNum
from Book
where Book.BookISBN='7050191836'
)
执行结果：
20．利用集合运算，查询同时借阅了图书标识为“1”和“2”图书的读者的借书证号。
由于在SQL Server 2000不支持直接使用保留字intersect进行交运算，所以用and逻辑与运算符来实现：
select distinct B1.UserID
from Borrow B1,Borrow B2
where (B1.UserID in(
      select UserID
      from Borrow
      where Borrow.BookID=1 )
    and B2.UserID in(
        select UserID
        from Borrow
        where Borrow.BookID=2))
and B1.UserID=B2.UserID
执行结果为：
21．实现集合减运算查询借阅了图书标识为“1”的图书而没有解压图书标识为“5”的图书的读者借书证号。
由于在SQL Server 2000不支持直接使用保留字except运算符，所以用and逻辑与运算符来实现：
select distinct B1.UserID
from Borrow B1,Borrow B2
where (B1.UserID in(
      select UserID
      from Borrow
      where Borrow.BookID=1 )
    and B2.UserID  not in(
        select UserID
        from Borrow
        where Borrow.BookID=5))
    and B1.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语句如下：
      create index I_BHReturnDate on BorrowHistory(BorrowReturnDate desc)
    利用SSMS设计工具，按如下步骤。
      在SSMS中选择指定的数据库LibraryLib，展开“表”节点。右击要为其创建索引的表BorrowHistory，然后从弹出的快捷键菜单中选择“设计”命令，右击菜单栏中选择“索引”如下图。
        在打开的属性窗口中创建索引
2．用T-SQL语句为“借出图书信息表Borrow”创建按“借书证号”升序排列的索引I_UserID。
create index I_UserID on Borrow(UserID)
3．创建表“图书信息表Book”的按“书号”升序排列的唯一索引I_BookISBN。
create unique index I_BookISBN on Book(BookID asc)
4．取消“借出图书信息表Borrow”的“借书证号”升序索引。
drop index Borrow.I_UserID
四、实验心得
本次实验相对比较简单，通过本次实验可以熟悉利用SSMS中的设计工具来创建索引，并掌握使用T-SQL语句来创建、取消索引的方法。但是对索引在检索数据库的应用的理解还不够，因为本次实验中数据库中的数据比较少，所以还没有很好的理解利用索引来加快查询速度的优点。
思考与练习
1．数据查询练习
(1)查询各个超期用户及其总数，结果按总数降序排列。
select UserID,count(*) as '总数'
from ExtraDateFee
group by UserID
order by '总数' desc
执行结果如下：
(2)查询已经超期一个月以上的还未归还图书的用户借书编号。
select distinct UserID
from Borrow
where getdate()-BorrowBeginDate>30
执行结果：
(3)查询已经超期一个月意思的还未归还图书的用户名称、以及超期图书名。
select UserName,UserTelephone,BookName
from Users,Borrow,Book
where Users.UserID=Borrow.UserID
  and Book.BookID=Borrow.BookID
and Borrow.UserID in (
select UserID
from Borrow
where getdate()-BorrowBeginDate>30)
执行结果：
(4)查询在同一天借阅了不同图书的用户借书证号
select B1.UserID
from Borrow B1,Borrow B2
where B1.BorrowBeginDate=B2.BorrowBeginDate
and  B1.BookID !=B2.BookID
查询结果为空
：
(5)查询借书从来没有被超期的用户信息。
select *
from Users
where UserID != all(
select UserID
from ExtraDateFee
)
执行结果：
(6)检索有图书所购册书大于或等于图书价格超过50的图书任一所购册书的图书的价格。
select BookPrice,BookName
from Book
where BookNum>= any(
select BookNum
from Book
where  BookPrice>50
)
执行结果：
索引练习
(1)为“借出图书信息表Borrow”创建按“应归还日期”降序排列的索引。
create index I_BorrowEndDate on Borrow(BorrowEndDate desc)
(2)为“图书信息表Book”创建按“书名”升序排列的唯一索引。
create unique index I_BookName on Book(BookName asc)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。