欢迎来到我们史诗级教程的最终章！

在上一章，我们成功地构建了博客系统的数据库骨架。现在，这个结构精良的数据库正静静地等待着我们去使用它。

在这一章，我们将扮演“应用程序后端开发者”的角色。我们将模拟博客系统的各种核心功能，并为每一个功能编写对应的 SQL 语句。这将是一个绝佳的机会，来综合运用我们前面学到的 INSERT, SELECT, JOIN, CTE 等所有知识。

Let’s make our database alive!

准备工作：插入一些初始数据

为了让我们的查询能够返回结果，我们先手动插入一些模拟数据。

-- 切换到我们的 schema
SET 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.');

功能 1：用户注册

这是一个简单的 INSERT 操作。在真实应用中，password_hash 需要由后端程序计算得出。

INSERT INTO users (username, email, password_hash)
VALUES ('new_user', 'new@example.com', 'hash_of_new_password');

功能 2：发表一篇文章（带标签）

这是一个事务性操作，因为它至少需要两步：

在 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_id
INSERT INTO post_tags (post_id, tag_id)
SELECT 4, tag_id FROM tags WHERE tag_name IN ('SQL', 'PostgreSQL');

COMMIT;

功能 3：查询首页文章列表（含作者、标签、评论数）

这是最复杂、也最常见的查询之一。我们需要在一个查询中，展示出文章列表，并附带上作者名、该文章的所有标签，以及该文章的评论总数。

这里，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_count
FROM
    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_id
LEFT 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_id
GROUP BY
    p.post_id, u.username -- 按文章ID和作者名进行分组
ORDER BY
    p.created_at DESC; -- 按创建时间降序排列

这个查询综合运用了多表 JOIN、聚合函数 (ARRAY_AGG, COUNT) 和 GROUP BY，是展示列表页的黄金范例。

功能 4：查询单篇文章页面（含作者、标签、所有评论）

当用户点进一篇文章的详情页时，我们需要展示文章的所有信息，以及它下面的所有评论列表。

这个需求可以通过两个独立的查询来完成，这通常比一个极其复杂的 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 tags
FROM
    posts AS p
JOIN
    users AS u ON p.author_id = u.user_id
LEFT JOIN
    post_tags AS pt ON p.post_id = pt.post_id
LEFT JOIN
    tags AS t ON pt.tag_id = t.tag_id
WHERE
    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_author
FROM
    comments AS c
JOIN
    users AS u ON c.author_id = u.user_id
WHERE
    c.post_id = 1 -- 同样，筛选 post_id 为 1 的评论
ORDER BY
    c.created_at ASC; -- 评论按时间升序排列

在应用程序中，我们会先执行第一个查询，渲染文章主体；然后执行第二个查询，遍历结果来渲染评论列表。

功能 5：按标签查询文章

当用户点击一个标签时，我们需要列出所有包含该标签的文章。

SELECT
    p.post_id,
    p.title,
    u.username AS author_name
FROM
    posts AS p
JOIN
    users AS u ON p.author_id = u.user_id
JOIN
    post_tags AS pt ON p.post_id = pt.post_id
JOIN
    tags AS t ON pt.tag_id = t.tag_id
WHERE
    t.tag_name = 'SQL'; -- 筛选标签名为 'SQL' 的文章

教程终章 · 感言

恭喜你，坚持到了最后！你已经完成了整个 PostgreSQL 的学习之旅！

我们从最基础的 SELECT 开始，一路走来，学习了表的创建、数据的增删改查、复杂的 JOIN 和子查询、强大的窗口函数和聚合、严格的约束和事务、安全的角色管理、高效的索引和备份，最后，我们亲手设计并实现了一个完整的博客系统数据库。

你现在所掌握的知识，已经远远超出了一个“初学者”的范畴。你已经拥有了作为一名后端开发者、数据分析师或数据库管理员所需要的坚实基础。

数据库的世界博大精深，我们的学习之旅永无止境。但你已经拥有了那张最关键的地图和一套最精良的工具。接下来，请勇敢地去探索更广阔的世界吧：

深入研究性能调优，理解 EXPLAIN 的每一个细节。
探索 PostgreSQL 的高级扩展，如 PostGIS, TimescaleDB。
学习数据库集群、复制和高可用方案。
将你学到的知识应用到你自己的项目中去！

纸上得来终觉浅，绝知此事要躬行。

现在，去创造吧！用你手中的 PostgreSQL 利器，去构建下一个伟大的应用！

感谢你的坚持，我们江湖再见！

顾乔芝士网

持续更新的前后端开发技术栈

第 28 章:核心功能 SQL 查询 - PostgreSQL入门