数据库第四次试验：数据库的组合查询、统计查询及视图

前言

为了帮助同学们完成痛苦的实验课程设计，本作者将其作出的实验结果及代码贴至CSDN中，供同学们学习参考。如有不足或描述不完善之处，敬请各位指出，欢迎各位的斧正！

一、实验目的

使学生熟练掌握SQL Server查询分析器的使用方法，加深对SQL语言的查询语句的理解。熟练掌握数据查询中的分组、统计、计算和组合的操作方法。

二、实验要求

1、分组查询实验。该实验包括分组条件表达、选择组条件和表达方法。
2、使用集函数查询的实验。该实验包括统计函数和分组统计函数的使用方法。
3、组合查询实验。
4、计算和分组计算查询的实验。
5、创建、查看、修改和删除视图。

三、实验原理、方法和手段

1、将查询需求用 SQL 语言表示
在SQL Server查询分析器的输入区中输入SQL查询语句；设置查询分析器的结果区Standard Execute（标准执行）或 Execute to Grid（网格执行）方式；发布执行命令，并在结果区中查看结果；如果结果不正确，要进行修改，直到正确为止。
2、创建视图
（1) 在SQL Server查询分析器的输入区中输入SQL语句创建视图。如在图书读者库中建立一个视图，取名为读者_VIEW，其操作用SQL语句表示为：
CREATE VIEW 借阅_计算机图书 AS
SELECT 图书.*，借阅.读者编号，借阅.借阅日期 FROM 图书，借阅
WHERE 图书.书号=借阅.书号 AND 图书.类别=‘计算机’；；
（2）利用SQL Sever 中提供的视图创建向导，来创建读者_VIEW视图。
点击数据库library前面的＋号，打开数据库的各种属性，选中下面的“视图”，单击鼠标右键，出现视图的菜单，如图 4.1 所示。

图 4.1

点击“新建视图”，出现添加表窗口，选中要定义视图的表，如图 4.2 所示。

图 4.2

然后进入视图窗口，选择需要的列及视图条件即可创建视图。如图 4.3 所示。

图 4.3

3、查看和修改视图
视图创建好后，就可以利用它进行查询信息了。如果发现视图的结构不能很好地满足要求，还可以在企业管理器中对它进行修改。

4、删除视图
删除视图的方法是：首先要在企业管理器中，将鼠标指针指向数据库中的视图文件夹单击右键。在随后出现的弹出菜单中，选择“删除”项，会出现删除视图对话框。选中欲删除的视图，单击“全部移出”按钮，被选中的视图就会从视图中被移出。

四、实验组织运行要求

本实验属于验证型实验，通过实验，加强对课堂讲授知识的理解。开始实验前，必须进行预习，写出实现所有查询要求的SQL语句。实验过程中，先集中由老师进行具体要求和注意事项的讲解，然后各自独立在机器上完成实验。实验过程中出现问题，在实验指导老师帮助下解决。

五、实验条件

（1）硬件条件：个人计算机。
（2）软件条件：Windows；MS SQL Server。

六、实验步骤

1、基本操作实验
（1）在图书读者库中实现其查询操作。
1）查找这样的图书类别：要求类别中最高的图书定价不低于全部按类别分组的图书平均定价的 2 倍。
2）求机械工业出版社出版的各类图书的平均定价，用 GROUP BY 表示。
3）列出计算机类图书的书号、名称及价格，最后求出册数和总价格。
4）列出计算机类图书的书号、名称及价格，并求出各出版社这类书的总价格，最后求出全部册数和总价格。
5）查询计算机类和机械工业出版社出版的图书。
（2）在图书读者库中创建视图
在 SQL Server 企业管理器中调出 Create View Wizard，按下列 SQL 语句描述的视图定义，创建借阅_计算机图书视图。
CREATE VIEW 借阅_计算机图书 AS
SELECT 图书.*，借阅.读者编号，借阅.借阅日期
FROM 图书，借阅
WHERE 图书.书号=借阅.书号 AND 图书.类别=‘计算机’；
2、提高操作实验
（1）对自己设计的数据库应用项目中的分组、统计、计算和组合查询用 SQL 语句表示，并通过 SQL Server 查询分析器实现其查询操作。
（2）对自己设计的数据库应用项目用 SQL 语句描述其视图定义，并在 SQL Server 企业管理器中，用视图创建向导创建这些视图。
（3）查看上述实验结果。如果结果有误，予以纠正。

七、思考题

1、写出实现所有查询要求的SQL语句。

八、实验报告

1、基本操作实验
（1）在图书读者库中实现其查询操作。
附：librarydb.dbo.Book：

附：librarydb.dbo.BORROW：

1）查找这样的图书类别：要求类别中最高的图书定价不低于全部按类别分组的图书平均定价的 2 倍。

select Bclass from Book where Bprice=all(select max(Bprice) from Book where Bprice<=All(select avg(Bprice)*2 from Book))

2）求Tsinghua出版的各类图书的平均定价，用 GROUP BY 表示。

select Bclass,avg(Bprice) 'agv(Bprice)' from Book where Bpublish='Tsinghua' group by Bclass

3）列出B类图书的书号、名称及价格，最后求出册数和总价格。

select Bno,Bname,Bprice from Book where Bclass='B' 

select sum(Bprice*Bcount) ABprice,sum(Bcount) ABcount from Book where Bclass='B'

4）列出B类图书的书号、名称及价格，并求出各出版社这类书的总价格，最后求出全部册数和总价格。

select Bno,Bname,Bprice from Book where Bclass='B'

select sum(Bprice*Bcount) ABprice,Bpublish from Book where Bclass='B' group by Bpublish

select sum(Bprice*Bcount) ABprice,sum(Bcount) Bcount from Book where Bclass='B'

5）查询B类和Tsinghua出版的图书。

select * from Book where Bclass='B' and Bpublish='Tsinghua'

（2）在图书读者库中创建视图
在 SQL Server 企业管理器中调出 Create View Wizard，按下列 SQL 语句描述的视图定义，创建借阅_计算机图书视图。

CREATE VIEW 借阅_计算机图书 AS
SELECT 图书.*，借阅.读者编号，借阅.借阅日期
FROM 图书，借阅
WHERE 图书.书号=借阅.书号 AND 图书.类别=‘B’；

create view BORROW_Book as select Book.*,BORROW.Rno,BORROW.BRdate from librarydb.dbo.Book,librarydb.dbo.BORROW where Book.Bno=BORROW.Bno and Book.Bclass='B'

3、提高操作实验
（1）对自己设计的数据库应用项目中的分组、统计、计算和组合查询用 SQL 语句表示，并通过 SQL Server 查询分析器实现其查询操作。
1.查询书名为“Killer”的书籍编号、书籍分类、作者名：

select Bno,Bclass,Bauthor from Book where Bname='Killer'

2.查询所有清华出版社出版的书籍数目和总价格

select count(*) Bcount,sum(Bprice) ABprice from Book where Bpublish='Tsinghua';

3.查找这样的图书：最低的图书定价不高于全部按类别分组的图书平均定价。

select Bclass from Book group by Bclass having avg(Bprice)>min(Bprice)

4.查询图书所在类别及其所在类别所有单价大于20的图书的单价的总价格

select Bclass,sum(Bprice) from Book where Bprice>20 group by Bclass 

（2）对自己设计的数据库应用项目用 SQL 语句描述其视图定义，并在 SQL Server 企业管理器中，用视图创建向导创建这些视图。
1.创建图书名及价格大于20的中高价书目介绍视图（用sql语句创建）

create view VBBook as select Book.Bname,Book.Bprice from Book where Bprice>20

2.创建Book-BORROW视图（用视图创建向导创建）

SELECT   dbo.Book.Bno, dbo.Book.Bclass, dbo.Book.Bpublish, dbo.Book.Bauthor, dbo.Book.Bname, dbo.Book.Bprice, 
                dbo.BORROW.Bno AS Expr1, dbo.BORROW.Rno
FROM      dbo.Book INNER JOIN
                dbo.BORROW ON dbo.Book.Bno = dbo.BORROW.Bno

（3）查看上述实验结果。如果结果有误，予以纠正。

九、其他说明

1、注意 WHERE<条件>和 HAVING<条件>的使用区别。
2、组合查询的子句间不能有语句的结束符。
3、子句 HAVING<条件>必须和 GROUP BY<分组字段>子句配合使用。