数据库增删改查-2024-8-23

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

char buf[20];

int show_TableName(sqlite3 * mydb)
{
    char ** result = NULL;
    int line = 0;
    int column = 0;
    char *errmsg = NULL;

    //---------------查看数据库有哪些表---------------
    if(sqlite3_get_table(mydb,"SELECT name FROM sqlite_master WHERE type='table';",&result,&line,&column,&errmsg) != SQLITE_OK)
    {
        fprintf(stderr,"sqlite3_get_table: %s\n",errmsg);
        return -1;
    }
    for(int i=1;i<(line+1)*column;i++)
    {
        printf("%s\t",result[i]);
        if((i+1)%column == 0)
            putchar(10);
    }

    //---------------判断输入的表名是否存在---------------
    printf("你要操作哪个表（表名）:\n");
    int i;
    while(1)
    {
        bzero(buf,20);
        scanf("%s",buf);
        for(i=1;i<(line+1)*column;i++)
        {
            if(strcmp(result[i],buf) == 0)
                break;
        }
        if(i == (line+1)*column)
            printf("没有你输入的表名，请重新输入\n");
        if(i<(line+1)*column)
            break;
    }
    return 0;
}

//查看数据表
int show_db(sqlite3 * mydb)
{
    char ** result = NULL;
    int line = 0;
    int column = 0;
    char *errmsg = NULL;

    show_TableName(mydb);

    //----------------打印出你选择的数据表------------------
    char buffer[100];
    bzero(buffer,100);
    snprintf(buffer,100,"SELECT * FROM %s;",buf);
    if(sqlite3_get_table(mydb,buffer,&result,&line,&column,&errmsg) != SQLITE_OK)
    {
        fprintf(stderr,"sqlite3_get_table: %s\n",errmsg);
        return -1;
    }
    for(int i=0;i<(line+1)*column;i++)
    {
        printf("%s\t",result[i]);
        if((i+1)%column == 0)
            putchar(10);
    }
    return 0;
}

//添加数据
int add_db(sqlite3 * mydb)
{
    char ** result = NULL;
    int line = 0;
    int column = 0;
    char *errmsg = NULL;

    show_TableName(mydb);

    char buffer[100];
    bzero(buffer,100);
    snprintf(buffer,100,"SELECT * FROM %s;",buf);
    //---------------查看数据库有哪些表---------------
    if(sqlite3_get_table(mydb,buffer,&result,&line,&column,&errmsg) != SQLITE_OK)
    {
        fprintf(stderr,"sqlite3_get_table: %s\n",errmsg);
        return -1;
    }
    printf("请输入对应输入内容（请用逗号,隔开,输入中文用'中文内容 '格式）：\n");
    for(int i=0;i<column;i++)
    {
        printf("%s\t",result[i]);
    }
    putchar(10);

    char buf1[100];
    bzero(buf1,100);
    getchar();
    fgets(buf1,100,stdin);

    bzero(buffer,100);
    snprintf(buffer,100,"INSERT INTO %s values(%s);",buf,buf1);
    if(sqlite3_get_table(mydb,buffer,&result,&line,&column,&errmsg) != SQLITE_OK)
    {
        fprintf(stderr,"sqlite3_get_table: %s\n",errmsg);
        return -1;
    }
    return 0;
}

//修改数据
int change_db(sqlite3 * mydb)
{
    char ** result = NULL;
    int line = 0;
    int column = 0;
    char *errmsg = NULL;

    show_db(mydb);
    printf("你要修改哪个id的数据：\n");
    char id[10];
    scanf("%s",id);


    char buffer[100];
    bzero(buffer,100);
    snprintf(buffer,100,"SELECT * FROM %s;",buf);
    if(sqlite3_get_table(mydb,buffer,&result,&line,&column,&errmsg) != SQLITE_OK)
    {
        fprintf(stderr,"sqlite3_get_table: %s\n",errmsg);
        return -1;
    }
    for(int i=0;i<(line+1)*column;i++)
    {

        if(strcmp(id,result[i])==0 || strcmp("id",result[i]) == 0)
        {
            for(int j =0;j<column;j++)
            {
                printf("%s\t",result[i]);
                i++;
            }
            putchar(10);
            i--;
        }
    }

    printf("请选择你要修改的内容（比如：name = '要修改的内容'）:");
    char buf1[20];
    bzero(buf1,20);
    getchar();
    fgets(buf1,20,stdin);

    bzero(buffer,100);
    snprintf(buffer,100,"UPDATE %s SET %s WHERE id = %s;",buf,buf1,id);
    if(sqlite3_get_table(mydb,buffer,&result,&line,&column,&errmsg) != SQLITE_OK)
    {
        fprintf(stderr,"sqlite3_get_table: %s\n",errmsg);
        return -1;
    }
    printf("修改成功\n");
    return 0;
}

//删除数据
int del_db(sqlite3 * mydb)
{
    char ** result = NULL;
    int line = 0;
    int column = 0;
    char *errmsg = NULL;

    show_db(mydb);
    printf("你要删除哪个id的数据：\n");
    char id[10];
    scanf("%s",id);

    char buffer[100];
    bzero(buffer,100);
    snprintf(buffer,100,"DELETE FROM %s WHERE id = %s;",buf,id);
    if(sqlite3_get_table(mydb,buffer,&result,&line,&column,&errmsg) != SQLITE_OK)
    {
        fprintf(stderr,"sqlite3_get_table: %s\n",errmsg);
        return -1;
    }
    printf("删除成功\n");
    return 0;

}

//阻塞
void block()
{
    while(getchar()!='\n');
    while(getchar()!='\n');
    system("clear");
}

int main(int argc, const char *argv[])
{
    //打开数据库
    sqlite3 * mydb;
    if(sqlite3_open("./STU.db",&mydb) != SQLITE_OK)
    {
        fprintf(stderr,"sqlite3_open:%d %s\n",sqlite3_errcode(mydb),sqlite3_errmsg(mydb));
        return -1;
    }
    printf("sqlite3_open sucess\n");

    while(1)
    {
        printf("**********数据库系统**********\n");
        printf("**********1.查看数据表\n");
        printf("**********2.添加数据\n");
        printf("**********3.修改数据\n");
        printf("**********4.删除数据\n");
        printf("**********5.退出系统\n");
        putchar(10);
        int choose;
        printf("请选择你要操作的选项(1-5):\n");
        scanf("%d",&choose);
        switch(choose)
        {
            //查看数据表
        case 1:
            show_db(mydb);
            block();
            break;
            //添加数据
        case 2:
            add_db(mydb);
            block();
            break;
            //修改数据
        case 3:
            change_db(mydb);
            block();
            break;
            //删除数据
        case 4:
            del_db(mydb);
            block;
            break;
            //退出系统
        case 5:
            return 0;
            break;
        default:
            printf("你的输入有误，请重新输入\n");
            block();
            break;
        }
    }
    return 0;
}