sqlite 实现增删改查（含代码）

嵌入式一般使用比较小的数据库实现增删改查，sqlite3满足这些要求

常用sql语法

select * from foods where name like 'j%'; --通配符 等价Linux * 并且不再检查j字符的大小写
select * from foods where name glob 'j*';--通配符 Linux 语法 不过会检测j字符的大小写
select * from foods where name glob 'J*';

select id,name from foods where name like '%ac%P%';

select id,name from foods where name like '%ac%P%' and name not like '%sch%';  --筛选且条件


select id from foods order by id desc;  --descending  降序排列
select id from foods order by id ; --ascending 默认升序 也可以加上

select id from foods order by id limit 5; --显示5个
select id from foods order by id limit 5 offset 2; -- 显示五个 不过会先跳过前2个 从第三个开始显示
select id from foods order by id limit 2,5 ; --上面的等价写法

代码实现

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

int do_insert(sqlite3 *db)
{
    char sql[128] = {0};
    int id;
    char name[32] = {0};
    int score;
    char *errmsg;
    printf("input id\n");
    scanf("%d",&id);
    getchar();

    printf("input name\n");
    scanf("%s",name);
    getchar();
    
    printf("input score\n");
    scanf("%d",&score);
    getchar();

    sprintf(sql,"insert into stu values (%d,'%s',%d)",id,name,score);

    if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg)) {
        printf("insert error:%s\n",errmsg);
    }
    else{
        printf("insert done\n");
    }
    return 0;
}
int do_delete(sqlite3 *db)
{
    int id;
    char sql[128] = {0};
    char *errmsg;
    printf("input id\n");
    scanf("%d",&id);
    getchar();


    sprintf(sql,"delete from stu where id=%d",id);

    if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg)) {
        printf("delete error:%s\n",errmsg);
    }
    else{
        printf("delete done\n");
    }
    return 0;


}
int do_update(sqlite3 *db) {
    char sql[128] = {0};
    int id, score;
    char *errmsg;
    printf("input id\n");
    scanf("%d",&id);
    getchar();

    printf("update scoce\n");
    scanf("%d",&score);
    getchar();

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

int do_query(sqlite3 *db)
{
    char sql[128] = {0};
    char *errmsg;
    char **resultp;
    int column,row;
    sprintf(sql,"select * from stu");
    int ret = sqlite3_get_table(db,sql,&resultp,&row,&column,&errmsg);
    if(SQLITE_OK!=ret) {
        printf("sqlite3_get_table error\n");
    }
    else{
        printf("sqlite3_get_table done\n");
    }
    int Index = column;
    for(int i=0;i<row;++i) {
        for(int j=0;j<column;++j) {
            printf("%s = %s ",resultp[j],resultp[Index]);
            ++Index;
        }
        printf("\n");
    }
    return 0;
}

int main()
{
    sqlite3 *db;
    char sql[128] = "0";
    char *errmsg;
    //create database and connect
    int ret = sqlite3_open("test.db",&db);
    if(ret != SQLITE_OK) {
        fprintf(stderr,"can not open database:%s\n",sqlite3_errmsg(db));
        return 0;
    }

    else{
        fprintf(stderr,"can open database sucessful\n");
    }

    memset(sql,0,sizeof(sql));
    sprintf(sql,"create table stu (id Integer, name char, score Integer)");
    printf("sql = %s\n",sql);
    ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if(ret!=SQLITE_OK) {
        printf("%s\n",errmsg);
    }
    else {
        printf("create table sucess!\n");
    }
    int cmd = 0;
    while(1) {
        printf("*************\n");
        printf("please input number\n");
        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:
            do_update(db);
            break;
            case 5:
            printf("quit\n");
            return 0;
            default:
            printf("Error cmd\n");
        }
    }
    

    return 0;
}

 此图为查询时，内容的存储方式