day02-6 子查询

zhanglei 2022年05月30日 229次浏览

3、子查询?

3.1、什么是子查询?
select语句中嵌套select语句,被嵌套的select语句称为子查询。

3.2、子查询都可以出现在哪里呢?
select
…(select).
from
…(select).
where
…(select).

3.3、where子句中的子查询

案例:找出比最低工资高的员工姓名和工资?
	select 
		ename,sal
	from
		emp 
	where
		sal > min(sal);

	ERROR 1111 (HY000): Invalid use of group function
	where子句中不能直接使用分组函数。

实现思路:
	第一步:查询最低工资是多少
		select min(sal) from emp;
		+----------+
		| min(sal) |
		+----------+
		|   800.00 |
		+----------+
	第二步:找出>800的
		select ename,sal from emp where sal > 800;
	
	第三步:合并
		select ename,sal from emp where sal > (select min(sal) from emp);
		+--------+---------+
		| ename  | sal     |
		+--------+---------+
		| ALLEN  | 1600.00 |
		| WARD   | 1250.00 |
		| JONES  | 2975.00 |
		| MARTIN | 1250.00 |
		| BLAKE  | 2850.00 |
		| CLARK  | 2450.00 |
		| SCOTT  | 3000.00 |
		| KING   | 5000.00 |
		| TURNER | 1500.00 |
		| ADAMS  | 1100.00 |
		| JAMES  |  950.00 |
		| FORD   | 3000.00 |
		| MILLER | 1300.00 |
		+--------+---------+

3.4、from子句中的子查询
注意:from后面的子查询,可以将子查询的查询结果当做一张临时表。(技巧)

案例:找出每个岗位的平均工资的薪资等级。

第一步:找出每个岗位的平均工资(按照岗位分组求平均值)
	select job,avg(sal) from emp group by job;
	+-----------+-------------+
	| job       | avgsal      |
	+-----------+-------------+
	| ANALYST   | 3000.000000 |
	| CLERK     | 1037.500000 |
	| MANAGER   | 2758.333333 |
	| PRESIDENT | 5000.000000 |
	| SALESMAN  | 1400.000000 |
	+-----------+-------------+t表

第二步:克服心理障碍,把以上的查询结果就当做一张真实存在的表t。
mysql> select * from salgrade; s表
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
t表和s表进行表连接,条件:t表avg(sal) between s.losal and s.hisal;
	
	select 
		t.*, s.grade
	from
		(select job,avg(sal) as avgsal from emp group by job) t
	join
		salgrade s
	on
		t.avgsal between s.losal and s.hisal;
	
	+-----------+-------------+-------+
	| job       | avgsal      | grade |
	+-----------+-------------+-------+
	| CLERK     | 1037.500000 |     1 |
	| SALESMAN  | 1400.000000 |     2 |
	| ANALYST   | 3000.000000 |     4 |
	| MANAGER   | 2758.333333 |     4 |
	| PRESIDENT | 5000.000000 |     5 |
	+-----------+-------------+-------+

3.5、select后面出现的子查询(这个内容不需要掌握,了解即可!!!)

案例:找出每个员工的部门名称,要求显示员工名,部门名?
select
e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname
from
emp e;

+--------+--------+------------+
| ename  | deptno | dname      |
+--------+--------+------------+
| SMITH  |     20 | RESEARCH   |
| ALLEN  |     30 | SALES      |
| WARD   |     30 | SALES      |
| JONES  |     20 | RESEARCH   |
| MARTIN |     30 | SALES      |
| BLAKE  |     30 | SALES      |
| CLARK  |     10 | ACCOUNTING |
| SCOTT  |     20 | RESEARCH   |
| KING   |     10 | ACCOUNTING |
| TURNER |     30 | SALES      |
| ADAMS  |     20 | RESEARCH   |
| JAMES  |     30 | SALES      |
| FORD   |     20 | RESEARCH   |
| MILLER |     10 | ACCOUNTING |
+--------+--------+------------+

//错误:ERROR 1242 (21000): Subquery returns more than 1 row
select 
	e.ename,e.deptno,(select dname from dept) as dname
from
	emp e;

注意:对于select后面的子查询来说,这个子查询只能一次返回1条结果,
多于1条,就报错了。!