iOS開發之資料存放區之SQLite3（包括FMDB）

標籤：

1、概述

SQLite3是一款開源的嵌入式關係型資料庫，可移植性好、易使用、記憶體開銷小。

SQLite3是無類型的，意味著你可以儲存任何類型的資料到任意表的任意欄位中。比如下列的創表語句是合法的：

create table t_person(name, age);

為了保證可讀性，建議還是把欄位類型加上：

create table t_person(name text, age integer);

SQLite將資料劃分為以下幾種儲存類型：

integer : 整型值

real : 浮點值

text : 文本字串

blob : 位元據（比如檔案）

為了保持良好的編程規範、方便程式員之間的交流，編寫建表語句的時候最好加上每個欄位的具體類型

在iOS中使用SQLite3，首先要添加庫檔案libsqlite3.dylib和匯入主標頭檔。

2、增（創）表

格式：

（1）create table 表名 (欄位名1 欄位類型1, 欄位名2 欄位類型2, …) ;

（2）create table if not exists 表名 (欄位名1 欄位類型1, 欄位名2 欄位類型2, …) ;

例如：

create table t_student (id integer, name text, age inetger, score real)

3、刪表

格式：

（1）drop table 表名 ;

（2）drop table if exists 表名 ;

例如：

drop table t_student ;

4、插入資料（insert）

格式：

insert into 表名 (欄位1, 欄位2, …) values (欄位1的值, 欄位2的值, …) ;

例如：

insert into t_student (name, age) values (‘mj’, 10) ;

【備忘】資料庫中的字串內容應該用單引號 ’ 括住

5、更新資料（update）

格式：

update 表名 set 欄位1 = 欄位1的值, 欄位2 = 欄位2的值, … ;

例如：

update t_student set name = ‘jack’, age = 20 ;

【備忘】上面的樣本會將t_student表中所有記錄的name都改為jack，age都改為20。

6、刪除資料（delete）

格式：

delete from 表名 ;

例如：

delete from t_student ;

【備忘】上面的樣本會將t_student表中所有記錄都刪掉

7、條件陳述式

如果只想更新或者刪除某些固定的記錄，那就必須在語句後加上一些條件。

條件陳述式的常見格式：

where 欄位 = 某個值 ;   // 不能用兩個 =

where 欄位 is 某個值 ;   // is 相當於 =

where 欄位 != 某個值 ;

where 欄位 is not 某個值 ;   // is not 相當於 !=

where 欄位 > 某個值 ;

where 欄位1 = 某個值 and 欄位2 > 某個值 ;  // and相當於C語言中的 &&

where 欄位1 = 某個值 or 欄位2 = 某個值 ;  //  or 相當於C語言中的 ||

例如：

delete from t_student where age <= 10 or age > 30 ;

8、資料庫查詢（select）

格式：

（1）select 欄位1, 欄位2, … from 表名 ;

（2）select * from 表名;   //  查詢所有的欄位

例如：

select name, age from t_student ;

select * from t_student ;

select * from t_student where age > 10 ;  //  條件查詢

9、起別名

格式(欄位和表都可以起別名)：

（1）select 欄位1 別名 , 欄位2 別名 , … from 表名 別名 ;

（2）select 欄位1 別名, 欄位2 as 別名, … from 表名 as 別名 ;

（3）select 別名.欄位1, 別名.欄位2, … from 表名 別名 ;

例如：

select name myname, age myage from t_student ;

給name起個叫做myname的別名，給age起個叫做myage的別名

 

select s.name, s.age from t_student s ;

給t_student表起個別名叫做s，利用s來參考資料表中的欄位

10、計算記錄的數量

格式：

（1）select count (欄位) from 表名 ;

（2）select count ( * ) from 表名 ;

例如：

select count (age) from t_student ;

select count ( * ) from t_student where score >= 60;

11、排序

查詢出來的結果可以用order by進行排序：

（1）select * from t_student order by 欄位 ;

例如：

select * from t_student order by age ;

預設是按照升序排序（由小到大），也可以變為降序（由大到小）

（1）select * from t_student order by age desc ;  //降序

（2）select * from t_student order by age asc ;   //升序（預設）

也可以用多個欄位進行排序：

（1）select * from t_student order by age asc, height desc ;

先按照年齡排序（升序），年齡相等就按照身高排序（降序）

12、limit

使用limit可以精確地控制查詢結果的數量，比如每次只查詢10條資料。

格式：

select * from 表名 limit 數值1, 數值2 ;

例如：

select * from t_student limit 4, 8 ;

可以理解為：跳過最前面4條語句，然後取8條記錄

limit常用來做分頁查詢，比如每頁固定顯示5條資料，那麼應該這樣取資料：

第1頁：limit 0, 5

第2頁：limit 5, 5

第3頁：limit 10, 5

…

第n頁：limit 5*(n-1), 5

例如：

select * from t_student limit 7 ;

相當於select * from t_student limit 0, 7 ;

表示取最前面的7條記錄

13、簡單約束

建表時可以給特定的欄位設定一些約束條件，常見的約束有：

not null ：規定欄位的值不能為null

unique ：規定欄位的值必須唯一

default ：指定欄位的預設值

（建議：盡量給欄位設定嚴格的約束，以保證資料的規範性）

例如：

create table t_student (id integer, name text not null unique, age integer not null default 1) ;

name欄位不能為null，並且唯一

age欄位不能為null，並且預設為1

14、主鍵約束

如果t_student表中就name和age兩個欄位，而且有些記錄的name和age欄位的值都一樣時，那麼就沒法區分這些資料，造成資料庫的記錄不唯一，這樣就不方便管理資料。

良好的資料庫編程規範應該要保證每條記錄的唯一性，為此，增加了主鍵約束。

也就是說，每張表都必須有一個主鍵，用來標識記錄的唯一性。

主鍵簡介：

主鍵（Primary Key，簡稱PK）用來唯一地標識某一條記錄。

例如t_student可以增加一個id欄位作為主鍵，相當於人的身份證。

主鍵可以是一個欄位或多個欄位。

主鍵設計原則：

主鍵應當是對使用者沒有意義的

永遠也不要更新主鍵

主鍵不應包含動態變化的資料

主鍵應當由電腦自動產生

主鍵的聲明：

在創表的時候用primary key聲明一個主鍵：

create table t_student (id integer primary key, name text, age integer) ;

integer類型的id作為t_student表的主鍵

主鍵欄位：

只要聲明為primary key，就說明是一個主鍵欄位。

主鍵欄位預設就包含了not null 和 unique 兩個約束。

如果想要讓主鍵自動成長（必須是integer類型），應該增加autoincrement：

create table t_student (id integer primary key autoincrement, name text, age integer) ;

15、外鍵約束

利用外鍵約束可以用來建立表與表之間的聯絡。外鍵的一般情況是：一張表的某個欄位，引用著另一張表的主鍵欄位。

建立一個外鍵：

create table t_student (id integer primary key autoincrement, name text, age integer, class_id integer, constraint fk_t_student_class_id_t_class_id foreign key (class_id) (id)) ; references t_class

t_student表中有一個叫做fk_t_student_class_id_t_class_id的外鍵

這個外鍵的作用是用t_student表中的class_id欄位引用t_class表的id欄位

16、表串連查詢

什麼是表串連查詢：

需要聯合多張表才能查到想要的資料

表串連的類型（不要求掌握）：

（1）內串連：inner join 或者 join  （顯示的是左右表都有完整欄位值的記錄）

（2）左外串連：left outer join （保證左表資料的完整性）

例如：

查詢0316iOS班的所有學生：

select s.name,s.age from t_student s, t_class c where s.class_id = c.id and c.name = ‘0316iOS’;

17、建立、開啟、關閉資料庫（建立資料庫）

建立或開啟資料庫

// path為：~/Documents/person.db

sqlite3 *db;

int result = sqlite3_open([path UTF8String], &db); //注意傳入的是db地址

代碼解析：

（1）sqlite3_open()將根據檔案路徑開啟資料庫，如果不存在，則會建立一個新的資料庫。如果result等於常量SQLITE_OK，則表示成功開啟資料庫。

（2）sqlite3 *db：一個開啟的資料庫執行個體

（3）資料庫檔案的路徑必須以C字串(而非NSString)傳入，[path UTF8String]就是將NSString字串轉成C語言的Char型字串。等價於：path.UTF8String

關閉資料庫：

sqlite3_close(db);

18、執行不返回資料的SQL語句（在資料庫中建立表/刪除表/更新表）

執行創表語句：

char *errorMsg;  // 用來儲存錯誤資訊

char *sql = "create table if not exists t_person(id integer primary key autoincrement, name text, age integer);";

int result = sqlite3_exec(db, sql, NULL, NULL, &errorMsg);

代碼解析：

sqlite3_exec()可以執行任何SQL語句，比如創表、更新、插入和刪除操作。但是一般不用它執行查詢語句，因為它不會返回查詢到的資料。如果result等於常量SQLITE_OK，表示創表成功。

sqlite3_exec()還可以執行的語句：

①    開啟事務：begin transaction;

②    復原事務：rollback;

③    提交事務：commit;

19、帶預留位置插入資料（可以防sql注入）

char *sql = "insert into t_person(name, age) values(?, ?);";

sqlite3_stmt *stmt;

if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) == SQLITE_OK) {

    sqlite3_bind_text(stmt, 1, "母雞", -1, NULL);

    sqlite3_bind_int(stmt, 2, 27);

}

if (sqlite3_step(stmt) != SQLITE_DONE) {

    NSLog(@"插入資料錯誤");

}

sqlite3_finalize(stmt);

代碼解析：

sqlite3_prepare_v2()傳回值等於SQLITE_OK，說明SQL語句已經準備成功，沒有文法問題。

sqlite3_bind_text()：大部分綁定函數都只有3個參數：

（1）第1個參數是sqlite3_stmt *類型

（2）第2個參數指預留位置的位置，第一個預留位置的位置是1，不是0

（3）第3個參數指預留位置要綁定的值

（4）第4個參數指在第3個參數中所傳遞資料的長度，對於C字串，可以傳遞-1代替字串的長度

（5）第5個參數是一個可選的函數回調，一般用於在語句執行後完成記憶體清理工作

sqlite_step()：執行SQL語句，返回SQLITE_DONE代表成功執行完畢

sqlite_finalize()：銷毀sqlite3_stmt *對象

20、查詢資料（查詢表資料）

char *sql = "select id,name,age from t_person;";

sqlite3_stmt *stmt;//存放結果集

if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) == SQLITE_OK) {//返回SQLITE_OK代表sql語句沒有錯誤

    while (sqlite3_step(stmt) == SQLITE_ROW) {//返回SQLITE_ROW說明查詢到了資料

        int _id = sqlite3_column_int(stmt, 0);//id

        char *_name = (char *)sqlite3_column_text(stmt, 1); //name

        NSString *name = [NSString stringWithUTF8String:_name];

        int _age = sqlite3_column_int(stmt, 2); //age

        NSLog(@"id=%i, name=%@, age=%i", _id, name, _age);

    }

}

sqlite3_finalize(stmt);//關閉結果集

代碼解析：

（1）sqlite3_step()返回SQLITE_ROW代表遍曆到一條新記錄

（2）sqlite3_column_*()用於擷取每個欄位對應的值，第2個參數是欄位的索引，從0開始

21、FMDB

（1）概述

FMDB是iOS平台的SQLite資料庫架構。

FMDB以OC的方式封裝了SQLite的C語言API。

FMDB的優點：

使用起來更加物件導向，省去了很多麻煩、冗餘的C語言代碼。

對比蘋果內建的Core Data架構，更加輕量級和靈活。

提供了多安全執行緒的資料庫操作方法，有效地防止資料混亂。

FMDB的github地址：

https://github.com/ccgus/fmdb

點擊查看本地儲存的FMDB架構

（2）FMDB有三個主要的類

FMDatabase

一個FMDatabase對象就代表一個單獨的SQLite資料庫。

用來執行SQL語句。

 

FMResultSet

使用FMDatabase執行查詢後的結果集。

 

FMDatabaseQueue

用於在多線程中執行多個查詢或更新，它是安全執行緒的。

（3）開啟資料庫

通過指定SQLite資料庫檔案路徑來建立FMDatabase對象

FMDatabase *db = [FMDatabase databaseWithPath:path];

if (![db open]) {

    NSLog(@"資料庫開啟失敗！");

}

檔案路徑有三種情況：

（a）具體檔案路徑。

如果不存在會自動建立。

（b）Null 字元串@""

會在臨時目錄建立一個空的資料庫。

當FMDatabase串連關閉時，資料庫檔案也被刪除。

（c）nil

會建立一個記憶體中臨時資料庫，當FMDatabase串連關閉時，資料庫會被銷毀。

（4）執行更新

在FMDB中，除查詢以外的所有操作，都稱為“更新”：

create、drop、insert、update、delete等。

使用executeUpdate:方法執行更新：

- (BOOL)executeUpdate:(NSString*)sql, ...

- (BOOL)executeUpdateWithFormat:(NSString*)format, ...

- (BOOL)executeUpdate:(NSString*)sql withArgumentsInArray:(NSArray *)arguments

例如：

[db executeUpdate:@"UPDATE t_student SET age = ? WHERE name = ?;", @20, @"Jack"]

例如，開啟資料庫並建立新表：

@property (nonatomic, strong) FMDatabase *db;

實現代碼：

- (void)viewDidLoad

{

    [super viewDidLoad];

   

    // 0.獲得沙箱中的資料庫檔案名

NSString *filename

 = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:

@"student.sqlite"];

   

    // 1.建立資料庫執行個體對象

    self.db = [FMDatabase databaseWithPath:filename];

  

    // 2.開啟資料庫

    if ( [self.db open] ) {

        NSLog(@"資料庫開啟成功");

        // 創表

        BOOL result = [self.db executeUpdate:@"create table if not exists t_student (id integer primary key autoincrement, name text, age integer);"];

        if (result) {

            NSLog(@"創表成功");

        } else {

            NSLog(@"創表失敗");

        }

    } else {

        NSLog(@"資料庫開啟失敗");

    }

}

（5）執行查詢

查詢方法：

- (FMResultSet *)executeQuery:(NSString*)sql, ...

- (FMResultSet *)executeQueryWithFormat:(NSString*)format, ...

- (FMResultSet *)executeQuery:(NSString *)sql withArgumentsInArray:

(NSArray *)arguments

例如：

// 查詢資料

FMResultSet *rs = [db executeQuery:@"SELECT * FROM t_student"];

// 遍曆結果集

while ([rs next]) {

    NSString *name = [rs stringForColumn:@"name"];

    int age = [rs intForColumn:@"age"];

    double score = [rs doubleForColumn:@"score"];

}

【備忘】關閉資料庫[db close];

（6）FMDatabaseQueue

FMDatabase這個類是線程不安全的，如果在多個線程中同時使用一個FMDatabase執行個體，會造成資料混亂等問題。

為了保證安全執行緒，FMDB提供方便快捷的FMDatabaseQueue類。

FMDatabaseQueue的建立：

FMDatabaseQueue *queue =

[FMDatabaseQueue databaseQueueWithPath:path];

 [queue inDatabase:^(FMDatabase *db) {//從這個block中直接獲得開啟的資料庫執行個體

    [db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Jack"];

    [db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Rose"];

    [db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Jim"];

   

    FMResultSet *rs = [db executeQuery:@"select * from t_student"];

    while ([rs next]) {

        // …

    }

}];

（7）FMDatabaseQueue使用事務

[queue inTransaction:^(FMDatabase *db, BOOL *rollback) {

    [db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Jack"];

    [db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Rose"];

    [db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Jim"];

if(發現情況不對)

{

           *rollback = YES//復原事務

}

    FMResultSet *rs = [db executeQuery:@"select * from t_student"];

    while ([rs next]) {

        // …

    }

}];

當然也可以使用如下方法：

[self.queue inDatabase:^(FMDatabase *db) {

        // 開啟事務

        [db executeUpdate:@"begin transaction;"];//等價於[db beginTransaction];

 

        [db executeUpdate:@"update t_student set age = ? where name = ?;", @20, @"jack"];

        [db executeUpdate:@"update t_student set age = ? where name = ?;", @20, @"jack"];

       

        if (發現情況不對){

            // 復原事務

           [db executeUpdate:@"rollback transaction;"];//等價於[db rollback];

 

       }

       

        [db executeUpdate:@"update t_student set age = ? where name = ?;", @20, @"jack"];

       

        // 提交事務

       [db executeUpdate:@"commit transaction;"];//等價於[db commit];

    }];

iOS開發之資料存放區之SQLite3（包括FMDB）