SQL Server 基础语法3(超详细!)

---

统计所有学生平均分

use school 
select AVG(grade) as 平均分
from SC
1
2
3

统计每个学生的总分、平均分、课程门数

select sno as 学号,SUM(grade) as 总分,AVG(grade) as 平均分,COUNT(sno) 门数
from SC
group by sno
1
2
3

求学号为“20010101”学生的总分和平均分

select sno,SUM(grade) as 总分,AVG(grade) as 平均分
from SC
where sno=20010101
group by sno
1
2
3
4

求选修“C1”号课程的最高分、最低分及之间相差的分数

select MAX(grade) as 最高分,MIN(grade) as 最低分,MAX(grade)-MIN(grade) as 差值
from SC
where cno='C1' --int型无需加引号，varchar型要
1
2
3

求选修“C1”号课程的最高分、最低分及之间相差的分数

select cno as 课程号,MAX(grade) as 最高分,MIN(grade) as 最低分,MAX(grade)-MIN(grade) as 差值
from SC
where cno='C1' --int型无需加引号，varchar型要
group by cno --单个列与聚合函数同时查找时，分组需要按照这个列来分组
1
2
3
4

求选修“C1”号课程的学生人数和最高分

select COUNT(sno) 选课人数,MAX(grade) as 最高分
from SC                                                                         
where cno='C1'
1
2
3

求“计算机系”学生的总数

select COUNT(distinct sno) as 总数
from Student
where sdept='计算机'
1
2
3

统计有成绩同学的人数

select COUNT(distinct sno) as 人数 --如果不加distinct，那么会把不同课程的成绩，但是学号相同的算作多个人
from SC
where grade is not NULL
--where grade!=NULL --是空（is NULL）或不是空（is not NULL),没有等不等于NULL
1
2
3
4

3.8求女学生总数和平均年龄

select COUNT(distinct sno) as 女生人数,AVG(sage) as 平均年龄
from Student
where sex='女'
1
2
3

按系来统计女学生的人数

select sdept as 系,COUNT(distinct sno) as 女生人数
from Student
where sex='女'--出现在条件中的列不能出现在查询中
group by sdept
1
2
3
4

查询选修两门以上课程的学生的学号和选课门数

select sno as 学号,COUNT(cno) as 选课门数
from SC
group by sno 
--where COUNT(cno)>=2 where语句的子句不能用聚合类型
having COUNT(cno)>=2
1
2
3
4
5

查询平均成绩大于70分的课程号和平均成绩

select cno as 课程号,AVG(grade) as 平均成绩
from SC
group by cno
having AVG(grade)>70
1
2
3
4

查询选修“C1”的学生学号和成绩，并按成绩降序排列

select sno as 学号,grade as 成绩
from SC
where cno='C1'
order by grade desc
1
2
3
4

查询选修“C2、C3、C4”或“C5”课程的学号、课程号和成绩，

 --查询结果按学号升序排列，学号相同再按成绩降序排列cx
select sno as 学号,cno as 课程号,grade as 成绩
from SC
where cno in('C2','C3','C4','C5')
order by sno asc,grade desc--asc升序，desc降序
1
2
3
4
5

求有三门以上选课成绩及格的学生的学号及其总成绩，查询结果按总成绩降序列出

select  sno as 学号,SUM(grade) as 总分
from SC
where grade>=60
group by sno
having COUNT(cno)>3
order by SUM(grade) desc
1
2
3
4
5
6