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;
}
运行结果：
