MySQL基础篇：如何利用分组查询和子查询优化你的数据库操作-摩杜云开发者社区

MySQL基础篇：如何利用分组查询和子查询优化你的数据库操作_子查询

分组查询主要涉及到两个子句，分别是：group by和having

group by

取得每个工作岗位的工资合计，要求显示岗位名称和工资合计

mysql> select JOB,sum(SAL) from EMP group by JOB;
+-----------+----------+
| JOB       | sum(SAL) |
+-----------+----------+
| ANALYST   |  6000.00 |
| CLERK     |  4150.00 |
| MANAGER   |  8275.00 |
| PRESIDENT |  5000.00 |
| SALESMAN  |  5600.00 |
+-----------+----------+
5 rows in set (0.29 sec)

having

如果想对分组数据再进行过滤需要使用having子句。

取得每个岗位的平均工资大于2000

mysql> select JOB,avg(SAL) from EMP group by JOB having avg(SAL)>2000;
+-----------+-------------+
| JOB       | avg(SAL)    |
+-----------+-------------+
| ANALYST   | 3000.000000 |
| MANAGER   | 2758.333333 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+
3 rows in set (0.20 sec)

mysql>

分组函数的执行顺序：

根据条件查询数据

分组

采用having过滤，取得正确的数据

select语句总结

一个完整的select语句格式如下

select 字段
from 表名
where …….
group by ……..
having …….(就是为了过滤分组后的数据而存在的—不可以单独的出现)
order by ……..

以上语句的执行顺序

1. 首先执行where语句过滤原始数据

2. 执行group by进行分组

3. 执行having对分组数据进行操作

4. 执行select选出数据

5. 执行order by排序

原则：能在where中过滤的数据，尽量在where中过滤，效率较高。having的过滤是专门对分组之后的数据进行过滤的。

子查询

子查询就是嵌套的select语句，可以理解为子查询是一张表

在where语句中使用子查询，也就是在where语句中加入select语句

查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名

实现思路：

首先取得管理者编号，去除重复的

mysql> select distinct MGR from EMP where MGR is not null;
+------+
| MGR  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+

查询员工编号包含管理者编号的

mysql> select EMPNO,ENAME from EMP where EMPNO in (select distinct  MGR from EMP where MGR is not null);
+-------+-------+
| EMPNO | ENAME |
+-------+-------+
|  7902 | FORD  |
|  7698 | BLAKE |
|  7839 | KING  |
|  7566 | JONES |
|  7788 | SCOTT |
|  7782 | CLARK |
+-------+-------+
6 rows in set (0.28 sec)

查询哪些人的薪水高于员工的平均薪水，需要显示员工编号，员工姓名，薪水

实现思路：

1、取得平均薪水

mysql> select avg(SAL) from EMP;
+-------------+
| avg(SAL)    |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.30 sec)

2、取得大于平均薪水的员工

mysql> select EMPNO,ENAME,SAL from EMP where SAL >(select avg(SAL) from EMP);
+-------+-------+---------+
| EMPNO | ENAME | SAL     |
+-------+-------+---------+
|  7566 | JONES | 2975.00 |
|  7698 | BLAKE | 2850.00 |
|  7782 | CLARK | 2450.00 |
|  7788 | SCOTT | 3000.00 |
|  7839 | KING  | 5000.00 |
|  7902 | FORD  | 3000.00 |
+-------+-------+---------+
6 rows in set (0.30 sec)

在from语句中使用子查询，可以将该子查询看做一张表

查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名。

1、首先取得管理者的编号，去除重复的。

mysql> select distinct MGR from EMP where MGR is not null;
+------+
| MGR  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+

将以上查询作为一张表，放到from语句的后面。

使用92语法：
select e.empno, e.ename from emp e, (select distinct mgr from emp where mgr is not null) m where e.empno=m.mgr;
使用99语法：
select e.empno, e.ename from emp e join (select distinct mgr from emp where mgr is not null) m on e.empno=m.mgr;

查询各个部门的平均薪水所属等级，需要显示部门编号，平均薪水，等级编号

实现思路

1、首先取得各个部门的平均薪水

mysql> select DEPTNO,avg(SAL) from EMP group by DEPTNO;
+--------+-------------+
| DEPTNO | avg(SAL)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.21 sec)

2、将部门的平均薪水作为一张表与薪水等级表建立连接，取得等级

mysql> select a.DEPTNO,a.avg_sal,g.GRADE from (select DEPTNO,avg(SAL) as avg_sal from EMP group by DEPTNO) a join SALGRADE g on a.avg_sal between g.LOSAL AND g.HISAL;
+--------+-------------+-------+
| DEPTNO | avg_sal     | GRADE |
+--------+-------------+-------+
|     10 | 2916.666667 |     4 |
|     20 | 2175.000000 |     4 |
|     30 | 1566.666667 |     3 |
+--------+-------------+-------+
3 rows in set (0.68 sec)

在select语句中使用子查询

查询员工信息，并显示出员工所属的部门名称。

第一种做法，将员工表和部门表连接。

mysql> select  e.ENAME,d.DNAME  from DEPT d,EMP e where e.DEPTNO=d.DEPTNO;
+--------+------------+
| 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.29 sec)

第二种做法，在select语句中再次嵌套select语句完成部分名称的查询。

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.59 sec)

union

union可以合并集合（相加）

查询job包含MANAGER和包含SALESMAN的员工。

mysql> select * from EMP where JOB in ("SALESMAN","MANAGER");
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  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 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
7 rows in set (0.24 sec)

mysql>

采用union来合并

mysql> select * from EMP where JOB="SALESMAN"
    -> union
    -> select * from EMP where JOB="MANAGER";
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  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 |
+-------+--------+----------+------+------------+---------+---------+--------+
7 rows in set (0.24 sec)

limit 的使用

mySql提供了limit ，主要用于提取前几条或者中间某几行数据。

select * from table limit m,n

其中m是指记录开始的index，从0开始，表示第一条记录

n是指从第m+1条开始，取n条。

取得前5条数据

mysql> select * from EMP limit 5;
+-------+--------+----------+------+------------+---------+---------+--------+
| 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 |
+-------+--------+----------+------+------------+---------+---------+--------+
5 rows in set (0.22 sec)

从第二条开始取两条数据

mysql> select * from EMP limit 1,2;
+-------+-------+----------+------+------------+---------+--------+--------+
| EMPNO | ENAME | JOB      | MGR  | HIREDATE   | SAL     | COMM   | DEPTNO |
+-------+-------+----------+------+------------+---------+--------+--------+
|  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 |     30 |
|  7521 | WARD  | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 |     30 |
+-------+-------+----------+------+------------+---------+--------+--------+
2 rows in set (0.22 sec)

取得薪水最高的前5名

mysql> select * from EMP order by SAL desc limit 5;
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
+-------+-------+-----------+------+------------+---------+------+--------+
5 rows in set (0.19 sec)