Linux——C语言调用sqlite3

之前有记录了在linux下的sqlite3的安装以及简单使用，实际上sqlite3是提供了很多C语言的API接口的，接下来就来结合一个实例来学习C语言+sqlite3的使用。

常用的API有：
sqlite3_open()
sqlite3_prepare()
sqlite3_step()
sqlite3_column()
sqlite3_finalize()
sqlite3_close()
sqlite3_exec()
sqlite3_get_table()
sqlite3_reset()
sqlite3_bind()
最常用的也只有其中的几个，所以应该不是很困难，需要的时候在进行查阅即可。

一个简单的书本管理系统：

/*********************************************************************************
 *      Copyright:  (C) 2017 TangBin<tangbinmvp@gmail.com>
 *                  All rights reserved.
 *
 *       Filename:  book.c
 *    Description:  This file 
 *                 
 *        Version:  1.0.0(07/16/2017)
 *         Author:  TangBin <tangbinmvp@gmail.com>
 *      ChangeLog:  1, Release initial version on "07/16/2017 07:27:09 PM"
 *                 
 ********************************************************************************/

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

char *errmsg;

void insert_values(sqlite3 *db)
{
    char *sql;

    char book_name[48];
    int book_num;
    float price;

    printf("输入书号：");
    scanf("%d", &book_num);

    printf("输入书名：");
    scanf("%s", book_name);

    printf("输入价格：");
    scanf("%f", &price);

    sql = sqlite3_mprintf("insert into book values('%d', '%s', '%f')", book_num, book_name, price);

    if(SQLITE_OK != sqlite3_exec(db, sql, NULL, NULL, &errmsg) )
    {
        printf("sqlite3_exec insert  error\n");
        exit(1);
    }
    else
        printf("插入成功！\n");

    return;
}

void del_values(sqlite3 *db)
{
    char *sql;
    int n;

    printf("输入要删除的序号：");
    scanf("%d", &n);

    sql = sqlite3_mprintf("delete from book where number ='%d'", n);

    if(SQLITE_OK != sqlite3_exec(db, sql, NULL, NULL, &errmsg) )
    {        
        printf("sqlite3_exec delete error\n");
        exit(1);
    }
    else
    {
        printf("删除成功！\n");
    }

    return;

}

void update_values(sqlite3 *db)
{
    char *sql;

    char book_name[48];
    int book_num;
    float price;

    printf("书号：");
    scanf("%d", &book_num);

    printf("更新-输入书名：");
    scanf("%s", book_name);

    printf("更新-输入价格：");
    scanf("%f", &price);

    sql = sqlite3_mprintf("update book set book_name = '%s', price = '%f' where book_num = '%d'", book_name, price, book_num);

    if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg) )
    {
        printf("sqlite3_exec update error\n");
        exit(1);
    }
    else
    {
        printf("更新成功！\n");
    }

    return;
}

void display(sqlite3 *db)
{
    char **pResult;
    int row, column, i, j, index;
    char *sql = "select * from book";

    if(SQLITE_OK != sqlite3_get_table(db,sql,&pResult,&row,&column,&errmsg) )
    {
        printf("can not get table\n");
        exit(1);
    }

    index = column;
    for(i=0; i<column; i++)
    {
        printf("%-s\t", pResult[i]);
    }
    printf("\n");
    for(i=0; i<row; i++)
    {
        for(j=0; j<column; j++)
        {
           printf("%-5s    \t", pResult[index++]);
        }
        printf("\n");
    }

    return;
}

void quit(sqlite3 *db)
{
    printf("byebye!\n");
    sqlite3_close(db);
    exit(0);
}

void drop_table(sqlite3 *db)
{
    char *sql = "DROP TABLE IF EXISTS book;";
    if(SQLITE_OK != sqlite3_exec(db, sql, NULL, NULL, &errmsg) )
    {
        printf("drop table error!\n");
        exit(1);
    }
    printf("drop book table!\n");
}
int main()
{
    int n;
    sqlite3 *db;
    if(SQLITE_OK != sqlite3_open("book.db",&db) )
    {
        printf("sqlite3_open error\n");
        exit(1);
    }

    const char *sql = "CREATE TABLE IF NOT EXISTS book\
        (book_num int primary key,\
         book_name text NOT NULL,\
         price real);";
    if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg) )
    {
        printf("表创建失败！\n");
        return 0;
    }

    while(1)
    {
        printf("-- <1> insert values --\n");
        printf("-- <2> delete values --\n");
        printf("-- <3> update values --\n");
        printf("-- <4> display --------\n");
        printf("-- <5> quit -----------\n");
        printf("-- <6> drop book table \n");

        printf("Enter number you want:\n");
        scanf("%d", &n);

        switch(n)
        {
            case 1:
                insert_values(db);
                break;
            case 2:
                del_values(db);
                break;
            case 3:
                update_values(db);
                break;
            case 4:
                display(db);
                break;
            case 5:
                quit(db);
                break;
            case 6:
                drop_table(db);
                break;
        }
    }

    return 0;
}

程序还有一些缺陷，比如更新数据前没有判断表是不是存在等。比较简单，用来熟悉学习一下还是可以的。