mysql+avg+limit_MySQL学习-子查询及limit分页

操作的表

mysql> select * from emp;

+-------+--------+-----------+------+------------+---------+---------+--------+

| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |

+-------+--------+-----------+------+------------+---------+---------+--------+

| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |

| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |

| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |

| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |

| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |

| 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 |

| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |

| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |

| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |

| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |

| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |

+-------+--------+-----------+------+------------+---------+---------+--------+

其每个字段的字段名分别为员工编号、员工姓名、员工职位、员工上级领导编号、员工雇佣日期，员工薪水、员工津贴、员工部门编号。

mysql> select * from dept;

+--------+------------+----------+

| DEPTNO | DNAME | LOC |

+--------+------------+----------+

| 10 | ACCOUNTING | NEW YORK |

| 20 | RESEARCH | DALLAS |

| 30 | SALES | CHICAGO |

| 40 | OPERATIONS | BOSTON |

+--------+------------+----------+

其每个字段的字段名分别部门编号、部门名称、位置。

mysql> select * from salgrade;

+-------+-------+-------+

| GRADE | LOSAL | HISAL |

+-------+-------+-------+

| 1 | 700 | 1200 |

| 2 | 1201 | 1400 |

| 3 | 1401 | 2000 |

| 4 | 2001 | 3000 |

| 5 | 3001 | 9999 |

+-------+-------+-------+

其中每个字段的字段名分别为等级、该等级薪水下限、该等级薪水上限

where后面嵌套子查询

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

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

子查询可以出现在哪里？

select

…(select).

from

…(select).

where

…(select).

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

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

第一步：找出平均薪资

select avg(sal) from emp;

+-------------+

| avg(sal) |

+-------------+

| 2073.214286 |

+-------------+

第二步：where过滤

select * from emp where sal > 2073.214286;

+-------+-------+-----------+------+------------+---------+------+--------+

| 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 |

+-------+-------+-----------+------+------------+---------+------+--------+

第一步和第二步合并：

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 |

+-------+-------+-----------+------+------------+---------+------+--------

from后面嵌套子查询

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

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

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

+--------+-------------+

| deptno | avgsal |

+--------+-------------+

| 10 | 2916.666667 |

| 20 | 2175.000000 |

| 30 | 1566.666667 |

+--------+-------------+

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

SELECT

t.*,s.grade

from

t

JOIN

salgrade s

on

t.avgsal BETWEEN s.LOSAL and s.HISAL;

而t暂时未知，所以我们需要把第一步的查询结果当成表t，改成如下所示

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;

+--------+-------------+-------+

| deptno | avgsal | grade |

+--------+-------------+-------+

| 10 | 2916.666667 | 4 |

| 20 | 2175.000000 | 4 |

| 30 | 1566.666667 | 3 |

+--------+-------------+-------+

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

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

select e.deptno,e.ename,e.sal,s.grade from emp e join salgrade s on e.SAL BETWEEN s.LOSAL and s.HISAL

+--------+--------+---------+-------+

| deptno | ename | sal | grade |

+--------+--------+---------+-------+

| 20 | SMITH | 800.00 | 1 |

| 30 | ALLEN | 1600.00 | 3 |

| 30 | WARD | 1250.00 | 2 |

| 20 | JONES | 2975.00 | 4 |

| 30 | MARTIN | 1250.00 | 2 |

| 30 | BLAKE | 2850.00 | 4 |

| 10 | CLARK | 2450.00 | 4 |

| 20 | SCOTT | 3000.00 | 4 |

| 10 | KING | 5000.00 | 5 |

| 30 | TURNER | 1500.00 | 3 |

| 20 | ADAMS | 1100.00 | 1 |

| 30 | JAMES | 950.00 | 1 |

| 20 | FORD | 3000.00 | 4 |

| 10 | MILLER | 1300.00 | 2 |

+--------+--------+---------+-------+

第二步：基于以上结果，继续按照deptno分组，求grade平均值。

select

t.deptno,avg(t.grade)

from

(select e.deptno,e.ename,e.sal,s.grade from emp e join salgrade s on e.SAL BETWEEN s.LOSAL and s.HISAL) t

group BY

t.deptno;

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 |

| JONES | RESEARCH |

| MARTIN | SALES |

| BLAKE | SALES |

| CLARK | ACCOUNTING |

| SCOTT | RESEARCH |

| KING | ACCOUNTING |

| TURNER | SALES |

| ADAMS | RESEARCH |

| JAMES | SALES |

| FORD | RESEARCH |

| MILLER | ACCOUNTING |

+--------+------------+

union的用法

union (可以将查询结果集相加)

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

第一种：select ename,job from emp where job = ‘MANAGER’ or job = ‘SALESMAN’;

第二种：select ename,job from emp where job in(‘MANAGER’,‘SALESMAN’);

第三种：

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 |

+--------+----------+

两张不相干的表中的数据拼接在一起显示？

select ename from emp

union

select dname from dept;

第一个查询结果得数量要和第二个查询结果得列数一致

mysql> select ename,sal from emp

-> union

-> select dname from dept;

ERROR 1222 (21000): The used SELECT statements have a different number of columns

limit以及通用分页SQL

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

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

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

语法机制：

limit startIndex, length

startIndex表示起始位置，从0开始，0表示第一条数据。

length表示取几个

案例：取出工资前5名的员工(思路：降序取前5个)

select ename,sal from emp ORDER BY sal desc limit 0,5

select ename,sal from emp ORDER BY sal desc limit 5(取前5个)

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

select5

...

from1

...

where2

...

group by3

...

having4

...

order by6

...

limit7

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

+---------+

| sal |

+---------+

| 2975.00 |

| 2850.00 |

| 2450.00 |

| 1600.00 |

| 1500.00 |

| 1300.00 |

+---------+

通用的标准分页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

}