关于FMDB简单的举例说明

小提示：查看数据库的图形化工具可以使用火狐浏览器的插件SQLite Manager。
主要类：

FMDatabase ： 一个单一的SQLite数据库，用于执行SQL语句。
FMResultSet ：执行查询一个FMDatabase结果集。
FMDatabaseQueue ：在多个线程来执行查询和更新时会使用这个类。

1、获得数据库的路径

/*
 1. 如果该路径下已经存在该数据库，直接获取该数据库;
 2. 如果不存在就创建一个新的数据库;
 3. 如果传@""，会在临时目录创建一个空的数据库，当数据库关闭时，数据库文件也被删除;
 4. 如果传nil，会在内存中临时创建一个空的数据库，当数据库关闭时，数据库文件也被删除;
*/
+ (FMDatabase *)databaseWithPath:(NSString *)filePath;

// 获取数据库的路径
    NSString *dbPath = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"student.sqlite"];
    // 获得数据库
    FMDatabase *db = [FMDatabase databaseWithPath:dbPath];
    self.db = db;

2、执行更新语句

/* 执行更新的SQL语句，字符串里面的"?"，依次用后面的参数替代，必须是对象，不能是int等基本类型 */
- (BOOL)executeUpdate:(NSString *)sql,... ;
/* 执行更新的SQL语句，可以使用字符串的格式化进行构建SQL语句 */
- (BOOL)executeUpdateWithFormat:(NSString*)format,... ;
/* 执行更新的SQL语句，字符串中有"?"，依次用arguments的元素替代 */
- (BOOL)executeUpdate:(NSString*)sql withArgumentsInArray:(NSArray *)arguments;

- (IBAction)createTable:(UIButton *)sender {
    
    // 创建表
    if ([_db open]) {
        BOOL result = [_db executeUpdate:@"create table if not exists t_student (id integer primary key autoincrement, name text not NULL, age integer not NULL);"];
        if (result) {
            NSLog(@"创建成功");
        }
        
        [_db close];
    }
}

- (IBAction)insertData:(UIButton *)sender {
    
    static int age = 15;
    if ([_db open]) {
        BOOL insertResult = [_db executeUpdateWithFormat:@"insert into t_student (name, age) values (%@, %d);", @"MJCheung", age];
        //或者
//        BOOL insertResult = [_db executeUpdate:@"insert into t_student (name, age) values (?, ?);", @"MJCheung", @(age)];
        
        if (insertResult) {
            NSLog(@"插入数据成功%d", age);
            age++;
        } else {
            NSLog(@"插入数据失败");
        }
        
        [_db close];
    }
}

- (IBAction)deleteSelectedData:(id)sender {
    
    if ([_db open]) {
//        BOOL deleteResult = [_db executeUpdate:@"delete from t_student where id = ?;", @1];
        // 或者
        BOOL deleteResult = [_db executeUpdateWithFormat:@"delete from t_student where name = %@;", @"MJCheung"];
        if (deleteResult) {
            NSLog(@"delete success");
        }
        
        [_db close];
    }
    
}

// 删除所有的数据
- (IBAction)clearAllData:(id)sender {
    
    if ([_db open]) {
        BOOL result = [_db executeUpdate:@"delete from t_student"];
        if (result) {
            NSLog(@"delete success");
        }
    }
}

3、执行查询语句

常用select的相关方法
/* 执行查询SQL语句，返回FMResultSet查询结果 */
- (FMResultSet *)executeQuery:(NSString*)sql, ... ;
- (FMResultSet *)executeQueryWithFormat:(NSString*)format, ... ;
- (FMResultSet *)executeQuery:(NSString *)sql withArgumentsInArray:(NSArray *)arguments;
处理结果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;
举例：
- (IBAction)queryDataFromTable:(id)sender {
    
    if ([_db open]) {
        FMResultSet *res = [_db executeQuery:@"select * from t_student"];
        while ([res next]) {
            NSInteger ID = [res intForColumn:@"id"];
            NSString *name = [res stringForColumn:@"name"];
            NSInteger age = [res intForColumn:@"age"];
            NSLog(@"%ld-%@-%ld", ID, name, age);
        }
        [_db close];
    }
}

附上一个比较全的操作：

#import "ViewController.h"

#import "FMDB.h"

@interface ViewController ()

@property (nonatomic, strong) FMDatabase *database;
@property (nonatomic, copy) NSString *path;

@end

@implementation ViewController

- (void)viewDidLoad {
    [super viewDidLoad];
    
    self.path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).firstObject stringByAppendingPathComponent:@"test.db"];
    self.database = [FMDatabase databaseWithPath:self.path];
    if (self.database) {
        NSLog(@"创建数据库成功");
    } else {
        NSLog(@"创建数据库失败");
    }
    NSLog(@"数据库路径:\n%@", NSHomeDirectory());
}

// 创建数据表
- (IBAction)createTable {
    
    if ([self.database open]) {
        NSString *createSql = @"create table if not exists User(id integer primary key autoincrement, username text, phone text, age integer)";
        BOOL result = [self.database executeUpdate:createSql];
        if (result) {
            NSLog(@"创建User表成功");
        } else{
            NSLog(@"创建User表失败");
        }
        // 每次执行完sql之后,一定要记得关闭数据库
        [self.database close];
    }
}

// 添加数据
- (IBAction)add {
    if ([self.database open]) {
        NSString *addSql = @"insert into User (username, phone, age) values (?, ?, ?)";
        for (NSInteger i = 0; i < 10; i++) {
            BOOL result = [self.database executeUpdate:addSql,
                           [NSString stringWithFormat:@"老张_%ld", i],
                           [NSString stringWithFormat:@"1866586519%ld", i],
                           [NSString stringWithFormat:@"2%ld", i]];
            if (result) {
                NSLog(@"添加数据成功");
            } else{
                NSLog(@"添加数据失败");
            }
        }
        [self.database close];
    }
}

// 删除数据
- (IBAction)delete:(id)sender {
    if ([self.database open]) {
        
        BOOL result = [self.database executeUpdateWithFormat:@"delete from User where username = %@;", @"老张_8"];
//        或者
//        NSString *deleteSql = @"delete from User where username = ?";
//        BOOL result = [self.database executeUpdate:deleteSql, @"老张_1"];
//        或者
//        NSString *deleteSql = @"delete from User where username = \"老张_2\"";
//        BOOL result = [self.database executeUpdate:deleteSql];
        if (result) {
            NSLog(@"删除数据成功");
        } else{
            NSLog(@"删除数据失败");
        }
        [self.database close];
    }
}

// 更新数据
- (IBAction)update:(id)sender {
    if ([self.database open]) {
        NSString *updateSql = @"update User set username = ? where phone = ?";
        BOOL result = [self.database executeUpdate:updateSql, @"更新操作", @"18665865197"];
        if (result) {
            NSLog(@"更新数据成功");
        } else{
            NSLog(@"更新数据失败");
        }
        [self.database close];
    }
}

// 查询数据
- (IBAction)query:(id)sender {
    if ([self.database open]) {
//        NSString *querySql = @"select * from User";
//        FMResultSet *resultSet = [self.database executeQuery:querySql];
//        while ([resultSet next]) {
//            NSString *username = [resultSet stringForColumn:@"username"];
//            NSString *phone = [resultSet stringForColumn:@"phone"];
//            NSInteger age = [resultSet intForColumn:@"age"];
//            NSLog(@"username = %@, phone = %@, age = %ld", username, phone, age);
//        }
//        或者
        NSString *querySql = @"select * from User where age > 28";
        FMResultSet *resultSet = [self.database executeQuery:querySql];
        NSString *columnName = [resultSet columnNameForIndex:1];
        NSInteger columnCount = [resultSet columnCount];
        NSLog(@"%@---%ld", columnName, columnCount);
        while ([resultSet next]) {
            NSString *username = [resultSet stringForColumn:@"username"];
            NSString *phone = [resultSet stringForColumn:@"phone"];
            NSInteger age = [resultSet intForColumn:@"age"];
            NSLog(@"username = %@, phone = %@, age = %ld", username, phone, age);
        }
//        或者
//        NSString *querySql = @"select age from User where age > 25";
//        FMResultSet *resultSet = [self.database executeQuery:querySql];
//        while ([resultSet next]) {
//            NSInteger age = [resultSet intForColumn:@"age"];
//            NSLog(@"age = %ld", age);
//        }
        
        // ❗️一定要注意,关闭数据库
        [self.database close];
    }
}

- (IBAction)deleteTable {
    if ([self.database open]) {
        
        BOOL result = [self.database executeUpdateWithFormat:@"drop table if exists User"];
        if (result) {
            NSLog(@"删除表成功");
        } else{
            NSLog(@"删除表失败");
        }
    }
}

@end
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容