摘要：
1、 表格创建用户表格tb_ usercreatetableifnotexistb_User（User_idintauto_increment，User_namevarchar（32）notnull，User_ passwordvarchar，64）notnull，User_ avatavarchar
一、建表
用户表tb_user
create table if not existstb_user( user_id intauto_increment, user_name varchar(32) not null, user_password varchar(64) not null, user_avatar varchar(128) default null, user_city varchar(32) not null, user_createdat datetime default current_timestamp, user_updatedat datetime default current_timestamp on update current_timestamp, primary key(user_id), unique(user_name) )default charset = utf8;
博客表tb_blog
create table if not existstb_blog( blog_id intauto_increment, blog_user_id int not null, blog_title varchar(100) not null, blog_content varchar(1024) not null, blog_createdat datetime default current_timestamp, blog_updatedat datetime default current_timestamp on update current_timestamp, primary key(blog_id), foreign key(blog_user_id) references tb_user(user_id) on delete cascade on update cascade)default charset=utf8;
标签表tb_tag
create table if not existstb_tag( tag_id intauto_increment, tag_content varchar(16) not null, primary key(tag_id) )default charset = utf8;
博客标签表tb_blog_tag
create table if not existstb_blog_tag( blog_tag_id intauto_increment, rel_blog_id int not null, rel_tag_id int not null, primary key(blog_tag_id), foreign key(rel_blog_id) references tb_blog(blog_id) on delete cascade on update cascade, foreign key(rel_tag_id) references tb_tag(tag_id) on delete cascade on update cascade)default charset=utf8;
评论内容tb_comment
create table if not existstb_comment( comment_id intauto_increment, comment_blog_id int not null, comment_user_id int not null, comment_content varchar(256) not null, comment_createdat datetime default current_timestamp, comment_updatedat datetime default current_timestamp on update current_timestamp, primary key(comment_id), foreign key(comment_blog_id) references tb_blog(blog_id) on delete cascade on update cascade, foreign key(comment_user_id) references tb_user(user_id) on delete cascade on update cascade)default charset = utf8;
二、相关查询
1.从用户表中查询beijing和shanghai的所有用户信息
select user_name,user_city fromtb_user where user_city='beijing' or user_city='shanghai'; select user_name,user_city fromtb_user where user_city in ('beijing','shanghai');
2.从用户表中查询2018-7-3 15:50:00至2018-7-3 15:53:00之间注册的所有用户信息
select user_name,user_city,user_createdat fromtb_user where user_createdat > '2018-07-03 15:50:00' and user_createdat < '2018-07-03 15:53:00'; select user_name,user_city,user_createdat fromtb_user where user_createdat between '2018-07-03 15:50:00' and '2018-07-03 15:53:00';
3.从用户表中找到最晚的注册时间
select max(user_createdat) fromtb_user; select max(user_createdat)abc from tb_user;
4.从用户表中查询每个城市的最晚注册时间(城市名称，时间)
select user_city,max(user_createdat) fromtb_user group by user_city;
5.从用户表中查询最晚注册用户的信息
select user_name,user_createdat fromtb_user where user_createdat = (select max(user_createdat) from tb_user);
6.每个城市的最晚注册用户的信息
前面已经得到了每个城市最晚注册时间表，只是还没有和用户关联起来，那么现在只需要把每个城市最晚注册时间表和用户表做迪卡尔积，再通过城市和时间这两个条件把满足条件的用户筛选出来
select user_name,tb_user.user_city,user_createdat fromtb_user join (select user_city,max(user_createdat)m fromtb_user group byuser_city )t on tb_user.user_city = t.user_city and tb_user.user_createdat = t.m;
7.查询所有作者及其blog信息
select user_name,blog_title fromtb_user jointb_blog on user_id = blog_user_id;
8.查询所有作者及其blog信息，显示时，将一个作者写的所有博客标题放到一行显示
select user_name,group_concat(blog_title) fromtb_user jointb_blog on user_id =blog_user_id group by user_name;
9.查询所有用户及其写的blog
select user_name,blog_title fromtb_user left jointb_blog on user_id = blog_user_id;
10.查询所有的博客及其标签信息
step1. 从tb_blog_tag找有标签的博客id以及他们的标签id
selectrel_blog_id,rel_tag_id from tb_blog_tag
step2. 将step1产生的结果集与标签表进行联合查询，找到标签id对应的具体标签内容
selectrel_blog_id, tag_content fromtb_tag join( selectrel_blog_id,rel_tag_id fromtb_blog_tag ) on tag_id = rel_tag_id
step3.将一篇博客的多个标签放到一行显示
selectrel_blog_id, group_concat(tag_content) fromtb_tag join( selectrel_blog_id,rel_tag_id fromtb_blog_tag )t on tag_id =rel_tag_id group by rel_blog_id
step4.将博客表tb_blog与step3产生的结果集进行联合查询，找到博客的具体信息
selectblog_id,blog_title,tc fromtb_blog left join( selectrel_blog_id, group_concat(tag_content)tc fromtb_tag join( selectrel_blog_id,rel_tag_id fromtb_blog_tag )t on tag_id =rel_tag_id group byrel_blog_id )t1 on blog_id = rel_blog_id;
11.查询所有的博客及其标签信息和作者信息
这个就是在10题的基础上再和tb_user做一次笛卡儿积
select user_name,user_avatar,blog_id,blog_title,tc fromtb_user join( selectblog_id,blog_title,tc,blog_user_id fromtb_blog left join( selectrel_blog_id, group_concat(tag_content)tc fromtb_tag join( selectrel_blog_id,rel_tag_id fromtb_blog_tag )t on tag_id =rel_tag_id group byrel_blog_id )t1 on blog_id =rel_blog_id )t2 on user_id = blog_user_id;