摘要：在这一章，我们将扮演“应用程序后端开发者”的角色。我们将模拟博客系统的各种核心功能，并为每一个功能编写对应的 SQL 语句。这将是一个绝佳的机会，来综合运用我们前面学到的 INSERT, SELECT, JOIN, CTE 等所有知识。
欢迎来到我们史诗级教程的最终章！
在上一章，我们成功地构建了博客系统的数据库骨架。现在，这个结构精良的数据库正静静地等待着我们去使用它。
在这一章，我们将扮演“应用程序后端开发者”的角色。我们将模拟博客系统的各种核心功能，并为每一个功能编写对应的 SQL 语句。这将是一个绝佳的机会，来综合运用我们前面学到的 INSERT, SELECT, JOIN, CTE 等所有知识。
Let’s make our database alive!
为了让我们的查询能够返回结果，我们先手动插入一些模拟数据。
-- 切换到我们的 schemaSET search_path TO blog, public;-- 插入用户 (密码应该是经过哈希处理的，这里为了演示简化)INSERT INTO users (username, email, password_hash) VALUES('alice', 'alice@example.com', 'hash_of_password1'),('bob', 'bob@example.com', 'hash_of_password2'),('charlie', 'charlie@example.com', 'hash_of_password3');-- 插入标签INSERT INTO tags (tag_name) VALUES('SQL'),('PostgreSQL'),('Web Development'),('Tutorial');-- 插入文章-- alice 发表了 2 篇文章INSERT INTO posts (author_id, title, content) VALUES(1, 'My First Post about SQL', 'This is a post about the basics of SQL...'),(1, 'Advanced PostgreSQL Features', 'Let''s talk about window functions and CTEs...');-- bob 发表了 1 篇文章INSERT INTO posts (author_id, title, content) VALUES(2, 'A Guide to Web Development', 'HTML, CSS, and JavaScript are the core...');-- 为文章打上标签 (建立多对多关系)-- 'My First Post about SQL' (post_id=1) 有 'SQL' 和 'Tutorial' 标签INSERT INTO post_tags (post_id, tag_id) VALUES (1, 1), (1, 4);-- 'Advanced PostgreSQL Features' (post_id=2) 有 'PostgreSQL', 'SQL', 'Tutorial' 标签INSERT INTO post_tags (post_id, tag_id) VALUES (2, 2), (2, 1), (2, 4);-- 'A Guide to Web Development' (post_id=3) 有 'Web Development' 和 'Tutorial' 标签INSERT INTO post_tags (post_id, tag_id) VALUES (3, 3), (3, 4);-- 插入评论-- bob 和 charlie 评论了 alice 的第一篇文章INSERT INTO comments (post_id, author_id, content) VALUES(1, 2, 'Great introduction!'),(1, 3, 'Thanks for sharing, very helpful.');-- alice 评论了 bob 的文章INSERT INTO comments (post_id, author_id, content) VALUES(3, 1, 'Nice overview of web dev.');这是一个简单的 INSERT 操作。在真实应用中，password_hash 需要由后端程序计算得出。
INSERT INTO users (username, email, password_hash)VALUES ('new_user', 'new@example.com', 'hash_of_new_password');在 posts 表中插入文章。在 post_tags 表中插入文章和标签的关联关系。我们需要保证这两步要么都成功，要么都失败。
BEGIN;-- 第一步：插入文章，并获取新生成的 post_id-- `RETURNING post_id` 是一个非常有用的技巧，可以立即返回新插入行的某个字段值INSERT INTO posts (author_id, title, content)VALUES (1, 'A New Post on Transactions', 'Transactions are very important...')RETURNING post_id;-- 假设上一步返回的 post_id 是 4-- 第二步：为新文章 (post_id=4) 添加标签 ('SQL', 'PostgreSQL')-- 我们需要先根据标签名查出 tag_idINSERT INTO post_tags (post_id, tag_id)SELECT 4, tag_id FROM tags WHERE tag_name IN ('SQL', 'PostgreSQL');COMMIT;这是最复杂、也最常见的查询之一。我们需要在一个查询中，展示出文章列表，并附带上作者名、该文章的所有标签，以及该文章的评论总数。
这里，LEFT JOIN 和 GROUP BY 将大显身手。
SELECT p.post_id, p.title, p.created_at, u.username AS author_name, -- 使用 ARRAY_AGG 和 DISTINCT 将该文章的所有标签名聚合成一个数组 ARRAY_AGG(DISTINCT t.tag_name) AS tags, -- 使用 COUNT 和 DISTINCT 统计评论数 COUNT(DISTINCT c.comment_id) AS comment_countFROM posts AS p-- JOIN 作者信息JOIN users AS u ON p.author_id = u.user_id-- LEFT JOIN 标签信息 (用 LEFT JOIN 是为了即使文章没有标签，也依然能显示出来)LEFT JOIN post_tags AS pt ON p.post_id = pt.post_idLEFT JOIN tags AS t ON pt.tag_id = t.tag_id-- LEFT JOIN 评论信息 (用 LEFT JOIN 是为了即使文章没有评论，评论数也为0)LEFT JOIN comments AS c ON p.post_id = c.post_idGROUP BY p.post_id, u.username -- 按文章ID和作者名进行分组ORDER BY p.created_at DESC; -- 按创建时间降序排列这个查询综合运用了多表 JOIN、聚合函数 (ARRAY_AGG, COUNT) 和 GROUP BY，是展示列表页的黄金范例。
当用户点进一篇文章的详情页时，我们需要展示文章的所有信息，以及它下面的所有评论列表。
这个需求可以通过两个独立的查询来完成，这通常比一个极其复杂的 JOIN 更高效、更清晰。
查询 1：获取文章本身和作者、标签信息
SELECT p.post_id, p.title, p.content, p.created_at, u.username AS author_name, ARRAY_AGG(DISTINCT t.tag_name) AS tagsFROM posts AS pJOIN users AS u ON p.author_id = u.user_idLEFT JOIN post_tags AS pt ON p.post_id = pt.post_idLEFT JOIN tags AS t ON pt.tag_id = t.tag_idWHERE p.post_id = 1 -- 假设我们正在查看 post_id 为 1 的文章GROUP BY p.post_id, u.username;查询 2：获取该文章的所有评论，以及评论者的名字
SELECT c.content, c.created_at, u.username AS comment_authorFROM comments AS cJOIN users AS u ON c.author_id = u.user_idWHERE c.post_id = 1 -- 同样，筛选 post_id 为 1 的评论ORDER BY c.created_at ASC; -- 评论按时间升序排列在应用程序中，我们会先执行第一个查询，渲染文章主体；然后执行第二个查询，遍历结果来渲染评论列表。
当用户点击一个标签时，我们需要列出所有包含该标签的文章。
SELECT p.post_id, p.title, u.username AS author_nameFROM posts AS pJOIN users AS u ON p.author_id = u.user_idJOIN post_tags AS pt ON p.post_id = pt.post_idJOIN tags AS t ON pt.tag_id = t.tag_idWHERE t.tag_name = 'SQL'; -- 筛选标签名为 'SQL' 的文章我们从最基础的 SELECT 开始，一路走来，学习了表的创建、数据的增删改查、复杂的 JOIN 和子查询、强大的窗口函数和聚合、严格的约束和事务、安全的角色管理、高效的索引和备份，最后，我们亲手设计并实现了一个完整的博客系统数据库。
你现在所掌握的知识，已经远远超出了一个“初学者”的范畴。你已经拥有了作为一名后端开发者、数据分析师或数据库管理员所需要的坚实基础。
数据库的世界博大精深，我们的学习之旅永无止境。但你已经拥有了那张最关键的地图和一套最精良的工具。接下来，请勇敢地去探索更广阔的世界吧：
深入研究性能调优，理解 EXPLAIN 的每一个细节。探索 PostgreSQL 的高级扩展，如 PostGIS, TimescaleDB。学习数据库集群、复制和高可用方案。将你学到的知识应用到你自己的项目中去！纸上得来终觉浅，绝知此事要躬行。
现在，去创造吧！用你手中的 PostgreSQL 利器，去构建下一个伟大的应用！
感谢你的坚持，我们江湖再见！
来源：linux运维菜
