学习目标
1.汇总函数;2.分组;3.对分组结果指定条件;4.用SQL解决业务问题;5.对查询结果排序
一. 汇总函数
汇总函数:1.功能 2.输入(参数) 3.输出(返回值)
COUNT:求某列的行数
SUM:对某列数据求和
AVG:求某列的平均值
MAX:求某列数据的最大值
MIN:求某列数据的最小值
1)COUNT
COUNT(<列名>):得到的是除去该列空值NULL以后的行数。
注:其他汇总函数一样,若输入的是列名,会先排除空值再计算。
SELECT COUNT(教师姓名)
FROM teacher;
SELECT COUNT(*)
FROM teacher;
2)SUM & AVG
SUM和AVG只能对数值类型的列计算
SELECT SUM(成绩)
FROM score;
SELECT AVG(成绩)
FROM score;
3)MAX & MIN
SELECT MAX(成绩), MIN(成绩)
FROM score;
补充:先删除列中的重复值
SELECT COUNT(DISTINCT 教师姓名)
FROM teacher;
练习
1.查询课程编号为“0002”的总成绩。
-- 查询课程编号为“0002”的总成绩
SELECT SUM(成绩)
FROM score
WHERE 课程号 = '0002';
2.查询选了课程的学生人数。
-- 查询选了课程的学生人数
SELECT COUNT(DISTINCT 学号)
FROM score;
二. 分组
对数据分组:GROUP BY。 注:分组经常和汇总函数一起使用。
- 分析方法
第一步:对数据按某种方法进行分组; e.g. 对学生按性别分组 GROUP BY gender
第二步:在第一步分组以后的数据上应用函数; e.g. 求女生成绩的平均值 AVG(成绩)
第三步:把第二步每组的结果总结在一个表中。 e.g. SELECT 子句
2. SQL运行顺序
练习
- 查询各科成绩最高和最低的分。
SELECT 课程号, MAX(成绩) AS '最高分', MIN(成绩) AS '最低分'
FROM score
GROUP BY 课程号;
2. 查询每门课程被选修的学生数
SELECT 课程号, COUNT(DISTINCT 学号) AS '学生数'
FROM score
GROUP BY 课程号;
3.查询男生、女生人数
SELECT 性别, COUNT(*)
FROM student
GROUP BY 性别;
三. 对分组结果指定条件
对分组结果指定条件:HAVING 子句
-- 取出男生组
SELECT 性别, COUNT(*)
FROM student
GROUP BY 性别
HAVING COUNT(*) > 1;
练习
1.查询平均成绩大于60分学生的学号和平均成绩
SELECT 学号, AVG(成绩) AS '平均成绩'
FROM score
GROUP BY 学号
HAVING AVG(成绩) > 60;
2.查询至少选修两门课程的学生学号
SELECT 学号
FROM score
GROUP BY 学号
HAVING COUNT(课程号) >= 2;
3.查询同名同姓学生名单并统计同名人数
SELECT 姓名, COUNT(*) AS '同名人数'
FROM student
GROUP BY 姓名
HAVING COUNT(*) > 1;
四. 用SQL解决业务问题
工作中:先有业务背景,再写出解决这个业务背景的SQL语句。
案例:如何计算各科的平均成绩
1.翻译成大白话
如何计算各科的平均成绩 = 如何计算每门课程的平均成绩
2.写出分析思路
SELECT : 查询结果(每门课程:课程号;平均成绩:AVG(成绩))
FROM : 从那张表查找数据(成绩表:score)
WHERE : 查询条件(无)
GROUP BY : 分组(每门课程:按 课程号 分组)
HAVING : 对分组结果指定条件(无)
3.写出对应的SQL语句
SELECT 课程号, AVG(成绩)
FROM score
GROUP BY 课程号;
SQL运行过程
练习
如何计算每门课程的平均成绩,并且平均成绩大于等于80分?
分析思路:
SELECT : 查询结果(每门课程:课程号;平均成绩:AVG(成绩))
FROM : 从那张表查找数据(成绩表:score)
WHERE : 查询条件(无)
GROUP BY : 分组(每门课程:按 课程号 分组)
HAVING : 对分组结果指定条件(平均成绩大于等于80分: AVG(成绩) >= 80)
SQL语句:
SELECT 课程号, AVG(成绩)
FROM score
GROUP BY 课程号
HAVING AVG(成绩) >= 80;
五. 对查询结果排序
1)ORDER BY
降序(DESC):从大到小
升序(ASC):从小到大
SELECT 课程号, AVG(成绩) AS '平均成绩'
FROM score
GROUP BY 课程号
HAVING AVG(成绩) >= 80
ORDER BY 平均成绩 DESC;
- SQL运行顺序
- 指定多个排序列名
按ORDER BY后面的列名顺序依次排序
SELECT *
FROM score
ORDER BY 成绩 ASC, 课程号 DESC;
- 空值NULL的排序
SELECT *
FROM teacher
ORDER BY 教师姓名;
2)取出指定行: LIMIT
-- 从查询结果中取出前2行数据
SELECT * FROM score LIMIT 2;
SQL运行顺序
练习:
1.查询不及格的课程并按课程号从大到小排列。
SELECT 课程号
FROM score
WHERE 成绩 < 60
ORDER BY 课程号 DESC;
2.查询每门课程的平均成绩,结果按平均成绩升序排序。平均成绩相同时,按课程号降序排序。
SELECT 课程号, AVG(成绩) AS '平均成绩'
FROM score
GROUP BY 课程号
ORDER BY 平均成绩 ASC, 课程号 DESC;