鸽主姓名查询成绩_从零学会SQL:多表查询

学习纲要

• 表的加法
• 表的联结
• 联结应用案例
• case表达式

一、表的加法

加法语句：union、union all

（一）应用举例

-- union查询
select 课程号,课程名称
from course
union 
select 课程号,课程名称
from course1;

-- union all查询
select 课程号,课程名称
from course
union all
select 课程号,课程名称
from course1;

二、表的联结

交叉联结（cross join）、内联结（inner join）、左联结（left join）、右联结（right join）、全联结（full join）

一、应用举例

-- 内联结
select a.学号,a.姓名,b.课程号
from student1 as a inner join score1 as b on a.学号=b.学号;

-- 左联结
select a.学号,a.姓名,b.课程号
from student1 as a left join score1 as b on a.学号=b.学号;

-- 完全左联结
select a.学号,a.姓名,b.课程号
from student1 as a left join score1 as b on a.学号=b.学号
where b.学号=Null;

-- 右联结
select a.学号,a.姓名,b.课程号
from student1 as a right join score1 as b on a.学号=b.学号;

-- 完全右联结
select a.学号,a.姓名,b.课程号
from student1 as a right join score1 as b on a.学号=b.学号
where b.学号=Null;

三、联结应用案例

（一）练习

1.查询所有学生的学号、姓名、选课数、总成绩

-- 查询所有学生的学号、姓名、选课数、总成绩
select a.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩
from student as a left join score as b on a.学号=b.学号
group by a.学号;

2.查询平均成绩大于85的所有学生的学号、姓名和平均成绩

-- 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select a.学号,a.姓名,avg(b.成绩) as 平均成绩
from student as a left join score as b on a.学号=b.学号
group by a.学号
having 平均成绩 > 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.课程号;

四、case表达式

（一）练习

1.查询出每门课程的及格人数和不及格人数

-- 查询出每门课程的及格人数和不及格人数
select 课程号,
sum(case when 成绩>=60 then 1 else 0 end) as 及格人数,
sum(case when 成绩<60 then 1 else 0 end) as 不及格人数
from score
group by 课程号;

2.使用分段100-85，85-70，70-60，<60来统计各科成绩，分别统计各分段人数：课程号和课程名称

/*使用分段100-85，85-70，70-60，<60来统计各科成绩，
分别统计各分段人数：课程号和课程名称*/
select a.课程号,b.课程名称,
sum(case when 成绩 between 85 and 100 then 1 else 0 end) as '[85-100]',
sum(case when 成绩>=70 AND 成绩<85 then 1 else 0 end) as '[70-85]',
sum(case when 成绩>=60 AND 成绩<70 then 1 else 0 end) as '[60-70]',
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.课程号;

总结

sqlzoo练习