MySQL 子查询与多表联合查询

子查询：就是一个查询的结果作为另一个查询的数据源或者条件。

如何查询出工资最大的人的姓名？

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)