数据库存储

主键:唯一性(不能重复),不能为空.
外键:关联表与表之间的联系


什么是数据库:
1.以一定方式存储在一起
2.能为多个用户共享
3.具有尽可能小的冗余度
4.与程序彼此独立的数据集合

数据库模型:
1.层次结构模型
2.网状结构模型
3.关系结构模型

SQL命令:
1.数据插入命令 insert
  insert into 表名(每一列名) values(每一列的值);
2.数据更新命令 update
  update 表名 set 列名=值 where 主键=值;
3.数据删除命令 delete
  delete from 表名 where 主键=值;
4.数据查询命令 select
  select * from 表名;
  select 主键=值 from 表名;

#import "StudentManager.h"#import <sqlite3.h>#import "Student.h"// 单例全局变量,默认为空static StudentManager *manager = nil;// 定义数据库指针对象static sqlite3 *dbPoint = nil;@implementation StudentManager// 创建一个单例+ (id)shareManager{    // 声明一个GCD全局变量    static dispatch_once_t onceToken;        // 块语法中的内容只执行一次,线程安全(单例安全)    dispatch_once(&onceToken, ^{        // 保证内存唯一        if (manager == nil) {            manager = [[StudentManager alloc] init];        }    });    return manager;    [UIDevice currentDevice];    [UIApplication sharedApplication];    [NSUserDefaults standardUserDefaults];    [NSNotificationCenter defaultCenter]; // 消息中心}// 创建并打开数据库文件- (void)openWithTable:(NSString *)tableName{    self.tablename = tableName;    NSString *path = [StudentManager documentsWithName:@"Student.sqlite"];        // 打开数据库并获得数据库对象,如没有则创建    int result = sqlite3_open([path UTF8String], &dbPoint);    if (result != SQLITE_OK) {        UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"提示" message:@"数据库打开失败" delegate:self cancelButtonTitle:@"确定" otherButtonTitles:nil, nil];        [alert show];        [alert release];    } else {        NSLog(@"打开成功");    }        [self createTable:tableName];}// 关闭数据库- (void)close{    sqlite3_close(dbPoint);    NSLog(@"关闭成功");}// 根据传入的表名创建表- (void)createTable:(NSString *)tableName{        NSString *sqlStr = [NSString stringWithFormat:@"create table %@ (stuid int,stuname text,stuscore float)",tableName];    // 执行SQL语句 (数据库指针,转换后的SQL语句,)    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);    if (result == SQLITE_OK) {        UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"提示" message:@"表创建成功" delegate:nil cancelButtonTitle:@"确定" otherButtonTitles:nil, nil];        [alert show];        [alert release];    }}- (void)insertWithStudent:(Student *)stu{    NSString *sqlStr = [NSString stringWithFormat:@"insert into %@ values(%d,'%@',%g)",self.tablename, stu.stuid, stu.stuname, stu.stuscore];    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);    if (result == SQLITE_OK) {        NSLog(@"插入成功");    }}// 删除数据- (void)deleteWithStudent:(Student *)stu{    NSString *sqlStr = [NSString stringWithFormat:@"delete from %@ where stuid=%d",_tablename, stu.stuid];    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);    if (result == SQLITE_OK) {        UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"提示" message:@"删除成功" delegate:nil cancelButtonTitle:@"确定" otherButtonTitles:nil, nil];        [alert show];        [alert release];    }}// 更新数据- (void)updateWithStudent:(Student *)stu{    NSString *sqlStr = [NSString stringWithFormat:@"update %@ set stuscore=%g where stuid=%d",self.tablename, stu.stuscore, stu.stuid];    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);    if (result == SQLITE_OK) {        UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"提示" message:@"修改成功" delegate:nil cancelButtonTitle:@"确定" otherButtonTitles:nil, nil];        [alert show];        [alert release];    }    }//查询数据- (NSArray *)selectStuWithScore:(float)score{    NSString *sqlStr = [NSString stringWithFormat:@"select * from %@ where stuscore>=%g",self.tablename,score];    // 数据库对象的替身    sqlite3_stmt *stmt = nil;    // (数据库指针,转换后的SQL语句,SQL语句长度范围,查询结果,哪句SQL不用)    int result = sqlite3_prepare_v2(dbPoint, [sqlStr UTF8String], -1, &stmt, NULL);    NSMutableArray *arr = [NSMutableArray array];    if (result == SQLITE_OK) {        // 查找是否有下一行数据,并且判断,如果有了,取出来放到数组中        while (sqlite3_step(stmt) == SQLITE_ROW) {            int stuid = sqlite3_column_int(stmt, 0);            const unsigned char *stuname = sqlite3_column_text(stmt, 1);            float stuscore = sqlite3_column_double(stmt, 2);            Student *stu = [Student studentWithStuid:stuid stuname:[NSString stringWithUTF8String:(const char *)stuname] stuscore:stuscore];            [arr addObject:stu];        }    }    // 将替身清空    sqlite3_finalize(stmt);    for (Student * a in arr) {        NSLog(@"id===%d name ====%@ score ===== %g",a.stuid,a.stuname,a.stuscore);    }    return arr;}// 返回数据库文件路径+ (NSString *)documentsWithName:(NSString *)name{    NSString *doc = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];    NSString *path = [NSString stringWithFormat:@"%@/%@",doc,name];    NSLog(@"%@",path);    return path;}@end