MySQL--多表查询--概述

多表查询--笛卡尔积

部门表

员工表 

 笛卡尔积展示

 

 消除多余的笛卡尔积

-- 多表查询----笛卡尔积
select *from emp,dept where emp.dept_id=dept.id;

 

 多表查询分类

连接查询--内连接

-- 内链接演示
-- 1、查询每一个员工的姓名，及关联的部门的名称（隐式内连接实现）
-- 表结构 ：emp dept
-- 连接条件：emp.dept_id = dept.id

select emp.name,dept.name from emp, dept where emp.dept_id = dept.id;

select e.name,d.name from emp e ,dept d where e.dept_id = d.id;
-- 起别名以后 就不能使用原名了，所以要加强对于执行顺序的记忆
-- 2、查询每一个员工的姓名 ，及关联的部门的名称（显式内连接实现） ---INNER JOIN... ON...
-- inner 可以省略
select e.name,d.name from emp e inner join dept d on e.dept_id = d.id;

select e.name,d.name from emp e join dept d on e.dept_id = d.id;

连接查询--外连接

 

-- 外连接演示
-- 1、查询 emp表的所有数据，和对应的部门信息（左外连接）
-- 表结构：emp，dept
-- 连接条件：emp.dept_id = dept.id;

select e.*,d.name from emp e left outer join dept d on e.dept_id = d.id;
-- outer 可以省略
select e.*,d.name from emp e left join dept d on e.dept_id = d.id;


-- 2、查询dept表的所有数据，和对应的员工信息（右外连接）
select d.*,e.* from emp e right outer join dept d on e.dept_id = d.id;

select d.*,e.* from dept d left outer join emp e on e.dept_id=d.id;
-- 通常会用左表来查询数据，因为左表也能实现右表的功能

左外连接

右外连接

连接查询--自连接

 

-- 自连接
-- 1、查询员工 及其 所属领导的名字
-- 表结构：emp
select a.name,b.name from emp a,emp b where a.managerid=b.id;


-- 2、查询所有员工 emp 及其领导的名字 emp ，如果员工没有领导，也需要查询出来
-- 表结构 emp a, emp b

select  a.name '员工' ,b.name '领导' from emp a left join emp b on a.managerid = b.id;

-- 其中left的目的是为了查询出直属领导为null的员工

联合查询

-- union all,union
-- 1、将薪资低于5000的员工，和年龄大于50岁的员工全部查询出来

select * from emp where salary < 5000
union all
select * from emp where age > 50;

-- 去重操作 去除all
select * from emp where salary < 5000
union
select * from emp where age > 50;

子查询

标量子查询

-- --------------------子查询------------------

-- 标量子查询
-- 1、查询 “销售部” 的所有员工信息
-- a.查询 “销售部” 部门ID
select id from dept where name = '销售部';

-- b.根据销售部部门ID，查询员工信息
select * from emp where dept_id = 4;

-- 可以合并为标量子查询
select * from emp where dept_id = (select id from dept where name = '销售部');


-- 2、查询在 “方东白” 入职之后的员工信息
-- a. 查询 方东白 的入职日期
select entrtdate from emp where name = '方东白';

-- b.查询指定入职日期之后入职的员工信息
select * from emp where entrtdate > '2009-02-12';

-- 可以合并为标量子查询
select * from emp where entrtdate > (select entrtdate from emp where name = '方东白');

案例1： 

a.

b.

案例2：

a.

b.

列子查询

-- 列子查询
-- 1、查询 “销售部” 和 “市场部”  的所有员工信息
-- a. 查询 “销售部” 和 “市场部” 的部门ID
select id from dept where name = '销售部' or name = '市场部';

-- b. 根据部门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、查询比财务部所有人工资都高的员工信息
-- a.查询所有 财务部 人员的工资
select id from dept where name = '财务部';

select salary from emp where dept_id = (select id from dept where name = '财务部');
-- b.比 财务部 所有人工资都高的员工信息

select  * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name = '财务部'));

-- 3、查询比研发部其中任意一人 工资高的员工信息
-- a.查询研发部所有人工资
select id from dept where name = '研发部';

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


-- all是与最大值相比
-- any或者some是与最小值相比

案例1：

a.

b.

案例2：

a.

b.

案例3：

a.

b.

行子查询

-- 行子查询
-- 1、查询与 “张无忌”的薪资与直属领导相同的员工信息
-- a。查询“张无忌”的薪资以及直属领导
select salary,managerid from emp where name = '张无忌';

-- b.查询与"张无忌"的薪资及直属领导
select * from emp where (salary,managerid) = (select salary,managerid from emp where name = '张无忌');

案例1：

a.

b.

表子查询

-- 表子查询
-- 1、查询与 “鹿杖客”，“宋远桥” 的职位和薪资相同的员工信息
-- a.查询 “鹿杖客” ，“宋远桥” 的职位和薪资
select job,salary from emp where name in('宋远桥','鹿杖客');

-- b.查询与 “鹿杖客”,“宋远桥” 的职位和薪资相同的员工信息
select * from emp where (job,salary) in (select job,salary from emp where name in('宋远桥','鹿杖客'));


-- 2、查询入职日期是“2006-01-01” 之后的员工信息，及其部门信息
-- a.入职日期是“2006-01-01” 之后的员工信息
select * from emp where entrtdate > '2006-01-01';

-- b. 查询这部分员工，对应的部门信息
select e.*,d.* from (select * from emp where entrtdate > '2006-01-01') e left join dept d on e.dept_id = d.id;

案例1：

a.

b.

案例2：

a.

b.