1、汇总函数
常见的汇总函数有以下5个:
COUNT 求某列的行数、SUM 对某列数据求和、AVG 求某列数据的平均值
MAX 求某列数据的最大值、MIN 求某列数据的最小值
1)对于COUNT函数,计数时是会忽略空值的,即图中红框那一行是不会被计入行数的
如果输入
SELECT COUNT(教师姓名)
FROM teacher;
就会得到下面结果:
如果想对整个列计算行数,则使用下面的语句即可:
SELECT COUNT(*)
FROM teacher;
2)SUM和AVG的使用规则是类似的,都只能对数值类型的数据进行计算。如图
3)MAX和MIN函数则可以对任意类型数据进行计算,得出某一列的最大值和最小值:
tips:可以在聚合函数中插入DISTINCT进行去重,位置是在列名之前,例子如下
这里强调一下书写规则:一定记住列名不得使用空格。在书写子句时,列名不需要加单引号,因为列名不是字符串。
练习题:
1)查询课程编号为0002的总成绩
SELECT 课程号, SUM(成绩) AS 总成绩
FROM score
WHERE 课程号 = '0002';
2)查询选了课程的学生人数(注意去重)
SELECT COUNT(DISTINCT 学号) AS 学生人数
FROM score;
2、分组
分组需要使用子句GROUP BY,例如
SELECT 性别,COUNT(*)
FROM student
WHERE 出生日期>'1990-01-01'
GROUP BY 性别;
注意SQL的运行顺序和书写顺序是不一致的,具体到上一个例子,运行顺序为:
FROM——WHERE——GROUP BY——SELECT
练习题:
1)查询各科成绩最高和最低的分
SELECT 课程号,MAX(成绩) AS 最高分,MIN(成绩) AS 最低分
FROM score
GROUP BY 课程号;
2)查询每门课程被选修的学生数
SELECT 课程号,COUNT(学号) AS 选修人数
FROM score
GROUP BY 课程号;
3)查询男生、女生人数
SELECT 性别,COUNT(性别) AS 人数
FROM student
GROUP BY 性别;
这里用AS对选择结果进行了重命名,使结果看起来更直观。
3、对分组结果指定条件
使用HAVING子句对GROUP BY子句进行指定条件,具体运行顺序如下
练习题
1)查询平均成绩大于60分学生的学生号和平均成绩
SELECT 学号, AVG(成绩) AS 平均成绩
FROM score
GROUP BY 学号
HAVING AVG(成绩)>60;
2)查询至少选修两门课程的学生学号
SELECT 学号,COUNT(课程号) AS 选修课数目
FROM score
GROUP BY 学号
HAVING COUNT(课程号)>=2;
3)查询同名同姓学生名单并统计同名人数
SELECT 姓名,COUNT(姓名) AS 人数
FROM student
GROUP BY 姓名
HAVING COUNT(姓名)>1;
tips:在翻译成大白话的过程中,一旦出现每个每门每人,就是提示我们要用分组的思路解决问题。
4、用SQL解决业务问题的步骤
1)翻译成大白话,越方便理解越好
2)写出分析思路,按照SQL子句的书写顺序,逐项列出来
3)根据分析思路,写出对应的SQL子句
5、对查询结果排序
使用ORDER BY子句,书写顺序为:
SELECT——FROM——WHERE——GROUP BY——HAVING——ORDER BY
运行顺序和书写顺序是不一样的,具体如下图
所以ORDER BY里用来排序的列名,可以是SELECT子句里重命名的别名,但是为了避免混淆,不建议使用。
tips:
1)在ORDER BY子句的最后加上DESC,则为降序排列;加上ASC,则为升序排列。
2)可以在一个ORDER BY子句里同时指定多个排序列名。例如
3)如果查询的列中有空值,空值会在最前面的几行里显示。
如果数据量很大,只需要取一部分数据时,可以使用LIMIT子句限定显示的行数,例如
SELECT *
FROM score
LIMIT 2;
运行结果就是只显示前两行数据。
练习题
1)查询不及格的课程并按课程号从大到小排列
SELECT 课程号
FROM score
WHERE 成绩<60
ORDER BY 课程号 DESC;
2)查询每门课程的平均成绩,结果按平均成绩升序排序,平均成绩相同时,按课程号降序排列
SELECT 课程号,AVG(成绩) AS 平均成绩
FROM score
GROUP BY 课程号
ORDER BY 平均成绩 ASC,课程号 DESC;
6、常见错误
1)在GROUP BY子句中使用了SELECT子句里的别名
改正:只能在GROUP BY子句中使用原列名,因为GROUP BY子句的执行顺序在SELECT子句前面。
2)在WHERE子句中使用了聚合函数
改正:改用HAVING子句做条件限制
3)数据类型错误,SQL查询的数字结果和原数值不一样
原因是这个数据的类型没有设置为数字型,而是字符串型,这样SQL就会按照字符串的规则进行排序或计算。改正:把数据类型修改为数字型即可。
7、SQLZOO练习
第一部分
SELECT from Nobel Tutorial/zhigs.sqlzoo.net第二部分
SUM and COUNT/zhigs.sqlzoo.net