Data-Science-For-Beginners 关系数据库实战指南从表、主键到 SQL 与 JOIN 查询【免费下载链接】Data-Science-For-Beginners10 Weeks, 20 Lessons, Data Science for All!项目地址: https://gitcode.com/GitHub_Trending/da/Data-Science-For-Beginners本指南以 Data-Science-For-Beginners 课程第 2 部分《Working with Data》的第 5 课《Relational Databases》为核心系统讲解关系数据库的建模思想与 SQL 检索技能从单表设计的缺陷出发逐步引入主键PK、外键FK与多表关联并落地到SELECT、WHERE、INNER JOIN等核心查询语句。学完本篇你将能够理解关系数据库为什么要把数据拆分为多张表并能使用 SQL 在真实数据库本仓库自带的 airports.db上完成跨表联查。本文对应的课程文档位于 2-Working-With-Data/05-relational-databases/README.md含丹麦语译本 translations/da/2-Working-With-Data/05-relational-databases/README.md配套实战作业见 assignment.md。本课程隶属于处理数据学习单元同单元还包括 非关系数据库、用 Python 处理数据 与 数据准备 四课。一切从表开始关系数据库的核心构件是表table。这与电子表格spreadsheet的直觉一致表是**行row与列column**的集合——行存放我们想处理的数据或信息例如一座城市的名字、某年的降雨量列则描述它存放的数据有时被称为元数据metadata。假设我们要建一张表保存城市信息从名称和国家开始CityCountryTokyoJapanAtlantaUnited StatesAucklandNew Zealand注意City、Country这些列名描述的是所存数据的含义而每一行都对应一座城市的完整信息。在仓库配套的真实数据库 airports.db 中Cities表正是这种结构id主键、city文本、country文本共存放了 170 座英国与爱尔兰城市。单表方案的两个缺陷上述表看起来熟悉但一旦开始追加更多维度数据问题就来了。假设我们要为东京增加 2018–2020 三年的年降雨量毫米CityCountryYearAmountTokyoJapan20201690TokyoJapan20191874TokyoJapan20181445你立刻会发现城市名称和国家被反复重复。这既浪费存储空间又毫无必要——Tokyo 毕竟只有一个我们关心的名字。这是典型的**数据冗余duplication**问题。再试试另一种方案为每个年份新建一列。CityCountry201820192020TokyoJapan144518741690AtlantaUnited States177911111683AucklandNew Zealand13869421176这虽然避免了行重复却又引入了新麻烦每来一个新年份就得改表结构而且随着数据增长把年份当作列会让检索与计算如跨年聚合变得困难。结论正如课程所指出的我们需要多张表与关系relationships。通过拆分数据我们可以避免重复并在处理数据时获得更高的灵活性。关系的核心概念主键与外键回到城市数据我们决定把城市名称 国家单独放一张表。但创建下一张表之前必须先回答一个关键问题如何引用每一座城市——我们需要一种标识符即数据库术语中的主键Primary Key常缩写为 PK。主键是用于唯一标识表中某一行某条记录的值。虽然理论上可以直接用业务值充当主键例如城市名但主键几乎总应该是一个数字或其他标识符。原因在于我们绝不想让 id 改变——一旦改变就会破坏它与其他表建立的关系。大多数情况下主键id是数据库自动生成的自增数字。课程示例中带主键的cities表city_idCityCountry1TokyoJapan2AtlantaUnited States3AucklandNew Zealand✅ 本课程中id与主键两个词交替使用。这些概念同样适用于之后要学习的 DataFrame——DataFrame 不使用主键这个术语但行为上非常相似。再看仓库真实数据库的建表语句airports.db可以印证主键的两种典型写法CREATE TABLE Cities ( id INTEGER PRIMARY KEY AUTOINCREMENT, city text NOT NULL, country text NOT NULL )这里id INTEGER PRIMARY KEY AUTOINCREMENT表示id是整型主键由 SQLite 自动递增生成新插入城市时无需手工赋值——这正是课程所说的自动生成数字主键。创建完城市表后我们存放降雨数据。与其重复整段城市信息不如直接引用城市 id。同时新表也应拥有自己的id列因为所有表都应有一个 id 或主键rainfall_idcity_idYearAmount11201814452120191874312020169042201817795220191111622020168373201813868320199429320201176注意新表rainfall中的city_id列它存放的值指向cities表中的 id。在关系数据库术语中这叫做外键Foreign Key常缩写为 FK——本质上是另一张表的主键你可以把它理解成一个引用或指针city_id为 1 表示这条降雨记录属于 Tokyo。同样airports.db的Airports表也体现了这一设计因为一些城市可能拥有多个机场所以拆成两张表并通过外键关联CREATE TABLE Airports ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, code TEXT, city_id INTEGER, FOREIGN KEY(city_id) REFERENCES Cities(id) )FOREIGN KEY(city_id) REFERENCES Cities(id)从数据库层面声明Airports.city_id引用Cities.id。例如真实数据中Belfast International AirportEGAA与George Best Belfast City AirportEGAC都指向city_id 2Belfast正是一城多机场 → 一表主数据、一表明细的拆表动机。用 SQL 检索数据SELECT 与 FROM数据拆分到多张表后如何取回如果使用 MySQL、SQL Server、Oracle 等关系数据库可以借助一门标准语言——结构化查询语言 SQLStructured Query Language它用于在关系数据库中检索与修改数据。检索数据的命令是SELECT。其核心逻辑是从FROM包含这些列的表中选择SELECT你想看的列。例如只显示城市名称SELECT city FROM cities; -- Output: -- Tokyo -- Atlanta -- AucklandSELECT后列出列名FROM后列出表名。[!NOTE] SQL 语法不区分大小写select与SELECT含义相同但不同数据库中列名与表名可能区分大小写。因此最佳实践是编程中一律按区分大小写对待。书写 SQL 时通用惯例是将关键字全部大写。用 WHERE 过滤数据上面的查询会返回所有城市。若只想显示新西兰的城市就需要某种过滤器——SQL 关键字是WHERE表示某条件为真where something is trueSELECT city FROM cities WHERE country New Zealand; -- Output: -- Auckland这一语法在真实数据库上同样有效。例如对 airports.db 查询爱尔兰的所有城市SELECT city FROM Cities WHERE country Ireland ORDER BY city;实测可返回 16 条结果包括 Dublin、Cork、Galway、Shannon、Sligo、Waterford 等数据来源Cities表中country Ireland的记录。连接数据INNER JOIN到目前为止我们只从单张表取数。现在要把cities与rainfall两张表的数据合在一起看这需要**连接join**它们。连接的本质是在两张表之间缝一道线把每张表中某一列的值互相匹配。在我们的例子中用rainfall表的city_id与cities表的city_id匹配把降雨量对号入座到对应城市。这种连接类型叫做inner join内连接任何在另一张表中找不到匹配的行都不会显示。本例中每座城市都有降雨记录所以结果包含全部数据。分两步执行第一步通过连接列city_id把两张表缝起来同时选出我们关心的两列cities.city与rainfall.amountSELECT cities.city rainfall.amount FROM cities INNER JOIN rainfall ON cities.city_id rainfall.city_id第二步加上WHERE过滤出 2019 年的降雨量SELECT cities.city rainfall.amount FROM cities INNER JOIN rainfall ON cities.city_id rainfall.city_id WHERE rainfall.year 2019 -- Output -- city | amount -- -------- | ------ -- Tokyo | 1874 -- Atlanta | 1111 -- Auckland | 942注意当多张表存在同名列如两侧都有city_id时需要用表名.列名的限定写法消除歧义——这也是cities.city、rainfall.year这类写法的原因。实战演练用 SQLite 查询机场数据课程配套作业assignment.md提供了一份基于SQLite的示例数据库 airports.db包含英国与爱尔兰的机场信息非常适合把上面的概念跑通。其 schema表设计如下Citiesid (PK, integer)city (text)country (text)Airportsid (PK, integer)name (text)code (text)city_id (FK to id inCities)环境准备与打开数据库安装 Visual Studio Code然后在其扩展市场中安装SQLite扩展将本仓库的 airports.db 保存到本地目录在 VS Code 中按Ctrl-Shift-PMac 为Cmd-Shift-P打开命令面板输入并执行SQLite: Open database选择Choose database from file打开下载好的airports.db打开后界面不会有明显变化再次打开命令面板执行SQLite: New query新建查询窗口窗口内按Ctrl-Shift-QMac 为Cmd-Shift-Q即可对数据库运行 SQL。[!NOTE] 关于 SQLite 扩展的更多用法可查阅其 Marketplace 文档页。也可以不依赖图形界面直接用 Python 内置的sqlite3模块以命令行方式验证同一套查询import sqlite3 conn sqlite3.connect(airports.db) cur conn.cursor() cur.execute(SELECT a.name, c.city, c.country FROM Airports a INNER JOIN Cities c ON a.city_id c.id LIMIT 5) print(cur.fetchall())四道作业查询及可验证的参考答案1. 查询Cities表中所有城市名SELECT city FROM Cities;2. 查询爱尔兰的所有城市SELECT city FROM Cities WHERE country Ireland;3. 查询所有机场名称及其所在城市与国家内连接两表SELECT a.name, c.city, c.country FROM Airports a INNER JOIN Cities c ON a.city_id c.id;4. 查询英国伦敦的所有机场SELECT a.name, a.code, c.city, c.country FROM Airports a INNER JOIN Cities c ON a.city_id c.id WHERE c.city London AND c.country United Kingdom;上述查询在本仓库 airports.db 上实测均可运行。其中第 3 题返回全部 181 个机场记录例如Belfast International Airport | Belfast | United Kingdom第 4 题返回伦敦的 6 个机场London LutonEGGW、London GatwickEGKK、London CityEGLC、London HeathrowEGLL、London StanstedEGSS与 London HeliportEGLW——这正是一座城市多座机场 → 靠city_id外键 JOIN 关联的直观印证。总结关系数据库的核心思想是把信息拆分到多张表中再通过连接把它们重新组合起来用于展示与分析从而获得极高的灵活性来进行计算和数据操纵。本篇你已掌握关系数据库的四个核心概念——表、主键PK、外键FK以及基于它们的SQL 查询SELECT / FROM / WHERE / INNER JOIN并通过仓库自带的 SQLite 机场数据库完成了从单表检索到双表连接的完整实战。 挑战互联网上有大量可用的关系数据库如开源数据集合。你可以运用本篇学到的SELECT、WHERE与INNER JOIN技能去探索这些公开数据尝试回答自己的分析问题。自测与延伸学习预习/课后测验本课对应课程的第 8、9 号小测验见课程 README 中的 Quiz 链接复习巩固完成课程作业 Displaying airport data显示机场数据并对照上文四道查询验证自己的理解继续学习本课程单元后续内容还包括 非关系数据库对比关系型与文档型/键值型存储以及 用 Python 处理数据进一步了解 DataFrame 与类主键行为可一并阅读以构建完整的数据处理知识体系。【免费下载链接】Data-Science-For-Beginners10 Weeks, 20 Lessons, Data Science for All!项目地址: https://gitcode.com/GitHub_Trending/da/Data-Science-For-Beginners创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考