一、概述
1、定义
多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。
2、前提条件
这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。
3、多表查询分类关系
分类：
隐式内连接
内连接（查交集）
显示内连接
左外连接（差左表+包括交集的部分）
连接查询 外连接
右外连接（查右表+包括交集部分）
自连接：当前表与自身的连接查询
标量子查询（查询结果为单个值，比如数字、字符串、日期等）
列子查询（查询的结果只有一列）
子查询 行子查询（查询的结果只有一行）
表子查询（查询的结果是一个表，为多行或列）
4、准备数据
dept表和emp表：
dept部门表：
create table dept (
id int auto_increment primary key comment 'id',
name varchar(50) not null comment '部门名称'
) comment '部门表';
insert into dept (id, name)
values (1, '研发部'),
(2, '市场部'),
(3, '财务部'),
(4, '销售部'),
(5, '总经办'),
(6, '人事部');
emp员工表：
create table emp(
id int auto_increment primary key ,
name varchar(50) not null ,
age int,
job varchar(20) comment '职位',
salary int ,
entrydate date comment '入职时间',
managerid int comment '直属领导id',
dept_id int comment '所在部门id'
) comment '员工表';
insert into emp
values ( 1, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5 ),
( 2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1 ),
( 3, '杨晓', 33, '开发', 8400, '2000-11-03', 2, 1 ),
( 4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1 ),
( 5, '陈玉存', 43, '开发', 10500, '2004-09-07', 3, 1 ),
( 6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1 ),
( 7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3 ),
( 8, '周芷若', 19, '会计', 48000, '2006-06-02', 7, 3 ),
( 9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3 ),
( 10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2 ),
( 11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2 ),
( 12, '何碧文', 19, '职员', 3750, '2007-05-09', 10, 2 ),
( 13, '东方白', 19, '职员', 5500, '2009-02-12', 10, 2 ),
( 14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4 ),
( 15, '鱼梁洲', 38, '销售', 4600, '2004-10-12', 14, 4 ),
( 16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4 ),
( 17, '陈友谅', 42, null, 2000, '2011-10-12', 1, null );
二、分类
1、连接查询
（1）、内连接
隐式内连接：
select 字段列表 from 表1,表2 where 条件;
显示内连接：
select 字段列表 from 表1 [inner] join 表2 on 连接条件;
-- 查询每一个员工的姓名及关联的部门的名称
-- 隐式内连接
select emp.name, dept.name from emp, dept where emp.dept_id=dept.id;
-- 显示内连接
select emp.name, dept.name from emp inner join dept on emp.dept_id = dept.id;
查询结果：
（2）、外连接
左外连接：select 字段列表 from 表1 left [outer] join 表2 on 条件;
右外连接： select 字段列表 from 表1 right [outer] join 表2 on 条件;
-- 查询emp表的所有数据，和应于的部门信息(左查询)
select emp.*, dept.* from emp left outer join dept on emp.dept_id = dept.id;
-- 查询dept表的所有数据，和对于的员工信息(右查询)
select dept.*, emp.* from emp right outer join dept on emp.dept_id = dept.id;
查询结果：
左查询：
右查询：
（3）、自连接
select 字段列表 from 表a 别名a join 表a 别名b on 条件;
-- 查询员工及其所属领导的名字
select a.name, b.name from emp a join emp b on a.managerid=b.id;
查询结果：
2、联合查询----union、union all
select 字段列表 from 表a
union [all]
select 字段列表 from 表b
对于联合查询就是把多次查询的结果合并起来，形成一个新的查询结果。
注意：
对于联合查询的多张表的列数必须保持一致，字段类型也要保持一致。
union all会将全部的数据合并在一起，union会对合并之后的数据去重。
-- 将薪资低于5000的员工和年龄大于50的员工查询出来
select * from emp where salary>5000
union all
select * from emp where age>50;
-- 没有all重复满足条件的只出现一次
-- 将薪资低于5000的员工和年龄大于50的员工查询出来
select * from emp where salary>5000
union
select * from emp where age>50;查询结果：
union会进行去重：
3、子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。
select * from 表1 where 字段=(select 字段 from 表2);
SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。
（1）、标量子查询
子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询称为标量子查询
常用符号：=、<>、>、>=、<、<=
-- 查询销售部所有员工信息
select id from dept where name='销售部'; -- 先查询销售部门的id，结果为4
select * from emp where dept_id=4;-- 再查询销售部门中员工的信息
-- 合并为一个查询
select * from emp where dept_id=(select dept.id from dept where dept.name='销售部' );
查询结果：
（2）、列子查询
子查询的结果为一列(可以是多行)的，这种子查询为列子查询。
常用操作符：in not in any some alll
-- 列子查询
-- 查询销售部和市场部的所有员工信息
select id from dept where name='销售部' or name='市场部';-- 查询销售部和市场部的id 结果为id为2 4
select * from emp where dept_id in (2,4); -- 查询两个部门的所有员工
-- 合并
select * from emp where dept_id in (select id from dept where name='销售部' or name='市场部');
查询结果：
（3）、行子查询
子查询返回的结果是一行(可以是多列)，这种子查询为行子查询
常用操作符：=、<>、in、not in
-- 行子查询
-- 查询与张无忌的薪资及直属领导相同的员工信息
select * from emp where (salary,managerid)=(select salary, managerid from emp where name='张无忌');
查询结果：
（4）、表子查询
子查询的结果是多行多列这种查询为表子查询
常用操作符：in
-- 表子查询
-- 查询与鹿杖客和宋远桥的职位和薪资相同的员工信息
select * from emp where (job, salary) in ( select job, salary from emp where name in ('鹿杖客', '宋远桥'));
查询结果：
