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