iOS 基于FMDB简单的查询操作

上一篇文章介绍了FMDB的安装增删改查等的操作

这篇文章主要是数据查询的筛选和常用的数据库统计函数，便于以后的查询和学习.

查询函数主要分为两大类

第一大类就是数据的筛选 是基于数据表的筛选

数据源

-(void)dataConfig{
    _dataArr = [[NSMutableArray alloc] init];
    
    Worker *w1 = [[Worker alloc] init];
    w1.workId = @"2";
    w1.name = @"小明1";
    w1.age = 12;
    Worker *w2 = [[Worker alloc] init];
    w2.workId = @"1";
    w2.name = @"小明2";
    w2.age = 13;
    Worker *w3 = [[Worker alloc] init];
    w3.workId = @"3";
    w3.name = @"小李";
    w3.age = 14;
    Worker *w4 = [[Worker alloc] init];
    w4.workId = @"4";
    w4.name = @"小李";
    w4.age = 15;
    [_dataArr addObject:w1];
    [_dataArr addObject:w2];
    [_dataArr addObject:w3];
    [_dataArr addObject:w4];
    
}

先封装一下数据的统计

-(NSMutableArray *)getReArrFromSql:(NSString *)sql{
    FMResultSet *rs = [_database executeQuery:sql];
    NSMutableArray *arr = [[NSMutableArray alloc] init];
    while ([rs next]) {
        Worker *model = [[Worker alloc] init];
        model.workId = [rs stringForColumn:@"workId"];
        model.name = [rs stringForColumn:@"name"];
        model.age = [rs intForColumn:@"age"];
        [arr addObject:model];
    }
    return arr;
}

数据的升序和降序

//order by
//1 排序
//asc 默认 升序
//desc 降序
-(NSMutableArray *)getAllDataOrderBy:(NSString *)type{
    NSString *sql = [NSString stringWithFormat:@"select * from workTable order by workId %@",type];
    return [self getReArrFromSql:sql];
}

去重

//distinct
//1 只能放在最前面
//2 只能作用于一列
-(NSMutableArray *)getAllDataDistinct{
    NSString *sql = [NSString stringWithFormat:@"select distinct name from workTable"];
    return [self getReArrFromSql:sql];
}

并

//and就是并的作用
-(NSMutableArray *)getAllDataAnd{
    NSString *sql = [NSString stringWithFormat:@"select * from workTable where workId = 1 and name = '小张'"];
    return [self getReArrFromSql:sql];
}

或

//or就是或的作用
-(NSMutableArray *)getAllDataOr{
    NSString *sql = [NSString stringWithFormat:@"select * from workTable where workId = 1 or name = '小李'"];
    return [self getReArrFromSql:sql];
}

like 模糊查询

//like
//结尾是李的所有数据
-(NSMutableArray *)getAllDataLike1{
    NSString *sql = [NSString stringWithFormat:@"select * from workTable where name like '%%李'"];
    return [self getReArrFromSql:sql];
}

//开头是小的所有数据
-(NSMutableArray *)getAllDataLike2{
    NSString *sql = [NSString stringWithFormat:@"select * from workTable where name like '小%%'"];
    return [self getReArrFromSql:sql];
}

//中间是明的所有数据
-(NSMutableArray *)getAllDataLike3{
    NSString *sql = [NSString stringWithFormat:@"select * from workTable where name like '%%明%%'"];
    return [self getReArrFromSql:sql];
}

//不包含明的所有数据
-(NSMutableArray *)getAllDataLike4{
    NSString *sql = [NSString stringWithFormat:@"select * from workTable where name not like '%%明%%'"];
    return [self getReArrFromSql:sql];
}

in 相当于集合

//in 相当于集合
-(NSMutableArray *)getAllDataIn{
    NSString *sql = [NSString stringWithFormat:@"select * from workTable where name in ('小明1','小李')"];
    return [self getReArrFromSql:sql];
}

between and 闭区间

//between sqlite里面的between都是闭区间
-(NSMutableArray *)getAllDataBetween{
    NSString *sql = [NSString stringWithFormat:@"select * from workTable where workId between 1 and 3"];
    return [self getReArrFromSql:sql];
}

第二大类是数据的统计，比如数据量，某个字段的平均值
分组并筛选

//group by 筛选结果的计算
//having 对group by 结果的进一步筛选
-(NSMutableArray *)getAllDataGroupBy{
    NSString *sql = [NSString stringWithFormat:@"select name,sum(age) s from workTable group by name having s>15"];
    NSMutableArray *arr = [[NSMutableArray alloc] init];
    FMResultSet *rs = [_database executeQuery:sql];
    while ([rs next]) {
        Worker *model = [[Worker alloc] init];
        model.name = [rs stringForColumn:@"name"];
        model.age = [rs intForColumn:@"s"];
        [arr addObject:model];
    }
    return arr;
}

最小值

//min 最小值
-(NSInteger)getAllDataMin{
    NSString *sql = [NSString stringWithFormat:@"select min(age) minAge from workTable"];
    NSMutableArray *arr = [[NSMutableArray alloc] init];
    FMResultSet *rs = [_database executeQuery:sql];
    while ([rs next]) {
        Worker *model = [[Worker alloc] init];
        model.statistic = [rs intForColumn:@"minAge"];
        [arr addObject:model];
    }
    Worker *model = [arr firstObject];
    return model.statistic;
}

最大值

//max 最大值
-(NSInteger)getAllDataMax{
    NSString *sql = [NSString stringWithFormat:@"select max(age) maxAge from workTable"];
    NSMutableArray *arr = [[NSMutableArray alloc] init];
    FMResultSet *rs = [_database executeQuery:sql];
    while ([rs next]) {
        Worker *model = [[Worker alloc] init];
        model.statistic = [rs intForColumn:@"maxAge"];
        [arr addObject:model];
    }
    Worker *model = [arr firstObject];
    return model.statistic;
}

数据之和

//sum 数据之和
-(NSInteger)getAllDataSum{
    NSString *sql = [NSString stringWithFormat:@"select sum(age) sumAge from workTable"];
    NSMutableArray *arr = [[NSMutableArray alloc] init];
    FMResultSet *rs = [_database executeQuery:sql];
    while ([rs next]) {
        Worker *model = [[Worker alloc] init];
        model.statistic = [rs intForColumn:@"sumAge"];
        [arr addObject:model];
    }
    Worker *model = [arr firstObject];
    return model.statistic;
}

数据平均值

//avg 数据的平均值
-(NSInteger)getAllDataAvg{
    NSString *sql = [NSString stringWithFormat:@"select avg(age) avgAge from workTable"];
    NSMutableArray *arr = [[NSMutableArray alloc] init];
    FMResultSet *rs = [_database executeQuery:sql];
    while ([rs next]) {
        Worker *model = [[Worker alloc] init];
        model.statistic = [rs intForColumn:@"avgAge"];
        [arr addObject:model];
    }
    Worker *model = [arr firstObject];
    return model.statistic;
}

数据量

//count 表中的数据量
-(NSInteger)getAllDataCount{
    NSString *sql = [NSString stringWithFormat:@"select count(*) count from workTable"];
    NSMutableArray *arr = [[NSMutableArray alloc] init];
    FMResultSet *rs = [_database executeQuery:sql];
    while ([rs next]) {
        Worker *model = [[Worker alloc] init];
        model.statistic = [rs intForColumn:@"count"];
        [arr addObject:model];
    }
    Worker *model = [arr firstObject];
    return model.statistic;
}

以上就是简单的数据库查询和统计

Demo http://download.csdn.net/detail/rpf2014/9689776