sqlite3 命令行简单使用

sqlite3 路径 //打开数据库路径连接

select * from sqlite_master where type=”table”; //显示全部表结构

select * from testable; //显示某张表数据

.tables //查看表

.help //查看帮助

.quit //退出

xcode使用sqlite3步骤

1.加入libsqlite3.dylib

watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQveHVuZGg=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" alt="这里写图片描写叙述" title="">

2.头文件

//

//  DbUtils.h

//  smart

//

//  Created by 谢厂节 on 15/5/12.

//  Copyright (c) 2015年 WHR. All rights reserved.

//

#import <Foundation/Foundation.h>

#import "sqlite3.h"

@interface DbUtils : NSObject

{

    sqlite3 *db; //声明一个sqlite3数据库

}

- (NSString *)filePath;//数据库文件的路径。一般在沙箱的Documents里边操作

-(void)openDB;

-(void)closeDB;

-(NSMutableArray *)getAllTypes;

@end

.m文件

//

//  DbUtils.m

//  smart

//

//  Created by 谢厂节 on 15/5/12.

//  Copyright (c) 2015年 WHR. All rights reserved.

//

#import "DbUtils.h"

#import "KMTypes.h"

#import "KMContents.h"

@implementation DbUtils

//打开数据库的方法

- (void)openDB{

       ///文件是否存在

    NSFileManager* fileManager = [NSFileManager defaultManager];

    NSString *dbpath=[self filePath];

    NSLog(@"database path:%@",dbpath);

    BOOL success = [fileManager fileExistsAtPath:dbpath];

    if (!success) {

        NSString *resourcePath=[[NSBundle mainBundle]resourcePath];

        //自己主动复制

        NSString *sourceDBPath=[resourcePath stringByAppendingPathComponent:@"app.bundle/datas.sqlite"];

        NSError *error;

        success = [fileManager copyItemAtPath:sourceDBPath toPath:dbpath error:&error];

        if(!success)

            NSAssert1(0,@"数据库附加失败。'%@'.", [error localizedDescription]);

        else

            NSLog(@"数据库附加成功:%@",dbpath);

    }

    if (sqlite3_open([[self filePath] UTF8String], &db) != SQLITE_OK) {

        sqlite3_close(db);

        NSAssert(0, @"数据库打开失败。");

    }

}

- (void)closeDB{

    sqlite3_close(db);

}

//该方法用于返回数据库在Documents目录中的全路径信息

- (NSString *)filePath{

    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

    NSString *documentsDir = [paths objectAtIndex:0];

    return [documentsDir stringByAppendingPathComponent:@"datas.sqlite"];

}

////查询数据全部类别

- (NSMutableArray *)getAllTypes{

    [self openDB];

    NSMutableArray *array=[NSMutableArray arrayWithCapacity:6 ];

    NSString *sql = @"SELECT * FROM km_types";

    sqlite3_stmt *statement;

    if (sqlite3_prepare_v2(db, [sql UTF8String], -1, &statement, nil) == SQLITE_OK) {

        while (sqlite3_step(statement) == SQLITE_ROW) {

            KMTypes* k= [[KMTypes alloc]init];

            int type_id = (int)sqlite3_column_int(statement,0);

            int parent_id = (int)sqlite3_column_int(statement,1);

            char *type_title = (char *)sqlite3_column_text(statement, 2);

            int type_order = (int)sqlite3_column_int(statement,3);

            int topic_count=(int)sqlite3_column_int(statement,4);

            NSString *type_titleStr = [[NSString alloc] initWithUTF8String:type_title];

            k.type_title = type_titleStr;

            k.type_id=type_id;

            k.parent_id = parent_id;

            k.type_order = type_order;

            k.topic_count=topic_count;

            [array addObject:k];

        }

        sqlite3_finalize(statement);

    }

    [self closeDB];

    return array;

}

@end

这里仅仅实现一个简单的查询功能。