iOS sqlite的使用

1、基本的数据库操作

（1）加libsqlite3.0.dylib库， #import  "/usr/include/sqlite3.h"

（2）打开数据库

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

（3）对表的操作

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有五种数据存储类型：

（a）INTEGER.整型值

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

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

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

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

（4）示例

#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）