from:http://blog.163.com/18_2/blog/static/2174448200851045114653/

sqlite3在Linux下的安装和使用

2008-06-10 16:51:14| 分类： sqlite |字号 订阅

一.本地sqlite3的安装和配置

下载sqlite3源码包

tar xvfz sqlite-src-3.3.5

cd sqlite-3.3.5

./configure –no-tcl

make

(如果在arm板上用需修改Makefile,在LIBS 项中加入 –L/usr/local/arm/3.3.2/lib 保存后退出.然后再make)



安装成功后生成文件将被复制到指定目录

再加入一个环境变量



vi /etc/profile

在export区域加入 sqlITE3_PATH=/usr/local/sqlite-3.3.5 保存后退出

source /etc/profile



有用文件分别为 $sqlITE3_PATH/include/sqlite3.h 头文件

$sqlITE3_PATH/bin/sqlite3 可执行文件

$sqlITE3_PATH/lib/libsqlite3.a

libsqlite3.la

libsqlite3.so -> libsqlite3.so.0.8.6

libsqlite3.so.0 -> libsqlite3.so.0.8.6

libsqlite3.so.0.8.6



在程序使用到数据库

#include <sqlite3.h>



在.pro文件中添加:

INCLUDEPATH +=$(sqlITE3_PATH)/include

DEPENDPATH +=$(sqlITE3_PATH)/include

LIBS +=-L$(sqlITE3_PATH)/lib

LIBS +=-lsqlite3

保存后退出

tmake hello.pro –o Makefile

make

二.程序的编写:

sqlite3 *db;

int rc = sqlite3_open(“data.db”,&db); //打开数据库data.db

if(rc!=sqlITE_OK);//打开失败



创建表rc = sqlite3_exec(db,”create table student(ID integer

primarykey,name nvarchar(32))”,NULL,&mrrmsg);



插入rc = sqlite3_exec(db,”insert into student values(‘3’,’Zhang San’)”,&mrrmsg);





修改rc = sqlite3_exec(db,”update student set name=’Li Si’ where ID=’4’”,&mrrmsg);



删除rc = sqlite3_exec(db,”delete * from student where name=’Wang Wu’”,&mrrmsg);



查询是重点

回调式查询 rc = sqlite3_exec(db,”select * from student where ID>2

and name<>’Zhang San’”,LoadInfo,&mrrmsg);

查询的结果通过LoadInfo函数来导出



typedef int(*sqlite3_callback)(void *,int char **,char **);

int LoadInfo(void *para,//db

int n_column,//记录的列数目

char** column_value,//查出值

char** column_name)//字段名称

{

int i;

printf(“记录含%d个字段\n”,n_column);

for(i=0;i<n_column;i++)

printf(“字段名:%s,字段值:%s \n”,column_name[i],column_value[]i);

return 0;

}



函数式查询 rc = sqlite_get_table(db,”select ID from student where ID>2

and name<>’Zhang San’”,&result,&nrow,&ncolumn,&errmsg);



char** result;

int nrow,ncolumn;

int rc;

char* errmsg=NULL;

rc = sqlite_get_table(db,&errmsg);

int i;

char id[10];

char name[10];

for(int i=1;i<=nrow;i++)

{

id[i]=result[i*2];

name[i]=result[i*2+1];

}

三.sqlite3命令:

sqlite3 data.db //打开data.db .tables 查看数据库中包含表的信息 .schema 输出格式 .mode line / column /list //格式 单行,列,列表 .timeout //用时 .output test.txt //输出到test.txt .q退出 可执行一般的sql语句 [root@localhost root]#sqlite3 data.db sqlite>create table student(ID integer primary key,name nvarchar(32)); sqlite>insert into student values(1,’Sun Wukong’); sqlite>insert into student values(2,’Tang Seng’); sqlite>select * from student; 1|Sun Wukong 2|Tang Seng sqlite>update student set name=’Zhu Bajie’ where ID=’2’; sqlite>select * from student; 1|Sun Wukong 2|Zhu Bajie sqlite>delete from student where ID=’2’ and name=’ Zhu Bajie’; sqlite>select * from student; 1|Sun Wukong sqlite>.tables student 对中文的支持 [root@localhost root]#sqlite3 data.db sqlite>create table student(ID integer primary key,name text);//注意:此处name类型为text sqlite>insert into student values(1,’孙悟空’); sqlite>insert into student values(2,’唐僧’); sqlite>select * from student; 1|孙悟空 2|唐僧 sqlite>update student set name=’猪八戒’ where ID=’2’; sqlite>select * from student; 1|孙悟空 2|猪八戒 sqlite>delete from student where ID=’2’ and name=’ 猪八戒’; sqlite>select * from student; 原文链接：https://www.f2er.com/sqlite/201306.html