MySql 多表查询

作者：秋名山码民

2022 年 6 月 08 日
本文字数：2211 字
阅读完需：约 7 分钟

多表查询

多表关系

一对多（多对一）
多对多
一对一

一对多

案例：部门与员工关系：一个部门对应多个员工，一个员工对应一个部门实现：在多的一方建立外键，指向一的一方的主键

多对多

案例：学生与课程关系：一个学生可以选多门课程，一门课程也可以供多个学生选修实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

一对一

案例：用户与用户详情关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）

查询

合并查询（笛卡尔积，会展示所有组合结果）：select * from employee, dept;

笛卡尔积：两个集合 A 集合和 B 集合的所有组合情况（在多表查询时，需要消除无效的笛卡尔积）

消除无效笛卡尔积：select * from employee, dept where employee.dept = dept.id;

内连接查询

内连接查询的是两张表交集的部分

隐式内连接：SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;

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

显式性能比隐式高

例子：

-- 查询员工姓名，及关联的部门的名称-- 隐式select e.name, d.name from employee as e, dept as d where e.dept = d.id;-- 显式select e.name, d.name from employee as e inner join dept as d on e.dept = d.id;

复制代码

外连接查询

左外连接：查询左表所有数据，以及两张表交集部分数据SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ...;相当于查询表 1 的所有数据，包含表 1 和表 2 交集部分数据

右外连接：查询右表所有数据，以及两张表交集部分数据SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ...;

例子：

-- 左select e.*, d.name from employee as e left outer join dept as d on e.dept = d.id;select d.name, e.* from dept d left outer join emp e on e.dept = d.id;  -- 这条语句与下面的语句效果一样-- 右select d.name, e.* from employee as e right outer join dept as d on e.dept = d.id;

复制代码

左连接可以查询到没有 dept 的 employee，右连接可以查询到没有 employee 的 dept

自连接查询

当前表与自身的连接查询，自连接必须使用表别名

语法：SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;

自连接查询，可以是内连接查询，也可以是外连接查询

例子：

-- 查询员工及其所属领导的名字select a.name, b.name from employee a, employee b where a.manager = b.id;-- 没有领导的也查询出来select a.name, b.name from employee a left join employee b on a.manager = b.id;

复制代码

联合查询 union, union all

把多次查询的结果合并，形成一个新的查询集

语法：

SELECT 字段列表 FROM 表A ...UNION [ALL]SELECT 字段列表 FROM 表B ...

复制代码

注意事项

UNION ALL 会有重复结果，UNION 不会
联合查询比使用 or 效率高，不会使索引失效

子查询

SQL 语句中嵌套 SELECT 语句，称谓嵌套查询，又称子查询。SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2);子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个

根据子查询结果可以分为：

标量子查询（子查询结果为单个值）
列子查询（子查询结果为一列）
行子查询（子查询结果为一行）
表子查询（子查询结果为多行多列）

根据子查询位置可分为：

WHERE 之后
FROM 之后
SELECT 之后

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等）。常用操作符：- < > > >= < <=

例子：

-- 查询销售部所有员工select id from dept where name = '销售部';-- 根据销售部部门ID，查询员工信息select * from employee where dept = 4;-- 合并（子查询）select * from employee where dept = (select id from dept where name = '销售部');
-- 查询xxx入职之后的员工信息select * from employee where entrydate > (select entrydate from employee where name = 'xxx');

复制代码

列子查询

返回的结果是一列（可以是多行）。

常用操作符：

例子：

-- 查询销售部和市场部的所有员工信息select * from employee where dept in (select id from dept where name = '销售部' or name = '市场部');-- 查询比财务部所有人工资都高的员工信息select * from employee where salary > all(select salary from employee where dept = (select id from dept where name = '财务部'));-- 查询比研发部任意一人工资高的员工信息select * from employee where salary > any (select salary from employee where dept = (select id from dept where name = '研发部'));

复制代码

行子查询

返回的结果是一行（可以是多列）。常用操作符：=, <, >, IN, NOT IN

例子：

-- 查询与xxx的薪资及直属领导相同的员工信息select * from employee where (salary, manager) = (12500, 1);select * from employee where (salary, manager) = (select salary, manager from employee where name = 'xxx');

复制代码

表子查询

返回的结果是多行多列常用操作符：IN

例子：

-- 查询与xxx1，xxx2的职位和薪资相同的员工select * from employee where (job, salary) in (select job, salary from employee where name = 'xxx1' or name = 'xxx2');-- 查询入职日期是2006-01-01之后的员工，及其部门信息select e.*, d.* from (select * from employee where entrydate > '2006-01-01') as e left join dept as d on e.dept = d.id;

复制代码

发布于: 刚刚阅读数: 4

原文链接:【http://xie.infoq.cn/article/c28f9c474786c5e3313e104d2】。

秋名山码民

关注

卷不死，就往…… 2021.10.19 加入

2019NOIP退役成员，华为云享专家，阿里云专家博主，csdn博主，努力进行算法分享，有问题欢迎私聊

发布

暂无评论

创作场景

MySql 多表查询

多表查询

多表关系

一对多

多对多

一对一

查询

内连接查询

外连接查询

自连接查询

联合查询 union, union all

注意事项

子查询

标量子查询

列子查询

行子查询

表子查询

秋名山码民

评论