day2-sqlite3下

sqlite3 数据库 C语言 API

int sqlite3_open(const char *filename, /* Database filename (UTF-8) * sqlite3 **ppDb /* OUT: SQLite db handle */

);

功能：打开数据库

参数：filename 数据库名称

ppdb 数据库句柄

返回值：成功为0 SQLITE_OK ,出错 错误码

int sqlite3_close(sqlite3* db);

功能：关闭数据库

参数：

返回值：成功为0 SQLITE_OK ,出错 错误码

const char *sqlite3_errmsg(sqlite3*db);

功能：得到错误信息的描述

int sqlite3_exec(

sqlite3* db, /* An open database */

const char *sql, /* SQL to be evaluated */

int (*callback)(void* arg,int,char**,char**), /* Callback function */

void * arg, /* 1st argument to callback */

char **errmsg /* Error msg written here */

);

功能：执行一条sql语句

参数：db 数据库句柄

sql sql语句

callback 回调函数，只有在查询时，才传参

arg 为回调函数传递参数

errmsg 错误消息

返回值：成功 SQLITE_OK

查询回调函数：

int (*callback)(void* arg,int ncolumns ,char** f_value,char** f_name), /* Callback function */

功能：查询语句执行之后，会回调此函数

参数：arg 接收sqlite3_exec 传递来的参数

ncolumns 列数

f_value　列的值得地址

f_name 列的名称

返回值：0，

int sqlite3_get_table(

sqlite3 *db, /* An open database */

const char *zSql, /* SQL to be evaluated */

char ***pazResult, /* Results of the query */

int *pnRow, /* Number of result rows written here */

int *pnColumn, /* Number of result columns written here */

char **pzErrmsg /* Error msg written here */

);

void sqlite3_free_table(char **result);

代码示例

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

#define DATABASE "stu.db"

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

    //使用sprintf拼接
    sprintf(sql,"insert into stu values(%d,'%s',%d);",id,name,score);
    if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK){
        printf("%s\n",errmsg);
        return -1;
    }else{
        printf("insert done\n");
    }
    return 0;
    
}
int do_delete(sqlite3 *db){
    int id;
    char sql[128] = {};
    char *errmsg;
    printf("Input id:");
    scanf("%d",&id);
    getchar();
    
    //使用sprintf拼接
    sprintf(sql,"delete from stu where id = %d",id);
    if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK){
        printf("%s\n",errmsg);
        return -1;
    }else{
        printf("delete done\n");
    }
    return 0;
    
}

int callback(void *arg,int ncolumns,char **f_value,char ** f_name){
    int i;
    for(i=0;i<ncolumns;i++){
        printf("%-8s",f_value[i]);
    }
    puts("");
    return 0;
}

int do_query(sqlite3 *db){
    char sql[128] = {};
    char *errmsg;
    
    //使用sprintf拼接
    strcpy(sql,"select * from stu;");
    if(sqlite3_exec(db,sql,callback,NULL,&errmsg)!=SQLITE_OK){
        printf("%s\n",errmsg);
        return -1;
    }else{
        printf("query done\n");
    }
    return 0;
    
}

int do_query1(sqlite3 * db){    
    char sql[128] = {};
    char *errmsg;
    char **pazResult;
    int nrow;
    int ncolumns;
    
    //使用sprintf拼接
    strcpy(sql,"select * from stu;");
    if(sqlite3_get_table(db,sql,&pazResult,&nrow,&ncolumns,&errmsg)!=SQLITE_OK){
        printf("%s\n",errmsg);
        return -1;
    }else{
        printf("query done\n");
    }

    int i = 0;
    int j = 0;
    int index = ncolumns;
    for(;i<ncolumns;i++){
        printf("%-8s",pazResult[i]);
    }
    puts("");
    for(i=0;i<nrow;i++){
        for(j=0;j<ncolumns;j++){
            printf("%-8s",pazResult[index++]);
        }
        puts("");
    }

    return 0;
}

int do_update(sqlite3 *db){
    int id;
    char name[32] = {};
    int score;
    char sql[128] = {};
    char *errmsg;
    printf("Input id:");
    scanf("%d",&id);
    getchar();
    
    printf("Input name:");
    scanf("%s",name);
    getchar();
    
    printf("Input score:");
    scanf("%d",&score);
    getchar();

    //使用sprintf拼接
    sprintf(sql,"update stu set name = '%s',score = %d where id = %d",name,score,id);
    if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK){
        printf("%s\n",errmsg);
        return -1;
    }else{
        printf("update done\n");
    }
    return 0;    
}

int main(int argc, char *argv[])
{
    sqlite3 *db;
    char * errmsg;
    if(sqlite3_open(DATABASE,&db)!= SQLITE_OK){
        printf("%s",sqlite3_errmsg(db));
        return -1;
    }
    else{
        printf("Open DATABASE success.\n");
    }

    //创建一张数据表 
    if(sqlite3_exec(db,"create table stu(id Integer,name char,score Integer);",NULL,NULL,&errmsg)!=SQLITE_OK){
        printf("%s\n",errmsg);
    }
    else{
        printf("create table success\n");
    }
    int cmd;
    while(1){
        printf("---------------------------------------------\n");
        printf("1.insert  2.delete  3.query  4.update  5.quit\n");
        printf("---------------------------------------------\n");
        printf("input cmd: ");
        scanf("%d",&cmd);
        getchar();
        switch(cmd){
            case 1:
                do_insert(db);
                break;
            case 2:
                do_delete(db);
                break;
            case 3:
                do_query1(db);
                break;
            case 4:
                do_update(db);
                break;
            case 5:
                sqlite3_close(db);
                exit(0);
            default:
                printf("err cmd\n");
        }

    }


    return 0;
}