sqlite 簡單使用

標籤：

////  ViewController.m//  sqlitedemo////  Created by lam_TT on 15-4-11.//  Copyright (c) 2015年 lam_TT. All rights reserved.//#import "ViewController.h"#import <sqlite3.h>@interface ViewController () {     sqlite3 *_db;}@end@implementation ViewController- (void)viewDidLoad {    [super viewDidLoad];    [self openSQLite];    [self initButton];    }- (void)initButton {    NSArray *array = [[NSArray alloc]initWithObjects:@"增",@"刪",@"改",@"查", nil];    for (int i = 0; i < 4; i ++) {        UIButton *button = [[UIButton alloc]init];        button.bounds = CGRectMake(0, 0, 150, 30);        button.tag = 10 + i;        [button setTitle:array[i] forState:UIControlStateNormal];        button.backgroundColor = [UIColor grayColor];        [button addTarget:self action:@selector(buttonPressed:) forControlEvents:UIControlEventTouchUpInside];        button.center = CGPointMake(CGRectGetWidth(self.view.bounds)/2, 100 + i * 50);        [self.view addSubview:button];    }}#pragma mark - 按鈕點擊事件- (void)buttonPressed:(UIButton *)sender {    NSInteger index = sender.tag - 10;        switch (index) {        case 0:            [self initInsertData];            break;                    case 1:            [self initDelete];            break;        case 2:            [self initUpdata];            break;        case 3:            [self initSelect];            break;    }}#pragma mark - 建立資料庫- (void)openSQLite {//    sqlite3 *db;    //路徑    NSString *string = [NSString stringWithFormat:@"%@/Documents",NSHomeDirectory()];    NSString *fileName = [string stringByAppendingString:@"/students.sqlite"];    //OC-C    const char *cfileName = fileName.UTF8String;        //開啟資料庫，沒有就自動建立    int result = sqlite3_open(cfileName, &_db);        if (result == SQLITE_OK) {        NSLog(@"成功開啟資料庫");    }else {        NSLog(@"開啟資料庫失敗");    }    //可用sqliteManger查看    #pragma mark - 建立表        const char  *sql="CREATE TABLE IF NOT EXISTS t_students (id integer PRIMARY KEY AUTOINCREMENT,name text NOT NULL,age integer NOT NULL);";        char *errmsg = NULL;        //參數：第一個參數為資料庫的控制代碼（db），第二個參數為sql語句，第三個參數為回調參數，是一個指向函數的指標，如果把callback前面的*改成^則就是一個block程式碼片段，第四個參數可以寫NULL，第五個參數為錯誤資訊，用以代碼調試。    result = sqlite3_exec(_db, sql, NULL, NULL, &errmsg);        if (result == SQLITE_OK) {        NSLog(@"建立表成功");    }else {        printf("創表失敗---%s----%s---%d",errmsg,__FILE__,__LINE__);    }}#pragma mark - 插入資料- (void)initInsertData {    for (int i = 0; i < 20; i ++) {        //1.拼接sql語句                NSString *name = [NSString stringWithFormat:@"資料--%d",arc4random_uniform(100)];                int age = arc4random_uniform(20) + 10;                NSString *sql=[NSString stringWithFormat:@"INSERT INTO t_students (name,age) VALUES ('%@',%d);",name,age];                //2.執行sql語句                char *errmsg = NULL;        sqlite3_exec(_db, sql.UTF8String, NULL, NULL, &errmsg);        if (errmsg) { //如果有錯誤資訊            NSLog(@"插入資料失敗-- %s",errmsg);        }else {            NSLog(@"插入資料成功");        }            }    }#pragma mark - 選擇(select)查詢操作- (void)initSelect {    //從這個表裡面尋找年齡小於20得資料    const char *sql="SELECT id,name,age FROM t_students WHERE age<20;";    sqlite3_stmt *stmt = NULL;        //進行查詢前得準備工作            if (sqlite3_prepare_v2(_db, sql, -1, &stmt, NULL) == SQLITE_OK) { //SQL語句沒有問題        NSLog(@"SQL語句沒有問題");        //每調用一次sqlite_step函數，stmt就會指向下一條記錄        while (sqlite3_step(stmt) == SQLITE_ROW) {            //取出資料            //(1)取出第0欄欄位得值(int類型得值)            int ID = sqlite3_column_int(stmt, 0);            //(2)取出第2欄欄位得值（text類型得值）            const unsigned char *name = sqlite3_column_text(stmt, 1);            //(3)取出第2欄欄位得值            int age = sqlite3_column_int(stmt, 2);            printf("%d %s %d\n",ID,name,age);        }                    }else {        NSLog(@"查詢語句有問題，或者沒有資料");    }    }#pragma mark - 刪除資料- (void)initDelete {    const char *sql = "delete from t_students";    char *errmsg = NULL;    sqlite3_exec(_db, sql, NULL, NULL, &errmsg);    if (errmsg) {        NSLog(@"刪除失敗");    }else {        NSLog(@"刪除成功");    }}- (void)initUpdata {    const char *sql = "update t_students set name = '小明' , age = 16 ;";    char *errmsg = NULL;    sqlite3_exec(_db, sql, NULL, NULL, &errmsg);    if (errmsg) {        NSLog(@"修改失敗");    }else {        NSLog(@"修改成功");    }}@end

註：要匯入 libsqlite3.dylib

sqlite 簡單使用