SQLite基本了解和使用

SQL语句三种分类：DML、DDL、DCL（数据库控制语言）
 
 DDL 数据库操作语言:
 --创建表：
 CREATE TABLE IF NOT EXISTS TestTable (field1 TEXT PRIMARY KEY, field2 TEXT NOT NULL, field3 TEXT);
 --更改表结构，添加一个新人字段（不能直接删除字段）
 ALTER TABLE TestTable ADD field4 TEXT;
 --删除表
 DROP TABLE TestTable;
 
 DML 数据库定义语言 :
 --更
 UPDATE TestTable SET field1 = "update" WHERE field1 = "condition";
 
 --删
 DELETE FROM TestTable WHERE field1 = "condition1" AND field2 = "condition2";
 DELETE FROM TestTable WHERE field1 = "condition1" OR field2 = "condition2";
 
 --改
 INSERT OR REPLACE INTO TestTable (field1, field2, field3) VALUES("p1", "p2", "p3");
 
 --查
 SELECT * FROM TestTable;   --所有
 SELECT field1, field2 FROM TestTable;
 SELECT COUNT(*) FROM TestTable;    --数量
 SELECT COUNT(DISTINCT(*)) FROM TestTable;  --去除重复的数量

//-----------------------------在IOS编程中使用sql-------------------------------------
 在IOS编程中使用sql:
 //使用SQLite数据库导入libsqlite3.0.dylib
 //并#import <sqlite3.h>
 
 //常用方法：
 //打开数据库
 //使用C的函数接口，所以NSString要使用[string UTF8String]
 sqlite3_open(const char *filename, sqlite3 **ppDb);
 //关闭数据库
 sqlite3_close(sqlite3 *);
 //执行sql语句
 sqlite3_exec(sqlite3 *, const char *sql, int (*callback)(void *, int, char **, char **), void *, char **errmsg);
 //编译sql语句
 sqlite3_prepare_v2(sqlite3 *db, const char *zSql, int nByte, sqlite3_stmt **ppStmt, const char **pzTail);
 //执行查询sql语句
 sqlite3_step(sqlite3_stmt *);
 //结束sql语句
 sqlite3_finalize(sqlite3_stmt *pStmt);
 //绑定参数(text是绑定参数类型)
 sqlite3_bind_text(sqlite3_stmt *, int, const char *, int n, void (*)(void *));
 //查询字段上的数据
 sqlite3_column_text(sqlite3_stmt *, int iCol);

使用SQL

- (void)_initSQLite {
    sqlite3 *sqlite = nil;
    
    NSString *filePath = [NSHomeDirectory() stringByAppendingPathComponent:@"Documents/data.sqlite"];
    
    //如果文件不存在，sqlite3_open会创建这个sqlite文件(测试)
    if (sqlite3_open([filePath UTF8String], &sqlite) != SQLITE_OK) {
        NSLog(@"打开数据库失败");
        
    }
    
    //创建表的SQL语句
    NSString *sqlStr = @"CREATE TABLE IF NOT EXISTS TestTable (field1 TEXT NOT NULL PRIMARY KEY, field2 TEXT NOT NULL)";
    char *error;
    //执行SQL语句,判断是否执行成功
    if (sqlite3_exec(sqlite, [sqlStr UTF8String], NULL, NULL, &error) != SQLITE_OK) {
        NSLog(@"创建表失败, %s", error);
        return;
    }
    
    //----------------------------数据-----------------------
    sqlite3_stmt *stmt = nil;
    
    //创建插入数据的SQL语句，？表示点位符可逐个填充
    NSString *insertStr = @"INSERT OR REPLACE INTO TestTable (field1, field2) VALUES (?, ?)";
    
    //编译SQL语句
    sqlite3_prepare_v2(sqlite, [insertStr UTF8String], -1, &stmt, NULL);
    NSString *field1Str = @"first";
    NSString *field2Str = @"hehe";
    
    //往SQL中填充数据,起始位是1（没有0）
    sqlite3_bind_text(stmt, 1, [field1Str UTF8String], -1, NULL);
    sqlite3_bind_text(stmt, 2, [field2Str UTF8String], -1, NULL);
    
    //执行SQL语句
    int result = sqlite3_step(stmt);
    if (result == SQLITE_ERROR || result == SQLITE_MISUSE) {
        NSLog(@"执行SQL语句失败1");
    }
    
    //不用占位符
    NSString *insertStr2 = @"INSERT OR REPLACE INTO TestTable (field1, field2) VALUES ('second', 'haha')";
    sqlite3_prepare_v2(sqlite, [insertStr2 UTF8String], -1, &stmt, NULL);
    result = sqlite3_step(stmt);
    if (result == SQLITE_ERROR || result == SQLITE_MISUSE) {
        NSLog(@"执行SQL语句失败2");
    }
    
    //不用编译直接执行
    NSString *insertStr3 = @"INSERT OR REPLACE INTO TestTable (field1, field2) VALUES ('third', 'en')";
    result = sqlite3_exec(sqlite, [insertStr3 UTF8String], NULL, NULL, &error);
    if (error != Nil) {
        NSLog(@"执行SQL语句失败3, %s", error);
    }
    
    //-------------------------------查询------------------------------------------
    NSString *searchStr = @"SELECT * FROM TestTable";
    sqlite3_stmt *selectStmt = nil;
    sqlite3_prepare_v2(sqlite, [searchStr UTF8String], -1, &selectStmt, NULL);
    result = sqlite3_step(selectStmt);
    
//#define SQLITE_ROW         100  /* sqlite3_step() has another row ready */
//#define SQLITE_DONE        101  /* sqlite3_step() has finished executing */

 while (result == SQLITE_ROW) {
        //取出索引为0、1、2字段的数据
        char *field1 = (char *)sqlite3_column_text(selectStmt, 0);
        char *field2 = (char *)sqlite3_column_text(selectStmt, 1);
        
        NSLog(@"field1:%s, field2:%s", field1, field2);
        
        //游标移到下一条结果数据
        result = sqlite3_step(selectStmt);
    }
    
    //关闭数据库句柄
    sqlite3_finalize(stmt);
    sqlite3_finalize(selectStmt);
    
    //关闭数据库
    sqlite3_close(sqlite);
}