一.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;
}
?
