查询各分数段人数设计视图_MySQL多表查询学习

SQL多表查询学习主要包括下列知识点：

• 表的加法
• 表的联结（包括：交叉联结、内联结、左联结、右联结、全联结）
• case when的表达式

• 内联结、左联结、右联结的示意文氏图：

联结练习：

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

STEP1：翻译问题成大白话

• 学号和姓名在student表中；
• 选课数：每个学生所选课的数量，可在score表中按学号分组用count计数课程号；
• 总成绩：每个学生的总成绩，在score表中按学号分组用sum计算成绩总和；

STEP2：分析思路

STEP3：写出对应sql子句

select x.学号,x.姓名,count(y.课程号) as '选课数' ,sum(y.成绩) as '总成绩'
from student as x left join score as y 
on x.学号=y.学号
group by x.学号

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

STEP1：翻译问题成大白话

查询所有学生的学号、姓名和平均成绩，且平均成绩是大于85的。

STEP2：分析思路

STEP3：写出对应sql子句

select x.学号,x.姓名,avg(y.成绩) as '平均成绩'
from student as x left join score as y 
on x.学号=y.学号
group by x.学号
having avg(y.成绩) >85

3、查询学生的选课情况：学号，姓名，课程号，课程名称

STEP1：翻译问题成大白话

查询每个学生的学号、姓名、所选课程的课程号和课程名称。

STEP2：分析思路

STEP3：写出对应sql子句

select x.学号,x.姓名,y.课程号,z.课程名称
from student as x inner join score as y on x.学号=y.学号
inner join course as z on y.课程号=z.课程号

4、查询出每门课程的及格人数和不及格人数

STEP1：翻译问题成大白话

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

STEP2：分析思路

及格人数=sum(case when 成绩>=60 then 1 else 0 end) as '及格人数'

不及格人数=sum(case when 成绩<60 then 1 else 0 end) as'不及格人数'

STEP3：写出对应sql子句

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 课程号

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

STEP1：翻译问题成大白话

查询每个课程号及课程名称的各分段人数

STEP2：分析思路

各分数段人数：

sum( case when 成绩 bet2een 85 and 100 then 1 else 0 end) as '[100-85]',

sum( case when 成绩>= 70 and 成绩<85 then 1 else 0 end) as '[85-70]',

sum( case when 成绩 >= 60 and 成绩<70 then 1 else 0 end) as '[70-60]',

sum( case when 成绩 <60 then 1 else 0 end) as '[<60]',

STEP3：写出对应sql子句

select x.课程号, y.课程名称,
sum(case when 成绩 between 85 and 100 then 1 else 0 end) as '[100-85]', 
sum(case when 成绩>= 70 and 成绩<85 then 1 else 0 end) as '[85-70]', 
sum(case when 成绩 >= 60 and 成绩<70 then 1 else 0 end) as '[70-60]', 
sum(case when 成绩 <60 then 1 else 0 end) as '[<60]'
from score as x RIGHT JOIN course as y 
on x.课程号=y.课程号
group by x.课程号,y.课程名称

sqlzoo多表查询练习：