SQLite C/C++ 增删改查

运行代码在文末

一 准备

C下要加入的头文件   

#include "sqlite3.h"

用到的头文件密码：gboy

二 3个常用 C/C++ api

sqlite3_open(const char *filename, sqlite3 **ppDb) 

sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void *data, char **errmsg)

sqlite3_close(sqlite3*)

注意点：

sqlite3_exec提供一个执行 SQL 命令的快捷方式 ,sqlite_callback为回调函数，具体如下

// int sqlite_callback( 
// void* pvoid, /* 由 sqlite3_exec()  的第四个参数data 传递而来,其他参数会自动赋值 */ 
// int argc, /* 表的列数 */ 
// char** argv, /* 指向查询结果的指针数组, 可以由 sqlite3_column_text() 得到 */ 
// char** col /* 指向表头名的指针数组, 可以由 sqlite3_column_name() 得到 */ 
// );

回调函数要加return 语句 ，否则errmsg会赋值会

SQLITE_ABORT        4   /* Callback routine requested an abort */

三代码

#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 sqlite_callback( 
void* pvoid,  //由 sqlite3_exec() 的第四个参数传递而来 
int argc,  //表的列数 
char** argv, //指向查询结果的指针数组, 可以由 sqlite3_column_text() 得到  
char** col //指向表头名的指针数组, 可以由 sqlite3_column_name() 得到 
); 
*/

//回调函数 每一条记录执行一次
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;
}