本篇文章在SQL简单查询的基础上,讨论如何对数据进行求和、均值、最大值最小值等汇总分析的方法,同时对查询结果进行排序。
一、常见汇总函数及含义
汇总分析练习:
1、查询课程编号为‘0002’的总成绩
SELECT sum(成绩) FROM score WHERE 课程号='0002';
2、查询选了课程的学生人数
SELECT count(DISTINCT 学号) FROM score;
二、对查询结果进行分组
使用Group by子句对查询结果进行分组,结合汇总函数,可以将查询结果按照类似“性别”,“种类”等进行划分。要注意group by后面不能使用列的别名,要使用原名。同时where子句中不能使用聚合函数。
分组练习:
1、查询各科成绩最高和最低分
SELECT 课程号, MAX(成绩)AS 最高分 FROM score GROUP BY 课程号;
2、查询每门课程被选修的学生数
SELECT 课程号, COUNT(DISTINCT 学号)AS 学生数 FROM score GROUP BY 课程号;
3、查询男生、女生人数
SELECT 性别, COUNT(DISTINCT 学号)AS 学生数 FROM student GROUP BY 性别;
三、为聚合结果指定条件
使用count函数对数据表中数据进行汇总操作时,采用having子句而不是where子句来指定条件。having语句主要作用是为聚合结果指定条件。其中,where子句是用来指定数据行的条件,having子句指定分组条件。其中聚合函数可以在select子句,having子句和order by字句中使用。
分组结果条件练习
1、查询平均成绩大于60分的学生的学号和平均成绩。
SELECT 学号,AVG(成绩)as 平均成绩 FROM score GROUP BY 学号 HAVING AVG(成绩)>60;
2、查询至少选修两门课程的学生学号。
SELECT 学号,COUNT(课程号)AS 课程数量 FROM score GROUP BY 学号 HAVING COUNT(课程号)>1;
3、查询同名同姓学生名单并统计同名人数。
SELECT 姓名,COUNT(姓名)AS 同名数量 FROM student GROUP BY 姓名 HAVING COUNT(姓名)>1;
练习:每门课程的平均成绩
如何计算每门课程的平均成绩,并且平均成绩大于等于80分。
SELECT 课程号,AVG(成绩) FROM score GROUP BY 课程号 HAVING AVG(成绩)>60;
四、理解SQL运行顺序,并对结果进行排序
使用order by子句对查询结果进行排序,其中desc为按照降序排列,asc为按照升序排列,排序键值包含NULL时,会在开头或者末尾进行汇总。
练习:理解SQL的运行顺序
查询不及格的课程并按课程号从大到小排列
SELECT 课程号,成绩 FROM score GROUP BY 课程号 HAVING AVG(成绩)<60 ORDER BY 课程号 DESC;
查询每门课程的平均成绩,结果按平均成绩升序排序,平均成绩相同时,按课程号降序排列
SELECT 课程号,AVG(成绩)AS 平均成绩 FROM score GROUP BY 课程号 ORDER BY 平均成绩 asc,课程号 DESC;
五、看懂报错信息
常见易出错的地方包括:
1、group by 中不能使用select里的别名,因为group by 是先运行的
2、在where中不能使用聚合函数
3、注意字符串类型的数字,因为数值类型的排序和字符串不一样,要数据改为数字类型,再进行操作。
总结:本章的重点首先是理解SQL运行的顺序,SQL语句先运行from、where、group by、having等查询条件和分组条件的子句,第二步运行select 子句显示查询结果,第三步运行order by, limit对查询结果进行排序或者取出指定的行。在理解上述运行顺序的基础上,重点掌握汇总函数,分组语句以及分组条件语句,同时对查询的结果还要学会排序。