[MySQL学习笔记] 多表查询、联合查询、子查询

原创于 2025-08-04 17:43:16 发布 · 846 阅读

13 ·

CC 4.0 BY-SA版权

文章标签：

#学习 #笔记

[MySQL学习笔记] 专栏收录该内容

11 篇文章

订阅专栏

1 多表关系

在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

一对多多对多一对一

1.1 一对多

案例: 部门与员工的关系

关系: 一个部门对应多个员工，一个员工对应一个部门

实现: 在多的一方建立外键，指向一的一方的主键

案例如下：

1.2 多对多

案例: 学生与课程的关系

关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择

实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

案例如下所示：

1.3 一对一

案例: 用户与用户详情的关系

关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率

实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

案例如下：

2 多表查询

本部分采用以下的案例，进行讲解。

员工表emp：

部门表demp：

上图中A表示A表中的所有数据，B表示B表中所有数据。而两表相交部分表示两表关联字段均存在的数据。

多表查询分类：

内连接：相当于查询A、B交集部分数据

外连接：分为左外连接和右外连接，相当于查询左（右）表所有数据，以及两张表交集部分数据自连接：当前表与自身的连接查询，自连接必须使用表别名

注：在多表查询时建议给表起别名。表面 as 别名 tour ，as可省略

2.1 内连接

1.显式内连接

SELECT  字段列表   FROM   表1  [ INNER ]  JOIN 表2  ON  连接条件 ... ;

2.隐式内连接

SELECT  字段列表   FROM   表1 , 表2   WHERE   条件 ... ;

案例：

select emp.name, dept.name from emp inner join dept where emp.dept_id=dept.id;
select emp.name, dept.name from emp ,dept where emp.dept_id=dept.id;

2.2 外连接

1.左外连接

SELECT  字段列表   FROM   表1  LEFT  [ OUTER ]  JOIN 表2  ON  条件 ... ;

2.右外连接

SELECT  字段列表   FROM   表1  RIGHT [ OUTER ]  JOIN 表2  ON  条件 ... ;

左外连接和右外连接可以互相转换，只要交换表的顺序即可。日常开发偏向于使用左连接。

案例：

select emp.*, dept.* from emp left outer join dept on emp.dept_id=dept.id;
select dept.*, emp.* from emp right outer join dept on emp.dept_id=dept.id;

3.3 自连接

当查询的两张表为同一张表时，此时便为自连接查询。自连接查询可以是内连接也可以是外连接。

语法与上面相同，只不过查询的两张表是同一张表。

案例：

select a.name '员工', b.name '领导'from emp a left join emp b on a.managerid=b.id;

3 联合查询

联合查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

对于联的多张表的列数必须保持一致，字段类型也需要保持一致，否则将报错。 union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

select * from emp where salary<5000
union
select * from emp where age>50;

4 子查询

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。

分类：

根据子查询结果不同，分为：

A. 标量子查询（子查询结果为单个值）

B. 列子查询(子查询结果为一列)

C. 行子查询(子查询结果为一行)

D. 表子查询(子查询结果为多行多列)

根据子查询出现不同，分为：

A. WHERE之后

B. FROM之后

C. SELECT之后

4.1 标量子查询

子查询返回的结果是单个值（数字、字符串、日期等）

案例：

1.查询 "销售部" 的所有员工信息

select e.* from emp e where e.dept_id = (select id from dept d where name ='销售部');

2.查询在 "方东白" 入职之后的员工信息

select * from emp where entrydate> (select emp.entrydate from emp where emp.name='方东白');

4.2 列子查询

子查询返回的结果是一列，这种子查询称为列子查询。

常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL

案例：

1.查询 "销售部" 和 "市场部" 的所有员工信息

select * from emp where emp.dept_id in (select id from dept where name='销售部' or name ='市场部');

2.查询比财务部所有人工资都高的员工信息

select * from emp where emp.salary > all (select salary from emp where emp.id = (select id from dept where dept.name = '财务部'));

4.3 行子查询

子查询返回的结果是一行，这种子查询称为行子查询。

案例：

1.查询与 "张无忌" 的薪资及直属领导相同的员工信息

select * from emp where (salary, managerid) = (select salary, managerid from emp where emp.name = '张无忌');

4.4 表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。

案例：

1.查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息

select * from emp where (salary, managerid) in (select salary, managerid from emp where emp.name = '鹿杖客' or emp.name = '宋远桥');

2.查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息

select e.*, d.* from (select * from emp where entrydate > 2006-01-01 ) e left outer join dept d on e.dept_id=d.id;