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 操作方法

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    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 命令行

    1
    2
    3
    4
    5
    6
    .help    :帮助
    .quit :退出
    .database:列出数据库信息
    .dump :查看所有的 sql 语句
    .schema :查看表结构
    .tables :显示所有的表
  • SQL 语句常用数据类型

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    1)整型:

    bigint :整形数据,大小为 8 个字节
    integer :整形数据,大小为 4 个字节
    smallint:整形数据,大小为 2 个字节
    tinyint :从 0255 的整形数据,存储大小为 1 字节

    2)浮点型:

    float4 字节浮点数
    double8 字节浮点数
    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-12147483647)个字符。

    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
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    (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、使用

    • 添加头文件:

      1
      #import "sqlite3.h"
    • 配置数据库路径

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      // 设置数据库文件路径
      NSString *databaseFilePath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,
      NSUserDomainMask,
      YES).lastObject
      stringByAppendingPathComponent:@"mydb.sqlite"];

      // 创建数据库句柄
      sqlite3 *db;

      // 错误记录
      char *error;
  • 3、打开数据库

    1
    2
    3
    4
    5
    6
    7
    8
    9
    // 数据库文件不存在时,自动创建文件

    if (sqlite3_open([databaseFilePath UTF8String], &db) == SQLITE_OK) {

    NSLog(@"sqlite dadabase is opened.");
    } else {

    NSLog(@"sqlite dadabase open fail.");
    }
  • 4、创建数据表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    /*
    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、插入记录

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    // 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、删除记录

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    // 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、修改记录

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    // 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、查询记录

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    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、关闭数据库

    1
    2
    3
    4
    5
    6
    7
    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、使用

    • 添加头文件:

      1
      #import "FMDB.h"
    • 配置数据库路径

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      // 设置数据库文件路径
      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、打开数据库

    1
    2
    3
    4
    5
    6
    7
    if ([self.db open]) {

    NSLog(@"sqlite dadabase is opened.");
    } else {

    NSLog(@"sqlite dadabase open fail.");
    }
  • 4、创建数据表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    /*
    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、插入记录

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    // 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、删除记录

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    // 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、修改记录

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    // 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、查询记录

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    // 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、关闭数据库

    1
    2
    3
    4
    5
    6
    7
    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 的管理。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    // 设置数据库文件路径
    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 也许是个很好的选择。
文章目录
  1. 1. 1、SQLite 数据库
  2. 2. 2、iOS 自带 SQLite 的使用
  3. 3. 3、fmdb 的使用
  4. 4. 4、fmdb 多线程操作
  5. 5. 5、其他 SQLite 的第三方封装库
隐藏目录