1

    SQLite 权威指南

    The Definitive Guide to SQLite

    (内容摘要)

    Michael Owens

    Copyright . 2006 by Michael Owens

    本书的示例代码可到http:www.apress.com下载。

    2

    推荐者的话

    最近对SQLite很感兴趣，认真学习有一个多月了。

    学习时基本找不到既好又系统的中文文章，也买不到好的中文书籍，看来 SQLite 在国内还

    是不够流行，这么好的东西，可惜了。

    以我中等偏下的眼界，《The Definitive Guide to SQLite》是我所见到的最好的“SQLite入门+

    大全”了，可惜也是英文的。实在找不到别的，也只好看它了，尽管我英语很不好。

    由于英语很不好，又因为是打字员出身的干部，所以多年来养成了一个更不好的毛病，就是

    在不得不看英文资料时总喜欢一边看一边翻，主要是怕下次再看时还是看不懂。看《The

    Definitive Guide to SQLite》时这个毛病也没改，当然了，看的时候就是挑着看的，翻的也只

    是书中的一小部分了。

    一般情况下看完也就看完了，很少有 “下次再看”的机会，这次例外。 由于越学越觉着 SQLite

    好玩，就想向身边的人也介绍一下，就“再看”了。越看越羞愧，本来英语就差，还随看随

    翻，结果可想而知。但由于没什么动力，也就无意再重新润色了，就这样吧，反正也没什么

    人看，估计遗害不会太广。

    SQLite 是没有版权的，但这本书却是受版权保护的，也不知我这样做是否合法。发到网上

    也只是想做一个好事，响应 SQLite 的共享精神。估计不会有人来告我吧，反正我没钱。另

    外，我也只翻译了书中很小的一部分，也许根本算不上翻译(不能乱抬高自己)，就算是对

    SQLite 和《The Definitive Guide to SQLite》一书的一个推荐吧，同样对 SQLite 感兴趣但又

    看不懂我的中文的兄弟，强烈建议看原文。感谢 Richard Hipp 编出这么好的程序，感谢

    Michael Owens写出这么好的书。

    “空转”只是我的网名之一，网上网下知之者甚少，也就是一起骑车的几个人知道吧。如果

    本文对您能有一点点帮助，也算是我对 SQLite 做了一点贡献吧。本文中带有“空注”的内

    容是我个人所做的简单说明和忏悔，与原作者无关(以我的翻译水平，估计全文跟原作者都

    没什么关)。

    接触 SQLite 时间不长，所以本文难免会有很多错误，不是故意误导大家，是真的水平低。

    如果有兄弟想对我提出指导，我的邮箱是：njgaoyi@yahoo.com.cn。如果我没有回信，不是

    因为不想回，是因为我很少上网，在此先行谢过。

    分析源程序时，发现每个SQLite 源文件的头部都有这样一段话：

    The author disclaims copyright to this source code. In place of a legal notice, here is a blessing:

    May you do good and not evil.

    May you find forgiveness for yourself and forgive others.

    May you share freely, never taking more than you give.

    这几句话我很喜欢，翻译不好，就拿原文出来吧，与大家共勉。

    空转

    Ver 1.00: 2009-11-07 于南京

    (如果以后有时间、兴趣，就把翻译过的内容好好修改一下，或者再多翻一些。但愿还有以

    后的版本)

    3

    总目录

    ■前言

    ■第1章 SQLite介绍

    ■第2章 入门

    ■第3章 关系模型

    ■第4章 SQL

    ■第5章 设计和概念

    ■第6章 核心C API

    ■第7章 扩充C API

    ■第8章 语言扩展

    ■第9章 SQLite内核

    ■附录A SQL参考

    ■附录B C API参考

    ■附录C Codd的12条准则

    ■索引 4

    目录

    SQLite 权威指南..............................................................................................................................1

    总目录..............................................................................................................................................3

    目录..................................................................................................................................................4

    前言..................................................................................................................................................1

    第1章 SQLite介绍......................................................................................................................2

    内嵌式数据库..........................................................................................................................2

    开发者的数据库......................................................................................................................3

    管理员的数据库......................................................................................................................3

    SQLite的历史..........................................................................................................................3

    谁使用SQLite..........................................................................................................................4

    体系结构..................................................................................................................................4

    接口(Interface)..................................................................................................................5

    编译器(Compiler).............................................................................................................5

    虚拟机(Virtual Machine)..................................................................................................5

    后端(Back-end) ................................................................................................................6

    工具和测试代码(Utilities and Test Code) .......................................................................7

    SQLite的特色..........................................................................................................................7

    零配置..............................................................................................................................7

    兼容性..............................................................................................................................7

    紧凑性..............................................................................................................................7

    简单..................................................................................................................................8

    适应性..............................................................................................................................8

    不受拘束的授权..............................................................................................................8

    可靠性..............................................................................................................................8

    易用性..............................................................................................................................8

    性能和限制..............................................................................................................................9

    附加信息..................................................................................................................................9

    第2章 入门................................................................................................................................10

    从哪得到SQLite....................................................................................................................10

    在Windows上使用SQLite...................................................................................................10

    获得命令行程序............................................................................................................10

    获得SQLite的动态链接库(DLL).................................................................................10

    在Windows环境下编译SQLite源代码......................................................................10

    用Microsoft Visual C++构建 SQLite DLL ................................................................... 11

    用Microsoft Visual C++构建 SQLite CLP.................................................................... 11

    使用SQLite数据库............................................................................................................... 11

    Shell模式下使用CLP................................................................................................... 11

    在命令行方式下执行CLP ............................................................................................15

    数据库管理............................................................................................................................15

    创建、备份和删除数据库.............................................................................................15 5

    获得数据库文件的信息.................................................................................................15

    其它 SQLite工具...................................................................................................................16

    第3章 关系模型........................................................................................................................17

    第4章 SQL.................................................................................................................................18

    关系模型................................................................................................................................18

    查询语言........................................................................................................................18

    SQL的发展....................................................................................................................18

    示例数据库............................................................................................................................18

    建立................................................................................................................................19

    运行示例........................................................................................................................19

    语法........................................................................................................................................19

    命令................................................................................................................................20

    常量................................................................................................................................20

    保留字和标识符............................................................................................................20

    注释................................................................................................................................20

    创建一个数据库....................................................................................................................21

    创建表............................................................................................................................21

    改变表............................................................................................................................21

    在数据库中查询....................................................................................................................22

    关系操作........................................................................................................................22

    操作管道........................................................................................................................23

    过滤................................................................................................................................23

    限定和排序....................................................................................................................25

    函数(Function)和聚合(Aggregate) ................................................................................26

    分组(Grouping) ..............................................................................................................27

    去掉重复........................................................................................................................27

    多表连接........................................................................................................................27

    名称和别名....................................................................................................................28

    修改数据................................................................................................................................28

    插入记录........................................................................................................................28

    修改记录........................................................................................................................28

    删除记录........................................................................................................................29

    数据完整性............................................................................................................................29

    实体完整性....................................................................................................................29

    域完整性........................................................................................................................30

    存储类(Storage Classes).................................................................................................31

    弱类型(manifest typing) .................................................................................................32

    类型亲和性(Type Affinity) ............................................................................................33

    事务........................................................................................................................................35

    事务的范围....................................................................................................................36

    冲突解决........................................................................................................................36

    数据库锁........................................................................................................................36

    死锁................................................................................................................................37

    事务的种类....................................................................................................................38 6

    数据库管理............................................................................................................................38

    视图................................................................................................................................38

    索引................................................................................................................................39

    触发器............................................................................................................................39

    附加(Attaching)数据库..................................................................................................40

    清洁数据库....................................................................................................................40

    数据库配置....................................................................................................................40

    系统表............................................................................................................................42

    查看Query的执行.........................................................................................................42

    第5章 设计和概念....................................................................................................................44

    API..........................................................................................................................................44

    SQLite版本3的新特性................................................................................................44

    主要的数据结构............................................................................................................45

    核心API .........................................................................................................................46

    操作控制........................................................................................................................52

    扩充API .........................................................................................................................53

    事务........................................................................................................................................54

    事务的生命周期............................................................................................................54

    锁的状态........................................................................................................................55

    读事务............................................................................................................................56

    写事务............................................................................................................................56

    调整页缓冲区................................................................................................................58

    等待加锁........................................................................................................................59

    编码........................................................................................................................................60

    使用多个连接................................................................................................................60

    表锁................................................................................................................................61

    有趣的临时表................................................................................................................62

    定案的重要性................................................................................................................63

    共享缓冲区模式............................................................................................................63

    第6章 核心C API......................................................................................................................65

    封装的查询............................................................................................................................65

    连接和断开连接............................................................................................................65

    执行Query .....................................................................................................................66

    字符串处理....................................................................................................................69

    Get Table查询................................................................................................................70

    预处理的查询........................................................................................................................71

    取记录............................................................................................................................73

    参数化的查询................................................................................................................76

    错误和意外............................................................................................................................76

    处理错误........................................................................................................................76

    处理忙状态....................................................................................................................78

    操作控制................................................................................................................................78

    提交Hook函数..............................................................................................................78

    回卷Hook 函数..............................................................................................................78 7

    修改Hook函数..............................................................................................................78

    授权函数........................................................................................................................79

    线程........................................................................................................................................84

    共享缓冲区模式............................................................................................................85

    线程和内存管理............................................................................................................85

    第7章 扩充C API......................................................................................................................86

    API..........................................................................................................................................86

    注册函数........................................................................................................................86

    步进函数........................................................................................................................86

    返回值............................................................................................................................86

    函数........................................................................................................................................86

    返回值............................................................................................................................86

    一个完整的例子............................................................................................................86

    一个实际的应用程序.....................................................................................................88

    聚合........................................................................................................................................88

    一个实际的例子............................................................................................................88

    排序法....................................................................................................................................90

    排序法定义....................................................................................................................90

    一个简单的例子............................................................................................................90

    按需排序(Collation on Demand)....................................................................................93

    一个实际的应用程序.....................................................................................................93

    第8章 语言扩展......................................................................................................................100

    第9章 SQLite内核..................................................................................................................101

    虚拟数据库引擎(VDBE) .....................................................................................................101

    栈(Stack) .......................................................................................................................103

    程序体..........................................................................................................................103

    程序开始与停止..........................................................................................................104

    指令的类型..................................................................................................................105

    B-Tree和Pager模型...........................................................................................................105

    数据库文件格式..........................................................................................................106

    B-Tree API ....................................................................................................................109

    编译器.................................................................................................................................. 111

    分词器(Tokenizer) ........................................................................................................ 111

    分析器(Parser).............................................................................................................. 112

    代码生成器(Code Generator)....................................................................................... 113

    优化.............................................................................................................................. 114

    1

    前言

    2000年春天， 当我刚开始编写SQLite时， 根本没想到它会在编程社区受到如此强烈的认可。

    今天，有成百万的 SQLite 拷贝在默默地运行，在计算机中，或在不同公司生产的各种各样

    的小设备中。你可能已经在无意识的情况下使用过 SQLite，在你的手机、MP3 或机顶盒里

    可能就有 SQLite。在你的计算机里也可能至少会有一个 SQLite 的拷贝，它可能来自 Apple

    的Mac OS X，或者在大多数的Linux版本中，或者在 Windows中安装某个第三方软件时。

    很多Web网站的后台都使用SQLite， 这要感谢它已经被包含为PHP5语言的一部分。 SQLite

    也被用于很多航空电子设备、建模和仿真程序、工业控制、智能卡、决策支持包、医药信息

    系统等。因为没有SQLite 使用的全面报告，所以，肯定还有很多我不知道的SQLite 部署。

    SQLite 的普及很大程度上应该归功于 Michael Owens。Mike在 The Linux Journal (June 2003)

    和 The CC++ Users Journal (March 2004)上的文章吸引了无数程序员。每篇文章发表后，SQLite 网站的访问量都会显著上升。通过这本书你可以看到 Mike的才华和他所做的大量工

    作，相信你不会失望。本书包含了关于 SQLite 所需要了解的所有内容，你应该一直把它放

    在伸手可及的地方。

    SQLite是自由软件。 尽管我是它的架构师和代码的主要编写者，但SQLite 并不是我的程序。

    SQLite 不属于任何人，也不在版权的保护范围之内。所有曾经为 SQLite 项目贡献过代码的

    人都签署过一个宣誓书将他们的贡献发布到公共域，我把这些宣誓书的原件保存在办公室的

    保险箱里。我还尽力保证在 SQLite 中不使用专利算法，这些预防措施意味着你可以以任何

    形式使用SQLite，而不需要付版税、许可证费用或受到其它任何限制。

    SQLite 仍然在发展。但我和其他开发者都坚守它的核心价值。我们将保持代码的小规模—

    —核心库不会超过 250KB。我们将保持公共 API 和文件格式的向上兼容性。我们将继续保

    证SQLite是充分测试的和无bug的。我们希望你总是能够将新版本的 SQLite 放到你老的程

    序中，既得到它新的特性和优化，又不需要或仅需要很少的代码改动，且不需要做进一步的

    调试。2004年，我们将SQLite从版本2 升级到版本 3 时确实没能保持向上兼容性，但从那

    以后，我们已经能够达到上述所有目标并准备在将来继续这样做。没有 SQLite 版本 4 的计

    划。

    真诚希望你觉着 SQLite 是有用的，我代表 SQLite 的所有贡献者保证，使用 SQLite 你会：

    做出美好的产品，你的产品将会是快速、稳定和易用的。寻求宽恕并宽恕他人。因为你已经

    免费地得到了 SQLite，也请你免费地给予他人一些东西作为回报。做一回志愿者，贡献出

    其它的软件项目或找到其它途径来回报。

    Richard Hipp

    Charlotte, NC

    April 11, 2006 2

    第 1 章 SQLite 介绍

    SQLite 是一个开源的、内嵌式的关系型数据库。它最初发布于 2000年，在便携性、易用性、紧凑性、有效性和可靠性方面有突出的表现。

    内嵌式数据库

    SQLite是一个内嵌式的数据库。

    数据库服务器就在你的程序中，其好处是不需要网络配置和管理。数据库的服务器和客户端

    运行在同一个进程中。这样可以减少网络访问的消耗，简化数据库管理，使你的程序部署起

    来更容易。所有需要你做的都已经和你的程序一起编译好了。

    如图1-1所示。一个Perl脚本、一个标准CC++程序和一个使用PHP 编写的Apache进程都

    使用SQLite。Perl脚本导入DBI::SQLite 模板，并通过它来访问 C API。PHP采用与 C相似

    的方式访问C API。总之，它们都需要访问C API。尽管它们每个进程中都有独立的数据库

    服务器，但它们可以操作相同的数据库文件。SQLite 利用操作系统功能来完成数据的同步

    和加锁。

    图1-1 内嵌的主进程中的SQLite

    目前市场上有多种为内嵌应用所设计的关系型数据库产品，如 Sybase SQL Anywhere、InterSystems Caché、Pervasive PSQL和微软的Jet Engine。有些厂家从他们的大型数据库产

    品翻新出内嵌式的变种，如 IBM 的 DB2 Everyplace、Oracle 的 10g 和微软的 SQL Server

    Desktop Engine。 开源的数据库MySQL和 Firebird 都提供内嵌式的版本。 在所有这些产品中， 3

    仅有两个是完全开放源代码的且不收许可证费用——Firebird 和 SQLite。在这两个当中，仅

    有一个是专门为内嵌式应用设计的——SQLite。

    开发者的数据库

    SQLite 具有多方面的特性。它是一个数据库，一个程序库，一个命令行工具，也是一个学

    习关系型数据库的很好的工具。确实有很多途径可以使用它——内嵌环境、网站、操作系统

    服务、脚本语言和应用程序。对于程序员来说，SQLite 就象一个数据传送带，提供了一种

    方便的将应用程序绑定的数据的方法。就象传送带一样，对SQLite 的使用没有终点。

    除了仅仅作为一个存储容器，SQLite 还可以作为一个单纯的数据处理的工具。如果大小和

    复杂性合适，使用 SQLite 可以很容易地将应用程序所使用的数据结构转化为表，并保存在

    一个内在数据库中。用此方法，你可以操作互相关联的数据，可以完成很繁重的任务页不必

    写自己的算法来对数据结构操作和排序。如果你是一个程序员，想像一下在你的程序中自行

    完成下面 SQL语句所代表的工作需要多少代码：

    SELECT AVG(z-y) FROM table GROUP BY x

    HAVING x > MIN(z) OR x < MAX(y)

    ORDER BY y DESC LIMIT 10 OFFSET 3;

    SQLite 还是一个很好的学习程序设计的工具，通过它可以研究很多计算机科学的课题。分

    析器、分词器、虚拟机、Btree 算法、高整缓存、程序体系结构，通过这些内容可以搞清楚

    很多计算机科学的经典概念。SQLite 的模块化、小型化和简易性，使你可以很容易地专门

    研究其中的一个问题。

    管理员的数据库

    SQLite 不仅是程序员的数据库，它对系统管理员也很有用。它很小、紧凑而精致，就像一

    些Unix 的常用工具，如find、rsync或grep。SQLite 提供了命令行工具供用户交互操作。

    另外，对于关系型数据库的初学者来说，SQLite 是一个学习各种关系相关概念的方便的学

    习工具。它可以很快很容易地安装在各类操作系统中，它的数据库文件可以自由共享页不需

    要任何转换。它具有关系型数据库的各种特色而又不令人生畏。它的程序和数据库文件仅用

    U 盘就能传递。

    SQLite的历史

    从某个角度来说， SQLite 最初的构思是在一条军舰上进行的。 SQLite 的作者D. Richard Hipp

    当时正在为美国海军编制一种使用在导弹驱逐舰上的程序。那个程序最初是运行在

    Hewlett-Packard Unix (HPUX)上，后台使用Informix数据库。对那个程序来说，Informix有

    点儿太强大了。一个有经验的数据库管理员(DBA)可能需要一整天来对它进行安装和升级，如果没经验，这个工作就可能永远也做不完了。

    2000年一月，Hipp开始和一个同事讨论关于创建一个简单的内嵌式 SQL数据库的想法，这

    个数据库将使用GNU DBM B-Tree library (gdbm)做后台，同时这个数据库将不需要安装和

    管理支持。后来，当有些空闲时间时，Hipp就开始实施这项工作，并在 2000年的八月份发

    布了SQLite的1.0版。 4

    按照原定计划， SQLite 1.0 用gdbm来做存储管理。 但后来， Hipp很快就换成了自己的 B-tree，以支持事务和记录按主键的存储。随着最初的升级，SQLite 在功能和用户数上都得到了稳

    步的发展。在2001年中期，很多项目——开源的或商业的——都开始使用 SQLite。在那以

    后的几年中， 开源社区的其他成员开始为他们喜欢的程序设计语言编写SQLite 扩展。 SQLite

    的 ODBC 接口可以为 Perl、Python、Ruby、Java 和其它主流的程序设计语言提供支持，这

    证明了SQLite有广阔的应用前景。

    2004年， SQLite从版本2升级到版本3， 这是一次大升级。 主要目的是增加内置的对UTF-8、UTF-16及用户定义字符集的支持。While 3.0 was originally slated for release in summer 2005,America Online provided the necessary funding to see that it was completed by July 2004. 除国

    际化功能外，版本 3 的其它新特性包括：经过修补的 C API，更紧凑的数据库文件格式(比

    原来节省25%的空间)， 弱类型，大二进制对象(BLOB)的支持， 64-bit的 ROWID， autovacuum

    和改进了的并发控制。尽管增加了这一系列新特性，版本 3 的运行库仍然小于 240K字节。

    Another improvement in version 3 was a good code cleanup—revisiting and rewriting, or

    otherwise throwing out extraneous stuff accumulated in the 2.x series.

    SQLite 持续增长并始终坚持其最初的设计目标：简单、弹性、紧凑、速度和彻底的易用。

    本书出版时，SQLite已经增加了CHECK约束，下面就要增加外键约束，再下面呢？

    谁使用 SQLite

    当前，SQLite已经被多种软件和产品所使用。它被用在Apple的 Mac OS X操作系统中，被

    用作其 CoreData 应用程序架构的一部分。它还应用于 Safari 的 Web 浏览器、Mail.app 的电

    子邮件程序、RSS的管理、Apple的Aperture照片软件。

    尽管SQLite很少做广告，但它还是被用在了多种消费类产品中。

    体系结构

    SQLite 拥有一个精致的、模块化的体系结构，并引进了一些独特的方法进行关系型数据库

    的管理。它由被组织在3个子系统中的8个独立的模块组成，如图 1-2所示。这个模型将查

    询过程划分为几个不连续的任务，就像在流水线上工作一样。在体系结构栈的顶部编译查询

    语句，在中部执行它，在底部处理操作系统的存储和接口。 5

    图1-2 SQLite的体系结构

    接口(Interface)

    接口由SQLite C API组成，也就是说不管是程序、脚本语言还是库文件，最终都是通过它

    与SQLite交互的(我们经常使用的ODBCJDBC 最后也会转化为相应C API的调用)。

    编译器(Compiler)

    编译过程从分词器(Tokenizer)和分析器(Parser)开始。它们协作处理文本形式的结构化查询

    (Structured Query Language, SQL)语句，分析其语法有效性，转化为底层能更方便处理的层

    次数据结构——语法树，然后把语法树传给代码生成器(code generator)进行处理。SQLite 分

    词器的代码是手工编写的，分析器代码是由 SQLite 定制的分析器生成器(称为 Lemon)生成

    的。The Lemon parser generator is designed for high performance and takes special precautions to

    guard against memory leaks. 一旦SQL语句被分解为串值并组织到语法树中，分析器就将该

    树下传给代码生成器进行处理。而代码生成器根据它生成一种 SQLite 专用的汇编代码，最

    后由虚拟机(Virtual Machine)执行。

    虚拟机(Virtual Machine)

    架构中最核心的部分是虚拟机，或者叫做虚拟数据库引擎(Virtual DataBase Engine,VDBE)。

    它和Java虚拟机相似，解释执行字节代码。VDBE的字节代码(称为虚拟机语言)由 128个操

    作码(opcodes)构成，主要是进行数据库操作。它的每一条指令或者用来完成特定的数据库操

    作(比如打开一个表的游标、开始一个事务等)，或者为完成这些操作做准备。总之，所有的

    这些指令都是为了满足SQL命令的要求。 VDBE的指令集能满足任何复杂SQL命令的要求。 6

    所有的SQLite SQL语句——从选择和修改记录到创建表、视图和索引——都是首先编译成

    此种虚拟机语言，组成一个独立程序，定义如何完成给定的命令。例如，在 SQLite 的 CLP

    中执行下面语句：

    sqlite> .m col

    sqlite> .h on

    sqlite> .w 4 15 3 3 3 10 3

    sqlite> explain SELECT name FROM episodes LIMIT 10;

    SQLite会显示编译后的VDBE汇编程序，如列表 1-1所示。

    列表1-1 VDBE汇编程序

    addr opcode p1 p2 p3 p4 p5 comment---- --------------- --- --- --- --------------- ---- ----------

    0 Trace 0 0 0 00

    1 Integer 10 1 0 00

    2 MustBeInt 1 0 0 00

    3 IfZero 1 13 0 00

    4 Goto 0 14 0 00

    5 OpenRead 0 2 0 3 00

    6 Rewind 0 12 0 00

    7 Column 0 2 2 00

    8 ResultRow 2 1 0 00

    9 AddImm 1 -1 0 00

    10 IfZero 1 12 0 00

    11 Next 0 7 0 01

    12 Close 0 0 0 00

    13 Halt 0 0 0 00

    14 Transaction 0 0 0 00

    15 VerifyCookie 0 40 0 00

    16 TableLock 0 2 0 episodes 00

    17 Goto 0 5 0 00

    程序由 17 条指令组成。通过对给定的操作数完成特别的操作，这些指令将会返回 episodes

    表前10个记录的name字段的值。episodes表是本书示例数据库的一部分。

    从多个方面都可以看出，VDBE是SQLite的核心：它上面的各模块都是用于创建VDBE程

    序，它下面的各模块都是用于执行VDBE程序，每次执行一条指令。

    后端(Back-end)

    后端由B-tree、页缓冲(page cache，pager)和操作系统接口(即系统调用)构成。B-tree 和 page

    cache共同对数据进行管理。它们操作的是数据库页，这些页具有相同的大小，就像集装箱。

    页里面的“货物”是表示信息的大量 bit，这些信息包括记录、字段和索引入口等。B-tree

    和pager都不知道信息的具体内容，它们只负责“运输”这些页，页不关心这些“集装箱”

    里面是什么。

    B-tree 的主要功能就是索引，它维护着各个页之间的复杂的关系，便于快速找到所需数据。

    它把页组织成树型的结构(这是它名称的由来)，这种树是为查询而高度优化了的。Page 为

    B-tree 服务，为它提供页。Pager的主要作用就是通过 OS 接口在B-tree 和磁盘之间传递页。 7

    磁盘操作是计算机到目前为止所必须做的最慢的事情。所以，pager 尽力提高速度，其方法

    是把经常使用的页存放到内存当中的页缓冲区里，从而尽量减少操作磁盘的次数。它使用特

    殊的算法来预测下面要使用哪些页，从而使B-tree 能够更快地工作。

    工具和测试代码(Utilities and Test Code)

    工具模块中包含各种各样的实用功能，还有一些如内存分配、字符串比较、Unicode转换之

    类的公共服务也在工具模块中。这个模块就是一个包罗万象的工具箱，很多其它模块都需要

    调用和共享它。

    测试模块中包含了无数的回归测试语句，用来检查数据库代码的每个细微角落。这个模块是

    SQLite 性能如此可靠的原因之一。

    SQLite的特色

    尽管SQLite是如此之小，却提供了如此之多的特色和性能。它支持 ANSI SQL92 的一个大

    子集(包括事务、视图、检查约束、关联子查询和复合查询等)，还支持其它很多关系型数据

    库的特色，如触发器、索引、自动增长字段和 LIMITOFFSET 子句等。SQLite 还有很多独

    特的特色，如内在数据库、动态类型和冲突解决(下面解释)。

    如本章开始时所述，在 SQLite 的观念和实现中，都遵循着一系列指导原则。下面就来详述

    这些原则。

    零配置

    从 SQLite 的设计之始，就没准备在应用时使用 DBA。配置和管理 SQLite 就像得到它一样

    简单。SQLite包含了正好适合于一个程序员的脑筋的特色。

    兼容性

    SQLite 在设计时特别注意了兼容性。它可以编译运行在 Windows、Linux、BSD、Mac OS X

    及商用的 Unix 系统如 Solaris、HPUX 和 AIX，还可以应用于很多嵌入式平台如 QNX、VxWorks、Symbian、Palm OS和Windows CE。它可以无缝地工作在 16-bit、32-bit和 64-bit

    体系结构中并且能同时适应字节的大端格式和小端格式。SQLite 的兼容性并不只表现在代

    码上，还表现在其数据库文件上。SQLite 的数据库文件在其所支持的所有操作系统、硬件

    体系结构和字节顺序上都是二进制一致的。你可以在 Sun SPARC 工作站上创建一个 SQLite

    数据库然后在Mac或Windows的机器上——甚至移动电话上——使用它，而不需要做任何

    转换和修改。此外，SQLite 数据库可以支撑 2TB 的数据量(受操作系统限制)，还内置地同

    时支持UTF-8 和UTF-16 编码。

    紧凑性

    SQLite 的设计可以说是功能齐全但体积很小：1个头文件，1 个库，不需要扩展的数据库服 8

    务。所有的东西，包括客户端、服务器和虚拟机等，都被打包在 14兆大小之内。如果在编

    译时去掉一些不需要的特性，程序库可以缩小至 170KB (在 x86硬件平台上使用 GNU C进

    行编译)。此外，还有一个SQLite 的私有版本，大小是 69KB，可以运行在智能卡上(参“附

    加信息”一节)。

    空注：我下载的DLL有500多KB。

    简单

    作为程序库，SQLite 的 API 可以算是最简单最易用的了。SQLite 既有很好的文档又很容易

    望文知意。

    适应性

    SQLite 的几个特性使其成为一个适应性极强的数据库。作为一个内嵌式的数据库，SQLite

    在以下两个方面都做得最好：强有力而可伸缩的关系型数据库前端，简单而紧凑的 B-tree

    后端。

    不受拘束的授权

    SQLite 的全部代码都在公共域中，不需要授权。SQLite的任何一部分都没有附加版权要求。

    所有曾经为 SQLite 项目贡献过代码的人都签署过一个宣誓书将他们的贡献发布到公共域。

    也就是说，无论你如何使用 SQLite 的代码都不会有法律方面的限制。你可以修改、合并、发布、出售或将这些代码用于任何目的，商业和中非商业的，不需要支付任何费用，不会受

    到任何限制。

    可靠性

    SQLite 的源代码不但免费，还编写得很好。SQLite 源代码包含大约 30000 行标准 C 代码，它是干净的、模块化的和完好注释的。SQLite 源代码易理解、易定制。

    SQLite 的核心软件(库和工具)由约30000行代码组成，但分发的程序中还包含有超过 30000

    行的回归测试代码，它们覆盖了 97%的核心代码。也就是说，超过一半的 SQLite 项目代码

    是专门用于回归测试的，也就是说，差不多每写一行功能代码，都要写一行测试代码对它进

    行测试。

    易用性

    SQLite 还提供一些独特的功能来提高易用性，包括动态类型、冲突解决和“附加”多个数

    据库到一个连接的能力。 9

    性能和限制

    SQLite是一个快速数据库。但“快速”这个词本身是一个主观的和不明确的词。诚实地讲，有些事情SQLite能比其它数据库做得快，也有些事情不能。这么说吧，利用 SQLite 提供的

    配置参数，SQLite 是足够快速和高效的。跟大多数其它数据库一样，SQLite 使用 B-tree 处

    理索引，使用B+tree 处理表数据。因此，在对单表进行查询时，SQLite 要快于(或至少相当

    于)其它数据库的速度。

    在一些情况下SQLite 可能不如大型数据库快，但大多数这些情况是可理解的。SQLite 是一

    个内嵌式的数据库，设计用于中小规模的应用程序。这些限制是符合设计目的的。很多新用

    户错误地假设使用 SQLite 可以代替大型关系型数据库，这有时行，但有时不行，依赖于你

    准备用SQLite来做什么。一般情况下，SQLite 在三个主要的方面具有局限性：

    l 并发。

    l 数据库大小。

    l 网络。

    尽管SQLite做得已经很好了，但仍有部分特性未能实现，包括：

    l 外键约束

    空注：SQLite的最新版本3.6.19好像已经支持了。

    l 完整的触发器支持。

    l 完整的ALTER TABLE 支持。

    l 事务嵌套。

    l RIGHT 和FULL OUTER JOIN。

    l 可修改视图。

    l GRANT 和REVOKE。

    附加信息

    SQLite 网站有丰富的信息，包括官方文档、邮件列表、Wiki 和其它的一般信息，它的网址

    是 www.sqlite.org。SQLite 社区也是很有帮助的，你可能从邮件列表中找到任何你所需要的

    东西。另外，SQLite的作者提供了SQLite的专业培训和支持，包括定制程序(如移植到嵌入

    式平台)和增强的SQLite版本，这些版本包括内置了加密功能的版本和为嵌入式应用优化的

    极小化版本。更多的信息可以从www.hwaci.comswsqliteprosupport.html中找到。 10

    第 2 章 入门

    无论您使用何种操作系统，SQLite 都很容易上手。对大多数用户，安装 SQLite 并创建一个

    新的数据库不会超过5分钟，且不需要任何经验。

    空注：本章我只看了Windows操作系统下使用VC的内容。

    从哪得到 SQLite

    SQLite 网站(www.sqlite.org)同时提供 SQLite 的已编译版本和源程序。编译版本可同时适用

    于Windows和Linux。

    有几种形式的二进制包供选择，以适应 SQLite 的不同使用方式。包括：

    l 静态链接的命令行程序(CLP)

    l SQLite动态链接库(DLL)

    l Tcl扩展

    SQLite 源代码以两种形式提供，以适应不同的平台。一种为了在 Windows 下编译，另一种

    为了在POSIX 平台(如Linux, BSD, and Solaris)下编译，这两种形式下源代码本身是没有差

    别的。

    在 Windows上使用 SQLite

    无论你是作为终端用户还是作为程序员来使用 SQLite，SQLite 都可以很容易地安装在

    Windows环境下。本节我们将讨论所有相关的内容——安装二进制包或在最普通的编译环境

    下使用源代码。

    获得命令行程序

    SQLite 命令行程序(CLP)是开始使用SQLite 的一个比较好的选择。

    略，参原文。

    获得 SQLite的动态链接库(DLL)

    SQLite 的 DLL 文件供编译好的程序动态连接 SQLite。大多数使用 SQLite 的软件都会拥有

    自己的SQLite DLL拷贝并随软件自动安装。

    在 Windows 环境下编译 SQLite源代码

    在Windows环境下编译SQLite源代码是很简单的。根据你所使用的编译器和你要做什么，有几种方法来编译SQLite。最常见的环境是Microsoft Visual C++或 MinGW，本节都会加以 11

    介绍 。 关 于 使用其 它 编译 器 编译 SQLite 的内容，可 参考 SQLite Wiki

    (www.sqlite.orgcvstracwiki?p=HowToCompile)。

    用 Microsoft Visual C++构建 SQLite DLL

    通过以下步聚，可使用源代码，在Visual C++上构建 SQLite DLL：

    1. 启动Visual Studio。在解包的SQLite 源程序目录中创建一个新的 DLL“空”项目。

    高：不同版本操作略有不同，不详细解释了。

    2. 将全部SQLite 源文件加入到项目中来。包括所有的.c文件和.h文件。除了：

    shell.c：该文件包括main函数，用于创建 CLP 可执行程序。

    tclsqlite.c：该文件用于TCL支持。

    空注：我使用的版本(sqlite-source-3_6_18.zip)有些函数有重复定义，还得去掉两个文件，不

    知会引起什么后果，它们是fts3.c和 fts3_tokenizer.c。

    3. 执行构建(Build)命令，OK。

    还可以选择构建线程完全的DLL或发布 (Release) 版的DLL，参原文。

    用 Microsoft Visual C++构建 SQLite CLP

    方法基本同上。

    创建项目时选择Win32 Console Application，添加文件时把 shell.c也加上，即可。

    使用 SQLite数据库

    SQLite 的CLP 是使用和管理SQLite数据库最常用的方法。

    它可运行于多种平台，学会使用 CLP，可以保证你永远有一个通用和熟悉的途径来管理你

    的数据库。CLP 其实是两个程序。它可以运行在命令行模式下完成各种数据库管理任务，也可以运行在Shell模式下，以交互的方式执行查询操作。

    Shell模式下使用 CLP

    运行DOS shell，进入工作目录，在命令行上键入 sqlite3命令，命令后跟随一个可选的数据

    库文件名。如果在命令行上不指定数据库名，SQLite 将会使用一个内存数据库，其内容在

    退出CLP 时将会丢失。

    CLP 以交互形式运行，你可以在其上执行查询、获得 schema信息、导入导出数据和执行其

    它各种各样的数据库任务。CLP 认为你输入的任何语句都是一个查询命令(query)，除非命令

    是以点(.)开始，这些命令用于特殊操作。键入.help 或.h 可以得到这些操作的完整列表。键

    入.exit 或.e退出CLP。

    让我们从创建一个称为test.db的数据库开始。在DOS shell下键入：

    sqlite3 test.db

    尽管我们提供了数据库名， 但如果这个数据库并不存在， SQLite并不会真正地创建它。 SQLite

    会等到你真正地向其中增加了数据库对象之后才创建它，比如在其中创建了表或视图。这样

    做的原因是给你机会在将数据库写到外部文件之前对数据库做一些永久性的设置， 如页的大 12

    小等。有些设置，如页大小、字符集(UTF-8 或 UTF-16)等，一旦数据库创建之后就不能再

    修改了。这个中间期是你能改它们的唯一机会。我们采用默认设置，因此，要将数据库写到

    磁盘，我们仅需要在其中创建一个表。输入如下语句：

    sqlite> create table test (id integer primary key, value text);

    现在你有了一个称为test.db的数据库文件，其中包含一个表 test，该表包含两个字段。

    l 一个称为 id 的主键字段,它带有自动增长属性。无论何时你定义一个整型主键字段，SQLite 都会对该字段应用自动增长属性。

    l 一个简单的称为value的文本字段。

    向表中插入几行数据：

    sqlite> insert into test (value) values('eenie');

    sqlite> insert into test (value) values('meenie');

    sqlite> insert into test (value) values('miny');

    sqlite> insert into test (value) values('mo');

    将插入的数据取回：

    sqlite> .mode col

    sqlite> .headers on

    sqlite> SELECT FROM test;

    系统显示：

    id value

    1 eenie

    2 meenie

    3 miny

    4 mo

    SELECT语句前的两个命令(.headers and .mode)用于改进输出的格式。可以看到SQLite 为 id

    字段赋予了连接的整数值，而这些值我们在 INSERT 语句中并没的提供。对于自动增长的字

    段， 你可能会关心最后插入的一条记录该字段的取值， 此值可以用SQL函数last_insert_rowid

    得到。

    sqlite> select last_insert_rowid;

    last_insert_rowid

    4

    在退出 CLP 之前，让我们来为数据库创建一个索引和一个视图，后面的内容中将会用到它

    们。

    sqlite> create index test_idx on test (value);

    sqlite> create view schema as select from sqlite_master;

    使用.exit命令退出CLP。

    sqlite> .exit

    C:\Temp>

    获得数据库的 Schema信息

    有几个shell命令用于获得有关数据库内容的信息。你可以键入命令.tables [pattern]来得到所

    有表和视图的列表，其中[pattern]可以是任何类 SQL的操作符。执行上述命令会返回符合条

    件的所有表和视图，如果没有pattern项，返回所有表和视图。

    sqlite> .tables 13

    schema test

    可以看到我们创建的表 test 和视图 schema。同样的，要显示一个表的索引，可以键入命

    令.indices [table name]：

    sqlite> .indices test

    test_idx

    可以看到我们为表test所创建的名为test_idx的索引。使用.schema [table name]可以得到一

    个表或视图的定义(DDL)语句。 如果没提供表名， 则返回所有数据库对象(包括table、 indexe、view和index)的定义语句：

    sqlite> .schema test

    CREATE TABLE test (id integer primary key, value text);

    CREATE INDEX test_idx on test (value);

    sqlite> .schema

    CREATE TABLE test (id integer primary key, value text);

    CREATE VIEW schema as select from sqlite_master;

    CREATE INDEX test_idx on test (value);

    更详细的schema信息可以通过SQLite唯一的一个系统视图sqlite_master得到。这个视图是

    一个系统目录，它的结构如表2-1所示。

    表2-1 sqlite_master表结构

    编号 字段 说明

    1 type 值为table、 index、 trigger或view之一。

    2 name 对象名称，值为字符串。

    3 tbl_name 如果是表或视图对象，此字段值与字段 2相同。如果是索引或触

    发器对象，此字段值为与其相关的表名。

    4 rootpage 对触发器或视图对象，此字段值为 0。对表或索引对象，此字段

    值为其根页的编号。

    5 SQL 字符串，创建此对象时所使用的 SQL语句。

    查询当前数据库的sqlite_master表，返回：

    sqlite> .mode col

    sqlite> .headers on

    sqlite> select type, name, tbl_name, sql from sqlite_master order by type;

    type name tbl_name sql---------

    index test_idx test CREATE INDEX test_idx on test (value)

    table test test CREATE TABLE test (id integer primary

    view schema schema CREATE VIEW schema as select from s

    ■Tip：使用向上的箭头键可以回滚到前面输入过的命令。

    数据导出

    可以使用.dump 命令将数据库导出为 SQL格式的文件。不使用任何参数，.dump 将导出整个

    数据库。如果提供参数，CLP 把参数理解为表名或视图名。

    sqlite> .output file.sql 14

    sqlite> .dump

    sqlite> .output stdout

    数据导入

    有两种方法可以导入数据，用哪种方法决定于要导入的文件的格式。如果文件由 SQL 语句

    构成，可以使用.read 命令导入(执行)文件。如果文件是由逗号或其它定界符分隔的值

    (comma-separated values, CSV)组成，可使用.import [file][table]命令。此命令将解析指定的文

    件并尝试将数据插入到指定的表中。

    sqlite> .show

    echo: off

    explain: off

    headers: on

    mode: column

    nullvalue:

    output: stdout

    separator: |

    width:

    .read 命令用来导入由.dump 命令创建的文件。如果要使用前面作为备份文件所导出的

    file.sql，需要先移除已经存在的数据库对象(test表和 schema视图)，然后用下面方法导入：

    sqlite> drop table test;

    sqlite> drop view schema;

    sqlite> .read file.sql

    格式化

    CLP 提供了几个格式化选项命令。最简单的是.echo, 如果设置.echo on，则新输入的命令在

    执行前都会回显，默认值是 off。.headers 设置为 on 时，查询结果显示时带有字段名。当遇

    到NULL值时，如果需要以一个字符串来显示，使用.nullvalue命令设置，如：

    sqlite> .nullvalue NULL

    默认情况下使用空串。如果要改变CLP 的 shell提示符，使用.prompt [value]，如：

    sqlite> .prompt 'sqlite3> '

    sqlite3>

    .mode 命令可以设置结果数据的几种输出格式。可选的格式为 csv、column、html、insert、line、list、tabs 和 tcl。默认值是 list，在此模式下显示结果时列间以默认的分隔符分隔。如

    果你想以CSV 格式输出一个表的数据，可如下操作：

    sqlite3> .output file.csv

    sqlite3> .separator ,sqlite3> select from test;

    sqlite3> .output stdout

    文件file.csv的内容为：

    1,eenie

    2,meenie 15

    3,miny

    4,mo

    因为有一个CSV模式，所以下面的命令会得到相似的结果：

    sqlite3> .output file.csv

    sqlite3> .mode csv

    sqlite3> select from test;

    sqlite3> .output stdout

    在命令行方式下执行 CLP

    在DOS 或UNIX 的命令行方式下，直接执行 SQLite 的数据库操作。

    数据库管理

    所有的数据库管理任务都可以在shell和命令行模式下完成。

    创建、备份和删除数据库

    数据库的备份有两种方法。第1种是使用.dump，可得到 SQL格式的文件。在命令行方式下

    可如下做：

    sqlite3 test.db .dump > test.sql

    在CLP 中可如下做：

    sqlite> .output file.sql

    sqlite> .dump

    sqlite> .exit

    相应地，导入一个SQL格式备份的数据库可如下做：

    sqlite3 test.db < test.sql

    此处假设test.db不存在。如果它存在，则或许会因为数据库中有同名的对象而出错。

    可以用复制的方法得到一个二进制的数据库文件拷贝。但也许在复制之前你想先抽空

    (vacuum)它，也就是释放数据库文件中未使用的空间，以得到一个更小的数据库文件。可操

    作如下：

    sqlite3 test.db VACUUM

    cp test.db test.backup

    一般情况下，二进制的备份如不SQL备份兼容性好。尽管SQLite有很好的向上兼容性和各

    操作系统间文件格式的一致性，但如果想要将备份文件保留很长时间，还是 SQL 格式保险

    一些。

    当一个数据库你不想再用时，简单地从操作系统中将其文件删除就行了。

    获得数据库文件的信息

    按前文所述，获得数据库信息的主要途径是使用sqlite_master视图，它提供一个数据库所包

    含的所有对象的细节信息。 16

    如果你想获得关于物理的数据库结构信息，可以使用一个称为 SQLite Analyzer的工具，它

    可以在SQLite网站上下载得到。SQLite Analyzer可以提供磁盘 SQLite数据库的详细技术信

    息。

    (输出结果略)

    其它 SQLite工具

    有很多其它开源的或商业的程序可工作于 SQLite，其中具有优秀图形化界面且跨平台的有：

    l SQLite Database Browser (http:sqlitebrowser.sourceforge.net)

    l SQLite Control Center (http:bobmanc.home.comcast.netsqlitecc.html)

    l SQLiteManager (www.sqlabs.netsqlitemanager.php) 17

    第 3 章 关系模型

    SQL具有非常实用的外观和非常理论化的内涵， 这个内涵就是关系模型。 关系模型早于 SQL

    出现并对SQL的出现提出了需求。SQL的原动力不在语言本身，而是深藏在关系模型的概

    念当中。这些概念构成了SQL设计和操作的基础。

    空注：数据库基本理论，参考其它书吧。 18

    第 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定义如下： 19

    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语法结构 20

    命令

    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风格的 形式。 21

    创建一个数据库

    数据库中所有的工作都围绕表进行。表由行和列组成，看起来简单，但其实并非如此。表跟

    其它所有的概念有关，涉及本章的大部分篇幅。在此我们用 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 22

    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

    之外的所有操作(这些操作可用其它间接的方法完成)。 23

    操作管道

    从语法上来说，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+25)或函数的结果(COUNT(foods.name))值。 24

    操作符

    操作符使用一个或多个值做为输入并产生一个新值做为输出。这所以叫“操作符”是因为它

    完成某种操作并产生某种结果。二目操作符操作两个输入值(或称操作数)，三目操作符操作

    三个操作数，单目操作符操作一个操作数，等等。

    图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 25

    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 wSour

    384 15 Big Salad

    385 15 Broccoli 26

    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 27

    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 28

    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 29

    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 为主键字段提供自动增长

    特性。 30

    域完整性

    默认值

    保留字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 种预定义格式的 ANSIISO 预定字用于生成日期和时间值。

    CURRENT_TIME 将会生成 ANSIISO 格式(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 31

    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的推理方法：

    l SQL语句中用单引号或双引号括起来的文字被指派为 TEXT。

    l 如果文字是未用引号括起来的数据，并且没有小数点和指数，被指派为 INTEGER。

    l 如果文字是未用引号括起来的数据，并且带有小数点或指数，被指派为 REAL。

    l 用NULL说明的值被指派为NULL存储类。

    l 如果一个值的格式为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); 32

    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); 33

    现在执行同样的插入语句：

    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按下面的规则为字段指派亲和性：

    l 默认的，一个字段默认的亲和性是 NUMERIC。如果一个字段不是 INTEGER、TEXT

    或NONE 的，那它自动地被指派为NUMERIC 亲和性。

    l 如果为字段声明的类型中包含了'INT'(无论大小写)， 该字段被指派为 INTEGER 亲和性。

    l 如果为字段声明的类型中包含了'CHAR'、'CLOB'或'TEXT'(无论大小写)，该字段被指派

    为TEXT亲和性。如'VARCHAR'包含了'CHAR'，所以被指派为 TEXT亲和性。

    l 如果为字段声明的类型中包含了'BLOB'(无论大小写)，或者没有为该字段声明类型，该

    字段被指派为NONE亲和性。

    注意：如果没有为字段声明类型，该字段的亲和性为 NONE，在这种情况下，所有的值都将

    以它们本身的(或从它们的表示法中推断的)存储类存储。如果你暂时还不确定要往一个字段

    里放什么内容，或准备将来修改，用NONE 亲和性是一个好的选择。但 SQLite默认的亲和

    性是 NUMERIC。例如，如果为一定字段声明了类型 JUJYFRUIT，该字段的亲和性不是

    NONE，因为 SQLite 不认识这种类型，会给它指派默认的 NUMERIC 亲和性。所以，与其

    用一个不认识的类型最终得到 NUMERIC 亲和性，还不如不为它指定类型，从而使它得到

    NONE亲和性。 34

    亲和性和存储

    亲和性对值如何存储到字段有影响，规则如下：

    l 一个 NUMERIC 字段可能包括所有 5 种存储类。一个 NUMERIC 字段具有数字存储类

    的偏好(INTEGER 和 REAL)。当一个 TEXT 值被插入到一个 NUMERIC 字段，将会试

    图将其转化为INTEGER存储类；如果转化失败，将会试图将其转化为 REAL存储类；

    如果还是失败，将会用TEXT存储类来存储。

    l 一个INTEGER 字段的处理很像 NUMERIC 字段。一个INTEGER 字段会将REAL值按

    REAL存储类存储。也就是说， 如果这个REAL值没有小数部分， 就会被转化为INTEGER

    存储类。INTEGER 字段将会试着将TEXT 值按REAL存储；如果转化失败，将会试图

    将其转化为INTEGER存储类；如果还是失败，将会用TEXT 存储类来存储。

    l 一个TEXT 字段将会把所有的INTEGER或 REAL值转化为TEXT。

    l 一个NONE字段不试图做任何类型转化。所有值按它们本身的存储类存储。

    l 没有字段试图向 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) 35

    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 之间进行转换。

    为进行二进制的比较，遵循如下规则：

    l 当一个字段值与一个表达式的结果进行比较，字段的亲和性会在比较之前应用于表达式

    的结果。

    l 当两个字段值进行比较， 如果一个字段拥有 INTEGER 或NUMERIC 亲和性而另一个没

    有，NUMERIC 亲和性会应用于非NUMERIC 字段的 TEXT值。

    l 当两个表达式进行比较，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命令的边界，这组命令或者作为一个整体被全部执行，或者都不执行。 36

    事务的典型实例是转帐。

    事务的范围

    事务由 3 个命令控制：BEGIN、COMMIT 和 ROLLBACK。BEGIN 开始一个事务，之后的

    所有操作 ......