mysql 自我练习基础 5：子查询

- 子查询
-- 标量子查询：
-- 查询基本工资高于公司平均工资的员工信息
select * 
from emp
where sal>(select 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 deptno,job from emp where ename='smith';-- 第一步找出smith的部门以及职位

select * from emp where deptno=20 and job='clerk' and ename<>'smith';-- 第二部找出和smith相同部门20以及相同职位clerk的员工

select * from emp where (deptno,job)=(20,'clerk') and ename<>'smith';-- 第二部找出和smith相同部门20以及相同职位clerk的员工 方法二，当where后面有几个等值“=”赛选条件时可以用（）代替and

select empno,ename,job,deptno
 from emp
 where (deptno,job)=(select deptno,job from emp where ename='smith') 
 and ename<>'smith';-- 第三部将部门20以及职位clerk 用第一步select 语句代替，行子查询是值第一个selsect语句返回的结果（该结果用于后面语句的条件判断）是一行，至于判断后最终结果返回是多行是可以的


-- 列子查询：
-- 查询普通员工的工资等级：empno,ename,sal,grade
-- 查询普通员工的基本工资：empno,ename,sal（老师课程上将上面要求修改为该条）
select distinct mgr from emp where mgr is not null;-- 第一步将领导找出来，找出来的数据不能有空，因为后续需要同这些数据进行比较 任何值和null进行比较反馈结果都是null,因此需要除去空值，然后反馈结果是有重复的  该题可以用distinct去重也可以不去重
select empno,ename,sal
from emp 
where empno not in (select distinct mgr from emp where mgr is not null);-- 采用列子查询判断是否在结果中  where <字段名> [not] in (子查询)
-- 查询基本工资高于30号部门任意员工的员工信息
 
select * from  emp where sal> any (select sal from emp where deptno=30) and deptno<>30;-- 根据题目意思要排除30号部门的员工

-- 方法二直接找出30号部门最低工资，然后筛选比30号部门最低工资大的非30号部门的员工，由于返回是一个值 则为标量子查询
 
select * from  emp where sal>(select min(sal) from emp where deptno=30) and deptno<>30;-- 根据题目意思要排除30号部门的员工




-- 查询基本工资高于30号部门所有员工的员工信息
select * from  emp where sal> all (select sal from emp where deptno=30);-- 既然比30号部门员工工资高那就不存在30号部门的员工，因此不需要额外排除30号的员工

-- 方法二 先查询30号部门所有员工中最高工资（返回的为标量子），然后大于该最大值
select max(sal) from emp where deptno=30;
select * from emp where sal>(select max(sal) from emp where deptno=30);

-- from子查询
-- 查询各部门最高工资的员工：empno,ename,sal,deptno
select deptno,max(sal) from emp group by deptno;-- 先找出每个部门的最高工资，然后将emp 为主表查询到的表为附表连接，最后判断emp的工资=最高工资即可
select empno,ename,sal,emp.deptno
 from emp 
 left join (select deptno,max(sal) as 最高工资 from emp group by deptno) as t
on emp.deptno=t.deptno
where emp.sal=最高工资;

-- select子查询
-- 查询各部门员工人数占比
select count(*) from emp;-- 查询公司总人数
select deptno,count(*) as 部门人数, count(*)/(select count(*) from emp) as 人数占比
from emp  
group by deptno;