如何在mysql中设计小型论坛板块_mysql论坛板块项目实战

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  email VARCHAR(100),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE topics (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  content TEXT NOT NULL,
  user_id INT,
  category_id INT,
  view_count INT DEFAULT 0,
  reply_count INT DEFAULT 0,
  last_reply_time DATETIME DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
);

CREATE TABLE replies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  content TEXT NOT NULL,
  user_id INT,
  topic_id INT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (topic_id) REFERENCES topics(id) ON DELETE CASCADE
);

SELECT t.id, t.title, u.username, t.reply_count, t.last_reply_time 
FROM topics t 
JOIN users u ON t.user_id = u.id 
WHERE t.category_id = 1 
ORDER BY t.last_reply_time DESC;

SELECT r.content, u.username, r.created_at 
FROM replies r 
JOIN users u ON r.user_id = u.id 
WHERE r.topic_id = 100 
ORDER BY r.created_at ASC;

SELECT c.name, COUNT(t.id) as topic_count 
FROM categories c 
LEFT JOIN topics t ON c.id = t.category_id 
GROUP BY c.id;