Swift SQLite 使用

374

1.找到沙盒根目录

fileprivate let sqlitePath : String = NSSearchPathForDirectoriesInDomains(FileManager.SearchPathDirectory.documentDirectory, FileManager.SearchPathDomainMask.userDomainMask, true)[0] + "/"

2.链接数据库(初始化Connection对象)

   //无此数据库则创建
    let db = try? Connection(sqlitePath + "swift.sqlite")

初始化完成沙盒内出现swift.sqlite 文件

3.创建数据表

 let student = Table.init("Students")
        
        let id = Expression<Int>("id")
        
        let name = Expression<String>("name")
        
        let address = Expression<Int>("address")
      
         try? db?.run(student.create { t in
            t.column(id, primaryKey: true)
            t.column(name)
            t.column(address, unique: false)
        })
        

上面的语句等价于sq语句(unique 为独特的意思不能与其他数据重复)

CREATE TABLE IF NOT EXISTS "student" (
     "id" INTEGER PRIMARY KEY NOT NULL,
     "name" TEXT,
     "address" TEXT ,
)

链接数据库完成并创建Students表

屏幕快照 2017-10-31 下午2.59.25.png

4.插入数据

       let insert = student.insert(name <- "huang",address <- "河南")
        //返回此条数据的主健
       let rowid = try? db?.run(insert);

等价于sq语句

INSERT INTO "student" ("name", "address") VALUES ('huang', '河南')

屏幕快照 2017-10-31 下午3.17.24.png

5.查询数据表中的所有数据

 for item in (try? db?.prepare(student))!! {
            print("id: \(item[id]), name: \(item[name]), address: \(item[address])")
        }

等价于sq语句

SELECT * FROM "student"

屏幕快照 2017-10-31 下午3.19.22.png

6.根据条件查询数据

 //查询数据表(根据条件)
        for item in (try? db?.prepare(student.filter(id == 1)))!! {
            print("id: \(item[id]), name: \(item[name]), address: \(item[address])")
        }

7.更新某一条数据

 // 更新数据
        let update = student.filter(id == 1)
// “江苏”为旧数据  “上海”为新数据   如果旧数据错误则替换失败
        let resu = try! db?.run(update.update(address <- address.replace("江苏", with: "上海")))

8.删除某一条数据

  //删除某一条数据
        let data = student.filter(id==1)
        try! db?.run(data.delete())

9.数据库数据的个数

 //数据表的count
   let count = try? db?.scalar(student.count)