数据库sqilite学习三（FMDB的使用）

01、打开数据库并新建一个表

- (BOOL)openDatabase{
    //1.获得数据库文件的路径
    NSString *path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)lastObject];
    NSString *sqlFilePath = [path stringByAppendingPathComponent:@"1234.sqlite"];
    //2.获得数据库
    FMDatabase *db=[FMDatabase databaseWithPath:sqlFilePath];
    self.db=db;
    
    //3.打开数据库
    if ([db open]) {
        BOOL result= [db executeUpdate:@"create table if not exists t_student (id integer primary key autoincrement, name text, age integer,useid text);"];
        NSLog(@"新建t_student成功");
        return result;
    }
    return NO;
}

02、插入数据(insert)

• 格式:

• insert into 表名(字段1，字段2，...)values(字段1的值，字段2的值,...)

• 示例：

• insert into t_student(useid,name)values('001'，'zhangsan',);

 -(void)insert
 {
      for (int i = 0; i<self.DataArray.count; i++) {
      Student *s =self.DataArray[i];
          
      BOOL sucess=[self.db executeUpdateWithFormat:@"INSERT INTO t_student  (useid,name,age) VALUES (%@,%@,%@);",s.useid,s.name,@(s.age)];
          
    //BOOL sucess=[self.db executeUpdate:@"INSERT INTO t_student (useid,name,age) VALUES (?,?,?);",s.useid , s.name,@(s.age)];//不确定的参数用?来占位
          
    // BOOL sucess=[self.db  executeUpdate:@"INSERT INTO t_student (useid,name,age) VALUES (?,?,?);" withArgumentsInArray:@[s.useid,s.name,@(s.age)]];
          
          if (sucess) {
              NSLog(@"插入成功");
          }
     }
 }

03、更新数据(update)

• 格式:
• update 表名 set 字段1 = 字段1的值，字段2 = 字段2的值,...;
• 示例：
• update t_student set name = 'jack',age = 20;
• update t_student set age = 20 where name = '张三';
• update t_student set name = 'jack',age is 20;
• update t_student set age = 20 where name != '张三';
• update t_student set name = 'jack',age is not 20;
• update t_student set age= 18 where name = '张三' and name = '李四'

 NSString *sqlite = @"update t_student set age = ?";
 BOOL result =    [_db executeUpdate:sqlite,@"30"];

04、删除数据(delete)

• 格式:
• delete from 表名;
• 示例：
• delete from t_student ;//删除所有行
• delete * from t_student ;//删除所有行
• delete from t_student where name = '张三'//删除某行

    NSString *sqlite = @"delete from t_student where name = ?";
     BOOL result =    [_db executeUpdate:sqlite,@"张三"];

05、查询数据

• 格式:
• select 字段1，字段2,... from 表名;
• select * from 表名；
• 示例：
• select * from t_student ;//查询所有的列
• select age from t_student where name = '张三'//查询某一列的某一字段

    NSMutableArray *array = [NSMutableArray new];
    FMResultSet *result = [_db executeQuery:@"select * from t_student"];
    while([result next]) {
     NSDictionary *dic = [result resultDictionary];
        [array addObject:dic];
    }

06、其他

/*分页*/
SELECT * FROM t_student ORDER BY id ASC LIMIT 30, 10;

/*排序*/
SELECT * FROM t_student WHERE score > 50 ORDER BY age DESC;
SELECT * FROM t_student WHERE score < 50 ORDER BY age ASC , score DESC;

/*计量*/
SELECT COUNT(*) FROM t_student WHERE age > 50;