sqlite数据的简易增删改查

需求：

创建一个sqlite3数据库,实现增删改查以及退出功能

代码实现过程：

#include<stdio.h>
#include <sqlite3.h>
#include <string.h>
#include <stdlib.h>

int do_insert(sqlite3* db);

int do_delete(sqlite3* db);

int do_modify(sqlite3* db);

int do_search(sqlite3* db);

int callback(void *err, int ColNum, char **ColContent, char **ColName);

int main(int argc, const char *argv[])
{
    sqlite3* db;
    if(sqlite3_open("./sql.db",&db) != SQLITE_OK){
        fprintf(stderr,"line : %d sqlite3_open failed:%d:%s\n",__LINE__,sqlite3_errcode(db),sqlite3_errmsg(db));
        return -1;
    }
    printf("sqlite3_open success\n");

    char sql[128] = "create table if not exists stu (id int PRIMARY KEY,name char,score float);";
    char* errmsg = NULL;
    printf("sql = %s\n",sql);

    if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK){
        fprintf(stderr,"[line : %d] : sqlite3_exec failed : %s\n",__LINE__,errmsg);
        return -1;
    }

    int choose;
    int buflen;
    while(1){
        system("clear");
        printf("-----------------------------\n");
        printf("----------1.增加-------------\n");
        printf("----------2.删除-------------\n");
        printf("----------3.修改-------------\n");
        printf("----------4.查找-------------\n");
        printf("----------5.退出-------------\n");
        printf("-----------------------------\n");
        printf("请输入操作选项>>>\n");
        scanf("%d",&choose);
        while(getchar() != 10);
        switch(choose){
            //增加
            case 1:
                do_insert(db);
                break;
            //删除
            case 2:
                do_delete(db);
                break;
            //修改
            case 3:
                do_modify(db);
                break;
            case 4:
                do_search(db);
                break;
            case 5:
                goto END;
                break;
            default:
                printf("输入错误，请重新输入\n");
                break;
        }
        printf("输入任意字符清屏\n");
        while(getchar()!=10);
    }
END:
    if(sqlite3_close(db) != SQLITE_OK){
        fprintf(stderr,"line : %d sqlite3_close failed:%d:%s\n",__LINE__,sqlite3_errcode(db),sqlite3_errmsg(db));
        return -1;
    }
    printf("sqlite3_close success\n");
    return 0;
}

int do_insert(sqlite3* db){
    int id;
    char name[32];
    float score;

    printf("请输入id号>>>");
    scanf("%d",&id);
    while(getchar()!=10);

    printf("请输入name>>>");
    scanf("%s",name);
    while(getchar()!=10);
    
    printf("请输入score>>>");
    scanf("%f",&score);
    while(getchar()!=10);
    
    char sql[128] = "";
    sprintf(sql,"insert into stu values (%d,\"%s\",%g);",id,name,score);
//    printf("sql = %s\n",sql);

    char *errmsg = NULL;
    if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK){
        fprintf(stderr,"[line : %d] : sqlite3_exec failed : %s\n",__LINE__,errmsg);
        return -1;
    }
    printf("添加成功\n");
}

int do_delete(sqlite3* db){
    char sql[128] = "";
    int buflen = 0;
    strcpy(sql,"delete from stu where ");
    printf("请按照[arg = *]格式输入\n");
    buflen = strlen(sql);
    fgets(sql+buflen,sizeof(sql)-buflen,stdin);
    sql[strlen(sql)-1] = 0;
//    printf("sql = %s\n",sql);
    char* errmsg = NULL;
    if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK){
        fprintf(stderr,"[line : %d] : sqlite3_exec failed : %s\n",__LINE__,errmsg);
        return -1;
    }
    printf("删除成功\n");
}

int do_modify(sqlite3* db){
    char sql[128] = "";
    int buflen = 0;
    bzero(sql,sizeof(sql));
    strcpy(sql,"update stu set ");
    printf("请按照[arg = *]格式输入需要更新的值\n");
    buflen = strlen(sql);
    fgets(sql+buflen,sizeof(sql)-buflen,stdin);
    sql[strlen(sql)-1] = 0;
    buflen = strlen(sql);
    strcpy(sql+buflen," where ");
    printf("请按照[arg = *]格式输入判断条件\n");
    buflen = strlen(sql);
    fgets(sql+buflen,sizeof(sql)-buflen,stdin);
    sql[strlen(sql)-1] = 0;
    //printf("sql = %s\n",sql);
    char* errmsg = NULL;
    if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK){
        fprintf(stderr,"[line : %d] : sqlite3_exec failed : %s\n",__LINE__,errmsg);
        return -1;
    }
    printf("修改成功\n");
}

int do_search(sqlite3* db){
    char sql[128] = "";
    int buflen = 0;
    bzero(sql,sizeof(sql));
    strcpy(sql,"select * from stu where ");
    buflen = strlen(sql);
    printf("请按照[arg = *]格式输入判断条件\n");
    fgets(sql+buflen,sizeof(sql)-buflen,stdin);
    sql[strlen(sql)-1] = 0;
    printf("sql = %s\n",sql);
    char* errmsg = NULL;
    if(sqlite3_exec(db,sql,callback,NULL,&errmsg) != SQLITE_OK){
        fprintf(stderr,"[line : %d] : sqlite3_exec failed : %s\n",__LINE__,errmsg);
        return -1;
    }
}
int callback(void *err, int ColNum, char **ColContent, char **ColName){
   for(int i=0; i<ColNum; i++){
      printf("%s = %s\n", ColName[i], ColContent[i] ? ColContent[i] : "NULL");
   }
   return 0;
}

代码实现结果：

增加：

删除：

修改：

查找：

退出：