当前位置： 首页 > news >正文

MySQL8.0 从建库建表到多表联查实战：手把手吃透内连接、左连接、聚合统计

news 2026/8/8 7:31:54

前言

很多初学 MySQL 的小伙伴，单独写单表增删改查问题不大，一碰到多表关联、JOIN 连接、分组统计、子查询就一头雾水。 本文基于一套完整实战库ai_workspace（用户、项目、技能三张业务表），带着大家从零走完：建库→建表约束→插入测试数据→三种 JOIN 详解→多表联查嵌套→聚合分组统计，全程对应实操命令，踩坑点也会逐一拆解，看完就能上手仿写业务 SQL。 环境：Windows + MySQL 8.0.46 社区版。

整体业务库设计说明

本次搭建简易 AI 开发者工作台数据库，三张核心表关系：

  1. users 用户表：存储开发者基础信息，主键id；
  2. projects 项目表：每个项目归属一个开发者，user_id作为外键关联users.id，一对多关系（一个人多个项目）；
  3. skills 技能表：存储开发者掌握的技术栈，同样通过user_id外键绑定用户。 整体 ER 关系：users(1) ——一对多—— projects(n)、users(1) ——一对多—— skills(n)。

一、步骤 1：登录 MySQL 并初始化数据库

1.1终端登录 MySQL

# 打开cmd，先验证MySQL环境变量配置 mysql --version # 输入账号密码登录root用户 mysql -u root -p

输入密码后进入 MySQL 命令行客户端，如图中所示成功连接。

二、步骤 1：创建业务数据库并指定字符集

2.1 创建数据库

-- IF NOT EXISTS：数据库不存在才创建，重复执行不会报错 -- ai_workspace：自定义数据库名称 -- DEFAULT CHARSET utf8mb4：指定字符集，完整版UTF-8，支持中文、emoji表情，生产环境强制使用 CREATE DATABASE IF NOT EXISTS ai_workspace DEFAULT CHARSET utf8mb4; -- 切换进入当前数据库，后续建表、增删改查全部在该库执行 USE ai_workspace;

执行成功提示：

Query OK, 1 row affected (0.04 sec) Database changed

💡新手避坑：MySQL 原生utf8仅支持 3 字节字符，无法存储完整 emoji、部分生僻中文；utf8mb4才是标准完整版 UTF-8，开发项目统一选用。

三、步骤 2：三张数据表创建（主键、自增、默认值、外键约束）

3.1 users 用户表

CREATE TABLE users( id INT PRIMARY KEY AUTO_INCREMENT, -- 主键id，自增主键，每条用户记录唯一标识 username VARCHAR(50) NOT NULL, -- 用户名，非空约束，必须填写 email VARCHAR(100), -- 邮箱，允许为空 role VARCHAR(20) DEFAULT '开发者', -- 角色，默认值为「开发者」，不填自动赋值 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 创建时间，插入数据时自动记录当前时间 );

执行：Query OK, 0 rows affected (0.04 sec)

3.2 projects 项目表（外键关联用户表）

CREATE TABLE projects( id INT PRIMARY KEY AUTO_INCREMENT, -- 项目自增主键 user_id INT NOT NULL, -- 用户id，绑定所属开发者，非空 project_name VARCHAR(100) NOT NULL, -- 项目名称，必填 tech_stack VARCHAR(200), -- 项目技术栈 status VARCHAR(20) DEFAULT '进行中', -- 项目状态，默认「进行中」 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 项目创建时间自动填充 -- 外键约束：user_id 关联 users表的主键id，保证数据合法性，不能绑定不存在的用户 FOREIGN KEY (user_id) REFERENCES users(id) );

执行：Query OK, 0 rows affected (0.05 sec)

3.3 skills 技能表（外键关联用户表）

CREATE TABLE skills( id INT PRIMARY KEY AUTO_INCREMENT, -- 技能记录自增主键 user_id INT NOT NULL, -- 归属用户id skill_name VARCHAR(50) NOT NULL, -- 技能名称，必填 level VARCHAR(20) DEFAULT '入门', -- 技能熟练度，默认入门 FOREIGN KEY (user_id) REFERENCES users(id) -- 外键关联用户表id );

执行：Query OK, 0 rows affected (0.05 sec)

四、步骤 3：批量插入测试业务数据

4.1 插入用户测试数据

-- 批量插入3位开发者数据，字段顺序和表结构一一对应 INSERT INTO users(username, email, role) VALUES ('张琪','zhangqi@example.com','AI开发'), ('李明','liming@example.com','前端开发'), ('王芳','wangfang@example.com','测试工程师');

执行结果：Query OK, 3 rows affected (0.02 sec)

4.2 插入项目数据

-- 为不同用户绑定对应项目，user_id对应用户表主键 INSERT INTO projects(user_id,project_name,tech_stack,status) VALUES (1,'人脸识别系统','Python+dlib+OpenCV','已完成'), (1,'企业AI知识库助手','Python+Flask+小程序','进行中'), (1,'树莓派环境监测','Python+树莓派','已完成'), (2,'公司官网','HTML+CSS+JS','已完成'), (2,'后台管理系统','Vue+ElementUI','进行中'), (3,'自动化测试框架','Python+Selenium','进行中');

执行结果：Query OK, 6 rows affected (0.01 sec)

4.3 插入开发者技能数据

INSERT INTO skills(user_id,skill_name,level) VALUES (1,'Python','熟练'), (1,'Flask','中等'), (1,'OpenCV','中等'), (2,'JavaScript','熟练'), (2,'Vue','熟练'), (3,'Selenium','中等');

执行结果：Query OK, 6 rows affected (0.04 sec)

五、核心重点：三种 JOIN 多表联查实战

5.1 INNER JOIN 内连接（只返回两张表互相匹配的数据）

业务需求：查询所有有项目的开发者 + 对应项目名称、项目状态，没有项目的用户不会展示

SELECT u.username, -- 开发者用户名 p.project_name, -- 项目名称 p.status -- 项目当前状态 FROM users u -- users表起别名u INNER JOIN projects p -- 内连接项目表，起别名p ON u.id = p.user_id; -- 关联条件：用户主键id = 项目所属用户id

执行结果：所有绑定了项目的用户数据全部展示，无项目的用户不会出现。

5.2 LEFT JOIN 左连接（左表数据全部保留，右表无匹配则填充 NULL）

业务需求：展示全部开发者，不管有没有项目都要展示，无项目的项目字段为空

SELECT u.username, p.project_name FROM users u LEFT JOIN projects p -- 左表users全部保留，右表projects匹配不上显示NULL ON u.id = p.user_id;

5.3 RIGHT JOIN 右连接（右表数据全部保留，左表无匹配填充 NULL）

本案例中项目一定归属用户，效果和内连接一致，逻辑：以项目表为基准，所有项目必须展示

SELECT u.username, p.project_name FROM users u RIGHT JOIN projects p ON u.id = p.user_id;

5.4 三表联查：用户 + 项目 + 技能精准筛选

需求：演示三表 INNER JOIN 基础联表语法；查看张琪关联的项目与技能数据。
注意：由于一对多双表联查会生成笛卡尔积，表格里会出现大量重复项目数据，这是语法演示带来的正常现象，真实业务场景建议分开两次查询，或是使用聚合函数整合数据。

SELECT u.username, p.project_name, p.status, s.skill_name, s.level FROM users u JOIN projects p ON u.id = p.user_id -- 用户关联项目 JOIN skills s ON u.id = s.user_id -- 用户关联技能 WHERE u.username = '张琪'; -- 精准筛选指定用户

可以看到同一条项目重复出现多次，本质是每条项目分别匹配了用户的每一项技能，属于笛卡尔积造成的数据冗余，仅作为联表学习示例。

六、GROUP BY + COUNT 分组聚合统计实战

6.1 统计每位开发者名下总项目数量

SELECT u.username, -- 开发者姓名 COUNT(p.id) AS project_count -- 统计项目主键数量，别名project_count FROM users u LEFT JOIN projects p ON u.id = p.user_id -- 左连接保证无项目用户也会统计为0 GROUP BY u.id, u.username; -- MySQL8.0规范：分组字段必须写在SELECT中

本节使用LEFT JOIN统计系统内全部开发者，无论有无项目都会展示；
下一小节 6.2 业务需求发生变更，仅需要筛选本身存在项目的用户，因此切换为INNER JOIN提前剔除无项目人员后，再执行分组筛选。

6.2HAVING 筛选分组后数据：在有项目的开发者里筛选项目数≥2的人员

WHERE过滤原始数据，HAVING过滤分组聚合后的结果

SELECT u.username, COUNT(p.id) AS project_count FROM users u JOIN projects p ON u.id = p.user_id GROUP BY u.id, u.username HAVING project_count >= 2; -- 分组完成后，只保留项目数量大于等于2的用户

6.3 子查询：查询做过「已完成」状态项目的所有开发者

SELECT username FROM users WHERE id IN( -- 子查询：先查出所有状态为已完成项目对应的用户id SELECT DISTINCT user_id FROM projects WHERE status = '已完成' );

6.4 按项目状态分组，统计已完成 / 进行中项目各自总数

SELECT status, -- 项目状态字段 COUNT(*) AS count -- 统计每组内总条数 FROM projects GROUP BY status; -- 根据状态分组统计

七、知识点总结

  1. 建库规范：生产环境统一utf8mb4字符集，IF NOT EXISTS避免重复执行报错；
  2. 外键作用：约束关联数据合法性，防止插入不存在的用户 ID；
  3. 三种 JOIN 区别
    • INNER JOIN：两边表互相匹配的数据；
    • LEFT JOIN：左表全部数据，右表匹配不到为 NULL；
    • RIGHT JOIN：右表全部数据，左表匹配不到为 NULL；
  4. WHERE 和 HAVING：WHERE 聚合前过滤数据，HAVING 聚合分组后过滤统计结果；
  5. GROUP BY 规范：MySQL8.0 严格模式下，SELECT 里非聚合字段必须加入 GROUP BY。

结尾

本篇完整覆盖日常开发高频多表查询场景，新手建议跟着命令一行行实操，理解每张表的关联逻辑后，复杂多表联查就不再晦涩。后续可以基于这套表拓展：分页查询、关联更新、事务、索引优化等进阶内容。

http://www.jsqmd.com/news/1351362/

相关文章：

  • B站直播推流码获取终极指南：告别官方限制，轻松实现专业直播
  • 基于Qt C++的配置文件编辑器：从架构设计到工程实践
  • 深度解析Cursor Pro破解技术原理与风险，探讨开发者工具替代方案
  • 商汤SenseNova公测API实战：从环境配置到深度测试的开发者指南
  • CenterPoint：基于BEV特征图的3D目标检测核心原理与工程实践
  • C语言字符处理核心：空白符、转义字符与标准库函数实战解析
  • 北京管道疏通马桶下水道地漏除臭本地团队全天候快速上门服务（2026最新） - 北京优选
  • 【第二部分：大模型应用开发基础】7.Function Calling：让大模型调用真实程序能力
  • Unity2D界面动画事件失效全解析：从原理到实战解决方案
  • 2026亲测有效教程：交作业图片转PDF用什么工具最省事 - 图片处理研究员
  • 抖音保存无水印图片方法、合规说明与**工具、第三方工具风险全解析 - 免费软件工具方法教程
  • Java面向对象三大特性：封装、继承、多态详解
  • 电偶极子：从物理模型到Python可视化与工程应用
  • Python Pickle反序列化漏洞：绕过WAF黑名单的五种高级技巧
  • STM32 USB开发实战：从协议原理到HID键盘与虚拟串口实现
  • AI绘图提示词结构化指南：从零生成专业景观分析图
  • PSO-SVM模型在电力负荷预测中的应用与优化
  • AI编程Token成本控制：从原理到实战的开发者生存指南
  • 深耕本土与拥抱未来：青冈县网站建设全指南之如何打造高转化率的数字化名片
  • NMOS与PMOS实战指南：从原理到应用，掌握MOS管核心设计
  • Dify 中级实验（04）：迭代进阶——如何批量处理数据并守住性能边界？
  • 2026年常州保鲜冷库厂家，专业冷库安装设计，食品医药冷库工程，冷链仓储设备公司优选 - 优企名品
  • PagedAttention显存管理算法
  • MiniExcel 从入门到实战：.NET 中极速、零依赖处理大数据的终极方案
  • Speechless：5分钟快速掌握微博备份终极方案
  • VC++ MFC彩票模拟器开发：从随机数算法到Windows桌面应用实战
  • SpringBoot动漫商城架构设计与高并发实践
  • Emdash 拆解：多 Agent 并行开发桌面端的实现思路，兼谈 ACP 与 A2A
  • 2026湖州拆除复原毛坯找哪家？优选施工队对比指南 - geo交流
  • Java Selenium自动化破解滑动验证码：从图像识别到轨迹模拟实战