SQL(四) - 子查询和union以及limit分页

子查询概念

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

select语句当中嵌套select语句，被嵌套的select语句是子查询。
子查询可以出现在哪里？

select
	..(select).
from
	..(select).
where
	..(select).

1.where子句中使用子查询

案例：找出高于平均薪资的员工信息。

select * from emp where sal > avg(sal); //错误的写法，where后面不能直接使用分组函数。

错误的写法:where后面不能直接使用分组函数。

第一步：找出平均薪资

select avg(sal) from emp;

第二步：where过滤

select * from emp where sal > 2073.214286;

第一步和第二步合并：

select * from emp where sal > (select avg(sal) from emp);

2.from后面嵌套子查询 (重要)

应用场景：将查询后得到的表，与其他表做连接查询。

案例：找出每个部门平均薪水的等级。

第一步：找出每个部门平均薪水（按照部门编号分组，求sal的平均值）

select deptno,avg(sal) as avgsal from emp group by deptno;

第二步：将以上的查询结果当做临时表t，让t表和salgrade s表连接，
条件是：t.avgsal between s.losal and s.hisal

select 
	t.*,s.grade
from
	(select deptno,avg(sal) as avgsal from emp group by deptno) t
join
	salgrade s
on
	t.avgsal between s.losal and s.hisal;

案例：找出每个部门平均的薪水等级(这个案例不需要看作临时表)。

第一步：找出每个员工的薪水等级。

select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

第二步：基于以上结果，继续按照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;

3.在select后面嵌套子查询 (很少用)。

案例：找出每个员工所在的部门名称，要求显示员工名和部门名。

方法一：等值内连接

select 
	e.ename,d.dname
from
	emp e
join
	dept d
on
	e.deptno = d.deptno;

方法二：select后面子嵌套

select 
	e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname 
from 
	emp e;

4.union （可以将查询结果集相加）

案例：找出工作岗位是SALESMAN和MANAGER的员工？

第一种：select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
第二种：select ename,job from emp where job in('MANAGER','SALESMAN');

第三种：union

select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';

只能用union的情况：
案例：两张不相干的表中的数据拼接在一起显示？

select ename from emp
union
select dname from dept;

注意：两张表查询结果的列数必须一样

5.limit (重点中的重点，以后分页查询全靠它了。)

limit是mysql特有的，其他数据库中没有，不通用。（Oracle中有一个相同的机制，叫做rownum）

作用：limit取结果集中的部分数据，这是它的作用。

语法机制：
limit startIndex, length

• startIndex表示起始位置，从0开始，0表示第一条数据。
• length表示取几个

1.案例：取出工资前5名的员工（思路：降序取前5个）

		select ename,sal from emp order by sal desc;

取前5个：

select ename,sal from emp order by sal desc limit 0, 5;
select ename,sal from emp order by sal desc limit 5;   //startIndex省略表示从0开始

注意：limit是sql语句最后执行的一个环节：

select			5
		...
from			1
		...		
where			2
		...	
group by		3
		...
having			4
		...
order by		6
		...
limit			7
		...;

2.案例：找出工资排名在第4到第9名的员工？

select ename,sal from emp order by sal desc limit 3,6;

通用的标准分页sql？

每页显示3条记录：
第1页：0, 3
第2页：3, 3
第3页：6, 3
第4页：9, 3
第5页：12, 3

每页显示pageSize条记录：

第pageNo页：(pageNo - 1) * pageSize, pageSize

pageSize:是每页显示多少条记录
pageNo :显示第几页

java代码{
	int pageNo = 2; // 页码是2
	int pageSize = 10; // 每页显示10条
	
	limit (pageNo - 1) * pageSize, pageSize
}