人工智能 —— 博客服务器

 

编程语言：python
框架：tornado
数据库：mysql

 

一、数据库

1、数据库设计

用户表	用户ID	用户名	用户密码	用户头像	创建时间	更新时间
user_id	user_id	user_name	user_password	user_avatar	user_createdat	user_updatedat
博客表	博客ID	博客名	博客标题	博客正文	创建时间	更新时间
tb_blog	blog_id	blog_user_id	blog_title	blog_content	blog_createdat	blog_updatedat
评论内容	评论ID	博主	评论者	评论正文	创建时间	更新时间
tb_comment	comment_id	comment_blog_id	comment_user_id	comment_content	comment_createdat	comment_updatedat
标签表	标签ID	标签内容
tb_tag	tag_id	tag_content
博客标签表	博客标签ID	关联博客ID	关联标签ID
tb_blog_tag	blog_tag_id	rel_blog_id	rel_tag_id

2、数据库创建

        创建blog_db数据库：

create database if not exists blog_db default character set = utf8;

        查看数据库创建：
                help create database

                        help说明：
                                {}多选一
                                []可以忽略
                                | 多选

        注：
            查看数据库：show databases;
            删除数据库：drop blog_db; (help drop database)

3、数据表创建

        进入数据库：

use blog_db;

        查看标准数据表创建：
                help create table

a、用户表tb_user
        说明：
                user_id：主键索引，自增长
                user_name：位置索引
                user_updatedat：级联更新动作
        命令：

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

b、博客表tb_blog
        说明：
                blog_id：主键索引，自增长
                blog_user_id：外键参考（tb_user(user_id)），数据级联更新（用户表的更新和删除）
                blog_updatedat：级联更新动作
        命令：

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

c、博客标签表tb_blog_tag
        说明：
                blog_tag_id：主键索引，自增长
                rel_blog_id：外键参考（tb_blog(blog_id)），数据级联更新（用户表的更新和删除）
                rel_tag_id：外键参考（tb_tag(tag_id)），数据级联更新（用户表的更新和删除）
        命令：

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

d、标签表tb_tag
        说明：
                tag_id：主键索引，自增长
        命令：

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

e、评论内容tb_comment
        说明：
                comment_id：主键索引，自增长
                comment_blog_id：外键参考（tb_blog(blog_id)），数据级联更新（用户表的更新和删除）
                comment_user_id：外键参考（tb_user(user_id)），数据级联更新（用户表的更新和删除）
                comment_updatedat：级联更新动作
        命令：

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

4、数据库插入数据

        向用户表tb_user插入用户信息
                insert into
                tb_user(user_name,user_password,user_city)
                values ('abc','123','beijing')

        用户表中的用户至少来自３个城市
        每个城市至少有３名用户
        请不要使用批量插入

insert into tb_user(user_name,user_password,user_city) values ('bbc','123','shanghai');

insert into tb_user(user_name,user_password,user_city) values ('ccc','123','beijing');

insert into tb_user(user_name,user_password,user_city) values ('tom','123','guangzhou');

5、数据查询：

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-8-3 15:50:00至2018-8-3 15:53:00之间注册的所有用户信息

select user_name,user_city,user_createdat
from tb_user
where user_createdat > '2018-08-03 15:50:00' and user_createdat < '2018-08-03 15:53:00'

select user_name,user_city,user_createdat
from tb_user
where user_createdat between '2018-08-03 15:50:00' and '2018-08-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、从用户表中查询最晚注册用户的信息．

错误的写法！使用聚合函数但没有用group_by
select user_name,max(user_createdat)
from tb_user

正确的写法：
select user_name,user_createdat
from tb_user
where user_createdat = (select max(user_createdat) from tb_user)

6、从用户表中查询每个城市的最晚注册用户的信息

多表联合查询
1.内联接查询inner join,mysql可以简写为join
2.外联接查询
左外联接查询left outer join,mysql可以简写为left join
右外联接查询right outer join,mysql可以简写为right join

7、每个城市的最晚注册用户信息

用户表和每个城市的最晚注册时间做迪卡尔积

select user_name,tb_user.user_city,user_createdat,m
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

8、查询所有blog及其作者信息．（blog_title,user_name）

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

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

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

10、查询所有用户及其写的blog(user_name,blog_title)
select user_name,blog_title
from tb_user
left join tb_blog
on user_id = blog_user_id

11、查询所有的博客及其标签信息

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
     )t
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

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

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

13、查询所有的博客及其标签信息，作者信息和评论条数

step1.去评论表中tb_comment中查询每一条博客的评论条数

select comment_blog_id,count(*)
from tb_comment
group by comment_blog_id

step2. 将上题答案与step1进行联合查询

select user_name,user_avatar,blog_title,blog_content,tc,c
from (
        select comment_blog_id,count(*)c
        from tb_comment
        group by comment_blog_id
  )t3
right join (
        select user_name,user_avatar,blog_id,blog_title,blog_content,tc
        from tb_user
        join (
                select blog_id,blog_title,blog_content,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
    )t4
on comment_blog_id = blog_id

二、利用pymysql操作数据库的步骤

step1. 建立与数据库的联接
step2. 如果step1成功，利用联接获得一个游标(Cursor)
step3. 利用游标发送SQL语句，操作数据库
step4. 如果有需要，利用游标获取数据库的返回结果集

三、参考

参考源码：https://github.com/hilqiqi0/AI/tree/master/3.Web/tornado/sixth

四、最终版

优化（封装和单例）和异步处理（ajax）

参考源码：https://github.com/hilqiqi0/AI/tree/master/3.Web/tornado/eighth

五、附录：学习参考

        人工智能 —— 目录汇总