Mysql 单表查询-排序-分页-group by初识

mysql 单表查询-排序-分页-group by初识

对于select 来说, 分组聚合(((group by; aggregation), 排序 (order by** ), 分页查询 (limit), 等这些操作, 都是结合 where 过滤(算术表达式, 逻辑表达式, 判空, 范围过滤, 模糊查询), 来进一步对数据集为所欲为地操作哦, group by 也是我工作中每天都必用的, 因为我每天要拆分数据嘛, 先按一或多个字段 group by, 然后, 再对每组的抑或多个字段进行聚合(sum, avg, max, quartile...)等.

直接来理解sql分组聚合, 一开始还是不容易的, 一个是对group by 不熟, 另一个对sql语句不熟. 针对这点, 我是每天都在用excel的透视表, 各种透视, 多条件透视, 整吐了过后, 就彻底明白了. 虽然我是有些讨厌excel, 但不得不说, 它能让你直观地理解很多数据, 数据结构, 编程相关的概念.

数据依然是上篇, students表(where 初识那一篇).

准备测试数据

-- win 下, 进入终端 (cmd + r)
mysql -u root -p

c:\users\chenjie>mysql -u root -p
enter password: ********

-- 再重新来手打一遍吧, 一点经验之谈, 对我学习方法就是, 多抄代码, 抄多了, 手就有感觉了, 跟打字一样的, 变成了一种下意识, 而非脑袋过一遍就觉得懂了哦,真的是唯手熟尔哦.

-- 创建数据库
drop database if exists student_db;
create database student_db charset=utf8;

use student_db;

-- 创建学生表-测试
create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default "",
    age tinyint unsigned default 0, -- 建议给默认值, null查询慢
    height decimal(5,2),  -- 共5位, 小数占2位
    gender enum("男", "女", "未填写"),
    class_id int unsigned default 1,
    is_delete bit default 0
);

-- 班级表
drop table if exists classes;
create table classes(
    id int unsigned auto_increment primary key not null,
    name varchar(20) not null
);

-- 插入偶像, 用于测试哦只是, 别无它意
insert into students values
(0,'爱因斯坦',18,180.00,1,1,0),
(0,'居里夫人',18,180.00,2,2,1),
(0,'小王子',14,185.00,1,1,0),
(0,'李银河',59,175.00,1,2,1),
(0,'黄蓉',38,160.00,2,1,0),
(0,'冰心',28,150.00,2,2,1),
(0,'王祖贤',18,172.00,2,1,1),
(0,'周杰伦',36,null,1,1,0),
(0,'王小波',57,181.00,1,2,0),
(0,'林徽因',25,166.00,2,2,0),
(0,'小星',33,162.00,3,3,1),
(0,'张爱玲',12,180.00,2,4,0),
(0,'冯唐',12,170.00,1,4,0),
(0,'胡适',34,176.00,2,5,0);

insert into classes values
(0, "科学"),
(0, "艺术");

-- 如果失败, 要么就是语法不对, 要么就是 没有开服服务
query ok, 14 rows affected (0.03 sec)
records: 14  duplicates: 0  warnings: 0

mysql>
mysql> insert into classes values
    -> (0, "科学"),
    -> (0, "艺术");
query ok, 2 rows affected (0.04 sec)
records: 2  duplicates: 0  warnings: 0

-- 测试
select * from students;
select * from calsses;

+----+--------------+------+--------+-----------+----------+-----------+
| id | name         | age  | height | gender    | class_id | is_delete |
+----+--------------+------+--------+-----------+----------+-----------+
|  1 | 爱因斯坦     |   18 | 180.00 | 男        |        1 |           |
|  2 | 居里夫人     |   18 | 180.00 | 女        |        2 |          |
|  3 | 小王子       |   14 | 185.00 | 男        |        1 |           |
|  4 | 李银河       |   59 | 175.00 | 男        |        2 |          |
|  5 | 黄蓉         |   38 | 160.00 | 女        |        1 |           |
|  6 | 冰心         |   28 | 150.00 | 女        |        2 |          |
|  7 | 王祖贤       |   18 | 172.00 | 女        |        1 |          |
|  8 | 周杰伦       |   36 |   null | 男        |        1 |           |
|  9 | 王小波       |   57 | 181.00 | 男        |        2 |           |
| 10 | 林徽因       |   25 | 166.00 | 女        |        2 |           |
| 11 | 小星         |   33 | 162.00 | 未填写    |        3 |          |
| 12 | 张爱玲       |   12 | 180.00 | 女        |        4 |           |
| 13 | 冯唐         |   12 | 170.00 | 男        |        4 |           |
| 14 | 胡适         |   34 | 176.00 | 女        |        5 |           |
+----+--------------+------+--------+-----------+----------+-----------+
14 rows in set (0.00 sec)

mysql> select * from classes;
+----+--------+
| id | name   |
+----+--------+
|  1 | 科学   |
|  2 | 艺术   |
+----+--------+

-- 手抖了, 改下 胡适先生的性别
update students as s
set s.gender := "男"
where s.name = "胡适";

query ok, 1 row affected (0.10 sec)
rows matched: 1  changed: 1  warnings: 0

-- 检测一波
select s.name, s.gender
from students s  -- 养成 as 取别名的习惯, 为后面多张表连接预热哦
where s.name = "胡适";

+--------+--------+
| name   | gender |
+--------+--------+
| 胡适   | 男     |
+--------+--------+
1 row in set (0.00 sec)

排序 order by

• order by 字段
• desc 降序
• asc 升序, 默认是asc

-- 查询18到28之间的女生姓名及身高, 并按年龄降序
select s.name, s.height
from students as s
where (age between 18 and 24) and (gender = "女")
order by age desc;

+--------------+--------+
| name         | height |
+--------------+--------+
| 居里夫人     | 180.00 |
| 王祖贤       | 172.00 |
+--------------+--------+
2 rows in set (0.00 sec)

-- 查询年龄在18到35岁的男性姓名, 年龄,身高, 班级, 要求按身高降序, 身高相同则按年龄升序
select s.name, s.age, s.height, s.class_id
from students as s
where (age between 18 and 35) and (gender = "男")
order by height desc, age asc;

+--------------+------+--------+----------+
| name         | age  | height | class_id |
+--------------+------+--------+----------+
| 爱因斯坦      |   18 | 180.00 |        1 |
| 胡适         |   34 | 176.00 |        5 |
+--------------+------+--------+----------+

-- 查询18-28的女生, 要求按身高降序, 若身高相同则按年龄升序, 身高相同则按id降序
select * 
from students s  -- as 可以省略的
where age between 18 and 28 and gender = "女"
order by height desc, age asc, id desc;

+----+----------+-----+--------+--------+----------+-----------+
| id | name     | age | height | gender | class_id | is_delete |
+----+----------+-----+--------+--------+----------+-----------+
|  2 | 居里夫人 |  18 | 180.00 | 女     |        2 | 1         |
|  7 | 王祖贤   |  18 | 172.00 | 女     |        1 | 1         |
| 10 | 林徽因   |  25 | 166.00 | 女     |        2 | 0         |
|  6 | 冰心     |  28 | 150.00 | 女     |        2 | 1         |
+----+----------+-----+--------+--------+----------+-----------+
4 rows in set (0.06 sec)

分页查询 limit

为啥要分页查询呢, 就好比我们平时浏览网页时, 一下也看不完那么多内容的, 网页内容也是随用户看一点, 加载一点, 这样, 边看边查询, 就不会给服务器太大压力了呀.

-- 语法: limit start=0, count;
-- 需求: 每页显示 m 条数据, 当前显示第 n 页;
-- 原理: 第 n 页前有(n-1)页数据, 即(n-1)*m 条数据, 即第n页的的小标 start = (n-1)*m;
-- 案例: 假设有10条数据, 要求每页显示2条数据, 要显示第3页数则, start=(3-1)*2=4, 即 limit 4;

-- 查询前3个数据
select * from students limit 3

-- 查询第3个页面, 每页显示2个数据
-- start = (3-1)* = 4
select * from students limit 4, 2;

+----+------+-----+--------+--------+----------+-----------+
| id | name | age | height | gender | class_id | is_delete |
+----+------+-----+--------+--------+----------+-----------+
|  5 | 黄蓉 |  38 | 160.00 | 女     |        1 | 0         |
|  6 | 冰心 |  28 | 150.00 | 女     |        2 | 1         |
+----+------+-----+--------+--------+----------+-----------+

-- 查询第4页, 每页显示3个学生的姓名, 班级
-- start = (4-1)*3 = 9
select s.name, s.class_id
from students s
limit 9, 3;

+--------+----------+
| name   | class_id |
+--------+----------+
| 林徽因 |        2 |
| 小星   |        3 |
| 张爱玲 |        4 |
+--------+----------+
3 rows in set (0.08 sec)

分组 group by

分组聚合是数据分析的基础, 就我平时的工作而言, 天天都是在分组聚合, 俗称"拉一下数据, 透视一下表, 拆分数更细",都是一个道理, 先分组, 再聚合.

以excel表为例, 说明一下字段的值. 基本就两种, 类别值字段 和 连续值字段, 比如年龄就是一个连续值字段, 可以对其求平均值, 最大值等(当然也可以离散化). 而对于性别来讲, 就是分类值字段, 要么男性, 要么女性, 要么...反正数得过来, 一般很少听到, 对性别求平均, 求最值, 大概率是没有滴吧.

常见聚合函数

max, min, sqrt, sum, avg, count ...等

-- 最高的身高是多少
select max(height) from students;
+-------------+
| max(height) |
+-------------+
| 185.00      |
+-------------+

-- 身高最高的女生姓名,身高, 年龄 (提前剧透一下子查询吧)
-- step1: 先查询最高的女性身高, 得到一个标量值
select max(s.height)
from students as s
where gender="女";

+---------------+
| max(s.height) |
+---------------+
| 180.00        |
+---------------+

-- step2: 将该值作为where条件即可
select s.name, s.age, s.height
from students as s
where height = (
    select max(s.height)
    from students as s
    where gender="女"
);

+----------+-----+--------+
| name     | age | height |
+----------+-----+--------+
| 爱因斯坦 |  18 | 180.00 |
| 居里夫人 |  18 | 180.00 |
| 张爱玲   |  12 | 180.00 |
+----------+-----+--------+

-- 所有学生的平均年龄, 当然可直接用avg函数
-- count(*)表示不忽略空值计算
select sum(age) / count(id)  as "平均年龄"
from students; 

+----------+
| 平均年龄 |
+----------+
| 28.7143  |
+----------+
1 row in set (0.07 sec)

-- 计算女性的平均身高, 并保留2位小数
select round(avg(s.height), 2) as "女生平均身高"
from students as s
where s.gender="女";

+--------------+
| 女生平均身高 |
+--------------+
| 168.00       |
+--------------+
1 row in set (0.06 sec)

分组groupby

分组就一种情景, 按单字段或多字段 分组, 不懂的请自行熟练excel透视表 就明白了.

-- 根据性别分组, 显示所有的年龄
select s.gender
from students as s
group by gender;

+--------+
| gender |
+--------+
| 男     |
| 女     |
| 未填写 |
+--------+
3 rows in set (0.06 sec)

-- 计算不同班级的人数各为多少
select s.class_id, count(id)  as "班级人数"
from students as s
group by class_id;

+----------+----------+
| class_id | 班级人数 |
+----------+----------+
|        1 |        5 |
|        2 |        5 |
|        3 |        1 |
|        4 |        2 |
|        5 |        1 |
+----------+----------+
5 rows in set (0.06 sec)

-- 计算每个班学生的平均年龄, 结果保留2位小数
select s.class_id as "班级编号", round(avg(age),2) as "平均年龄"
from students s
group by s.class_id;

+----------+----------+
| 班级编号 | 平均年龄 |
+----------+----------+
|        1 | 24.80    |
|        2 | 37.40    |
|        3 | 33.00    |
|        4 | 12.00    |
|        5 | 34.00    |
+----------+----------+
5 rows in set (0.07 sec)

-- 查询出每个班级中的每个学生的姓名
-- group_concat() 组内查询
select s.class_id, group_concat(s.name)
from students as s
group by class_id;

+----------+------------------------------------+
| class_id | group_concat(s.name)               |
+----------+------------------------------------+
|        1 | 爱因斯坦,小王子,黄蓉,王祖贤,周杰伦 |
|        2 | 居里夫人,李银河,冰心,王小波,林徽因 |
|        3 | 小星                               |
|        4 | 张爱玲,冯唐                        |
|        5 | 胡适                               |
+----------+------------------------------------+
5 rows in set (0.08 sec)

-- 查询平均年龄大于25岁的学生姓名, 性别
-- 方案1: 以性别分组, 然后 having 组内过滤
-- 方案2: 子查询
select gender, group_concat(name), avg(age)
from students as s
group by gender
having avg(age) >=25;

+--------+------------------------------------------------+----------+
| gender | group_concat(name)                             | avg(age) |
+--------+------------------------------------------------+----------+
| 男     | 爱因斯坦,小王子,李银河,周杰伦,王小波,冯唐,胡适 | 32.8571  |
| 未填写 | 小星                                           | 33.0000  |
+--------+------------------------------------------------+----------+
2 rows in set (0.07 sec)

-- 查询不同性别中, 人数多于2个的学生性别,身高
select gender, group_concat(name), count(id)
from students
group by gender
having count(id) > 2;

+--------+------------------------------------------------+-----------+
| gender | group_concat(name)                             | count(id) |
+--------+------------------------------------------------+-----------+
| 男     | 爱因斯坦,小王子,李银河,周杰伦,王小波,冯唐,胡适 |         7 |
| 女     | 居里夫人,黄蓉,冰心,王祖贤,林徽因,张爱玲        |         6 |
+--------+------------------------------------------------+-----------+
2 rows in set (0.08 sec)

-- with rollup 汇总, 不习惯sql汇总, 用其他编程语言来整更灵活方便呀
select gender,group_concat(name) 
from students 
group by gender
with rollup;

小结

• where 过滤, 结合排序, 分组, 聚合 这一套组合操作, 能满足更多的业务场景

• 排序: order by 默认升序asc; 降序desc

• 分页: limit; 语法: limit start=0, count; 查询第n页显示m个数据, 则 start = (n-1) * m

• 分类汇总, 先分类, 再汇总, 特别特重要, group by 结合聚合函数, excel的数据透视表玩6了就没问题.

• 常见聚合函数: min, max, sum, avg, count, round, sqrt .....

• group by 及理解组内过滤 group_concat 的内在逻辑.

• 预告: 下一节整一波多表连接 join查询结合group by 和 aggregate.