网络编程-sqlite3(增删改)

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

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

    // 2.创建表
    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\n", __LINE__, errmsg);
        return -1;
    }

    char c;
    int id = 0; // id
    char name[20] = "";
    char field[20] = ""; // 字段
    char value[32] = ""; // 数据
    char qualification[32] = ""; // 限制条件
    float score = 0;
    while (1) {
        puts("------------------------");
        puts("---------1.增-----------");
        puts("---------2.删-----------");
        puts("---------3.改-----------");
        puts("---------4.查-----------");
        puts("--------5.退出----------");
        puts("------------------------");
        c = getchar();
        while (getchar() != 10);
        switch (c) {
        case '1': // 增
            printf("请输入增加:id name score\n");
            scanf("%d %s %f", &id, name, &score);
            getchar(); // 吸收\n

            bzero(sql, sizeof(sql));
            sprintf(sql, "insert into stu values (%d, \"%s\", %f);", id, name, score);

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

            break;
        case '2': // 删
            bzero(qualification, sizeof(qualification));
            printf("请输入删除条件:(不加限制添加按回车键)\n");
            getchar(); // 吸收\n
            fgets(qualification, sizeof(qualification), stdin);
            qualification[strlen(qualification) - 1] = '\0';

            bzero(sql, sizeof(sql));
            if (strlen(qualification) == 0) {
                sprintf(sql, "delete from stu;");
            } else {
                sprintf(sql, "delete from stu where %s;", qualification);
            }

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

            break;
        case '3': // 改
            bzero(qualification, sizeof(qualification));
            printf("请输入要修改的: 字段 新值\n");
            scanf("%s %s", field, value);
            getchar(); // 吸收\n
            printf("请输入:限制条件(不加限制添加按回车键)\n");
            fgets(qualification, sizeof(qualification), stdin);
            qualification[strlen(qualification) - 1] = '\0';

            bzero(sql, sizeof(sql));
            if (strcmp(field, "name") == 0) {
                if (strlen(qualification) == 0) {
                    sprintf(sql, "update stu set %s=\"%s\";", field, value);
                } else {
                    sprintf(sql, "update stu set %s=\"%s\" where %s;", field, value, qualification);
                }
            } else {
                if (strlen(qualification) == 0) {
                    sprintf(sql, "update stu set %s=%s;", field, value);
                } else {
                    sprintf(sql, "update stu set %s=%s where %s;", field, value, qualification);
                }
            }

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

            break;
        case '4': // 查

            break;
        case '5': // 退出
            goto END;
            break;

        default:
            puts("输入错误,请重新输入");
        }
        printf("按回车键清屏>>>");
        while (getchar() != 10);
        system("clear");
    }

END:
    // 关闭数据库
    if (sqlite3_close(db) != SQLITE_OK) {
        fprintf(stderr, "sqlite3_open:%s  error_code:%d\n", sqlite3_errmsg(db), sqlite3_errcode(db));
        return -1;
    }
    return 0;
}