网络编程 day6

sqlite增删改查

main.c

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
void do_insert(sqlite3 *db)
{
    int id;
    char name[64];
    char sql[128];
    float score;
    char *errmsg = NULL;
    printf("请输入要插入的数据:\nid name socre  (空格隔开)\n");
    int flag = scanf("%d %s %f", &id, name, &score);
    while (getchar() != 10)
        ;
    if (flag != 3)
    {
        printf("输入错误!\n");
        return;
    };

    // 拼接sql语句
    sprintf(sql, "insert into stu values (%d,'%s',%f);", id, name, score);
    // fprintf(stderr, "sql=%s\n", sql);
    if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        fprintf(stderr, "line:%d sqlite3_exec:%s errocode:%d\n",
                __LINE__, sqlite3_errmsg(db), sqlite3_errcode(db));
    }
    printf("插入成功!\n");
}
void do_delete(sqlite3 *db)
{
    int id;
    char sql[128];
    char *errmsg = NULL;
    printf("请输入要删除的id\n");
    scanf("%d", &id);
    while (getchar() != 10)
        ;
    // 拼接sql语句
    sprintf(sql, "delete from stu where id = %d;", id);
    // fprintf(stderr, "sql=%s\n", sql);
    if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        fprintf(stderr, "line:%d sqlite3_exec:%s errocode:%d\n",
                __LINE__, sqlite3_errmsg(db), sqlite3_errcode(db));
    }
    printf("删除成功!\n");
}
void do_update(sqlite3 *db)
{
    int update_id;
    int id;
    char name[64];
    char sql[128];
    float score;
    char *errmsg = NULL;
    printf("请输入要修改信息的id号\n");
    scanf("%d", &update_id);
    while (getchar() != 10)
        ;
    printf("请输入新的数据:\nid name socre  (空格隔开)\n");
    int flag = scanf("%d %s %f", &id, name, &score);
    while (getchar() != 10)
        ;
    if (flag != 3)
    {
        printf("输入错误!\n");
        return;
    };
    // 拼接sql语句
    sprintf(sql, "update stu set id=%d,name='%s',score=%f where id=%d", id, name, score, update_id);
    // fprintf(stderr, "sql=%s\n", sql);
    if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        fprintf(stderr, "line:%d sqlite3_exec:%s errocode:%d\n",
                __LINE__, sqlite3_errmsg(db), sqlite3_errcode(db));
    }
    printf("修改成功!\n");
}
int main(int argc, const char *argv[])
{
    sqlite3 *db = NULL;
    // 如果数据库不存在，则创建后打开
    // 如果存在则直接打开
    if (sqlite3_open("./my.db", &db) != SQLITE_OK)
    {
        fprintf(stderr, "sqlite3_open:%s errocode:%d\n",
                sqlite3_errmsg(db), sqlite3_errcode(db));
        return;
    }
    printf("打开数据库成功\n");
    // 创建一个表
    char sql[128] = "create table if not exists stu(id int,name char,score float);";
    char *errmsg = NULL;
    if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        fprintf(stderr, "line:%d sqlite3_exec:%s errocode:%d\n",
                __LINE__, sqlite3_errmsg(db), sqlite3_errcode(db));
    }
    printf("创建表stu成功\n");

    char c = 0;
    while (1)
    {
        system("clear");
        printf("-----------------------\n");
        printf("---------1.增----------\n");
        printf("---------2.删----------\n");
        printf("---------3.改----------\n");
        printf("---------4.查----------\n");
        printf("---------5.退出--------\n");
        printf("-----------------------\n");
        printf("请输入>>>");
        c = getchar();
        while (getchar() != 10)
            ;
        switch (c)
        {
        case '1':
            do_insert(db);
            break;
        case '2':
            do_delete(db);
            break;
        case '3':
            do_update(db);
            break;
        case '4':
            // do_select();
            break;
        case '5':
            goto END;
            break;

        default:
            printf("输入错误请重新输入\n");
            break;
        }
        printf("输入任意字符继续...\n");
        while (getchar() != 10)
            ;
    }
END:
    // 关闭数据库
    if (sqlite3_close(db) != SQLITE_OK)
    {
        fprintf(stderr, "sqlite3_close:%s errocode:%d\n",
                sqlite3_errmsg(db), sqlite3_errcode(db));
        return -1;
    }

    return 0;
}