C linux sqlite3 数据库的使用

题目：在c程序中调用sqlite3数据库，实现数据库的添加表格，添加记录，显示记录等各种功能

程序如下：

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <string.h>
void create_table(sqlite3 * db);
void insert_record(sqlite3 * db);
void inquire_usecb(sqlite3 * db);
void inquire_nocb(sqlite3 * db);
void delete_record(sqlite3 * db);
void delete_table(sqlite3 * db);
int  displaycb(void * para,int n_col,char ** col_value,char ** col_name);




int main()
{
sqlite3 * db = NULL;
int ret;
ret = sqlite3_open("mydatabase.db",&db);
if(ret != SQLITE_OK)
{
perror("sqlite open");
exit(-1);
}
else
{
printf("you have open a sqlite3 database successfyl!\n");
}
create_table(db);
insert_record(db);
inquire_usecb(db);
inquire_nocb(db);
delete_table(db);
sqlite3_close(db);


    return 0;


}


void create_table(sqlite3 * db)
{
char *errmsg = NULL;
char *sql;
sql = "create table if not exists mytable(id integer primary key,name text);";
if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("create failed:%s\n",errmsg);
exit(-1);
}
}


void insert_record(sqlite3 * db)
{
char * errmsg = NULL;
char  sql[100];
char  name[20];
int id;
int i;
int num;
printf("enter record num:");
scanf("%d",&num);
getchar();
for(i = 0; i < num; i++)
{
memset(name,0,20);
printf("please enter id:");
scanf("%d",&id);
getchar();
printf("please enter name:");
scanf("%s",name);
getchar();
sprintf(sql,"insert into mytable(id,name)values(%d,'%s');",id,name);
if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("insert error:%s\n",errmsg);
exit(-1);
}
}
/*sql = "insert into mytable(id,name)values(NULL,'keyu');";
if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("insert error:%s\n",errmsg);
exit(-1);
}
sql = "insert into mytable(id,name)values(NULL,'zhangxing');";
if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("insert error:%s\n",errmsg);
exit(-1);
}*/
}


void inquire_usecb(sqlite3 * db)
{
char * errmsg = NULL;
char *sql;
sql = "select * from mytable";
if(SQLITE_OK != sqlite3_exec(db,sql,displaycb,NULL,&errmsg))
{
printf("inquire error%s\n",errmsg);
exit(-1);
}
}


void inquire_nocb(sqlite3 * db)
{
int nrow,ncol;
char **result;
char *errmsg;
int i;
char * sql;
sql = "select * from mytable;";
if(SQLITE_OK != sqlite3_get_table(db,sql,&result,&nrow,&ncol,&errmsg))
{
printf("get_table failed:%s\n",errmsg);
exit(-1);
}
printf("row:%d    column:%d\n",nrow,ncol);
printf("the result of quiring is :\n");
for(i = 0; i < (nrow + 1) * ncol; i++)
{
printf("%-10s ",result[i]);
if((i + 1) % ncol == 0)
{
printf("\n");
}
}
sqlite3_free_table(result);
}
int displaycb(void * para,int n_col,char ** col_value,char ** col_name)
{
int i;
printf("total column is %d\n",n_col);
for(i = 0; i < n_col; i++)
{
printf("column_name:%s --> column_value:%s\n",col_name[i],col_value[i]);
printf("------------------------------\n");
}
return 0;
}


void delete_table(sqlite3 * db)
{
char * errmsg = NULL;
char * sql;
sql = "drop table mytable;";
if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("delete error:%s\n",errmsg);
exit(-1);
}
}