mysql-210712-03

mysql-210712-03

• 子查询
  • 什么是子查询
  • 子查询可以出现在哪里

---

子查询

什么是子查询

select 语句当中嵌套select语句，被嵌套的select语句是子查询

子查询可以出现在哪里

select
	...(select)
form
	...(select)
where
	...(select)

---

案例1（where后面）

// 找出高于平均薪资的员工
mysql> select * from emp where sal > (select avg(sal) from emp);
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |

---

案例2（from后面）

from 后面只能跟表

// 找出每个部门的平均薪资等级
mysql> select
    -> e.deptno,e.avgsal,s.grade
    -> from
    -> (select deptno,avg(sal) as avgsal from emp group by deptno) e
    -> join
    -> salgrade s
    -> on
    -> e.avgsal between s.losal and hisal;
+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     20 | 2175.000000 |     4 |
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
+--------+-------------+-------+
3 rows in set (0.00 sec)



分析：
	把  select deptno,avg(sal) as avgsal from emp group by deptno
	查询出来的结果当做一个临时表

mysql> select
    -> e.deptno,d.dname,e.avgsal,s.grade
    -> from
    -> (select deptno,avg(sal) as avgsal from emp group by deptno) e
    -> join
    -> salgrade s
    -> on
    -> e.avgsal between s.losal and s.hisal
    -> join
    -> dept d
    -> on
    -> d.deptno = e.deptno
    -> order by
    -> e.deptno asc;
+--------+------------+-------------+-------+
| deptno | dname      | avgsal      | grade |
+--------+------------+-------------+-------+
|     10 | ACCOUNTING | 2916.666667 |     4 |
|     20 | RESEARCH   | 2175.000000 |     4 |
|     30 | SALES      | 1566.666667 |     3 |
+--------+------------+-------------+-------+
3 rows in set (0.00 sec)

---

案例3（from后面）

// 找出每个部门薪资等级的平均值
select 
	ee.deptno,avg(ee.grade)
from
	(select e.*,s.grade from emp e join salgrade s on e.sal between s.losal and hisal) ee
group by
	ee.deptno;
+--------+---------------+
| DEPTNO | avg(ee.grade) |
+--------+---------------+
|     20 |        2.8000 |
|     30 |        2.5000 |
|     10 |        3.6667 |
+--------+---------------+
3 rows in set (0.00 sec)

// 找出每个部门薪资等级的平均值
// 这种方式效率要快些
// 第一步，找出每个员共的薪资等级
select
	e.empno,ename,e.deptno,s.grade 
from
	emp e 
join 
	salgrade s 
on 
	e.sal between s.losal and s.hisal
order by
	e.deptno;
+-------+--------+--------+-------+
| empno | ename  | deptno | grade |
+-------+--------+--------+-------+
|  7782 | CLARK  |     10 |     4 |
|  7839 | KING   |     10 |     5 |
|  7934 | MILLER |     10 |     2 |
|  7369 | SMITH  |     20 |     1 |
|  ...                            |
|  ...                            |
|  7698 | BLAKE  |     30 |     4 |
|  7844 | TURNER |     30 |     3 |
|  7900 | JAMES  |     30 |     1 |
+-------+--------+--------+-------+
14 rows in set (0.00 sec)
	
// 第二步，基于第一步结果，按照deptno分组，求grade平均值
select 
	e.deptno,avg(s.grade)
from 
	emp e
join
	salgrade s
on 
	e.sal between s.losal and s.hisal
group by
	e.deptno
order by
	e.deptno desc;
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
|     30 |       2.5000 |
|     20 |       2.8000 |
|     10 |       3.6667 |
+--------+--------------+
3 rows in set (0.00 sec)

select 
	ee.deptno,avg(ee.grade)
from
	(select e.*,s.grade from emp e join salgrade s on e.sal between s.losal and hisal) ee
group by
	ee.deptno
order by
	ee.deptno;
+--------+---------------+
| DEPTNO | avg(ee.grade) |
+--------+---------------+
|     10 |        3.6667 |
|     20 |        2.8000 |
|     30 |        2.5000 |
+--------+---------------+
3 rows in set (0.00 sec)

select 
	ee.deptno,avg(ee.grade)
from
	(select e.*,s.grade from emp e join salgrade s on e.sal between s.losal and hisal) ee
group by
	ee.deptno
join 
	salgrade s
on
	ee.depno = s.depno
order by
	ee.deptno;
	
应该还得再套一次才可以
	
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'join
salgrade s
on
ee.depno = s.depno
order by
ee.deptno' at line 7

---

案例4（select后面）

// 找出每个员工所在的部门名称，要求显示员工名和部门名
select
	e.ename,d.dname
from 
	emp e
join
	dept d
on
	e.deptno = d.deptno;

// 找出每个员工所在的部门名称，要求显示员工名和部门名
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      |
| ...                 |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+