运维实施15-聚合函数分组+排序组合使用

1、关键概念说明

1. 聚合函数：

   • 必须出现在SELECT子句或HAVING子句中

   • 常见函数：COUNT(), SUM(), AVG(), MAX(), MIN()

2. GROUP BY：

   • 非聚合字段必须出现在GROUP BY中

   • 执行顺序：WHERE → GROUP BY → HAVING

3. ORDER BY：

   • 总是最后执行

   • 可以使用字段别名（如class_avg）

   • 默认升序(ASC)，降序需明确写DESC

2、基础准备

创建示例表（DDL）

-- 创建学生成绩表
CREATE TABLE student_scores (
    student_id INT PRIMARY KEY,
    name VARCHAR(20),
    gender CHAR(1),   -- M/F
    class VARCHAR(10),-- 班级名称
    score INT         -- 考试成绩
);

插入测试数据（DML）

INSERT INTO student_scores VALUES(1, '张三', 'M', '一班', 85),
(2, '李四', 'F', '二班', 92),
(3, '王五', 'M', '一班', 78),
(4, '赵六', 'F', '三班', 88),
(5, '陈七', 'M', '二班', 95),
(6, '孙八', 'F', '三班', 90);

3、聚合函数

COUNT() - 统计行数

-- 统计总学生数
SELECT COUNT(*) AS total_students 
FROM student_scores;

SUM() - 求和

-- 计算全年级总分
SELECT SUM(score) AS total_score 
​
FROM student_scores;

AVG() - 求平均值

-- 计算全年级平均分
SELECT AVG(score) AS average_score 
FROM student_scores;

4. MAX()/MIN() - 极值查询

-- 查询最高分和最低分
SELECT MAX(score) AS max_score, 
       MIN(score) AS min_score 
FROM student_scores;

4、GROUP BY 分组

基础分组

-- 按班级统计平均分
SELECT class, 
       AVG(score) AS class_avg
FROM student_scores
GROUP BY class;

多字段分组

-- 按班级和性别统计人数
SELECT class, 
       gender,
       COUNT(*) AS student_count
FROM student_scores
GROUP BY class, gender;

5、ORDER BY 排序

单字段排序

-- 按成绩从高到低显示学生
SELECT name, score
FROM student_scores
ORDER BY score DESC;

组合排序

-- 先按班级升序，再按成绩降序
SELECT class, name, score
FROM student_scores
ORDER BY class ASC, score DESC;

分组+排序组合使用

-- 按班级统计平均分，并按平均分降序排列
SELECT class, 
       AVG(score) AS class_avg
FROM student_scores
GROUP BY class
ORDER BY class_avg DESC;

6、代码示例

-- 1查询病人表的所有信息
select * from patient
-- 2查询指定的相应信息  空格或者as给列取别名
select patientID as'病人诊疗卡',patientName as '病人名称',birthDate '出生日期',gender '性别',phoneNum'电话号码' from patient
-- 3 根据条件查询 根据性别和地址查询 and 并且
select * from patient where gender='男' and address='北京市'
-- 4 聚合函数 
--  count() 统计个数
select count(*) '病人个数' from patient
-- max(所求的列名) 最大值
select max(patientID) from patient
-- mim(所求的列名) 最小值
select min(patientID) from patient
-- sum(所求的列名) 求和
select sum(patientID) from patient
-- avg(所求的列名) 求平均值
select avg(patientID) from patient
-- 统计病人男女的个数 分组 group by 列名
select count(*) '人数',gender '性别' from patient group by gender
-- 排序 order by desc(降序)  asc(升序,默认)
select * from patient order by birthDate desc


-- 1 查询出生日期在2000年之前的所有男病人
select * from patient where birthDate<'2000-01-01' and gender='男'
-- 2 查询地址在北京市的病人，并按照病人的年龄降序排列
select * from patient where address='北京市' order by birthDate 
-- 3 统计80，90后的所有病人个数 80后即1980-1989年出生的人
select count(*) '总人数' from patient where birthDate>='1980-01-01' and birthDate<='1999-12-31'

-- between and 介于什么之间  区间值 等同于>=  <=
select count(*) '总人数' from patient where birthDate between '1980-01-01' and  '1999-12-31'