ios Sqlite3的简单使用 增删改查

- (void)viewDidLoad {

    [superviewDidLoad];

    

    // 数据库操作句柄

    sqlite3 *dbHandle;

    

    // 数据库路径

    NSString *documentPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES)[0];

    NSString *dbPath = [documentPathstringByAppendingPathComponent:@"stock.db"];

    

   //打开数据库获取句柄不存在则会创建

    int result =sqlite3_open([dbPathUTF8String], &dbHandle);

    if (result ==SQLITE_OK) {

        //创建表

        NSString * sql = @"CREATE TABLE IF NOT EXISTS t_stock_info (Number text PRIMARY KEY,Name text NOT NULL,Data text NOT NULL,FinalPrice float NOT NULL);";

        char *error =NULL;

        result = sqlite3_exec(dbHandle, [sql UTF8String], NULL,NULL, &error);

        if (result ==SQLITE_OK) {

            

        }

        else

        {

            [ShowAlertControllershowAlertInVC:self.view.window.rootViewControllermessage:@"Error"title:@"Open / Create table failed!"];

        }

    }

    else

    {

        [ShowAlertControllershowAlertInVC:self.view.window.rootViewControllermessage:@"Error"title:@"Open / Create database failed!"];

    }

    

    [selfinsertDataNumber:@"600582"name:@"天地科技"data:@"2017.2.21"finalPrice:5.4toSqliteDB:dbHandlewithTableName:@"t_stock_info"];

    [selfinsertDataNumber:@"002266"name:@"浙富控股"data:@"2017.2.23"finalPrice:5.8toSqliteDB:dbHandlewithTableName:@"t_stock_info"];

    

    [selfgetInfoFromTable:@"t_stock_info"inSqliteDB:dbHandle];

    

    [selfdeletName:@"Name"realName:@"浙富控股"inSqliteDB:dbHandletbName:@"t_stock_info"];

    

    [selfupdatePrice:5.7name:@"FinalPrice"inSqliteDB:dbHandletbName:@"t_stock_info"];

}

#pragma mark - 增

-(void)insertDataNumber:(NSString *)number name:(NSString *)name data:(NSString *)data finalPrice:(float)price toSqliteDB:(sqlite3 *)dbHandle withTableName:(NSString *)tbName

{

    NSString *sql=[NSStringstringWithFormat:@"INSERT INTO %@ (Number,Name,Data,FinalPrice) VALUES ('%@','%@','%@',%f);",tbName,number,name,data,price];

    

    char *errmsg =NULL;

    int result =sqlite3_exec(dbHandle, sql.UTF8String,NULL,NULL, &errmsg);

    if (result ==SQLITE_OK) {

        

    }

    else

    {

        

    }

}

#pragma mark - 查

-(void)getInfoFromTable:(NSString *)tbName inSqliteDB:(sqlite3 *)dbHandle

{

    NSString *sql = @"SELECT * FROM t_stock_info WHERE FinalPrice>=0;";

    sqlite3_stmt *stmt =NULL;

   //进行查询前的准备工作

    int result =sqlite3_prepare_v2(dbHandle, [sqlUTF8String], -1, &stmt,NULL);

    if (result ==SQLITE_OK) {//SQL语句没有问题

    // 每调用一次sqlite3_step函数，stmt就会指向下一条记录

    NSLog(@"Begin get info");

    while (sqlite3_step(stmt) ==SQLITE_ROW) {//找到一条记录

    // 取出数据

         char *number = (char *)sqlite3_column_text(stmt,0);

         char *name = (char *)sqlite3_column_text(stmt,1);

         char *data = (char *)sqlite3_column_text(stmt,2);

         float price =sqlite3_column_double(stmt,3);

        

        NSLog(@"number = %@ ,name = %@ ,data %@,price %f",[NSStringstringWithUTF8String:number],[NSStringstringWithUTF8String:name],[NSStringstringWithUTF8String:data],price);

        }

    }

    else

    {

        

    }

}

#pragma mark - 改

-(void)updatePrice:(float)price name:(NSString *)name inSqliteDB:(sqlite3 *)dbHandle tbName:(NSString *)tbName

{

    NSString *sql = [NSStringstringWithFormat:@"UPDATE %@ SET %@ = %f WHERE Name == '天地科技';",tbName,name,price];

    sqlite3_stmt *stmt =NULL;

    

   //进行查询前的准备工作

    int result =sqlite3_prepare_v2(dbHandle, [sqlUTF8String], -1, &stmt,NULL);

    if (result ==SQLITE_OK) {//SQL语句没有问题

            if (sqlite3_step(stmt) ==SQLITE_DONE) {

                sqlite3_finalize(stmt);

                

                [selfgetInfoFromTable:tbNameinSqliteDB:dbHandle];

            }

    }

    else

    {

        

    }

}

#pragma mark - 删

-(void)deletName:(NSString *)name realName:(NSString *)realName inSqliteDB:(sqlite3 *)dbHandle tbName:(NSString *)tbName

{

    NSString *sql = [NSStringstringWithFormat:@"DELETE FROM %@ WHERE %@ == '%@';",tbName,name,realName];

    sqlite3_stmt *stmt =NULL;

    

   //进行查询前的准备工作

    int result =sqlite3_prepare_v2(dbHandle, [sqlUTF8String], -1, &stmt,NULL);

    if (result ==SQLITE_OK) {//SQL语句没有问题

        if (sqlite3_step(stmt) ==SQLITE_DONE) {

            sqlite3_finalize(stmt);

            

            [selfgetInfoFromTable:tbNameinSqliteDB:dbHandle];

        }

    }

    else

    {

        

    }

}