20160616更新
參考： http://www.runoob.com/sqlite/sqlite-tutorial.html
1. SQLite PRAGMA：可以用在 SQLite 環境内控制各種環境變量和狀态标志。
一個 PRAGMA 值可以被讀取，也可以根據需求進行設定。
（1）讀取文法：隻需要提供該 pragma 的名字
PRAGMA pragma_name; （2）設定文法：
PRAGMA pragma_name = value; （3）舉幾個例子：pragma.txt
詳情請參考：http://www.runoob.com/sqlite/sqlite-pragma.html
pragma auto_vacuum; -- 這裡是檢視
pragma cache_size;
pragma case_sensitive_like;
pragma count_changes;
pragma database_list;
pragma encoding;
pragma freelist_count;
pragma auto_vacuum = FULL; -- 這裡是設定
pragma cache_size = 10;
pragma case_sensitive_like = true;
pragma count_changes = true;
2. SQLite 限制：限制是在表的資料列上強制執行的規則
限制可以是列級或表級。列級限制僅适用于列，表級限制被應用到整個表
（1）以下是在 SQLite 中常用的限制
- NOT NULL 限制：確定某列不能有 NULL 值。
- DEFAULT 限制：當某列沒有指定值時，為該列提供預設值。
- UNIQUE 限制：確定某列中的所有值是不同的。
- PRIMARY Key 限制：唯一辨別資料庫表中的各行/記錄。
- CHECK 限制：CHECK 限制確定某列中的所有值滿足一定條件
（2）primary key限制：
PRIMARY KEY 限制唯一辨別資料庫表中的每個記錄。
在一個表中可以有多個 UNIQUE 列，但隻能有一個主鍵。
在設計資料庫表時，主鍵是很重要的。主鍵是唯一的 ID。
在 SQLite 中，主鍵可以是 NULL，這是與其他資料庫不同的地方。
主鍵是表中的一個字段，唯一辨別資料庫表中的各行/記錄。主鍵必須包含唯一值。主鍵列不能有 NULL 值。
一個表隻能有一個主鍵，它可以由一個或多個字段組成。當多個字段作為主鍵，它們被稱為複合鍵。
如果一個表在任何字段上定義了一個主鍵，那麼在這些字段上不能有兩個記錄具有相同的值。
（3）執行個體： constraint.txt
create table tab_test
(
ID INT PRIMARY KEY NOT NULL, -- 主鍵 非空
NAME TEXT NOT NULL UNIQUE, -- 非空， 不相等
AGE INT CHECK(AGE > 0), -- check限制，AGE必須大于0
ADDRESS CHAR(50),
SALARY REAL DEFAULT 5000.00 -- 預設限制
);
（4）删除限制：在 SQLite 中，ALTER TABLE 指令允許使用者重命名表，或向現有表添加一個新的列。重命名列，删除一列，或從一個表中添加或删除限制都是不可能的。
3. SQLite joins：用于結合兩個或多個資料庫中表的記錄。JOIN 是一種通過共同值來結合兩個表中字段的手段
（1）主要有三種連接配接方式：
- 交叉連接配接 - CROSS JOIN
- 内連接配接 - INNER JOIN
- 外連接配接 - OUTER JOIN
（2）為了練習需要建立一個表： create_department.txt
create table department(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT NOT NULL );
-- 插入一些資料
insert into department values(1, 'IT Billing', 1);
insert into department values(2, 'Engineering', 2);
insert into department values(3, 'Finance', 7);
select * from department;
（3）交叉連接配接：把第一個表的每一行與第二個表的每一行進行比對。如果兩個輸入表分别有 x 和 y 列，則結果表有 x*y 列。有時會特别龐大
文法：
SELECT ... FROM table1 CROSS JOIN table2 ... （4）内連接配接 inner join：根據連接配接謂詞結合兩個表（table1 和 table2）的列值來建立一個新的結果表。
查詢會把 table1 中的每一行與 table2 中的每一行進行比較，找到所有滿足連接配接謂詞的行的比對對。
為了避免備援，并保持較短的措辭，可以使用 USING 表達式聲明内連接配接（INNER JOIN）條件。這個表達式指定一個或多個列的清單：
SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ... 自然連接配接（NATURAL JOIN）類似于 JOIN...USING，隻是它會自動測試存在兩個表中的每一列的值之間相等值：
SELECT ... FROM table1 NATURAL JOIN table2...
SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ... （5）外連接配接 outer join：雖然 SQL 标準定義了三種類型的外連接配接：LEFT、RIGHT、FULL，但 SQLite 隻支援 左外連接配接（LEFT OUTER JOIN）。
外連接配接（OUTER JOIN）聲明條件的方法與内連接配接（INNER JOIN）是相同的，使用 ON、USING 或 NATURAL 關鍵字來表達。
最初的結果表以相同的方式進行計算。一旦主連接配接計算完成，外連接配接（OUTER JOIN）将從一個或兩個表中任何未連接配接的行合并進來，外連接配接的列使用 NULL 值，将它們附加到結果表中。
為了避免備援，并保持較短的措辭，可以使用 USING 表達式聲明外連接配接（OUTER JOIN）條件。這個表達式指定一個或多個列的清單：
SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ... （6）執行個體： join.txt
-- 交叉連接配接，表1與表2的所有列進行一一比對
select EMP_ID, NAME, DEPT from company cross join department;
-- 内連接配接，滿足連接配接謂詞時就生成一個新的結果
select EMP_ID, NAME, DEPT from company inner join department on company.ID = department.EMP_ID;
-- 左外連接配接，從一個或兩個表中任何未連接配接的行合并進來，外連接配接的列使用 NULL 值，将它們附加到結果表中。
select EMP_ID, NAME, DEPT from company left outer join department on company.ID = department.EMP_ID;
結果：
（7）比較：（不知道這樣算不算正确，我也不是了解的太深刻）
交叉連接配接後結果非常多，慎用
左外連接配接會填充NULL，内連接配接不會
4, SQLite Unions子句：用于合并兩個或多個 SELECT 語句的結果，不傳回任何重複的行。
為了使用 UNION，每個 SELECT 被選擇的列數必須是相同的，相同數目的清單達式，相同的資料類型，并確定它們有相同的順序，但它們不必具有相同的長度
（1）UNION基本文法：
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition] （2）union all基本文法：用于結合兩個 SELECT 語句的結果，包括重複行。适用于 UNION 的規則同樣适用于 UNION ALL 運算符。
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION ALL
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition] （3）執行個體：unions.txt
select EMP_ID,NAME,DEPT from company inner join department -- 内連接配接
on company.ID = department.EMP_ID
UNION
select EMP_ID,NAME,DEPT from company left outer join department -- 左外連接配接
on company.ID = department.EMP_ID;
UNION ALL
5. SQLite NULL值：
SQLite 的 NULL 是用來表示一個缺失值的項。表中的一個 NULL 值是在字段中顯示為空白的一個值。
帶有 NULL 值的字段是一個不帶有值的字段。NULL 值與零值或包含空格的字段是不同的，了解這點是非常重要的。
（1）帶有 NULL 值的字段在記錄建立的時候可以保留為空。
（2）NULL 值在選擇資料時會引起問題，因為當把一個未知的值與另一個值進行比較時，結果總是未知的，且不會包含在最後的結果中。
6. SQLite 别名：暫時把表或列重命名為另一個名字，這被稱為别名。
重命名是臨時的改變，在資料庫中實際的表的名稱不會改變。
列别名用來為某個特定的 SQLite 語句重命名表中的列。
（1）文法：
表 别名的基本文法如下：
SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition]; 列 别名的基本文法如下：
SELECT column_name AS alias_name
FROM table_name
WHERE [condition]; （2）執行個體：
select C.ID as company_ID, C.NAME as company_NAME, D.DEPT as department_DEPT -- 這裡是給列取别名
from company as C , department as D -- 這裡是給表取别名
where C.ID = D.EMP_ID; - 并且使用
（3）效果：
7. SQLite觸發器： Trigger SQLite 的觸發器是資料庫的回調函數，它會自動執行/指定的資料庫事件發生時調用。
（1）要點：
- 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() 可用于觸發器程式内抛出異常。
（2）文法：
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為例：
CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
BEGIN
-- Trigger logic goes here.... -- 這裡是執行update時的操作，可以寫進一張表裡
END;
（3）執行個體：以插入資料為例：
先建立一張表，存儲插入資訊： create_audit.txt
CREATE TABLE AUDIT(
EMP_ID INT NOT NULL,
ENTRY_DATE TEXT NOT NULL
);
再建立觸發器： trigger.txt
create trigger audit_log after insert
on company
begin
insert into audit (EMP_ID, ENIRY_DATA) values(new.ID, datetime('now')); -- 每次插入資料，就往audit中插入一條資料
end;
注意： new代表新的， 還可以用old，表示操作之前的資訊。
這個例子中，如果insert換成delete，插入時就必須用 old.ID了，因為記錄已經被删除了，new已經沒有意義了。
再把company表中的資料清空：
delete from company;
最後運作插入資料： insert_company.txt
可以看到audit中的資料：
（4）列出 觸發器：列出所有的觸發器
select name from sqlite_master where type = 'trigger'; -- 列出所有的觸發器
select name from sqlite_master where type = 'trigger' AND tbl_name='company'; -- 找出特定表中的觸發器
（5）删除觸發器：drop指令
如： drop trigger trigger_name;
8.SQLite 索引：是一種特殊的查找表，資料庫搜尋引擎用來加快資料檢索
索引是一個指向表中資料的指針。一個資料庫中的索引與一本書後邊的索引是非常相似的。
索引有助于加快 SELECT 查詢和 WHERE 子句，但它會減慢使用 UPDATE 和 INSERT 語句時的資料輸入。
索引可以建立或删除，但不會影響資料。
（1）基本文法
CREATE INDEX index_name ON table_name; （2）
單列索引：單列索引是一個隻基于表的一個列上建立的索引。基本文法如下：
CREATE INDEX index_name
ON table_name (column_name); 唯一索引：使用唯一索引不僅是為了性能，同時也為了資料的完整性。唯一索引不允許任何重複的值插入到表中。基本文法如下
CREATE INDEX index_name
on table_name (column_name); 組合索引：組合索引是基于一個表的兩個或多個列上建立的索引
CREATE INDEX index_name
on table_name (column1, column2); 隐式索引：隐式索引是在建立對象時，由資料庫伺服器自動建立的索引。索引自動建立為主鍵限制和唯一限制。
（3）執行個體： index.txt
在company的SALARY上建立索引：
CREATE INDEX salary_index ON COMPANY (SALARY);
（4）執行個體： find_index.txt
.indices company -- .indices指令 列出company表上可用的所有的索引
select * from sqlite_master where type='index'; -- 列出資料庫範圍内的所有索引
（5）删除索引： drop index index_name;
（6）什麼情況下要避免使用索引：
- 索引不應該使用在較小的表上。
- 索引不應該使用在有頻繁的大批量的更新或插入操作的表上。
- 索引不應該使用在含有大量的 NULL 值的列上。
- 索引不應該使用在頻繁操作的列上。
9、SQLite index by："INDEXED BY index-name" 子句規定必須需要命名的索引來查找前面表中值
如果索引名 index-name 不存在或不能用于查詢，然後 SQLite 語句的準備失敗。
SELECT|DELETE|UPDATE column1, column2...
from table_name
INDEXED BY (index_name）
WHERE (CONDITION); （2）、執行個體：indexby.txt
create index salary_index on company(SALARY); -- 建立索引
select * from company INDEXED BY salary_index where SALARY > 4000; -- 利用索引進行查詢