【MySQL】子查询

子查询：嵌入在其他sql语句中的select语句，也叫做嵌套语句。

单行子查询（只返回一行数据的子查询语句）；

多行子查询（返回多行数据的子查询语句），使用关键字in；

多列子查询（返回多列数据的子查询语句）。

子查询还可以当成临时表使用。

#子查询
#创建员工表emps，部门表dept，工资级别表salgrade
-- create table emps (empno int,ename varchar(256),job varchar(256),mgr int,birthday datetime,sal double,comm double,deptno int);
-- create table dept(deptno int,deptname varchar(256),loc varchar(256));
-- create table salgrade(grade int,lowsal double,highsal double);
-- insert into emps values(1234,'abby','cleck',3456,'1990-12-12',8000,null,10),
--                        (2345,'bitty','saleman',1234,'1987-12-12',6000,500,20),
--                        (3456,'cindy','manager',null,'1980-12-12',120000,1200,30),
--                        (4567,'doller','cleck',1234,'2000-12-12',7000,800,10),
--                        (5678,'eggie','saleman',4567,'1994-12-12',9000,1000,20);
-- select * from emps;
-- insert into dept values(10,'accouting','shanghai'),						 
--                         (20,'sales','beijing'),
-- 												(30,'management','guangzhou');
-- select * from dept;
-- insert into salgrade values(1,5000,6500),(2,6800,7500),(3,8000,15000);		
-- select * from salgrade;	

#单行子查询
#查询与abby同一部门的职工
select * from emps where deptno = (select deptno from emps where ename = 'abby');

#多行子查询
#查询和部门10的工作相同的职工名字，岗位，工资，部门号，但不包含10
#步骤：
# ①查询出跟部门10工作相同的工作有哪些  select distinct job from emps where deptno = 10
# ②将上边语句当子查询使用 
select ename,job,sal,deptno from emps where job in (select distinct job from emps where deptno = 10 ) and deptno <> 10

#any，all
#显示工资比部门30所有职工高的职工名字，工资和部门号
select ename,sal,deptno from emps where sal > all (select sal from emps where deptno = 30)
#显示工资比部门30其中任一职工高的职工名字，工资和部门号
select ename,sal,deptno from emps where sal > any (select sal from emps where deptno = 30)

#多列子查询
#查询与abby部门岗位完全相同的职工（不包括abby）
select * from emps where (job,deptno) = (select job,deptno from emps where ename = 'abby') and ename !='abby'

#子查询可当为临时表使用
#查询每个部门工资高于本部门平均工资的职工
#步骤：
#① 查询每个部门部门号和平均工资
#select deptno,avg(sal) from emps group by deptno
#②以①查询结果为子查询，跟emps表多表查询
select * from emps,(select deptno,avg(sal) as avg_sal from emps group by deptno) as other where emps.deptno = other.deptno and emps.sal > other.avg_sal 

#查询每个部门工资最高的职工
#每个部门部门号和最高工资 select deptno,max(sal) from emps group by deptno
select * from emps,(select deptno,max(sal) as max_sal from emps group by deptno)temp where temp.deptno = emps.deptno and emps.sal = temp.max_sal

#查询每个部门部门名称，部门编号，地址和职工数量
#查询出每个部门编号和职工数量 select count(*),deptno from emps group by deptno
select deptname,dept.deptno,loc,temp.total from dept,(select count(*) as total,deptno from emps group by deptno)temp where dept.deptno = temp.deptno