-- 1、创建库

drop database if exists blog; -- 直接删除数据库，不提醒

create database blog; -- 创建数据库

use blog; -- 选择数据库

-- 2、创建表

--

-- table structure for table `bll_article` 文章

--

drop table if exists `bll_article`;

create table `bll_article` (

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',

  `createtime` datetime default current_timestamp comment '创建时间',

  `creator` varchar(60) not null comment '创建人',

  `typeid` bigint(20) default null comment '文章所属类型id',

  `title` varchar(50) default null comment '文章标题',

  `content` varchar(10000) default null comment '文章内容',

  `comcount` int(11) default '0' comment '文章被评论条数',

  `readcount` int(11) default '0' comment '文章被阅读条数',

  `suggestcount` int(11) default '0' comment '文章被点赞次数',

  primary key (`id`)

) engine=innodb default charset=utf8 comment='用户发表的文章';

--

-- table structure for table `bll_articletype` 文章类别

--

drop table if exists `bll_articletype`;

create table `bll_articletype` (

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',

  `createtime` datetime default current_timestamp comment '创建时间',

  `creator` varchar(60) not null comment '创建人',

  `typename` varchar(50) default null comment '文章类型名称',

  `description` varchar(50) default null comment '描述',

  primary key (`id`)

) engine=innodb default charset=utf8 comment='文章类型';

--

-- table structure for table `bll_commont` 评论

--

drop table if exists `bll_comment`;

create table `bll_comment` (

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',

  `createtime` datetime default current_timestamp comment '创建时间',

  `creator` varchar(60) not null comment '创建人',

  `articleid` bigint(20) default null comment '被评论的文章id',

  `comcontent` varchar(2000) default null comment '评论内容',

  `email` varchar(200) default null comment '评论人邮箱',

  primary key (`id`)

) engine=innodb default charset=utf8 comment='对文章的评论信息';

--

-- table structure for table `bll_leavemessage` 留言

--

drop table if exists `bll_leavemessage`;

create table `bll_leavemessage` (

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',

  `createtime` datetime default current_timestamp comment '创建时间',

  `creator` varchar(60) not null comment '创建人',

  `content` varchar(2000) default null comment '留言内容',

  `email` varchar(200) default null comment '留言人邮箱',

  primary key (`id`)

) engine=innodb default charset=utf8 comment='留言';

-- 3、创建触发器

-- (1)评论表增加一条记录，则文章表对应记录的评论数增加1

create trigger addcomcount

after insert on bll_comment

for each row

	update bll_article set comcount=comcount+1 where id=new.articleid;

-- (2)评论删除一条记录，则文章表对应记录的评论数减去1

create trigger pluscomcount

after delete on bll_comment

for each row

	update bll_article set comcount=comcount-1 where id=old.articleid;

-- 初始化数据

insert into bll_articletype(creator,typename,description) values ('tim','编程','编程技术、设计等');

insert into bll_articletype(creator,typename,description) values ('tim','感悟','人生感悟等');

insert into bll_articletype(creator,typename,description) values ('tim','摄影','拍摄的照片等');

insert into bll_articletype(creator,typename,description) values ('tim','英语','英语文章');