linux学习：sqlite3简略数据库查询操作

linux学习：sqlite3简单数据库查询操作

1、Fedora下首先确保已装sqlite3，用命令行的方式装比较麻烦，其实Febora20中软件里面可以找到一个名叫Sqliteman的软件，下载安装就行了

/*
 ============================================================================
 Name        : Test.c
 Author      : wangchuan
 Version     :
 Copyright   : Your copyright notice
 Description : Hello World in C, Ansi-style
 ============================================================================
 */

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

sqlite3 *db = NULL;

int open_database(const char *dbfile)
{
	int result;
	result = sqlite3_open(dbfile,&db);
	return result;
}
int close_database(void)
{
	int result;
	result = sqlite3_close(db);
	return result;
}

#define TM_L 14
typedef struct _calllist callist;
struct _calllist{
	long id;
	int type;
	long telnum;
	char btime[TM_L+1];
	long tcount;
	float charge_rate;
	float charge_sum;
};
void printdb(callist *cl,int length)
{
	int i= sizeof(callist);
	int j;
	char btime[TM_L+1]={0};
	for(j=0;j<length/i;j++){
		memcpy(btime,(cl+j*i)->btime,TM_L);
		printf("%ld, %d, %ld, %s, %ld, %1.2f, %1.2f\n",
				(cl+j*i)->id,
				(cl+j*i)->type,
				(cl+j*i)->telnum,
				btime,
				(cl+j*i)->tcount,
				(cl+j*i)->charge_rate,
				(cl+j*i)->charge_sum);
	}
}
int main() {
	const char *dbfile = "data.sqlite";
	callist *p_cl,*cl;
	int res;
	char *errmsg=NULL;
	char **result;
	int row,col;
	int i;
	res=open_database(dbfile);
	if(res!=0){
		printf("数据库打开失败:%s\n",sqlite3_errmsg(db));
		return 1;
	}
	puts("数据库已打开");
	const char *sqlcmd = "SELECT * FROM list";
	res = sqlite3_get_table(db,sqlcmd,&result,&row,&col,&errmsg);
	if(res!=SQLITE_OK)
		printf("查询失败，代码：%d-%s\n",res,errmsg);
	else if(row<1)
		puts("查询结果为0条");
	else{
		puts("查询成功，查询结果为：");
		cl = malloc(sizeof(callist)*row);

		p_cl=cl;

		for(i=1;i<=row;i++,p_cl+=sizeof(callist)){
			p_cl->id=atol(result[i*col]);
			p_cl->type=atoi(result[i*col+1]);
			p_cl->telnum=atol(result[i*col+2]);
			memcpy(p_cl->btime,result[i*col+3],TM_L);
			p_cl->tcount=atol(result[i*col+4]);
			p_cl->charge_rate=atof(result[i*col+5]);
			p_cl->charge_sum=atof(result[i*col+6]);
			//printf("%d\n",p_cl->id);
		}
		sqlite3_free_table(result);
		printdb(cl,sizeof(callist)*row);
		free(cl);
	}

	res=close_database();
	if(res!=0){
		printf("数据库关闭失败：%s\n",sqlite3_errmsg(db));
		return 1;
	}
	puts("数据库已关闭");
	return 0;
}

2、cd到此目录下编译：

cc Test.c -o Test -lsqlite3

3、运行：

./Test

注：在最后关闭数据库时会提示段错误（吐核），也没找到什么原因造成的。