子查询

子查询

select语句 中嵌套 select语句,被嵌套 select语句即为子查询。
语法:
select 
  ...(select...)
from
  ...(select...)
where
  ...(select...)

where 后面嵌套

示例:找出高于平均薪资的员工

mysql> select ename,sal from emp where sal > (select avg(sal) from emp );
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
6 rows in set (0.00 sec)

from 后面嵌套

将查询出来的表作为一张新表,与另一张表进行连接查询

示例:找出每个部门平均薪水的薪资等级

第一次查询:
mysql> select avg(sal) as avgsal,deptno from emp group by deptno;
+-------------+--------+
| avgsal      | deptno |
+-------------+--------+
| 2916.666667 |     10 |
| 2175.000000 |     20 |
| 1566.666667 |     30 |
+-------------+--------+
将查询出来的表作为一张新表 t,与 salgrade 表连接查询

第二次查询:
select t.*,s.grade from t join salgrade s on t.avgsal between s.losal and s.hisal;

将表t 替换为select子查询
select t.*,s.grade from (select avg(sal) as avgsal,deptno from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal;

mysql> select t.*,s.grade from (select avg(sal) as avgsal,deptno from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal;
+-------------+--------+-------+
| avgsal      | deptno | grade |
+-------------+--------+-------+
| 2916.666667 |     10 |     4 |
| 2175.000000 |     20 |     4 |
| 1566.666667 |     30 |     3 |
+-------------+--------+-------+

示例二:找出每个部门平均的薪水等级

员工,薪水等级:
mysql> select e.ename,e.deptno,s.grade from emp e left join salgrade s on e.sal between s.losal and s.hisal;
+--------+--------+-------+
| ename  | deptno | grade |
+--------+--------+-------+
| SMITH  |     20 |     1 |
| ADAMS  |     20 |     1 |
| JAMES  |     30 |     1 |
| WARD   |     30 |     2 |
| MARTIN |     30 |     2 |
| MILLER |     10 |     2 |
| ALLEN  |     30 |     3 |
| TURNER |     30 |     3 |
| JONES  |     20 |     4 |
| BLAKE  |     30 |     4 |
| CLARK  |     10 |     4 |
| SCOTT  |     20 |     4 |
| FORD   |     20 |     4 |
| KING   |     10 |     5 |
+--------+--------+-------+

mysql>  select e.deptno,avg(s.grade) from emp e left join salgrade s on e.sal between s.losal and s.hisal group by e.deptno;
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
|     10 |       3.6667 |
|     20 |       2.8000 |
|     30 |       2.5000 |
+--------+--------------+

select 后面嵌套

示例:查出每个员工对应的部门

方法一:连接查询
mysql> select e.ename,d.dname from emp e left join dept d on e.deptno=d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+

方法二:select嵌套select
mysql> select e.ename,(select d.dname from dept d where e.deptno=d.deptno) as dname from emp e;
+--------+------------+
| 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 |
+--------+------------+
已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页