网络编程（day6）——数据库sqlite3

完成数据库的插入记录、删除记录、修改记录。

代码如下：

 

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

void Insert_Record(sqlite3* ppDb); // 插入记录
void Delete_Record(sqlite3* ppDb); // 删除记录
void Update_Record(sqlite3* ppDb); // 修改记录

int main(int argc, char const* argv[])
{
    // 创建并打开数据库
    sqlite3* ppDb = NULL;
    if (sqlite3_open("./my.db", &ppDb) != SQLITE_OK) {
        printf("[line : %d] sqlite3_open error : [errcode: %d] %s\n",
            __LINE__, sqlite3_errcode(ppDb), sqlite3_errmsg(ppDb));
        return -1;
    }
    printf("sqlite_open successful\n");

    // 创建表格
    // 在数据库中怎么写,在C代码中就怎么写
    char sql[128] = "create table if not exists stu(id int primary key,name char,score float);";
    char* errmsg = NULL;
    if (sqlite3_exec(ppDb, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
        printf("[line : %d] create table error : %s\n", __LINE__, errmsg);
        return -1;
    }
    printf("create table stu successful\n");

    int choice;

    while (1) {
        printf("\n---------------------\n");
        printf("-------1. 插入-------\n");
        printf("-------2. 删除-------\n");
        printf("-------3. 修改-------\n");
        printf("-------4. 查询-------\n");
        printf("-------5. 退出-------\n");
        printf("---------------------\n\n");

        printf("请选择你要执行的功能>>> ");

        scanf("%d", &choice);
        switch (choice) {
        case 1:
            Insert_Record(ppDb);
            break;
        case 2:
            Delete_Record(ppDb);
            break;
        case 3:
            Update_Record(ppDb);
            break;
        case 4:
            /* code */
            break;
        case 5:
            exit(EXIT_SUCCESS);
            break;
        default:
            printf("\n输入格式错误!!!\n请在功能1 2 3 4 5中选择!!!\n\n");
            sleep(1);
            break;
        }
    }

    // 释放数据库内存空间
    if (sqlite3_close(ppDb) != SQLITE_OK) {
        printf("[line : %d] sqlite3_close error : [errcode: %d] %s\n",
            __LINE__, sqlite3_errcode(ppDb), sqlite3_errmsg(ppDb));
        return -1;
    }
    printf("sqlite_close successful\n");
    ppDb = NULL;

    return 0;
}

// 插入记录
void Insert_Record(sqlite3* ppDb)
{
    int ID;
    char name[20];
    float score;
    char sql[128];
    char* errmsg = NULL;
    printf("请输入ID值:");
    scanf("%d", &ID);
    printf("请输入姓名:");
    scanf("%s", name);
    printf("请输入分数:");
    scanf("%f", &score);
    sprintf(sql, "INSERT INTO stu VALUES (%d,\"%s\",%f);", ID, name, score);
    if (sqlite3_exec(ppDb, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
        printf("[line : %d] insert record error : %s\n", __LINE__, errmsg);
        return;
    }
    printf("insert record into stu successful\n");
}

// 删除记录
void Delete_Record(sqlite3* ppDb)
{
    char sql[128];
    int ID;
    char* errmsg = NULL;
    printf("请输入删除记录的ID值:");
    scanf("%d", &ID);
    sprintf(sql, "DELETE FROM stu WHERE id=%d", ID);
    if (sqlite3_exec(ppDb, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
        printf("[line : %d] delete record error %s\n", __LINE__, errmsg);
        return;
    }
    printf("delete record from stu successful\n");
}

// 修改记录
void Update_Record(sqlite3* ppDb)
{
    int choice;
    char sql[128];
    int ID;
    char name[20];
    int limit_id;
    float score;
    char* errmsg = NULL;
    printf("请输入修改记录的ID值:");
    scanf("%d", &limit_id);

    printf("\n---------------------\n");
    printf("--------1. ID--------\n");
    printf("-------2. name-------\n");
    printf("-------3. score------\n");
    printf("---------------------\n\n");

    printf("请选择要修改记录的字段>>> ");
    scanf("%d", &choice);
    if (choice == 1) {
        printf("请输入修改后的id:");
        scanf("%d", &ID);
        sprintf(sql, "UPDATE stu SET id=%d WHERE id=%d", ID, limit_id);
    } else if (choice == 2) {
        printf("请输入修改后的name:");
        scanf("%s", name);
        sprintf(sql, "UPDATE stu SET name=\"%s\" WHERE id=%d", name, limit_id);
    } else if (choice == 3) {
        printf("请输入修改后的score:");
        scanf("%f", &score);
        sprintf(sql, "UPDATE stu SET score=%f WHERE id=%d", score, limit_id);
    }

    if (sqlite3_exec(ppDb, sql, NULL, NULL, &errmsg) != SQLITE_OK) {
        printf("[line : %d] update record error: %s\n", __LINE__, errmsg);
        return;
    }
    printf("update record from stu successful\n");
}