数据库实验五六

实验五数据查询和连接查询一. 实验目的使学生熟练掌握简单表的数据查询、数据连接查询以及数据排序的操作方法，并加深对Transact-SQL语言的查询语句的理解。

二. 实验软件环境SQL Server2012三. 实验内容（一）实验要求：在SQL Server 2012查询设计器中，并用SELECT语句及WHERE查询条件进行简单查询及连接查询实验，其次使用ORDER BY排序子句实现输出结果的排序。

主要内容包括：1）简单查询操作包括投影、选择条件、数据排序等。

2）连接查询操作包括等值连接、自然连接、外连接、内连接、左连接和右连接等。

（二）要求完成以下查询要求：1）将清华大学出版社的书存入永久计算机图书表。

2）将借书日期在2012年以前的借阅记录存入临时超期借阅表。

3）查询发生了借阅关系的借书证号、借阅姓名等信息，并按借书证号降序排列。

4）查询“清华大学出版社”出版的图书情况。

5）查询目前被借出去的书籍的书号、借书证号以及借出日期，并按借出日期降序排序。

6）查询“程军”老师所借阅的书籍的书名。

7）查询借书者的编号、姓名、单位、所借书号、书名和借阅日期，并按读者编号排序。

四. 实验的结果及分析（1）将计算机类的书存入永久计算机图书表CREATE TABLE Computer_Book（bno char（8） PRIMARY KEY,category varchar （10）,title varchar(40) NOT NULL, press varchar(30) NOT NULL, book_year Int NOT NULL, author varchar(20),pricedecimal(7,2) NOT NULL, book_totalInt NOT NULL)INSERT INTO Computer_Book SELECT *FROM Book WHERE press='清华大学出版社select *from Computer_Book;【查看表】[3结果缶消息bao flats gorytitle4uth4r pri1 门 ! ]计算机科学 严瞬设H淸华尢学出版社 3033J 容。

口 30.00 125 210计尊机科学 pythanAll青华大学出饭社 2C10 Flying 4S.00 120 3 □人文社科思考的技术淸华大学岀販社 ?035Jerry43.00120(2) 将借书日期在2012年以前的借阅记录存入临时超期借阅表。

CREATE TABLE #Overdue_Borrow ( enochar(7) FOREIGN KEY(CNO) REFERENCES Card(c no), bnochar(8) FOREIGN KEY(BNO) REFERENCES Book(b no),borrow_date SMALLDATETIME DEFAULT GETDATE(), return_date SMALLDATETIME, CONSTRAINT CBB_PRIM PRIMARY KEY(c no,b no,borrow_date))INSERT INTO Overdue_Borrow SELECT *from Borrow WHERE YEAR(borrow_date)<2012;(3) 查询发生了借阅关系的借书证号、借阅姓名等信息，并按借书证号降序排列。

SELECT DISTINCT cn o,c name FROM Card where cn o in (select cno from Borrow) ORDER BY eno DESC;(4) 查询“清华大学出版社”出版的图书情况。

SELECT *FROM Book WHERE press='清华大学出版社';210 计尊机科学pythm人门看华長学出版社2010 3 5 人文社科思誉的技术肓华大学出版社2015Flying43.00 12048. 00 LEO(5)查询目前被借出去的书籍的书号、借书证号以及借出日期，并按借出日期排序。

SELECT bn o,cno,borrow_date FROM Borrow WHERE return_date IS NULL ORDER BY borrow_date;(6)查询“程军”老师所借阅的书籍的书名。

SELECT title FROM Book,Card,BorrowWHERE (Card.c no=Borrow.c no) and (Borrow.b no=Book.b no) and (n ame=S 军')(7)查询借书者的编号、姓名、单位、所借书号、书名和借阅日期，并按读者编号排序。

SELECT Borrow.c no,n ame,departme nt,Borrow.b no,title,borrow_date FROM Borrow,Card,BookWHERE(o=o) and (Borrow.b no=Book.b no) ORDER Borrow.c no操作中出现的问题：由于借书日期默认都是系统当前时间，因此不存在借书日期在2012年以前的数据，因此，我们可以尝试着插入一条数据记录INSERT INTO Borrow(cno,b no,borrow_date,return_date) VALUES ('1008',7','2010-11-01','2018-08-25');//但是出现问题：存在外键约束限制。

分析：外键约束，比如B表存在一个字段b为B表的外码，有外键约束，字段B是A表的主键，那么在向B 表插入数据时，字段b必须为A表中b已经存在的值，否则会报违反外键约束。

正是因为Book表不存在bno = 7的书，因此首先要添加数据。

解决方法：在前面的语句前加入：INSERT INTO Book VALUES ('7','小说','斗破苍穹','中国文学出版社',2010,'wisdo n',20.00,30);思考题:(1)请思考数据库中永久表和临时表之间的差异临时表与永久表相似，但临时表存储在tempdb中，当不再使用时会自动删除。

临时表有两种类型：本地和全局。

它们在名称、可见性以及可用性上有区别。

本地临时表的名称以单个数字符号倂)打头；它们仅对当前的用户连接是可见的；当用户从SQL Server实例断开连接时被删除。

全局临时表的名称以两个数字符号(##)打头，创建后对任何用户都是可见的，当所有引用该表的用户从SQL Server断开连接时被删除。

(2)对于“查询程军老师所借阅的书籍的书名”的查询语句，分别使用左连接、右连接和自然连接命令，试比较所产生的结果有何不同，说明其原因，并指出对于该查询要求，哪种连接更符合实际要求。

答：程军老师的借书证号：1006使用左select title from book left join borrow on (book.b no=borrow.b no and eno='1006')使用右连接：select title from borrow right join book on (borrow.bno=book.bno and eno='1006') 使用自然连接：select title * from borrow,book where borrow.bno=book.bno and eno='1006'。

该查询使用自然连接更符合实际要求。

五. 实验心得体会通过本实验基本学会如何进行简单表的数据查询、数据联接查询以及数据排序，并加深对SQL 和Transaet-SQL语言的查询语句的理解，通过实践验证能较好地掌握书本上的知识。

实验六嵌套查询一. 实验目的使学生熟练掌握数据查询中嵌套查询语句的操作方法，并加深对Transaet-SQL语言的查询语句的理解。

二. 实验软件环境Microsoft SQL Server 20012.实验内容要求完成以下查询要求：1)查询哪一年的图书最多2）查询每本借书证的借书册数。

3）今年未借过书的借书证4） 哪个系的同学借书最多。

5） 今年哪种类别的书借出最多。

四.实验的结果及分析根据实验内容，利用SQL 语句在查询分析器中输入如下代码并可得相应输出结果，/*查询哪一年的图书最多*/ use Liabery_3115001482;select book_year from book group by book_year;hav ing sum(book_total)>=all(select sum(book_total) from book group by book_year);口结果Lj :肯息1boo]c_year pniirMimraviiiimii irMii■>2015/*查询每本借书证的借书册数*/ use Liabery_3115001482;select eno ,co un t(c no )as coun t_borrow from borrow group by eno;口鉅貝jIQD.D1 LiiKM L2 LCHE 13 LOd3 14 LOCH I5 LCDF5L g L 财 t 7 LOB ILlWt/*今年未借过书的借书证*/ use Liabery_3115001482;select * from card where not exists (select * from borrow where eno=card.c no );/*哪个系的同学借书最多*/use Liabery_3115001482;select departme nt from Card, Borrowwhere Card.c no = Borrow.c no group by departme nt hav ing coun t(Borrow.c no) >=all(select coun t(Borrow.c no)from Card, Borrow where Card.c no = Borrow.c no group by departme nt)园结果匕消息/*今年哪种类型的书借出最多*/ use Liabery_3115001482;select category from Book, Borrow where Book.b no = Borrow.b no group by categoryhav ing coun t(Borrow.b no) >=all(select coun t(Borrow.b no)from Book, Borrow where Book.b no = Borrow.b no group by category)O 结果衍消息五.实验心得体会通过实验，对嵌套查询进行实质性的操作，在实践的基础上，更是懂得了其理论原理, 老师及书本上的知识更进一步的了解，对数据库的学习又迈出了新的一步。