SQLite 是一個開源的嵌入式關(guān)系型數(shù)據(jù)庫矾削，是一個自給自足的壤玫、無服務(wù)器的、零配置的哼凯、事務(wù)性的 SQL 數(shù)據(jù)庫引擎欲间。SQLite 具有如下特點(diǎn)：

• 無需單獨(dú)的服務(wù)器進(jìn)程，是無服務(wù)器的
• 不需要配置断部，無需復(fù)雜的安裝或管理步驟
• 數(shù)據(jù)庫存儲在一個單一的跨平臺的磁盤文件中
• 非常小的猎贴，輕量級的
• 自給自足的，不需要任何外部的依賴
• 事務(wù)是完全兼容 ACID蝴光，允許從多個進(jìn)程或線程安全訪問
• 支持 SQL92（SQL2）標(biāo)準(zhǔn)的大多數(shù)查詢語言的功能
• 使用 ANSI-C 編寫的她渴，并提供了簡單易用 API
• 兼容類 UNIX 平臺（Linux, Mac OS-X, Android, iOS）和 Windows（Win32, WinCE, WinRT）平臺

與其他高級的關(guān)系型數(shù)據(jù)庫相比，SQLite 不支持用戶管理蔑祟，不支持表的訪問權(quán)限管理趁耗，數(shù)據(jù)庫的訪問權(quán)限跟隨系統(tǒng)文件，不支持使用各種技巧來進(jìn)行額外的性能優(yōu)化疆虚。其適用于需要頻繁直接讀/寫磁盤文件的應(yīng)用苛败，需要遷移且不需要擴(kuò)展的應(yīng)用，完成產(chǎn)品功能測試等径簿。

SQLite 是不區(qū)分大小寫的著拭。其不支持表和字段注釋，但支持在 SQL 語句中添加附加注釋牍帚，以增加可讀性儡遮，附加注釋以 -- 開頭。每個 SQL 語句以 ; 結(jié)束暗赶。

常用命令

SQLite 提供了命令行工具 sqlite3 (第三個版本鄙币，目前最新的版本)，以便在命令行對數(shù)據(jù)庫進(jìn)行操作蹂随。先看一下 sqlite3 工具的命令行幫助：

$ sqlite3 -help
Usage: sqlite3 [OPTIONS] FILENAME [SQL]
FILENAME is the name of an SQLite database. A new database is created
if the file does not previously exist.
OPTIONS include:
   -ascii               set output mode to 'ascii'
   -bail                stop after hitting an error
   -batch               force batch I/O
   -column              set output mode to 'column'
   -cmd COMMAND         run "COMMAND" before reading stdin
   -csv                 set output mode to 'csv'
   -echo                print commands before execution
   -init FILENAME       read/process named file
   -[no]header          turn headers on or off
   -help                show this message
   -html                set output mode to HTML
   -interactive         force interactive I/O
   -line                set output mode to 'line'
   -list                set output mode to 'list'
   -lookaside SIZE N    use N entries of SZ bytes for lookaside memory
   -mmap N              default mmap size set to N
   -newline SEP         set output row separator. Default: '\n'
   -nullvalue TEXT      set text string for NULL values. Default ''
   -pagecache SIZE N    use N slots of SZ bytes each for page cache memory
   -scratch SIZE N      use N slots of SZ bytes each for scratch memory
   -separator SEP       set output column separator. Default: '|'
   -stats               print memory stats before each finalize
   -version             show SQLite version
   -vfs NAME            use NAME as the default VFS

又幫助信息可知十嘿，該工具直接接一個數(shù)據(jù)庫文件路徑來打開并連接一個數(shù)據(jù)庫，如果文件不存在則創(chuàng)建一個新的數(shù)據(jù)庫岳锁。進(jìn)入命令行模式后便可對數(shù)據(jù)庫進(jìn)行操作：

$ sqlite3
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

sqlite3 工具的內(nèi)置命令都以 . 開頭绩衷，否則都認(rèn)為是 SQL 語句。輸入 .help 可以查看所有支持的內(nèi)置命令激率，以下列舉一些常用的命令：

打開如下配置可以格式化查詢輸出顶籽，使輸出更易讀：

.headers on
.mode column

使用 .mode MODE 設(shè)置輸出模式時玩般，MODE 可以是以下類型：

• csv 逗號分隔的值
• column 左對齊的列
• html HTML 的 <table> 代碼
• insert TABLE 表的 SQL 插入（insert）語句
• line 每行一個值
• list 由 .separator 字符串分隔的值
• tabs 由 Tab 分隔的值
• tcl TCL 列表元素

設(shè)置格式化輸出還可以通過指定命令行參數(shù)實(shí)現(xiàn)：

$ sqlite3 -column -header <dbfile> "select * from <tablename>"

數(shù)據(jù)類型

SQLite 數(shù)據(jù)類型是一個用來指定任何對象的數(shù)據(jù)類型的屬性。一般的高級數(shù)據(jù)庫采用的是固定的靜態(tài)數(shù)據(jù)類型礼饱，而 SQLite 采用的是動態(tài)數(shù)據(jù)類型壤短。在 SQLite 中，值的數(shù)據(jù)類型與值本身是相關(guān)的慨仿，而不是與它的容器(比如列)相關(guān)久脯。因此，雖然可以在創(chuàng)建表的時候?yàn)榱兄付〝?shù)據(jù)類型镰吆，但這并不是必須的帘撰，SQLite 會根據(jù)存入值自動判斷它的類型。

SQLite 數(shù)據(jù)庫可以不為列指定數(shù)據(jù)類型万皿，其一個字段中存儲的值可以有多種數(shù)據(jù)類型摧找，因此如下的操作是合法的：

sqlite> create table test(x, y, z);
sqlite> insert into test values (1, '2', 3.0);
sqlite> insert into test values ('a', 'b', 'c');
sqlite> insert into test values (4, 5, null);
sqlite> select * from test;
x           y           z
----------  ----------  ----------
1           2           3.0
a           b           c
4           5

這得益于 SQLite 使用的一個更普遍的動態(tài)類型系統(tǒng)。它將數(shù)據(jù)分別存儲為不同的五種類別牢硅，又實(shí)現(xiàn)了 Type Affinity 以兼容 SQL 標(biāo)準(zhǔn)蹬耘。

存儲類別(Storage Classes)

SQLite 數(shù)據(jù)庫中的值被存儲為以下存儲類別之一：

• NULL： 值是一個 NULL 值
• INTEGER： 值是一個帶符號的整數(shù)，根據(jù)值的大小存儲在 1减余、2综苔、3、4位岔、6 或 8 字節(jié)中
• REAL： 值是一個浮點(diǎn)值如筛，存儲為 8 字節(jié)的 IEEE 浮點(diǎn)數(shù)字
• TEXT： 值是一個文本字符串，使用數(shù)據(jù)庫編碼（UTF-8抒抬、UTF-16BE 或 UTF-16LE）存儲
• BLOB： 值是一個 blob 數(shù)據(jù)杨刨，完全根據(jù)它的輸入存儲，即直接存儲為二進(jìn)制

SQLite 存儲類別 的概念比 數(shù)據(jù)類型 的概念更為通用擦剑。 例如妖胀，INTEGER 存儲類別包括 6 種不同長度的不同整數(shù)數(shù)據(jù)類型，這僅僅在磁盤存儲上有所不同惠勒。但是一旦從磁盤讀取 INTEGER 值并進(jìn)入內(nèi)存進(jìn)行處理時赚抡，其會被轉(zhuǎn)換為最通用的數(shù)據(jù)類型（8字節(jié)有符號整數(shù)）。 因此捉撮，在 SQLite 中怕品，“存儲類別” 與 “數(shù)據(jù)類型” 無法明確區(qū)分妇垢，并且這兩個術(shù)語可以互換使用巾遭。

除了 整形主鍵(INTEGER PRIMARY KEY) 列之外肉康，SQLite3 數(shù)據(jù)庫中的任何列都可用于存儲任何存儲類別的值。對于 SQLite 而言灼舍，即使在表聲明中明確指定了字段類型吼和，也仍然可以在該字段中存儲其它類型的數(shù)據(jù)。然而骑素，考慮到數(shù)據(jù)庫的移植性問題炫乓，在實(shí)際的應(yīng)用中應(yīng)該盡可能的保證數(shù)據(jù)類型的存儲和聲明的一致性。除非有極為充分的理由献丑，或者不用考慮移植性問題末捣，此時確實(shí)可以使用 SQLite 提供的這種特征。

類型親緣(Type Affinity)

為了兼容 SQL 標(biāo)準(zhǔn)创橄，以及兼容其他數(shù)據(jù)庫引擎的數(shù)據(jù)類型（方便在不同數(shù)據(jù)庫之間移植）箩做，SQLite 提出了 類型親緣性(Type Affinity) 的概念。簡單的理解這一概念就是妥畏，在表字段被聲明之后邦邦，SQLite 會根據(jù)字段聲明時的類型為其選擇一種親緣類型，當(dāng)數(shù)據(jù)插入時醉蚁，該字段的數(shù)據(jù)將會 優(yōu)先采用親緣類型作為該值的存儲方式燃辖，除非親緣類型不匹配或無法轉(zhuǎn)換當(dāng)前數(shù)據(jù)到該親緣類型，這時再考慮其它更適合該值的類型存儲該值网棍。SQLite 支持以下五種親緣類型：

• TEXT
• NUMERIC
• INTEGER
• REAL
• BLOB

Storage Class 是 SQLite 中數(shù)據(jù)的存儲類型黔龟，它們代表的是數(shù)據(jù)實(shí)際在磁盤上面的存放類型。而 Type Affinity 則可以理解為是數(shù)據(jù)字段的推薦類型滥玷。因?yàn)?SQLite 的數(shù)據(jù)類型是動態(tài)的捌锭，只能給這個字段推薦一個類型，而不能規(guī)定這個字段只能是某一類型罗捎。

親緣類型類型是用于 CREATE TABLE 語句中指定字段類型的观谦，創(chuàng)建表時，除可以使用上述的五種親緣類型外桨菜，還可以使用與其它高級數(shù)據(jù)庫兼容的類型名稱豁状。字段的親緣性由字段的聲明類型決定，并按以下規(guī)則和順序轉(zhuǎn)換：

• 1. 如果類型字符串中包含 "INT"倒得，那么該字段的親緣類型是 INTEGER
• 2. 如果類型字符串中包含 "CHAR"泻红、"CLOB"、或 "TEXT"霞掺，那么該字段的親緣類型是 TEXT谊路，如 VARCHAR
• 3. 如果類型字符串中包含 "BLOB"，或者沒有指定類型菩彬，那么該字段的親緣類型是 BLOB
• 4. 如果類型字符串中包含 "REAL"缠劝、"FLOA" 或 "DOUB"潮梯，那么該字段的親緣類型是REAL
• 5. 其余情況下，字段的親緣類型為 NUMERIC

如果某一字段類型同時符合兩種親緣性惨恭，那么排在前面的規(guī)則將先產(chǎn)生作用秉馏。下表列出了創(chuàng)建表時可使用的各種數(shù)據(jù)類型名稱以及對應(yīng)的 Affinity：

示例：

sqlite> CREATE TABLE TEST(ID INTEGER PRIMARY KEY, XX VARCHAR(255), YY TINYINT);
sqlite> INSERT INTO TEST VALUES (NULL, 'a', 1);
sqlite> INSERT INTO TEST VALUES (NULL, 2, '3');
sqlite> INSERT INTO TEST VALUES (NULL, 4.0, 5.0);
sqlite> INSERT INTO TEST VALUES (NULL, '-2.1', 'abc');
sqlite> SELECT * FROM TEST;
ID          XX          YY
----------  ----------  ----------
1           a           1
2           2           3
3           4.0         5
4           -2.1        abc
sqlite> SELECT typeof(id), typeof(xx), typeof(yy) FROM TEST;
typeof(id)  typeof(xx)  typeof(yy)
----------  ----------  ----------
integer     text        integer
integer     text        integer
integer     text        integer
integer     text        text

注意，SQLite 沒有單獨(dú)的 Boolean 數(shù)據(jù)存儲類別脱羡。需要存儲布爾值時可以使用整數(shù) 0（false）和 1（true）代替萝究。

時間/日期類型

SQLite 沒有單獨(dú)的用于存儲日期/時間的存儲類別，但其能夠把日期和時間存儲為 TEXT锉罐、REAL 或 INTEGER 值帆竹。各類日期和時間的存儲類別如下表所示：

可以以任何上述格式來存儲日期和時間，并且可以使用內(nèi)置的日期和時間函數(shù)來自由轉(zhuǎn)換不同格式脓规。

數(shù)值比較

在 SQLite3 中支持的比較表達(dá)式有："=", "==", "<", "<=", ">", ">=", "!=", "", "IN", "NOT IN", "BETWEEN", "IS", and "IS NOT"馆揉。

數(shù)據(jù)的比較結(jié)果主要依賴于操作數(shù)的存儲方式，其規(guī)則為：

• 1. 存儲方式為 NULL 的數(shù)值小于其它存儲類型的值
• 2. 存儲方式為 INTEGER 和 REAL 的數(shù)值小于 TEXT 或 BLOB 類型的值抖拦，如果同為 INTEGER 或 REAL升酣，則基于數(shù)值規(guī)則進(jìn)行比較
• 3. 存儲方式為 TEXT 的數(shù)值小于 BLOB 類型的值，如果同為 TEXT态罪，則基于文本規(guī)則(ASCII 值)進(jìn)行比較
• 4. 如果是兩個BLOB類型的數(shù)值進(jìn)行比較噩茄，其結(jié)果為 C 運(yùn)行時函數(shù) memcmp() 的結(jié)果

數(shù)據(jù)庫管理

創(chuàng)建數(shù)據(jù)庫

SQLite 的數(shù)據(jù)庫為一個單一的文件，使用 sqlite3 命令行工具接一個文件名即可創(chuàng)建一個數(shù)據(jù)庫复颈。創(chuàng)建數(shù)據(jù)庫的權(quán)限為當(dāng)前用戶創(chuàng)建文件的權(quán)限绩聘。數(shù)據(jù)庫文件沒有特殊的擴(kuò)展名要求，但通常為 sqlite 數(shù)據(jù)庫文件加上 .sqlite 或 .db 后綴耗啦。

使用 sqlite3 命令創(chuàng)建數(shù)據(jù)庫示例：

$ sqlite3 test.db
SQLite version 3.19.3
Enter ".help" for usage hints.
sqlite> .databases
main: /home/huoty/temp/test.db
sqlite> .exit

$ ls
test.db

以上示例會在當(dāng)前目錄下創(chuàng)建一個 test.db 文件凿菩，該文件將被 SQLite 引擎用作數(shù)據(jù)庫。數(shù)據(jù)庫被創(chuàng)建后帜讲，可以使用 .databases 命令來檢查其是否在數(shù)據(jù)庫列表中衅谷。

附加數(shù)據(jù)庫

對于連接到一個數(shù)據(jù)庫后，同時又需要訪問另一個數(shù)據(jù)庫中的數(shù)據(jù)的情況似将，SQLite 提供了一種將外部數(shù)據(jù)庫附加到當(dāng)前數(shù)據(jù)庫連接的機(jī)制获黔，即 ATTACH DATABAS≡谘椋基本語法：

ATTACH [DATABASE] 'database_path' As 'alias_name';

取消附加(分離數(shù)據(jù)庫)：

DETACH [DATABASE] 'alias_name';

使用示例：

$ sqlite3 test.db
SQLite version 3.19.3
Enter ".help" for usage hints.
sqlite> create table t1(a, b, c);
sqlite> insert into t1 values (1, 2, 3);
sqlite> .databases
main: /home/huoty/temp/test.db
sqlite> .tables
t1
sqlite> attach 'test2.db' as test2;
sqlite> .databases
main: /home/huoty/temp/test.db
test2: /home/huoty/temp/test2.db
sqlite> create table test2.t2(x, y, z);
sqlite> .tables
t1        test2.t2
sqlite> insert into test2.t2 values (1.1, 1.2, 1.3);
sqlite> select * from t1;
a           b           c
----------  ----------  ----------
1           2           3
sqlite> select * from t2;
x           y           z
----------  ----------  ----------
1.1         1.2         1.3
sqlite> select * from test2.t2;
x           y           z
----------  ----------  ----------
1.1         1.2         1.3
sqlite> detach test2;
sqlite> .databases
main: /home/huoty/temp/test.db

備份與恢復(fù)

要備份數(shù)據(jù)庫可以使用 .backup 命令玷氏，從備份恢復(fù)數(shù)據(jù)時使用 .restore 命令:

sqlite> .backup 'backup.db'
sqlite> .restore 'backup.db'

實(shí)際上，.backup 命令導(dǎo)出的備份文件是原數(shù)據(jù)庫的一個拷貝腋舌，其完全可以當(dāng)成一個完成的數(shù)據(jù)庫來用盏触。 另一種備份方式是，使用 .dump 命令，將完整的數(shù)據(jù)庫或指定的表導(dǎo)出到一個文本文件中赞辩，該方式導(dǎo)出的是 SQL 語句雌芽。如果不指定表名則導(dǎo)出整個數(shù)據(jù)庫，否則僅導(dǎo)出指定表的內(nèi)容诗宣。示例：

$ sqlite3 test.db .dump > test.db.sql

要從導(dǎo)出的 SQL 文件中恢復(fù)數(shù)據(jù)膘怕，采用如下方式：

$ sqlite3 test.db < test.db.sql

或者進(jìn)入命令行使用 .read 命令：

sqlite> .read test.db.sql

還可以是用 .output 將輸出重定向輸出文件中想诅。例如通過 .mode 和 .separator 命令可以將輸出配置為 csv 格式召庞，然后重定向到文件。如果要從 csv 文件恢復(fù)數(shù)據(jù)来破，可以使用 .import 命令篮灼。

表管理

創(chuàng)建表

創(chuàng)建數(shù)據(jù)庫使用 CREATE TABLE 語句完成，其基本語法為：

CREATE TABLE database_name.table_name(
       column1 datatype  PRIMARY KEY,
       column2 datatype,
       column3 datatype,
       .....
       columnN datatype,
    );

例如創(chuàng)建一個 COMPANY 表：

CREATE TABLE COMPANY(
       ID      INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
       NAME    TEXT    NOT NULL UNIQUE,
       AGE     INT     NOT NULL DEFAULT 0,
       ADDRESS CHAR(50),
       SALARY  REAL,
       ADD_TIME    DATETIME NOT NULL DEFAULT (datetime('now','localtime')),
       UPDATE_TIME DATETIME NULL
);

如果設(shè)置自增列徘禁，則該列必須是 INTEGER 類型诅诱，且必須是主鍵。UNIQUE 關(guān)鍵字可用于約束字段的唯一性送朱。DEFAULT 關(guān)鍵字可以指定缺省值娘荡，如果需要設(shè)置缺省日期、時間為本地當(dāng)前時間可以用 datetime 函數(shù)實(shí)現(xiàn)驶沼，如：

DEFAULT (datetime('now','localtime'))

內(nèi)置表

SQLite 數(shù)據(jù)庫中有一個特殊的名叫 sqlite_master 內(nèi)置的表炮沐，它定義數(shù)據(jù)庫的模式，記錄了所有已創(chuàng)建表的信息回怜。該表的結(jié)構(gòu)為：

sqlite> .schema sqlite_master
CREATE TABLE sqlite_master (
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);

對于表來說大年，type 字段永遠(yuǎn)是 'table'，name 字段永遠(yuǎn)是表的名字玉雾。所以翔试，要獲得數(shù)據(jù)庫中所有表的列表， 可以使用 SELECT 語句查詢該表：

SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;

對于索引复旬，type 等于 'index', name 則是索引的名字垦缅，tbl_name 是該索引所屬的表的名字。 不管是表還是索引驹碍，sql 字段是原先用 CREATE TABLE 或 CREATE INDEX 語句創(chuàng)建它們時的命令文本失都。對于自動創(chuàng)建的索引（用來實(shí)現(xiàn) PRIMARY KEY 或 UNIQUE 約束），sql 字段為 NULL幸冻。

SQLITE_MASTER 表是只讀的粹庞。不能對它使用 UPDATE、INSERT 或 DELETE洽损。 它會被 CREATE TABLE庞溜、CREATE INDEX、DROP TABLE 和 DROP INDEX 命令自動更新。 臨時表不會出現(xiàn)在 sqlite_master 表中流码。臨時表及其索引和觸發(fā)器存放在另外一個叫 sqlite_temp_master 的表中又官。SQLITE_TEMP_MASTER 跟 SQLITE_MASTER 差不多， 但它只是對于創(chuàng)建那些臨時表的應(yīng)用可見漫试。如果要獲得所有表的列表六敬， 不管是永久的還是臨時的，可以使用類似下面的語句：

SELECT name FROM
(SELECT * FROM sqlite_master UNION ALL
SELECT * FROM sqlite_temp_master)
WHERE type='table'
ORDER BY name;

此外驾荣，SQLite 還有一個 sqlite_sequence 的內(nèi)置表外构，該表用來保存其他表的 RowID 的最大值設(shè)置。當(dāng)用戶創(chuàng)建的表包含自增列時播掷，會在 sqlite_sequence 表中增加一條記錄审编，并記錄該表中當(dāng)前自增值的最大值。如果需要設(shè)置表的自增字段起始值歧匈，可以用如下語句

UPDATE sqlite_sequence SET seq=<n> WHERE name='tablename'

清空表

清空表使用不帶 WHERE 條件的 DELETE 語句垒酬，該語句是一行一行的刪除記錄，直至全部刪除件炉。如果表設(shè)置了自增字段勘究，delete 表數(shù)據(jù)后計數(shù)器不會歸零，所以還需在 sqlite_sequence 表中將自增起始值置為 0斟冕。語句如下：

DELETE FROM tablename;
UPDATE sqlite_sequence SET seq=0 where name='tablename';

刪除表

要刪除表定義及其所有相關(guān)數(shù)據(jù)口糕、索引、觸發(fā)器宫静、約束走净，使用 DROP TABLE 語句：

DROP TABLE tablename;

修改表

修改表使用 ALTER TABLE 語句，該語句可以重命名表孤里，以及給表添加額外的列伏伯。除此外，該語句不支持其他操作捌袜∷到粒基本語句：

-- 重命名表
ALTER TABLE tablename RENAME TO new_tablename;

-- 添加一列
ALTER TABLE table_name ADD COLUMN column_def...;

復(fù)制表

有時可能需要將一個表的數(shù)據(jù)復(fù)制到另一個表，或者整體復(fù)制一個表的結(jié)構(gòu)和數(shù)據(jù)虏等。復(fù)制表的操作語法如下：

-- 僅復(fù)制數(shù)據(jù)
INSERT INTO dest_table SELECT * FROM source_table [WHERE ...];

-- 僅復(fù)制表結(jié)構(gòu)
CREATE TABLE new_table AS SELECT * FROM old_table WHERE 0;

-- 同時復(fù)制表結(jié)構(gòu)和數(shù)據(jù)
CREATE TABLE new_table AS SELECT * FROM old_table;

約束

約束用于確保數(shù)據(jù)庫中數(shù)據(jù)的準(zhǔn)確性和可靠性弄唧。約束通過在表的數(shù)據(jù)列上強(qiáng)制執(zhí)行的規(guī)則，從而限制可以插入到表中的數(shù)據(jù)的類型霍衫。SQLite 中支持的約束包括

• NOT NULL 約束： 確保某列不能有 NULL 值
• DEFAULT 約束： 當(dāng)某列沒有指定值時候引，為該列提供默認(rèn)值
• UNIQUE 約束： 確保某列中的所有值是不同的
• PRIMARY KEY 約束： 唯一標(biāo)識數(shù)據(jù)庫表中的各行/記錄
• CHECK 約束： CHECK 約束確保某列中的所有值滿足一定條件
• FOREIGN KEY 約束： 外鍵約束，默認(rèn)不支持敦跌，需要手動開啟

一個表中可以有多個 UNIQUE 列澄干，但只能有一個主鍵。如果設(shè)置自增主鍵，則該列必須是 INTEGER 類型麸俘。主鍵可以由一個或多個字段組成辩稽，當(dāng)多個字段作為主鍵時，其被稱為復(fù)合鍵从媚。聲明復(fù)合鍵的方式為：

CREATE TABLE tablename (
  column1,
  column2,
  column3,
  PRIMARY KEY (column1, column2)
);

CHECK 約束會在插入記錄時檢查值是否符合條件逞泄。如果條件值為 false，則記錄違反約束拜效，且不能插入到表喷众。示例：

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

SQLite 不支持修改表中字段的約束條件，用于修改表結(jié)構(gòu)的語句 ALTER TABLE 僅允許重命名表拂檩，或向現(xiàn)有表添加一個新的列侮腹。重命名列嘲碧，刪除一列稻励，或從一個表中添加或刪除約束都是不可能的，只有在創(chuàng)建表時就定義好約束條件愈涩。

SQLite 從版本 3.6.19 (2009-10-14) 開始支持 外鍵約束(Foreign Key)望抽。外鍵約束用于關(guān)聯(lián)兩個表，使之存在關(guān)系履婉，這也是關(guān)系型數(shù)據(jù)庫的重要特點(diǎn)之一煤篙。SQLite 的外鍵支持默認(rèn)是關(guān)閉的，必須在運(yùn)行時手動開啟毁腿。開啟外鍵支持需使用 PRAGMA 語句：

PRAGMA foreign_keys=ON;

通常辑奈，外鍵約束的列在父表里需要是主鍵。如果不是主鍵已烤，那么也必須受一個 UNIQUE 約束或者有一個 UNIQUE 索引鸠窗。即外鍵在父表中必須是唯一的。創(chuàng)建外鍵的基本語法

CREATE TABLE table_name
(
    column1 datatype [ NULL | NOT NULL ],
    column2 datatype [ NULL | NOT NULL ],
    ...
    CONSTRAINT fk_column
    FOREIGN KEY (column1, column2, ... column_n)
    REFERENCES parent_table (column1, column2, ... column_n)
);

-- 其中 CONSTRAINT fk_column 表示為定義的外鍵聲明一個名字

示例：

-- 父表
CREATE TABLE pt(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(20) NOT NULL
);

-- 擁有外鍵的表
CREATE TABLE st(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    p_name VARCHAR(20) NOT NULL,
    value FLOAT,
    CONSTRAINT fk_p_name FOREIGN KEY (p_name) REFERENCES pt(name)
);

索引

索引(Index)用來排序數(shù)據(jù)以加快搜索和排序操作的速度胯究，其是一種特殊的查找表稍计，是一個指向表中數(shù)據(jù)的指針。索引具有如下特點(diǎn)：

• 索引改善檢索操作的性能裕循，但降低了數(shù)據(jù)插入臣嚣、修改和刪除的性能
• 索引數(shù)據(jù)可能占用大量的存儲空間
• 索引用于數(shù)據(jù)過濾和數(shù)據(jù)排序
• 可以指定多個列來組成一個索引，即復(fù)合索引（或稱多列索引）
• 索引可以創(chuàng)建或刪除剥哑，但不影響原始數(shù)據(jù)
• 索引可以指定唯一性硅则，即唯一索引

創(chuàng)建索引的基本語法如下：

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

索引自動創(chuàng)建為

數(shù)據(jù)庫會為擁有主鍵約束和唯一約束的列自動創(chuàng)建索引，這樣的索引可以稱之為隱式索引株婴。索引可以使用 DROP 語句刪除怎虫，基本語法如下：

-- 刪除索引
DROP INDEX index_name;

在操作表時，可以顯式的指定使用哪個索引或者指定不使用索引。顯式指定索引用 INDEXED BY 語句揪垄，禁用索引用 NOT INDEXED 語句穷吮。基本語法如下：

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

并非所有數(shù)據(jù)都適合做索引饥努，使用索引時應(yīng)該考慮下列準(zhǔn)則：

• 索引不應(yīng)該使用在較小的表上
• 索引不應(yīng)該使用在有頻繁的大批量的更新或插入操作的表上
• 索引不應(yīng)該使用在含有大量的 NULL 值的列上
• 索引不應(yīng)該使用在頻繁操作的列上
• 索引不應(yīng)該使用在有較少可能值的列上

觸發(fā)器

SQLite 的觸發(fā)器是數(shù)據(jù)庫的回調(diào)函數(shù)捡鱼，它在特定的數(shù)據(jù)庫活動發(fā)生時自動執(zhí)行。觸發(fā)器可以與特定表上的 INSERT酷愧、 UPDATE 和 DELETE 操作（或它們的組合）相關(guān)聯(lián)驾诈。觸發(fā)器的一些常見用途如下：

• 保證數(shù)據(jù)一致性，如在 INSERT 或 UPDATE 操作時對數(shù)據(jù)進(jìn)行統(tǒng)一轉(zhuǎn)化
• 計算列的值或更新時間戳溶浴，如每次 UPDATE 時更新 update_time 字段
• 某個表中的記錄變化時在其他表上執(zhí)行活動乍迄，如更新或刪除數(shù)據(jù)時將審計跟蹤記錄寫入日志表
• 驗(yàn)證輸入數(shù)據(jù)是否符合預(yù)期，如限定 age 字段必須大于 18

創(chuàng)建觸發(fā)器的基本語法如下：

CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name
ON table_name
[FOR EACH ROW]
[WHEN condition]
BEGIN
    -- Trigger logic goes here....
END;

BEFORE 或 AFTER 關(guān)鍵字決定何時執(zhí)行觸發(fā)器動作士败，決定是在關(guān)聯(lián)行的插入闯两、修改或刪除之前或者之后執(zhí)行觸發(fā)器動作。接著的 event_name 可以是 INSERT谅将，DELETE 和 UPDATE 數(shù)據(jù)庫操作漾狼。

SQLite 目前僅支持 FOR EACH ROW 觸發(fā)器，沒有 FOR EACH STATEMENT 觸發(fā)器饥臂。因此逊躁，不用明確指定 FOR EACH ROW 語句，可以省略隅熙。

如果提供了 WHEN 子句稽煤，則只針對 WHEN 子句為真的行執(zhí)行 SQL 語句，否則針對所有行執(zhí)行 SQL 語句囚戚。

WHEN 子句和觸發(fā)器動作可能訪問使用表單 NEW.column-name 和 OLD.column-name 的引用插入酵熙、刪除或更新的行元素。其中 column-name 是從與觸發(fā)器關(guān)聯(lián)的表的列的名稱弯淘，NEW 只出現(xiàn)在 INSERT 和 UPDATE 時绿店，OLD 只出現(xiàn)在 UPDATE 和 DELETE 時，在 INSERT 時 NEW 表示新插入的行數(shù)據(jù)庐橙，UPDATE 時 NEW 表示要替換的新數(shù)據(jù)假勿、OLD 表示要被更改的原來的數(shù)據(jù)行， DELETE 時 OLD 表示要被刪除的數(shù)據(jù)态鳖。

日期與時間函數(shù)

SQLite 支持如下的時間/日期處理函數(shù)：

以上函數(shù)將時間字符串作為參數(shù)转培，通過指定零個或者多個 modifier 修飾符來輸出時間。支持的時間字符串換樣式如下：

時間字符串后邊可跟著零個或多個的修飾符浆竭，這將改變時間/日期函數(shù)的輸出浸须，修飾符從左到右依次生效惨寿。SQLite 支持的修飾符如下：

一些使用示例：

sqlite> SELECT DATE('2006-10-17', '+1 day', '+1 year');
2007-10-18
sqlite> SELECT TIME('now','localtime');
15:52:20
sqlite> SELECT DATETIME('2016-10-17 00:20:00', '+1 hour', '-12 minute');
2016-10-17 01:08:00
sqlite> SELECT DATETIME('now', 'localtime');
2018-07-22 15:52:47
sqlite> SELECT DATETIME('now', '+8 hour');
2018-07-22 15:52:55
sqlite> SELECT DATETIME('now', 'localtime', '+2 hour' , '+20 minute' , '-20 second');
2018-07-22 18:12:52
sqlite> SELECT DATETIME ('now', 'localtime', 'start of year');
2018-01-01 00:00:00
sqlite> SELECT DATETIME('2016-10-17 00:20:00', '+1 hour', '-12 minute', 'start of month');
2016-10-01 00:00:00
sqlite> SELECT DATETIME('now', 'localtime', 'start of day');
2018-07-22 00:00:00
sqlite> SELECT DATE('now', 'start of month', '+1 month', '-1 day');
2018-07-31
sqlite> SELECT STRFTIME('%Y.%m.%d %H:%M:%S','now','localtime');
2018.07.22 15:55:57

鎖機(jī)制

SQLite 基于鎖來實(shí)現(xiàn)并發(fā)控制删窒，以保證數(shù)據(jù)的安全和完成性裂垦。SQLite 的鎖是粗粒度的，并不像其他高級數(shù)據(jù)引擎一樣擁有更加細(xì)粒度的表鎖或行鎖肌索。當(dāng)有連接在寫數(shù)據(jù)庫時蕉拢，數(shù)據(jù)庫則被鎖住直到寫事務(wù)結(jié)束，所以所有其它的連接都無法對數(shù)據(jù)庫進(jìn)行讀寫操作诚亚。SQLite 在 3.7.0 版本開始引入了 WAL 技術(shù)晕换，全稱叫 Write Ahead Log（預(yù)寫日志），WAL 技術(shù)可以解決讀寫互斥的問題站宗。

鎖狀態(tài)

SQLite 的數(shù)據(jù)庫連接有 5 種鎖狀態(tài)：未加鎖(unlocked)闸准、 共享鎖(shared)、 預(yù)留鎖(reserved)梢灭、 預(yù)留鎖(reserved)夷家、 未決鎖(pending)、 排它鎖(exclusive)或辖。SQLite 使用鎖逐步上升機(jī)制往弓，如果需要寫數(shù)據(jù)庫渴杆，連接必須逐級地獲得排它鎖主儡。

• 未加鎖: 未加鎖狀態(tài)的情況包括挺勿，未和數(shù)據(jù)庫建立連接奸柬，已建立連接但是還沒訪問數(shù)據(jù)庫酿矢，已用 BEGIN 開始了一個事務(wù)但未開始讀寫數(shù)據(jù)庫樱衷。該狀態(tài)為缺省狀態(tài)创译，其它的連接可以在數(shù)據(jù)庫上執(zhí)行任意的讀寫操作
• 共享鎖: 當(dāng)連接要從數(shù)據(jù)庫中讀取數(shù)據(jù)時膀曾，必須先申請獲得一個共享鎖县爬，如果獲得成功，則進(jìn)入到共享狀態(tài)添谊。在該狀態(tài)下财喳，數(shù)據(jù)庫可以被讀取但是不能被寫入。在同一時刻可以有任意數(shù)量的連接在同一個數(shù)據(jù)庫上持有共享鎖斩狱，因此讀操作是并發(fā)的耳高。也就是說，只要有一個或多個共享鎖處于活動狀態(tài)所踊，就不再允許有數(shù)據(jù)庫文件寫入的操作存在
• 預(yù)留鎖: 當(dāng)連接需要寫數(shù)據(jù)庫時泌枪，需先申請一個預(yù)留鎖。一個數(shù)據(jù)庫同時只能有一個預(yù)留鎖秕岛，預(yù)留鎖可以與共享鎖共存碌燕，保留鎖是寫數(shù)據(jù)庫的第一階段误证。保留鎖即不阻止其它擁有共享鎖的連接繼續(xù)讀數(shù)據(jù)庫，也不阻止其它連接獲得新的共享鎖修壕。獲得預(yù)留鎖后進(jìn)入預(yù)留狀態(tài)愈捅，這時會先在緩沖區(qū)中進(jìn)行需要的修改、更新操作慈鸠，操作后的結(jié)果依然保存在緩沖區(qū)中改鲫，未真正寫入數(shù)據(jù)庫
• 未決鎖: 當(dāng)連接需要從預(yù)留升為排它鎖以完成寫操作（提交修改）時，需要先升級到未決鎖林束，此時其它連接就不能再獲得共享鎖像棘，但已經(jīng)擁有共享鎖的連接仍然可以繼續(xù)正常讀數(shù)據(jù)庫。擁有未決鎖的連接會等待其它擁有共享鎖的連接完成讀操作并釋放其共享鎖后壶冒，提成到排它鎖
• 排它鎖: 當(dāng)連接需要提交修改時缕题，需要先獲得排它鎖。排它鎖不允許其它連接獲得任何鎖胖腾，直到當(dāng)前連接的排它鎖被釋放烟零。獲得排它鎖的連接可以自由地對數(shù)據(jù)庫進(jìn)行修改，所有以前對緩沖區(qū)所做的修改都會被寫到數(shù)據(jù)庫文件

死鎖

在使用事務(wù)的情況下咸作，SQLite的鎖機(jī)制存在死鎖的可能性锨阿。如以下例子：

• 執(zhí)行順序3：連接B要執(zhí)行寫操作，獲得預(yù)留鎖
• 執(zhí)行順序4：連接A要執(zhí)行讀操作记罚，獲得共享鎖
• 執(zhí)行順序5：連接B要提交修改墅诡，預(yù)留鎖升級為未決鎖
• 執(zhí)行順序6：連接B想要升級為排它鎖，必須先等待連接A釋放共享鎖
• 執(zhí)行順序7：連接A要執(zhí)行寫操作桐智，需要獲得預(yù)留鎖
• 執(zhí)行順序8：連接A獲得預(yù)留鎖失敗末早，必須先等待連接B釋放未決鎖

于是連接 A 和連接 B 相互等待對方，發(fā)生死鎖说庭∪涣祝可以通過正確的使用事務(wù)類型來解決以上死鎖問題。

WAL 技術(shù)

WAL 技術(shù)的原理是刊驴，修改并不直接寫入到數(shù)據(jù)庫文件中姿搜，而是寫入到另外一個稱為 WAL 的文件中；如果事務(wù)失敗捆憎，WAL 中的記錄會被忽略舅柜，撤銷修改；如果事務(wù)成功攻礼，它將在隨后的某個時間被寫回到數(shù)據(jù)庫文件中业踢，提交修改。

WAL 使用檢查點(diǎn)將修改寫回數(shù)據(jù)庫礁扮，默認(rèn)情況下知举，當(dāng) WAL 文件發(fā)現(xiàn)有 1000 頁修改時瞬沦，將自動調(diào)用檢查點(diǎn)。這個頁數(shù)大小可以自行配置雇锡。WAL 技術(shù)帶來以下優(yōu)點(diǎn)：

• 讀寫操作不再互相阻塞逛钻，一定程度上解決了處理高并發(fā)上的性能瓶頸
• 磁盤 I/O 行為更容易被預(yù)測
• 數(shù)據(jù)達(dá)到 GB 級時，性能可能會降低锰提。

啟用 WAL 需用 PRAGMA 執(zhí)行以下語句：

PRAGMA journal_mode=WAL;

與 MySQL 差異

以下總結(jié)了在工作和學(xué)習(xí)過程中發(fā)現(xiàn)的 SQLite 與 MySQL 之間的一些差異：

• SQLite 不支持表和列注釋曙痘，不支持存儲過程
• MySQL 采用的是靜態(tài)數(shù)據(jù)類型，每個字段中值得類型在創(chuàng)建表時被確定立肘，而 SQLite 采用的是動態(tài)類型边坤，一個字段中存儲的值的類型可以不同
• 自增鍵 MySQL 為 AUTO_INCREMENT， SQLite 為 AUTOINCREMENT
• SQLite 自增主鍵只能是 INTEGER 類型
• SQLite 必須在定義字段時聲明為 PRIMARY KEY 才能用 AUTOINCREMENT

`ID`  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL

• MySQL 可以直接在定義表結(jié)構(gòu)時指定自增字段的起始值谅年，而 SQLite 需要手動在 內(nèi)置表中設(shè)置：

-- MySQL 通過 AUTO_INCREMENT 來設(shè)置自增起始值
CREATE TABLE `test` (
  `ID` INT(11) NOT NULL AUTO_INCREMENT,
  `DATA` FLOAT DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=520 DEFAULT CHARSET=utf8;

-- SQLite 通過更新 sqlite_sequence 表來說設(shè)置自增起始值
CREATE TABLE `test` (
`ID`  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
`DATA` REAL,
PRIMARY KEY (`ID`)
);
UPDATE sqlite_sequence SET seq=520 WHERE name='test';

• SQLite 不能自己填充自增主鍵字段茧痒，即主鍵不能缺失，需要將自增主鍵設(shè)為 NULL 才能自動自增：

INSERT INTO test VALUES (NULL, 1);

• 設(shè)置字段的缺省日期/時間為本地當(dāng)前時間：

-- MySQL 用 CURRENT_TIMESTAMP 實(shí)現(xiàn)設(shè)置缺省值為當(dāng)前時間
-- 但是包含 CURRENT_TIMESTAMP 缺省值的字段只能有一個
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

-- MySQL 還可以用 ON UPDATE 指定在更新記錄是自動填充該字段為當(dāng)前時間
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP


-- SQLite 也支持 CURRENT_TIMESTAMP 字段融蹂，但其不能返回當(dāng)前時區(qū)的時間
-- 要返回當(dāng)前時區(qū)的時間需要使用日期/時間函數(shù)旺订，并指定 localtime 修飾符
`add_time`    DATETIME NOT NULL DEFAULT (datetime('now', 'localtime'))

-- SQLite 支持多個字段存在缺省日期/時間，但不像 MySQL 那樣支持 ON UPDATE
-- 要實(shí)現(xiàn) ON UPDATE 的功能超燃，可以用觸發(fā)器實(shí)現(xiàn)

• 存在則更新不存在則插入区拳，MySQL 用 ON DUPLICATE KEY UPDATE，而 SQLite 只能用 REPLACE INTO意乓。需要注意的是樱调，MySQL 也支持 REPLACE INTO，該語句的原理是洽瞬，首先嘗試插入數(shù)據(jù)到表中本涕，如果發(fā)現(xiàn)表中已經(jīng)有此行數(shù)據(jù)（根據(jù)主鍵或者唯一索引判斷）則 先刪除此行數(shù)據(jù)，然后插入新的數(shù)據(jù)伙窃。

參考資料

• https://www.sqlite.org/datatype3.html
• https://doc.yonyoucloud.com/doc/wiki/project/sqlite/index.html
• https://blog.csdn.net/yanbober/article/details/45567149
• https://dba.stackexchange.com/questions/40656/how-to-properly-format-sqlite-shell-output
• http://liuduo.me/2016/04/02/sqlitelockandwal/