SQlite

数据库 SQlite

sqlite 多表间通过主键和外键联系

打开数据库：
int result = sqlite3_open(self.path.UTF8String, &db);
if (result == SQLITE_OK) {
return db;
}

关闭数据库：
if (db) {
NSLog(@”关闭”);
sqlite3_close(db);
}

创建数据库：
char *sql = “create table if not exists Student (name text,age integer ,number integer primary key)”;

int result = sqlite3_exec(db, sql, nil, nil, nil);
if (SQLITE_OK == result) {
    NSLog(@"creat successful");
}else{
    NSLog(@"creat fail");
}

[self closeDataBase:db];

插入数据（添加数据）:
sqlite3 *db = [self openDataBase];

char *sql = "insert into Student(name,age,number) values(?,?,?)";
sqlite3_stmt *stmt;

int result = sqlite3_prepare_v2(db, sql, -1, &stmt, nil);

if (SQLITE_OK == result) {
    sqlite3_bind_text(stmt, 1, student.name.UTF8String, -1, nil);
    sqlite3_bind_int(stmt, 2, (int)student.age);
    sqlite3_bind_int(stmt, 3, (int)student.number);

    //逐步执行
    int state = sqlite3_step(stmt);

    if (SQLITE_DONE == state) {
        NSLog(@"insert successful");
    }else{
        NSLog(@"insert fail");
    }
}

sqlite3_free(stmt);

[self closeDataBase:db];

更新数据：
sqlite3 *db = [self openDataBase];

NSString *sql = [NSString stringWithFormat:@"update Student set name = ?,age = ? where number is %ld",student.number];
sqlite3_stmt *stmt = nil;
int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
if (SQLITE_OK == result) {

    //第二个参数代表的是第几个问号(从一开始)
    sqlite3_bind_text(stmt, 1, student.name.UTF8String, -1, nil);
    sqlite3_bind_int(stmt, 2, (int)student.age);

    int state = sqlite3_step(stmt);
    if (SQLITE_DONE == state) {
        NSLog(@"update successful");
    }else{
        NSLog(@"update fail");
    }

}
[self closeDataBase:db];

删除数据：
sqlite3 *db = [self openDataBase];

char *sql = "delete from Student where number is ?";
sqlite3_stmt *stmt = nil;
int result = sqlite3_prepare_v2(db, sql, -1, &stmt, nil);
if (SQLITE_OK == result) {
    sqlite3_bind_int(stmt, 1, (int)student.number);

    int state = sqlite3_step(stmt);
    if (SQLITE_DONE == state) {
        NSLog(@"delete successful");
    }else{
        NSLog(@"delete fail");
    }

}
sqlite3_free(stmt);
[self closeDataBase:db];

查询数据：
sqlite3 *db = [self openDataBase];

char *sql = "select *from Student";
sqlite3_stmt *stmt = nil;

int result = sqlite3_prepare_v2(db, sql, -1, &stmt, nil);

NSMutableArray *dataArray = [NSMutableArray array];

if (SQLITE_OK == result) {

    while (SQLITE_ROW == sqlite3_step(stmt)) {

        //第二个参数代表的是表中的第几列(从0开始)
        char *name = (char *)sqlite3_column_text(stmt, 0);
        int age = sqlite3_column_int(stmt, 1);
        int number = sqlite3_column_int(stmt, 2);

        Student *student = [[Student alloc]init];
        student.name = [NSString stringWithUTF8String:name];
        student.age = age;
        student.number = number;

        [dataArray addObject:student];

    }

}

[self closeDataBase:db];

if (dataArray.count != 0) {
    NSLog(@"select successful");
}else{
    NSLog(@"select fail");
}

return dataArray;

删除表：
sqlite3 *db = [self openDataBase];

char *sql = "drop table Student";

int result = sqlite3_exec(db, sql, nil, nil, nil);

if (result == SQLITE_OK) {
    NSLog(@"drop successful");
}else{
    NSLog(@"drop fail");
}

[self closeDataBase:db];