欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL中的连接(极客时间)

程序员文章站 2022-10-18 10:43:47
SQL中的连接 关系型数据库的核心之一就是连接, 而在不同的标准中, 连接的写法上可能有区别, 最为主要的两个SQL标准就是SQL92和SQL99了, 后面的数字表示的是标准提出的时间. SQL92中的连接 案例使用的表是球员表, 球队表和身高级别表, 下载: 笛卡尔积 笛卡尔积是一个数学运算, 假 ......

sql中的连接

关系型数据库的核心之一就是连接, 而在不同的标准中, 连接的写法上可能有区别, 最为主要的两个sql标准就是sql92和sql99了, 后面的数字表示的是标准提出的时间.

sql92中的连接

案例使用的表是球员表, 球队表和身高级别表, 下载: https://github.com/cystanford/sql_nba_data

笛卡尔积

笛卡尔积是一个数学运算, 假设两个集合x和y, 那么x和y的笛卡尔积就是x和y的所有可能组合.

sql: select * from player, team; // 笛卡尔积

简单的来说就是两个表的数据行的乘积就是结果行.

等值连接

等值连接就是用两张表都存在的列进行连接, 可以对多张表进行等值连接.

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; // 查询每个球员的身高级别
外连接

外连接是查询某一方不满足条件的记录. 两张表的外连接, 会有一张是主表, 一张是从表. 多张表的外连接, 第一张表是主表, 剩下的表全是从表, 在sql92中采用(+)代表从表所在的位置, 并且在sql92中只有左外连接和右外连接, 没有全外连接.

左外连接就是左边的表是主表, 需要显示全部行, 右侧的表是从表. 右外连接则相反.

select * from player, team where player.team_id = team.team_id(+); // 左外连接

select * from player, team where player.team_id(+) = team.team_id; // 右外连接

不过笔者在测试的时候发现(+)是不能使用的, 所以请参考下面的sql99的写法

sql:select * from player left join team on player.team_id = team.team_id; // 左外连接
sql: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; // 自连接查询比布雷克 - 格里芬身高高的球员

SQL中的连接(极客时间)

sql99的连接

还是上面的表, 看看sql99中是如何使用连接的.

交叉连接

交叉连接也就是sql92中的笛卡尔积, 这里采用cross join, 如下可以得到笛卡尔积的结果:

select * from player cross join team;  // 使用cross join实现笛卡尔积

自然连接

自然连接就是sql92中的等值连接, 会自动查询连接表中所有相同的字段, 进行等值连接.

select player_id, team_id, player_name, height, team_name from player natural join team ; // 使用natural join可以自动查询连接表的相同字段

这个有缺点有优点, 缺点在于如果设计表的时候设计不规范, 可能造成两张表字段名相同但表达意义不同, 这个时候直接使用natural join出来的结果可能就不是想要的结果. 当然优点在于写法简单

on连接

on连接用来指定想要的连接条件, 同样可以帮助我们实现自然连接的功能

select player_id, player.team_id, player_name, height, team_name from player join team on player.team_id = team.team_id; // on连接实现自然连接

on连接也可以进行非等值连接, 如下

select p.player_name, p.height, h.height_level
from player as p join height_grades as h
on height between h.height_lowest and h.height_highest; // 查询球员的身高等级

using连接

using指定数据表里同名字段进行等值连接

select player_id, team_id, player_name, height, team_name from player join team using(team_id); 

优点缺点与用natural join一样

外连接

sql99中的外连接:

  1. 左外连接: left join或left outer join
  2. 右外连接: right join或right outer join
  3. 全外连接: full join或full outer join
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 * from player full join team on player.team_id = team.team_id; // 全外连接

需要注意的是 mysql 不支持全外连接,否则的话全外连接会返回左表和右表中的所有行。当表之间有匹配的行,会显示内连接的结果。当某行在另一个表中没有匹配时,那么会把另一个表中选择的列显示为空值。

也就是说,全外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。

自连接

自连接在sql99中的表述如下:

select b.player_name, b.height from player as a join player as b on a.player_name = '布雷克 - 格里芬' and a.height < b.height; // 自连接

SQL中的连接(极客时间)

建议使用sql99的写法, 看上去会更简洁与清晰明了.