1.1 SQLite概述
1.1.1 SQLite简介
SQLite是一款轻型的数据库，是遵守ACID的关联式数据库管理系统。SQLite是D. Richard Hipp 用C语言编写的开源嵌入式数据库引擎。SQLite是完全独立的，不具有外部依赖性。SQLite第一个Alpha版本诞生于2000年5月，至今已经有10个年头，最新版本Sqlite3已经发布。SQLite是免费开源软件。
SQLite能够运行在Windows/Linux/Unix等各种操作系统，同时还能够跟其他编程语言相结合，如PHP、Python、Tcl、Java等，还有ODBC接口，SQLite支持SQL92标准。SQLIte源代码大约3万行，250KB大小，占用资源非常少。相比Mysql、PostgreSQL这两款著名的开源数据库，SQLite占用资源更少，处理速度更快。
ACID指数据库事务正确执行的四个基本要素的缩写：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。一个支持事务（Transaction）处理的数据库系统，必须具备这四种特性，否则在事务过程（Transaction processing）中无法保证数据的正确性。
SQLite最初的设计目标是用于嵌入式系统，目前已经在嵌入式产品中广泛使用。它占用资源非常少，在嵌入式设备中，只需要几百K的内存就够了。
SQLite特性：
（1）支持ACID，保证数据的正确性；
（2）数据保存在一个单一的磁盘文件，支持数据库大小至2TB；
（3）数据库文件可以在不同字节顺序的机器间自由的共享；
（4）占用资源少，处理速度快；
（5）提供C/C++等API接口，方便使用；
（6）支持多种开发语言，如PHP、Python、Tcl、Perl等。
1.1.2 SQLite安装
安装SQLite可以通过二进制RPM包，或者源码安装。
（1）二进制RPM包安装
从其官方网站（http://www.sqlite.org）或其他网站下载sqlite-3.2.8-15.2.i586.rpm、sqlite-devel-3.2.8-15.2.i586.rpm或更高版本。sqlite-3.2.8-15.2.i586.rpm主要包含sqlite命令文件和库文件；sqlite-devel-3.2.8-15.2.i586.rpm主要包括sqlite头文件和库文件。
# rpm -qa | grep sqlite // 查询是否安装sqlite
# rpm -e xxx // 卸载RMP包
# rpm -e xxx --nodeps // 强制卸载RPM包
如果版本太低，可以先卸载老版本，再安装先版本。
# rpm -qpl sqlite-3.2.8-15.2.i586.rpm // 查看sqlite安装路径
# rpm -qpl sqlite-devel-3.2.8-15.2.i586.rpm // 查看sqlite-devel安装路径
# rpm –ivh sqlite-3.2.8-15.2.i586.rpm // 安装sqlite
# rpm –ivh sqlite-devel-3.2.8-15.2.i586.rpm // 安装sqlite-devel
|
目录文件 |
说明 |
|
/usr/bin/sqlite3 |
sqlite3命令文件 |
|
/usr/include/sqlite3.h |
sqlite头文件 |
|
/usr/lib/libsqlite3.so.0 |
sqlite动态库 |
|
/usr/lib/libsqlite3.a |
sqlite静态库 |
|
/usr/share/doc/ |
sqlite帮助文档 |
（2）源码安装
从其官方网站（http://www.sqlite.org/download.html）下载源码sqlite-3.7.0.tar.gz或更高版本。
# tar zxvf sqlite-3.7.0.tar.gz // 解压源文件
# cd sqlite-3.7.0.tar.gz // 进入目录
# ./configure --prefix=/usr/local/sqlite // 配置，安装到/usr/local/sqlite目录
# make // 编译
# make install // 安装
安装好SQLite后，主要包括命令文件、头文件和库文件。
|
目录文件 |
说明 |
|
/usr/local/sqlite/bin |
只有一个命令文件Sqlite |
|
/usr/local/sqlite/include |
包含sqlite3.h和sqlite3ext.h两个头文件 |
|
/usr/local/sqlite/lib |
包含libsqlite3.so和libsqlite3.a等SQLite静态库和动态库文件 |
使用时将sqlite3命令文件和库文件路径导入到环境变量，可修改~/.bashrc文件或~/.bash_profile文件或/etc/profile文件。
$ vi ~/.bashrc // 修改配置文件，添加以下两行
export PATH=/usr/local/sqlite/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/sqlite/lib:$LD_LIBRARY_PATH
$ source ~/.bashrc // 执行配置文件
如果要将其移植到嵌入式系统ARM平台，在配置的时候设置一下即可。
$ ../configure –prefix=$PWD/_install –disable-tcl –build=x86-linux –host=arm-linux
1.1.3 SQLite列类型
SQLite与其他数据库最大不同是它对数据类型的支持，其他常见数据库支持强类型的数据，即必须指定每一列具体的、严格的数据类型。但SQLite采用的是弱类型的数据。SQLite 2.0版本把所有列的值都存储成ASCII文本格式；SQLite 3.0版本支持更多的数据类型。
每个数据值本身的数据类型可以是下列五种类型对象之一：
（1）NULL，空值；
（2）INTEGER，整型，根据大小使用1、2、3、4、6、8个字节来存储；
（3）REAL，浮点型，用来存储8个字节的IEEE浮点；
（4）TEXT，文本字符串，使用UTF-8、UTF-16、UTF-32等保存数据；
（5）BLOB（Binary Large Objects），二进制类型，按照二进制存储，不做任何改变。
为了增强SQLite数据库和其他数据库列类型的兼容性，SQLite支持列的"类型亲和性"。列的亲和性是为该列所存储的数据建议一个类型（注意：是建议而不是强迫）。理论上讲，任何列依然可以存储任何类型的数据；只是针对某些列，如果给出了建议类型，数据库将按建议的类型先转换再存储，这个被优先使用的数据类型则被称为"亲和类型"。
在SQLite3.0版中数据库中的列类型有五种类型亲和性：文本类型、数字类型、整数类型、浮点类型、NULL无类型。
（1）一个具有文本类型亲和性的列，可以使用NULL、TEXT、BLOB值类型存储数据。比如数字数据被插入一个具有文本类型亲和性的列，在存储之前数字将被转换成文本。
（2）一个具有数字类型亲和性的列，可以使用NULL、INTEGER、REAL、TEXT、BLOB五种值类型保存数据。比如一个文本类型数据被插入到一个具有数字类型亲和性的列，在存储之前将被转变成整型或浮点型。
（3）一个具有整数亲和性的列，在转换方面和具有数字亲和性的列是一样的，但也有些区别，比如浮点型的值，将被转换成整型。
（4）一个具有浮点亲和性的列，可以使用REAL、FLOAT、DOUBLE值类型保存数据。
（5）一个具有无类型亲和性的列，不会选择用哪个类型保存数据，数据不会进行任何转换。
一个列的亲和类型是由该列所宣称的类型决定的，遵守以下规则：
（1）如果列的数据类型包括字符串"INT"，它被定义为具有整数型亲和性；
（2）如果列的数据类型包括字符串"CHAR"、"CLOB"、"TEXT"，它被定义为具有文本亲和性（注意：VARCHAR类型包括字符串"CHAR"，因此具有文本类型亲和性）；
（3）如果列的数据类型包括字符串"BLOB"，或没有指定类型，它被定义为无类型亲和性；
（4）如果列的数据类型包括字符串"REAL"、"FLOA"、"DOUB"，它被定义为浮点型亲和性；
（5）其他的都被定义为数字型亲和性。
|
类型 |
亲和类型 |
|
INT、INTEGER、TINYINT、SMALLINT、MEDIUMINT BIGINT、UNSIGNED BIG INT、INT 2、INT8 |
INTEGER |
|
CHARACTER(20)、VARCHAR(255)、NCHAR(55) VARYING CHARACTER(255)、NATIVE CHARACTER(70) NVARCHAR(100)、TEXT、CLOB |
TEXT |
|
BOLB、no datatype specified |
NONE |
|
REAL、FLOAT、DOUBLE、DOUBLE PRECISION、 |
REAL |
|
NUMERIC、DECIMAL(10,5)、BOOLEAN、DATE、DATETIME |
NUMERIC |
TEXT存储可变长度的数据，最大长度为2^31-1(2,147,483,647)个字符。
设置主键使用INTEGER PRIMARY KEY，不能用INT。自动增加是AUTOINCREMENT，不同于MySQL的AUTO_INCREMENT。
1.2 SQLite命令
1.2.1 创建数据库
创建数据库test.db
1 $ sqlite3 test.db // 运行sqlite3，并指定数据库
2 SQLite version 3.5.1
3 Enter ".help" for instructions
4 sqlite> .help // 查看帮助
后面的参数指定创建或打开的数据库。
1.2.2 创建表
创建一个表class，有5列分别是id、name，sex、phone、address。
sqlite> create table class(id integer primary key autoincrement, name varchar(10) not null, sex varchar(6), phone char(15), address varchar(20));
设置主键使用INTEGER PRIMARY KEY，不能用INT。自动增加是autoincrement，不同于MySQL的auto_increment。
1.2.3 插入数据
向表class中插入数据
sqlite> insert into class(id, name, sex, phone, address) values(1, 'hongdy', 'male', '11111', 'shanghai');
sqlite> insert into class(name, sex, phone, address) values('Tom', 'male', '22222', 'nanjing');
sqlite> insert into class(name, sex, phone, address) values('Mary', 'female', '33333', 'beijing');
sqlite> insert into class(name, sex, phone, address) values('Jacky', 'female', '44444', 'guangzhou');
1.2.4 查询数据
查询表class中的数据
sqlite > select * from class; // 选择所有数据
sqlite > select * from class where name="hongdy"; // 选择特定的行
sqlite > select name, phone from class; // 选择特定的列
sqlite> select * from class order by id desc; // 根据ID降序排列
sqlite> select * from class order by id asc; // 根据ID升序排列
sqlite> select * from class order by id desc limit 1; // 选择ID最大的一行
sqlite> select * from class where id=(select max(id) from class); // 通过函数取ID最大值一行
1.2.5 更新数据
更新表class中name='hongdy'的phone='88888'
sqlite> update class set phone='88888' where name='hongdy';
1.2.6 删除数据
删除表中的某一行，或删除表中所有的行
sqlite> delete from class where name='hongdy'; // 删除表中name='hongdy'的那一行
sqlite> delete from class; // 删除表中所有的行
1.2.7 删除表
删除表class
sqlite> drop table class;
1.2.8 设置输出模式
表的输出模式有：column、list、tables、csv、insert、line、html、tcl。
设置完表的模式后，使用select查询语句，即可看到最新的输出格式。
sqlite> .mode column class; // 设置column输出模式
sqlite> select * from class; // 查看column输出模式结果
sqlite> .mode list class; // 设置list输出模式
sqlite> select * from class; // 查看list输出模式结果
sqlite> .mode tables class; // 设置tables输出模式
sqlite> select * from class; // 查看tables输出模式结果
sqlite> .mode cvs class; // 设置cvs输出模式
sqlite> select * from class; // 查看cvs输出模式结果
sqlite> .mode insert class; // 设置insert输出模式
sqlite> select * from class; // 查看insert输出模式结果
sqlite> .mode line class; // 设置line输出模式
sqlite> select * from class; // 查看line输出模式结果
sqlite> .mode html class; // 设置html输出模式
sqlite> select * from class; // 查看html输出模式结果
1.2.9 导出数据
先设置导出的文件名，然后查询数据，这样查询的数据就全部输出到指定的文件，终端看不到数据，此后所有的查询操作，数据都输出到文件。如果想重新让数据在终端显示，设置输出模式为stdout
sqlite> .output 'result' // 指定导出文件
sqlite> select * from class; // 查询数据，输出到文件
sqlite> .read 'result' // 查看到处的文件数据
sqlite> .output stdout // 重新设置输出终端
sqlite> select * from class; // 查询数据，可在终端看到数据
1.2.10 导入数据
从某一个文件中导入数据
sqlite> .import 'result' class // 将result文件的数据导入到表class中
sqlite> select * from class; // 查询数据
1.2.11 显示数据库信息
sqlite> .database // 显示数据库文件
sqlite> .tables // 显示数据库中的表
sqlite> .schema // 显示数据库中所有表的创建模式
sqlite> .schema class // 显示数据库中class表的创建模式
sqlite> .header on // 显示表头
sqlite> .header off // 关闭表头
sqlite> .show // 显示sqlite设置
1.2.12 显示时间
显示现在的日期、时间
sqlite> select datetime("now");
2007-10-29 15:39:05
sqlite> select date("now");
2007-10-29
sqlite> select time("now");
15:39:19
1.3 SQLite编程
1.3.1 SQLite函数接口
SQLite应用编程接口API非常简单易用，只需要三个用来执行SQL和获得数据的函数。它还是可以扩展的，允许程序员自定义函数然后以callback的形式集合进去。C语言API是其它接口的基础，开放源码团体基于此已经扩展了众多的客户接口，使得其他语言对sqlite的使用也成为可能。
创建或打开数据库
int sqlite3_open(const char*, sqlite3**);
执行语句
typedef int (*sqlite_callback)(void*,int,char**, char**);
int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, char**);
int sqlite3_close(sqlite3*);
1.3.2 SQLite编程实例
Sqlite应用程序实例
编写测试文件
$ vi test_sqlite.c
1 #include <stdio.h>
2 #include <stdlib.h>
3 #include <sqlite3.h>
4
5 int main(void)
6 {
7 sqlite3 *db;
8 char *zErrMsg = 0;
9 char **resultp;
10 int nrow;
11 int ncolumn;
12 char *errmsg;
13 int i,j;
14
15 // create db test.db
16 if( (sqlite3_open("people.db", &db)) != 0 ){
17 fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
18 exit(1);
19 }
20
21 // create table
22 if( (sqlite3_exec(db, "create table person(name varchar(30) PRIMARY KEY, age int);", NULL, NULL, &zErrMsg)) != SQLITE_OK){
23 fprintf(stderr, "SQL error: %s\n", zErrMsg);
24 exit(1);
25 }
26
27 // insert into table
28 sqlite3_exec(db, "insert into person values('zhouxh', 30)", NULL, NULL, &zErrMsg);
29 sqlite3_exec(db, "insert into person values('hongdy', 28)", NULL, NULL, &zErrMsg);
30 sqlite3_exec(db, "insert into person values('xuhong', 26)", NULL, NULL, &zErrMsg);
31
32 // query from table
33 sqlite3_get_table(db, "select * from person", &resultp, &nrow, &ncolumn, &errmsg);
34
35 // print query result
36 for(i=0;i<=nrow;i++)
37 {
38 for( j=0;j<ncolumn;j++){
39 printf("%s\t",resultp[i*ncolumn+j]);
40 }
41 printf("\n");
42 }
43
44 // close db
45 sqlite3_close(db);
46 return(0);
47 }
编译、运行
# gcc test_sqlite.c -lsqlite3 -o test_sqlite // 编译
# ./test_sqlite // 运行
如果使用RPM包安装方式，编译时sqlite.h头文件自动到/usr/include目录去查找，sqlite3.so库文件自动到/usr/lib目录去查找。
如果使用源码安装方式，编译时应加上头文件和库文件搜索路径。
$ gcc test_sqlite.c –I /usr/local/sqlite/include –L /usr/local/sqlite/lib -lsqlite3 -o test_sqlite
$ export LD_LIBRARY_PATH=/usr/local/sqlite/lib:$LD_LIBRARY_PATH
$ ./test-sqlite
参考文献