文章目录
SQL 语句查询的最终目的其实是为了得到一些分析结果,在SQL语言中支持一些基本的统计分析函数帮我们完成数据的查询和分析。
在进行统计分析之前,首先要有数据,参考上篇文章《 3. SQL–数据库基础查询操作》,将数据生成出来。
1. 汇总分析
常用的汇总分析函数如下图所示:
下面用几个查询案例,来看一下函数的用法。
1.1 计数
- 求教师表中有几个教师。
SELECT COUNT(*)
FROM teacher;
按照所有列统计数目。
2. 求教师表中有几个教师姓名。
SELECT COUNT(教师姓名)
FROM teacher;
由于教师表中,0003教师的名字为NULL,因此不计入总数。
1.2 求和与平均值
- 对成绩表中所有成绩求和
SELECT SUM(成绩) AS 成绩总和
FROM score;
2. 对成绩表中所有成绩求平均值
SELECT AVG(成绩) AS 平均成绩
FROM score;
1.3 求最大最小值
SELECT MAX(成绩) AS 最高成绩,MIN(成绩) AS 最低成绩
FROM score;
1.4 空值的处理
注意:
- 除了count(*)为特例(不忽略null值)外,其余聚集函数确实是忽略null值。
- 当然如果count(列名)针对某一列,那么也会忽略空值null。
2. 分组聚合和统计分析
统计分析往往需要对某一类别的数据进行统计,而数据表中记录的是所有类别。所以一旦涉及到某个类别的统计分析,就要考虑使用GROUP BY 分组聚合统计分析。
注意:分组一般都要和聚合函数一起使用,如果不一起虽然不会报错,但显然没什么意义。
如下图是加入分组聚合之后的SQL语句运行顺序,篮框中按照书写顺序执行:
2.1 查询每个性别的学生数
SELECT 性别, COUNT(*) AS 数目
FROM student
GROUP BY 性别;
查询结果如下图所示:
2.2 查询90后每个性别学生数
SELECT 性别, COUNT(*) AS 数目
FROM student
WHERE 出生日期 > '1990-01-01'
GROUP BY 性别;
查询结果如下图所示:
2.3 对分组结果指定条件
我们知道WHERE子句可以加限定条件,但是对于分组条件的指定需要用HAVING 子句。
如果想要查出平均成绩大于60分的学生的学号和平均成绩,怎么实现?
SELECT 学号, AVG(成绩) AS 平均成绩
FROM score
GROUP BY 学号
HAVING AVG(成绩) > 70;
查询结果如下图所示:
3. SQL解决业务问题的步骤
对于所有的业务问题,基本可以分三步解决:
- 翻译成大白话
翻译成大白话,简言之就是将问题分而治之,分成几个小步骤。 - 写出分析思路
每个小步骤,可以通过SQL子句一一表达,需要用哪些SQL子句。 - 写出对应的SQL子句
将各步骤用SQL子句翻译,然后拼接到一起。
3.1 计算各科的平均成绩
- 翻译成大白话
计算每门课程的平均成绩。 - 写出分析思路
从哪张表中查找:FROM score
提到每**: 就需要使用GROUP BY来对每门课程分组
最终求平均值 - 翻译成SQL语句
SELECT 查询结果[课程号,平均成绩]
FROM 从哪张表中查找数据
WHERE 限定条件(无)
GROUP BY 分组 [每门课程]
HAVING 对各分组有无条件 (无)
最终的SQL 查询语句:
SELECT 课程号,AVG(成绩) AS 平均成绩
FROM score
GROUP BY 课程号;
查询结果如下图所示:
4. 排序
对分组结果聚合完成之后,需要对结果进行排序,排序使用ORDER BY,加入ORDER BY 之后的SQL 语句执行顺序如下图所示:
4.1 指定某列排序
SELECT * FROM score ORDER BY 成绩 asc; ---按照成绩降序排列
SELECT * FROM score ORDER BY 成绩 asc,课程号 desc; ---先按成绩降序,相同成绩按照课程号升序排列
使用ORDER BY 排序查询结果如下图所示:
4.2 各科平均成绩大于80分的降序排列
- 翻译成大白话
每门课程计算平均成绩,取出平均成绩大于80分的课程,然后排序 - 分析思路
每门课程
平均成绩
取出大于80
降序 - SQL实现
SELECT 课程号, AVG(成绩) AS 平均成绩
FROM score
GROUP BY 课程号
HAVING AVG(成绩) > 80
ORDER BY 平均成绩 desc;
查询结果如下图所示:
注意:有空值的列排序,空值会被放到查询结果的头部,所以可以用此方法来查看所有空值元素。
5. 限定查询结果数量
有时候我们的查询结果一页显示不下,可以考虑使用LIMIT OFFSET 来限制。
LIMIT : 一页最多显示多少条数据
OFFSET:从第几条开始显示
加入LIMIT OFFSET 之后SQL子句的执行顺序如下:
查询按成绩排序成绩表,每页最多显示3个从2个开始显示.
SELECT * FROM score ORDER BY 成绩 LIMIT 3 OFFSET 2;
查询结果如下图所示:
6. SQL 常见错误
- 在group by 、having中使用了别名(MySQL数据库可以用别名,但是其他数据库不一定)
- 在where子句中使用聚合函数
select 课程号,count(*) from score where count(*)>2 group by 课程号;
- 字符型类型的数字排序是按照字符串排序规则排序