前言FDE系列内容总纲【大纲】FDE 前沿部署工程师学习系列教程-CSDN博客前置课程列表见文档结尾附录。阶段2·Day 32多表查询 — JOIN 与聚合FDE 学习系列教程 · 第二阶段 · 第 3 周 · Day 2预计时长3 小时 | 难度★★★☆☆ | 前置知识Day 31 单表增删改查一句话目标搞懂为什么要拆表、什么是主键外键熟练用 INNER JOIN / LEFT JOIN 关联多张业务表用 GROUP BY 聚合函数做分组统计。‍‍ 开场为什么不能把数据全塞一张表回忆昨天的devices表里面有个owner字段存着李工这种文字。图省事是吧但客户的真实系统绝不会这么设计。为什么来看个翻车现场。假设你把设备和负责人信息全塞一张表❌ 全塞一张表反范式设计 devices 表 | id | 设备名 | 负责人 | 手机号 | 车间 | |----|----------|--------|------------|--------| | 1 | 注塑机A1 | 李工 | 138xxx | 一车间 | | 2 | 注塑机A2 | 李工 | 138xxx | 一车间 | ← 李工信息重复 3 遍 | 3 | 注塑机A3 | 李工 | 138xxx | 一车间 |发现问题了吗同一个李工名字、手机号、车间被抄了 3 份他管 30 台设备就抄 30 份。这会引发三大灾难改不动李工换手机号了你得改 30 条记录漏改 1 条数据就对不上存得乱同一手机号可能存成 138 xxx、138-xxx、138xxx 三种格式占空间大量重复信息纯浪费正确做法是拆表——各存各的用编号关联✅ 拆成两张表范式化设计 engineers工程师表 devices设备表 | id | 名字 | 手机号 | 车间 | | id | 设备名 | engineer_id | |----|------|--------|--------| |----|---------|-------------| | 1 | 李工 | 138xxx | 一车间 | | 1 | 注塑机A1 | 1 | | 2 | 王工 | 139xxx | 二车间 | | 2 | 注塑机A2 | 1 | | 3 | 注塑机A3 | 2 | 改手机号只改 engineers 表 id1 这一处30 台设备自动跟着变。 查数据设备表只有编号 1名字要去工程师表找——这就需要 JOIN。 一句话拆表是为了一处修改处处生效JOIN 是为了查询时把拆开的数据再拼回来。 一、主键、外键表和表之间的桥这里要拐个弯了稍微费点脑子但摸透了后面处处通透。┌────────────────────────────────────────────────────────────┐ │ 主键Primary Key │ │ · 一张表里唯一标识每一行的列通常叫 id │ │ · 不能重复、不能为空 │ │ · 相当于一个人的身份证号 │ │ │ │ 外键Foreign Key │ │ · 一张表里指向另一张表主键的列 │ │ · devices.engineer_id 就是外键指向 engineers.id │ │ · 它是表与表之间的桥 │ └────────────────────────────────────────────────────────────┘ engineers 表 devices 表 ┌────┬──────┐ ┌────┬──────────┬─────────────┐ │ id │ name │ ◄──── 主键 │ id │ name │ engineer_id │ ──┐ ├────┼──────┤ ├────┼──────────┼─────────────┤ │ │ 1 │ 李工 │ │ 1 │ 注塑机A1 │ 1 ●───┼───┘ │ 2 │ 王工 │ │ 2 │ 注塑机A2 │ 1 ●───┼──┐ └────┴──────┘ │ 3 │ 注塑机A3 │ 2 ●───┼──┤ ▲ └────┴──────────┴─────────────┘ │ └────────────────────────── 外键指向主键 ───────────────────────┘表关系的三种形态FDE 看客户的 ER 图时会遇到关系说法例子一对多1:N一个工程师管多台设备工程师 1 ── 设备 N一对一1:1一个员工对应一份档案用户 1 ── 1 身份证多对多M:N一个学生选多门课一门课多个学生需要中间表拆成两个 1:N 现实中90% 的关联是一对多今天的三张表就是这种。多对多不用慌它本质是两张一对多 一张中间表比如工单和标签之间加一张ticket_tags关联表。️ 二、实操建三张关联表并插数据今天我们搭一个迷你工厂管理系统工程师表、设备表、工单表。这三张表是 FDE 最常见的数据形态。新建04_multi_table.py 建三张关联表engineers / devices_new / tickets 文件04_multi_table.py import sqlite3 conn sqlite3.connect(fde_workshop.db) cur conn.cursor() # ① 工程师表 cur.execute( CREATE TABLE IF NOT EXISTS engineers ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, phone TEXT, department TEXT, title TEXT DEFAULT 工程师 ) ) # ② 设备表用 engineer_id 外键关联工程师 cur.execute(DROP TABLE IF EXISTS devices_new) # 重建避免昨天结构干扰 cur.execute( CREATE TABLE devices_new ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, type TEXT NOT NULL, temperature REAL DEFAULT 0, vibration REAL DEFAULT 0, is_running INTEGER DEFAULT 1, engineer_id INTEGER, created_at TEXT DEFAULT (datetime(now,localtime)), FOREIGN KEY (engineer_id) REFERENCES engineers(id) ) ) # ③ 工单表一张工单关联一台设备 一个上报人 cur.execute( CREATE TABLE IF NOT EXISTS tickets ( id INTEGER PRIMARY KEY AUTOINCREMENT, device_id INTEGER NOT NULL, title TEXT NOT NULL, priority TEXT DEFAULT 中, status TEXT DEFAULT 待处理, reporter_id INTEGER, created_at TEXT DEFAULT (datetime(now,localtime)), FOREIGN KEY (device_id) REFERENCES devices_new(id), FOREIGN KEY (reporter_id) REFERENCES engineers(id) ) ) # 插 4 个工程师 cur.executemany( INSERT INTO engineers (name, phone, department, title) VALUES (?,?,?,?), [(李工,13800001111,一车间,高级工程师), (王工,13800002222,二车间,工程师), (赵工,13800003333,一车间,工程师), (陈工,13800004444,二车间,实习生)] ) # 插 5 台设备最后一列是 engineer_id cur.executemany( INSERT INTO devices_new (name,type,temperature,vibration,is_running,engineer_id) VALUES (?,?,?,?,?,?) , [ (注塑机A1,注塑,65, 8,1,1), (注塑机A2,注塑,82,12,1,2), (注塑机A3,注塑,91,16,1,3), (冲压机B1,冲压,75, 9,0,1), (冲压机B2,冲压,88, 6,1,4), ]) # 插 5 张工单设备id, 标题, 优先级, 状态, 上报人id cur.executemany( INSERT INTO tickets (device_id,title,priority,status,reporter_id) VALUES (?,?,?,?,?) , [ (1,温度偏高,高,处理中,3), (3,温度严重超标,高,处理中,3), (2,振动异常,中,待处理,2), (5,例行保养,低,已解决,1), (3,振动超标,中,待处理,2), ]) conn.commit() for t in [engineers,devices_new,tickets]: cur.execute(fSELECT COUNT(*) FROM {t}) print(f {t:12s}{cur.fetchone()[0]} 条) conn.close() print(✅ 三张表建好数据已插入)运行python 04_multi_table.pyengineers 4 条 devices_new 5 条 tickets 5 条 ✅ 三张表建好数据已插入三张表的关系图务必看懂这个engineers工程师 ┌────┬──────┬────────┐ │ id │ name │ 车间 │ │ 1 │ 李工 │ 一车间 │ │ 2 │ 王工 │ 二车间 │ │ 3 │ 赵工 │ 一车间 │ │ 4 │ 陈工 │ 二车间 │ └─▲──┴──────┴────────┘ │ ┌─────────────────────┐ │ reporter_id上报人 │ tickets工单 │ └──────────────────────────│ id │ │ device_id ──┐ │ │ title │ │ │ priority │ │ │ status │ │ └─────────────┼────────┘ │ engineers工程师 │ device_id ┌────┬──────┐ │ │ id │ name │◄──── engineer_id ───────────┤ └────┴──────┘ ▼ ┌──────────────────────────────────────┐ │ devices_new设备 │ │ id │ name │ type │ temperature │ │ 1 │ 注塑机A1 │ 注塑 │ 65 │ │ 3 │ 注塑机A3 │ 注塑 │ 91 │ └──────────────────────────────────────┘ 读法 · 一台设备属于一个工程师devices.engineer_id → engineers.id · 一张工单挂在一台设备上tickets.device_id → devices.id · 一张工单有一个上报人tickets.reporter_id → engineers.id️ 三、INNER JOIN两边都匹配上的才保留需求查每台设备的名称和它的负责人姓名。设备表只有engineer_id编号 1、2、3名字得去工程师表捞——JOIN 登场。在 DBeaver 的 SQL 编辑器里写推荐或新建05_join.pySELECT d.name AS 设备名, d.temperature AS 温度, e.name AS 负责人, e.department AS 车间 FROM devices_new d INNER JOIN engineers e ON d.engineer_id e.id;结果设备名 | 温度 | 负责人 | 车间 注塑机A1 | 65.0 | 李工 | 一车间 注塑机A2 | 82.0 | 王工 | 二车间 注塑机A3 | 91.0 | 赵工 | 一车间 冲压机B1 | 75.0 | 李工 | 一车间 冲压机B2 | 88.0 | 陈工 | 二车间把 JOIN 语法拆开揉碎SELECT d.name, e.name -- 要哪些列用 别名.列名 区分 FROM devices_new d -- 主表起别名 dAS 可省略 INNER JOIN engineers e -- 要拼接的表起别名 e ON d.engineer_id e.id; -- 拼接条件编号对得上 └────────────────────┘ 这座桥在哪逐块理解部分作用FROM devices_new d以设备表为主顺手起个短名dINNER JOIN engineers e把工程师表e拼进来ON d.engineer_id e.id拼接规则设备表的外键 工程师表的主键AS 设备名给结果列起中文名仅显示用为什么要起别名两张表都有id、name列不写表名数据库分不清你要哪个。d.name表示设备表的 namee.name表示工程师表的 name一目了然。INNER JOIN 图示交集devices设备 engineers工程师 ┌─────────────────┐ ┌─────────────────┐ │ A1 → engineer 1 │────────────│ id 1 李工 │ │ A2 → engineer 2 │────────────│ id 2 王工 │ │ A3 → engineer 3 │────────────│ id 3 赵工 │ │ B9 → engineer 9 │ ✗ │ id 4 陈工 │ ◄── B9 指向的 9 不存在 └─────────────────┘ └─────────────────┘ │ ▼ INNER JOIN 结果 只有桥两边都接上的行才保留 B9 因为找不到 id9直接消失INNER JOIN 取交集两边能对上号的行才出现对不上的统统丢掉。️ 四、LEFT JOIN左边全保留右边没有的补 NULL换个需求找出一张工单都没有的设备。用 INNER JOIN 能做到吗做不到——INNER JOIN 会把没有工单的设备丢掉而那恰恰是你要找的。这时候用LEFT JOINSELECT d.name AS 设备名, t.title AS 工单标题 FROM devices_new d LEFT JOIN tickets t ON t.device_id d.id;先看这个查询的完整结果左表设备全部保留设备名 | 工单标题 注塑机A1 | 温度偏高 注塑机A2 | 振动异常 注塑机A3 | 温度严重超标 注塑机A3 | 振动超标 ← A3 有 2 张工单所以出现 2 行 冲压机B1 | NULL ← B1 没有工单右表补 NULL 冲压机B2 | 例行保养再加一个 WHERE把右表为 NULL 的捞出来就是答案SELECT d.name AS 没有工单的设备 FROM devices_new d LEFT JOIN tickets t ON t.device_id d.id WHERE t.id IS NULL; -- 右表拼不上为 NULL的没有工单的设备 冲压机B1INNER vs LEFT 一图看懂INNER JOIN内连接 LEFT JOIN左连接 左表 右表 左表 右表 ┌───┐ ┌───┐ ┌───┐ ┌───┐ │ A │ ●────● a │ │ A │ ●────● a │ │ B │ ●────● b │ │ B │ ●────● b │ │ C │ ✗ └───┘ │ C │ ● NULL │ ← C 保留右边补空 └───┘ └───┘ 结果A、B2行 结果A、B、C3行 对不上的两边都不保留 左表全保留右边对不上填 NULL经典套路记牢LEFT JOIN 右表 ... WHERE 右表.主键 IS NULL专门用来回答找出没有 XXX 的 YYY没有工单的设备没有下过单的客户没有员工的部门从没被借阅过的图书 还有个 RIGHT JOIN右连接右边全保留实际极少用——把表位置一换LEFT JOIN 就能实现所以业界基本只写 LEFT JOIN。️ 五、三表 JOIN一条 SQL 串起工单、设备、人真实报表常要工单标题 设备名 上报人姓名信息分散在三张表。别怕这个概念听着唬人拆开看就那么回事——三表 JOIN 就是做两次两表 JOIN。SELECT t.id AS 工单号, t.title AS 标题, t.priority AS 优先级, t.status AS 状态, d.name AS 设备名, e.name AS 上报人 FROM tickets t INNER JOIN devices_new d ON t.device_id d.id -- 第1次拼工单→设备 INNER JOIN engineers e ON t.reporter_id e.id -- 第2次拼工单→工程师 ORDER BY t.priority DESC, t.created_at DESC;结果工单号 | 标题 | 优先级 | 状态 | 设备名 | 上报人 1 | 温度偏高 | 高 | 处理中 | 注塑机A1 | 赵工 2 | 温度严重超标 | 高 | 处理中 | 注塑机A3 | 赵工 3 | 振动异常 | 中 | 待处理 | 注塑机A2 | 王工 5 | 振动超标 | 中 | 待处理 | 注塑机A3 | 王工 4 | 例行保养 | 低 | 已解决 | 冲压机B2 | 李工执行逻辑数据库先拿工单表按device_id拼上设备名再按reporter_id拼上上报人姓名。每次 JOIN 都是在加列。⚠️多表 JOIN 避坑SELECT 里的列名最好都带上表别名t.id而不是id因为三张表里可能有同名列比如都有 id、created_at不写清楚数据库会报ambiguous column列名有歧义。️ 六、聚合函数 GROUP BY分组统计JOIN 解决拼列GROUP BY 解决合并行做统计。需求按车间统计设备数量和平均温度。一个车间有好几台设备我们想每个车间只出一行统计数字SELECT e.department AS 车间, COUNT(*) AS 设备数, ROUND(AVG(d.temperature), 1) AS 平均温度, MAX(d.temperature) AS 最高温度, MIN(d.temperature) AS 最低温度 FROM devices_new d INNER JOIN engineers e ON d.engineer_id e.id GROUP BY e.department;结果车间 | 设备数 | 平均温度 | 最高温度 | 最低温度 一车间 | 3 | 77.0 | 91.0 | 65.0 二车间 | 2 | 85.0 | 88.0 | 82.0聚合函数全家福函数作用例子COUNT(*)统计行数包括 NULL这个车间有几台设备COUNT(列名)统计该列非 NULL的行数有几个人填了手机号SUM(列)求和所有设备振动值总和AVG(列)平均值平均温度MAX(列)/MIN(列)最大 / 最小值最高温 / 最低温ROUND(值, 1)是保留 1 位小数纯粹让结果好看。COUNT(*)和COUNT(列)的区别是个高频面试点前者数所有行后者只数该列不是 NULL 的行。GROUP BY 的行→组变化分组前每台设备一行 GROUP BY department 后每车间一行 设备 车间 温度 车间 COUNT AVG 注塑机A1 一车间 65 一车间 3 77.0 冲压机B1 一车间 75 ───► 二车间 2 85.0 注塑机A3 一车间 91 注塑机A2 二车间 82 冲压机B2 二车间 88 5 行被压扁成 2 行每行为一组的统计值⚠️GROUP BY 黄金规则SELECT 后面除了聚合函数包起来的列其他列必须出现在 GROUP BY 里。SELECT department, name, COUNT(*) ... GROUP BY department是错的——一个车间有 3 个设备名数据库不知道该显示哪个。HAVING分组之后再筛选需求找出工单数量 ≥ 2 的设备。注意过滤条件是分组后的统计结果这时候 WHERE 不够用了WHERE 在分组之前执行得用HAVINGSELECT d.name AS 设备名, COUNT(t.id) AS 工单数 FROM devices_new d LEFT JOIN tickets t ON t.device_id d.id GROUP BY d.id, d.name HAVING COUNT(t.id) 2; -- 分组后只留工单数≥2的组设备名 | 工单数 注塑机A3 | 2WHERE vs HAVING最容易混的点┌────────────────────────────────────────────────────────┐ │ WHERE → 分组之前过滤行针对原始的每一行 │ │ HAVING → 分组之后过滤组针对聚合后的统计结果 │ └────────────────────────────────────────────────────────┘ 例子在温度60的设备里统计每个车间的设备数只看数量≥2的车间 SELECT department, COUNT(*) FROM devices WHERE temperature 60 ← 先把温度≤60的行踢掉 GROUP BY department HAVING COUNT(*) 2; ← 分组后把数量2的车间踢掉SQL 执行顺序理解这个从此不写错你书写的顺序 ≠ 数据库执行的顺序你写的顺序 数据库实际执行顺序 ───────────── ───────────────────── SELECT ... ⑤ ① FROM JOIN 先确定从哪些表拼数据 FROM ... ① ② WHERE 逐行过滤 JOIN ... ① ③ GROUP BY 分组 WHERE ... ② ④ HAVING 过滤分组 GROUP BY ... ③ ⑤ SELECT 确定输出哪些列/聚合 HAVING ... ④ ⑥ ORDER BY 排序 ORDER BY ... ⑥ ⑦ LIMIT 截取前 N 行 LIMIT ... ⑦ 这解释了一个经典坑SELECT 里用AS 平均温度起的别名WHERE 里不能用WHERE 在 ② 执行SELECT ⑤ 还没跑别名还没诞生但 ORDER BY ⑥ 可以用那时别名已存在。踩过一次就记住了。 本课小结知识点一句话记住拆表消除数据重复一处修改处处生效主键一行的唯一身份证通常 id外键指向别的表主键的列是表间的桥INNER JOIN取交集两边匹配上的才保留LEFT JOIN左表全保留右边匹配不上补 NULL三表 JOIN就是连续做两次两表 JOIN聚合函数COUNT / SUM / AVG / MAX / MINGROUP BY按某列分组每组出一行统计HAVING分组后过滤组WHERE 分组前过滤行执行顺序FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY→LIMIT表别名FROM devices d多表时列名要带别名防歧义 核心认知JOIN 是横向拼列把分散在多表的字段拼到一行GROUP BY 是纵向压行把多行压成一行统计。这两个方向搞清楚多表查询就通了。 课后练习在 DBeaver SQL 编辑器里完成先自己想卡壳再翻笔记查每张工单的完整信息工单号、标题、优先级、状态、设备名、上报人姓名三表 JOIN按优先级统计工单数高/中/低各有几张按工程师统计每人负责几台设备、所管设备的平均温度找出有高优先级工单的设备名称提示WHERE priority高找出一张工单都没有的设备LEFT JOIN IS NULL统计每个车间的工单总数只显示工单数 0 的车间按 status 分组统计工单数并按数量从多到少排序思考题如果想查上报工单最多的工程师是谁需要哪几个 JOIN 和聚合试着写出来 下节预告今天的 JOIN GROUP BY 已经能搞定现场 80% 的查询需求了。明天上点硬菜——窗口函数和 CTE。有个经典需求找出每个车间温度最高的 2 台设备。用 GROUP BY 做不到一分组每个车间只剩一行设备明细没了。明天学的窗口函数能做到不合并行但每行都带着它所在组的排名CTE 能把复杂查询拆成清晰的几段。这是从会写 SQL到写得漂亮的分水岭咱们明天见真章附录前置课程列表阶段一【FDE系列】阶段1Day 1AI 层级关系 — 四个嵌套的圈-CSDN博客【FDE系列】阶段1Day 2AI 三阶段发展史 — 会认 → 会判断 → 会创造-CSDN博客【FDE系列】阶段1Day 3符号 AI vs 机器学习 — 两条路线的本质区别-CSDN博客【FDE系列】阶段1Day 4Transformer 的历史意义 — 2017 年的分水岭-CSDN博客【FDE系列】阶段1Day 5本周复习与自测 — 检验你的 AI 认知地基-CSDN博客【FDE系列】阶段1Day 6Transformer 架构 — 一张图纸盖出千千万万栋楼-CSDN博客【FDE系列】阶段1Day 7LLM 本质 — 文字接龙机器-CSDN博客【FDE系列】阶段1Day 8Token — 模型眼中的最小单位-CSDN博客【FDE系列】阶段1Day 9AI 幻觉 — 为什么会一本正经地胡说八道-CSDN博客【FDE系列】阶段1Day 10上下文窗口 — 模型的记忆力上限 本周复习-CSDN博客【FDE系列】阶段1Day 11Prompt — 给模型立规矩-CSDN博客【FDE系列】阶段1Day 12Memory — 让模型记住上下文【FDE系列】阶段1Day 13RAG — 给模型配图书管理员-CSDN博客【FDE系列】阶段1Day 14Tool Use — 让模型动手操作-CSDN博客【FDE系列】阶段1Day 15MCP — 统一的工具接口标准 第三周复习-CSDN博客【FDE系列】阶段1Day 16什么是 FDE — 把 AI 变成客户结果的人-CSDN博客【FDE系列】阶段1Day 17FDE vs 传统实施 — 三大本质区别-CSDN博客【FDE系列】阶段1Day 18FDE 三重身份 C6 胜任力模型-CSDN博客【FDE系列】阶段1Day 19七阶段行动路径 行业经验的价值-CSDN博客【FDE系列】阶段1Day 20阶段总结与产出物 — 第一阶段收官-CSDN博客阶段二【FDE系列】阶段2Day 21Python 环境搭建 — 写出你的第一行代码-CSDN博客【FDE系列】阶段2Day 22变量、数据类型、条件判断 — Python 的“记忆“和“判断“-CSDN博客【FDE系列】阶段2Day 23循环与函数 — 让代码跑 100 遍、把逻辑打包复用-CSDN博客【FDE系列】阶段2Day 24数据结构 — 列表、字典、集合、元组-CSDN博客【FDE系列】阶段2Day 25文件读写与 JSON — 让程序连通外部数据第一周收官-CSDN博客【FDE系列】阶段2Day 26模块化编程 — 把代码拆成“抽屉柜“-CSDN博客【FDE系列】阶段2Day 27异常处理与日志 — 让程序“摔不烂、查得到“-CSDN博客【FDE系列】阶段2Day 28FastAPI 入门 — 把你的函数变成 API 服务-CSDN博客【FDE系列】阶段2Day 29FastAPI 进阶 — Pydantic 模型与完整 CRUD 实战-CSDN博客【FDE系列】阶段2Day 30生产代码规范 — 测试、类型注解、配置管理第二周收官-CSDN博客【FDE系列】阶段2Day 31SQL 基础 — 增删改查一把梭-CSDN博客