转载自: https://blog.csdn.net/rpf2014/article/details/53116906
上一篇文章介绍了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
- //结尾是李的所有数据
- -(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 相当于集合
- -(NSMutableArray *)getAllDataIn{
- NSString *sql = [NSString stringWithFormat:@"select * from workTable where name in ('小明1','小李')"];
- return [self getReArrFromSql:sql];
- }
- //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