Sqlite数据库

数据库SQLite

一.1.1 SQLite特性：

（1）支持ACID，保证数据的正确性；

（2）数据保存在一个单一的磁盘文件，支持数据库大小至2TB；

（3）数据库文件可以在不同字节顺序的机器间自由的共享；

（4）占用资源少，处理速度快；

（5）提供C/C++等API接口，方便使用；

（6）支持多种开发语言，如PHP、Python、Tcl、Perl等。

一.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 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 SQLite命令

一.1.1 创建数据库

创建数据库test.db

$ sqlite3 test.db // 运行sqlite3，并指定数据库

SQLite version 3.5.1

Enter ".help" for instructions

sqlite> .help // 查看帮助

后面的参数指定创建或打开的数据库。

一.1.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.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');

假定插入数据之前，数据表是空的，第一条数据指定了id，实际上这个值是自动增加的，下面的插入语句就没有指定这个值。

一.1.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最大值一行

limit子句可被用来限制SELECT语句返回的行数。Limit有1或2个参数，如给定2个参数，第一个指定要返回的第一行偏移量，第二个指定返回行的最大数目，初始行的偏移量是0。

一.1.1 更新数据

更新表class中name='hongdy'的phone='88888'

sqlite> update class set phone='88888' where name='hongdy';

一.1.2 删除数据

删除表中的某一行，或删除表中所有的行

sqlite> delete from class where name='hongdy'; // 删除表中name='hongdy'的那一行

sqlite> delete from class; // 删除表中所有的行

一.1.3 删除表

删除表class

sqlite> drop table class;

一.1.4 设置输出模式

表的输出模式有：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.5 导出数据

先设置导出的文件名，然后查询数据，这样查询的数据就全部输出到指定的文件，终端看不到数据，此后所有的查询操作，数据都输出到文件。如果想重新让数据在终端显示，设置输出模式为stdout

sqlite> .output 'result' // 指定导出文件

sqlite> select * from class; // 查询数据，输出到文件

sqlite> .read 'result' // 查看到处的文件数据

sqlite> .output stdout // 重新设置输出终端

sqlite> select * from class; // 查询数据，可在终端看到数据

一.1.6 导入数据

从某一个文件中导入数据

sqlite> .import 'result' class // 将result文件的数据导入到表class中

sqlite> select * from class; // 查询数据

一.1.7 显示数据库信息

sqlite> .database // 显示数据库文件

sqlite> .tables // 显示数据库中的表

sqlite> .schema // 显示数据库中所有表的创建模式

sqlite> .schema class // 显示数据库中class表的创建模式

sqlite> .header on // 显示表头

sqlite> .header off // 关闭表头

sqlite> .show // 显示sqlite设置

一.1.8 显示时间

显示现在的日期、时间

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 SQLite编程

一.1.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.2 SQLite编程实例

Sqlite应用程序实例

编写测试文件 

$ vi test_sqlite.c

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>

int main(void)
{
    sqlite3 *db;
    char  *zErrMsg = 0;
    char  **resultp;
    int  nrow;
    int  ncolumn;
    char  *errmsg;
    int  i,j;
    // create db test.db
    if( (sqlite3_open("people.db", &db)) != 0 ){
        fprintf(stderr, "Can't open database: %s
", sqlite3_errmsg(db));
        exit(1);
    }
    // create table

    if( (sqlite3_exec(db, "create table person(name varchar(30) PRIMARY KEY, age int);", NULL, NULL, &zErrMsg)) != SQLITE_OK){
        fprintf(stderr, "SQL error: %s
", zErrMsg);
        exit(1);
    }
    // insert into table
    sqlite3_exec(db, "insert into person values('zhouxh', 30)", NULL, NULL, &zErrMsg);
    sqlite3_exec(db, "insert into person values('hongdy', 28)", NULL, NULL, &zErrMsg);
    sqlite3_exec(db, "insert into person values('xuhong', 26)", NULL, NULL, &zErrMsg);
    // query from table
    sqlite3_get_table(db, "select * from person", &resultp, &nrow, &ncolumn, &errmsg);
    // print query result
    for(i=0;i<=nrow;i++)
    {
        for( j=0;j<ncolumn;j++){
            printf("%s	",resultp[i*ncolumn+j]);
        }
        printf("
");
    }
    // close db
    sqlite3_close(db);
    return(0);
}

编译、运行

# 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

 

参考文献

http://sqlite.org/datatype3.html

http://www.sqlite.com.cn/