Data-Science-For-Beginners 第 05 课实战：用 VS Code SQLite 扩展查询 airports.db 机场数据库

Data-Science-For-Beginners 第 05 课实战用 VS Code SQLite 扩展查询 airports.db 机场数据库【免费下载链接】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 课程第 05 课Relational Databases关系型数据库的课后作业 assignment.md法语版对应英文版 assignment.md展开围绕仓库自带的示例数据库 airports.db完整讲解从安装 Visual Studio Code 的 SQLite 扩展、打开数据库、查看表结构到编写SELECT与JOIN查询返回城市/机场信息的全部实操过程。读完并动手完成后你将掌握在 VS Code 中操作 SQLite 单文件数据库的完整工作流并能独立写出带WHERE过滤与内连接INNER JOIN的 SQL 查询。为什么用“两张表 外键”来建模机场数据在动手查询之前先理解这份示例数据库的设计意图这是理解后续所有查询的前提。关系型数据库的核心思想是把信息拆分散落在多张表中再通过关系外键把它们拼回来看——第 05 课正文用“城市 多年份降雨量”的例子解释了为什么单表存储会导致数据冗余要么每行重复城市名与国家名要么每新增一年就要改表结构。拆表之后子表只保存指向主表的编号即可。airports数据库正是这一思想的落地因为一座城市可能拥有多个机场例如伦敦有 6 个所以数据被拆成Cities和Airports两张表机场表通过city_id外键指回城市表。从 airports.db 文件的真实建表语句sqlite_master中的 DDL可以看到其精确结构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) );可以看到两张表都使用AUTOINCREMENT自增整数主键Cities.city与Cities.country带有NOT NULL约束而Airports.city_id允许为NULL——这个细节会在后面讲解 JOIN 结果行数时用到。环境准备VS Code SQLite 扩展作业给出的工具链是Visual Studio Code SQLite 扩展Marketplace 上的 alexcvzz.vscode-sqlite 扩展它允许你在编辑器里直接打开.db文件并执行 SQL。按原作业步骤配置系统到 Visual Studio Code 官网按指引安装 VS Code在扩展市场Marketplace中搜索并安装SQLite扩展。关于该扩展的更多用法可直接查看扩展自带的文档页面。下载并打开 airports.db作业要求先把示例数据库拿到本地。由于本仓库已经自带该文件直接使用仓库内的 airports.db 即可无需另行下载。后续步骤将airports.db文件所在目录用 Visual Studio Code 打开按Ctrl-Shift-PmacOS 为Cmd-Shift-P打开命令面板输入SQLite: Open database选择Choose database from file打开airports.db文件数据库打开后界面不会有明显变化这是正常现象再按Ctrl-Shift-P/Cmd-Shift-P输入SQLite: New query新建一个查询窗口在新查询窗口中即可编写并执行 SQL执行查询的快捷键是Ctrl-Shift-QmacOS 为Cmd-Shift-Q。数据库模式Schema速览作业文档给出的模式说明如下Citiesid (PK, integer)city (text)country (text)Airportsid (PK, integer)name (text)code (text)city_id (FK, 引用 Cities.id)其中 PK 为主键Primary KeyFK 为外键Foreign Key。对照上文的真实 DDL文档描述与数据库文件完全一致。打开数据库后你可以用下面两条语句自行验证模式这也是排查“表叫什么、列叫什么”最可靠的方式-- 查看数据库中所有表 .tables -- 查看某张表的建表语句 .schema Cities .schema Airports实际数据规模可直接查询验证SELECT COUNT(*) FROM Cities; -- 170其中英国United Kingdom154 个爱尔兰Ireland16 个 SELECT COUNT(*) FROM Airports; -- 181Cities表覆盖英国与爱尔兰的城市例如 Belfastid2、Manchester、Dublin 等Airports表中的code为 ICAO 四字代码如 EGAABelfast International Airport、EGLLLondon Heathrow Airport。作业任务四条查询逐一实现原作业要求“Create queries to return the following information”Cities表中所有城市名Cities表中所有爱尔兰城市所有机场名称及其所在城市与国家英国伦敦的所有机场。下面给出每条任务的完整 SQL 与经数据库实际执行验证过的预期结果。任务 1所有城市名最直接的单表查询——SELECT指定列、FROM指定表SELECT city FROM Cities;执行后返回 170 行例如 Belfast、Enniskillen、Londonderry、Birmingham、Manchester 等。注意 SQL 关键字不区分大小写但表名、列名在 SQLite 中按定义区分大小写建议表名保持Cities/Airports原样书写。任务 2所有爱尔兰城市需要过滤条件使用WHERE子句按country列精确匹配SELECT city, country FROM Cities WHERE country Ireland;返回 16 行Cork、Galway、Dublin、Shannon、Sligo、Waterford 等。WHERE子句支持、、、LIKE等谓词这里用的是等值匹配字符串字面量用单引号包裹。任务 3机场名称 城市 国家两表内连接机场信息name、code在Airports表而城市与国家在Cities表必须用INNER JOIN把两张表在city_id这条“接缝”上拼起来SELECT Airports.name, Cities.city, Cities.country FROM Airports INNER JOIN Cities ON Airports.city_id Cities.id;结果共 180 行示例namecitycountryBelfast International AirportBelfastUnited KingdomGeorge Best Belfast City AirportBelfastUnited KingdomBirmingham International AirportBirminghamUnited KingdomLondon Heathrow AirportLondonUnited Kingdom有一个值得注意的“坑”Airports表有 181 行而本查询只返回 180 行。原因是存在一行Newcastle Aerodrome代码 EINC的city_id为NULL在 INNER JOIN 语义下它无法与Cities表中任何id匹配因此被排除——这正是内连接“只保留两表都能匹配上的行”的定义。若想确认这类“孤儿”记录可以用-- 查找无法关联到任何城市的机场结果EINC Newcastle Aerodrome1 行 SELECT a.name, a.code FROM Airports a LEFT JOIN Cities c ON a.city_id c.id WHERE c.id IS NULL;任务 4英国伦敦的所有机场在任务 3 的基础上追加WHERE过滤同时限定城市名与国家避免未来数据中出现同名城市时产生歧义SELECT Airports.name, Airports.code, Cities.city, Cities.country FROM Airports INNER JOIN Cities ON Airports.city_id Cities.id WHERE Cities.city London AND Cities.country United Kingdom;执行后返回 6 行伦敦的机场一览namecodeLondon City AirportEGLCLondon Gatwick AirportEGKKLondon Heathrow AirportEGLLLondon HeliportEGLWLondon Luton AirportEGGWLondon Stansted AirportEGSS这条查询也印证了作业文档中“因为一座城市可能有多个机场所以拆成两张表”的设计动机——伦敦恰好是“一城多港”的典型。评分标准Rubric原作业采用三档评分框架assignment.md 中的 Rubric 表头Exemplary优秀Adequate合格Needs Improvement待改进四条查询均正确、语句简洁、JOIN/WHERE 使用规范查询能返回正确结果但写法可简化结果不正确或语法不通对照上文已验证的结果行数即可自检任务 1 应为 170 行、任务 2 为 16 行、任务 3 为 180 行、任务 4 为 6 行。行数不符通常意味着过滤条件写错例如任务 4 漏掉country条件、任务 3 误用了CITIES.id之外的关联列。小结与后续学习本文完整覆盖了第 05 课作业的全部内容从 VS Code SQLite 扩展的环境配置、命令面板操作SQLite: Open database/SQLite: New query/ Ctrl-Shift-Q 执行、Cities与Airports的表结构与真实 DDL到四条SELECT查询的可复制实现与经验证的预期结果并顺带澄清了 INNER JOIN 对NULL外键行的过滤行为。下一步建议重读 第 05 课正文巩固主键PK、外键FK与INNER JOIN的概念推导在查询窗口中把INNER JOIN换成LEFT JOIN重跑任务 3观察Newcastle Aerodrome一行以NULL城市出现直观对比两种 JOIN 的差异继续 第 06 课学习 JSON、电子表格等非关系型数据形态与本课的表结构模型形成对照。【免费下载链接】Data-Science-For-Beginners10 Weeks, 20 Lessons, Data Science for All!项目地址: https://gitcode.com/GitHub_Trending/da/Data-Science-For-Beginners创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考