数据库 SQL

 总结今天所学习的数据库知识：

首先在AddressBook 工程里建4个属性

#import <Foundation/Foundation.h>

@interface AddressBook : NSObject

@property (nonatomic, copy) NSString *ADID;

@property (nonatomic, copy) NSString *name;

@property (nonatomic, copy) NSString *phone;

@property (nonatomic, assign) NSUInteger age;


@end

在DataBaseHandle.h 工程里写方法

单列

+ (DataBaseHandle *)shareDataBaseHandle;

然后在写数据库的方法

//打开数据库

- (void) openDB;

关闭数据库

- (void) closeDB;

创建表

- (void) cesateTable;

增加

- (void) insertIntoTable:(AddressBook *)AB;

删除

- (void) deleteFromTable:(NSString *)ADID;

修改

- (void) UpdateToTable:(NSString *)name toName:(NSString *)toName;

查询

- (AddressBook *)selectAlll;

条件查询

- (NSArray *)selectFromTableWhereName;(NSString *)name;

在DataBaseHandle.m 里面实现

<span style="font-size:18px;">#import "DataBaseHandle.h"
#import <sqlite3.h>
@implementation DataBaseHandle

+ (DataBaseHandle *)shareDataBaseHandle
{
    //单列类声明的对象，在第一次初始化之后 就会在静态去存在。 在程序结束之后才会被释放，不会第二次初始化；
    static dispatch_once_t onceToken;
    static DataBaseHandle *dataBH = nil;
    dispatch_once(&onceToken, ^{
        if (dataBH == nil) {
            dataBH = [[DataBaseHandle alloc]init];
        }
    });
    
        return dataBH;
}




//在静态去声明一个数据库的指针
static sqlite3 *db =nil;


//
- (void)openDB
{
    if (db != nil) {
        return;
    }
    
NSString *documentStr = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
    
    NSString *dbPath = [documentStr stringByAppendingString:@"/addressBook.sqlite"];
    NSLog(@"%@",dbPath);
    
    //在dbPath里面创建一个文件，并作为数据库，而且这一步做完之后 就已经初始化了数据库了 静态区的数据库已经不为nil了
    int result = sqlite3_open(dbPath.UTF8String, &db);
    
    if (result == SQLITE_OK) {
        NSLog(@"打开成功");
        
    }else{
    
        NSLog(@"打开失败");
    }
    
}


-(void)closeDB
{

    int result = sqlite3_close(db);
    if (result == SQLITE_OK) {
        NSLog(@"成功");
    }else{
    
        NSLog(@"失败");
    }

}

- (void)cesateTable
{
    //创建表的SQL语句 格式：CREATE TABLE +表明（字段 类型。。。字段 类型）
NSString *cresteStr  = @"CREATE TABLE AddressBook(ADID text PRIMARY KEY,name text,phone text, age integer)";

    //第一个参数：代表的是要在哪个数据库里执行或操作
    //第二个参数：代表的是要执行哪一条SQL语句
    //第三个参数：回调的函数
    //第四个参数：回调函数的一些函数
    //第五个参数; 错误信息
    
    
    int result = sqlite3_exec(db, cresteStr.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"成功");
    }else{
    
        NSLog(@"失败");
    }

}


- (void)insertIntoTable:(AddressBook *)AB

{
//插入数据sql语句
    NSString *insertSTR = [NSString stringWithFormat:@"INSERT INTO AddressBook(ADID,name,phone,age)VALUES('%@','%@','%@','%ld')",AB.ADID,AB.name,AB.phone,(unsigned long)AB.age];
    int result = sqlite3_exec(db, insertSTR.UTF8String, NULL,NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }else
    {
        NSLog(@"插入失败");
    
    }
    

}


- (void)deleteFromTable:(NSString *)ADID
{
    //删除的SQL语句
    //DELETE FROM +表名  WHERE 删除的数据引参数 ＝ 参数
    
    NSString *deleteStr = [NSString stringWithFormat:@"DELETE FROM AddressBook WHERE ADID = '%@'", ADID];
    int result = sqlite3_exec(db, deleteStr.UTF8String, NULL,NULL,NULL);
    if (result == SQLITE_OK) {
        NSLog(@"SC成功");
    }else
    {
        NSLog(@"SC失败");
        
    }
    
}




- (void)UpdateToTable:(NSString *)name toName:(NSString *)toName
{
    NSString *updateStr = [NSString stringWithFormat:@"UPDATE AddressBook SET name = '%@' WHERE name = '%@'",name ,toName];
    int resylt = sqlite3_exec(db, updateStr.UTF8String, NULL, NULL, NULL)   ;
    if (resylt == SQLITE_OK) {
        NSLog(@"SC成功");
    }else
    {
        NSLog(@"SC失败");

    }
}


- (NSArray *)selectAll
{
    //查询SQL语句
    //SELECT * FROM ＋ 表名
    
    NSMutableArray *dataArray = [[NSMutableArray alloc]init];
NSString *selectStr = @"SELECT * FROM AddressBook";

    //伴随指针（保存sql语句，保存查询出来的数据）
    sqlite3_stmt *stmt = nil;


    int result = sqlite3_prepare(db, selectStr.UTF8String, -1 , &stmt, NULL);
    if (result == SQLITE_OK) {
        //判断stmt里面是否有适合的数据（一整条的）
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            NSString *ADID = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)];
            NSString *name =[NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
             NSString *phone =[NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
            NSInteger age = sqlite3_column_int(stmt, 3);
            AddressBook *add  = [[AddressBook alloc]init];
            add.ADID = ADID;
            add.name = name;
            add.phone = phone;
            add.age = age;
            [dataArray addObject:add];
        }
    }
    //释放掉 伴随指针
    sqlite3_finalize(stmt);
    
//    for (AddressBook * arr in dataArray) {
//        NSLog(@"%@,%@,%@,%ld",arr.ADID,arr.name,arr.phone,arr.age);
//    }
    return dataArray;

}


-(NSArray *)selectFromTableWhereName:(NSString *)name
{
    //查询SQL语句
    //SELECT * FROM ＋ 表名 WHERE + 条件 ＋ ？
    NSMutableArray *dataArray = nil;
    
    NSString *selectStr = @"SELECT * FROM AddressBook WHERE name = ?";
    
       //伴随指针（保存sql语句，保存查询出来的数据）
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare(db, selectStr.UTF8String, -1 , &stmt, NULL);
    
    
    if (result == SQLITE_OK) {
        
        //判断stmt里面是否有适合的数据（一整条的）
        
        //填充查询语句里面的？
        sqlite3_bind_text(stmt, 1, name.UTF8String, -1, NULL);
        dataArray = [NSMutableArray array];
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            NSString *ADID = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)];
            NSString *name =[NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            NSString *phone =[NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
            NSInteger age = sqlite3_column_int(stmt, 3);
            AddressBook *add  = [[AddressBook alloc]init];
            add.ADID = ADID;
            add.name = name;
            add.phone = phone;
            add.age = age;
            [dataArray addObject:add];

        }
    }
    //释放掉 伴随指针
    sqlite3_finalize(stmt);
    
    for (AddressBook * arr in dataArray) {
        NSLog(@"%@",arr.name);
    }
    return dataArray;
    
}





@end


在ViewController.m里面打印出来
</span><pre name="code" class="objc"><span style="font-size:18px;">#import "ViewController.h"
#import "DataBaseHandle.h"
#import "AddressBook.h"
@interface ViewController ()

@end

@implementation ViewController

- (void)viewDidLoad {
    [super viewDidLoad];

    DataBaseHandle *data = [DataBaseHandle shareDataBaseHandle];
    [data openDB];
//    [data closeDB];（做其他操作时，先注销 关掉数据库方法 ）
    [data cesateTable];
  
    AddressBook *add = [[AddressBook alloc]init];
    add.ADID = [NSString stringWithFormat:@"%d%d",arc4random()% 1000,arc4random() % 2000];
    NSInteger index = arc4random() %200;
    if (index % 2) {
        add.name = @"小强";
        add.phone = @"10086";
        add.age = 18;
    }else
    {
        add.name = @"光头强";
        add.phone = @"100000";
        add.age = 28;
    
    }
    [data insertIntoTable:add];
        [data selectAll];
    [data selectFromTableWhereName:@"zhang2 san"];
    
    
    
    
}
</span>

今天学习的总结。有什么地方错的希望大神指导下，谢谢