网络编程 3.21

#include <myhead.h>

int do_add(sqlite3 *ppDb)
{
    int add_numb = 0;
    char add_name[20] = {0};
    double add_score = 0;
    printf("请输入学号:");
    scanf("%d",&add_numb);
    printf("请输入姓名:");
    scanf("%s",add_name);
    printf("请输入成绩:");
    scanf("%lf",&add_score);
    getchar();

    char sql[128] = {0};
    sprintf(sql,"insert into Stu values(%d,\"%s\",%lf);",add_numb,add_name,add_score);
    char *errmsg =NULL;

    if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg) != SQLITE_OK)
    {
        printf("exec error:%s\n",errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    printf("插入成功\n");
    return 0;
}

int do_del(sqlite3 *ppDb)
{
    int del_numb = 0;
    char del_name[20] = {0};
    double del_score = 0;
    int choose = -1;
    printf("请输入你想删除的字段:\n1.学号\n2.姓名\n3.成绩\n");
    scanf("%d",&choose);
    getchar();
    char sql[128] = {0};

    switch(choose)
    {
    case 1:
        {
            printf("请输入您的学号:");
            scanf("%d",&del_numb);
            getchar();    
            sprintf(sql,"delete from Stu where del_numb == %d;",del_numb);
            break;
        }
    case 2:
        {
            printf("请输入您的姓名:");
            scanf("%s",del_name);
            getchar();        
            sprintf(sql,"delete from Stu where del_name == \"%s\";",del_name);
            break;
        }
    case 3:
        {
            printf("请输入您的成绩:");
            scanf("%lf",&del_score);
            getchar();    
            sprintf(sql,"delete from Stu where del_score == %lf;",del_score);
            break;
        }
    default:
        {
            printf("输入错误,请重新输入\n");
        }
    }
    char *errmsg =NULL;

    if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg) != SQLITE_OK)
    {
        printf("exec error:%s\n",errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    printf("删除成功\n");
    return 0;

}


int do_update(sqlite3 *ppDb)
{
    int update_numb = 0;
    char update_name[20] = {0};
    double update_score = 0;
    int choose = -1;
    int choose1 = -1;
    printf("请输入您想要修改的信息:\n1.学号\n2.姓名\n3.成绩\n");
    scanf("%d",&choose);
    getchar();
    char sql[128] = {0};
    switch(choose)
    {
    case 1:
        {
            printf("请输入您的学号:");
            scanf("%d",&update_numb);
            getchar();        

            printf("请输入您想要限制的条件:\n1.姓名\n2.成绩\n");
            scanf("%d",&choose1);
            getchar();
            switch(choose1)
            {
            case 1:
                {
                    printf("请输入您的姓名:");
                    scanf("%s",update_name);
                    getchar();
                    sprintf(sql,"update Stu set update_numb = %d where update_name == \"%s\";",update_numb,update_name);
                    break;
                }
            case 2:
                {
                    printf("请输入您的成绩:");
                    scanf("%lf",&update_score);
                    getchar();
                    sprintf(sql,"update Stu set update_numb = %d where update_score == %lf;",update_numb,update_score);
                    break;
                }
            default:
                {
                    printf("输入错误,请重新输入\n");
                }
            }
        }
    case 2:
        {
            printf("请输入您的姓名:");
            scanf("%s",update_name);
            getchar();        

            printf("请输入您想要限制的条件:\n1.学号\n2.成绩\n");
            scanf("%d",&choose1);
            getchar();
            switch(choose1)
            {
            case 1:
                {
                    printf("请输入您的学号:");
                    scanf("%d",&update_numb);
                    getchar();
                    sprintf(sql,"update Stu set update_name = \"%s\" where update_numb == %d;",update_name,update_numb);
                    break;
                }
            case 2:
                {
                    printf("请输入您的成绩:");
                    scanf("%lf",&update_score);
                    getchar();
                    sprintf(sql,"update Stu set update_name = \"%s\" where update_score == %lf;",update_name,update_score);
                    break;
                }
            default:
                {
                    printf("输入错误,请重新输入\n");
                }
            }
        }
    case 3:
        {
            printf("请输入您的成绩:");
            scanf("%lf",&update_score);
            getchar();

            printf("请输入您想要限制的条件:\n1.学号\n2.姓名\n");
            scanf("%d",&choose1);
            getchar();
            switch(choose1)
            {
            case 1:
                {
                    printf("请输入您的学号:");
                    scanf("%d",&update_numb);
                    getchar();
                    sprintf(sql,"update Stu set update_score = %lf where update_numb == %d;",update_score,update_numb);
                    break;
                }
            case 2:
                {
                    printf("请输入您的姓名:");
                    scanf("%s",update_name);
                    getchar();
                    sprintf(sql,"update Stu set update_score = %lf where update_name == \"%s\";",update_score,update_name);
                    break;
                }
            default:
                {
                    printf("输入错误,请重新输入\n");
                }
            }
        }
    }
    char *errmsg =NULL;

    if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg) != SQLITE_OK)
    {
        printf("exec error:%s\n",errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    printf("修改成功\n");
    return 0;
}

int callback(void *arg,int cols,char **msgText,char **msgHeader)
{
    if(*((int *)arg) == 0)
    {
        for(int i = 0;i<cols;i++)
        {
            printf("%s\t",*(msgHeader+i));
        }
        printf("\n");
        (*((int *)arg)) = 1;
    }

    for(int i = 0;i<cols;i++)
    {
        printf("%s\t",*(msgText+i));
    }
    printf("\n");
    return 0;

}


int do_search(sqlite3 *ppDb)
{
    char sql[128] = "select * from Stu;";

    char *errmsg = NULL;
    int flag = 0;

    if(sqlite3_exec(ppDb,sql,callback,&flag,&errmsg) != SQLITE_OK)
    {
        printf("exec error:%s\n",errmsg);

        sqlite3_free(errmsg);
        return -1;
    }
    return 0;
}


int do_search_1(sqlite3 *ppDb)
{
    char sql[128] = "select * from Stu;";
    char *errmsg = NULL;
    char **res = NULL;
    int rows = 0;
    int cols = 0;
    if(sqlite3_get_table(ppDb,sql,&res,&rows,&cols,&errmsg) != SQLITE_OK)
    {
        printf("get table error:%s\n",errmsg);
        sqlite3_free(errmsg);
        return -1;
    }

    for(int i = 0;i<rows;i++)
    {
        for(int j = 0;j<cols;j++)
        {
            printf("%s\t",*(res+(i*cols)+j));
        }
        printf("\n");
    }
    sqlite3_free_table(res);
    return 0;


}

int main(int argc, const char *argv[])
{
    sqlite3 *ppDb = NULL;

    if(sqlite3_open("./my.db",&ppDb) != SQLITE_OK)
    {
        fprintf(stderr,"sqlite3_open error:errmsg = %s,errcode = %d\n",sqlite3_errmsg(ppDb),sqlite3_errcode(ppDb));
        return -1;
    }
    printf("sqlite3_open success\n");

    char sql[128] = "create table if not exists Stu(numb int,name char,score double);";
    char *errmsg = NULL;

    if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg) != SQLITE_OK)
    {
        printf("exec error:%s\n",errmsg);
        sqlite3_free(errmsg);
        return -1;
    }
    printf("create table Stu success\n");

    int menu = -1;
    while(1)
    {
        system("clear");
        printf("\t\t==========XXX学生管理系统==========\n");
        printf("\t\t==========1、添加学生信息==========\n");
        printf("\t\t==========2、删除学生信息==========\n");
        printf("\t\t==========3、修改学生信息==========\n");
        printf("\t\t==========4、查找学生信息==========\n");
        printf("\t\t==========0、退出学生信息==========\n");

        printf("请输入功能:");
        scanf("%d",&menu);
        getchar();

        switch(menu)
        {
        case 1:
            {
                do_add(ppDb);
            }
            break;

        case 2:
            {
                do_del(ppDb);
            }
            break;

        case 3:
            {
                do_update(ppDb);
            }
            break;

        case 4:
            {
                do_search(ppDb);
                //do_search_1(ppDb);
            }
            break;
        case 0:goto END;
        default:printf("您输入的功能有误，请重新输入\n");
        }
        printf("请输入任意键回车后清屏\n");
        while(getchar()!='\n');
    }
END:
    sqlite3_close(ppDb);

    return 0;
}