Data Science For Beginners 实战用 SQLite 与 SQL 查询展示机场数据库【免费下载链接】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 课程第 5 课《关系数据库》的配套作业展开讲解如何基于仓库自带的airports.dbSQLite 数据库借助 Visual Studio Code 的 SQLite 扩展编写并运行 SQL 查询完成按城市展示机场信息的完整实践。读完本文你将掌握关系数据库中主键PK、外键FK与表关联的底层结构能够独立写出SELECT、WHERE、INNER JOIN等核心 SQL 语句并验证查询结果的正确性。任务背景一个关于机场的关系型数据库本作业对应课程作业说明文档 translations/da/2-Working-With-Data/05-relational-databases/assignment.md英文原版为 2-Working-With-Data/05-relational-databases/assignment.md其课程讲义位于 2-Working-With-Data/05-relational-databases/README.md。作业要求你使用一个基于 SQLite。数据库中存储了英国与爱尔兰各地城市及其机场的信息。核心难点在于一座城市可能拥有多个机场例如伦敦有 6 座机场贝尔法斯特、格拉斯哥、曼彻斯特等各有 2 座因此数据被拆分为Cities与Airports两张表通过外键建立一对多关系。作业要求你编写 SQL 查询把分散在两张表中的信息重新缝合起来展示。环境准备安装 Visual Studio Code 与 SQLite 扩展作业的官方推荐路线是使用 Visual Studio CodeVS Code搭配官方 SQLite 扩展无需安装独立数据库服务即可交互式地打开.db文件并运行查询。访问 code.visualstudio.com按页面指引安装 Visual Studio Code在 VS Code 的扩展市场Extensions中搜索并安装 SQLite 扩展扩展 IDalexcvzz.vscode-sqlite按 Marketplace 页面上的说明完成安装。安装完成后你不需要配置任何数据库连接字符串——SQLite 是文件型数据库所有数据都封装在单一的airports.db文件中这也是它作为入门教学载体非常轻量的原因。下载并打开 airports.db 数据库接下来按以下步骤把数据库载入 VS Code从仓库下载 airports.db 并保存到本地任意目录如果已通过git clone拉取本仓库该文件已存在于2-Working-With-Data/05-relational-databases/目录下无需额外下载启动 Visual Studio Code按Ctrl-Shift-PMac 为Cmd-Shift-P打开命令面板输入并选择SQLite: Open database选择Choose database from file在文件选择器中打开前面保存的airports.db打开数据库后屏幕上不会立刻出现变化这是正常现象此时再次按Ctrl-Shift-PMac 为Cmd-Shift-P输入并选择SQLite: New query创建新的查询窗口。查询窗口创建后即可在其中编写 SQL 语句按Ctrl-Shift-QMac 为Cmd-Shift-Q即可对数据库执行查询。SQLite 扩展的更多用法可查阅其 官方文档。提示如果当前环境没有安装 VS Code也可以使用 Python 内置的sqlite3模块或任意 SQLite 客户端如sqlite3命令行工具完成本作业SQL 语句本身完全一致。数据库模式Schema主键、外键与一对多关系数据库模式schema指的是表的设计与结构。作业说明中给出了两张表的逻辑设计Citiesid (PK, integer)city (text)country (text)Airportsid (PK, integer)name (text)code (text)city_id (FK 指向 Cities.id)从仓库实际数据库文件验证airports.db的物理建表语句与上述设计一致可通过SELECT sql FROM sqlite_master查看CREATE TABLE Cities ( id INTEGER PRIMARY KEY AUTOINCREMENT, city text NOT NULL, country text NOT NULL ); CREATE TABLE Airports ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, code TEXT, city_id INTEGER, FOREIGN KEY(city_id) REFERENCES Cities(id) );对照课程讲义可以确认其设计动机主键Primary KeyPKCities.id与Airports.id都使用自增整数用于唯一标识一行。讲义强调主键几乎总是自动生成的数字因为人名、地名等业务值可能变化一旦变化就会破坏关系外键Foreign KeyFKAirports.city_id保存的是Cities.id的引用值相当于指向Cities表的一根指针从而避免在Airports表中反复复制城市名与国家名消除冗余存储为什么要拆成两张表一座城市可以有多个机场如果全部塞进一张表城市名与国家名会大量重复正如讲义中东京降雨量示例展示的重复行问题反之把年份做成列又会迫使表结构随数据增长而频繁改动。拆分表后新增机场只需新增一行并引用对应city_id无需修改表结构。数据库实际规模从仓库数据库文件实测Cities表共 170 行覆盖英国与爱尔兰的城市Airports表共 181 行。其中伦敦London关联了 6 座机场贝尔法斯特、布里斯托尔、格拉斯哥、利兹、曼彻斯特各关联 2 座机场——这正是一城多场必须用外键建模的直接证据。作业任务四道 SQL 查询的解析与参考解答作业要求编写查询返回以下四类信息。下面给出每道题的完整 SQL、写法讲解与基于仓库数据库的真实运行结果。1. 查询 Cities 表中所有城市名SELECT city FROM Cities;SELECT指定要返回的列FROM指定数据来源表。执行后返回 170 行城市名按插入顺序。若希望按字母序排列可追加ORDER BYSELECT city FROM Cities ORDER BY city; -- 部分输出 -- Aberdeen -- Angelsey -- Bantry -- Barkston Heath -- ...2. 查询 Cities 表中爱尔兰Ireland的所有城市SELECT city FROM Cities WHERE country Ireland;WHERE是 SQL 的过滤关键字语义是只保留使条件为真的行。执行结果共 16 行包括 Cork、Dublin、Galway、Kerry、Shannon、Sligo、Waterford 等爱尔兰城市。注意字符串字面量在 SQL 中要用单引号包裹例如Ireland。3. 查询所有机场名称及其所在城市和国家SELECT Airports.name, Cities.city, Cities.country FROM Cities INNER JOIN Airports ON Cities.id Airports.city_id;这是本作业的核心——连接JOIN。由于机场信息在Airports表、城市与国家信息在Cities表必须通过外键Airports.city_id Cities.id把两张表缝在一起才能同时取出机场名、城市名与国家名。INNER JOIN内连接的含义是只有两张表中都能匹配到对应关系的行才会出现在结果集中。对当前数据库执行后共返回 180 行。注意这个数字比Airports表的总行数181少 1——因为Airports表中有一条记录Newcastle Aerodrome代码EINC的city_id为 NULL在Cities表中找不到匹配的城市被内连接过滤掉了。这是一个观察内连接行为的绝佳例子内连接天然会丢弃孤儿记录。4. 查询英国伦敦London, United Kingdom的所有机场SELECT Airports.name, Airports.code FROM Cities INNER JOIN Airports ON Cities.id Airports.city_id WHERE Cities.city London AND Cities.country United Kingdom;在INNER JOIN的基础上叠加WHERE过滤即可精确圈定英国伦敦用AND同时限定城市名与国家名避免与任何同名城市混淆。真实运行结果恰好 6 行机场名代码ICAOLondon Heathrow AirportEGLLLondon Gatwick AirportEGKKLondon City AirportEGLCLondon Luton AirportEGGWLondon Stansted AirportEGSSLondon HeliportEGLW这道题同时印证了作业开头的设计说明正因为伦敦有多座机场才需要两张表 外键的建模方式单表方案在这里会产生大量重复数据。深入原理JOIN 的执行方式与书写细节讲义 2-Working-With-Data/05-relational-databases/README.md 用降雨量示例详细演示了INNER JOIN的分步写法其模式与本作业第 3、4 题完全一致SELECT cities.city, rainfall.amount FROM cities INNER JOIN rainfall ON cities.city_id rainfall.city_id WHERE rainfall.year 2019;在编写 JOIN 查询时有几点值得注意连接条件的本质ON子句描述哪一列与哪一列相等本作业即ON Cities.id Airports.city_id。因为两侧列名不同无需别名即可表达若两张表存在同名列如讲义示例中的city_id同时存在于两张表则必须用表名.列名的全限定写法消除歧义SQL 大小写SQL 关键字大小写不敏感select与SELECT等价但表名、列名在不同数据库中可能区分大小写。通用最佳实践是关键字一律大写、标识符保持与建表语句一致、日常编程一律按大小写敏感对待INNER JOIN 的过滤行为如前面Newcastle Aerodrome的例子所示内连接只保留匹配成功的行。若想保留没有机场的城市或没有城市的机场需要改用LEFT JOIN左外连接它会把左表所有行保留下来右表无匹配时以 NULL 填充。扩展实战在作业之外继续探索 airports.db完成四道必做题后可以用下面的进阶查询进一步巩固对关系数据库的理解全部语句均可直接在 VS Code 的查询窗口中运行按城市统计机场数量找出一城多场的城市SELECT Cities.city, COUNT(Airports.id) AS airport_count FROM Cities INNER JOIN Airports ON Cities.id Airports.city_id GROUP BY Cities.city HAVING COUNT(Airports.id) 1 ORDER BY airport_count DESC;真实结果会显示 London6 座、Belfast2 座、Bristol2 座、Glasgow2 座、Leeds2 座、Manchester2 座。统计每个国家的机场数量SELECT Cities.country, COUNT(Airports.id) AS airport_count FROM Cities INNER JOIN Airports ON Cities.id Airports.city_id GROUP BY Cities.country;找出没有匹配城市的孤儿机场对比内连接与外连接SELECT Airports.name, Airports.code FROM Airports LEFT JOIN Cities ON Airports.city_id Cities.id WHERE Cities.id IS NULL;真实结果恰好 1 行Newcastle AerodromeEINC其city_id为空。评分标准与自检清单作业在 translations/da/2-Working-With-Data/05-relational-databases/assignment.md 末尾给出了三档评分维度优秀Exemplary合格Adequate需要改进Needs Improvement四道查询全部正确返回预期信息部分查询正确或查询能运行但有瑕疵查询无法运行或结果明显错误对照上面给出的真实结果可做自检第 1 题应返回 170 个城市名第 2 题应返回 16 个爱尔兰城市第 3 题应返回 180 行机场名 城市 国家若返回 181 行或缺少列请检查 JOIN 条件与外键方向第 4 题应精确返回上述 6 座伦敦机场若多了其他机场请确认WHERE同时限定了city与country。本作业与课程讲义配套使用讲义中对主键、外键、SELECT、WHERE与INNER JOIN的完整推导过程可回看 2-Working-With-Data/05-relational-databases/README.md整个处理数据单元从关系数据库、非关系数据库、Python 入门到数据准备依次递进后续课程内容位于 2-Working-With-Data/README.md。完成本作业后你就掌握了关系数据库最核心的建模与查询能力可以继续深入学习 SQLite 扩展文档 中的更多高级查询技巧。【免费下载链接】Data-Science-For-Beginners10 Weeks, 20 Lessons, Data Science for All!项目地址: https://gitcode.com/GitHub_Trending/da/Data-Science-For-Beginners创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考