SQL查询整理
SQL查询相关知识整理
习题中会用到的表
一、简单查询
1.查询姓“猴”的学生名单
select 学号，姓名
from student
where 姓名 like ‘猴%’；2、查询姓名中最后一个字是“猴”的学生名单
select 学号，姓名
from student
where 姓名 like ‘%猴’；3、查询姓名中带“猴”的学生名单
select 学号，姓名
from student
where 姓名 like ‘%猴%’；4、查询姓“孟”老师的个数
select count （教师号）
from teacher
where 教师姓名 like ‘孟%’；二、汇总分析
1、查询课程编号为“0002”的总成绩
select sum（成绩）
from score
group by 课程号
having 课程号=‘0002’；2、查询选了课程的学生人数
/*查询有多少人选修了课程，select 学号
成绩表里学号有重复值需要去掉
from 从课程表查询 score
/*
select sum（distinct 学号）as 学生人数
from score3、查询各科成绩最高和最低的分
select max（成绩）as 最高分 ，min（成绩）as最低分
from score
group by 课程号；4、查询每门课程被选修的学生数
select 课程号，count（学号）as学生人数
from score
group by 课程号；5、查询男生、女生人数
/*select 查询结果：性别，对应性别人数：汇总函数count
from 从哪张表查 student
where 查询条件：没有
group by 按性别分组
order by 没有排序
/*
select 性别，count（*）
from student
group by 性别；6、查询平均成绩大于60分学生的学号和平均成绩
select 学号，avg（成绩）
from score
group by 学号
having avg（成绩）>60;7、查询至少选修两门课程的学生学号
select 学号，count（课程号）as选修课程数
from score
group by 学号
having count（课程号）>=2；8、查询同名同姓学生名单并统计同名人数
/*翻译：
查询出姓名相同的学生有谁，每个姓名相同学生的人数
查询结果：姓名，人数
条件：怎么算姓名相同？按姓名分组后人数大于等于2，因为同名的人数大于等于2
*/
select 姓名 ，count（*）as 人数
from student
group by 姓名
having count（*）>=2;9、查询不及格的课程并按课程号从大到小排序
select 课程号
from score
where 成绩<60
order by 课程号 desc；10、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
select 课程号，avg（成绩）as平均成绩
from score
group by 课程号
order by 平均成绩 asc，课程号 desc；11、检索课程号为‘0004’且分数小于60的学生学号，结果按分数降序排序
select 学号
from score
where 课程号=“0004”and 分数 <60；
order by 成绩 desc；12、统计每门课程的学生选修人数（超过2人的课程才统计）
要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序
select 课程号，count（学号）as学生人数
from score
group by 课程号
having count（学号）>2
order by count（学号） desc，课程号 asc；13、查询两门以上不及格课程的同学的学号及其平均成绩
select 学号，avg（成绩），count（成绩）as不及格科目数
from score
where 成绩 <60
group by 学号
having count（成绩）>=2；三、复杂查询
1、（子查询）查询所有课程成绩小于60分学生的学号、姓名
select 学号，姓名
from student
where 学号 in（
select 学号
from score
group by 学号
having max（成绩）<60
）；2、查询没有学全所有课的学生的学号、姓名
select 学号，姓名
from score
where 学号 in（
select 学号
from score
group by 学号
having count（课程号）<(selece count(课程号）from course）
);3、查询出只选修了两门课程的全部学生的学号和姓名
select 学号，姓名
from score
where 学号 in （select 学号
from score
group by 学号
having count（课程号）=2
）；4、查询1990年出生的学生名单
select 学号
from student
where year（出生日期）=1990；5、按课程号分组取成绩最大值所在行的数据
select *
from score as a
where 成绩=（
select max（成绩）
from score as b
where a.课程号=b.课程号
）；6、按课程号分组去成绩最小值所在行的数据
select *
from score as a
where 成绩=（
select min（成绩）
from score as b
where a.课程号=b.课程号
）；7、查询各科成绩前两名的记录
(select *
from score
where 课程号 = ‘0001’
order by 成绩 desc
limit 2）
union all
（select * from score where 课程号 =‘0002’order by 成绩 desc limit 2）
union all
（select * from score where 课程号 = ‘0003’order by 成绩 desc limit 2）四、多表查询
1、查询所有学生的学号、姓名、选课数、总成绩
select a.学号，a.姓名，count（b.课程号）as选课数，sum（b.成绩）as 总成绩
from student as a inner join score as b
on a.学号= b.学号
group by a.学号；2、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select a.学号，a.姓名 ，avg（b.成绩）as平均成绩
from student as a left join score as b
on a. 学号= b.学号
group by a.学号
having avg（b.成绩）>85;3、查询学生的选课情况：学号，姓名，课程号，课程名称
select a.学号，a.姓名，c.课程号，c.课程名称
from student as a inner join score as b
on a.学号=b.学号
inner join course as c
on b.课程号=c.课程号；4、查询出每门课程的及格人数和不及格人数
select 课程号，
sum（case 成绩>=60 then 1
else 0
end）as及格人数
sum（case 成绩 <60 then 1
else
end）as 不及格人数
from score
group by 课程号；4、使用分段【100-85】、【85-70】、【70-60】、【<60】来统计各科成绩，分别统计：各分数段人数、课程号和课程名称
select a.课程号，b.课程名称，
sum（case when 成绩 between 100 and 85 then 1
else 0
end ）as'100-85人数'
sum (case when 成绩 <85 and 成绩>=70 then 1
else 0
end)as '85-70人数'
sum(case when 成绩<70 and 成绩>=60 then 1
else 0
end）as '70-60人数'
sum(case when 成绩<60 then 1
else 0
end) as '<60人数'
from score as a right join course as b
on a.课程号=b.课程号
group by a.课程号，b.课程名称；5、查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名
select a.姓名，a.学号
from student as a inner join score as b
on a.学号=b.学号
where b.课程号=‘0003’and b.成绩>80；
6、
这类题目属于行列互换，解题思路如下：
1）用常量列输出目标表的结构，可以看到查询结果已经和目标很接近
select 学号，‘课程号0001’，‘课程号0002’，‘课程号0003’
from score；
2）使用case表达式，替换常量列为对应成绩
select 学号，（
case 课程号 when‘0001’then 成绩 else 0 end）as ‘课程号0001’，
（case 课程号 when‘0002’then 成绩 else 0 end）as ‘课程号0002’，
（case 课程号 when‘0003’then 成绩 else 0 end）as ‘课程号0003’
from score；
这个查询结果，每一行表示了某个学生某一门课程的成绩。
3）分组，并使用最大值函数max取出最大值
select 学号，
max(case 课程号 when '0001'then 成绩 else 0 end)as '课程号0001',
max(case 课程号 when '0001'then 成绩 else 0 end)as '课程号0001',
max(case 课程号 when '0001'then 成绩 else 0 end)as '课程号0001'
from score
group by 学号;