sqlite3 c例程（大家直接拿来改就行）

#include <stdio.h>
#include <stdlib.h> //exit等函数的声明
#include <sqlite3.h>

int displaycb(void *para,int n_column,char **column_value,char **column_name);

int inquire_Usecb(sqlite3 *db);

int inquire_nocb(sqlite3 *db);

int createTable(sqlite3 *db);

int insertRecord(sqlite3 *db);

int deleteRecord(sqlite3 *db);

//回调函数 每一条记录执行一次
int displaycb(void *para,int n_column,char **column_value,char **column_name)
{
 int i = 0;
 printf("Total column is %d\n",n_column);
 for(i = 0;i<n_column;i++){
  printf("字段名: %s---->字段值:%s\n",column_name[i],column_value[i]);
 }
 printf("==========================\n");
 return 0;
}
//使用回调函数的查询
int inquire_Usecb(sqlite3 *db)
{
 char *sql;
 char *zErrMsg;
 sql = "select * from 'SensorData';";
 if(SQLITE_OK != sqlite3_exec(db,sql,displaycb,NULL,&zErrMsg)){
  printf("operate failed: %s\n",zErrMsg);
 }
 return 0;
}
//不使用回调函数的查询
int inquire_nocb(sqlite3 *db)
{
 int nrow = 0,ncolumn = 0;
 char **azResult=0;
 int i = 0;
 char *sql;
 char *zErrMsg;
 sql = "SELECT * FROM SensorData";
 if(SQLITE_OK != sqlite3_get_table(db,sql,&azResult,&nrow,&ncolumn,&zErrMsg)){
  printf("operate failed: %s\n",zErrMsg);
 }

 printf("row:%d column = %d\n",nrow,ncolumn);
 printf("The result of querying is :\n");
 for ( i = 0;i < ( nrow + 1) * ncolumn; i++)
  printf("azResult[%d] = %s\n",i,azResult[i]);
 sqlite3_free_table(azResult);
 return 0;
}
//创建表函数
int createTable(sqlite3 *db)
{
 char *zErrMsg = 0;
 char *sql = "CREATE TABLE SensorData(ID INTEGER PRIMARY KEY,SensorID INTEGER,SiteNum INTERER,Time VARCHAR(12),SensorParameter REAL);";
 if(SQLITE_OK != sqlite3_exec(db,sql,0,0,&zErrMsg)){
  printf("operate failed: %s\n",zErrMsg);
 }
}
//插入数据函数
int insertRecord(sqlite3 *db)
{
 char *sql;
 char *zErrMsg;
 sql = "INSERT INTO 'SensorData' VALUES(NULL,101,261,'20100314',18.9);";
 if(SQLITE_OK != sqlite3_exec(db,sql,0,0,&zErrMsg)){
  printf("operate failed: %s\n",zErrMsg);
 }

 sql = "INSERT INTO 'SensorData' VALUES(NULL,369,281,'20100616',113);";
 if(SQLITE_OK != sqlite3_exec(db,sql,0,0,&zErrMsg)){
  printf("operate failed: %s\n",zErrMsg);
 }

 sql = "INSERT INTO 'SensorData' VALUES(NULL,667,290,'20110315',27);";
 if(SQLITE_OK != sqlite3_exec(db,sql,0,0,&zErrMsg)){
  printf("operate failed: %s\n",zErrMsg);
 }

 sql = "INSERT INTO 'SensorData' VALUES(NULL,865,300,'20120616',323);";
 if(SQLITE_OK != sqlite3_exec(db,sql,0,0,&zErrMsg)){
  printf("operate failed: %s\n",zErrMsg);
 }

}
//删除数据函数
int deleteRecord(sqlite3 *db)
{
 char *sql;
 char *zErrMsg;
 char **azResult=0;
 int nrow = 0,ncolumn = 0;
 int i;
 sql = "DELETE FROM SensorData WHERE ID=4;";
 if(SQLITE_OK != sqlite3_exec(db,sql,0,0,&zErrMsg)){
  printf("operate failed: %s\n",zErrMsg);
 }

 sql = "SELECT * FROM SensorData;";
 if(SQLITE_OK != sqlite3_get_table(db,sql,&azResult ,&nrow,&ncolumn,&zErrMsg)){
  printf("operate failed: %s\n",zErrMsg);
 }
 printf("row:%d column:%d\n",nrow,ncolumn);
 printf("After deleting,the result of querying is :\n");
 for(i=0;i<(nrow+1)*ncolumn;i++)
  printf("azResult[%d] = %s\n",i,azResult[i]);
 sqlite3_free_table(azResult);
}
int main(void)
{
 sqlite3 *db = NULL;
 int rc;

 //打开指定的数据库文件
 rc = sqlite3_open("mydatabase.db",&db);
 if(rc){
  fprintf(stderr,"can't open database: %s\n",sqlite3_errmsg(db));
  sqlite3_close(db);
  exit(1);
 }else
  printf("You have opened a sqlite3 database successfully!\n");
 //创建表
 createTable(db);
 //插入记录
 insertRecord(db);
 //查询
 //使用sqlite3_get_table实现查询
 inquire_nocb(db);
 printf("ppppppppppppppppppppppppppppppppppp\n");
 //使用回调函数实现查询
 inquire_Usecb(db);
 //删除记录
 deleteRecord(db);
 //关闭数据库
 sqlite3_close(db);
 return 0;
}

 

执行结果：

 

[root@localhost mysqlite_databasefile]# ./mysqlite3
You have opened a sqlite3 database successfully!
row:4 column = 5
The result of querying is :
azResult[0] = ID
azResult[1] = SensorID
azResult[2] = SiteNum
azResult[3] = Time
azResult[4] = SensorParameter
azResult[5] = 1
azResult[6] = 101
azResult[7] = 261
azResult[8] = 20100314
azResult[9] = 18.9
azResult[10] = 2
azResult[11] = 369
azResult[12] = 281
azResult[13] = 20100616
azResult[14] = 113.0
azResult[15] = 3
azResult[16] = 667
azResult[17] = 290
azResult[18] = 20110315
azResult[19] = 27.0
azResult[20] = 4
azResult[21] = 865
azResult[22] = 300
azResult[23] = 20120616
azResult[24] = 323.0
ppppppppppppppppppppppppppppppppppp
Total column is 5
字段名: ID---->字段值:1
字段名: SensorID---->字段值:101
字段名: SiteNum---->字段值:261
字段名: Time---->字段值:20100314
字段名: SensorParameter---->字段值:18.9
==========================
Total column is 5
字段名: ID---->字段值:2
字段名: SensorID---->字段值:369
字段名: SiteNum---->字段值:281
字段名: Time---->字段值:20100616
字段名: SensorParameter---->字段值:113.0
==========================
Total column is 5
字段名: ID---->字段值:3
字段名: SensorID---->字段值:667
字段名: SiteNum---->字段值:290
字段名: Time---->字段值:20110315
字段名: SensorParameter---->字段值:27.0
==========================
Total column is 5
字段名: ID---->字段值:4
字段名: SensorID---->字段值:865
字段名: SiteNum---->字段值:300
字段名: Time---->字段值:20120616
字段名: SensorParameter---->字段值:323.0
==========================
operate failed: near "DELETE?": syntax error
row:4 column:5
After deleting,the result of querying is :
azResult[0] = ID
azResult[1] = SensorID
azResult[2] = Si