sql 相加_SQL 多表查询之表的联结 case表达式

• 表的加法
• 表的联结和应用
• case表达式
• 总结和sqlzoo练习题

一、表的加法

两个表数据相加 union

表的加法会自动删除重复的行，如果想要保留重复行 使用union all

课程表1 和课程表2 相加

select 课程号,课程名称
from course
UNION
select 课程号,课程名称
from course1;

二、表的联结

• 交叉联结
• 内联结
• 左联结
• 右联结
• 全联结
• 联结的应用

1、交叉联结 cross join

交叉联结也叫笛卡尔积，将表中的每一行都与另一个表中的每一行合并在一起。结果行数是两个表行数的乘积，交叉联结是其他联结的基础。

2、内联结 inner join

内联结是查找出同时存在两张表中的数据

查找学号，姓名，课程号

Select a.学号,a.姓名,b.课程号
From student as a inner join score as b
On a.学号=b.学号

3、左联结 left join

左联结会将左侧的表中的数据全部取出来。

Select a.学号,a.姓名,b.课程号
From student as a left join score as b
On a.学号=b.学号

4、右联结 right join

右联结会将右侧的表中的数据全部取出来

Select a.学号,a.姓名,b.课程号
From student as a right join score as b
On a.学号=b.学号

当右联结需要去除重复部分 可以使用Where a.学号=Null;

Select a.学号,a.姓名,b.课程号
From student as a right join score as b
On a.学号=b.学号
Where a.学号=Null;

5、全联结 full join

全连接查询结果会返回所有的行，当有匹配进行合并。当没有匹配对应的行用空值来代替。

6、联结的应用

一般都使用内联结，当实际工作业务中想要生成固定行数的表单或者特别说明要哪一张表的全部数据的时候使用左右连接。

分析方法：翻译大白话，写出分析思路，对应写出SQL子句。

练习：查询所有学生的学号，姓名，选课数，总成绩？

拆解：

1）、学号，姓名——学生表（student）

2）、选课数 每个学生的选课数。成绩表score

成绩表score 按学号分组，对课程号计数count

3）、总成绩，成绩表score（每个学生的总成绩：成绩表score 按学号分组，成绩求和sum）

分析思路：

select查询结果：学号，选课数，总成绩

From表示从那张表中查找，涉及2个表，所以需要联结，考虑哪种联结，因为要查到每个学生的信息，所有要保留所有学号，所以使用左联结。

Group by分组：每个学生的选课数，需要对学号分组，对课程号汇总计数。总成绩需对按学号分组，对成绩求和。

Select a.学号,a.姓名,count(课程号) as 选课数,sum(成绩) as 总成绩
From student as a left join score as b
on a.学号=b.学号
group by a.学号

练习：查询平均成绩大于85分的所有学生的学号，姓名，平均成绩?

翻译问题：

学号，姓名（来自学生表student），平均成绩（来自成绩表score 每个学生的平均成绩）

按学号分组，求平均成绩 avg（成绩）

平均成绩 > 85分

Select 学号，姓名，平均成绩

From 学生表和成绩表（多表查询 用联结），因为要查所有学生，保留左边数据，使用左联结。On 通过学号产生联结

Group by 按学号分组

平均成绩是分组后得到的 给分组结果加上条件

Having 取出大于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

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

翻译大白话

学号，姓名（来自学生表），课程号，课程名称（课程表），学生表和课程表没办法产生关系，需要通过中间成绩表产生联结。

Select a.学生,b.姓名 ,c.课程号,c.课程名称
From student a inner join score b On a.学号=b.学号
Inner join course c on b.课程号=c.课程号

三、case表达式

使用case表达式可以帮忙解决复杂查询问题，相当于一个条件判断条件，判断每一行是否符合条件。如果符合就 运行then。如果不符合就进行下一个when子句，如果都没有，则运行else。

查询学号，课程号，成绩，与成绩是否及格

select 学号,课程号,成绩,
(case when 成绩>=60 then '及格'
 when 成绩<60 then '不及格'
 else NULL
end) as 是否及格
from score

案例：查找每门课程的及格人数和不及格人数

分析：把及格人数 符合设置为 1 ，不及格设置为0，sum求和就得到及格总人数。

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

注意点

Else 可以不写默认为空值

End 不可以省略不写

Case作为条件判断。

当有多种情况，需要条件判断，需要使用case表达式。

练习：使用分段 来统计各科人数，分别统计各分段人数，课程号，课程名称？

分段：[100-85] ,[85-70],[70-60],[<60]

Select a.课程号,b.课程名称,
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 a right join course as b
On a.课程号 = b.课程号
Group by a.课程号

四、总结和SQL练习题

学习了表的加法和联结，还有case表达式

1、加法 union 自动去重，不要去重复 union all

2、多种联结方式 都是以交叉联结为基础

在from中同时使用两张表，由于表名太长会影响sqL的可读性，所以使用as 对表取别名，方便使用。给学生表取别名a，给成绩表取别名b。使用列名时，为了方便区分这个列是哪个表的，会在列名前用（表名.列名）格式。列名前面都加了别名。From子句中用inner join将连个表联结起来，这里的inner join表示联结方式是内联结，选出同时存在两个表中的数据。From子句中关键词On 表示两个表是通过哪个列匹配产生关系的。On a.学号=b.学号，On 表示联结两个表的桥梁一样。

3、case 表达式 用于调节判断 case when else end 判断语句

SQL练习题目

其中8、11、12、13题目感觉比较难，需要回顾重做 复习

-- 1.

select matchid,player
from goal
where teamid = 'GER'


-- 2.

 SELECT id,stadium,team1,team2
 FROM game
where id='1012' 


-- 3.

 SELECT goal.player,goal.teamid,game.stadium,game.mdate
 FROM game right JOIN goal ON (id=matchid)
where goal.teamid = 'GER'
 
-- 4.

SELECT game.team1,game.team2,goal.player
 FROM game right JOIN goal ON (id=matchid)
where goal.player LIKE 'Mario%'
 
-- 5.

SELECT goal.player,goal.teamid,eteam.coach ,goal.gtime
 FROM goal JOIN eteam on (teamid=id)
 WHERE goal.gtime<=10
 
-- 6.

select game.mdate,eteam.teamname
from game JOIN eteam ON (team1=eteam.id)
where eteam.coach='Fernando Santos'
 
 
-- 7.

select goal.player
from game JOIN goal ON (id=matchid)
where game.stadium='National Stadium, Warsaw' 

-- 8

 SELECT distinct b.player 
from game as a inner join goal as b on a.id=b.matchid
WHERE (b.teamid=a.team1 and a.team2='GER') OR (b.teamid=a.team2 and a.team1='GER')
 
-- 9、
SELECT eteam.teamname,count(goal.player)
 FROM eteam JOIN goal ON (id=teamid)
 group BY eteam.teamname.
 
-- 10.
 
SELECT game.stadium,count(goal.player)
FROM game JOIN goal ON (id=matchid)
group BY game.stadium
 
-- 11.

select a.id,a.mdate, count(b.player)
from game as a inner join goal as b on a.id = b.matchid 
where (team1 = 'POL' or team2 = 'POL')
group by a.id,a.mdate
 
-- 12.
select game.id,game.mdate,count(goal.player)
from game join goal on (id = matchid)
where goal.teamid='GER'
group by game.id,game.mdate
 
--  13.
SELECT game.mdate,game.team1,
 sum(CASE WHEN goal.teamid=game.team1 THEN 1 ELSE 0 END)as score1,
game.team2,
 sum(CASE WHEN goal.teamid=game.team2 THEN 1 ELSE 0 END)as score2
 FROM game left JOIN goal ON (matchid = id)
group by game.id,game.mdate,game.team1,game.team2