1. Toy模板网首页
  2. > 编程之道
  3. > Sql

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

12月前 分类：编程之道 / Sql 阅读(179)

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

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

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

关系数据库的实际应用

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

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

客户表

idfirst_namelast_nameemailgenderstatephone
653466635TimotheaCrattcrat0@bandcamp.comFemaleWashington206-220-3752
418540868KettieFugglekfuggle1@cafepress.comFemaleCalifornia661-793-1372
857532654BoonieSommerlandbsommerland2@soundcloud.comMaleNorth Carolina919-299-0715
563295938-4RedSeldonrseldon3@addthis.comMaleIndiana765-880-7420
024844147MarikaGallatlymgallatly4@loc.govFemaleNew York718-126-1462
900992907SharleneMcMastersmcmaster5@gmpg.orgFemaleNevada775-376-0931
329211747-XGroverOkeygokey6@weather.comMaleTexas915-913-0625
656608031FarlyPluckfpluck7@buzzfeed.comMaleTexas432-670-8809
906380018SumnerPickerellspickerellb@bloglovin.comMaleColorado719-239-5042

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

订单表

item_idcustomer_iditem_purchasedfirst_namelast_nameamountdate_purchased
82565290-530d-4272-9c8b-38dc0bc7426a653466635Creme De Menthe GreenTimotheaCrat$8.575/13/18
9cfa5f5c-6a9c-4400-8f0f-f8262a787cd0653466635Veal Inside - ProvimiTimotheaCrat$5.773/3/18
5dea0cce-c6be-4f35-91f6-0c6a1a8b8f11656608031Arizona - Plum Green TeaGroverOkey$1.729/6/18
b4813421-12e8-479b-a3b6-3d1c4c539625656608031Beer - FruliGroverOkey$4.0510/1/18
4e7c8548-340f-4e89-a7f1-95173dcc6e53656608031BoogiesGroverOkey$1.9712/17/18
65261e94-494d-48cc-8d5a-642ae6921600656608031Cup - 3.5oz; FoamGroverOkey$1.8411/28/18
1bfdca0f-d54a-4845-bbf5-982813ab4a65656608031Arizona - Green TeaGroverGauford$0.225/23/18
d20d7add-bad4-4559-8896-d4f6d05aa3dd906380018Lemonade - Strawberry; 591 MlSumnerTortoishell$7.9810/11/18
12134510-bc6c-4bd7-b733-b549a61edaa3906380018Pasta - Cappellini; DrySumnerWash$0.3111/13/18
80f1957c-df4d-40dc-b9c4-2c3939dd0865906380018Remy Red Berry InfusionSumnerPisculli$1.2512/31/18
a75f7593-3312-43e4-a604-43405f02efdd906380018Veal - Slab BaconSumnerJanaszewski$9.803/9/18
c6ef1f55-f35d-4618-8de7-36f59ea6653a906380018-5Beans - Black Bean; DrySumnerPiegrome$1.3612/11/18
c5b87ee3-da94-41b1-973a-ef544a3ffb6f906380018Calypso - Strawberry LemonadeSumnerPiegrome$7.712/21/19
e383c58b-d8da-40ac-afd6-7ee629dc95c6656608031Basil - Primerba; PasteMohammedReed$2.7710/21/18
d88ccd5b-0acb-4144-aceb-c4b4b46d3b17656608031Cheese - FontinaMohammedReed$4.247/14/18
659df773-719c-447e-a1a9-4577dc9c6885656608031Cotton Wet Mop 16 OzJockSkittles$8.441/24/19
ff52e91e-4a49-4a52-b9a5-ddc0b9316429656608031Pastry - Trippleberry Muffin - MiniJockSkittles$9.7711/17/18
86f8ad6a-c04c-4714-8f39-01c28dcbb3cb656608031Bread - OliveJockSkittles$4.511/10/19
e7a66b71-86ff-4700-ac57-71291e6997b0656608031Wine - White; Riesling; Semi - DryFarlyPluck$4.234/15/18
c448db87-1246-494a-bae4-dceb8ee8a7ae656608031Melon - Honey DewFarlyPluck$1.009/10/18
725c171a-452d-45ef-9f23-73ef20109b90656608031Sugar - InvertFarlyPluck$9.043/24/18
849f9140-1469-4e23-a1de-83533af5fb88656608031YokalineFarlyPluck$3.2112/31/18
2ea79a6b-bfec-4a08-9457-04128f3b37a9656608031Cake - Bande Of FruitFarlyPluck$1.575/20/18

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

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

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

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

订单的外键引用客户的 ID

订单的外键引用客户的 ID

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

连接数据集

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

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

  • 行比较：我们查找表 A中的列值与表 B中的列值匹配的行。

  • 列合并：任何联接的目的都是得到一个包含两个表中的列的表。

左连接和右连接

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模板网！

原文地址:https://www.toymoban.com/diary/sql/576.html

如若转载，请注明出处： 如若内容造成侵权/违法违规/事实不符，请联系站长进行投诉反馈，一经查实，立即删除！

分享到：
领支付宝红包 赞助服务器费用
从零开始学习SQL数据库操作：选择、更新和删除数据
上一篇 2023年12月09日 10:43
从零开始学习SQL数据库操作：学习聚合函数的使用方法
下一篇 2023年12月09日 14:23

相关文章

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包，优惠每天领

二维码1

领取红包

二维码2

领红包