我们分析数据库和 SQL 语言。（第 3 部分）-“Java 项目从头到尾”

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

• 检查作业
  • 添加主键
  • 添加摩尔多瓦
  • 创建城市表
  • 为城市添加外键
  • 奖金部分。测试
• 选择语句
  • WHERE 参数
  • 排序依据参数
  • 分组依据参数
• 家庭作业
• 结论

大家好，女士们先生们，我们继续聊数据库、SQL之类的事情。今天的材料将包含部分理论和部分实践。让我提醒您，上次我们讨论了如何设置一切，如何创建数据库、表并从中获取数据。是时候看看遥感是否能发挥作用了。我认为，仅根据上一篇文章就可以完成一半。事实证明，为了正确组装一个应用程序并使一切变得或多或少漂亮，您需要谈论数据库，而谈论它们需要花费大量时间。

检查作业

向所有成功完成任务的人致以崇高的敬意。这意味着您明白只有您需要这个并且它只会对您有帮助。对于那些忽视了我的任务的人，让我提醒你们一下情况：

1. 您需要将 ID 字段中的主键（PRIMARY KEY）添加到国家/地区表架构中。
2. 将另一个国家/地区添加到国家/地区表中 - 摩尔多瓦。
3. 按照上一篇文章的方案，创建一个表city，其中将包含所描述的所有字段。字段名称如下：id、name、country_id、population。
4. 向城市表添加主键。
5. 向城市表添加外键。

首先，让我们使用上一篇文章的第一部分并转到数据库终端。

添加主键

您可以通过两种方式添加主键（PRIMARY KEY）：在创建表时立即添加，或者在创建后使用 ALTER TABLE。

建表时的主键

由于我们已经创建了一个表，如果不删除它，我们将无法在该数据库中显示此方法，因此我们将简单地创建一个临时测试数据库，在其中执行所有操作。让我们输入以下命令：

• 创建一个新数据库：

  $CREATE DATABASE 测试；

• 创建一个表并添加主键：

  $ CREATE TABLE 国家(id INT, name VARCHAR(30), PRIMARY KEY (id));

一般来说，没什么复杂的。声明变量后，添加以下部分PRIMARY KEY (id)，其中将作为主键的字段名称在括号中传递。让我们看看表模式发生了怎样的变化： $ DESC Country; 正如您所看到的，值PRI已出现在id 条目的Key字段中。

创建表后的主键

正如我之前所说，创建表后的第一个键可以使用ALTER TABLE分配。我们将在我们的城市数据库中运行这个示例：

• 让我们从测试数据库转到我们的数据库：

  $USE城市；

• 让我们检查一下我们是否确实在我们的数据库中（那里应该有另一个字段 - 人口）。为此，我们写：

  $ DESC 人群；

一切都正确，桌子是我们的。让我们写下以下内容：

$ ALTER TABLE 国家/地区添加主键（id）；

• 并立即使用命令检查它：

  $DESC 国家；

从图中可以看出，一切都是正确的，PRI值正是它应该在的位置。顺便说一下，我们使用了一个测试数据库。现在我们需要删除它：为什么我们需要弄乱服务器，对吧？为此，我们使用一个相当知名的命令： $ DROP DATABASE test;

添加摩尔多瓦

首先我们需要决定要记录什么。我们的下一个 ID 为 4。名称为 Moldova，人口为 3550900。因此，我们执行我们已知的 INSERT INTO 命令： $ INSERT INTO Country VALUES (4, 'Moldova', 3550900); 我们检查数据库中的值是否准确： $ SELECT * FROM Country WHERE id = 4; 在数据请求中，我立即确定要搜索哪个字段，因此我们只得到了一条记录，这就是我们需要的。

创建城市表

使用第一篇有关数据库的文章中的图表，我们获得有关表的必要信息。它将包含以下字段：

• id——唯一标识符；
• name——城市名称；
• Country_id — 国家/地区外键；
• 人口——城市人口。

每次都写一个唯一的ID有点压力，你不觉得吗？我想把这个问题留给MySQL当局。还有这样一种方式——AUTO INCRMENT。我们需要将其添加到数字字段中，如果我们不显式传递值，MySQL 本身会比之前的 ID 加一。因此，创建表将如下所示： $ CREATE TABLE city ( id INT AUTO_INCRMENT, name VARCHAR(30),country_id INT,population INT, PRIMARY KEY (id)); 让我们看一下表格图，看看一切是否都正确完成： $ DESC city; 从表图中可以看到，我们对 id 字段有了一个新的描述 - auto_increment。所以我们一切都做对了。让我们检查完全配置的表上的数据。为此，我们将完成任务的最后一部分 - 外键。

为城市添加外键

对于外键，将有以下命令： $ ALTER TABLE city ADD FOREIGN KEY (country_id) REFERENCES country(id); 让我们立即检查表架构出了什么问题：在一小时内它是否发生了变化？ $DESC 城市；

奖金部分。测试

我忘记将其添加到任务中 - 填写第一部分屏幕截图中的数据。我忘记了，所以现在我自己做。对于那些感兴趣的人，您可以在没有我的情况下自己做，然后我们会检查；）有哈尔科夫、基辅、明斯克、敖德萨、沃罗涅日，我们还将添加基希讷乌。但这一次我们不会传输 ID，我们将跳过它们： $ INSERT INTO city (name,country_id,population)VALUES('Kharkov', 1, 1443000), ('Kyiv', 1, 3703100), ('Minsk' , 3, 2545500), ('敖德萨', 1, 1017699), ('沃罗涅日', 2, 1058261), ('基希讷乌', 4, 695400); 正如您所看到的，您可以使用一个 INSERT INTO 命令同时输入多个条目。记住，这是一个方便的事情）然后立即让我们看看表中的内容： $ SELECT * FROM city; AUTO_INCRMENT - 完全按照我们想要的方式工作。尽管我们没有提交，但身份证件已经全部填写完毕。外键是一个依赖的东西。要检查是否正常工作，可以尝试编写外部表中不存在的外键。假设我们确定 id = 5 是哈萨克斯坦。但实际上它并不在国家表中。并检查数据库是否会发誓，添加城市 - 阿斯塔纳： $ INSERT INTO city(name,country_id,population)VALUES('Astana', 5, 1136156); 我们自然会得到错误：现在外键确保我们不会尝试将国家/地区分配给不在我们数据库中的城市。这部分作业算是完成了——转入新作业：）

选择语句

好吧，一切看起来不再那么可怕了，对吧？我想再次强调，对于Java开发人员来说，数据库知识是必须具备的。没有数据库你就哪儿也去不了。是的，我已经想开始写申请了，我同意。但这是必要的。所以我们会继续这样。使用 SELECT 语句，我们从数据库中检索数据。也就是说，这是一个典型的 DML 操作（您已经忘记它是什么了吗？...）））重新阅读之前的文章）。关系数据库有什么好处？它们具有强大的聚合和检索数据功能。这就是 SELECT 语句的用途。看起来应该没什么复杂的吧？但事实证明，还有很多东西需要理解）了解我们可以构建的基础知识对我们来说很重要。使用 SELECT 语句的最简单查询是从一个表中选择所有数据。我真的很喜欢 wiki 中有关运算符在 SELECT 查询中应遵循的顺序的描述，因此我将厚颜无耻地将其复制到此处：

SELECT
  [DISTINCT | DISTINCTROW | ALL]
  select_expression,...
FROM table_references
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula}]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]

在这里您可以看到，不能先放置 GROUP BY 运算符，然后再放置 WHERE 运算符。需要记住这一点，这样以后就不会因为不清楚从何而来的错误而怨恨。 $SELECT * FROM 城市；但抓取所有数据对我们来说显然并不有趣。如果我们想用显微镜锤钉子，这完全一样[1] , [2]。由于数据库执行过滤、排序和聚合操作的速度比Java代码快得多，因此最好将这件事留给数据库。因此，通过使任务复杂化，我们将开放新功能。

WHERE 参数

要过滤选择，请使用WHERE词。这应该解释如下： SELECT * FROM tablename （从表 tablename 中选择所有字段） WHERE talbe_row = 1 （其中在记录中 table_row 字段等于 1）。需要注意的是，查询中关键字的顺序很重要。您不能编写 WHERE a =1 FROM table_name SELECT *。对于俄语来说这是可以的，对于某些人来说这可能看起来并没有那么糟糕，但是对于 SQL 来说这是不可接受的。我们编写以下查询： $ SELECT * FROM city WHERE country_id = 1; 我们选择了乌克兰城市。不错吧？如果我们不仅想要乌克兰语，还想要白俄罗斯语怎么办？为此，我们可以列出该字段可以取的值的集合： $SELECT * FROM city WHERE country_id IN(1, 3); 我们已经有两个国家的城市做出了回应。如果有多个条件需要过滤怎么办？假设我们想要人口超过200万的城市？为此，请使用OR和AND一词： $ SELECT * FROM city WHERE Country_id IN (1, 3) AND Population > 2000000; 很好，但是如果我们需要再添加一个条件 - 通过正则表达式搜索姓名（我不会在这里描述正则表达式：这里有一个人分 4 个部分“简单地”做了这个）怎么办？例如，我们记住了如何拼写城市，但不完全...为此，您可以将LIKE关键字添加到过滤表达式中： $ SELECT * FROM city WHERE country_id IN (1, 3) AND Population > 2000000 OR名字就像“%hark%”；这样我们也得到了哈尔科夫。结果，我们可以说我们的搜索结果非常好。但我想不按 ID 排序，而是按人口排序，但如何排序呢？是的，非常简单...

排序依据参数

使用 ORDER BY，我们可以按特定字段对收到的记录进行排序。它对数字和字符串进行排序。让我们扩展前面的查询，按人口排序，添加 ORDER BY Population： $ SELECT * FROM city WHERE Country_id IN (1, 3) AND Population > 2000000 OR name LIKE “%hark%” ORDER BY Population; 正如我们所看到的，排序是按自然顺序进行的，即升序。如果我们想要相反的结果怎么办？为此，您需要添加单词 DESC： $ SELECT * FROM city WHERE Country_id IN (1, 3) AND Population > 2000000 OR name LIKE “%hark%” ORDER BY Population DESC; 现在排序是基于人口减少。数据库执行此操作的速度非常快：没有Collections.sort可以进行比较。现在让我们按行、按名称倒序排序： $ SELECT * FROM city WHERE country_id IN (1, 3) AND Population > 2000000 OR name LIKE “%hark%” ORDER BY name DESC;

分组依据参数

用于按特定字段对记录进行分组。这通常需要使用聚合函数...什么是聚合函数？））如果不同记录的某些字段相同，则按某些字段进行分组是有意义的。让我们用我们的例子来看看这意味着什么。假设城市有外键 - 国家/地区 ID。因此，来自同一国家/地区的城市的 ID 是相同的。因此，您可以按记录对记录进行分组： $ SELECTcountry_id, COUNT(*) FROM city GROUP BYcountry_id; 但你必须承认，如果没有聚合函数，它看起来有点暗淡。因此，让我们看一下一些最常见的函数：

• COUNT - 记录数，可以不分组使用，用作COUNT(*)。在按某个字段分组的情况下 - COUNT(groupped_field);
• MAX - 查找特定字段的最大值；
• MIN - 查找特定字段的最小值；
• SUM - 查找特定字段的总和；
• AVG - 求平均值。

一般来说，这些功能无需分组即可使用，只显示一个字段。让我们尝试一下我们的城市人口： $ SELECT COUNT(*) FROM city; 他们所要求的就是他们得到的。只是记录的数量。有时这很有用。例如，如果我们需要找出某个作者的文章数量。无需将它们从数据库中取出并进行计数。您可以简单地使用 COUNT()。 $ SELECT AVG(人口) FROM city; $ SELECT MIN(population) FROM city； 这就是分组发挥作用的地方。例如，任务是获得该国最小的城市。已经知道该怎么做了吗？自己尝试一下，然后观看： $ SELECT Country_id as Country, MIN(population) FROM city WHERE GROUP BY Country_id; 到目前为止，我们只看到了国家/地区的 ID，但这并不重要 - 下次我们会做所有事情。这样就已经有了结果，我们得到了我们想要的——ID = 1 的国家中最小的城市。其余的功能将是相同的。需要注意的是，使用分组聚合时通过* 来耙出所有字段是行不通的！想想吧；）

家庭作业

根据前面几篇文章的结果，很明显正在做作业，所以我们继续））是的，每个做作业的人都会在评论中继续打“+”。对我来说重要的是，作业的话题对你来说很有趣，这样我以后才能继续做。是的，我定期阅读您的评论。当然，我回答的次数较少。我看到他们要求给出更难的SQL问题。在我们学习连接之前，不会有任何有趣的问题，因此我需要进一步的材料来解决这些问题。

任务：

了解HAVING运算符并为示例中的表编写示例查询。如果您需要添加一些字段或更多值以使其更清晰，请添加它们。如果有人愿意，请在评论中写下您的示例解决方案：这样，如果我有时间，我也可以检查它。
1. 安装 MySQL Workbench 以通过 UI 操作数据库。我认为我们已经对控制台工作进行了足够的练习。连接到数据库。如果您使用其他东西来处理数据库，请随意跳过此任务。在这里以及以后我将仅使用 MySQL Workbench。
2. 使用我们的数据编写接收请求：
   1. 最小/人口最多的国家；
   2. 该国的平均居民人数；
   3. 名称以“a”结尾的国家的平均居民人数；
   4. 人口超过四百万的国家数量；
   5. 按居民数量递减对国家进行排序；
   6. 按自然顺序按名称对国家/地区进行排序。

结论

今天我们详细讨论了上一课的作业。此外，我认为这对于那些没有这样做的人和那些已经这样做的人来说都很重要。对于前者，这是一个找出答案的机会，对于后者，这是一个将其与您的结果进行比较的机会。 订阅我的 GitHub 帐户以随时了解项目的更改。我将在那里维护整个代码库。一切都将在这个组织中发生。 接下来，我们讨论 SELECT 语句。他对我们来说是最重要的。所有数据请求都会通过它，我们必须理解它。最重要的是记住参数添加的顺序（WHERE、ORDER BY、GROUP BY 等）。是的，我没有告诉所有可能的事情，但我没有为自己设定这样的目标。是的，我知道您已经迫不及待地想写一份应用程序了。要有耐心，这就是你所需要的。无论是为了项目还是为了您的职业发展。在等待期间，请确保您已经熟悉 Git。我将默认使用它，作为一个众所周知的工具。感谢大家的阅读。在下一篇文章中，我们将讨论数据库连接和联接。这就是很酷的任务所在））

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