The Odin Project 数据库课程：SQL 与关系型数据库核心实战指南-创锋一号

The Odin Project 数据库课程：SQL 与关系型数据库核心实战指南

【免费下载链接】curriculumThe open curriculum for learning web development项目地址: https://gitcode.com/GitHub_Trending/cu/curriculum

导读

本文是开源课程仓库 curriculum（The Odin Project 开源 Web 开发课程）中「数据库与 SQL」一课的完整技术指南，围绕 databases_and_sql.md 展开。无论你未来使用 Rails 的 Active Record、Node.js 的 Prisma 还是直接手写 SQL，理解关系型数据库的表、主外键、CRUD、JOIN 与聚合查询，都是把数据问题问清楚的基础。读完本文，你将掌握从建库建表、增删改查到多表连接、分组统计与条件过滤的完整 SQL 能力，并能看懂课程仓库中 using_postgresql.md 等实战章节的底层原理。

为什么数据与 SQL 是 Web 开发的核心

数据是任何优秀 Web 应用的核心。对 SQL 的良好掌握，不仅能让你在使用对象关系映射（ORM，Object-Relational Mapper，如 Rails 中的 Active Record、Node.js 中的 Prisma）时理解其背后发生的一切，还能让你更有底气地向数据提出更复杂的问题。正如课程开篇所说——SQL 的本质就是向数据库提问，偶尔再往里面添加或修改一些东西。

简单场景下，你可能想列出所有在 12 月通过促销码FREESTUFF注册的用户；想按主题和创建时间排序展示当前用户的所有评论。复杂场景下，你可能想按数量和订单总金额，列出所有发往用户数超过 1000 人的州的订单；或者出于内部原因，分析哪些推广渠道带来的用户满足了"每个工作日阅读五篇文章"的参与标准。这些例子都涉及与数据库的交互，而它们都能用 SQL 表达。

幸运的是，SQL 是一门很小的语言——总共几十个单词，你日常反复使用的不过十几个。真正的难点不在语法本身，而在于背后的概念模型：你要能把"数据库中有一堆不同的表"这件事在脑中可视化。课程作者的建议是"把 Excel 表格在脑中移动、互相合并、按需重排"。

课程后续的 databases.md 一课补充了前置概念：数据库是 Web 应用的最底层，负责"替你记住一切"；关系型数据库用表存储不同类型的数据；而 SQL（结构化查询语言）正是用来查询数据库的、语法非常精简的语言。该课还对比了 SQL 与 NoSQL（非关系型）数据库的差异，并指出本课程全程使用 SQL。

本课将带你超越SELECT users.* FROM users LIMIT 1这样的入门查询，进入连接（JOIN）多张表、对结果进行计算、以新方式对结果分组等更动态的主题。

关系型数据库的核心心智模型：表、行、列、主键、外键与 Schema

要理解 SQL，首先要在脑中建立关系型数据库的结构模型。

表（Table）：数据库用很多张表存储不同类型的数据（例如users表和posts表）。表就像电子表格一样的长列表。
行（Row）：每一行是一条记录（record），或者说一个对象，例如一个具体的用户。
列（Column）：每一列是该记录的一个属性，例如姓名、邮箱等。

主键（Primary Key）

每张表都包含一个特殊的ID列，它为每一行提供唯一的行号，这一列被称为该记录的主键。主键是行在表中的唯一身份标识，因此基于主键的查找（如WHERE users.id = 42）总是精确且唯一的。

外键（Foreign Key）

你可以通过让一张表的某一列指向另一张表的 ID 来"链接"两张表。例如，posts表中的一行会在名为user_id的列中存放作者的 ID。因为posts表持有另一张表的 ID，所以这一列被称为外键。外键正是关系型数据库建立"关系"的机制，也是后续 JOIN 得以成立的前提。

Schema（模式）

数据库的设置信息存储在一个特殊文件中，称为Schema（模式）。每当你修改数据库的结构，它就会被更新。可以这样理解 Schema："这就是我们的数据库，它有几张表。第一张表是users，它有 'ID' 列（整数类型）、'name' 列（一串字符）、'email' 列（一串字符）……"。

Schema 在工程上的价值，在课程仓库的 prisma_orm.md 中有更深入的体现：当所有数据库交互都用裸 SQL 完成时，代码库中没有任何地方能让你一眼看懂数据表、表间关系与列的数据类型——你不得不登录数据库才能理解代码库在做什么。而大多数 ORM 通过把数据库定义（即 schema）引入代码库解决这一问题，让你能快速扫一眼某张表的 schema 就明白它有哪些列。在 Rails 侧，active_record_basics.md 也反复强调"数据模型是几乎所有大型 Web 应用的基石"。

建立与销毁数据库：DDL 命令与索引

SQL 允许你做所有事情。第一类命令用于搭建数据库结构：

CREATE DATABASE：创建数据库。
CREATE TABLE：创建单张表。
以及类似的用于修改（ALTER）或销毁（DROP）它们的命令。

课程仓库的 using_postgresql.md 给出了在 PostgreSQL shell（psql）中的完整实操序列，可以作为本课概念的直接落地示范：

-- 查看当前已有数据库 \l -- 创建数据库 CREATE DATABASE top_users; -- 连接刚创建的数据库 \c top_users -- 建表：id 列用 IDENTITY 自动生成，username 列限长 255 CREATE TABLE usernames ( id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, username VARCHAR ( 255 ) ); -- 用 \d 验证表已创建 \d

注意GENERATED ALWAYS AS IDENTITY定义了标识列（identity column）：PostgreSQL 会自动为id列生成值（默认从 1 开始每次递增 1），并隐式创建usernames_id_seq这一序列对象来追踪下一个要用的值——这相当于数据库层面自动维护主键的机制。

索引（Index）为什么重要

除了建表，你还可以告诉数据库在某个列上只允许唯一值（例如用户名），或用CREATE INDEX为某列建立索引以便日后更快地搜索。索引基本上"提前替你完成了排序的所有苦力活"——为那些你以后很可能用来搜索的列（如username）建立索引，会让你的数据库快得多。这也是本课知识检查中"Indexes 是做什么用的"的答案：它们是为了加速查询而预先组织好的数据结构。

SQL 的语法约定

SQL 喜欢在语句末尾使用分号（;），并且使用单引号（'）而非双引号（"）表示字符串字面量。这是你在手写 SQL 时最容易踩到的两个细节。

CRUD 语句与条件子句：往表里"折腾"数据

数据库建好、表还是空的时候，就要用 SQL 语句往里填充数据了。核心动作就是我们熟悉的CRUD——Create（创建）、Read（读取）、Update（更新）、Destroy（销毁）。由于你会花大量时间向数据提问并尝试展示它，绝大多数命令属于 "Read" 类别。

命令的三要素

每条 CRUD 命令都包含几个部分：动作（statement）、它作用的表、以及条件（clauses）。如果只对一张表执行动作而不指定条件，它将作用于整张表——你很可能因此弄坏东西。

Destroy（删除）

经典失误是敲出没有WHERE子句的DELETE FROM users，这会删光表中所有用户。你通常只需要删一个用户，应该用某个（最好是唯一的）属性如name或id来指定条件：

DELETE FROM users WHERE users.id = 1;

条件子句支持各种常识性操作：用比较运算符（>、<、<=等）指定作用于某组行；用逻辑运算符（AND、OR、NOT等）把多个子句串联起来：

DELETE FROM users WHERE id > 12 AND name = 'foo';

Create（创建）

创建用INSERT INTO，需要指定要插入值的列，后面跟上值本身：

INSERT INTO users (name, email) VALUES ('foobar', 'foo@bar.com');

技术上可以省略列名，但这被认为是糟糕的实践，一般不建议这么做。显式列出列名可以让语句自文档化，并在表结构变化时更不容易出错。

这是少数几种你不需要小心"选中了哪些行"的查询——因为你只是往表里新增行。

Update（更新）

更新用UPDATE，需要告诉它要SET什么数据（键值对）以及为哪些行做更新：

UPDATE users SET name='barfoo', email='bar@foo.com' WHERE email='foo@bar.com';

务必小心：如果WHERE子句匹配到多行（例如按常见的名字搜索），它们会被全部更新。真实世界中你应该按id搜索，因为它总是唯一的。

Read（读取）

读取用SELECT，是最常见的语句，例如：

SELECT * FROM users WHERE created_at < '2013-12-11 15:35:59 -0800';

这里的*表示"所有列"。指定列时最好同时带上表名和列名——单表查询时只写列名也能凑合，但一旦涉及多张表，SQL 就会报错。所以请始终写全表名：

SELECT users.id, users.name FROM users;

当只想取某列的唯一值时，用SELECT的近亲SELECT DISTINCT。例如想要用户所有不重复的名字：

SELECT DISTINCT users.name FROM users;

课程仓库中的 using_postgresql.md 展示了这些语句在 Node.js 应用中如何被pg库调用（并强调所有 SQL 都应放在 SQL 侧完成，例如搜索功能用WHERE username LIKE在 SQL 里做，而不是把数据拉回 JavaScript 再过滤）：

async function getAllUsernames() { const { rows } = await pool.query("SELECT * FROM usernames"); return rows; } async function insertUsername(username) { await pool.query("INSERT INTO usernames (username) VALUES ($1)", [username]); }

而 authentication_basics.md 则展示了建表与查询在真实认证场景中的完整形态——CREATE TABLE users建表、INSERT INTO users (username, password) VALUES ($1, $2)写入、SELECT * FROM users WHERE username = $1与SELECT * FROM users WHERE id = $1按用户名/ID 精确查找。

参数化查询与 SQL 注入（实战红线）

如果你把用户输入直接拼进 SQL 字符串，比如"INSERT INTO usernames (username) VALUES ('" + username + "')"，一个恶意用户可能输入sike'); DROP TABLE usernames; --来摧毁整张表——这就是著名的SQL 注入。pg提供的查询参数化（把用户输入放在第二个参数的数组里，$1、$2是占位符）正是为了防止这一点。这也是本课"Read"类语句实战中必须遵守的安全红线。

把表"拼接"起来：JOIN 与四种连接方式

如果你想拿到某个用户写的所有帖子，需要告诉 SQL 用哪些列把表"拉链"在一起——用ON子句指定连接条件，用JOIN命令执行"拉链"操作。但问题来了：如果两张表的数据不完全匹配（例如一个用户有多篇帖子），到底保留哪些行？一共有四种可能：

明确 LEFT 的含义：在 JOIN 中，"left"（左）表是指FROM子句指定的那张原始表，例如下面例子中的users。

INNER JOIN（即JOIN）——你的好朋友，95% 的场景都用它。只保留两张表中互相匹配的行。例如SELECT * FROM users JOIN posts ON users.id = posts.user_id只返回真正写过帖子的用户，以及那些在user_id列中指明了作者身份的帖子。如果一位作者写了多篇帖子，会返回多行（但包含用户数据的列会重复出现）。
LEFT OUTER JOIN——保留左表的所有行，再添加上右表中与左表匹配的行；由此产生的空单元格置为NULL。例如返回所有用户（无论是否写过帖子）：写过帖子的列出帖子，没写过的则把从posts表请求的列置为NULL。
RIGHT OUTER JOIN——正好相反：保留右表的所有行。
FULL OUTER JOIN——保留所有表的所有行，即使表之间存在不匹配，不匹配的单元格置为NULL。

JOIN 自然也能带条件。例如只想要某个特定用户的帖子：

SELECT * FROM users JOIN posts ON users.id = posts.user_id WHERE users.id = 42;

从实践角度看，INNER JOIN覆盖了绝大多数需求；LEFT OUTER JOIN在"列出所有 X 及其可选关联 Y"的场景（如"所有用户及其帖子"）中高频出现。理解四种连接的本质区别（保留哪些行、NULL出现在哪里），是后续做报表、统计类查询的基础。

用聚合函数汇总数据，用 GROUP BY 分组，用 HAVING 过滤

裸 SQL 查询常常返回一堆行。有时候你只想返回一个聚合了某列的单个值，比如某个用户写的帖子总数。这时就用到 SQL 提供的"聚合"函数——SUM、MIN、MAX、AVG等大多都在意料之中。聚合函数作为SELECT的一部分使用：

SELECT MAX(users.age) FROM users;

函数只作用于单一列，除非你指定*——而*只对部分函数有意义，如COUNT(*)（统计所有行）；像MAX(*)就没有意义——"取所有东西的最大值"是什么意思呢？

别名 AS

你常看到用别名（AS）重命名列或聚合函数，以便之后用别名引用它：

SELECT MAX(users.age) AS highest_age FROM users;

这会返回一列名为highest_age、值为最大年龄的结果。

GROUP BY：对数据分块后分别聚合

返回整个数据集单一值的聚合函数（如COUNT）很不错，但真正的威力在于对数据的特定分块分别聚合，再按块分组展示——例如显示每位用户的帖子数（而不是所有用户的帖子总数）：

SELECT users.id, users.name, COUNT(posts.id) AS posts_written FROM users JOIN posts ON users.id = posts.user_id GROUP BY users.id, users.name;

在GROUP BY中除了users.id还加上users.name，能提升可读性并符合最佳实践——显式地把所有被选中的非聚合列都放进GROUP BY子句；虽然对多数数据库而言可能并非严格必需。

HAVING：聚合之上的条件过滤

最后一个巧妙技巧是只展示数据的一个子集。正常情况下你会用WHERE子句来收窄范围；但一旦用了COUNT这类聚合函数（比如上面按用户统计帖子数），WHERE就不再生效了。所以，要基于聚合函数的结果条件化地取回记录，用的是HAVING子句——它本质上就是"聚合版本的 WHERE"。例如只显示写了 10 篇以上帖子的用户：

SELECT users.id, users.name, COUNT(posts.id) AS posts_written FROM users JOIN posts ON users.id = posts.user_id GROUP BY users.id, users.name HAVING COUNT(posts.id) >= 10;

WHERE与HAVING的核心区别（本课知识检查的必考点）：WHERE在分组/聚合之前过滤原始行；HAVING在分组/聚合之后过滤聚合结果。同一个条件该放哪里，取决于它是针对"行的属性"还是"组的统计值"。

以上所有示例可以借助课程配套的 project_sql_zoo.md 在线实践项目逐题验证——SQL Zoo 是为数不多让你能对已有表真正构建并运行查询的在线资源，从 "SELECT basics" 开始做到 Tutorial 0–9（含带 +/- 标记的题目）和每节末尾的测验，其中 GROUP BY 与 HAVING 的"头部急转弯"题目最能巩固本课概念。

为什么 SQL 比你的应用代码更快

学习这些内容之所以重要，是因为巧妙地用 SQL 构建查询，比把一大堆数据从数据库拖出来再用编程语言（如 Ruby 或 JavaScript）处理要快得多。例如要取所有用户的唯一名字，你当然可以先SELECT users.name FROM users拉回整张表，再用 JS/Ruby 方法去重——但这要求你把所有数据移出数据库、放进内存、再在代码里遍历。改用SELECT DISTINCT users.name FROM users，让 SQL 一步完成。

SQL 天生为速度而生：它内置查询优化器，会审视你即将运行的整条查询，精确推算出需要连接哪些表、以及如何最快地执行这条查询。SELECT与SELECT DISTINCT之间的性能差异，与你亲自处理数据的耗时相比可以忽略不计。学好 SQL，写出能做更多事情的更优查询，你的应用会快得多。课程原文也提醒：学会把这些概念在 project_sql_zoo.md 的项目里实践一遍，后续再进入应用层使用 ORM 时会受益良多。

从 SQL 到 ORM：课程中的落地衔接

本课为课程后续的数据库实战打下了语法与概念基础，仓库中与之直接衔接的内容包括：

using_postgresql.md：在 Express 应用中用pg（node-postgres）操作 PostgreSQL。它教你在psql里用CREATE DATABASE、CREATE TABLE、INSERT、SELECT完成建库建表填充，再通过Pool/Client两种连接方式执行参数化查询，并用db/populatedb.js脚本一键建表+填充数据。该课明确要求先完成本 SQL 课程，后续课程将默认你理解 SQL 语法与概念。
prisma_orm.md：Prisma ORM 用 Prisma Schema 语言把模型、列类型与表间关系（relation）写进代码库；npx prisma generate根据 schema 生成类型安全的 Prisma Client；Prisma Migrate用迁移文件把 schema 变更应用到数据库。它正是本课所说"ORM 让你在背后理解发生了什么"的 Node.js 侧实例。
active_record_basics.md：Rails 的 Active Record 把SELECT * FROM users变成User.all，把"建对象再保存"变成User.create(name: "Sven", email: "...")两步合并一步——是"ORM 平滑掉不同数据库差异"的 Ruby 侧实例。
authentication_basics.md：认证实战中直接手写CREATE TABLE、参数化INSERT与SELECT，是本课 CRUD 与安全知识（SQL 注入防护）的真实应用场景。

知识检查

以下是本课需要能够回答的关键问题（均可在上文对应小节中找到答案）：

外键和主键的区别是什么？（见主键与外键一节）
数据库的设置信息存储在哪里？（见 Schema 一节）
一条 SQL 命令的重要部分有哪些？（见命令的三要素一节）
CRUD 缩写中对应 "Read" 的是哪条 SQL 语句？（见Read（读取）一节）
哪种JOIN语句只保留两张表中互相匹配的行？（见JOIN 与四种连接方式一节）
如何使用聚合函数？（见聚合函数一节）
什么情况下应该使用HAVING子句？（见 HAVING 一节）
为什么不能只用代码处理数据库数据？（见为什么 SQL 比你的应用代码更快一节）

结语

SQL 是一套需要费点心思才能掌握的概念组合，尤其是"多表连接后如何条件化地展示与分组结果"。从普通的 JOIN 到普通的聚合函数，都属于核心知识，值得你下功夫消化。那些真正高级的概念，未来可能只在少数场景用到——即便现在全部学一遍，日后遇到特定高级查询时，你大概率还是会上网搜索具体写法。更重要的是一旦进入后续课程，把 SQL 应用到实际代码库、用上 ORM 工具之后，你会发现它们让开发生活轻松太多——但正如课程作者所说：别在转向更美好的工具时，把老伙计 SQL 忘得一干二净。

【免费下载链接】curriculumThe open curriculum for learning web development项目地址: https://gitcode.com/GitHub_Trending/cu/curriculum

创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考

企业官网建设流程全解析