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;
- charchar *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);
- }