MYSQL学习笔记(三)：分组、排序、分页查询

前言：

• 学习和使用数据库可以说是程序员必须具备能力，这里将更新关于MYSQL的使用讲解，大概应该会更新30篇+，涵盖入门、进阶、高级(一些原理分析);
• 这一篇是讲解分组、排序、分页查询，并且结合案例进行讲解；
• 虽然MYSQL命令很多，但是自己去多敲一点，到后面忘记了，查一下就可以回忆起来使用了；
• 这一系列也是本人学习MYSQL做的笔记，也是为了方便后面忘记查询；
• 参考资料：尚硅谷、黑马、csdn和知乎博客；
• 欢迎收藏 + 关注，本人将会持续更新。

课程使用数据文件: 后台私信获取。

分组查询

什么是分组呢？

分组这个概念在生活中其实是很常见的，比如有以下需求：

• 1.在一个班级之中，要求男女各一组进行辩论赛
• 2.在公司中，要求每个部门一组进行拔河比赛

👓 解决：

• 对于第一个需求，假设存在学生表，那么在学生表之中一定会存在一个性别字段，性别只可能是男或者女。
• 而在公司之中，如果要进行部门分组，肯定需要一个部门列，存储员工所在部门

🚯 注意：

• 分组只对数据有重复的字段才有意义

🛋 语法

SELECT 分组字段/聚合函数 FROM 表名 [WHERE 条件] GROUP BY 分组字段 [HAVING 分组后条件];

• 要注意：where、having的使用位置，条件。

WHERE和HAVING区别

• WHERE：是在分组之前使用(可以没有GROUP BY)，不允许使用统计函数；WHERE 主要用来过滤行数据
• HAVING：是在分组之后使用(必须结合GROUP BY)，允许使用统计函数，HAVING 主要用于对聚合函数（如 COUNT(), SUM(), AVG() 等）的结果进行条件筛选。

案例实践

1. 统计出每个工作岗位的人数

select job '工作',count(job) '人数' from emp group by job;
/*
+-----------+------+
| 工作      | 人数 |
+-----------+------+
| CLERK     |    4 |
| SALESMAN  |    4 |
| MANAGER   |    3 |
| ANALYST   |    2 |
| PRESIDENT |    1 |
+-----------+------+
5 rows in set (0.00 sec)
*/

2. 统计出每种职位的最低和最高工资

select min(sal) 'min_salary', max(sal) 'max_salary' from emp group by job;
/*
+------------+------------+
| min_salary | max_salary |
+------------+------------+
|        800 |       1300 |
|       1250 |       1600 |
|       2450 |       2975 |
|       3000 |       3000 |
|       5000 |       5000 |
+------------+------------+
5 rows in set (0.00 sec)
*/

🔬 注意事项

1. 如果ya查询之中不存在GROUP BY子句，那么在SELECT子句之中如果出现统计函数，其他任何字段都不允许出现。

#错误的语句
SELECT ename,COUNT(job) FROM emp;
#正确的语句
SELECT COUNT(job) FROM emp;

2. 在统计查询之中(存在GROUP BY子句)，SELECT子句之中只允许出现分组字段(GROUP BY之后定义后含有的字段)和统计函数，其他的任何字段都不允许出现。

#错误的语句
SELECT ename,COUNT(job) FROM emp GROUP BY job;
#正确的语句
SELECT job,COUNT(job) FROM emp GROUP BY job;

总的来说，只允许出现统计函数和group by后面跟的字段。

---

查询需求

1. 显示不同职位薪资少于1500的人数

select job, count(ename) 'nums' from emp where sal < 1500 group by job;
/*
+----------+------+
| job      | nums |
+----------+------+
| CLERK    |    4 |
| SALESMAN |    2 |
+----------+------+
2 rows in set (0.00 sec)
*/

1. 显示非销售人员(salesman)工作名称以及从事同一工作的员工的月工资的总和，并且要满足从事同一工作的员工的月工资合计大于等于5000。

# 分析：拆成两部分
-- 显示非销售人员(salesman)工作名称以及从事同一工作的员工的月工资的总和
-- 并且要满足从事同一工作的员工的月工资合计大于等于5000select job, sum(sal) as sal_sum from emp where job != 'salesman' group by job having 
sum(sal) >= 5000;
/*
+-----------+---------+
| job       | sal_sum |
+-----------+---------+
| MANAGER   |    8275 |
| ANALYST   |    6000 |
| PRESIDENT |    5000 |
+-----------+---------+
3 rows in set (0.00 sec)
*/

排序查询

通过条件查询语句可以查询到符合用户需求的数据，但是查询到的数据一般都是按照数据最初被添加到表中的顺序来显示。为了使查询结果的顺序满足用户的要求，MySQL 提供了 ORDER BY 关键字来对查询结果进行排序。

在实际应用中经常需要对查询结果进行排序，比如，在网上购物时，可以将商品按照价格进行排序；在医院的挂号系统中，可以按照挂号的先后顺序进行排序等。

语法

SELECT 字段名 FROM 表名 ... ORDER BY 排序字段名 [ASC|DESC],[排序字段名 [ASC|DESC]];

语法说明：

• 排序字段名：表示需要排序的字段名称，多个字段时用逗号隔开。
• ASC|DESC：ASC表示字段按升序排序；DESC表示字段按降序排序。其中ASC为默认值。

特点

• ORDER BY子句一般放到查询语句的最后面。
• 当排序的字段中存在空值时，ORDER BY 会将该空值作为最小值来对待。
• ORDER BY 指定多个字段进行排序时，MySQL 会按照字段的顺序从左到右依次进行排序。
• 注意：在对多个字段进行排序时，排序的第一个字段必须有相同的值，才会对第二个字段进行排序,如果第一个字段数据中所有的值都是唯一的，MySQL 将不再对第二个字段进行排序。

项目案例

1. 根据姓名对员工进行排序(升序)

select * from emp order by ename asc;/*
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal  | comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100 | NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 |  300 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950 | NULL |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000 | NULL |     20 |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800 | NULL |     20 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 |    0 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 |  500 |     30 |
+-------+--------+-----------+------+------------+------+------+--------+
14 rows in set (0.00 sec)
*/

2. 查询入职时间大于1981年6月6日的员工信息，并按照入职时间升序排列

select * from emp where hiredate > '1981-06-06' order by hiredate asc;
/*
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal  | comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 |    0 |     30 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950 | NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000 | NULL |     20 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100 | NULL |     20 |
+-------+--------+-----------+------+------------+------+------+--------+
9 rows in set (0.00 sec)
*/

3. 根据工作职位，进行升序排列，职位相同的情况下，再根据入职时间降序排列

select * from emp order by job asc, hiredate desc;
/*
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal  | comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000 | NULL |     20 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300 | NULL |     10 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950 | NULL |     30 |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800 | NULL |     20 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 |    0 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 |  500 |     30 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 |  300 |     30 |
+-------+--------+-----------+------+------------+------+------+--------+
14 rows in set (0.00 sec)
*/

4. 查询工资不在1000到2000之间的员工的姓名和工资，工资按降序排列

select ename, sal from emp where sal > 1000 and sal > 2000 order by sal desc;/*
+-------+------+
| ename | sal  |
+-------+------+
| KING  | 5000 |
| SCOTT | 3000 |
| FORD  | 3000 |
| JONES | 2975 |
| BLAKE | 2850 |
| CLARK | 2450 |
+-------+------+
6 rows in set (0.00 sec)
*/

5. 按工作职位进行分组，查看每个工作职位的人数，工作职位按升序排列

select ename, sal from emp where sal > 1000 and sal > 2000 order by sal desc;
/*
+-------+------+
| ename | sal  |
+-------+------+
| KING  | 5000 |
| SCOTT | 3000 |
| FORD  | 3000 |
| JONES | 2975 |
| BLAKE | 2850 |
| CLARK | 2450 |
+-------+------+
6 rows in set (0.00 sec)
*/

分页查询

对于比较多的数据，如果在一个页面全部显示，查看起来会眼花缭乱。如果能进行分页显示，将更加快捷、清新的浏览，如下图所示。

语法

SELECT 字段名 FROM 表名 LIMIT 起始索引, 查询记录数; #起始索引代表从第几条开始

特点

• 起始索引从0开始，起始索引 = (查询页码 - 1) * 每页显示记录数;
• 如果查询的是第一页数据，起始索引可以省略，直接简写为LIMIT 10;
• limit使用位置一般在最后一个。

优点：

• 约束返回结果的数量可以减少数据表的网络传输量 ，也可以提升查询效率，核心：提高效率，有时候还需要结合索引一起使用。

查询需求

1. 查询第一页员工数据，每页展示5条记录

select * from emp limit 0, 5;
/*
+-------+--------+----------+------+------------+------+------+--------+
| empno | ename  | job      | mgr  | hiredate   | sal  | comm | deptno |
+-------+--------+----------+------+------------+------+------+--------+
|  7369 | SMITH  | CLERK    | 7902 | 1980-12-17 |  800 | NULL |     20 |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600 |  300 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250 |  500 |     30 |
|  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 | 2975 | NULL |     20 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 |     30 |
+-------+--------+----------+------+------------+------+------+--------+
5 rows in set (0.00 sec)
*/

2. 查询工资大于1500的员工数据，按工资升序排列，并分页，每页展示5条数据

select * from emp where sal > 1500 order by sal asc limit 0, 5;
/*
+-------+-------+----------+------+------------+------+------+--------+
| empno | ename | job      | mgr  | hiredate   | sal  | comm | deptno |
+-------+-------+----------+------+------------+------+------+--------+
|  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 |  300 |     30 |
|  7782 | CLARK | MANAGER  | 7839 | 1981-06-09 | 2450 | NULL |     10 |
|  7698 | BLAKE | MANAGER  | 7839 | 1981-05-01 | 2850 | NULL |     30 |
|  7566 | JONES | MANAGER  | 7839 | 1981-04-02 | 2975 | NULL |     20 |
|  7788 | SCOTT | ANALYST  | 7566 | 1987-04-19 | 3000 | NULL |     20 |
+-------+-------+----------+------+------------+------+------+--------+
5 rows in set (0.00 sec)
*/