iOS开发系列-SQLite

概述

SQLite3是一款轻型的嵌入式数据库。它占用资源非常低，在嵌入式设备中，可能只需要几百K的内存就够了。它的处理速度比Mysql、PostgreSQL这两款著名的数据库速度还快。

数据库简介

常见的数据库可以分为2大类

• 关系型数据库(主流)
• 对象型数据库

常用的关系型数据库

• PC端：Oracle、MySQL、SQL Server、Access、DB2、Sybase
• 嵌入式\移动客户端: SQLite

对于iOS开发中小型、中型的数据存储可以使用Plist、偏好设置、NSCoding, 但是对于大型数据必须使用SQLite 数据库。苹果开发的CoreData也是对SQlite的封装。

SQLite

SQLite数据数据库存储数据以表(table)为单位的方式存储，每张表中对应很多字段，每个字段都有个对应的数据类型。

SQLite数据类型

SQLite将数据划分为以下几种存储类型：

• integer 整型值
• real 浮点值
• text 文本字符串
• blob 二进制数据（比如文件）

提示：实际上SQLite是无类型的。就算声明为integer类型，还是能存储字符串文本(主键除外)

SQL语句

SQL语句是一种关系型数据库中的数据进行定义和操作的语言，是structured query language简写。特点就是不区分大小写、每条语句以分号；结尾。
常常将SQL语句分为三大类，分别为DDL、DML、DQL。

DDL

DDL(Data Definition Language)数据定义语句，包含create和drop等操作，在数据库中创建新表或删表(creat table 或 drop table)。

/* 创建表 */
create table if not EXISTS t_student(
	id integer PRIMARY KEY AUTOINCREMENT,
	name text,
	score real
);

/* 删除表 */
drop table if exists t_student;

DML

DML(Data Manipulation language)数据操作语句，包括insert、update、delete等操作。

/* 插入数据 */
insert into t_student(id, name, score)values(1, 'Jake', 100.00);
insert into t_student(name, score) values ('Rose', 98.00);

/* 更新数据 */
update t_student set score = 99.00 WHERE name= 'Rose';

数据库中的字符串内容应该使用单引号栝住。

DQL

DML(Data Query language)数据查询语句，关键字select是所有SQL语句用的最多的语句。

/* 查询所有字段 */
select *from t_student;

/* 查询指定字段 */
select 字段1， 字段2, ... from t_student;

条件语句

如果只想更新或者删除某些固定的记录，那就必须在DML语句后加上一些条件

排序

对查询出来的结果可以使用order by进行排序

select *from t_student order by 字段 [desc\asc]；

desc将结果按某个字段的降序排列，asc将结果按某个字段的升序排列。
也可以用多个字段进行排序

select *from t_student order by age asc, height desc;

先按找年龄排序(升序)，年龄相等就按照身高排序（降序）。

limit

使用limit可以精确地控制查询结果的数量，必须每次只查询10条数据。

select *from t_student limit 10, 10;

可以理解为跳过最前面的10条语句，然后去10条数据。

limit常用作分页查询，固定每页的数量，然后传入对应也页码，获取数据。假设每页的数据为10条，获取第n页的数据

select *from t_student limit 10*(n-1), 10;

简单约束

建表时可以给特定的字段设置一些约束条件，常见的约束

• not null 规定字段的值不能为null
• unique 规定字段的值必须唯一
• default 指定字段的默认值

外键约束

利用外键约束可以用来建立表与表之间的关系。外键的情况是：一张表的某个字段，引用者另一张表的主键字段。

/* 创建班级表 */
create table if not EXISTS t_class(
        id integer PRIMARY KEY AUTOINCREMENT,
        name text
);

/* 创建学生表 */
create table if not EXISTS t_student(
	id integer PRIMARY KEY AUTOINCREMENT,
	name text,
	class_id integer,
	/* 添加外键约束 */
	constraint fk_t_student_class_id foreign key (class_id) references t_class(id)
);

表连接查询

表连接查询就是需要联合多张表才能查到想要的数据。

select stu.name, c.name from t_student stu, t_class c;

这种方式的联合查询获得的结果是笛卡尔积。需要增加条件

select stu.name, c.name from t_student stu, t_class c where stu.id = c.id;

如果想查询iOS学科的学生

select stu.name, sub.subject from t_student stu, t_subject sub `where stu.subject_id = sub.id and sub.subject='iOS'

Navicat图形界面

Navicat是一款著名的数据库管理软件，支持大部分主流的数据库(包括MySQL、SQLite)。

Navicate 建立数据库连接

Navicate创建表

执行SQL语句

修改表结构

执行SQL语句文件

SQLite编码

苹果提供了libsqlite3.tbd动态库，可以使用该动态库来使用SQLite存储数据。libsqlite3.tbd是纯C语言的。
在使用之前，在Target->Build Phase -> Link Binary with Libraries添加libsqlite3.tbd。

导入头文件#import <sqlite3.h>

创建数据库

int sqlite3_open( const char *filename,  sqlite3 **ppDb);

第一个参数是生成SQLite数据库文件的路径。
第二个参数是返回的数据库实例。这个方法如果发现SQLite数据库文件已存在，就不会再创建。
返回值int类型，如果为SQLITE_OK代表创建数据库成功。

执行SQL语句

 int sqlite3_exec(sqlite3*,  const char *sql, int (*callback)(void*,int,char**,char**), void *, char **errmsg);

第一个参数是数据库实例。
第二个参数执行的SQL语句
第三个参数执行SQL语句的回调 可以传NULL
第四个参数是回调参数 一般传NULL
第五个参数 返回的错误
返回值int类型，如果为SQLITE_OK代表SQL语句执行成功。

该函数适用于创建表、删除表、数据的增、删、改等语句的执行。不能用于执行查询语句，因为查询函数需要返回数据。

查询数据

查询数据需要2个步骤，第一步验证SQL语句的合法性

int sqlite3_prepare_v2(sqlite3 *db,  const char *zSql,  int nByte, sqlite3_stmt **ppStmt,  const char **pzTail );

第一个参数是数据库实例。
第二个参数执行的SQL语句
第三个参数SQL语句长度，一般传递-1，代表内部自己计算
第四个参数返回的结果集
第五个参数暂时用不到，一般传NULL

第二步查询数据

int sqlite3_step(sqlite3_stmt*);

传入第一步的返回的结果集实例
返回值如果为SQLITE_ROW代表查询到一条数据，如果存在数据的话，每调用一次该函数就会自动获取到下一条数据。因此可以使用while循环获取所有查询到的数据。

// 开始查询数据
while(sqlite3_step(ppStmt) == SQLITE_ROW){
            
    int sid = sqlite3_column_int(ppStmt, 0);
    const unsigned char *name = sqlite3_column_text(ppStmt, 1);
    double score = sqlite3_column_double(ppStmt, 2);
            
    NSLog(@"sid=%d, name=%s, score=%.2f", sid, name, score);
}

通过列的索引从结果集中获取数据，我们在写查询语句时select后写上列名，不要写*。

防止SQL注入

防止SQL注入，在写SQL语句查询语句时对于语句中的value使用占位符 ?，然后通过函数进行对占位符进行赋值

int sqlite3_bind_int(sqlite3_stmt*, int, int);
int sqlite3_bind_double(sqlite3_stmt*, int, double);
int sqlite3_bind_text(sqlite3_stmt*,int,const char*,int,void(*)(void*));
...

第一个参数 验证SQL合法性后返回的结果集
第二个参数 是占位符'?'在SQL语句的索引 注意索引是从1开始
对于int sqlite3_bind_text第三个参数传-1就好，回调传NULL

// 查询数据之前先检测SQL语句的合法性
    char *sql = "select id, name, score from t_student where name like ?";
    sqlite3_stmt *ppStmt = NULL; // 结果集
    
    // -1 代表内部自己计算sql语句长度
    int ret = sqlite3_prepare_v2(_db, sql, -1, &ppStmt, NULL);
    if (ret == SQLITE_OK) {
        NSLog(@"查询语句是合法的");
        
        NSMutableArray *temp = [NSMutableArray array];
        const char *c = [[NSString stringWithFormat:@"%%%@%%", condition] UTF8String];
        
        // 防止SQL注入 设置占位符内容 从1开始
        sqlite3_bind_text(ppStmt, 1, c, -1, NULL);
        
        // 开始查询数据
        while(sqlite3_step(ppStmt) == SQLITE_ROW){
            
            int sid = sqlite3_column_int(ppStmt, 0);
            const unsigned char *name = sqlite3_column_text(ppStmt, 1);
            double score = sqlite3_column_double(ppStmt, 2);
            
            NSLog(@"sid=%d, name=%s, score=%.2f", sid, name, score);
        }
        return [NSArray arrayWithArray:temp];
        
    }else{
        NSLog(@"查询语句是非法的");
        return nil;
    }