case when then else_（五）SQL知识点--UNION、各类JOIN、CASE

最新推荐文章于 2020-12-22 14:13:10 发布

weixin_39662578

最新推荐文章于 2020-12-22 14:13:10 发布

阅读量182

点赞数

1. 组合查询 UNION

UNION 联合，将多条查询语句的结果合并成一个结果，可以理解为是将多表结果加在一起。

语法：

查询语句1
UNION/UNION ALL
查询语句2
...

应用场景：

要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时。

特点:

要求多条查询语句的查询列数保持一致；
要求多条查询语句查询的每一列的类型和顺序最好一致（不一致也不会报错，但内容就乱了）；
union 关键字默认去重，如果使用union all可以包含重复项。

2. 多表联结 JOIN

联结是指，将多个存在相关关系的数据表，通过指定的条件（字段之间的比较运算），联结成一个数据表的过程。

【分类】

CROSS JOIN --交叉联结
INNER JOIN --内联结
LEFT JOIN --左联结
RIGHT JOIN --右联结
FULL JOIN --全联结

注意：多表联结，每个表名建议起别名。

2.1 交叉联结 CROSS JOIN

又称笛卡尔积，是将每个表的所有行都联结在一起，联结表的行数=表1的行数 * 表2的行数。

为了便于理解，我列出2个简单数据表来表示联结关系。

2.2 内联结 INNER JOIN

内联结就是等值联结，只保留多个表里的交集数据。

案例：

2.3 左联结 LEFT JOIN

左联结是在多个表数据联结时保留下左边表的所有数据，和右边表满足关系条件的数据。

常见关系条件是相等关系(=)

关系条件也可以是不等值关系(<,>,<=,>=,<>）

只取左表仅有的数据

案例：

当多表联结的联结类型变化了一个，结果也不同：

2.4 右联结 RIGHT JOIN

右联结是在多个表数据联结时保留下右边表的所有数据，和左边表满足关系条件的数据。

案例：

2.5 全联结 FULL JOIN

全联结查询结果是保留多个表里的所有行，满足联结关系条件的数据行合并只保留一次，待联结的表在其他表里匹配不到满足关系条件的数据时其他表里用null填充。

（but，Mysql不支持全联结~~~）

3. JOIN语句练习

4. 判断函数--CASE

应用场景：自定义分组、统计有筛选条件的数量等

语法一：

CASE 变量或要判断的字段或表达式
WHEN 常量1 THEN 要显示的值1（或语句1;）
WHEN 常量2 THEN 要显示的值2（或语句2;）
....
ELSE 要显示的值n（或语句n;）
END

当case语句放在select语句中作为表达式来使用时，此时case结构中就不能含有语句部分。

语法二：

CASE WHEN 条件1 THEN 要显示的值1（或语句1;）
     WHEN 条件1 THEN 要显示的值2（或语句2;）
     ....
     ELSE 要显示的值n（或语句n;）
     END

语法一和语法二的区别在case后是否有内容，语法一是等值判断，when后省略等于号；语法二是区间判断。
ELSE 可以省略，对应的结果会自动填充为NULL。

案例：

5. sqlzoo练习

表信息如下：

赛事表game: 
         id(编号),mdate(日期),stadium(场馆),team1(队伍1编号),team2(队伍2编号)
入球表goal:
          matchid(赛事编号),teamid(队伍编号),player(入球球员),gtime(入球时间)
欧洲队伍表eteam:
         id(编号),teamname(队名),coach(教练)

题目如下：

#1.列出 賽事編號matchid 和球員名 player ,該球員代表德國隊Germany入球的。
SELECT matchid,player
FROM goal
WHERE teamid='GER';


#2.由以上查詢，你可見Lars Bender's 於賽事 1012入球。.現在我們想知道此賽事的對賽隊伍是哪一隊。只顯示賽事1012的 id, stadium, team1, team2
SELECT id,stadium,team1,team2
FROM game
WHERE id='1012';


#3.顯示每一個德國入球的球員名，隊伍名，場館和日期。
SELECT player,teamid,stadium,mdate
FROM goal AS a 
     INNER JOIN game AS b ON a.matchid=b.id
WHERE a.teamid='GER;


#4.列出球員名字叫Mario (player LIKE 'Mario%')有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player。
SELECT team1,team2,player
FROM game AS a
     INNER JOIN goal AS b ON a.id=b.matchid
WHERE player LIKE'Mario%';


#5.列出每場球賽中首10分鐘gtime<=10有入球的球員 player, 隊伍teamid, 教練coach, 入球時間gtime
SELECT player,teamid,coach,gtime
FROM goal AS a 
     INNER JOIN eteam AS b ON a.teamid=b.id
WHERE gtime<10;


#6.列出'Fernando Santos'作為隊伍1 team1 的教練的賽事日期，和隊伍名。
SELECT mdate,teamname
FROM eteam AS a
     INNER JOIN game AS b ON a.id=b.team1
WHERE coach='Fernando Santos';


#7.列出場館 'National Stadium, Warsaw'的入球球員。（！注意名字Warsaw前和逗号之前有空格）
SELECT b.player
FROM game AS a
     INNER JOIN goal AS b ON a.id=b.matchid
WHERE a.stadium ='National Stadium, Warsaw';


#8.列出全部賽事，射入德國龍門的球員名字。
SELECT DISTINCT player
FROM game AS a 
     INNER JOIN goal AS b ON a.id=b.matchid
WHERE (team1='GER'
   OR team2='GER')
AND teamid!='GER';


#9.列出隊e伍名稱 teamname 和該隊入球總數
SELECT teamname,COUNT(player)
FROM eteam AS a 
     INNER JOIN goal AS b ON a.id=b.teamid
GROUP BY teamname;


#10.列出場館名和在該場館的入球數字。
SELECT stadium,COUNT(player)
FROM game AS a 
     INNER JOIN goal AS b ON a.id=b.matchid
GROUP BY stadium;


#11.每一場波蘭'POL'有參與的賽事中，列出賽事編號 matchid, 日期date 和入球數字。
SELECT matchid,mdate,COUNT(teamid)
FROM game AS a
     INNER JOIN goal AS b ON a.id=b.matchid
WHERE team1='POL' 
   OR team2='POL'
GROUP BY matchid,mdate;


#12.每一場德國'GER'有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。
SELECT matchid,mdate,COUNT(teamid)
FROM game AS a
     INNER JOIN goal AS b ON a.id=b.matchid
WHERE (team1='GER' 
        OR team2='GER')
  AND teamid='GER'
GROUP BY matchid,mdate;


#13. 查出所有比赛日期,每场比赛双方各自的进球数。
SELECT a.mdate,
       a.team1,
       SUM(CASE WHEN b.teamid=a.team1 THEN 1 ELSE 0 END)AS score1,
       a.team2,
       SUM(CASE WHEN b.teamid=a.team2 THEN 1 ELSE 0 END)AS score2
FROM game AS a
     LEFT JOIN goal AS b
       ON a.id=b.matchid
GROUP BY a.mdate,a.team1,a.team2
ORDER BY a.mdate,a.id,a.tea