iOS总结1 - 数据库sqlite的使用

一、集成测试环境

1.在github中搜索FMDB https://github.com/ccgus/fmdb
2.创建测试工程
3.创建podfile 文件，安装FMDB pod 'FMDB'
4.导入 #import <FMDB.h> 头文件

二、测试用例

1.生成数据库

NSString *path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject stringByAppendingPathComponent:@"fm.db"];
FMDatabase *db = [FMDatabase databaseWithPath:path];

2.创建表文件语句

empty.png

3.在里面输入

create table if not exists T_Person(
    id integer not null primary key autoincrement,
    age integer,
    name text
);
create table if not exists T_Student(
    id integer not null primary key autoincrement,
    age integer,
    name text,
    score integer
);

4.加载sql 语句

NSString *sqlPath = [[NSBundle mainBundle] pathForResource:@"db.sql" ofType:nil];
NSString *sql = [NSString stringWithContentsOfFile:sqlPath encoding:NSUTF8StringEncoding error:nil];

5.执行sql语句

   [db open];
    //执行多条sql 语句
    BOOL result = [db executeStatements:sql];
    if (result) {
        NSLog(@"创表成功");
    } else {
        NSLog(@"创表失败");
    }
    [db close];

6.打开火狐浏览器，打开 工具，选择 附加组件，点击左侧的扩展,然后右侧的搜索栏里面，输入sqlite，点击 安装,重启火狐浏览器,点击工具，打开

Snip20170815_2.png
然后点击 Snip20170815_3.png

选择刚才创建好报表的数据库

Snip20170815_4.png

三、 插入，查询，修改，删除

1.插入

    NSString *insertSql = @"INSERT INTO T_Person(age,name) values(?,?)";
    BOOL insertResult =  [db executeUpdate:insertSql withArgumentsInArray:@[@(18),@"周文刚"]];
    if (insertResult) {
        NSLog(@"插入成功");
    } else {
        NSLog(@"插入失败");
    }

2.查询

  //1.简单查询
    NSString *selectSql = @"select id,name,age from T_Person";
    FMResultSet *selectResult = [db executeQuery:selectSql];
    while (selectResult.next) {
        NSInteger key = [selectResult intForColumn:@"id"];
        NSString *name = [selectResult stringForColumn:@"name"];
        NSInteger age = [selectResult intForColumn:@"age"];
        NSLog(@"key = %ld,name = %@,age = %ld",(long)key,name,(long)age);
    }

执行的结果

Snip20170815_5.png

  //2.复杂的查询
  NSMutableArray *selectArray = [NSMutableArray array];
  FMResultSet *selectResult1 = [db executeQuery:selectSql];
  while (selectResult1.next) {
      NSMutableDictionary *dic = [NSMutableDictionary dictionary];
      NSInteger count = selectResult1.columnCount;
      for (int i = 0; i < count; i++) {
          NSString *colName = [selectResult1 columnNameForIndex:i];
          id colValue = [selectResult1 objectForColumn:colName];
          
          dic[colName] = colValue;
      }
    
      [selectArray addObject:dic];
  
  }
  NSLog(@"%@",selectArray);

运行结果截图

Snip20170815_6.png

3.更新

    NSString *updateSql = @"update T_Person set name = ?, age = ? where id = ?";
    BOOL resultupdate = [db executeUpdate:updateSql withArgumentsInArray:@[@"小明",@17,@1]];
    if (resultupdate) {
        NSLog(@"更新成功");
    } else {
        NSLog(@"更新失败");
    }

Snip20170815_7.png

4.删除

    NSString *deleteSql = @"delete from T_Person where id = 2";
    BOOL resultDelete = [db executeUpdate:deleteSql];
    if (resultDelete) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }

运行结果

Snip20170815_8.png