1. SQLite 視圖（View）
2. SQLite 觸發器（Trigger）
3. SQLite 索引（Index）
3.1 單列索引
3.2 唯一索引
3.3 組合索引
3.4 隱式索引
3.5 什麼情況下要避免使用索引？
4. SQLite 事務（Transaction）
4.1 事務的屬性（ ACID）
4.2 事務控制
5. SQLite 約束
6. SQLite 子查詢
6.1 SELECT 子查詢使用
6.2 INSERT 子查詢使用
6.3 UPDATE 子查詢使用
6.4 DELETE 子查詢使用
1. SQLite 視圖（View）
視圖（View）只不過是通過相關的名稱存儲在數據庫中的一個 SQLite 語句。視圖（View）實際上是一個以預定義的 SQLite 查詢形式存在的表的組合。
視圖（View）可以包含一個表的所有行或從一個或多個表選定行。視圖（View）可以從一個或多個表創建，這取決於要創建視圖的 SQLite 查詢。
視圖（View）是一種虛表，允許用戶實現以下幾點：
- 用戶或用戶組查找結構數據的方式更自然或直觀。
- 限制數據訪問，用戶只能看到有限的數據，而不是完整的表。
- 彙總各種表中的數據，用於生成報告。
SQLite 視圖是隻讀的，因此可能無法在視圖上執行 DELETE、INSERT 或 UPDATE 語句。但是可以在視圖上創建一個觸發器，當嘗試 DELETE、INSERT 或 UPDATE 視圖時觸發，需要做的動作在觸發器內容中定義。
1.1 創建視圖
SQLite 的視圖是使用 CREATE VIEW 語句創建的。SQLite 視圖可以從一個單一的表、多個表或其他視圖創建。
CREATE VIEW 的基本語法如下：
CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];
您可以在 SELECT 語句中包含多個表，這與在正常的 SQL SELECT 查詢中的方式非常相似。如果使用了可選的 TEMP 或 TEMPORARY 關鍵字，則將在臨時數據庫中創建視圖。
1.2 刪除視圖
要刪除視圖，只需使用帶有 view_name 的 DROP VIEW 語句。DROP VIEW 的基本語法如下：
sqlite> DROP VIEW view_name;
下面的命令將刪除我們在前面創建的 COMPANY_VIEW 視圖：
sqlite> DROP VIEW COMPANY_VIEW;
2. SQLite 觸發器（Trigger）
SQLite 觸發器（Trigger）是數據庫的回調函數，它會在指定的數據庫事件發生時自動執行/調用。以下是關於 SQLite 的觸發器（Trigger）的要點：
- SQLite 的觸發器（Trigger）可以指定在特定的數據庫表發生 DELETE、INSERT 或 UPDATE 時觸發，或在一個或多個指定表的列發生更新時觸發。
- SQLite 只支持 FOR EACH ROW 觸發器（Trigger），沒有 FOR EACH STATEMENT 觸發器（Trigger）。因此，明確指定 FOR EACH ROW 是可選的。
- WHEN 子句和觸發器（Trigger）動作可能訪問使用表單 NEW.column-name 和 OLD.column-name 的引用插入、刪除或更新的行元素，其中 column-name 是從與觸發器關聯的表的列的名稱。
- 如果提供 WHEN 子句，則只針對 WHEN 子句為真的指定行執行 SQL 語句。如果沒有提供 WHEN 子句，則針對所有行執行 SQL 語句。
- BEFORE 或 AFTER 關鍵字決定何時執行觸發器動作，決定是在關聯行的插入、修改或刪除之前或者之後執行觸發器動作。
- 當觸發器相關聯的表刪除時，自動刪除觸發器（Trigger）。
- 要修改的表必須存在於同一數據庫中，作為觸發器被附加的表或視圖，且必須只使用 tablename，而不是 database.tablename。
- 一個特殊的 SQL 函數 RAISE() 可用於觸發器程序內拋出異常。
語法
創建 觸發器（Trigger） 的基本語法如下：
CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name
ON table_name
BEGIN
-- Trigger logic goes here....
END;
在這裡，event_name 可以是在所提到的表 table_name 上的 INSERT、DELETE 和 UPDATE 數據庫操作。您可以在表名後選擇指定 FOR EACH ROW。
以下是在 UPDATE 操作上在表的一個或多個指定列上創建觸發器（Trigger）的語法：
CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
BEGIN
-- Trigger logic goes here....
END;
3. SQLite 索引（Index）
索引（Index）是一種特殊的查找表，數據庫搜索引擎用來加快數據檢索。簡單地說，索引是一個指向表中數據的指針。一個數據庫中的索引與一本書後邊的索引是非常相似的。
例如，如果您想在一本討論某個話題的書中引用所有頁面，您首先需要指向索引，索引按字母順序列出了所有主題，然後指向一個或多個特定的頁碼。
索引有助於加快 SELECT 查詢和 WHERE 子句，但它會減慢使用 UPDATE 和 INSERT 語句時的數據輸入。索引可以創建或刪除，但不會影響數據。
使用 CREATE INDEX 語句創建索引，它允許命名索引，指定表及要索引的一列或多列，並指示索引是升序排列還是降序排列。
索引也可以是唯一的，與 UNIQUE 約束類似，在列上或列組合上防止重複條目。
CREATE INDEX 命令
CREATE INDEX 的基本語法如下：
CREATE INDEX index_name ON table_name;
3.1 單列索引
單列索引是一個只基於表的一個列上創建的索引。基本語法如下：
CREATE INDEX index_name
ON table_name (column_name);
3.3 唯一索引
使用唯一索引不僅是為了性能，同時也為了數據的完整性。唯一索引不允許任何重複的值插入到表中。基本語法如下：
CREATE UNIQUE INDEX index_name
on table_name (column_name);
3.3 組合索引
組合索引是基於一個表的兩個或多個列上創建的索引。基本語法如下：
CREATE INDEX index_name
on table_name (column1, column2);
是否要創建一個單列索引還是組合索引，要考慮到您在作為查詢過濾條件的 WHERE 子句中使用非常頻繁的列。
如果值使用到一個列，則選擇使用單列索引。如果在作為過濾的 WHERE 子句中有兩個或多個列經常使用，則選擇使用組合索引。
3.4 隱式索引
隱式索引是在創建對象時，由數據庫服務器自動創建的索引。索引自動創建為主鍵約束和唯一約束。
3.5 什麼情況下要避免使用索引？
雖然索引的目的在於提高數據庫的性能，但這裡有幾個情況需要避免使用索引。使用索引時，應重新考慮下列準則：
- 索引不應該使用在較小的表上。
- 索引不應該使用在有頻繁的大批量的更新或插入操作的表上。
- 索引不應該使用在含有大量的 NULL 值的列上。
- 索引不應該使用在頻繁操作的列上。
4. SQLite 事務（Transaction）
事務（Transaction）是一個對數據庫執行工作單元。事務（Transaction）是以邏輯順序完成的工作單位或序列，可以是由用戶手動操作完成，也可以是由某種數據庫程序自動完成。
事務（Transaction）是指一個或多個更改數據庫的擴展。例如，如果您正在創建一個記錄或者更新一個記錄或者從表中刪除一個記錄，那麼您正在該表上執行事務。重要的是要控制事務以確保數據的完整性和處理數據庫錯誤。
實際上，您可以把許多的 SQLite 查詢聯合成一組，把所有這些放在一起作為事務的一部分進行執行。
4.1 事務的屬性（ ACID）
事務（Transaction）具有以下四個標準屬性，通常根據首字母縮寫為 ACID：
- 原子性（Atomicity）：確保工作單位內的所有操作都成功完成，否則，事務會在出現故障時終止，之前的操作也會回滾到以前的狀態。
- 一致性（Consistency)：確保數據庫在成功提交的事務上正確地改變狀態。
- 隔離性（Isolation）：使事務操作相互獨立和透明。
- 持久性（Durability）：確保已提交事務的結果或效果在系統發生故障的情況下仍然存在。
4.2 事務控制
使用下面的命令來控制事務：
- BEGIN TRANSACTION：開始事務處理。
- COMMIT：保存更改，或者可以使用 END TRANSACTION 命令。
- ROLLBACK：回滾所做的更改。
事務控制命令只與 DML 命令 INSERT、UPDATE 和 DELETE 一起使用。他們不能在創建表或刪除表時使用，因為這些操作在數據庫中是自動提交的。
4.2.1 BEGIN TRANSACTION 命令
事務（Transaction）可以使用 BEGIN TRANSACTION 命令或簡單的 BEGIN 命令來啟動。此類事務通常會持續執行下去，直到遇到下一個 COMMIT 或 ROLLBACK 命令。不過在數據庫關閉或發生錯誤時，事務處理也會回滾。以下是啟動一個事務的簡單語法：
BEGIN;
or
BEGIN TRANSACTION;
4.2.2 COMMIT 命令
COMMIT 命令是用於把事務調用的更改保存到數據庫中的事務命令。
COMMIT 命令把自上次 COMMIT 或 ROLLBACK 命令以來的所有事務保存到數據庫。
COMMIT 命令的語法如下：
COMMIT;
or
END TRANSACTION;
4.2.3 ROLLBACK 命令
ROLLBACK 命令是用於撤消尚未保存到數據庫的事務的事務命令。
ROLLBACK 命令只能用於撤銷自上次發出 COMMIT 或 ROLLBACK 命令以來的事務。
ROLLBACK 命令的語法如下：
ROLLBACK;
5. SQLite 約束
約束是在表的數據列上強制執行的規則。這些是用來限制可以插入到表中的數據類型。這確保了數據庫中數據的準確性和可靠性。
約束可以是列級或表級。列級約束僅適用於列，表級約束被應用到整個表。
以下是在 SQLite 中常用的約束。
- NOT NULL 約束：確保某列不能有 NULL 值。
- DEFAULT 約束：當某列沒有指定值時，為該列提供默認值。
- UNIQUE 約束：確保某列中的所有值是不同的。
- PRIMARY Key 約束：唯一標識數據庫表中的各行/記錄。
- CHECK 約束：CHECK 約束確保某列中的所有值滿足一定條件。
6. SQLite 子查詢
子查詢或內部查詢或嵌套查詢是在另一個 SQLite 查詢內嵌入在 WHERE 子句中的查詢。
使用子查詢返回的數據將被用在主查詢中作為條件，以進一步限制要檢索的數據。
子查詢可以與 SELECT、INSERT、UPDATE 和 DELETE 語句一起使用，可伴隨著使用運算符如 =、、>=、<=、IN、BETWEEN 等。
以下是子查詢必須遵循的幾個規則：
- 子查詢必須用括號括起來。
- 子查詢在 SELECT 子句中只能有一個列，除非在主查詢中有多列，與子查詢的所選列進行比較。
- ORDER BY 不能用在子查詢中，雖然主查詢可以使用 ORDER BY。可以在子查詢中使用 GROUP BY，功能與 ORDER BY 相同。
- 子查詢返回多於一行，只能與多值運算符一起使用，如 IN 運算符。
- BETWEEN 運算符不能與子查詢一起使用，但是，BETWEEN 可在子查詢內使用。
6.1 SELECT 語句中的子查詢使用
子查詢通常與 SELECT 語句一起使用。基本語法如下：
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])
6.2 INSERT 語句中的子查詢使用
子查詢也可以與 INSERT 語句一起使用。INSERT 語句使用子查詢返回的數據插入到另一個表中。在子查詢中所選擇的數據可以用任何字符、日期或數字函數修改。
基本語法如下：
INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [ *|column1 [, column2 ]
FROM table1 [, table2 ]
[ WHERE VALUE OPERATOR ]
6.3 UPDATE 語句中的子查詢使用
子查詢可以與 UPDATE 語句結合使用。當通過 UPDATE 語句使用子查詢時，表中單個或多個列被更新。
基本語法如下：
UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
6.4 DELETE 語句中的子查詢使用
子查詢可以與 DELETE 語句結合使用，就像上面提到的其他語句一樣。
基本語法如下：
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
閱讀更多 架構師訓練營 的文章
