数据库存储

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


什么是数据库:
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