No.07 Xcode(5.x) 使用SQLite

在终端中使用SQLite3

在终端中涉及的命令有两种, 一个针对SQLite3本身, 另一个是针对数据库操作. 数据库命令必须以分号';'结尾.

也可以选择FMDB封装, 参考资料: http://www.it165.net/pro/html/201503/35539.html

SQLite3命令:

1)打开数据库, 注意:filePath中的空格需要转义, -column 搜索结果按列排列整齐, -header 搜索结果显示表头
SQLITE3 -column -header /Users/xxx.db

2)查看数据库中的所有表
.tables

3)查询数据库中的所有表的结构
.schema

4)退出SQLITE3
.quit

数据库命令:

1)创建表
CREATE TABLE user_info (userId INTEGER PRIMARY KEY AUTOINCREMENT, userName TEXT, userAge INTEGER);
CREATE TABLE IF NOT EXISTS user_info (userId INTEGER PRIMARY KEY AUTOINCREMENT, userName TEXT, userAge INTEGER);
CREATE TABLE user_info (userId INTEGER, userName TEXT, userAge INTEGER, PRIMARY KEY(userId, userName));

2)销毁表
DROP TABLE user_info;

3)修改表--增加一列
ALTER TABLE tab_name ADD COLUMN col_name TEXT;

4)修改表--修改表名, 注意:新的表名会被带上双引号
ALTER TABLE tab_name RENAME TO new_tab_name;

5)删除项
DELETE FROM user_info WHERE userName='saber';

6)添加项
INSERT INTO user_info(userName, userAge) VALUES('saber', 18);
INSERT INTO user_info(userName, userAge) VALUES('saber', 18), ('baserker', 22);
INSERT INTO mo_personmap (personid, belongid) SELECT b.id, b.departid FROM mo_person b;

6)替换或新增
REPLACE INTO user_info(userName, userAge) VALUES('saber', 18);

7)修改项
UPDATE user_info SET userAge=16 WHERE userName='saber';

8)查询项
SELECT * FROM user_info;

在代码中使用SQLite3

1)需要在工程配置中加入libsqlite3.dylib, 然后在代码中引入头文件sqlite3.h

2)程序因意外而中断时, 已经执行的数据库操作依然会保留下来

3)需要用到的接口函数大致如下

sqlite3_open	打开或者创建数据库文件
sqlite3_close	关闭由sqlite3_open建立的连接
sqlite3_exec	sqlite3_prepare_v2，sqlite3_step()和sqlite3_finalize()的封装，能让程序多次执行sql语句而不要写许多重复的代码
sqlite3_prepare_v2	可以理解为数据库操作的开始
sqlite3_step	可以理解为数据库操作的过程
sqlite3_finalize	可以理解为数据库操作的结束
sqlite3_column_int sqlite3_column_text sqlite3_column_blob	sqlite3_step时, 通过这些函数从数据库操作中取到某行列那个单元格的值

4)示例代码

#import "sqlite3.h"

typedef void(^sqlite3_result)(sqlite3_stmt *ppStmt);

int sqlite3_search(sqlite3 *db, const char *sql, sqlite3_result result)
{
    sqlite3_stmt *statement = 0;
    
    int ret = sqlite3_prepare_v2(db, sql, -1, &statement, NULL);
    if (ret != SQLITE_OK)
    {
        NSLog(@"sqlite3_search failed. ret=%d, sql=%s", ret, sql);
    }
    else while (sqlite3_step(statement) == SQLITE_ROW)
    {
        result(statement);
    }
    sqlite3_finalize(statement);
    
    return ret;
}

- (void)buttonClicked:(UIButton*)sender
{    
    NSString *docuPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
    NSString *filePath = [docuPath stringByAppendingPathComponent:@"userinfo.dat"];
    NSLog(@"%@", filePath);
    
    // 启动程序时, 如果文件存在, 删除它
    NSFileManager *fileManager = [NSFileManager defaultManager];
    if ([fileManager fileExistsAtPath:filePath] == YES)
        [fileManager removeItemAtPath:filePath error:nil];
    
    sqlite3 *database = 0;
    NSString *sql = nil;
    char *err = 0;
    int ret = 0;
    
    // 打开或创建文件
    ret = sqlite3_open(filePath.UTF8String, &database);
    if (ret != SQLITE_OK)
    {
        NSLog(@"sqlite3_open failed. ret=%d", ret);
        return;
    }
    
    // 创建表
    sql = @"CREATE TABLE IF NOT EXISTS userinfo (userid INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT, password TEXT)";
    ret = sqlite3_exec(database, sql.UTF8String, NULL, NULL, &err);
    if (ret != SQLITE_OK)
    {
        NSLog(@"sqlite3_exec failed. ret=%d, err=%s, sql=%@", ret, err, sql);
    }
    
    // 插入数据
    for (int i = 0; i < 4; i++)
    {
        sql = [NSString stringWithFormat:@"INSERT INTO userinfo (username, password) VALUES('name%02d', 'pswd%03d')", i, i];
        ret = sqlite3_exec(database, sql.UTF8String, NULL, NULL, &err);
        if (ret != SQLITE_OK)
        {
            NSLog(@"sqlite3_exec failed. ret=%d, err=%s, sql=%@", ret, err, sql);
        }
    }
    
    // 删除数据
    sql = @"DELETE FROM userinfo WHERE username='name02'";
    ret = sqlite3_exec(database, sql.UTF8String, NULL, NULL, &err);
    if (ret != SQLITE_OK)
    {
        NSLog(@"sqlite3_exec failed. ret=%d, err=%s, sql=%@", ret, err, sql);
    }
    
    // 修改数据
    sql = @"UPDATE userinfo SET password='123456' WHERE username='name01'";
    ret = sqlite3_exec(database, sql.UTF8String, NULL, NULL, &err);
    if (ret != SQLITE_OK)
    {
        NSLog(@"sqlite3_exec failed. ret=%d, err=%s, sql=%@", ret, err, sql);
    }
    
    // 查询数据
    sql = @"SELECT userid, username, password FROM userinfo";
    ret = sqlite3_search(database, sql.UTF8String, ^(sqlite3_stmt *ppStmt) {
        NSInteger userid   = sqlite3_column_int(ppStmt, 0);
        NSString *username = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(ppStmt, 1)];
        NSString *password = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(ppStmt, 2)];
        NSLog(@"userid=%d, username='%@', password='%@'", userid, username, password);
    });
    
    // 关闭文件
    sqlite3_close(database);
}