MySQL中的多表联合查询功能操作

-- 创建部门表
create table if not exists dept3(
  deptno varchar(20) primary key ,  -- 部门号
  name varchar(20) -- 部门名字
);

-- 创建员工表
create table if not exists emp3(
  eid varchar(20) primary key , -- 员工编号
  ename varchar(20), -- 员工名字
  age int,  -- 员工年龄
  dept_id varchar(20)  -- 员工所属部门
);

-- 给dept3表添加数据
insert into dept3 values('1001','研发部');
insert into dept3 values('1002','销售部');
insert into dept3 values('1003','财务部');
insert into dept3 values('1004','人事部');

-- 给emp3表添加数据
insert into emp3 values('1','乔峰',20, '1001');
insert into emp3 values('2','段誉',21, '1001');
insert into emp3 values('3','虚竹',23, '1001');
insert into emp3 values('4','阿紫',18, '1001');
insert into emp3 values('5','扫地僧',85, '1002');
insert into emp3 values('6','李秋水',33, '1002');
insert into emp3 values('7','鸠摩智',50, '1002'); 
insert into emp3 values('8','天山童姥',60, '1003');
insert into emp3 values('9','慕容博',58, '1003');
insert into emp3 values('10','丁春秋',71, '1005');

-- 查询研发部门的所属员工
-- 隐式内连接
select * from emp3 e ,dept3 d where e.dept_id =d.deptno and name ='研发部';
-- 显式内连接
select * from dept3 d join emp3 e on d.deptno =e.dept_id and name ='研发部'; 
-- 查询研发部和销售部的所属员工
select * from dept3 a join emp3 b on a.deptno = b.dept_id and (name = '研发部' or name = '销售部') ; 
select * from dept3 a join emp3 b on a.deptno = b.dept_id and name in('研发部' ,'销售部') ; 
 
-- 查询每个部门的员工数,并升序排序
 
select 
	a.name,a.deptno,count(1) 
from dept3 a 
   join emp3 b on a.deptno = b.dept_id 
group by 
  a.deptno,name;
 
 
-- 查询人数大于等于3的部门，并按照人数降序排序
 
select
  a.deptno,
  a.name,
  count(1) as total_cnt
from dept3 a
	join emp3 b on a.deptno = b.dept_id
group by 
  a.deptno,a.name
having 
  total_cnt >= 3
order by 
  total_cnt desc;

-- 外连接查询
-- 查询哪些部门有员工，哪些部门没有员工
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id;
-- 查询员工有对应的部门，哪些没有
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
-- 使用union关键字实现左外连接和右外连接的并集
select * from dept3 left outer join emp3 on dept3. deptno = emp3.dept_id
union
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;

-- 查询年龄最大的员工信息，显示信息包含员工号、员工名字，员工年龄
select * from emp3 where age=(select max(age) from emp3 );
 
select eid,ename ,age from emp3 where age = (select max (age) from emp3);
-- 查询年研发部和销售部的员工信息,包含员工号、员工名字
select  * from emp3 where emp3.dept_id in (select deptno from dept3 d where d.name='研发部' or d.name='销售部');
 
select eid,ename , t.name from emp3 where dept_id in (select deptno, name from dept3where name ='研发部'or name ='销售部');
-- 查询研发部20岁以下的员工信息，包括员工号、员工名字，部门名字
 
-- 方式一：关联查询
select * from dept3 d join emp3 e on d.deptno =e.dept_id and (d.name='研发部' and e.age<20); 
-- 方式二：子查询
select eid,age ,ename , name from (select * from dept3 where name = '研发部')t1 , (select * from emp3 where age <20) t2 where  t1.deptno =t2.dept_id ;
 
select eid,age ,ename , name from (select * from dept3 where name = '研发部')t1 join  (select * from emp3 where age <20) t2 on t1.deptno =t2.dept_id ;

-- 创建表，并建立自关联约束
create table t_sanguo (
    eid int primary key ,
    ename varchar (20) ,
    manager_id int,
    foreign key (manager_id) references t_sanguo (eid) -- 添加自关联约束
);

--添加数据
insert into t_sanguo values (1,'刘协',NULL) ,
                             (2,'刘备',1),
                             (3,'关羽',2),
                            (4,'张飞',2),
                             (5,'曹操',1),
                            (6,'许褚',5),
                             (7,'典韦',5),
                             (8,'孙权',1) ,
                             (9,'周瑜',8),
                            (10,'鲁肃',8) ;

-- 进行关联查询
-- 1.查询每个三国人物及他的上级信息，如:关羽刘备
select * from t_sanguo ts ,t_sanguo ts2 where ts .manager_id  =ts2.eid  ;						
select ts.ename ,ts2.ename  from t_sanguo ts ,t_sanguo ts2 where ts .manager_id  =ts2.eid  ;						
-- 2.查询所有人物及上级(刘协（没有上级）也输出)
select a.ename,b.ename from t_sanguo a left join t_sanguo b on a.manager_id = b.eid;
-- 3.查询所有人物、上级，上上级比如:张飞刘备刘协
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;