SQLite簡介

SQLite簡介

常見的關係型資料庫有SQLite，MySQL，SQL Server等，通常學習關係型資料庫時不會使用SQLite，但是SQLite 是世界上使用最廣泛的資料庫引擎。SQLite 內建於所有手機和大多數計算機中，並捆綁在人們每天使用的無數其他應用程式中。SQLite 是一個由C語音開發的嵌入式庫，具有小型、 快速、 自包含、 高可靠、 功能齊全等特點。

文章以3.32.2版本為例講述SQLite的一些基本知識

SQLite常用命令和示例

在命令列中使用SQLite命令，需要先執行sqlite3命令，進入SQLite提示符。

建立或開啟資料庫

有兩種方法可以建立或開啟資料庫，一個是在sqlite3命令後面加上資料庫路徑，另一個是使用點命令.open

sqlite3 /.../xxx.db法

通過執行這個命令進入SQLite提示符時，如果資料庫檔案已經存在，則直接開啟對應資料庫，否則不會立即在對應路徑建立xxx.db檔案。要等到執行了新增資料表，檢視等資料庫物件的命令之後。

• 示例

  先執行如下命令，此時沒有建立出comms_ease.db檔案

sqlite3 comms_ease.db

​ 再執行如下命令建立一張表，在當前目錄出現comms_ease.db檔案

.open /.../xxx.db法

使用.open是一個點命令，使用它需要先執行sqlite3命令進入SQLite提示符。.open命令的使用方式也是在命令後面追加資料庫路徑，不過和sqlite3 /.../xxx.db法不同的是，執行.open命令後，資料庫檔案會被直接建立出來，不需要再建立資料庫物件。

建立表

SQLite的建立語句為CREATE TABLE，完整的建立表語句內容豐富，除了建立普通表外，還能具備判斷表是否已經存在，建立臨時表等能力。常見的建立普通表的句式為。

CREATE TABLE 表名 (
        列1名稱 列型別 以空格隔開的一個或多個列約束, 
        列2名稱 列型別 以空格隔開的一個或多個列約束,
        ...
);

預設情況下，一張表的最大列數為2000，每一行能存下的最大位元組數為十億，能滿足絕大多數的需求，建立普通表的示例如下

CREATE TABLE table_comms_ease (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    value VARCHAR
);

例子中，建立了一個名為table_comms_ease的資料表，包含兩列，第一列是id，型別是整形，不能為空，而且是表的主鍵，並可以自動生成； 第二列是value，型別為字串

在表名已經存在的情況下，呼叫CREATE TABLE 表名語句會報錯，要避免，可以使用CREATE TABLE IF NOT EXISTS 表名語句。如果不存在，則建立表，如果存在，則什麼都不做。示例如下

CREATE TABLE IF NOT EXISTS table_comms_ease (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    value VARCHAR
);

如果希望臨時儲存一些資料，而且只對當前連線有效，可以試試臨時表。臨時表的建立語句為CREATE TEMP TABLE。臨時表只對當前資料庫連線有效，重新建立連線或者同時存在的其他連線都無法訪問到。示例如下

CREATE TEMP TABLE temp_table_comms_ease (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    value VARCHAR
);

其它常用點命令

點命令是SQLite資料庫獨特的命令形式，它們通常比較簡單，而且不需要以分號結尾，常見的點命令如下表所示

增

向表內新增一行，即為每一列構建一個值，並填入新的一行中。新增行的命令是INSERT，新增方式有三種，一種是指定值新增；二是新增Select語句的結果；三是預設新增。

1. 指定值新增就是指定部分或者所有列的值，剩下列使用預設值的方式，對於沒有通過DEFAULTE，AUTOINCREMENT等方式宣告預設值的列，如果沒有指定NOT NULL則填入NULL，否則報錯。至少需要指定一列的值，如需全部填寫預設值，可以使用預設新增方式。需要注意的是，值的順序要和列的順序保持一致。示例如下：

   INSERT INTO table_comms_ease (value) VALUES ('value of 1');
   INSERT INTO table_comms_ease (value, id) VALUES ('value of 2', 2);

   如果需要指定填入所有列的值，也可以不把列名列舉出來，將值按建立表時各個列的順序列舉出來即可。示例如下：

   INSERT INTO table_comms_ease VALUES (3, 'value of 3');

2. 通過新增加Select語句的結果新增一行的格式為INSERT INTO 表名 SELECT ...通過此方法新增一行時，預設值不會被自動填充，SELECT語句查出的資料量必須和表的列數一致。假設已經存在表operator包含列key和description而且某一行的key為'.backup'，description為備份資料庫到檔案，給出示例如下：

   INSERT INTO table_comms_ease SELECT  4, description FROM operator WHERE key IS '.backup';

3. 預設新增的格式為INSERT INTO 表名 DEFAULT VALUES;為每一列都填入預設值，如果沒有特別宣告預設值，則填入NULL。示例如下：

   INSERT INTO table_comms_ease DEFAULT VALUES;

通過建立表章節的示例語句建立出資料表，再依次呼叫上述示例語句，則資料表內會出現5行資料，如下

刪

刪除表內的一行，命令是DELETE，常用格式為

DELETE FROM 表名 WHERE 過濾語句

刪除命令本身比較簡單，指定表名和刪除條件即可刪除一列，如下示例表示如果value列中的值有value of開頭，則刪除。

DELETE FROM table_comms_ease WHERE value LIKE 'value of %';

如上命令操作後，table_comms_ease表還剩的資料為

改

修改表內資料的命令為UPDATE，其常用格式為

UPDATE 表名 SET 一個或多個列的賦值 WHERE 過濾語句

修改多列內容時可以採用先寫出列名，再按順序賦值的方式，也可以採用一列一列修改的方式。如果要更新id為4的所在行的值，設定id為123，value為new value，兩種修改方式分別如下

UPDATE table_comms_ease SET (id, value)= (123, 'new value') WHERE id = 4;

或者

UPDATE table_comms_ease SET id=123, value='new value' WHERE id = 4;

修改後，table_comms_ease表中的資料為

修改單列的方法和修改多列的方法相似，比如將id為5這一行的value也修改為new value，可以如下操作

UPDATE table_comms_ease SET (value)=('new value') WHERE id = 5;

或者

UPDATE table_comms_ease SET value='new value' WHERE id = 5;

修改後，table_comms_ease表中的資料為

查

查詢語句的命令是SELECT，它不會修改資料庫，結果的行數在自然數範圍內，每一行代表一個查詢結果。SELECT命令的常用格式為

SELECT 去重策略 列名列表 FROM 表名或者子查詢語句 WHERE 過濾語句 ORDER BY 排序策略 LIMIT 數量限制

查詢語句中可用的配置比較多，但是大都不是必須的。查詢table_comms_ease表的所有內容只需要如下命令即可

SELECT * FROM table_comms_ease;

上面命令中的*表示所有列，命令相當於

SELECT id,value FROM table_comms_ease;

結果為

另外，可以通過VALUES語句構建一個查詢結果，結果的列名為column1, column2, column3等等。比如

VALUES (1,2,3),('a','b','c');

的結果為

去重策略

去重策略有兩種，一種是預設策略ALL，代表不去重；另一種是DISTINCT，代表去重。table_comms_ease表中value列的值相同，使用ALL和DISTINCT分別查詢value列時，命令和結果如下：

ALL命令

SELECT ALL * FROM table_comms_ease;

結果為

DISTINCT命令

SELECT DISTINCT value FROM table_comms_ease;

結果為

可以看出在有重複結果時，ALL策略會保留所有結果，而DISTINCT策略只保留其中一個

表名或者子查詢語句

查詢語句的FROM關鍵字後面可以跟表名或者子查詢語句，用於限制查詢範圍。當填寫表名時，可以填寫多個表名，用逗號或者連線運算子分隔。當填寫查詢語句時，可以視為先查詢出一張表，再從此表中查詢出資料。

假設還有一張表table_comms_ease_1，列資訊和table_comms_ease表相同，值為

則此欄位填寫table_comms_ease,table_comms_ease_1時得到如下命令

SELECT * FROM table_comms_ease, table_comms_ease_1;

結果為

當此欄位填寫兩個子查詢語句，如一個是id為5，另一個是id為123時，則得到如下命令

SELECT * FROM (SELECT * FROM table_comms_ease WHERE iD=5), (SELECT * FROM table_comms_ease WHERE iD=123)；

結果為

將子查詢語句的結果視為一張表，則可以統一對兩種填寫格式的理解。另外查詢命令也支援混合填寫表名和查詢語句。

排序條件

排序條件決定了結果的排列順序，常用格式如下

ORDER BY 列名 COLLATE 比較方式 排序方式 NULL值的排序方式

比較方式有三種，分別為BINARY, NOCASE和 RTRIM

• BINARY：使用標準C庫中的memcmp()函式逐位元組比較
• NOCASE：先把ASC II碼中的大寫字母轉為小寫字母，再按照BINARY方式比較
• RTRIM：去掉末尾空格後按照BINARY方式比較

通過下面命令為表table_comms_ease新增幾條資料，

INSERT INTO table_comms_ease VALUES (6, 'A'), (7, 'new value '), (8, 'Z');

則表中的資料變為

注意：id為7的一行對應的value的末尾有一個空格

如下示例展示了三種不同比較方式的區別

BINARY命令

SELECT * FROM table_comms_ease ORDER BY table_comms_ease.value COLLATE BINARY;

結果：

NOCASE命令

SELECT * FROM table_comms_ease ORDER BY table_comms_ease.value COLLATE NOCASE;

結果：

RTRIM命令

SELECT * FROM table_comms_ease ORDER BY table_comms_ease.value COLLATE RTRIM;

結果：

數量限制

數量限制語句可以限制查詢結果的行數，常用格式如下

LIMIT 數量 OFFET 偏移量

設數量為n，偏移量為o,則上面格式的意義是從第o+1條開始，取最多n條資料，如果沒有符合條件的資料，則結果為空。

限制數量為3，得到如下表示式

SELECT * FROM table_comms_ease LIMIT 3;

結果為

由於表的總行數是5，所以如果限制數量≥5，則會查出整張表。

如果限制數量為3，同時指定偏移量為1，得到如下表示式

SELECT * FROM table_comms_ease LIMIT 3 OFFSET 1;

結果過濾掉第一條資料(5, newvalue)，並向後取3條，得到

如果限制數量為3，同時指定偏移量為3，得到如下表示式

SELECT * FROM table_comms_ease LIMIT 3 OFFSET 3;

結果過濾掉前三條資料，並向後取3條，但是後面只有2條，所以得到

如果偏移量≥5，則什麼都查不到

SQLite常見限制

資料庫中的B樹

B樹與B+樹簡介

B樹是一種平衡多路查詢樹，每個結點包含三個部分：鍵，值，指向子結點的指標。假設一個B樹結點中有n個鍵，則它同時有n個值。如果這是一個葉子結點，則它沒有指向子結點的指標，否則有n+1個指向子結點的指標。下圖為n==2時的結點情況。

圖中鍵1和鍵2兩個值需要滿足鍵1<鍵2。 值1和值2分別與鍵1和鍵2對應；三個指向子結點的指標，分別指向具有不同範圍的鍵的子結點。子結點1中的鍵都小於鍵1；子結點2中的鍵都大於鍵1，且小於鍵2；子結點3中的鍵都大於鍵2。如下圖提供了一個3路B樹的示例。

上圖中每個結點有三排，第一排是鍵；第二排是值；第三排是指向子結點的指標。根節點有50和100兩個鍵，因此它的左子樹中結點的鍵都小於50；中子樹中結點的鍵都大於50且小於100；右子樹中結點的鍵都大於100。

B+樹和B樹類似，但是B+樹的內部結點中只有鍵和指向子結點的指標，而葉子結點具有鍵、值和指向下一組值的指標，即只在葉子結點上儲存資料。因此父結點中的鍵還會再出現在子結點上。而且B+樹的葉子結點的指向下一組值的指標，將所有值都串成了一個連結串列。由於內部結點不需要儲存值，B+樹可以儲存更多的鍵。下圖用5路B+樹儲存了上面3路B樹的內容。

B樹頁

資料庫檔案由一頁或多頁組成。同一個資料庫中，每頁的大小相同，都是 在512 和 65536 之間，並且為2的整數次冪。資料庫的頁分為鎖定位元組(lock-byte)頁、freelist頁、B樹頁、負載溢位頁和指標對映頁。

B樹演算法為SQLite提供了鍵值儲存模式，而且保證了鍵的有序性和唯一性。SQLite資料庫中B樹的結點就是一個頁面，所以指向的子結點的指標實際上是對應頁面的頁碼。SQLite資料庫使用了兩種B樹變體，在葉子節點儲存資料的表B樹和不儲存資料的索引B樹。一顆完整的樹只能是完全的表B樹或者完全的索引B樹。

表B樹和索引B樹

表B樹類似B+樹，只將值存放在葉子結點中；索引B樹是一顆沒有值的樹，鍵就是資料本身，因此索引B樹其實類似於B樹。下表列出了它們之間的一些差異。