【知識點】SQLite3總結

• 2021 年 6 月 27 日
• 筆記
• 資料庫

基本的DDL、DML

創建表，包含日期欄位

CREATE TABLE  WordTable (id  integer PRIMARY KEY autoincrement , word  TEXT ,  updatedate datetime )  

插入一行，包含日期欄位

INSERT INTO WordTable (id,word,updatedate) VALUES (6,'ceshi','2020-08-11 13:42:29');

查找，包含日期欄位

SELECT * FROM WordTable  WHERE updatedate>='2020-08-11 10:42:29' AND updatedate<='2020-08-11 10:42:29'

查找，按照排序以及限制條目輸出

SELECT * FROM TX WHERE updatedate LIKE '%:%' ORDER BY updatedate ASC LIMIT 1  
---》DESC:降序 ASC:升序 
---》%：匹配任意長度字元串
---》_：匹配一個字元
---》LIMIT 1：限制輸出一條結果，必須在sql語句最後

刪除記錄

DELETE FROM table WHERE User='555'

更新數據

某一項數值加1

UPDATE User SET Logins=Logins+1 WHERE User='555'

更新多個欄位

UPDATE Tx SET Ip='11111',Name='2222' WHERE Uid='TTTTTTTTTTTTTTT1'

視圖

視圖（View） 是一種虛表，允許用戶實現以下幾點：

• 用戶或用戶組查找結構數據的方式更自然或直觀。
• 限制數據訪問，用戶只能看到有限的數據，而不是完整的表。
• 匯總各種表中的數據，用於生成報告。

SQLite 視圖內容是隨著實際數據表實時變化，是只讀的，因此可能無法在視圖上執行 DELETE、INSERT 或 UPDATE 語句。但是可以在視圖上創建一個觸發器，當嘗試 DELETE、INSERT 或 UPDATE 視圖時觸發，需要做的動作在觸發器內容中定義。

//創建
CREATE VIEW my_view AS SELECT Ip FROM TX
//刪除
DROP VIEW my_view

索引

原理

• 使用的是B-樹原理

影響

• 提高查詢的速度
• 創建索引需要耗費一定的時間
• 索引需要佔用物理空間
• 當對表中的數據進行增加、刪除和修改的時候，索引也要動態的維護，降低了數據的維護速度

使用場景

• 在經常需要搜索的列上創建
• 索引不應該使用在較小的表上。
• 索引不應該使用在有頻繁的大批量的更新或插入操作的表上。
• 索引不應該使用在含有大量的 NULL 值的列上。

單列索引

CREATE INDEX index_name ON table_name (column_name);

唯一索引
使用唯一索引不僅是為了性能，同時也為了數據的完整性。唯一索引不允許任何重複的值插入到表中。

CREATE UNIQUE INDEX index_name on table_name (column_name);

組合索引

CREATE INDEX index_name on table_name (column1, column2);

隱式索引

隱式索引是在創建對象時，由資料庫伺服器自動創建的索引。索引自動創建為主鍵約束和唯一約束。

刪除索引

DROP INDEX index_name;

如何正確使用索引
比如通過：CREATE INDEX comp_ind ON table1(x, y, z)創建索引，那麼x,xy,xyz都是前導列，而yz，y，z這樣的就不是。在WHERE子句中，前導列必須使用等於或者IN操作，最右邊的列可以使用不等式，這樣索引才可以完全生效。同時，WHERE子句中的列不需要全建立了索引，但是必須保證建立索引的列之間沒有間隙。
例子：

//創建索引
CREATE INDEX idx_ex1 ON ex1(a,b,c,d,e,...,y,z); 

//查詢語句
...WHERE a=5 AND b IN (1,2,3) AND c IS NULL AND d='hello' 
abcd四列都是有效的，因為只有等於和IN操作，並且是前導列。 

//查詢語句
... WHERE a=5 AND b IN (1,2,3) AND c>12 AND d='hello' 
那這裡只有a，b和c的索引會是有效的，d列的索引會失效，因為它在c列的右邊，而c列使用了不等式，根據使用不等式的限制，c列已經屬於最右邊。

//查詢語句
... WHERE b IN (1,2,3) AND c NOT NULL AND d='hello' 
索引將不會被使用，因為沒有使用前導列，這個查詢會是一個全表查詢。

//對於between，or，like語句，都無法使用索引

觸發器

SQLite 觸發器（Trigger） 是資料庫的回調函數，它會在指定的資料庫事件發生時自動執行/調用。
關鍵字

• new 代表剛創建的行，old剛刪除的行
• new在before觸發器中賦值，取值；在after觸發器中取值。
• new是新插入的數據，old是原來的數據
• insert只會有new，代表著要插入的新記錄
• delete只會有old，代表著要刪除的記錄

INSERT、DELETE 和 UPDATE創建觸發器的基本語法

CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name 
ON table_name
BEGIN
 -- 觸發器邏輯....
END;

UPDATE指定欄位創建觸發器語法（INSERT、DELETE沒有此特性）

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name 
ON table_name
BEGIN
 -- 觸發器邏輯....
END;

列出觸發器

1. 列出資料庫所有觸發器

SELECT name FROM sqlite_master
WHERE type = 'trigger';

2. 列出特定表觸發器

SELECT name FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'tablename';

刪除觸發器

DROP TRIGGER trigger_name;

實例

1. 創建COMPANY表

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

2. 創建AUDIT表

CREATE TABLE AUDIT(
    EMP_ID INT NOT NULL,
    ENTRY_DATE TEXT NOT NULL
);

3. 在 COMPANY 表上創建一個觸發器

CREATE TRIGGER audit_log AFTER INSERT 
ON COMPANY
BEGIN
   INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;

4. 在 COMPANY 表中創建一個記錄，

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

5. 結果

COMPANY表

AUDIT 表

事務

• 事務定義了一組SQL命令的邊界，這一組命令或者作為一個整體被全部執行，或者全部不執行，這被稱作為資料庫完整性的原子性原則。
• 默認情況下，SQLite中每條命令都會自成一個事務，自動提交或回滾，此操作模式稱為隱式事務或者自動提交模式。
• 事務控制命令只與 DML 命令 INSERT、UPDATE 和 DELETE 一起使用。他們不能在創建表或刪除表時使用，因為這些操作在資料庫中是自動提交的。

開啟事務命令

• 事務（Transaction）可以使用 BEGIN TRANSACTION 命令或簡單的 BEGIN 命令來啟動。此類事務通常會持續執行下去，直到遇到下一個 COMMIT 或 ROLLBACK 命令。不過在資料庫關閉或發生錯誤時，事務處理也會回滾。

BEGIN;
或者
BEGIN TRANSACTION

提交事務命令

• 用於把事務調用的更改保存到資料庫中的事務命令。
• 把自上次 COMMIT 或 ROLLBACK 命令以來的所有事務保存到資料庫。

COMMIT
或者
END TRANSACTION

回滾命令

• 用於撤消尚未保存到資料庫的事務的事務命令。
• 只能用於撤銷自上次發出 COMMIT 或 ROLLBACK 命令以來的事務。

ROLLBACK

實例

1. 刪除後再回滾，記錄還在

BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
ROLLBACK;

2. 刪除後提交，刪除成功，記錄已被刪除

BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
COMMIT;

專業術語

DML（Data Manipulation Lanaguage，數據操縱語言）
DML就是我們經常用到的SELECT、INSERT、UPDATE和DELETE語句，主要是用來對數據進行CRUD：增查改刪（create, read (retrieve), update, delete）操作。
DDL（Data Definition Language，數據定義語言）
DDL就是我們在創建表的時候用到的一些語句，比如說CREATE、ALTER、DROP等。DDL主要是用在定義或改變表的結構、數據類型、表之間的鏈接或約束等初始化工作上。
DCL（Data Control Language，資料庫控制語言）
DCL是用來設置或更改資料庫用戶或角色許可權的語句，包括GRANT、DENY、REVOKE等語句。