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

发布于:2022-11-09 ⋅ 阅读:(9) ⋅ 点赞:(0) ⋅ 评论:(0)

目录

一、聚合函数

 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