随着版本的开发迭代,数据库中表的字段,不可避免的需要进行删减或者添加;那么,我们就从数据库的创建,使用、添加字段、删除字段四个方面,简单介绍一下;
1:简单使用
创建库

// 创建数据库
- (void)creatDB {
    NSString *docuPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
    NSString *dbPath = [docuPath stringByAppendingPathComponent:@"test.db"];
    NSLog(@"%@", dbPath);
    //2.创建对应路径下数据库
    self.db = [FMDatabase databaseWithPath:dbPath];
}

创建表

// 创建表
- (void)createTable {
    if ([self.db open]) {
        NSLog(@"数据库打开成功");
    };
    NSString *sql = @"create table if not exists t_student ('ID' INTEGER PRIMARY KEY AUTOINCREMENT,'name' TEXT NOT NULL, 'phone' TEXT NOT NULL,'score' INTEGER NOT NULL)";
   BOOL result = [self.db executeUpdate:sql];
   if (result) {
       NSLog(@"create table success");
   }else {
       NSLog(@"create table faile");
   }
    [self.db close];
}

插入数据

- (void)insertMessage {
    [self.db open];
    BOOL result = [self.db executeUpdate:@"insert into 't_student'(ID,name,phone,score) values(?,?,?,?)" withArgumentsInArray:@[@113,@"x3",@"13",@53]];
    if (result) {
        NSLog(@"insert into 't_studet' success");
    } else {
        NSLog(@"insert into 't_studet' faile");
    }
    [self.db close];
}

2:添加字段
方案:判断是否存在字段,如果不存在,则添加;

- (void)updateTable {
    if ([self.db open]) {
        NSLog(@"数据库打开成功");
    }
    // 判断表中是否包含某个字段
    if (![self.db columnExists:@"sex" inTableWithName:@"t_student"]) {
        NSString *update = [NSString stringWithFormat:@"ALTER TABLE %@ ADD %@ TEXT",@"t_student",@"sex"];
        BOOL worked = [self.db executeUpdate:update];
        if(worked){
            NSLog(@"添加成功");
        }else{
            NSLog(@"添加失败");
        }
    }
}

插入新数据:

- (void)insertNewMessage {
    [self.db open];
    BOOL result = [self.db executeUpdate:@"insert into 't_student'(ID,name,phone,score,sex) values(?,?,?,?,?)" withArgumentsInArray:@[@114,@"x3",@"13",@53,@"男"]];
    if (result) {
        NSLog(@"insert into 't_studet' success");
    } else {
        NSLog(@"insert into 't_studet' faile");
    }
    [self.db close];
}

3:删除字段
由于SQLite不支持字段的删除操作,故,只能使用表的复制方法:
1、先创建备份表tableA_backup将tableA数据备份
2、将原表tableA中的数据对应的复制到tableA_backup表中
3、此时实际已经得到了改过字段名的表:tableA_backup,然后将原表tableA删掉
4、然后创建表tableA
5、再把备份表tableA_backup中的数据复制到tableA中
6、把备份表tableA_backup删除
伪代码

// 3.删除旧字段(sqlite3无法使用DROP删除字段,先复制备份,再创建新的表)
    if (![db executeUpdate:@"BEGIN TRANSACTION"]) {
        DLog(@"BEGIN TRANSACTION失败");
    }
    NSString *table_backup = [NSString stringWithFormat:@"%@_backup", tableName];
    NSString *sql = [NSString stringWithFormat:@"CREATE TEMPORARY TABLE %@(%@)", table_backup, renamedColumnsAndTypes];
    if (![db executeUpdate:sql]) {
        DLog(@"CREATE TEMPORARY TABLE %@ 失败", table_backup);
    }
    if (![db executeUpdate:[NSString stringWithFormat:@"INSERT INTO %@ SELECT %@ FROM %@", table_backup, renamedColumnNames, tableName]]) {
        DLog(@"INSERT INTO %@ 失败", table_backup);
    }
    if (![db executeUpdate:[NSString stringWithFormat:@"DROP TABLE %@", tableName]]) {
        DLog(@"DROP TABLE %@ 失败", tableName);
    }
    if (![db executeUpdate:[NSString stringWithFormat:@"CREATE TABLE %@(%@)", tableName, renamedColumnsAndTypes]]) {
        DLog(@"CREATE TABLE %@ 失败", tableName);
    }
    if (![db executeUpdate:[NSString stringWithFormat:@"INSERT INTO %@ SELECT %@ FROM %@", tableName, renamedColumnNames, table_backup]]) {
        DLog(@"INSERT INTO %@ SELECT FROM %@ 失败", tableName, table_backup);
    }
    if (![db executeUpdate:[NSString stringWithFormat:@"DROP TABLE %@", table_backup]]) {
        DLog(@"DROP TABLE %@ 失败", table_backup);
    }
    if (![db executeUpdate:@"COMMIT"]) {
        DLog(@"COMMIT失败");
    }

参考网址:
iOS中SQLite3数据库修改字段名
iOS FMDB 理论与实践