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

2017级计算机专业集美大学计算机工程学院2018~2019学年第二学期1《数据库原理》实验报告一、实验目的：掌握SELECT语句的基本语法；掌握子查询、连接查询使用方法；掌握SELECT语句的GROUPBY和ORDERBY子句的作用和使用方法。掌握使用创建、删除索引的基本方法掌握视图的定义（创建和删除），查询，更新（注意更新的条件）；掌握索引分析与维护的常用方法。二、实验使用环境：SQLserver2012、powerdesigner16三、实验内容与完成情况：结果截图：--题目一createtable总金额(--创建表商品名称nvarchar(20),进货总价格money)selecttop50percentGoo_nameas商品名称,(Pur_price*Pur_num)as进货总价格into总金额--导入fromGoodsinnerjoinPurchase--连接货物表与购单表onGoods.Goo_no=Purchase.Goo_no--连接条件为货物名相等2017级计算机专业集美大学计算机工程学院2018~2019学年第二学期2解题思路：本题是查询进货单中前50％的商品的名称和进货总价格，因此先将货物表和进货表在货物编号相等的条件下进行内连接，再在新形成的表中进行查询。进货表中一共有16条数据，此处查询到8条数据。结果截图：解题思路：本题先按照雇员号进行分组，再用聚合函数SUM算出所有雇员在2018年的销售额之和，最后通过将销售总金额进行降序排列，在排列好的数据中选出top1即得到结果。结果截--题目二selecttop1Emp_noas雇员号,sum(Sell_prices*Sell_num)as销售总金额fromSell--选出按照销售总金额降序排序后的top1whereyear(Sell_date)=2018--年份为2018年groupbyEmp_no--按照雇员号分组orderby销售总金额DESC--降序排列--题目三selectGoo_no商品编号,sum(M.A)进货数量from(--对分组后同一商品数据进行求和累加(selectsum(Pur_num)asA,Goo_nofromPurchase--查询购单表中同一商品的数量groupbyGoo_no)union--将两张表进行并操作(selectsum(Sell_num)asS,Goo_nofromSell--查询售卖表中同一商品的数量groupbyGoo_no))asMgroupbyGoo_no--对合并的表通过商品编号分组2017级计算机专业集美大学计算机工程学院2018~2019学年第二学期3图：解题思路：先分别查询出Sell和Purchase表中同一商品的商品编号和商品数量，用UNION把查询到的两张表做并操作，得到不同时期的库存数量和已售数量，再按照商品编号进行分组，把同一组的数量进行相加得到结果。结果截图：selectEmployees.Emp_noas员工号,Emp_nameas员工姓名,Dep_noas部门号,sum(Sell_num*Sell_prices)as销售总金额fromEmployeesfulljoin(select*--运用全连接，保存所有员工信息fromSellwhereSell_date'2019-2-1')asSell--时间限制在2月之前onEmployees.Emp_no=Sell.Emp_nogroupbyEmployees.Emp_no,Emp_name,Dep_no--分组2017级计算机专业集美大学计算机工程学院2018~2019学年第二学期4解题思路：本题有两个关键点，第一个是：2019年2月之前，用时间小于该时间即可筛选；第二个是要同时显示没有销售量的员工，这就需要对两张表进行左连接或者全连接，保留员工表中的所有数据。结果截图：解题思路：先用内连接把两张表进行连接将销售信息和员工信息一一对应起来再通过时间的筛选得到第四季度的销售信息，得到要求时间内的销售信息后按照员工号进行分组，再把同一组的销售额加起来排序后就是所得结果。结果截图：--题目六selectGoo_name,sum(Sell_num)as销售总量fromGoodsleftjoinSell--左连接保留所有商品名称onGoods.Goo_no=Sell.Goo_no--连接条件是商品名相同groupbyGoo_name--按照商品名分组orderby销售总量DESC--降序排列selectEmployees.Emp_noas编号,Emp_nameas姓名,sum(Sell_prices*Sell_num)as总销售额fromEmployeesinnerjoinSell--连接销售表和员工表onEmployees.Emp_no=Sell.Emp_no--连接条件是员工号相同where'2018-9-1'=Sell_date--查询时间在第四季度（9-12月）andSell_date='2018-12-31'groupbyEmp_name,Employees.Emp_no2017级计算机专业集美大学计算机工程学院2018~2019学年第二学期5解题思路：先把两张表按照商品名称相等进行连接，再按照商品名称进行分组，分组后对同意商品进行求和，求和结果进行降序排列。解题思路：筛选需要的信息后直接按照部门号、性别分组，再求和即得到结果。结果截图：--题目七selectDep_no部门号,Emp_sex性别,count(Emp_name)员工人数fromEmployees--分组后求和groupbyEmp_sex,Dep_no--按照性别、部门号分组--题目八selectDep_no专业,Emp_sex性别,count(Emp_name)人数fromEmployeesgroupbyDep_no,Emp_sex--按照学院、性别分组withrollup--显示所有分组聚合情况2017级计算机专业集美大学计算机工程学院2018~2019学年第二学期6结果截图：解题思路：在上个题的基础上加上withrollup显示所有分组聚合情况。区别是这个是先按照部门号分组，再按照性别分组。上个题是先按照性别分组，再按照部门号分组--题目九selectEmployees.Emp_noas编号,Employees.Emp_nameas姓名,s.sumaryas总销售额,(casewhensumary100000thensumary*0.01whensumary500000andsumary100000thensumary*0.02whensumary500000thensumary*0.03End)asbonus--使用casewhen语句对不同条件下的总销售额进行分类计算fromEmployees,(selectEmp_no,sum(Sell_num*Sell_prices)assumaryfromSell--先进行员工号、12月的销售额的选取，并作为表为上面提供来源whereSell_date='2018-12-01'andSell_date='2018-12-31'groupbySell.Emp_no)asswhereEmployees.Emp_no=s.Emp_no2017级计算机专业集美大学计算机工程学院2018~2019学年第二学期7结果截图：解题思路：先把员工号、要求时间内的总销售额查询出来并作为一张表，再以这张表为数据来源之一，通过casewhen进行判断提成的类型，最后得到结果。Ps：此题碰坑，详见问题栏。结果截图：--题目十gocreateviewView_JNwithencryption--视图加密asselectSell_noas销售编号,Sell.Goo_noas商品编码,Goo_nameas商品名称,Sell_numas销售数量,Pur_priceas进货价,Sell_pricesas销售价,(Sell_prices-Pur_price)*Sell_numas销售纯利润from(SellinnerjoinPurchase--由于存在销售数量、单价，进货单价，因此先把销售表和进货表连接onSell.Goo_no=Purchase.Goo_no)--连接条件为商品编号相等innerjoinGoods--把上面两张表的连接结果与货物表连接才能得到商品名称onGoods.Goo_no=Sell.Goo_nowhereSell_datelike'2018-12%'--通过like语句把时间限制在2018年12月andGoods.Goo_noin(selectGoods.Goo_nofromGoods--通过嵌套进行限制为佳能公司的商品whereGoods.Pro_name='佳能公司')go--分批操作select*fromView_JN--查看视图go2017级计算机专业集美大学计算机工程学院2018~2019学年第二学期8解题思路：此题数据较多，输出涉及到三张表的内容，且要求建立视图。先通过两次商品编号相等条件下的内连接把三张表进行连接，然后进行条件的逐个筛选，其中用到了嵌套查询选择公司的条件。顺便，记得用go来进行分批操作。结果截图：解题思路：此题同样为分条件的判断题，第四季度理解为10月到12月的三个月，通过casewhenthen判断时间范围将销售额归类为不同月份，对归类后的销售额通过sum聚合函数进行累加即可得到结果。selectEmp_noas员工号,sum(casewhenSell_date='2018-10-01'ANDSell_date='2018-10-31'then(Sell_prices*Sell_num)else0end)as'2018年10月销售总金额',sum(casewhenSell_date='2018-11-01'ANDSell_date='2018-11-30'then(Sell_prices*Sell_num)else0end)as'2018年11月销售总金额',sum(casewhenSell_date='2018-12-01'ANDSell_date='2018-12-31'then(Sell_prices*Sell_num)else0end)as'2018年12月销售总金额'fromSell--通过case进行条件判断groupbyEmp_no--建表createtableTableIndex(T_idintIDENTITY(1,1),--自增长DataValuenumeric(15,8))2017级计算机专业集美大学计算机工程学院2018~2019学年第二学期9/*---向TestIndex数据库表中插入10000条数据---*/declare@rnumeric(15,8)declare@nintset@n=0while(1=1)beginset@r=rand()insertintoTableIndex(DataValue)values(@r)set@n=@n+1if(@n10000)breakend/*----------------无索引查询-------------------*//*---计算开始时间---*/setnocountondeclare@ddatetimeset@d=getDate()/*---查询DataValue值在0.3~0.9的数据---*/select*fromTableIndexwhereDataValuebetween0.3and0.9/*---显示查询所需时间---*/declare@timeintset@time=datediff(ms,@d,getDate())print'查询耗时：'+convert(varchar(10),@time)+'毫秒'/*---------------添加索引-------------------*//*---为DataValue列添加索引---*/ifexists(se