我们分析数据库和 SQL 语言。（第 5 部分 - 连接和联接） - “Java 项目从 A 到 Z”

有关创建 Java 项目的系列文章中的一篇文章（其他材料的链接位于最后）。其目标是分析关键技术，结果是编写一个电报机器人。

• 数据库中的关系类型
  • 一对多
  • 一对一（一对一）
  • 多对多
• 连接（连接）
  • 内部联接
  • 左连接
  • 右连接
  • 保护连接
• 家庭作业

大家好，未来的软件老年人和老年人。正如我在上一部分（检查作业）中所说，今天会有新材料。对于那些特别渴望的人，我挖了一个有趣的作业，以便那些已经知道一切的人和那些不知道但想谷歌的人可以练习和测试他们的技能。今天我们将讨论连接和联接的类型。

数据库中的关系类型

要理解什么是关系，您需要记住什么是外键。对于那些忘记的人，欢迎来到本系列的开头。

一对多

让我们记住我们的国家和城市的例子。显然，一个城市必须有一个国家。如何将国家与城市联系起来？有必要为每个城市附加其所属国家的唯一标识符（ID）：我们已经这样做了。这被称为连接类型之一 -一对多（最好了解英文版本 - 一对多）。换句话说，我们可以说：几个城市可以属于一个国家。这就是您应该记住的方式：一对多关系。到目前为止已经很清楚了，对吧？如果没有，那么这是来自互联网的第一张图片： 它显示有客户和他们的订单。一位客户可以有多个订单，这是有道理的。有一对多:) 或者另一个例子：有三个表：出版商、作者和书籍。每一个不想破产、想要成功的出版商都有不止一位作者，你不同意吗？反过来，每个作者可能拥有不止一本书——这一点也是毫无疑问的。这又意味着一位作者与多本书的联系，一位出版商与多位作者的联系。还有很多例子可以举出。一开始感知的困难可能只在于学习抽象思考：从外部观察桌子及其相互作用。

一对一（一对一）

这可以说是一对多通信的一个特例。一个表中的一条记录仅与另一表中的一条记录相关的情况。生活中还能有哪些例子呢？如果我们排除一夫多妻制，那么我们可以说夫妻之间是一对一的关系。虽然即使我们说允许一夫多妻制，但是每个妻子仍然只能拥有一个丈夫。对于父母来说也是如此。每个人只能有一名亲生父亲和一名亲生母亲。明确的一对一关系。当我写这篇文章时，我想到了一个想法：如果一对一关系已经具有一对一关系，为什么还要将一对一关系划分为不同表中的两条记录呢？我自己想出了答案。这些记录还可以通过其他方式链接到其他记录。我在说什么？一对一联系的另一个例子是国家和总统之间。是否可以在“国家”表中写下有关总统的所有数据？是的，可以，SQL 不会说一句话。但如果你认为总统也是一个人……而且他可能还有妻子（另一种一对一的关系）和孩子（另一种一对多的关系），然后事实证明会是将国家与总统的妻子和孩子联系起来是必要的…… 听起来很疯狂，对吧？:D 这种联系还有很多其他的例子。此外，在这种情况下，您可以向两个表添加外键，这与一对多关系不同。

多对多

根据名字，你就可以猜到我们要讨论的内容了。在生活中，当我们规划我们的生活时，经常会出现上述类型的连接不足以描述我们需要的东西的情况。我们已经讨论过出版商、书籍和作者。这里有太多的联系......每个出版物都可以有多个作者 - 一对多的联系。同时，每个作者可能有多个出版商（为什么不呢，例如，作者在一个地方出版，为钱吵架，去了另一家出版社）。这又是一对多的关系。或者这样：每个作者可以有几本书，但每本书也可以有多个作者。再次强调，作者与书籍、书籍与作者之间是一对多的关系。从这个例子我们可以得出一个更形式化的结论： 很清楚如何在 SQL 中设置前面的连接类型：我们只需将该连接类型的 ID 传递给那些记录，这些记录有很多，对吧？一个国家将其 ID 作为许多城市的外键。如何处理多对多关系？这种方法不适合。我们需要添加另一个表来连接这两个表。例如，让我们进入 MySQL，创建一个新数据库 Manytomany，创建两个表，author 和 book，其中仅包含姓名及其 ID： CREATE DATABASE Manytomany; 使用多对多；创建表作者（id INT AUTO_INCRMENT，名称VARCHAR（100），主键（id））；创建表 book( id INT AUTO_INCRMENT, name VARCHAR(100), PRIMARY KEY (id) ); 现在让我们创建第三个表，该表将具有来自作者和书籍表的两个外键，并且此链接将是唯一的。也就是说，不可能两次添加具有相同键的记录： CREATE TABLEauthors_x_books ( book_id INT NOT NULL,author_id INT NOT NULL, FOREIGN KEY (book_id) REFERENCES book(id), FOREIGN KEY (author_id) REFERENCES Author （id），唯一（book_id，author_id））；这里我们使用了几个新特性，需要单独注释一下：

• NOT NULL 意味着该字段必须始终填写，如果我们不填写，SQL 会告诉我们；
• UNIQUE 表示一个字段或一组字段在表中必须是唯一的。经常发生的情况是，除了唯一标识符之外，每条记录还必须有一个字段是唯一的。而UNIQUE正是负责这件事情的。

根据我的实践：当从旧系统迁移到新系统时，我们作为开发人员必须存储旧系统的 ID 以便使用它并创建我们自己的系统。为什么要创建自己的而不是使用旧的？它们可能不够独特，或者这种创建 ID 的方法可能不再相关且受到限制。为此，我们使旧的 ID 名称在表中也是唯一的。要检查这一点，您需要添加数据。添加书籍和作者： NSERT INTO book (name) VALUES ("book1"); INSERT INTO 作者（姓名）VALUES（“author1”）； 我们从之前的文章中已经知道它们的ID为1和1。因此，我们可以立即向第三个表添加一条记录： INSERT INTOauthors_x_booksVALUES(1,1); 一切都会很好，直到我们想再次重复最后一个命令：也就是说，再次写下相同的 ID：结果将很自然 - 一个错误。会有重复的。该条目不会被记录。这就是创建多对多连接的方式...所有这一切都非常酷且有趣，但是出现了一个逻辑问题：如何获取这些信息？如何将不同表中的数据组合在一起并得到一个答案？这就是我们将在下一部分中讨论的内容））

连接（连接）

在上一部分中，我帮助您立即了解连接是什么以及在哪里使用它们。因为我深信，一旦理解了，一切都会立即变得非常简单，所有关于联接的文章都会像婴儿的眼睛一样清晰:D 粗略地讲，联接是通过某种方式从多个表中获取结果JOIN（来自英语 join 的连接）。这就是全部...）要连接，您需要指定连接表的字段。魔鬼并不像画中的那么可怕，对吧？）接下来，我们只讨论连接有哪些类型以及如何使用它们。连接的类型有很多种，我们不会全部考虑。只有那些我们真正需要的。这就是为什么我们对 Cross 和 Natural 这样的奇异连接不感兴趣。我完全忘记了，我们需要记住一个细微差别：表和字段可以有别名- 假名。它们可以方便地用于连接。例如，您可以这样做： SELECT * FROM table1; 如果查询经常使用table1，那么你可以给它一个别名： SELECT* FROM table1 as t1; 或者更容易写： SELECT * FROM table1 t1; 然后稍后在查询中可以使用t1作为该表的别名。

内部联接

最常见、最简单的连接。它表示，当我们有两个表和一个可以连接它的字段时，将选择两个表中存在关系的所有记录。不知何故很难说。让我们看一个例子：让我们向城市数据库添加一条记录。一项用于城市，一项用于国家： $ INSERT INTO Country VALUES(5, "Uzbekistan", 34036800); $ INSERT INTO city (名称, 人口) VALUES("第比利斯", 1171100); 我们添加了一个在表中没有城市的国家/地区，以及一个与表中的国家/地区没有关联的城市。因此，INNER JOIN 致力于为两个表中的连接发布所有记录。当我们想要连接两个表 table1 和 table2 时，一般语法如下所示： SELECT * FROM table1 t1 INNER JOIN table2 ON t1.id = t2.t1_id; 然后将返回两个表中具有关系的所有记录。对于我们的例子，当我们想要接收国家和城市的信息时，结果将是这样的： $ SELECT * FROM city ci INNER JOIN Country co ON ci.country_id = co.id; 在这里，虽然名称相同，但您可以清楚地看到城市的字段在前，然后是国家的字段。但我们上面添加的两个条目不存在。因为这正是 INNER JOIN 的工作原理。

左连接

在某些情况下，而且经常是，我们对主表的字段丢失不满意，因为相邻表中没有该字段的记录。这就是 LEFT JOIN 的用途。如果在之前的请求中我们指定 LEFT 而不是 INNER，我们将在响应中添加另一个城市 - 第比利斯： $ SELECT * FROM city ci LEFT JOIN Country co ON ci.country_id = co.id; 有一个关于第比利斯的新条目，与该国家相关的所有内容均为null。这就是它经常被使用的方式。

右连接

这里与 LEFT JOIN 的区别在于，所有字段都不会选择在左侧，而是在连接的右侧。也就是说，不是城市，而是所有国家： $ SELECT * FROM city ci RIGHT JOIN Country co ON ci.country_id = co.id; 现在很明显，在这种情况下，不会有第比利斯，但我们会有乌兹别克斯坦。类似的东西......））

保护连接

现在我想向你们展示一张初级人员在面试前死记硬背的典型图片，以让他们相信自己了解连接的本质：这里一切都以集合的形式显示，每个圆圈都是一个表。那些被覆盖的地方就是那些将在 SELECT 中显示的部分。我们看看吧：

• INNER JOIN 只是集合的交集，即那些与两个表 A 和 B 有连接的记录；
• LEFT JOIN 是表 A 中的所有记录，包括表 B 中与 A 有交集（连接）的所有记录；
• RIGHT JOIN 与 LEFT JOIN 完全相反 - 表 B 中的所有记录与 A 中的记录都有关系。

经过这一切，这张图应该很清楚了））

家庭作业

这次的任务将非常有趣，所有成功解决它们的人都可以放心，他们已经准备好开始在 SQL 方面工作了！这些任务没有经过咀嚼，是为中学生写的，所以对你来说不会容易和无聊:)我会给你一周的时间自己做任务，然后我会单独发表一篇文章进行详细分析我给你的任务的解决方案。

实际任务：

1. 编写一个 SQL 脚本来创建包含以下字段的“学生”表：id（主键）、name、last_name、e_mail（唯一）。
2. 编写一个 SQL 脚本来创建包含以下字段的“Book”表：id、title（id + title = 主键）。通过“学生”一对多“书籍”关系链接“学生”和“书籍”。
3. 编写一个 SQL 脚本来创建包含以下字段的“教师”表：id（主键）、姓名、姓氏、电子邮件（唯一）、主题。
4. 通过“学生”多对多教师的关系链接“学生”和“教师”。
5. 选择姓氏中含有“oro”的“学生”，例如“Sid oro v”、“V oro novsky”。
6. 从“学生”表中选择所有姓氏 ('last_name') 及其重复次数。考虑一下数据库中有同名的人。按数量降序排列。它应该看起来像这样：

   姓	数量
   彼得罗夫	15
   伊万诺夫	12
   西多罗夫	3

7. 从“学生”中选择重复次数最多的 3 个姓名。按数量降序排列。它应该看起来像这样：

   姓名	数量
   亚历山大	27
   谢尔盖	10
   彼得	7

8. 选择拥有最多“书籍”数量的“学生”和关联的“老师”。按数量降序排列。它应该看起来像这样：

   老师的姓氏	学生的姓氏	书籍数量
   彼得罗夫	西多罗夫	7
   伊万诺夫	史密斯	5
   彼得罗夫	坎卡瓦	2>

9. 从所有“学生”中选择拥有最多“书籍”数量的“老师”。按数量降序排列。它应该看起来像这样：

   老师的姓氏	书籍数量
   彼得罗夫	9
   伊万诺夫	5

10. 选择其所有“学生”的“书籍”数量在 7 到 11 之间的“老师”。按数量降序排列。它应该看起来像这样：

    老师的姓氏	书籍数量
    彼得罗夫	十一
    西多罗夫	9
    伊万诺夫	7

11. 打印所有“教师”和“学生”的所有“姓氏”和“姓名”，字段“类型”（学生或教师）。按“姓氏”字母顺序排序。它应该看起来像这样：

    姓	类型
    伊万诺夫	学生
    坎卡瓦	老师
    史密斯	学生
    西多罗夫	老师
    彼得罗夫	老师

12. 将“rate”列添加到现有的“Student”表中，该列将存储学生当前所在的课程（数值从 1 到 6）。
13. 该项目不是必需的，但会是一个优点。编写一个函数，该函数将遍历所有“书籍”并输出以逗号分隔的所有“标题”。

结论

关于数据库的系列有点拖沓了。同意。然而，我们已经走了很长的路，结果我们对此事有了了解！感谢大家的阅读，我提醒大家，每个想要继续前进并关注该项目的人都需要在GitHub上创建一个帐户并订阅我的帐户:) 更多内容 - 让我们谈谈 Maven 和 Docker。感谢大家的阅读。我再说一遍：走的人才能掌握路；）

该系列所有材料的列表位于本文开头。