-- 聚合函数中null的处理,建立一个test1 表
create table test1(
id int primary key auto_increment,
name varchar(20)
);
-- 向test1表中输入数据ABCD和四个空
insert into test1(name) values
('A'),('B'),('C'),('D'),
(Null),(Null),(Null),(Null);
select count(*) from test1; --4条
select count(name) from test1; -- 4条,因为null不算
select count(null) from test1; -- 0条,全是字符全部被null替换
-- 在sum/avg/max/min遇到null(包含其他非数字字段),值会视为0,聚合的时候就不考虑这些了
-- 建立一个test2表
create table test2(
id int primary key auto_increment,
name int(20)
);
-- 向test2表中输入数据
insert into test2(name) values
('1'),('2'),('3'),('4'),
(Null),(Null),(Null),(Null);
select sum(name) from test2; -- 求和1+2+3+4=10
select avg(name) from test2; -- 求平均值2.5
select max(name) from test2; -- 求最大值4
select min(name) from test2; --求最小值1
-- 符合规则:有null参与的表达式
select count(age+null) from students; -- 0
-- 因为 count(age + null) 视为 count( null ) 了。
-- 带where条件的聚合,首先进行where条件的筛选,再进行聚合
-- 建立students表,并向其中输入数据
create table students(
id int primary key auto_increment,
name varchar(20) not null,
age int not null,
gender varchar(1) not null
);
insert into students(name,age,gender) values
('小红','1','女'),
('小蓝','1','男'),
('小刚','2','男'),
('小黄','2','女'),
('小橙','3','女');
select count(*) from students; -- 所有结果
select count(*) from students where gender='女'; -- 性别为女的人数
select count(*) from students where gender='男'; -- 性别为男的人数
select count(*) from students where age>1; -- id大于1的人数(我忘了,求大神指点)
select count(age)from students where age>2; -- age大于2的人数
select sum(age) from students where age>2; -- age>2的age求和
-- 分组聚合
-- 按性别进行分类
-- 由于5行数据中,gender字段的值只有两个
select count(*) from students group by gender;
-- 聚合查询的select后边,可以出现两种东西:聚合函数、分组聚合时间、分组的依据字段
-- gender作为分组凭证,可以出现在select后面,所以有
select gender,count(*) from students group by gender;
select name,count(*) from studentd;-- 错误用法
-- 分组凭证可以是字段,也可以是表达式
select age % 2 as 奇偶性,count(*) from students group by age%2;
select age % 2 as 奇偶性,count(*) from students group by 奇偶性;
-- 可以同时以多个表达式作为分组
select age % 2 as 奇偶性,gender,count(*) from students group by gender,奇偶性;
select age,count(*) from students group by age;
-- 从分组聚合的结果中,再进行一次聚合:having语句
select * from students;
select * from students where age <= 2;
select age , count(*) from students where age <= 2 group by age;
select age , count(*) from students where age <= 1 group by age having count(*) =1;
select age , count(*) from students where age <= 1 group by age having count(*) =1 order by age limit 1 offset 0;