SQLite使用总结

SQLite数据库操作是移动开发中非常基础也非常重要的一部分，本文将从SQLite的概念到SQLite在iOS中的使用来介绍它。

SQLite简介

SQLite (英语发音:赊扣耐特)是遵守ACID的关系型数据库管理系统,它包含在一个相对小的C程序库中。与许多其它数据库管理系统不同，SQLite不是一个客户端/服务器结构的数据库引擎，而是被集成在用户程序中。
SQLite遵守ACID,实现了大多数SQL标准。它使用动态的、弱类型的SQL语法。它作为嵌入式数据库，是应用程序，如网页浏览器，在本地/客户端存储数据的常见选择。它可能是最广泛部署的数据库引擎，因为它正在被一些流行的浏览器、操作系统、嵌入式系统所使用。同时，它有许多程序设计语言的语言绑定。
维基百科

关于数据库一般划分为五大类型：

• 关系型
• 键-值型
• 多列型
• 面向文档型
• 图型

目前应用最广的应该是关系型数据库（Relational DataBase Management System,RDBMS)，它是以集合理论为基础的系统，实现为具有行和列的二维表。与RDBMS交互的标准方法是结构化查询语言（Structured Query Language,SQL)。

今天的主角SQLite就属于关系型数据库。SQLite是一个轻量级的库（只包含一个.h文件和一个.c文件，大概有八万多行代码),支持很多种语言的编程接口,包括C/C++、Java、Python、dotNet、Ruby、Perl等等。作为一款开源的嵌入式数据库，SQLite拥有强大而灵活的关系型数据库前端和简单而紧凑的B-tree后端。因此你无需任何配置，不用考虑平台限制和许可便将其放入到你的程序中。（目前sqlite版本为sqlite3)

SQLite是一款被广泛应用于移动设备的数据库,本文将主要介绍如何在iOS上使用SQLite。

以下是SQLite体系结构图

sqlite体系结构图

SQLite的基础操作

首先要注意的是SQLite是忽略大小写的，为了不方便阅读，以下例子中我将关键字全部小写。

根据SQL惯例，称关系为表（TABLE),属性为列（COLUMN),元组为行（ROW)。最基本的数据库操作无非就是创建、读取、更新和删除。忽略创建就变成了我们常说的&＃8221;增删改查&＃8221;。下面是iOS中的基本操作例子。

创建数据库

这里使用到的方法为sqlite3_open,如果指定沙盒路径下不存在数据库，则先创建，再打开。

sqlite3 *_db;
NSString *documents = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)] objectAtIndex:0];
NSString *_dbPath = [documents stringByAppendingPathComponent:@"person.sqlite"];
int result = sqlite3_open(_dbPath.UTF8String,&_db);
if (result == SQLITE_OK) {
NSLog(@"open success");
} else {
NSLog(@"open failed");
}


创建数据表

NSString *sqlCreateTable = @"create table if not exists person (id integer primary key autoincrement,username text,age integer,gender integer,birthday numeric)";
char *error = NULL;
int result = sqlite3_exec(_db,sqlCreateTable.UTF8String,NULL,NULL ,&error);
if (error) {
NSLog(@"sqlite execute error");
sqlite3_free(error);
}



创建完毕可以在沙盒中查看数据库,表结构如下

person.sqlite

插入数据

NSString *sqlInsert = [NSString stringWithFromat:@"insert into person(username,age,gender,birthday) values(?,?,?,?)",@"小明",@(22),@(1),[NSDate date]] ;
char *errorMsg = NULL;
sqlite3_stmt *stmt;
if (sqlite3_prepare_v2(_db, sqlInsert.UTF8String, -1, &stmt, NULL) == SQLITE_OK) {
sqlite3_bind_text(stmt, 1, [username UTF8String], -1, NULL);
sqlite3_bind_int(stmt, 2, [age intValue]);
sqlite3_bind_int(stmt, 3, [gender intValue]);
sqlite3_bind_double(stmt, 4, [birthday timeIntervalSince1970]);
}
if (sqlite3_step(stmt) != SQLITE_DONE)
NSLog(@"insert error");


插入之后

sqliteInsert

删除数据

删除指定姓名的数据

NSString *sqlDelete = [NSString stringWithFromat:@"delete from person where username=?",username] ;
sqlite3_stmt *stmt = NULL;
int result = sqlite3_prepare_v2(_db, sqlDelete.UTF8String, -1, &stmt, NULL);
if (result != SQLITE_OK) {
NSLog("sqlite stmt prepare error");
return NO;
}
sqlite3_bind_text(stmt, 1, username.UTF8String, -1, NULL);
result = sqlite3_step(stmt);
sqlite3_finalize(stmt);
if (result == SQLITE_ERROR) {
NSLog(@"delete error");
return NO;
}



查找数据

查找指定姓名的数据

NSString *sqlQuery = [NSString stringWithFromat:@"select * from person where username=%@",username] ;
sqlite3_stmt * statement;

if (sqlite3_prepare_v2(db, [sqlQuery UTF8String], -1, &statement, nil) == SQLITE_OK) {
sqlite3_bind_text(stmt, 1, username.UTF8String, -1, NULL);
if (sqlite3_step(statement) == SQLITE_ROW) {
char *username = (char*)sqlite3_column_text(statement, 1);
NSString *usernameStr = [[NSString alloc]initWithUTF8String:username];
int age = sqlite3_column_int(statement, 2);
int gender = sqlite3_column_int(statement, 3);
double birthday = sqlite3_column_double(statement, 4);
NSDate *birthdayDate = [NSDate dateWithTimeIntervalSince1970:birthday];

}
}



另外在sql语句中还有一些运算符可以帮助快速过滤数据

• like
  like是搜索中使用比较广的运算符。like比较列值和给定模式字符串，%匹配任意数量的任何字符，_匹配一个字符。（like只能用于简单的通配符，更强大的字符串匹配语法是正则表达式)
• order by
  搜索结果按照关键字的升序(asc)或降序(desc)排列
• limit
  限制返回的数据量 ，可以与offset配合使用
  SELECT username,age,gender FROM person LIMIT 3 OFFSET 2;
  从第3行开始(默认第一行，偏移两行),取3条数据
• distinct
  消除所有重复的数据
• group by
  根据指定规则对数据进行分组
  SELECT name,sum(age) from person GROUP BY name;
  根据名字获取personn表中的 name和age字段，同名的age累加
• 聚合函数
  上例中的sum()便是聚合函数，它从一组记录中计算聚合值，类似的聚合函数还有avg(),count(),min(),max()等

修改数据

修改指定姓名的数据的年龄

NSString *sqlUpdate = [NSString stringWithFromat:@"update person set age=? where username=?",age,username] ;
sqlite3_stmt *stmt;
if (sqlite3_prepare_v2(_db, sqlUpdate.UTF8String, -1, &stmt, NULL) == SQLITE_OK) {
sqlite3_bind_int(stmt, 1, (int)age(NULL));
sqlite3_bind_text(stmt, 2, username.UTF8String, -1, NULL);
int result = sqlite3_step(stmt);
if (result != SQLITE_DONE) {
NSLog(@"update error");
return NO;
}
}



SQLite的高级使用

事务

事务保障了关系数据库的一致性，事务的准则是，要么全部成功，要么全部失败。
当发出begin命令时，事务将持续到调用commit或rollback,或者sql命令引起约束违反进而导致rollback。
一般情况下，锁持续时间隐藏在事务持续时间内，他们总是一起结束。下图可以看到事务的声明周期和锁状态

sqlite锁状态

事务的控制主要有三条命令

• BEGIN TRANSACTION :开始处理事务
• COMMIT:把事务调用的更改保存到数据库中 或者用END TRANSACTION
• ROLLBACK:回滚
  在iOS中的操作实际上就是执行一条sql语句，三条命令对应的sql语句如下:
  begin: @&＃8221;begin exclusive transaction&＃8221;
  commit: @&＃8221;commit transaction&＃8221;
  rollback: @&＃8221;rollback transaction&＃8221;

PS : 另外可以创建延迟事务,也可以根据保存点的名字回滚到指定版本

子查询

子查询是指select语句中嵌套select语句,通常应用在where子句，特别是in操作符中.例如
select count(*) from person where username in (select username from family where age=20 or age=22);

复合查询

复合查询是用来处理多个查询的结果，SQLite中对应使用关键字union,intersect和except。

UNION : 用于合并两个或多个select语句的结果，不返回任何重复的行，为了使用union，每个select被选择的列数必须相同
INTERSECT : 操作输入两个关系A和B,选择那些既在A也在B的行。
EXCEPT : 操作输入两个关系A和B,找出所有在A但不在B的行。

例如:

select username,age,gender from person
join family on person.username=family.username
union all
select username,age,gender from person
left outer join family on person.username=family.username;


join是把person和family的每一行进行比较，找到名字相同的行，并将列值合并成一个结果行，完整写法是inner join ,inner是可选的。

outer join指外连接，标准的SQL定义了三种外连接left,right和full,SQLite只支持left。左外连接主要看左边的表，先把左边表全部显示，右边满足条件 person.username=family.username的显示，不满足的显示为空。

小结

关于SQLite的使用还有很多，比如触发器、视图、注入等等。但是在实际使用中很少用到这些，所以考虑到篇幅的原因暂时先省了。另外在sqlite存取数据中还有一个比较特殊的blob类型，用来存取二进制文件，实际上只是多了一步字节长度的存取，以后有空会补上。

如果想了解SQLite的底层实现机制可参考下列文章:

• 《SQLite源码分析》
• 《SQLite中的B-tree、B+tree》
• 《B-Tree和B+Tree》。

参考

• http://www.runoob.com/sqlite/sqlite-view.html
• http://my.oschina.net/u/587236/blog/129022
• https://www.sqlite.org/foreignkeys.html