Sqlite3学习实践总结

最新推荐文章于 2020-07-18 16:11:51 发布

人生如叶

最新推荐文章于 2020-07-18 16:11:51 发布

阅读量346

点赞数

分类专栏： SQLite3

SQLite3 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

【实践环境】

操作系统：mac 10.6

工具：终端，Xcode

【资料和实践】
官方网址：http://www.sqlite.org/index.html
当前版本3.7.7.1

在mac 10.6上，版本是3.6.12

在终端上，程序名称是sqlite3

sqlite3 -help来看此程序用法

常用：
sqlite3 FileName，来进入某一个特定数据库文件
sqlite3 -version，查看版本

好，实践录入：
mac-Pro:~ user$sqlite3 testdb

sqlite>

在这里可以使用标准的ddl和dml语句，比如：
create table t1 ......;
insert into t1 (...) values (...);
Select * from t1;

这里命令都以;作为结束符号

除了标准的sql语句外，还有一些用法，用于管理的命令，这类命令以.开头，可以输入.help来阅读

这里列举简单的命令:
.databases
.tables
.exit
.quit
至于其他命令用.help来阅读

说到这里，在终端上使用sqlite3基本上实践了下

这里回到开发上来

以C\C++接口来说，官方资料http://www.sqlite.org/capi3.html

1、
sqlite3的类型，只有以下几类：
#define SQLITE_INTEGER 1
#define SQLITE_FLOAT    2
#define SQLITE_TEXT     3
#define SQLITE_BLOB     4
#define SQLITE_NULL     5

涉及函数：
int sqlite3_column_type(sqlite3_stmt*, int iCol);返回列类型

2、
打开sqlite数据库，关闭sqlite数据库
int sqlite3_open(const char*, sqlite3**);
int sqlite3_open16(const void*, sqlite3**);
int sqlite3_close(sqlite3*);

默认编码utf-8,第二个函数使用utf-16
返回整型错误码
#define SQLITE_OK           0   /* Successful result */
#define SQLITE_ERROR        1   /* SQL error or missing database */
#define SQLITE_INTERNAL     2   /* An internal logic error in SQLite */
#define SQLITE_PERM         3   /* Access permission denied */
#define SQLITE_ABORT        4   /* Callback routine requested an abort */
#define SQLITE_BUSY         5   /* The database file is locked */
#define SQLITE_LOCKED       6   /* A table in the database is locked */
#define SQLITE_NOMEM        7   /* A malloc() failed */
#define SQLITE_READONLY     8   /* Attempt to write a readonly database */
#define SQLITE_INTERRUPT    9   /* Operation terminated by sqlite_interrupt() */
#define SQLITE_IOERR       10   /* Some kind of disk I/O error occurred */
#define SQLITE_CORRUPT     11   /* The database disk image is malformed */
#define SQLITE_NOTFOUND    12   /* (Internal Only) Table or record not found */
#define SQLITE_FULL        13   /* Insertion failed because database is full */
#define SQLITE_CANTOPEN    14   /* Unable to open the database file */
#define SQLITE_PROTOCOL    15   /* Database lock protocol error */
#define SQLITE_EMPTY       16   /* (Internal Only) Database table is empty */
#define SQLITE_SCHEMA      17   /* The database schema changed */
#define SQLITE_TOOBIG      18   /* Too much data for one row of a table */
#define SQLITE_CONSTRAINT 19   /* Abort due to contraint violation */
#define SQLITE_MISMATCH    20   /* Data type mismatch */
#define SQLITE_MISUSE      21   /* Library used incorrectly */
#define SQLITE_NOLFS       22   /* Uses OS features not supported on host */
#define SQLITE_AUTH        23   /* Authorization denied */
#define SQLITE_ROW         100 /* sqlite_step() has another row ready */
#define SQLITE_DONE        101 /* sqlite_step() has finished executing */

实践代码：
sqlite3* database;
NSString* path;//need you set

if(sqlite3_open([path UTF8String], &database) == SQLITE_OK)
{
    NSLog(@"DB created and opened");
}

sqlite3_close(database);

3、
数据操纵语句-insert ,update ,delete

In SQLite version 3, the sqlite3_exec routine is just a wrapper around calls to the prepared statement interface.
在SQLite3下，程序sqlite3_exec【这个工作在SQLite2下】被分装调用在预处理语句接口

typedef struct sqlite3_stmt sqlite3_stmt;
int sqlite3_prepare(sqlite3*, const char*, int, sqlite3_stmt**, const char**);
int sqlite3_prepare16(sqlite3*, const void*, int, sqlite3_stmt**, const void**);
int sqlite3_finalize(sqlite3_stmt*);
int sqlite3_reset(sqlite3_stmt*);

预处理语句后，应该使用这个函数来执行：
int sqlite3_step(sqlite3_stmt*);
    The sqlite3_step() routine return SQLITE_ROW if it is returning a single row of the result set, or SQLITE_DONE if execution has completed, either normally or due to an error.

如果有完整sql语句，可以用以下来直接调用
-(BOOL) ExecSql:(char *)sql
{
    BOOL isOK = NO;
    sqlite3_stmt *statement;
    if (sqlite3_prepare_v2(self->_database, sql, -1, &statement, nil) != SQLITE_OK)
    {
        isOK = NO;
    }
    else
    {
        int success = sqlite3_step(statement);
        sqlite3_finalize(statement);
        if (success != SQLITE_DONE)
        {
            isOK = NO;
        }
        else
        {
            isOK = YES;
        }
    }

    return isOK;
}

还有一种，类似参数化语句的执行方式，需要绑定参数，绑定函数有，
int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
int sqlite3_bind_double(sqlite3_stmt*, int, double);
int sqlite3_bind_int(sqlite3_stmt*, int, int);
int sqlite3_bind_int64(sqlite3_stmt*, int, long long int);
int sqlite3_bind_null(sqlite3_stmt*, int);
int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*));
int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);

The second argument is the index of the SQL parameter to be set. The leftmost SQL parameter has an index of 1.
第二个参数是sql参数的索引值，以1开始

看下此代码：
-(void) SaveMBKey:(NSString *)key
{
    BOOL isOK = NO;

    sqlite3_stmt *statement;

    static char* sql ="Insert Into MBKey (key) values (?);";

    int success = sqlite3_prepare_v2(self->_database, sql, -1, &statement, NULL);
    if (success != SQLITE_OK)
    {
        isOK = NO;
    }
    else
    {
        sqlite3_bind_text(statement, 1, [key UTF8String], -1, SQLITE_TRANSIENT);

        success = sqlite3_step(statement);
        sqlite3_finalize(statement);
    }

    if (success == SQLITE_ERROR)
    {
        isOK = NO;
    }
    else
    {
        isOK = YES;
    }

    return;
}

4、
查询和填充，此处理和上述类似，唯一的不同需要列值绑定

当sqlite3_step返回SQLITE_ROW，需要以下函数来接收数据

const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);
int sqlite3_column_bytes(sqlite3_stmt*, int iCol);
int sqlite3_column_bytes16(sqlite3_stmt*, int iCol);
double sqlite3_column_double(sqlite3_stmt*, int iCol);
int sqlite3_column_int(sqlite3_stmt*, int iCol);
sqlite3_int64 sqlite3_column_int64(sqlite3_stmt*, int iCol);
const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
const void *sqlite3_column_text16(sqlite3_stmt*, int iCol);
int sqlite3_column_type(sqlite3_stmt*, int iCol);
sqlite3_value *sqlite3_column_value(sqlite3_stmt*, int iCol);

These routines form the "result set" interface.

the second argument is the index of the column for which information should be returned. The leftmost column of the result set has the index 0.
注意：iCol参数起始值是0

好，看下下面的实践代码：
-(void) GetList:(NSMutableArray*)KeysList
{
    BOOL isOK = NO;

    sqlite3_stmt *statement;

    static char* sql ="Select KeyID ,Key From MBKey Order by KeyID;";

    int success = sqlite3_prepare_v2(self->_database, sql, -1, &statement, NULL);
    if (success != SQLITE_OK)
    {
        isOK = NO;
    }
    else
    {
        //查询结果集中一条一条的遍历所有的记录，这里的数字对应的是列值。
        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            int kid = sqlite3_column_int(statement, 0);
            char* key = (char*)sqlite3_column_text(statement, 1);

            KeyInfo* keyInfo = [[KeyInfo alloc] init];
            if(key)
                [keyInfo.Key setString:[NSString stringWithUTF8String:key]];
            keyInfo.KeyID = kid;

            [KeysList addObject:keyInfo];
            [keyInfo release];
        }
        sqlite3_finalize(statement);
    }

    if (success == SQLITE_ERROR)
    {
        isOK = NO;
    }
    else
    {
        isOK = YES;
    }

    return;
}

说明下：在当前实践环境下，可以支持inner join，支持left join ,不支持right out join 和full join
select * from a inner join b on a.id = b.id;
select * from a join b on a.id = b.id;
select * from a left join b on a.id = b.id;
select * from a right outer join b on a.id = b.id;//报错，不支持

好，使用编程的方式能对sqlite数据库进行基础操作，基本上能开发小型的db程序了

参考sqlite不支持sql语法文档：http://wenku.baidu.com/view/823c3e92daef5ef7ba0d3cd5.html

/****************/
关于sqlite3文件格式描述地址：
http://www.sqlite.org/fileformat2.html

/****************/

5、sql字符处理
A、subStr(X,Y,Z),X字段名，Y以1作为起始字符索引值，缩写函数是 subStr(X,Y)
B、like语句，支持_,%通配符
C、字符拼接使用“||”，eg:select 'a'||'b' from c;输出ab
[ABC,2012-07-02补充]

6、日期数据处理
存储日期数据建议采用'%Y-%m-%d'或'%Y-%m-%d %H:%M:%S'，其中月和日均为2位，不足以0补高位

参看官网支持格式：
YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS

常用sqlite日期格式范例：

select date();
2012-07-04

select datetime();
2012-07-04 02:47:23

select strftime('%Y', 'now' )
2012

select strftime('%m', 'now' )
07

select strftime('%d', 'now' )
04

select strftime('%s', 'now' )
1341371148//返回当前时间离1970-1-1的秒数

SELECT date('now','start of month','+1 month','-1 day');
2012-07-31//当前月份最后一天

SELECT julianday(date()) - julianday('2012-07-01');
3//当前2012-07-04离2012-7-1的天数

good luck

转自：http://www.cnblogs.com/GoGoagg/archive/2011/08/12/2136280.html