Mysql day03_子查询

子查询

思考：
查询工资比JONES工资高的员工信息
分步
1）查询出JONES工资（查询员工信息为JONES的员工的工资）

SELECT sal
FROM emp
WHERE ename='JONES';

2）查询比上述工资高的员工信息
查询工资高于2975的员工信息

SELECT *
FROM emp
WHERE sal>1975;

SELECT *
FROM emp
WHERE sal>
(SELECT sal
FROM emp
WHERE ename='JONES');

子查询种类：根据查询结果来分类

单行

（只有一个结果，单行单列）<,> ,=,<=,>=,<>,!=
显示和雇员7369从事相同通知并且工资大于雇员7876的雇员的姓名和工作
分步
1)雇员7369从事的工作

SELECT job
FROM emp
WHERE empno=7369;

2）雇员7876的工资

SELECT sal
FROM emp
WHERE empno=7876;

3)查询job=clerk sal>1100

SELECT ename,job
FROM emp
WHERE job='clerk' AND salg>1100;

SELECT ename,job
FROM emp
WHERE job=
(SELECT job
FROM emp
WHERE empno=7369) 
AND sal>(
SELECT sal
FROM emp
WHERE empno=7876); 

查询工资最低的员工姓名，岗位及工资

SELECT ename,job,sal
FROM emp
having sal=min(sal);

1)最低工资

SELECT min(sal)
FROM emp;

2）sal=min()

SELECT ename,job,sal
FROM emp
WHERE sal=min()

SELECT ename,job,sal
FROM emp
WHERE sal=
(SELECT min(sal)
FROM emp);

查询部门最低工资比20号部门最低工资高的部门编号及最低工资
1）各部门的编号和最低工资

SELECT deptno,min(sal)
FROM emp
GROUP BY deptno;

2)20号部门的最低工资

SELECT min(sal)
FROM emp
WHERE deptno=20;

3)比对

SELECT deptno,min(sal)
FROM emp
GROUP BY deptno
HAVING min(sal)>
(SELECT min(sal)
FROM emp
WHERE deptno=20);

HAVING子句中用子查询
WHERE 或者 HAVING
条件里比较运算符左侧不含聚合函数的用 where
条件里比较运算符左侧含聚合函数的用 having
查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称

SELECT e.ename,e.sal,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno AND  e.sal>
(SELECT sal
FROM emp
WHERE ename='SMITH')
AND d.loc='CHICAGO';

查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期

SELECT ename,hiredate
FROM emp
WHERE hiredate<
(SELECT min(hiredate)
FROM emp
WHERE deptno=20);

查询部门人数大于所有部门平均人数的部门编号，部门名称，部门人数

SELECT d.deptno,d.dname,count(*)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY deptno
HAVING count(e.empno)>
(SELECT count(e.empno)/count(DISTINCT d.deptno) 
FROM emp e RIGHT JOIN dept d
ON e.deptno=d.deptno);

FROM 中使用子查询（了解 经常可以用视图来代替）

查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资

1）如果有一张表记录着部门编号，该部门的平均工资可以解决

SELECT deptno,avg(sal) avgsal
FROM emp
GROUP BY deptno;

SELECT e.ename,e.sal,e.deptno,a.avgsal
FROM emp e,
(SELECT deptno,avg(sal) avgsal
FROM emp
GROUP BY deptno) a
WHERE e.deptno=a.deptno AND sal>a.avgsal;

SELECT e.ename,e.sal,e.deptno,a.avgsal
FROM emp e,
(v_dept_avgsal) a
WHERE e.deptno=a.deptno AND sal>a.avgsal;

视图 v_dept_avgsal :
SELECT deptno,avg(sal) avgsalFROM emp GROUP BY deptno

2）多行子查询：
in
查询是经理的员工姓名，工资

SELECT mgr FROM emp WHERE mgr IS NOT NULL;

SELECT ename,sal
FROM emp
WHERE empno IN(SELECT mgr FROM emp WHERE mgr IS NOT NULL);

子查询中的空值
查询不是经理的员工姓名

SELECT ename,sal
FROM emp
WHERE empno NOT IN(SELECT mgr FROM emp WHERE mgr IS NOT NULL);

ANY 任意
=ANY 相当于 in
查询比10号部门任意一个人的工资高的员工信息
(比最小的大即可)
SELECT *
FROM emp
WHERE sal>ANY(SELECT sal FROM emp WHERE deptno=10);

ALL 所有
查询比10号部门所有一个人的工资高的员工信息
(比最大的大即可)

SELECT *
FROM emp
WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=10);

查询部门编号不为20，且工资比20部门所有员工工资高的员工编号，姓名，职位，工资。

SELECT deptno,ename,job,sal
FROM emp
WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=20) AND deptno<>20;

查询部门编号不为10，且工资和10部门所有员工工资相等的员工编号，姓名，职位，工资。

SELECT deptno,ename,job,sal
FROM emp
WHERE sal=ALL(SELECT sal FROM emp WHERE deptno=10) AND deptno<>10;

查询部门编号不为20，且工资比20部门任意一个员工工资高的员工编号，姓名，职位，工资。

SELECT deptno,ename,job,sal
FROM emp
WHERE sal>ANY(SELECT sal FROM emp WHERE deptno=10) AND deptno<>20;

查询部门编号不为10，且工资和10部门任意一个员工工资相等的员工编号，姓名，职位，工资

SELECT deptno,ename,job,sal
FROM emp
WHERE sal=ANY(SELECT sal FROM emp WHERE deptno=10) AND deptno<>10;

3）多列 （）