sqlite3基本用法

linux系统下调用sqlite3库函数实现基本的数据库创建，查询，更改功能。

#include <sys/types.h>#include <sys/socket.h>#include <linux/in.h>#include <linux/un.h>#include <string.h>#include <signal.h>#include <stdio.h>#include <stdlib.h>#include <errno.h>#include <unistd.h>#include <sys/ioctl.h>#include <sys/select.h>#include <sys/time.h>#include <unistd.h>#include <time.h>#include <string.h>#include "sqlite3.h"#include <dlfcn.h>//gcc  -o sqlite_1 sqlite_1.c -L. -lsqlite3 -ldl -lpthread static char *conf[]={"12.1","13.1","14.1","15.1","16.1","17.1","18.1","19.1","20.1","21.1"}; static char *update[]={"22.1","23.1","24.1","25.1","26.1","27.1","28.1","29.1","30.1","31.1"}; static sqlite3 *db=NULL; int i,result; char sql[128]; char *errmsg; int ret; char **dbResult; float argument[10]; int nRow,nColumn; int CurrentIndex;void  sqlite_test_db_init(){  //打开或新建一个数据库 ret = sqlite3_open("sqlite_test.db",&db); if(ret!=SQLITE_OK)   {     printf("open error!\n");     exit(1);   } //数据库中建表result = sqlite3_exec(db, "create table if not exists base_info_table(ID integer primary key autoincrement,value);", NULL, NULL, &errmsg); if(result!=SQLITE_OK)   {      printf("create base_info_table error!\n");     return ;   }result = sqlite3_exec(db, "create table if not exists index_table(ID integer primary key autoincrement,current_index);", NULL, NULL, &errmsg); if(result!=SQLITE_OK)   {        printf("create index_table error!\n");     return ;   }//数据库中数据插入 result = sqlite3_exec(db,"BEGIN",0,0,&errmsg); for(i=1;i<11;i++)   {    sprintf(sql,"insert into base_info_table values(%d,'%s')",i,conf[i-1]);    result = sqlite3_exec(db,sql,0,0,&errmsg);    if(result!=SQLITE_OK)     {      printf("insert error\n");       return;     }   } result = sqlite3_exec(db,"COMMIT",0,0,&errmsg);// result = sqlite3_exec(db,"BEGIN",0,0,&errmsg);    sprintf(sql,"insert into index_table values(%d,%d)",1,1);    result = sqlite3_exec(db,sql,0,0,&errmsg);    if(result!=SQLITE_OK)     {      printf("insert error\n");       return;     } result = sqlite3_exec(db,"COMMIT",0,0,&errmsg);} void  get_data_sqlite_test_db(){//从数据库中读取数据并转换数据类型 ret = sqlite3_get_table(db,"select *from base_info_table",&dbResult,&nRow,&nColumn,&errmsg); if(ret == SQLITE_OK)   {     for(i=0;i<10;i++)//     {      argument[i] = atof(dbResult[3+2*i]);      printf("%f\n",argument[i]);     }   }}static int GetCurrentIndex(){ result = sqlite3_get_table(db,"select *from index_table",&dbResult,&nRow,&nColumn,&errmsg); if(result == SQLITE_OK) {  return atoi(dbResult[nColumn+1]); }}static int PutCurrentIndex(int index){ sprintf(sql,"update index_table set current_index=%d where id=1",index); sqlite3_stmt *stmt; sqlite3_prepare(db,sql,strlen(sql),&stmt,NULL); if(sqlite3_step(stmt)!=SQLITE_DONE)   {     sqlite3_finalize(stmt);     return 0;   }}void update_date_sqlite_test_db(){ CurrentIndex=GetCurrentIndex(); printf("CurrentIndex=%d\n",CurrentIndex); ret = sqlite3_exec(db,"BEGIN",0,0,&errmsg); for(i=1;i<11;i++) { sprintf(sql,"update base_info_table set value='%s' where id=%d",update[i-1],i); ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg); CurrentIndex++; } ret = sqlite3_exec(db,"COMMIT",0,0,&errmsg);  PutCurrentIndex(CurrentIndex);}int main(void){ system("rm sqlite_test.db"); sqlite_test_db_init(); get_data_sqlite_test_db(); update_date_sqlite_test_db(); get_data_sqlite_test_db(); sqlite3_free_table(dbResult);//释放内存 sqlite3_close(db);//关闭数据库 防止内存泄漏 return;}