网络编程DAY 6（Sqlite3）

代码：

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

void db_insert(void);
void db_delete(void);
void db_update(void);
void db_select(void);
int callback(void *arg, int columns, char** column_text, char** column_name)
{
    // void *arg：          sqlite3_exec函数的第四个参数;
    // int columns：        查询结果的列数;
    // char** column_text： 指向表格中记录的内容;记录存储在指针数组中;
    // char** column_name： 指向表格的列名；列名存储在指针数组中;
    int count = *((int *)arg);
    if(0 == count)
    {
        for(int i = 0; i < columns; i++)
        {
            printf("%s\t", column_name[i]);
        }
        printf("\n========================\n");
    }
    for(int i = 0; i < columns; i++)
    {
        printf("%s\t", column_text[i]);
    }
    printf("\n");

    *((int *)arg) = count+1;
    return 0;
}

sqlite3 *db = NULL;
char sql[128] = "";
char *errmsg = NULL;

int main(int argc, char const *argv[])
{
    /*创建数据库*/
    if(sqlite3_open("./dict.db", &db) != SQLITE_OK)
    {
        fprintf(stderr, "sqlite3_open::%d::%s\n", sqlite3_errcode(db), sqlite3_errmsg(db));
        return -1;
    }
    /*创建表格*/
    strcpy(sql, "create table if not exists dict (id int primary key, english char, chinese char);");
    if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        fprintf(stderr, "sqlite3_exec::%d::%s __%d__\n", sqlite3_errcode(db), sqlite3_errmsg(db), __LINE__);
        return -1; 
    }

    int opcode = -1;
    while(1)
    {
        printf("1::增\n");
        printf("2::删\n");
        printf("3::改\n");
        printf("4::查\n");
        printf("9::退出\n");
        printf("请输入操作码::");
        scanf("%d", &opcode);getchar();
        switch(opcode)
        {
            case 1: /*增*/db_insert(); break;
            case 2: /*删*/db_delete(); break;
            case 3: /*改*/db_update(); break;
            case 4: /*查*/db_select(); break;
            case 9: /*退出*/goto END;
            default :  break;
        }
        printf("按任意键继续运行::");fflush(stdout);
        while(getchar() != 10);
        system("clear");
    }

END:
    /*删除数据表*/
    strcpy(sql, "DROP TABLE dict;");
    if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        fprintf(stderr, "sqlite3_exec::%d::%s __%d__\n", sqlite3_errcode(db), sqlite3_errmsg(db), __LINE__);
        return -1; 
    }
    /*关闭数据库*/
    if(sqlite3_close(db) != SQLITE_OK)
    {
        fprintf(stderr, "sqlite3_close::%d::%s\n", sqlite3_errcode(db), sqlite3_errmsg(db));
        return -1;
    }
    printf("系统退出...\n");
    return 0;
}

void db_insert(void)
{
    bzero(sql, sizeof(sql));
    int data1 = -1; char data2[32] = ""; char data3[32] = "";
    printf("示例::insert into 表名[dict] values(数据1[int], 数据2[char], 数据3[char]);\n");
    printf("请输入插入SQL语句的参数::\n");
    printf("数据1[int]>>>");scanf("%d", &data1);getchar();
    printf("数据2[char]>>>");scanf("%s", data2);getchar();
    printf("数据3[char]>>>");scanf("%s", data3);getchar();
    sprintf(sql, "INSERT INTO dict VALUES (%d, \"%s\", \"%s\");", data1, data2, data3);
    printf("您生成的sql语句::%s\n", sql);

    if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        fprintf(stderr, "sqlite3_exec::%d::%s __%d__\n", sqlite3_errcode(db), sqlite3_errmsg(db), __LINE__);
        return ; 
    }
    return ;
}
void db_delete(void)
{
    bzero(sql, sizeof(sql));
    char data1[32] = "";
    printf("示例::delete from 表名 where 字段=数值;\n");
    printf("请输入删除SQL语句的参数::\n");
    printf("where[字段=数值]>>>");scanf("%s", data1);getchar();
    sprintf(sql, "DELETE FROM dict WHERE %s;", data1);
    printf("您生成的sql语句::%s\n", sql);

    if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        fprintf(stderr, "sqlite3_exec::%d::%s __%d__\n", sqlite3_errcode(db), sqlite3_errmsg(db), __LINE__);
        return ; 
    }
    return ;
}
void db_update(void)
{
    bzero(sql, sizeof(sql));
    char data1[32] = ""; char data2[32] = "";
    printf("示例::update 表名 set 字段名=数值 where 字段=数值;\n");
    printf("请输入修改SQL语句的参数::\n");
    printf("set[字段名=数值]>>>");scanf("%s", data1);getchar();
    printf("where[字段=数值]>>>");scanf("%s", data2);getchar();
    sprintf(sql, "UPDATE dict SET %s WHERE %s;", data1, data2);
    printf("您生成的sql语句::%s\n", sql);

    if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
    {
        fprintf(stderr, "sqlite3_exec::%d::%s __%d__\n", sqlite3_errcode(db), sqlite3_errmsg(db), __LINE__);
        return ; 
    }
    return ;
}
void db_select(void)
{
    char data1[32] = ""; char data2[32] = "";
    printf("示例1::select * from 表名;\n");
    printf("示例2::select * from 表名 where 限制条件;\n");
    printf("示例3::select 字段名1, 字段名2 from 表名 where 限制条件;\n");
    printf("请输入查询SQL语句的参数::\n");
    printf("字段名1, 字段名2, ...[缺省为*]>>>");scanf("%s", data1);getchar();
    printf("限制条件[缺省为空]>>>");fgets(data2, sizeof(data2), stdin);
    if(0 == strcmp(data2, "\n"))
    {
        sprintf(sql, "SELECT %s FROM dict;", data1);
    }
    else
    {
        sprintf(sql, "SELECT %s FROM dict WHERE %s;", data1, data2);
    }
    printf("您生成的sql语句::%s\n", sql);

    int count = 0;
    if(sqlite3_exec(db, sql, callback, &count, &errmsg) != SQLITE_OK)
    {
        fprintf(stderr, "sqlite3_exec::%d::%s __%d__\n", sqlite3_errcode(db), sqlite3_errmsg(db), __LINE__);
        return ; 
    }
    return ;
}