扫码加入数据分析学习群

、

-- 查询所有员工姓名及其直属领导姓名（自连接：通过别名，将同一张表视为多张表）

use alex_abc;

select a.ename,a.mgr,b.ename from emp as a -- a是员工表，b是领导表

left join

emp as b on a.mgr = b.empno;

-- 查询入职日期早于其直属领导的员工姓名及其所属部门：empno,ename,dname (两张以上的多表连接）

select a.empno,a.ename,c.dname

from emp as a

left join emp as b on a.mgr = b.empno

left join dept as c on a.deptno = c.deptno

where a.hiredate<b.hiredate;

select * from emp;

select * from dept;

show tables;

-- 查询每位员工的工资等级；empno,ename,sal,grade(不等值连接)

select * from salgrade;

select empno,ename,sal,grade

from emp

left join salgrade

on emp.sal between salgrade.losal and salgrade.hisal;

-- 子查询

-- 标量子查询：

-- 查询基本工资高于公司平均工资的员工信息

select *

from emp

where sal>(select avg(sal) as avg_sal from emp);

-- 练习：查询和allen同一个领导的员工：empno,ename,job,mgr

select empno,ename,job,mgr

from emp

where mgr=(select mgr from emp where ename='allen') and ename<>'allen';

-- 行子查询

-- 查询和smith同部门同职位的员工：empno,ename,job,deptno

select empno,ename,job,deptno

from emp

where (deptno,job) = (select deptno,job from emp where ename='smith')-- where 里（deptno,job ）这个顺序和子查询的字段顺序必须一致

and ename <>'smith';

-- 列子查询：

-- 查询普通员工的工资等级：empno,ename,sal,grade

select empno,ename,sal,grade from emp

left join salgrade

on emp.sal between salgrade.losal and salgrade.hisal

where empno not in (select distinct mgr from emp where mgr is not null);