sqlite3基本操作
在移动设备上进行高性能高效率的大量数据存储，我们一般用得时sqlite这款轻巧型的数据库，这里介绍其增删改查基本功能
在ios开发中我们需要先导入"libsqlite3.dylib"这个动态库，然后在控制器中#import "sqlite3.h"
在类扩展中定义成员变量：sqlite3 *_db
然后定义数据库文件在手机沙盒中的存储位置以及数据库名称：
NSString *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
NSString *fileName = [path stringByAppendingPathComponent:@"myData.sqlite"];
执行打开数据库操作：sqlite3_open([fileName UTF8String], &_db)
如果数据库是正常打开，我们可以根据需要来创建数据表：
CREATE TABLE IF NOT EXISTS cPerson(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,age INTEGER)
然后可以对数据表的数据进行增删改查等操作：
增：INSERT INTO cPerson (id, name, age) VALUES ('2','hehe','20')"
删：DELETE FROM cPerson WHERE id = 2
改：UPDATE cPerson set name = 'ashui' where id = 2
查：SELECT * FROM cPerson
最后执行关闭数据库操作：sqlite3_close(_db)
绑定VALUES(?,?)中?的值：sqlite3_bind_text(sqlite3_stmt *, int, const char *,int n, void (*)(void *))
具体代码实现：
1 #import "WYSViewController.h" 2 #import "sqlite3.h" 3 4 @interface WYSViewController (){ 5 6 // 数据库 7 sqlite3 *_db; 8 } 9 10 @end 11 12 @implementation WYSViewController 13 14 - (void)viewDidLoad 15 { 16 [super viewDidLoad]; 17 18 NSString *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0]; 19 NSString *fileName = [path stringByAppendingPathComponent:@"myData.sqlite"]; 20 21 // 打开数据库 22 if (sqlite3_open([fileName UTF8String], &_db) == SQLITE_OK){ 23 24 NSLog(@"数据库打开成功"); 25 26 // 如果不存在，则创建表 27 NSString *cPerson = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS cPerson(id integer primary key autoincrement,name text,age integer)"]; 28 [self executeWithString:cPerson]; 29 30 } 31 } 32 33 // 执行操作 34 - (void)executeWithString:(NSString *)string 35 { 36 char *error = nil; 37 38 if (sqlite3_exec(_db, [string UTF8String], NULL, NULL, &error) == SQLITE_OK){ 39 40 NSLog(@"操作成功"); 41 } 42 43 } 44 45 - (IBAction)insertData:(id)sender{ 46 47 NSString *iP1 = [NSString stringWithFormat:@"INSERT INTO cPerson (id, name, age) VALUES ('%d','%@','%d')",2,@"hehe",20]; 48 [self executeWithString:iP1]; 49 50 NSString *iP2 = [NSString stringWithFormat:@"INSERT INTO cPerson (id, name, age) VALUES ('%d','%@','%d')",3,@"haha",22]; 51 [self executeWithString:iP2]; 52 } 53 54 // 删除数据 55 - (IBAction)deleteData:(id)sender { 56 57 NSString *dPerson = [NSString stringWithFormat:@"DELETE FROM cPerson WHERE id = 2"]; 58 59 [self executeWithString:dPerson]; 60 61 // 这种删除方式也行 62 // sqlite3_stmt *statement; // 用于保存编译好的SQL语句 63 // 64 // if (sqlite3_prepare_v2(_db, [dPerson UTF8String], -1, &statement, nil) == SQLITE_OK){ 65 // 66 // while (sqlite3_step(statement) == SQLITE_ROW) { 67 // 68 //// if (sqlite3_step(statement) == SQLITE_DONE){ 69 // 70 //// } 71 // } 72 // 73 // NSLog(@"删除成功"); 74 // 75 // sqlite3_finalize(statement); 76 // } 77 78 // sqlite3_close(_db); 79 } 80 81 // 更新数据 82 - (IBAction)updataData:(id)sender { 83 84 NSString *uPerson = [NSString stringWithFormat:@"UPDATE cPerson set name = 'shuige' where id = 3"]; 85 86 [self executeWithString:uPerson]; 87 88 89 } 90 91 // 查询数据 92 - (IBAction)selectData:(id)sender { 93 94 95 NSString *sPerson = @"SELECT * FROM cPerson"; 96 sqlite3_stmt *statement; 97 98 if (sqlite3_prepare_v2(_db, [sPerson UTF8String], -1, &statement, nil) == SQLITE_OK){ 99 100 while(sqlite3_step(statement) == SQLITE_ROW){ 101 102 int id = sqlite3_column_int(statement, 0); 103 104 char *name = (char *)sqlite3_column_text(statement, 1); 105 106 NSString *nameStr = [[NSString alloc] initWithUTF8String:name]; 107 108 int age = sqlite3_column_int(statement, 2); 109 110 NSLog(@"id:%d name:%@ age:%d",id,nameStr,age); 111 } 112 113 sqlite3_finalize(statement); 114 } 115 116 } 117 @end
