SQLite 关系数据库查询实战：使用 VS Code 与 SQL 查询机场数据库（Data-Science-For-Beginners 第 5 课实验）

SQLite 关系数据库查询实战使用 VS Code 与 SQL 查询机场数据库Data-Science-For-Beginners 第 5 课实验【免费下载链接】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 部分数据处理中第 5 课《关系数据库》的配套实验展开该实验以仓库内置的 SQLite 示例数据库 airports.db 为核心任务是使用 Visual Studio Code 的 SQLite 扩展编写 SQL 查询展示英国与爱尔兰各城市的机场信息。读完本文你将掌握在 VS Code 中打开并查询 SQLite 数据库的完整操作流程、读懂主键/外键构成的两表关系模型以及独立编写SELECT、WHERE与INNER JOIN组合查询的能力。实验任务书与讲义均有德语与英文双版本分别位于 德语译文 与 英文原版。实验背景与目标本实验是 第 5 课讲义德语版见 translations/de/2-Working-With-Data/05-relational-databases/README.md的课后实践环节。讲义部分重点阐述了三个前提概念本实验正是对它们的直接应用表Table关系数据库以表格为核心行存放数据、列描述数据主键与关系拆分为避免单表存储导致的重复数据例如同一城市名反复出现和结构僵化例如按年份新增列需要把数据拆分到多张表并用主键Primary Key简称 PK唯一标识每一行外键与连接Join子表中存放引用父表主键的外键Foreign Key简称 FK列查询时通过INNER JOIN把多张表缝合回来。讲义用城市 年降水量的例子演示了同样的建模思路而本实验把这一思路换成了更贴近生活的场景——一座城市拥有多个机场如伦敦就有希思罗、盖特威克等多个机场因此机场信息需要单独建表通过外键city_id关联回城市表。环境准备安装 VS Code 与 SQLite 扩展实验要求使用图形化工具与 SQLite 数据库交互而非命令行。共两步安装 Visual Studio Code前往 VS Code 官方网站下载并安装原任务书提供了官网跳转链接此处从略安装 SQLite 扩展在 VS Code 扩展市场中搜索并安装SQLite扩展扩展标识alexcvzz.vscode-sqlite。安装完成后VS Code 即可识别.db文件并执行 SQL 语句。提示任务书针对 SQLite 扩展还提供了官方文档链接用于深入学习实际使用中常用的能力包括打开数据库、新建查询窗口与运行查询下面逐一说明。打开数据库三步走操作流程第一步准备数据库文件任务书原始步骤是从 GitHub 下载示例数据库不过该数据库已内置在仓库中路径为 2-Working-With-Data/05-relational-databases/airports.db无需联网即可直接使用也方便你随时对照源码仓库复现本实验。第二步在 SQLite 扩展中打开数据库启动 Visual Studio Code按下CtrlShiftPmacOS 为CmdShiftP打开命令面板输入并选择SQLite: Open database在弹出的选项中点击Choose database from file选中前面准备好的airports.db文件。任务书特别提醒打开数据库后屏幕上不会立即出现可见变化这是正常现象——数据库已被注册到扩展的数据库中继续下一步即可。第三步新建查询窗口并运行 SQL再次按下CtrlShiftP或CmdShiftP输入并选择SQLite: New query创建一个空白查询窗口在查询窗口中输入 SQL 语句后使用CtrlShiftQmacOS 为CmdShiftQ运行该查询结果会显示在下方面板中。至此你已经可以在查询窗口中自由编写并执行任意 SQL 语句了。理解数据库结构Cities 与 Airports 两表模型任务书给出了数据库的逻辑结构直接阅读airports.db内部的建表语句可以验证其真实实现。通过 SQLite 的sqlite_master系统表可以看到两张业务表实际的CREATE TABLE定义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) );两张表的逻辑字段如下Cities城市Airports机场idPK整数自增idPK整数自增city文本name文本country文本code文本如 IATA/ICAO 三/四字码city_idFK引用 Cities 表的id结构要点主键自增两张表的id均声明为INTEGER PRIMARY KEY AUTOINCREMENT由数据库自动生成递增整数无需也不应由业务数据承担标识职责——这正是讲义强调的主键几乎总是自动生成的数字外键约束Airports.city_id通过FOREIGN KEY(city_id) REFERENCES Cities(id)显式声明了对Cities.id的引用关系一对多关系由于一座城市可能拥有多个机场机场表通过重复的city_id值指向同一座城市实现了典型的一对多建模。以本数据库实际数据为例城市Londonid28名下挂接了 6 个机场数据规模实测Cities表包含 170 行城市数据英国为主、爱尔兰 16 个Airports表包含 181 行机场数据。任务一查询全部城市名称第一道题是列出Cities表中的所有城市名称这是SELECT ... FROM ...最基础的用法SELECT city FROM Cities;语法要点SELECT后面列出要查看的列FROM后面列出列所在的表。执行后返回全部 170 行城市名开头几条为Belfast、Enniskillen、Londonderry、Birmingham、Coventry等。任务二筛选爱尔兰的所有城市第二道题引入筛选条件只返回爱尔兰Ireland的城市用到WHERE子句SELECT city FROM Cities WHERE country Ireland;WHERE表示满足某条件才显示。注意 SQL 中字符串字面量使用单引号Ireland包裹。实测结果为 16 个城市Cork、Galway、Dublin、Connaught、Kerry、Casement、Shannon、Sligo、Waterford、Dongloe、Leixlip、Inis Mor、Indreabhan、Inisheer、Inishmaan、Bantry。任务三连接两表查询机场及其所在城市与国家第三道题要求同时输出机场名称、所在城市和所在国家信息分散在Airports与Cities两张表中必须进行连接SELECT Airports.name, Cities.city, Cities.country FROM Airports INNER JOIN Cities ON Airports.city_id Cities.id;连接Join的原理正如讲义所述在两表之间制造一条接缝用一列把两边的行对应起来。这里用机场表的city_id去匹配城市表的id即外键对主键从而为每个机场找回它所属的城市名称与国家。本实验采用的是INNER JOIN内连接只要某行在另一张表中找不到匹配项就不会出现在结果里。实测返回 181 行样例输出namecitycountryBelfast International AirportBelfastUnited KingdomSt Angelo AirportEnniskillenUnited KingdomGeorge Best Belfast City AirportBelfastUnited Kingdom注意一个书写细节多表查询时用表名.列名如Airports.name明确指明列归属避免两表存在同名列时产生歧义。任务四查询伦敦英国的所有机场最后一道题在连接的基础上叠加筛选条件只保留英国伦敦的机场SELECT Airports.name FROM Airports INNER JOIN Cities ON Airports.city_id Cities.id WHERE Cities.city London AND Cities.country United Kingdom;由于数据库中爱尔兰没有名为 London 的城市这里同时限定城市名与国名以精确命中。实测返回伦敦的 6 个机场London Heathrow AirportEGLLLondon Gatwick AirportEGKKLondon City AirportEGLCLondon Luton AirportEGGWLondon Stansted AirportEGSSLondon HeliportEGLW这道题完整串起了本课的全部核心语法SELECT选择列、INNER JOIN ... ON建立两表连接、WHERE ... AND叠加过滤条件正是讲义末尾连接数据一节的实战化版本讲义中的降水量例子使用INNER JOIN rainfall ON cities.city_id rainfall.city_id再加WHERE rainfall.year 2019与本实验第四问结构完全同构。进阶观察数据中的边界情况在完成任务的基础上结合真实数据可以验证两个有价值的细节1. 内连接会丢弃无匹配的行。实测Airports表中存在一条city_id为NULL的记录——Newcastle Aerodrome代码EINC。由于它没有指向任何城市在任务三与任务四的INNER JOIN结果中均不会出现。这恰好用真实数据印证了讲义对 INNER JOIN 的定义如果某些行与另一张表的任何行都不匹配它们就不会被显示。若想保留无匹配行则需要换用LEFT JOIN这属于后续可自行探索的进阶话题。2. 一对多关系在结果中直观可见。城市Manchesterid14对应Manchester Airport与City Airport Manchester两个机场Londonid28对应 6 个机场。这说明机场表的city_id反复引用同一城市主键正是拆分表结构后避免数据重复的价值所在——城市名称在Cities表中只存储一份。评分标准任务书末尾给出了可对照自评的评分量表优秀Exemplary合格Adequate待改进Needs Improvement正确完成全部四道查询任务且能正确使用连接与筛选完成部分查询任务基本语法正确无法编写正确查询或结果错误建议自测时额外检查查询 2 是否漏掉条件、查询 3/4 是否正确使用了INNER JOIN且连接字段无误Airports.city_id Cities.id、查询 4 是否同时限定了城市名与国家。实验小结通过本实验你完成了一次完整的关系数据库实战闭环从安装 VS Code 与 SQLite 扩展、用图形化方式打开.db文件到读懂主键/外键构成的两表 schema再到用SELECT、WHERE、INNER JOIN三类语句组合出四道查询。这些能力与后续课程中基于 DataFrame 的数据处理一脉相承——正如讲义所提示DataFrame 虽不使用主键术语其按索引/键合并数据的思路与关系数据库的主外键连接如出一辙。若想继续深入仓库中的 第 5 课讲义英文 提供了表设计、关系拆分与连接语句的完整推导过程是理解本实验背后原理的最佳配套阅读材料。【免费下载链接】Data-Science-For-Beginners10 Weeks, 20 Lessons, Data Science for All!项目地址: https://gitcode.com/GitHub_Trending/da/Data-Science-For-Beginners创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考