iOS SQLite 读书笔记

 

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）