sqlite 表与表之间的关系_sql多表查询：表与表之间的故事

最新推荐文章于 2022-05-14 00:46:12 发布

weixin_39609071

最新推荐文章于 2022-05-14 00:46:12 发布

阅读量678

点赞数

文章标签： sqlite 表与表之间的关系

本文链接：https://blog.csdn.net/weixin_39609071/article/details/111383238

版权

本文通过实例介绍了SQLite中不同类型的表联结操作，包括INNER JOIN、LEFT JOIN、RIGHT JOIN以及UNION等，结合SQLZOO的练习题目详细解释了每个查询的逻辑和实现方式，帮助读者掌握表与表之间关系的理解和应用。

摘要由CSDN通过智能技术生成

日常遇到的数据表总有很多张。和人类社会一样，表与表之间也不是孤立存在的。没有表是一座孤岛。

在sql世界中，表与表的关系就是join，中文译联结。举个例子，有2个表相恋了，左边的叫t1,右边的叫t2。看t1和t2的交集，就用inner join；看t1和交集部分，就用left join；看t1-交集，用left join且where t2=null；看t2和交集，用right join;看t2-交集，用right join 且where t1=null；t1和t2结婚且交集合二为一，用union；t1和t2作为两个独立个体结婚，用union all。

1.sql知识总结

例行回顾闯关获得的装备。

2.逛动物园

练习题目来自The JOIN operation。sqlzoo这一关的问题稍微复杂，每一个都需要拆解。但问题越复杂，解决后的成就感就更upupup.

题干使用的3个表及表间键的关系：

1.Modify it to show thematchidandplayername for all goals scored by Germany. To identify German players, check for:teamid = 'GER'

解析：

德国队、matchid和player都在表goal中
条件：德国队：where teamid =‘ger’

sql：

select matchid,player

from goal

where teamid='ger';

2.Show id, stadium, team1, team2 for just game 1012

解析：

id, stadium, team1, team2都在game表

game 1012：where id=1012

sql:

select id, stadium, team1, team2

from game

where id=1012;

3.Modify it to show the player, teamid, stadium and mdate for every German goal.

解析：

player, teamid 在goal表中
stadium，mdate在game表中
goal表as t1和game表as t2 inner join，on t1.matchid=t2. id
German goal：where teamid='ger'

sql:

select t1.player, t1.teamid, t2.stadium, t2.mdate

from goal as t1 and game as t2 on t1.matchid =t2.id

where t1.teamid='ger';

4.Show the team1, team2 and player for every goal scored by a player called Mario

解析：

scored by a player called Mario：where player like 'Mario%'
team1, team2 在game表中
player在goal表中
game表和goal表inner join，on id=matchid

sql：

select t1.team1, t1.team2,t2.player

from game as t1 inner join goal as t2

on t1.id=t2.matchid

where t2.player like 'Mario%';

5.showplayer,teamid,coach,gtimefor all goals scored in the first 10 minutes

解析：

player,teamid,gtime在goal表中
coach在eteam表中
inner join
all goals scored in the first 10 minutes：gtime<=10

sql:

select t1.player, t1. teamid, t2.coach, t1.gtime

from goal as t1 inner join eteam as t2

on t1.teamid =t2.id

where gtime<=10;

6.List the dates of the matches and the name of the team in which 'Fernando Santos' was the team1 coach.

解析：

which 'Fernando Santos' was the team1 coach：coach= 'Fernando Santos' and team1=id
team1在game表
dates of the matches :mdate 在game表
the name of the team：teamname 在eteam 表
inner join on team1=id

sql：

select t1.mdate,t2.teamname

from game as t1 inner join eteam as t2

on t1.team1=t2.id

where t2.coach= 'Fernando Santos'

7.List the player for every goal scored in a game where the stadium was 'National Stadium, Warsaw'

解析：