• SQLite数据操作


    1.向学生表中插入100条数据

    2.按条件查询学生数据

    3.修改学生数据

    4.删除学生数据

    import UIKit

    class ViewController: UIViewController {

        lazy var documentsPath:String={

            let paths=NSSearchPathForDirectoriesInDomains(.DocumentDirectory,.UserDomainMask,true)

            return paths.first!

        }()

        var db:COpaquePointer=nil

        var stmt:COpaquePointer=nil

        override func viewDidLoad() {

            super.viewDidLoad()

            createOrOpenDatabase()

            //createTable()

            //基本步骤

            //1.打开数据库

            //2.处理数据

            //3.关闭数据库

            

            //插入数据

            //insertStudents()

            //查询数据

            //queryStudents()

            //修改学生数据

            //updateStudent()

            //queryStudents()

            //删除学生数据

            deleteStudents()

            queryStudents()

            //关闭数据库

            sqlite3_close(db)

        }

    }

    extension ViewController{

        func createOrOpenDatabase(){

            print("(NSHomeDirectory())")

            let path:NSString = "(documentsPath)/test.sqlite3"

            let filename=path.UTF8String

            if sqlite3_open(filename,&db) != SQLITE_OK {

                print("create or open failed.......")

                sqlite3_close(db)

            }

        }

        func createTable(){

            let string:NSString = "create table if not exists Student(id integer primary key autoincrement,sno text,name text,score,integer)"

            let sql = string.UTF8String

            if sqlite3_exec(db,sql,nil,nil,nil) != SQLITE_OK{

                print("create table failed......")

                sqlite3_close(db)

            }

        }

        func insertStudents(){

            //sno: "1001~1100"

            //name: "[a-j][01-10]"

            //score: 0-100

            let strs1=["a","b","c","d","e","f","g","h","i","j"]

            let strs2=["01","02","03","04","05","06","07","08","09","10"]

            

            for i in 0..<100{

                let sno="(1001+i)"

                let name=strs1[i/10] + strs2[i%10]

                let score=i

                insertStudent(sno:sno,name:name,score:score)

            }

        }

        func insertStudent(sno sno:String,name:String,score:Int){

            //准备SQL语句

            let string:NSString="insert into Student(sno,name,score) values(?,?,?)"

            let sql=string.UTF8String

            

            //解析SQL文本语句

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

                sqlite3_close(db)

                print("(sno),insert failed......")

            }

            

            //绑定参数

            let csno=(sno as NSString).UTF8String

            let cname=(name as NSString).UTF8String

            

            sqlite3_bind_text(stmt,1,csno,-1,nil)

            sqlite3_bind_text(stmt,2,cname,-1,nil)

            sqlite3_bind_int(stmt,3,Int32(score))

            

            //执行SQL语句

            if sqlite3_step(stmt) == SQLITE_ERROR{

                sqlite3_close(db)

                print("(sno),insert failed......")

            }else{

                //释放资源

                sqlite3_finalize(stmt)

            }

        }

        func queryStudents(){

            //准备SQL语句

            let string:NSString="select sno,name,score from Student"

            //let string:NSString="select sno,name,score from Student where score > 60"

            //let string:NSString="select sno,name,score from Student where name like 'a%'"

            let sql=string.UTF8String

            //解析SQL文本语句

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

                sqlite3_close(db)

                print("query failed......")

                return

            }

            //执行SQL语句

            while sqlite3_step(stmt) == SQLITE_ROW{

                let csno = sqlite3_column_text(stmt,0)

                let sno = NSString(UTF8String:UnsafePointer(csno))!

                let cname = sqlite3_column_text(stmt,1)

                let name=NSString(UTF8String:UnsafePointer(cname))!

                let score=sqlite3_column_int(stmt,2)

                print("(sno),(name),(score)")

            }

            //释放资源

            sqlite3_finalize(stmt)

        }

        func updateStudent(){

            let string:NSString = "update Student set score = 100 where name like 'a%'"

            let sql = string.UTF8String

            

            if sqlite3_exec(db,sql,nil,nil,nil) != SQLITE_OK{

                sqlite3_close(db)

                print("update failed......")

            }

        }

        func deleteStudents(){

            let string:NSString="delete from Student where score <60"

            let sql = string.UTF8String

            

            if sqlite3_exec(db,sql,nil,nil,nil) != SQLITE_OK {

                sqlite3_close(db)

                print("delete failed......")

            }

        }

    }

  • 相关阅读:
    「联赛模拟测试33」题解
    分享几个基于vue的移动端框架
    11-15
    test
    联赛模拟测试20 C. Weed
    联赛模拟测试20 D. Drink
    联赛模拟测试24 联合权值·改
    联赛模拟测试21 表格
    近期的一些考试题目
    shell脚本执行错误 $' ':command not found
  • 原文地址:https://www.cnblogs.com/daochong/p/5211453.html
Copyright © 2020-2023  润新知