IOS 資料庫管理系統(SQLite)

標籤：io   ar   os   使用   sp   for   strong   檔案   資料   

嵌入式資料庫

SQLite嵌入式資料庫優點

1.支援事件,不需要配置,不需要安裝,不需要管理員

2.支援發部分SQL92

3.完整的資料庫儲存在磁碟上面一個檔案,同一個資料庫檔案可以在不同機器上面使用,最大支援資料庫到2T

4.整個系統少於3萬行,少於250KB的記憶體佔用

Linux系統級的SQLite技術實現架構libsqlite3.0.dylib

開始使用SQLite  

1.引入<sqlite3.h>標頭檔

2.開啟資料庫

3.執行SQL命令

4.關閉資料庫

一下代碼展示整個資料庫的 開啟,關閉,增加,刪除,尋找,修改,

建立一個單例

#import <Foundation/Foundation.h>@class Student;@interface DataBaseHandle : NSObject//建立一個單例+(DataBaseHandle *)shareDB;//擷取Documents路徑- (NSString *)documentsPath;//開啟資料庫- (void)openDB;//關閉資料庫- (void)closeDB;//建立表- (void)createTable;//插入資訊- (void)insertStudent:(Student *)stu;//修改資訊- (void)updateMessage;//刪除資訊- (void)deleteMessage;//尋找全部- (void)selectAllStudent;//條件尋找- (void)selectWithSex:(NSString *)sex;@end

#import "DataBaseHandle.h"#import <sqlite3.h>#import "Student.h"//全域單利對象static DataBaseHandle *shareModle = nil;@implementation DataBaseHandle#pragma mark 實現單利+ (DataBaseHandle *)shareDB{    if (nil == shareModle)    {        shareModle = [[DataBaseHandle alloc] init];    }    return shareModle;}#pragma  mark 擷取Documents路徑- (NSString *)documentsPath{    NSString *documentPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];    return documentPath;}//在操作資料之前引入架構(sql...3.0)//聲明一個資料庫的對象static sqlite3 *db = nil;#pragma mark 開啟資料庫- (void)openDB{    //判斷資料庫是不是為空白    if (nil == db)    {        //與documents路徑拼接資料庫存放路徑(獲得存放路徑)        NSString *dbPath = [[self documentsPath] stringByAppendingString:@"/Student.sqlite"];        //根據路徑開啟資料庫,如該路徑下沒有資料庫,就自動建立一個資料庫        //開啟資料庫(c語言中的文法)        int result = sqlite3_open(dbPath.UTF8String, &db);        //判斷是否開啟資料庫成功        if (result == SQLITE_OK)        {            NSLog(@"資料庫開啟成功");        }        else        {            NSLog(@"資料庫開啟失敗");        }    }}#pragma mark 關閉資料庫- (void)closeDB{    int result = sqlite3_close(db);    if (result == SQLITE_OK)    {        //如果關閉成功,置為nill        db = nil;        NSLog(@"資料庫關閉成功");    }    else    {        NSLog(@"資料庫關閉失敗");    }}#pragma mark 建立表- (void)createTable{    //要建立表,就要使用SQL語句.    //SQL語句在這是以字串的形式存在的    NSString *createString = @"CREATE  TABLE  IF NOT EXISTS student (sid INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , sname TEXT, ssex TEXT, sage TEXT)";    //執行SQL語句   int result = sqlite3_exec(db, createString.UTF8String, NULL, NULL, NULL);        if (result == SQLITE_OK)    {        NSLog(@"建立表成功");    }    else    {        NSLog(@"建立失敗");    }    }#pragma mark 插入資訊- (void)insertStudent:(Student *)stu{    //準備插入語句    NSString *insertString = [NSString stringWithFormat:@"INSERT INTO student (sname,ssex,sage) VALUES ('%@','%@','%@')",stu.name,stu.sex,stu.age];    //執行語句    int result = sqlite3_exec(db, insertString.UTF8String, NULL, NULL, NULL);    if (result == SQLITE_OK)    {        NSLog(@"插入成功");    }    else    {        NSLog(@"插入失敗");    }            }#pragma mark 修改資料- (void)updateMessage{    //準備修改語句    NSString *undataString = @"UPDATE student SET ssex = '女' ,sname = '小夢' WHERE  sid = 10";    int result = sqlite3_exec(db, undataString.UTF8String, NULL, NULL, NULL);    if (result == SQLITE_OK)    {        NSLog(@"修改成功");    }    else    {        NSLog(@"修改失敗");    }    }#pragma  mark 刪除資料- (void)deleteMessage{    //準備刪除語句    NSString *deleteString = @"DELETE  FROM student WHERE sid = 1";    int result = sqlite3_exec(db, deleteString.UTF8String, NULL, NULL, NULL);    if (result == SQLITE_OK)    {        NSLog(@"delete OK");    }    else    {        NSLog(@"delete _NO");    }    }#pragma mark 尋找全部- (void)selectAllStudent{    //準備尋找語句    NSString *selectAll = @"SELECT *FROM student";    //建立伴隨指標    sqlite3_stmt *stmt = nil;        //預備執行    int result = sqlite3_prepare(db, selectAll.UTF8String, -1, &stmt, NULL);    if (result == SQLITE_OK)    {        //在沒有尋找完成之前一直迴圈執行        while (sqlite3_step(stmt) == SQLITE_ROW)        {            //取出sid(第0列)            NSInteger sid = sqlite3_column_int(stmt, 0);            //取出sname(第1列)            NSString *sname = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];            //取出ssex(第2列)            NSString *ssex = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];            //取出sage(第3列)            NSString *sage = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 3)];            NSLog(@"sid:%d sname:%@ ssex:%@ sage:%@",sid,sname,ssex,sage);                        }        sqlite3_finalize(stmt);    }    else    {        //如果尋找失敗,結束伴隨指標        sqlite3_finalize(stmt);        NSLog(@"尋找失敗");    }    }#pragma mark 條件尋找- (void)selectWithSex:(NSString *)sex{    //準備尋找語句    NSString *selecstString = @"SELECT * FROM student WHERE ssex = ?";    //建立伴隨指標    sqlite3_stmt *stmt = nil;    //預執行     int result = sqlite3_prepare(db, selecstString.UTF8String, -1, &stmt, NULL);        if (result == SQLITE_OK)    {        //綁定?的值        //"1"代表第一個問號"?"和哪個參數綁定        sqlite3_bind_text(stmt, 1, sex.UTF8String, -1, NULL);        while (sqlite3_step(stmt) == SQLITE_ROW)        {            NSInteger sid = sqlite3_column_int(stmt, 0);            NSString *sname = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];            NSString *ssex = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];            NSString *sage = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 3)];            NSLog(@"sid: %d sname: %@ ssex: %@ sage: %@",sid,sname,ssex,sage);                    }                    }    else    {        //關閉指標        sqlite3_finalize(stmt);        NSLog(@"查詢失敗");    }}@end

建立一個Student類

#import <Foundation/Foundation.h>@interface Student : NSObject@property(nonatomic,strong)NSString *name;@property(nonatomic,strong)NSString *age;@property(nonatomic,strong)NSString *sex;@end

在 ViewController.m中 ViewDidLoad 調用方法

    //列印路徑    NSLog(@"%@",[[DataBaseHandle shareDB] documentsPath]);    //開啟資料庫    [[DataBaseHandle shareDB] openDB];    //建立表    [[DataBaseHandle shareDB] createTable];        //插入資訊//    Student *stu = [[Student alloc] init];//    stu.name = @"小露";//    stu.age = @"29";//    stu.sex = @"女";//    [[DataBaseHandle shareDB] insertStudent:stu];    //修改//    [[DataBaseHandle shareDB] updateMessage];    //刪除//    [[DataBaseHandle shareDB] deleteMessage];    //尋找全部資訊//    [[DataBaseHandle shareDB] selectAllStudent];    //根據條件尋找資訊    [[DataBaseHandle shareDB] selectWithSex:@"女"];

IOS 資料庫管理系統(SQLite)