数据库连接查询

实验二 数据库的简单查询和连接查询实验
一、实验目的
1、熟练掌握SQL Server查询窗口的使用方法，加深对SQL和Transact-SQL语言查询语句的理解。2、掌握单表简单查询和多表的连接查询语句的正确写法和实验操作方法。
二 实验环境
实验环境：Microsoft Windows 2014操作系统，Microsoft SQL Server 2014数据库管理系统标准版或企业版。
三、实验内容
1、实现基于单个表的查询
A. SELECT * FROM BOOK;
B.SELECT * FROM BOOK WHERE B_Name='JAVA';
C.SELECT COUNT(*) FROM BOOK GROUP BY B_Quantity;//group by 是将by后面的对象分 组,值相同的为一组.
D.SELECT R_Number , COUNT(B_Number) FROM BORROW GROUP BY R_Number ORDER BY R_Number//order by 是对记录进行升序排序.
2、用连接查询或嵌套查询的方法实现下列查询
在“图书读者数据库”（Book_Reader_DB）中，用连接查询的方法完成下列查询任 务：
1.查询借阅了类别为“教学”类图书的所有读者编号、姓名及单位:
语句SELECT READER.R_Number,R_Department FROM READER
INNER JOIN BORROW ON READER.R_Number=BORROW.R_Number
INNER JOIN BOOK ON BOOK.B_Number=BORROW.B_Number
WHERE BOOK.B_Classification=’教学’
结果

2.查询借阅过图书的读者编号、姓名及单位；
语句 SELECT READER.R_Number,R_Department FROM READER
INNER JOIN BORROW ON READER.R_Number=BORROW.R_Number
INNER JOIN BORROW ON READER.R_Number=BORROW.R_Number
结果:

3.查询姓名为“杨宇轩1”的读者目前借阅的图书书号和书名；
语句select BOOK.B_Number,B_name from Book
inner join BORROW on BORROW.B_Number=BOOK.B_Number
inner join READER on READER.R_Number = BORROW.R_Number
where R_Name='杨宇轩3'
结果:

4.查询借书过期的所有读者姓名及所借图书名（假定借阅期为5天）；
语句:
SELECT R_Name,B_Name FROM BORROW
INNER JOIN READER on BORROW.R_Number=READER.R_Number
INNER JOIN BOOK on BORROW.B_Number=Book.B_Number
where DATEDIFF(day,BorrowDate,GETDATE())>5
结果:

5.查询借出次数超过10次的所有图书的书号和书名；
语句SELECT B_Name, BORROW.B_Number FROM BORROW
INNER JOIN BOOK ON BOOK.B_Number=BORROW.B_Number
GROUP BY BORROW.B_Number,B_Name
HAVING COUNT(BORROW.R_Number) > 0
结果:

四 实验总结
学会了等值连接inner join,对聚合函数group by的作用了解更深刻.