1 SQLite

數(shù)據(jù)庫的SQL語句分為三種分別是：DDL DML DQL

1.1 約束關(guān)鍵字

常見約束關(guān)鍵字(不區(qū)分大小寫)：

if not exists  如果不存在
if exists 如果存在
primary key 主鍵
autoincrement 自增
not null 不能為空
default 默認(rèn)值

常見數(shù)據(jù)庫數(shù)據(jù)類型

integer 整型
text 文本
real 浮點(diǎn)型

1.2 DDL語句

DDL語句一般是用來操作數(shù)據(jù)表的渗柿，對數(shù)據(jù)表進(jìn)行定義萝究、刪除和修改操作的。對應(yīng)的關(guān)鍵字分別為：create/drop/alter

SQL語句示例

//1 創(chuàng)建數(shù)據(jù)表（創(chuàng)建學(xué)生表t_student）
create table if not exists t_student(id integer primary key autoincrement,name text not null,age integer,score real default 60)

//2 刪除數(shù)據(jù)表 (刪除學(xué)生表t_student)
drop table if exists s_student

//3 修改數(shù)據(jù)表
//--3.1 修改數(shù)據(jù)表的名稱
alter table t_student rename to t_person
//--3.2 增加數(shù)據(jù)表字段
alter table t_stu add column telephone integer

1.3 DML語句

DML語句一般是用來操作數(shù)據(jù)表中的記錄的。對數(shù)據(jù)表的記錄進(jìn)行增刪改操作通稱為DML語句

SQL語句示例

//1 插入記錄
insert into t_stu(name,age,score) values('chmn',24,100)

//2 刪除記錄
delete from t_stu where name = 'chmn'
delete from t_stu where name is 'chmn'

//3 修改記錄
update t_stu set score = 99 where name = 'chmn'

1.4 DQL語句

該語句就是對數(shù)據(jù)表字段進(jìn)行查詢操作顶伞，可以多表查詢

<b style="color:red">
注意：

比如說逆趣，有一個學(xué)生表和一個分?jǐn)?shù)表。學(xué)生表里面的學(xué)號是固定的膝蜈，是學(xué)生表的主鍵鹅心。分?jǐn)?shù)表中也有一個No,它的取值由學(xué)生表里面的學(xué)號字段決定吕粗，那么就需要在分?jǐn)?shù)表中設(shè)置外鍵，外鍵為學(xué)生表的學(xué)號

</b>

SQL語句示例

//1 查詢所有
select * from t_stu
//2 多表查詢
select * from t_stu,t_score where t_stu.no = t_score_no
//3 排序order by 
select * from t_score order by score DESC  降序
//4 統(tǒng)計count average min max
select count(*) from t_score 
select sum(score) from t_score 

//5 分頁limit
select * from t_score limit 1,2   //跳過1條旭愧，取2條
select * from t_score limit 3 //跳過0條颅筋，取3條

2 代碼實現(xiàn)SQL語句

2.1 實現(xiàn)大致步驟

• 導(dǎo)入框架sqlite3.tdb,導(dǎo)入頭文件sqlite3.h
• 創(chuàng)建并打開數(shù)據(jù)庫 （sqlite3_open）
• 執(zhí)行sql語句 (sqlite3_exec)

2.2 代碼實現(xiàn)DDL語句

點(diǎn)擊屏幕后，打開數(shù)據(jù)庫并創(chuàng)建數(shù)據(jù)表t_person

    //1 打開數(shù)據(jù)庫
    //--1.1 數(shù)據(jù)庫路徑(后綴名任意输枯，一般為sqlite或者db)
    let path = "/Users/apple/Pictures/myDb.sqlite"
    //--1.2 定義數(shù)據(jù)庫指針议泵，用來指向打開的數(shù)據(jù)庫
    var db:COpaquePointer = nil
    //--1.3 打開數(shù)據(jù)庫(如果該路徑不存在，則創(chuàng)建后打開)
    if sqlite3_open(path, &db) == SQLITE_OK {
        print("數(shù)據(jù)庫打開成功")
    }else {
        print("數(shù)據(jù)庫打開失敗")
    }
    
    
    //2 創(chuàng)建數(shù)據(jù)表
    //--2.1 獲取SQL語句
    let sql = "create table if not exists t_person(name text,age integer)"
    //--2.2 執(zhí)行sql語句
    /*
      參數(shù)1 打開的數(shù)據(jù)庫
      參數(shù)2 執(zhí)行的sql語句
      參數(shù)3 回調(diào)
      參數(shù)4 回調(diào)的參數(shù)
      參數(shù)5 錯誤信息
    */
    if sqlite3_exec(db, sql, nil, nil, nil) == SQLITE_OK {
        print("數(shù)據(jù)表創(chuàng)建成功")
    }else{
        print("數(shù)據(jù)表創(chuàng)建失敗")
    }
    
    
    //3 刪除數(shù)據(jù)表
    let deleSql = "drop table if exists t_person"
    if sqlite3_exec(db, deleSql, nil, nil, nil) == SQLITE_OK {
        print("刪除表創(chuàng)建成功")
    }else{
        print("刪除表創(chuàng)建失敗")
    }

一般將其進(jìn)行封裝成工具類桃熄，直接使用

封裝的工具類 SqliteTool.swift

class SqliteTool: NSObject {


//設(shè)置單例
static let shareInstance = SqliteTool()

//定義數(shù)據(jù)庫指針先口，用來指向打開的數(shù)據(jù)庫
var db:COpaquePointer = nil

override init() {
    super.init()
    /**打開數(shù)據(jù)庫*/
    
     //1 打開數(shù)據(jù)庫
    let path = "/Users/apple/Pictures/myDb.sqlite"
    //打開數(shù)據(jù)庫(如果該路徑不存在，則創(chuàng)建后打開)
    if sqlite3_open(path, &db) == SQLITE_OK {
        print("數(shù)據(jù)庫打開成功")
        
    }else {
        print("數(shù)據(jù)庫打開失敗")
    }
}

/**執(zhí)行sql語句*/
func execute(sql:String) -> Bool
{
    return (sqlite3_exec(db, sql, nil, nil, nil) == SQLITE_OK)
}
     
/**創(chuàng)建table*/
func createTable()
{
    //獲取SQL語句
    let sql = "create table if not exists t_person(name text,age integer)"
    //執(zhí)行sql語句
    /*
    參數(shù)1 打開的數(shù)據(jù)庫
    參數(shù)2 執(zhí)行的sql語句
    參數(shù)3 回調(diào)
    參數(shù)4 回調(diào)的參數(shù)
    參數(shù)5 錯誤信息
    */
    if execute(sql){
        print("創(chuàng)建table成功")
    }else{
        print("創(chuàng)建table失敗")
    }
}


/**刪除table*/
func dropTable()
{
    //刪除數(shù)據(jù)表
    let sql = "drop table if exists t_person"
    if execute(sql){
        print("刪除table成功")
    }else{
        print("刪除table失敗")
    }
       
}
 

}

2.3 代碼實現(xiàn)DML語句

定義Person類蜻拨，有姓名和年齡兩個字段池充，將這個這個實例對象插入到上述的數(shù)據(jù)表中，做更新等操作

Person.swift增加以下對象方法

2.3.1 插入

/**插入到數(shù)據(jù)表*/
func insertTable()
{
    //1 常見sql
    let sql = "insert into t_person(name,age) values('\(self.name)',\(self.age))"
    //2 執(zhí)行sql語句
    if SqliteTool.shareInstance.execute(sql) {
        print("插入成功")
    }else{
        print("插入失敗")
    }
}

2.3.2 刪除

/**從數(shù)據(jù)表中刪除*/
func deleteFromTable()
{
    let sql = "delete from t_person where name = '\(self.name)'"
    
    if SqliteTool.shareInstance.execute(sql){
        print("刪除成功")
    }else{
        print("刪除失敗")
    }
    
}

2.3.3 修改

func updateTable()
{
    let sql = "update t_person set name = '\(self.name)',age = \(self.age)"
    if SqliteTool.shareInstance.execute(sql){
        print("更新成功")
    }else{
        print("更新失敗")
    }

}

2.3.4 綁定插入

• 到這里缎讼，我們所使用的插入都是直接使用的sqlite_exec方法實現(xiàn)的。其實坑匠，iOS提供了預(yù)處理語句來綁定插入血崭。而且，上面的sqlite_exec其實是對綁定插入的一次封裝。
• 綁定插入夹纫，其實就是首先提供預(yù)處理語句咽瓷，然后再綁定預(yù)處理語句中的參數(shù)，實現(xiàn)插入
• 一般地舰讹，綁定插入比直接使用sqlite_exec的效率高茅姜，因為sqlite_exec也是對綁定插入進(jìn)行的封裝

綁定插入的步驟

1、 創(chuàng)建sql語句月匣，參數(shù)用問號?表示

2 钻洒、根據(jù)sql創(chuàng)建預(yù)處理語句 sqlite3_prepare_v2

3、 分別綁定參數(shù) sqlite3_bind_text sqlite3_bind_int sqlite3_bind_Double

4锄开、 執(zhí)行預(yù)處理語句 sqlite3_step

5素标、 重置預(yù)處理語句 sqlite3_reset

6、 釋放預(yù)處理語句 sqlite3_finalize

<b style = "color:red">
一般用法:如果在大量數(shù)據(jù)插入中萍悴，1/2/7步只需要執(zhí)行一次头遭，中間循環(huán)執(zhí)行3~6步驟，這樣可以提高效率
</b>

/**綁定插入*/
func bindInsert()
{
    //1 創(chuàng)建預(yù)處理語句--可變參數(shù)用 癣诱？表示计维，固定寫法
    let db = SqliteTool.shareInstance.db //打開的數(shù)據(jù)庫
    let zSql = "insert into t_person(name,age,score) values(?,?,?)" //sql語句
    var ppStmt : COpaquePointer = nil //生成的處理語句-傳入地址
    //-參數(shù)4 ,取出字符串zSql的長度。-1表示自動計算
    //-參數(shù)5 ,表示zSql除去參數(shù)3指定的長度后剩下的語句
    if sqlite3_prepare_v2(db, zSql, -1, &ppStmt, nil) != SQLITE_OK {
        print("預(yù)處理失敗")
    }
    
    
    //2 綁定整型
    //-參數(shù)1撕予，表示上面創(chuàng)建好的預(yù)編譯語句
    //-參數(shù)2享潜，表示綁定zSql中的哪個問號表示的參數(shù)，是索引嗅蔬。從1開始的
    //-參數(shù)3剑按，對應(yīng)參數(shù)2的值
    sqlite3_bind_int(ppStmt, 2, 24)  //綁定age = 24
    
    //3 綁定浮點(diǎn)型
    sqlite3_bind_double(ppStmt, 3, 89.7) //綁定score = 89.7
    
    //4 綁定文本類型
    //-參數(shù)1 預(yù)處理語句
    //-參數(shù)2 綁定的索引
    //-參數(shù)3 需要綁定的值
    //-參數(shù)4 從參數(shù)3中取出多長的數(shù)據(jù)進(jìn)行綁定，-1表示自動計算
    //-參數(shù)5 對參數(shù)值的處理方法澜术，有以下兩種
    //-------SQLITE_STATIC :認(rèn)為參數(shù)是一個常量艺蝴，不會被釋放，不會做任何的引用 宏 0
    //-------SQLITE_TRANSIENT:會對參數(shù)進(jìn)行引用 它是一個宏鸟废，-1
    //-------進(jìn)行按位轉(zhuǎn)換猜敢，可以進(jìn)入頭文件查看，因為swift是不能有宏的盒延，所以要自己轉(zhuǎn)換
    //-------將該宏表示的-1轉(zhuǎn)換成sqlite3_destructor_type類型
    let SQLITE_TRANSIENT = unsafeBitCast(-1, sqlite3_destructor_type.self)
    sqlite3_bind_text(ppStmt, 1, "zhangsan", -1, SQLITE_TRANSIENT)
    
    
    
    //5 執(zhí)行預(yù)處理語句
    if sqlite3_step(ppStmt) == SQLITE_DONE {
        print("執(zhí)行成功")
    }else {
        print("執(zhí)行失敗")
    }
    
    
    //6 重置預(yù)處理語句-重置綁定
    sqlite3_reset(ppStmt)
    
    //7 釋放預(yù)處理語句
    sqlite3_finalize(ppStmt)
}

2.3.5綁定插入(sqlite3_step)與插入(sqlite3_exec)的效率分析

測試方法：

當(dāng)前執(zhí)行時刻的獲取方法 CFAbsoluteTimeGetCurrent()

1 使用綁定插入循環(huán)插入10000條數(shù)據(jù)缩擂，計算插入開始和完成的時間差

測試中，循環(huán)執(zhí)行上面代碼的3~6步添寺，計算時間差

2 創(chuàng)建插入sql,使用sqlite3_exec插入胯盯，計算時間差

3 比較兩個效率

結(jié)論：

效率差別有，但不是很大计露。整體插入大數(shù)據(jù)都會出現(xiàn)時間很長的情況博脑，亟待優(yōu)化

2.3.6 大數(shù)據(jù)插入的優(yōu)化

問題的原因

不管是綁定預(yù)處理執(zhí)行(sqlite3_step)還是直接sql執(zhí)行(sqlite3_exec)憎乙，當(dāng)進(jìn)行大數(shù)據(jù)操作的時候，都會出現(xiàn)耗時很長的情況叉趣。其原因：

1 sqlite3_exec是對sqlite3_step的封裝泞边，效率會低

2 sqlite3_step綁定時，雖然創(chuàng)建預(yù)處理語句疗杉，釋放預(yù)處理語句都只執(zhí)行一次阵谚。但是，在每次執(zhí)行數(shù)據(jù)庫操作前烟具，都會首先開啟一個“事務(wù)”梢什，執(zhí)行完一個操作后，提交一個“事務(wù)”净赴。循環(huán)執(zhí)行大量操作時绳矩，開啟事務(wù)和提交事務(wù)的過程會耗時非常大，這才是根本原因

優(yōu)化方法

1 使用綁定操作sqlite3_step

2 創(chuàng)建預(yù)處理語句和釋放預(yù)處理語句都保證只執(zhí)行一次

3 手動開啟事務(wù)和提交事務(wù)

代碼

在綁定參數(shù)之前玖翅，手動開啟事務(wù)

let sql = "begin transaction"
sqlite3_exec(sql)

在結(jié)束插入翼馆，釋放預(yù)處理語句之前，手動提交事務(wù)

let sql = "commit transaction"
sqlite3_exec(sql)

2.4 代碼實現(xiàn)DQL語句

查詢操作也分為兩種金度，一種是直接使用sqlite3_exec執(zhí)行sql語句查詢应媚；另一種是使用sqlite3_step執(zhí)行預(yù)處理語句查詢

2.4.1 sqlite3_exec直接執(zhí)行sql查詢

此時就需要使用到了sqlite3_exec的回調(diào)方法了，每查詢到一行就調(diào)用回調(diào)方法猜极，直到回調(diào)方法返回1或者直到查詢完成中姜，回調(diào)方法中的參數(shù)分別為

• 參數(shù)1 無意義，由slite3_exec的參數(shù)4傳入的跟伏，類似于上下文之類的
• 參數(shù)2 查詢到當(dāng)前行的列數(shù)(字段的個數(shù))
• 參數(shù)3 查詢到當(dāng)前行的字段值的數(shù)組 --- sqlite3_exec方法查詢到所有字段值都當(dāng)做文本類型
• 參數(shù)4 查詢到當(dāng)前行的字段名稱數(shù)組
• 返回值 表示是否終止查詢丢胚，我們自行控制。如果返回0受扳，表示一直查詢直到完全查出携龟；如果返回1，表示查詢終止勘高，不再查詢了

舉例：

func queryAll()
{
    //1 創(chuàng)建sql語句
    let sql = "select * from t_person"
    let db = SqliteTool.shareInstance.db //打開的數(shù)據(jù)庫
    //2 執(zhí)行查詢語句 -- 每查詢到一行數(shù)據(jù)就會調(diào)用這個回調(diào)方法
    sqlite3_exec(db, sql, { (firstValue, columnCount, columnValues, columnNames) -> Int32 in
        
        // 遍歷當(dāng)前行的所有字段(列)
        let count = Int(columnCount)
        for i in 0..<count {
            
            // 獲取當(dāng)前的列名（字段名）
            //---類型UnsafeMutablePointer<Int8>相當(dāng)于UnsafeMutablePointer<CChar> ,也相當(dāng)于char * 指向字符串
            let columnName = columnNames[i]
            //---轉(zhuǎn)換成字符串
            let columnNameStr = String(CString: columnName, encoding: NSUTF8StringEncoding)
            
            // 獲取當(dāng)前字段的值
            let columnValue = columnValues[i]
            let columnValues = String(CString: columnValue, encoding: NSUTF8StringEncoding)
            
            print(columnNameStr,columnValues)
            
        }
        return 0 //如果返回0峡蟋，就表示一直查詢，直到查詢結(jié)束华望；如果返回1蕊蝗，表示到當(dāng)前位置終止查詢
        
        }, nil, nil)
}

2.4.2 sqlite3_step執(zhí)行預(yù)處理語句查詢

主要步驟與綁定插入的步驟一樣，首先要創(chuàng)建預(yù)處理語句赖舟，再次綁定語句蓬戚，然后執(zhí)行語句，其次重置語句建蹄，最后釋放語句碌更。

執(zhí)行的結(jié)果集需要我循環(huán)獲取裕偿，它拿到的結(jié)果集是不僅僅是文本型洞慎，可以根據(jù)數(shù)據(jù)庫存儲的實際類型進(jìn)行解析

舉例：

func prepareQuaryAll()
{
    
    let sql = "select * from t_person"
    let db = SqliteTool.shareInstance.db
    var stmt : COpaquePointer = nil
    // 創(chuàng)建預(yù)處理語句
    if sqlite3_prepare_v2(db, sql, -1, &stmt, nil) != SQLITE_OK {
        print("預(yù)處理失敗")
        return
    }
    
    
    
    // 綁定 - 如果沒有需要綁定的參數(shù)痛单，可以省略
    
    // 執(zhí)行預(yù)處理語句 -- 每執(zhí)行一次就是一行數(shù)據(jù)，每行記錄（結(jié)果集）都放在stmt中
    while sqlite3_step(stmt) == SQLITE_ROW {
        
        // 獲取當(dāng)前行有多少列(字段)
        let columnCount = sqlite3_column_count(stmt)
        
        // 循環(huán)取出每個字段
        for i in 0..<columnCount {
            // 獲取當(dāng)前字段的字段名稱(當(dāng)前索引劲腿，第幾個字段)
            let columnName = sqlite3_column_name(stmt, i)
            let columnNameStr = String(CString: columnName, encoding: NSUTF8StringEncoding)
            
            
            // 獲取當(dāng)前字段的類型
            let type = sqlite3_column_type(stmt, i)
            
            // 根據(jù)類型取出對應(yīng)類型的值
            if type == SQLITE_TEXT { //如果是文本類型
                let value = sqlite3_column_text(stmt, i)
                let valueInt8 = UnsafePointer<CChar>(value)
                let valueStr = String(CString: valueInt8, encoding: NSUTF8StringEncoding)
                print(columnNameStr,valueStr)
            }else if type == SQLITE_INTEGER{ //如果是整型
                let value = sqlite3_column_int(stmt, i)
                print(columnNameStr,value)
            }else if type == SQLITE_FLOAT { //如果是浮點(diǎn)型
                let value = sqlite3_column_double(stmt, i)
                print(columnNameStr,value)
            }
        }
    }
    
}

3 事務(wù)Transaction(批量操作的優(yōu)化)

• 事務(wù)是并發(fā)控制的單位旭绒，是用戶定義的操作序列。事務(wù)里面的操作焦人，要么都做挥吵，要么都不做。
• 事務(wù)是一個不可分割的工作單位花椭。通過事務(wù)忽匈，可以把邏輯相關(guān)的一些操作綁定在一起
• 事務(wù)通常以begin transaction開始的；以commit transaction或者rollback transaction結(jié)束的
• 當(dāng)大量的操作執(zhí)行的時候矿辽，默認(rèn)情況下丹允，每個操作都會開啟一個事務(wù)(成功之后提交事務(wù)，失敗后回滾事務(wù))袋倔，頻繁的開啟事務(wù)操作會耗時很大
• 所有雕蔽，大量操作執(zhí)行的時候，我們將這些操作放在一個事務(wù)中宾娜，整體只開啟一個事務(wù)批狐，成功之后提交事務(wù)，失敗之后回滾事務(wù)前塔，提高效率嚣艇。

舉例：把插入的語句放到一個事務(wù)transaction中執(zhí)行

func insertOperation()
{
    //創(chuàng)建插入sql
    let sql1 = "insert into t_stu(name,age) values('chenhua',23)" //該數(shù)據(jù)庫存在
    let sql2 = "insert into t_stu2(name,age) values('lisi',24)"   //該數(shù)據(jù)庫不存在
    
    ///開啟事務(wù)
    SqliteTool.shareInstance.execute("begin transaction")
    
    //執(zhí)行操作
    let result1 = SqliteTool.shareInstance.execute(sql1)
    let result2 = SqliteTool.shareInstance.execute(sql2)
    
    //判斷結(jié)果，處理事務(wù)
    if result1 && result2 {
        print("提交")
        SqliteTool.shareInstance.execute("commit transaction")
    }else{
        print("回滾")
        SqliteTool.shareInstance.execute("rollback transaction")        
}

4 FMDB框架的使用

是一個面向?qū)ο蟮腟QLite框架华弓，對sqlite進(jìn)行了封裝食零。所以在導(dǎo)入框架后，需要添加框架依賴 sqlite3.0.tdb

4.1 創(chuàng)建數(shù)據(jù)庫并打開

    let path = "/Users/apple/Desktop/haha/fmdb.sqlite" 
    // 打開數(shù)據(jù)庫
    let db = FMDatabase(path: path)
    db.open()

4.2 創(chuàng)建數(shù)據(jù)表

    // 創(chuàng)建數(shù)據(jù)表
    let tableSql = "create table if not exists t_student(name text,age integer,score real)"
    if db.executeStatements(tableSql) {
        print("創(chuàng)建數(shù)據(jù)表成功")
    }else {
        print("創(chuàng)建數(shù)據(jù)表失敗")
    }

4.3 更新數(shù)據(jù)(增刪改使用executeUpdate方法)

    // 更新數(shù)據(jù)（增刪改都是用executeUpdate）
    let insertSql = "insert into t_student(name,age,score) values('jim',23,22)"
    if db.executeUpdate(insertSql, withArgumentsInArray: nil) {
        print("插入數(shù)據(jù)成功")
    }else{
        print("插入數(shù)據(jù)失敗")
    }

4.4 查詢數(shù)據(jù)（使用executeQuery方法）

    // 查詢數(shù)據(jù) -- 查詢的結(jié)果都放在結(jié)果集中
    let querySql = "select * from t_student"
    let resultSet = db.executeQuery(querySql, withArgumentsInArray: nil)
    //---循環(huán)取出里面的記錄
    while resultSet.next() {
        // 獲取當(dāng)前行的字段數(shù)（列數(shù)）
        let columnCount = resultSet.columnCount()
        // 獲取指定字段的值
        let name = resultSet.stringForColumn("name")
        let age  = resultSet.intForColumn("age")
        let score = resultSet.doubleForColumn("score")
        print(name,age,score)
    }

4.5 執(zhí)行事務(wù)

    // 開啟事務(wù)
    db.beginTransaction()
    
    // 執(zhí)行操作
    let insertSql1 = "insert into t_student(name,age,score) values('jim',23,22)"
    let insertSql2 = "insert into t_student2(name,age,score) values('jim',23,22)"
    let result1 = db.executeUpdate(insertSql1, withArgumentsInArray: nil)
    let result2 = db.executeUpdate(insertSql2, withArgumentsInArray: nil)
    
    // 提交或者回滾事務(wù)
    if result1 && result2
    {
        db.commit()
    }else{
        db.rollback()
    }

4.6 預(yù)處理語句

普通狀態(tài)下一樣该抒，不錯需要使用標(biāo)準(zhǔn)語法 用問號慌洪？代表參數(shù)

4.7 線程安全的操作數(shù)據(jù)庫

不能直接使用FMDataBase開啟db，然后執(zhí)行操作凑保。需要使用FMDatabaseQueue類冈爹，他在回調(diào)block中提供了db

    // 創(chuàng)建數(shù)據(jù)庫隊列
    let dbQueue = FMDatabaseQueue(path: path)
    
    // 在數(shù)據(jù)庫中操作
    dbQueue.inDatabase { (db:FMDatabase!) -> Void in
        
        //使用db執(zhí)行響應(yīng)的增刪改查操作
        db.executeUpdate(sql,nil)
          
    }