MySQL多表查询与约束

一、为什么需要多表？

在真实项目里，数据不会只放在一张表里，比如一个简单博客系统可能有：

• users 表：存用户。
• articles 表：存文章。
• comments 表：存评论。

我们往往需要同时用到多张表的数据，比如：

• 查询每篇文章的作者名字；
• 查询某个用户发过哪些文章；
• 查询文章及其评论内容。

要解决这些问题，就需要多表查询（JOIN） 和 约束（Constraint）。

---

二、准备两张表：用户和文章

继续使用 demo 数据库，我们再建一张 articles 表：

USE demo;

CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,               -- 作者 id，对应 users.id
    title VARCHAR(200) NOT NULL,        -- 标题
    content TEXT,                       -- 正文
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

暂时先不加外键约束，先理解多表查询的用法，再回头补充约束。

插入一些测试数据：

INSERT INTO users (name, email, age) VALUES
('张三', 'zhangsan@test.com', 20),
('李四', 'lisi@test.com', 22);

INSERT INTO articles (user_id, title, content) VALUES
(1, '张三的第一篇文章', '内容A'),
(1, '张三的第二篇文章', '内容B'),
(2, '李四的第一篇文章', '内容C');

---

三、内连接（INNER JOIN）：最常用的多表查询

3.1 查询文章及其作者姓名

SELECT
    a.id,
    a.title,
    u.name AS author_name
FROM articles AS a
INNER JOIN users AS u
    ON a.user_id = u.id;

解释：

• FROM articles AS a：给 articles 取别名 a，后面写起来更简洁。
• INNER JOIN users AS u ON a.user_id = u.id：
  • 只取两张表中 匹配得上的那部分行。
  • 这里的匹配条件是：文章里的 user_id 等于用户表里的 id。

3.2 带条件的多表查询

-- 查询“张三”发过的文章
SELECT
    a.id,
    a.title,
    u.name AS author_name
FROM articles AS a
JOIN users AS u ON a.user_id = u.id
WHERE u.name = '张三';

说明：INNER JOIN 可以省略写成 JOIN，默认就是内连接。

---

四、左连接（LEFT JOIN）：保留左表的全部

4.1 场景：哪怕没有文章，也要把用户查出来

假设有一个用户目前还没有发文章，如果我们想看“所有用户及其文章数”，就要用 LEFT JOIN。

SELECT
    u.id,
    u.name,
    a.title
FROM users AS u
LEFT JOIN articles AS a
    ON u.id = a.user_id;

特点：

• 左表是 users，所有用户都会显示出来；
• 对于还没有文章的用户，a.title 会是 NULL。

4.2 统计每个用户发了几篇文章

SELECT
    u.id,
    u.name,
    COUNT(a.id) AS article_count
FROM users AS u
LEFT JOIN articles AS a
    ON u.id = a.user_id
GROUP BY u.id, u.name;

说明：

• COUNT(a.id)：统计每个用户匹配到的文章行数。
• 即使某个用户一篇文章都没有，LEFT JOIN 也会保留这行，只是文章相关列为 NULL，COUNT(a.id) 得到 0。

---

五、约束（Constraint）：保证数据“靠谱”

5.1 常见约束类型

• PRIMARY KEY：主键，唯一标识一行记录。
• UNIQUE：唯一约束，列的值不能重复。
• NOT NULL：不能为空。
• DEFAULT：默认值。
• FOREIGN KEY：外键约束，用来保证表与表之间的关联关系正确。

5.2 外键约束（FOREIGN KEY）概念

以 articles.user_id 为例：

• 我们希望：articles.user_id 必须是 users.id 中 已经存在的某个值；
• 避免插入一篇“作者不存在”的文章。

这时候就可以加一个外键约束：

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_articles_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
);

效果：

• 当你往 articles 插入数据时，如果 user_id 在 users.id 里不存在，就会报错。
• 保证了“文章一定属于一个真实存在的用户”。

提示：在很多互联网项目里，出于性能和迁移灵活性考虑，有些团队会不在数据库层加外键，只在业务代码里控制。作为初学者，先理解外键的概念及作用就好。

---

六、常用聚合函数与分组（GROUP BY）

6.1 常见聚合函数

• COUNT(*)：统计行数。
• SUM(col)：求和。
• AVG(col)：平均值。
• MAX(col)：最大值。
• MIN(col)：最小值。

6.2 按用户统计文章数

SELECT
    u.id,
    u.name,
    COUNT(a.id) AS article_count
FROM users AS u
LEFT JOIN articles AS a
    ON u.id = a.user_id
GROUP BY u.id, u.name
ORDER BY article_count DESC;

解释：

• GROUP BY u.id, u.name：按用户分组，每个用户一行。
• COUNT(a.id)：对每个用户单独统计 ta 关联到的文章数量。

---

七、练习题（按顺序做一遍）

在 demo 数据库中，基于 users 和 articles 表，尝试写出下面的 SQL：

1. 查询所有文章的标题和作者名字（使用 INNER JOIN）。
2. 查询“张三”写过的所有文章标题。
3. 查询所有用户及他们发的文章数，按文章数从多到少排序（LEFT JOIN + GROUP BY + ORDER BY）。
4. 为 articles.user_id 加上外键约束，保证所有文章都有合法作者（可以新建一张带外键的表练习）。

如果这些你都能独立写出来，就说明你已经掌握了：

• 最常用的多表查询（INNER JOIN / LEFT JOIN）；
• 聚合统计（COUNT + GROUP BY）；
• 约束的基本概念（尤其是主键、唯一键、外键）。

可以继续看下一篇：索引、事务与常见问题，了解如何让查询更快、数据更安全。+