我們分析資料庫和 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。我將默認使用它，作為一個眾所周知的工具。感謝大家的閱讀。在下一篇文章中，我們將討論資料庫連線和聯結。這就是很酷的任務所在））

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