热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

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

本文介绍了iOS数据库Sqlite的SQL语句分类和常见约束关键字。SQL语句分为DDL、DML和DQL三种类型,其中DDL语句用于定义、删除和修改数据表,关键字包括create、drop和alter。常见约束关键字包括ifnotexists、ifexists、primarykey、autoincrement、notnull和default。此外,还介绍了常见的数据库数据类型,包括integer、text和real。

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 查询到当前行的字段值的数组 — 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)

}

推荐阅读
  • Java太阳系小游戏分析和源码详解
    本文介绍了一个基于Java的太阳系小游戏的分析和源码详解。通过对面向对象的知识的学习和实践,作者实现了太阳系各行星绕太阳转的效果。文章详细介绍了游戏的设计思路和源码结构,包括工具类、常量、图片加载、面板等。通过这个小游戏的制作,读者可以巩固和应用所学的知识,如类的继承、方法的重载与重写、多态和封装等。 ... [详细]
  • Java学习笔记之面向对象编程(OOP)
    本文介绍了Java学习笔记中的面向对象编程(OOP)内容,包括OOP的三大特性(封装、继承、多态)和五大原则(单一职责原则、开放封闭原则、里式替换原则、依赖倒置原则)。通过学习OOP,可以提高代码复用性、拓展性和安全性。 ... [详细]
  • SpringBoot uri统一权限管理的实现方法及步骤详解
    本文详细介绍了SpringBoot中实现uri统一权限管理的方法,包括表结构定义、自动统计URI并自动删除脏数据、程序启动加载等步骤。通过该方法可以提高系统的安全性,实现对系统任意接口的权限拦截验证。 ... [详细]
  • Java容器中的compareto方法排序原理解析
    本文从源码解析Java容器中的compareto方法的排序原理,讲解了在使用数组存储数据时的限制以及存储效率的问题。同时提到了Redis的五大数据结构和list、set等知识点,回忆了作者大学时代的Java学习经历。文章以作者做的思维导图作为目录,展示了整个讲解过程。 ... [详细]
  • 本文讨论了一个关于cuowu类的问题,作者在使用cuowu类时遇到了错误提示和使用AdjustmentListener的问题。文章提供了16个解决方案,并给出了两个可能导致错误的原因。 ... [详细]
  • [大整数乘法] java代码实现
    本文介绍了使用java代码实现大整数乘法的过程,同时也涉及到大整数加法和大整数减法的计算方法。通过分治算法来提高计算效率,并对算法的时间复杂度进行了研究。详细代码实现请参考文章链接。 ... [详细]
  • Java中包装类的设计原因以及操作方法
    本文主要介绍了Java中设计包装类的原因以及操作方法。在Java中,除了对象类型,还有八大基本类型,为了将基本类型转换成对象,Java引入了包装类。文章通过介绍包装类的定义和实现,解答了为什么需要包装类的问题,并提供了简单易用的操作方法。通过本文的学习,读者可以更好地理解和应用Java中的包装类。 ... [详细]
  • 先看官方文档TheJavaTutorialshavebeenwrittenforJDK8.Examplesandpracticesdescribedinthispagedontta ... [详细]
  • Android系统源码分析Zygote和SystemServer启动过程详解
    本文详细解析了Android系统源码中Zygote和SystemServer的启动过程。首先介绍了系统framework层启动的内容,帮助理解四大组件的启动和管理过程。接着介绍了AMS、PMS等系统服务的作用和调用方式。然后详细分析了Zygote的启动过程,解释了Zygote在Android启动过程中的决定作用。最后通过时序图展示了整个过程。 ... [详细]
  • Iamtryingtomakeaclassthatwillreadatextfileofnamesintoanarray,thenreturnthatarra ... [详细]
  • 本文介绍了一个Java猜拳小游戏的代码,通过使用Scanner类获取用户输入的拳的数字,并随机生成计算机的拳,然后判断胜负。该游戏可以选择剪刀、石头、布三种拳,通过比较两者的拳来决定胜负。 ... [详细]
  • JVM 学习总结(三)——对象存活判定算法的两种实现
    本文介绍了垃圾收集器在回收堆内存前确定对象存活的两种算法:引用计数算法和可达性分析算法。引用计数算法通过计数器判定对象是否存活,虽然简单高效,但无法解决循环引用的问题;可达性分析算法通过判断对象是否可达来确定存活对象,是主流的Java虚拟机内存管理算法。 ... [详细]
  • Android源码深入理解JNI技术的概述和应用
    本文介绍了Android源码中的JNI技术,包括概述和应用。JNI是Java Native Interface的缩写,是一种技术,可以实现Java程序调用Native语言写的函数,以及Native程序调用Java层的函数。在Android平台上,JNI充当了连接Java世界和Native世界的桥梁。本文通过分析Android源码中的相关文件和位置,深入探讨了JNI技术在Android开发中的重要性和应用场景。 ... [详细]
  • 本文介绍了Swing组件的用法,重点讲解了图标接口的定义和创建方法。图标接口用来将图标与各种组件相关联,可以是简单的绘画或使用磁盘上的GIF格式图像。文章详细介绍了图标接口的属性和绘制方法,并给出了一个菱形图标的实现示例。该示例可以配置图标的尺寸、颜色和填充状态。 ... [详细]
  • 欢乐的票圈重构之旅——RecyclerView的头尾布局增加
    项目重构的Git地址:https:github.comrazerdpFriendCircletreemain-dev项目同步更新的文集:http:www.jianshu.comno ... [详细]
author-avatar
手机用户2502856985
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有