sql 多表多行模糊查询_中级数据分析5：SQL多表查询

一.知识点总结

/* --------------- 第5关：多表查询----------------------*/


/* ------------- 1.表的加法union-----------------*/
select 课程号,课程名称
from course
union 
select 课程号,课程名称
from course1;

-- 如果想保留重复行（即重复课程）union all 
select 课程号,课程名称
from course
union all 
select 课程号,课程名称
from course1;

-- ------------------------------------------------------------

/*--------------2.表的联结join-------------------------*/
/* 交叉联结（cross join）;内联结（inner join）;左联结（left join）;
    右联结（right join）;全联结（full join）*/

-- (2.1)交叉联结：表1中的第一行分别与表2中的每一行联结；表1中的其他行的操作与第一行相同。

-- (2.2)内联结：取出两个表中同时存在的行，将两个表中取出的行进行交叉联结
select a.学号,a.姓名,b.课程号
from student as a inner join score as b
on a.学号=b.学号;   -- 通过学号进行联结

-- (2.3)左联结：将左表作为主表，将左表的行全都取出来，再与右表进行交叉联结
select a.学号,a.姓名,b.课程号
from student as a left join score as b
on a.学号=b.学号;  
-- 若是想取出联结表中左表特有的行
select a.学号,a.姓名,b.课程号
from student as a left join score as b
on a.学号=b.学号 
where b.学号 is null; 

-- (2.4)右联结：将右表作为主表，将右表的行全都取出来，再与左表进行交叉联结
select a.学号,a.姓名,b.课程号
from student as a right join score as b
on a.学号=b.学号;  
-- 若是想取出联结表中右表特有的行
select a.学号,a.姓名,b.课程号
from student as a right join score as b
on a.学号=b.学号 
where b.学号 is null; 

-- (2.5)全联结：返回左右表的所有联结行，当一个表中某一行有数据，
                而另一个表中对应行没有数据时用空值填满。
-- MySQL不支持全联结语句，这里理解即可。

-- -------------------------------------------------------------

/*--------------3.联结应用案例-------------------------*/
-- (3.1)查询所有学生的学号，姓名，选课数，总成绩
-- 思路：student表（所有学生的学号，姓名）；score表（count(课程号),sum(成绩) ）
select a.学号,a.姓名, count(b.课程号) as 课程数,sum(b.成绩) as 总成绩
from student as a left join score as b 
on a.学号=b.学号 
group by a.学号;

-- (3.2)查询平均成绩大于85的所有学生的学号，姓名，和平均成绩
-- 思路：student表（所有学生的学号，姓名）；score表（avg(成绩)）
select a.学号,a.姓名, avg(b.成绩) as 平均成绩
from student as a left join score as b 
on a.学号=b.学号 
group by a.学号    -- 由于是左联结，所以按照a.学号进行分组，而不是b.学号 
having avg(b.成绩)>85;

-- (3.3)查询学生的选课情况：学号，姓名，课程号，课程名称
-- 思路：student表（学号，姓名）；course表（课程号，课程名称），
--       通过score表将student表与course表联结
select a.学号,a.姓名, c.课程号, c.课程名称
from student as a left join score as b on a.学号=b.学号    -- 一个join对应一个on
left join course as c on b.课程号=c.课程号;   

-- -------------------------------------------------------------

/*--------------4.case表达式-------------------------*/
/* case表达式中，else可以省略
case when 判断表达式 then 表达式
     when 判断表达式 then 表达式 
     when 判断表达式 then 表达式  
     ... 
     else 表达式
end 
*/
-- (4.1)注意：成绩后面有逗号，否则会报错;case表达式可以放在任意子句中，如放在select子句中。
select 学号,课程号,成绩, 
(case when 成绩>=60 then '及格'
     when 成绩<60 then '不及格'
     else null 
end) as 是否及格
from score;

-- (4.2)查询出每门课程的及格人数和不及格人数
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 课程号;

-- (4.3)使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，
-- 分别统计各分数段人数，课程号，课程名称。
select a.课程号,b.课程名称,
sum(case when 成绩>=85 then 1
    else 0  
    end) as '[100-85)',    -- 注意，这里的[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 a right join course as b 
on a.课程号=b.课程号
group by a.课程号,b.课程名称;

运行结果：

总结：

二.练习题

多表查询的练习题：The JOIN operation/zh

答案：第5关《从零学会SQL：多表查询》练习题答案

其中：

/* 8.查找射入德国球门的球员姓名 */
/* 
where (a.team1=b.teamid and b.teamid<> 'GER' and team2='GER')
   or (a.team2=b.teamid and b.teamid<> 'GER'and team1='GER');
这句也可以，但是太啰嗦了，team2是GER时，team1肯定就不是GER了，不用特别说明。
*/
select distinct b.player
from game as a inner join goal as b on a.id=b.matchid
where (a.team1=b.teamid and team2='GER') or (a.team2=b.teamid and team1='GER');

/*11. 查找出有波兰球队'POL'参加的比赛编号，比赛日期，对应这场比赛的进球数*/
select b.matchid,a.mdate,count(b.matchid)
from game as a inner join goal as b on a.id=b.matchid
where team1='POL' or  team2='POL'
group by b.matchid,a.mdate;  -- 注意，这里需要把b.matchid,a.mdate都写上

/*
13.查找出所有比赛的日期，每场比赛中对战双方各自的进球数（也就是team1进球数，team2进球数）
*/
select a.mdate,a.team1,sum(case when a.team1=b.teamid then 1 else 0 end) as score1,
a.team2,sum(case when a.team2=b.teamid then 1 else 0 end) as score2
from game as a left join goal as b on a.id=b.matchid
group by b.matchid,a.mdate,a.team1,a.team2
order by a.mdate,b.matchid,a.team1,a.team2;