PostgreSQL資料函式庫與表格建立

PostgreSQL 作為一個開源的物件關聯式資料函式庫系統，廣泛應用於各種應用程式開發。要有效地使用 PostgreSQL，首先需要了解如何建立和管理資料函式庫以及表格。本文將引導讀者使用 pgAdmin 圖形化介面和 SQL 語法，建立名為 analysis 的資料函式庫，並在其中建立 teachers 表格，示範如何定義欄位及其資料型別，例如 bigserial、varchar、date 和 numeric。接著，文章將詳細說明如何使用 INSERT INTO 陳述式將多筆教師資料插入 teachers 表格，並透過 pgAdmin 檢視插入的資料，確保操作的正確性。最後，文章還提供了一些 SQL 語法錯誤處理的技巧，以及如何編寫更具可讀性的 SQL 陳述式，以提升程式碼維護性和團隊協作效率。

設定 PostgreSQL 與 pgAdmin

在開始使用 PostgreSQL 之前，我們需要了解如何在 macOS 或 Windows 上啟動 pgAdmin，以及如何連線到預設的 postgres 資料函式庫。pgAdmin 是一個強大的圖形化工具，可以幫助我們管理和查詢 PostgreSQL 資料函式庫。

在 macOS 上啟動 pgAdmin

首次在 macOS 上啟動 pgAdmin 時，可能會出現一個對話方塊，顯示「pgAdmin4.app 無法開啟，因為它來自未確認的開發者」。此時，您可以右鍵點選圖示並選擇「開啟」。接下來的對話方塊應該會給您開啟應用程式的選項。之後，您的 Mac 將記住您已授予此許可權。

pgAdmin 介面介紹

pgAdmin 的介面包含一個左側垂直面板，顯示物件瀏覽器，您可以在其中檢視可用的伺服器、資料函式庫、使用者和其他物件。在螢幕頂部是一系列功能表專案，下面則是顯示資料函式庫物件和效能各個方面的標籤。

連線到預設 postgres 資料函式庫

PostgreSQL 是一個資料函式倉管理系統，這意味著它是一種允許您定義、管理和查詢資料函式庫的軟體。當您安裝 PostgreSQL 時，它會建立一個資料函式庫伺服器——一個在您的電腦上執行的應用程式例項，其中包含一個名為 postgres 的預設資料函式庫。資料函式庫是一組物件的集合，包括表格、函式等，您的實際資料將儲存在這裡。我們使用 SQL 語言（以及 pgAdmin）來管理和查詢儲存在資料函式庫中的物件和資料。

要連線到預設的 postgres 資料函式庫，請按照以下步驟進行：

1. 在物件瀏覽器中，點選「Servers」節點左邊的向下箭頭以顯示預設伺服器。根據您的作業系統，預設伺服器名稱可能是 localhost 或 PostgreSQL x，其中 x 是 Postgres 版本號。
2. 雙擊伺服器名稱。如果出現提示，請輸入您在安裝過程中選擇的資料函式庫密碼（您可以選擇儲存密碼，以便將來不必再次輸入）。當 pgAdmin 建立連線時，會出現一條簡短的訊息。連線成功後，應在伺服器名稱下顯示幾個新的物件專案。
3. 展開「Databases」，然後展開預設資料函式庫 postgres。
4. 在 postgres 下，展開「Schemas」物件，然後展開 public。

您的物件瀏覽器面板應類別似於圖 1-4。

探索查詢工具

pgAdmin 應用程式包含一個查詢工具，您可以在此編寫和執行程式碼。要開啟查詢工具，請在 pgAdmin 的物件瀏覽器中首先點選任何資料函式庫以突出顯示它。例如，點選 postgres 資料函式庫，然後選擇「Tools」▶「Query Tool」。您將看到三個面板：查詢編輯器、用於儲存程式碼片段的暫存區，以及顯示查詢結果的資料輸出面板。您可以開啟多個標籤來連線到不同的資料函式庫或以您喜歡的方式組織程式碼。要開啟另一個標籤，請點選物件瀏覽器中的資料函式庫，然後再次透過功能表開啟查詢工具。

簡單查詢範例

讓我們執行一個簡單的查詢，並檢視其輸出，使用清單 1-1 中的陳述式傳回您已安裝的 PostgreSQL 版本。

SELECT version();

清單 1-1：檢查您的 PostgreSQL 版本

將程式碼輸入查詢編輯器，或如果您從 GitHub 下載了本文的程式碼，請點選 pgAdmin 工具列上的「Open File」圖示，然後導航到您儲存程式碼的資料夾，以開啟 Chapter_01 資料夾中的 Chapter_01.sql 檔案。要執行陳述式，請突出顯示以 SELECT 開頭的行，然後點選工具列中的「Execute/Refresh」圖示（形狀像播放按鈕）。PostgreSQL 應在 pgAdmin 資料輸出面板中傳回伺服器的版本作為結果，如圖 1-5 所示（您可能需要透過點選右邊緣並向右拖動來擴充套件資料輸出面板中的列，以檢視完整結果）。

#### 內容解密：

此查詢使用了 PostgreSQL 特有的函式 version() 來檢索伺服器的版本資訊。在本例中，輸出顯示正在執行 PostgreSQL 13.3，並提供了有關軟體版本的額外詳細資訊。

自訂 pgAdmin

從 pgAdmin 功能表中選擇「File」▶「Preferences」會開啟一個對話方塊，您可以在其中自訂 pgAdmin 的外觀和選項。以下是三個您可能希望立即存取的選項：

• 「Miscellaneous」▶「Themes」允許您在標準的淺色 pgAdmin 主題和深色主題之間進行選擇。
• 「Query Tool」▶「Results grid」允許您設定查詢結果中的最大列寬。在該對話方塊中，選擇「Column data」並在「Maximum column width」中輸入 300。
• 「Browser」部分允許您組態 pgAdmin 的佈局並設定鍵盤快捷鍵。

要獲得有關 pgAdmin 選項的幫助，請從功能表中選擇「Help」▶「Online Help」。在繼續之前，請隨意進一步探索偏好設定。

pgAdmin 的替代方案

雖然 pgAdmin 對初學者來說非常好，但您並非必須使用它來完成這些練習。如果您更喜歡使用其他與 PostgreSQL 相容的管理工具，請隨意使用。如果您想使用系統命令列完成本文中的所有練習，第 18 章提供了有關如何使用 PostgreSQL 互動式終端機 psql 從命令列操作的說明。（附錄列出了您可以探索的其他 PostgreSQL 資源，以尋找額外的管理工具。）

建立你的第一個資料函式庫和表格

SQL 不僅僅是一種從資料中提取知識的工具，也是一種定義資料結構以組織資料之間關係的語言。其中最重要的結構就是表格。表格是一個由列和欄組成的網格，用於儲存資料。每列包含特定型別的資料，通常是數字、字元或日期。我們使用 SQL 來定義表格的結構，以及表格之間的關聯。同時，我們也使用 SQL 來提取或查詢表格中的資料。

在本章中，你將建立你的第一個資料函式庫，新增一個表格，然後使用 pgAdmin 介面將幾行資料插入表格中。接著，你將使用 pgAdmin 檢視結果。讓我們從瞭解表格開始。

瞭解表格

瞭解你的表格是理解資料函式庫中資料的基礎。每當我開始使用一個新的資料函式庫時，我做的第一件事就是檢視其中的表格。我會從表格名稱和其欄位結構中尋找線索。這些表格包含文字、數字，還是兩者都有？每個表格中有多少行資料？

接下來，我會檢視資料函式庫中有多少個表格。最簡單的資料函式庫可能只有一個表格。一個完整的應用程式可能包含數十或數百個表格，用於處理客戶資料或追蹤航空旅行。表格的數量不僅告訴我需要分析多少資料，也提示我應該探索每個表格中資料之間的關係。

在深入 SQL 之前，讓我們先看看錶格內容可能是什麼樣子。我們將使用一個假設的學校課程註冊資料函式庫作為例子。在這個資料函式庫中有幾個表格，用於追蹤學生和他們的課程。第一個表格稱為 student_enrollment，顯示了每個課程部分的註冊學生：

這個表格顯示了兩名學生註冊了 COMPSCI101，三名學生註冊了 ENG101。但是每個學生和課程的詳細資訊在哪裡？在這個例子中，這些詳細資訊儲存在名為 students 和 classes 的單獨表格中，這些表格與 student_enrollment 表格相關聯。這就是關係型資料函式庫的強大之處。

students 表格的前幾行包括以下內容：

students 表格包含每個學生的詳細資訊，使用 student_id 列中的值來識別每個學生。該值作為一個唯一的鍵，將兩個表格連線起來，讓你可以建立像下面這樣的一行：

同樣地，classes 表格也有一個 class_id 列和幾個包含課程詳細資訊的列。資料函式庫設計者喜歡使用單獨的表格來儲存每個主要實體的資料，以減少冗餘資料。在這個例子中，我們只儲存每個學生的姓名和出生日期一次。即使學生註冊了多門課程，我們也不需要在 student_enrollment 表格中為每門課程重複輸入學生的姓名，只需包含學生的 ID。

建立資料函式庫

PostgreSQL 程式是你安裝在第1章中的一個資料函式倉管理系統，一個允許你定義、管理和查詢儲存在資料函式庫中的資料的軟體套件。一個資料函式庫是一組物件的集合，包括表格、函式等。當你安裝 PostgreSQL 時，它建立了一個資料函式庫伺服器——一個在你的電腦上執行的應用程式例項，其中包括一個預設的 postgres 資料函式庫。

根據 PostgreSQL 檔案，預設的 postgres 資料函式庫是「供使用者、實用工具和第三方應用程式使用」。我們將建立一個新的資料函式庫來用於書中的範例，而不是使用預設的 postgres 資料函式庫，這樣可以將與特定主題或應用程式相關的物件組織在一起。這是一個好的做法：它有助於避免在單個資料函式庫中堆積無關的表格，並確保如果你的資料將用於為應用程式提供動力，例如行動應用程式，那麼應用程式資料函式庫將只包含相關資訊。

要建立一個資料函式庫，你只需要一行 SQL，如清單 2-1 所示，我們將在 pgAdmin 中執行它。你可以在 https://www.nostarch.com/practical-sql-2nd-edition/ 下載本文中的所有範例程式碼。

CREATE DATABASE analysis;

清單 2-1：建立名為 analysis 的資料函式庫

這條陳述式使用預設的 PostgreSQL 設定在你的伺服器上建立一個名為 analysis 的資料函式庫。注意，該陳述式由兩個關鍵字——CREATE 和 DATABASE——組成，後面跟著新資料函式庫的名稱。你在陳述式結尾處加上分號，表示命令結束。根據 ANSI SQL 標準，你必須在所有 PostgreSQL 陳述式結尾處加上分號。在某些情況下，即使你省略了分號，你的查詢也可能會執行，但並非總是如此，因此養成使用分號的習慣是一個好主意。

在 pgAdmin 中執行 SQL

在第1章中，你安裝了圖形化管理工具 pgAdmin（如果你還沒安裝，請現在安裝）。我們將主要使用 pgAdmin 來執行你所寫的 SQL 陳述式，也就是執行程式碼。在本文第18章中，我將向你展示如何使用 PostgreSQL 命令列程式 psql 在終端機視窗中執行 SQL 陳述式，但使用圖形化介面入門更容易一些。

我們將使用 pgAdmin 來執行清單 2-1 中的 SQL 陳述式，建立一個新的資料函式庫。然後，我們將連線到新的資料函式庫並建立一個表格。步驟如下：

1. 啟動 PostgreSQL。如果你使用的是 Windows，安裝程式會設定 PostgreSQL 在每次啟動時自動執行。在 macOS 上，你需要雙擊 …（此段因被截斷，未完整內容）

在 PostgreSQL 中建立資料函式庫與表格

建立新的資料函式庫

首先，我們需要在 PostgreSQL 中建立一個新的資料函式庫，名為 analysis。這個資料函式庫將用於存放我們接下來要建立的表格和資料。

1. 開啟 pgAdmin 並連線到預設的伺服器。
2. 在物件瀏覽器中，展開 Databases 節點，並點選 postgres 資料函式庫。
3. 開啟 Query Tool，並在查詢編輯器中輸入以下 SQL 陳述式：

CREATE DATABASE analysis;

4. 點選 Execute/Refresh 圖示（形狀像一個向右箭頭）來執行陳述式。

執行成功後，你將在物件瀏覽器中看到新建立的 analysis 資料函式庫。

連線到 analysis 資料函式庫

在建立表格之前，我們需要確保 pgAdmin 連線到的是 analysis 資料函式庫，而不是預設的 postgres 資料函式庫。

1. 關閉 Query Tool。
2. 在物件瀏覽器中，點選 analysis 資料函式庫。
3. 開啟新的 Query Tool 視窗，連線到 analysis 資料函式庫。

現在，你所執行的任何 SQL 陳述式都將套用到 analysis 資料函式庫。

建立表格

表格是用來存放資料和定義資料之間關係的地方。在建立表格時，我們需要為每個欄位指定名稱和資料型別。

使用 CREATE TABLE 陳述式

讓我們建立一個名為 teachers 的表格，用來存放教師的相關資料。以下是 SQL 陳述式：

CREATE TABLE teachers (
    id bigserial,
    first_name varchar(25),
    last_name varchar(50),
    school varchar(50),
    hire_date date,
    salary numeric
);

內容解密：

1. CREATE TABLE teachers： 這個陳述式告訴 PostgreSQL 我們要建立一個名為 teachers 的新表格。
2. id bigserial： id 欄位被定義為 bigserial 資料型別，這是一種特殊的整數型別，每當我們新增一筆資料到表格中時，它就會自動遞增。
3. first_name、last_name 和 school 欄位： 這些欄位被定義為 varchar 資料型別，用來存放文字資料。括號中的數字代表該欄位可以存放的最大字元數。
4. hire_date 和 salary 欄位： hire_date 被定義為 date 資料型別，用來存放日期資料；salary 被定義為 numeric 資料型別，用來存放數值資料。

建立 teachers 表格

1. 開啟 pgAdmin 的 Query Tool。
2. 將上述 CREATE TABLE 陳述式複製到查詢編輯器中。
3. 點選 Execute/Refresh 圖示來執行陳述式。

執行成功後，你將在物件瀏覽器中看到新建立的 teachers 表格。

將資料插入資料表

你可以使用多種方式將資料新增至 PostgreSQL 資料表。通常，你會處理大量的資料列，因此最簡單的方法是直接從文字檔案或其他資料函式庫匯入資料至資料表。但為了入門，我們將使用 INSERT INTO ... VALUES 陳述式來新增幾列資料，該陳述式指定了目標欄位和資料值。然後，我們將檢視新資料在資料表中的呈現。

使用 INSERT 陳述式

要將資料插入 teachers 資料表，首先需要清除剛剛執行的 CREATE TABLE 陳述式。接著，按照建立資料函式庫和資料表的步驟，將清單 2-3 中的程式碼複製到 pgAdmin 查詢工具中（或者，如果你在查詢工具中開啟了來自 GitHub 的 Chapter_02.sql 檔案，則選中此清單）。

INSERT INTO teachers (first_name, last_name, school, hire_date, salary)
VALUES 
('Janet', 'Smith', 'F.D. Roosevelt HS', '2011-10-30', 36200),
('Lee', 'Reynolds', 'F.D. Roosevelt HS', '1993-05-22', 65000),
('Samuel', 'Cole', 'Myers Middle School', '2005-08-01', 43500),
('Samantha', 'Bush', 'Myers Middle School', '2011-10-30', 36200),
('Betty', 'Diaz', 'Myers Middle School', '2005-08-30', 43500),
('Kathleen', 'Roush', 'F.D. Roosevelt HS', '2010-10-22', 38500);

內容解密：

1. INSERT INTO teachers 陳述式指定要插入資料的資料表名稱：此陳述式告訴 PostgreSQL 要在哪個資料表中新增資料。
2. 指定欄位名稱：(first_name, last_name, school, hire_date, salary) 指定了要填入資料的欄位。這種做法讓 SQL 陳述式更具彈性，因為你不需要為每個欄位提供值。
3. VALUES 關鍵字後面跟著要插入的資料：每個括號內的資料集合代表一列新的資料。例如，('Janet', 'Smith', 'F.D. Roosevelt HS', '2011-10-30', 36200) 代表第一列要插入的資料。
4. 文字和日期需要使用單引號包圍，而數字則不需要：這是 SQL 的標準要求。例如，名字 'Janet' 和日期 '2011-10-30' 都需要使用單引號，而薪水 36200 則不需要。
5. id 欄位未被明確插入值，但 PostgreSQL 自動為其指定：因為在建立 teachers 資料表時，id 欄位被定義為 bigserial 資料型別，這使得它具有自動遞增的整數特性，PostgreSQL 會在插入新列時自動填入該欄位。

執行此程式碼後，查詢工具的訊息區域應該會顯示：

INSERT 0 6
Query returned successfully in 150 msec.

這表示成功插入了 6 列資料。

檢視資料

你可以使用 pgAdmin 直接檢視剛剛載入 teachers 資料表的資料。在物件瀏覽器中，找到該資料表並右鍵點選。選擇 View/Edit Data ▶ All Rows。如圖 2-6 所示，你將看到資料表中的六列資料，每個欄位都填入了 SQL 陳述式中的值。

處理錯誤

當程式碼出錯時，保持冷靜並仔細閱讀錯誤訊息是非常重要的。例如，如果你在清單 2-3 的程式碼中忘記了一個逗號，PostgreSQL 可能會回報以下錯誤：

ERROR: syntax error at or near "("
LINE 4: ('Samuel', 'Cole', 'Myers Middle School', '2005-08-01', 43...
^

錯誤訊息指出了語法錯誤發生的位置，讓你可以精確地修正問題。

編寫可讀的 SQL 陳述式

雖然 SQL 陳述式不需要特定的格式就能執行，但為了提高可讀性和成為一個好的程式設計師，遵循通用的格式規範是非常重要的。這包括使用一致的大小寫、縮排等，以使你的 SQL 陳述式更容易被他人理解。

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title PostgreSQL資料函式庫與表格建立

package "資料庫架構" {
    package "應用層" {
        component [連線池] as pool
        component [ORM 框架] as orm
    }

    package "資料庫引擎" {
        component [查詢解析器] as parser
        component [優化器] as optimizer
        component [執行引擎] as executor
    }

    package "儲存層" {
        database [主資料庫] as master
        database [讀取副本] as replica
        database [快取層] as cache
    }
}

pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中

master --> replica : 資料同步

note right of cache
  Redis/Memcached
  減少資料庫負載
end note

@enduml

此圖示展示了撰寫 SQL 陳述式的流程，從開始到提高可讀性與維護性的過程。透過遵循這些步驟，可以讓你的 SQL 陳述式更加專業且易於理解。