# 第4章 SQL
本章介紹SQL的基本內容和SQLite的特殊實現。本章內容的編排假設你沒有SQL和關系模型的基礎知識。如果你是SQL新手，SQLite將帶你進入關系模型的精彩世界。
空注：使用過很多種數據庫，所以本章只關注SQLite與其它DBMS不同的地方，如弱類型什么的。
## 關系模型
如第3章所述，SQL是關系模型的產物，關系模型是由E. F. Codd在1969年提出的。關系模型要求關系型數據庫能夠提供一種查詢語言，幾年后，SQL應運而生。
關系模型由三部分構成：表單(form)、功能(function)和一致性(consistency)。表單表示信息的結構。在關系模型中只使用一種單獨的數據結構來表達所有信息，這種結構稱為關系(relation，在SQL中被稱為表、table)。關系由多個元組(tuples，在SQL中被稱為行、記錄、rows)構成，每個元組又由多個屬性(attributes，在SQL中被稱為列、字段、columns)構成。
## 查詢語言
查詢語言將外部世界和數據的邏輯表現聯系在一起，并使它們能夠交互。它提供了取數據和修改數據的途徑，是關系模型的動態部分。
## SQL的發展
第一個被采用的此類查詢語言可能是在IBM的System R當中。System R是一個關系型數據庫的研究項目，此項目直接派生出了Codd的論文。這個語言開始時被稱作SEQUEL，是“Structured English Query Language”的縮寫。后來被縮短為SQL，或“Structured Query Language”。
## 示例數據庫
示例數據庫在本章和后面的章節中將會用到，其中存儲了Seinfeld所有episode(約180個)的食品(約412種)。數據庫中的表如圖4-1所示。
圖4-1 Seinfeld食品數據庫
數據庫的schema定義如下：
```
create table episodes (
??id integer primary key,
? season int,
? name text );
create table foods(
? id integer primary key,
? type_id integer,
? name text );
create table food_types(
? id integer primary key,
? name text );
? create table foods_episodes(
? food_id integer,
? episode_id integer );
```
主表是foods。foods中的每條記錄代表一種食品，其名稱存儲于name字段。type_id參照food_types，food_types表存儲了食品的分類(如烘烤食品、飲品、垃圾食品等)。foods_episodes表是foods和episodes的關聯表。
## 建立
示例數據庫文件可在隨書的zip文件中找到。
## 運行示例
為了您的方便，本章的所有SQL示例都保存在隨書zip文件根目錄的sql.sql文件中。
對于長SQL語句，一個方便的執行方法是將其復制到一個單獨的文本文件，如test.sql中，然后執行：
```
sqlite3 foods.db < test.sql
```
為了增加輸出的易讀性，你應用把下面內容包含在文件中：
```
.echo on
.mode col
.headers on
.nullvalue NULL
```
## 語法
SQL的語法很像自然語言。每個語句都是一個祈使句，以動詞開頭，表示所要做的動作。后面跟的是主題和謂詞，如圖4-2所示。
圖 4-2 一般的SQL語法結構
## 命令
SQL由命令組成，每個命令以分號(;)結束。如下面是3個獨立的命令：
```
SELECT id, name FROM foods;
INSERT INTO foods VALUES (NULL, 'Whataburger');
DELETE FROM foods WHERE id=413;
```
## 常量
也稱為Literals，表示確切的值，有3種：字符串常量、數據常量和二進制常量。字符串常量如：
```
'Jerry'
'Newman'
'JujyFruit'
```
字符串值用單引號(')括起來，如果字符串中本身包含單引號，需要雙寫。如“Kenny’s chicken”需要寫成：
```
'Kenny''s chicken'
```
數字常量有整數、十進制數和科學記數法表示的數，如：
```
-1
3.142
6.0221415E23
```
二進制值用如x'0000'的表示法，其中每個數據是一個16進制數。二進制值必須由兩個兩個的16進制數(8 bits)組成，如：
```
x'01'
X'0fff'
x'0F0EFF'
X'0f0effab'
```
## 保留字和標識符
保留字由SQL保留用做特殊的用途，如SELECT、UPDATE、INSERT、CREATE、DROP和BEGIN等。標識符指明數據庫里的具體對象，如表或索引。保留字預定義，不能用做標識符。SQL不區分大小寫，下面是相同的語句：
```
SELECT * from foo;
SeLeCt * FrOm FOO;
```
為清楚起見，本章中保留字都用大寫，標識符都用小寫。
但是，SQLite對字符串的值是大小寫敏感的。
## 注釋
SQL中單行注釋用雙減號開始，多行注釋采用C風格的/* */形式。
## 創建一個數據庫
數據庫中所有的工作都圍繞表進行。表由行和列組成，看起來簡單，但其實并非如此。表跟其它所有的概念有關，涉及本章的大部分篇幅。在此我們用2分鐘的時間給出一個預覽。
## 創建表
在SQL中，創建和刪除數據庫對象的語句一般被稱為數據定義語言(data definition language， DDL)，操作這些對象中數據的語句稱為數據操作語言(data manipulation language，DML)。創建表的語句屬于DDL，用CREATE TABLE命令，如下定義：
```
CREATE [TEMP] TABLE table_name (column_definitions [, constraints]);
```
用TEMP或TEMPORARY保留字聲明的表為臨時表，只存活于當前會話，一旦連接斷開，就會被自動刪除。
中括號表示可選項。
另外，豎線表示在多個中選一，如：
```
CREATE [TEMP|TEMPORARY] TABLE … ;
```
如果沒有指明創建臨時表，則創建的是基本表，將會在數據庫中持久存在。
數據庫中還有其它類型的表，如系統表和視圖，現在先不介紹。
CREATE TABLE命令至少需要一個表名和一個字段名。命令中table_name表示表名，必須與其它所有的標識符不同。column_definitions表示一個用逗號分隔的字段列表。每個字段定義包括一個名稱、一個域和一個逗號分隔的字段約束表。“域”一般情況下是一個類型，與編程語言中的數據類型同名，指明存儲在該列的數據的類型。在SQLite中有5種本地類型：INTEGER、REAL、TEXT、BLOB和NULL，所有這些域將在本章后面的“存儲類”一節中介紹。“約束”用來控制什么樣的值可以存儲在表中或特定的字段中。例如，你可以用UNIQUE約束來規定所有記錄中某個字段的值要各不相同。約束將會在“數據完整性”一節中介紹。
在字段列表后面，可以跟隨一個附加的字段約束，如下例：
```
CREATE TABLE contacts ( id INTEGER PRIMARY KEY,
??? name TEXT NOT NULL COLLATE NOCASE,
??? phone TEXT NOT NULL DEFAULT 'UNKNOWN',
??? UNIQUE (name,phone) );
```
## 改變表
你可以用ALTER TABLE命令改變表的結構。SQLite版的ALTER TABLE命令既可以改變表名，也可以增加字段。一般格式為：
```
ALTER TABLE table { RENAME TO name | ADD COLUMN column_def }
```
注意這里又出現了新的符號{}。花括號括起來一個選項列表，必須從各選項中選擇一個。此處，我們或者ALTER TABLE table RENAME…，或者ALTERTABLE table ADD COLUMN…。That is, you can either rename the table using the RENAME clause, or add a column with the ADDCOLUMN clause. To rename a table, you simply provide the new name given by name. If you add a column, the column definition, denoted by column_def, follows the form in the CREATE TABLE statement. It is a name, followed by an optional domain and list of constraints. 例如：
```
sqlite> ALTER TABLE contacts
??? ADD COLUMN email TEXT NOT NULL DEFAULT '' COLLATE NOCASE;
sqlite> .schema contacts
??? CREATE TABLE contacts ( id INTEGER PRIMARY KEY,
??? name TEXT NOT NULL COLLATE NOCASE,
??? phone TEXT NOT NULL DEFAULT 'UNKNOWN',
??? email TEXT NOT NULL DEFAULT '' COLLATE NOCASE,
??? UNIQUE (name,phone) );
```
顯示了當前的表定義。
表還可以由SELECT語句創建，你可以在創建表結構的同時創建數據。這種特別的CREATE TABLE語句將在“插入記錄”一節中介紹。
## 在數據庫中查詢
SELECT是SQL命令中最大最復雜的命令。SELECT的很多操作都來源于關系代數。
## 關系操作
SELECT中使用3大類13種關系操作：
+ 基本的操作
+ Restriction(限制)
+ Projection
+ Cartesian Product(笛卡爾積)
+ Union(聯合)
+ Difference(差)
+ Rename(重命名)
+ 附加的操作
+ Intersection(交叉)
+ Natural Join(自然連接)
+ Assign(指派 OR 賦值)
+ 擴展的操作
+ Generalized Projection
+ Left Outer Join
+ Right Outer Join
+ Full Outer Join
基本的關系操作，除重命名外，在集合論中都有相應的理論基礎。附加操作是為了方便， 它們可以用基本操作來完成，一般情況下，附加操作可以作為常用基本操作序列的快捷方式。擴展操作為基本操作和附加操作增加特性。
ANSI SQL的SELECT可以完成上述所有的關系操作。這些操作覆蓋了Codd最初定義的所有關系運算符，只有一個例外——divide。SQLite支持ANSI SQL中除right和full outer join之外的所有操作(這些操作可用其它間接的方法完成)。
## 操作管道
從語法上來說，SELECT命令用一系列子句將很多關系操作組合在一起。每個子句代表一種特定的關系操作。幾乎所有這些子句都是可選的，你可以只選你所需要的操作。
SELECT是一個很大的命令。下面是SELECT的一個簡單形式：
```
SELECT DISTINCT heading FROM tables WHERE predicate
GROUP BY columns HAVING predicate
ORDER BY columns LIMIT count,offset;
```
每個保留字——DISTINCT、FROM、WHERE和HAVING——都是一個單獨的子句。每個子句由保留字和跟隨的參數構成。
表4-1 SELECT的子句
| 編號 | 子句 | 操作 | 輸入 |
| --- | --- | --- | --- |
| 1 | FROM | Join | List of tables |
| 2 | WHERE | Restriction | Logical predicate |
| 3 | ORDER BY | List of columns |
| 4 | GROUP BY | Restriction | List of columns |
| 5 | HAVING | Restriction | Logical predicate |
| 6 | SELECT | Restriction | List of columns or expressions |
| 7 | DISTINCT | Restriction | List of columns |
| 8 | LIMIT | Restriction | Integer value |
| 9 | OFFSET | Restriction | Integer value |
圖4-3 SELECT phases
## 過濾
如果SELECT是SQL中最復雜的命令，那么WHERE就是SELECT中最復雜的子句。
### 值
“值”可以按它們所屬的域(或類型)來分類，如數字值(1, 2, 3, etc.)或字符串值(“Jujy-Fruit”)。值可以表現為文字的值(1, 2, 3 or “JujyFruit”)、變量(一般是如foods.name的列名)、表達式(3+2/5)或函數的結果(COUNT(foods.name))值。
### 操作符
操作符使用一個或多個值做為輸入并產生一個新值做為輸出。這所以叫“操作符”是因為它完成某種操作并產生某種結果。二目操作符操作兩個輸入值(或稱操作數)，三目操作符操作三個操作數，單目操作符操作一個操作數，等等。
圖4-7 單目、二目和三目操作符
### 二目操作符
二目操作符是最常用的SQL操作符。表4-2列出了SQLite所支持的二目操作符。表中按優先級從高到低的次序排列，同色的一組中具有相同的優先級，圓括號可以覆蓋原有的優先級。
表4-2二目操作符
| 操作符 | 類型 | 作用 |
| --- | --- | --- |
| || | String | Concatenation |
| * | Arithmetic | Multiply |
| / | Arithmetic | Divide |
| % | Arithmetic | Modulus |
| + | Arithmetic | Add |
| – | Arithmetic | Subtract |
| <<? | Bitwise | Right shift |
| >>? | Bitwise | Left shift |
| & | Logical | And |
| | | Logical | Or |
| <? | Relational | Less than |
| <= | Relational | Less than or equal to |
| >? | Relational | Greater than |
| >= | Relational | Greater than or equal to |
| = | Relational | Equal to |
| == | Relational | Equal to |
| <>? | Relational | Not equal to |
| != | Relational | Not equal to |
| IN | Logical | In |
| AND | Logical | And |
| OR | Logical | Or |
| LIKE | Relational | String matching |
| GLOB | Relational | Filename matching |
### LIKE操作符
一個很有用的關系操作符是LIKE。LIKE的作用與相等(=)很像，但卻是通過一個模板來進行字符串匹配。例如，要查詢所有名稱以字符“J”開始的食品，可使用如下語句：
```
sqlite> SELECT id, name FROM foods WHERE name LIKE 'J%';
id name
156 Juice box
236 Juicy Fruit Gum
243 Jello with Bananas
244 JujyFruit
245 Junior Mints
370 Jambalaya
```
模板中的百分號(%)可與任意0到多個字符匹配。下劃線(_)可與任意單個字符匹配。
```
sqlite> SELECT id, name FROM foods WHERE name LIKE '%ac%P%';
id name
127 Guacamole Dip
168 Peach Schnapps
198 Mackinaw Peaches
```
另一個有用的竅門是使用NOT：
```
sqlite> SELECT id, name FROM foods
WHERE name like '%ac%P%' AND name NOT LIKE '%Sch%'
id name
38 Pie (Blackberry) Pie
127 Guacamole Dip
198 Mackinaw peaches
```
## 限定和排序
可以用LIMIT和OFFSET保留字限定結果集的大小和范圍。LIMIT指定返回記錄的最大數量。OFFSET指定偏移的記錄數。例如，下面的命令返回food_types表中id排第2的記錄：
```
SELECT * FROM food_types LIMIT 1 OFFSET 1 ORDER BY id;
```
保留字OFFSET在結果集中跳過一行(Bakery)，保留字LIMIT限制最多返回一行(Cereal)。
上面語句中還有一個ORDER BY子句，它使記錄集在返回之前按一個或多個字段的值排序。例如：
```
sqlite> SELECT * FROM foods WHERE name LIKE 'B%'
ORDER BY type_id DESC, name LIMIT 10;
id type_id name
382 15 Baked Beans
383 15 Baked Potato w/Sour
384 15 Big Salad
385 15 Broccoli
362 14 Bouillabaisse
328 12 BLT
327 12 Bacon Club (no turke
326 12 Bologna
329 12 Brisket Sandwich
274 10 Bacon
```
## 函數(Function)和聚合(Aggregate)
SQLite提供了多種內置的函數和聚合，可以用在不同的子句中。函數的種類包括：數學函數，如ABS()計算絕對值；字符串格式函數，如UPPER()和LOWER()，它們將字符串的值轉化為大寫或小寫。例如：
```
sqlite> SELECT UPPER('hello newman'), LENGTH('hello newman'), ABS(-12);
UPPER('hello newman') LENGTH('hello newman') ABS(-12)
HELLO NEWMAN 12 12
```
函數名是不分大小寫的(或upper()和UPPER()是同一個函數)。函數可以接受字段值作為參數：
```
sqlite> SELECT id, UPPER(name), LENGTH(name) FROM foods
????? WHERE type_id=1 LIMIT 10;
id UPPER(name) LENGTH(name)
-----------
1 BAGELS 6
2 BAGELS, RAISIN 14
3 BAVARIAN CREAM PIE 18
4 BEAR CLAWS 10
5 BLACK AND WHITE COOKIES 23
6 BREAD (WITH NUTS) 17
7 BUTTERFINGERS 13
8 CARROT CAKE 11
9 CHIPS AHOY COOKIES 18
10 CHOCOLATE BOBKA 15
```
因為函數可以是任意表達式的一部分，所以函數也可以用在WHERE子句中：
```
sqlite> SELECT id, UPPER(name), LENGTH(name) FROM foods
????? WHERE LENGTH(name) < 5 LIMIT 5;
id upper(name) length(name)
36PIE 3
48 BRAN 4
56KIX 3
57 LIFE 4
80 DUCK 4
```
聚合是一類特殊的函數，它從一組記錄中計算聚合值。標準的聚合函數包括SUM()、AVG()、COUNT()、MIN()和MAX()。例如，要得到烘烤食品(type_id=1)的數量，可使用如下語句：
```
sqlite> SELECT COUNT(*) FROM foods WHERE type_id=1;
count
47
```
## 分組(Grouping)
聚合的精華部分是分組。聚合不只是能夠計算整個結果集的聚合值，你還可以把結果集分成多個組，然后計算每個組的聚合值。這些都可以在一步當中完成，方法就是使用GROUP BY子句，如：
```
sqlite> SELECT type_id FROM foods GROUP BY type_id;
type_id
1
2
3
.
.
.
15
```
## 去掉重復
操作管道中的下一個限制是DISTINCT。DISTINCT處理SELECT的結果并過濾掉其中重復的行。例如，你想從foods表中取得所有不同的type_id值：
```
sqlite> SELECT DISTINCT type_id FROM foods;
type_id
1
2
3
.
.
.
15
```
## 多表連接
連接(join)是SELECT命令的第一個操作，它產生初始的信息，供語句的其它部分過濾和處理。連接的結果是一個合成的關系(或表)，它是SELECT后繼操作的輸入。
也許從一個例子開始是最簡單的。
```
sqlite> SELECT foods.name, food_types.name
????? FROM foods, food_types
????? WHERE foods.type_id=food_types.id LIMIT 10;
name name
Bagels Bakery
Bagels, raisin Bakery
Bavarian Cream Pie Bakery
Bear Claws Bakery
Black and White cookies Bakery
Bread (with nuts) Bakery
Butterfingers Bakery
Carrot Cake Bakery
Chips Ahoy Cookies Bakery
Chocolate Bobka Bakery
```
## 名稱和別名
當把多個表連接在一起時，字段可能重名。
```
SELECT B.name FROM A JOIN B USING (a);
```
## 修改數據
跟SELECT命令相比，用于修改數據的語句就太簡單太容易理解了。有3個DML語句用于修改數據——INSERT、UPDATE和DELETE。
## 插入記錄
使用INSERT命令向表中插入記錄。使用INSERT命令可以一次插入1條記錄，也可以使用SELECT命令一次插入多條記錄。INSERT語句的一般格式為：
```
INSERT INTO table (column_list) VALUES (value_list);
```
Table指明數據插入到哪個表中。column_list是用逗號分隔的字段名表，這些字段必須是表中存在的。value_list是用逗號分隔的值表，這些值與column_list中的字段一一對應。例如，下面語句向foods表插入數據：
```
sqlite> INSERT INTO foods (name, type_id) VALUES ('Cinnamon Bobka', 1);
```
## 修改記錄
UPDATE命令用于修改一個表中的記錄。UPDATE命令可以修改一個表中一行或多行中的一個或多個字段。UPDATE語句的一般格式為：
```
UPDATE table SET update_list WHERE predicate;
```
update_list是一個或多個“字段賦值”的列表，字段賦值的格式為column_name=value。WHERE子句的用法與SELECT語句相同，確定需要進行修改的記錄。如：
```
UPDATE foods SET name='CHOCOLATE BOBKA'
??? WHERE name='Chocolate Bobka';
SELECT * FROM foods WHERE name LIKE 'CHOCOLATE%';
id type_ name
10 1 CHOCOLATE BOBKA
11 1 Chocolate Eclairs
12 1 Chocolate Cream Pie
222 9 Chocolates, box of
223 9 Chocolate Chip Mint
224 9 Chocolate Covered Cherries
```
## 刪除記錄
DELETE用于刪除一個表中的記錄。DELETE語句的一般格式為：
```
DELETE FROM table WHERE predicate;
```
同樣，WHERE子句的用法與SELECT語句相同，確定需要被刪除的記錄。如：
```
DELETE FROM foods WHERE name='CHOCOLATE BOBKA';
```
## 數據完整性
數據完整性用于定義和保護表內部或表之間數據的關系。有四種完整性：域完整性、實體完整性、參照完整性和用戶定義完整性。
### 唯一約束
因為唯一(UNIQUE)約束是主鍵的基礎，所以先介紹它。一個唯一約束要求一個字段或一組字段的所有值互不相同，或者說唯一。如果你試圖插入一個重復值，或將一個值改成一個已存在的值，數據庫將引發一個約束非法，并取消操作。唯一約束可以在字段級或表級定義。
NULL和UNIQUE：
問題：如果一個字段已經聲明為UNIQUE，可以向這個字段插入多少個NULL值？
回答：與數據庫的種類有關。PostgreSQL和Oracle可以插入多個。Informix和Microsoft SQL Server只能一個。DB2、SQL Anywhere和Borland Inter-Base不能。SQLite采用了與PostgreSQL和Oracle相同的解決方案。
另一個困擾大家的關于NULL的經典問題是：兩個NULL值是否相等？你沒有足夠的信息來證明它們相等，但也沒有足夠的信息證明它們不等。SQLite的觀點是假設所有的NULL都是不同的。所以你可以向唯一字段中插入任意多個NULL值。
### 主鍵約束
在SQLite中，當你定義一個表時總要確定一個主鍵，不管你自己有沒有定義。這個字段是一個64-bit整型字段，稱為ROWID。它還有兩個別名——_ROWID_和OID，用這兩個別名同樣可以取到它的值。它的默認取值按照增序自動生成。SQLite為主鍵字段提供自動增長特性。
### 默認值
保留字DEFAULT為字段提供一個默認值。如果用INSERT語句插入記錄時沒有為該定做指定值，則為它賦默認值。DEFAULT不是一個約束(constraint)，因為它沒有強制任何事情。這所以把它歸為域完整性，是因為它提供了處理NULL值的一個策略。如果一個字段沒有指定默認址，在插入時也沒有為該字段指定值，SQLite將向該字段插入一個NULL。例如，contacts.name字段有一個默認值'UNKNOWN'，請看下面例子：
```
sqlite> INSERT INTO contacts (name) VALUES ('Jerry');
sqlite> SELECT * FROM contacts;
id name phone
Jerry UNKNOWN
```
DEFAULT還可以接受3種預定義格式的ANSI/ISO預定字用于生成日期和時間值。CURRENT_TIME將會生成ANSI/ISO格式(HH:MM:SS)的當前時間。CURRENT_DATE會生成當前日期(格式為YYYY-MM-DD)。CURRENT_TIMESTAMP會生成一個日期時間的組合(格式為YYYY-MM-DD HH:MM:SS)。例如：
```
CREATE TABLE times ( id int,
??? date NOT NULL DEFAULT CURRENT_DATE,
??? time NOT NULL DEFAULT CURRENT_TIME,
??? timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP );
INSERT INTO times(1);
INSERT INTO times(2);
SELECT * FROMS times;
id date time timestamp
1 2006-03-15 23:30:25 2006-03-15 23:30:25
2 2006-03-15 23:30:40 2006-03-15 23:30:40
```
### NOT NULL約束
### CHECK約束
### 排序法(Collation)
排序法定義如何唯一地確定文本的值。排序法主要用于規定文本值如何進行比較。不同的排序法有不同的比較方法。例如，某種排序法是大小寫不敏感的，于是'JujyFruit'和'JUJYFRUIT'被認為是相等的。另外一個排序法或許是大小寫敏感的，這時上面兩個字符串就不相等了。
SQLite有3種內置的排序法。默認為BINARY，它使用一個C函數memcmp()來對文本進行逐字節的比較。這很適合于大多數西方語言，如英語。NOCASE對26個字母是大小寫不敏感的。Finally there is REVERSE, which is the reverse of the BINARY collation. REVERSE is more for testing (and perhaps illustration) than anything else.
SQLite C API提供了一種創建定制排序法的手段，詳見第7章。
## 存儲類(Storage Classes)
如前文所述，SQLite在處理數據類型時與其它的數據庫不同。區別在于它所支持的類型以及這些類型是如何存儲、比較、強化(enforc)和指派(assign)。下面各節介紹SQLite處理數據類型的獨特方法和它與域完整性的關系。
對于數據類型，SQLite的域完整性被稱為域親和性(affinity)更合適。在SQLite中，它被稱為類型親和性(type affinity)。為了理解類型親和性，你必須先要理解存儲類和弱類型(manifest typing)。
SQLite有5個原始的數據類型，被稱為存儲類。存儲類這個詞表明了一個值在磁盤上存儲的格式，其實就是類型或數據類型的同義詞。這5個存儲類在表4-6中描述。
表 4-6 SQLite存儲類
| 名稱 | 說明 |
| --- | --- |
| INTEGER | 整數值是全數字(包括正和負)。整數可以是1, 2, 3, 4, 6或 8字節。整數的最大范圍(8 bytes)是{-9223372036854775808, 0, +9223372036854775807}。SQLite根據數字的值自動控制整數所占的字節數。空注：參可變長整數的概念。 |
| REAL | 實數是10進制的數值。SQLite使用8字節的符點數來存儲實數。 |
| TEXT | 文本(TEXT)是字符數據。SQLite支持幾種字符編碼，包括UTF-8和UTF-16。字符串的大小沒有限制。 |
| BLOB | 二進制大對象(BLOB)是任意類型的數據。BLOB的大小沒有限制。 |
| NULL | NULL表示沒有值。SQLite具有對NULL的完全支持。 |
SQLite通過值的表示法來判斷其類型，下面就是SQLite的推理方法：
+ SQL語句中用單引號或雙引號括起來的文字被指派為TEXT。
+ 如果文字是未用引號括起來的數據，并且沒有小數點和指數，被指派為INTEGER。
+ 如果文字是未用引號括起來的數據，并且帶有小數點或指數，被指派為REAL。
+ 用NULL說明的值被指派為NULL存儲類。
+ 如果一個值的格式為X'ABCD'，其中ABCD為16進制數字，則該值被指派為BLOB。X前綴大小寫皆可。
SQL函數typeof()根據值的表示法返回其存儲類。使用這個函數，下面SQL語句返回的結果為：
```
sqlite> select typeof(3.14), typeof('3.14'), typeof(314), typeof(x'3142'), typeof(NULL);
typeof(3.14)?? typeof('3.14')? typeof(314)??? typeof(x'3142')?????? typeof(NULL)
real??????????????? text??????????????? integer?????????? blob????????????????????? null
```
SQLite單獨的一個字段可能包含不同存儲類的值。請看下面的示例：
```
sqlite> DROP TABLE domain;
sqlite> CREATE TABLE domain(x);
sqlite> INSERT INTO domain VALUES (3.142);
sqlite> INSERT INTO domain VALUES ('3.142');
sqlite> INSERT INTO domain VALUES (3142);
sqlite> INSERT INTO domain VALUES (x'3142');
sqlite> INSERT INTO domain VALUES (NULL);
sqlite> SELECT ROWID, x, typeof(x) FROM domain;
```
返回結果為：
```
rowid????? x??????????? typeof(x)
1 ?????????? 3.142????? real
2 ?????????? 3.142????? text
3 ?????????? 3142 ???? integer
4 ?????????? 1B ??????? blob
5 ?????????? NULL ??? null
```
這帶來一些問題。這種字段中的值如何存儲和比較？如何對一個包含了INTEGER、REAL、TEXT、BLOB和NULL值的字段排序？一個整數和一個BLOB如何比較？哪個更大？它們能相等嗎？
答案是：具有不同存儲類的值可以存儲在同一個字段中。可以被排序，因為這些值可以相互比較。有完善定義的規則來做這件事。不同存儲類的值可以通過它們各自類的“類值”進行排序，定義如下：
1\. NULL存儲類具有最低的類值。一個具有NULL存儲類的值比所有其它值都小(包括其它具有NULL存儲類的值)。在NULL值之間，沒有特別的可排序值。
2\. INTEGER或REAL存儲類值高于NULL，它們的類值相等。INTEGER值和REAL值通過其數值進行比較。
3\. TEXT存儲類的值比INTEGER和REAL高。數值永遠比字符串的值低。當兩個TEXT值進行比較時，其值大小由“排序法”決定。
4\. BLOB存儲類具有最高的類值。具有BLOB類的值大于其它所有類的值。BLOB值之間在比較時使用C函數memcmp()。
所以，當SQLite對一個字段進行排序時，首先按存儲類排序，然后再進行類內的排序 (NULL類內部各值不必排序) 。下面的SQL說明了存儲類值的不同：
```
sqlite> SELECT 3 < 3.142, 3.142 < '3.142', '3.142' < x'3000', x'3000' < x'3001';
```
返回：
```
3 < 3.142????????????? 3.142 < '3.142'????????????? '3.142' < x'3000'???? x'3000' < x'3001'
1?????????????????? 1????????????????????????? 1????????????????????????? 1
```
## 弱類型(manifest typing)
SQLite使用弱類型。
看下面的表：
```
CREATE TABLE foo( x integer,
??? y text, z real );
```
向該表插入一條記錄：
```
INSERT INTO foo VALUES ('1', '1', '1');
```
當SQLite創建這條記錄時，x、y和z這3個字段中存儲的是什么類型呢？答案是INTEGER, TEXT和REAL。
再看下面例子：
```
CREATE TABLE foo(x, y, z);
```
現在執行同樣的插入語句：
```
INSERT INTO foo VALUES ('1', '1', '1');
```
現在，x、y和z中存儲的是什么類型呢？答案是TEXT、TEXT和TEXT。
那么，是否SQLite的字段類型默認為TEXT呢？再看，還是第2個表，執行如下插入語句：
```
INSERT INTO foo VALUES (1, 1.0, x'10');
```
現在，x、y和z中存儲的是什么類型呢？答案是INTEGER、REAL和BLOB。
如果你愿意，可以為SQLite的字段定義類型，這看起來跟其它數據庫一樣。但這不是必須的，你可以盡管違反類型定義。這是因為在任何情況下，SQLite都可以接受一個值并推斷它的類型。
總之，SQLite的弱類型可表示為：1)字段可以有類型，2)類型可以通過值來推斷。類型親和性介紹這兩個規定如何相互關聯。所謂類型親和性就是在強類型(strict typing)和動態類型(dynamic typing)之間的平衡藝術。
## 類型親和性(Type Affinity)
在SQLite中，字段沒有類型或域。當給一個字段聲明了類型，該字段實際上僅僅具有了該類型的新和性。聲明類型和類型親和性是兩回事。類型親和性預定SQLite用什么存儲類在字段中存儲值。在存儲一個給定的值時到底SQLite會在該字段中用什么存儲類決定于值的存儲類和字段親和性的結合。我們先來介紹一下字段如何獲得它的親和性。
### 字段類型和親和性
首先，每個字段都具有一種親和性。共有四種親和性：NUMERIC、INTEGER、TEXT和NONE。一個字段的親和性由它預聲明的類型決定。所以，當你為字段聲明了類型，從根本上說是為字段指定了親和性。SQLite按下面的規則為字段指派親和性：
+ 默認的，一個字段默認的親和性是NUMERIC。如果一個字段不是INTEGER、TEXT或NONE的，那它自動地被指派為NUMERIC親和性。
+ 如果為字段聲明的類型中包含了'INT'(無論大小寫)，該字段被指派為INTEGER親和性。
+ 如果為字段聲明的類型中包含了'CHAR'、'CLOB'或'TEXT'(無論大小寫)，該字段被指派為TEXT親和性。如'VARCHAR'包含了'CHAR'，所以被指派為TEXT親和性。
+ 如果為字段聲明的類型中包含了'BLOB'(無論大小寫)，或者沒有為該字段聲明類型，該字段被指派為NONE親和性。
注意：如果沒有為字段聲明類型，該字段的親和性為NONE，在這種情況下，所有的值都將以它們本身的(或從它們的表示法中推斷的)存儲類存儲。如果你暫時還不確定要往一個字段里放什么內容，或準備將來修改，用NONE親和性是一個好的選擇。但SQLite默認的親和性是NUMERIC。例如，如果為一定字段聲明了類型JUJYFRUIT，該字段的親和性不是NONE，因為SQLite不認識這種類型，會給它指派默認的NUMERIC親和性。所以，與其用一個不認識的類型最終得到NUMERIC親和性，還不如不為它指定類型，從而使它得到NONE親和性。
### 親和性和存儲
親和性對值如何存儲到字段有影響，規則如下：
+ 一個NUMERIC字段可能包括所有5種存儲類。一個NUMERIC字段具有數字存儲類的偏好(INTEGER和REAL)。當一個TEXT值被插入到一個NUMERIC字段，將會試圖將其轉化為INTEGER存儲類；如果轉化失敗，將會試圖將其轉化為REAL存儲類；如果還是失敗，將會用TEXT存儲類來存儲。
+ 一個INTEGER字段的處理很像NUMERIC字段。一個INTEGER字段會將REAL值按REAL存儲類存儲。也就是說，如果這個REAL值沒有小數部分，就會被轉化為INTEGER存儲類。INTEGER字段將會試著將TEXT值按REAL存儲；如果轉化失敗，將會試圖將其轉化為INTEGER存儲類；如果還是失敗，將會用TEXT存儲類來存儲。
+ 一個TEXT字段將會把所有的INTEGER或REAL值轉化為TEXT。
+ 一個NONE字段不試圖做任何類型轉化。所有值按它們本身的存儲類存儲。
+ 沒有字段試圖向NULL或BLOB值轉化——如無論用什么親和性。NULL和BLOB值永遠都按本來的方式存儲在所有字段。
這些規則初看起來比較復雜，但總的設計目標很簡單，就是：如果你需要，SQLite會盡量模仿其它的關系型數據庫。也就是說，如果你將SQLite看成是一個傳統數據庫，類型親和性將會按你的期望來存儲值。如果你聲明了一個INTEGER字段，并向里面放一個整數，就會按整數來存儲。如果你聲明了一個具有TEXT, CHAR或VARCHAR類型的字段并向里放一個整數，整數將會轉化為TEXT。可是，如果你不遵守這些規定，SQLite也會找到辦法來存儲你的值。
### 親和性的運行
讓我們看一些例子來了解親和性是如何工作的：
```
sqlite> CREATE TABLE domain(i int, n numeric, t text, b blob);
sqlite> INSERT INTO domain VALUES (3.142,3.142,3.142,3.142);
sqlite> INSERT INTO domain VALUES ('3.142','3.142','3.142','3.142');
sqlite> INSERT INTO domain VALUES (3142,3142,3142,3142);
sqlite> INSERT INTO domain VALUES (x'3142',x'3142',x'3142',x'3142');
sqlite> INSERT INTO domain VALUES (null,null,null,null);
sqlite> SELECT ROWID,typeof(i),typeof(n),typeof(t),typeof(b) FROM domain;
```
返回：
```
rowid????? typeof(i)? typeof(n) typeof(t)? typeof(b)
1??????????? real???????? real???????? text???????? real
2??????????? real???????? real???????? text???????? text
3??????????? integer??? integer??? text???????? integer
4??????????? blob??????? blob??????? blob??????? blob
5??????????? null???????? null???????? null???????? null
```
下面的SQL說明存儲類的排序情況：
```
sqlite> SELECT ROWID, b, typeof(b) FROM domain ORDER BY b;
```
返回：
```
rowid b typeof(b)
5 NULL null
1 3.142 real
3 3142 integer
2 3.142 text
4 1B blob
sqlite> SELECT ROWID, b, typeof(b), b<1000 FROM domain ORDER BY b;
```
返回：
```
rowid b typeof(b) b<1000
NULL null NULL
1 3.142 real 1
3 3142 integer 1
2 3.142 text 0
4 1B blob 0
```
### 存儲類和類型轉換
關于存儲類，需要關注的另一件事是：存儲類有時會影響到值如何進行比較。特別是SQLite有時在進行比較之前，會將值在數字存儲類(INTEGER和REAL)和TEXT之間進行轉換。為進行二進制的比較，遵循如下規則：
+ 當一個字段值與一個表達式的結果進行比較，字段的親和性會在比較之前應用于表達式的結果。
+ 當兩個字段值進行比較，如果一個字段擁有INTEGER或NUMERIC親和性而另一個沒有，NUMERIC親和性會應用于非NUMERIC字段的TEXT值。
+ 當兩個表達式進行比較，SQLite不做任何轉換。如果兩個表達式有相似的存儲類，則直接按它們的值進行比較；否則按類值進行比較。
請看下面例子：
```
sqlite> select ROWID,b,typeof(i),i>'2.9' from domain ORDER BY b;
rowid b typeof(i i>'2.9'
5 NULL null NULL
1 3.142 real 1
3 3142 integer 1
2 3.142 real 1
4 1B blob 1
```
### 也算是“強類型(STRICT TYPING)”
如果你需要比類型親和性更強的域完整性，可以使用CHECK約束。你可以使用一個單獨的內置函數和一個CHECK約束來實現一個“假的”強類型。
## 事務
事務定義了一組SQL命令的邊界，這組命令或者作為一個整體被全部執行，或者都不執行。事務的典型實例是轉帳。
## 事務的范圍
事務由3個命令控制：BEGIN、COMMIT和ROLLBACK。BEGIN開始一個事務，之后的所有操作都可以取消。COMMIT使BEGIN后的所有命令得到確認；而ROLLBACK還原BEGIN之后的所有操作。如：
```
sqlite> BEGIN;
sqlite> DELETE FROM foods;
sqlite> ROLLBACK;
sqlite> SELECT COUNT(*) FROM foods;
COUNT(*)
412
```
上面開始了一個事務，先刪除了foods表的所有行，但是又用ROLLBACK進行了回卷。再執行SELECT時發現表中沒發生任何改變。
SQLite默認情況下，每條SQL語句自成事務(自動提交模式)。
## 沖突解決
如前所述，違反約束會導致事務的非法結束。大多數數據庫(管理系統)都是簡單地將前面所做的修改全部取消。
SQLite有其獨特的方法來處理約束違反(或說從約束違反中恢復)，被稱為沖突解決。
如：
```
sqlite> UPDATE foods SET id=800-id;
SQL error: PRIMARY KEY must be unique
```
SQLite提供5種沖突解決方案：REPLACE、IGNORE、FAIL、ABORT和ROLLBACK。
+ REPLACE: 當發違反了唯一完整性，SQLite將造成這種違反的記錄刪除，替代以新插入或修改的新記錄，SQL繼續執行，不報錯。
+ IGNORE
+ FAIL
+ ABORT
+ ROLLBACK
## 數據庫鎖
在SQLite中，鎖和事務是緊密聯系的。為了有效地使用事務，需要了解一些關于如何加鎖的知識。
SQLite采用粗放型的鎖。當一個連接要寫數據庫，所有其它的連接被鎖住，直到寫連接結束了它的事務。SQLite有一個加鎖表，來幫助不同的寫數據庫都能夠在最后一刻再加鎖，以保證最大的并發性。
SQLite使用鎖逐步上升機制，為了寫數據庫，連接需要逐級地獲得排它鎖。SQLite有5個不同的鎖狀態：未加鎖(UNLOCKED)、共享(SHARED)、保留(RESERVED)、未決(PENDING)和排它(EXCLUSIVE)。每個數據庫連接在同一時刻只能處于其中一個狀態。每種狀態(未加鎖狀態除外)都有一種鎖與之對應。
最初的狀態是未加鎖狀態，在此狀態下，連接還沒有存取數據庫。當連接到了一個數據庫，甚至已經用BEGIN開始了一個事務時，連接都還處于未加鎖狀態。
未加鎖狀態的下一個狀態是共享狀態。為了能夠從數據庫中讀(不寫)數據，連接必須首先進入共享狀態，也就是說首先要獲得一個共享鎖。多個連接可以同時獲得并保持共享鎖，也就是說多個連接可以同時從同一個數據庫中讀數據。但哪怕只有一個共享鎖還沒有釋放，也不允許任何連接寫數據庫。
如果一個連接想要寫數據庫，它必須首先獲得一個保留鎖。一個數據庫上同時只能有一個保留鎖。保留鎖可以與共享鎖共存，保留鎖是寫數據庫的第1階段。保留鎖即不阻止其它擁有共享鎖的連接繼續讀數據庫，也不阻止其它連接獲得新的共享鎖。
一旦一個連接獲得了保留鎖，它就可以開始處理數據庫修改操作了，盡管這些修改只能在緩沖區中進行，而不是實際地寫到磁盤。對讀出內容所做的修改保存在內存緩沖區中。
當連接想要提交修改(或事務)時，需要將保留鎖提升為排它鎖。為了得到排它鎖，還必須首先將保留鎖提升為未決鎖。獲得未決鎖之后，其它連接就不能再獲得新的共享鎖了，但已經擁有共享鎖的連接仍然可以繼續正常讀數據庫。此時，擁有未決鎖的連接等待其它擁有共享鎖的連接完成工作并釋放其共享鎖。
一旦所有其它共享鎖都被釋放，擁有未決鎖的連接就可以將其鎖提升至排它鎖，此時就可以自由地對數據庫進行修改了。所有以前對緩沖區所做的修改都會被寫到數據庫文件。
## 死鎖
為什么需要了解鎖的機制呢？為了避免死鎖。
考慮下面表4-7所假設的情況。兩個連接——A和B——同時但完全獨立地工作于同一個數據庫。A執行第1條命令，B執行第2、3條，等等。
表4-7 一個死鎖的假設情況
| A連接 | B連接 |
| --- | --- |
| sqlite> BEGIN; |
| | sqlite> BEGIN; |
| | sqlite> INSERT INTO foo VALUES ('x'); |
| sqlite> SELECT * FROM foo; |
| | sqlite> COMMIT; |
| | SQL error: database is locked |
| sqlite> INSERT INTO foo VALUES ('x'); |
| SQL error: database is locked |
兩個連接都在死鎖中結束。B首先嘗試寫數據庫，也就擁有了一個未決鎖。A再試圖寫，但當其INSERT語句試圖將共享鎖提升為保留鎖時失敗。
為了討論的方便，假設連接A和B都一直等待數據庫可寫。那么此時，其它的連接甚至都不能夠再讀數據庫了，因為B擁有未決鎖(它能阻止其它連接獲得共享鎖)。那么時此，不僅A和B不能工作，其它所有進程都不能再操作此數據庫了。
如果避免此情況呢？當然不能讓A和B通過談判解決，因為它們甚至不知道彼此的存在。答案是采用正確的事務類型來完成工作。
## 事務的種類
SQLite有三種不同的事務，使用不同的鎖狀態。事務可以開始于：DEFERRED、MMEDIATE或EXCLUSIVE。事務類型在BEGIN命令中指定：
```
BEGIN [ DEFERRED | IMMEDIATE | EXCLUSIVE ] TRANSACTION;
```
一個DEFERRED事務不獲取任何鎖(直到它需要鎖的時候)，BEGIN語句本身也不會做什么事情——它開始于UNLOCK狀態。默認情況下就是這樣的，如果僅僅用BEGIN開始一個事務，那么事務就是DEFERRED的，同時它不會獲取任何鎖；當對數據庫進行第一次讀操作時，它會獲取SHARED鎖；同樣，當進行第一次寫操作時，它會獲取RESERVED鎖。
由BEGIN開始的IMMEDIATE事務會嘗試獲取RESERVED鎖。如果成功，BEGIN IMMEDIATE保證沒有別的連接可以寫數據庫。但是，別的連接可以對數據庫進行讀操作；但是，RESERVED鎖會阻止其它連接的BEGIN IMMEDIATE或者BEGIN EXCLUSIVE命令，當其它連接執行上述命令時，會返回SQLITE_BUSY錯誤。這時你就可以對數據庫進行修改操作了，但是你還不能提交，當你COMMIT時，會返回SQLITE_BUSY錯誤，這意味著還有其它的讀事務沒有完成，得等它們執行完后才能提交事務。
EXCLUSIVE事務會試著獲取對數據庫的EXCLUSIVE鎖。這與IMMEDIATE類似，但是一旦成功，EXCLUSIVE事務保證沒有其它的連接，所以就可對數據庫進行讀寫操作了。
上節那個例子的問題在于兩個連接最終都想寫數據庫，但是它們都沒有放棄各自原來的鎖，最終，SHARED鎖導致了問題的出現。如果兩個連接都以BEGIN IMMEDIATE開始事務，那么死鎖就不會發生。在這種情況下，在同一時刻只能有一個連接進入BEGIN IMMEDIATE，其它的連接就得等待。BEGIN IMMEDIATE和BEGIN EXCLUSIVE通常被寫事務使用。就像同步機制一樣，它防止了死鎖的產生。
基本的準則是：如果你正在使用的數據庫沒有其它的連接，用BEGIN就足夠了。但是，如果你使用的數據庫有其它的連接也會對數據庫進行寫操作，就得使用BEGIN IMMEDIATE或BEGIN EXCLUSIVE開始你的事務。
## 數據庫管理
數據庫管理用于控制數據庫如何操作。從SQL的角度，數據庫管理包括一些主題如會視圖(view)、觸發器(trigger)和索引(indexe)。另外，SQLite包括自己一些獨特的管理，如數據庫pragma，可以用來配置數據庫參數。
## 視圖
### 物化的視圖
在關系模型中稱為數據可修改的視圖。
## 索引
### 索引的利用
理解索引何時被利用及何時不被利用是重要的。SQLite有明確的條件來決定是否使用索引。如果可能，在WHERE子名中有下列表達式時，SQLite將使用單字段索引：
```
column {=|>|>=|<=|<} expression
expression {=|>|>=|<=|<} column
column IN (expression-list)
column IN (subquery)
```
多字段索引的使用有很明確的條件。這最好用例子來說。假設你有如下定義的一個表：
```
CREATE TABLE foo (a,b,c,d);
```
## 觸發器
當特定的表上發生特定的數據庫事件時，觸發器會執行特定的SQL命令。創建觸發器的一般語法如下：
```
CREATE [TEMP|TEMPORARY] TRIGGER name [BEFORE|AFTER]
?[INSERT|DELETE|UPDATE|UPDATE OF columns] ON table
action
```
### UPDATE觸發器
不同于INSERT and DELETE觸發器，UPDATE觸發器可以定義在一個表的特定的字段上。The general form of this kind of trigger is as follows:
```
CREATE TRIGGER name [BEFORE|AFTER] UPDATE OF column ON table
action
```
The following is a SQL script that shows an UPDATE trigger in action:
```
.h on
.m col
.w 50
.echo on
CREATE TEMP TABLE log(x);
CREATE TEMP TRIGGER foods_update_log UPDATE of name ON foods
BEGIN
??? INSERT INTO log VALUES('updated foods: new name=' || NEW.name);
END;
BEGIN;
UPDATE foods set name='JUJYFRUIT' where name='JujyFruit';
SELECT * FROM log;
ROLLBACK;
```
### 錯誤處理
定義一個事件的before觸發器給了你一個阻止事件發生的機會。before觸發器可以實現新的完整性約束。SQLite為觸發器提供了一個稱為RAISE()的特殊SQL函數，可以在觸發器體中喚起一個錯誤。RAISE如下定義：
```
RAISE(resolution, error_message);
```
### 使用觸發器的外鍵約束
在SQLite中，觸發器最有趣的應用之一是實現外鍵約束。為了進一步了解觸發器，我將用這個想法在foods表和food_types表之間實現外鍵。
## 附加(Attaching)數據庫
SQLite允許你用ATTACH命令將多個數據庫“附加”到當前連接上來。當你附加了一個數據庫，它的所有內容在當前數據庫文件的全局范圍內都是可存取的。ATTACH的語法為：
```
ATTACH [DATABASE] filename AS database_name;
```
## 清潔數據庫
SQLite有兩個命令用于數據庫清潔——REINDEX和VACUUM。REINDEX用于重建索引，有兩種形式：
```
REINDEX collation_name;
REINDEX table_name|index_name;
```
第一種形式利用給定的排序法名稱重新建立所有的索引。
VACUUM通過重建數據庫文件來清除數據庫內所有的未用空間。
## 數據庫配置
SQLite沒有配置文件。所有這些配置參數都是用pragma來實現。Pragma以獨特的方式工作，有些像變量，又有些像命令。
### 連接緩沖區大小
緩沖區尺寸pragma控制一個連接可以在內存中使用多少個數據庫頁。要查看當前緩沖區大小的默認值，執行：
```
sqlite> PRAGMA cache_size;
cache_size
2000
```
要改變緩沖區大小，執行：
```
sqlite> PRAGMA cache_size=10000;
sqlite> PRAGMA cache_size;
cache_size
10000
```
### 獲得數據庫信息
可以使用數據庫的schema pragma來獲得數據庫信息，定義如下：
+ database_list: Lists information about all attached databases.
+ index_info: Lists information about the columns within an index. It takes an index name as an argument.
+ index_list: Lists information about the indexes in a table. It takes a table name as an argument.
+ table_info: Lists information about all columns in a table.
請看下面示例：
```
sqlite> PRAGMA database_list;
seq name file
0 main /tmp/foods.db
2 db2 /tmp/db
sqlite> CREATE INDEX foods_name_type_idx ON foods(name,type_id);
sqlite> PRAGMA index_info(foods_name_type_idx);
seqn cid name
0 2 name
1 1 type_id
sqlite> PRAGMA index_list(foods);
seq name unique
0 foods_name_type_idx 0
sqlite> PRAGMA table_info(foods);
cid name type notn dflt pk
0 id integer 0 1
1 type_id integer 0 0
2 name text 0 0
```
### 頁大小、編碼和自動排空
The database page size, encoding, and autovacuuming must be set before a database is created. That is, in order to alter the defaults, you must first set these pragmas before creating any database objects in a new database. The defaults are a 1,024-byte page size and UTF-8 encoding. SQLite supports page sizes ranging from 512 to 32,786 bytes, in powers of 2\. Supported encodings are UTF-8, UTF-16le (little-endian UTF-16 encoding), and UTF-16be (big-endian UTF-16 encoding).
如果使用auto_vacuum pragma，可以使數據庫自動維持最小。一般情況下，當一個事務從數據庫中刪除了數據并提交后，數據庫文件的大小保持不變。當使用了auto_vacuum pragma后，當刪除事務提交時，數據庫文件會自動縮小。
## 系統表
sqlite_master表是一個系統表，它包含數據庫中所有表、視圖、索引和觸發器的信息。例如，foods的當前內容如下：
```
sqlite> SELECT type, name, rootpage FROM sqlite_master;
type??????? name????????????? rootpage
table????????????? episodes 2
table????????????? foods 3
table????????????? foods_episodes 4
table????????????? food_types 5
index????? foods_name_idx 30
table????????????? sqlite_sequence 50
trigger???? foods_update_trg 0
trigger???? foods_insert_trg 0
trigger???? foods_delete_trg 0
```
有關sqlite_master表的結構請參考第2章的“獲得數據庫的Schema信息”一節。
sqlite_master包含一個稱為sql的字段，存儲了創建對象的DDL命令，如：
```
sqlite> SELECT sql FROM sqlite_master WHERE name='foods_update_trg';
```
返回：
```
CREATE TRIGGER foods_update_trg
BEFORE UPDATE OF type_id ON foods
BEGIN
?SELECT CASE
?WHEN (SELECT id FROM food_types WHERE id=NEW.type_id) IS NULL
?THEN RAISE( ABORT,
?'Foreign Key Violation: foods.type_id is not in food_types.id')
END;
END
```
## 查看Query的執行
可以用EXPLAIN命令查看SQLite執行一個查詢的方法。EXPLAIN列出一個SQL命令編譯后的VDBE程序。
```
sqlite> .m col
sqlite> .h on
sqlite> .w 4 15 3 3 3 10 3
sqlite> EXPLAIN SELECT * FROM foods;
addr? opcode?????????? p1?? p2?? p3?? p4????????? p5?? comment
----? ---------------? ---? ---? ---? ----------? ---? -------
0???? Trace??????????? 0??? 0??? 0??????????????? 00
1???? Goto???????????? 0??? 11?? 0??????????????? 00
2???? OpenRead ????????0??? 7??? 0??? 3?????????? 00
3???? Rewind?????????? 0??? 9??? 0??????????????? 00
4???? Rowid??????????? 0??? 1??? 0??????????????? 00
5???? Column?????????? 0??? 1??? 2??????????????? 00
6???? Column?????????? 0??? 2??? 3??????????????? 00
7???? ResultRow??????? 1??? 3??? 0??????????????? 00
8???? Next???????????? 0??? 4??? 0??????????????? 01
9???? Close??????????? 0??? 0??? 0??????????????? 00
10??? Halt???????????? 0??? 0??? 0??????????????? 00
11??? Transaction????? 0??? 0??? 0??????????????? 00
12??? VerifyCookie???? 0??? 40?? 0??????????????? 00
13??? TableLock??????? 0??? 7??? 0??? foods?????? 00
14??? Goto???????????? 0??? 2??? 0??????????????? 00
```
