网络编程day7

 sqlite3 数据库语句

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <unistd.h>
#include <fcntl.h>
#include <sqlite3.h>

int do_insert(sqlite3 *db);
int do_delete(sqlite3 *db);
int do_update(sqlite3 *db);
int main(int argc, const char *argv[])
{
    // 如果库函数不存在， 则创建并打开
    // 如果库函数存在，则直接打开
    sqlite3 *db = NULL;
    if (sqlite3_open("./my.db", &db) != SQLITE_OK)
    {
        fprintf(stderr, "sqlite3_open : %s errcode : %d\n", sqlite3_errmsg(db), sqlite3_errcode(db));
        return -1;
    }
    printf("sqlite3_open success\n");
    // 创建sql语句
    char sql[128] = "create table if not exists mystu (id int primary key, name char, scoer float)";
    // 创建一个表格
    char *errmsg = NULL;
    if (sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        fprintf(stderr, "sqlite3_exec : %s __%d__\n", errmsg, __LINE__);
        return -1;
    }
    char c = 0;
    while (1)
    {
        system("clear");
        printf("*****************************\n");
        printf("*************1.增************\n");
        printf("*************2.删************\n");
        printf("*************3.改************\n");
        printf("*************4.查************\n");
        printf("************0.退出***********\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_find()
            break;
        case '0':
            goto END;
            // break;
        default:
            printf("没有此功能,请重新输入>>>");
            continue;
        }
        printf("请输入任意键清屏>>>");
        while (getchar() != 10)
            ;
    }
END:
    // 关闭数据库
    if (sqlite3_close(db) != SQLITE_OK)
    {
        fprintf(stderr, "sqlite3_close : %s errcode : %d\n", sqlite3_errmsg(db), sqlite3_errcode(db));
        return -1;
    }
    return 0;
}

int do_insert(sqlite3 *db)
{
    int id = -1;
    char buf[32] = "";
    float scoer = -1;
    char buf1[128] = "";
    char *errmsg = NULL;

    printf("请输入要插入的id>>>");
    scanf("%d", &id);
    getchar();
    printf("请输入要插入的name>>>");
    scanf("%s", buf);
    getchar();
    printf("请输入要插入的scoer>>>");
    scanf("%f", &scoer);
    getchar();

    // sprintf (buf1, "%s%c%d%c%c%s%c%c%.2f%c", "insert into mystu values ", '(', id, ',', '"', buf, '"', ',', scoer, ')');
    sprintf(buf1, "insert into mystu values (%d,'%s',%.2f)", id, buf, scoer);

    if (sqlite3_exec(db, buf1, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        fprintf(stderr, "sqlite3_exec : %s __%d__\n", errmsg, __LINE__);
        return -1;
    }
}

int do_delete(sqlite3 *db)
{
    int id = -1;
    char buf[32] = "";
    float scoer = -1;
    char buf1[128] = "";
    char *errmsg = NULL;

    printf("请输入要删除的行id>>>");
    scanf("%d", &id);
    getchar();

    // sprintf (buf1, "%s%c%d%c%c%s%c%c%.2f%c", "insert into mystu values ", '(', id, ',', '"', buf, '"', ',', scoer, ')');
    sprintf(buf1, "delete from mystu where id = %d", id);

    if (sqlite3_exec(db, buf1, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        fprintf(stderr, "sqlite3_exec : %s __%d__\n", errmsg, __LINE__);
        return -1;
    }
}

int do_update(sqlite3 *db)
{
    char c = 0;
    int id1 = -1;
    int id2 = -1;
    char buf1[128] = "";
    char *errmsg = NULL;
    char buf[32] = "";
    float scoer = -1;

    while (1)
    {
        system("clear");
        printf("*****************************\n");
        printf("************1.更改id*********\n");
        printf("************2.更改name*******\n");
        printf("************3.更改scoer******\n");
        printf("************0.退出***********\n");
        printf("*****************************\n");
        printf("请输入要执行的功能>>>");
        c = getchar();
        while (getchar() != 10)
            ;
        switch (c)
        {
        case '1':
        {

            printf("请输入要修改的id>>>");
            scanf("%d", &id1);
            getchar();
            printf("请输入修改后的id>>>");
            scanf("%d", &id2);
            getchar();

            bzero (buf1, sizeof(buf));
            sprintf(buf1, "update mystu set id=%d where id=%d;", id2, id1);

            if (sqlite3_exec(db, buf1, NULL, NULL, &errmsg) != SQLITE_OK)
            {
                fprintf(stderr, "sqlite3_exec : %s __%d__\n", errmsg, __LINE__);
                return -1;
            }
            break;
        }
        case '2':

            printf("请输入name所在的id号>>>");
            scanf("%d", &id1);
            getchar();
            printf("请输入修改后的name>>>");
            scanf("%s", buf);
            getchar();

            bzero (buf1, sizeof(buf));
            sprintf(buf1, "update mystu set name='%s' where id = %d;", buf, id1);

            if (sqlite3_exec(db, buf1, NULL, NULL, &errmsg) != SQLITE_OK)
            {
                fprintf(stderr, "sqlite3_exec : %s __%d__\n", errmsg, __LINE__);
                return -1;
            }
            break;
        case '3':

            printf("请输入scoer所在的id号>>>");
            scanf("%d", &id1);
            getchar();
            printf("请输入修改后的scoer>>>");
            scanf("%f", &scoer);
            getchar();

            bzero (buf1, sizeof(buf));
            sprintf(buf1, "update mystu set scoer=%.2f where id = %d;", scoer, id1);

            if (sqlite3_exec(db, buf1, NULL, NULL, &errmsg) != SQLITE_OK)
            {
                fprintf(stderr, "sqlite3_exec : %s __%d__\n", errmsg, __LINE__);
                return -1;
            }
            break;
        case '0':
            return 0;
        default:
            printf("没有此功能,请重新输入\n");
            printf("请按任意键清屏");
            while (getchar() != 10)
                ;
        }
        printf("请按任意键清屏>>>");
        while (getchar() != 10)
            ;
    }
}