The Odin Project 数据库课程SQL 与关系型数据库核心实战指南【免费下载链接】curriculumThe open curriculum for learning web development项目地址: https://gitcode.com/GitHub_Trending/cu/curriculum导读本文是开源课程仓库 curriculumThe 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 的良好掌握不仅能让你在使用对象关系映射ORMObject-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 shellpsql中的完整实操序列可以作为本课概念的直接落地示范-- 查看当前已有数据库 \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 columnPostgreSQL 会自动为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, foobar.com);技术上可以省略列名但这被认为是糟糕的实践一般不建议这么做。显式列出列名可以让语句自文档化并在表结构变化时更不容易出错。这是少数几种你不需要小心选中了哪些行的查询——因为你只是往表里新增行。Update更新更新用UPDATE需要告诉它要SET什么数据键值对以及为哪些行做更新UPDATE users SET namebarfoo, emailbarfoo.com WHERE emailfoobar.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 应用中用pgnode-postgres操作 PostgreSQL。它教你在psql里用CREATE DATABASE、CREATE TABLE、INSERT、SELECT完成建库建表填充再通过Pool/Client两种连接方式执行参数化查询并用db/populatedb.js脚本一键建表填充数据。该课明确要求先完成本 SQL 课程后续课程将默认你理解 SQL 语法与概念。prisma_orm.mdPrisma ORM 用 Prisma Schema 语言把模型、列类型与表间关系relation写进代码库npx prisma generate根据 schema 生成类型安全的 Prisma ClientPrisma Migrate用迁移文件把 schema 变更应用到数据库。它正是本课所说ORM 让你在背后理解发生了什么的 Node.js 侧实例。active_record_basics.mdRails 的 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），仅供参考