SQL 数据库设计与数据填充：文章、类别、标签、用户、评论和回复

本示例展示了如何使用 SQL 语句创建文章、类别、标签、用户、评论和回复等表，并填充示例数据，以及如何查询文章详情信息，包括文章基本信息、类别、标签、评论和回复等。

1. 创建类别表

CREATE TABLE category (
  id int NOT NULL COMMENT '类别ID',
  name varchar(50) NOT NULL COMMENT '类别名称',
  PRIMARY KEY(id)
) ENGINE=InnoDB COMMENT='文章类别表';

-- 添加类别表数据
INSERT INTO category (id, name) VALUES
(1, '科技'),
(2, '娱乐'),
(3, '新闻');

2. 创建标签表

CREATE TABLE tag (
  id int NOT NULL COMMENT '标签ID',
  name varchar(50) NOT NULL COMMENT '标签名称',
  PRIMARY KEY (id)
) ENGINE=InnoDB COMMENT='文章标签表';

-- 添加标签数据
INSERT INTO tag (id, name) VALUES
(1, '科学'),
(2, '电影'),
(3, '体育');

3. 创建用户表

CREATE TABLE user (
  id int NOT NULL COMMENT '用户ID',
  username varchar(50) NOT NULL COMMENT '用户名',
  password varchar(50) NOT NULL COMMENT '密码',
  PRIMARY KEY (id)
) ENGINE=InnoDB COMMENT='用户表';

-- 添加用户信息
INSERT INTO user (id, username, password) VALUES
(1, '小明', '123456'),
(2, '小红', '654321');

4. 创建文章表

CREATE TABLE article (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT '文章ID',
  title varchar(100) NOT NULL COMMENT '文章标题',
  content text NOT NULL COMMENT '文章内容',
  category_id int(11) NOT NULL COMMENT '文章类别ID',
  user_id int(11) NOT NULL COMMENT '用户ID',
  PRIMARY KEY (id),
  KEY fk_article_category_id (category_id),
  KEY fk_article_user_id (user_id),
  CONSTRAINT fk_article_category_id FOREIGN KEY (category_id) REFERENCES category (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_article_user_id FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章表';

-- 添加文章信息
INSERT INTO article (id, title, content, category_id, user_id) VALUES
(1, '科技新闻1', '这是一篇科技新闻的内容', 1, 1),
(2, '科技新闻2', '这是一篇科技新闻的内容', 1, 1),
(3, '娱乐新闻1', '这是一篇娱乐新闻的内容', 2, 2),
(4, '娱乐新闻2', '这是一篇娱乐新闻的内容', 2, 2),
(5, '新闻1', '这是一篇新闻的内容', 3, 1),
(6, '新闻2', '这是一篇新闻的内容', 3, 2);

5. 创建文章标签关联表

CREATE TABLE article_tag (
  article_id int NOT NULL COMMENT '文章ID',
  tag_id int NOT NULL COMMENT '标签ID',
  PRIMARY KEY (article_id,tag_id),
  KEY fk_article_tag_tag_id (tag_id),
  CONSTRAINT fk_article_tag_article_id foreign key (article_id) references article (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_article_tag_tag_id FOREIGN KEY (tag_id) REFERENCES tag (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB COMMENT='文章标签关联表';
-- 添加文章关联表数据
INSERT INTO article_tag (article_id, tag_id) VALUES
(1, 1),
(1, 2),
(2, 1),
(3, 2),
(4, 2),
(4, 3),
(5, 1),
(6, 3);

6. 创建文章评论表

CREATE TABLE comment (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT '评论ID',
  article_id int(11) NOT NULL COMMENT '文章ID',
  user_id int(11) NOT NULL COMMENT '用户ID',
  content text NOT NULL COMMENT '评论内容',
  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (id),
  KEY fk_comment_article_id (article_id),
  KEY fk_comment_user_id (user_id),
  CONSTRAINT fk_comment_article_id FOREIGN KEY (article_id) REFERENCES article (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_comment_user_id FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB COMMENT='文章评论表';
-- 添加文章评论数据
INSERT INTO comment (id, article_id, user_id, content, created_at) VALUES
(1, 1, 2, '这是一条评论的内容', '2021-01-01 00:00:00'),
(2, 1, 1, '这是一条评论的内容', '2021-01-02 00:00:00'),
(3, 2, 2, '这是一条评论的内容', '2021-01-03 00:00:00'),
(4, 3, 1, '这是一条评论的内容', '2021-01-04 00:00:00'),
(5, 4, 2, '这是一条评论的内容', '2021-01-05 00:00:00');

7. 创建评论回复表

CREATE TABLE reply (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT '回复ID',
  comment_id int(11) NOT NULL COMMENT '评论ID',
  user_id int(11) NOT NULL COMMENT '用户ID',
  content text NOT NULL COMMENT '回复内容',
  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (id),
  KEY fk_reply_comment_id (comment_id),
  KEY fk_reply_user_id (user_id),
  CONSTRAINT fk_reply_comment_id FOREIGN KEY (comment_id) REFERENCES comment (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_reply_user_id FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB COMMENT='评论回复表';
-- 添加评论回复数据
INSERT INTO reply (id, comment_id, user_id, content, created_at) VALUES
(1, 1, 2, '这是一条回复的内容', '2021-01-02 01:00:00'),
(2, 1, 1, '这是一条回复的内容', '2021-01-02 02:00:00'),
(3, 3, 2, '这是一条回复的内容', '2021-01-03 01:00:00'),
(4, 5, 1, '这是一条回复的内容', '2021-01-05 01:00:00');

8. 查询文章详情

SELECT 
  article.id AS article_id,
  article.title AS article_title,
  article.content AS article_content,
  category.name AS category_name,
  GROUP_CONCAT(tag.name) AS tag_names,
  user.username AS user_name,
  comment.id AS comment_id,
  comment.content AS comment_content,
  comment.created_at AS comment_created_at,
  reply.id AS reply_id,
  reply.content AS reply_content,
  reply.created_at AS reply_created_at
FROM article
LEFT JOIN category ON article.category_id = category.id
LEFT JOIN article_tag ON article.id = article_tag.article_id
LEFT JOIN tag ON article_tag.tag_id = tag.id
LEFT JOIN user ON article.user_id = user.id
LEFT JOIN comment ON article.id = comment.article_id
LEFT JOIN reply ON comment.id = reply.comment_id
WHERE article.id = 1
GROUP BY comment.id, reply.id
ORDER BY comment.id, reply.id;

上述代码展示了如何创建相关数据库表，填充示例数据，以及如何查询文章详细信息。您可以根据自己的需求修改代码，并进行进一步的数据库操作。