SQLite之SQL语句构建

1、数据库链接(打开数据库)->传统方式创建表

 let path = Bundle.main.path(forResource: "test", ofType:".db")
 print(path!)
 let db = try! Connection(path!)
 try! db.execute("create table t_teacher(name text, email text)")

2、构建创建表SQL

    let users = Table("users")
    let id = Expression<Int>("id")
    let name = Expression<String>("name")
    let email = Expression<String?>("email")
    let sql = users.create { (t) in
          t.column(id)
          t.column(name)
          t.column(email)
    }
    print(sql)

3、执行动态SQL(主键，唯一，不能够为空)

try! db.run(sql)

4、插入数据

    let insert = users.insert(id <- 2, name <- "NSError", email <- "nserror@qq.com")
    try! db.run(insert)

5、更新数据 + 删除数据

更新所有行

  let update = users.update(email <- "dream@qq.com", name <- "andy")
  print(update.asSQL())
  try! db.run(update)

条件更新

 let filter = users.filter(name == "andy")
 try! db.run(filter.update(email <- "dream@qq.com"))

内容替换

  let filter1 = users.filter(id == 1)
  try! db.run(filter1.update(name <- name.replace("r", with: "i")))

删除所有

try! db.run(users.delete())

条件删除->where条件

let alice = users.filter(id == 1)
let delete = alice.delete()
print(delete.asSQL())
try! db.run(delete)

6、查询数据

查询所有

let seq:AnySequence = try! db.prepare(users)
for user in seq {
   //user->Row->表示一行数据
   print("name: \(user[name]), email: \(user[email])")
}

条件查询->子段查询->只查询某些字段

for user in try! db.prepare(users.select(name)) {
     print("name: \(user[name])")
 }

条件查询->"*"查询(所有字段)->通配符

  //查询所有特殊方法"*"->特殊字符
  for user in try! db.prepare(users.select(*)) {
      print("name: \(user[name])")
  }

查询子句

查询子句->filter->where条件

   let query = users.select(email).filter(name == "NSLog")
   print(query.asSQL())
   let rows = try! db.prepare(query)
   for user in rows {
       print("name: \(user[email])")
   }

查询子句->排序"order"

  let query = users.select(email, name).order(name.desc)
  print(query.asSQL())
  let rows = try! db.prepare(query)
  for user in rows {
     print("name: \(user[email]), phone:\(user[name])")
  }

查询子句->LIMIT->查询多条->指定查询数据行数

        let query = users.select(email).limit(50)
        print(query.asSQL())
        for user in try! db.prepare(query) {
            print("name: \(user[email])")
        }

查询子句->LIMIT->查询多条->指定查询数据开始位置行数

        let query = users.select(email).limit(1, offset: 1)
        print(query.asSQL())
        for user in try! db.prepare(query) {
            print("name: \(user[email])")
        }

查询子句->LIMIT->查询一条(根据表)->不能指定数量->只有一条

        //注意：表中的第一条数据
        if let user = try! db.pluck(users) {
            print("phone: \(user[name])")
        }

查询子句->过滤数据->contains

        //注意：必需是属性值全称(作为选项)
        let filter2 = users.filter(["Andy"].contains(name))
        print(filter2.asSQL())
        for user in try! db.prepare(filter2) {
            print("name: \(user[name])")
        }

查询子句->过滤数据->模糊查询->like

        //后缀：查询以".com"结尾的行数据
        //前缀：查询以"andy@"开头的行数据
        //中缀：查询以"@"中间条件的行数据
        //模糊查询：特殊表示符号"%"
        let filter3 = users.filter(name.like("%n%"))
        print(filter3.asSQL())
        for user in try! db.prepare(filter3) {
            print("name: \(user[name]), email: \(user[email])")
        }

查询子句->过滤数据->多条件查询->重载运算符->"&&"

        //例如：sql语句->select * from t_user where name = "Dream" and phone = 3
        //数据库框架中表示：&&->and
        let filter4 = users.filter( id == "1" && name == "NSError")
        print(filter4.asSQL())
        for user in try! db.prepare(filter4) {
            print("email: \(user[email])")
        }

查询子句->过滤数据->多条件查询->重载运算符->"||"

        //||->"或"满足其中之一即可->对应数据库->"or"
        let filter5 = users.filter(name == "love" || name == "NSLog")
        print(filter5.asSQL())
        for user in try! db.prepare(filter5) {
            print("email: \(user[email])")
        }

查询子句->过滤数据->多条件查询->重载运算符->">="

        let filter6 = users.filter(id >= "3")
        print(filter6.asSQL())
        for user in try! db.prepare(filter6) {
            print("email: \(user[email])")
        }

查询子句->过滤数据->多条件查询->重载运算符->"<="

        let filter7 = users.filter(id <= "2")
        print(filter7.asSQL())
        for user in try! db.prepare(filter7) {
            print("email: \(user[email])")
        }

查询子句->过滤数据->多条件查询->重载运算符->">"

        let filter8 = users.filter(name > "2")
        print(filter8.asSQL())
        for user in try! db.prepare(filter8) {
            print("email: \(user[email])")
        }

查询子句->过滤数据->多条件查询->重载运算符->"<"

        let filter9 = users.filter(name < "2")
        print(filter9.asSQL())
        for user in try! db.prepare(filter9) {
            print("email: \(user[email])")
        }

查询子句->过滤数据->多条件查询->重载运算符->"!="

        let filter10 = users.filter(name != "2")
        print(filter10.asSQL())
        for user in try! db.prepare(filter10) {
            print("email: \(user[email])")
        }

查询子句->过滤数据->大小写转换

        //  name.lowercaseString == "DREAM"->结果->hello
        //  uppercaseString->大小->小写
        let filter11 = users.filter(name.uppercaseString == "LOVE")
        print(filter11.asSQL())
        for user in try! db.prepare(filter11) {
            print("email: \(user[email])")
        }

查询子句->过滤数据->Table->where条件

        let filter12 = users.where(name == "love")
        print(filter12.asSQL())
        for user in try! db.prepare(filter12) {
            print("email: \(user[email])")
        }

查询子句->过滤数据->group分组

        let filter13 = users.group(name)
        print(filter13.asSQL())
        for user in try! db.prepare(filter13) {
            print("email: \(user[name])")
        }

查询函数->scalar

count->当前表有多少行数据

        //获取行数方法
        //scalar->执行查询函数
        let c = users.count
        print(c.asSQL())
        let count = try! db.scalar(c)

count->查询指定条件对应的数据行数

        let count = try! db.scalar(users.select(name.count))
        print(count)

max/min->最大值/最小值

        let max = try! db.scalar(users.select(name.max))
        print(max!)
        let min = try! db.scalar(users.select(name.min))
        print(min!)

avg/sum(动态类型)/total->平均值/总和/总和

        let average = try! db.scalar(users.select(id.average))
        print(average!)

distinct->有几个分类(不同个数)

        let count = try! db.scalar(users.select(name.distinct.count))
        print(count)

7、表管理 + 表约束条件 + 事务处理

重命名表名->首先：给Table增加rename方法

        try! db.run(users.rename(Table("t_user")))

添加表字段

      //1、第一步：新增添加字段方法
        let sex = Expression<String?>("sex")
        try! db.run(users.addColumn(sex))
        
        //表约束条件(国外开源项目->牛逼->借鉴了FMDB->演变过来的)
        let students = Table("students")
        //备注：查询增加
        let sid = Expression<Int>("sid")
        let sname = Expression<String>("sname")
        let semail = Expression<String>("semail")
        //1、添加主键
        //做一个模版->然后根据我的模版自己尝试去实现其他约束条件
        //什么是主键？->核心：唯一性，不能够存在相同的值->自动增长
        let ssql = students.create { (t) in
            t.column(sid, primaryKey: true)
            t.column(sname)
            t.column(semail)
        }
        try! db.run(ssql)

事务处理

        try! db.transaction {
            let insert = users.insert(id <- 5, name <- "efly", email <- "efly@qq.com")
            try! db.run(insert)
            try! db.run(users.insert(email <- "fan@qq.com"))
            //测试一下
            //思路
            //第一步：设置一个属性不能够为空->null->例如：name属性
            //提示：之前讲解添加约束条件方法
            //第二步：插入数据->事务管理->报错了
        }

SQLite之SQL语句构建

推荐阅读更多精彩内容