数据库上机实验答案4

数据库上机实验答案4学号：姓名：日期：年月日实验目的：学习使用ODBC的方法；体验T-SQL的功能；体验存储过程的功能；体验表值函数、标量值函数的作用；体验ranking等功能。1练习视图及连接查询。（1）创建一个视图，视图名为viNF，视图内容为selectid,count(*)asnffromfriendsgroupbyid。执行成功后，将SQL语句复制到下方。createviewviNFas(selectid,count(*)asnffromfriendsgroupbyid)（2）基于viNF视图，查找拥有最多好友的用户、最少好友的用户。执行成功后，将SQL语句复制到下方。selecttop1*fromviNForderbynfascselecttop1*fromviNForderbynfdesc或者select*fromviNFwherenf=(selectmin(nf)fromviNF)select*fromviNFwherenf=(selectmax(nf)fromviNF)（3）基于users表和viNF视图进行连接查询。分别进行内连接、全外连接、左外连接、右外连接四种操作。执行成功后，将SQL语句复制到下方，并回答：四种结果表，哪两个的结果是一致的，为什么？select*fromusersinnerjoinviNFonusers.id=viNF.idselect*fromusersfullouterjoinviNFonusers.id=viNF.idselect*fromusersleftouterjoinviNFonusers.id=viNF.idselect*fromusersrightouterjoinviNFonusers.id=viNF.id其中，innerjoin和rightouterjoin结果一致，都是63条；fullouterjoin和leftouterjoin结果一致，都是64条。这是因为users表有64条数据，而viNF只有63条数据。有一个id没有朋友，因此不在viNF视图中出现。（4）将题（3）中全外连接保存为一个新的视图viUAF。createviewviUAFas(selectusers.*,viNF.nffromusersfullouterjoinviNFonusers.id=viNF.id)21通过ODBC用Excel打开users表。控制面板管理工具数据源(ODBC)添加新的数据源SQLServerExcel数据OLEBODBC3体验T-SQL。回顾实验2中的题目：定义最低价格为成本价；依据此成本价做如下计算：连接Goods，Goods_Extent，Sellers表，按照总利润，输出前10名；要求输出表的格式为（商品名称，卖家名称，商品价格，运费，卖家信誉，卖家好评率，历史销量，历史利润，期内销量，期内利润，总销量，总利润）利用如下语句进行查询，体会和之前有什么不同。如有时间，自己可以仿照写一个变量定义、赋值及应用的例子。declare@costasfloat;select@cost=min(good_price)fromgoods;selecttop10good_nameas商品名称,goods.seller_nameas卖家名称,good_priceas商品价格,good_shippingas运费,rp_as_selleras卖家信誉,positive_percent_as_selleras卖家好评率,good_soldbeforeas历史销量,good_soldbefore*(good_price-@cost)as历史利润,good_soldintermas期内销量,good_soldinterm*(good_price-@cost)as期内利润,good_soldbefore+good_soldintermas总销量,(good_soldbefore+good_soldinterm)*(good_price-@cost)as总利润fromgoods,goods_extent,sellerswheregoods.good_id=goods_extent.good_idandgoods.seller_id=sellers.seller_idorderby总利润desc;注意：这里用了declare@costasfloat来声明变量用了select@cost=min(good_price)fromgoods;来赋值应用时直接用@cost4体验存储过程。执行如下语句：CREATEPROCEDUREgetFriendsList@idintASBEGINprint('存储过程样例');selectid,gender,homeprovincefromuserswhereidin(selectfriendIDfromfriendswhereid=@id);END执行完毕后，在可编程性存储过程下查看自己创建的存储过程，并右键点击修改存储过程，观察所展示的代码与上述代码的不同之处。在查询窗口中执行如下语句，理解这一存储过程：execgetFriendsList155100422;注意：创建时为CREATE，修改时为MODIFY@id为传入参数执行存储过程要用exec…5体验存储过程的输入和输出。执行如下语句：CREATEProcedurecal_median@sourceTBnvarchar(100),@sourceColumnnvarchar(100),@sourceConditionsnvarchar(500),@medianfloatoutASBEGINdeclare@sqlnvarchar(1000)declare@median_bottomfloatdeclare@median_topfloatset@sql='SELECT@median_bottom=MAX('+@sourceColumn+')FROM(SELECTTOP50PERCENT'+@sourceColumn+'FROM(select'+@sourceColumn+'from'+@sourceTB+'where'+@sourceConditions+')asvariable_listORDERBY'+@sourceColumn+')ASBottomHalf'execsp_executesql@sql,N'@median_bottomfloatout',@median_bottomoutset@sql='SELECT@median_top=MIN('+@sourceColumn+')FROM(SELECTTOP50PERCENT'+@sourceColumn+'FROM(select'+@sourceColumn+'from'+@sourceTB+'where'+@sourceConditions+')asvariable_listORDERBY'+@sourceColumn+'DESC)ASTopHalf'execsp_executesql@sql,N'@median_topfloatout',@median_topoutset@median=round((@median_bottom+@median_top)*1.0/2,2)END在查询窗口中执行如下语句，理解这一存储过程是如何实现输出的：declare@medianfloatexeccal_median'DBCourse.dbo.users','nFriends','homeProvince=''陕西''',@medianoutprint@median注意：这个存储过程用来计算中值。计算的过程为：前一半的最大值和后一半的最小值的平均值。存储过程的输出用out来指出。6体验表值函数。执行如下语句：createfunctiongetFriends(@idasint)returns@friendstable(idint,gendervarchar(2),placevarchar(50))asbegininsertinto@friendsselectid,gender,homeprovincefromuserswhereidin(selectfriendIDfromfriendswhereid=@id);return;end执行完毕后，在可编程性函数表值函数下查看自己创建的表值函数，并右键点击修改函数，观察所展示的代码与上述代码的不同之处。在查询窗口中执行如下语句，理解表值函数的应用方式：select*fromgetFriends(155100422)注意：表值函数类似于创建一张表并插入数据。7体验标量值函数。执行如下语句：createfunctiongetFriendsNumber(@idasint)returnsintasbegindeclare@nFriendsint;set@nFriends=(selectcount(*)fromfriendswhereid=@id);return@nFriends;end执行完毕后，在可编程性函数标量值函数下查看自己创建的标量值函数，并右键点击修改函数，观察所展示的代码与上述代码的不同之处。在查询窗口中执行如下语句，理解标量值函数的应用方式：select*,dbo.getFriendsNumber(id)fromusers注：若此处报错，查看标量值函数的函数名，对dbo做相应修改。注