数据库操作之FMDB的使用方法

1.在工程中导入libsqlite3.0.dylib框架

2.引入头文件

#import "FMDatabase.h"

#import "FMDatabaseQueue.h"

3.添加成员变量

FMDatabase *db;

NSString *database_path;

#define DBNAME    @"personinfo.sqlite"

#define ID        @"id"

#define NAME      @"name"

#define AGE       @"age"

#define ADDRESS   @"address"

#define TABLENAME @"PERSONINFO"

4.创建表

if([db open]){

NSString *createTable = [NSString stringWithFormat:@"create table if not exists '%@' ('%@',integer primary key auto increment,'%@' text, '%@' integer, '%@', text )",

TABLENAME,ID,NAME,AGE,ADDRESS];

BOOL result = [db executeUpdate:createTable];

if (!result) {

 NSLog("创建表失败");

} else {

 NSLog("创建成功");

}

[db close];

}

5.插入数据

if ([db open]){

 NSString *insert = [NSString stringWithFormat:@"insert into '%@' ('%@','%@','%@') values('%@', '%@', '%@', TABLEMANE, NAME,AGE, ADDRESS, @"老蒋",@"30",@"河南")];

BOOL result = [dbexecuteUpdate:insert];

if (!result) {

            NSLog(@"插入错误");

        }else {

            NSLog(@"插入成功");

        }

        [dbclose];

}

6.修改数据

 if ([dbopen]) {

       NSString *updateSql = [NSStringstringWithFormat:

                               @"UPDATE '%@' SET '%@' = '%@' WHERE '%@' = '%@'",

                              TABLENAME,   AGE, @"15" ,AGE, @"13"];

       BOOL res = [dbexecuteUpdate:updateSql];

       if (!res) {

            NSLog(@"error when update db table");

        }else {

            NSLog(@"success to update db table");

        }

        [dbclose];

    }

7.删除数据

 if ([dbopen]) {

        

       NSString *deleteSql = [NSStringstringWithFormat:

                               @"delete from %@ where %@ = '%@'",

                              TABLENAME, NAME,@"张三"];

       BOOL res = [dbexecuteUpdate:deleteSql];

        

       if (!res) {

            NSLog(@"error when delete db table");

        }else {

            NSLog(@"success to delete db table");

        }

        [dbclose];

    }

8.查询数据

 if ([dbopen]) {

       NSString * sql = [NSStringstringWithFormat:

                         @"SELECT * FROM %@",TABLENAME];

       FMResultSet * rs = [dbexecuteQuery:sql];

       while ([rs next]) {

           int Id = [rs intForColumn:ID];

           NSString * name = [rs stringForColumn:NAME];

           NSString * age = [rs stringForColumn:AGE];

           NSString * address = [rs stringForColumn:ADDRESS];

            NSLog(@"id = %d, name = %@, age = %@  address = %@", Id, name, age, address);

        }

        [dbclose];

    }