Sqlite数据库

数据库SQLite

一.1.1 SQLite特性:

1支持ACID,保证数据的正确性

2)数据保存在一个单一的磁盘文件,支持数据库大小至2TB

3数据库文件可以在不同字节顺序的机器间*的共享;

4)占用资源少,处理速度快;

5)提供C/C++API接口,方便使用;

6)支持多种开发语言,如PHPPythonTclPerl等。

一.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版本支持更多的数据类型。 

每个数据值本身的数据类型可以是下列五种类型对象之一:

1NULL,空值;

2INTEGER,整型,根据大小使用123468个字节来存储;

3REAL,浮点型,用来存储8个字节的IEEE浮点;

4TEXT,文本字符串,使用UTF-8UTF-16UTF-32等保存数据;

5BLOBBinary Large Objects),二进制类型,按照二进制存储,不做任何改变。

为了增强SQLite数据库和其他数据库列类型的兼容性,SQLite支持列的"类型亲和性"。列的亲和性是为该列所存储的数据建议一个类型(注意:是建议而不是强迫)。理论上讲,任何列依然可以存储任何类型的数据;只是针对某些列,如果给出了建议类型,数据库将按建议的类型先转换再存储,这个被优先使用的数据类型则被称为"亲和类型"

SQLite3.0版中数据库中的列类型有五种类型亲和性:文本类型、数字类型、整数类型、浮点类型、NULL无类型。

1)一个具有文本类型亲和性的列,可以使用NULLTEXTBLOB值类型存储数据。比如数字数据被插入一个具有文本类型亲和性的列,在存储之前数字将被转换成文本。

2)一个具有数字类型亲和性的列,可以使用NULLINTEGERREALTEXTBLOB五种值类型保存数据。比如一个文本类型数据被插入到一个具有数字类型亲和性的列,在存储之前将被转变成整型或浮点型。

3)一个具有整数亲和性的列,在转换方面和具有数字亲和性的列是一样的,但也有些区别,比如浮点型的值,将被转换成整型。

4)一个具有浮点亲和性的列,可以使用REALFLOATDOUBLE值类型保存数据。

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 2INT8

INTEGER

CHARACTER(20)、VARCHAR(255)、NCHAR(55)

VARYING CHARACTER(255)、NATIVE CHARACTER(70)

NVARCHAR(100)、TEXT、CLOB

TEXT

BOLBno 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,不同于MySQLAUTO_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列分别是idnamesexphoneaddress

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,不同于MySQLauto_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语句返回的行数。Limit12个参数,如给定2个参数,第一个指定要返回的第一行偏移量,第二个指定返回行的最大数目,初始行的偏移量是0

一.1.1 更新数据

更新表classname=‘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 设置输出模式

表的输出模式有:columnlisttablescsvinsertlinehtmltcl

设置完表的模式后,使用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\n", 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\n", 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\t",resultp[i*ncolumn+j]);
        }
        printf("\n");
    }
    // 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/

 

Sqlite数据库

上一篇:如何在 YouTube 中开启画中画、影片循环播放?


下一篇:基于实际项目的SQL学习总结