汇总分析
目录:
- 汇总分析
- 基本查询语句
- 练习题
一、汇总分析
- count:求某列的行数(列名查询则排除空值)不重复计数count(distinct)
- sum:求和
- avg:求平均值
- max:最大值
- min:最小值
二、基本查询语句
select--查询指定显示的字段;查询所有列:*
from--所查询的表
where--限制条件(如:大于,小于等)
group by--分组
having--对分组结果指定条件
order by--对查询结果进行排序(Asc 升序,Desc降序),默认是升序
limit--从查询结果取出前2行数据(limit 2)
注意:group by与having不能使用别名,运行顺序在select之前;where不能使用聚合函数
三、练习题
创建表
/*在student中插入数据*/
insert into student(学号,姓名,出生日期,性别)
values('0001' , '猴子' , '1989-01-01' , '男');
insert into student(学号,姓名,出生日期,性别)
values('0002' , '猴子' , '1990-12-21' , '女');
insert into student(学号,姓名,出生日期,性别)
values('0003' , '马云' , '1991-12-21' , '男');
insert into student(学号,姓名,出生日期,性别)
values('0004' , '王思聪' , '1990-05-20' , '男');
/*创建表score,并在score中插入数据*/
create table score (学号 varchar(225),课程号 varchar(225),成绩 varchar(225));
insert into score(学号,课程号,成绩)
values('0001' , '0001' , 80);
insert into score(学号,课程号,成绩)
values('0001' , '0002' , 90);
insert into score(学号,课程号,成绩)
values('0001' , '0003' , 99);
insert into score(学号,课程号,成绩)
values('0002' , '0002' , 60);
insert into score(学号,课程号,成绩)
values('0002' , '0003' , 80);
insert into score(学号,课程号,成绩)
values('0003' , '0001' , 80);
insert into score(学号,课程号,成绩)
values('0003' , '0002' , 80);
insert into score(学号,课程号,成绩)
values('0003' , '0003' , 80);
/*创建表course,并在course中插入数据*/
create table course (课程号 varchar(225),课程名称 varchar(225),教师号 varchar(225));
insert into course(课程号,课程名称,教师号)
values('0001' , '语文' , '0002');
insert into course(课程号,课程名称,教师号)
values('0002' , '数学' , '0001');
insert into course(课程号,课程名称,教师号)
values('0003' , '英语' , '0003');
-- 创建teacher,并添加数据
create table teacher (教师号 varchar(225),教师姓名 varchar(225));
insert into teacher(教师号,教师姓名)
values('0001' , '孟扎扎');
insert into teacher(教师号,教师姓名)
values('0002' , '马化腾');
-- 这里的教师姓名是空值(null)
insert into teacher(教师号,教师姓名)
values('0003' , null);
-- 这里的教师姓名是空字符串('')
insert into teacher(教师号,教师姓名)
values('0004' , '');
1.练习:汇总分析
查询课程编号为“0002”的总成绩
select sum(成绩) from score where 课程号 = '0002';
查询选了课程的学生人数
select count(distinct 学号) as 学生人数 from score;
2.练习:分组
查询各科成绩最高和最低分
-- 考查分组
select 课程号,max(成绩) as 最高分,min(成绩) as 最低分
from score
group by 课程号;
查询每门课程被选修的学生人数
select 课程号, count(学号)
from score
group by 课程号
查询男生、女生人数
select 性别,count(*)
from student
group by 性别
3.练习:分组结果的条件
查询平均成绩大于60分学生的学号和平均成绩
select 学号, avg(成绩)
from score
group by 学号
having avg(成绩)>60
查询至少选修两门课程的学生学号
select 学号, count(课程号) as 选修课程数目
from score
group by 学号
having count(课程号)>=2
查询同名同性学生名单并统计同名人数
select 姓名,count(*) as 人数
from student
group by 姓名
having count(*)>=2
4.练习:理解sql的运行顺序
查询不及格的课程并按课程号从大到小排列
select 课程号
from score
where 成绩<60
order by 课程号 desc
查询每门课程的平均成绩,结果按平均成绩升序排序,平均成绩相同时,按课程号降序排列
select 课程号, avg(成绩) as 平均成绩
from score
group by 课程号
order by 平均成绩 asc,课程号 desc
检索课程编号为“04”且分数小于60的学生学号,结果按按分数降序排列
select 学号
from score
where 课程号='04' and 成绩 <60
order by 成绩 desc
统计每门课程的学生选修人数(超过2人的课程才统计)
要求输出课程号和选修人数,查询结果按人数降序排序,若人数相同,按课程号升序排序
select 课程号, count(学号) as '选修人数'
from score
group by 课程号
having count(学号)>2
order by count(学号) desc,课程号 asc
查询两门以上不及格课程的同学的学号及不及格课程的平均成绩
select 学号, avg(成绩) as 平均成绩,count(课程号)
from score
where 成绩 <60
group by 学号
having count(课程号)>=2;