SQLite 增删改查

1、增

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

#define ERROR_SUCCESS 0
#define ERROR_FAILURE 1

int main(int argc, char* argv[])
{
    sqlite3 *db;
    char *errMsg = NULL;

    int ret = sqlite3_open("test.db", &db);
    if(SQLITE_OK != ret)
    {
        fprintf(stderr,"%s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return ERROR_FAILURE;
    }
    
    const char *sql = "DROP TABLE IF EXISTS Cars;"
                      "CREATE TABLE Cars(ID INT, MANUFACTURE TEXT NOT NULL, PRICE INT);"
                      "INSERT INTO Cars VALUES (1, 'Audi', 52642);"
                      "INSERT INTO Cars VALUES (2, 'Mercedes', 57127);"
                      "INSERT INTO Cars VALUES (3, 'Skoda', 9000);"
                      "INSERT INTO Cars VALUES (4, 'Volvo', 29000);"
                      "INSERT INTO Cars VALUES (5, 'Bentley', 350000);";


    ret = sqlite3_exec(db, sql, 0, 0, &errMsg);
    if(SQLITE_OK != ret)
    {
        fprintf(stderr, "SQL exec error: %s\n", errMsg);
        sqlite3_free(errMsg);
        sqlite3_close(db);
        return ERROR_FAILURE;
    }
    
    printf("Create TABLE successfully\n");
    sqlite3_close(db);
    return ERROR_SUCCESS;
}

int sqlite3_open(const char *filename, sqlite3 **ppDb);

参数： filename:数据库文件名； ppDb:输出数据库句柄

返回值：SQLITE_OK 表示成功，其余返回值可通过sqlite3_errmsg()获取错误信息

const char *sqlite3_errmsg (sqlite3 *db);

参数：db 数据库指针

返回值：错误信息 例如 "out of memory"

int sqlite3_exec( sqlite3 *db, const char *sql, int (*callback)(void*,int,char**,char**), void *arg, char **errmsg );

参数：db是数据库指针，sql是执行语句， callback是exec的回调函数，arg是用户自定义参数，errmsg是错误信息，需要sqlite3_free()手动释放

返回值：成功返回SQLITE_OK,失败返回其他错误码例如SQLITE_ERROR，SQLITE_BUSY，SQLITE_CONSTRAINT

  

2、删

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

#define ERROR_SUCCESS 0
#define ERROR_FAILURE 1

int main(int argc, char* argv[])
{
    sqlite3 *db;
    char *errMsg = NULL;

    int ret = sqlite3_open("test.db", &db);
    if(SQLITE_OK != ret)
    {
        fprintf(stderr,"%s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return ERROR_FAILURE;
    }
    
    const char *sql = "DELETE from Cars where ID = 4";

    ret = sqlite3_exec(db, sql, 0, 0, &errMsg);
    if(SQLITE_OK != ret)
    {
        fprintf(stderr, "SQL exec error: %s\n", errMsg);
        sqlite3_free(errMsg);
        sqlite3_close(db);
        return ERROR_FAILURE;
    }

    printf("Delete row successfully\n");
    sqlite3_close(db);
    return ERROR_SUCCESS;
}

 

3、改

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

#define ERROR_SUCCESS 0
#define ERROR_FAILURE 1

int main(int argc, char* argv[])
{
    sqlite3 *db;
    char *errMsg = NULL;

    int ret = sqlite3_open("test.db", &db);
    if(SQLITE_OK != ret)
    {
        fprintf(stderr,"%s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return ERROR_FAILURE;
    }
    
    const char *sql = "UPDATE Cars SET Price = 22222 where MANUFACTURE = 'Audi';";

    ret = sqlite3_exec(db, sql, 0, 0, &errMsg);
    if(SQLITE_OK != ret)
    {
        fprintf(stderr, "SQL exec error: %s\n", errMsg);
        sqlite3_free(errMsg);
        sqlite3_close(db);
        return ERROR_FAILURE;
    }

    printf("UPDATE TABLE successfully\n");
    sqlite3_close(db);
    return ERROR_SUCCESS;
}

 

4、查

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

#define ERROR_SUCCESS 0
#define ERROR_FAILURE 1

static int callback(void *NotUsed, int argc, char **argv, char **azColName) 
{
    fprintf(stdout, "%s:\n", (const char *)NotUsed);
    for(int i = 0; i < argc; i++) 
    {
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }

    return ERROR_SUCCESS;
}

int main(int argc, char* argv[])
{
    sqlite3 *db;
    char *errMsg = NULL;

    int ret = sqlite3_open("test.db", &db);
    if(SQLITE_OK != ret)
    {
        fprintf(stderr,"%s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return ERROR_FAILURE;
    }
    
    const char *sql = "SELECT * from Cars where MANUFACTURE = 'Skoda';";

    ret = sqlite3_exec(db, sql, callback, "Callback Function", &errMsg);
    if(SQLITE_OK != ret)
    {
        fprintf(stderr, "SQL exec error: %s\n", errMsg);
        sqlite3_free(errMsg);
        sqlite3_close(db);
        return ERROR_FAILURE;
    }

    printf("Query TABLE successfully\n");
    sqlite3_close(db);
    return ERROR_SUCCESS;
}

回调函数callback()原型：

int callback(void *user_data, int column_count, char **column_values, char **column_names);

参数：

user_data: 是由 sqlite3_exec() 的 arg 参数传递，用于自定义上下文数据；

column_count 当前结果行的列数（即 SELECT 查询的字段数）

column_values 数组指针，存储当前行每列的值（字符串形式），NULL 表示该列值为 NULL

column_names ：数组指针，存储每列的字段名（与 column_values 一一对应）