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];