1.创建数据库
NSArray *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *filePath = [[path lastObject] stringByAppendingPathComponent:@"yourName.sqlite"];
//创建数据库
FMDatabase *db = [FMDatabase databaseWithPath:filePath];
//打开数据库
if ([db open]) {
NSLog(@"打开数据库成功");
}else{
NSLog(@"打开数据库失败");
}
2.建表
/*
如果t_student这个表不存在就创建，并且data不能为null，id为自增的主键
*/
NSString *createTable = @"create table if not exists t_student (id integer primary key autoincrement,data blob not null)";
[db executeUpdate:createTable];
不同类型的判空
//字符串name不能为空
name text not null
//数据类型age不能为空
age integer not null
// 对象类型data不能为空
data blob not null
对数据库中存储的每一个值都有一个类型
NULL 这个值为空值
INTEGER 值被标识为整数，依据值的大小可以依次被存储1～8个字节
REAL 所有值都是浮动的数值
TEXT 值为文本字符串
BLOB 值为blob数据
3.插入数据
/*
向数据库的t_student这个表中的name和age两个字段插入数据
*/
NSArray *ary = @[
@{@"name":@"porsche",@"age":@"22"},
@{@"name":@"kinn",@"age":@"29"},
];
1）存储字符串
for (NSDictionary *dic in ary) {
NSString *insertData = @"insert into t_student (name,age) values (?,?)";
[db executeUpdate:insertData,dic[@"name"],dic[@"age"]];
}
2）存储数组
NSError *err = nil;
NSData *jsonData = [NSJSONSerialization dataWithJSONObject:ary options:NSJSONWritingPrettyPrinted error:&err];
NSString *jsonStr = [[NSString alloc] initWithData:jsonData encoding:NSUTF8StringEncoding];
NSString *insertData = @"insert into t_student (data) values (?)";
[db executeUpdate:insertData,jsonStr];
4.查询数据
1）查询字符串
NSString *sql = @"select id,name,age FROM t_student";
FMResultSet *rs = [db executeQuery:sql];
NSMutableArray *saveAry = [@[] mutableCopy];
while ([rs next]) {
int id = [rs objectForColumnIndex:0];
NSString *name = [rs stringForColumnIndex:1];
NSString *age = [rs stringForColumnIndex:2];
NSDictionary *studentDict = [NSDictionary dictionaryWithObjectsAndKeys: name, @"name",age,@"age", nil];
[saveAry addObject:studentDic];
}
2）查询数组
NSString *sql = @"select * FROM t_student";
FMResultSet *rs = [db executeQuery:sql];
NSMutableArray *saveAry = [@[] mutableCopy];
while ([rs next]) {
int id = [rs objectForColumnIndex:0];
id data = [rs objectForColumnIndex:1];
[saveAry addObject:data];
}
3）数组去重
NSSet *set = [NSSet setWithArray:saveAry];
NSArray *newArray = [set allObjects];
5.删除表
if (![db open]) {
return;
}
NSString *deleteSql = @"DELETE FROM t_student";
BOOL res = [db executeUpdate:deleteSql];
if (res) {
NSLog(@"删除成功");
}else{
NSLog(@"删除失败");
}
