从零开始学习SQL数据库操作：建立关系和组合数据集

阅读本文章，你将了解 SQL 有趣的一面，即连接表并创建 UNION。

如果您在本系列到目前为止对 SQL 感到有点距离或疏远，请不要害怕：我们即将发现关系数据库如此 … 关系的魔力。关掉灯光，播放您最喜欢的 Marvin Gaye 歌曲；我们即将在另一个层面上建立联系。

我发现现有的解释数据库关系（特别是 JOIN）的尝试在说明这些概念方面完全失败了。我们都习惯看到的维恩图对于从未见过 JOIN 发生的人来说毫无意义，即使如此，它们真的描述了正在发生的事情吗？我很乐意将一些快速动画放在一起作为替代方案，但很可能我会像我们其他人一样选择平庸的动画。

关系数据库的实际应用

尽管到目前为止我们已经介绍了 SQL，但我们仍然没有进行“讨论”。天啊，不，不是那个谈话；我的意思是必须举例说明两张桌子如何与另一张桌子相关联。这个谈话没那么尴尬，但它绝对不会让你为生活中更美好的事情做好准备。开玩笑，数据是生活中美好的一部分。或者至少它在我的身上。我们不要在这个问题上停留太久。

让我们看一下用于说明数据关系的最常见场景：客户与订单的困境。假设我们决定开设一个有机纯素古酮羽衣甘蓝 Voltron 5000 健康食品市场来迎合高端客户：自命不凡的富有混蛋。碰巧的是，“富有的混蛋”市场非常容易接受客户关系中的最佳实践，因此我们启动了 CRM 来跟踪我们的最佳客户。这种记录保存可以帮助我们假装记住客户的姓名和个性：

客户表

另一方面，我们需要跟踪库存和已售商品。既然我们已经在刷信用卡并获取所有这些个人客户数据，为什么不将购买与忠实客户关联起来呢？因此，我们有一个交易列表，看起来像这样：

订单表

当然，顾客会购买不止一件商品；他们买了很多。尤其是底部的那个 Farly Pluck 家伙——这是一个非常不幸的自动生成的名字。

作为独立的表，客户表和订单表各自至少有一个简单的用途。客户表帮助我们进行消费者人口统计分析，而订单表则确保我们赚钱并且不会被抢。虽然这两个功能很重要，但都不是特别具有革命性：自 70 年代以来，基本的记录保存水平一直是几乎所有企业的核心。

组合数据的能力使我们能够获得更重要的见解。我们可以奖励忠实的客户，根据个人的喜好满足他们的需求，甚至可能将 Pluck 先生过去 4 个月每周二和周四所在地点和时间的个人数据出售给出价最高的数据经纪人（提示：他在我们店里）。

感谢关系数据库，我们既不限于单个整体表，也不被我们前面设置的表的约束所束缚。关联数据很简单，只要我们有一种方法可以通过 来关联它。下面是将订单表中的外键与客户表中的主键进行匹配的可视化：

订单的外键引用客户的 ID

上面说明了我们已经了解过的内容：外键关联。主键和外键对于描述表之间的关系以及执行 SQL 连接至关重要。话不多说，让我们加入一些数据。

连接数据集

“连接”多组数据就是将多个表合并为一个。

这种合并的方式取决于我们使用的四种连接表方法中的哪一种：内连接、右连接、左连接和外连接（左连接和右连接有点相同，但无论如何）。无论 join 的类型如何，所有 join 都具有以下共同点：

左连接和右连接

LEFT 和 RIGHT 联接涵盖了无数的用例。只要发挥一点创造力，左 / 右连接就可以帮助解决我们可能没有预料到的问题。术语“左”和“右”是指从左到右阅读时我们想要加入的表格。当通过 连接表时 LEFT JOIN，查询中的第一个表将是“左”表。或者，aRIGHT JOIN 指最后一个表。

当我们说“要加入的表”时，我们指定哪个表的键值将成为我们合并的“权限”。在 a 中 LEFT MERGE，表 A 中的所有记录都将在合并后保留下来：

• 对于在 Table B 中具有匹配项的行，这些行将被“扩展”以包含 Table B 中的数据。这意味着从表 B 添加到表 A 的新列将包含已进行关联的所有行的数据。

• 对于表 A 中存在但在表 B 中不匹配的行，这些行不受影响：它们将包含与连接之前相同的数据，新列中的值留空。

• 表 B 中存在但表 A 中不存在的键将被丢弃。这些连接的目的是丰富主表的数据。

下面是我用来为“项目”页面上的看板模块提供支持的实际左连接示例。左表是 JIRA 问题表，右表是基于问题的自定义的集合，例如问题类型的自定义图标和颜色。看看这些数据是如何关联的，以及是什么使它进入最终的表：

左表上的键决定哪些行保留或消失。

查询的结构 LEFT JOIN 如下所示：

SELECT
  table_1.*, table_2.*
FROM
  t1
    LEFT JOIN
  t2 ON t1.column_name = t2.column_name;

这是一个具有实际值的示例：

SELECT first_name, last_name, order_date, order_amount
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id;

将此与 RIGHT JOIN 进行比较：

SELECT first_name, last_name, order_date, order_amount
FROM customers c RIGHT JOIN orders o
ON c.customer_id = o.customer_id;

内连接（或交叉连接）

内连接是连接数据集最保守的方法。LEFT 与或连接不同 RIGHT，内部连接中没有权威表：只有在所有表中包含匹配项的行才能在连接中幸存。所有其他行将被忽略：

SELECT table_1.column_name(s), table_2.column_name(s),
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

由于内部联接仅作用于所有受影响表中匹配的行，因此内部联接通常包含最“完整”的数据集（满足值的最大列数），但包含最少的行数。

外部连接

外连接实际上有几种不同的风格。一般来说，外连接可以最大限度地提高执行连接后存活的数据量。

左（或右）外连接

乍一看，您可能会看到左 / 右外连接的结果，并误认为它们与纯左 / 右连接对应的结果完全相同。嗯，其实你根本就不会错！是的，我在撒谎：连接类型之间本质上没有区别（因此我们提及它们的时间毫无价值）。

全外连接

在完全外连接中，所有列和行都将连接到结果输出中，无论行是否与我们指定的键匹配。您可能会问，为什么我们要指定一个密钥？键上的匹配行仍然会合并与所有涉及的表相似的行（如果在合并过程中确实没有具有共同点的行，您应该问自己为什么首先要合并两个不相关的数据集）。

结果有点乱。我将在这里借用 Pandas 文档可以查看：（pandas.pydata.org/pandas-docs/stable/user_guide/merging.html）

虽然 B 列似乎保持完整，但看看它周围发生的情况：由于连接而生成了标记为 A_x 和 A_y 的列。外连接创建了一个表，其中存在 B 列中键值的所有可能组合。因此，新表中的行数实际上是表 A 的 长度 * 表 B 的长度。

我个人很少使用外连接，但这只是我的情况。

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

场景：从多个 JOIN 创建新表

到目前为止，我们只查看了同时连接两个表的示例。事实上，我们可以一次合并任意数量的表！回到 JIRA 示例，下面是我用来创建支持自定义看板的最终表的实际查询：

CREATE TABLE jira
AS
SELECT
  jira_issues.*,
  jira_issuetypes.issuetype_url,
  jira_issuetypes.issuetype_color,
  jira_epiccolors.epic_color
FROM
  jira_issues
  LEFT JOIN jira_issuetypes ON jira_issues.issuetype = jira_issuetypes.issuetype
  LEFT JOIN jira_epiccolors ON jira_issues.epic_name = jira_epiccolors.epic_name;

如果您使用 PostgreSQL，视图是保存联接结果而无需添加其他表的好方法。不要使用 CREATE TABLE，而是尝试使用 CREATE VIEW：

CREATE VIEW jira
AS SELECT
  jira_issues.*,
  jira_issuetypes.issuetype_url,
  jira_issuetypes.issuetype_color,
  jira_epiccolors.epic_color
FROM
  jira_issues
  LEFT JOIN jira_issuetypes ON jira_issues.issuetype = jira_issuetypes.issuetype
  LEFT JOIN jira_epiccolors ON jira_issues.epic_name = jira_epiccolors.epic_name;

工会和全体工会

考虑 JOINs 的一个好方法是水平扩展我们的数据集。UNION 那么，A 是一种垂直组合数据的方式。联合 将具有相同结构的数据集组合起来：它们只是创建一个包含两个表中的行的表。UNION 运算符可以组合两个或多个 SELECT 语句的结果集，只要：

• UNION 中的每个 SELECT 语句必须具有相同的列数。

• 这些列还必须具有相似的数据类型。

• 每个 SELECT 语句中的列也必须具有相同的顺序。

联盟

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

联合（与 WHERE）

我们还可以通过 where 语句向联合添加逻辑：

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

联合所有

UNION 一个有趣的区别是 vs 的存在 UNION ALL。两者之中，UNION 是更“智能”的操作：如果两个 SELECT 中都存在相同的行 queries，则 aUNION 会知道只给我们一行以避免重复。另一方面，UNION ALL 确实返回重复项：这会导致更快的查询，并且对于那些想知道这两个 SELECT 语句中的内容的人可能很有用：

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

UNION ALL（与 WHERE）

就像 一样，我们可以通过 whereUNION 语句添加逻辑来联合所有：

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

更多 SQL 未来

我希望可视化 JOIN 和 UNION 的工作方式能够帮助减少 SQL 新手的摩擦。我发现很难相信人类能够在没有亲眼目睹这些概念发生的情况下完全掌握这些概念，这就引出了一个问题：为什么有人会在不知道其好处的情况下探索解释得如此糟糕的东西？

如果您发现这些指南有用，欢迎向我喊话，让它们继续出现。我们的系列中还有更多 SQL：请继续关注我们探索聚合值等内容！

文章来源地址 https://www.toymoban.com/diary/sql/576.html

到此这篇关于从零开始学习 SQL 数据库操作：建立关系和组合数据集的文章就介绍到这了, 更多相关内容可以在右上角搜索或继续浏览下面的相关文章，希望大家以后多多支持 TOY 模板网！