sql 两条数据空值合并_SQL学习系列：多表查询

最新推荐文章于 2023-10-06 16:07:50 发布

weixin_39718173

最新推荐文章于 2023-10-06 16:07:50 发布

阅读量1k

点赞数

文章标签： sql 两条数据空值合并 sql三表关联查询 sql三表查询 sql两张表纵向合并 sql怎么判断sum sql纵向合并两张表

1）表的加法

1.1表的加法 union

select 课程号,课程名称
from course
union
select 课程号,课程名称
from course1
;
-- union的为表的加法，将两张表进行合并，可以理解为文视图中的A and B 关系

1.2 保留重复行 union all

select 课程号,课程名称
from course
union all
select 课程号,课程名称
from course1
;
-- union all左右为合并表并且保留重复行，将所有的重复的行显示出来。合并前的表和合并后的表没有直接关系。

1.3 如何复制表

复制表

重命名表

打开表后就可以快速修改

2) 表的联结

表的联结练习使用表数据情况：student和score表

2.1 交叉联结（笛卡尔联结）cross join

表中的每一行都与另一张表的每一行合并在一起。在实际运用中比较少，结果行数太多需要花费大量的运算成本和设备的支持，交叉结果行数太多没有实际的价值。这是为了更好理解后面的联结。

2.2 内联结 inner join

同时查找出同时存在于两张表的数据，然后通过交叉联结显示。

理解内联结

select a.学号,a.姓名,b.课程号
from student as a inner join score as b -- 此处为防止表明太长影响使用，使用as 为两个表增加别名
on a.学号= b.学号 -- 表示两个表通过哪一列产生匹配关系，此处通过“学号”产生匹配关系
;
select student.学号,student.姓名,score.课程号
from student inner join score
on student.学号=score.学号
; -- 非使用别名，便于熟悉inner join的使用，结果与上面相同。

2.3 左联结 left join

左连接两个表通过某一列产生匹配关系，会将左侧表中的数据全部取出来，右侧表只取出与左侧表相同列匹配的数据，然后通过交叉连接显示。

理解左联结

select a.学号,a.姓名,b.课程号
from student as a left join score as b
on a.学号= b.学号
;
-- 因为学号0004在score表中没有对应的行，所以显示的课程号为空值

如何能够做到下图的情况呢？其实，从文氏图可以看出，红色区域为左联结的基础上去掉了公共区域。

select a.学号,a.姓名,b.课程号
from student as a left join score as b
on a.学号= b.学号
where b.学号 is null
;
-- 这里需要说明is null和 = null 之间的区别，null 表示什么也不是，不能=、>、< … 所有的判断，结果都是false，所有只能用 is null进行判断。

2.4 右联结 right join

右联结与左联结相似，连接两个表通过某一列产生匹配关系，会将右侧表中的数据全部取出来，左侧表只取出与右侧表相同列匹配的数据，然后通过交叉连接显示。

理解右连接

select a.学号,a.姓名,b.课程号
from student as a right join score as b
on a.学号= b.学号
;

练习能够做到下图的情况，与刚才使用的左联结一样，使用null进行处理。

select a.学号,a.姓名,b.课程号
from student as a left join score as b
on a.学号= b.学号
where a.学号 is null
;
-- 因为成绩表中已经没有独有的行，所以显示结果为空值

2.5 全联结 full join

全联结查询结果会返回左表和右表中的所有行，当某行和另一个表中有匹配的时候，两个行进行合并；如果某行和另一个表中没有匹配的时候，另一个表中使用空值进行匹配。另一个表中使用空值进行匹配，由于MYSQL并不支持全联结，此处只做理解。在某些数据库中， FULL JOIN 称为 FULL OUTER JOIN。

理解全联结

2.6 总结

当实际业务中，想要生成固定行数的表单，或者特别说明要哪一张表里全部数据的时候，使用左联结或右联结，其余情况使用内联结获取两个表的公共部分。

非MySQL JOIN理解

3）联结应用案例

3.1 如果使用SQL解决业务问题

将问题转化为具体的要求
写出分析思路
写出对应SQL语句

3.2 应用案例

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

1.将问题转化为具体的要求

学号、姓名来自表student；

选课数为每个学生的所选课程号计数(count)，课程号来自成绩表score；

总成绩由学生所有成绩求和(sum)，成绩来自成绩表score；

2.写出分析思路

select 查询结果 (学号,姓名,选课数,总成绩)

from 从哪张表中查找数据(student,score) 两张表如何连接(通过学号连接) 用哪种联结(左联结)

where 查询条件(此问题不需要)

group by 分组(每个学生的选课数目：按学生学号分组，使用count对课程号计数；每个学生的总成绩：按学生学号分组，使用sum对成绩求和)

having 对分组结果制定条件(此问题不需要)

order by 对查询结果排序(此问题不需要)

limit 从查询结果中取出指定行(此问题不需要)

3.写出对应SQL语句

select 学号,姓名,count(课程号) as 选课数,sum(成绩) as 总成绩
from student as a left join score as b
on a.学号 = b.学号
group by 学号
;
-- 这里会报一个错误：[Err] 1052 - Column '学号' in field list is ambiguous，这是由于select 学号的时候没有指定是student表还是score表，导致指代不明;对于两张表有相同的列名时需要注意

正确的代码

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

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

1.将问题转化为具体的要求

学号、姓名来自表student；

平均成绩为每个学生的所有成绩的平均数(avg)，成绩来自成绩表score，按学号分组；

平均成绩要大于85；

2.写出分析思路

select 查询结果 (学号,姓名,平均成绩)

from 从哪张表中查找数据(student,score) 两张表如何连接(通过学号连接) 用哪种联结(左联结)

where 查询条件(此问题不需要)

group by 分组(每个学生的平均成绩：按学生学号分组，使用avg对成绩求平均数；)

having 对分组结果制定条件(avg(成绩)>85)

order by 对查询结果排序(此问题不需要)

limit 从查询结果中取出指定行(此问题不需要)

3.写出对应SQL语句

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

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

此处涉及三表(student、score、course)之间的联结。student表和score表通过学号联结，score表和course表通过课程号进行联结，从而得到三表联结

select a.学号,姓名,b.课程号,c.课程名称 -- 课程名称这一列名因为course表中独有列名，因此可以直接使用列名，为了代码的完整性，故写为'c.课程名称'
from student as a inner join score as b
on a.学号 = b.学号
inner join course as c
on b.课程号 = c.课程号
;

4）case表达式

4.1 什么是case表达式

使用case表达式能帮我们解决复杂问题，case表达式就相当于是一个条件判断函数(和excel中if函数类似)。

case when <判断表达式> then <表达式>
when <判断表达式> then <表达式>
when <判断表达式> then <表达式>
...
else <表达式>
end

判断成绩属于及格还是不及格

select 学号,课程号,成绩,
(case when 成绩 >=60 then '及格'
when 成绩 <60 then '不及格'
else null -- 此处else可以省略，因为只有两种条件，最好保持完整性都写上
end) as 是否及格
from score
；

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

select 课程号,及格人数,不及格人数,
count(case when 成绩 >=60 then '1'
else null
end) as 及格人数
count(case when 成绩 <60 then '1'
else null
end) as 不及格人数
from score
group by 课程号
;
-- 这里出现多处错误1.计算及格人数不及格人数应该使用sum来计数 2.对于计数作用可以不用加引号，加了为字符的含义，else后不应使用null，使用0作为不计数 3.select后面直接使用函数，不需要写及格人数和不及格人数，会报错识别不了列名 4.select后面的列名需要用逗号隔开

正确代码

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.2 case 表达式注意事项

1.else 子句可以省略不写，默认为 else null，为了保证完成尽量都写齐

2.最后结尾的end不能省略不写

3.case表达式可以放在SQL任何位置

4.3 练习

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

select 课程号,课程名称
sum(case when 成绩 between 85 and 100
then 1
else 0
end) as [100-85]分段人数,
sum(case when 成绩 between 70 and 85
then 1
else 0
end) as [85-70]分段人数,
sum(case when 成绩 between 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.课程名称
;
-- 1.由于范围问题，连续使用between会造成重复计数，需要修改条件 2.select中，课程名称后面漏写逗号，最后一个case语句中多写了一个逗号 3.as后面需要加引号

正确代码

select 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.课程号,b.课程名称
;
-- group by子句里，使用多个列进行分组时，这几个列的值全部相同才算同一组，这里是一对一关系，如果是多对多的情况，多个列则不能省略

5）SQLZOO练习SELECT in SELECT

1.第一個例子列出球員姓氏為'Bender'的入球數據。 * 表示列出表格的全部欄位，簡化了寫matchid, teamid, player, gtime語句。修改此SQL以列出賽事編號matchid 和球員名 player ,該球員代表德國隊Germany入球的。要找出德國隊球員，要檢查:teamid = 'GER'

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

2.由以上查詢，你可見Lars Bender's 於賽事 1012入球。.現在我們想知道此賽事的對賽隊伍是哪一隊。留意在 goal 表格中的欄位 matchid ，是對應表格game的欄位id。我們可以在表格 game中找出賽事1012的資料。只顯示賽事1012的 id, stadium, team1, team2

SELECT id,stadium,team1,team2
FROM game inner join goal
on game.id = goal.matchid
where matchid = '1012'
group by id
;

3.我們可以利用JOIN來同時進行以上兩個步驟。

SELECT *
  FROM game JOIN goal ON (id=matchid)

語句FROM 表示合拼兩個表格game 和 goal的數據。語句 ON 表示如何找出 game中每一列應該配對goal中的哪一列 -- goal的 id 必須配對game的 matchid 。簡單來說，就是 ON (game.id=goal.matchid)以下SQL列出每個入球的球員(來自goal表格)和場館名(來自game表格)修改它來顯示每一個德國入球的球員名，隊伍名，場館和日期。

SELECT player, teamid, stadium, mdate
FROM game JOIN goal
ON (id=matchid) -- 当两表列名不同时，可以直接写列名，但是最好不省略
WHERE teamid LIKE 'GER'
;

4.使用上題相同的 JOIN語句，列出球員名字叫Mario (player LIKE 'Mario%')有入球的隊伍1 team1, 隊伍2 team2 和球員名 player

SELECT team1, team2, player
FROM game JOIN goal
ON (game.id=goal.matchid)
WHERE player LIKE 'Mario%'
;

5.表格eteam 貯存了每一國家隊的資料，包括教練。你可以使用語句 goal JOIN eteam on teamid=id來合拼 JOIN 表格goal 到表格eteam。列出每場球賽中首10分鐘gtime<=10有入球的球員 player, 隊伍teamid, 教練coach, 入球時間gtime

SELECT player, teamid, coach, gtime
FROM eteam JOIN goal
ON (eteam.id = goal.teamid)
WHERE gtime < 10
;

6.要合拼JOIN 表格game 和表格 eteam，你可以使用game JOIN eteam ON (team1=eteam.id)或game JOIN eteam ON (team2=eteam.id)注意欄位id同時是表格game 和表格 eteam的欄位，你要清楚指出eteam.id而不是只用id。列出'Fernando Santos'作為隊伍1 team1 的教練的賽事日期，和隊伍名。

SELECT mdate, teamname
FROM eteam JOIN game
ON (eteam.id = game.team1)
WHERE coach = 'Fernando Santos'
;

7.列出場館 'National Stadium, Warsaw'的入球球員。

SELECT player
FROM game JOIN goal
ON (game.id= goal.matchid)
WHERE stadium in ('National Stadium, Warsaw')
;

8.以下例子找出德國-希臘Germany-Greece 的八強賽事的入球。修改它，只列出全部賽事，射入德國龍門的球員名字。

SELECT distinct(player)
FROM game JOIN goal
ON matchid = id
WHERE teamid!='GER'
;
-- 只考虑到非德国球员但是还需要要考虑是和德国队比赛的条件
SELECT distinct(player)
FROM game JOIN goal
ON matchid = id
WHERE teamid!='GER' and (team1 = 'GER' OR team2 = 'GER')
;

9.列出隊伍名稱 teamname 和該隊入球總數

SELECT teamname, count(*)
FROM eteam
JOIN goal ON id=teamid
GROUP BY teamname
;

10.列出場館名和在該場館的入球數字。

SELECT stadium, count(*)
FROM game
JOIN goal ON id=matchid
GROUP BY stadium
;

11.每一場波蘭'POL'有參與的賽事中，列出賽事編號 matchid, 日期date 和入球數字。

SELECT matchid,mdate,count(*)
FROM game JOIN goal ON matchid = id
WHERE (team1 = 'POL' OR team2 = 'POL')
group by matchid
;

12.每一場德國'GER'有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。

SELECT matchid,mdate,count(*)
FROM game JOIN goal ON matchid = id
WHERE teamid = 'GER'
group by matchid
;

13.List every match with the goals scored by each team as shown. This will use "CASE WHEN" which has not been explained in any previous exercises.

Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1. Sort your result by mdate, matchid, team1 and team2.

SELECT mdate,team1,
sum(CASE WHEN teamid=team1 THEN 1
ELSE 0
END) as score1,team2,
sum(CASE WHEN teamid=team2 THEN 1
ELSE 0
END) as score2
FROM game left JOIN goal
ON matchid = id
group by mdate,matchid,team1,team2
-- 这里需要使用left join