-- 多表连接查询
create table t1(key1 char,v1 int);

create table t2(key2 char,v2 int);

insert into t1 values('a',1),('a',2),('b',3),('c',4),('a',13);
                        
insert into t2 values('b',10),('b',11),('a',12),('a',13),('e',14);
                         
select * from t1;
select * from t2;

-- 内连接
select * from t1 inner join t2 on key1=key2;-- inner 可以省略
select * from t1 join t2 on t1.key1=t2.key2;-- 标准写法需要加上表限定，当两个字段名字不一致的时候可以省略表限定，on后面的前后顺序没有关系，只是判断该条件是否成立

-- 左连接
select * from t1 left join t2 on key1=key2;


-- 右连接
select * from t1 right join t2 on key1=key2;


-- 合并查询
-- union去重
select * from t1 union select * from t2;-- 纵向合并后去重


-- union all不去重
select * from t1 union all select * from t2;-- union all纵向合并,不去重

-- mysql实现全连接的方法
select * from t1 left join t2 on key1=key2
union
select * from t1 right join t2 on key1=key2; -- 采用左连接＋右连接 然后用union进行连接去重


-- 多表查询练习
create table salgrade(grade int,losal int,hisal int);
insert into salgrade values(1,700,1200),
                            (2,1201,1400),
                            (3,1401,2000),
                            (4,2001,3000),
                            (5,3001,9999);

select * from salgrade;-- 5
select * from emp;-- 14
select * from dept;-- 4


-- 查询每位员工的ename,dname,sal
select ename,dname,sal 
from emp  
left join dept 
on dept.deptno=emp.deptno;

-- 查询各地区的员工数（统计每个地区，没有员工计为0）
select loc,count(empno)
from dept 
left join emp 
on dept.deptno=emp.deptno 
group by loc;-- count后面直接加（）中间不能有空格


-- 查询manager的姓名、所属部门名称和入职日期：ename,dname,job,hiredate(内连接/笛卡尔积连接)

select ename,dname,job,hiredate
from emp
inner join dept
on dept.deptno=emp.deptno  
having job='manager';-- 方法一 本次内连接和左连接结果是一致的，当主表中关键字段的取值在附表中都存在的情况下,老师用的是where 不是having

select ename,dname,job,hiredate
from emp,dept
where dept.deptno=emp.deptno and job='manager';-- 方法二，采用笛卡尔连接，然后用where进行条件筛选，相比内连接速度更快


-- 查询所有员工姓名及其直属领导姓名（自连接：通过表别名，将同一张表视为多张表）
select 员工表.ename as 员工姓名,领导表.ename as 领导姓名
from emp as 员工表
left join emp as 领导表
on 员工表.mgr=领导表.empno;-- 


-- 查询入职日期早于其直属领导的员工姓名及其所属部门：empno,ename,dname (两张以上的多表连接）
select 员工表.empno as 编号,员工表.ename as 姓名,dname as 所属部门
from emp as 员工表
left join emp as 领导表 on 员工表.mgr=领导表.empno
left join dept on 员工表.deptno=dept.deptno
where 员工表.hiredate<领导表.hiredate;-- 三张表相连，先连接前两张表，继续left join（inner join）的三张表


-- 查询每位员工的工资等级；empno,ename,sal,grade(不等值连接) 
select empno,ename,sal,grade
from emp 
left join salgrade
on emp.sal>losal and emp.sal<hisal;--