表结构
分组函数GROUP BY
SELECT 字段名,聚合函数(字段名)
FROM 表名
WHERE 条件表达式
GROUP BY 字段名;
1、根据学生姓名(偷懒把几个名字写成了一样的)分组,查询id>=100的学生平均年龄
SELECT AVG(stu_age) AS "平均年龄",stu_name
FROM student
WHERE id>=100
GROUP BY stu_name
2、根据学生姓名分组,查询stu_account不为null的学生的年龄最大值
SELECT MAX(stu_age) "MAX",stu_name
FROM student
WHERE stu_account IS NOT NULL
GROUP BY stu_name
ORDER BY MAX DESC
# PS:发现一个问题,order by根据别名排序时不加引号,哪怕别名和关键字冲突也能正常运行,加了引号反而会不执行
分组后筛选HAVING
WHERE子句无法和聚合函数一起使用
可以用HAVING代替
查询名字重复超过1次的学生
SELECT COUNT(*), stu_name
FROM student
GROUP BY stu_name
HAVING COUNT(*)>1
按聚合函数分组
按姓名长度分组查询
SELECT COUNT(*), stu_name, LENGTH(stu_name) AS "姓名长度"
FROM student
GROUP BY LENGTH(stu_name) # PS:这里的LENGTH函数可以用别名“姓名长度”替换