[大数据]MySql之多表查询

数据准备

一、交叉联合查询

select 字段列表?from 表1,表2;

将表内每行都进行匹配，会有很多冗余数据

select * from dept3,emp3;

?二、内连接查询

1、隐式内连接

selsec 字段列表 from 表1，表2 where 条件...;

2、显式内连接

selsec 字段列表 from 表1 [inner] join 表2 on?条件...;

查询两张表中符合条件的交集部分

-- 查询每个部门员工
select * from dept3,emp3 where depton = dept_id;
select * from dept3 inner join emp3 on depton = dept_id;
-- 查询研发部门员工 
select * from dept3,emp3 where depton = dept_id and name = '研发部';
select * from dept3 inner join emp3 on depton=dept_id and name = '研发部';
-- 查询研发部和销售部
select * from dept3 inner join emp3 on depton=dept_id and name in( '研发部','销售部');
-- 查询每个部门的员工数，并且降序
select depton,count(*) count from dept3 inner join emp3 on depton=dept_id group by depton order by count desc;
-- 查询人数大于3的部门
select depton,count(*) count from dept3 inner join emp3 on depton=dept_id group by depton having count > 3;

?三、外连接查询

1、左外连接

select 字段列表 from 表1 left [outer] join 表2 on 条件...;

相当于查询表1所有数据，然后将两表左右合并

-- 查询哪些部门有员工
select * from dept3 left outer join emp3 on depton = dept_id;

2、右外连接

select 字段列表 from 表1 right [outer] join 表2 on 条件...;

相当于查询表2所有数据，然后将两表左右合并两表

-- 查询哪些员工有对应部门
select * from dept3 right outer join emp3 on depton = dept_id;

3、满外连接

select 字段列表 from 表1 left [outer] join 表2 on 条件...

union [all]

select 字段列表 from 表1 right [outer] join 表2 on 条件...;

将左、右外连接结果上下合并

select * from dept3 left outer join emp3 on depton = dept_id 
union
select * from dept3 right outer join emp3 on depton = dept_id;

四、联合查询

select 字段列表 from 表1 ...

union [all]

select 字段列表 from 表1 ...;

将多次查询结果上下合并

五、子查询

1、all

select 字段列表 from 表名?where 字段 > all(select...)；

查询符合所有子查询结果的数据

-- 查询年龄大于1003部门所有员工的员工
select * from emp3 where age > all(select age from emp3 where dept_id = '1003')
-- 查询不属于任何部门的员工信息
select * from emp3 where dept_id != all(select depton from dept3);

2、any/some

select 字段列表 from 表名?where 字段 > any(select...)；

查询符合任意子查询结果的数据

-- 查询年龄大于1003部门任意部门的员工
select * from emp3 where age > any(select age from emp3 where dept_id = '1003') and dept_id != '1003';

3、in

select 字段列表 from 表名?where 字段 in(select...)；

查询字段是否存在于子查询结果中

-- 查询研发部和销售部员工
select * from emp3 where dept_id in(select depton from dept3 where name = '研发部' or name = '销售部')

4、exists

select 字段列表?from emp3 where exists(select ...);

先执行外侧，然后判断子查询结果，为true则输出外侧查询结果，否则不输出

-- 全表输出 
select * from emp3 where exists(select 1);
-- 查询公司是否有大于60的员工
select * from emp3 where age > 60 and exists (select * from emp3 where age > 60);
select * from emp3 a where exists (select * from emp3 where a.age > 60);
-- 查询有部门的员工信息
select * from emp3 a where exists (select * from dept3 b where a.dept_id = b.depton);

六、自关联查询

select 字段列表 from 表1?别名1? join 表1?别名2 on 条件...;

自关联查询必须别名，可以是内连接也可以是外连接查询

-- 查询每个员工以及上级信息
select a.ename name ,b.ename manager_name from t_sanguo a left join t_sanguo b on a.manager_id = b.eid;
select a.ename,b.ename,c.ename from t_sanguo a left join t_sanguo b on a.manager_id = b.eid left join t_sanguo c on b.manager_id = c.eid;