mysql基础汇总_MySQL基础知识—汇总查询

一、汇总函数

汇总函数也就是聚合函数，是将多行结果汇总为一行输出，下图是常见的汇总函数：

接着我们对上面的函数一个个进行学习，看看他们具体的使用是怎么样的

count函数：

SELECT COUNT(教师姓名)

FROM teacher;

输出结果为什么是4个呢？这是因为教师号0003的教师名称就是Null，不是真正意义上的空值，他算一个数据，会被计数；教师号0004的教师名称是空格，也是一个字符串，只是肉眼看不到，它也算是一个数据；教师号0005的教师名称是(Null)，它才是真正意义上的空值，才是真正代表什么也没有。因此5个数据中只有最后一个教师姓名的数据是为空，因此查询结果是4个。

下面再看一个例子：

SELECT COUNT(*)

FROM teacher;

*代表是全部，因此count函数计数的是全部行，包括空值的行，最后的查询结果为5。

注意：除了COUNT(*)例外，其余所有的汇总函数会将NULL数据排除之后再进行计算。

sum函数：(只能对数值类型的列进行计算)

SELECT SUM(成绩)

FROM score;

avg函数：(只能对数值类型的列进行计算)

SELECT AVG(成绩)

FROM score;

max函数和min函数(几乎适用于所有数据类型的列)：

SELECT MAX(成绩),MIN(成绩)

FROM score;

需要注意的是：

将MAX()函数应用于文本数据时，会返回该列按照升序排列的最后一行

将MIN()函数应用于文本数据时，会返回该列按照升序排列的第一行。

比如:MAX(姓名),MIN(姓名)，返回的结果分别是马云和李猴子

如果我不想重复计算相同的数据，那么在计数的列名前面加distinct

SELECT COUNT(DISTINCT 姓名)

FROM student;

接下来我们进行习题练习：

查询课程学号为‘0002’的总成绩

SELECT SUM( 成绩 )

FROM score

WHERE 学号 = '0002';

查询选了课程的学生人数

SELECT COUNT(DISTINCT 学号) AS 学生人数

FROM score;

注意：

1、所有的汇总函数都可以使用DISTINCT，在汇总函数的参数中使用DISTINCT，可以删除重复数据

二、分组(GROUP BY子句)

我想将student表中的数据按照性别进行分组统计男生和女生各有多少人，那么查询语法为：

SELECT 性别,COUNT(*) AS 学生人数 -- 统计出所有行男女性别的个数FROM student -- 从student的表中查询GROUP BY 性别;-- 对性别进行分组

对某列进行分组，那么该列的列名就被称为聚合建，上面的例子中，“性别”这一列就是聚合建。

使用GROUP BY子句常见易错知识点：

1、使用GROUP BY子句中，SELECT子句中不能出现聚合键之外的列名。总结一下就是SELECT子句中只能存在下面三种元素：

常数

汇总函数

GROUP BY子句中指定的列名(聚合键)

2、GROUP BY子句不能使用SELECT子句中定义的别名，

因为SQL在执行中是先执行GROUP BY子句，后执行SELECT子句，所以在SELECT子句中定义的别名在SQL执行GROUP BY子句时，他是不知道这别名代表是什么东西。

3、GROUP BY子句的结果是无序的

接下来我们进行习题练习：

查询各科成绩最高和最低的分

SELECT 课程表,MAX(成绩) AS 最高分,MIN(成绩) AS 最低分

FROM score

GROUP BY 课程表;

查询每门课程被选修的学生数

SELECT 课程表,COUNT(学号) AS 学生人数

FROM score

GROUP BY 课程表;

查询男生、女生人数

SELECT 性别,COUNT(性别) AS 学生人数

FROM student

GROUP BY 性别;

三、对分组结果指定条件(HAVING子句)

如果我想从student表中只查询出男生的人数，而不要女生的人数。

SELECT 性别,COUNT(性别) AS 学生人数

FROM student

GROUP BY 性别

HAVING COUNT(*)>1;-- 行数大于1

注意：

1、HAVING子句和SELECT子句一样，只能存在下面三种元素：

常数

汇总函数

GROUP BY子句中指定的列名(聚合键)

2、聚合键所对应的条件建议写在WHERE子句中，而不是HAVING子句中，这样可以提高运算速度，SQL语句也更容易理解。他们两者的作用区别 可以理解为：

WHERE子句=指定行所对应的的条件

HAVING子句=指定组所对应的条件

接下来我们进行习题练习：

查询平均成绩大于60分学生的学号和平均成绩

SELECT 学号,AVG(成绩)

FROM score

GROUP BY 学号

HAVING AVG(成绩) > 60;

查询至少选修两门功课的学生的学号

SELECT 学号,COUNT(课程号) AS 选修课程数

FROM score

GROUP BY 学号

HAVING COUNT(课程号) >= 2;

查询同名同姓学生名单并统计同名人数

SELECT 姓名,COUNT(姓名) AS 同名人数

FROM student

GROUP BY 姓名

HAVING COUNT(姓名) > 1;

查询每门课程的平均成绩，并且平均成绩大于等于60分

SELECT 课程号,AVG(成绩)

FROM score

GROUP BY 课程号

HAVING AVG(成绩)>=60;

四、对查询结果排序(ORDER BY)

将成绩按降序进行排序(从大到小)：

SELECT 课程号,成绩

FROM score

ORDER BY 成绩 DESC;

将成绩按升序进行排序(从小到大)：(下面程序不加ASC也是可以的，不加排序条件情况下，默认是从小到大，升序排列)

SELECT 课程号,成绩

FROM score

ORDER BY 成绩 ASC;

查询每门课程的平均成绩，并且平均成绩大于等于60分，并按降序进行排序(从大到小)：

SELECT 课程号,AVG(成绩) AS 平均成绩

FROM score

GROUP BY 课程号

HAVING AVG(成绩)>=60

ORDER BY 平均成绩 DESC;

对多列进行排序，会按列名从左到右排序

SELECT *

FROM score

ORDER BY 成绩 ASC,课程号 DESC;

上面查询语句中先按成绩升序排列，成绩相同时课程号按照降序排列。

空值(null)的排序(空值会在查询结果的开头或者末尾显示)

SELECT *

FROM teacher

ORDER BY 教师姓名;

MYSQL这里NULL是在开头显示

注意：在ORDER BY子句中可以使用聚合函数或者使用SELECT子句中未使用的列

如果查询出来的结果有几万行，我只想看前三行，那么该怎么办？

SELECT *

FROM score

LIMIT 3;

如果我想查询第4行后面的2行数据，要怎么办？

SELECT *

FROM score

LIMIT 2 OFFSET 4;

接下来我们进行习题练习：

查询不及格的课程并按课程号从大到小排列

SELECT 课程号,成绩

FROM score

WHERE 成绩<60

ORDER BY 课程号 DESC;

查询每门课程的平均成绩，结果按照平均成绩升序排列，平均成绩相同时，按课程号进行降序排列

SELECT 课程号,AVG(成绩)

FROM score

GROUP BY 课程号

ORDER BY AVG(成绩) ASC,课程号 DESC;

欢迎大家看看我写的其他MySQL基础知识系列文章，希望对你们有所帮助！

1、MySQL基础入门知识

2、MySQL基础知识—简单查询

4、MySQL基础知识—复杂查询

5、MySQL基础知识—多表查询

五、实践出真知

现在我们打开SQLZOO网站：https://sqlzoo.net/，点击下面图片红色框选部分，开始习题练习，进行查询语句的练习：

第一部分、选择诺贝尔奖

第一题代码：

SELECT yr, subject, winner

FROM nobel

WHERE yr = 1950;

第二题代码：

SELECT winner

FROM nobel

WHERE yr = 1962 AND subject = 'Literature';

第三题代码：

SELECT yr, subject

FROM nobel

WHERE winner = 'Albert Einstein';

第四题代码：

SELECT winner

FROM nobel

WHERE yr>=2000 and subject = 'Peace';

第五题代码：

SELECT yr, subject, winner

FROM nobel

WHERE subject='Literature'and yr>=1980 and yr<=1989;

第六题代码：

SELECT *

FROM nobel

WHERE winner IN ('Theodore Roosevelt','Woodrow Wilson', 'Jimmy Carter','Barack Obama');

第七题代码：

SELECT winner

FROM nobel

WHERE winner LIKE 'John%';

第八题代码：

SELECT yr,subject,winner

FROM nobel

WHERE (yr='1980'and subject = 'Physics')OR(yr='1984'and subject = 'Chemistry');

第九题代码:

SELECT yr,subject,winner

FROM nobel

WHERE yr='1980'AND subject NOT IN('Chemistry','Medicine');

第十题代码：

SELECT yr,subject,winner

FROM nobel

WHERE (yr<1910 and subject='Medicine')OR (yr>='2004'and subject='Literature');

第十一题代码：

SELECT yr,subject,winner

FROM nobel

WHERE winner IN ('PETER GRÜNBERG');

第十二题代码：

SELECT yr,subject,winner

FROM nobel

WHERE winner = 'EUGENE O''NEILL'

第十三题代码：

SELECT winner,yr,subject

FROM nobel

WHERE winner LIKE 'sir%'

ORDER BY yr DESC,winner;

第十四题代码：

SELECT winner, subject

FROM nobel

WHERE yr=1984

ORDER BY subject IN ('Physics','Chemistry'),subject,winner

第二部分、SUM和COUNT

第一题代码：

SELECT SUM(population)

FROM world;

第二题代码：

SELECT DISTINCT continent

FROM world;

第三题代码：

SELECT SUM(gdp)

FROM world

WHERE continent = 'Africa';

第四题代码：

SELECT count(name)

FROM world

WHERE area>=1000000;

第五题代码：

SELECT sum(population)

FROM world

WHERE name IN ('Estonia','Latvia','Lithuania');

第六题代码：

SELECT continent,count(name)

FROM world

GROUP BY continent;

第七题代码：

SELECT continent,count(name)

FROM world

WHERE population>=10000000

GROUP BY continent;

第八题代码：

SELECT continent

FROM world

GROUP BY continent

HAVING sum(population)>=100000000;