子查询：就是一个查询的结果作为另一个查询的数据源或者条件。
如何查询出工资最大的人的姓名？
mysql> select max(sal) from emp; --查询出工资最大值 +----------+ | max(sal) | +----------+ | 5000.00 | +----------+ 1 row in set (0.00 sec) mysql> select ename,sal from emp where sal = (select max(sal) from emp);--可以将工资的最大值最为一个筛选条件，select ename,sal from emp执行完之后赋值给sal sal = (select max(sal) from emp); +-------+---------+ | ename | sal | +-------+---------+ | KING | 5000.00 | +-------+---------+ 1 row in set (0.32 sec) mysql> select ename,sal from emp where sal = (select min(sal) from emp);--查询最小工资 +-------+--------+ | ename | sal | +-------+--------+ | SMITH | 800.00 | +-------+--------+ 1 row in set (0.00 sec)
子查询情况；将查询结果作为另一个查询的条件
查询出公司都有那些人是经理人；看那些对应的的empno出现在mgr字段中，谁就是经理人
mysql> select mgr from emp; 查询 +------+ | mgr | +------+ | 7902 | | 7698 | | 7698 | | 7839 | | 7698 | | 7839 | | 7839 | | 7566 | | NULL | | 7698 | | 7788 | | 7698 | | 7566 | | 7782 | | NULL | +------+ 15 rows in set (0.00 sec) mysql> select distinct mgr from emp; --去重 +------+ | mgr | +------+ | 7902 | | 7698 | | 7839 | | 7566 | | NULL | | 7788 | | 7782 | +------+ 7 rows in set (0.00 sec) mysql> select distinct mgr from emp where mgr is not null; --去掉null空值，通过这条语句得到的信息就是都有哪些empno出现在mgr字段中 +------+ | mgr | +------+ | 7902 | | 7698 | | 7839 | | 7566 | | 7788 | | 7782 | +------+ 6 rows in set (0.01 sec) mysql> select ename,empmo from emp where empmo in(select distinct mgr from emp);--查询公司都有哪些人是经理人in关键字 +-------+-------+ | ename | empmo | +-------+-------+ | JONES | 7566 | | BLAKE | 7698 | | CLARK | 7782 | | SCOTT | 7788 | | KING | 7839 | | FORD | 7902 | +-------+-------+ 6 rows in set (0.00 sec) mysql> select ename,empmo from emp where empmo in(select distinct mgr from emp where mgr is not null); +-------+-------+ | ename | empmo | +-------+-------+ | JONES | 7566 | | BLAKE | 7698 | | CLARK | 7782 | | SCOTT | 7788 | | KING | 7839 | | FORD | 7902 | +-------+-------+ 6 rows in set (0.00 sec)
注意：以上这两种情况都是将整个查询的一个结果作为另一个查询的条件，作为条件的时候这里用到了等值比较、in，其他的<\>\<=\>=都是可以的。查出平均工资最大的部门是那个？
1、每个部门的平均工资需要先查出来，组函数不可以嵌套
查询结果作为另一个查询的数据源，可以当成一张表。
注意：当成表的过程中必须起别名
mysql> select avg(sal),deptno from emp group by deptno;--查询平均工资和对应的部门编号 +-------------+--------+ | avg(sal) | deptno | +-------------+--------+ | 2356.540000 | NULL | | 2916.666667 | 10 | | 2175.000000 | 20 | | 1566.666667 | 30 | +-------------+--------+ 4 rows in set (0.00 sec)
MySQL多表联合查询
查询员工的姓名和员工所在部门的名称
mysql> select ename,DNAME from emp,dept; +------------+------------+ | ename | DNAME | +------------+------------+ | SMITH | ACCOUNTING | | SMITH | RESEARCH | | SMITH | SALES | | SMITH | OPERATIONS | | ALLEN | ACCOUNTING | | ALLEN | RESEARCH | | ALLEN | SALES | | ALLEN | OPERATIONS | | WARD | ACCOUNTING | | WARD | RESEARCH | | WARD | SALES | | WARD | OPERATIONS | | JONES | ACCOUNTING | | JONES | RESEARCH | | JONES | SALES | | JONES | OPERATIONS | | MARTIN | ACCOUNTING | | MARTIN | RESEARCH | | MARTIN | SALES | | MARTIN | OPERATIONS | | BLAKE | ACCOUNTING | | BLAKE | RESEARCH | | BLAKE | SALES | | BLAKE | OPERATIONS | | CLARK | ACCOUNTING | | CLARK | RESEARCH | | CLARK | SALES | | CLARK | OPERATIONS | | SCOTT | ACCOUNTING | | SCOTT | RESEARCH | | SCOTT | SALES | | SCOTT | OPERATIONS | | KING | ACCOUNTING | | KING | RESEARCH | | KING | SALES | | KING | OPERATIONS | | TURNER | ACCOUNTING | | TURNER | RESEARCH | | TURNER | SALES | | TURNER | OPERATIONS | | ADAMS | ACCOUNTING | | ADAMS | RESEARCH | | ADAMS | SALES | | ADAMS | OPERATIONS | | JAMES | ACCOUNTING | | JAMES | RESEARCH | | JAMES | SALES | | JAMES | OPERATIONS | | FORD | ACCOUNTING | | FORD | RESEARCH | | FORD | SALES | | FORD | OPERATIONS | | MILLER | ACCOUNTING | | MILLER | RESEARCH | | MILLER | SALES | | MILLER | OPERATIONS | | zhang | ACCOUNTING | | zhang | RESEARCH | | zhang | SALES | | zhang | OPERATIONS | +------------+------------+ 60 rows in set (0.29 sec)
笛卡尔积
（14*4）emp的每一条数据与dept表的每一条数据组合。也就是56条数据。
多表联合查询
两个表中的deptno是相等的
mysql> select ename,DNAME from emp,dept where emp.deptno = dept.deptno; +--------+------------+ | ename | DNAME | +--------+------------+ | SMITH | RESEARCH | | ALLEN | SALES | | WARD | SALES | | JONES | RESEARCH | | MARTIN | SALES | | BLAKE | SALES | | CLARK | ACCOUNTING | | SCOTT | RESEARCH | | KING | ACCOUNTING | | TURNER | SALES | | ADAMS | RESEARCH | | JAMES | SALES | | FORD | RESEARCH | | MILLER | ACCOUNTING | +--------+------------+ 14 rows in set (0.01 sec)
关键字join= 联和 on = 条件;查询ename和dname在emp表和dept表查询，查询对应的数据on emp.deptno = dept.deptno
mysql> select ename,dname from emp join dept on emp.deptno = dept.deptno; +--------+------------+ | ename | dname | +--------+------------+ | SMITH | RESEARCH | | ALLEN | SALES | | WARD | SALES | | JONES | RESEARCH | | MARTIN | SALES | | BLAKE | SALES | | CLARK | ACCOUNTING | | SCOTT | RESEARCH | | KING | ACCOUNTING | | TURNER | SALES | | ADAMS | RESEARCH | | JAMES | SALES | | FORD | RESEARCH | | MILLER | ACCOUNTING | +--------+------------+ 14 rows in set (0.00 sec)
查询某个人的姓名、所在的部门、工资的等级
mysql> select ename,DNAME,GRADE from emp join dept join salgrade on emp.deptno = dept.deptno and emp.sal between losal and hisal; +--------+------------+-------+ | ename | DNAME | GRADE | +--------+------------+-------+ | SMITH | RESEARCH | 1 | | ADAMS | RESEARCH | 1 | | JAMES | SALES | 1 | | WARD | SALES | 2 | | MARTIN | SALES | 2 | | MILLER | ACCOUNTING | 2 | | ALLEN | SALES | 3 | | TURNER | SALES | 3 | | JONES | RESEARCH | 4 | | BLAKE | SALES | 4 | | CLARK | ACCOUNTING | 4 | | SCOTT | RESEARCH | 4 | | FORD | RESEARCH | 4 | | KING | ACCOUNTING | 5 | +--------+------------+-------+ 14 rows in set (0.01 sec)