数据库的基础使用

1.

#include <head.h>

int flag = 0;
void inquire(sqlite3* db);
int callBack(void* arg,int ncolumn,char** f_value,char** f_name);
int main(int argc,const char * argv[])
{   
    
    sqlite3* db;
    if((sqlite3_open("./serr.db",&db)) != 0)
    {
        printf("sqlite3_open error %d\n",sqlite3_errcode(db));
        return -1;
    }
    printf("sqlites_open success\n");

    //CREATE TABLE if not exists stu1 (id int, name char, score float);
    int a;
    char str[128] ="create table if not exists stu5 (id int,name char,sex char,score float);";
    char *errmsg;
    if((sqlite3_exec(db,str,NULL,NULL,&errmsg)) != 0)
    {
        printf(" __%d__sqlites:%s\n",__LINE__,errmsg);
    }
    printf("open success\n");
    int id = 0;
    int oid = 0;
    char name[20] = "";
    char sex;
    float score;
    while(1)
    {
        system("clear");
        printf("-----------------------------\n");
        printf("-----------1.增--------------\n");
        printf("-----------2.删--------------\n");
        printf("-----------3.改--------------\n");
        printf("-----------4.查--------------\n");
        printf("-----------5.退出------------\n");
        printf("-----------------------------\n");
        printf("请输入>>>");
        scanf(" %d",&a);
        
        switch(a)
        {
            //insert into 表名 values (数据1, 数据2, 数据3);
            case 1:
            memset(str,0,sizeof(str));
            printf("请输入,id,name,sex,score\n");
            scanf("%d %s %c %f",&id,name,&sex,&score);
            sprintf(str,"insert into stu5 values (%d,'%s','%c',%f);",id,name,sex,score);
            if((sqlite3_exec(db,str,NULL,NULL,&errmsg)) != 0)
            {
                printf(" __%d__sqlites:%s\n",__LINE__,errmsg);
            }
                break;
                //DELETE FROM stu WHERE id=1;
            case 2:
            //
            memset(str,0,sizeof(str));
            printf("请输入需要删除的id号>>>>");
            scanf("%d",&id);
            sprintf(str,"DELETE FROM stu5 WHERE id=%d;",id);
            if((sqlite3_exec(db,str,NULL,NULL,&errmsg)) != 0)
            {
                printf(" __%d__sqlites:%s\n",__LINE__,errmsg);
            }
            printf("删除成功\n");
                break;
            case 3:
                // UPDATE stu SET score=60 WHERE id=1;
                memset(str,0,sizeof(str));
                printf("请输入需要修改的id和修过后的id,name,sex,score>>>>");
                scanf("%d %d %s %c %f",&oid,&id,name,&sex,&score);
                sprintf(str,"update stu set id=%d,sex='%c',name='%s',score=%f where id='%d';",id,sex,name,score,oid);
                if((sqlite3_exec(db,str,NULL,NULL,&errmsg)) != 0)
                {
                    printf(" __%d__sqlites:%s\n",__LINE__,errmsg);
                }
                break;
            case 4://
                flag = 0;
                inquire(db);
                break;
            case 5:
            goto END;
                break;
            default:
                printf("输入有误请重新输入\n");
        }
        getchar();
        printf("\n");
        printf("请输入任意字符>>>>\n");
        getchar();

    }
END:
    if((sqlite3_close(db)) !=0)
    {   
        printf("close error %d\n",sqlite3_errcode(db));
        return -1;
    }
    printf("sqlites_close success\n");

    return 0;
}
//
void inquire(sqlite3* db)
{
    char str[128] = "";
    //printf("请输入需要修改的id和修过后的id,name,sex,score>>>>");
    //scanf("%d %d %s %c %f",&oid,&id,name,&sex,&score);
    sprintf(str,"SELECT * FROM stu5");
    if((sqlite3_exec(db,str,callBack,NULL,NULL)) != 0)
    {
        printf(" __%d__sqlites:%d\n",__LINE__,sqlite3_errcode(db));
    }
}
//回调函数
int callBack(void* arg,int ncolumn,char** f_value,char** f_name)
{   
    int i = 0;
    if(0 == flag)
    {
        for(i= 0; i < ncolumn; i++)
        {
            printf("%10s",f_name[i]);
        }
        flag = 1;
    }
    printf("\n");
    for(i= 0; i < ncolumn; i++)
    {
        printf("%10s",f_value[i]);
    }

    return 0;
}