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<[e-mail]> *                  All rights reserved. * *       Filename:  book.c *    Description:  This file  *                  *        Version:  1.0.0(07/16/2017) *         Author:  TangBin <[e-mail]> *      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;}

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