数据库 - 第六次实验

a)

SELECT * 
FROM video 
WHERE vid=35

结果：

b)

SELECT user.*   
FROM user JOIN post ON user.uid = post.uid   
WHERE post.vid = 35

结果：

c)

SELECT count(*) 
FROM watch 
WHERE vid=35

结果：

d)

SELECT count(*) 
FROM like 
WHERE vid=35

结果：

e)

SELECT count(*) 
FROM collect 
WHERE vid=35

结果：

f)

SELECT sum(coin.amount) 
FROM coin 
WHERE vid=35

结果：

g)

SELECT * 
FROM comment 
WHERE vid=35 
ORDER BY video_time ASC

结果：

SELECT user.nickname,count(post.vid)
FROM user JOIN post ON user.uid = post.uid  
GROUP BY user.uid, user.nickname  
ORDER BY count(post.vid) DESC  
LIMIT 5

结果：

SELECT user.nickname,user.profile,user.userlevel, count(follow.uid_followed)
FROM user JOIN follow ON user.uid = follow.uid_followed
GROUP BY user.nickname,user.profile,user.userlevel
ORDER BY count(follow.uid_followed) DESC
LIMIT 10

结果：

SELECT video.name,video.description,count(watch.vid)
FROM video JOIN watch ON video.vid = watch.vid
GROUP BY video.name,video.description,video.vid
ORDER BY count(watch.vid) DESC
LIMIT 10

结果：

SELECT video.chanel,count(watch.vid)
FROM video JOIN watch ON video.vid = watch.vid
GROUP BY video.chanel

结果：

SELECT video.name,count(watch.uid)
FROM watch JOIN video ON watch.vid = video.vid
WHERE video.chanel = '美食'
GROUP BY video.vid
ORDER BY count(watch.uid) DESC
LIMIT 5

结果：

SELECT video.chanel,count(watch.vid)
FROM watch JOIN video ON watch.vid = video.vid
WHERE watch.uid = 15
GROUP BY video.chanel
ORDER BY count(watch.vid) DESC
LIMIT 5

结果：

SELECT video.name,video.description
FROM user JOIN watch ON watch.uid = user.uid
          JOIN video ON watch.vid = video.vid
WHERE user.uid = 15 AND watch.time > 900

结果：

SELECT user.nickname,user.profile,user.userlevel
FROM user JOIN follow ON user.uid = follow.uid_follower
WHERE follow.uid_followed = 15

结果：

SELECT video.name,video.description,post.post_time
FROM video JOIN post ON video.vid = post.vid
           JOIN follow ON post.uid=follow.uid_followed
WHERE follow.uid_follower = 15
ORDER BY post.post_time DESC

结果：

SELECT user.nickname,user.profile,user.userlevel
FROM user JOIN follow f1 ON user.uid = f1.uid_followed
          JOIN follow f2 ON user.uid = f2.uid_follower
WHERE f1.uid_follower = 15 AND f2.uid_followed = 15

结果：