数据库操作

创建blog数据库并且创建articles，category,comments,tags,users表(tips:首先链接mysql,如果不会，请看文章-4文章4)

create database blog;
use blog;
CREATE TABLE `articles` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '所属用户',
  `category_id` int(11) NOT NULL DEFAULT '0' COMMENT '分类id',
  `tag_id` int(11) NOT NULL DEFAULT '0' COMMENT '标签id',
  `title` varchar(255) NOT NULL DEFAULT '' COMMENT 'title',
  `introduction` varchar(255) NOT NULL,
  `content` varchar(5000) NOT NULL DEFAULT '' COMMENT '内容',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `read_num` int(11) NOT NULL DEFAULT '0',
  `articles_img` varchar(255) NOT NULL DEFAULT '',
  `keyword` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `Iuser_id` (`user_id`),
  KEY `Icategory_id` (`category_id`),
  KEY `Itag_id` (`tag_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `category` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL DEFAULT '' COMMENT '类型name',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `articles_num` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `Iuser_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `comments` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `articles_id` int(11) NOT NULL COMMENT '文章id',
  `p_id` int(11) NOT NULL DEFAULT '0' COMMENT '父级评论(0为第一级)',
  `content` varchar(255) NOT NULL DEFAULT '',
  `user_id` int(11) NOT NULL DEFAULT '0',
  `username` varchar(255) NOT NULL DEFAULT '',
  `email` varchar(255) NOT NULL DEFAULT '',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `Iarticles_id` (`articles_id`),
  KEY `Ip_id` (`p_id`),
  KEY `Iuser_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `tags` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `name` varchar(124) DEFAULT '' COMMENT '标签名字',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `articles_num` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `Iuser_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL DEFAULT '',
  `password` varchar(124) NOT NULL DEFAULT '',
  `sex` enum('1','2') NOT NULL DEFAULT '1' COMMENT '1男2女',
  `header_img` varchar(124) DEFAULT NULL,
  `introduction` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8mb4;

上一篇-使用nuxt,express,mysql,nginx创建个人博客系列-04
下一篇-使用nuxt,express,mysql,nginx创建个人博客系列-06