sqlite 表操作 创建 增 删 查找

（一）环境
下载：https://www.sqlite.org/download.html Pre-release Snapshots
解压：tar -xzf  sqlite-autoconf-3260000.tar.gz
编译：进主目录 sqlite-autoconf-3260000 编译
  linux/Unix:  . /configure    make
  Windows:   nmake /f Makefile.msc

其他： 参考文档留存
https://sqlite.org/inmemorydb.html
http://www.runoob.com/sqlite/sqlite-tutorial.html

（二）参考代码

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

// SQLITE_ABORT 时候 需要释放 zErrMsg
/*if( zErrMsg ) { printf("zErrMsg=[%s]\n",zErrMsg); sqlite3_free(zErrMsg); zErrMsg = NULL; };*/
#define SQL_POST_PEOCESS(ret,TipMsg,zErrMsg,azResult)\
	do{\
	if( ret != SQLITE_OK ) fprintf(stderr, "ret=[%d] %s ERROR: %s\n", ret, TipMsg, sqlite3_errmsg(db));\
	if( zErrMsg ) {sqlite3_free(zErrMsg); zErrMsg = NULL; };\
	if( azResult ) { sqlite3_free_table(azResult); azResult = NULL; };\
	}while(0);

#define DATABASE_NAME "testDB:memory:" //内存数据库

int main(int argc, char**argv)
{
    sqlite3 *db=NULL;
    int ret;
    int i=0;
    int nrow=0;
    int ncolumn = 0;
    char *zErrMsg =NULL;
    char **azResult=NULL; //二维数组存放结果
    char *sql=NULL;
    
    // 打开数据库
    ret = sqlite3_open(DATABASE_NAME,&db);
    if( ret != SQLITE_OK )
    {
      //sqlite3_errmsg(db)用以获得数据库打开错误码的英文描述
      fprintf(stderr, "open database error: %s\n", sqlite3_errmsg(db));
      sqlite3_close(db);
      exit(1);
    }

    // 如果表存在，删除
    sql = "select * from sqlite_master where type = 'table' and name = 'TestTable' ;";
    ret = sqlite3_get_table( db , sql , &azResult , &nrow , &ncolumn , &zErrMsg );
    SQL_POST_PEOCESS(ret, "CHECK TABLE EXIST", zErrMsg, azResult);

    // 表存在
    if(nrow == 1)
    {
       printf("the table already exist, del...:\n");
       sql = "DROP TABLE TestTable ;";
       ret = sqlite3_exec(db,sql,NULL,NULL,&zErrMsg);
       SQL_POST_PEOCESS(ret, "DROP TABLE", zErrMsg, azResult);
    }

    // 创建
    sql = " CREATE TABLE TestTable(\
       ID INTEDER PRIMARY KEY,\
       column_1 INTEGER,\
       column_2 VARCHAR(24),\
       column_3 VARCHAR(16),\
       column_4 REAL\
       );" ;
    ret = sqlite3_exec(db,sql,NULL,NULL,&zErrMsg);
    SQL_POST_PEOCESS(ret, "CREATE TABLE", zErrMsg, azResult);

    //插入
    sql ="INSERT INTO 'TestTable' VALUES(1,100,\"string test1\",2012/05/05,88.8);";
    ret = sqlite3_exec(db,sql,NULL,NULL,&zErrMsg);
    SQL_POST_PEOCESS(ret, "INSERT", zErrMsg, azResult);
    sql ="INSERT INTO 'TestTable' VALUES(2,100,\"string test0\",2012/05/05,88.8);";
    ret = sqlite3_exec(db,sql,NULL,NULL,&zErrMsg);
    SQL_POST_PEOCESS(ret, "INSERT", zErrMsg, azResult);
    sql ="INSERT INTO 'TestTable' VALUES(3,100,\"string test1\",2012/05/05,88.8);";
    ret = sqlite3_exec(db,sql,NULL,NULL,&zErrMsg);
    SQL_POST_PEOCESS(ret, "INSERT", zErrMsg, azResult);

    // 查询
    sql="SELECT * FROM TestTable; ";
    ret = sqlite3_get_table( db , sql , &azResult , &nrow , &ncolumn , &zErrMsg );
    printf("nrow=%d ncolumn=%d  result is:\n",nrow,ncolumn);
    for(i=0;i<(nrow+1)*ncolumn;i++)
    {
      if(i%ncolumn == 0) { printf("\n"); }
      printf(" %s ",azResult[i]);
    }
    printf("\n\n---------------------------------\n");
    SQL_POST_PEOCESS(ret, "SELECT *", zErrMsg, azResult);

    // 删除
    sql="delete from TestTable where column_1 = 100 and column_2 = \"string test1\";";
    ret = sqlite3_exec( db , sql , NULL , NULL , &zErrMsg );
    SQL_POST_PEOCESS(ret, "DELETE", zErrMsg, azResult);

    // 查询
    sql = "SELECT * FROM TestTable ";
    sqlite3_get_table( db , sql , &azResult , &nrow , &ncolumn , &zErrMsg );
    printf("after del:  nrow=%d ncolumn=%d  result is:\n",nrow,ncolumn);
    for(i=0;i<(nrow+1)*ncolumn;i++)
    {
      if(i%ncolumn == 0) { printf("\n"); }
      printf(" %s ",azResult[i]);
    }
    printf("\n---------------------------------\n");
    SQL_POST_PEOCESS(ret, "SELECT *", zErrMsg, azResult);

    sqlite3_close(db);
    return 0;
 
}

//gcc -o table_operation table_operation.c  -lsqlite3
//./operation

...