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......")
}
}
}