数据库学习

C语言实现数据的增删改查

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

void show_table(sqlite3 *db, char *zErrMsg);
void insert_data(sqlite3 *db, char *zErrMsg);
void update_data(sqlite3 *db, char *zErrMsg);
void delete_data(sqlite3 *db, char *zErrMsg);
void union_find(sqlite3 *db, char *zErrMsg);
static int callback(void *data, int argc, char **argv, char **azColName);


int main(int argc, char **argv)
{
    sqlite3 *db;
    char *zErrMsg = 0;
    int rc;

    sqlite3_open("/home/sumu/test.db", &db);
   
    int flag = 0;
    while(1)
    {
        printf("1.遍历数据表\n");
        printf("2.插入数据\n");
        printf("3.更新数据\n");
        printf("4.删除数据\n");
        printf("5.联合查询\n");
        printf("6.退出\n");
        printf("请输入选项：");
        scanf("%d", &flag);
        getchar();
        switch(flag)
        {
            case 1:
                show_table(db, zErrMsg);
                break;
            case 2:
                insert_data(db, zErrMsg);
                break;
            case 3:
                update_data(db, zErrMsg);
                break;
            case 4:
                delete_data(db, zErrMsg);
                break;
            case 5:
                union_find(db, zErrMsg);
                break;
            case 6:
                printf("退出程序！\n");
                sqlite3_close(db);
                exit(0);
            default:
                printf("输入错误，请重新输入！\n");
                break;
        }
    }
    

    sqlite3_close(db);
}

void show_table(sqlite3 *db, char *zErrMsg)
{
    char *sql = (char *)malloc(1024);
    char *data = (char *)malloc(1024);
    char *table = (char *)malloc(1024);
    char *wdata = (char *)malloc(1024);
    printf("select data(%%s) from table(%%s) where (%%s)\n");

    printf("输入表名：\n");
    fgets(table, 1024, stdin);
    table[strcspn(table, "\n")] = '\0';//去掉最后的回车

    printf("输入你要查询的数据名：[多个数据用逗号分隔](*:表示所有数据)\n");
    fgets(data, 1024, stdin);
    data[strcspn(data, "\n")] = '\0';//去掉最后的回车
    if(strncmp(data, "*",1) == 0)
    {
        sprintf(sql, "select * from %s", table);
    }
    else
    {
        printf("输入条件：[列名 = 值](输入null为不添加条件)\n");
        fgets(wdata, 1024, stdin);
        wdata[strcspn(wdata, "\n")] = '\0';//去掉最后的回车
        if(strcmp(wdata, "null") == 0)
        {
            sprintf(sql, "select %s from %s", data ,table);
        }
        else
        {
            sprintf(sql, "select %s from %s where %s", data, table,wdata);
        }
    }
    printf("sql语句:%s\n", sql);
    int ret = sqlite3_exec(db, sql, callback, (void*)"查询数据", &zErrMsg);
    if( ret )
    {
        fprintf(stderr, "查询数据失败: %s\n", sqlite3_errmsg(db));
    }
    free(sql);free(data);free(table);free(wdata);
}

void insert_data(sqlite3 *db, char *zErrMsg)
{
    char *sql = (char *)malloc(1024);
    char *table = (char *)malloc(1024);
    char *data = (char *)malloc(1024);
    printf("insert into table(%%s) values(%%s)\n");
    printf("输入表名：\n");
    fgets(table, 1024, stdin);
    table[strcspn(table, "\n")] = '\0';//去掉最后的回车
    for (char *p = table; *p != '\0'; p++) {
        *p = tolower(*p);
    }
    if(strcmp(table, "student") == 0)
    {
        printf("id(int),name(varchar),age(float),sex(varchar),score(float)\n");
        printf("值(用逗号分隔):\n");
        fgets(data, 1024, stdin);
        data[strcspn(data, "\n")] = '\0';//去掉最后的回车
        sprintf(sql, "insert into %s(id,name,age,sex,score) values(%s)", table, data);
    }
    else if(strcmp(table, "teacher") == 0)
    {
        printf("id(int),name(varchar),age(float),sex(varchar),salary(float)\n");
        printf("值(用逗号分隔):\n");
        fgets(data, 1024, stdin);
        data[strcspn(data, "\n")] = '\0';//去掉最后的回车
        sprintf(sql, "insert into %s(id,name,age,sex,salary) values(%s)", table, data);
    }
    int ret = sqlite3_exec(db, sql, NULL, 0, &zErrMsg);
    if( ret )
    {
        fprintf(stderr, "查询数据失败: %s\n", sqlite3_errmsg(db));
    }
    free(sql);free(data);free(table);
}

void update_data(sqlite3 *db, char *zErrMsg)
{
    char *sql = (char *)malloc(1024);
    char *table = (char *)malloc(1024);
    char *wdata = (char *)malloc(1024);
    char *wdata2 = (char *)malloc(1024);
    printf("update table(%%s) set (%%s) where (%%s)\n");
    printf("输入表名：\n");
    fgets(table, 1024, stdin);
    table[strcspn(table, "\n")] = '\0';//去掉最后的回车
    for (char *p = table; *p != '\0'; p++) {
        *p = tolower(*p);
    }
    if(strcmp(table, "student") == 0)
    {
        printf("id(int),name(varchar),age(float),sex(varchar),score(float)\n");
    }
    else if(strcmp(table, "teacher") == 0)
    {
        printf("id(int),name(varchar),age(float),sex(varchar),salary(float)\n");
    }
    printf("输入要修改的列名和值(列名 = 值):\n");
    fgets(wdata, 1024, stdin);
    wdata[strcspn(wdata, "\n")] = '\0';//去掉最后的回车
    printf("wdata:%s\n", wdata);

    printf("输入条件：[列名 = 值]\n");
    fgets(wdata2, 1024, stdin);
    wdata2[strcspn(wdata2, "\n")] = '\0';//去掉最后的回车
   
    sprintf(sql, "update %s set %s where %s", table, wdata, wdata2);
    printf("sql语句:%s\n", sql);

    int ret = sqlite3_exec(db, sql, NULL, 0, &zErrMsg);
    if( ret )
    {
        fprintf(stderr, "更新数据失败: %s\n", sqlite3_errmsg(db));
    }
    free(sql);free(table);free(wdata);free(wdata2);
}

void delete_data(sqlite3 *db, char *zErrMsg)
{
    char *sql = (char *)malloc(1024);
    char *table = (char *)malloc(1024);
    char *wdata = (char *)malloc(1024);
    printf("delete from table(%%s) where (%%s)\n");
    printf("输入表名：\n");
    fgets(table, 1024, stdin);
    table[strcspn(table, "\n")] = '\0';//去掉最后的回车
    for (char *p = table; *p != '\0'; p++) {
        *p = tolower(*p);
    }
     if(strcmp(table, "student") == 0)
    {
        printf("id(int),name(varchar),age(float),sex(varchar),score(float)\n");
    }
    else if(strcmp(table, "teacher") == 0)
    {
        printf("id(int),name(varchar),age(float),sex(varchar),salary(float)\n");
    }
    printf("输入条件：[列名 = 值](输入null为删除所有数据)\n");
    fgets(wdata, 1024, stdin);
    wdata[strcspn(wdata, "\n")] = '\0';//去掉最后的回车
    if(strcmp(wdata, "null") == 0)
    {
        sprintf(sql, "delete from %s", table);
    }
    else
    {
        sprintf(sql, "delete from %s where %s", table, wdata);
    }
    printf("sql语句:%s\n", sql);

    int ret = sqlite3_exec(db, sql, NULL, 0, &zErrMsg);
    if( ret )
    {
        fprintf(stderr, "删除数据失败: %s\n", sqlite3_errmsg(db));
    }
    free(sql);free(table);free(wdata);
}

void union_find(sqlite3 *db, char *zErrMsg)
{
    char *sql = (char *)malloc(1024);
    char *table1 = (char *)malloc(1024);
    char *table2 = (char *)malloc(1024);
    char *data1 = (char *)malloc(1024);
    char *data2 = (char *)malloc(1024);
    printf("select data(%%s) from table(%%s) where (%%s)\n");
    printf("输入表名1:\n");
    fgets(table1, 1024, stdin);
    table1[strcspn(table1, "\n")] = '\0';//去掉最后的回车
    for (char *p = table1; *p != '\0'; p++) {
        *p = tolower(*p);
    }
    printf("输入表名2：\n");
    fgets(table2, 1024, stdin);
    table2[strcspn(table2, "\n")] = '\0';//去掉最后的回车
    for (char *p = table2; *p != '\0'; p++) {
        *p = tolower(*p);
    }
    printf("输入条件1：[列名 = 值]\n");
    fgets(data1, 1024, stdin);
    data1[strcspn(data1, "\n")] = '\0';//去掉最后的回车
    printf("输入条件2：[列名 = 值]\n");
    fgets(data2, 1024, stdin);
    data2[strcspn(data2, "\n")] = '\0';//去掉最后的回车
    sprintf(sql, "select * from %s where %s union select * from %s where %s", table1, data1, table2, data2);
    printf("sql语句:%s\n", sql);
    int ret = sqlite3_exec(db, sql, callback, (void*)"查询数据", &zErrMsg);
    if( ret )
    {
        fprintf(stderr, "查询数据失败: %s\n", sqlite3_errmsg(db));
    }
    free(sql);free(table1);free(table2);free(data1);free(data2);
}


static int callback(void *data, int argc, char **argv, char **azColName)
{
   int i;
   fprintf(stderr, "%s:\n", (const char*)data);
   for(i=0; i<argc; i++){
      printf("%s = %s\t", azColName[i], argv[i] ? argv[i] : "NULL");
      //azColName是列名，argv是每一行的列值，argc是列数
      //循环打印每一行的列名和值
   }
   printf("\n");
   return 0;
}