数据库

数据库包括:打开数据库,新建数据库表,插入,删除,更新,查询
先建一个student.h

#import <Foundation/Foundation.h>

@interface Student : NSObject

@property(nonatomic,copy)NSString *name;
@property(nonatomic,copy)NSString *sex;
@property(nonatomic,copy)NSString *hobby;
@property(nonatomic,assign)NSInteger number;

@end

创建一个数据库对象,为了保证该数据库对象在整个工程里是唯一的,通过单例模式进行创建
单例必须是+号方法,对方法名也有要求,比如要以share,main,standard,default,开头
常见单例比如:NSFileManager, NSUserDefaults, UIScreen, NSURLSession, UIMenuCountroller等
.h

+(instancetype)shareDataBaseHandle;

.m

+(instancetype)shareDataBaseHandle{
    ///第一种写法
//    static DataBaseHandle *dataBase;
//    if (!dataBase) {
//        dataBase=[[DataBaseHandle alloc] init];
//    }
//    return dataBase;

    ///第二种写法
    static DataBaseHandle *dataBase=nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        //这个方法只会执行一次
        dataBase=[[DataBaseHandle alloc] init];
    });
    return dataBase;

}

一. 打开数据库

-(void)openDB;

-(void)openDB{
    //找沙盒路径
    NSString *sandBoxPath=[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, 1, YES)lastObject];
    NSLog(@"%@",sandBoxPath);
    //拼接文件路径,如果数据库的文件没有的话,系统会自动创建一个
    NSString *dbPath=[sandBoxPath stringByAppendingPathComponent:@"Student.sqlite"];
    //根据路径去打开数据库
    int result = sqlite3_open([dbPath UTF8String], &dataBasePoint);
    if (result== SQLITE_OK) {
        NSLog(@"打开成功");
    }else{
        NSLog(@"%d",result);
    }

}

主函数调用

- (IBAction)openDB:(id)sender {
    [[DataBaseHandle shareDataBaseHandle] openDB];


}

二. 创建表 create table if not exists student(number integer primary key autoincrement, name text,sex text,hobby text

-(void)createTable;

-(void)createTable{
    NSString *sqlStr=@"create table if not exists student(number integer primary key autoincrement, name text,sex text,hobby text)";
    //执行sql语句
    int result=sqlite3_exec(dataBasePoint, [sqlStr UTF8String], nil, nil, nil);
    if (result==SQLITE_OK) {
        NSLog(@"创建表成功");
    }else{
        NSLog(@"创建表失败");
    }
}

主函数调用

- (IBAction)createTable:(id)sender {
    [[DataBaseHandle shareDataBaseHandle] createTable];

}

三. //添加一个学生 insert into student(name, sex, hobby) values (‘%@’,’%@’,’%@’)”,stu.name,stu.sex,stu.hobby

-(void)insertStu:(Student *)stu;

-(void)insertStu:(Student *)stu{
    NSString *sqlStr=[NSString stringWithFormat:@"insert into student(name, sex, hobby) values ('%@','%@','%@')",stu.name,stu.sex,stu.hobby];
    int result=sqlite3_exec(dataBasePoint, [sqlStr UTF8String], nil, nil, nil);
    if (result==SQLITE_OK) {
        NSLog(@"添加学生成功");
    }else{
        NSLog(@"失败");
    }

}

主函数调用

- (IBAction)insertStu:(id)sender {
    Student *stu=[[Student alloc] init];
    stu.name=@"李四";
    stu.sex=@"男";
    stu.hobby=@"学习";
    [[DataBaseHandle shareDataBaseHandle] insertStu:stu];

}

四. //删除 delete from student where name=’李四’

-(void)deleteStu:(Student *)stu;

-(void)deleteStu:(Student *)stu{
    NSString *sqlStr=[NSString stringWithFormat:@"delete from student where name='李四'"];
    int result=sqlite3_exec(dataBasePoint, [sqlStr UTF8String], nil, nil, nil);
    if (result==SQLITE_OK) {
        NSLog(@"删除学生成功");
    }else{
        NSLog(@"删除失败");
    }
}

主函数调用

- (IBAction)deleteStu:(id)sender {
    Student *stu=[[Student alloc] init];
    stu.name=@"李四";
    [[DataBaseHandle shareDataBaseHandle] deleteStu:stu];
}

五.//刷新 update student set name=’王五’ where number=’3’

-(void)updateStu:(Student *)stu;

-(void)updateStu:(Student *)stu{
    NSString *sqlStr=[NSString stringWithFormat:@"update student set name='王五' where number='3'"];
    int result=sqlite3_exec(dataBasePoint, [sqlStr UTF8String], nil, nil, nil);
    if (result==SQLITE_OK) {
        NSLog(@"刷新学生成功");
    }else{
        NSLog(@"刷新失败");
    }
}

主函数调用

- (IBAction)updateStu:(id)sender {
    Student *stu=[[Student alloc] init];
    stu.name=@"王五";
    stu.sex=@"男";
    stu.hobby=@"吃";
    [[DataBaseHandle shareDataBaseHandle] updateStu:stu];

}

六. //查询 select * from student

-(NSMutableArray *)selectAllStu;

-(NSMutableArray *)selectAllStu{
    //*代表是通配符,带表了所有的字段名
    NSString *sqlStr=@"select * from student";

    //需要创建一个跟随指针,用来便利查询数据库中表的每行数据
    sqlite3_stmt *stmt=nil;
    int result=sqlite3_prepare_v2(dataBasePoint, [sqlStr UTF8String], -1, &stmt, nil);
    NSMutableArray *arr=[NSMutableArray array];

    if (result==SQLITE_OK) {
        NSLog(@"查询成功");
        while (sqlite3_step(stmt)==SQLITE_ROW) {
            int number=sqlite3_column_int(stmt, 0);
            Student *stu=[[Student alloc] init];
            //把表里的值赋值给属性
            stu.number=number;
            //表里取文本内容
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            stu.name = [NSString stringWithUTF8String:(const char *)name];
            [arr addObject:stu];
        }
    }

    //注销跟随指针
    sqlite3_finalize(stmt);

    return arr;
}

调用

- (IBAction)selectStu:(id)sender {
    Student *stu=[[Student alloc] init];
    [[DataBaseHandle shareDataBaseHandle] selectAllStu];   
}

/////加(1) 查询number=65的学生

-(Student *)selectOneStu;

- (Student *)selectOneStu{
    Student *stu = [[Student alloc] init];
    /// SQL语句
    NSString *sqlStr = @"select * from student where number = '65'";
    /// 执行SQL语句
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(dataBasePoint, [sqlStr UTF8String], -1, &stmt, nil);
    if(result == SQLITE_OK){
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            /// 获得整形内容
            int number = sqlite3_column_int(stmt, 0);
            stu.number = number;
            /// 获得文本内容
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            stu.name = [NSString stringWithUTF8String:(const char *)name];

            const unsigned char *sex = sqlite3_column_text(stmt, 2);
            stu.sex = [NSString stringWithUTF8String:(const char *)sex];

            const unsigned char *hobby = sqlite3_column_text(stmt, 3);
            stu.hobby = [NSString stringWithUTF8String:(const char *)hobby];
        }
    }
    /// 注销跟随指针
    sqlite3_finalize(stmt);
    return stu;
}

/////加(2)找到多个名字叫张三的学生

-(NSInteger)selectByName;

-(NSInteger)selectByName{
    NSString *sqlStr=@"select * from student where name='张三'";
    sqlite3_stmt *stmt=nil;
    int result=sqlite3_prepare_v2(dataBasePoint, [sqlStr UTF8String], -1, &stmt, nil);
    NSInteger count =0;
    if (result == SQLITE_OK) {
        NSLog(@"查询成功");
        while (sqlite3_step(stmt)==SQLITE_ROW) {

                count++;

        }
    }
    // 注销跟随指针
    sqlite3_finalize(stmt);

    return count;
}