數(shù)據(jù)庫應(yīng)用

1.建工程，在工程中添加sql外包贺归，如圖所示,選擇第二個libsqlite3.tbd

2.創(chuàng)建一個橋接文件

command+n創(chuàng)建Header File淆两，在里面寫#import <sqlite3.h>

3.獲取document路徑

let doc=NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true)[0]
        let sqlPath=doc+"/Student.sqlite"
        print(sqlPath)

4.打開數(shù)據(jù)庫，如果沒有創(chuàng)建

//定義為全局變量
var db:OpaquePointer?=nil 
if sqlite3_open(sqlPath,&db) == SQLITE_OK {
            print("數(shù)據(jù)庫打開")
        }

5.創(chuàng)建表

let sql = "create table if not exists user (id integer , name text, age integer)"
        //1.數(shù)據(jù)庫 2.sql語句 3.執(zhí)行結(jié)果 4.參數(shù) 5.錯誤信息
        let resdult=sqlite3_exec(db, sql, nil,nil, nil)
        if resdult == SQLITE_OK {
            print("創(chuàng)建表成功")
        }else{
            print("創(chuàng)建表失敗")
        }

6.sql語句操作

//插入數(shù)據(jù)
        sqlite3_exec(db, "insert into user(id,name,age) values(1,'小明',12)",nil, nil, nil)
        if resdult==SQLITE_OK{
            print("插入成功")
        }else{
            print("插入失敗")
        }
        //更新
        let updateSql="update user set name='小粒' where name='小明'";
        let resdult1=sqlite3_exec(db, updateSql, nil, nil, nil)
        if resdult1==SQLITE_OK{
            print("更新成功")
        }else{
           print("更新失敗")
       }
        //刪除
        let deleteSql="delete from user where name='小粒'";
        let resdult2=sqlite3_exec(db,deleteSql, nil, nil, nil)
        if resdult2==SQLITE_OK{
           print("刪除成功")
        }else{
            print("刪除失敗")
        }
         //查找
         let searchSql="select * from user where name=?"
        //1.數(shù)據(jù)庫 2.sql 3.sql長度 -1 自動計算 4.伴隨指針 5.nil
        var stmt:OpaquePointer?=nil
        let resdult3=sqlite3_prepare_v2(db, searchSql,-1, &stmt, nil)
        //給拂酣？賦值
        //1.伴隨指針 2.秋冰？在第幾位 從1開始 3.綁定的值
        sqlite3_bind_int(stmt, 2, 1)
        //將SQLITE_TRANSIENT定義為全局變量
            private let SQLITE_TRANSIENT = unsafeBitCast(-1, to:     sqlite3_destructor_type.self)
        sqlite3_bind_text(stmt, 1, "小明", -1, SQLITE_TRANSIENT)
        if resdult3==SQLITE_OK{
                       print("預(yù)編譯成功")
                   }else{
                       print("預(yù)編譯失敗")
                    }
        //執(zhí)行sql
        while sqlite3_step(stmt) == SQLITE_ROW{
            //取值
            let id=sqlite3_column_int(stmt, 0)
            let cname=sqlite3_column_text(stmt, 1)
            let name=String(cString: cname!)
            let age=sqlite3_column_int(stmt,2)
            print("id=\(id),name=\(name),age=\(age)")
            //把對象裝到數(shù)組中
        }
        //釋放stmt
        sqlite3_finalize(stmt)
        //關(guān)閉數(shù)據(jù)庫
        sqlite3_close(db)