Android筆記(38)-SQL基礎

瞭解關聯資料庫(relational databases)的基本概念，並練習使用 Android Studio 中的資料庫檢查器(Database Inspector)執行 SQL 查詢(queries)。

學習目標

關聯資料庫(relational database)的結構：資料表(tables)、資料欄(columns) 和 資料列(rows)
包含 WHERE、ORDER BY、GROUP BY 和 LIMIT 子句的 SELECT 陳述式
如何使用 SQL insert、update 及 delete 資料列(rows)

關聯資料庫總覽

什麼是關聯資料庫(relational database)？

在運算中，資料庫(database)只是可用電子方式存取和寫入的結構化資料集合。資料庫(database)可使用 Kotlin 儲存任何可在 app 中呈現的相同資訊。在行動裝置(mobile devices)上，資料庫(database)通常會用於儲存執行中 app 的資料，如此一來，下次開啟 app 時即可存取該資料，而無須從網際網路等其他來源擷取資料。這就是所謂的資料持續性(data persistence)。

談論資料持續性(data persistence)時，通常會聽到關聯資料庫(relational database)一詞。關聯資料庫(relational database)是一種常見的資料庫，可將資料整理成資料表(tables)、資料欄(columns)和資料列(rows)。編寫 Kotlin 程式碼時，您會建立代表 objects 的 classes。關聯資料庫中的 table 運作方式相同。除了呈現資料之外，table 也可以引用其他 table，以便為 table 建立關聯。常見的範例包括「學生(students)」、「老師(teachers)」和「課程(course)」資料表。課程可能只有一位老師，但學生可能會有許多課程。database 可呈現這些 table 之間的關係，因此您會經常聽到 relational database 一詞。

tables 間的關係可呈現實際關係(real world relationships)。

Tables、columns 和 rows

建立 relational database 的第一步就是定義 tables，或您要呈現的 data。您也必須思考各 table 中要儲存哪些特定資訊。特定屬性會以 columns 表示。Columns 是由 name 和 data type 所組成。您已經熟悉在 Kotlin 中使用 classes 的屬性。您也可以用相同方式看待 SQL tables。Table 就如同 class 定義，用於說明您想要呈現的「內容(thing)」type。Column 是 table 中每個項目所建立「內容(thing)」的特定屬性。

植物(Plant)
id	INTEGER
species	TEXT (string)
name	TEXT (string)
color	TEXT (string)

花園(Garden)
id	INTEGER
name	TEXT (string)
length	INTEGER
width	INTEGER

個別 table 項目(entries)稱為 rows。這就如同 Kotlin 中 class 的 instance。每個 row 的資料都會與各個 column 相對應。Table 會提供範本，但這些 rows 會定義 table 中實際儲存的資料。

id	species	name	color
1	Camellia Sinensis	Tea Plant	green
2	Echinacea Purpurea	Purple Coneflower	purple
3	Ferula Foetida	Asafoetida	green

主鍵(Primary Key)

在上述範例中，請留意 id 屬性的 column 形式。自然界中的植物物種(plant species)或您在 database 中呈現的任何內容，可能沒有方便編號的 id，但 data table 中的 rows 必須具有某種唯一識別碼(unique identifier)。這通常稱為主鍵( primary key)，而且 table 中的每一列(row)皆不得重複。當您需引用其他 tables 內其中一個 data table 的 row 時，這項功能就非常實用。

舉例來說，還有另一個名為 garden 的 table，您希望將 garden 與其中所有的植物物種(plant species)建立關聯。您可以使用 plants table 的 primary key，從 garden table 中的項目(entry)或 database 中的其他 table 引用 plant。

Primary keys 可在 relational database 中建立關係(relationships)。在本課程中，雖然您不會使用含有多個 table 的 database，但具有 unique ID 可協助查詢(querying)、更新(updating)及刪除(deleting) table 中的現有項目。

Data Types

如同定義 Kotlin classes 的屬性，database 中的 columns 可為多種 data types 的其中一種。Column 可以呈現 character、string、number(帶或不帶小數)，或 binary data。根據用途，其他 data (例如 dates 和 times) 可以用 numerically 或 string 表示。使用 Room 時，您主要使用的是 Kotlin types，但是其會對應至幕後的 SQL types。

注意：如同在 Kotlin 中，database 中的 columns 可能含有不同的 data types。這些 types 與 Kotlin 中的 data types 不太一樣。如需更多資訊，請參閱這項資源，以瞭解 SQL 中的 basic data types。不過，在 Room 中使用 database 時，您無法直接使用這些 data types，而是使用同等的 Kotlin data types，在程式碼中定義 data tables。

SQL

無論是自行或使用 Room 等 library 存取 relational database，您都需要使用 SQL。

什麼是 SQL？SQL (有時發音為「sequel」) 代表 Structured Query Language(結構化查詢語言)，可讓您讀取和操控 relational database 中的 data。但別擔心，您不需要為了在 app 中實作持續性(persistence)，而學習全新的程式設計語言。不同於 Kotlin 等程式設計語言，SQL 只包含幾種 statements，用於讀取(reading)及寫入(writing)資料庫。瞭解基本格式後，只需在空格中填入您從 database 讀取(reading)或寫入(writing)的特定資訊即可。

以下為最常見的 SQL statements，以及您要使用的 statements。

SELECT	從 data table 取得特定資訊，並透過多種方式篩選(filtered)及排序(sorted)結果。
INSERT	在 table 中新增 row。
UPDATE	更新 table 中的現有 row。
DELETE	從 table 中移除現有的 row。

現在，您必須先具備 database，才能在 SQL 中執行任何操作。在下一個畫面中，您將會進行範例專案設定，其包含 database，可供您執行 SQL 查詢(queries)。

範例程式碼 - Parks database

將要下載的範例程式碼與先前程式碼研究室的程式碼略有不同。我們會提供簡易的 Android Studio 專案，其可建立 database 讓您用於執行 SQL 查詢(queries)，而非在現有專案上進行建構。執行 app 一次後，您即可使用名為資料庫檢查器(Database Inspector)的 Android Studio tool 存取 database。

範例程式碼網址：https://github.com/google-developer-training/android-basics-kotlin-sql-basics-app
分支版本：main

在前往下一個部分之前，請先完成下列步驟，確保您已透過範例專案完成相關設定。

執行 application。app 應會顯示如下所示的單一畫面。

在 Android Studio 中，前往「View」>「Tool Windows」>「Database Inspector」，開啟 Database Inspector。
您應該會在底部看到標示「Database Inspector」的新分頁標籤(tab)。載入可能需要幾秒鐘的時間，但您可在左側看到含有 data tables 的 list，您可選取以執行查詢(queries)。

注意：如果您使用 Android Studio 2020.3.1 Arctic Fox，可前往「View」>「Tool」>「App Inspection」存取 Database Inspector。

基本 SELECT statements

針對下列練習，您必須在資料庫檢查器(Database Inspector)中執行查詢。請務必從左側窗格中選取正確的資料表 (park)，然後按一下「Open New Query Tab」按鈕，接著就會看到可輸入 SQL 指令的文字方塊。

SQL statement 是用於存取(accesses) (讀取(reading)或寫入(writing)) database 的指令，類似程式碼行。在 SQL 中，您可執行的最簡單操作為取得 table 中的所有 data。為此，請先使用 SELECT 字詞，代表您要讀取 data。接著加上星號 (*)。您可以在此處指定要選取的 columns，使用 * 代表選取所有資料欄。然後使用 FROM 關鍵字，後面加上 data table 的名稱 (park)。在 Database Inspector 中執行下列指令，並觀察整個 table 的所有 columns 與 rows。

1	SELECT * FROM park

如果您只想選取特定 column，而非 table 中的所有 columns，您可以指定 column 名稱。

1	SELECT city FROM park

您也可以選取多個特定 columns，並以半形逗號分隔。

1	SELECT name, established, city FROM park

有時候，您不一定要選取 database 中的所有 rows。您可以新增 SQL statement 的子句部分，以進一步縮小結果範圍。

其中一個子句是 LIMIT，可讓您限制傳回的 rows 數量。因此，以下查詢只會傳回前五個，不會傳回全部 23 個結果。

1 2	SELECT name FROM park LIMIT 5

最常見且實用的子句之一就是 WHERE 子句。WHERE 子句可讓您根據一或多個 columns 篩選結果。

1 2	SELECT name FROM park WHERE type = "national_park"

注意：有別於 Kotlin，由於 = 運算子用於指派，而 == 則用於比較，因此在 SQL 中只能使用一個等號來比較兩個值。

此外，也有「不等於」(!=) 運算子。以下查詢會列出不屬於 recreation_area，且佔地超過 100,000 英畝的所有公園。使用 WHERE 子句時，您也可以使用 AND 或 OR 等 Boolean 運算子，以新增多個條件。

1
2
3

SELECT name FROM park
WHERE type != "recreation_area"
AND area_acres > 100000

練習

SQL 查詢相當適合回答各種資料相關問題，而最佳的練習做法就是自行編寫查詢。接下來幾個步驟，您將可編寫查詢，以回答特定問題。請務必先在 Database Inspector 中進行測試，再繼續進行操作。

問題 1：
請編寫 SQL 查詢，取得訪客(visitors)少於 1,000,000 人的所有 parks 的 names。

1	SELECT name from park WHERE park_visitors < 1000000

常見的 SQL 函式

您編寫的第一個查詢只會傳回 database 中的所有 rows。

1	SELECT * FROM park

不過，您可能不希望傳回冗長的 results list。SQL 也提供匯總函式，可協助您將 data 縮減為單一個有意義的值。舉例來說，假設您想知道 park table 中的 rows 數量。如果您不使用 SELECT * ...，而是改用 COUNT() 函式並傳遞 * (所有 rows) 或 column 名稱，查詢將會傳回所有 rows 的數量。

1	SELECT COUNT(*) FROM park

另一個實用的匯總函式為 SUM() 函式，可用於加總 columns 中的值。此查詢只會篩選 national parks (由於這是 park_visitors column 非空值的唯一項目(entries))，並將每個 park 的總訪客(visitors)數加總。

1 2	SELECT SUM(park_visitors) FROM park WHERE type = "national_park"

值得注意的是，您仍然可以在空值中使用 SUM()，但值將會視為零。以下查詢會傳回上述相同結果。但仍建議您盡量具體指定，以避免在應用程式中使用 SQL 時出現錯誤。

1	SELECT SUM(park_visitors) FROM park

除了匯總值之外，還提供其他實用的函式 (例如 MAX() 和 MIN())，以分別取得最大或最小值。

1 2	SELECT MAX(area_acres) FROM park WHERE type = 'national_park'

取得 DISTINCT 值

您可能會注意到，在某些 rows 中，此欄與其他 rows 的值相同。舉例來說，type column 值的數量有限。您可以使用 DISTINCT 關鍵字，排除查詢結果中的重複值。例如，如要取得 type column 的所有不重複值，您可以使用以下查詢。

1	SELECT DISTINCT type FROM park

您也可以在匯總函式中使用 DISTINCT，如此即可直接傳回計數(count)，而不必列出不重複的 type 並自行計算。

1	SELECT COUNT(DISTINCT type) FROM park

練習

問題 2：
請編寫 SQL 查詢，取得 park table 中不同城市(distinct cities)的數量

1	SELECT COUNT(DISTINCT city) FROM park

問題 3：
請編寫 SQL 查詢，取得 San Francisco parks 的遊客(visitors)總數。

1	SELECT SUM(park_visitors) FROM park WHERE city = "San Francisco"

排序(ordering)及分組(grouping)查詢結果

在先前的範例中，要找出特定項目(entry)並不容易。所幸，您也可以使用 ORDER BY 子句排序 SELECT statement 的結果。您可在 WHERE 子句後 (如果有) 的查詢結尾新增 ORDER BY 子句，並指定要做為排序依據的 column 名稱。以下範例會取得 database 中每個 park 的名稱，並依字母順序排列結果。

1 2	SELECT name FROM park ORDER BY name

根據預設，系統會按遞增順序排序結果，但您可以將 ASC 或 DESC keyword 新增至 Order by 子句，以遞增或遞減順序加以排序。您不需要指定 ASC，因為第一個查詢會以遞增順序列出結果，但如要以遞減順序取得結果，請將 DESC keyword 新增至 ORDER BY 子句結尾。

1 2	SELECT name FROM park ORDER BY name DESC

您還可以依 column 將結果分組(group)，以便閱讀。在 ORDER BY 子句 (如果有) 前，您可以視需要指定 GROUP BY 子句和 column。這會將結果分割為 GROUP BY 中 column 特定的子集，每一欄(column)的結果會根據查詢的其餘部分進行篩選(filtered)及排序(ordered)。

1
2
3

SELECT type, name FROM park
GROUP BY type
ORDER BY name

這最容易透過範例理解。您不需計算 database 中的所有 parks 數量，即可得知每個 type 的 parks 數量，並取得每種 type 的個別數量(separate count)。

1
2
3

SELECT type, COUNT(*) FROM park
GROUP BY type
ORDER BY type

練習

問題 4：
查詢排名前 5 的 park name，和其遊客(visitors)最多的遊客數量(visitor count)(按遞減排序) 編寫一個 SQL 查詢。

1
2
3

SELECT name, park_visitors FROM park
ORDER BY park_visitors DESC
LIMIT 5

插入及刪除 rows

您必須能夠寫入 data，才能使用 Room 完整發揮 Android 的持續性資料功能。除了查詢 database 之外，您也可以使用 SQL statements 插入(inserting)、更新(updating)及刪除(deleting) rows。稍後在課程 2 中學習使用 Room 寫入資料時，將會需要具備此基本知識。

INSERT statement

如要新增 row，請使用 INSERT statement。INSERT statement 後方是 INTO keyword，以及您想新增 row 的 table 名稱。在 VALUES keyword 之後，您必須將每個 column 的值加上括號 ()，每個值以半形逗號隔開。INSERT statement 的格式如下。

1 2	INSERT INTO table_name VALUES (column1, column2, ...)

如要在 park table 中新增 row，INSERT statement 將如下所示。這些值的順序會和為 park table 所定義的 columns 順序相符。請注意，系統不會指定部分 data。這目前沒關係，在您插入(inserted) row 後，可隨時更新(update) row。

1 2	INSERT INTO park VALUES (null, 'Googleplex', 'Mountain View', 12, null, 0, '')

另請注意，您針對 ID 傳入 null。雖然可以提供特定編號，但這實際上並不便利，因為 app 必須追蹤最新的 ID，確保無重複。但是，您可以設定 database，使主鍵(primary key)自動增加，如此處操作。如此一來，當您傳入 null 時，系統就會自動選擇下一個 ID。

確認已使用 WHERE 子句建立項目，以指定名為 “Googleplex” 的 park。

1 2	SELECT * FROM park WHERE name = 'Googleplex'

UPDATE statement

Row 建立後，您可以隨時變更其中的內容。使用 UPDATE statement 即可進行變更。如同您看過的所有其他 SQL statement，您必須先指定 table 名稱。在 SET 子句中，只需設定您想要變更為新值的 column 即可。

UPDATE table_name
SET column1 = ...,
column2 = ...,
...
WHERE column_name = ...
...

如果是 Googleplex 項目(entry)，系統會更新現有的屬性，並在其他欄位(fields)填入內容 (這些欄位(fields)先前具有值，但為空白字串(empty string) "")。您可以使用 UPDATE statement 一次更新多個 (或全部) 欄位(fields)。

UPDATE park
SET area_acres = 46,
established = 1088640000,
type = 'office'
WHERE name = 'Googleplex'

查看查詢結果中反映的更新

1 2	SELECT * FROM park WHERE name = 'Googleplex'

DELETE statement

最後，您也可以使用 SQL 指令刪除 database 中的 rows。請再次指定 table 名稱，如同使用 SELECT statement，您可以使用 WHERE 子句為要刪除的 rows 提供條件。由於 WHERE 子句可以比對多個 rows，因此只需一個指令就能刪除多個 rows。

1 2	DELETE FROM table_name WHERE <column_name> = ...

由於 Googleplex 並非 national park，請嘗試使用 DELETE statement 從 database 中移除此項目。

1 2	DELETE FROM park WHERE name = 'Googleplex'

進行驗證，確認使用 SELECT statement 刪除 rows。查詢不應傳回任何結果，代表所有名為「Googleplex」的 rows 都已成功刪除。

1 2	SELECT * FROM park WHERE name = 'Googleplex'

這些就是插入(inserting)、更新(updating)及刪除(deleting) data 的所有步驟。您只需瞭解要執行 SQL 指令的格式，並指定符合 database 中 columns 的值。我們在下一個程式碼研究室中介紹 Room 時，重點在於從 database 進行讀取(reading)。插入(inserting)、更新(updating)及刪除(deleting) data 的步驟將於課程 2 中詳細說明。