我們分析資料庫和 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), FORE KEY (teacher_Uid); 正如您所看到的，一切都做得清晰且連貫。我們有兩個外鍵的複合鍵：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 = stIN 書籍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。 = 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 Teacherid ON JOINtchidchert.按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(sb.books) > 6 bookw. ; 我已經突出顯示了我添加的部分。事實上，預期的結果是：只有瓦西連科通過了這一輪））

任務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視圖
• 群組連接
• 聯盟

等等。感謝所有有能力閱讀並重複我所做的事情的人。誰知道如何更好地提出請求 - 寫在評論中，我一定會閱讀它們）

此系列所有資料的清單位於本文開頭。