SQL 資料函式庫查詢入門 SELECT 陳述式

– 10 分鐘閱讀

SQL 的 SELECT 陳述式是資料函式庫互動的基礎，如同與資料函式庫對話的橋樑。它允許我們從資料表中提取特定資訊，如同精準提問以取得所需答案。從選擇所有欄位到指定特定欄位，SELECT 提供了靈活的資料擷取方式。結合 ORDER BY，我們可以根據需求排序結果，例如依薪資高低排序員薪水料。DISTINCT 則能有效過濾重複資料，確保結果的唯一性，例如統計不同學校的名稱。更進一步，LIKE 和 ILIKE 運算元實作了模糊搜尋，可以根據部分字串匹配資料，例如搜尋名字中包含特定字元的老師。最後，AND 和 OR 運算元則能組合多個條件，實作更精確的資料篩選，例如查詢特定學校中特定薪資範圍的老師。

資料檢索的起點：SELECT 陳述式初探

在進行資料分析的過程中，最令人興奮的時刻之一便是與資料進行「對話」。這就像是在面試一位求職者，需要提出適當的問題來瞭解其真實能力是否與履歷相符。在 SQL 中，這種與資料的對話始於 SELECT 關鍵字，它能夠從資料函式庫中的一個或多個表格中檢索出所需的行和列。

基本的 SELECT 語法

讓我們先從一個簡單的 SELECT 陳述式開始，看看如何從名為 my_table 的表格中擷取所有的行和列：

SELECT * FROM my_table;

這行程式碼展示了 SQL 查詢最基本的形態。其中，SELECT 後面的星號（*）是萬用字元，代表「選擇所有列」。如果你指定了特定的欄位名稱而不是萬用字元，那麼查詢就會傳回該欄位的值。FROM 關鍵字則用來指定要查詢的表格名稱。

內容解密：

  1. SELECT *：表示選擇所有的欄位。* 是萬用字元，代表所有可能的欄位名稱。
  2. FROM my_table：指定要查詢的表格是 my_table。
  3. ;：分號表示 SQL 查詢陳述式的結束。

現在，讓我們使用這個 SELECT 陳述式來查詢第 2 章中建立的 teachers 表格。開啟 pgAdmin，選擇 analysis 資料函式庫，並開啟查詢工具。然後執行以下陳述式：

SELECT * FROM teachers;

內容解密：

  • 這條查詢會傳回 teachers 表格中的所有行和列。
  • 結果集中包含了 id、first_name、last_name、school、hire_date 和 salary 等欄位。
  • id 欄位（型別為 bigserial）會自動填入連續的整數，即使我們沒有明確地插入它們。

執行查詢後，你會在查詢工具的輸出面板中看到所有插入到 teachers 表格中的資料。

查詢特定欄位

在許多情況下，我們並不需要檢索表格中的所有欄位，尤其是當資料函式庫規模較大時。這時，我們可以指定要查詢的欄位，以避免處理過多的資料。

內容解密：

  • 限制欄位的好處：可以提高查詢效率，減少不必要的資料傳輸。
  • 如何指定欄位：只需在 SELECT 後面列出所需的欄位名稱，而不是使用萬用字元 *。

例如，如果我們只想查詢 teachers 表格中的 first_name 和 last_name，可以這樣寫：

SELECT first_name, last_name FROM teachers;

內容解密：

  1. SELECT first_name, last_name：表示選擇 first_name 和 last_name 兩個欄位。
  2. FROM teachers：指定要查詢的表格是 teachers。

透過這種方式，我們可以更精確地控制查詢結果，獲得所需的資料。接下來，我們將繼續探索更多關於 SELECT 陳述式的高階用法，以便更有效地與資料進行互動。

資料查詢與排序

在進行資料分析時，瞭解如何有效地查詢和排序資料是至關重要的。SQL（Structured Query Language）提供了多種語法和功能來幫助我們達成這些目標。本篇文章將探討如何使用 SELECT、ORDER BY 和 DISTINCT 等 SQL 關鍵字來查詢和排序資料。

使用 SELECT 查詢特定欄位

當我們需要從資料表中檢索特定的資料時，可以使用 SELECT 陳述式。透過指定欄位名稱，我們可以只檢索需要的資料，而不是整個資料表的所有欄位。

SELECT some_column, another_column, amazing_column 
FROM table_name;

例如，若我們想從 teachers 資料表中檢索老師的姓名和薪水，可以使用以下查詢：

SELECT last_name, first_name, salary 
FROM teachers;

內容解密：

  • SELECT 陳述式用於指定要檢索的欄位。
  • last_name、first_name 和 salary 是我們感興趣的欄位。
  • FROM teachers 指定了資料來源，即 teachers 資料表。

使用 ORDER BY 排序資料

排序資料可以幫助我們更好地理解資料的分佈和模式。SQL 中的 ORDER BY 子句允許我們按照一個或多個欄位對查詢結果進行排序。

SELECT first_name, last_name, salary
FROM teachers
ORDER BY salary DESC;

內容解密：

  • ORDER BY salary DESC 將結果按照 salary 欄位進行降序排序。
  • DESC 關鍵字表示降序排序，而預設的排序方式是升序（ASC）。

多欄位排序

我們也可以按照多個欄位進行排序，以獲得更詳細的資料洞察。

SELECT last_name, school, hire_date
FROM teachers
ORDER BY school ASC, hire_date DESC;

內容解密：

  • ORDER BY school ASC, hire_date DESC 首先按照 school 欄位進行升序排序，然後在每個學校內部按照 hire_date 進行降序排序。
  • 這種排序方式可以幫助我們找出每個學校中最晚被聘用的老師。

使用 DISTINCT 找出唯一值

在資料分析中，瞭解某個欄位中有哪些不同的取值是非常有用的。SQL 的 DISTINCT 關鍵字可以幫助我們實作這一點。

SELECT DISTINCT school
FROM teachers
ORDER BY school;

內容解密：

  • SELECT DISTINCT school 將檢索 school 欄位中的所有唯一值。
  • 結果將不包含重複的學校名稱，並且按照學校名稱進行排序。

使用DISTINCT關鍵字過濾重複資料

在處理資料時，我們經常會遇到重複的資料列。SQL提供了DISTINCT關鍵字來幫助我們找出並刪除重複的資料列。當我們對某個欄位使用DISTINCT時，查詢結果將只包含該欄位中獨特的值。

單一欄位的DISTINCT查詢

假設我們要找出teachers表中所有不同的school名稱，可以使用以下查詢：

SELECT DISTINCT school
FROM teachers
ORDER BY school;

這個查詢將傳回teachers表中所有不同的學校名稱，並按照學校名稱排序。

多欄位的DISTINCT查詢

DISTINCT關鍵字也可以應用於多個欄位。當我們對多個欄位使用DISTINCT時，查詢結果將包含這些欄位中所有獨特的組合。

SELECT DISTINCT school, salary
FROM teachers
ORDER BY school, salary;

這個查詢將傳回每個學校中所有不同的薪水值。如果有多名教師具有相同的薪水，則該組合只會出現一次。

使用WHERE子句過濾資料列

有時，我們希望查詢結果只包含滿足特定條件的資料列。這時，我們可以使用WHERE子句來過濾資料。

基本的WHERE子句範例

SELECT last_name, school, hire_date
FROM teachers
WHERE school = 'Myers Middle School';

這個查詢將傳回所有在Myers Middle School任教的教師的姓氏、學校名稱和聘用日期。

常用的比較運算子

在WHERE子句中，我們可以使用各種比較運算子來指定過濾條件。以下是一些常用的比較運算子：

運算子功能範例
=等於WHERE school = 'Baker Middle'
<> 或 !=不等於WHERE school <> 'Baker Middle'
>大於WHERE salary > 20000
<小於WHERE salary < 60500
>=大於或等於WHERE salary >= 20000
<=小於或等於WHERE salary <= 60500
BETWEEN在某個範圍內WHERE salary BETWEEN 20000 AND 40000
IN符合某個值集合WHERE last_name IN ('Bush', 'Roush')
LIKE符合某個模式（區分大小寫）WHERE first_name LIKE 'Sam%'
ILIKE符合某個模式（不區分大小寫）WHERE first_name ILIKE 'sam%'
NOT否定某個條件WHERE first_name NOT ILIKE 'sam%'

LIKE和ILIKE的使用

LIKE和ILIKE運算子允許我們根據某個模式來搜尋資料。LIKE是區分大小寫的，而ILIKE則不區分大小寫。

使用LIKE和ILIKE的範例

SELECT first_name, last_name, school
FROM teachers
WHERE first_name LIKE 'Janet';

這個查詢將傳回所有名為Janet的教師。

SELECT school
FROM teachers
WHERE school <> 'F.D. Roosevelt HS';

這個查詢將傳回所有非F.D. Roosevelt HS的學校名稱。

SELECT first_name, last_name, hire_date
FROM teachers
WHERE hire_date < '2000-01-01';

這個查詢將傳回所有在2000年1月1日之前被聘用的教師。

SELECT first_name, last_name, salary
FROM teachers
WHERE salary >= 43500;

這個查詢將傳回所有薪水大於或等於43500的教師。

使用BETWEEN運算子

SELECT first_name, last_name, school, salary
FROM teachers
WHERE salary BETWEEN 40000 AND 65000;

這個查詢將傳回所有薪水在40000到65000之間的教師。需要注意的是，BETWEEN運算子是包含邊界值的，因此上述查詢將包含薪水恰好為40000和65000的教師。

為了避免潛在的重複計算，可以使用更明確的大於和小於運算子來定義範圍：

SELECT first_name, last_name, school, salary
FROM teachers
WHERE salary >= 40000 AND salary <= 65000;

這個查詢與前一個查詢結果相同，但更明確地指定了範圍。#### 內容解密：

  1. SELECT DISTINCT school FROM teachers ORDER BY school;：此查詢用於找出 teachers 表中所有不同的學校名稱，並按照學校名稱進行排序。

    • 作用：刪除重複的學校名稱，只保留獨特的值。
    • 邏輯：使用 DISTINCT 關鍵字來確保結果集中每個學校名稱只出現一次，並使用 ORDER BY 對結果進行排序，使其更易閱讀。

  2. SELECT DISTINCT school, salary FROM teachers ORDER BY school, salary;：此查詢用於找出每個學校中不同的薪水值。

    • 作用：傳回每個學校中獨特的薪水組合。
    • 邏輯：當對多個欄位使用 DISTINCT 時，查詢結果會顯示這些欄位的所有獨特組合。例如，如果多名教師具有相同的薪水，該組合只會出現一次。

  3. SELECT last_name, school, hire_date FROM teachers WHERE school = 'Myers Middle School';：此查詢用於找出在Myers Middle School任教的教師的姓氏、學校名稱和聘用日期。

    • 作用：過濾出符合特定條件（學校名稱為Myers Middle School）的資料列。
    • 邏輯：使用 WHERE 子句指定篩選條件，這裡使用了等於運算子（=）來匹配特定的學校名稱。

  4. 比較運算子的使用：

    • 作用：提供多種條件篩選方式，例如等於、不等於、大於、小於、範圍內等，以滿足不同的查詢需求。
    • 邏輯：每個運算子根據其定義對資料進行比較，傳回符合條件的資料列。例如，使用 BETWEEN 來篩選出在某個範圍內的薪水值。

  5. SELECT first_name, last_name, school, salary FROM teachers WHERE salary BETWEEN 40000 AND 65000;：此查詢用於找出薪水在40000到65000之間的教師。

    • 作用：篩選出薪水在指定範圍內的教師資訊。
    • 邏輯：使用 BETWEEN 運算子來定義範圍，注意該運算子是包含邊界值的，即包含40000和65000。如果需要排除邊界值，可以使用大於（>）和小於（<）運算子來代替。

  6. LIKE 和 ILIKE：

    • LIKE 用於模式匹配，並且區分大小寫。例如，查詢名字以 ‘Sam’ 開頭的教師。
    • ILIKE 與 LIKE 相似，但不區分大小寫。
    • 作用：允許根據部分匹配條件進行查詢，對於不完全記住搜尋內容的情況非常有用。
@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title SQL 資料函式庫查詢入門 SELECT 陳述式

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查詢的流程：

  • 首先判斷是否需要使用 DISTINCT 進行獨特值查詢。
  • 如果不需要 DISTINCT，則進一步判斷是否需要使用 WHERE 子句進行條件篩選。
  • 根據不同的條件執行相應的SQL查詢，並傳回結果。
  • 最終結束查詢流程。

使用 LIKE 和 ILIKE 進行模式匹配

在進行資料查詢時，我們經常需要根據特定的模式來篩選資料。PostgreSQL 提供了 LIKE 和 ILIKE 運算元來實作模式匹配的功能。

LIKE 和 ILIKE 的使用

LIKE 和 ILIKE 的主要差異在於是否區分大小寫。LIKE 是 ANSI SQL 標準的一部分，區分大小寫；而 ILIKE 是 PostgreSQL 的特定實作，不區分大小寫。

要使用 LIKE 或 ILIKE，你需要指定一個模式，其中可以包含以下特殊字元：

  • 百分號（%）：匹配一個或多個字元的萬用字元
  • 下劃線（_）：匹配單一字元的萬用字元

例如，若要尋找包含 “baker” 的字串，以下 LIKE 模式都能夠匹配：

LIKE 'b%'
LIKE '%ak%'
LIKE '_aker'
LIKE 'ba_er'

內容解密：

  1. 'b%'：匹配任何以 “b” 開頭的字串。
  2. '%ak%'：匹配任何包含 “ak” 的字串。
  3. '_aker'：匹配任何以任意一個字元開頭，後面跟著 “aker” 的字串。
  4. 'ba_er'：匹配任何以 “ba” 開頭，中間有任意一個字元，後面跟著 “er” 的字串。

使用範例

清單 3-8 展示了 LIKE 和 ILIKE 的不同結果。第一個查詢使用 LIKE 尋找名字以 “sam” 開頭的老師，由於區分大小寫，因此不會傳回任何結果。第二個查詢使用不區分大小寫的 ILIKE，因此會傳回 “Samuel” 和 “Samantha”。

SELECT first_name
FROM teachers
WHERE first_name LIKE 'sam%';

SELECT first_name
FROM teachers
WHERE first_name ILIKE 'sam%';

效能考量

由於 LIKE 和 ILIKE 是根據模式進行搜尋，因此在大規模資料函式庫上的效能可能會較慢。我們可以透過建立索引來改善查詢效能，這部分將在第 8 章「使用索引加速查詢」中詳細介紹。

結合 AND 和 OR 運算元

比較運算元可以與邏輯運算元 AND 和 OR 結合使用，以實作更複雜的篩選條件。

使用範例

清單 3-9 展示了三個結合運算元的查詢範例。

SELECT *
FROM teachers
WHERE school = 'Myers Middle School'
AND salary < 40000;

SELECT *
FROM teachers
WHERE last_name = 'Cole'
OR last_name = 'Bush';

SELECT *
FROM teachers
WHERE school = 'F.D. Roosevelt HS'
AND (salary < 38000 OR salary > 40000);

內容解密：

  1. 第一個查詢使用 AND 連線兩個條件，尋找在 Myers Middle School 任教且薪水低於 $40,000 的老師。
  2. 第二個查詢使用 OR 尋找姓氏為 “Cole” 或 “Bush” 的老師。
  3. 第三個查詢結合了 AND 和 OR，並使用了括號來確保正確的評估順序。它尋找在 F.D. Roosevelt HS 任教且薪水低於 $38,000 或高於 $40,000 的老師。
玄貓 BlackCat

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。