MySQL数据库操作题(课程作业)

1.具体任务：进入dos窗口登录Mysql数据库，使用SQL语句实现以下任务：

(1)创建博客数据库名为：db_blog。

(2)在db_blog数据库内创建两个表：用户信息表:users及文档信息表：blog。

表结构如下：

用户信息表:users
序号	字段名	说明	类型	是否为空	约束条件	其他
1	id	id号	int	not null	无符号、主键、自增
2	name	姓名	varchar(32)	not null	唯一性
3	email	邮箱地址	varchar(100)	null
4	cdate	注册时间	datetime	null
博客信息表:blog
序号	字段名	说明	类型	是否为空	约束条件	其他
1	id	id号	int	not null
2	title	标题	varchar(100)	not null
3	abstract	摘要	varchar(200)	not null
4	content	博文内容	text	not null
5	uid	用户标识	int	null	无符号
6	pcount	点赞数	int		无符号：默认值0
7	flag	状态	tinyint		无符号:（默）0新建,1发布,2删除
8	cdate	创建时间	datetime

(3)在用户信息表内添加5条以上的测试数据。

(4)在博客信息表中添加10条以上的测试数据。

2.操作步骤如下：

(1)创建数据库db_blog：

create database if not exists db_blog;

(2)选择进入db_blog数据库：

use db_blog;

(3)创建用户信息users表格：

create table users( id int unsigned auto_increment not null primary key comment 'id号', name varchar(32) not null unique comment '姓名', email varchar(100) comment '邮箱地址', cdate datetime comment '注册时间' )default charset=utf8;

（4）创建博客信息表blog：

create table blog( id int unsigned not null auto_increment primary key comment 'id号', title varchar(100) not null comment '标题', abstract varchar(200) not null comment '摘要', content text not null comment '博文内容', uid int unsigned comment '用户标识', pcount int unsigned default 0 comment '点赞数', flag tinyint unsigned default 0 comment '0新建,1发布,2删除', cdate datetime comment '创建时间' )default charset=utf8;

(5)向用户信息表users内添加数据:

insert into users values (1,'张三','测试邮箱1',now()), (null,'李四','测试邮箱2',now()), (null,'王二','测试邮箱3',now()), (null,'戴五','测试邮箱4',now()), (null,'麻子','测试邮箱5',now());

(6)向博客数据信息表users内添加数据:

insert into blog values (1,'测试标题1','测试大纲1','测试文章内容1',1,5,1,now()), (null,'测试标题2','测试大纲2','测试文章内容2',1,15,1,now()), (null,'测试标题3','测试大纲3','测试文章内容3',1,7,1,now()), (null,'测试标题4','测试大纲4','测试文章内容4',1,9,2,now()), (null,'测试标题5','测试大纲5','测试文章内容5',1,25,1,now()), (null,'测试标题6','测试大纲6','测试文章内容6',1,18,2,now()), (null,'测试标题7','测试大纲7','测试文章内容7',1,19,2,now()), (null,'测试标题8','测试大纲8','测试文章内容8',1,11,1,now()), (null,'测试标题9','测试大纲9','测试文章内容9',1,32,0,now()), (null,'测试标题10','测试大纲10','测试文章内容10',1,23,1,now()), (null,'测试标题11','测试大纲11','测试文章内容11',1,8,0,now());

3.在之前表数据基础之上实现以下功能：

(1)在users表中查询注册时间最早的十条会员信息。

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

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

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

(5)删除没有发表博文的所有会员信息。

具体操作步骤如下：

(1)在users表中查询注册时间最早的十条会员信息。

select * from users order by cdate asc limit 10;

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

select b.id 博文id,b.title 标题,b.pcount 点赞数,u.name 会员名 from blog b,users u where b.uid=u.id order by b.pcount desc limit 5;

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

select u.id 会员id号,u.name 姓名,count(b.uid) 博文数量 from users u left join blog b on u.id=b.uid and b.flag=1 group by u.id order by 博文数量 desc;

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

select u.id 会员id,u.name 姓名,avg(b.pcount) 平均点赞数 from users u left join blog b on u.id=b.uid group by u.id order by 平均点赞数 desc limit 3;

(5)删除没有发表博文的所有会员信息。

delete from users where id in(select * from (select u.id from users u left join blog b on u.id=b.uid and b.flag=1 group by u.id having count(b.uid)=0) as temp);

参考文件链接：

https://blog.csdn.net/momobaba2018/article/details/82701454

获取源码及视频讲解等更多内容请扫码关注下方公众号二维码。