iOS数据库Sqlite的SQL语句分类和常见约束关键字

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语句

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

//--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 查询到当前行的字段值的数组 &＃8212; 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相当于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.. // 获取当前字段的字段名称(当前索引，第几个字段)
let columnName = sqlite3_column_name(stmt, i)
let columnNameStr = String(CString: columnName, encoding: NSUTF8StringEncoding)


// 获取当前字段的类型
let type = sqlite3_column_type(stmt, i)

// 根据类型取出对应类型的值
if type == SQLITE_TEXT { //如果是文本类型
let value = sqlite3_column_text(stmt, i)
let valueInt8 = UnsafePointer(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)

}