数据库实验五六

1、实验五 数据查询和连接查询一实验目的使学生熟练掌握简单表的数据查询、数据连接查询以及数据排序的操作方法，并加深对Transact-SQL语言的查询语句的理解。二实验软件环境SQL Server2012三实验内容(一) 实验要求： 在SQL Server 2012查询设计器中，并用SELECT语句及WHERE查询条件进行简单查询及连接查询实验，其次使用ORDER BY排序子句实现输出结果的排序。主要内容包括： 1）简单查询操作  包括投影、选择条件、数据排序等。 2）连接查询操作  包括

2、等值连接、自然连接、外连接、内连接、左连接和右连接等。 (二) 要求完成以下查询要求： 1）将清华大学出版社的书存入永久计算机图书表。 2）将借书日期在2012年以前的借阅记录存入临时超期借阅表。3）查询发生了借阅关系的借书证号、借阅姓名等信息，并按借书证号降序排列。 4）查询“清华大学出版社”出版的图书情况。 5）查询目前被借出去的书籍的书号、借书证号以及借出日期，并按借出日期降序排序。 6）查询“程军”老师所借阅的书籍的书名。 7）查询借书者的编号、姓名、单位、所借书号、书名和借阅日期，并按读者编号排序。四实验

3、的结果及分析（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

4、 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, C

5、ONSTRAINT 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 *FRO

6、M 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,Borrow WHERE (Co=Bo) and (Borrow.bno=Book.bn

7、o) and (name='程军')  （7）查询借书者的编号、姓名、单位、所借书号、书名和借阅日期，并按读者编号排序。 SELECT Bo,name,department,Borrow.bno,title,borrow_date FROM Borrow,Card,Book WHERE(Co=Bo) and (Borrow.bno=Book.bno) ORDER Bo 操作中出现的问题：由于借书日期默认都是系统当前时间，因此不存在借书日期在

8、2012年以前的数据，因此，我们可以尝试着插入一条数据记录INSERT INTO Borrow(cno,bno,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的书，因此首先要添加数据。解决方法：在前面

9、的语句前加入：INSERT INTO Book VALUES ('7','小说','斗破苍穹','中国文学出版社',2010,'wisdon',20.00,30); 思考题：（1）请思考数据库中永久表和临时表之间的差异临时表与永久表相似，但临时表存储在tempdb中，当不再使用时会自动删除。临时表有两种类型：本地和全局。它们在名称、可见性以及可用性上有区别。本地临时表的名称以单个数字符号 (#) 打头；它们仅对当前的用户连接是可见的；当用户从 SQL Server 实例断开连接时被删除。全局临时表的名称以两个数字符

10、号 (#) 打头，创建后对任何用户都是可见的，当所有引用该表的用户从 SQL Server 断开连接时被删除。（2）对于“查询程军老师所借阅的书籍的书名”的查询语句，分别使用左连接、右连接和自然连接命令，试比较所产生的结果有何不同，说明其原因，并指出对于该查询要求，哪种连接更符合实际要求。 答：程军老师的借书证号：1006使用左接： select title from book left join borrow on (book.bno=borrow.bno and cno='

11、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' 

12、; 。该查询使用自然连接更符合实际要求。五实验心得体会通过本实验基本学会如何进行简单表的数据查询、数据联接查询以及数据排序，并加深对SQL和Transact-SQL语言的查询语句的理解，通过实践验证能较好地掌握书本上的知识。实验六 嵌套查询一实验目的使学生熟练掌握数据查询中嵌套查询语句的操作方法，并加深对Transact-SQL语言的查询语句的理解。二实验软件环境Microsoft SQL Server 20012三实验内容要求完成以下查询要求：1）查询哪一年的图书最多。2）查询每本借书证的借书册数。3）今年未借过书的借书证4）哪个系的同学借书最多。5）今年哪种类别的书借出最多。四实

13、验的结果及分析根据实验内容，利用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

14、 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 Co = Bogroup by departmenthaving count(Bo)>=all(select count(Bo)from Card, Borrowwhere Co = Bo group by department)/*今年哪种类型的书借出最多*/use Liabery_3115001482; select category from Book, Borrowwhere Book.bno = Borrow.bno