#import <sqlite3.h>

@interface ViewController ()
{
 sqlite3 *_sqldb;
}

@end

@implementation ViewController

- (void)viewDidLoad
{
 [super viewDidLoad];
 // Do any additional setup after loading the view, typically from a nib.
 [self OpenDb];
 [self createTable];
 [self insertData];
 [self FindData];
}


//打開(kāi)數(shù)據(jù)庫(kù)

-(void)OpenDb{
 
 NSArray *arrs= NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
 //創(chuàng)建數(shù)據(jù)庫(kù)，如果數(shù)據(jù)庫(kù)存在就直接打開(kāi)，不存在就創(chuàng)建打開(kāi)
 NSString *path=[arrs lastObject] ;
 NSString *documentpath= [path stringByAppendingPathComponent:@"sql.db"];
 int reslut= sqlite3_open([documentpath UTF8String], &_sqldb);
 if(reslut==SQLITE_OK){
 NSLog(@"數(shù)據(jù)庫(kù)已被打開(kāi)");
 }
 
}
//通過(guò)數(shù)據(jù)庫(kù)實(shí)例創(chuàng)建表
-(void)createTable{
 //不帶參數(shù)的sql語(yǔ)句
 const char* sql="create table if not exists t_person (id integer primary key autoincrement,name text,age integer);";
 char *error;
 //sqlite3_exec可以執(zhí)行一切不帶參數(shù)的SQL語(yǔ)句。如果是帶參數(shù)最好不用，防止SQL注入漏洞攻擊
 int resutl= sqlite3_exec(_sqldb, sql, NULL, NULL, &error);
 if(resutl==SQLITE_OK){
 NSLog(@"創(chuàng)建表成功");
 }
else{
 NSLog(@"創(chuàng)建表失?。?s",error);
}
}

//插入數(shù)據(jù)
-(void)insertData{
 //帶參數(shù)的SQL語(yǔ)句 "?"是帶參數(shù)的占位符
 const char * sql="insert into t_person(name,age) values(?,?);";
 sqlite3_stmt *stmp;
 //在執(zhí)行SQL語(yǔ)句之前檢查SQL語(yǔ)句語(yǔ)法,-1代表字符串的長(zhǎng)度
 int result= sqlite3_prepare_v2(_sqldb, sql, -1, &stmp, NULL);
 if(result==SQLITE_OK){
 NSLog(@"插入SQL語(yǔ)句語(yǔ)法沒(méi)有問(wèn)題");
 //綁定參數(shù),插入的參數(shù)的下標(biāo)是從1開(kāi)始
 sqlite3_bind_text(stmp, 1, "gcb", -1, NULL);
 sqlite3_bind_int(stmp, 2, 12);
 
 //執(zhí)行參參數(shù)的SQL語(yǔ)句，不能有exec
 int result=sqlite3_step(stmp);
 //插入進(jìn)行判斷,要用sqLite_Done來(lái)判斷
 if(result==SQLITE_DONE){
  NSLog(@"插入成功");
 }
 else{
  NSLog(@"插入失敗") ;
 }
 
 }
 else{
 NSLog(@"插入SQL語(yǔ)句有問(wèn)題");
 }
}

-(void)FindData{
 char *sql="select id,name,age from t_person";
 //查詢做好用step執(zhí)行
 sqlite3_stmt *stmt;
 //檢查SQL語(yǔ)句的語(yǔ)法問(wèn)題
 int result= sqlite3_prepare_v2(_sqldb, sql, -1, &stmt, NULL);
 if(result==SQLITE_OK){
 while (sqlite3_step(stmt)==SQLITE_ROW) {
  //查詢的列是0開(kāi)始 插入的列從1開(kāi)始
//  int xh＝sqlite3_column_int(stmt, 0);
  int xh=sqlite3_column_int(stmt, 0);
  char * name=(char *)sqlite3_column_text(stmt, 1);
  int age=sqlite3_column_int(stmt, 2);
  NSLog(@"xh=%i-->name=%s-->age=%i",xh,name,age);
  
  
  
 }
 }
 else{
 NSLog(@"查詢SQL語(yǔ)法有誤");
 }

}