C语言操作SQLite

1 下载源代码

采用C语言操作SQLite数据库时需要引入sqlite3库，我们可以在官网下载源代码后编译。

2 C代码-不用回调函数执行SQL语句

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

int main(void){
    sqlite3 *db; //数据库
    
    sqlite3_open("test.db", &db); //打开数据库

    char **pazResult, *pzErrmsg;
    int pnRow, pnColumn;
    if (sqlite3_get_table(db, "select * from test;", &pazResult, &pnRow, &pnColumn,             
                         &pzErrmsg) != 0){
        printf(stderr, "error: %s\n", pzErrmsg);
        sqlite3_free(pzErrmsg);
    }else{
        int i;
        for (i=0; i<pnRow * pnColumn; i += pnColumn){
            printf("%s\t%s\n", pazResult[i], pazResult[i+1]);
        }
    }

    sqlite3_close(db); //关闭数据库

    return EXIT_SUCCESS;
}

 3 C代码-使用回调函数执行SQL语句

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

//每找到一条记录自动执行一次回调函数
int callback(void *para, int f_num, char **f_value, char **f_name){
    int i;
    for (i=0; i<f_num; ++i){
        printf("%s\t", f_value[i]);
    }

    return 0;
}

int main(void){
    sqlite3 *db; //数据库
    
    sqlite3_open("test.db", &db); //打开数据库

    char *error;
    sqlite3_exec(db, "select * from test", callback, NULL, &error);

    sqlite3_close(db); //关闭数据库

    return EXIT_SUCCESS;
}

  4 C代码-创建表

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

int main(int argc, char *argv[]){
    sqlite3 *db; //数据库
    char *errormsg;
    char **result;
    int row, column;
    int ret;
    
    ret = sqlite3_open("NBA.db", &db); //打开数据库
    if (ret != SQLITE_OK){
        printf("open failed!\n");
        return -1;
    }

    ret = sqlite3_exec(db, "CREATE TABLE Lakers(Name TEXT NOT NULL UNIQUE, \
            Height REAL NOT NULL, PointsPerGames REAL NOT NULL);",\
            NULL, NULL, &errormsg);
    if (ret != SQLITE_OK){
        printf("%s\n", errormsg);
        sqlite3_free(errormsg);
    }

    ret = sqlite3_exec(db, "INSERT INTO Lakers (Name, Height, PointsPerGames)\
            VALUES('James', 2.03, 27.8);", NULL, NULL, &errormsg);
    if (ret != SQLITE_OK){
        printf("%s\n", errormsg);
        sqlite3_free(errormsg);
    }

    ret = sqlite3_get_table(db, "SELECT * FROM Lakers;", &result, &row, &column,             
                         &errormsg); 
    if (ret != SQLITE_OK){
        printf("%s\n", errormsg);
        sqlite3_free(errormsg);
    }else{
        printf("row:%d, column:%d", row, column);
        int i = 0;
        for (i=0; i<(row+1)*column; i++){
            printf("%s ", result[i]);
            if ((i+1)%column == 0)
                printf("\n");
        }
    }

    sqlite3_close(db); //关闭数据库

    return 0;
}

5 编译 

gcc filename.c -o filename -lsqlite3