聚合函数04

一、聚合函数

1、conut() 计数

select cno count(sno) from sc

2、sum() 求和

-- 每名学生的总成绩
select cno ,sum(grade) from sc group by cno;

3、avg() 求平均值

-- 每名学生的总成绩
select cno ,sum(grade) from sc group by cno;

二、分组

1、group by

#求某课的选课人数
select cno count(sno) from sc group by sno
#在学好组内进行统计  每个学生
select sno 学号,count(*) 选课门数, avg(grade) 平均成绩 from sc group by sno
#统计每个系的女生人数  每个系
select dept,count(*) 女生人数 from student where sex = '女' group by dept
#多个列分组， 统计每个系的男生人数和女生人数以及男生最大年龄和女生的最大年龄，结果按系名的升序排序
#分组 系别 性别
select dept,sex,count(*) 人数,max(sage) 最大年龄 
from student 
group by dept,sex 
order by dept asc   #(默认升序)

2、having

用在分组之后，再进行筛选

#查询选课门数超过3门的学生的学号和选课门数
select sno,count(*) 选课门数 from sc group by sno having count(*) > 3
#查询
select sno,avg(grade) 平均成绩,count(*) 选课门数 from sc group by sno having count(*) >=4
#查询计算机系和信息管理系每个系的学生人数
select dept,count(*) 
from student 
group by dept 
having dept in ('计算机系','信息管理系')   # 先分组后筛选   不推荐

select dept,count(*) 
from student 
where dept in ('计算机系','信息管理系')
group by dept  # 先筛选后分组  推荐

三、多表查询

1、内连接

• 非ANSI ：where子句中执行
• ANSI：join子句中执行

select * from student inner join sc on student.sno = sc.sno
# 去重复项
select student.sno,sname,sex,sage,dept,cno,grade from student inner join sc on student.sno = sc.sno
# 查询计算机系学生的修课情况，要求列出学生的名字、所修课的课程号和成绩
select sname,cno,grade from student  join sc on student.sno = sc.sno where dept = '计算机系'
# 表取别名  在后面就只能用别名了

#查询“信息管理系”修了“计算机文化学"的学生姓名和成绩
select sname,grade
from student s
join sc on s.sno=sc,sno
join course c on c.cno = sc.cno
where dept = '信息管理系'
and cname = '计算机文化学'
#查询所有选修了java课程的学生情况，列出学生姓名和所在系
select sname,dept from student s
join sc on s.sno = sc.sno
join course c on c.cno=sc.cno
where cname='java'
# 有分组的多变连接查询，统计每个系的学生的考试平均成绩
select dept,avg(grade) as 平均成绩 
from student s join sc
on s.sno=sc.sno
group by dept
# 有分组和行选择条件的多表连接查询。统计计算机系学生每门课程的选课人数、平均成绩、最高成绩和最低成绩
select cno,count(*),avg(grade),max(grade),min(grade)
from student s 
join sc on s.sno=sc.sno
where dept = '计算机系'
group by cno

2、自连接

• 特殊的内连接
• 物理上为同一张表
• 必须给表取别名，使之在逻辑上成为两个表

# 查询与刘晨在同一个系学习的学生姓名和所在的系
select s2.sname,s2.dept
from student s1 join student s2
on s1.dept = s2.dept
where s1.sname = '刘晨'
and s2.sname != '刘晨'

# 查询至少被两个以上的学生选的课程的课程号
select cno from sc
group by cno
having count(*) > 1

3、内，外连接

• 内连接：inner join

  公共部分

• 左外连接 left join

  以左表内容为主

• 右外连接 right join

  以右表内容为主

# 查询全体学生的选课抢矿，包括选修了课程的学生和没有选修课程的学生
select student ,sno,sname,cno,grade from student left join sc on student.sno=sc.sno

select student.sno,sname,cno,grade from sc right join student on student.sno=sc.sno

#查询没有人选的课程号
select c.cno,cname from course c left  outer join sc on c.cno=sc.cno where sc.cno is null;

#查询计算机系没有选课的学生姓名
select sname from student left outer join sc on student.sno=sc.sno where sc.cno is null and  dept='计算机系'

#查询计算机系所有学生选课门数按照门数降序排序
select sname ,count(cno) from student left join sc on student.sno=sc.sno where dept='计算机系' 
group by sname order by count(cno) desc;

• 全外连接 full join

  保留两个表中都不满足连接条件的数据行

4、top

#取前三行
select top 3  sno from student;
#返回并列项  使用了with ties 后面就一定需要 order by 对结果进行排序
select top 3 with ties sname,dept,grade 
from student s 
join sc 
on s.sno =sc.sno
join course c
on c.cno=sc.cno
where cname ='java'
order by grade desc;
#查询选课人数最少的两门课程（不包括没有人选的课程）,列出课程号和选课人数
select top 2 with ties cno,count(*) 选课人数 
from sc
group by cno
order bu conut(cno) asc
#查询计算机系选课门数超过2门的学生中，考试平均成绩最高的前2名(包括并列的情况)学生的学号，选课门数和平均成绩
select top 2 with ties s.sno,count(*) 选课门数,avg(grade) 平均成绩
from student s
join sc
on s.sno-sc.sno
where dept='计算机系'
group by s.sno
having count(*)>2
order by avg(grade) desc;
#

5、查询并保存

临时表存与内存

#：局部临时表：创建开始-当前连接使用

##：全局临时表：创建开始-所有连接都可以使用

# 将计算机系的学生细心保存到# computerStudent局部临时表中
select sno,sname,sex,sage into #computerStudent from student where dept='计算机系'

#将选了java课程的学生的学号及成绩存入永久表java_grade中
select sno,grade into java_grade from sc join course con c.cno =sc.con where cname='java';