MYSQL-多表联合查询汇总

-- 关键词:select * from A,B,C.....;
 
select * from bumen1,emp1;

-- 一:查询各个部门所有的人员信息
-- 显式
SELECT * from emp1 inner join bumen1
on detpno = dept_id;
 
 
-- 隐式查询:
SELECT * from emp1,bumen1 where detpno = dept_id;
-- 二:查询研发部以及销售部所属员工
SELECT * from emp1 a join bumen1 b
on detpno=dept_id && (b.name='研发部' || b.name ='销售部'); 
 
-- 查询每个部门当中大于三的部门人数,并且按照升序进行排列
select bumen1.name,COUNT(bumen1.name) '人数'
from bumen1 
join emp1 
on detpno=dept_id 
group by detpno
HAVING count(bumen1.name)>3
order by count(bumen1.NAME)
;
 
-- 查询每个部门的员工数量,并且按照升序进行排列
SELECT b.name,count(1) 
from emp1 a
join bumen1 b
on  detpno=dept_id
GROUP BY detpno 
order by count(1);
 
-- 切记! group by 之后要跟having!

-- 查询那些部门有员工,那些部门没有员工
use mydb3;
 
select * from bumen1 left outer join emp1 on detpno = dept_id;
-- 左外连接,左边没有空值,右外连接右边没有空值
-- 查询那些员工有部门,那些员工没有部门
select * from bumen1 right join emp1 on detpno=dept_id;
 
-- 通过使用满外连接来展现表中的数据---对于union的运用
select * from bumen1 left outer join emp1 on detpno=dept_id
UNION
SELECT * from bumen1 right outer join emp1 on detpno=dept_id;

 
 
-- 一:查询研发部和销售部门的员工信息以及人数
方法一:内连接查询:
select * from bumen1 join emp1 on deptno=dept_id && (bumen1.name='研发部' || bumen1.name='销售部'); 
方法二:分步查询:
1.先查询研发部门或者是销售部门的编号
select deptno from bumen1 where name in ('销售部','研发部');
2.将查询过后两个部门的ID再在员工当中进行相应的匹配
TIPS:需要注意的是:在这里有两个结果.所以可以使用in的形式来接结果
select * from emp1 where dept_id in (select deptno from bumen1 where name in ('销售部','研发部'));
 
-- 二:查询研发部门20以下的员工信息,包括员工号,员工姓名,以及部门的名字
方法一:分布查询:
1.先查询研发部门的编号是多少
select deptno from bumen1 where name ='研发部';
2.再在表中进行查询年龄为限制为20岁以下的
select * from emp1 where age<20;
3.将两个表当作是两个不同的表再使用内显性查询即可
select * from (select deptno from bumen1 where name ='研发部') a
join 
(select * from emp1 where age<20 ) b
on a.deptno = b.dept_id
;

-- 子查询小技巧
子查询当中的关键子:
1.ALL关键子  (包括查询结果的所有数值)
1.ANY关键子  (表示满足查询结果:其中的任意一个即可)
1.SOME关键子  (可以当作是ANY的一个别名,用途跟ANY是一样的)
1.IN关键子  (可以是查询结果也可以不是,是其中的任何一个就行)
1.EXISTS关键子 (判断查询的最后有没有结果)
 
 
 
 
ALL的使用:
-- 一:查询不属于任何一个部门的员工信息
select * from emp1 where dept_id!=all(SELECT deptno from bumen1);
 
 
ANY的使用:
-- 二:查询年龄大于'1003'部门任意一个员工年龄的员工信息
select * from emp1 where age>any(select age from emp1 where 
dept_id='1003');
 
IN的使用:
-- 特点1:IN关键子,可以用来判断某个记录的值,是否再指定的集合当中
-- 特点2:在in的关键字之前也可以加上not将条件反过来
-- 三:查询研发部以及销售部的所有员工信息,包含员工号,以及员工名字
select * from emp1 where dept_id in(select deptno from bumen1 where NAME in('研发部','销售部'));
 
 
 exists的用法,作用其实跟IN是差不多的
 EXISTS的使用:
 特点:
 1.如果在子查询当中有结果数据,至少有一行结果输出,那么该exists输出的结果就是TRUE,外层的查询能够继续的往下进行执行,相反,没有的话就执行为FALSE,外层的查询结果不成立
 2.EXISTS后面的查询结果不返回任何的值,仅仅只返回是TRUE还是FALSE,并且仅仅只有为TRUE的时候条件成立
 -- 操作:
 -- 查询公司是否有大于20岁的员工,有则输出
 select * from emp1 a where EXISTS(SELECT * from emp1 where a.age>20);
 
-- 查询所有有相关部门的员工信心
select * from emp1 a where exists(select * from bumen1 b where a.dept_id=b.deptno);

-- 关键词语:FOREIGN key 表[列名] REFERENCES 表[列名];
-- 实质上跟之前的外键约束差不多
 
create database mydb4;
 
use mydb4;
 
create table if not EXISTS sanguo(
eid int primary key,
ename varchar(20),
manager_id int,
foreign key (manager_id) REFERENCES sanguo(eid)
);
 
insert into sanguo VALUES(1,'刘协',NULL);
insert into sanguo VALUES(2,'刘备',1);
insert into sanguo VALUES(3,'关羽',2);
insert into sanguo VALUES(4,'张飞',2);
insert into sanguo VALUES(5,'曹操',1);
insert into sanguo VALUES(6,'典韦',5);
insert into sanguo VALUES(7,'孙权',1);
insert into sanguo VALUES(8,'周瑜',8);
insert into sanguo VALUES(9,'鲁肃',8);
 
-- 在一张表当中表示出来各个三国人物和他们的上司
select a.ename,b.ename from sanguo a join sanguo b on  a.manager_id=b.eid; 
 
-- 在一张表当中表示出来所有的人物,并展现出来他们各自的上司;
SELECT a.ename,b.ename from sanguo a left join sanguo b on a.manager_id=b.eid;
-- 在这里使用了外连接查询的左连接查询
 
-- 以三列的形式显示出上下级关系:比如:张飞 刘备 刘协
select a.ename,b.ename,c.ename from sanguo a 
 left join sanguo b on a.manager_id=b.eid
 left  join sanguo c
on b.manager_id=c.eid;