使用 SQL 讀取資料庫並在其中寫入資料

1. 事前準備

您使用的許多應用程式都會直接在裝置上儲存資料。舉例來說，「時鐘」會保存週期性鬧鐘，「Google 地圖」會儲存最近搜尋內容的清單，而「聯絡人」可新增、編輯與移除聯絡人資訊。

資料持續性意指在裝置上儲存或保存資料，是 Android 開發工作中重要的一環。持續性資料可確保使用者產生的內容不會在應用程式關閉時遺失，也可確保從網際網路下載的資料妥善儲存，這樣之後就不必重新下載資料。

Android 應用程式經常透過 Android SDK 提供的 SQLite 保存資料。SQLite 提供了關聯資料庫，可讓您以類似使用 Kotlin 類別建立資料結構的方式呈現資料。本程式碼研究室將說明 SQL (結構化查詢語言) 的基礎知識。SQL 雖然不是實際的程式設計語言，但可讓您只編寫幾行程式碼，就輕鬆靈活地讀取及修改 SQLite 資料庫。

瞭解 SQL 基礎知識後，您即可在本單元的後續部分中使用 Room 程式庫，為應用程式加入持續性機制。

2. 關聯資料庫的重要概念

什麼是資料庫？

如果您很熟悉 Google 試算表等試算表程式，就已經瞭解資料庫的基本類比。

試算表是由同一活頁簿中的個別資料表或單個試算表組成。

1f2b00d3ca083c4a.png

每份資料表均包含欄和列，欄用來定義資料代表的內容，列則用於呈現個別項目，內含各欄的值。以下圖為例，您可將各欄內容分別定義為學生的 ID、名字、主修科系和成績。

a441da5cc7be346b.png

每列都含有一位學生在各欄的資料值。

6131d8a59996f521.png

關聯資料庫採用相同的運作方式。

  • 針對您想呈現的資料，資料表定義了高階分組方式，就像上方試算表畫面中的「student」和「professor」工作表。
  • 欄則定義資料表中每列包含的資料。
  • 列包含資料表中每欄的值所構成的實際資料。

如果您已瞭解 Kotlin 中的類別和物件，同樣概念也適用於關聯資料庫的架構。

data class Student(
    id: Int,
    name: String,
    major: String,
    gpa: Double
)
  • 類別好比資料表，用於建立您想在應用程式中呈現的資料結構。
  • 屬性好比資料欄，定義了類別的每個例項應包含的特定資料內容。
  • 物件好比資料列，屬於實際資料。物件含有類別中所定義每個屬性的值，就像資料列含有資料表中所定義各欄的值一樣。

正如試算表可以包含多份工作表，應用程式也能包含多種類別，而資料庫則可包含多份資料表。如果資料庫能夠建立資料表之間的關係結構，便稱為關聯資料庫。舉例來說，一名研究生可以有一位博士論文指導教授，不過這位教授可以指導多名學生的博士論文。

7f1b56e05520dc3.png

關聯資料庫中的每份資料表都有一欄包含各列的專屬 ID，例如自動遞增的整數。這個 ID 稱為主鍵。

當某份資料表參照另一份資料表的主鍵時，則稱為外鍵。如果出現外鍵，就意味著兩份資料表有所關聯。

什麼是 SQLite？

SQLite 是常用的關聯資料庫。具體來說，SQLite 是指輕量化的 C 程式庫，用於以 SQL (有時唸成「sequel」) 管理關聯資料庫。

您不必瞭解 C 或任何全新的程式設計語言，就能使用關聯資料庫。有了 SQL，只需編寫幾行程式碼，就能輕鬆在關聯資料庫中新增及擷取資料。

使用 SQLite 呈現資料

您已熟悉 Kotlin 中的 Int 和 Boolean 等資料類型，而 SQLite 資料庫也會使用資料類型！資料表的欄必須包含特定資料類型。下表列出常見 Kotlin 資料類型與相等 SQLite 資料類型的對應關係。

Kotlin 資料類型

SQLite 資料類型

Int

INTEGER

String

VARCHAR 或 TEXT

Boolean

BOOLEAN

Float、Double

REAL

資料庫中的資料表以及各資料表內的欄統稱為「結構定義」。在下一個部分中，您將下載範例資料集，並進一步瞭解結構定義。

3. 下載範例資料集

這個程式碼研究室的資料庫適用於一個虛構的電子郵件應用程式。本程式碼研究室將列舉常見作業為例 (比如排序與篩選郵件，或者依主旨文字或寄件者進行搜尋)，展示 SQL 的所有強大功能。這些例子也可確保您在後續課程中使用 Room 前，先具備處理應用程式內各種可能情況的經驗。

請按這裡，從「SQL 基本資訊」程式碼研究室的 GitHub 存放區 compose 分支中下載範例專案。

使用資料庫檢查器

如要使用資料庫檢查器，請按照下列步驟操作：

  1. 在 Android Studio 中執行「SQL 基本資訊」程式碼研究室的應用程式。應用程式啟動後，會顯示以下畫面。

76e94dfe2234c2b1.png

  1. 在 Android Studio 中，依序點選「View」>「Tool Windows」>「App Inspection」。

cd5dd859d31cbab3.png

畫面底部現在會出現標示為「App Inspection」的新分頁，且已選取「Database Inspector」分頁標籤。另外還有兩個您不需使用的分頁。載入可能需要幾秒鐘的時間，但載入後，畫面左側會顯示含有資料表的清單，供您選取以執行查詢。

5ace24ac5cc15abc.png

  1. 按一下「Open New Query Tab」按鈕開啟窗格，即可對資料庫執行查詢。

277ecff401ca5f1a.png

email 資料表含有 7 個欄：

  • id：主鍵。
  • subject：電子郵件主旨行。
  • sender：電子郵件的來源電子郵件地址。
  • folder：郵件所在的資料夾，例如收件匣或垃圾郵件匣。
  • starred：使用者是否為電子郵件加上星號。
  • read：使用者是否閱讀電子郵件。
  • received：收到電子郵件當下的時間戳記。

4. 使用 SELECT 陳述式讀取資料

SQL SELECT 陳述式

SQL 陳述式 (有時稱為查詢) 的用途為讀取或操控資料庫。

您可以使用 SELECT 陳述式讀取 SQLite 資料庫中的資料。簡單的 SELECT 陳述式包含 SELECT 關鍵字，後方依序為資料欄名稱、FROM 關鍵字和資料表名稱。每個 SQL 陳述式皆以半形分號 (;) 結尾。

2d7ff99736b072b9.png

SELECT 陳述式也可以傳回多個欄中的資料。您必須使用半形逗號分隔資料欄名稱。

cf94edd5de825043.png

如要選取資料表中的所有欄，請將資料欄名稱替換為萬用字元 (*)。

fb75d3033c59949a.png

無論選取特定或所有資料欄，這類簡單的 SELECT 陳述式都會傳回資料表中的每一列。您只需指定要傳回的資料欄名稱。

使用 SELECT 陳述式讀取電子郵件資料

電子郵件應用程式需執行的其中一項主要作業是顯示郵件清單。透過 SQL 資料庫，您可以使用 SELECT 陳述式取得這類資訊。

  1. 確認已在「Database Inspector」分頁中選取「email」資料表。

ffc77f938ea09071.png

  1. 首先嘗試選取 email 資料表中每列的所有欄。
SELECT * FROM email;
  1. 按一下文字方塊右下角的「Run」按鈕。您會發現此查詢傳回整份 email 資料表。

4c3ea237c6ed2b57.png

  1. 接著嘗試只選取每列的主旨。
SELECT subject FROM email;
  1. 請注意，此查詢會再次傳回所有列，但只有特定一欄的值。

69a20935721dcc2.png

  1. 您也可以選取多個欄。請嘗試選取主旨和寄件者。
SELECT subject, sender FROM email;
  1. 您會發現此查詢傳回 email 資料表中的所有列，但只有「subject」和「sender」欄的值。

4ae739dad54397ea.png

恭喜！您成功執行了第一個查詢，做得好！您已經踏出學習 SQL 陳述式的第一步。

如要編寫更具體的 SELECT 陳述式，您可以新增子句，用於指定部分資料，甚至變更輸出內容的格式。在後續章節中，您將瞭解常用的 SELECT 陳述式子句，並學習如何設定資料格式。

5. 使用 SELECT 陳述式搭配匯總函式及 DISTINCT 關鍵字

使用匯總函式減少從資料欄傳回的值

SQL 陳述式不只可以傳回資料列。SQL 提供多種函式，可對特定資料欄執行作業或運算程序，例如找出最大值，或者計算特定一欄可能的不重複值數量。這類函式稱為匯總函式。您可以從特定資料欄中傳回一個值，而不是傳回該欄的所有資料。

以下列舉一些 SQL 匯總函式：

  • COUNT()：傳回符合查詢的資料列總數。
  • SUM()：將所選資料欄中每列的值相加，傳回總和。
  • AVG()：傳回所選資料欄中所有值的平均值。
  • MIN()：傳回所選資料欄中的最小值。
  • MAX()：傳回所選資料欄中的最大值。

您可以不直接使用資料欄名稱，改為呼叫匯總函式，並在半形括號中傳入資料欄名稱做為引數。

6730a62d583a0d9.png

呼叫匯總函式只會傳回一個值，而非資料表中每列在該欄的值。

如果不需讀取資料庫中的所有資料，即可使用匯總函式，有效率地計算特定值。舉例來說，您可以找出某欄中所有值的平均值，不必將整個資料庫載入清單並手動計算。

以下是實際對 email 資料表使用匯總函式的例子：

  1. 應用程式可能會需要取得所收到電子郵件的總數。如要計算這個值，您可以使用 COUNT() 函式和萬用字元 (*)。
SELECT COUNT(*) FROM email;
  1. 此查詢會傳回一個值。您可以完全使用 SQL 查詢執行這項操作，不必透過任何 Kotlin 程式碼手動計算資料列數量。

5d49b987545184bb.png

  1. 如要取得收到最新郵件的時間，您可以對「received」欄使用 MAX() 函式，因為最新 Unix 時間戳記的數字最大。
SELECT MAX(received) FROM email;
  1. 此查詢會傳回一項結果，也就是「received」欄中數字最大 (意即最新) 的時間戳記。

d0241dce845c3955.png

使用 DISTINCT 篩選重複結果

選取資料欄時，您可以在前方加上 DISTINCT 關鍵字。如要從查詢結果中移除重複內容，這種做法就能派上用場。

4f02533256302f26.png

舉例來說，許多電子郵件應用程式都提供自動完成地址的功能，而且您可能會想在一份清單中加入並顯示所有寄件者地址。

  1. 執行以下查詢，傳回每列在「sender」欄的值。
SELECT sender FROM email;
  1. 您會發現結果中包含許多重複內容。對使用者而言，這絕對不是理想的體驗！

4f0489d1668dbede.png

  1. 在「sender」欄前方加上 DISTINCT 關鍵字，然後重新執行查詢。
SELECT DISTINCT sender FROM email;
  1. 請注意，現在結果中包含較少內容，而且每個值都不重複。

43a47ad8d18fee6e.png

您也可以在匯總函式中的欄名稱前方加上 DISTINCT 關鍵字。

55c45cb9c258e882.png

假設您想知道資料庫中不重複寄件者的人數，可以使用 COUNT() 匯總函式，並在資料欄名稱 sender 前方加上 DISTINCT 關鍵字，這樣就能計算該人數。

  1. 執行 SELECT 陳述式，將 DISTINCT sender 傳入 COUNT() 函式。
SELECT COUNT(DISTINCT sender) FROM email;
  1. 您會發現此查詢傳回 14 位不重複寄件者。

19ae43b0bc9a927e.png

6. 使用 WHERE 子句篩選查詢

在許多電子郵件應用程式中，使用者可以指定資料、搜尋字詞、資料夾、寄件者等特定篩選條件，只顯示相符郵件。針對這類用途，您可以在 SELECT 查詢中加入 WHERE 子句。

請在資料表名稱後方新增一行，然後依序添加 WHERE 關鍵字和運算式。編寫較複雜的 SQL 查詢時，為了方便閱讀，每行輸入一個子句是很常見的做法。

707b0641aa2de0f.png

此查詢會為所選各列執行布林值檢查。如果檢查傳回 true，資料列就會納入查詢結果；如果檢查傳回 false，則資料列不會納入查詢結果。

舉例來說，電子郵件應用程式可能會提供垃圾郵件、垃圾桶、草稿的篩選器，或者讓使用者自行建立篩選器。以下說明如何使用 WHERE 子句執行這項操作：

  1. 執行 SELECT 陳述式，傳回 email 資料表中的所有欄 (*)，並加上 WHERE 子句檢查以下條件：folder = 'inbox'。這並不是輸入錯誤，您可以在 SQL 中使用單等號檢查內容是否相同，並利用單引號 (而非雙引號) 表示字串值。
SELECT * FROM email
WHERE folder = 'inbox';
  1. 查詢結果只會傳回使用者收件匣中郵件的資料列。

6e9f2a17186d7faa.png

在 WHERE 子句中使用邏輯運算子

SQL WHERE 子句可加入多個運算式。如果希望結果只包含同時符合兩項條件的資料列，可以使用 AND 關鍵字，這等同於 Kotlin and 運算子 (&&)。

d8a698416e55d11b.png

此外，如果希望結果包含符合任一條件的資料列，可以使用 OR 關鍵字，這等同於 Kotlin or 運算子 (||)。

f3cecac289e7650d.png

為了方便閱讀，您也可以使用 NOT 關鍵字否定運算式。

27300a0a38ef0343.png

許多電子郵件應用程式都允許使用多個篩選器，例如只顯示未讀取的郵件。

請嘗試對 email 資料表使用下列較複雜的 WHERE 子句：

  1. 除了只傳回使用者收件匣中的郵件外，也可以嘗試限制查詢結果只包含未讀取的郵件，這類郵件在「read」欄的值是 false。
SELECT * FROM email
WHERE folder = 'inbox' AND read = false;
  1. 執行此查詢後，您會發現結果只包含使用者收件匣中未讀取的電子郵件。

d9ebd307a146d320.png

  1. 傳回位於 important 資料夾 OR 已加星號 (starred = true) 的所有電子郵件。如此一來，各個資料夾中的電子郵件只要已加星號，就會納入結果中。
SELECT * FROM email
WHERE folder = 'important' OR starred = true;
  1. 查看結果。

fd2f0dc7b6444956.png

使用 LIKE 搜尋文字

WHERE 子句有一項非常實用的功能，那就是搜尋特定資料欄中的文字。如果要這麼做，請指定資料欄名稱，後方依序加上 LIKE 關鍵字和搜尋字串。

6692c0d491b6f9af.png

搜尋字串的開頭是百分比符號 (%)，後方輸入要搜尋的文字 (搜尋字詞)，最後再加上另一個百分比符號 (%)。

c69c15f654645ee2.png

如要搜尋前置字串 (以特定文字開頭的結果)，請省略第一個百分比符號 (%)。

fbe6a94daaf173ae.png

此外，如要搜尋後置字串，請省略最後的百分比符號 (%)。

141f567c9cbc4029.png

應用程式可將文字搜尋功能用於許多用途，例如搜尋主旨行包含特定文字的電子郵件，或者在使用者輸入內容時更新自動完成建議。

如要在查詢 email 資料表時使用文字搜尋功能，請按照下列說明操作。

  1. 這個資料庫中的寄件者和莎士比亞筆下的角色一樣，都喜歡談論傻瓜 (fool)。請執行以下查詢，取得主旨行包含「fool」一詞的電子郵件總數。
SELECT COUNT(*) FROM email
WHERE subject LIKE '%fool%';
  1. 查看結果。

fd2ff96969824b0d.png

  1. 執行以下查詢，傳回主旨結尾為「fool」一詞的所有資料列，當中包含每欄的值。
SELECT * FROM email
WHERE subject LIKE '%fool';
  1. 您會發現此查詢傳回兩個資料列。

a23379e507e39c0b.png

  1. 執行以下查詢，傳回 sender 欄中開頭為字母 h 的不重複值。
SELECT DISTINCT sender FROM email
WHERE sender LIKE 'h%';
  1. 您會發現此查詢傳回三個值：helena@example.com、hyppolytus@example.com 和 hermia@example.com。

47ada07aee5cd8d9.png