50个经典SQL查询语句实战（学生选课表案例）

数据表结构与背景

在进行后续操作之前，首先需要明确实验环境的表结构。本系列语句基于以下四张核心表设计，主要使用标准T-SQL/SQL Server语法：

• Student(S#,Sname,Sage,Ssex)：学生表（学号、姓名、年龄、性别）
• Course(C#,Cname,T#)：课程表（课程编号、课程名称、教师编号）
• SC(S#,C#,score)：成绩表（学号、课程编号、分数）
• Teacher(T#,Tname)：教师表（教师编号、姓名）

第1步：基础筛选与单表统计

本阶段针对单一或简单连接的表进行条件过滤和简单的计数统计，是理解数据分布的基础。

查询姓“李”的老师的个数

使用LIKE模糊匹配结合聚合函数。

select count(distinct(Tname))
from Teacher
where Tname like '李%';

查询男生、女生人数

利用group by对性别字段进行分组并统计。

Select count(Ssex) as 男生人数 from Student group by Ssex having Ssex='男';
Select count(Ssex) as 女生人数 from Student group by Ssex having Ssex='女';

查询所有课程成绩小于60分的同学的学号、姓名

通过not in排除存在任何一门课成绩大于60分的学生。

select S#,Sname
from Student
where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);

查询没有学全所有课的同学的学号、姓名

比较该学生的选课数量与系统中课程的总数。

select Student.S#,Student.Sname
from Student,SC
where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);

第2步：多表连接与复合条件查询

当数据分散在不同表中时，需通过主外键关系进行Join操作。此部分重点演示了自连接和多表嵌套查询的应用。

查询“001”课程比“002”课程成绩高的所有学生的学号

将SC表分别作为别名a和b进行自连接，对比不同课程的成绩。

select a.S# from (select s#,score from SC where C#='001') a,(select s#,score
from SC where C#='002') b
where a.score>b.score and a.s#=b.s#;

查询学过“001”并且也学过编号“002”课程的同学的学号、姓名

使用exists关键字验证是否存在满足另一条件的记录。

select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');

查询“叶平”老师所教的所有课中成绩最高的学生姓名及其成绩

关联三个表定位特定老师，并通过子查询找出对应课程下的最高分。

select Student.Sname,score
from Student,SC,Course C,Teacher
where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname='叶平' and SC.score=(select max(score)from SC where C#=C.C# );

查询没学过“叶平”老师讲授的任一门课程的学生姓名

反向逻辑，先查出上过课的名单，再将其排除。

select Sname from Student where S# not in (select S# from Course,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and Tname='叶平');

第3步：分组聚合与统计计算

这是最核心的数据处理环节，涉及AVG、SUM、COUNT配合CASE WHEN的高级用法，常用于报表生成。

查询平均成绩大于60分的同学的学号和平均成绩

注意必须在group by后使用having子句进行聚合后的过滤。

select S#,avg(score)
from sc
group by S# having avg(score) >60;

按各科平均成绩从低到高和及格率的百分数从高到低顺序统计

利用CASE WHEN实现条件计数计算及格率百分比。

SELECT t.C# AS 课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0) AS 平均成绩
,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数
FROM SC T,Course
where t.C#=course.C#
GROUP BY t.C#
ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC

查询每门课程被选修的学生数

基本的分组计数示例。

select c#,count(S#) from sc group by C#;

统计每门课程的学生选修人数（超过10人的课程才统计）

结合排序与分组过滤。

select C# as 课程号,count(*) as 人数
from sc
group by C#
order by count(*) desc,c#

第4步：复杂透视与排名查询

将行数据转化为列显示，或获取指定排名的结果，是数据分析中的高阶需求。

按平均成绩从高到低显示“数据库”、“企业管理”等三门课成绩

使用相关子查询进行行转列处理，构造宽表格式。

SELECT S# as 学生ID
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 数据库
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企业管理
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英语
,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩
FROM SC AS t
GROUP BY S#
ORDER BY avg(t.score)

查询各分数段人数（如85-100, 70-85等）

再次展示CASE WHEN在区间统计中的应用。

SELECT SC.C# as 课程ID, Cname as 课程名称
,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85]
,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70]
,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60]
,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [不及格]
FROM SC,Course
where SC.C#=Course.C#
GROUP BY SC.C#,Cname;

查询学生平均成绩及其名次

在传统SQL中，名次通常通过嵌套子查询计算得出。

SELECT 1+(SELECT COUNT( distinct 平均成绩)
FROM (SELECT S#,AVG(score) AS 平均成绩
FROM SC
GROUP BY S#
) AS T1
WHERE 平均成绩 > T2.平均成绩) as 名次,
S# as 学生学号,平均成绩
FROM (SELECT S#,AVG(score) 平均成绩
FROM SC
GROUP BY S#
) AS T2
ORDER BY 平均成绩 desc;

查询每门课程前三名的记录

使用TOP关键字配合外部关联进行筛选。

SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM SC t1
WHERE score IN (SELECT TOP 3 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC
)
ORDER BY t1.C#;

第5步：修改与维护操作

除了读取数据，实际业务中也常涉及数据的批量更新与清理。

把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩

使用带有表连接限制的UPDATE语句，需谨慎操作建议先备份数据。

update SC set score=(select avg(SC_2.score)
from SC SC_2
where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平');

删除学习“叶平”老师课的SC表记录

同样需要多表联合删除，确认条件后再执行。

DELETE SC
from course ,Teacher
where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平';