PostgreSQL 因其跨平台特性、遵循 SQL 標準、活躍的社群支援以及豐富的擴充功能，成為資料函式庫應用的理想選擇。本文除了涵蓋 SQL 基礎語法，也將探討 PostgreSQL 特有的功能，例如地理空間資料處理和全文檢索，並示範如何與 Python 等程式語言整合。即使用其他資料函式庫系統，本文的 SQL 基礎概念和程式碼邏輯也能提供參考。對於資料分析領域的新手，SQL 提供了理解資料結構和程式邏輯的入門途徑，而 PostgreSQL 的強大功能能滿足網頁應用程式和資料科學等進階應用需求。
為何選擇PostgreSQL與SQL？
在眾多資料函式庫系統中，PostgreSQL（簡稱Postgres）是一個強健且功能豐富的選擇。本文選擇PostgreSQL作為範例資料函式庫系統的原因包括：
- 免費且跨平台：PostgreSQL免費提供，並且支援Windows、macOS和Linux等多種作業系統。
- 遵循SQL標準：其SQL實作力求緊密遵循SQL標準，確保相容性和可移植性。
- 廣泛使用與社群支援：由於PostgreSQL被廣泛採用，使用者可以輕易線上上找到幫助和資源。
- 強大的地理空間擴充功能：PostGIS擴充功能允許使用者分析和處理幾何資料，並與QGIS等地圖軟體結合使用。
- 雲端運算支援：PostgreSQL可在Amazon Web Services和Google Cloud等雲端運算環境中使用。
- 適合網頁應用程式：它是許多網頁應用程式的常見資料儲存選擇，包括流行的Django網頁框架。
雖然本文以PostgreSQL為主，但其所涵蓋的SQL基礎概念和語法慣例大多適用於其他資料函式庫系統。因此，即使讀者使用的是MySQL或其他資料函式庫，也可以運用本文所學或輕易找到對應的程式碼概念。
為何學習SQL？
在資料分析的世界中，SQL並非唯一的工具。許多人從Microsoft Excel試算表開始，隨著需求的增加，可能會轉而使用Access。然而，Excel和Access都有其限制。例如，Excel目前的工作表列數上限為1,048,576行，而Access的資料函式庫大小限制為2 GB，每張表格的欄位數量上限為255。對於許多實際應用來說，這些限制可能導致問題。
使用強健的SQL資料函式庫系統，可以處理TB級的資料、多個相關表格和數千個欄位。它提供了對資料結構的精細控制，從而帶來效率、速度和準確性。
SQL與程式語言的結合
SQL也是資料科學中常用程式語言（如R和Python）的優秀輔助工具。使用者可以連線到SQL資料函式庫，甚至在某些情況下直接將SQL語法納入程式語言中。對於沒有程式設計背景的人來說，SQL通常是理解資料結構和程式邏輯相關概念的易於理解的入門途徑。
SQL在網頁應用程式中的角色
除了資料分析之外，SQL在建立網頁應用程式時也非常有用。許多常見的網頁框架、互動式地圖和內容管理系統都依賴資料函式庫提供後端支援。當需要深入這些應用程式的底層時，管理和操作資料的能力將變得非常有用。
本文適用物件
本文《實用SQL》針對的是那些在日常生活中遇到資料，並希望學習如何分析和轉換資料的人。我們使用真實世界的資料和場景，例如美國人口普查的人口統計資料、犯罪報告和紐約市的計程車行駛資料。我們的目標不僅是瞭解SQL的工作原理，還要學習如何利用它來發現有價值的見解。
學習前提
本文是為初學者編寫的，因此前幾章涵蓋了關於資料函式庫、資料和SQL語法的基本知識。具備一定SQL經驗的讀者可以從後面的章節中受益，這些章節涵蓋了更進階的主題，如地理資訊系統（GIS）。
本文內容概述
《實用SQL》首先介紹如何設定工作環境並取得範例程式碼和資料，接著逐步介紹SQL的基礎知識，包括資料函式庫、查詢、表格和資料型別等共通概念。在後面的章節中，我們將探討PostgreSQL特有的功能，如全文檢索、函式和GIS等。
章節安排
以下是本文各章節的詳細摘要：
- 第1章：設定工作環境，包括安裝PostgreSQL、pgAdmin使用者介面和文字編輯器，以及下載範例程式碼和資料。
- 第2章：建立第一個資料函式庫和表格，逐步介紹如何載入一個簡單的教師資料集到新的資料函式庫中。
- 第3章：使用SELECT進行初步的資料探索，涵蓋基本的SQL查詢語法，包括如何排序和篩選資料。
- 第4章：瞭解資料型別，介紹如何為表格中的欄位設定特定的資料型別，從文字到日期到各種數字形式。
- 第5章：匯入和匯出資料，說明如何使用SQL命令從外部檔案載入資料並匯出。讀者將載入一個美國人口普查的人口資料表格，並在整本文中使用它。
- 第6章：使用SQL進行基本的數學和統計運算，涵蓋算術運算和聚合函式，用於計算總和、平均值和中位數等。
- 第7章：在關聯式資料函式庫中連線表格，介紹如何在關鍵欄位上連線多個相關表格，並學習何時使用不同型別的連線。
- 第8章：設計有效的表格結構，涵蓋如何設定表格以改善資料的組織和完整性，以及如何使用索引加快查詢速度。
- 第9章：透過分組和匯總提取資訊，使用聚合函式來找出美國圖書館年度調查中的趨勢。
- 第10章：檢查和修改資料，介紹如何找出並修正不完整或不準確的資料，以肉類別、雞蛋和家禽生產者的記錄為例。
- 第11章：SQL中的統計函式，介紹相關性、迴歸分析、排名等函式，以幫助從資料集中提取更多意義。
- 第12章：處理日期和時間，說明如何在資料函式庫中建立、操作和查詢日期和時間，包括處理時區和使用紐約市計程車行程及Amtrak火車時刻表等資料。
程式碼解析
-- 查詢教師資料表的總筆數
SELECT COUNT(*) FROM teachers;
內容解密：
SELECT COUNT(*): 使用聚合函式COUNT(*)來計算表格中的總列數。FROM teachers: 指定要查詢的表格名稱為"teachers"。- 整個查詢用於統計"teachers"表格中的教師總數，提供快速瞭解資料規模的方法。
設定您的程式設計環境
要開始使用SQL進行資料分析，首先需要安裝必要的工具和軟體。本章將引導您完成安裝文字編輯器、下載範例程式碼和資料，以及安裝PostgreSQL資料函式庫系統和其配套的圖形使用者介面pgAdmin。
安裝文字編輯器
在進行資料分析時，您通常會使用多個文字檔案來儲存資料。這些檔案通常採用逗號分隔值（CSV）格式。為了避免在使用過程中損壞資料，建議使用純文字編輯器來開啟和編輯這些檔案。
常見的辦公軟體，如文書處理器和試算表程式，往往會在檔案中新增樣式或隱藏字元，這對於資料分析工作來說是有問題的，因為資料軟體需要精確的格式。例如，如果您使用Microsoft Excel開啟CSV檔案，程式會自動更改某些資料以使其更易讀；它可能會將專案程式碼3-09視為日期並將其格式化為9-Mar。文字編輯器則只處理純文字，不新增任何格式，因此程式設計師常用它們來編輯原始碼、資料和軟體組態等檔案。
您可以選擇自己喜歡的文字編輯器，以下是一些推薦的選項：
- Visual Studio Code by Microsoft：https://code.visualstudio.com/
- Atom by GitHub：https://atom.io/
- Sublime Text by Sublime HQ（免費試用，但需要購買以繼續使用）：https://www.sublimetext.com/
- Notepad++ by Don Ho（僅限Windows）：https://notepad-plus-plus.org/
對於喜歡在命令列中工作的進階使用者，可以使用預設安裝在macOS和Linux中的以下兩個文字編輯器：
- vim by Bram Moolenaar和開源社群：https://www.vim.org/
- GNU nano by Chris Allegretta和開源社群：https://www.nano-editor.org/
如果您還沒有文字編輯器，請下載並安裝一個，並熟悉開啟資料夾和處理檔案的基本操作。
從GitHub下載程式碼和資料
本文的所有範例程式碼和資料都可以在GitHub上下載。請按照以下步驟操作：
2. 在該頁面上，點選「Download the code from GitHub」連結，前往GitHub上的程式碼倉函式庫。
3. 在GitHub上的Practical SQL 2nd Edition頁面上，您應該會看到一個「Code」按鈕。點選它，然後選擇「Download ZIP」以下載ZIP檔案到您的電腦。將其存放在容易找到的位置，例如桌面。
4. 解壓縮ZIP檔案。您應該會看到一個名為practical-sql-2-master的資料夾，其中包含本文所需的各種檔案和子資料夾。同樣，將此資料夾存放在容易找到的位置。
注意事項
Windows使用者需要授予PostgreSQL資料函式庫讀取和寫入practical-sql-2-master資料夾內容的許可權。為此，請右鍵點選該資料夾，選擇「屬性」，然後點選「安全」標籤。點選「編輯」，然後點選「新增」。在物件名稱框中輸入Everyone，然後點選「確定」。在使用者列表中突出顯示Everyone，勾選「允許」下的所有方框，然後點選「套用」和「確定」。
安裝 PostgreSQL 與 pgAdmin
在開始練習之前，我們需要安裝 PostgreSQL 資料函式庫系統以及其配套的圖形化管理工具 pgAdmin。pgAdmin 提供了一個視覺化的工作空間，讓我們能夠輕鬆地管理 PostgreSQL 資料函式庫，包括檢視資料函式庫物件、管理設定、匯入及匯出資料，以及撰寫查詢陳述式等。
為何選擇 PostgreSQL？
PostgreSQL 是一個開放原始碼的資料函式庫系統，擁有活躍的社群支援，提供優秀的安裝，使其能夠快速地上手。本章節將介紹在 Windows、macOS 和 Linux 上安裝 PostgreSQL 的步驟。由於軟體版本和作業系統的不斷更新，具體步驟可能會有所變化，請務必參考各章節中提到的檔案和 GitHub 儲存函式庫，以取得最新的更新和常見問題的解答。
重點提示
建議安裝最新版本的 PostgreSQL，以確保系統具備最新的安全補丁和功能。本文假設讀者使用的是 11.0 或更高版本。
Windows 安裝步驟
在 Windows 上安裝 PostgreSQL，建議使用 EDB（前身為 EnterpriseDB）提供的安裝程式。EDB 為 PostgreSQL 使用者提供支援和服務。透過 EDB 下載的 PostgreSQL 套件包，除了包含 pgAdmin 之外，還包括 Stack Builder，這是一個用於下載其他有用工具的工具。
下載安裝程式：存取 https://www.postgresql.org/download/windows/，點選 EDB 部分的「Download the installer」，進入 EDB 網站的下載頁面。選擇最新的 64 位元 Windows 版本的 PostgreSQL，除非您使用的是較舊的 32 位元 Windows 系統。
執行安裝程式：下載完成後，以系統管理員身份執行安裝程式，並允許程式對您的電腦進行變更。
選擇安裝元件：在「Select Components」畫面中，選擇安裝 PostgreSQL Server、pgAdmin 工具、Stack Builder 和命令列工具。
設定資料儲存位置：您可以選擇預設的資料儲存位置，通常是在 PostgreSQL 目錄下的 data 子目錄中。
設定密碼：為預設的資料函式庫超級使用者帳號（postgres）設定密碼。PostgreSQL 對安全性和許可權管理非常嚴格。
選擇連線埠：選擇伺服器監聽的連線埠號碼，除非已有其他資料函式庫或應用程式佔用預設的 5432 連線埠，否則使用預設值即可。
選擇地區設定：使用預設的地區設定即可。
完成安裝並啟動 Stack Builder：安裝完成後，勾選啟動 Stack Builder 的選項，以下載額外的套件。
安裝額外元件：透過 Stack Builder，下載並安裝 PostGIS Bundle（用於空間資料擴充）和 EDB Language Pack（支援多種程式語言，包括 Python）。
程式碼範例與說明
以下是一個簡單的 SQL 程式碼範例，用於建立一個新的資料表：
CREATE TABLE public.customers (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL
);
內容解密：
CREATE TABLE public.customers: 建立一個名為customers的新資料表於publicschema 下。id SERIAL PRIMARY KEY: 建立一個名為id的欄位，型別為自動遞增的整數，並設為主鍵。name VARCHAR(100) NOT NULL: 建立一個名為name的欄位，型別為最大長度 100 的字串，且不可為空。email VARCHAR(100) UNIQUE NOT NULL: 建立一個名為email的欄位，型別為最大長度 100 的字串，必須唯一且不可為空。
Plantuml 圖表範例
@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle
title PostgreSQL與SQL資料函式庫應用
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
此圖示描述了在 Windows 上安裝 PostgreSQL 的主要步驟。
組態PostgreSQL與PostGIS於Windows與macOS
Windows環境下的PostgreSQL與PostGIS安裝
在Windows系統上安裝PostgreSQL和PostGIS需要經過幾個步驟，包括下載安裝程式、執行安裝、以及設定相關環境變數。
安裝步驟
下載並執行安裝程式：首先，從官方或可信賴的來源下載PostgreSQL的安裝程式。執行安裝程式，並遵循提示完成安裝。在安裝過程中，會詢問是否安裝額外的工具，如Stack Builder，用於安裝PostGIS。
安裝PostGIS：使用Stack Builder安裝PostGIS。在安裝過程中，系統會提示註冊
PROJ_LIB和GDAL_DATA環境變數，以及設定POSTGIS_ENABLED_DRIVERS和啟用POSTGIS_ENABLE_OUTDB_RASTERS環境變數。請對這些提示回答“是”，以確保PostGIS正確安裝和組態。完成安裝：完成上述步驟後，繼續點選“Finish”以完成安裝。根據安裝程式的版本，可能會提示重新啟動電腦。
安裝後的組態
- 安裝完成後，在Windows的“開始”選單中應該會出現兩個新的資料夾：一個用於PostgreSQL，另一個用於PostGIS。
- 如果需要使用Python與PostgreSQL配合，需要設定相關的環境變數。
組態Python語言支援
要在PostgreSQL中使用Python，需要組態Python語言支援。
- 開啟Windows控制台，搜尋“環境變數”，並選擇“編輯系統環境變數”。
- 在“系統屬性”對話方塊中，切換到“進階”標籤，點選“環境變數”。
- 在“系統變數”區域，如果沒有
PATH變數，則點選“新建”建立一個；如果已有PATH變數，則點選“編輯”進行修改。 - 新增或編輯
PATH變數，加入Python的路徑（例如：C:\edb\languagepack\v2\Python-3.9），並將其移到變數列表的最上方，以確保PostgreSQL使用正確的Python版本。 - 新增一個名為
PYTHONHOME的系統變數，值設為Python的安裝路徑（例如：C:\edb\languagepack\v2\Python-3.9）。
macOS上的安裝
對於macOS使用者，推薦使用Postgres.app，這是一個包含PostgreSQL、PostGIS和其他工具的開源應用程式。此外，還需要單獨安裝pgAdmin GUI和Python語言環境。
安裝Postgres.app和pgAdmin
- 下載並安裝Postgres.app：存取Postgres.app官方網站，下載最新版本的
.dmg檔案。雙擊開啟，並將應用程式圖示拖入“應用程式”資料夾。 - 啟動Postgres.app：在“應用程式”資料夾中找到Postgres.app並雙擊啟動。首次啟動時，可能需要初始化資料函式庫伺服器。
- 設定命令列工具：開啟Terminal，執行特定命令列以設定PostgreSQL命令列工具的路徑。
- 安裝pgAdmin：從pgAdmin官方網站下載macOS版本的安裝程式。雙擊
.dmg檔案，並將pgAdmin圖示拖入“應用程式”資料夾完成安裝。
安裝Python
- 下載Python 3.9：存取Python官方網站，下載與您的Mac處理器相容的Python 3.9版本（Intel晶片或Apple Silicon）。
- 安裝Python：執行下載的
.pkg檔案，按照提示完成Python的安裝。
在不同作業系統上安裝PostgreSQL與pgAdmin
安裝前的準備
在開始安裝PostgreSQL和pgAdmin之前，請先確認您的作業系統版本，並確保您具備系統管理員許可權。安裝過程中需要下載相關套件並進行設定。
macOS安裝步驟
要在macOS上安裝PostgreSQL，您可以使用Postgres.app這個工具。以下是安裝步驟：
- 前往Postgres.app官方網站下載最新版本的安裝包。
- 雙擊下載的安裝包，並遵循安裝指示完成安裝。
- 安裝完成後，啟動Postgres.app。
請注意，Postgres.app對Python有特定要求。安裝後請檢查Postgres.app的Python檔案以確保相容性。
Linux安裝步驟
在Linux上安裝PostgreSQL可以透過套件管理器完成。以下是Ubuntu的安裝範例：
Ubuntu安裝範例
- 開啟終端機，輸入以下指令以匯入PostgreSQL APT儲存函式庫的金鑰：
sudo apt-get install curl ca-certificates gnupg curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - - 建立
/etc/apt/sources.list.d/pgdg.list檔案：sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' - 更新套件列表並安裝PostgreSQL 13（可根據需要選擇最新版本）：
sudo apt-get update sudo apt-get install postgresql-13 - 登入PostgreSQL伺服器並設定密碼：
sudo -u postgres psql postgres postgres=# \password postgres - 建立具有SUPERUSER許可權的使用者帳號（將
anthony替換為您的Ubuntu使用者名稱）：postgres=# CREATE USER anthony SUPERUSER; - 安裝pgAdmin 4：
curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update' sudo apt-get install pgadmin4-desktop - 安裝PostGIS和PL/Python擴充功能（根據您的PostgreSQL版本調整版本號）：
sudo apt install postgresql-13-postgis-3 sudo apt install postgresql-plpython3-13
使用pgAdmin
pgAdmin是一個強大的PostgreSQL管理工具，提供圖形介面以組態伺服器和資料函式庫，並支援SQL查詢的編寫、執行和儲存。
啟動pgAdmin並設定主密碼
- 根據您的作業系統啟動pgAdmin。
- 第一次啟動時，您將被提示設定主密碼。請設定一個安全的密碼並點選OK。
pgAdmin功能簡介
pgAdmin提供以下功能：
- 資料函式庫伺服器管理：可管理多個PostgreSQL伺服器。
- 資料函式庫物件管理：可建立、修改和刪除資料函式庫物件，如資料表、檢視表、函式等。
- SQL查詢工具：提供強大的SQL查詢編輯器，支援語法高亮、自動完成和查詢結果展示。
透過pgAdmin，您可以更方便地管理和操作PostgreSQL資料函式庫。接下來的章節將深入介紹如何使用pgAdmin進行資料函式倉管理和開發工作。