iOS中FMDB数据库框架使用示例

// 建表

// 数据库路径
NSString *path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).firstObject stringByAppendingPathComponent:dbExtention];
// 建数据库
FMDatabaseQueue *dbQueue = [FMDatabaseQueue databaseQueueWithPath:path];
    
 [dbQueue inDatabase:^(FMDatabase *db) {
        // 建表
        NSString * sql = [NSString stringWithFormat:@"CREATE TABLE '%@' ('id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , 'fileName' text, 'timeStamp' INTEGER, 'duration' INTEGER, 'noNum' INTEGER, 'score' text, 'hasPlayed' INTEGER)", tableName];

        BOOL res = [db executeUpdate:sql];
        NSLog(@"建表%@", (res ? @"成功" : @"失败"));
    }];

// 删表    
    [dbQueue inDatabase:^(FMDatabase *db) {
        
        NSString * sql = [NSString stringWithFormat:@"DROP TABLE '%@'", tableName];
        BOOL res = [db executeUpdate:sql];
        
        NSLog(@"删表%@", (res ? @"成功" : @"失败"));
    }];

// 判断表是否存在
[dbQueue inDatabase:^(FMDatabase *db) {
        
        NSString *existsSql = [NSString stringWithFormat:@"select count(name) as countNum from sqlite_master where type = 'table' and name = '%@'", tableName];
        FMResultSet *res = [db executeQuery:existsSql];
        
        NSLog(@"表存在%@", (res ? @"成功" : @"失败"));
    }];

// 插入数据
[dbQueue inDatabase:^(FMDatabase *db) {
        
        NSString *fileName = record.fileName;
        NSInteger timeStamp = record.timeStamp;
        NSInteger duration = record.duration;
        NSInteger noNum = record.noNum;
        NSString *score = record.score;
        NSInteger hasPlayed = record.hasPlayed;
        
        NSString * sql = [NSString stringWithFormat:@"INSERT INTO '%@' ('fileName', 'timeStamp', 'duration', 'noNum', 'score', 'hasPlayed') VALUES ('%@', '%ld', '%ld', '%ld', '%@', '%ld')", tableName, fileName, timeStamp, duration, noNum, score, hasPlayed];
        
        BOOL res = [db executeUpdate:sql];
        NSLog(@"插入%@", (res ? @"成功" : @"失败"));
        
        if(callBack) {
            
            callBack(res);
        }
    }];

// 更新数据
NSString * sql1 = [NSString stringWithFormat:@"update '%@' set 'noNum' = '%ld' where timeStamp = %ld", tableName, noNum, timeStamp];
NSString * sql2 = [NSString stringWithFormat:@"update '%@' set 'score' = '%@' where timeStamp = %ld", tableName, score, timeStamp];
NSString * sql3 = [NSString stringWithFormat:@"update '%@' set 'hasPlayed' = '%ld' where timeStamp = %ld", tableName, hasPlayed, timeStamp];
        
        BOOL res1 = [db executeUpdate:sql1];
        BOOL res2 = [db executeUpdate:sql2];
        BOOL res3 = [db executeUpdate:sql3];
        
        NSLog(@"插入%@", ((res1 && res2 && res3) ? @"成功" : @"失败"));

// 查询
NSMutableArray *recordArr = [NSMutableArray array];
NSString * sql = [NSString stringWithFormat:@"SELECT * FROM '%@' ORDER BY timeStamp ASC", tableName];
FMResultSet * rs = [db executeQuery:sql];

        while ([rs next]) {
            
            NSString *filePath = [rs stringForColumn:@"fileName"];
            NSInteger timeStamp = [rs longForColumn:@"timeStamp"];
            NSInteger duration = [rs intForColumn:@"duration"];
            NSInteger noNum = [rs intForColumn:@"noNum"];
            NSString *score = [rs stringForColumn:@"score"];
            NSInteger hasPlayed = [rs intForColumn:@"hasPlayed"];
            
            CYUserRecord *record = [[CYUserRecord alloc] init];
            record.fileName = filePath;
            record.timeStamp = timeStamp;
            record.duration = duration;
            record.noNum = noNum;
            record.score = score;
            record.hasPlayed = hasPlayed;
            
            [recordArr addObject:record];
        }

NSString * sql1 = [NSString stringWithFormat:@"SELECT * FROM '%@' WHERE patched = 0 ORDER BY timeStamp ASC", tableName];

删除某一条数据：
NSString * sqlDelete = [NSString stringWithFormat:@"DELETE FROM '%@' WHERE fileName = '%@'", tableName, fileName];
[db executeUpdate:sqlDelete];

从一个表直接往另一个表添加数据：
insert into table1 select  * from table2