sqlite3 C语言编程

最新推荐文章于 2025-06-25 10:41:50 发布

原创最新推荐文章于 2025-06-25 10:41:50 发布 · 2.6k 阅读

0 ·

CC 4.0 BY-SA版权

嵌入式 Linux编程专栏收录该内容

12 篇文章

订阅专栏

本文介绍了如何使用C语言进行SQLite3数据库的操作，包括基本的数据库操作例程，并展示了运行结果。此外，还提到了一套不同的查询接口。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

sqlite数据库操作例程：

#include<stdio.h>
#include<sqlite3.h>
#include<string.h>

int main()
{
	sqlite3 *db;
	char *zErrMsg = 0;
	char sql[128];		//存放sqlite执行命令
	char buff[1024];	//存放读取到的数据结果
	int rc;
	int len = 0;
	memset(buff,0,sizeof(buff));
	rc = sqlite3_open("test.db",&db);	//打开数据库
	if(rc != SQLITE_OK)
	{
		printf("zErrMsg = %s\n",zErrMsg);
		return -1;
	}

	memset(sql,'\0',128);
	strcpy(sql,"create table student(id integer,name varchar(10),f float);");//创建表
	sqlite3_exec(db,sql,0,0,&zErrMsg);	//执行sqlite命令语句

	memset(sql,'\0',128);
	strcpy(sql,"insert into student values(1,'xiaoming',1.1);");	//插入数据
	rc = sqlite3_exec(db,sql,0,0,&zErrMsg);
	if(rc != SQLITE_OK)
	{
		printf("zErrMsg = %s\n",zErrMsg);
		return -1;
	}

	memset(sql,'\0',128);
	strcpy(sql,"insert into student values(2,'xiaohong',1.2);");
	rc = sqlite3_exec(db,sql,0,0,&zErrMsg);
	if(rc != SQLITE_OK)
	{
		printf("zErrMsg = %s\n",zErrMsg);
		return -1;
	}

	int nrow = 0,ncolnum = 0;
	char **azResult;	//存放查询结果

	memset(sql,'\0',128);
	strcpy(sql,"select * from student;");
	rc = sqlite3_get_table(db,sql,&azResult,&nrow,&ncolnum,&zErrMsg); //查询数据
	if(rc != SQLITE_OK)
	{
		printf("zErrMsg = %s\n",zErrMsg);
		return -1;
	}

	int i = 0;
	printf("row:%d colnum=%d\n",nrow,ncolnum);
	for(i = ncolnum;i < (nrow+1)*ncolnum;i++)
	{
		printf("azResult[%d] = %s\n",i,azResult[i]);	//打印
		memcpy(buff+len,azResult[i],strlen(azResult[i])); //整条数据保存到另一个buff
		len+=strlen(azResult[i]);
	}
	for(i = 0;i < len;i++)
	{
		printf("%c ",buff[i]);
	}
	printf("\n");

	memset(sql,'\0',128);
	strcpy(sql,"delete from student;");	//删除
	sqlite3_exec(db,sql,0,0,&zErrMsg);

	sqlite3_free(zErrMsg);
	sqlite3_free_table(azResult);	//释放空间
	sqlite3_close(db);		//关闭数据库
	return 0;	 
}

编译：gcc test.c -o test -lsqlite3

运行结果：

另一套查询接口：

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

int main()
{
	sqlite3 *db;
	char *zErrMsg = 0;
	char sql[128];
	int rc;
	int len = 0;
	rc = sqlite3_open("test1.db",&db);
	if(rc != SQLITE_OK)
	{
		printf("zErrMsg = %s\n",zErrMsg);
		return -1;
	}
	sprintf(sql,"create table student(id integer,name varchar(10),f float,Occur DATETIME DEFAULT(datetime('now','localtime')));");
	sqlite3_exec(db,sql,0,0,&zErrMsg);
  	sprintf(sql,"insert into student(id,name,f) values(%d,'%s',%f);",1,"小明",3.33);
  	if(sqlite3_exec(db,sql,0,0,&zErrMsg)!=SQLITE_OK)
	{
		printf("zErrMsg = %s\n",zErrMsg);   
		return 0x0b;
	}
	
	sqlite3_stmt *stmt;
	int ncols;
	sprintf(sql,"select * from student");
	rc = sqlite3_prepare(db,sql,strlen(sql),&stmt,NULL);
	if(rc !=SQLITE_OK)
	{
		fprintf(stderr,"sql error:%s\n",sqlite3_errmsg(db));
	}
	while(sqlite3_step(stmt) == SQLITE_ROW)
	{
		fprintf(stdout,"%d ,%s ,%f,%s\n",
			sqlite3_column_int(stmt,0),
			sqlite3_column_text(stmt,1),
			sqlite3_column_double(stmt,2),			
			sqlite3_column_text(stmt,3)
		);
	}
	sqlite3_finalize(stmt);
	sqlite3_close(db);
}