Blog博客系统-数据库设计

Blog博客系统-数据库设计，最后有Sql语句

[toc]

01-创建数据库 blog

02-创建用户表 blog_auth

03-创建分类表 blog_tag

04-创建文章表 blog_article

05-创建评论表 blog_comment

06-创建外键约束 blog_article blog_comment

07-插入数据用于模拟

• 【7】插入语句blog_auth用户表
  

• 【7】插入语句blog_tag分类表
  

• 【7】插入语句blog_article文章表
  

• 【7】插入语句blog_comment评论表
  

08-查询语句

• 【8】查询语句-查询用户admin的所有发布文章
  

• 【08】查询语句-查询用户admin发布的所有评论
  

09-统计语句

• 【9】统计语句-统计用户admin发布评论总数
  

• 【9】统计语句-统计用户admin发布文章总数
  

10-排序语句

• 【10】排序语句-文章列表按照id倒序排列
  

11-删除语句

• 【11】删除语句-清空所有评论
  

• 【11】删除语句-删除所有未发布的文章
  

• 【11】删除语句-删除整个数据库
  

12-数据库E-R图

附录

--【1】创建 blog数据库
CREATE DATABASE blog CHARACTER SET utf8 COLLATE UTF8_GENERAL_CI;


--【2】创建用户表 blog_auth

CREATE TABLE `blog_auth` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT '' COMMENT '用户名',
  `password` varchar(50) DEFAULT '' COMMENT '密码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';


--【3】创建分类表 blog_tag

CREATE TABLE `blog_tag` (
  `id`    int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name`  varchar(100) DEFAULT '' COMMENT '标签名称',
  `state` tinyint(3) unsigned DEFAULT '1' COMMENT '状态 0为禁用、1为启用',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='分类表';

--【4】创建文章表 blog_article

CREATE TABLE `blog_article` (
  `id`      int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tag_id`  int(10) unsigned DEFAULT '0' COMMENT '分类id',
  `user_id` int(10) unsigned NOT NULL  DEFAULT 0 COMMENT '创建者id',
  `title` varchar(100) DEFAULT '' COMMENT '文章标题',
  `desc`  varchar(255) DEFAULT '' COMMENT '简述',
  `content` text COMMENT '内容',
  `cover_image_url` varchar(255) DEFAULT '' COMMENT '封面图片地址',
  `time` int(10) unsigned       DEFAULT '0' COMMENT '发布时间',  
  `state` tinyint(3) unsigned   DEFAULT '1' COMMENT '状态 1发布 0未发布',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='文章表';



--【5】创建评论表 blog_comment

CREATE TABLE `blog_comment` (
  `id`      int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL  DEFAULT 0 COMMENT '创建者id',
  `article_id` int(10) unsigned NOT NULL  DEFAULT 0 COMMENT '文章id',
  `comment`  varchar(255)               DEFAULT '' COMMENT '评论内容',
  `time`    int(10) unsigned            DEFAULT '0' COMMENT '发布时间',  
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='评论表';


--【6】创建外键约束
ALTER TABLE  blog_article ADD CONSTRAINT FK_ARTICLE_TAGID FOREIGN KEY(tag_id) REFERENCES blog_tag(id);
ALTER TABLE  blog_article ADD CONSTRAINT FK_ARTICLE_USERID FOREIGN KEY(user_id) REFERENCES blog_auth(id);

ALTER TABLE  blog_comment ADD CONSTRAINT FK_COMMENT_USERID FOREIGN KEY(user_id) REFERENCES blog_tag(id);
ALTER TABLE  blog_comment ADD CONSTRAINT FK_COMMENT_ARTICLEID FOREIGN KEY(article_id) REFERENCES blog_article(id);



--【7】插入数据 用于模拟


INSERT INTO `blog_auth` (`id`, `username`, `password`) VALUES (1, 'admin', 'e10adc3949ba59abbe56e057f20f883e');



INSERT INTO `blog_tag` (`id`, `name`, `state`) VALUES 
(1, 'HTML', 1),
(2, 'CSS', 1),
(3, 'JAVASCRIPT', 1),
(4, 'PHP', 1),
(5, 'PYTHON', 1),
(6, 'JAVA', 1),
(7, 'GOLAND', 1),
(8, 'C', 1),
(9, 'C++', 1),
(10, 'C#', 1),
(11, 'Visual Basic', 0),
(12, 'RUBY', 0);


INSERT INTO `blog_article` (`id`, `tag_id`, `user_id`,`title`, `desc`, `content`, `cover_image_url`, `time`, `state`) VALUES 
(1, 1, 1,'测试1','测试','测试1','https://cn.bing.com/sa/simg/favicon-2x.ico', '1616488930', 1),
(2, 1, 1,'测试2','测试','测试1','https://cn.bing.com/sa/simg/favicon-2x.ico', '1616488930', 1),
(3, 1, 1,'测试3','测试','测试1','https://cn.bing.com/sa/simg/favicon-2x.ico', '1616488930', 1),
(4, 1, 1,'测试4','测试','测试1','https://cn.bing.com/sa/simg/favicon-2x.ico', '1616488930', 1),
(5, 1, 1,'测试5','测试','测试1','https://cn.bing.com/sa/simg/favicon-2x.ico', '1616488930', 1),
(6, 1, 1,'测试6','测试','测试1','https://cn.bing.com/sa/simg/favicon-2x.ico', '1616488930', 1),
(7, 1, 1,'测试7','测试','测试1','https://cn.bing.com/sa/simg/favicon-2x.ico', '1616488930', 1),
(8, 1, 1,'测试8','测试','测试1','https://cn.bing.com/sa/simg/favicon-2x.ico', '1616488930', 1),
(9, 1, 1,'测试9','测试','测试1','https://cn.bing.com/sa/simg/favicon-2x.ico', '1616488930', 1),
(10, 1, 1,'测试10','测试','测试1','https://cn.bing.com/sa/simg/favicon-2x.ico', '1616488930', 1),
(11, 1, 1,'测试11','测试','测试1','https://cn.bing.com/sa/simg/favicon-2x.ico', '1616488930', 0),
(12, 1, 1,'测试12','测试','测试1','https://cn.bing.com/sa/simg/favicon-2x.ico', '1616488930',0);


INSERT INTO `blog_comment` (`id`, `user_id`,`article_id`, `comment`,`time`) VALUES 
(1, 1, 1,'奥里给', '1616488930'),
(2, 1, 1,'整不赖', '1616488930'),
(3, 1, 1,'猴赛雷', '1616488930'),
(4, 1, 1,'冲冲冲', '1616488930'),
(5, 1, 1,'干饭', '1616488930'),
(6, 1, 1,'挺好的', '1616488930'),
(7, 1, 1,'好漂亮阿', '1616488930'),
(8, 1, 1,'你明白我的意思不', '1616488930'),
(9, 1, 1,'阿伟死了，你选的嘛 偶像', '1616488930'),
(10, 1, 1,'2021年2月23号下午五点十五分钟，在这一分钟前', '1616488930'),
(11, 1, 1,'OJBK', '1616488930'),
(12, 1, 1,'欧克', '1616488930');


--【8】查询语句

-- [1] 查询用户admin的所有发布文章
select blog_article.* , blog_auth.username from blog_article 
    left join blog_auth 
    on blog_article.user_id = blog_auth.id 
    where blog_auth.username = 'admin' and blog_article.state = 1

-- [2] 查询用户admin发布的所有评论
SELECT blog_comment.id, blog_comment.comment, blog_comment.time, blog_article.title from blog_comment  
    LEFT JOIN blog_article
    ON blog_comment.article_id = blog_article.id 
    WHERE blog_comment.user_id IN (
        SELECT id FROM blog_auth WHERE username = 'admin'
    )

--【9】统计语句

-- 统计用户admin发布文章总数
select COUNT(*) AS "用户发布文章总数" from blog_auth  
    left join blog_article 
    on blog_article.user_id = blog_auth.id 
    where blog_auth.username = 'admin' and blog_article.state = 1

-- 统计用户admin发布评论总数
SELECT COUNT(*) AS "admin评论发布总数" from blog_comment  
    LEFT JOIN blog_article
    ON blog_comment.article_id = blog_article.id 
    WHERE blog_comment.user_id IN (
        SELECT id FROM blog_auth WHERE username = 'admin'
    )

--【10】排序语句

-- 文章列表按照id倒序排列
SELECT * FROM blog_article ORDER BY id DESC 


--【11】删除语句

-- 删除未发布的文章
DELETE FROM blog_article WHERE state = 0 

-- 清空整个评论表 
truncate table blog_comment;

-- 删除整个数据库
drop database blog

本作品采用《CC 协议》，转载必须注明作者和本文链接