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

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

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

-- 給emp3表添加數(shù)據(jù)
insert into emp3 values('1','喬峰',20, '1001');
insert into emp3 values('2','段譽(yù)',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');

-- 查詢(xún)研發(fā)部門(mén)的所屬員工
-- 隱式內(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ā)部'; 
-- 查詢(xún)研發(fā)部和銷(xiāo)售部的所屬員工
select * from dept3 a join emp3 b on a.deptno = b.dept_id and (name = '研發(fā)部' or name = '銷(xiāo)售部') ; 
select * from dept3 a join emp3 b on a.deptno = b.dept_id and name in('研發(fā)部' ,'銷(xiāo)售部') ; 
 
-- 查詢(xún)每個(gè)部門(mén)的員工數(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;
 
 
-- 查詢(xún)?nèi)藬?shù)大于等于3的部門(mén)，并按照人數(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;

-- 外連接查詢(xún)
-- 查詢(xún)哪些部門(mén)有員工，哪些部門(mén)沒(méi)有員工
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id;
-- 查詢(xún)員工有對(duì)應(yīng)的部門(mén)，哪些沒(méi)有
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
-- 使用union關(guān)鍵字實(shí)現(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;

-- 查詢(xún)年齡最大的員工信息，顯示信息包含員工號(hào)、員工名字，員工年齡
select * from emp3 where age=(select max(age) from emp3 );
 
select eid,ename ,age from emp3 where age = (select max (age) from emp3);
-- 查詢(xún)年研發(fā)部和銷(xiāo)售部的員工信息,包含員工號(hào)、員工名字
select  * from emp3 where emp3.dept_id in (select deptno from dept3 d where d.name='研發(fā)部' or d.name='銷(xiāo)售部');
 
select eid,ename , t.name from emp3 where dept_id in (select deptno, name from dept3where name ='研發(fā)部'or name ='銷(xiāo)售部');
-- 查詢(xún)研發(fā)部20歲以下的員工信息，包括員工號(hào)、員工名字，部門(mén)名字
 
-- 方式一：關(guān)聯(lián)查詢(xún)
select * from dept3 d join emp3 e on d.deptno =e.dept_id and (d.name='研發(fā)部' and e.age<20); 
-- 方式二：子查詢(xún)
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) ;

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