MYSQL(六)

mysql学习笔记整理-排序聚合分组

排序聚合分组

ORDER

排序介绍

mysql 对查询结果使用order by 进行排序

• 对任何字段进行排序
• desc 降序 asc 升序
• 对别名字段可排序
• 对函数结果可排序
• 支持多列表排序
• 排序受校对规则影响（请查看其他章节课程了解校对规则）

排序实例

从男到女排序

SELECT * FROM stu ORDER BY sex ASC;

从男到女排序年龄从小到大排序

SELECT * FROM stu ORDER BY sex ASC,birthday DESC;

随机获取学生

SELECT * FROM stu ORDER BY RAND() LIMIT 1;

按出生月份从小到大排序

SELECT birthday,MONTH(birthday) as m FROM stu ORDER BY m ASC;

# 或使用字符串函数操作
SELECT birthday,mid(birthday,6,2) as m FROM stu ORDER BY m ASC;

自定义排序

field 函数用于比较值在集合中的索引，利用这一特性可以自定义排序

SELECT FIELD('a','c','a','b');
# 第一个a 为比较字符，后面的 c/a/b为集合，所以结果为2，如果在集合中不存在为0

使用 field 进行自定义排序

SELECT * FROM stu ORDER BY FIELD(left(sname,1),'何','赵');

COUNT

统计所有学生人数

SELECT COUNT(*) FROM stu;

所有女生人数

SELECT COUNT(*) FROM stu WHERE sex=2;

统计所有分配班级的学生（count(字段)不会统计 null 值，使用 count(*)时会计算 null），所以下面使用具体的字段

SELECT COUNT(class_id) FROM stu;

MIN/MAX

获取最小的学生出生年份

SELECT year(max(birthday)) from stu;

最大的班级编号

SELECT max(class_id) FROM stu;

获取点击数最少的文章

SELECT * FROM article WHERE click = (SELECT MIN(click) FROM article);

SUM/AVG

获取所有文章总点击数

SELECT SUM(click) FROM article;

获取平均点击数

SELECT AVG(click) FROM article;

获取低于平均点击数据的文章

SELECT * FROM article WHERE click < (SELECT AVG(click) FROM article);

获取学生的平均年龄

SELECT ROUND(AVG(TIMESTAMPDIFF(YEAR,birthday,now())))  FROM stu ;

DISTINCT

distinct 用于去除结果集中的重复记录

获取所有班级编号

SELECT DISTINCT class_id AS class FROM stu WHERE class_id IS NOT NULL;

获取学生数，同班同名的算一个

SELECT COUNT(DISTINCT class_id,sname) FROM stu WHERE class_id IS NOT NULL;

获取班级平均人数，去掉重复的数值

SELECT DISTINCT (count(id)) AS c FROM users GROUP BY class_id

GROUP

统计受 ONLY_FULL_GROUP_BY 模式影响，有关 ONLY_FULL_GROUP_BY 的详细讨论请查看 「运行模式」章节。

统计每个班级的人数

SELECT COUNT(*),class_id FROM stu WHERE class_id IS NOT NULL GROUP BY class_id ;

每个班年龄最大的同学

结果中要求出现班级编号和学生姓名，如果 GROUP BY 中只有班级编号字段，默认运行模式下 SELECT 中不能出现学生姓名。

MYSQL 默认使用 ONLY_FULL_GROUP_BY 模式要求 select 中的列要在 group 中使用。有多种方式可以处理这个问题

• 可以通过更改查询模式，允许 select 的列不在 group 中出现
• 使用聚合函数
• 使用 any_value 函数处理
• GROUP BY 中使用 PRIMAY KEY 或 UNIQUE NOT NULL 字段
• 有关 ONLY_FULL_GROUP_BY 模式已经在「运行模式」章节讨论过

直接查询将产生错误，因为 sname 不是 GROUP BY 使用的字段

SELECT min(birthday),sname FROM stu GROUP BY class_id;

使用聚合函数解决这个问题

SELECT min(birthday),min(sname) FROM stu GROUP BY class_id;

使用 any_value 函数解决

SELECT min(birthday),any_value(sname) FROM stu GROUP BY class_id;

也可以使用子查询

SELECT * FROM stu where birthday IN(
SELECT min(birthday) FROM stu GROUP BY class_id);

或使用聚合函数

SELECT min(birthday),min(sname) FROM stu GROUP BY class_id;

也可以更改查询模式，去掉 ONLY_FULL_GROUP_BY 模式的方式解决。

很多后台程序框架提供配置项用于禁止 ONLY_FULL_GROUP_BY 模式

SET sql_mode=''

SELECT min(birthday),sname FROM stu GROUP BY class_id;

统计每班的男、女人数

SELECT concat(class_id,'班'),if(sex=1,'男','女') as sex,
count(*) FROM stu
WHERE class_id IS NOT NULL
GROUP BY class_id,sex ORDER BY class_id;

查找超过两个同学的班级

SELECT class_id FROM stu GROUP BY class_id HAVING count(*)>2;

查找本周迟到超过两次的同学

SELECT stu_id FROM attendance
WHERE date(created_at)>date(DATE_ADD(NOW(),INTERVAL 0-WEEKDAY(NOW()) day))
AND time(created_at)>'08:30:00'
GROUP BY stu_id
HAVING COUNT(*)>2;

本周哪个同学准时到校次数最多

SELECT count(*) as c,stu_id FROM attendance
WHERE date(created_at)>=date(date_add(now(),interval 0-WEEKDAY(now()) day))
AND time(created_at)<='08:30:00'
GROUP BY stu_id
ORDER by c desc
limit 1;

本周哪一天迟到的人数最少

SELECT date(created_at) FROM attendance
WHERE date(created_at)>date(DATE_ADD(NOW(),INTERVAL 0-WEEKDAY(NOW()) day))
AND time(created_at)<='08:30:00'
GROUP BY created_at
ORDER BY COUNT(*) DESC
LIMIT 1;

查找哪个姓的同学最多

SELECT left(sname,1) as s,count(*) AS c FROM stu
GROUP BY s
ORDER BY c DESC
LIMIT 1;

查找超过两个同学的姓氏

SELECT left(sname,1) as s,count(*) AS c FROM stu
GROUP BY s
HAVING c>=2;