SQLite学习笔记

1、sqlite3_open系列函数:

int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);
int sqlite3_open16(
  const void *filename,   /* Database filename (UTF-16) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);
int sqlite3_open_v2(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb,         /* OUT: SQLite db handle */
  int flags,              /* Flags */
  const char *zVfs        /* Name of VFS module to use */
);

【功能简述】:
打开一个数据库连接(若指定数据库名不存在,则创建一个新的数据库),并返回数据库连接句柄。
【参数说明】:
(1)sqlite3_open()和sqlite3_open_v2()以UTF-8编码格式解释filename参数,sqlite3_open16()以UTF-16(本地字节序,即大端还是小断视本地主机而定)解释filename参数。
(2)通过ppDb参数返回sqlite3对象指针(也称为数据库连接句柄),若SQLite为sqlite3对象分配内存失败,则ppDb返回为NULL。
(3)其它参数详情参见http://www.sqlite.org/c3ref/open.html。
【返回值】:
若打开(或创建)数据库成功,则返回SQLITE_OK,否则返回错误代号(error code),通过sqlite3_errmsg()或sqlite3_errmsg16()函数可以获取错误信息。
【附加说明】:
(1)使用sqlite3_open()和sqlite3_open_v2()打开或创建的数据库,默认以UTF-8格式编码,而使用sqlite3_open16()打开或创建的数据库以UTF-16格式的本地字节序形式编码。
(2)无论sqlite3_open()系列函数是否成功打开或创建数据库,都应该在不需要的时候调用sqlite3_close()来释放数据库资源。

2、sqlite3_prepare系列函数:

int sqlite3_prepare(
  sqlite3 *db,            /* Database handle */
  const char *zSql,       /* SQL statement, UTF-8 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const char **pzTail     /* OUT: Pointer to unused portion of zSql */
);
int sqlite3_prepare_v2(
  sqlite3 *db,            /* Database handle */
  const char *zSql,       /* SQL statement, UTF-8 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const char **pzTail     /* OUT: Pointer to unused portion of zSql */
);
int sqlite3_prepare16(
  sqlite3 *db,            /* Database handle */
  const void *zSql,       /* SQL statement, UTF-16 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const void **pzTail     /* OUT: Pointer to unused portion of zSql */
);
int sqlite3_prepare16_v2(
  sqlite3 *db,            /* Database handle */
  const void *zSql,       /* SQL statement, UTF-16 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const void **pzTail     /* OUT: Pointer to unused portion of zSql */
);

【功能简述】:
编译一条SQL语句,并返回
【参数说明】:
(1)参数db为sqlite3_open系列函数打开的数据库连接句柄。
(2)参数zSql用来指定需要编译的SQL语句。
(3)参数nByte用来指定一次编译SQL语句最大长度(若SQL语句长度超过nByte,则会被截断,之编译前nByte个字节),若nByte小于0,则编译长度为zSql语句的实际长度(strlen(zSql))。
(4)参数ppStmt返回编译后的prepared statement对象,供后续sqlite3_step函数使用,若编译失败则返回NULL。
(5)参数pzTail若不为NULL,则返回截断后为编译的部分SQL语句的指针。
【返回值】:
编译成功返回SQLITE_OK,否则返回相应错误代号。
【附加说明】:
使用sqlite3_prepare系列函数编译、执行后,若不再使用编译语句,需要调用sqlite3_finalize函数删除编译语句。

3、sqlite3_bind系列函数:

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, sqlite3_int64);
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, void(*)(void*));
int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);
int sqlite3_bind_zeroblob(sqlite3_stmt*, int, int n);

【功能简述】:
绑定实际值到编译语句。
【参数说明】:
(1)第一个参数为prepared statement对象。
(2)第二个参数为SQL语句参数索引,如果是模板?NNN形式的SQL语句,则为对应的NNN值。其它模板形式请参考http://www.sqlite.org/c3ref/bind_blob.html。
(3)第三个参数为绑定值。
(4)第四个参数为绑定字节数,对sqlite3_bind_text()和sqlite3_bind_text16(),指定为负数则根据字符串长度决定。
(5)第五个参数为析构函数,对sqlite3_bind_text()和sqlite3_bind_text16()来说,若绑定字节内存不是在堆上分配的,可以使用指定为SQLITE_STATIC,即0,也就是说不许要free。

4、sqlite3_step函数:

int sqlite3_step(sqlite3_stmt*);

【功能简述】:
执行编译后的SQL语句。
【参数说明】:
编译后的prepared statement对象。
【返回值】:
(1)SQLITE_DONE:若成功执行create、update、delete等一次性可以完成的SQL语句。
(2)SQLITE_ROW:若执行查询语句,可能有多行结果,每次执行返回一行(查询完最后一行后再次调用返回SQLITE_DONE)。
(3)SQLITE_ERROR:运行出错。
(4)SQLITE_MISUSE:可能是由于prepared statement对象已经被删除,或者先前返回了SQLITE_ERROR或SQLITE_DONE。
(5)SQLITE_BUSY:数据库正忙(不能获得数据库锁),若执行的是COMMIT语句或这发生在explicit transaction之外,则可以重试,否则应该先回滚事物,再继续。(暂时不能完全理解这一点)

5、sqlite3_column系列函数:

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);

【功能简述】:
获取记录字段值。
【参数说明】:
(1)第一个参数为prepared statement对象。
(2)第二个参数为字段索引(最左边字段为0)。

6、sqlite3_reset函数

int sqlite3_reset(sqlite3_stmt *pStmt);

【功能简述】:
重置prepared statement对象到初始状态。
【参数说明】:略。
【返回值】:略。

7、sqlite3_finalize函数

int sqlite3_finalize(sqlite3_stmt *pStmt);

 【功能简述】:
删除编译语句。

8、sqlite3_close函数

int sqlite3_close(sqlite3*);
int sqlite3_close_v2(sqlite3*);

【功能简述】:
删除数据库连接句柄,释放所有资源。

9、完整示例

#include <stdio.h>
#include <stdlib.h>
#include "sqlite3.h"

#define SUCCESS 1
#define FAILED -1
#define DATABASE_NAME    "data.db"

struct sqlite3 *g_pdb;

int OpenDatabase();
void CloseDatabase();
int CreateTable();
void CreateNewUser(void* name, int* sex, int* age, void* email);
int InsertData(unsigned int count);
int QueryUserBetweenAge(int min, int max);

int main()
{
    if ( OpenDatabase() == FAILED )
        return 1;
    
    if ( CreateTable() == FAILED )
    {
        CloseDatabase();
        return 1;
    }

    // insert 100 records
    if ( InsertData(100) == FAILED )
    {
        CloseDatabase();
        return 1;
    }
    
    // query users whose age in range 18 to 30
    QueryUserBetweenAge(18, 30);
    
    CloseDatabase();
    return 0;
}

// open database, if not existed then create a new one
int OpenDatabase()
{
    printf("Try to open database.\n");
    int ret = sqlite3_open(DATABASE_NAME, &g_pdb);
    if ( ret != SQLITE_OK)
    {
        fprintf(stderr, "Error: sqlite3_open() failed, error infomation: %s\n", sqlite3_errmsg(g_pdb));
        CloseDatabase();
        return FAILED;
    }
    printf("Open database success.\n");
    return SUCCESS;
}

void CloseDatabase()
{
    printf("Close database.\n");
    sqlite3_close(g_pdb);
}

int CreateTable()
{
    printf("Try to create a table.\n");
    
    const char* pSql = "create table if not exists USER_LIST (name char(16), sex integer, age integer, email char(32))";
    
    // compile create table statement
    sqlite3_stmt *pStmt;
    int ret = sqlite3_prepare(g_pdb, pSql, -1, &pStmt, 0);
    if ( ret != SQLITE_OK)
    {
        fprintf(stderr, "Error: compile create table statement failed: %s\n", sqlite3_errmsg(g_pdb));
        return FAILED;
    }
    printf("Compile create table statement success.\n");
    
    // execute create table statement
    ret = sqlite3_step(pStmt);
    if (ret != SQLITE_DONE)
    {
        fprintf(stderr, "Error: execute create table statement faild\n");
        sqlite3_finalize(pStmt);
        return FAILED;
    }
    
    // delete create table statement
    sqlite3_finalize(pStmt);
    printf("Create table success\n");
    return SUCCESS;
}

int InsertData(unsigned int count)
{
    printf("Try to inesrt %d data.\n", (int)count);
    
    const char* pSql = "insert into USER_LIST (name, sex, age, email) values (?1, ?2, ?3, ?4)";

    // compile insert statement
    sqlite3_stmt *pStmt;
    int ret = sqlite3_prepare(g_pdb, pSql, -1, &pStmt, 0);
    if ( ret != SQLITE_OK)
    {
        fprintf(stderr, "Error: compile insert statement failed: %s\n", sqlite3_errmsg(g_pdb));
        return FAILED;
    }
    printf("Compile insert statement success.\n");
    
    int i, sex, age;
    char name[4] = {0};
    char email[12] = {0};
    for (i = 0; i < count; i++)
    {
        CreateNewUser((void*)name, &sex, &age, (void*)email);
        //printf("[Name]: %s, [Sex]: %s, [Age]: %d, [Email]: %s\n", name, sex % 2 ? "male" : "female", age, email);
        sqlite3_bind_text(pStmt, 1, name, -1, SQLITE_STATIC);
        sqlite3_bind_int(pStmt, 2, sex);
        sqlite3_bind_int(pStmt, 3, age);
        sqlite3_bind_text(pStmt, 4, email, -1, SQLITE_STATIC);
        
        ret = sqlite3_step(pStmt);
        if (ret != SQLITE_DONE)
        {
            fprintf(stderr, "Error: execute insert statement %d faild\n", i);
            continue;
        }
        sqlite3_reset(pStmt);
    }
    
    // delete insert statement
    sqlite3_finalize(pStmt);
    printf("Insert data over\n");
    return SUCCESS;
}

void CreateNewUser(void*name, int* sex, int* age, void *email)
{
    ((char*)name)[0] = rand() % 25 + 'A';
    ((char*)name)[1] = rand() % 25 + 'A';
    ((char*)name)[2] = rand() % 25 + 'A';
    *sex = rand() % 2;       // 1: male, 0: female
    *age = rand() % 100 + 1; // age range 1-100
    ((char*)email)[0] = ((char*)name)[0] | 0x20; // convert case to lowercase
    ((char*)email)[1] = ((char*)name)[1] | 0x20; // convert case to lowercase
    ((char*)email)[2] = ((char*)name)[2] | 0x20; // convert case to lowercase
    ((char*)email)[3] = '@';
    ((char*)email)[4] = rand() % 10 + '0';
    ((char*)email)[5] = rand() % 10 + '0';
    ((char*)email)[6] = rand() % 10 + '0';
    ((char*)email)[7] = '.';
    ((char*)email)[8] = 'c';
    ((char*)email)[9] = 'o';
    ((char*)email)[10] = 'm';
    ((char*)email)[11] = '\0';
}

int QueryUserBetweenAge(int min, int max)
{
    printf("Try to query users whose age is between %d and %d.\n", min, max);
    
    char pSql[128] = {0};
    sprintf(pSql, "select * from USER_LIST where age > %d and age < %d order by age, name asc", min - 1, max + 1);
// compile select statement sqlite3_stmt *pStmt; int ret = sqlite3_prepare(g_pdb, pSql, -1, &pStmt, 0); if ( ret != SQLITE_OK) { fprintf(stderr, "Error: compile select statement failed: %s\n", sqlite3_errmsg(g_pdb)); return FAILED; } printf("Compile select statement success.\n"); // execute select statement int count = 0; printf(" ID\t Name\t Sex\t Age\t Email\n"); printf("------------------------------------------------------\n"); while (sqlite3_step(pStmt) == SQLITE_ROW) // return SQLITE_DONE after last row { // display query result printf("%3d\t ", ++count); printf("%s\t ", sqlite3_column_text(pStmt, 0)); printf("%s\t ", sqlite3_column_int(pStmt, 1) > 0 ? "male" : "female"); printf("%d\t ", sqlite3_column_int(pStmt, 2)); printf("%s\n", sqlite3_column_text(pStmt, 3)); } printf("------------------------------------------------------\n"); printf("Total: %d\n", count); // delete select statement sqlite3_finalize(pStmt); printf("Query data success\n"); return SUCCESS; }
原文地址:https://www.cnblogs.com/opangle/p/3043124.html