【MySQL7】多表查询

一、数据库多表关系设计项目中业务数据存在关联表与表分为三种关系一对多、多对多、一对一不同关系建表规则不同。1.1 一对多最常用业务场景部门表 员工表一个部门包含多名员工一名员工仅归属一个部门建表规则在多的一方员工表添加外键关联一的一方部门表主键。1.2 多对多业务场景学生表 课程表一名学生可选多门课程一门课程可被多名学生选择建表规则新增中间关联表中间表至少两个外键分别关联两张业务表主键。sql-- 学生课程中间表多对多关联 create table student_course( id int auto_increment comment 主键 primary key, studentid int not null comment 学生ID, courseid int not null comment 课程ID, constraint fk_courseid foreign key (courseid) references course (id), constraint fk_studentid foreign key (studentid) references student (id) )comment 学生课程中间表; -- 测试数据 insert into student_course values (null,1,1),(null,1,2),(null,1,3), (null,2,2),(null,2,3),(null,3,4);1.3 一对一业务场景用户基础信息表 用户教育详情表一个用户仅对应一条教育档案一条教育档案仅归属一个用户建表规则任意一方添加外键关联另一张表主键外键字段添加unique唯一约束保证一对一。sql-- 用户基本信息表 create table tb_user( id int auto_increment primary key comment 主键ID, name varchar(10) comment 姓名, age int comment 年龄, gender char(1) comment 1: 男 , 2: 女, phone char(11) comment 手机号 ) comment 用户基本信息表; -- 用户教育信息表一对一userid加unique约束 create table tb_user_edu( id int auto_increment primary key comment 主键ID, degree varchar(20) comment 学历, major varchar(50) comment 专业, primaryschool varchar(50) comment 小学, middleschool varchar(50) comment 中学, university varchar(50) comment 大学, userid int unique comment 用户ID, constraint fk_userid foreign key (userid) references tb_user(id) ) comment 用户教育信息表; -- 插入用户基础数据 insert into tb_user(id, name, age, gender, phone) values (null,黄渤,45,1,18800001111), (null,冰冰,35,2,18800002222), (null,码云,55,1,18800008888), (null,李彦宏,50,1,18800009999); -- 插入用户教育详情数据 insert into tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid) values (null,本科,舞蹈,静安区第一小学,静安区第一中学,北京舞蹈学院,1), (null,硕士,表演,朝阳区第一小学,朝阳区第一中学,北京电影学院,2), (null,本科,英语,杭州市第一小学,杭州市第一中学,杭州师范大学,3), (null,本科,应用数学,阳泉第一小学,阳泉区第一中学,清华大学,4);二、多表查询基础概念2.1 笛卡尔积直接查询多张表不加条件会生成两张表所有数据无序组合大量无效数据sql-- 产生笛卡尔积错误写法 select * from emp, dept;2.2 消除笛卡尔积添加关联等值条件只查询存在业务关联的数据sql-- 正确通过部门id关联过滤无效数据 select * from emp, dept where emp.dept_id dept.id;2.3 多表查询四大分类内连接查询两张表交集数据外连接左外连接、右外连接保留单表全部数据自连接一张表当做两张表关联查询必须设置表别名联合查询union / union all合并多条查询结果子查询select嵌套查询分为标量 / 列 / 行 / 表子查询三、内连接 INNER JOIN只返回两张表满足关联条件的交集数据分为隐式内连接、显式内连接。3.1 隐式内连接逗号分隔表where 写条件sql-- 基础写法 select emp.name, dept.name from emp, dept where emp.dept_id dept.id; -- 别名简化写法推荐 select e.name, d.name from emp e, dept d where e.dept_id d.id;3.2 显式内连接JOIN ... ON 标准写法可读性更强sql-- 查询员工姓名所属部门名称 select e.name, d.name from emp e inner join dept d on e.dept_id d.id;说明inner关键字可省略仅写join默认代表内连接。四、外连接 LEFT / RIGHT JOIN4.1 左外连接 LEFT OUTER JOIN保留左表全部数据右表无匹配数据填充null开发最常用。sql-- 查询所有员工附带对应部门信息无部门员工也保留 select e.*, d.name from emp e left outer join dept d on e.dept_id d.id;4.2 右外连接 RIGHT OUTER JOIN保留右表全部数据左表无匹配数据填充null。sql-- 查询所有部门附带对应员工信息无员工部门也保留 select d.*, e.* from emp e right outer join dept d on e.dept_id d.id;说明outer关键字可省略left join/right join等价。五、自连接同表关联一张表通过别名拆分成两张虚拟表实现自身关联查询必须定义表别名。场景 1内连接查询员工及直属领导无领导员工不展示sqlselect a.name as 员工, b.name as 领导 from emp a, emp b where a.managerid b.id;场景 2左外连接查询所有员工无领导员工也展示sqlselect a.name as 员工, b.name as 领导 from emp a left join emp b on a.managerid b.id;六、联合查询 UNION / UNION ALL合并多条select查询结果要求多条查询字段数量、字段类型完全一致。union all直接合并所有数据不去重效率更高union合并后自动去重排序性能略差sql-- 需求查询薪资低于5000 或 年龄大于50的员工 -- union all不去重 select * from emp where salary 5000 union all select * from emp where age 50; -- union自动去重 select * from emp where salary 5000 union select * from emp where age 50;七、子查询嵌套 SELECTSQL 语句中嵌套select查询外层可为select / insert / update / delete。子查询分类按返回结果划分标量子查询返回单个值一行一列列子查询返回一列多行行子查询返回一行多列表子查询返回多行多列7.1 标量子查询返回单个值示例 1查询销售部全部员工sql-- 分步1.查销售部id 2.根据id查员工 select id from dept where name 销售部; select * from emp where dept_id 4; -- 合并为标量子查询 select * from emp where dept_id (select id from dept where name 销售部);示例 2查询比方东白入职晚的员工sqlselect * from emp where entrydate (select entrydate from emp where name 方东白);7.2 列子查询返回一列多行搭配 IN/ANY/ALL表格关键字作用IN匹配集合中任意一个值ANY/SOME满足集合中任意一个条件即可ALL必须满足集合中全部条件示例 1查询销售部、市场部所有员工INsqlselect * from emp where dept_id in (select id from dept where name in (销售部,市场部));示例 2查询工资高于财务部所有人的员工ALLsqlselect * from emp where salary all( select salary from emp where dept_id (select id from dept where name 财务部) );示例 3查询工资高于研发部任意一人的员工ANYsqlselect * from emp where salary any( select salary from emp where dept_id (select id from dept where name 研发部) );7.3 行子查询返回一行多列多字段等值匹配需求查询和张无忌薪资、直属领导完全相同的员工sql-- 1. 获取张无忌薪资、领导id select salary, managerid from emp where name 张无忌; -- 2. 行子查询匹配双字段 select * from emp where (salary, managerid) (select salary, managerid from emp where name 张无忌);7.4 表子查询返回多行多列当做临时表示例 1查询和鹿杖客、宋远桥职位、薪资完全相同的员工sqlselect * from emp where (job, salary) in ( select job, salary from emp where name in (鹿杖客,宋远桥) );示例 2查询 2006-01-01 后入职员工 对应部门信息sql-- 写法1标量子查询 select e.*, (select dname from dept d where d.deptno e.deptno) as dept_name from emp e where e.hiredate 2006-01-01; -- 写法2关联JOIN开发推荐 select e.*, d.* from emp e join dept d on e.deptno d.deptno where e.hiredate 2006-01-01;八、总结本文覆盖 MySQL 多表查询全部核心考点表关系设计、内外连接、自连接、联合查询、四类子查询所有 SQL 均可直接复制运行。 多表查询核心要点多表关联必须添加等值条件避免笛卡尔积业务优先使用左外连接保留主表全部数据自连接必须设置表别名子查询根据返回结果分为四类标量子查询最常用union all性能优于union无去重需求优先使用。