实验三、数据查询、汇总、性能优化

《数据库原理》实验报告

一、实验目的：

●掌握SELECT语句的基本语法；

●掌握子查询、连接查询使用方法；

●掌握SELECT语句的GROUPBY和ORDERBY子句的作用和使用方法。

●掌握使用创建、删除索引的基本方法

●掌握视图的定义（创建和删除），查询，更新（注意更新的条件）；

●掌握索引分析与维护的常用方法。

二、实验使用环境：

SQL server 2012、powerdesigner16

三、实验内容与完成情况：

结果截图：

--题目一

create table总金额( --创建表

商品名称nvarchar(20),

进货总价格money

)

select top 50 percent Goo_name as商品名称,(Pur_price*Pur_num)as进货总价格into总金额 --导入

from Goods inner join Purchase--连接货物表与购单表

on Goods.Goo_no=Purchase.Goo_no--连接条件为货物名相等

解题思路：本题是查询进货单中前50％的商品的名称和进货总价格，因此先将货物表和进货表在货物编号相等的条件下进行内连接，再在新形成的表中进行查询。进货表中一共有16条数据，此处查询到8条数据。

结果截图：

解题思路：本题先按照雇员号进行分组，再用聚合函数SUM算出所有雇员在2018年的销售额之和，最后通过将销售总金额进行降序排列，在排列好的数据中选出top 1 即得到结果。

结果截--题目二

select top 1 Emp_no as雇员号,sum(Sell_prices*Sell_num)as销售总金额

from Sell--选出按照销售总金额降序排序后的top1

where year(Sell_date)=2018 --年份为2018年

group by Emp_no--按照雇员号分组

order by销售总金额DESC--降序排列

--题目三

select Goo_no商品编号,sum(M.A)进货数量

from(--对分组后同一商品数据进行求和累加

(select sum(Pur_num)as A,Goo_no

from Purchase--查询购单表中同一商品的数量

group by Goo_no)

union--将两张表进行并操作

(select sum(Sell_num)as S,Goo_no

from Sell--查询售卖表中同一商品的数量

图：

解题思路：先分别查询出Sell和Purchase表中同一商品的商品编号和商品数量，用UNION把查询到的两张表做并操作，得到不同时期的库存数量和已售数量，再按照商品编号进行分组，把同一组的数量进行相加得到结果。

结果截图：

select Employees.Emp_no as员工号,Emp_name as员工姓名,Dep_no as部门

号,sum(Sell_num*Sell_prices)as销售总金额

from Employees full join(select*--运用全连接，保存所有员工信息

from Sell

where Sell_date<'2019-2-1')as Sell--时

间限制在2月之前

on Employees.Emp_no=Sell.Emp_no

group by Employees.Emp_no,Emp_name,Dep_no--分组

解题思路：本题有两个关键点，第一个是：2019年2月之前，用时间小于该时间即可筛选；第二个是要同时显示没有销售量的员工，这就需要对两张表进行左连接或者全连接，保留员工表中的所有数据。

结果截图：

解题思路：先用内连接把两张表进行连接将销售信息和员工信息一一对应起来再通过时间的筛选得到第四季度的销售信息，得到要求时间内的销售信息后按照员工号进行分组，再把同一组的销售额加起来排序后就是所得结果。

结果截图：

--题目六

select Goo_name,sum(Sell_num)as销售总量

from Goods left join Sell--左连接保留所有商品名称

on Goods.Goo_no=Sell.Goo_no--连接条件是商品名相同

group by Goo_name--按照商品名分组

order by销售总量DESC--降序排列

select Employees.Emp_no as编号,Emp_name as姓名,sum(Sell_prices*Sell_num)as总

销售额

from Employees inner join Sell--连接销售表和员工表

on Employees.Emp_no=Sell.Emp_no--连接条件是员工号相同

where'2018-9-1'<=Sell_date--查询时间在第四季度（9-12月）

and Sell_date<='2018-12-31'

group by Emp_name,Employees.Emp_no

解题思路：先把两张表按照商品名称相等进行连接，再按照商品名称进行分组，分组后对同意商品进行求和，求和结果进行降序排列。

解题思路：筛选需要的信息后直接按照部门号、性别分组，再求和即得到结果。

结果截图：

--题目七

select Dep_no部门号,Emp_sex性别,count(Emp_name)员工人数

from Employees--分组后求和

group by Emp_sex,Dep_no--按照性别、部门号分组

--题目八

select Dep_no专业,Emp_sex性别,count(Emp_name)人数

from Employees

group by Dep_no,Emp_sex--按照学院、性别分组

with rollup--显示所有分组聚合情况