mysql 个人博客应用的建表和相关查询

一、建表

用户表tb_user

create table if not exists tb_user(
user_id int auto_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 exists tb_blog(
blog_id int auto_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 exists tb_tag(
tag_id int auto_increment,
tag_content varchar(16) not null,
primary key(tag_id)
)default charset = utf8;

博客标签表tb_blog_tag

create table if not exists tb_blog_tag(
blog_tag_id int auto_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 exists tb_comment(
comment_id int auto_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 
from tb_user
where user_city='beijing' or user_city='shanghai';

select user_name,user_city 
from tb_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
from tb_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
from tb_user
where user_createdat between '2018-07-03 15:50:00' and '2018-07-03 15:53:00';

3.从用户表中找到最晚的注册时间

select max(user_createdat) from tb_user;
select max(user_createdat)abc from tb_user;

4.从用户表中查询每个城市的最晚注册时间(城市名称，时间)

select user_city,max(user_createdat)
from tb_user
group by user_city;

5.从用户表中查询最晚注册用户的信息

select user_name,user_createdat
from tb_user
where user_createdat = (select max(user_createdat) from tb_user);

6.每个城市的最晚注册用户的信息

前面已经得到了每个城市最晚注册时间表，只是还没有和用户关联起来，那么现在只需要把每个城市最晚注册时间表和用户表做迪卡尔积，再通过城市和时间这两个条件把满足条件的用户筛选出来

select user_name,tb_user.user_city,user_createdat
from tb_user
join (select user_city,max(user_createdat)m
      from tb_user
      group by user_city
)t
on tb_user.user_city = t.user_city and tb_user.user_createdat = t.m;

7.查询所有作者及其blog信息

select user_name,blog_title
from tb_user
join tb_blog
on user_id = blog_user_id;

8.查询所有作者及其blog信息，显示时，将一个作者写的所有博客标题放到一行显示

select user_name,group_concat(blog_title)
from tb_user
join tb_blog
on user_id = blog_user_id
group by user_name;

9.查询所有用户及其写的blog

select user_name,blog_title
from tb_user
left join tb_blog
on user_id = blog_user_id;

10.查询所有的博客及其标签信息

step1. 从tb_blog_tag找有标签的博客id以及他们的标签id

select rel_blog_id,rel_tag_id
from tb_blog_tag

step2. 将step1产生的结果集与标签表进行联合查询，找到标签id对应的具体标签内容

select rel_blog_id, tag_content
from tb_tag
join (
      select rel_blog_id,rel_tag_id
      from tb_blog_tag
     )
on tag_id = rel_tag_id

step3.将一篇博客的多个标签放到一行显示

select rel_blog_id, group_concat(tag_content)
from tb_tag
join (
      select rel_blog_id,rel_tag_id
      from tb_blog_tag
     )t
on tag_id = rel_tag_id
group by rel_blog_id

step4.将博客表tb_blog与step3产生的结果集进行联合查询，找到博客的具体信息

select blog_id,blog_title,tc
from tb_blog
left join (
    select rel_blog_id, group_concat(tag_content)tc
    from tb_tag
    join (
         select rel_blog_id,rel_tag_id
         from tb_blog_tag
         )t
    on tag_id = rel_tag_id
    group by rel_blog_id
    )t1
on blog_id = rel_blog_id;

11.查询所有的博客及其标签信息和作者信息

这个就是在10题的基础上再和tb_user做一次笛卡儿积

select user_name,user_avatar,blog_id,blog_title,tc

from tb_user

join (
     select blog_id,blog_title,tc,blog_user_id
     from tb_blog
     left join (
       select rel_blog_id, group_concat(tag_content)tc
       from tb_tag
       join (
           select rel_blog_id,rel_tag_id
           from tb_blog_tag
          )t
       on tag_id = rel_tag_id
       group by rel_blog_id
         )t1
     on blog_id = rel_blog_id
     )t2
on user_id = blog_user_id;