mysql 子查询等

-- 工资等级
CREATE TABLE salgrade
( sal INT, 
losal DOUBLE,
hisal DOUBLE ); 
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
alter table salgrade change sal grade int;

select * from salgrade;
select * from emp;
-- 笛卡尔积连接
use test;
select ename, dname, job, hiredate
from emp, dept
where emp.deptno = dept.deptno and job = 'manager';

select ename, dname, job, hiredate
from emp inner join dept on emp.deptno = dept.deptno and job = 'manager';

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

select * from emp;

-- 查询每个工资等级的员工数
select grade, count(empno)
from salgrade left join emp on sal between losal and hisal
group by grade;

-- 查询所有员工姓名及其直属领导姓名
select 员工表.ename 员工姓名, 领导表.ename 领导姓名 from emp as 员工表 left join emp as 领导表 on
员工表.mgr = 领导表.empno;

-- 查询入职日期早于其直属领导的员工：empno,ename,dname
select * from emp;

select empno, ename, dname from 
(select * from 
(select 员工表.empno, 员工表.ename, 员工表.hiredate 员工入职日期, 领导表.hiredate 领导入职日期, 员工表.deptno
from emp as 员工表 left join emp as 领导表 on
员工表.mgr = 领导表.empno) as vo
where 员工入职日期 < 领导入职日期) as vt
left join dept on vt.deptno = dept.deptno;

select 员工表.empno, 员工表.ename, dept.dname 
from emp as 员工表
left join emp as 领导表 on 员工表.mgr = 领导表.empno
left join dept on 领导表.deptno = dept.deptno
where 员工表.hiredate < 领导表.hiredate;

-- 子查询
-- 标量子查询
-- 查询基本工资高于公司平均工资的员工信息
select * from emp 
where
sal > (select avg(sal) from emp);
-- where字句中不能使用聚合函数
-- 练习：查询和allen同一个领导的员工：empno,ename,job,mgr
-- 查询allen的直属领导工号
select mgr from emp where ename='allen';
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 * from emp;
select job, deptno
from emp
where ename = 'smitz';

select empno, ename, job, deptno
from emp
where (deptno,job)=(20, 'clerk') and ename<>'smitz';

select empno, ename, job, deptno
from emp
where (deptno, job) =
(select deptno, job from emp where ename = 'smitz');

-- 列子查询
-- 查询普通员工的工资等级：ename, ename, sal, grade
select distinct mgr from emp where mgr is not null;

select ename, ename, sal, grade
from emp left join salgrade on sal between losal and hisal
where empno not in (select distinct mgr from emp where mgr is not null);

-- 练习，查询员工数不少于5人的部门的所有员工：ename, ename, deptno
select * from emp;
select deptno, count(empno) from emp group by deptno
having count(empno) >= 5;

select ename, ename, deptno from emp
where deptno = (select deptno from emp group by deptno
having count(empno) >= 5);

-- 查询基本工资高于30号部门任意员工的员工信息
select sal from emp where deptno = 30;

select * from
emp where sal > any(select sal from emp where deptno = 30) and deptno<>30;

# 查询30号部门员工的基本工资
select sal from emp where
deptno = 30;
-- 查询基本工资高于30号部门所有员工的员工信息
select * from
emp where sal > (select max(sal) from emp where deptno = 30) and deptno<>30;

-- from子查询
-- 查询各部门最高工资的员工：empno, ename, sal, deptno
select deptno, max(sal) from 
emp group by deptno;

select empno, ename, sal ,deptno
from emp where sal in (select max(sal) from 
emp group by deptno);

select empno, ename, sal ,emp.deptno
from emp 
left join (select deptno, max(sal) max_sal from 
emp group by deptno) as t
on emp.deptno = t.deptno
where sal = max_sal;

行子查询：=

列子查询：in/not in