程序员

iOS数据库-Sqlite

1 SQLite

数据库的SQL语句分为三种分别是:DDL DML DQL

1.1 约束关键字

常见约束关键字(不区分大小写):

if not exists  如果不存在
if exists 如果存在
primary key 主键
autoincrement 自增
not null 不能为空
default 默认值

常见数据库数据类型

integer 整型
text 文本
real 浮点型

1.2 DDL语句

DDL语句一般是用来操作数据表的,对数据表进行定义、删除和修改操作的。对应的关键字分别为:create/drop/alter

SQL语句示例

//1 创建数据表(创建学生表t_student)
create table if not exists t_student(id integer primary key autoincrement,name text not null,age integer,score real default 60)

//2 删除数据表 (删除学生表t_student)
drop table if exists s_student

//3 修改数据表
//--3.1 修改数据表的名称
alter table t_student rename to t_person
//--3.2 增加数据表字段
alter table t_stu add column telephone integer

1.3 DML语句

DML语句一般是用来操作数据表中的记录的。对数据表的记录进行增删改操作通称为DML语句

SQL语句示例

//1 插入记录
insert into t_stu(name,age,score) values('chmn',24,100)

//2 删除记录
delete from t_stu where name = 'chmn'
delete from t_stu where name is 'chmn'

//3 修改记录
update t_stu set score = 99 where name = 'chmn'

1.4 DQL语句

该语句就是对数据表字段进行查询操作,可以多表查询


注意:

比如说,有一个学生表和一个分数表。学生表里面的学号是固定的,是学生表的主键。分数表中也有一个No,它的取值由学生表里面的学号字段决定,那么就需要在分数表中设置外键,外键为学生表的学号

SQL语句示例

//1 查询所有
select * from t_stu
//2 多表查询
select * from t_stu,t_score where t_stu.no = t_score_no
//3 排序order by 
select * from t_score order by score DESC  降序
//4 统计count average min max
select count(*) from t_score 
select sum(score) from t_score 

//5 分页limit
select * from t_score limit 1,2   //跳过1条,取2条
select * from t_score limit 3 //跳过0条,取3条

2 代码实现SQL语句

2.1 实现大致步骤

  • 导入框架sqlite3.tdb,导入头文件sqlite3.h
  • 创建并打开数据库 (sqlite3_open)
  • 执行sql语句 (sqlite3_exec)

2.2 代码实现DDL语句

点击屏幕后,打开数据库并创建数据表t_person

    //1 打开数据库
    //--1.1 数据库路径(后缀名任意,一般为sqlite或者db)
    let path = "/Users/apple/Pictures/myDb.sqlite"
    //--1.2 定义数据库指针,用来指向打开的数据库
    var db:COpaquePointer = nil
    //--1.3 打开数据库(如果该路径不存在,则创建后打开)
    if sqlite3_open(path, &db) == SQLITE_OK {
        print("数据库打开成功")
    }else {
        print("数据库打开失败")
    }


    //2 创建数据表
    //--2.1 获取SQL语句
    let sql = "create table if not exists t_person(name text,age integer)"
    //--2.2 执行sql语句
    /*
      参数1 打开的数据库
      参数2 执行的sql语句
      参数3 回调
      参数4 回调的参数
      参数5 错误信息
    */
    if sqlite3_exec(db, sql, nil, nil, nil) == SQLITE_OK {
        print("数据表创建成功")
    }else{
        print("数据表创建失败")
    }


    //3 删除数据表
    let deleSql = "drop table if exists t_person"
    if sqlite3_exec(db, deleSql, nil, nil, nil) == SQLITE_OK {
        print("删除表创建成功")
    }else{
        print("删除表创建失败")
    }

一般将其进行封装成工具类,直接使用

封装的工具类 SqliteTool.swift

class SqliteTool: NSObject {


//设置单例
static let shareInstance = SqliteTool()

//定义数据库指针,用来指向打开的数据库
var db:COpaquePointer = nil

override init() {
    super.init()
    /**打开数据库*/

     //1 打开数据库
    let path = "/Users/apple/Pictures/myDb.sqlite"
    //打开数据库(如果该路径不存在,则创建后打开)
    if sqlite3_open(path, &db) == SQLITE_OK {
        print("数据库打开成功")

    }else {
        print("数据库打开失败")
    }
}

/**执行sql语句*/
func execute(sql:String) -> Bool
{
    return (sqlite3_exec(db, sql, nil, nil, nil) == SQLITE_OK)
}

/**创建table*/
func createTable()
{
    //获取SQL语句
    let sql = "create table if not exists t_person(name text,age integer)"
    //执行sql语句
    /*
    参数1 打开的数据库
    参数2 执行的sql语句
    参数3 回调
    参数4 回调的参数
    参数5 错误信息
    */
    if execute(sql){
        print("创建table成功")
    }else{
        print("创建table失败")
    }
}


/**删除table*/
func dropTable()
{
    //删除数据表
    let sql = "drop table if exists t_person"
    if execute(sql){
        print("删除table成功")
    }else{
        print("删除table失败")
    }

}


}

2.3 代码实现DML语句

定义Person类,有姓名和年龄两个字段,将这个这个实例对象插入到上述的数据表中,做更新等操作

Person.swift增加以下对象方法

2.3.1 插入

/**插入到数据表*/
func insertTable()
{
    //1 常见sql
    let sql = "insert into t_person(name,age) values('\(self.name)',\(self.age))"
    //2 执行sql语句
    if SqliteTool.shareInstance.execute(sql) {
        print("插入成功")
    }else{
        print("插入失败")
    }
}

2.3.2 删除

/**从数据表中删除*/
func deleteFromTable()
{
    let sql = "delete from t_person where name = '\(self.name)'"

    if SqliteTool.shareInstance.execute(sql){
        print("删除成功")
    }else{
        print("删除失败")
    }

}

2.3.3 修改

func updateTable()
{
    let sql = "update t_person set name = '\(self.name)',age = \(self.age)"
    if SqliteTool.shareInstance.execute(sql){
        print("更新成功")
    }else{
        print("更新失败")
    }

}

2.3.4 绑定插入

  • 到这里,我们所使用的插入都是直接使用的sqlite_exec方法实现的。其实,iOS提供了预处理语句来绑定插入。而且,上面的sqlite_exec其实是对绑定插入的一次封装。
  • 绑定插入,其实就是首先提供预处理语句,然后再绑定预处理语句中的参数,实现插入
  • 一般地,绑定插入比直接使用sqlite_exec的效率高,因为sqlite_exec也是对绑定插入进行的封装

绑定插入的步骤

1、 创建sql语句,参数用问号?表示

2 、根据sql创建预处理语句 sqlite3_prepare_v2

3、 分别绑定参数 sqlite3_bind_text sqlite3_bind_int sqlite3_bind_Double

4、 执行预处理语句 sqlite3_step

5、 重置预处理语句 sqlite3_reset

6、 释放预处理语句 sqlite3_finalize


一般用法:如果在大量数据插入中,1/2/7步只需要执行一次,中间循环执行3~6步骤,这样可以提高效率

/**绑定插入*/
func bindInsert()
{
    //1 创建预处理语句--可变参数用 ?表示,固定写法
    let db = SqliteTool.shareInstance.db //打开的数据库
    let zSql = "insert into t_person(name,age,score) values(?,?,?)" //sql语句
    var ppStmt : COpaquePointer = nil //生成的处理语句-传入地址
    //-参数4 ,取出字符串zSql的长度。-1表示自动计算
    //-参数5 ,表示zSql除去参数3指定的长度后剩下的语句
    if sqlite3_prepare_v2(db, zSql, -1, &ppStmt, nil) != SQLITE_OK {
        print("预处理失败")
    }


    //2 绑定整型
    //-参数1,表示上面创建好的预编译语句
    //-参数2,表示绑定zSql中的哪个问号表示的参数,是索引。从1开始的
    //-参数3,对应参数2的值
    sqlite3_bind_int(ppStmt, 2, 24)  //绑定age = 24

    //3 绑定浮点型
    sqlite3_bind_double(ppStmt, 3, 89.7) //绑定score = 89.7

    //4 绑定文本类型
    //-参数1 预处理语句
    //-参数2 绑定的索引
    //-参数3 需要绑定的值
    //-参数4 从参数3中取出多长的数据进行绑定,-1表示自动计算
    //-参数5 对参数值的处理方法,有以下两种
    //-------SQLITE_STATIC :认为参数是一个常量,不会被释放,不会做任何的引用 宏 0
    //-------SQLITE_TRANSIENT:会对参数进行引用 它是一个宏,-1
    //-------进行按位转换,可以进入头文件查看,因为swift是不能有宏的,所以要自己转换
    //-------将该宏表示的-1转换成sqlite3_destructor_type类型
    let SQLITE_TRANSIENT = unsafeBitCast(-1, sqlite3_destructor_type.self)
    sqlite3_bind_text(ppStmt, 1, "zhangsan", -1, SQLITE_TRANSIENT)



    //5 执行预处理语句
    if sqlite3_step(ppStmt) == SQLITE_DONE {
        print("执行成功")
    }else {
        print("执行失败")
    }


    //6 重置预处理语句-重置绑定
    sqlite3_reset(ppStmt)

    //7 释放预处理语句
    sqlite3_finalize(ppStmt)
}

2.3.5绑定插入(sqlite3_step)与插入(sqlite3_exec)的效率分析

测试方法:

当前执行时刻的获取方法 CFAbsoluteTimeGetCurrent()

1 使用绑定插入循环插入10000条数据,计算插入开始和完成的时间差

测试中,循环执行上面代码的3~6步,计算时间差

2 创建插入sql,使用sqlite3_exec插入,计算时间差

3 比较两个效率

结论:

效率差别有,但不是很大。整体插入大数据都会出现时间很长的情况,亟待优化

2.3.6 大数据插入的优化

问题的原因

不管是绑定预处理执行(sqlite3_step)还是直接sql执行(sqlite3_exec),当进行大数据操作的时候,都会出现耗时很长的情况。其原因:

1 sqlite3_exec是对sqlite3_step的封装,效率会低

2 sqlite3_step绑定时,虽然创建预处理语句,释放预处理语句都只执行一次。但是,在每次执行数据库操作前,都会首先开启一个“事务”,执行完一个操作后,提交一个“事务”。循环执行大量操作时,开启事务和提交事务的过程会耗时非常大,这才是根本原因

优化方法

1 使用绑定操作sqlite3_step

2 创建预处理语句和释放预处理语句都保证只执行一次

3 手动开启事务和提交事务

代码

在绑定参数之前,手动开启事务

let sql = "begin transaction"
sqlite3_exec(sql)

在结束插入,释放预处理语句之前,手动提交事务

let sql = "commit transaction"
sqlite3_exec(sql)

2.4 代码实现DQL语句

查询操作也分为两种,一种是直接使用sqlite3_exec执行sql语句查询;另一种是使用sqlite3_step执行预处理语句查询

2.4.1 sqlite3_exec直接执行sql查询

此时就需要使用到了sqlite3_exec的回调方法了,每查询到一行就调用回调方法,直到回调方法返回1或者直到查询完成,回调方法中的参数分别为

  • 参数1 无意义,由slite3_exec的参数4传入的,类似于上下文之类的
  • 参数2 查询到当前行的列数(字段的个数)
  • 参数3 查询到当前行的字段值的数组 — sqlite3_exec方法查询到所有字段值都当做文本类型
  • 参数4 查询到当前行的字段名称数组
  • 返回值 表示是否终止查询,我们自行控制。如果返回0,表示一直查询直到完全查出;如果返回1,表示查询终止,不再查询了

举例:

func queryAll()
{
    //1 创建sql语句
    let sql = "select * from t_person"
    let db = SqliteTool.shareInstance.db //打开的数据库
    //2 执行查询语句 -- 每查询到一行数据就会调用这个回调方法
    sqlite3_exec(db, sql, { (firstValue, columnCount, columnValues, columnNames) -> Int32 in

        // 遍历当前行的所有字段(列)
        let count = Int(columnCount)
        for i in 0..相当于UnsafeMutablePointer ,也相当于char * 指向字符串
            let columnName = columnNames[i]
            //---转换成字符串
            let columnNameStr = String(CString: columnName, encoding: NSUTF8StringEncoding)

            // 获取当前字段的值
            let columnValue = columnValues[i]
            let columnValues = String(CString: columnValue, encoding: NSUTF8StringEncoding)

            print(columnNameStr,columnValues)

        }
        return 0 //如果返回0,就表示一直查询,直到查询结束;如果返回1,表示到当前位置终止查询

        }, nil, nil)
}

2.4.2 sqlite3_step执行预处理语句查询

主要步骤与绑定插入的步骤一样,首先要创建预处理语句,再次绑定语句,然后执行语句,其次重置语句,最后释放语句。

执行的结果集需要我循环获取,它拿到的结果集是不仅仅是文本型,可以根据数据库存储的实际类型进行解析

举例:

func prepareQuaryAll()
{

    let sql = "select * from t_person"
    let db = SqliteTool.shareInstance.db
    var stmt : COpaquePointer = nil
    // 创建预处理语句
    if sqlite3_prepare_v2(db, sql, -1, &stmt, nil) != SQLITE_OK {
        print("预处理失败")
        return
    }



    // 绑定 - 如果没有需要绑定的参数,可以省略

    // 执行预处理语句 -- 每执行一次就是一行数据,每行记录(结果集)都放在stmt中
    while sqlite3_step(stmt) == SQLITE_ROW {

        // 获取当前行有多少列(字段)
        let columnCount = sqlite3_column_count(stmt)

        // 循环取出每个字段
        for i in 0..(value)
                let valueStr = String(CString: valueInt8, encoding: NSUTF8StringEncoding)
                print(columnNameStr,valueStr)
            }else if type == SQLITE_INTEGER{ //如果是整型
                let value = sqlite3_column_int(stmt, i)
                print(columnNameStr,value)
            }else if type == SQLITE_FLOAT { //如果是浮点型
                let value = sqlite3_column_double(stmt, i)
                print(columnNameStr,value)
            }
        }
    }

}

3 事务Transaction(批量操作的优化)

  • 事务是并发控制的单位,是用户定义的操作序列。事务里面的操作,要么都做,要么都不做。
  • 事务是一个不可分割的工作单位。通过事务,可以把逻辑相关的一些操作绑定在一起
  • 事务通常以begin transaction开始的;以commit transaction或者rollback transaction结束的
  • 当大量的操作执行的时候,默认情况下,每个操作都会开启一个事务(成功之后提交事务,失败后回滚事务),频繁的开启事务操作会耗时很大
  • 所有,大量操作执行的时候,我们将这些操作放在一个事务中,整体只开启一个事务,成功之后提交事务,失败之后回滚事务,提高效率。

举例:把插入的语句放到一个事务transaction中执行

func insertOperation()
{
    //创建插入sql
    let sql1 = "insert into t_stu(name,age) values('chenhua',23)" //该数据库存在
    let sql2 = "insert into t_stu2(name,age) values('lisi',24)"   //该数据库不存在

    ///开启事务
    SqliteTool.shareInstance.execute("begin transaction")

    //执行操作
    let result1 = SqliteTool.shareInstance.execute(sql1)
    let result2 = SqliteTool.shareInstance.execute(sql2)

    //判断结果,处理事务
    if result1 && result2 {
        print("提交")
        SqliteTool.shareInstance.execute("commit transaction")
    }else{
        print("回滚")
        SqliteTool.shareInstance.execute("rollback transaction")        
}

4 FMDB框架的使用

是一个面向对象的SQLite框架,对sqlite进行了封装。所以在导入框架后,需要添加框架依赖 sqlite3.0.tdb

4.1 创建数据库并打开

    let path = "/Users/apple/Desktop/haha/fmdb.sqlite" 
    // 打开数据库
    let db = FMDatabase(path: path)
    db.open()

4.2 创建数据表

    // 创建数据表
    let tableSql = "create table if not exists t_student(name text,age integer,score real)"
    if db.executeStatements(tableSql) {
        print("创建数据表成功")
    }else {
        print("创建数据表失败")
    }

4.3 更新数据(增删改使用executeUpdate方法)

    // 更新数据(增删改都是用executeUpdate)
    let insertSql = "insert into t_student(name,age,score) values('jim',23,22)"
    if db.executeUpdate(insertSql, withArgumentsInArray: nil) {
        print("插入数据成功")
    }else{
        print("插入数据失败")
    }

4.4 查询数据(使用executeQuery方法)

    // 查询数据 -- 查询的结果都放在结果集中
    let querySql = "select * from t_student"
    let resultSet = db.executeQuery(querySql, withArgumentsInArray: nil)
    //---循环取出里面的记录
    while resultSet.next() {
        // 获取当前行的字段数(列数)
        let columnCount = resultSet.columnCount()
        // 获取指定字段的值
        let name = resultSet.stringForColumn("name")
        let age  = resultSet.intForColumn("age")
        let score = resultSet.doubleForColumn("score")
        print(name,age,score)
    }

4.5 执行事务

    // 开启事务
    db.beginTransaction()

    // 执行操作
    let insertSql1 = "insert into t_student(name,age,score) values('jim',23,22)"
    let insertSql2 = "insert into t_student2(name,age,score) values('jim',23,22)"
    let result1 = db.executeUpdate(insertSql1, withArgumentsInArray: nil)
    let result2 = db.executeUpdate(insertSql2, withArgumentsInArray: nil)

    // 提交或者回滚事务
    if result1 && result2
    {
        db.commit()
    }else{
        db.rollback()
    }

4.6 预处理语句

普通状态下一样,不错需要使用标准语法 用问号?代表参数

4.7 线程安全的操作数据库

不能直接使用FMDataBase开启db,然后执行操作。需要使用FMDatabaseQueue类,他在回调block中提供了db

    // 创建数据库队列
    let dbQueue = FMDatabaseQueue(path: path)

    // 在数据库中操作
    dbQueue.inDatabase { (db:FMDatabase!) -> Void in

        //使用db执行响应的增删改查操作
        db.executeUpdate(sql,nil)

    }