第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的子句

图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二目操作符

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存储类

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 一个死锁的假设情况

两个连接都在死锁中结束。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