iOS Sqlite的详解

1.创建数据库

 //创建数据库路径
    NSString *path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).firstObject stringByAppendingPathComponent:@"Person.sqlite"];
    NSLog(@"Path: %@", path);
    //打开上面的创建的数据库，如果不存在，则会创建
    //路径转为统一格式
    const char *filename = [path cStringUsingEncoding:NSUTF8StringEncoding];
    //#import <sqlite3.h>  以及 添加 libsqlite3.0.tbd
    sqlite3 *ppDb = NULL;
    int response = sqlite3_open(filename, &ppDb);
    if (response == SQLITE_OK) {
        NSLog(@"打开Person.sqlite数据库成功");
    }else NSLog(@"打开Person.sqlite数据库失败");

2.创建表格

  //创建表table,这个要熟悉数据库的建表语句，这个了解一下即可
    const char *creatTable = "create table if not exists Student(name varchar, age integer, sex varchar, class integer, school varchar, address varchar, studentID varchar primary key)";
    //表创建失败 table Student already exists
    char *error = NULL;
    response = sqlite3_exec(ppDb, creatTable, NULL, NULL, &error);
    if (response == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else{
        NSLog(@"表创建失败 %s", error);
    }

3.插入以及更新记录

 //插入一条记录
    const char *insertSql = "insert into Student values ('YaoMing', 30, 'Male', 'SofeWare 1', 'SofeWare', '广州上下九步行街', '20095531011')";
    response = sqlite3_exec(ppDb, insertSql, NULL, NULL, &error);
    if (response == SQLITE_OK) {
        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败 %s", error);
    }
    //update 记录
    const char *updateSchool = "update Student set school = '广州大学'";
    response = sqlite3_exec(ppDb, updateSchool, NULL, NULL, &error);
    if (response == SQLITE_OK) {
        NSLog(@"update successful");
    }else NSLog(@"update failed: %s", error);

4.删除行

    //删除 记录
    const char *delete = "delete from Student";
    response = sqlite3_exec(ppDb, delete, NULL, NULL, &error);
    if (response == SQLITE_OK) {
        NSLog(@"delete 成功");
    }else NSLog(@"删除失败 %s", error);

5.查询存储的数据

  //查询所有的数据
    const char *querySql = "select * from Student";
    //用于存储查询出来的数据
    sqlite3_stmt *ppStmt = NULL;
    response = sqlite3_prepare_v2(ppDb, querySql, -1, &ppStmt, NULL);
    //遍历列表中的每一条数据,拿到其中的内容
    while (sqlite3_step(ppStmt) == SQLITE_ROW) {
        //拿到数据中第0列的内容
        const unsigned char *name = sqlite3_column_text(ppStmt, 0);
        //第1列的内容
        const unsigned char *age = sqlite3_column_text(ppStmt, 1);

        NSLog(@"%s", name);
        NSLog(@"%s", age);
//        NSString *nameStr = [NSString stringWithUTF8String:name];
//        NSLog(@"%@", nameStr);

6.drop 表

    //drop table
    const char *dropTable = "drop table Student";
    response = sqlite3_exec(ppDb, dropTable, NULL, NULL, &error);
    if (response == SQLITE_OK) {
        NSLog(@"drop 成功");
    }else NSLog(@"drop失败 %s", error);

7.最后一点：

操作完毕后，关闭数据库，有开必有关

sqlite3_close(ppDb);