NSArray *documentsPaths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask , YES);
NSString *databaseFilePath=[[documentsPaths objectAtIndex:0] stringByAppendingPathComponent:@"mydb"];
//上面两句已经比较熟悉了吧!
//打开数据库
if (sqlite3_open([databaseFilePath UTF8String], &database)==SQLITE_OK) {
NSLog(@"sqlite dadabase is opened.");
}
else{ return;}//打开不成功就返回
char *error;
const char *createSql="create table(id integer primary key autoincrement, name text)";
if (sqlite3_exec(database, createSql, NULL, NULL, &error)==SQLITE_OK) {
NSLog(@"create table is ok.");
}
else
{
NSLog(@"error: %s",error);
sqlite3_free(error);//每次使用完毕清空error字符串,提供给下一次使用
}
const char *insertSql="insert into a person (name) values(‘gg’)";
if (sqlite3_exec(database, insertSql, NULL, NULL, &error)==SQLITE_OK) {
NSLog(@"insert operation is ok.");
}
else
{
NSLog(@"error: %s",error);
sqlite3_free(error);//每次使用完毕清空error字符串,提供给下一次使用
}
const char *selectSql="select id,name from a person";
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(database,selectSql, -1, &statement, nil)==SQLITE_OK) {
NSLog(@"select operation is ok.");
}
else
{
NSLog(@"error: %s",error);
sqlite3_free(error);
}
while(sqlite3_step(statement)==SQLITE_ROW) {
int _id=sqlite3_column_int(statement, 0);
NSString *name=(char*)sqlite3_column_text(statement, 1);
NSLog(@"row>>id %i, name %s",_id,name);
}
sqlite3_finalize(statement);
sqlite3_close(database);
注意:
写入数据库,字符串可以采用char方式,而从数据库中取出char类型,当char类型有表示中文字符时,会出现乱码。这是因为数据库默认使用ascII编码方式。所以要想正确从数据库中取出中文,需要用NSString来接收从数据库取出的字符串。// 继承
# pod 'FMDB/SQLCipher'
NSString *path = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"shops.sqlite"];
self.db = [FMDatabase databaseWithPath:path];
[self.db open];
[self.db executeUpdate:@"CREATE TABLE IF NOT EXISTS t_shop (id integer PRIMARY KEY, name text NOT NULL, price real);"];
// executeQuery:查询数据
// [self.db executeQuery:NSString *];
// executeUpdate:除查询数据以外的其他操作
// [self.db executeUpdate:NSString *];
[self.db executeUpdate:@"DELETE FROM t_shop WHERE price < 800;"];
FMResultSet *set = [self.db executeQuery:@"SELECT * FROM t_shop;"];
// 不断往下取数据
while (set.next) {
// 获得当前所指向的数据
NSString *name = [set stringForColumn:@"name"];
double price = [set doubleForColumn:@"price"];
NSLog(@"%@ %f", name, price);
}
for (int i = 0; i<100; i++) {
NSString *name = [NSString stringWithFormat:@"手机-%d", i];
#warning 这里的字符串不用再加上''
[self.db executeUpdateWithFormat:@"INSERT INTO t_shop(name, price) VALUES (%@, %d);", name, arc4random()%1000];
}