数据库上机实验答案3

1、数据库上机实验答案 3学号： 姓名： 日期：20 年 月 日实验目的：练习连接查询，练习视图。综合练习SQL查询语句。实验内容：基于提供的社交网络数据，包括：社交网络用户users表，社交网络好友关系friends表，完成如下查询，并回答问题。要求：（1）输出查询结果；（2）输出查询语句。各表的字段解释如下：Users 用户列表（id 用户IDgender 用户性别homeprovince 家乡所在省份nFriends 好友数nPosts 发表的日志数nAlbums 相册数nVisits 空间访问人数）Friends好友关系表（id 用户IDfriendID 好友ID）1 二度好友研究所谓二度

2、好友，指的是好友的好友。如果B是A的好友，而C是B的好友，那么称C为A的二度好友。（1）查询二度好友表（ID, FriendID, FFID）select a.*,b.friendID as ffID from friends as a inner join friends as b on a.friendID=b.id（2）统计用户在社交网络中的好友数、二度好友数（ID, nF, nFF）注意：社交网络中的好友数与users表中的好友数可能不同。with tb_nf as (select ID,count(*) as nf from friends group by ID),tb_nff a

3、s (select a.ID,count(*) as nff from friends as a inner join friends as b on a.friendID=b.ID and a.IDb.friendID and (b.friendID not in (select friendID from friends where ID=a.ID) group by a.id)select tb_nf.ID,nf,nff from tb_nf inner join tb_nff on tb_nf.ID=tb_nff.ID注：这里用了两个with语句来预定义两个子查询tb_nf tb_nf

4、f，分别计算nF, nFF的值。在计算nFF的值的时候，考虑到自己不能是自己的二度好友（a.IDb.friendID），以及自己的一度好友不能是自己的二度好友（b.friendID not in (select friendID from friends where ID=a.ID)）。此外，注意到nF与users表中的nFriends是不同的，nFriends指的是在整个社交网络上的好友数，而nF是在我们选取的局部网络中的好友数。这个查询的结果有63条记录，而users表有64条记录。原因是其中一个user在此局部网络中没有好友，因此没有在friends表中出现。（3）根据users表，创建

5、一个完全相同的新表users_update，暂时不插入数据。create table users_update(id int,gender varchar(2),homeprovince varchar(50),nFriends int,nPosts int,nAlbums int,nVisits int)（4）在users_update后面新增两列nF, nFF。alter table users_update add nF int, nFF int（5）在users_update中插入数据。要求全部列都填写完毕。可选方案：可以先插入users数据，再update nF与nFF；或者直接插入

6、全部数据。可选方案1：可以先插入users数据，再update nF与nFF；插入users数据（64条）：insert into users_update select *,null,null from users;update nF（63条）:with tb_nf as (select ID,count(*) as nf from friends group by ID)update users_update set nF=tb_nf.nf from tb_nf where users_update.ID=tb_nf.ID;update nFF（63条）:with tb_nff as (s

7、elect a.ID,count(*) as nff from friends as a inner join friends as b on a.friendID=b.ID and a.IDb.friendID and (b.friendID not in (select friendID from friends where ID=a.ID) group by a.id)update users_update set nFF=tb_nff.nff from tb_nff where users_update.ID=tb_nff.ID;可选方案2：直接插入全部数据with tb_nf as

8、(select ID,count(*) as nf from friends group by ID),tb_nff as (select a.ID,count(*) as nff from friends as a inner join friends as b on a.friendID=b.ID and a.IDb.friendID and (b.friendID not in (select friendID from friends where ID=a.ID) group by a.id)insert into users_update select users.*,nf,nff

9、from users left outer join tb_nf on users.ID=tb_nf.id left outer join tb_nff on tb_nf.ID=tb_nff.ID注：如果用inner join，就会出现只有63条数据，而不是64条；因为在局部网络中没有好友的用户在join时被忽略了。2 为某个用户推荐好友为用户推荐好友的最简单的和最常见的方式，就是推荐朋友的朋友。假如A有3个好友，这三个好友同时又是B的好友，那么A和B有3个共同好友。推荐好友的原则，是共同好友数越多越好。提示步骤：（1）先连接friends表与friends表，得到二度好友表（ID, Frie

10、ndID, FFID）结构的表。（2）通过对ID, FFID分组统计，得到共同好友数。（3）对结果按照ID, 共同好友数排序。（4）为你选择的ID推荐好友。select a.id,b.friendID as ffid, count(*) as cn from friends as a inner join friends as b on a.friendID=b.id where b.friendIDa.id and b.friendID not in (select friendID from friends where id=a.id) group by a.id,b.friendID order by a.id,cn desc注：where子句里两个条件分别是：推荐的结果不能是用户自己；推荐的结果不能是用户已有的好友。3 查找493173055与251048342之间的“最短路径”select a.*,b.friendID as ffid,c.fri