linux学习——数据库API创建

一.API操作

1.int sqlite3_open(char *filename,sqlite3 **db)
        功能：打开sqlite数据库
        参数：
                filename:数据库文件路径
                db:指向sqlite句柄的指针 （splite3* db;）
        返回值：
                成功返回0，失败返回错误码（非0值）

                成功返回SQLITE_OK

2.int sqlite3_close(sqlite3 *db);
        功能：关闭sqlite数据库
        参数：
                db 操作数据库的指针
        返回值：
                成功返回0，失败返回错误码
3.const char *sqlite3_errmsg(sqlite3 *db);
        功能：通过DB句柄得到数据库操作的错误信息
        参数：
                db 操作数据库的指针
        返回值：
                返回错误信息的首地址

4.int sqlite3_exec(

                                  sqlite3*db,

                                  const char *sql,
                                  int (*callback)(void *,int,char**,char *8)
                                  void *,
                                 char **errmsg);
                        功能：执行一条sql语句
        参数
                db:数据库操作句柄
                sql：一条sql语句
                callback：回调函数，只有sql为查询语句的时候，
                        才会执行此语句
                void * ：给回调函数传递参数
                errmsg:错误信息
        返回值：
                成功：SQLITE_OK

int (*callback)(void *,int,char**,char *8)
功能：查询结果，是一个函数指针类型，传递一个函数名

用到第三方库需要 -lsqlite3

回调函数接口：
typedef int(*sqlite3_callback)(
                                                void *para,
                                                int f_num,
                                                char ** f_value,
                                                char ** f_name)
        功能： 每找到一条记录自动执行一次回调函数
                para:传递给回调函数的参数
                f_num:记录中包含的字段数目
                f_value:包含每个字段值的指针数组
                f_name:包含每个字段名称的指针数组
        返回值

                成功返回0，失败返回-1

int callback(void *para,int f_num,char **f_value,char **f_name)
{int i=0;for(i=0;i<f_num;i++){printf("%s",f_value[i]);}putchar(10);return 0;
}
int do_query(sqlite3 *db)
{char sql[128]={0};char *errmsg;sprintf(sql,"select * from stu");if(sqlite3_exec(db,sql,callback,NULL,&errmsg) !=SQLITE_OK){printf("%s\n",errmsg);exit(0);}else{printf("query success\n");}return 0;
}

不使用回调函数执行SQL语句
int sqlite3_get_table(
                                        sqlite3 *db,
                                        const char *sql,
                                        char ***resultp,
                                        int *nrow, // 相当于行
                                        int *ncolumu,//列
                                        char **errmsg)
功能：执行sql操作
        db:数据库句柄
        sql:sql语句
        resultp:用来指向sql执行结果的指针
        nrow:满足条件的记录的数目
        ncolumn:每条记录包含的字段数目
        errmsg:错误信息指针的地址
返回值：

        成功返回0，失败返回错误码

这是没有打印抬头的

int  do_query2(sqlite3 *db)
{char sql[128] ={};char *errmsg;char **resultp;int nrow;int ncloumn;int i,j,index;sprintf(sql,"select * from stu");if(sqlite3_get_table(db,sql,&resultp,&nrow,&ncloumn,&errmsg) !=SQLITE_OK){printf("%s",errmsg);}else{printf("query success\n");}index =ncloumn;for(i=0;i<nrow;i++){for(j=0;j<ncloumn;j++){printf("%-11s",resultp[index++]);}putchar(10);}
}

加上代码之后

 for(j=0;j<ncloumn;j++){printf("%-11s",resultp[j]);}putchar(10);

数据库API创建的全部代码

#include<stdio.h>
#include<stdlib.h>
#include <sqlite3.h>#define DATABASE "student.db"
int do_insert(sqlite3 *db)
{int id;char name[32]={0};int score;char sql[128]={0};char* errmsg;printf("please input id:");scanf("%d",&id);getchar();printf("please input name:");scanf("%s",name);getchar();printf("please input score:");scanf("%d",&score);getchar();sprintf(sql,"insert into stu values(%d,'%s',%d);",id,name,score);if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) !=SQLITE_OK){printf("%s\n",errmsg);exit(0);}else{printf("insert success\n");}return 0;
}
int do_delete(sqlite3 *db)
{int id;char sql[128]={0};char *errmsg;printf("please input id:");scanf("%d",&id);getchar();sprintf(sql,"delete from stu where id=%d;",id);if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) !=SQLITE_OK){printf("%s\n",errmsg);exit(0);}else{printf("delete success\n");}return 0;
}
int do_update(sqlite3 *db)
{int id;char sql[128] ={0};int score;char *errmsg;printf("please input id:");scanf("%d",&id);getchar();printf("please input score:");scanf("%d",&score);getchar();sprintf(sql,"update stu set score =%d where id=%d;",score,id);if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)  !=SQLITE_OK){printf("%s\n",errmsg);exit(0);}else{printf("update success\n");}return 0;
}
int callback(void *para,int f_num,char **f_value,char **f_name)
{int i=0;for(i=0;i<f_num;i++){printf("%s",f_value[i]);}putchar(10);return 0;
}
int do_query1(sqlite3 *db)
{char sql[128]={0};char *errmsg;sprintf(sql,"select * from stu");if(sqlite3_exec(db,sql,callback,NULL,&errmsg) !=SQLITE_OK){printf("%s\n",errmsg);exit(0);}else{printf("query success\n");}return 0;
}
int  do_query2(sqlite3 *db)
{char sql[128] ={};char *errmsg;char **resultp;int nrow;int ncloumn;int i,j,index;sprintf(sql,"select * from stu");if(sqlite3_get_table(db,sql,&resultp,&nrow,&ncloumn,&errmsg) !=SQLITE_OK){printf("%s",errmsg);}else{printf("query success\n");}index =ncloumn;for(j=0;j<ncloumn;j++){printf("%-11s",resultp[j]);}putchar(10);for(i=0;i<nrow;i++){for(j=0;j<ncloumn;j++){printf("%-11s",resultp[index++]);}putchar(10);}
}
int main()
{char *errmsg;sqlite3* db;int cmd;if(sqlite3_open(DATABASE,&db) !=SQLITE_OK){printf("%s\n",sqlite3_errmsg(db));exit(1);}else{printf("open slite3 success\n");}if(sqlite3_exec(db,"create table stu(id Integer,name char,score Integer);",NULL,NULL,&errmsg) !=SQLITE_OK){printf("%s\n",sqlite3_errmsg(db));exit(1);}else{printf("create or open table success\n");}while(1){printf("*********************\n");printf("1:insert,2:delete,3:quert,4:update,5:quit\n");printf("*********************\n");printf("please input cmd\n");scanf("%d",&cmd);getchar();switch(cmd){case 1:do_insert(db);break;case 2:do_delete(db);break;case 3:do_query2(db);break;case 4:do_update(db);break;case 5:sqlite3_close(db);exit(0);break;case 6:printf("err cmd\n");}}return 0;
}