数据库上机实验报告 4
学号：姓名：日期：年月日
实验目的：（1）练习连接查询；（2）练习视图的创建与使用；（3）学习使用ODBC的方法；（4）体验T-SQL的功能；体验存储过程的功能；体验表值函数、标量值函数的作用；体验ranking等功能。
1 练习视图及连接查询。
（1）创建一个视图，视图名为viNF，视图内容为select id,count(*) as nf from friends group by id。
执行成功后，将SQL语句复制到下方。
（2）基于viNF视图，查找拥有最多好友的用户、最少好友的用户。
执行成功后，将SQL语句复制到下方。
（3）基于users表和viNF视图进行连接查询。
分别进行内连接、全外连接、左外连接、右外连接四种操作。
执行成功后，将SQL语句复制到下方，并回答：四种结果表，哪两个的结果是一致的，为什么？
（4）将题（3）中全外连接保存为一个新的视图viUAF。
2 通过ODBC用Excel打开users表。
3 体验T-SQL。
回顾实验2中的题目：
定义最低价格为成本价；依据此成本价做如下计算：
连接Goods，Goods_Extent，Sellers表，按照总利润，输出前10名；要求输出表的格式为（商品名称，卖家名称，商品价格，运费，卖家信誉，卖家好评率，历史销量，历史利润，期内销量，期内利润，总销量，总利润）
利用如下语句进行查询，体会和之前有什么不同。
如感兴趣，自己可以仿照写一个变量定义、赋值及应用的例子。
declare @cost as float;
select @cost=min(good_price)from goods;
select top 10
good_name as商品名称,
goods.seller_name as卖家名称,
good_price as商品价格,
good_shipping as运费,
rp_as_seller as卖家信誉,
positive_percent_as_seller as卖家好评率,
good_soldbefore as历史销量,
good_soldbefore*(good_price-@cost)as历史利润,
good_soldinterm as期内销量,
good_soldinterm*(good_price-@cost)as期内利润,
good_soldbefore+good_soldinterm as总销量,
(good_soldbefore+good_soldinterm)*(good_price-@cost)as总利润
from goods,goods_extent,sellers
where goods.good_id=goods_extent.good_id and goods.seller_id=sellers.seller_id
order by总利润desc;
4 体验存储过程。
执行如下语句：
CREATE PROCEDURE Notes_SQL
@id int
AS
BEGIN
print('存储过程样例');
select id,gender,homeprovince from users where id in(select friendID from friends where id=@id);
END
执行完毕后，在可编程性 存储过程下查看自己创建的存储过程，并右键点击修改存储过程，观察所展示的代码与上述代码的不同之处。
在查询窗口中执行如下语句，理解这一存储过程：
exec Notes_SQL 155100422;
5 体验存储过程的输入和输出。
执行如下语句：
CREATE Procedure cal_median
@sourceTB nvarchar(100),
@sourceColumn nvarchar(100),
@sourceConditions nvarchar(500),
@median float out
AS
BEGIN
declare @sql nvarchar(1000)
declare @median_bottom float
declare @median_top float
set@sql ='SELECT @median_bottom=MAX( '+@sourceColumn +' ) FROM (SELECT TOP 50 PERCENT
'+ @sourceColumn +' FROM (select '+ @sourceColumn +' from '+ @sourceTB +' where ' + @sourceConditions +') as variable_list ORDER BY '+ @sourceColumn +' ) AS BottomHalf' exec sp_executesql@sql,N'@median_bottom float out', @median_bottom out
set @sql ='SELECT @median_top=MIN( '+ @sourceColumn +' ) FROM (SELECT TOP 50 PERCENT '+ @sourceColumn +' FROM (select '+ @sourceColumn +' from '+ @sourceTB +' where ' +@sourceConditions +') as variable_list ORDER BY '+@sourceColumn +' DESC) AS TopHalf' exec sp_executesql@sql,N'@median_top float out', @median_top out
set @median =round((@median_bottom+@median_top)*1.0/2,2)
END
在查询窗口中执行如下语句，理解这一存储过程是如何实现输出的：
declare @median float
exec cal_median'users','nFriends','homeProvince=''陕西''',@median out
print @median
6 体验表值函数。
执行如下语句：
create function getFriends(@id as int)
returns @friends table (id int, gender varchar(2), place varchar(50))
as
begin
insert into@friends select id,gender,homeprovince from users where id in(select friendID from friends where id=@id);
return;
end
执行完毕后，在可编程性→函数→表值函数下查看自己创建的表值函数，并右键点击修改函数，观察所展示的代码与上述代码的不同之处。
在查询窗口中执行如下语句，理解表值函数的应用方式：
select*from getFriends(155100422)
7 体验标量值函数。
执行如下语句：
create function getFriendsNumber(@id as int)
returns int
as
begin
declare @nFriends int;
set @nFriends=(select count(*)from friends where id=@id);
return @nFriends;
end
执行完毕后，在可编程性→函数→标量值函数下查看自己创建的标量值函数，并右键点击修改函数，观察所展示的代码与上述代码的不同之处。
在查询窗口中执行如下语句，理解标量值函数的应用方式：
select*,dbo.getFriendsNumber(id)from users
注：若此处报错，查看标量值函数的函数名，对dbo做相应修改。
8 体验ranking等功能。
执行如下语句：
select*,row_number()over (partition by good_place order by good_price)from goods
执行完毕后，观察执行结果。
将row_number替换为rank, dense_rank，观察不同之处。