数据库实验五六

实验五数据查询和连接查询一．实验目的使学生熟练掌握简单表的数据查询、数据连接查询以及数据排序的操作方法，并加深对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),price decimal(7,2) NOT NULL,book_total Int NOT NULL)INSERT INTO Computer_Book SELECT *FROM Book WHERE press='清华大学出版社';select *from Computer_Book; 【查看表】（2）将借书日期在2012年以前的借阅记录存入临时超期借阅表。

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

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

SELECT *FROM Book WHERE press='清华大学出版社';（5）查询目前被借出去的书籍的书号、借书证号以及借出日期，并按借出日期排序。

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

SELECT title FROM Book,Card,BorrowWHERE (o=o) and (Borrow.bno=Book.bno) and (name='程军')（7）查询借书者的编号、姓名、单位、所借书号、书名和借阅日期，并按读者编号排序。

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

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

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

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

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

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

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

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

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

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

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

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

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

二．实验软件环境Microsoft SQL Server 20012三．实验内容要求完成以下查询要求：1）查询哪一年的图书最多。

2）查询每本借书证的借书册数。

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

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

四．实验的结果及分析根据实验内容，利用SQL语句在查询分析器中输入如下代码并可得相应输出结果，/*查询哪一年的图书最多*/use Liabery_3115001482;select book_year from book group by book_year;having sum(book_total)>=all(select sum(book_total) from book group by book_year);/*查询每本借书证的借书册数*/use Liabery_3115001482;select cno,count(cno)as count_borrow from borrow group by cno;/*今年未借过书的借书证*/use Liabery_3115001482;select * from card where not exists (select * from borrow where cno=o );/*哪个系的同学借书最多*/use Liabery_3115001482;select department from Card, Borrowwhere o = ogroup by departmenthaving count(o)>=all(select count(o)from Card, Borrowwhere o = o group by department)/*今年哪种类型的书借出最多*/use Liabery_3115001482;select category from Book, Borrowwhere Book.bno = Borrow.bnogroup by categoryhaving count(Borrow.bno)>=all(select count(Borrow.bno)from Book, Borrowwhere Book.bno = Borrow.bno group by category)——这里是并列一样多。

五．实验心得体会通过实验，对嵌套查询进行实质性的操作，在实践的基础上，更是懂得了其理论原理，表明对老师及书本上的知识更进一步的了解，对数据库的学习又迈出了新的一步。