iOS - SQLite 数据库存储
1、SQLite 数据库 SQLite 是一种轻型的嵌入式数据库，安卓和 iOS 开发使用的都是 SQLite 数据库。它占用资源非常低，在嵌入式设备中，可能需要几百 K 的内存数据就够了。他的处理速度比 Mysql、PostgreSQL 这两款著名的数据库都要快。数据库的存储和 Excel 很像，以表（table）为单位。表由多个字段（列、属性、column）组成，表里面的每一行数据称为记录。数据库操作包含打开数据库、创建表，表的增、删、改、查。 SQL（Structured Query Language）结构化查询语言，SQL 是一种对数据库中的数据进行定义和操作的语言。使用 SQL 语言编写出来的句子/代码叫 SQL 语句，在程序运行过程中，想要操作（增删改查，CRUD）数据库中的数据，必须使用 SQL 语句。SQL 语句不区分大小写，每句语句都必须以分号结尾。 SQL 中常用的关键字有 select、insert、update、delete、from、create、where、desc、orderby、table，数据库中不可以使用关键字来命名表、字段。SQL 语句中用 ？来作为占位符，不管字段是何种类型。 SQLite 语句的种类： 数据定义语句（DDL：Data Definition Language）：包括 create 和 drop 等操作，在数据库中创建新表或删除表（create table 或 drop table）。 数据操作语句（DML：Data Manipulation Language）：包括 insert、update、delete 等操作，上面的三种操作分别用于添加、修改、删除表中的数据。 数据查询语句（DQL：Data Query Language）：可以用于查询获得表中的数据，关键字 select 是 SQL（也是所有 SQL）用的最多的操作，其他 DQL 常用的关键字有 where、order by、group by 和 having。 注意：写入数据库，字符串可以采用 char 方式，而从数据库中取出 char 类型，当 char 类型有表示中文字符时，会出现乱码。这是因为数据库默认使用 ASCII 编码方式。所以要想正确从数据库中取出中文，需要用 NSString 来接收从数据库取出的字符串。 SQLite 操作方法 sqlite3 *db 数据库句柄，跟文件句柄很类似 sqlite3_stmt *stmt 这个相当于 ODBC 的 Command 对象，用于保存编译好的 SQL 语句 sqlite3_open() 打开数据库，没有数据库时创建。 sqlite3_exec() 执行非查询的 sql 语句 sqlite3_prepare_v2 执行查询的 sql 语句 Sqlite3_step() 在调用 sqlite3_prepare 后，使用这个函数在记录集中移动。 sqlite3_free() 清空变量 Sqlite3_close() 关闭数据库文件 还有一系列的函数，用于从记录集字段中获取数据，如: sqlite3_column_text() 取 text 类型的数据。 sqlite3_column_blob() 取 blob 类型的数据 sqlite3_column_int() 取 int 类型的数据 SQLite 命令行 .help ：帮助 .quit ：退出 .database：列出数据库信息 .dump ：查看所有的 sql 语句 .schema ：查看表结构 .tables ：显示所有的表 SQL 语句常用数据类型 （1）整型： bigint ：整形数据，大小为 8 个字节 integer ：整形数据，大小为 4 个字节 smallint：整形数据，大小为 2 个字节 tinyint ：从 0 到 255 的整形数据，存储大小为 1 字节 （2）浮点型： float ：4 字节浮点数 double：8 字节浮点数 real ：8 字节浮点数 （3）字符型： char(n) ：n 长度的字串，n 不能超过 254 varchar(n)：长度不固定且其长度为 n 的字串，n 不能超过 4000 text ：text 存储可变长度的非 unicode 数据，存放比 varchar 更大的字符串 注意事项： 尽量用 varchar 超过 255 字节的只能用 varchar 或 text 能用 varchar 的地方不用 text SQLite 字符串区别： char 存储定长数据很方便，char 字段上的索引效率极高，比如定义 char(10)，那么不论你存储的数据是否达到了 10 个字节，都要占去 10 个字节的空间，不足的自动用空格填充。 varchar 存储变长数据，但存储效率没有 char 高，如果一个字段可能的值是不固定长度的，我们只知道它不可能超过 10 个 > 字符，把它定义为 varchar(10) 是最合算的，varchar 类型的实际长度是它的值的实际长度 +1，为什么 +1 呢 ？这个字节用于保存实际使用了多大的长度。因此，从空间上考虑，用 varchar 合适，从效率上考虑，用 char 合适，关键是根据情况找到权衡点。 text 存储可变长度的非 Unicode 数据，最大长度为 2^31-1（2147483647）个字符。 （4）日期类型： date ：包含了年份，月份，日期 time ：包含了小时，分钟，秒 datetime ：包含了年，月，日，时，分，秒 timestamp：包含了年，月，日，时，分，秒，千分之一秒 注意：datetime 包含日期时间格式，必须写成 ‘2010-08-05’不能写为‘2010-8-5’，否则在读取时会产生错误。 （5）其他类型： null ：空值 blob ：二进制对象，主要用来存放图片和声音文件等 default ：缺省值 primary key ：主键值 autoincrement：主键自动增长 （6）什么是主键： primary key，主键就是一个表中，有一个字段，里面的内容不可以重复，一般一个表都需要设置一个主键，autoincrement 让主键自动增长。 （7）注意事项： 所有字符串必须要加 ‘ ’ 单引号 整数和浮点数不用加 ‘ ’ 日期需要加单引号 ‘ ’ 字段顺序没有关系，关键是 key 与 value 要对应 对于自动增长的主键不需要插入字段 简单基本的 SQL 语句 (1) 数据记录筛选： sql="select * from 数据表 where 字段名=字段值 order by 字段名 [desc]" sql="select * from 数据表 where 字段名 like '%字段值%' order by 字段名 [desc]" sql="select top 10 * from 数据表 where 字段名=字段值 order by 字段名 [desc]" sql="select top 10 * from 数据表 order by 字段名 [desc]" sql="select * from 数据表 where 字段名 in ('值1','值2','值3')" sql="select * from 数据表 where 字段名 between 值1 and 值2" (2) 更新数据记录： sql="update 数据表 set 字段名=字段值 where 条件表达式" sql="update 数据表 set 字段1=值1,字段2=值2 …… 字段n=值n where 条件表达式" (3) 删除数据记录： sql="delete from 数据表 where 条件表达式" sql="delete from 数据表" (将数据表所有记录删除) (4) 添加数据记录： sql="insert into 数据表 (字段1,字段2,字段3 …) values (值1,值2,值3 …)" sql="insert into 目标数据表 select * from 源数据表" (把源数据表的记录添加到目标数据表) (5) 数据记录统计函数： AVG(字段名) 得出一个表格栏平均值 COUNT(*;字段名) 对数据行数的统计或对某一栏有值的数据行数统计 MAX(字段名) 取得一个表格栏最大的值 MIN(字段名) 取得一个表格栏最小的值 SUM(字段名) 把数据栏的值相加 引用以上函数的方法： sql="select sum(字段名) as 别名 from 数据表 where 条件表达式" //set rs=conn.excute(sql) 用 rs("别名") 获取统计的值，其它函数运用同上。 查询去除重复值：select distinct * from table1 (6) 数据表的建立和删除： CREATE TABLE 数据表名称(字段1 类型1(长度),字段2 类型2(长度) …… ) 2、iOS 自带 SQLite 的使用 1、环境配置 在 TARGETS => Build Phases => Link Binary With Libraries => 中添加：libsqlite3.0.tbd 或者在 TARGETS => Build Settings => Linking => Other Linker Flags 中添加 -l< 所需 dylib 的名称 >：-lsqlite3.0 2、使用 添加头文件： #import "sqlite3.h" 配置数据库路径 // 设置数据库文件路径 NSString *databaseFilePath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject stringByAppendingPathComponent:@"mydb.sqlite"]; // 创建数据库句柄 sqlite3 *db; // 错误记录 char *error; 3、打开数据库 // 数据库文件不存在时，自动创建文件 if (sqlite3_open([databaseFilePath UTF8String], &db) == SQLITE_OK) { NSLog(@"sqlite dadabase is opened."); } else { NSLog(@"sqlite dadabase open fail."); } 4、创建数据表 /* sql 语句，专门用来操作数据库的语句。 create table if not exists 是固定的，如果表不存在就创建。 myTable() 表示一个表，myTable 是表名，小括号里是字段信息。 字段之间用逗号隔开，每一个字段的第一个单词是字段名，第二个单词是数据类型，primary key 代表主键，autoincrement 是自增。 */ // create table if not exists 表名(主键名 主键类型 primary key autoincrement, 键名 键类型, 键名 键类型, ...) NSString *createSql = @"create table if not exists myTable(id integer primary key autoincrement, name text, age integer, address text)"; if (sqlite3_exec(db, [createSql UTF8String], NULL, NULL, &error) == SQLITE_OK) { NSLog(@"create table is ok."); } else { NSLog(@"error: %s", error); // 每次使用完毕清空 error 字符串，提供给下一次使用 sqlite3_free(error); } 5、插入记录 // insert into 表名(键名, 键名, ...) values('键值', '键值', '...') NSString *insertSql = @"insert into myTable(name, age, address) values('小新', '8', '东城区')"; if (sqlite3_exec(db, [insertSql UTF8String], NULL, NULL, &error) == SQLITE_OK) { NSLog(@"insert operation is ok."); } else { NSLog(@"error: %s", error); // 每次使用完毕清空 error 字符串，提供给下一次使用 sqlite3_free(error); } 6、删除记录 // delete from 表名 查询条件(where id = 2) NSString *deleteSql = @"delete from myTable where id = 2"; if (sqlite3_exec(db, [deleteSql UTF8String], NULL, NULL, &error) == SQLITE_OK) { NSLog(@"delete operation is ok."); } else { NSLog(@"error: %s", error); // 每次使用完毕清空 error 字符串，提供给下一次使用 sqlite3_free(error); } 7、修改记录 // update 表名 set 键名 = '键值', 键名 = '键值', ... 查询条件(where id = 3) NSString *updateSql = @"update myTable set name = '小白', age = '10', address = '西城区' where id = 3"; if (sqlite3_exec(db, [updateSql UTF8String], NULL, NULL, &error) == SQLITE_OK) { NSLog(@"update operation is ok."); } else { NSLog(@"error: %s", error); // 每次使用完毕清空 error 字符串，提供给下一次使用 sqlite3_free(error); } 8、查询记录 sqlite3_stmt *statement; // select 键名, 键名, ... from 表名，select * from 表名：查询所有 key 值内容 NSString *selectSql = @"select id, name, age, address from myTable"; if (sqlite3_prepare_v2(db, [selectSql UTF8String], -1, &statement, nil) == SQLITE_OK) { while(sqlite3_step(statement) == SQLITE_ROW) { // 查询 id 的值 int _id = sqlite3_column_int(statement, 0); // 查询 name 的值 NSString *name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)]; // 查询 age int age = sqlite3_column_int(statement, 2); // 查询 address 的值 NSString *address = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)]; NSLog(@"id: %i, name: %@, age: %i, address: %@", _id, name, age, address); } } else { NSLog(@"select operation is fail."); } sqlite3_finalize(statement); 9、关闭数据库 if (sqlite3_close(db) == SQLITE_OK) { NSLog(@"sqlite dadabase is closed."); } else { NSLog(@"sqlite dadabase close fail."); } 3、fmdb 的使用 iOS 中原生的 SQLite API 在使用上相当不友好，在使用时，非常不便。于是，就出现了一系列将 SQLite API 进行封装的库，例如 fmdb、PlausibleDatabase、sqlitepersistentobjects 等，fmdb 是一款简洁、易用的封装库。 fmdb 同时兼容 ARC 和非 ARC 工程，会自动根据工程配置来调整相关的内存管理代码。 fmdb 常用类： FMDatabase ： 一个单一的 SQLite 数据库，用于执行 SQL 语句。 FMResultSet ：执行查询一个 FMDatabase 结果集，这个和 Android 的 Cursor 类似。 FMDatabaseQueue ：在多个线程来执行查询和更新时会使用这个类。 除了查询操作，fmdb 数据库操作都执行 executeUpdate 方法，这个方法返回 BOOL 型。后面跟的参数类型必须是对象类型，FMDataBase 对象会将传过来的参数，转化成与数据库字段相匹配的类型，再进行后续处理。 fmdb 数据库中查询操作使用 executeQuery，并涉及到 FMResultSet，FMResultSet 提供了多个方法来获取不同类型的数据。 1、环境配置 将 第三方框架 fmdb 添加到工程中，并在 TARGETS => Build Phases => Link Binary With Libraries => 中添加：libsqlite3.0.tbd 2、使用 添加头文件： #import "FMDB.h" 配置数据库路径 // 设置数据库文件路径 NSString *databaseFilePath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject stringByAppendingPathComponent:@"mydb.sqlite"]; // 声明数据库管理对象 @property (nonatomic, strong) FMDatabase *db; // 实例化数据库管理对象 /* 使用数据库的路径初始化 当数据库文件不存在时，fmdb 会自己创建一个。 如果你传入的参数是空串：@"" ，则fmdb会在临时文件目录下创建这个数据库，数据库断开连接时，数据库文件被删除。 如果你传入的参数是 NULL，则它会建立一个在内存中的数据库，数据库断开连接时，数据库文件被删除。 */ self.db = [[FMDatabase alloc] initWithPath:databaseFilePath]; 3、打开数据库 if ([self.db open]) { NSLog(@"sqlite dadabase is opened."); } else { NSLog(@"sqlite dadabase open fail."); } 4、创建数据表 /* sql 语句，专门用来操作数据库的语句 create table if not exists 是固定的，如果表不存在就创建 userInfo() 表示一个表，userInfo 是表名，小括号里是字段信息 字段之间用逗号隔开，每一个字段的第一个单词是字段名，第二个单词是数据类型，primary key 代表主键，autoincrement 是自增 */ // create table if not exists 表名(主键名 主键类型 primary key autoincrement, 键名 键类型, 键名 键类型, ...) NSString *createSql = @"create table if not exists myTable(id integer primary key autoincrement, name text, age integer, address text)"; if ([self.db executeUpdate:createSql]) { NSLog(@"create table is ok."); } else { NSLog(@"create error = %@", self.db.lastErrorMessage); } 5、插入记录 // insert into 表名(键名, 键名, ...) values('键值', '键值', '...') NSString *insertSql = @"insert into myTable(name, age, address) values('小新', '8', '东城区')"; if ([self.db executeUpdate:insertSql]) { NSLog(@"insert operation is ok."); } else { NSLog(@"insert error = %@", self.db.lastErrorMessage); } 6、删除记录 // delete from 表名 查询条件(where id = 2) NSString *deleteSql = @"delete from myTable where id = 2"; if ([self.db executeUpdate:deleteSql]) { NSLog(@"delete operation is ok."); } else { NSLog(@"delete error = %@", self.db.lastErrorMessage); } 7、修改记录 // update 表名 set 键名 = '键值', 键名 = '键值', ... 查询条件(where id = 3) NSString *updateSql = @"update myTable set name = '小白', age = '10', address = '西城区' where id = 3"; if ([self.db executeUpdate:updateSql]) { NSLog(@"update operation is ok."); } else { NSLog(@"update error = %@", self.db.lastErrorMessage); } 8、查询记录 // select 键名, 键名, ... from 表名，select * from 表名：查询所有 key 值内容 NSString *selectSql = @"select id, name, age, address from myTable"; // FMResultSet 查询结果，执行查询 SQL 语句 FMResultSet *set = [self.db executeQuery:selectSql]; // 类似于数组的快速遍历，set 会依次代表所有查询出来的数据，每次取出一整条数据，根据字段名称，取出字段的值，将查询到的数据转成模型 while ([set next]) { // 查询 id 的值 int _id = [set intForColumn:@"id"]; // 查询 name 的值 NSString *name = [set stringForColumn:@"name"]; // 查询 age int age = [set intForColumn:@"age"]; // 查询 address 的值 NSString *address = [set stringForColumn:@"address"]; NSLog(@"id: %i, name: %@, age: %i, address: %@", _id, name, age, address); } 9、关闭数据库 if ([self.db close]) { NSLog(@"sqlite dadabase is closed."); } else { NSLog(@"sqlite dadabase close fail."); } 4、fmdb 多线程操作 如果应用中使用了多线程操作数据库，那么就需要使用 FMDatabaseQueue 来保证线程安全了。 应用中不可在多个线程中共同使用一个 FMDatabase 对象操作数据库，这样会引起数据库数据混乱。为了多线程操作数据库安全，fmdb 使用了 FMDatabaseQueue，使用 FMDatabaseQueue 很简单，首先用一个数据库文件地址来初使化 FMDatabaseQueue，然后就可以将一个闭包(block)传入 inDatabase 方法中。在闭包中操作数据库，而不直接参与 FMDatabase 的管理。 // 设置数据库文件路径 NSString *databaseFilePath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject stringByAppendingPathComponent:@"mydb.sqlite"]; // 打开数据库 FMDatabaseQueue *dbQueue = [FMDatabaseQueue databaseQueueWithPath:databaseFilePath]; // 队列 1 dispatch_async(dispatch_queue_create("queue1", NULL), ^{ for (int i = 0; i < 50; ++i) { // 对数据库进行操作，操作完成后会自动关闭数据库 [dbQueue inDatabase:^(FMDatabase *db) { NSString *insertSql= [NSString stringWithFormat: @"insert into myTable(name, age, address) values('%@', '%d', '%@')", @"小新", i, @"东城区"]; if ([db executeUpdate:insertSql]) { NSLog(@"%@ insert queue1 %d operation is ok.", [NSThread currentThread], i); } else { NSLog(@"insert error = %@", db.lastErrorMessage); } }]; } }); // 队列 2 dispatch_async(dispatch_queue_create("queue2", NULL), ^{ for (int i = 0; i < 50; ++i) { // 对数据库进行操作，操作完成后会自动关闭数据库 [dbQueue inDatabase:^(FMDatabase *db) { NSString *insertSql= [NSString stringWithFormat: @"insert into myTable(name, age, address) values('%@', '%d', '%@')", @"小白", i, @"西城区"]; if ([db executeUpdate:insertSql]) { NSLog(@"%@ insert queue2 %d operation is ok.", [NSThread currentThread], i); } else { NSLog(@"insert error = %@", db.lastErrorMessage); } }]; } }); 5、其他 SQLite 的第三方封装库 1、PlausibleDatabase 也是一个数据库操作的 objective－c 版封装库，“SQLite is the initial and primary target, but the API has been designed to support more traditional databases.” 文件较多，一般的接口与 FMDataBase 一样，此外还支持 SQL 的预编译和参数绑定。 2、sqlitepersistentobjects 这个开源库的目标是以面对对象的方式的存储和加载数据，让对象本身就有 save 和 load 的功能，屏蔽数据库的相关操作（创建、更新等），让使用者在不写 SQL 语句的状况下都可以使用 SQLite。应该说是符合 ActiveRecored 标准的。 自从 iOS3.0 支持 Core Data 之后，sqlitepersistentobjects 就停止了更新。不过单从操作数据来说，这个库还是很优秀的，如果你的数据存储工作很简单（light），使用 Core Data 比较显得凝重的话，sqlitepersistentobjects 也许是个很好的选择。
