7、MySQL——聚合函数、分组查询、HAVING和WHERE子句的区别、LIMIT、分页查询

目录

一、聚合函数

 1、纵向统计：COUNT

 2、纵向求和：SUM

 3、纵向求平均数值：AVG

 4、MAX和MIN

 二、分组查询

 三、HAVING子句和WHERE子句的区别

四、LIMIT

五、分页查询

---

一、聚合函数

聚合函数是用来做纵向运算的函数。

（1）COUNT()：统计指定列不为NULL的记录行数;

（2）MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

（3）MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算

（4）SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

（5）AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0.

创建一个员工工资表：

CREATE DATABASE mysql07;

USE mysql07;

CREATE TABLE `wages`(

`num` INT PRIMARY KEY,

`name` VARCHAR(50) NOT NULL,

`age` INT NOT NULL,

`salary` DOUBLE NOT NULL,

`bonus` DOUBLE 

);

插入五条记录

INSERT INTO wages VALUES

(1,'zhangsan',32,5204,200),

 (2,'lisa',28,4900,500),

(3,'helen',35,6000,400),

(4,'wanger',33,5442,100),

(5,'mazi',29,4800,NULL);

 1、纵向统计：COUNT

（1）查询wages表中记录数

SELECT COUNT(*) FROM wages；

（2）查询wages表中有奖金的人数

SELECT COUNT(bonus) FROM wages;

（3）查询wages表中工资大于5000的人数

SELECT COUNT(*) FROM wages WHERE salary>5000;

 （4）查询工资与奖金之和大于5500的人数

SELECT COUNT(*) FROM wages WHERE salary+IFNULL(bonus,0)>5500;

 2、纵向求和：SUM

（1）查询所有员工月薪和

SELECT SUM(salary) FROM wages;

 （2）查询所有员工月薪和，奖金和

SELECT SUM(salary),SUM(bonus) FROM wages;

 （3）查询所有员工应发工资的和

SELECT SUM(salary+IFNULL(bonus,0)) FROM wages;

 3、纵向求平均数值：AVG

（1）统计所有员工平均月薪

SELECT AVG(salary) FROM wages;

（2）统计所有员工应发工资的平均数 

SELECT AVG(salary+IFNULL(bonus,0)) FROM wages;

 4、MAX和MIN

查询最低工资和最高奖金

SELECT MIN(salary),MAX(bonus) FROM wages;

 二、分组查询

英文单词：group  组，团体

注意：凡是和聚合函数同时出现的列名，一定要写在group by 之后

创建school数据表

CREATE TABLE `school`(

`class` INT NOT NULL,

`name` VARCHAR(50) NOT NULL,

`language` DOUBLE,

`maths· DOUBLE,

`english· DOUBLE

);

插入七条数据 

INSERT INTO school VALUES

    (1,'zhangsan',64,86,52),
    (3,'helen',88,94,90),
    (2,'lisa',92,74,86),
    (3,'wanger',48,84,68),
    (2,'mazi',72,68,76),
    (3,'bady',66,80,0),
    (1,'lisi',70,NULL,NULL);

 （1）查询每个班级的班级编号和每个班级的数学总分

SELECT class,SUM(maths) FROM school GROUP BY class;

 （2）查询每个班级的编号和每个班级的人数

SELECT class,COUNT(*) FROM school GROUP BY class;

 三、HAVING子句和WHERE子句的区别

查询英语成绩总和大于150的班级编号及英语总分

SELECT class,SUM(english) FROM school GROUP BY class HAVING SUM(english)>150;

 SELECT class,SUM(english) FROM school GROUP BY class WHERE SUM(english)>150;

或

 SELECT class,SUM(english) FROM school WHERE SUM(english)>150 GROUP BY class;

都会报错

 HAVING和WHERE的区别：

（1）HAVING是在分组后对数据进行过滤；

WHERE是在分组前对数据进行过滤。

（2）HAVING后面可以使用分组函数（统计函数）；

WHERE后面不可以使用分组函数。

WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。

四、LIMIT

LIMIT用来限定查询结果的起始行，以及总行数。 

（1）查询五条记录，起始行从0开始

 SELECT * FROM school LIMIT 0,5;

注意，起始行从0开始，即从第一行开始！ 

（2）查询5行记录，起始行从第2行开始

SELECT * FROM school LIMIT 2,5;

五、分页查询

如果一页记录为10条，希望查看第3页记录应该怎么查呢？

1. 第一页记录起始行为0，一共查询10行；
2. 第二页记录起始行为10，一共查询10行；
3. 第三页记录起始行为20，一共查询10行；

 分页查询：

查询语句书写顺序：select – from- where- group by- having- order by-limit

查询语句执行顺序：from - where -group by - having - select - order by-limit