SQLite3 使用教学

今天注意到SQLite 3.6.11（上个月发布的）增加了一个我期待已久的online backup接口，激动之余就顺便和大伙儿聊一下SQLite数据库。本帖权当是SQLite扫盲，如果你对SQLite已经很熟悉，本文就不必再看了。

　　★技术上的优点和特性
　　SQLite是一个轻量级、跨平台的关系型数据库。既然号称关系型数据库，支持SQL92标准中常用的玩意儿（比如视图、事务、触发器等）就是理所当然的了，咱今天就不细说了。今天主要聊聊一些有点特色的玩意儿。

　　◇轻量级
　　先说它的第一个特色：轻量级。想必SQLite的作者很看重这个特性，连它的Logo都是用的“羽毛”，来显摆它的轻飘飘。
　　SQLite和C/S模式的数据库软件不同，它是进程内的数据库引擎，因此不存在数据库的客户端和服务器。使用SQLite一般只需要带上它的一个动态库，就可以享受它的全部功能。而且那个动态库的尺寸也挺小，以版本3.6.11为例，Windows下487KB、Linux下347KB。

　　◇绿色软件
　　SQLite的另外一个特点是绿色：它的核心引擎本身不依赖第三方的软件，使用它也不需要“安装”。所以在部署的时候能够省去不少麻烦。

　　◇单一文件
　　所谓的“单一文件”，就是数据库中所有的信息（比如表、视图、触发器、等）都包含在一个文件内。这个文件可以copy到其它目录或其它机器上，也照用不误。

　　◇跨平台/可移植性
　　如果光支持主流操作系统，那就没啥好吹嘘的了。除了主流操作系统，SQLite还支持了很多冷门的操作系统。我个人比较感兴趣的是它对很多嵌入式系统（比如Android、Windows Mobile、Symbin、Palm、VxWorks等）的支持。

　　◇内存数据库（in-memory database）
　　这年头，内存越来越便宜，很多普通PC都开始以GB为单位来衡量内存（服务器就更甭提了）。这时候，SQLite的内存数据库特性就越发显得好用。
　　SQLite的API不区分当前操作的数据库是在内存还是在文件（对于存储介质是透明的）。所以如果你觉得磁盘I/O有可能成为瓶颈的话，可以考虑切换为内存方式。切换的时候，操作SQLite的代码基本不用大改，只要在开始时把文件Load到内存，结束时把内存的数据库Dump回文件就OK了。在这种情况下，前面提到的“online backup API”就派上用场了，聪明的同学应该明白我为啥这么期待backup功能了吧？

　　★技术上的缺点和不足
　　前面光聊了特性和优点，为了避免枪手写软文的嫌疑，再来说说SQLite的一些缺点。列位看官将来如果想用它，这些缺点要权衡一下。

　　◇并发访问的锁机制
　　SQLite在并发（包括多进程和多线程）读写方面的性能一直不太理想。数据库可能会被写操作独占，从而导致其它读写操作阻塞或出错。

　　◇SQL标准支持不全
　　在它的官方网站上，具体列举了不支持哪些SQL92标准。我个人感觉比较不爽的是不支持外键约束。

　　◇网络文件系统（以下简称NFS）
　　有时候需要访问其它机器上的SQLite数据库文件，就会把数据库文件放置到网络共享目录上。这时候你就要小心了。当SQLite文件放置于NFS时，在并发读写的情况下可能会出问题（比如数据损坏）。原因据说是由于某些NFS的文件锁实现上有Bug。

　　★编程语言接口
　　SQLite支持很多种语言的编程接口。这对于我这种喜欢混用多种编程语言的人来说，是很爽的。下面我大概介绍一下。

　　◇C/C++
　　由于SQLite本身是C写的，它自带的API也是C接口的。所以C/C++用起来最直接了。假如你不喜欢面向过程的C API风格，可以另外找个C++的包装库。想重新发明轮子的同学，也可以自己包装一个。
　　◇Java
　　如果要用Java访问SQLite，可以通过SQLite的JDBC驱动，或者通过专门的SQLite包装库。我个人建议走JDBC方式，万一将来要换数据库，代码就不用大改。
　　◇Python
　　pysqlite是Python操作SQLite的首选。从Python 2.5开始，它已经被整合到Python的标准库中。看来Python社区还是蛮喜欢SQLite嘛。
　　◇dotNet
　　对于喜欢dotNet的同学，可以通过SQLite的ADO.NET驱动来访问。
　　◇Ruby
　　Ruby可以通过SQLite-Ruby操作SQLite数据库，不过我没用过。
　　◇Perl
　　在CPAN上有DBD::SQLite，不过我也没用过。

　　★一些非技术的参考因素
　　前面讲的都是技术层面的话题，如果你考虑在公司的商业软件项目中使用SQLite。还需要根据“如何选择开源项目”里面提到的几个参考因素，再评估一下。
　　◇授权协议（License）
　　SQLite使用的是Public Domain协议，这是最爽一种，可以放心大胆地用。
　　◇用户的普及程度
　　最近这几年，使用SQLite的人越来越多（从Google Trends可以反应出来）。包括一些大公司也开始把它整合到产品中（比如Google的Gears、Apple的Safari、Adobe的AIR）。这说明它的健壮性、稳定性等方面不会有太大问题。
　　◇开发的活跃程度
　　如果到SQLite的Change Log上大致了解一下，可以看出最近5年基本上每1-2个月都会有更新。说明开发的活跃度还是非常高的。
　　从上述几个非技术因素来看，SQLite用于商业公司的软件项目还是非常靠谱的。

一、SQLite的使用场景

★如何权衡？
　　当你在权衡某个场合是否应该使用SQLite时，（在技术层面）至少要考虑如下几点：
　　◇能否发挥SQLite的某些特长？
　　◇是否还有其它的替代方案？
　　◇是否有啥潜在的技术风险？
　　想清楚上述问题之后，再做出决策。

　　★SQLite的特点
　　关于SQLite的特长，在上次的帖子中已经介绍过了。考虑到某些同学比较健忘，咱再回顾一下：
　　◇文件型数据库，且只有单一数据文件
　　◇轻量级
　　◇绿色（不依赖其它软件库）
　　◇跨平台（包括引擎和数据文件）
　　◇支持内存数据库
　　◇支持较大的数据文件（TB级别）

　　★可能的替代方案
　　刚才说了，权衡SQLite的使用需要考虑其它的替代方案，所以俺简单介绍一下和SQLite用途相近的其它几种技术手段。后面讲应用场景的时候，会结合这几个替代方案来作对比。

　　◇Access数据库
　　Access数据库也是文件型的数据库，支持的很多SQL特性都类似于SQLite。自从Windows 2000开始，Windows就内置了Access的数据库引擎（Microsoft Jet Database Engine）。所以Access数据库在上述系统中也是可以独立运行的（不依赖Office）。
　　Access数据库最主要的缺点就是不能跨平台。另外还有几个小缺点：文件大小有限制（2GB）、不支持内存数据库。

　　◇其它文件型数据库
　　其实，除了Access之外，还有另外一些文件型数据库。但是这些文件型数据库要么名气太小，要么不支持多种编程语言（比如HSQLDB），要么已经过时（比如FoxPro、Paradox）。所以后面分析应用场景的时候就不再提及这些玩意儿。

　　◇CSV文件
　　CSV（Comma Separated Values，详细解释见“这里”）是一种很简单的纯文本格式。它本身就是用来表示二维的数据信息的。一个CSV文件可以理解为数据库的一张表。
　　CSV的缺点主要在于：不便于存储非文本的数据信息（比如BLOB类型的信息）；如果需要同时存储多张表的信息，就需要对应有多个CSV文件（文件一多，就嫌麻烦）。

　　◇XML文件
　　XML文件想必大伙儿都知道，我就不多说了。XML格式主要缺点也有两个：一个是由于XML本身是树状结构，有时候不便于表示二维数据表的信息；另一个是数据量大（比如文件超过10MB或者XML节点层次很深）的时候，解析XML的开销蛮大的。

　　★作为数据库的应用场景
　　前面说了一大通，现在开始切入正题，先说说SQLite作为一个轻型数据库，方便干哪些事儿？
　　在这类场景中，由于是把SQLite作为数据库来使的，所以基本不用考虑CSV和XML这两种替代方案。

　　◇需要数据库的小型桌面软件
　　如果你开发一个小型的桌面软件并且需要用到数据库功能（比如某个背单词软件），那SQLite是一个不错的选择。因为SQLite很绿色又很短小精悍。
　　不过，由于Windows在桌面系统的比重很大。对于那些不考虑跨平台的开发人员，SQLite相对于Access来说，没有太大的优势。

　　◇需要数据库的手机软件
　　眼下手机应用的发展很迅猛，相应的开发人员也多起来了。假如你就是一个手机应用程序的开发人员，并且你开发的应用需要有数据库功能（比如某个字典工具），那SQLite简直是不二之选。由于手机操作系统名目繁多，同时手机的内存偏小，这时候SQLite跨平台和轻量级的特长就充分发挥出来了。目前几个知名的手机操作系统（比如Android、Windows Mobile、Symbin、Palm等），SQLite都支持得不错。
　　在这种场合，Access基本没戏。

　　★作为数据容器的应用场景
　　所谓数据容器，就是把SQLite作为装数据的容器，充分发挥SQLite单一数据文件的优点。另外，还可以避免自己定义一套数据文件格式的麻烦。要知道，定义一个完善的数据文件格式是难度极大滴（要考虑可扩展性、要考虑向下兼容、假如跨CPU架构还要考虑字节序、假如数据量大还要考虑性能、还要...）。

　　◇数据备份/恢复、数据导入/导出
　　某些软件系统（尤其是些企业应用系统）经常会碰到数据备份/恢复的功能需求。比如说，客户会要求你把一些数据（往往是业务相关的）定期备份成一个独立的数据文件，然后存储在别处。一旦软件系统自身发生不测，再把备份的数据恢复回来。
　　另外，导入/导出功能也是经常碰到的。一般是某个软件安装在多个地方。然后需要把一些数据（往往是业务相关的）从A处导出，然后在B处导入。
　　针对上述这两种需求：如果牵涉的数据比较大，就不宜使用XML或者Access；如果牵涉到跨平台，就无法使用Access；如果牵涉到多种数据，就不宜使用CSV（除非你能忍受多个CSV文件并存）。有上述条件限制的地方，都很适合用SQLite。

　　◇在线升级
　　这年头不联网的单机已经很少了，提供在线升级功能的软件会多起来。一般的在线升级分为两类：升级程序（比如Firefox自动升级新版本）和升级业务数据（比如杀毒软件升级病毒库）。这两种类型都可以使用SQLite来完成。把需要要升级的内容放置到SQLite数据库文件中，将来升级时只需下载单一的升级文件即可。
　　在这种场景，不太合适用CSV和XML。如果不考虑跨平台，倒也可以用Access来搞定。

　　★作为内存数据库的应用场景
　　在这种类型的场景中，咱们要充分发挥SQLite内存数据库的特长。由于SQLite的API设计比较合理，操作内存数据库和操作文件数据库几乎没啥区别，所以从文件型切换到内存型，代码不用大改。另外，从3.6.11开始，SQLite增加了online backup接口，便于在内存数据库和文件数据库之间进行数据的同步。

　　◇降低磁盘I/O开销
　　比如开发了某个字典工具，词库存储在SQLite数据库文件中。当词库越来越大的时候，你可能会发现，查词的速度越来越慢。当然啦，速度慢未必是磁盘 I/O引起的。这时候你可以把程序略微修改一下（可能就10行左右的代码），在初始化时把词库载入内存的SQLite数据库中。然后再对比测试一下性能。如果发现性能有明显提升，那你以后就可以一直用这种方式了。
　　使用这个招数，要小心内存数据库对内存空间的占用。比如对于普通的PC，占用个几兆、十几兆还行，再大的话就不爽了。另外，对于手机操作系统，此招数效果不好（手机本身的内存就不是很大，而且存储介质的速度已经蛮快了）。

　　◇作为临时表
　　内存数据库方式，还可以用来充当临时表，存放一些临时数据。当程序的进程退出时，内存数据库就自然消失了，不会留下任何垃圾。
　　不过这种方式只适合于某个程序独占临时表的情形。如果临时表需要被多个进程共用，这招就不灵了。
-------------------------------------------------------------------------------------------------------------------------

二、SQLite3 使用教学

OS X自从10.4后把SQLite这套相当出名的数据库软件，放进了作业系统工具集里。OS X包装的是第三版的SQLite，又称SQLite3。这套软件有几个特色：

• 软件属于公共财（public domain），SQLite可说是某种「美德软件」（virtueware），作者本人放弃着作权，而给使用SQLite的人以下的「祝福」（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. 愿你宽心与人分享，所取不多于你所施予
• 支援大多数的SQL指令（下面会简单介绍）。
• 一个档案就是一个数据库。不需要安装数据库服务器软件。
• 完整的Unicode支援（因此没有跨语系的问题）。
• 速度很快。

目前在OS X 10.4里，SQLite是以/usr/bin/sqlite3的形式包装，也就说这是一个命令列工具，必须先从终端机（Terminal.app或其他程序）进入shell之后才能使用。网络上有一些息协助使用SQLite的视觉化工具，但似乎都没有像CocoaMySQL（配合MySQL数据库使用）那般好用。或许随时有惊喜也未可知，以下仅介绍命令列的操作方式。

SQLite顾名思议是以SQL为基础的数据库软件，SQL是一套强大的数据库语言，主要概念是由「数据库」、「资料表」（table）、「查询指令」（queries）等单元组成的「关联性数据库」（进一步的概念可参考网络上各种关于SQL及关联性数据库的文件）。因为SQL的查询功能强大，语法一致而入门容易，因此成为现今主流数据库的标准语言（微软、Oracle等大厂的数据库软件都提供SQL语法的查询及操作）。

以下我们就建立数据库、建立资料表及索引、新增资料、查询资料、更改资料、移除资料、sqlite3命令列选项等几个项目做简单的介绍。

目录

• 1 建立数据库档案
• 2 在sqlite3提示列下操作
• 3 SQL的指令格式
• 4 建立资料表
• 5 建立索引
• 6 加入一笔资料
• 7 查询资料
• 8 如何更改或删除资料
• 9 其他sqlite的特别用法
• 10 小结

建立数据库档案

用sqlite3建立数据库的方法很简单，只要在shell下键入（以下$符号为shell提示号，请勿键入）：

$ sqlite3 foo.db

如果目录下没有foo.db，sqlite3就会建立这个数据库。sqlite3并没有强制数据库档名要怎么取，因此如果你喜欢，也可以取个例如foo.icannameitwhateverilike的档名。

在sqlite3提示列下操作

进入了sqlite3之后，会看到以下文字：

SQLite version 3.1.3
Enter ".help" for instructions
sqlite>

这时如果使用.help可以取得求助，.quit则是离开（请注意：不是quit）

SQL的指令格式

所以的SQL指令都是以分号（;）结尾的。如果遇到两个减号（--）则代表注解，sqlite3会略过去。

建立资料表

假设我们要建一个名叫film的资料表，只要键入以下指令就可以了：

create table film(title,length,year,starring);

这样我们就建立了一个名叫film的资料表，里面有name、length、year、starring四个字段。

这个create table指令的语法为：

create table table_name(field1,field2,field3,...);

table_name是资料表的名称，fieldx则是字段的名字。sqlite3与许多SQL数据库软件不同的是，它不在乎字段属于哪一种资料型态：sqlite3的字段可以储存任何东西：文字、数字、大量文字（blub），它会在适时自动转换。

建立索引

如果资料表有相当多的资料，我们便会建立索引来加快速度。好比说：

create index film_title_index on film(title);

意思是针对film资料表的name字段，建立一个名叫film_name_index的索引。这个指令的语法为

create index index_name on table_name(field_to_be_indexed);

一旦建立了索引，sqlite3会在针对该字段作查询时，自动使用该索引。这一切的操作都是在幕后自动发生的，无须使用者特别指令。

加入一笔资料

接下来我们要加入资料了，加入的方法为使用insert into指令，语法为：

insert into table_name values(data1,data2,data3,...);

例如我们可以加入

insert into film values ('Silence of the Lambs,The',118,1991,'Jodie Foster');
insert into film values ('Contact',153,1997,'Jodie Foster');
insert into film values ('Crouching Tiger,Hidden Dragon',120,2000,'Yun-Fat Chow');
insert into film values ('Hours,114,2002,'Nicole Kidman');

如果该字段没有资料，我们可以填NULL。

查询资料

讲到这里，我们终于要开始介绍SQL最强大的select指令了。我们首先简单介绍select的基本句型：

select columns from table_name where expression;

最常见的用法，当然是倒出所有数据库的内容：

select * from film;

如果资料太多了，我们或许会想限制笔数：

select * from film limit 10;

或是照着电影年份来排列：

select * from film order by year limit 10;

或是年份比较近的电影先列出来：

select * from film order by year desc limit 10;

或是我们只想看电影名称跟年份：

select title,year from film order by year desc limit 10;

查所有茱蒂佛斯特演过的电影：

select * from film where starring='Jodie Foster';

查所有演员名字开头叫茱蒂的电影('%' 符号便是 SQL 的万用字符）：

select * from film where starring like 'Jodie%';

查所有演员名字以茱蒂开头、年份晚于1985年、年份晚的优先列出、最多十笔，只列出电影名称和年份：

select title,year from film where starring like 'Jodie%' and year >= 1985 order by year desc limit 10;

有时候我们只想知道数据库一共有多少笔资料：

select count(*) from film;

有时候我们只想知道1985年以后的电影有几部：

select count(*) from film where year >= 1985;

（进一步的各种组合，要去看SQL专书，不过你大概已经知道SQL为什么这么流行了：这种语言允许你将各种查询条件组合在一起──而我们还没提到「跨数据库的联合查询」呢！）

如何更改或删除资料

了解select的用法非常重要，因为要在sqlite更改或删除一笔资料，也是靠同样的语法。

例如有一笔资料的名字打错了：

update film set starring='Jodie Foster' where starring='Jodee Foster';

就会把主角字段里，被打成'Jodee Foster'的那笔（或多笔）资料，改回成Jodie Foster。

delete from film where year < 1970;

就会删除所有年代早于1970年（不含）的电影了。

其他sqlite的特别用法

sqlite可以在shell底下直接执行命令：

sqlite3 film.db "select * from film;"

输出 HTML 表格：

sqlite3 -html film.db "select * from film;"

将数据库「倒出来」：

sqlite3 film.db ".dump" > output.sql

利用输出的资料，建立一个一模一样的数据库（加上以上指令，就是标准的SQL数据库备份了）：

sqlite3 film.db < output.sql

在大量插入资料时，你可能会需要先打这个指令：

begin;

插入完资料后要记得打这个指令，资料才会写进数据库中：

commit;

小结

以上我们介绍了SQLite这套数据库系统的用法。事实上OS X也有诸于SQLiteManagerX这类的图形接口程序，可以便利数据库的操作。不过万变不离其宗，了解SQL指令操作，SQLite与其各家变种就很容易上手了。

至于为什么要写这篇教学呢？除了因为OS X Tiger大量使用SQLite之外（例如：Safari的RSS reader，就是把文章存在SQLite数据库里！你可以开开看~/Library/Syndication/Database3这个档案，看看里面有什么料），OpenVanilla从0.7.2开始，也引进了以SQLite为基础的词汇管理工具，以及全字库的注音输入法。因为使用SQLite，这两个模块不管数据库内有多少笔资料，都可以做到「瞬间启动」以及相当快速的查询回应。

将一套方便好用的数据库软件包进OS X中，当然也算是Apple相当相当聪明的选择。再勤劳一点的朋友也许已经开始想拿SQLite来记录各种东西（像我们其中就有一人写了个程序，自动记录电池状态，写进SQLite数据库中再做统计......）了。想像空间可说相当宽广。

目前支援SQLite的程序语言，你能想到的大概都有了。这套数据库2005年还赢得了美国O'Reilly Open Source Conference的最佳开放源代码软件奖，奖评是「有什么东西能让Perl,Python,PHP,Ruby语言团结一致地支援的？就是SQLite」。由此可见SQLite的地位了。而SQLite程序非常小，更是少数打 "gcc -o sqlite3 *"，不需任何特殊设定就能跨平台编译的程序。小而省，小而美，SQLite连网站都不多赘言，直指SQL语法精要及API使用方法，原作者大概也可以算是某种程序设计之道（Tao of Programming）里所说的至人了。

SQLite数据库是中小站点CMS的最佳选择

作者：孙毓波 (AKCMS 作者)

SQLite 是一个类似Access的轻量级数据库系统，但是更小、更快、容量更大，并发更高。为什么说 SQLite 最适合做 CMS (内容管理系统)呢？并不是说其他数据库不好,Oracle、MySQL、SQLServer 也都是非常优秀的 DBS，只不过他们设计目标不同，特性不同，所以只有更适用某个应用场景，没有绝对的好坏之分。

我归纳的中小型站点的CMS的特点如下：

• 1、数据量不超过10万
• 2、日页面访问量不超过10万
• 3、 一部分网站全部生成静态页面，一部分网站实时查询数据库动态访问
• 4、 站长不懂技术，不懂得复杂的数据库维护，只会用 FTP 管理网站
• 5 、个人站点基本上是一个人管理，一般情况下只有一个人在访问后台，没有并发
• 6、 对数据库来说是读多写少，只有在站长访问后台的时候才会写入
• 7、 多运行于虚拟主机，大部分PHP主机均同时支持MySQL，小部分PHP主机需要单独购买MySQL，PHP+MySQL的主机价格较PHP主机价格高。（以万网为例：最便宜的PHP空间780元，最便宜的PHP+MySQL的PHP空间1150元）
• 8、 多数中小站点的HTTP服务与MySQL部署在同一服务器上

SQLite 的优点在中小网站CMS应用场景下表现突出：

• 1、与MySQL相比，它更彻底的免费，并且没有任何使用上的限制
• 2、非常小巧，PHP5以上版本中无需任何配置即可支持SQLite
• 3、无需单独购买数据库服务，无服务器进程，配置成本为零
• 4、整个数据库存储在一个单个的文件中，数据导入导出备份恢复都是复制文件，维护难度为零
• 5、读速度快，在数据量不是很大的情况下速度较快，更重要的是：省掉了一次数据库远程链接没有复杂的权限验证，打开就能操作

SQLite的缺点在中小网站 CMS 应用场景下被规避：

• 1、并发低 动态访问时当访问量不超过10万PV的时候，SQLite 超过 Access 的并发能力已经绰绰有余；生成静态页后更无需考虑数据库的并发问题
• 2、在大数据量的情况下表现较差 但是中小站点一般情况下数据量不超过10万，而SQlite 在 100 万数据量之下表现还不错，因为省掉了对数据库服务器的远程连接甚至会更快
• 3、写入较慢 默认配置下的 SQlite 的写入速度比MySQL慢了很多，但是 CMS 应用场景的写入操作较少。在插入新文章的时候基本感受不到慢。集中的写数据库操作只有在安装的时候会出现，不过只出现一次，可以忽略
• 4、为已有的表加索引较慢 但是在中小站点CMS中不会有这样的需求，可以忽略
• 5、无法将 MySQL 部署到与前端机不同的服务器上，但是中小站点也没有分开部署的需求

综上所述：在中小站点 CMS 的应用场景下 SQLite 能最大限度的降低建站成本，降低维护难度，又很好得规避了自身的缺点。所以我认为未来支持 SQLite 的 CMS 系统一定会大行其道。

SQLite For Windowns 安装配置方法

[作者:佚名 | 时间:2009-6-29] SQLite 是个使用档案方式储存的 Database，不需要另外安装如 MySQL 之类的 Server，而且 PHP5已经将 SQLite 內建了，相当好用，在某些方面效能比起其他 Database 系统有过之而无不及阿！
先介绍 SQLite 在 windows 的安裝方式：
PHP 4 版本
2.php.ini 加上 extension=php_sqlite.dll
3.重新启动 Web Server 即可。
PHP 5 版本
PHP 5 已经包含 SQLite 模组了，所以只需要载入模组即可。
修改 php.ini 找到 ;extension=php_sqlite.dll将前面的分号去掉。
不过目前测试結果在 PHP 5.1.1 和 5.1.2 只有这样是 run 不起来的，必须连 pdo 一起启动，所以在前面增加兩行：
extension=php_pdo.dll
extension=php_pdo_sqlite.dll
extension=php_ sqlite.dll 最后一样重新启动 Web Server 即可。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!