数据库与 SQL 入门：The Odin Project 课程中的关系数据库核心概念与实战

数据库与 SQL 入门The Odin Project 课程中的关系数据库核心概念与实战【免费下载链接】curriculumThe open curriculum for learning web development项目地址: https://gitcode.com/GitHub_Trending/cu/curriculum数据库是 Web 应用的底层基石负责替你记住一切用户数据——从登录密码到文章内容。本文以 databases/databases/databases.md 课程为主体系统讲解数据库是什么、关系数据库如何用表组织数据、SQL结构化查询语言如何增删改查数据并结合本仓库后续课程databases_and_sql.md与真实工程实践PostgreSQL、ORM、迁移展开。读完本文你将掌握数据库的基本心智模型能够理解并写出SELECT、INSERT等核心 SQL 语句为后续学习 ORM 与后端开发打下基础。数据库Web 应用最底层的数据基石你可能好奇过网站是如何记住所有用户数据的是谁记得你的登录密码好让你顺利登录答案是——任何 Web 应用的最底层都是数据库database它负责完成所有的记忆工作缓存是更后面的话题。数据库既可以简单得像一张 Excel 电子表格也可以复杂得像 Facebook 那样被拆分成许多巨大的部分。它们通常藏在 Web 应用的幕后因此初学者往往对其既好奇又敬畏。但事实上数据库并不需要让人望而生畏——随着学习的深入你会与它成为好朋友至少也是相爱相杀的伙伴最终能够熟练地与之交互甚至比一些在职开发人员做得更好。从整个 Web 开发的视角看本仓库的 后端导论 明确指出后端负责幕后的一切而数据库正是后端存储数据的地方。前端只用 HTML、CSS、JavaScript 与用户交互而后端则要考虑数据如何持久化——这就是数据库登场的时机。关系数据库用表来组织数据大多数主流数据库本课程聚焦的那类都是关系数据库relational database。关系数据库使用大量**表table**来存储不同类型的数据例如users表和posts表。可以把表想象成电子表格每一**行row是一条记录record即一个对象比如一个具体的用户每一列column**是该记录的一个属性比如姓名、邮箱。以下是本课程中反复出现的经典示例模型users 表posts 表idnameemailcreated_atiduser_idtitle1foobarfoobar.com...11Hello主键Primary Key所有表都包含一列ID为每一行提供唯一的编号这一列被称为记录的主键primary key。主键保证了每条记录可以被唯一标识——这也是后续删除、更新单条记录时的安全依据。外键Foreign Key关系数据库的威力在于关联。你可以通过让一张表的某一列指向另一张表的 ID 来链接两张表。例如posts表中的一行可能包含作者 ID存在user_id列中。因为posts表持有另一张表的 ID所以该列被称为外键foreign key。本仓库 Rails 关联基础课程 是这样总结的关系数据库如 SQLite3 或 PostgreSQL允许你通过主键把两张表链接起来而引用其他表的键就是外键这正是关系数据库的真正力量所在——利用这些关系。例如在 Rails 中声明has_many :posts/belongs_to :user之后数据库表层面就是posts表的每一行都有一个user_id列来标明它属于哪个用户。关系数据库与 XML 的区别课程大纲中特别提出了一个问题关系数据库与 XML 有何不同简而言之XML 是一种文档格式用嵌套标签如usersuser.../user/users以层级结构描述数据数据保存在文档里适合数据交换与配置表达而关系数据库以表和行的扁平结构存储数据通过主键/外键关系把不同实体关联起来并提供了标准化的查询语言SQL进行检索、聚合与更新。关系数据库强项在于大规模数据的 CRUD 操作、条件查询、跨表关联和事务一致性——这些都是 XML 文档难以高效胜任的场景。这也是为什么课程后续会把大量篇幅放在 SQL 与关系数据库上。SQL与关系数据库对话的语言**SQLStructured Query Language结构化查询语言**是用于查询数据库的语言。与已经学过的普通编程语言相比SQL 的语法非常简短——只需掌握一小撮动词statement例如SELECT、INSERT、UPDATE、DELETE、CREATE等。真正让人犯难的是你需要学会在脑海中可视化一条 SQL 语句到底在做什么——它选中了哪些表、筛选了哪些行、聚合了哪些列。SQL 并不是一门大语言更重要的是理解其背后的概念。SQL 的两个书写习惯值得一开始就养成语句以**分号;**结尾字符串使用**单引号**而非双引号。SQL 用来做什么SQL 的核心用途就是向数据库提问——查询数据偶尔也新增或修改数据。本仓库的 SQL 进阶课程 给出了生动的例子简单场景显示所有通过促销码 FREESTUFF 在 12 月注册的用户中等场景显示当前用户创建的所有评论并按主题和创建日期排序复杂场景列出所有发往用户数超过 1000 的州的订单按数量和订单总价排序内部运营场景统计哪些推广渠道带来的用户达到了每工作日阅读 5 篇文章的活跃标准。幸运的是我们要聚焦的数据库大多会说 SQL而 SQL 常用的动词只有十几个。学会 SQL你就能理解 ORM对象关系映射如 Rails 的 Active Record 或 Node.js 的 Prisma在幕后做了什么也能更自在地向数据提出更复杂的问题。Schema 与数据库搭建从建库到建表SQL 可以做所有事。第一类命令用于搭建环境创建数据库CREATE DATABASE、创建表CREATE TABLE以及修改或销毁它们的命令。什么是 Schema数据库的设置信息保存在一个特殊的地方叫做Schema模式。每当你修改数据库的结构Schema 都会被更新。可以把 Schema 理解为一份数据库说明书这是我们的数据库它有几张表。第一张表叫users包含ID整数、name一串字符、email一串字符……Rails 课程 迁移Migrations 也印证了这一点Schema 就是数据库的结构——创建和删除表、增删列都属于 Schema 变更而增删行不算。Rails 中的迁移本质上是 Schema 迁移在不移动数据位置的前提下修改数据库结构。用索引加速查询除了建表你还可以让数据库限制某列只能有唯一值比如用户名或者用CREATE INDEX为某列建立索引以便日后更快地搜索。索引本质上是在建表时提前做了排序工作——对将来要频繁用于搜索的列如用户名建立索引会让数据库快得多。一个真实的建库建表例子本仓库的 PostgreSQL 实战课程 展示了在 PostgreSQL shell 中完整的建库建表流程。先创建数据库并连接CREATE DATABASE top_users; \c top_users再创建一张存储用户名的表using_postgresql.mdCREATE TABLE usernames ( id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, username VARCHAR ( 255 ) );这里用到了几个关键概念id列被声明为主键GENERATED ALWAYS AS IDENTITY使其成为标识列identity column——PostgreSQL 会自动为每一行生成自增的值默认从 1 开始、每次加 1并隐式创建usernames_id_seq序列对象来跟踪下一个值。这正是主键保证每行唯一在生产数据库中的具体落地方式。用 CRUD 语句操纵数据数据库搭建好、有了空表之后就要开始填充数据了。核心操作就是我们熟悉的CRUDCreate创建、Read读取、Update更新、Destroy销毁。大部分命令属于读取类别因为你会花大量时间向数据提问并展示结果。每条 CRUD 命令都包含几个部分动作statement、它作用的表、以及条件clause。如果对整张表执行动作而不指定条件就会作用于整张表——你很可能搞砸某些东西。Create用 INSERT INTO 插入记录创建查询使用INSERT INTO需要指定要插入值的列后面跟上对应的值databases_and_sql.mdINSERT INTO users (name, email) VALUES (foobar, foobar.com);注意技术上可以省略列名但这属于不良实践通常不推荐。这是少数几种不需要小心选中了哪些行的查询因为你只是在向表里新增记录。PostgreSQL 实战中插入多条记录的形式using_postgresql.mdINSERT INTO usernames (username) VALUES (Mao), (nevz), (Lofty);Read用 SELECT 获取所有记录读取查询使用SELECT是最常用的语句。**如何获取一张表的所有记录**答案是SELECT * FROM users;这里的*表示所有列。课程还给出了带条件的读取示例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;Update用 UPDATE 修改记录更新查询使用UPDATE需要告诉它要SET什么数据键值对形式以及更新哪些行。小心如果WHERE子句匹配到多行比如按常见名字搜索它们都会被更新。更新用户邮箱的典型查询如下现实中应按始终唯一的 ID 搜索UPDATE users SET namebarfoo, emailbarfoo.com WHERE emailfoobar.com;Destroy用 DELETE 删除记录销毁查询的经典错误是只写DELETE FROM users而没有WHERE子句——这会清空表中所有用户。正确做法是基于某个希望是唯一的属性指定条件例如DELETE FROM users WHERE users.id 1;你可以运用各种常识性写法用比较运算符、、等指定要操作的行组或用逻辑运算符AND、OR、NOT等串联多个条件DELETE FROM users WHERE id 12 AND name foo;用 JOIN 把表缝合在一起如果想获取某个用户创建的所有帖子你需要告诉 SQL 用哪几列把两张表拉链起来——这就是ON子句执行拉链动作的是JOIN命令。但问题来了如果把两张数据不完全匹配的表拼在一起比如一个用户有多条帖子该保留哪些行有四种可能JOIN 类型行为INNER JOIN即JOIN只保留两张表中互相匹配的行。这是你最常用的类型大约覆盖 95% 的需求。LEFT OUTER JOIN保留左表所有行并把右表中匹配的行附加进来没有匹配的单元格置为NULL。RIGHT OUTER JOIN与 LEFT 相反保留右表所有行。FULL OUTER JOIN保留所有表中的所有行即使不匹配不匹配的单元格置为NULL。澄清JOIN 中的左表指的是FROM子句后面的原始表即ON之前的表例如下面示例中的users。例如获取所有写了帖子的用户及其帖子SELECT * FROM users JOIN posts ON users.id posts.user_id;如果某位作者写了多篇帖子会返回多行其中用户数据列会重复出现。JOIN 也可以自然地带条件比如只要 ID 为 42 的用户的帖子SELECT * FROM users JOIN posts ON users.id posts.user_id WHERE users.id 42;聚合函数、GROUP BY 与 HAVING普通的 SQL 查询通常返回一堆行。但有时你只想要一个能聚合某列的单一值比如统计某个用户写了多少帖子。这时就用 SQL 提供的聚合函数——SUM、MIN、MAX、AVG、COUNT等都是符合直觉的函数。聚合函数作为SELECT语句的一部分使用databases_and_sql.mdSELECT MAX(users.age) FROM users;聚合函数只作用于单列除非你指定*——*只对部分函数有意义比如COUNT(*)统计所有行而MAX(*)没有意义对一切取最大值是什么。用 AS 起别名通常会用别名AS重命名列或聚合函数便于之后引用SELECT MAX(users.age) AS highest_age FROM users;这条语句会返回一个名为highest_age的列其中是最大年龄。用 GROUP BY 分组聚合聚合函数真正的乐趣在于对数据中特定的块分组后再聚合。例如要显示每个用户的帖子数而不是所有用户的总帖子数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;建议除了users.id再显式把users.name也加入GROUP BY。把所有选中的非聚合列都放进GROUP BY是清晰且符合最佳实践的做法虽然对多数数据库来说并非严格必需。HAVING vs WHERE聚合后的条件过滤最后一个技巧如果只想显示数据的子集平时用WHERE缩小范围。但一旦用了COUNT这样的聚合函数比如上面的每个用户的帖子数WHERE就失效了。要基于聚合函数的结果条件化地取记录需要使用HAVING子句——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在聚合之后筛选分组作用于聚合结果如COUNT(...)的值。为什么用 SQL 而不是应用程序代码处理数据这部分知识尤其重要因为巧妙地用 SQL 构建查询比把一大堆数据拉出来再用编程语言Ruby 或 JavaScript处理要快得多。举例想要所有用户的不重复名字。你可以用SELECT users.name FROM users拉出全列表再用 JS/Ruby 方法去重——但这就需要把数据从数据库取出、放进内存、再遍历处理。改用SELECT DISTINCT users.name FROM users让 SQL 一步完成。原因在于 SQL 天生为速度而设计它内置查询优化器query optimizer会通盘审视即将执行的查询计算出需要连接哪些表、如何最快执行。SELECT与SELECT DISTINCT之间的性能差异相比你在代码里手动处理的耗时几乎可以忽略。学好 SQL写出能做更多事的更优查询会让你的应用快得多。课程配套练习与后续学习路径SQL Zoo 实战项目本课知识需要在实践中固化。仓库的 SQL Zoo 项目 是少有的能让你对着现成表实际构建并运行查询的在线资源每个教程展示一张表然后让你运行查询回答具体问题。项目要求完成 Tutorials 0-9包括带 /- 标记的及每节末尾的测验确保主页面右上角 Engine 下拉框选择 MySQL默认值注意大结果会被截断并非所有行和列都会显示因此答案可能看起来不完全正确这属正常现象。下一课SQL 进阶本课只是预告片。紧接着的 databases_and_sql.md 会深入讲解主键/外键、Schema、WHERE/LIKE/DISTINCT子句、AVG/COUNT/SUM函数、索引、WHERE与HAVING的区别并提供两个交互式 SQL 教程作为作业是巩固本课概念的自然延伸。从 SQL 到 ORM让数据库进入代码库学会 SQL 后你会接触 ORM对象关系映射。本仓库 Prisma ORM 课程 总结了裸写 SQL 的痛点代码量大、数据库结构难以在代码库中体现、迁移难以管理。ORM 通过把数据库定义Schema引入代码库、提供类型安全的查询客户端如prisma.message.findMany()和标准化的迁移机制来解决这些问题。Rails 侧的 Active Record 关联 则把外键关系抽象为has_many/belongs_to例如用User.first.posts就能拿到第一个用户的全部帖子。迁移Schema 的版本管理在实际工程中数据库结构会随需求演进。Rails 迁移课程 指出迁移是以可逆、可重复应用的方式设置或修改数据库 Schema 的脚本rails db:migrate执行未运行的迁移rails db:rollback撤销最近一批迁移——这都建立在对Schema 是数据库结构的清晰理解之上也就是本课的核心概念。知识检查课程末尾的知识检查问题及本文给出的答案速览什么是数据库数据库是 Web 应用最底层的存储层负责持久化并记住所有用户数据小到电子表格大到 Facebook 级别的分布式系统。什么是关系数据库使用多张表行记录、列属性存储数据并通过主键/外键在表之间建立关联的数据库。什么是主键每张表都有的ID列为每一行提供唯一编号用于唯一标识记录。什么是 SQL结构化查询语言用于与关系数据库对话语法简短、只有少量常用动词。如何获取一张表的所有记录使用SELECT * FROM table_name;。如何插入一条记录使用INSERT INTO table_name (col1, col2) VALUES (val1, val2);。结语数据库与 SQL 是任何 Web 应用的地基。本课的目标不是让你立刻精通而是建立起数据库如何工作的心智模型数据以表的形式组织主键与外键把实体关联起来SQL 用少量动词完成从建库建表到增删改查的全部操作。在后续课程中你会在 SQL Zoo 等实战项目中反复练习这些概念进而理解 ORM 如何在幕后替你生成 SQL——到那时你与数据库的关系将真正从敬畏变成专业。【免费下载链接】curriculumThe open curriculum for learning web development项目地址: https://gitcode.com/GitHub_Trending/cu/curriculum创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考