iOS进阶整理02SQLite3和基础的sql语句,bind,blob

一、SQLite3

SQLite是iOS开发普遍用的数据库，占用资源非常少 ，最初设计目标是用于嵌入式系统，在嵌入式设备中，值需要几百K内存。
在iOS中使用SQLite时，需要在Link Binary With Libraries中添加libsqlite3.0.tbd依赖，并引入sqlite3.h头文件。

基础的sql语句
这个太基础了，但是不搞后台的也只能略知皮毛，不用还记不清楚。整理下方便用的时候忘了看。
在终端中SQLite的相关操作

进入SQLite
sqlite3 mySql.sqlite
查看所有命令
.show
查看表
.tables
显示属性名
.headers on
设置列间的分割符号
.separator &＃8216;|&＃8217;

SQL语句常用的一些内容：
SQLite是无类型的数据库，可以保持任何类型的数据。为了与其他数据库兼容最大化，SQLite支持“类型近似”观点
integer : int
text : char，clob，text
real : real，floa，doub
其余：numeric
建一个iphones表（属性名，属性类型）

create table if not exists iphones(id text ,size integer , appearance text);
CREATE TABLE iphones(id text ptimary key default '11223',size integer not null default 4, appearance text, nickname char(20), appear2ance);
删除一个表
drop table 表名;
复制一个表,SQLite不支持drop删除表中的一列，可以通过复制一个新表过滤掉不需要的列
create table 表2名 as select id,size,nickname from 表1名 where 1=1;
添加一列
插入一条数据
insert into iphones(size)values(2);
insert into iphones('11',4,'dd');//
INSERT INTO 表名称 VALUES (值1, 值2,....)
INSERT INTO 表名称 (列1, 列2,...) VALUES (值1, 值2,....)
更新一条数据
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
删除一条
DELETE FROM 表名称 WHERE 列名称 = 值
查询
SELECT 列名称 FROM 表名称//查一列
SELECT * FROM 表名称//查全表
//加入where过滤，多重条件用and并列
SELECT 列名称 FROM 表名称 WHERE 列 运符 值
select name from 表名 where 列名(name) = '小明'


SQLite字段约束条件

PRIMARY KEY ：主键
NOT NULL ：非空
UNIQUE：不能重复
FOREIGN KEY：外键
CHECK：必须满足一定条件
DEFAULT：给定默认值
AUTOINCREATEMENT：该段数据如果为整型可自动加1

二、sqlite的增删改查操作

我们把sqlite的使用方法写在一个文件DataBaseHelper类里，使用的时候导入调用即可

//首先写一个DataBaseHelper类的单例方法，保证同时间只有一个对象访问数据库
+(DataBaseHelper *)sharedDataBaseHelper
{
static DataBaseHelper *dataBaseHelper = nil;
@synchronized(self) {
if (dataBaseHelper == nil) {
dataBaseHelper = [[DataBaseHelper alloc]init];
}
}
return dataBaseHelper;
}
//打开数据库方法
-(sqlite3*)openOrCreateDB
{
// //路径
NSArray* pathArray = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *dbPath = [pathArray[0]stringByAppendingPathComponent:@"myDB.sqlite"];
//创建数据库实例
sqlite3 *mySqlite = NULL;
//这里dbPath是NSString类型，转换成UTF8编码
int result = sqlite3_open(dbPath.UTF8String, &mySqlite);
if (result == SQLITE_OK) {//数据库打开成功
NSLog(@"db ok");
return mySqlite;
}
else
{
NSLog(@"db fs");
sqlite3_close(mySqlite);//关闭数据库
return NULL;
}
}


//执行无返回结果集的操作，添加，删除，和修改，是没有返回值的
-(void)execSqlWithSqlString:(NSString*)sqlStr Msg:(NSString *)msg
{
//打开数据库
sqlite3 *mySqlite = [self openOrCreateDB];
//执行sql语句
//第一个参数：SqLite3对象实例
//第二个参数：要执行的sql语句
//第三个参数：执行完成之后的回调方法，一般为null
//第四个参数：回调方法的第一个参数
//第五个参数：错误日志，类似于OC中的error
int result = sqlite3_exec(mySqlite, sqlStr.UTF8String, NULL, NULL, NULL);
if (result == SQLITE_OK) {//执行成功
NSLog(@"exec ok，%@",msg);
}
else
{
NSLog(@"exec fs,%@",msg);
}

}


//有返回结果集，也就是查询操作
//这是一个通用查询的方法，在不知道数据库中表的字段数量，名称，和字段类型的情况下，把表读出来存在字典里
//思路就是先得出有多少字段，再逐一判断字段的类型和名称，再逐一取出值
-(NSArray*)queryWithSQL:(NSString*)sqlStr
{
//打开数据库
sqlite3 *mySqlite = [self openOrCreateDB];
//声明statement实例
//也可以叫伴随指针
//在准备sql语句阶段用来指向sql语句，如果sql语句执行有问题，该指针就会被置为null
//在语句执行没问题时，该指针用来指向记录所在的内存地址
sqlite3_stmt *stament; //初始化可变数组用来盛放所有记录
NSMutableArray *mArray = [[NSMutableArray alloc]init];
//准备sql语句
//准备阶段的五个参数
//1.数据库实例
//2.sql语句
//3.要执行多少长度的sql语句，如果该值大于0，那么就会执行该值长度的sql语句，-1就是全部执行
//4.sql语句执行有文件就null
//5.保存没有执行的sql语句
int result = sqlite3_prepare(mySqlite, sqlStr.UTF8String, -1, &stament, NULL);
if (result == SQLITE_OK) {
//遍历取出每一天记录的完整信息，当step执行的返回值为SQLITE_ROW时，就说明后面还有数据
while (sqlite3_step(stament) == SQLITE_ROW) {
//动态获得字段的总数
int sumCol = sqlite3_column_count(stament);
//初始化可变字典用来存放一条记录的信息
NSMutableDictionary *mDic = [NSMutableDictionary dictionary];
//遍历所有字段取出对应的字段值存入可变字典
for (int i = 0; i //确定字段类型
int type = sqlite3_column_type(stament, i);

//得到字段名称
//分别取出该条记录每一列的值
//第二个参数：第几列
//返回一个无符号常量char*
const char* colName = sqlite3_column_name(stament, i);
//取出字段的名称
NSString *key = [NSString stringWithCString:colName encoding:NSUTF8StringEncoding];

switch (type) {
case SQLITE_INTEGER:
{
//得到值
int value = sqlite3_column_int(stament,i);
//加入字典
[mDic setObject:@(value) forKey:key];
}
break;
case SQLITE_TEXT:
{
//得到值
const unsigned char* str = sqlite3_column_text(stament, i);
NSString *strValue = [NSString stringWithCString:(const char*)str encoding:NSUTF8StringEncoding];
//加入字典
[mDic setObject:strValue forKey:key];
}
break;
default:
break;
}

}//for
//添加到数组
[mArray addObject:mDic];
}
//关闭数据库，并且释放资源
sqlite3_close(mySqlite);
sqlite3_finalize(stament);
return mArray;
}
else
{
NSLog(@"sqlStr error");
return mArray;
}
}


把这几个方法写在类里，使用的时候导入并调用，还是比较方便的。

三、存储二进制类型和bind方法

如果我们要存储图片，该怎么办呢
有一种笨办法，是将图片转换为NSData的格式，再转换为NSString的格式，就可以存了


//UIImage转换为NSString
-(NSString*)imageToStringWithImage:(UIImage*)image
{
//先将image转换为data类型,后面参数是品质
NSData *imageData = UIImageJPEGRepresentation(image, 1.0);
//再讲NSData转换为NSString
NSString *string = [imageData base64EncodedStringWithOptions:NSDataBase64Encoding64CharacterLineLength];
return string;
}
//将base64的字符串转换为图片
-(UIImage*)base64StringToImage:(NSString*)base64Str
{
//将字符串转换为NSData
NSData *imageData = [[NSData alloc]initWithBase64EncodedString:base64Str options:NSDataBase64DecodingIgnoreUnknownCharacters];
//NSData到图片
UIImage *image = [UIImage imageWithData:imageData];
return image;
}


通用的方式应该是把图片转成二进制去存，记得我当年写VB+SQLServer作业的时候，想给软件加个图片显示，怎么存储图片就成了问题
后来度娘告诉了我，二进制的方法，具体我也忘了，不过我总算能让NBA球队的队标也显示出来了：）

好吧正题
image存入数据库的类型选择blob类型
sqlite3_bind方法用于绑定参数
比如：insert into bindImage(image) values (?);这句sql里面有个问号是等我们填的
sqlite3_bind_blob(【stament】,【1】,【要填的数据的起始指针】,【要填的二进制数据的长度】,【不咋用写nil就行】)
五个参数，1表示第一个问号

//插入一张图片 create table if not extists bindImage(image blob)
//先把图片转换为NSData格式
-(void)insertImageData:(NSData*)imageData
{
//打开数据库
sqlite3 *mySqlite = [self openDB];
//sql语句
NSString *sqlStr = @"insert into bindImage(image)values(?);";
//伴随指针
sqlite3_stmt *stament;
//准备
int result = sqlite3_prepare(mySqlite, sqlStr.UTF8String, -1, &stament, NULL);
if (result == SQLITE_OK) {
//bind绑定 data转换类型
// 第1个参数：是前面prepare得到的 sqlite3_stmt * 类型变量。

// 第2个参数：?号的索引。前面prepare的sql语句里有一个?号，假如有多个?号怎么插入？方法就是改变 bind_blob 函数第2个参数。
这个参数我写1，表示这里插入的值要替换 stat 的第一个?号（这里的索引从1开始计数，而非从0开始）。如果你有多个?号，就写多个 bind_blob 语句，
并改变它们的第2个参数就替换到不同的?号。如果有?号没有替换，sqlite为它取值null。

// 第3个参数：二进制数据起始指针。

// 第4个参数：二进制数据的长度，以字节为单位,如果是二进制类型绝对不可以给-1，必须具体长度。

// 第5个参数：是个析够回调函数，告诉sqlite当把数据处理完后调用此函数来析够你的数据。这个参数我还没有使用过，因此理解也不深刻。
但是一般都填NULL，需要释放的内存自己用代码来释放。
sqlite3_bind_blob(stament, 1, imageData.bytes, (int)imageData.length, NULL);
//执行插入sql语句如果不是查询语句，while改为if，SQLITE_ROW 改为 SQLite_DONE
if (sqlite3_step(stament) == SQLITE_DONE) {
NSLog(@"in ok");
}
else
{
NSLog(@"in fs");
}
}
else
{
NSLog(@"in fs2");
}
}
//查询
-(NSArray*)query
{
NSString *sqlStr = @"select * from bindImage";
sqlite3 *mySqlite = [self openDB];
sqlite3_stmt *stament;
NSMutableArray *array = [[NSMutableArray alloc]init];
int result = sqlite3_prepare(mySqlite, sqlStr.UTF8String, -1, &stament, NULL);
if (result == SQLITE_OK)
{
while (sqlite3_step(stament) == SQLITE_ROW) {
//所在的第0列
const voidvoid *imageValue = sqlite3_column_blob(stament, 0);
//每次获取blob类型的值，需要同时获得该值的大小
int length = sqlite3_column_bytes(stament, 0);
//从byte到NSData
NSData *resultData = [[NSData alloc]initWithBytes:imageValue length:length];
[array addObject:resultData];
}
}
else
{
NSLog(@"sel fs，%d",result);
}
//释放资源
sqlite3_close(mySqlite);
sqlite3_finalize(stament);
//关闭后给个nil
mySqlite = nil;
return array;
}