一、练习目标
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[])
{//opensqlite3* db;if(sqlite3_open("test.db", &db)!=SQLITE_OK){fprintf(stderr, "数据库文件打开失败\n");return -1;} printf("数据库打开成功\n");//execchar* 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;}//closeif(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[])
{//opensqlite3* db;if(sqlite3_open("test.db", &db)!=SQLITE_OK){fprintf(stderr,"文件打开失败\n");return -1;}printf("文件打开成功");//get_tablechar* 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);//closeif(sqlite3_close(db)!=SQLITE_OK){fprintf(stderr,"文件关闭失败\n");return -1;}printf("文件关闭成功\n");return 0;
}
运行结果: