blob: 53bd1874c0342ca752e2b1a9396738cc66a24205 [file] [log] [blame]
晓瑞223cae85b72025-05-30 01:09:40 +08001-- 使用反引号包裹保留字表名
Your Name695f6a62025-05-17 00:58:04 +08002CREATE TABLE `user` (
3 `user_id` INT PRIMARY KEY AUTO_INCREMENT,
4 `username` VARCHAR(255) NOT NULL UNIQUE,
5 `email` VARCHAR(255) NOT NULL UNIQUE,
6 `password` VARCHAR(255) NOT NULL,
7 `address` VARCHAR(255),
8 `role` VARCHAR(50) NOT NULL DEFAULT 'user',
9 `profile_pic` VARCHAR(255),
晓瑞2231b422362025-06-04 01:09:31 +080010 `registration_date` TIMESTAMP NOT NULL,
晓瑞223cae85b72025-05-30 01:09:40 +080011 `identification_number` VARCHAR(18),
Your Name695f6a62025-05-17 00:58:04 +080012 `avatar` VARCHAR(255),
晓瑞223cae85b72025-05-30 01:09:40 +080013 `isfollowed` BOOLEAN NOT NULL DEFAULT FALSE
晓瑞2231b422362025-06-04 01:09:31 +080014);
Your Name695f6a62025-05-17 00:58:04 +080015
晓瑞223cae85b72025-05-30 01:09:40 +080016-- 外键表同样使用反引号
晓瑞2231b422362025-06-04 01:09:31 +080017CREATE TABLE `user_follow` (
18 `follower_id` INT NOT NULL,
19 `followed_id` INT NOT NULL,
20 `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
21 PRIMARY KEY (`follower_id`, `followed_id`),
22 FOREIGN KEY (`follower_id`) REFERENCES `user`(`user_id`),
23 FOREIGN KEY (`followed_id`) REFERENCES `user`(`user_id`)
Your Name695f6a62025-05-17 00:58:04 +080024);
25
晓瑞223e046c3a2025-06-05 22:08:29 +080026-- 新增任务表
27CREATE TABLE `task` (
28 `task_id` INT AUTO_INCREMENT PRIMARY KEY,
29 `user_id` INT NOT NULL,
30 `type` VARCHAR(50) NOT NULL, -- "seed", "post", "comment"
31 `completed` BOOLEAN NOT NULL DEFAULT FALSE,
32 FOREIGN KEY (`user_id`) REFERENCES `user`(`user_id`)
33);
34
35-- 新增帖子表
36CREATE TABLE `post` (
37 `post_id` BIGINT AUTO_INCREMENT PRIMARY KEY,
38 `user_id` INT NOT NULL,
39 `title` VARCHAR(255) NOT NULL,
40 `content` TEXT NOT NULL,
41 `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
42 `views` INT DEFAULT 0,
43 FOREIGN KEY (`user_id`) REFERENCES `user`(`user_id`)
44);
45
46-- 新增评论表
47CREATE TABLE `comment` (
48 `comment_id` BIGINT AUTO_INCREMENT PRIMARY KEY,
49 `post_id` BIGINT NOT NULL,
50 `user_id` INT NOT NULL,
51 `content` TEXT NOT NULL,
52 `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
53 FOREIGN KEY (`post_id`) REFERENCES `post`(`post_id`),
54 FOREIGN KEY (`user_id`) REFERENCES `user`(`user_id`)
55);
56
晓瑞223cae85b72025-05-30 01:09:40 +080057-- 插入语句使用反引号包裹表名和列名
Your Name695f6a62025-05-17 00:58:04 +080058INSERT INTO `user` (
晓瑞223cae85b72025-05-30 01:09:40 +080059 `username`, `email`, `password`, `registration_date`, `identification_number`, `role`
Your Name695f6a62025-05-17 00:58:04 +080060) VALUES (
晓瑞2231b422362025-06-04 01:09:31 +080061 'admin', 'admin@example.com', 'admin123', CURRENT_TIMESTAMP, '87654321', 'admin'
晓瑞223cae85b72025-05-30 01:09:40 +080062 );