-- 創(chuàng)建部門表
create table if not exists dept3(
  deptno varchar(20) primary key ,  -- 部門號
  name varchar(20) -- 部門名字
);

-- 創(chuàng)建員工表
create table if not exists emp3(
  eid varchar(20) primary key , -- 員工編號
  ename varchar(20), -- 員工名字
  age int,  -- 員工年齡
  dept_id varchar(20)  -- 員工所屬部門
);

-- 給dept3表添加數(shù)據(jù)
insert into dept3 values('1001','研發(fā)部');
insert into dept3 values('1002','銷售部');
insert into dept3 values('1003','財務(wù)部');
insert into dept3 values('1004','人事部');

-- 給emp3表添加數(shù)據(jù)
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');

-- 查詢研發(fā)部門的所屬員工
-- 隱式內(nèi)連接
select * from emp3 e ,dept3 d where e.dept_id =d.deptno and name ='研發(fā)部';
-- 顯式內(nèi)連接
select * from dept3 d join emp3 e on d.deptno =e.dept_id and name ='研發(fā)部'; 
-- 查詢研發(fā)部和銷售部的所屬員工
select * from dept3 a join emp3 b on a.deptno = b.dept_id and (name = '研發(fā)部' or name = '銷售部') ; 
select * from dept3 a join emp3 b on a.deptno = b.dept_id and name in('研發(fā)部' ,'銷售部') ; 
 
-- 查詢每個部門的員工數(shù),并升序排序
 
select 
	a.name,a.deptno,count(1) 
from dept3 a 
   join emp3 b on a.deptno = b.dept_id 
group by 
  a.deptno,name;
 
 
-- 查詢?nèi)藬?shù)大于等于3的部門，并按照人數(shù)降序排序
 
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;
-- 查詢員工有對應(yīng)的部門，哪些沒有
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
-- 使用union關(guān)鍵字實現(xiàn)左外連接和右外連接的并集
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);
-- 查詢年研發(fā)部和銷售部的員工信息,包含員工號、員工名字
select  * from emp3 where emp3.dept_id in (select deptno from dept3 d where d.name='研發(fā)部' or d.name='銷售部');
 
select eid,ename , t.name from emp3 where dept_id in (select deptno, name from dept3where name ='研發(fā)部'or name ='銷售部');
-- 查詢研發(fā)部20歲以下的員工信息，包括員工號、員工名字，部門名字
 
-- 方式一：關(guān)聯(lián)查詢
select * from dept3 d join emp3 e on d.deptno =e.dept_id and (d.name='研發(fā)部' and e.age<20); 
-- 方式二：子查詢
select eid,age ,ename , name from (select * from dept3 where name = '研發(fā)部')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 = '研發(fā)部')t1 join  (select * from emp3 where age <20) t2 on t1.deptno =t2.dept_id ;

-- 創(chuàng)建表，并建立自關(guān)聯(lián)約束
create table t_sanguo (
    eid int primary key ,
    ename varchar (20) ,
    manager_id int,
    foreign key (manager_id) references t_sanguo (eid) -- 添加自關(guān)聯(lián)約束
);

--添加數(shù)據(jù)
insert into t_sanguo values (1,'劉協(xié)',NULL) ,
                             (2,'劉備',1),
                             (3,'關(guān)羽',2),
                            (4,'張飛',2),
                             (5,'曹操',1),
                            (6,'許褚',5),
                             (7,'典韋',5),
                             (8,'孫權(quán)',1) ,
                             (9,'周瑜',8),
                            (10,'魯肅',8) ;

-- 進行關(guān)聯(lián)查詢
-- 1.查詢每個三國人物及他的上級信息，如:關(guān)羽劉備
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.查詢所有人物及上級(劉協(xié)（沒有上級）也輸出)
select a.ename,b.ename from t_sanguo a left join t_sanguo b on a.manager_id = b.eid;
-- 3.查詢所有人物、上級，上上級比如:張飛劉備劉協(xié)
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;