数据库多表联查

从两个表中查询点赞数最高的5条博客信息,要求显示字段:(博文id,标题,点赞数,会员名)

Select b.id,b.title,b.pcount,u.name from blog b,users,u where u.id=b.uid order by pcount desc limit 5

统计每个会员的发表博文数量(降序),要求显示字段(会员id号,姓名,博文数量)

Select u.id,u.name count(b.id) n from user u join left blog b on b.uid=u.id group by u.id order by n desc

获取会员的博文平均点赞数量最高的三位。显示字段(会员id,姓名,平均点赞数)

Select u.id,u.name avg(b.pcount) n from user u join left blog b on b.uid=u.id group by u.id order by n desc limit 3

删除没有发表博文的所有会员信息

Delete * from user where id not in (select distinct uid from blog)

posted @ 2019-04-23 10:33  新月0717  阅读(146)  评论(0编辑  收藏  举报