数据库

数据库：

需要导入：sqlite3

添加 删除 修改数据库中的数据都用的是：sqlite3_exec

查询数据库用的是：sqlite3_prepare_v2

// 创建一个sqlite3对象 以后都操作它
static sqlite3 *db = nil;

#pragma mark   1. 打开数据库
- (void)openDB {
    if (db == nil) {
        NSString *tempPath = NSTemporaryDirectory();
        NSLog(@"%@", tempPath);
        NSString *filePath = [tempPath stringByAppendingPathComponent:@"student.sqlite"];
        
        // 打开路径下的数据库
        int result = sqlite3_open(filePath.UTF8String, &db);
        if (result == SQLITE_OK) {
            NSLog(@"打开数据库成功");
            // 写sql语句(可用某些工具生成SQL语句)
            NSString *sql = @"CREATE  TABLE  IF NOT EXISTS student (number INTEGER PRIMARY KEY  NOT NULL , name TEXT, age INTEGER)";
            // 执行SQL语句
            int result2 = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
            if (result2 == SQLITE_OK) {
                NSLog(@"创建表格成功");
            }else
                NSLog(@"创建表格失败");
            
        }else
            NSLog(@"打开数据库失败");
    }
}

#pragma mark   2. 关闭数据库
- (void)closeDB {
    if (db != nil) {
        int result = sqlite3_close(db);
        if (result == SQLITE_OK) {
            NSLog(@"关闭数据库成功");
            // 同时把数据库对象置空
            db = nil;
        }else
            NSLog(@"关闭数据库失败");
    }
}


#pragma mark   3. 添加数据
- (void)insertStudent:(Student *)stu {
    // 添加的sql语句
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO student (number, name, age) VALUES ('%@', '%@', '%d')", stu.number, stu.name, stu.age];
    
    
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"添加数据成功");
    }else
        NSLog(@"添加数据失败");
}
#pragma mark   4. 删除数据
- (void)deleteStudent:(NSString *)num {
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM student WHERE number = '%@'", num];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
         NSLog(@"删除数据成功");
    }else
         NSLog(@"删除数据失败");
}
#pragma mark   5. 更新数据
- (void)updateStudent:(Student *)stu {
    NSString *sql = [NSString stringWithFormat:@"UPDATE student SET name = '%@' , age = '%d' WHERE number = '%@'", stu.name, stu.age, stu.number];
    int result = sqlite3_exec(db, sql.UTF8String, nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"更新数据成功");
    }else
        NSLog(@"更新数据失败");
    
}

#pragma mark   6. 查询数据
- (NSMutableArray *)selectStudent {
    NSMutableArray *arrayAll = [NSMutableArray array];
    
    
    // 有条件的查询 直接加一个where语句就可以啦
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM student"];
    
    
    // 游标
    sqlite3_stmt *stmt = nil;
    // int nByte为-1时：自动计算sql语句的长度
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"开始查找数据");
        // 一行一行的去查找数据  直到找完
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            // 获取number
            const char *num1 = (const char *)sqlite3_column_text(stmt, 0);// 第二个参数递增
            NSString *number = [NSString stringWithCString:num1 encoding:NSUTF8StringEncoding];
            // 获取name
            const char *name1 = (const char *)sqlite3_column_text(stmt, 1);
            NSString *name = [NSString stringWithCString:name1 encoding:NSUTF8StringEncoding];
            // 获取age
            int age = sqlite3_column_int(stmt, 2);
            // 把取到的数据放到数据模型中
            Student *stu = [[Student alloc] init];
            stu.name = name;
            stu.number = number;
            stu.age = age;
            
            [arrayAll addObject:stu];
        }
    }else
        NSLog(@"查询失败");
    // 游标用完之后要释放：
    sqlite3_finalize(stmt);
    return arrayAll;
}

第三方FMDB 也类似，比这个简单 在“自己看的东西”里有提到