Linux下Sqlite3数据库的基本操作

简要介绍sqlite3数据库在Linux操作系统下编程开发的基本操作：

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


typedef struct Data
{
int Id;
char* Name;
char* Num;
}* MyData;


int callback(void*,int,char**,char**);//回调函数


int main(void)
{
int res;
char* errmsg = NULL;
sqlite3* db = NULL;
char sql[100] = {0};
MyData mydata = (MyData)malloc(sizeof(MyData));

//打开或创建数据库
if((res = sqlite3_open("test.db",&db)) != SQLITE_OK)
{
printf("open or create test.db error!\n");
exit(1);
}


//创建一个表
sprintf(sql,"create table student(Id integer,Name text,StudentId text)");
if((res = sqlite3_exec(db,sql,NULL,NULL,&errmsg)) != SQLITE_OK)
{
printf("%s\n",errmsg);
exit(1);
}


//向表中插入数据1
sprintf(sql,"insert into student values(1,'zhangsan','100')");
if((res = sqlite3_exec(db,sql,NULL,NULL,&errmsg)) != SQLITE_OK)
{
printf("%s\n",errmsg);
exit(1);
}


//向表中插入数据2
sprintf(sql,"insert into student values(2,'lisi','101')");
if((res = sqlite3_exec(db,sql,NULL,NULL,&errmsg)) != SQLITE_OK)
{
printf("%s\n",errmsg);
exit(1);
}


//向表中插入数据3
sprintf(sql,"insert into student values(3,'wanger','102')");
if((res = sqlite3_exec(db,sql,NULL,NULL,&errmsg)) != SQLITE_OK)
{
printf("%s\n",errmsg);
exit(1);
}


//按条件在表中查找数据
sprintf(sql,"select * from student where Name=='wanger'");
if((res = sqlite3_exec(db,sql,callback,mydata,&errmsg)) != SQLITE_OK)
{
printf("%s\n",errmsg);
exit(1);
}
printf("%d\t%s\t%s\n",mydata->Id,mydata->Name,mydata->Num);


//删除表中数据3
sprintf(sql,"delete from student where Name=='wanger'");
if((res = sqlite3_exec(db,sql,NULL,NULL,&errmsg)) != SQLITE_OK)
{
printf("%s\n",errmsg);
exit(1);
}


//按条件修改表中数据
sprintf(sql,"update student set Name='seu' where StudentId='100'");
if((res = sqlite3_exec(db,sql,NULL,NULL,&errmsg)) != SQLITE_OK)
{
printf("%s\n",errmsg);
exit(1);
}
sprintf(sql,"select * from student where StudentId=='100'");
if((res = sqlite3_exec(db,sql,callback,mydata,&errmsg)) != SQLITE_OK)
{
printf("%s\n",errmsg);
exit(1);
}
printf("%d\t%s\t%s\n",mydata->Id,mydata->Name,mydata->Num);


sqlite3_close(db);
free(mydata);


return 0;
}


int callback(void* pData,int nCount,char** pValue,char** pName)
{
//数据类型强制转换
MyData myData = NULL;
myData = (MyData)pData;


myData->Id = (int)atoi(pValue[0]);
myData->Name = pValue[1];
myData->Num = pValue[2];


return 0;
}

编译时需要加上sqlite的链接库：-I /usr/local/sqlite3/include -L /usr/local/sqlite3/lib -lsqlite3 -lm