SQL: 连接

笛卡尔积

笛卡尔乘积是一个数学运算.假设我有两个集合X和Y,那么X和Y的笛卡尔积就是X和Y的所有可能组合,也就是第一个对象来自于X,第二个对象来自于Y的所有可能.

SELECT * FROM player;

SELECT * FROM team;


SELECT * FROM player, team;

MySQL数据库图标
image-2909

等值连接

两张表的等值连接就是用两张表中都存在的列进行连接。

SELECT player_id, player.team_id, player_name, height, team_name FROM player, team WHERE player.team_id = team.team_id;

SELECT player_id, a.team_id, player_name, height, team_name FROM player AS a, team AS b WHERE a.team_id = b.team_id;

非等值连接

当我们进行多表查询的时候,如果连接多个表的条件是等号时,就是等值连接,其他的运算符连接就是非等值查询。

SELECT p.player_name, p.height, h.height_level FROM player AS p, height_grades AS h WHERE p.height BETWEEN h.height_lowest AND h.height_highest;

外连接

左连接

SELECT * FROM player LEFT JOIN team on player.team_id = team.team_id;

右链接

SELECT * FROM player RIGHT JOIN team on player.team_id = team.team_id;

自连接

SELECT b.player_name, b.height FROM player as a , player as b WHERE a.player_name = '布雷克 - 格里芬' and a.height < b.height;

SELECT height FROM player WHERE player_name = '布雷克 - 格里芬';

SELECT player_name, height FROM player WHERE height > 2.08;

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据