我们分析数据库和 SQL 语言。（第 6 部分 - 检查最终作业） - “Java 项目从 A 到 Z”

有关创建 Java 项目的系列文章中的一篇文章（其他材料的链接位于最后）。其目标是分析关键技术，结果是编写一个电报机器人。这部分包含对数据库最终任务的分析。问候，亲爱的读者。今天我们将分析上一篇数据库文章中的任务。这很有趣，因为它是针对中层面试的。也就是说，完成这个任务后你已经可以去面试了，并且你将成功通过至少部分与关系数据库相关的内容。我知道这篇文章是多么必要，因此我将投入我所有的经验使其变得有用和有趣。如果你没有在文章中途睡着，那就说明我的目的达到了。我不会重复整个任务：我会在完成每项任务之前引用它，并用斜体下划线。我希望阅读本文的每个人都会在其数据库中运行所有查询并得到相同的结果。这将为企业带来最大的利益。想到我帮助别人完成了艰巨的任务，我会感到有点高兴）

练习1

编写一个 SQL 脚本来创建包含以下字段的“学生”表：id（主键）、name、last_name、e_mail（唯一）。 我们已经这样做了，所以应该不会有任何问题。在脚本中，您需要指定主键和一个不同于主键的唯一字段。首先，让我们为此任务创建一个新数据库： $ CREATE DATABASE Final_task; 让我们使用这个数据库： $ USE Final_task; 一旦环境设置完毕并准备好运行作业，我们就可以编写以下脚本： $ CREATE TABLE Student ( id INT AUTO_INCRMENT, name VARCHAR(40), last_name VARCHAR(50), email VARCHAR(100), PRIMARY KEY ( id），唯一（电子邮件））； 到目前为止，与我们已经经历过的事情相比，没有什么新的。任何评论都是不必要的，让我们继续吧。

任务2-3

编写一个 SQL 脚本来创建包含以下字段的“Book”表：id、title（id + title = 主键）。通过“学生”一对多“书籍”关系链接“学生”和“书籍”。 让我们将两项任务合并为一项，使其更快、更方便。我已经在之前的文章中讨论过如何添加单独的外键。另外，我们需要记住我们如何建立联系以及通过什么建立联系。上一篇文章会对您有所帮助，然后这里是脚本： $ CREATE TABLE book ( id INT, title VARCHAR(100), Student_id INT DEFAULT NULL, PRIMARY KEY (id, title), FOREIGN KEY (student_id) REFERENCES Student (id ) ); 通过这种简单的方式，我们为表添加了一个组合键PRIMARY KEY (id, title)，现在该键将恰好是该对。这意味着表中可以有多个相同的 id 字段值。标题也完全一样。

任务4

编写一个 SQL 脚本来创建包含以下字段的“教师”表：id（主键）、姓名、姓氏、电子邮件（唯一）、主题。 我们继续为查询准备数据库，创建教师表： $ CREATE TABLE Teacher( id INT AUTO_INCRMENT, name VARCHAR(30), last_name VARCHAR(30), email VARCHAR(100), subject VARCHAR(40), PRIMARY KEY ( id），唯一（电子邮件））； 到目前为止，这并不困难，对吧？三个任务已经结束了！

任务5

通过“学生”多对多教师的关系链接“学生”和“教师”。 现在这更有趣了！上次我们刚刚谈到过这个问题。让我提醒您实现此目标需要什么：您需要创建一个用于存储学生-教师对的中间表。在它的帮助下，将有可能创建多对多的关系。因此，我们创建一个表Students_x_techers。命名方式是开放的，也可以是：student_teacher。 $ CREATE TABLE Students_x_teachers ( Student_id INT NOT NULL, Teacher_id INT NOT NULL, PRIMARY KEY (student_id, Teacher_id), FOREIGN KEY (student_id) REFERENCES Student(id), FOREIGN KEY (teacher_id) REFERENCES Teacher(id) ); 正如您所看到的，一切都做得清晰且连贯。我们有两个外键的复合键：student_id 和 Teacher_id。为什么还有外键？为了让我们确保学生和教师表中存在正在记录的配对的记录。

任务6

选择姓氏中带有“oro”的“学生”，例如“Sidorov”、“Voronovsky”。 为了让我们变得有趣和直观，我建议首先添加几个学生，以便其中一些适合这个要求，有些则不适合。因此，让我们写下应包含在请求中的人员： $ INSERT INTO Student (name, last_name, email) VALUES ('Ivan', 'Sidorov', 'ivan.sidorov@gmail.com'); $ INSERT INTO 学生 (姓名、姓氏、电子邮件) VALUES ('Nikolay', 'Voronovsky', 'nikolay.voronovsky@gmail.com'); 那些不应该进入的人： $ INSERT INTO Student (name, last_name, email) VALUES ('Roman', 'Fortny', 'roman.fortny@gmail.com'); $ INSERT INTO 学生 (姓名、姓氏、电子邮件) VALUES('Kostya', 'Petrov', 'kostya.petrov@gmail.com'); 我们来检查一下结果，看看students表中的数据列表： $SELECT * FROM Student; 我们得到：总共有四条记录，其中两条应该适合，另外两条不应该。准备好请求的所有数据后，我们可以为任务本身发出请求： $ SELECT * FROM Student WHERE last_name LIKE '%oro%'; 于是，伊万和尼古拉就翻阅了这份名单。

任务7

下一个任务，我们读到： 从“学生”表中选择所有姓氏（“last_name”）及其重复次数。考虑一下数据库中有同名的人。按数量降序排列。它应该看起来像这样：

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

为了清楚起见，您需要添加更多数据。言归正传，让我们添加彼得罗夫、伊万诺夫和西多罗夫，他们不知道自己的亲属关系；）我不会发明电子邮件地址，我只是将其从新条目中排除。让我们运行以下命令 12 次： $ INSERT INTO Student (name, last_name) VALUES ('Ivan', 'Ivanov'); 让我们添加 15 个 Petrov： $ INSERT INTO Student (name, last_name) VALUES ('Petr', 'Petrov'); 还有两个 Sidorov（我们已经有一个）））： $ INSERT INTO Student (name, last_name) VALUES ('Sidor', 'Sidorov'); 现在数据已经准备好了。要获取这样的数据，需要进行分组；要做分组，需要使用Group By运算符，并且需要通过last_name字段来进行分组。您还可以注意到，重复次数被指定为数量，这里您还需要记住如何在 SQL 中创建别名： $ SELECT Last_name, COUNT(*) as amount FROM Student GROUP BY Last_name ORDER BY COUNT(*) DESC ; 所以我对彼得罗夫夫妇做得太过分了 - 结果是 16))

任务8

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

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

哦，为了这个目的，我们已经有了伊万斯、彼得斯和西多尔。因此不需要添加它们。我们已经知道如何排序。今天我们唯一没有讨论的是如何选择一定数量的记录。这在之前的数据库问题解决方案中已经出现过。对于那些还没有读过的人，请阅读它。剩下的，我们直接进入正题： $ SELECT name, COUNT(*) as amount FROM Student GROUP BY name ORDER BY COUNT(*) DESC LIMIT 3; 从查询中可以看出，如果知道 SELECT 查询中运算符的顺序，那么执行这样的查询就不会出现问题。而这个任务仍然取决于我们。而前面介绍的知识已经足够解决这个问题了。

任务9

任务条件： 选择“书本”数量最多的“学生”并关联“老师”。按数量降序排列。它应该看起来像这样：

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

那么，这个任务显然比之前的任务更加困难吧？难怪：它闻起来像加入……而且不止一个）首先，我们需要了解要做什么……很明显，Book 的数量需要分组。但什么？我们为什么要把它们分组？查询涉及到三张表、分组、排序。从没有显示书籍的记录来看，这意味着需要采取INNER JOIN。我们还将提出 LEFT JOIN 请求，这样就不会出现任何问题。并且有多种选择。我们要做的第一件事是将三个表连接成一个记录。接下来，我们按学生分组并添加老师的名字。我们会选择什么？老师、学生的姓名和书籍数量。让我们为请求添加数据：

• 三名教师；
• 十本书；
• 将两名学生与三名教师连接起来。

三位老师

$ INSERT INTO Teacher(last_name) VALUES ('Matvienko'); $ INSERT INTO Teacher(last_name) VALUES ('舍甫琴科'); $ INSERT INTO 教师(姓氏) VALUES ('Vasilenko');

10 本书

我会拿走第一名和第二名学生的身份证。我会把书附在上面。由于没有设置AUTO_INCRMENT，为了不每次都写入新的ID，需要执行以下操作： $ ALTER TABLE book MODIFY id INT NOT NULL AUTO_INCRMENT; 接下来，为第一个学生添加书籍： $ INSERT INTO book (title, Student_id) VALUES('book1', 1); $ INSERT INTO book (标题, Student_id) VALUES('book2', 1); $ INSERT INTO book (标题, Student_id) VALUES('book3', 1); $ INSERT INTO book (标题, Student_id) VALUES('book4', 1); $ INSERT INTO book (标题, Student_id) VALUES('book5', 1); $ INSERT INTO book (标题, Student_id) VALUES('book6', 1); 第二个学生的书籍： $ INSERT INTO book (title, Student_id) VALUES('book7', 2); $ INSERT INTO book (标题, Student_id) VALUES('book8', 2); $ INSERT INTO book (标题, Student_id) VALUES('book9', 2); $ INSERT INTO book (标题, Student_id) VALUES('book10', 2);

师生联系

为此，请将 Students_x_teachers 添加到表中： $ INSERT INTO Students_x_teachers VALUES (1,1); $INSERT INTO Students_x_teachers VALUES(1,2); $INSERT INTO Students_x_teachers VALUES(2,3);

我们来实现一下请求

我们进行第一阶段 - 将三个表链接到一条记录中： $ SELECT * FROM Teacher tch INNER JOIN Students_x_teachers st_x_tch ON tch.id = st_x_tch.teacher_id INNER JOIN Student st ON st_x_tch.student_id = st.id INNER JOIN book b ON st .id = b.student_id; 当然，我们还没有任何记录，但是，我们可以看到我们已经成功连接了三个表。现在我们添加书籍分组、排序和我们需要的字段： $ SELECT tch.last_name, st.last_name, st.id, COUNT(*) as books FROM Student st INNER JOIN book b ON st.id = b.student_id INNER JOIN Students_x_teachers st_x_tch ON st.id = st_x_tch.student_id INNER JOIN Teacher tch ON tch.id = st_x_tch.teacher_id GROUP BY st.id ORDER BY books DESC; 但是我们在 SQL 中收到错误和以下答案： 错误代码：1055。SELECT 列表的表达式 #1 不在 GROUP BY 子句中，并且包含非聚合列“final_task.tch.last_name”，该列在功能上不依赖于 GROUP BY 中的列It does not work take这些元素，因为教师和学生之间存在多对多关系。这是事实：我们不能为每个学生配备一名老师。那么我们换个方式吧。让我们使用名为“View SQL”的东西。这个想法是什么：我们创建一个单独的视图，它是一个新表，已经包含我们需要的分组。我们将在此表中添加必要的教师姓名。但我们考虑到可能有不止一位老师，所以条目会重复。创建视图： $ CREATE VIEW StudentBooks as SELECT st.last_name,st.id,COUNT(*) as books FROM Student st INNER JOIN book b ON st.id=b.student_id GROUP BY st.id ORDER BY books DESC; 接下来，我们将此视图作为一个简单的表来使用，该表具有三个字段：学生姓氏、学生 ID 和书数。根据学生的 ID，我们还可以通过两个 join 添加教师： $ SELECT tch.last_name as 'Teacher', sbw.last_name 'Student', sbw.books as 'Books' from Studentbook sbw INNER JOIN Students_x_teachers stch ON sbw。 id = stch.student_id INNER JOIN 教师 tch ON tch.id = stch.teacher_id; 现在的结果就是：嗯！这是一个请求，对吧？）结果正如预期的那样：id=1的学生有六本书和两位老师，id=2的学生有四本书和一位老师。

任务10

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

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

在这里，我们可以使用上一个任务中的现成请求。对此我们需要改变什么？我们已经有了这些数据，我们只需要添加另一个分组并从输出数据中删除学生的姓名。但首先，让我们为老师再添加一个学生，以使结果更有趣。为此，我们编写： $ INSERT INTO Students_x_teachers VALUES (2, 1); 查询本身： $ SELECT tch.last_name as 'Teacher', SUM(sbw.books) as 'Books' from Studentbook sbw INNER JOIN Students_x_teachers stch ON sbw.id = stch.student_id INNER JOIN Teacher tch ON tch.id = stch .teacher_id 按 tch.id 分组； 结果，我们得到：瓦西连科老师有 10 本书，舍甫琴科有 6 本书......）

任务11

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

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

这就是我们将使用 HAVING 的地方。我们谈到了他。请求将与以前完全相同，只是您需要添加图书数量在一定范围内的条件。正如我在之前的文章中所说，当我们需要在分组和/或聚合函数中进行过滤时，我们需要使用HAVING： $ SELECT tch.last_name as 'Teacher', SUM(sbw.books) as 'Books' from Studentbook sbw INNER JOIN Students_x_teachers stch ON sbw.id = stch.student_id INNER JOIN Teacher tch ON tch.id = stch.teacher_id GROUP BY tch.id HAVING SUM(sbw.books) > 6 AND SUM(sbw.books) < 12; 我已经突出显示了我添加的部分。事实上，预期的结果是：只有瓦西连科通过了这一轮））

任务12

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

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

也就是说，我们需要组合两个输出，这正是 UNION 的用途。换句话说，我们将从学生和教师那里获取记录并一起打印： $ SELECT last_name, 'teacher' as type from Teacher UNION ALL select last_name, 'student' as type from school ORDER BY last_name; 而且会有老师和学生。看起来一切都很简单，但这是我们已经看到结果的时候。所以你需要猜测两件事。

任务13

条件： 在现有的“Student”表中添加一列“rate”，该列将存储学生当前所在的课程（数值从1到6）。 更改表学生添加约束 check_rate CHECK（速率 > 0 且速率 < 7）； 这里我们通过 ALTER TABLE 和 CHECK 添加一个字段，将该字段的限制设置为 1 到 6。

任务14

条件： 此项目不是必需的，但会是一个加号。编写一个函数，该函数将遍历所有“书籍”并输出以逗号分隔的所有“标题”。 在这里，您只需要返回一行作为请求结果，其中将包含所有书籍的标题。在这里我再次不得不谷歌。有这样一个函数 - GROUP_CONCAT，用它可以非常简单地完成此操作： $ SELECT GROUP_CONCAT(title) from book; 就是这样...)) 所有 14 个任务都已准备就绪。

结论

呃……这并不容易。很有意思。我非常确定这些任务是值得的。当我们执行这些任务时，我们回顾了许多以前不知道的事情：

• SQL视图
• 组连接
• 联盟

等等。感谢所有有能力阅读并重复我所做的事情的人。谁知道如何更好地提出请求 - 写在评论中，我一定会阅读它们）

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