OC中Sql的（代码和终端中的）怎删改查

一：代码操作
1：第一步，必须是打开数据库

#import "SQLManageCenter.h"
#import <sqlite3.h>

@interface SQLManageCenter()

@property(nonatomic,assign)sqlite3 * sql;


@end

SQLManageCenter * instance = nil;
@implementation SQLManageCenter


+(SQLManageCenter *)sharedCenter{
    if (instance == nil) {
        instance = [[SQLManageCenter alloc]init];
    }
    return instance;
}

-(void)openSql{
    
    NSString * filePath = [NSSearchPathForDirectoriesInDomains(NSDocumentationDirectory, NSUserDomainMask, YES).lastObject stringByAppendingString:@"sql"];
    NSLog(@"%@",filePath);
    
    
    if (sqlite3_open(filePath.UTF8String, &_sql) == SQLITE_OK) {
        NSLog(@"打开数据空成功");
        
    }
    
}

//创建一个表格
-(void)createTableWithRegisterType:(kRegisterType)registerType{
    NSString * createStr;
    
    
    if (registerType == kRegisterTypeStudent) {
        //创建学生表
        createStr = @"create table if not exists student(id integer primary key autoincrement,name text,acountText text,password text,hostelID text,icon blob)";
    }else{
        //创建管理员表
        createStr = @"create table if not exists Administrator(id integer primary key autoincrement,name text,acountText text,password text,hostelID text,icon blob)";
    }
    
    if (sqlite3_exec(_sql, createStr.UTF8String, NULL, NULL, NULL) == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else{
        NSLog(@"创建表失败");
    }
}

//插入数据
-(void)insertModel:(UserModel *)model RegisterType:(kRegisterType)registerType{
    NSString * insertStr;

    
    if (registerType == kRegisterTypeStudent) {
        //插入学生表
        insertStr = @"insert into student(name,acountText,password,hostelID,icon) values(?,?,?,?,?)";
    }else{
        //插入管理员表
        insertStr = @"insert into Administrator(name,acountText,password,hostelID,icon) values(?,?,?,?,?)";
    }
    
    sqlite3_stmt * stmt = NULL;
    
    //预处理
    if (sqlite3_prepare(_sql, insertStr.UTF8String, -1, &stmt, NULL) == SQLITE_OK){
        NSLog(@"insert预处理成功");
        NSData * imageData = UIImagePNGRepresentation(model.headImage);
        //绑定数据
        sqlite3_bind_text(stmt, 1, model.nameText.UTF8String, -1, NULL);
        sqlite3_bind_text(stmt, 2, model.accountText.UTF8String, -1, NULL);
        sqlite3_bind_text(stmt, 3, model.passwordText.UTF8String, -1, NULL);
        sqlite3_bind_text(stmt, 4, model.hostelIdText.UTF8String, -1, NULL);
        sqlite3_bind_blob(stmt, 5, [imageData bytes], (int)imageData.length, NULL);
        
        //将宝绑定的数据写入到数据库
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            NSLog(@"保存数据成功");
        }else{
            NSLog(@"保存数据失败");
        }
    }else{
        NSLog(@"insert预处理失败");
    }
    sqlite3_finalize(stmt);
}


//判断密码正确与否 并返回除密码之外的Model
-(UserModel*)selectModelWithAcountText:(NSString *)acountText RegisterType:(kRegisterType)registerType{
    NSString * selectStr;
    
    NSArray * modelsArray = [NSArray new];
    modelsArray = [self selectModelsArrayWithRegisterType:registerType];
    
    //得到包含这个账号的model 或者 返回nil
    UserModel * model = [self isAcountText:acountText belongModelsArray:modelsArray];
    return model;
}



//查询所有数据
-(NSArray*)selectModelsArrayWithRegisterType:(kRegisterType)registerType{
    NSString * selectStr;
    if (registerType == kRegisterTypeAdministrator) {
        //查询管理员表
        selectStr = @"select name,acountText,password,hostelID,icon from Administrator";
    }else{
        //查询学生表
        selectStr =@"select name,acountText,password,hostelID,icon from student";
    }
    
    //创建一个保存Model的数组
    NSMutableArray * modelsArray = [[NSMutableArray alloc]init];
    NSLog(@"%@",selectStr);
    //预处理
    sqlite3_stmt * stmt = NULL;
    if (sqlite3_prepare(_sql, selectStr.UTF8String, -1, &stmt, NULL)  == SQLITE_OK) {
        NSLog(@"select预处理成功");
        //依次解析每一条数据
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            UserModel * model = [UserModel new];
            model.nameText = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 0)];
            model.accountText = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 1)];
            model.passwordText = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 2)];
            model.hostelIdText = [NSString stringWithUTF8String:(char*)sqlite3_column_text(stmt, 3)];
            
            NSData * imageData = [NSData dataWithBytes:sqlite3_column_blob(stmt, 4) length:sqlite3_column_bytes(stmt, 4)];
            model.headImage = [UIImage imageWithData:imageData];
            
            [modelsArray addObject:model];
        }
        
    }
    else{
        NSLog(@"select预处理失败");
    }
    sqlite3_finalize(stmt);
    return [modelsArray copy];
}


//判断数据库中是否包含这个账号

-(UserModel*)isAcountText:(NSString*)acountText belongModelsArray:(NSArray*)modelsArray{
    
    for (int i =0; i < modelsArray.count; i++) {
        UserModel * model = [modelsArray objectAtIndex:i];
        if ([model.accountText isEqualToString:acountText]  ==YES) {
            
            NSString * indexStr = [NSString stringWithFormat:@"%d",i];
            //记录着Model的Index值
            [[NSUserDefaults standardUserDefaults] setObject:indexStr forKey:kModelIndexKey];
            return model;
        }
    }
    
    return nil;
}


//根据账号改密码
-(void)upDatePasswordForAcount:(NSString *)acountText WithNewPassword:(NSString *)newPassword RegisterType:(kRegisterType)registerType{
    NSString * upDateStr;
    if (registerType == kRegisterTypeAdministrator) {
        //改管理员密码
        upDateStr = [NSString stringWithFormat:@"update Administrator set password=%@ where acountText=%@",[NSString stringWithFormat:@"'%@'",newPassword],[NSString stringWithFormat:@"'%@'",acountText]];
    }else{
        //改学生密码
        upDateStr = [NSString stringWithFormat:@"update student set password=%@ where acountText=%@",[NSString stringWithFormat:@"'%@'",newPassword],[NSString stringWithFormat:@"'%@'",acountText]];
    }
    NSLog(@"%@",upDateStr);
    if (sqlite3_exec(_sql, upDateStr.UTF8String, NULL, NULL, NULL) == SQLITE_OK) {
        NSLog(@"密码更改成功");
    }else{
        NSLog(@"密码更改失败");
    }
}


@end

二：终端中操作：
1：打开数据库:终端中输入sqlite3 + 数据库的路劲
2：.table 查看表格
3.增删改查如下
在这里插入图片描述