MySQL 子查询以及limit分页查询

1.子查询

​ 什么是子查询：select语句中嵌套select语句，被嵌套的select语句就是子查询。

​ 子查询可以出现在哪里：select，from，where

​ 1.1、where后使用子查询

​ 案例：找出高与平均薪资的员工信息

select ename ,sal from emp where sal>(select avg(sal) from emp);
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)

​ 1.2、from后使用子查询

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

select e.deptno,s.grade,e.avg(sal)
from 
/*1.找出每个部门平均薪水（按照部门编号分组）*/
select deptno,avg(sal) as avgsal from emp  group by deptno;
/*2.找出对应的薪资等级*/
select e1.deptno,e1.avgsal,s.grade 
from (select deptno,avg(sal) as avgsal from emp  group by deptno) e1
join salgrade s on e1.avgsal between s.losal and s.hisal;
/*3.找出对应的部门名称*/
select d.dname,x.avgsal,x.grade
from(select e1.deptno,e1.avgsal,s.grade 
from (select deptno,avg(sal) as avgsal from emp  group by deptno) e1
join salgrade s on e1.avgsal between s.losal and s.hisal) x
join dept d
on x.deptno = d.deptno;

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

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

/*1.先每个人的薪水等级按照*/
select e.deptno ,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
/*2.利用avg 并且分组*/
select t.deptno, avg(t.grade) 
from (select e.deptno ,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) t
group by deptno;
+--------+--------------+
| deptno | avg(t.grade) |
+--------+--------------+
|     20 |       2.8000 |
|     30 |       2.5000 |
|     10 |       3.6667 |
+--------+--------------+
3 rows in set (0.00 sec)

​ 1.3、在select后面使用子查询

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

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

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

2.union (这个可以将查询结果集相加)

​ union可以将两个结果集拼接到一起

例如

select ename from emp
union
select dname from dept;
+------------+
| ename      |
+------------+
| SMITH      |
| ALLEN      |
| WARD       |
| JONES      |
| MARTIN     |
| BLAKE      |
| CLARK      |
| SCOTT      |
| KING       |
| TURNER     |
| ADAMS      |
| JAMES      |
| FORD       |
| MILLER     |
| ACCOUNTING |
| RESEARCH   |
| SALES      |
| OPERATIONS |
+------------+
18 rows in set (0.00 sec)

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

select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';
+--------+----------+
| ename  | job      |
+--------+----------+
| JONES  | MANAGER  |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)

union可以将两个毫无关系的表 拼接 在一起显示。

表名会用第一个查询结果的名字

注意：union只能将两个 相同列数 的表拼接在一起，不同列数会报错。

3.limit以及通用分页SQL （重点！）

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

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

3.3、语法机制：

​ limit startIndex,length

​ startIndex 表示起始位置， length 表示取几个

案例：取出工资前五名 （降序取前五个）

select ename,sal from emp order by sal desc;
select ename,sal from emp order by sal desc limit 0,5;

/*如果前面不写起始位置，那么默认就是0*/
select ename,sal from emp order by sal desc limit 5;

mysql> select ename,sal from emp order by sal desc limit 0,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是sql语句最后执行的一个环节

语句	执行内容	执行优先级
select …	查找那些字段	5
from …	来自于哪张表	1
where …	查找的条件	2
group by …	按照什么分组	3
having …	分组后还需要过滤什么	4
order by …	按照什么顺序来显示	6
limit …	取那些数据	7

3.4、案例：找出工资排名在第4到第9的员工 第4个是从3开始的 4-9是6个

select ename ,sal from emp order by sal desc limit 3,6;
+--------+---------+
| ename  | sal     |
+--------+---------+
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
+--------+---------+
6 rows in set (0.00 sec)

3.5、通用的标准SQL

每页显示 3 条记录：

第一页：0, 3

第二页：3, 3

第三页：6, 3

第四页：9, 3

每页显示pageSize条记录：

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

java 代码{

​ int pageNo = 2; //第 n 页

​ int pageSize = 10; //每页多少条数据

​ limit (pageNo - 1) * pageSize , pageSize;

}