多表查询以及子查询（语法+案例两步走）

多表查询

查询也就是DQL语句，接下来要进行多张表进行一个联查操作。

多表关系

• 一对多（多对一）
  例如：一个部门有多个员工，而一个员工只能在一个部门，其中部门表就是一，员工表就是多。
  如何实现：在员工表中建立外键，指向部门表中的主键进行联系
  
• 多对多
  例如：一个学生可以选择多门课程，一门课程也可以由多个学生选择
  实现：建立中间表，包含两个外键，用来关联两个表的主键

• 一对一
  例如：用户表和用户详情表
  实现：一张表中加外键，关联另一张表的主键，设置外键为UNIQUE

多表查询

概述：多张表联查得到数据

例如：select * from emp,dept;
这句sql你可要注意了，这句话将会导致出现笛卡尔积现象。

何为笛卡尔积？

两张表直接进行连接，会导致emp表和dept表的有些数据没有意义。所以要消除笛卡尔积。

多表查询分类

连接查询

emp表：

dept表：

（1）内连接
取两个表的交集部分

• 隐式内连接：

select 字段列表 from 表1,表2 where 条件;

-- 查询每一个员工的姓名，及关联的部门的名称
select e.name,d.id
from emp e,dept d
where e.dept_id=d.id;

结果：

• 显示内连接

select 字段列表 from 表1 [inner] join 表2 on 连接条件

-- 查询每一个员工的姓名，及关联的部门的名称
select e.name,d.id
from emp e
join dept d
on e.dept_id=d.id;

结果和隐式内连接相同,此处不再重复

（2）外连接

<1>左外连接
查询左表的数据以及左表和右表交集的数据
语法：select 字段列表 from 表1 left [outer] join 表2 on 条件;

-- 查询emp表的所有数据和对应部门的信息
select e.*,d.name
from emp e
left join dept d
on e.dept_id=d.id;

结果：

此处注意表的记录由17条，而刚刚隐式和显式的内连接记录只有16条，是因为第17条记录中的员工没有对应的部门号，但是对emp表中的记录进行一个保留，没有部门，则用null值来进行填充
以emp为主，以dept表为辅

<2>右外连接
右外连接和左外连接差不多，将左外连接dept表和emp表互换，left改为right,即可形成同样的结果

-- 查询emp表的所有数据和对应部门的信息
select e.*,d.name
from dept d
right join emp e
on d.id=e.dept_id;

右外连接中主要看右边，也就是emp表，所以结果和刚刚的左外连接结果是一样的

（3）自连接
自己连接自己，也就是将一张表看成几张表进行连接查询

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

-- 查询员工及其领导的名字
select a.name,b.name
from emp a
left join emp b
on a.managerid=b.id;

你可要注意了，这里把a当作了员工表，b当作了领导表，查出来的是员工的名字和领导的名字，其中加left保证了即使该员工没有领导（也就是该员工就是老板）也会显示出来
不加left会导致，没有老板的员工将不会显示，此处不再赘述。

联合查询

概述： 也就是将多次查询结果进行合并得到新的查询结果
语法：

select 字段列表 from 表A
union [all]
select 字段列表 from 表B

注意：

1. 对于联合查询多表的列数和字段类型保持一致
2. union all 将数据直接合并，而union会进行一个去重操作
3. 直接将两次查询的记录拼起来（从上到下直接拼到一起）

例1：

-- 将薪资低于5000的员工和年龄大于50岁的员工全部查询处出来
select *
from emp
where salary<5000
union all
select *
from emp
where age>50;

用到了union all数据由重复记录

例2：

-- 将薪资低于5000的员工和年龄大于50岁的员工全部查询处出来
select *
from emp
where salary<5000
union
select *
from emp
where age>50;

利用union无重复记录

子查询

概念：sql中继续嵌套select语句，也就是嵌套查询（子查询）

种类	含义
标量子查询	子查询结果为单个值
列子查询	子查询结果为一列
行子查询	子查询结果为一行
表子查询	子查询结果为多行多列

两张表：
emp

dept

标量子查询

概念：子查询返回的是单个值（数字，字符串）
例1：

-- 需求：查询销售部的所有员工信息

--    1、查询销售部的部门号(部门号为4)
select id
from dept
where name='销售部';
--    2、根据销售部门的id查询所有员工信息
select *
from emp
where dept_id=4;

-- 直接子查询
select *
from emp
where dept_id=(select id from dept where name='销售部');

例2：

-- 方东白入职之后的员工信息

-- 1、查询东方白入职时间
select entrydate from emp where name='方东白';

-- 2、根据入职时间查询所有该时间的员工信息
select * from emp where entrydate='2009-02-12';

-- 子查询
select * from emp where entrydate=(select entrydate from emp where name='方东白');

列子查询

概念：子查询的返回结果是一列（多行）

常用操作符：

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

例1：

-- 查询销售部和市场部的所有员工信息

-- 1、查询销售部和市场部们的id(2,4)
select id from dept where name='销售部' or name='市场部';

-- 2、根据部门id查询两个部门的员工信息
select * from emp where dept_id in (2,4);

-- 直接子查询
select * from emp where dept_id in (select id from dept where name='销售部' or name='市场部');

例2：

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

-- 1、查询财务部的所有人工资
select id from dept where name='财务部';
select salary from emp where dept_id=3;

-- 2、查询笔财务部所有人工资都高的员工信息
-- 直接一步到位（嵌套）
select * from emp where salary > all ( select salary from emp where dept_id =
(select id from dept where name = '财务部') );

没有比财务部工资更高的，所以查出空表

例3：

-- 查询比研发部任意一人工资高的员工信息

-- 1、查研发部的所有人工资
select id from dept where name='研发部';
select salary from emp where dept_id=(select id from dept where name='研发部');

-- 2、比研发部任意一人工资高的员工信息
select * from emp where salary > any(select salary from emp where dept_id=(select id from dept where name='研发部'));

行子查询

概念：子查询返回的结果是一行（可以是多列）

例子：

-- 查询与张无忌薪资机器直属领导相同的员工信息

-- 1、查询张无忌的薪资以及直属领导
select salary,managerid from emp where name='张无忌';

-- 2、查询与张无忌薪资以及直属领导相同的员工信息(一步到位)
select * from emp where (salary,managerid) =(select salary,managerid from emp where name='张无忌');

结果：

表子查询

概念：返回的结果是多行多列

例1：

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

-- 1、查询 "鹿杖客" , "宋远桥"职位以及薪资
select job,salary from emp where name='鹿杖客' or name='宋远桥';

-- 2、查询和他俩职位，薪资相同的员工信息
select * from emp where (job,salary) in (select job,salary from emp where name='鹿杖客' or name='宋远桥') ;

结果：

例2：

-- 1、入职日期是2006-01-01之后的员工信息
select * from emp where entrydate>'2006-01-01';


-- 2、这些员工的部门信息
select e.*,d.*
from (
     select * from emp where entrydate>'2006-01-01'
    ) e
left join dept d
on e.dept_id=d.id;

结果：