總結

命令

終端命令

sqlite3 [databaseName.db]
sqlite3 test.db .dump > db.sql
sqlite3 test.db < db.sql

sqlite 命令

.help
.exit 或 .quit
.import <file> <table>
.mode <mode>
.show
.databases
.tables
.schema <table>         # 例： .schema sqlite_master
.indices [table]

子句

DISTINCT

表達式

WHERE

AND/OR

GROUP BY/HAVING

ORDER BY

OFFSET/LIMIT

運算符

算術運算符：+、-、*、/、%

比較運算符

==
=
!=
>
<
>=
<=
!<
!>

邏輯運算符

AND

BETWEEN

EXISTS
例：
 AGE 存在于子查詢返回的結果中的所有記錄
SELECT AGE FROM COMPANY 
        WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
IN
NOT IN
LIKE：
  % 代表零/一/多個數字或字符
  _ 代表一個單一的數字或字符
例：
SELECT column_list 
FROM table_name
WHERE column LIKE 'XXXX%'

SELECT column_list 
FROM table_name
WHERE column LIKE 'XXXX_’

GLOB：與 LIKE 作用相同，不同之處在于它是大小寫敏感的
  * 代表零/一/多個數字或字符
  ? 代表一個單一的數字或字符
例：
SELECT FROM table_name
WHERE column GLOB '*XXXX*'

SELECT FROM table_name
WHERE column GLOB 'XXXX?'

NOT

OR

IS NULL

IS

IS NOT

||：連接兩個不同的字符串，得到一個新的字符串

UNIQUE

位運算符：&、|、~、<<、>>

DDL

數據類型

• NULL
• INTEGER
• REAL(浮點數）
• TEXT
• BLOB

數據庫操作

ATTACH DATABASE ‘test.db’ AS ‘mydb’
DETACH DATABASE ‘mydb’

表操作

• CREATE

CREATE TABLE <dbName.tableName> (
	colName1 dataType colAttr,
	colName2 dataType colAttr
);

• DROP

DROP TABLE <dbName.tableName>;

• ALTER

重命名表：
ALTER TABLE database_name.table_name RENAME TO new_table_name;
例
ALTER TABLE COMPANY RENAME TO OLD_COMPANY;

添加列：
ALTER TABLE database_name.table_name ADD COLUMN column_def...;
例
ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1);

注：新添加的列是以 NULL 值來填充的

DML

INSERT

INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]  
VALUES (value1, value2, value3,...valueN);

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

INSERT INTO first_table_name [(column1, column2, ... columnN)] 
   SELECT column1, column2, ...columnN 
   FROM second_table_name
   [WHERE condition];

UPDATE

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

DELETE

DELETE FROM table_name
WHERE [condition];

實現 TRUNCATE 功能：
在 SQLite 中，并沒有 TRUNCATE TABLE 命令，但可以使用 SQLite 的 DELETE 命令從已有的表中刪除全部的數據。

DELETE FROM table_name;
DELETE FROM sqlite_sequence WHERE name = 'table_name';

當 SQLite 數據庫中包含自增列時，會自動建立一個名為 sqlite_sequence 的表。這個表包含兩個列：name 和 seq。name 記錄自增列所在的表，seq 記錄當前序號（下一條記錄的編號就是當前序號加 1）。如果想把某個自增列的序號歸零，只需要修改 sqlite_sequence 表就可以了。
UPDATE sqlite_sequence SET seq = 0 WHERE name = 'table_name';

• DQL：SELECT

SELECT column1, column2, columnN FROM table_name;
SELECT * FROM table_name;

高級

PRAGMA

PRAGMA pragma_name;
PRAGMA pragma_name = value;

字段約束

• NOT NULL
• DEFAULT
• UNIQUE
• PRIMARY KEY 只能用于 INTEGER 字段
• AUTOINCREMENT
• CHECK

例：
CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL,
   NAME TEXT NOT NULL UNIQUE,
   AGE INT NOT NULL DEFAULT 0,
   ADDRES  CHAR(50),
   SALARY REAL CHECK(SALARY > 0)
);

不能更改列：重命名列，刪除一列，或從一個表中添加或刪除約束都是不可能的。

JOIN

CROSS JOIN：笛卡爾積
>SELECT ... FROM table1 CROSS JOIN table2 ...


INNER JOIN：默認的連接類型
>SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...

自然連接（NATURAL JOIN）類似于 JOIN...USING，只是它會自動測試存在兩個表中的每一列的值之間相等值：
SELECT ... FROM table1 NATURAL JOIN table2...

OUTER JOIN：SQLite3 只支持 LEFT
（LEFT/RIGHT/FULL）
>SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...
···

##### UNION
···sql
UNION：
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION ALL：
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION ALL
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

別名

表別名：
SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];

字段別名：
ELECT column_name AS alias_name
FROM table_name
WHERE [condition];

關鍵字 AS 可被省略
SELECT id AS identification, name AS nickname FROM company;
SELECT id identification, name AS nickname FROM company;

觸發器

是數據庫的回調函數，它會在指定的數據庫事件發生時自動執行/調用
SQLite 只支持 FOR EACH ROW 觸發器（Trigger）
- 如果提供 WHEN 子句，則只針對 WHEN 子句為真的指定行執行 SQL 語句
- BEFORE 或 AFTER 關鍵字決定何時執行觸發器動作
- 當觸發器相關聯的表刪除時，自動刪除觸發器（Trigger）
- 要修改的表必須存在于同一數據庫中
- 一個特殊的 SQL 函數 RAISE() 可用于觸發器程序內拋出異常

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

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

— 刪除觸發器
DROP TRIGGER trigger_name;

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

sqlite_master

查看所有觸發器：
SELECT name FROM sqlite_master
WHERE type = 'trigger';

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

查看所有索引：
SELECT * FROM sqlite_master WHERE type = 'index';

索引

索引有助于加快 SELECT 查詢和 WHERE 子句，但它會減慢使用 UPDATE 和 INSERT 語句時的數據輸入
使用 CREATE INDEX 語句創建索引，它允許命名索引，指定表及要索引的一列或多列，并指示索引是升序排列還是降序排列。


CREATE INDEX index_name ON table_name;

否要創建一個單列索引還是組合索引，要考慮到您在作為查詢過濾條件的 WHERE 子句中使用非常頻繁的列。

===
單列索引：
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);

隱式索引：
列添加 UNIQUE 約束后會自動創建隱式索引
	?	INDEXD BY
INDEXD BY 指定必須需要命名的索引來查找前面表中值。

如果索引名 index-name 不存在或不能用于查詢，然后 SQLite 語句的準備失敗

"NOT INDEXED" 子句規定當訪問前面的表（包括由 UNIQUE 和 PRIMARY KEY 約束創建的隱式索引）時，沒有使用索引
使指定了 "NOT INDEXED"，INTEGER PRIMARY KEY 仍然可以被用于查找條目


SELECT|DELETE|UPDATE column1, column2...
FROM table_name
INDEXED BY (index_name)
WHERE (CONDITION);

例：
SELECT * FROM COMPANY INDEXED BY salary_index WHERE salary > 5000;

視圖

預定義的 SQLite 語句

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

語法：
CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

如果使用了可選的 TEMP 或 TEMPORARY 關鍵字，則將在臨時數據庫中創建視圖。

刪除視圖：
DROP VIEW view_name;

事務

事務屬性：
ACID

事務控制：只與 DML 命令 INSERT、UPDATE 和 DELETE 一起使用。他們不能在創建表或刪除表時使用，因為這些操作在數據庫中是自動提交的

BEGIN; 或 BEGIN TRANSACTION;

COMMIT; 或 END TRANSACTION;

ROLLBACK;

子查詢

SQL 注入

EXPLAIN

用于交互式分析和排除故障：

EXPLAIN [SQLite Query]

EXPLAIN  QUERY PLAN [SQLite Query]

VACUUM

通過復制主數據庫中的內容到一個臨時數據庫文件，然后清空主數據庫，并從副本中重新載入原始的數據庫文件。這消除了空閑頁，把表中的數據排列為連續的，另外會清理數據庫文件結構。

如果表中沒有明確的整型主鍵（INTEGER PRIMARY KEY），VACUUM 命令可能會改變表中條目的行 ID（ROWID）。VACUUM 命令只適用于主數據庫，附加的數據庫文件是不可能使用 VACUUM 命令。

如果有一個活動的事務，VACUUM 命令就會失敗。VACUUM 命令是一個用于內存數據庫的任何操作。由于 VACUUM 命令從頭開始重新創建數據庫文件，所以 VACUUM 也可以用于修改許多數據庫特定的配置參數。

$ sqlite3 database_name "VACUUM;"

sqlite> VACUUM;

sqlite> VACUUM table_name;
其它：AUTO_VACUUM

函數

日期與時間函數
date(timestring, modifier, modifier, ...)
time(timestring, modifier, modifier, ...)
strftime(format, timestring, modifier, modifier, ...)	
julianday(timestring, modifier, modifier, ...)	

數學與字符串函數
數學函數：

COUNT
MAX/MIN
AVG
SUM
RANDOM
ABS

字符串函數：
UPPER
LOWER
LENGTH

其它函數
sqlite_version

接口：與編程語言交互

參考

• https://www.runoob.com/sqlite/sqlite-tutorial.html
• http://songti.net/thread-636-1-1.html