五天搞定Mysql基础知识-Day03

【接上文】

学习目标：      

        1、掌握五种聚合函数的使用方法

        2、了解分组和分页

第五章 聚合函数

为了快速得到统计数据，经常会用到如下5个聚合函数

注意：聚合函数不能在where后面的条件中使用

一、count总记录数

count(*)表示计算总记录数，括号中写*与字段名，结果是相同的

例1：查询学生总数（学生总数就是students表中记录的总数）

        SELECT COUNT(*) FROM students;

        SELECT COUNT(NAME) FROM students;

例2：查询性别sex为‘女’的学生总数

        SELECT COUNT(*) FROM students WHERE sex = '女';

二、max最大值

max（字段）表示此字段的最大值

例3；查询最大age年龄

        SELECT MAX(age) FROM students;

例4：查询性别为‘女’的最大age年龄、

        SELECT max(age) FROM students WHERE sex = '女';

三、min最小值

min（字段）表示此字段的最小值

例5：查询学生最小age年龄

        SELECT min(age) FROM students;

例6：查询class班级为‘1班’的最小age年龄

        SELECT MIN(age) FROM students WHERE class = '1班';

四、sum求和

sum（字段）表示此字段的和

例7：查询学生age年龄总和

        SELECT SUM(age) FROM students;

例8：查询hometown为‘北京’的学生age年龄总和

        SELECT SUM(age) FROM students WHERE hometown = '北京';

五、avg平均值

avg（字段）表示此字段的平均值

avg的字段中如果有null，null不作为分母计算平均值

例9：查询学生平均年龄

        SELECT AVG(age) FROM students;

例10：查询sex性别为‘男’的平均年龄

        SELECT AVG(age) FROM students WHERE sex = '男';

练习：

练习1：查询所有学生的最大年龄、最小年龄、平均年龄

SELECT MAX(age), MIN(age), AVG(age) FROM students;

练习2：查询’1班‘共有多少个学生

SELECT COUNT(*) FROM students WHERE class = '1班';

练习3：查询’3班‘中年龄小于30岁的同学有几个

SELECT COUNT(*) FROM students WHERE class = '3班' AND age < 30;

第六章 数据分组

一、分组

按照字段分组，表示此字段相同的数据会被放到一个组中

分组的目的是配合聚合函数，聚合函数会对每一组数据分别进行统计

语法：

        select 字段1,字段2,聚合函数... from 表名 group by 字段1,字段2...

例1：查询各种sex性别的人数

        SELECT sex, COUNT(sex) FROM students GROUP BY sex;

例2：查询各种age年龄的人数

        SELECT age, COUNt(age) FROM students GROUP BY age;

例3：分别查询‘1班’不同性别学生数量

        SELECT sex, COUNT(*) FROM students WHERE class = '1班' GROUP BY sex;

练习：

练习1：用数据分组的方法，统计各个班级学生总数、平均年龄、最大年龄、最小年龄

SELECT class, COUNT(*), AVG(age), MAX(age), MIN(age) FROM students GROUP BY class;

练习2：用数据分组的方法，统计各个班级学生总数、平均年龄、最大年龄、最小年龄，但不统计三班，统计结果按班级名称从大到小排序

SELECT class, COUNT(*), AVG(age), MAX(age), MIN(age) FROM students WHERE class != '3班' GROUP BY class ORDER BY class DESC;

二、分组后的数据筛选

1、语法：

select 字段1,字段2,聚合... from 表名

group by 字段1,字段2,字段3...

having 字段1,...聚合...

2、having 后面的条件运算符与where的相同

例1：使用where子句，查询男生总人数

        SELECT COUNT(*) FROM students WHERE sex = '男';

例2：使用having子句，查询男生总人数

        SELECT sex, COUNT(*) FROM students GROUP BY sex HAVING sex = '男';

练习：

练习：用having子句，查询除了‘1班’以外，其他各个班级学生的平均年龄、最大年龄、最小年龄

SELECT class, MAX(age), MIN(age) FROM students GROUP BY class HAVING class != '1班';

三、having配合聚合函数的使用

where后面条件不能使用聚合函数，having可以使用聚合函数

例：求班级人数大于3人的班级名字

        SELECT class FROM students GROUP BY class HAVING COUNT(*) > 3;

四、对比where与having

1、where是对form后面指定的表进行数据筛选，属于对原始数据的筛选

2、having是对group by的结果进行筛选

3、having后面的条件中可以使用聚合函数，where后面的条件不可以使用聚合函数

练习：

练习1：查询班级总人数大于2人的班级名称以及班级对应的总人数

        SELECT class, COUNT() FROM students GROUP BY class HAVING COUNT() > 2;

练习2：查询平均年龄大于30岁的班级名称和班级总人数

        SELECT class, COUNT(*) FROM students GROUP BY class HAVING AVG(age) > 30;

第七章 数据分页显示

一、获取部分行

1、当数据量过大时，在一页中查看数据是一件非常麻烦的事情；

2、语法：limit 开始行，获取行数（select * from 表名 limit start, count）；

3、从start开始，获取count条数据；

4、start索引从0开始，如省略start默认从0开始；

例1：查询前3行学生的记录

        SELECT * FROM students LIMIT 0,3;

练习：

练习1：查询从第4行开始的3条学生记录

        SELECT * FROM students LIMIT 3,3;

练习2：使用limit语句，查询年龄最大的学生记录

        SELECT * FROM students ORDER BY age DESC LIMIT 0,1;

练习3：查询年龄最小的女同学信息

        SELECT * FROM students WHERE sex = '女' ORDER BY age ASC LIMIT 0,1;

注：当有 where 或者 group by 或者 order by，limit 总是出现在最后

二、分页

1、当一张表记录特别多的时候，就需要用到分页显示

2、已知：每页显示m条数据，求：查询第n页的数据

select * from students limit(n-1)*m, m

例1：每页显示4条记录，查询第3页的数据:

        m=4；

        n=3；

        (n-1)*m = (3-1)*4 = 8;

        select * from students limit 8,4;

例2：查询 students 表，每页显示5条记录，求总页数

1、查询记录总条数 a；

2、使用 a 除以每页显示条数5，得到 b；

3、如果 b 为整数，则 b 为总数页；

4、如果 b 不为整数，则 b+1 为总页数；

	操作	说明
第一步	select count(*) from students;	得到students表的总条数，结果为：12
第二步	12/5 = 2.4	不能整除
第三步	2 + 1 = 3	总页数为3页

练习：

-- 练习：查询students表，要求每页显示5条记录，每页用一条select语句，分别用多条select语句，显示从第一页到最后一页的数据

-- 第一页

SELECT * FROM students LIMIT 5;

-- 第二页

SELECT * FROM students LIMIT 5,5;

-- 第三页

SELECT * FROM students LIMIT 10,5;