MySQL子查询

子查询

1、子查询出现的位置

select 
	```(select)
from 
	```(select)
where
	```(select)

2、where子句中的子查询

案例：找出比最低工资高的员工姓名和工资？

思路：

1. 查询最低工资；

2. 找出 > min(sal)

3. 合并

3、from子句中的子查询

注意：from后的子查询，可以将子查询的结果当作一张临时表（技巧）

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

思路：

1. 找出每个岗位的平均工资（按照岗位分组求平均值）

   mysql> select job,avg(sal) from emp group by job;
   +-----------+-------------+
   | job       | avg(sal)    |
   +-----------+-------------+
   | ANALYST   | 3000.000000 |
   | CLERK     | 1037.500000 |
   | MANAGER   | 2758.333333 |
   | PRESIDENT | 5000.000000 |
   | SALESMAN  | 1400.000000 |
   +-----------+-------------+
   

2. 把以上的查询结果就当作一张真实存在的表t

   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 |
   +-----------+-------------+-------+
   5 rows in set (0.00 sec)
   

union合并查询结果集

1、用法

案例：查询工作为MANAGER,SALESMAN的人

mysql> select ename,job from emp where job in ('MANAGER','SALESMAN');
mysql> select ename,job from emp where job = 'MANAGER'
    -> union
    -> select ename,job from emp where job = 'SALESMAN';
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+

注：union的的效率高一些。对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍增加。

用union可减少匹配的次数。

union在进行结果集合并的时候，要求两个结果集的列数相同。

limit分页查询

1、用法

案例：按照薪资降序，取出排名在前5名的员工

mysql> select 
			ename,sal 
		from 
			emp 
		order by 
			sal desc 
		limit 
			5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)

完整用法：

limit startIndex,length

startIndex是起始下标，length是长度，起始下标从0开始

注：limit在order by之后执行

案例：取出工资排名在5-9名的员工

mysql> select
    -> ename,sal
    -> from
    -> emp
    -> order by
    -> sal desc
    -> limit
    -> 4,4;
+--------+---------+
| ename  | sal     |
+--------+---------+
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
+--------+---------+

2、分页

记公式：limit (pageNo - 1) * pageSize ,pageSize

总结：

DQL语句：

select
	```
from
	```
where
	```
group by
	```
having
	```
order by
	```
limit 
	```