MySQL多表查询怎么做？内连接外连接子查询全攻略_Mysql

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。

前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。

多表关系

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

一对多

案例：部门与员工

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

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

多对多

案例：学生与课程

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

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

一对一

案例：用户与用户详情

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

实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（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 employee_id, city, department_name
from employees e
inner join departments d
on d.department_id = e.department_id
inner join locations l
on d.location_id = l.location_id;

显式性能比隐式高：在 mysql 中，可以使用连接（join）代替子查询。连接查询不需要建临时表，其速度比子查询快，如果查询中使用索引的话，性能会更好。

例子：

-- 查询员工姓名，及关联的部门的名称
-- 隐式
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;

外连接查询

外连接：

除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。

两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外连接。没有匹配的行时, 结果表中相应的列为空(null)。

– 如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。

– 如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。

左外连接：

查询左表所有数据，以及两张表交集部分数据

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

自连接查询

当 table1 和 table2 本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。

语法：

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

合并查询结果利用union关键字，可以给出多条select语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。

各个 select 语句之间使用 union 或union all 关键字分隔。

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

区别：

union 操作符返回两个查询的结果集的并集，去除重复记录。
union all 操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

语法：

select 字段列表 from 表a ...
union [all]
select 字段列表 from 表b ...

注意事项

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

子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，sql 中子查询的使用大大增强了 select 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

子查询会在主查询之前一次执行完成，也就是说包含子查询的语句真正的执行次数至少是2次。

select * from t1 where column1 = ( select column1 from t2);

子查询外部的语句可以是 insert / update / delete / select 的任何一个

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

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

根据子查询位置可分为：

where 之后
from 之后
select 之后

单行子查询

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

常用操作符：- < > > >= < <=

例子：

# 题目：查询工资大于 2 号员工工资的员工的信息
select * from emp 
where salary > (select salary from emp where eid = 2);

列子查询

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

常用操作符：

操作符	描述
in	在指定的集合范围内，多选一
not in	不在指定的集合范围内
any	子查询返回列表中，有任意一个满足即可
some	与any等同，使用some的地方都可以使用any
all	子查询返回列表的所有值都必须满足

例子：

-- 查询销售部和市场部的所有员工信息
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;