Sqlite简单使用方法

1.首先要添加 libsqlite3.dylib 库。在添加库的时候，你会发现有个libsqlite3.dylib 还有一个 libsqlite3.0.dylib。这样你就会疑问，两个的区别。这里介绍一下：实际上libsqlite3.dylib本身是个链接，它指向了libsqlite3.0.dylib。libsqlite3.dylib总是指向最新的sqlite动态库。

2.在view中引入框架（2种方法）。

（1）#import （2）#import"/usr/include/sqlite3.h"

sqlite3 *database;

3.sqlite 数据库是文件数据库，是保存在文件 系统中的。在Documents下创建数据库（路径）。

NSArray *documentsPath =NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES);

NSString *databaseFilePath = [[documentsPathobjectAtIndex:0]stringByAppendingPathComponent:@"mydb"];

4.打开数据库

if (sqlite3_open([databaseFilePathUTF8String], &database) ==SQLITE_OK) {

NSLog(@"open sqlite db ok.");

}

5.建表语句

char *errorMsg;

constchar *createSql ="create table if not exists persons(id integer primary key autoincrement, name text)";

if (sqlite3_exec(database, createSql,NULL,NULL, &errorMsg) ==SQLITE_OK) {

NSLog(@"create ok");

}

6.向表中插入记录

const char *insertSql ="insert into persons (name) values ('zhangsan')";

if (sqlite3_exec(database, insertSql,NULL,NULL, &errorMsg) ==SQLITE_OK) {

NSLog(@"insert ok.");

}else{

NSLog(@"error:%s",errorMsg);

sqlite3_free(errorMsg);

}

7.查询结果

const char *selectSql ="select id,name from persons";

sqlite3_stmt *statement;

if (sqlite3_prepare_v2(database, selectSql, -1, &statement, nil) == SQLITE_OK) {

NSLog(@"select ok");

while (sqlite3_step(statement) ==SQLITE_ROW) {

int _id =sqlite3_column_int(statement,0);

char *name = (char*)sqlite3_column_text(statement,1);

NSString *nsAddressStr = [[NSStringalloc]initWithUTF8String:name];

NSLog(@"id:%i name:%@",_id,nsAddressStr);

}

}

8.关闭数据库

sqlite3_close(database);

sqlite3 *database；数据库句柄

sqlite3_stmt 用于保存编译好的sql语句

sqlite3_open() 打开数据库，没有的时候创建数据库

sqlite3_exec() 执行非查询的sql语句

sqlite3_prepare() 准备sql语句，执行select语句

sqlite3_step() 再调用sqlite3_prepare后，使用这个函数在记录集中移动

sqlite3_finalize() 释放sql资源

sqlite3_close() 关闭数据库

sqlite3_bind_int(stmt,1,1); //绑定第一个int参数

sqlite3_bind_text(stmt,2,[ UTF8String],-1,SQLITE_TRANSIENT);//绑定第二个字符串参数

记录集中获取字段

sqlite3_column_int() 取int类型数据

sqlite3_column_text() 取text型数据

sqlite3_column_blob() 取blob型数据

#define SQLITE_OK 0 /* Successful result */

/* beginning-of-error-codes */

#define SQLITE_ERROR 1 /* SQL error or missing database */

#define SQLITE_INTERNAL 2 /* Internal logic error in SQLite */

#define SQLITE_PERM 3 /* Access permission denied */

#define SQLITE_ABORT 4 /* Callback routine requested an abort */

#define SQLITE_BUSY 5 /* The database file is locked */

#define SQLITE_LOCKED 6 /* A table in the database is locked */

#define SQLITE_NOMEM 7 /* A malloc() failed */

#define SQLITE_READONLY 8 /* Attempt to write a readonly database */

#define SQLITE_INTERRUPT 9 /* Operation terminated by sqlite3_interrupt()*/

#define SQLITE_IOERR 10 /* Some kind of disk I/O error occurred */

#define SQLITE_CORRUPT 11 /* The database disk image is malformed */