FMDB常用语句整合

//获得数据库文件路径
    NSString *doc=[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *fileName=[doc stringByAppendingPathComponent:@"wktest.sqlite"];
    //获得数据库
    FMDatabase *db=[FMDatabase databaseWithPath:fileName];
    
    //打开数据库
    if ([db open]){
        //创表
        BOOL result=[db executeUpdate:@"CREATE TABLE IF NOT EXISTS customer_users (id integer PRIMARY KEY AUTOINCREMENT, name text NOT NULL, password text NOT NULL,phone text NOT NULL);"];
        if(result){
            NSLog(@"创表users成功");
        }
        else{
            NSLog(@"创表失败");
        }
    }

//插入数据
-(void)insert
{
    for (int i = 0;i<10;i++){
        NSString *name = [NSString stringWithFormat:@"user-%d",arc4random_uniform(100)];
        [self.db executeUpdate:@"INSERT INTO customer_user (name, password,phone) VALUES (?,'123456', '13524568745');", name];
    }
}

//删除数据
-(void)delete
{
    [self.db executeUpdate:@"DROP TABLE IF EXISTS customer_user;"];
    [self.db executeUpdate:@"CREATE TABLE IF NOT EXISTS customer_user (id integer PRIMARY KEY AUTOINCREMENT, name text NOT NULL, password text NOT NULL,phone text NOT NULL);"];
}

//查询数据
-(void)query
{
    //执行查询语句
    FMResultSet *resultSet = [self.db executeQuery:@"SELECT *FROM customer_user"];
    
    //遍历结果
    while([resultSet next]){
        int ID = [resultSet intForColumn:@"id"];
        NSString *name = [resultSet stringForColumn:@"name"];
        NSString *password = [resultSet stringForColumn:@"password"];
        NSString *phone = [resultSet stringForColumn:@"phone"];
        NSLog(@"%d %@ %@ %@",ID,name,password,phone);
    }
}

4种使用等价：

/* 1. 直接使用完整的SQL更新语句 */
[database executeUpdate:@"insert into mytable(num,name,sex) values(0,'liuting','m');"];

NSString *sql = @"insert into mytable(num,name,sex) values(?,?,?);";
/* 2. 使用不完整的SQL更新语句，里面含有待定字符串"?"，需要后面的参数进行替代 */
[database executeUpdate:sql,@0,@"liuting",@"m"];
/* 3. 使用不完整的SQL更新语句，里面含有待定字符串"?"，需要数组参数里面的参数进行替代 */
[database executeUpdate:sql 
   withArgumentsInArray:@[@0,@"liuting",@"m"]];

/* 4. SQL语句字符串可以使用字符串格式化 */
[database executeUpdateWithFormat:@"insert into mytable(num,name,sex) values(%d,%@,%@);",0,@"liuting","m

处理结果FMResultSet的常用方法：

/* 获取下一个记录 */
- (BOOL)next;
/* 获取记录有多少列 */
- (int)columnCount;
/* 通过列名得到列序号，通过列序号得到列名 */
- (int)columnIndexForName:(NSString *)columnName;
- (NSString *)columnNameForIndex:(int)columnIdx;
/* 获取存储的整形值 */
- (int)intForColumn:(NSString *)columnName;
- (int)intForColumnIndex:(int)columnIdx;
/* 获取存储的长整形值 */
- (long)longForColumn:(NSString *)columnName;
- (long)longForColumnIndex:(int)columnIdx;
/* 获取存储的布尔值 */
- (BOOL)boolForColumn:(NSString *)columnName;
- (BOOL)boolForColumnIndex:(int)columnIdx;
/* 获取存储的浮点值 */
- (double)doubleForColumn:(NSString *)columnName;
- (double)doubleForColumnIndex:(int)columnIdx;
/* 获取存储的字符串 */
- (NSString *)stringForColumn:(NSString *)columnName;
- (NSString *)stringForColumnIndex:(int)columnIdx;
/* 获取存储的日期数据 */
- (NSDate *)dateForColumn:(NSString *)columnName;
- (NSDate *)dateForColumnIndex:(int)columnIdx;
/* 获取存储的二进制数据 */
- (NSData *)dataForColumn:(NSString *)columnName;
- (NSData *)dataForColumnIndex:(int)columnIdx;
/* 获取存储的UTF8格式的C语言字符串 */
- (const unsigned cahr *)UTF8StringForColumnName:(NSString *)columnName;
- (const unsigned cahr *)UTF8StringForColumnIndex:(int)columnIdx;
/* 获取存储的对象，只能是NSNumber、NSString、NSData、NSNull */
- (id)objectForColumnName:(NSString *)columnName;
- (id)objectForColumnIndex:(int)columnIdx;

使用实例：

- (NSArray *)getResultFromDatabase{
    //执行查询SQL语句，返回查询结果
    FMResultSet *result = [_database executeQuery:@"select * from mytable"];
    NSMutableArray *array = [NSMutableArray array];
    //获取查询结果的下一个记录
    while ([result next]) {
        //根据字段名，获取记录的值，存储到字典中
        NSMutableDictionary *dict = [NSMutableDictionary dictionary];
        int num  = [result intForColumn:@"num"];
        NSString *name = [result stringForColumn:@"name"];
        NSString *sex  = [result stringForColumn:@"sex"];
        dict[@"num"] = @(num);
        dict[@"name"] = name;
        dict[@"sex"] = sex;
        //把字典添加进数组中
        [array addObject:dict];
    }
    return array;
}