聚合函数:
-- count,sum,avg,max,min 语句格式皆为:
select count(id) from student; -- 添加起别名后为:
select cout(id) as 总和 from student;
-- ifnull 语句格式:
select avg(ifnull(id,0)) as 总和 from student;
-- 即id这个字段中若有null,则在统计计算时变为0
group by:(分组)
案例:
create database mydb4;
use mydb4;
create table student(
name varchar(30),
sex char(10),
course varchar(30),
score int
);
insert into student values('张三', '男', '语文', 93);
insert into student values('张三', '男','数学', 96);
insert into student values('张三', '男', '英语', 99);
insert into student values('李四', '女', '语文', 90);
insert into student values('李四', '女','数学', 85);
insert into student values('王五', '男', '语文', 80);
insert into student values('王五', '男','数学', 75);
insert into student values('王五', '男', '英语', 70);
需求1: 求每个学生的总成绩?
需求2 : 求平均分大于80的学生?
解答1:
-- 需求1: 求每个学生的总成绩?
select name,sum(score) from student where name = "张三" union all
select name,sum(score) from student where name = "李四" union all
select name,sum(score) from student where name = "王五" union all;
-- 需求2 : 求平均分大于80的学生?
select name, avg(score)
from student
group by name
having avg(score)>80;