多表查询

多表查询

---

多表查询：在查询数据的时候，数据是从多张表中获取得到的

分类：

# 1. 内连接查询
# 2. 外连接查询
# 3. 子查询

---

多表查询的时候会出现一种情况--》笛卡尔积（如下）

select emp.id,emp.NAME,emp.gender,emp.salary,emp.join_date,dept.NAME
,dept.id,dept.name
from emp,dept;

（在开发过程中我们需要注意，笛卡尔积并不是所有的数据集都是我们需要的，我们要进行消除笛卡尔积操作）

消除笛卡尔积的方法

select 主表的字段，从表的字段 from 主表，从表 where 主表.主键=从表.外键（这里就是部门表的主键=员工表的外键）

select emp.id,emp.NAME,emp.gender,emp.salary,emp.join_date,dept.NAME,dept.id,dept.name
from emp,dept where dept.id =emp.dept_id;

---

**内连接查询：（ 就查询条件优先级而言 ** ON关键字执行优先级高于where）

# 内连接查询
# 两种形式：（表1为从表，表2为主表）
# 隐式内连接：
#   语法：select 查询列表 from 表1,表2 where 表1.外键=表2.主键;
# 显示内连接：
#   语法：select 查询列表 from 表1 inner join 表2 on 表1.外键=表2.主键;

/*
   TODO:多表查询技巧：
   第1步：明确要查询哪些数据
   第2步：明确所查询数据分别归属哪张表
   第3步：明确表和表之间的关系（寻找主键、外键）
 */
-- TODO:练习： 查询唐僧的信息. 显示：员工id、姓名、性别、工资、部门名字
/*
   TODO:第1步：明确要查询哪些数据
          id、姓名、性别、工资、部门名字
   TODO:第2步：明确所查询数据分别归属哪张表
          id、姓名、性别、工资  归属： emp表
          部门名字             归属： dept表
   TODO:第3步：明确表和表之间的关系（寻找主键、外键）
          emp.dept_id  =   dept.id
   TODO:其他要求：
           查询唐僧
*/
-- 语法1书写方式：  隐式内连接
select emp.id , emp.NAME , emp.gender  , emp.salary,
       dept.NAME
from emp , dept
where emp.dept_id = dept.id
      AND
      emp.NAME ='唐僧';
-- 语法2书写方式： 显式内连接
select emp.id , emp.NAME , emp.gender  , emp.salary,
       dept.NAME
from emp inner join dept
     on emp.dept_id = dept.id   -- ON关键字执行优先级高于where
where emp.NAME ='唐僧';

---

外连接查询（left join作为连接）--》放在left join左边的为主要查询的表，右边为另外一个表（查所有部门即把部门表放左边，查所有员工即把员工表放左边）

得到的结果表为放在left join左边的表的信息在左边，放在left join右边的表的信息在右边

# 左外连接查询
-- 新增部门信息
insert into dept(id,NAME) value  (null , '行政部');
-- 查询所有的部门，以及该部门下的员工
select d.name ,
       e.id , e.NAME , e.gender  , e.salary
from dept AS d left join emp AS e
on d.id  = e.dept_id;

-- 查询所有的员工，以及员工所在部门名字
select  emp.id , emp.NAME , emp.gender  , emp.salary, emp.join_date,
        dept.NAME
from dept right join emp
on dept.id = emp.dept_id;

---

子查询（**子查询结果分为： 单行单列、多行单列、多行多列）

单行单列的结果：外层查询可以使用条件关键字为 >，< ，=

多行单列的结果：外层查询可以使用条件关键字为in，all，any

多行多列的结果：可以看做一张虚拟表，外层查询可以通过和这张虚拟表进行连接来查询出需要的数据

1、外查询字段 in(子查询到范围)---》查询包含在in条件中的所有数据

select id,name from dept where id in (select dept_id from emp where salary >5000);

2、all(与> 或者 < 合并使用，表示大于或者小于所有数据的时候为真

第一种方式：

select * from emp where salary > all (select salary from emp where dept_id = 1);

第二种方式：

大于所有---》只需要大于最大的

小于所有---》只需要小于最小的

select * from emp where salary >  (select max(salary) from emp where dept_id = 1);			

3、any(与> 或者 < 合并使用，表示大于或者小于任意一个数据的时候为真

第一种方式：

select * from emp where salary > any (select salary from emp where dept_id = 1);

第二种方式：

大于任意一个---》只需要大于最小的

小于任意一个---》只需要小于最大的

select * from emp where salary >  (select min(salary) from emp where dept_id = 1);

---

多行多列的结果（可以看作一张临时表）

-- 查询2011年以后入职的员工信息及部门名字
select tempTable.* ,
      dept.name
from (select id, NAME, gender, salary, join_date, dept_id from emp where join_date>'2011-1-1') AS tempTable
     left join
     dept
on tempTable.dept_id = dept.id;