iOS个人整理27-SQLite3和基础的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 '|'

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 表名称//查一列
<pre>SELECT * FROM 表名称//查全表
//加入where过滤，多重条件用and并列
<pre>SELECT 列名称 FROM 表名称 WHERE 列 运算符 值          //(name = 'LI')

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 < sumCol; 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 void *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;
}