MYSQL开发- （1）

m0_59817723

324人浏览 · 2026-01-07 23:32:40

m0_59817723 · 2026-01-07 23:32:40 发布

use mydb2

--1:用户表，存储博主和评论者信息

create table users(

id int primary key auto_increment comment '用户ID,主键',

username varchar(50) not null unique comment '用户名，唯一',

email varchar(100) not null unique comment '邮箱，唯一',

password_hash varchar(225) not null comment '加密后的密码',

avatar_url varchar(255) default '' comment '头像图片链接',

created_at TIMESTAMP default CURRENT_TIMESTAMP comment '创建时间'

)comment '用户表'

--2:文章表，存储博客文章

create table posts(

id int primary key auto_increment comment '文章ID,主键',

user_id int not null comment '作者ID,关联users.id',

title varchar(200) not null comment '文章标题',

content TEXT not null comment '文章正文',

summary varchar(500) comment '文章摘要',

view_count int default 0 comment '阅读数',

status ENUM('draft','published') default 'draft' comment '状态:草稿,已发布',

created_at TIMESTAMP default current_timestamp comment '创建时间',

updated_at TIMESTAMP default current_timestamp on update current_timestamp comment '最后更新时间',

FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ---键约束：用户删除则文章级联删除

) comment '文章表'

--3：评论表：存储对文章的评论

create table comments(

id int primary key auto_increment comment '评论ID,主键',

post_id int not null comment '所属文章ID,关联posts.id',

user_id int not null comment '评论者ID,关联users.id',

content TEXT not null comment '评论内容',

parent_id int default null comment '父评论ID(用于回复功能）,自关联',

created_at TIMESTAMP default current_TIMESTAMP COMMENT '创建时间',

foreign key(user_id) references posts(id) on delete cascade, -- 文章删除则评论级联删除

foreign key(user_id) references users(id) on delete cascade -- 用户删除则评论级联删除

)comment '评论表'

-- 1. 插入模拟数据

INSERT INTO users (username, email, password_hash) VALUES

('小明', 'xiaoming@example.com', 'hash_of_password_123'),

('技术博主', 'tech@example.com', 'hash_of_password_456');

INSERT INTO posts (user_id, title, content, status) VALUES

(1, '我的第一篇博客', '今天天气真好，我开始写博客了...', 'published'),

(2, 'MySQL入门指南', '关系型数据库是Web开发的基石...', 'published'),

(2, '未完成的草稿', '这是一个还在构思中的文章...', 'draft');

INSERT INTO comments (post_id, user_id, content) VALUES

(1, 2, '欢迎加入博客圈！'),

(2, 1, '写得非常清晰，对我帮助很大！'),

(2, 2, '谢谢支持，欢迎持续关注。');

知识点：

1：ON DELETE CASCADE -

1.1：是外键约束的一个选项（约束动作），它指定了当父表中的记录被删除时，子表中对应的记录应该如何处理。

1.2：约束动作是当试图删除（delete) 或更新(update)父表中的记录时，为了保持数据完整性，可以定义子表中的记录如何响应。

1.3： on delete cascade 是在父表中删除一条记录时，数据库会自动删除子表中所有与之关联（即外键值匹配）的记录。这是一种级联删除操作。

2：其他的约束方式：

RESTRICT （或 NO ACTION）：拒绝删除父表中的记录。如果子表中存在关联记录，则删除操作会被中止。这是默认行为（如果未指定ON DELETE的话）。

SET NULL：将子表中对应记录的外键字段设置为NULL。这要求该外键字段允许为NULL。

SET DEFAULT：将子表中对应记录的外键字段设置为其默认值。这要求该外键字段有默认值定义。

二：基础查询

三：核心查询

SELECT 
    p.id,
    p.title,
    p.summary,
    p.view_count,
    p.created_at,
    u.username AS author_name, -- 通过JOIN获取作者名
    u.avatar_url AS author_avatar,
    COUNT(c.id) AS comment_count -- 聚合函数统计评论数
FROM posts p
JOIN users u ON p.user_id = u.id -- 关联用户表获取作者信息
LEFT JOIN comments c ON p.id = c.post_id -- LEFT JOIN很重要，因为文章可能没有评论
WHERE p.status = 'published'
GROUP BY p.id -- 按文章分组，以便COUNT生效
ORDER BY p.created_at DESC;

关键点：

使用 JOIN 连接 posts 和 users 表。
使用 LEFT JOIN 连接 comments 表，确保即使评论数为0的文章也会被列出。
使用 GROUP BY 和 COUNT() 聚合函数计算每篇文章的评论数。

--- 练习一：获取文章列表，并附带作者信息和评论数量

--- 1:文章ID,文章标题，文章摘要，文章阅读数，文章创建时间，作者名，统计评论数，

--- 2：条件；发布状态,根据创建时间越晚越优先

select p.id ,

p.title ,

p.content ,

p.view_count ,

p.created_at ,

u.username ,

count(c.id) as comment_count from posts p

join users u on p.user_id = u.id

left join comments c on p.id = c.post_id

where p.status = 'published'

group by p.id

order by created_at desc