《数据库原理与应用》课程实验报告四 --数据库的组合和统计查询

《数据库原理与应用》课程实验报告 数据库的组合和统计查询

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

二、实验过程及分析

1.实验内容

1、分组查询实验。该实验包括分组条件表达、选择组条件的表达方法。
2、使用函数查询的实验。该实验包括统计函数和分组统计函数的使用方法。
3、组合查询实验。
4、计算和分组计算查询的实验。

2.实验过程

创建图书表
create table 图书
(
  书号  varchar(10) primary key,
  书名 varchar(20)not null,
  类别 varchar(20),
  作者 varchar(10),
  出版社 varchar(20),
  定价   int
)
select *  from 图书
drop table  图书  
--插入数据
insert into  图书 values ('B101','数据结构','计算机类','陈越','清华出版社',38),
                         ('j0002','操作系统','计算机类','李祥','科学出版社',35),
						 ('j0001','数据库技术','计算机类','苗雪兰','清华出版社',25),
						 ('W100','明朝那些事','文学类','当年明月','北京联合出版社',38),
						 ('Y101','大学英语','英语类','李靖','机械工业出版社',20),
						 ('Y102','专业英语','英语类','jack','机械工业出版社',30),
						 ('Y103','科技英语','英语类','meric','机械工业出版社',150),
						 ('B102','数据库','计算机类','kkkk','机械工业出版社',89),
						 ('B103','计算机组成原理','计算机类','qqq','机械工业出版社',79),
						 ('B104','计算机概论','计算机类','oo','机械工业出版社',100)
						
--创建读者表
create table 读者
(
  读者编号 int primary key,
  姓名 varchar(20)not null,
  单位 varchar(20),
  性别 varchar(10),
  电话 varchar(11)
)
select *  from 读者
drop table  读者

--插入数据
insert into  读者 values(1801,'张三','生工院','男',12345678901),
                        (1802,'李白','机械院','女',12345678901),
						(1803,'翠花','英语院','男',12345678901),
						(1804,'狗蛋','思政院','男',12345678901)


--创建借阅表
create table    借阅表
(
  书号  varchar(10),
  读者编号 int,
  借书日期  date  not null,
  还书日期   date
  primary key (书号,读者编号),
  foreign key(书号) references 图书(书号),
  foreign key(读者编号) references 读者(读者编号)
)

select *  from 借阅表
drop table  借阅表
--插入数据
insert  into  借阅表  values  ('j0001',1801,'2019-01-01','2020-01-01'),
                                ('j0001',1802,'2019-12-11','2020-01-03'),
								('W100',1802,'2019-12-11','2020-01-03'),
								('B102',1802,'2019-12-11','2020-01-03'),
								 ('Y102',1803,'2019-12-30','2020-04-01'),
								  ('j0002',1801,'2019-01-01','2019-03-03'),
								  ('j0002',1804,'2019-04-01','2019-12-12'),
								  ('Y103',1801,'2019-05-01','2019-10-30'),
								  ('Y103',1802,'2018-12-30','2019-12-01'),
								  ('B103',1802,'2019-12-30','2019-12-01'),
								  ('Y103',1803,'2019-07-01','2019-12-18')

查询操作

--1、查找每个出版社所出版图书的最高价。
select    出版社, max(定价) as 最高价  from   图书   group by 出版社

--2、查找借阅图书超过3本的读者编号和借书册数，要求只统计2019-12-10以后的借书情况。
select  读者编号  , count(*) 借书册数   from   借阅表 where  借书日期 > '2019-12-10' 
                         group by  读者编号 having  count(*)>3

--3、查找读者的借阅情况，即读者编号、借书册数，并进行借书情况汇总。
select  读者编号 ,  COUNT(书号) as  册数 
from 借阅表
group  by   读者编号 

--4、求机械工业出版社出版的各类图书的平均定价，用GROUP BY表示。
select 类别  , AVG(定价)
from 图书 where  出版社='机械工业出版社'
group  by   类别

--5、按照年份统计每个读者的借书册数，并进行汇总。
select  读者编号 , YEAR(借书日期) as 年份 , COUNT(书号) as  册数 
from 借阅表
group  by   读者编号 ,YEAR(借书日期)

--6、查询借阅了书号为“j0001”或“j0002”的读者编号。
select  读者编号 
from  借阅表 
where  书号= 'j0001'
union
select  读者编号 
from  借阅表 
where  书号= 'j0002'

--7、查询既借阅了书号为“j0001”，又借阅了书号为“j0002”的读者编号。
select  读者编号 
from  借阅表 
where  书号= 'j0001'
intersect
select  读者编号 
from  借阅表 
where  书号= 'j0002'

--8查询借阅了书号为“j0001”，但没有借阅书号为“j0002”的读者编号。
select  读者编号 
from  借阅表 
where  书号= 'j0001'
except
select  读者编号 
from  借阅表 
where  书号= 'j0002'

--9、查找这样的图书类别：要求类别中最高的图书定价不低于按类别分组的图书平均定价的2倍。
select 类别
from  图书
group by  类别 having max(定价) >= 2*AVG(定价)

3.实验结果

4.实验分析

①分析实验结果；
顺利的完成实验
②说明实验过程中遇到的问题及怎么解决。
不能正确
查找借阅图书超过3本的读者编号和借书册数，要求只统计2019-12-10以后的借书情况
要正确的使用聚集函数，以及where 和 having的区别

select  读者编号  , count(*) 借书册数   from   借阅表 where  借书日期 > '2019-12-10' 
                         group by  读者编号 having  count(*)>3

③对错误方面进行分析；
where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。
having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件 显示特定的组，也可以使用多个分组标准进行分组。

三、实验总结

①.对本次实验掌握程度如何；
完成度8成
②.是否在规定的时间内完成，没完成的原因是什么；
没有，没能熟练的使用sql语句。
③.总结操作错误或无法得到结果的原因）
Where 和having 区别
1.类型：
“Where”是一个约束声明，在查询数据库的结果返回之前对数据库中的查询条件进行约束，即在结果返回之前起作用，且where后面不能使用“聚合函数”；
“Having”是一个过滤声明，所谓过滤是在查询数据库的结果返回之后进行过滤，即在结果返回之后起作用，并且having后面可以使用“聚合函数”。

2.使用的角度：
where后面之所以不能使用聚合函数是因为where的执行顺序在聚合函数之前，
如下面这个sql语句：select sum(score) from student group by student.sex where sum(student.age)>100;
having既然是对查出来的结果进行过滤，那么就不能对没有查出来的值使用having，
如下面这个sql语句： select student.id,student.name from student having student.score >90;