前言很多初学 MySQL 的小伙伴单独写单表增删改查问题不大一碰到多表关联、JOIN 连接、分组统计、子查询就一头雾水。 本文基于一套完整实战库ai_workspace用户、项目、技能三张业务表带着大家从零走完建库→建表约束→插入测试数据→三种 JOIN 详解→多表联查嵌套→聚合分组统计全程对应实操命令踩坑点也会逐一拆解看完就能上手仿写业务 SQL。 环境Windows MySQL 8.0.46 社区版。整体业务库设计说明本次搭建简易 AI 开发者工作台数据库三张核心表关系users 用户表存储开发者基础信息主键idprojects 项目表每个项目归属一个开发者user_id作为外键关联users.id一对多关系一个人多个项目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 (张琪,zhangqiexample.com,AI开发), (李明,limingexample.com,前端开发), (王芳,wangfangexample.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,人脸识别系统,PythondlibOpenCV,已完成), (1,企业AI知识库助手,PythonFlask小程序,进行中), (1,树莓派环境监测,Python树莓派,已完成), (2,公司官网,HTMLCSSJS,已完成), (2,后台管理系统,VueElementUI,进行中), (3,自动化测试框架,PythonSelenium,进行中);执行结果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; -- 根据状态分组统计七、知识点总结建库规范生产环境统一utf8mb4字符集IF NOT EXISTS避免重复执行报错外键作用约束关联数据合法性防止插入不存在的用户 ID三种 JOIN 区别INNER JOIN两边表互相匹配的数据LEFT JOIN左表全部数据右表匹配不到为 NULLRIGHT JOIN右表全部数据左表匹配不到为 NULLWHERE 和 HAVINGWHERE 聚合前过滤数据HAVING 聚合分组后过滤统计结果GROUP BY 规范MySQL8.0 严格模式下SELECT 里非聚合字段必须加入 GROUP BY。结尾本篇完整覆盖日常开发高频多表查询场景新手建议跟着命令一行行实操理解每张表的关联逻辑后复杂多表联查就不再晦涩。后续可以基于这套表拓展分页查询、关联更新、事务、索引优化等进阶内容。