iOS－SQLite数据库总结

2017-07-13 本文已影响25人彬至睢阳

数据库存在的意义就是对数据进行整合和管理，即对数据进行增，删，改，查的操作。

1.创建表的格式为

create table 表名(参数名1 类型修饰条件，参数名2，类型修饰参数，···)

sqlite中支持如下的类型：

smallint 短整型

integer 整型

real 实数型

float 单精度浮点

double 双精度浮点

currency 长整型

varchar 字符型

text 字符串

binary 二进制数据

blob 二进制大对象

boolean 布尔类型

date 日期类型

time 时间类型

timestamp 时间戳类型

关于修饰条件，常用的有如下几种：

PRIMARY KEY：将本参数这个为主键，主键的值必须唯一，可以作为数据的索引，例如编号。

NOT NULL ：标记本参数为非空属性。

UNIQUE：标记本参数的键值唯一，类似主键。

DEFAULT:设置本参数的默认值

CHECK：参数检查条件，例如上面代码，写入数据是count必须大于时才有效

2.添加数据格式如下

insert into 表名(键1，键2，···) values(值1，值2，···)

3.修改数据的格式如下

update 表名 set 键1=值1，键2=值2 where 条件－－即修改数据的条件

4.删除数据的格式

delete from 表名 where 条件

5.删除一张表的格式

drop table 表名

6.查询操作的格式

select 键名，键名··· from 表名

select 键名，键名，··· from 表名 order by 键名排序方式

order by 后面写要进行排序的键名，排序方式有 asc升序 desc降序

查找数据条数与查找位置限制：select 键名 from 表名 limit 最大条数 offset 查询起始位置

条件查询：select 键名 from 表名 where 条件

去重查询：select distinct 键名 from 表名

总结类－－－－

#import#import/** *sql数据库支持的类型宏定义 */#define YHBASE_SQL_DATATYPE_SMALLINT @"smallint" //short#define YHBASE_SQL_DATATYPE_INTRGER @"integer" //int#define YHBASE_SQL_DATATYPE_REAL @"real" //实数#define YHBASE_SQL_DATATYPE_FLOAT @"float" //float#define YHBASE_SQL_DATATYPE_DOUBLE @"double" //double#define YHBASE_SQL_DATATYPE_CURRENCY @"currency" //long#define YHBASE_SQL_DATATYPE_VARCHAR @"varchar" //char#define YHBASE_SQL_DATATYPE_TEXT @"text" //string#define YHBASE_SQL_DATATYPE_BINARY @"binary" //二进制#define YHBASE_SQL_DATATYPE_BLOB @"blob" //长二进制#define YHBASE_SQL_DATATYPE_BOOLEAN @"boolean" //bool#define YHBASE_SQL_DATATYPE_DATE @"date" //日期#define YHBASE_SQL_DATATYPE_TIME @"time" //时间#define YHBASE_SQL_DATATYPE_TIMESTAMP @"timestamp"//时间戳#define YHBASE_SQL_ORDERTYPE_ASC @"asc" //升序

#define YHBASE_SQL_ORDERTYPE_DESC @"desc"

//降序@interface Sqlite3Context : NSObject/** 操作的数据库名称 */

@property (nonatomic, strong) NSString* name;

/** sqlite3对象 */@property (nonatomic, assign) sqlite3* sqlite3_db

;/** 1. 打开一个数据库不存在则创建

2.path:数据库路径

3.return 是否操作成功 */

- (BOOL)openDataBaeWithName:(NSString* )path;

/** 1.在数据库中创建一张表，如果已经存在，则返回错误信息

2.表的名称

3.表中的键其中的字典中需传入键名：类型

4.calllBack 结果回调 */

- (void)createTableWithName:(NSString* )name keysDictionary:(NSDictionary*)dic callBack:(void (^)(Sqlite3Context* error))complete;/**

1.向表中添加一条数据 2.添加数据的键值对 3.插入表的名称 4.complete回调 */

- (void)insertData:(NSDictionary*)dataDict intoTable:(NSString* )name callBack:(void (^)(Sqlite3Context* error))complete;/**

1.向表中添加一个键 2.keyName-添加的键 3.type-类型 4.tableName--表名称 5.complete --结果回调 */

- (void)addKey:(NSString *)keyName keyType:(NSString *)type intoTable:(NSString *)tableName callBack:(void (^)(Sqlite3Context *error))complete;

/** 1.修改数据 2.dataDict --新的键值 3.whileStr--- 条件字符串一般通过主键找到对应数据修改可以为nil 4.complete---结果回调 */

- (void)update:(NSDictionary*)dataDict inTable:(NSString* )tableName whileString:(NSString*)whileStr callBack:(void(^)(Sqlite3Context *error))complete;

/** 1.删除数据 2.tableName 表名 3.whileStr 条件字符串一般通过主键找到对应数据删除可以为nil 不传这个参数将删除所有数据

*/- (void)deleteDataFromTable:(NSString *)tableName whereString:(NSString *)whileStr callBack:(void (^)(Sqlite3Context* error))complete;

/** 1.删除一张表 2.tableName--表名 */- (void)dropTable:(NSString *)tableName callBack:(void(^)(Sqlite3Context* error))complete;

/** 1.查询数据 2.keys ---要查询的键值及其对应的数据类型如果为nil则查询全部 3.tableName --表名 4.orderKey 进行排序的键值如果为nil,则不排序 5.type 排序方式 6。whileStr查询条件等同于查询单个数据 7.complete - dataArray 为查询到的数据其内为字典 */

- (void)selectKeys:(NSArray*)keys fromTable:(NSString *)tableName orderBy:(NSString *)orderKey orderType:(NSString* )type whileStr:(NSString *)whileStr callBack:(void(^)(NSArray* dataArray,Sqlite3Context* error))complete;

/**

1.关闭数据库上下文操作

2.调用此方法之后这个context对象将不再有效如果需要使用，需要在ssqliteManager中的类方法再次返回

- (void)closeContext;

异常提示信息

@property (nonatomic, strong) NSString* errorInfo;

异常对应的code 码

@property (nonatomic, assign) NSInteger errorCode;

@end

#import "Sqlite3Context.h"

@implementation Sqlite3Context

- (instancetype)init{

if (self = [super init]) {

} return self;}

#pragma mark-----打开一个数据库不存在则创建

- (BOOL)openDataBaeWithName:(NSString *)path{

if (sqlite3_open(path.UTF8String, &_sqlite3_db) !=SQLITE_OK) { sqlite3_close(_sqlite3_db);

_sqlite3_db = nil;

return NO; }

else{ return YES;

}}

#pragma mark----在数据库中创建一张表，如果已经存在，则返回错误信息－dic中的key为所创造的表的参数名；value为修饰条件- (void)createTableWithName:(NSString *)name keysDictionary:(NSDictionary*)dic callBack:(void (^)(Sqlite3Context *))complete{

NSMutableString * keys = [[NSMutableString alloc]init];

for (int i=0; i< dic.allKeys.count;i++){

NSString * key = dic.allKeys[i];

NSLog(@"%@",key);

if (i<dic.allKeys.count-1){

[keys appendFormat:@"%@ %@,",key,[dic objectForKey:key]];

}else{

[keys appendFormat:@"%@ %@",key,[dic objectForKey:key]];

}

NSString* sqlStr = [NSString stringWithFormat:@"create table %@(%@)",name,keys];

NSLog(@"%@",sqlStr);

[self runSQL:sqlStr callBack:^(Sqlite3Context *error) {

if (complete) {

complete(error);

}

}];

}

#pragma mark---插入数据

- (void)insertData:(NSDictionary*)dataDict intoTable:(NSString *)name callBack:(void (^)(Sqlite3Context *))complete{

NSMutableString* keys = [[NSMutableString alloc]init];

NSMutableString* values = [[NSMutableString alloc]init];

for (NSInteger index =dataDict.allKeys.count-1; index>=0; index--) {

NSString* key1 = dataDict.allKeys[index];

if (index <= dataDict.allKeys.count-1) {

if (index >0) {

[keys appendFormat:@"%@,",key1];

[values appendFormat:@"\"%@\",",[dataDict objectForKey:key1]];

}else if (index == 0){

[keys appendFormat:@"%@",key1];

[values appendFormat:@"\"%@\"",[dataDict objectForKey:key1]];

} } }

NSString* sqlStr = [NSString stringWithFormat:@"insert into %@(%@) values(%@)",name,keys,values];

[self runSQL:sqlStr callBack:^(Sqlite3Context *error) {

if (complete) { complete(error);

} }];}

#pragma mark-----向表中添加一个键

- (void)addKey:(NSString *)keyName keyType:(NSString *)type intoTable:(NSString *)tableName callBack:(void (^)(Sqlite3Context *))complete{

NSString* sqlStr = [NSString stringWithFormat:@"alter table %@ add %@ %@",tableName,keyName,type];

[self runSQL:sqlStr callBack:^(Sqlite3Context *error) {

if (complete) {

complete(error);

} }];}

#pragma mark----修改数据--whileStr 为空即可

- (void)update:(NSDictionary*)dataDict inTable:(NSString *)tableName whileString:(NSString *)whileStr callBack:(void (^)(Sqlite3Context *))complete{ NSMutableString* sqlStr = [[NSMutableString alloc]init];

[sqlStr appendFormat:@"update %@ set ",tableName];

for (int index = 0; index < dataDict.allKeys.count; index++) {

NSString* key = dataDict.allKeys[index];

if (index < dataDict.allKeys.count - 1) {

[sqlStr appendFormat:@"%@=\"%@\",",key,[dataDict objectForKey:key]];

}

else{ [sqlStr appendFormat:@"%@=\"%@\"",key,[dataDict objectForKey:key]];

if (whileStr != nil) {

[sqlStr appendFormat:@" where %@",whileStr];

} } }

[self runSQL:sqlStr callBack:^(Sqlite3Context *error) { if (complete) { complete(error);

} }];}

#pragma mark---删除数据

- (void)deleteDataFromTable:(NSString *)tableName whereString:(NSString *)whileStr callBack:(void (^)(Sqlite3Context *))complete{

NSMutableString* sqlStr = [[NSMutableString alloc]init];

[sqlStr appendFormat:@"delete from %@",tableName]; if (whileStr != nil) {

[sqlStr appendFormat:@" where %@",whileStr]; }

[self runSQL:sqlStr callBack:^(Sqlite3Context *error) {

if (complete) {

complete(error);

} }];}

#pragma mark----删除一张表

- (void)dropTable:(NSString *)tableName callBack:(void (^)(Sqlite3Context *))complete{ NSString* sqlStr = [NSString stringWithFormat:@"drop table %@",tableName]; [self runSQL:sqlStr callBack:^(Sqlite3Context *error) {

if (complete) {

complete(error);

} }];}

#pragma mark---查询数据

- (void)selectKeys:(NSArray*)keys fromTable:(NSString *)tableName orderBy:(NSString *)orderKey orderType:(NSString *)type whileStr:(NSString *)whileStr callBack:(void (^)(NSArray*, Sqlite3Context *))complete{

NSMutableString* sqlStr = [[NSMutableString alloc]init];

[sqlStr appendFormat:@"select"];

if (keys==nil || keys.count ==0) {

[sqlStr appendFormat:@" * from %@",tableName];

}

else{

for (int index = 0; index < keys.count; index++) {

if (index < keys.count-1) {

[sqlStr appendFormat:@" %@,",keys[index].allKeys.firstObject]; }

else{

[sqlStr appendFormat:@" %@ from %@",keys[index].allKeys.firstObject,tableName];

} } }

if (whileStr) {

[sqlStr appendFormat:@" where %@",whileStr];

}

if (orderKey) {

[sqlStr appendFormat:@" order by %@",orderKey]; }

if (type) {

[sqlStr appendFormat:@" %@",type];

}

NSMutableArray* keysArr = [[NSMutableArray alloc]init];

NSMutableArray* keysTypeArr = [[NSMutableArray alloc]init];

if (keys==nil ||keys.count == 0) {

NSArray* tmpArr = [self getTheTableAllKeys:tableName]; for (int index = 0; index < tmpArr.count; index++) {

NSString* key = tmpArr[index].allKeys.firstObject;

[keysArr addObject:key];

[keysTypeArr addObject:[tmpArr[index] objectForKey:key]];

}

}else{

for (int index = 0; index < keys.count; index++) {

NSString* key = keys[index].allKeys.firstObject;

[keysArr addObject:key];

[keysTypeArr addObject:[keys[index] objectForKey:key]];

} }

[self runSelectSQl:sqlStr withKeys:keysArr withDataType:keysTypeArr callBack:^(NSArray*dataArray, Sqlite3Context *error) {

if (complete) {

complete(dataArray,error);

} }];}

#pragma mark------关闭数据库上下文操作- (void)closeContext{ sqlite3_close(_sqlite3_db);

_sqlite3_db = nil;

}

#pragma mark----内部方法－运行创建独立的非查询Sqlite语句

- (void)runSQL:(NSString *)sql callBack:(void(^)(Sqlite3Context* error))complete{

char* error;

int code = sqlite3_exec(_sqlite3_db,sql.UTF8String, NULL, NULL, &error);

if (code != SQLITE_OK) {

Sqlite3Context* err = [[Sqlite3Context alloc]init];

err.errorInfo = [NSString stringWithCString:error encoding:NSUTF8StringEncoding]; err.errorCode = code;

complete(err); }

else{

complete(nil);

}}

#pragma mark----运行查询语句

- (void)runSelectSQl:(NSString *)sql withKeys:(NSArray *)keys withDataType:(NSArray *)dataTYpe callBack:(void (^)(NSArray* dataArray,Sqlite3Context* error))complete{

sqlite3_stmt* stmt = nil;

int code = sqlite3_prepare_v2(_sqlite3_db, sql.UTF8String, -1, &stmt, NULL);

if (code != SQLITE_OK) {

Sqlite3Context* error = [[Sqlite3Context alloc]init];

error.errorInfo = @"查询失败";

error.errorCode = code;

complete(nil,error);

}else{

NSMutableArray* resultArr = [[NSMutableArray alloc]init];

while (sqlite3_step(stmt) == SQLITE_ROW) {

//数据类型的分别解析

NSMutableDictionary* dic = [[NSMutableDictionary alloc]init];

for (int i = 0; i < dataTYpe.count; i++) {

NSString* type = dataTYpe[i];

if ([type isEqualToString:YHBASE_SQL_DATATYPE_BINARY]) {//二进制

int length = sqlite3_column_bytes(stmt, i);

const void* data = sqlite3_column_blob(stmt, i);

NSData* value = [NSData dataWithBytes:data length:length];