sqllite学习

一、定义

        sqllite属于轻量级的嵌入式数据库，能支持的数据量为2TB，体积小，支持ACID事务

二、c编程

安装

sudo apt update

sudo apt install sqlite3

sudo apt install libsqlite3-dev

pkg-config --libs sqlite3

2.1 接口

官网

Prepared Statement Object

1. 打开数据库

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

        filename为数据库路径名， ppdb为数据库的操作指针

2. 关闭数据库

int sqlite3_close(sqlite3 *db);

   db为数据库的操作指针

成功返回 SQLITE_OK,出错返回错误码

3.执行sql语句

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

        db为数据库操作句柄，sql为具体语句，callback为回调函数，只有sql为select时才会使用，arg为回调函数传参， errmsg为返回消息。

返回值：成功为SQLITE_OK

        在回调函数中有：

typedef int(*sqlite3_callback)
(void *para, int f_num, char **f_value, char **f_name);

para 为传递给回调函数的参数。f_num为记录中包含的字段数目，即列的数目。f_value为每个字段值的指针数组，f_name为每个字段名称的指针数组，成功返回0，失败返回-1。

因为每一个记录都会执行callback，所以每一行需要for循环输出每个列

 查询时也可以不用callback，使用：

sqlite3_get_table(sqlite3 *db, const char *sql, 
char ***resultp, int *nrow, int *ncolumn, char **errmsg);

其中,db为数据库句柄，sql为语句，resultp为指向sql结果的指针，nrow为满足条件的记录的数目，ncolumn为每条记录包含的字段数目，errmsg为错误信息，成功返回0，失败返回错误码

resultp为指向二维数组的指针，如下：

4.具体代码和结果

#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
#define DATABASE "stu.db"
int do_insert(sqlite3 *db){
    int id;
    char name[32] = {0};
    int score;
    char sql[128] = {0};
    char *errormsg;
    printf("input id:\n");
    scanf("%d", &id);
    printf("input name:\n");
    scanf("%s", name);
    printf("input score:\n");
    scanf("%d", &score);

    sprintf(sql, "insert into stu values(%d, '%s', %d);", id, name, score);
    if(sqlite3_exec(db, sql, NULL, NULL, &errormsg) !=SQLITE_OK){
        printf("%s\n", errormsg);
        return -1;
    }else{
        printf("insert done\n");
        return 0;
    }
};

int do_delete(sqlite3 *db){
    int id;
    char name[32] = {0};
    int score;
    char sql[128] = {0};
    char *errormsg;
    printf("delete id:\n");
    scanf("%d", &id);
   

    sprintf(sql, "delete from stu where id=%d;", id);
    if(sqlite3_exec(db, sql, NULL, NULL, &errormsg) !=SQLITE_OK){
        printf("%s\n", errormsg);
        return -1;
    }else{
        printf("delete done\n");
        return 0;
    }
};

int db_update(sqlite3 *db){
    int id;
    char name[32] = {0};
    int score;
    char sql[128] = {0};
    char *errormsg;
    printf("update id:\n");
    scanf("%d", &id);
    printf("update name:\n");
    scanf("%s", name);
    printf("update score:\n");
    scanf("%d", &score);

    sprintf(sql, "update stu set score = %d where id=%d;", score,id);
    if(sqlite3_exec(db, sql, NULL, NULL, &errormsg) !=SQLITE_OK){
        printf("%s\n", errormsg);
        return -1;
    }else{
        printf("update done\n");
        return 0;
    }
};

// 注意select要有回调函数和具体参数，每找到一个记录都会执行一次回调函数
int callback(void *para, int f_num, char **f_value, char **f_name){
    for(int i = 0; i<f_num;++i){
        printf("%s\n", f_value[i]);
    }
    return 0;
}
#if 0
int do_query(sqlite3 *db){
    int id;
    
    char *errormsg;
    printf("query id:\n");
    scanf("%d", &id);
    char sql[128] = {0};
    sprintf(sql, "select * from stu where id=%d;",id);
    // NULL对于callback的para
    if(sqlite3_exec(db, sql, callback, NULL, &errormsg) !=SQLITE_OK){
        printf("%s\n", errormsg);
        return -1;
    }else{
        printf("query done\n");
        return 0;
    }
};
#else
int do_query(sqlite3 *db){
    int id;
    char **resultp;
    char *errormsg;
    int nrow;
    int ncolumn;
    printf("query id:\n");
    scanf("%d", &id);
    char sql[128] = {0};
    sprintf(sql, "select * from stu where id=%d;",id);
    // NULL对于callback的para
    if(sqlite3_get_table(db, sql, &resultp, &nrow, &ncolumn, &errormsg) !=SQLITE_OK){
        printf("%s\n", errormsg);
        return -1;
    }else{
        printf("query done\n");     
    }

    for(int i = 0;i<nrow;++i){
        for(int j = 0; j<ncolumn;++j){
            // +1是因为把表头跳过
            printf("%s\t", resultp[(i + 1) * ncolumn + j]);
        }
        printf("\n");
    }
};
#endif
int main(int argc, void *argv[]){
    sqlite3 *db;
    char *errmsg;
    if(sqlite3_open(DATABASE, &db) != SQLITE_OK){
        // 返回错误信息     
        printf("%s\n", sqlite3_errmsg(db));
        return -1;
    }else{
        printf("open DATABASE success\n");
    }
    char *sql = "create table stu (id Integer, name char, score Integer);";
    if(sqlite3_exec(db, sql, NULL, NULL, &errmsg)!=SQLITE_OK){
        printf("%s\n", errmsg);
    }else{
        printf("create table success\n");
    }
    int cmd;
    while(1){
        printf("***************************");
        printf("1:insert,2:delete,3:query,4:update,5:quit\n");
        scanf("%d",&cmd);
        getchar();
        switch(cmd){
            case 1:
                do_insert(db);
                break;
            case 2:
                do_delete(db);
                break;
            case 3:
                do_query(db);
                break;
            case 4:
                db_update(db);
                break;
            case 5:
                break;
            default:
                printf("error cmd\n");
                return -1;
        }
    }
}

gcc -o mysqllite mysqllite.c -lsqlite3