数据库操作（增删改查+升级表结构）

时间：2021-09-09 08:31:35

这几天在做数据库，数据库的创建，表的创建和删除。表内数据的增删改查都是老生常谈的问题了。今天说数据库主要是刚建立数据库的时候需要注意些什么。

新建数据库时考虑到数据库需要升级的问题所以数据库需要判断app的版本号，这样就可以在升级app的时候同时升级数据库，从而防止数据库的升级遇到不可修复的bug。新建数据库语句如下：

NSString *pathDocuments = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
NSString *createPath = [NSString stringWithFormat:@"%@/DB", pathDocuments];
//db路径
self.dbPath = [NSString stringWithFormat:@"%@/database.sqlite",createPath,version];
if (![fileManager fileExistsAtPath:createPath]) {
            [fileManager createDirectoryAtPath:createPath withIntermediateDirectories:YES attributes:nil error:nil];
            [fileManager createFileAtPath:_dbPath contents:nil attributes:nil];
        }
//创建数据库
        qfdb = [FMDatabase databaseWithPath:_dbPath];

其实数据库的创建跟普通文件的创建一模一样，下面就应该是创建表了，而当数据库升级的时候实质上是升级表。所以下面就需要慎重了。
我这里为了使用统一的方法，创建不同的表使用同一个方法所以自己封装了下sql语句的拼接，代码如下

-(void)createTableInDataBase:(NSString*)tableName withAllKeys:(NSArray*)keys
{
//sql 语句
if ([qfdb open]) {
NSString *createSqlStr = @"";
for (NSString *sqfStr in keys) {
if ([createSqlStr isNotEmpty]) {
                {
if ([sqfStr isEqualToString:@"articleid"] && [tableName isEqualToString:@"have_read_articles"]) {
                    createSqlStr = [NSString stringWithFormat:@"'%@' TEXT PRIMARY KEY,",sqfStr];
                }
else
                {
                    createSqlStr = [NSString stringWithFormat:@"'%@' TEXT,",sqfStr];
                }
        }
        createSqlStr = [createSqlStr substringToIndex:createSqlStr.length - 1];
NSString *sqlCreateTable;
if ([tableName isEqualToString:@"have_load_articles"]) {
            sqlCreateTable =  [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS '%@' ('id' INTEGER PRIMARY KEY AUTOINCREMENT, %@)",tableName,createSqlStr];
        }
else
        {
            sqlCreateTable =  [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS '%@' ('id' INTEGER identity(1,1), %@)",tableName,createSqlStr];
        }
BOOL res = [qfdb executeUpdate:sqlCreateTable];
if (!res) {
NSLog(@"创建qfdb失败!");
        } else {
NSLog(@"创建qfdb成功!");
        }
        [qfdb close];
    }
}

上面就是根据传入的数组来新建表以及表结构的。然后就是插入数据了，代码如下：

-(void)inseartIntoTable:(NSString*)tableName withKeyAndValues:(NSArray*)list
{
if ([qfdb open]) {
NSString *inseartSqlKeyStr = @"";
NSString *inseartSqlValueStr = @"";
for (NSDictionary *sqfDic in list) {
if ([inseartSqlKeyStr isNotEmpty]) {
NSString *keyStr = sqfDic[@"key"];
NSString *valueStr = sqfDic[@"value"];
                inseartSqlKeyStr = [inseartSqlKeyStr stringByAppendingFormat:@"'%@',",keyStr];
                inseartSqlValueStr = [inseartSqlValueStr stringByAppendingFormat:@"'%@',",valueStr];
            }
else
            {
NSString *keyStr = sqfDic[@"key"];
NSString *valueStr = sqfDic[@"value"];
                inseartSqlKeyStr = [NSString stringWithFormat:@"'%@',",keyStr];
                inseartSqlValueStr = [NSString stringWithFormat:@"'%@',",valueStr];
            }
        }
        inseartSqlKeyStr = [inseartSqlKeyStr substringToIndex:inseartSqlKeyStr.length - 1];
        inseartSqlValueStr = [inseartSqlValueStr substringToIndex:inseartSqlValueStr.length - 1];
NSString *insertSql= [NSString stringWithFormat: @"INSERT INTO '%@' (%@) VALUES (%@)", tableName,inseartSqlKeyStr,inseartSqlValueStr];
BOOL res = [qfdb executeUpdate:insertSql];
if (!res) {
NSLog(@"插入数据失败!");
        } else {
NSLog(@"插入数据成功!");
        }
        [qfdb close];
    }
}

下面就是更新数据的方法，根据需要修改的条件去修改特定的表中的内容，代码如下：

-(void)updateDataInTable:(NSString*)tableName withAnalyseDic:(NSDictionary*)dict withChangedKeyAndValues:(NSArray*)list
{
if ([qfdb open]) {
NSString *updateSqlStr = @"";
for (NSDictionary *sqfDic in list) {
if ([updateSqlStr isNotEmpty]) {
NSString *keyStr = sqfDic[@"key"];
NSString *valueStr = sqfDic[@"value"];
                updateSqlStr = [updateSqlStr stringByAppendingFormat:@"%@ = '%@',",keyStr,valueStr];
            }
else
            {
NSString *keyStr = sqfDic[@"key"];
NSString *valueStr = sqfDic[@"value"];
                updateSqlStr = [updateSqlStr stringByAppendingFormat:@"%@ = '%@',",keyStr,valueStr];
            }
        }
        updateSqlStr = [updateSqlStr substringToIndex:updateSqlStr.length - 1];
NSString *updateSql = [NSString stringWithFormat:@"UPDATE '%@' SET %@ WHERE %@ = '%@'",tableName,updateSqlStr,dict[@"key"],dict[@"value"]];
BOOL res = [qfdb executeUpdate:updateSql];
if (!res) {
NSLog(@"更新数据库数据表失败!");
        } else {
NSLog(@"更新数据库数据表成功!");
        }
        [qfdb close];
    }
}

由于时间紧迫就简单写一个表的查询方法，不统一使用的方法来查询表内容了，不过依然可以借鉴，代码如下：

#pragma mark-查询表中的数据
-(NSArray*)selectRowsFromTable:(NSString*)tableName withselectedDic:(NSDictionary*)dict
{
NSMutableArray *resultArray = [NSMutableArray new];
if ([qfdb open]) {
NSString *selectSql =@"";
if ([dict isNotEmpty]) {
            selectSql = [NSString stringWithFormat:@"SELECT * FROM '%@' WHERE %@ = '%@' Order By id Desc Limit 15",tableName,dict[@"key"],dict[@"value"]];
        }
//查询结果
        FMResultSet *result = [qfdb executeQuery:selectSql];
//声明数组用于保存查询结果
NSMutableArray *resultArr = [NSMutableArray new];
while ([result next])
        {

NSString *aid = [result stringForColumn:@"aid"];
NSString *title = [result stringForColumn:@"title"];
NSString *public_url = [result stringForColumn:@"public_url"];
NSString *master_img = [result stringForColumn:@"master_img"];
NSString *look_num = [result stringForColumn:@"look_num"];
NSString *commend_num = [result stringForColumn:@"commend_num"];
NSString *atime = [result stringForColumn:@"atime"];
NSString *private_url = [result stringForColumn:@"private_url"];
NSString *durationInMMSS = [result stringForColumn:@"durationInMMSS"];
NSString *trace_id = [result stringForColumn:@"trace_id"];
NSString *acate = [result stringForColumn:@"acate"];
NSString *segement = [result stringForColumn:@"segement"];

NSDictionary *dict = [NSDictionary dictionaryWithObjectsAndKeys:(aid?aid:@""),@"aid",(title?title:@""),@"title",(public_url?public_url:@""),@"public_url",(master_img?master_img:@""),@"master_img",(look_num?look_num:@""),@"look_num",(commend_num?commend_num:@""),@"commend_num",(atime?atime:@""),@"atime",(private_url?private_url:@""),@"private_url",(durationInMMSS?durationInMMSS:@""),@"durationInMMSS",(trace_id?trace_id:@""),@"trace_id",(acate?acate:@""),@"acate",(segement?segement:@""),@"segement", nil];
            [resultArr addObject:dict];
        }
NSArray *arrayM = [MainRecommondModule mj_objectArrayWithKeyValuesArray:resultArr];
if (arrayM.count) {
for (NSInteger i=arrayM.count - 1; i>0; i--) {
                [resultArray addObject:arrayM[i]];
            }
        }
        [qfdb close];
    }
return resultArray;
}

最后就是删除数据了，代码如下：

-(BOOL)deleteRowFormTable:(NSString*)tableName withAnalyseDic:(NSDictionary*)dict
{
BOOL result;
if ([qfdb open]) {
NSString *selectSql =@"";
if ([dict isNotEmpty]) {
NSString *idStr = dict[@"id_key"]；
            selectSql = [NSString stringWithFormat:@"Delete from %@ WHERE id = '%@' ",tableName,idStr];
        }
        result = [qfdb executeUpdate:selectSql];
        [qfdb close];
    }
return result;
}
#pragma mark-清空表数据
-(BOOL)deleteAllRowsFormTable:(const char *)tableName
{
BOOL result;
if ([qfdb open]) {
NSString *selectSql = [NSString stringWithFormat:@"Delete from %s",tableName];
        result = [qfdb executeUpdate:selectSql];
        [qfdb close];
    }
return result;
}

最后上重点，现在先讲下思路，升级数据库，其实就是先根据原来的表新建一个新表，然后从旧表里面取出数据插入新表当中，然后再把旧表删除掉，这里所说的删除表是drop掉表而不是delete清空表内容。到这一步以后其实新表就建好了，如果我们查询，插入等sql语句不想改的话就需要将新建的表名称改回旧表名称了。这样的话就是新建旧表名的‘新’表，然后将刚刚的新表里面的内容插入到‘新’表之中。这样再把刚刚的新表drop掉就完全达到我们的目的了。
sql语句如下：

//新建修改字段后的表CREATE TABLE table_new(ID INTEGER PRIMARY KEY AUTOINCREMENT, Modify_Username text not null)
//从旧表中查询出数据 并插入新表INSERT INTO table_new SELECT ID,Username FROM table
//删除旧表DROP TABLE table

//新建修改字段后的表CREATE TABLE table(ID INTEGER PRIMARY KEY AUTOINCREMENT, Username text not null)
//从旧表中查询出数据 并插入新表INSERT INTO table SELECT ID,Username FROM table_new
//删除旧表DROP TABLE table_new

到此结束，当然还有更方便的方法，我上面的步骤需要六步，而还有其他的需要四步的方式。今天就到此了。
标签：

相关文章