MYSQL基础（DQL子查询）

1.子查询用小括号括起来

SELECT ename FROM emp  
WHERE 
(SELECT SAL 
FROM emp 
WHERE ENAME='jack') < sal

2.WHERE子句中的子查询,子查询可以包括分组函数
子查询可以在WHERE HAVING FROM UPDATE的SET
分组函数可以在 SELECT HAVING GROUP BY

SELECT ename 
FROM emp 
 WHERE  (SELECT SAL FROM emp WHERE ENAME='jack') < sal

SELECT ename,empno,sal 
FROM emp  
WHERE sal=(SELECT MIN(SAL) FROM emp )

关系运算和单行函数不能包含返回多行结果的子查询语句

SELECT ename,empno from emp 
WHERE sal=(SELECT MIN(sal) 
FROM emp 
GROUP BY deptno)

3.多行字查询运算符
IN的使用
查询每个部门工资最低的员工姓名，员工编号，员工薪资

SELECT ename,empno,sal from emp 
WHERE  sal in (SELECT MIN(sal) FROM emp GROUP BY deptno)

ANY 的使用 只要满足结果中任意一行即可查询大于所有部门最低工资的员工姓名，员工编号，员工薪资

SELECT MIN(sal) FROM emp 
GROUP BY deptno

SELECT ename,empno,sal 
FROM emp 
WHERE sal >ANY (SELECT MIN(sal) 
FROM emp 
GROUP BY deptno)

ALL 的使用 满足结果中所有行

SELECT MIN(sal) FROM emp GROUP BY deptno

SELECT ename,empno,sal from emp where sal > ALL (SELECT MIN(sal) FROM emp GROUP BY deptno)

4.相关子查询

SELECT * 
FROM emp,dept 
WHERE emp.deptno=dept.deptno

SELECT dname,(SELECT count(empno)
FROM emp 
WHERE emp.deptno=dept.deptno 
GROUP BY emp.deptno ) 
FROM dept