linux-c 使用c语言操作sqlite3数据库-1

一、练习目标

1、目标

1、使用sqlite3_exec执行查询语句，并将查询结果insert到链表中，最后打印链表的内容；

2、使用sqlite3_get_table执行查询语句，并以key：value的方式，打印查询结果。

2、环境准备

2.1、sql脚本文件

create table if not exists student(
	id integer primary key autoincrement,
	name text not null,
	sex text check(sex=='f' or sex='m') default 'f',
	chinese real check(chinese>=0 and chinese<=150) default 0,
	math real check(math>=0 and math<=150) default 0,
	english real check(english>=0 and english<=1500) default 0
);

insert into student (name,sex,chinese,math,english)values ('aaa','f',10,11,12);
insert into student (name,sex,chinese,math,english)values ('bbb','m',20,21,22);
insert into student (name,sex,chinese,math,english)values ('bbb','f',30,31,32);
insert into student (name,sex,chinese,math,english)values ('ccc','m',40,41,42);
insert into student (name,sex,chinese,math,english)values ('ddd','f',50,51,52);
insert into student (name,sex,chinese,math,english)values ('eee','m',60,61,62);
insert into student (name,sex,chinese,math,english)values ('fff','f',70,71,72);
insert into student (name,sex,chinese,math,english)values ('ggg','m',80,81,82);
insert into student (name,sex,chinese,math,english)values ('hhh','f',90,91,92);
insert into student (name,sex,chinese,math,english)values ('iii','m',100,101,102);
insert into student (name,sex,chinese,math,english)values ('jjj','f',110,111,112);

2.2、通过sqlite3的命令行工具，导入表结构及数据

# 导入建表语句和数据
.read sql脚本名称

# 配置sqlite3工具的select结果，显示表头
.head on

# 配置sqlite3工具的select结果,对其
.mode column

# 查询导入的表数据
select * from student;

# 显示上一行的查询结果
id  name  sex  chinese  math   english
--  ----  ---  -------  -----  -------
1   aaa   f    10.0     11.0   12.0   
2   bbb   m    20.0     21.0   22.0   
3   bbb   f    30.0     31.0   32.0   
4   ccc   m    40.0     41.0   42.0   
5   ddd   f    50.0     51.0   52.0   
6   eee   m    60.0     61.0   62.0   
7   fff   f    70.0     71.0   72.0   
8   ggg   m    80.0     81.0   82.0   
9   hhh   f    90.0     91.0   92.0   
10  iii   m    100.0    101.0  102.0  
11  jjj   f    110.0    111.0  112.0

2.3、gcc编译指令（使用到了sqlite3第三方库）

gcc 源码.c -lsqlite3

二、相关API

参考sqlite3官网： List Of SQLite Functions

1、sqlite3_open

2、sqlite3_close

3、sqlite3_exec

4、sqlite3_get_table

三、源码与结果

1、sqlite3_exec练习

typedef struct{
	int id;
	char name[20];
	char sex[5];
	float chinese;
	float math;
	float english;
}stu_t;

typedef struct nn{
	union{
		stu_t data;
		struct nn* tail;//尾节点
	};
	struct nn* next;
}link_t, node_t;

link_t* link_create();
void link_insertRear(link_t* L, stu_t stu);
void link_destroy(link_t* L);


int callback_func(void* arg, int argc, char** argv, char** colv){
	//printf("查询到了一条数据\n");
	link_t* L=(link_t*)arg;
	//
	stu_t stu;
	stu.id=atoi(argv[0]);
	strcpy(stu.name,argv[1]);
	strcpy(stu.sex,argv[2]);
	stu.chinese=atof(argv[3]);
	stu.math=atof(argv[4]);
	stu.english=atof(argv[5]);
	//
	link_insertRear(L, stu);
	

	return 0;
}


int main(int argc, const char *argv[])
{
	//open
	sqlite3* db;
	if(sqlite3_open("test.db", &db)!=SQLITE_OK){
		fprintf(stderr, "数据库文件打开失败\n");
		return -1;
	}	
	printf("数据库打开成功\n");
	
	//exec
	char* sql="select id,name,sex,chinese,math,english from student;";
	
	printf("SQLITE_OK=%d\n", SQLITE_OK);
	//
	link_t* L=link_create();
	
	char* errmsg=NULL;

	if(sqlite3_exec(db, sql, callback_func,L, &errmsg)!=SQLITE_OK){
		fprintf(stderr, "sqlite_exec 执行error, errmsg=[%s]\n", errmsg);
		return -1;
	}
	
	sqlite3_free(errmsg);
	//打印查询结果
	printf("查询成功\n");
	node_t* p=L->next;
	while(p){
		stu_t s=p->data;
		printf("id=[%d],name=[%s],sex=[%s],chinese=[%.1f],math=[%.1f],english=[%.1f]\n", s.id,
				s.name, s.sex, s.chinese, s.math, s.english);
		p=p->next;
	}
	
	//close
	if(sqlite3_close(db) != SQLITE_OK){
		fprintf(stderr, "数据库文件关闭失败\n");
		return -1;
	}

	printf("数据库文件已经关闭\n");

	return 0;
}

link_t* link_create(){
	link_t* L=(link_t*)malloc(sizeof(link_t));
	L->next=NULL;
	L->tail=L;
	return L;
}

void link_insertRear(link_t* L, stu_t stu){
	node_t* p=(node_t*)malloc(sizeof(node_t));
	p->next=NULL;
	memcpy(&p->data, &stu, sizeof(stu_t));
	//
	L->tail->next=p;
	L->tail=p;
}

void link_destroy(link_t* L){
	node_t* p=L;
	while(p!=NULL){
		node_t* temp=p;
		free(temp);
		p=p->next;
	}
	free(L);
}

运行结果：

2、sqlite3_get_table练习

源码：

int main(int argc, const char *argv[])
{
	//open
	sqlite3* db;
	if(sqlite3_open("test.db", &db)!=SQLITE_OK){
		fprintf(stderr,"文件打开失败\n");
		return -1;
	}
	printf("文件打开成功");
	

	//get_table
	char* sql="select id,name,sex,chinese,math,english from student;";
	char** res=NULL;
	int row,col;
	char* errmsg=NULL;

	if(sqlite3_get_table(db, sql,&res,&row,&col,&errmsg)!=SQLITE_OK){
		fprintf(stderr,"函数sqlite3_get_table执行失败, errmsg=[%s]\n", errmsg);
		return -1;
	}
	
	//处理查询返回结果
//	for(int i=0;i<row+1;i++){
//		for(int j=0;j<col;j++){
//			printf("%s\t", res[i*col+j]);
//		}
//		printf("\n");
//	}
	//
	printf("***********************");
	for(int i=1;i<row+1;i++){
		printf("第[%02d]行: ", i);
		for(int j=0;j<col;j++){
			printf("%s=[%s] ",res[0+j], res[i*col+j]);
		}
		printf("\n");
	}

	//释放
	sqlite3_free_table(res);	
	sqlite3_free(errmsg);

	//close
	if(sqlite3_close(db)!=SQLITE_OK){
		fprintf(stderr,"文件关闭失败\n");
		return -1;
	}
	printf("文件关闭成功\n");
	
	return 0;
}

运行结果：