课程 › 第 6 章 › 实验二
第 6 章 · 实验二 · 在第三节之后

统计、分组与排序

用聚合函数统计成绩和课时，按学生、课程分组统计，再对结果排序，取出前几名。

环　境

MySQL 8.0 和 MySQL Workbench，机房电脑已装好。所有语句在 Workbench 的 SQL 编辑区中执行。

执　行

Ctrl+Enter 执行光标所在的一句，Ctrl+Shift+Enter 执行全部。Mac 上把 Ctrl 换成 ⌘。

注　意

输入法切到英文。表名、字段名不加引号，字符串值才用单引号。

本次实验的顺序　后一个实验用到前一个的结果

  1. 实验 2-1聚合函数
  2. 实验 2-2分组统计
  3. 实验 2-3分组后筛选
  4. 实验 2-4排序
  5. 实验 2-5取前几名
第一步

数据准备（必做）

与实验一是同一份数据。之前没有修改过数据可以不再执行，不确定就再执行一次。

整段复制，全部执行下载 .sql
-- 第 6 章实验数据：教材 teaching 库的五张表和教材中的数据
-- 四次实验共用这一份数据。整段执行一次即可，数据改乱了重新执行就能恢复。
CREATE DATABASE IF NOT EXISTS teaching DEFAULT CHARACTER SET utf8mb4;
USE teaching;

-- 删除旧的表。先暂时关闭外键检查，库里有别的表引用它们时也能删除
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS sc, tc, avgsal, s, c, t;
SET FOREIGN_KEY_CHECKS = 1;

-- 课程表
CREATE TABLE c (
  cno CHAR(4) NOT NULL,
  cn VARCHAR(30) NOT NULL,
  ct INT,
  PRIMARY KEY (cno)
);
-- 学生表
CREATE TABLE s (
  sno CHAR(4) NOT NULL,
  sn VARCHAR(20) NOT NULL,
  sex CHAR(2),
  age INT,
  maj VARCHAR(30),
  dept VARCHAR(30),
  PRIMARY KEY (sno)
);
-- 教师表
CREATE TABLE t (
  tno CHAR(4) NOT NULL,
  tn VARCHAR(20) NOT NULL,
  sex CHAR(2),
  age INT,
  prof VARCHAR(20),
  maj VARCHAR(30),
  dept VARCHAR(30),
  sal DECIMAL(8,2),
  PRIMARY KEY (tno)
);
-- 选课表
CREATE TABLE sc (
  sno CHAR(4) NOT NULL,
  cno CHAR(4) NOT NULL,
  score DECIMAL(5,2),
  PRIMARY KEY (sno, cno),
  FOREIGN KEY (sno) REFERENCES s (sno),
  FOREIGN KEY (cno) REFERENCES c (cno)
);
-- 授课表
CREATE TABLE tc (
  tno CHAR(4) NOT NULL,
  cno CHAR(4) NOT NULL,
  tcdate DATE,
  PRIMARY KEY (tno, cno),
  FOREIGN KEY (tno) REFERENCES t (tno),
  FOREIGN KEY (cno) REFERENCES c (cno)
);

INSERT INTO c VALUES
('c1','Java程序设计',40), ('c2','程序设计基础',48), ('c3','线性代数',48),
('c4','数据结构',64), ('c5','数据库系统',56), ('c6','数据挖掘',32),
('c7','高等数学',60), ('c8','控制理论',32);

INSERT INTO s VALUES
('s1','王彤','女',18,'计算机','信息学院'),
('s2','苏乐','男',20,'信息','信息学院'),
('s3','林欣怡','女',19,'信息','信息学院'),
('s4','陶然','男',19,'自动化','工学院'),
('s5','魏立','男',17,'数学','理学院'),
('s6','何欣荣','女',21,'计算机','信息学院'),
('s7','赵琳琳','女',19,'数学','理学院'),
('s8','李轩','男',18,'自动化','工学院');

INSERT INTO t VALUES
('t1','刘杨','男',38,'教授','计算机','信息学院',3800.00),
('t2','石丽','女',28,'讲师','计算机','信息学院',2800.00),
('t3','顾伟','男',35,'副教授','计算机','信息学院',3200.00),
('t4','赵礼','男',45,'教授','自动化','工学院',4267.90),
('t5','赵希希','女',33,'副教授','数学','理学院',3332.67),
('t6','张刚','男',30,'讲师','计算机','工学院',2600.00);

INSERT INTO sc VALUES
('s1','c1',90.50), ('s1','c2',88.00),
('s2','c4',70.00), ('s2','c5',57.00), ('s2','c6',81.50), ('s2','c7',NULL),
('s3','c1',75.00), ('s3','c2',76.50), ('s3','c4',85.00),
('s4','c1',93.00), ('s4','c2',82.00), ('s4','c6',NULL), ('s4','c7',68.00),
('s5','c2',79.00), ('s5','c7',84.00),
('s7','c3',77.00), ('s7','c5',62.00), ('s7','c7',100.00),
('s8','c2',66.00), ('s8','c3',96.00);

INSERT INTO tc VALUES
('t1','c1','2021-09-03'), ('t1','c2','2021-09-04'),
('t2','c5','2021-03-06'), ('t2','c6','2021-09-06'),
('t3','c2','2021-03-08'), ('t3','c4','2021-09-08'),
('t4','c3','2021-03-10'),
('t5','c7','2021-09-12'), ('t5','c8','2021-03-15');
2-1

聚合函数

讲义 · 聚合函数讲义 · MAX 与 COUNT
目的
用 SUM、AVG、MAX、MIN、COUNT 进行统计，理解空值对统计结果的影响。
前提
已执行数据准备。
  1. 查询 s2 的总分和平均分
  2. 查询课程的最高课时、最低课时和两者之差
  3. 分别用 COUNT(*) 和 COUNT(score) 统计 s2 的选课门数，比较结果

参考答案

可以直接复制到 Workbench 执行
SELECT SUM(score) AS 总分, AVG(score) AS 平均分 FROM sc WHERE sno = 's2';
-- 聚合函数的结果还可以继续计算
SELECT MAX(ct) AS 最高课时, MIN(ct) AS 最低课时, MAX(ct) - MIN(ct) AS 课时差 FROM c;
-- COUNT(*) 统计行数，COUNT(score) 只统计成绩不为空的行
SELECT COUNT(*) AS 选课门数, COUNT(score) AS 有成绩门数 FROM sc WHERE sno = 's2';

语句解释

SUM(score)　AVG(score)求和、求平均值，成绩为空的行不参加计算
MAX(ct) - MIN(ct)聚合函数的结果可以参加运算
COUNT(*)统计行数
COUNT(score)统计 score 不为空的个数
执行后看：s2 的总分 208.50，平均分 69.500000，是 208.50 除以 3。COUNT(*) 是 4，COUNT(score) 是 3。
2-2

分组统计

讲义 · GROUP BY
目的
用 GROUP BY 分组，统计每一组的数据。
前提
已完成实验 2-1。
  1. 查询每门课的选课人数
  2. 查询每个学生的选课门数和平均分
  3. 查询学生表中每个院系的人数

参考答案

可以直接复制到 Workbench 执行
-- 按课程号分组，每组统计行数
SELECT cno AS 课程号, COUNT(*) AS 选课人数 FROM sc GROUP BY cno;
SELECT sno AS 学号, COUNT(*) AS 选课门数, AVG(score) AS 平均分 FROM sc GROUP BY sno;
SELECT dept AS 院系, COUNT(*) AS 人数 FROM s GROUP BY dept;

语句解释

GROUP BY cno课程号相同的行分为一组，每组在结果中占一行
COUNT(*)对每一组分别统计行数
SELECT 后面的字段除聚合函数外，只能写 GROUP BY 中出现的字段
执行后看：第一句中 c2 的选课人数最多，5 人。第二句有 7 行，s6 没有选课，所以不在结果中。院系有 3 个。
2-3

分组后筛选

讲义 · HAVING
目的
用 HAVING 筛选分组后的结果，区分 WHERE 和 HAVING。
前提
已完成实验 2-2。
  1. 查询选修了 3 门及以上课程的学生的学号和选课门数
  2. 只统计 60 分及以上的成绩，查询及格课程在 3 门及以上的学生

参考答案

可以直接复制到 Workbench 执行
-- 对组的条件写在 HAVING 中
SELECT sno AS 学号, COUNT(*) AS 选课门数
FROM sc
GROUP BY sno
HAVING COUNT(*) >= 3;

-- WHERE 先逐行去掉不及格和没有成绩的记录，再分组，最后 HAVING 筛组
SELECT sno AS 学号, COUNT(*) AS 及格门数
FROM sc
WHERE score >= 60
GROUP BY sno
HAVING COUNT(*) >= 3;

语句解释

HAVING COUNT(*) >= 3分组之后，只保留行数不少于 3 的组
WHERE score >= 60分组之前逐行筛选，只留下及格的记录
WHERE 与 HAVINGWHERE 筛选行，写在 GROUP BY 前面。HAVING 筛选组，写在后面。把 COUNT 写进 WHERE 会报 1111
执行后看：第一句 4 人：s2、s3、s4、s7。第二句只有 3 人，s2 不在其中，因为他有一门不及格、一门没有成绩。
2-4

排序

讲义 · 排序
目的
用 ORDER BY 对查询结果排序，会按多个字段排序。
前提
已完成实验 2-3。
  1. 查询 s2 的选课记录，按成绩从高到低排列
  2. 查询全部课程，按课时降序排列，课时相同的按课程名降序排列

参考答案

可以直接复制到 Workbench 执行
SELECT sno, cno, score FROM sc WHERE sno = 's2' ORDER BY score DESC;
-- 先按课时降序，课时相同时再按课程名降序
SELECT * FROM c ORDER BY ct DESC, cn DESC;

语句解释

ORDER BY score DESC按成绩降序。DESC 是降序，ASC 或不写是升序
ct DESC, cn DESC第一个字段相同时才按第二个字段排序，每个字段后面各写各的 DESC
执行后看：第一句中成绩为空的 c7 排在最后。第二句中课时同为 48 的两门课，线性代数排在程序设计基础前面。
2-5

取前几名

讲义 · LIMIT讲义 · 综合
目的
把分组、排序和 LIMIT 连起来使用，查询前几名。
前提
已完成实验 2-4。
  1. 查询选课人数最多的前 3 门课程的课程号和选课人数
  2. 按教师号排序，跳过第 1 位教师，查询接下来 3 位教师的教师号、姓名和职称

参考答案

可以直接复制到 Workbench 执行
SELECT cno AS 课程号, COUNT(*) AS 选课人数
FROM sc
GROUP BY cno
ORDER BY 选课人数 DESC
LIMIT 3;

-- 跳过 1 行，取 3 行
SELECT tno, tn, prof FROM t ORDER BY tno LIMIT 1, 3;

语句解释

ORDER BY 选课人数 DESCORDER BY 中可以使用 SELECT 中起的别名
LIMIT 3排好序以后取前 3 行
LIMIT 1, 3前一个数是跳过的行数，后一个数是返回的行数，也可以写成 LIMIT 3 OFFSET 1
执行后看：前三门是 c2 5 人、c7 4 人、c1 3 人。第二句返回 t2、t3、t4。
保山学院·人工智能教研室·曹鼎鼎