全栈进阶指南

皮肤切换

第 4 章：SELECT 进阶查询 ​

在前几章中，我们学习了基本的 SELECT 查询。现在我们将深入学习更高级的查询技巧，包括聚合函数、分组查询、条件筛选等，这些是数据分析和报表生成的基础。

本章内容概要 ​

  • 聚合函数（COUNT、SUM、AVG、MAX、MIN）
  • GROUP BY 分组查询
  • HAVING 子句
  • DISTINCT 去重
  • LIKE 模糊查询
  • BETWEEN 范围查询
  • IN 列表查询
  • IS NULL 判断空值
  • 实战练习：学生成绩统计分析

聚合函数 ​

聚合函数对一组值执行计算并返回单个值。

COUNT 函数 ​

COUNT 函数用于计算行数。

sql
USE student_management;

-- 计算学生总数
SELECT COUNT(*) AS 学生总数 FROM students;

-- 计算有邮箱的学生数量
SELECT COUNT(email) AS 有邮箱学生数 FROM students;

-- 计算不同专业的数量
SELECT COUNT(DISTINCT major) AS 专业数 FROM students;

SUM 函数 ​

SUM 函数用于计算数值列的总和。

首先，让我们为成绩表添加一些测试数据：

sql
-- 插入课程数据（如果还没有）
INSERT INTO courses (course_code, course_name, credits, description, teacher)
VALUES 
('CS101', '计算机基础', 3, '计算机科学入门课程', '张教授'),
('CS102', '数据结构', 4, '数据结构与算法分析', '李教授'),
('CS103', '数据库原理', 3, '关系数据库设计与实现', '王教授'),
('CS104', '操作系统', 4, '操作系统原理与实践', '赵教授');

-- 插入成绩数据
INSERT INTO grades (student_id, course_id, grade, exam_date, semester)
VALUES 
(1, 1, 85.5, '2023-12-15', '2023秋季'),
(1, 2, 78.0, '2023-12-20', '2023秋季'),
(2, 1, 92.0, '2023-12-15', '2023秋季'),
(2, 3, 88.5, '2023-12-22', '2023秋季'),
(3, 2, 76.5, '2023-12-20', '2023秋季'),
(3, 4, 82.0, '2023-12-25', '2023秋季'),
(4, 1, 89.0, '2023-12-15', '2023秋季'),
(4, 4, 91.5, '2023-12-25', '2023秋季');

现在可以使用 SUM 函数：

sql
-- 计算所有成绩的总分
SELECT SUM(grade) AS 总分 FROM grades;

-- 计算每个学生的总分
SELECT student_id, SUM(grade) AS 总分 
FROM grades 
GROUP BY student_id;

AVG 函数 ​

AVG 函数用于计算数值列的平均值。

sql
-- 计算所有成绩的平均分
SELECT AVG(grade) AS 平均分 FROM grades;

-- 计算每个学生的平均分
SELECT student_id, AVG(grade) AS 平均分 
FROM grades 
GROUP BY student_id;

MAX 和 MIN 函数 ​

MAX 和 MIN 函数分别用于查找最大值和最小值。

sql
-- 查找最高分和最低分
SELECT MAX(grade) AS 最高分, MIN(grade) AS 最低分 FROM grades;

-- 查找获得最高分的学生
SELECT student_id, MAX(grade) AS 最高分 
FROM grades 
GROUP BY student_id;

GROUP BY 分组查询 ​

GROUP BY 子句用于将结果集按一个或多个列进行分组。

基本分组 ​

sql
-- 按专业分组统计学生人数
SELECT major AS 专业, COUNT(*) AS 人数 
FROM students 
GROUP BY major;

-- 按性别分组统计学生人数
SELECT gender AS 性别, COUNT(*) AS 人数 
FROM students 
GROUP BY gender;

多列分组 ​

sql
-- 按专业和班级分组统计学生人数
SELECT major AS 专业, class_name AS 班级, COUNT(*) AS 人数 
FROM students 
GROUP BY major, class_name;

分组与聚合函数结合 ​

sql
-- 计算每个学生的各科成绩总分和平均分
SELECT 
    student_id AS 学生ID,
    COUNT(*) AS 选课数,
    SUM(grade) AS 总分,
    AVG(grade) AS 平均分,
    MAX(grade) AS 最高分,
    MIN(grade) AS 最低分
FROM grades 
GROUP BY student_id;

HAVING 子句 ​

HAVING 子句用于对分组后的结果进行筛选。

sql
-- 查找平均分大于85分的学生
SELECT 
    student_id AS 学生ID,
    AVG(grade) AS 平均分
FROM grades 
GROUP BY student_id
HAVING AVG(grade) > 85;

-- 查找选课数大于等于2门的学生
SELECT 
    student_id AS 学生ID,
    COUNT(*) AS 选课数
FROM grades 
GROUP BY student_id
HAVING COUNT(*) >= 2;

-- 查找有3门以上课程的学期
SELECT 
    semester AS 学期,
    COUNT(*) AS 课程数
FROM grades 
GROUP BY semester
HAVING COUNT(*) > 3;

DISTINCT 去重 ​

DISTINCT 关键字用于去除查询结果中的重复行。

sql
-- 查看所有不同的专业
SELECT DISTINCT major FROM students;

-- 查看所有不同的学期
SELECT DISTINCT semester FROM grades;

-- 统计不同专业的数量
SELECT COUNT(DISTINCT major) AS 专业数 FROM students;

LIKE 模糊查询 ​

LIKE 操作符用于在 WHERE 子句中进行模式匹配。

sql
-- 查找姓张的学生
SELECT * FROM students WHERE name LIKE '张%';

-- 查找名字中包含"三"的学生
SELECT * FROM students WHERE name LIKE '%三%';

-- 查找名字是两个字的学生
SELECT * FROM students WHERE name LIKE '__';

-- 查找邮箱是 example.com 域名的学生
SELECT * FROM students WHERE email LIKE '%@example.com';

-- 使用 NOT LIKE 查找不满足条件的记录
SELECT * FROM students WHERE name NOT LIKE '张%';

BETWEEN 范围查询 ​

BETWEEN 操作符用于选取介于两个值之间的数据。

sql
-- 查找出生日期在2005年1月1日到2005年12月31日之间的学生
SELECT * FROM students 
WHERE birth_date BETWEEN '2005-01-01' AND '2005-12-31';

-- 查找成绩在80到90之间的记录
SELECT * FROM grades 
WHERE grade BETWEEN 80 AND 90;

-- 查找入学日期在指定范围内的学生
SELECT * FROM students 
WHERE enrollment_date BETWEEN '2023-09-01' AND '2023-09-30';

IN 列表查询 ​

IN 操作符允许在 WHERE 子句中指定多个可能的值。

sql
-- 查找计算机科学与技术或软件工程专业的学生
SELECT * FROM students 
WHERE major IN ('计算机科学与技术', '软件工程');

-- 查找特定课程ID的成绩
SELECT * FROM grades 
WHERE course_id IN (1, 2, 3);

-- 使用 NOT IN 查找不在列表中的记录
SELECT * FROM students 
WHERE major NOT IN ('计算机科学与技术', '软件工程');

IS NULL 判断空值 ​

IS NULL 和 IS NOT NULL 用于检测 NULL 值。

sql
-- 查找没有电话号码的学生
SELECT * FROM students WHERE phone IS NULL;

-- 查找有邮箱的学生
SELECT * FROM students WHERE email IS NOT NULL;

-- 查找没有成绩记录的学生
SELECT * FROM students 
WHERE id NOT IN (SELECT DISTINCT student_id FROM grades WHERE student_id IS NOT NULL);

实战练习：学生成绩统计分析 ​

让我们通过一个完整的学生成绩统计分析来练习这些高级查询技巧。

1. 基础统计分析 ​

sql
-- 查看成绩表中的所有数据
SELECT 
    s.name AS 学生姓名,
    c.course_name AS 课程名称,
    g.grade AS 成绩,
    g.exam_date AS 考试日期,
    g.semester AS 学期
FROM grades g
JOIN students s ON g.student_id = s.id
JOIN courses c ON g.course_id = c.id
ORDER BY g.semester, s.name, c.course_name;

2. 学生个人成绩分析 ​

sql
-- 每个学生的成绩统计
SELECT 
    s.name AS 学生姓名,
    COUNT(g.id) AS 选课数,
    SUM(g.grade) AS 总分,
    AVG(g.grade) AS 平均分,
    MAX(g.grade) AS 最高分,
    MIN(g.grade) AS 最低分
FROM students s
LEFT JOIN grades g ON s.id = g.student_id
GROUP BY s.id, s.name
ORDER BY 平均分 DESC;

3. 课程成绩分析 ​

sql
-- 每门课程的成绩统计
SELECT 
    c.course_name AS 课程名称,
    COUNT(g.id) AS 参考人数,
    AVG(g.grade) AS 平均分,
    MAX(g.grade) AS 最高分,
    MIN(g.grade) AS 最低分
FROM courses c
LEFT JOIN grades g ON c.id = g.course_id
GROUP BY c.id, c.course_name
ORDER BY 平均分 DESC;

4. 优秀学生筛选 ​

sql
-- 平均分在85分以上的学生
SELECT 
    s.name AS 学生姓名,
    AVG(g.grade) AS 平均分
FROM students s
JOIN grades g ON s.id = g.student_id
GROUP BY s.id, s.name
HAVING AVG(g.grade) >= 85
ORDER BY 平均分 DESC;

5. 成绩分布分析 ​

sql
-- 成绩分布统计
SELECT 
    CASE 
        WHEN grade >= 90 THEN '90-100分'
        WHEN grade >= 80 THEN '80-89分'
        WHEN grade >= 70 THEN '70-79分'
        WHEN grade >= 60 THEN '60-69分'
        ELSE '60分以下'
    END AS 分数段,
    COUNT(*) AS 人数
FROM grades
GROUP BY 
    CASE 
        WHEN grade >= 90 THEN '90-100分'
        WHEN grade >= 80 THEN '80-89分'
        WHEN grade >= 70 THEN '70-79分'
        WHEN grade >= 60 THEN '60-69分'
        ELSE '60分以下'
    END
ORDER BY 分数段;

6. 学期成绩对比 ​

sql
-- 不同学期的成绩对比
SELECT 
    semester AS 学期,
    COUNT(*) AS 考试人次,
    AVG(grade) AS 平均分,
    MAX(grade) AS 最高分,
    MIN(grade) AS 最低分
FROM grades
GROUP BY semester
ORDER BY semester;

7. 综合排名分析 ​

sql
-- 学生综合排名（按平均分）
SELECT 
    s.name AS 学生姓名,
    s.major AS 专业,
    COUNT(g.id) AS 选课数,
    ROUND(AVG(g.grade), 2) AS 平均分,
    RANK() OVER (ORDER BY AVG(g.grade) DESC) AS 排名
FROM students s
JOIN grades g ON s.id = g.student_id
GROUP BY s.id, s.name, s.major
ORDER BY 平均分 DESC;

查询优化建议 ​

1. 合理使用索引 ​

对于经常用于 WHERE、ORDER BY、GROUP BY 的列，考虑创建索引：

sql
-- 为成绩表的 student_id 列创建索引
CREATE INDEX idx_grades_student_id ON grades(student_id);

-- 为成绩表的 course_id 列创建索引
CREATE INDEX idx_grades_course_id ON grades(course_id);

2. 避免 SELECT * ​

尽量只查询需要的列，而不是使用 SELECT *：

sql
-- 好的做法
SELECT name, major FROM students WHERE major = '计算机科学与技术';

-- 避免这样做
SELECT * FROM students WHERE major = '计算机科学与技术';

3. 使用 LIMIT 限制结果集 ​

当只需要部分结果时，使用 LIMIT：

sql
-- 只查询前10名学生
SELECT name, AVG(grade) AS 平均分
FROM students s
JOIN grades g ON s.id = g.student_id
GROUP BY s.id, s.name
ORDER BY 平均分 DESC
LIMIT 10;

小结 ​

在本章中，我们学习了 SELECT 进阶查询的各种技巧：

  1. 聚合函数 - COUNT、SUM、AVG、MAX、MIN 等函数用于数据统计
  2. GROUP BY - 按列分组进行统计分析
  3. HAVING - 对分组结果进行筛选
  4. DISTINCT - 去除重复记录
  5. LIKE - 模式匹配查询
  6. BETWEEN - 范围查询
  7. IN - 列表查询
  8. IS NULL - 空值判断

通过这些高级查询技巧，我们可以进行复杂的数据分析和报表生成。在下一章中，我们将学习多表查询与连接操作，这是处理关系型数据库的核心技能。

Copyright © 2025 梁欢的前后端技术笔记 鲁ICP备2025190626号