基本的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;
列出觸發器
- 列出資料庫所有觸發器
SELECT name FROM sqlite_master
WHERE type = 'trigger';
- 列出特定表觸發器
SELECT name FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'tablename';
刪除觸發器
DROP TRIGGER trigger_name;
例項
- 建立COMPANY表
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
- 建立AUDIT表
CREATE TABLE AUDIT(
EMP_ID INT NOT NULL,
ENTRY_DATE TEXT NOT NULL
);
- 在 COMPANY 表上建立一個觸發器
CREATE TRIGGER audit_log AFTER INSERT
ON COMPANY
BEGIN
INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;
- 在 COMPANY 表中建立一個記錄，
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );
- 結果
COMPANY表
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 1 | Paul | 32 | California | 20000.0 |
AUDIT 表
| EMP_ID | ENTRY_DATE |
|---|---|
| 1 | 2013-04-05 06:26:00 |
事務
- 事務定義了一組SQL命令的邊界，這一組命令或者作為一個整體被全部執行，或者全部不執行，這被稱作為資料庫完整性的原子性原則。
- 預設情況下，SQLite中每條命令都會自成一個事務，自動提交或回滾，此操作模式稱為隱式事務或者自動提交模式。
- 事務控制命令只與 DML 命令 INSERT、UPDATE 和 DELETE 一起使用。他們不能在建立表或刪除表時使用，因為這些操作在資料庫中是自動提交的。
開啟事務命令
- 事務（Transaction）可以使用 BEGIN TRANSACTION 命令或簡單的 BEGIN 命令來啟動。此類事務通常會持續執行下去，直到遇到下一個 COMMIT 或 ROLLBACK 命令。不過在資料庫關閉或發生錯誤時，事務處理也會回滾。
BEGIN;
或者
BEGIN TRANSACTION
提交事務命令
- 用於把事務呼叫的更改儲存到資料庫中的事務命令。
- 把自上次 COMMIT 或 ROLLBACK 命令以來的所有事務儲存到資料庫。
COMMIT
或者
END TRANSACTION
回滾命令
- 用於撤消尚未儲存到資料庫的事務的事務命令。
- 只能用於撤銷自上次發出 COMMIT 或 ROLLBACK 命令以來的事務。
ROLLBACK
例項
- 刪除後再回滾，記錄還在
BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
ROLLBACK;
- 刪除後提交，刪除成功，記錄已被刪除
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等語句。