1. 基本的数据库操作

  0) 加libsqlite3.0.dylib库， #import  "/usr/include/sqlite3.h"

  1) 打开数据库

   

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

2）对表的操作

int  sqlite3_exec(  sqlite3*,  /* An  open  database */ const  char  *sql, /* SQL  to be evaluated */ int  (*callback)(void*,int,char**,char**),  /*Callbk func*/ void  *, /* 1st  argument  to callback*/ char  **errmsg /* Error  msg  written  here */ );

 

SQLite有五种数据存储类型：

•  INTEGER. 整型值

•  REAL. 浮点类型（An  8-byte IEEE floating-point storage representing a floating-point number. ）

•  TEXT. 字符串 （A storage area for text. The text can be in any of the following encodings: UTF-8, UTF-16BE, or  UTF-16-LE. ）

•  BLOB. 大数据 （Used to store data exactly as entered — for example, an image. ）

•  NULL. 空值 （Used to store  the value  NULL.）

3）示例

#import  "/usr/include/sqlite3.h"  int  main(int  argc,  char  *argv[])  {  char  *sqlStatement;  sqlite3  *pDb;  char  *errorMsg;  int  returnCode;  char  *databaseName;  databaseName  =  "financial.db";  returnCode  =  sqlite3_open(databaseName,  &pDb);  if(returnCode!=SQLITE_OK)  {  　　fprintf(stderr,  "Error  in  opening  the  database.  Error:  %s",  　　sqlite3_errmsg(pDb));  　　sqlite3_close(pDb);  　　return  -1;  }  sqlStatement  =  "DROP  TABLE  IF  EXISTS  stocks";  returnCode  =  sqlite3_exec(pDb,  sqlStatement,  NULL,  NULL,  &errorMsg);  if(returnCode!=SQLITE_OK)  {  　　fprintf(stderr,  　　"Error  in  dropping  table  stocks.  Error:  %s",  errorMsg);  　　sqlite3_free(errorMsg);  }  sqlStatement  =  "CREATE  TABLE  stocks  (symbol  VARCHAR(5),  "  "purchasePrice  FLOAT(10,4),  "  "unitsPurchased  INTEGER,  "  "purchase_date  VARCHAR(10))";  returnCode  =  sqlite3_exec(pDb,  sqlStatement,  NULL,  NULL,  &errorMsg);  if(returnCode!=SQLITE_OK)  {  fprintf(stderr,  "Error  in  creating  the  stocks  table.  Error:  %s",  errorMsg);  sqlite3_free(errorMsg);  }  insertStockPurchase(pDb,  "ALU",  14.23,  100,  "03-17-2012");  insertStockPurchase(pDb,  "GOOG",  600.77,  20,  "01-09-2012");  insertStockPurchase(pDb,  "NT",  20.23,140,  "02-05-2012");  insertStockPurchase(pDb,  "MSFT",  30.23,  5,  "01-03-2012");  sqlite3_close(pDb);  return  0;  }  void  insertStockPurchase(sqlite3  *pDb,  const  char*symbol,  float  price,  int  units,  const  char*  theDate){  char  *errorMsg;  int  returnCode;  char  *st;  st  =  sqlite3_mprintf("INSERT  INTO  stocks  VALUES"  "  (’%q’,  %f,  %d,  ’%q’)",  symbol,  price,  units,  theDate);  returnCode  =  sqlite3_exec(pDb,  st,  NULL,  NULL,  &errorMsg);  if(returnCode!=SQLITE_OK)  {  fprintf(stderr,  "Error  in  inserting  into  the  stocks  table.  Error:  %s",  errorMsg);  sqlite3_free(errorMsg);  }  sqlite3_free(st);  }

 

2.  Processing Row Results

  利用回调函数来处理每行的数据

3. 预申明 （Prepared Statements）

  对于不返回数据的sql操作（insert,drop,create），用sqlite3_exec()；对于返回数据的操作(select)，

1）三个阶段：

    准备（Preparation），执行（Execution），结束（Finalization）

      准备 - sqlite3_prepare_v2(sqlite3  *db,const  char  *zSql ,int  nBytes,sqlite3_stmt  **ppStmt,const  char  **pzTail)

      执行 - sqlite3_step(sqlite3_stmt*);

      结束 - sqlite3_finalize(sqlite3_stmt  *pStmt);

 示例：

#import  "/usr/include/sqlite3.h"  int  main(int  argc,  char  *argv[])  {  　　char  *sqlStatement;  　　sqlite3  *database;  　　int  returnCode;  　　char  *databaseName;  　　sqlite3_stmt  *statement;  　　databaseName  =  "financial.db";  　　returnCode  =  sqlite3_open(databaseName,  &database);  　　if(returnCode!=SQLITE_OK)  {  　　　　fprintf(stderr,  "Error  in  opening  the  database.  Error:  %s",  　　　　sqlite3_errmsg(database));  　　　　sqlite3_close(database);  　　　　return  -1;  　　}  　　sqlStatement  =  sqlite3_mprintf(  　　"SELECT  S.symbol,  S.unitsPurchased,  "  　　"S.purchasePrice  FROM  stocks  AS  S  WHERE  "  　　"S.purchasePrice  >=  %f",  30.0);  　　returnCode  =  　　sqlite3_prepare_v2(database, sqlStatement, strlen(sqlStatement), &statement, NULL);  　　if(returnCode  !=  SQLITE_OK)  {  　　fprintf(stderr,  "Error  in  preparation  of  query.  Error:  %s",  　　sqlite3_errmsg(database));  　　sqlite3_close(database);  　　return  -1;  　　}  　　returnCode  =  sqlite3_step(statement);  　　while(returnCode  ==  SQLITE_ROW){  　　char  *symbol;  　　int  units;  　　double  price;  　　symbol  =  sqlite3_column_text(statement,  0);  　　units  =  sqlite3_column_int(statement,  1);  　　price  =  sqlite3_column_double(statement,  2);  　　printf("We  bought  %d  from  %s  at  a  price  equal  to  %.4f\n",  　　units,  symbol,  price);  　　returnCode  =  sqlite3_step(statement);  　　}  　　sqlite3_finalize(statement);  　　sqlite3_free(sqlStatement);  　　return  0;  }

 

4.  用户自定义函数 (User-Defined Functions)

   sqlite3_create_function()

5. 存储二进制大对象(Storing BLOBs)

 

6. 检索二进制大对象（Retrieving BLOBs）