关于sqlite

缘起

Rails的开发环境默认使用的数据库是Sqlite3，以前看到Andriod中也默认安装了Sqlite数据库系统。好奇且顺带学习一下Sqlite3。

1. 简介

Sqlite是最简单的数据库，适合小型的项目，不支持很多特性但足够好用，支持ACID事务。Sqlite是公共域软件（public domain software，对应私有软件和自由软件），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. 愿你宽心与人分享，所取不多于你所施予

Sqlite的特点：

• l 不要安装，不要配置，没有服务器（仅是一个库文件），不提供并发
• l 数据库以文件的形式保存，可以自由拷贝
• l 支持SQL-92的大多数标准
• l 不能通过IP远程访问
• l 支持Unicode，方便移植
• l 跨平台，小，快，美

Sqlite的官方站点:http://www.sqlite.org/，sqlite几乎被被所有主流的脚本语言（Perl, Python, PHP, Ruby,Tcl）支持。

2. 安装

如上所述，Sqlite就是一个库文件，使用需要库和访问前端（libsqlite3-dev和sqlite3）。Ubuntu下安装很简单：

sudo apt-get install sqlite3 libsqlite3-dev

其中，sqlite3是sqlite的一个命令行交互前端。在shell中输入sqlite3，就可以进入交互命令模式。

3. 使用

Sqlite3的使用可以分为命令模式和编程模式，其中编程模式中仅仅介绍一下Ruby中使用，其他的使用参考官方的文档。

3.1. 命令行使用

下面以一个简单的例子穿插其中，

3.1.1. 创建数据库

$sqlite3 foo.db

这里数据库的名字可以任意取，随你喜欢。

进入交互模式：$sqlite3

sqlite>.help #获取帮助:

sqlite>.quit或者.exit #退出sqlite3

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

SQL的语句：以分号（;）结尾的，注释为--

3.1.2. 建表命令-create table

create table指令的语法为：

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

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

例子:

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

建立了一个名叫film的表，里面有title、length、year、starring四个字段。

3.1.3. 建立索引-create index

create index的语法为：

create index index_name on table_name(field_to_be_indexed);

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

可以对上面建立的film数据表中title字段建立索引，从而加快对其的查询：

create index film_title_index on film(title);

3.1.4. 插入数据-insert into

insert into语法为：

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

例如，在film数据表中插入一些数据：

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, The', 114, 2002, 'Nicole Kidman');

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

3.1.5. 查表-select

select的基本句型：

select columns from table_name where expression;

以下，对film数据库进行一些查询：

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'; --查询Jodie Foster的电影

select * from film where starring like 'Jodie%';--查所有演员名以Jodie的电影

--查所有演员名字以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;

3.1.6. 更新或删除数据-update & delete

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

就会把主角字段里，被打成'Jodee Foster'的表项改为Jodie Foster。

delete from film where year < 1970;

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

3.1.7. 其他用法

sqlite可以在shell中直接执行命令：sqlite3 film.db "select * from film;"

输出 HTML 表格：sqlite3 -html film.db "select * from film;"

将数据库内容输出为sql脚本：

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

利用输出的脚本，建立一个相同的数据库（这就是标准的SQL数据库备份和迁移）：

sqlite3 film.db < output.sql

在大量插入数据时，可以这样处理（这里可以看作是sql）：

begin;

---大量insert语句--

commit;

3.2. Ruby中使用sqlite3

在ruby中使用sqlite3，首先需要安装sqlite3-ruby，是ruby版sqlite的驱动程序（或数据库适配器）。不过，Rails中不需要安装这个数据库适配器就能使用Sqlite3.

sudo gem install sqlite3-ruby

下面是一个在Ruby中使用SQLite3的例子脚本testsqlite.rb:

require 'rubygems'require 'sqlite3'begindb = SQLite3::Database.new('mydata2.db')result = db.execute("create table if not exists testtable(id INTEGER,name string) ")puts "before insert"db.execute("insert into testtable values (1,'aaaa')")#The following kind of select will return an array of ArrayWithTypeandFieldsresult = db.execute("select * from testtable")puts "empty" if result.empty?result.each do |x|puts x[0],x[1]end#The following kind of select will return a ResultSetresult = db.prepare("select sum(id) from testtable")result.execute do |x|        puts x.columnsx.each do |value|puts valueendendrescue SQLite3::CantOpenException => eputs "***********My God, open db error **********"puts eend

运行结果为：

$ruby testsqlite.rb 

before insert

1

aaaa

1

aaaa

sum(id)

2

备注：这里自己对ruby的理解也就是能看懂代码的程度，还有待加强。不过，纵然了解不深，我依然被Ruby语法的灵活性和优雅性所倾倒。

后记

可以看到sqlite3的使用非常的方便，这让我想起了当年我的数据库的课，想想当时使用SQL Server 2008，开个软件就要老半天，各种各样的菜单和强大的功能，这些对学习SQL一点用处都没有，反而分散了对SQL学习的精力，陷入那漫长的等待和图形界面的卡死的状态。当年教我数据的老师是个博导，一个只知道微软的SQL Server，还用的不熟的博导。

我觉得，我国的计算机教育要发展，高校计算机教育去微软化很重要。一个个都只会用windows的操作系统教操作系统和只会用SQL Server教数据库的老师，能平地教出个图灵机奖吗？Linux+Open source才是救赎的开始，好像有点偏激了。不过，windows真的浪费了我很多时间和精力，让我一次又有一次的困惑不已，自从用上了Ubuntu，我整个世界都清净多了，再也不觉得自己是SB，I am more smart than before！

我又一次成功的吐槽了windows！

参考文献

1.Rails中使用sqlite3做数据库:http://rubyer.me/blog/556/

2.SQLite3安装及在Ruby下的使用:http://zhiyongz.iteye.com/blog/600912

3.大道至简—SQLite3 使用教学:http://blog.csdn.net/don211/article/details/8088332