首页

# SQLite 类型

1、存储类

SQLite 存储类属性划分为以下几种类型：

类型描述
NULL表示该值为 NULL 值。
INTEGER无符号整型值，根据值的大小存储在 1、2、3、4、6 或 8 字节中。
REAL浮点值，存储为 8 字节的 IEEE 浮点数字。
TEXT文本字符串，使用 UTF-8、UTF-16BE 或 UTF-16LE 编码方式存储。
BLOB存储 Blob 数据，该类型数据和输入数据完全相同。

2、数据类型

  • 布尔数据类型：
    SQLite 并没有提供专门的布尔存储类型，取而代之的是存储整型 1 表示 true，0 表示 false。

  • 日期和时间数据类型：
    和布尔类型一样，SQLite 也同样没有提供专门的日期时间存储类型，而是以 TEXT、REAL 和 INTEGER 类型分别不同的格式表示该类型，如：
    TEXT : "YYYY-MM-DD HH:MM:SS.SSS"。
    REAL ：以 Julian 日期格式存储。
    INTEGER ：以 Unix 时间形式保存数据值，即从 1970-01-01 00:00:00 到当前时间所流经的秒数。

3、类型亲缘性（Type Affinity）

为了最大限度地提高 SQLite 和其他数据库引擎之间的兼容性，SQLite 支持 “类型亲缘性” 的概念。使其它 SQL 数据库引擎能在 SQLite 上运行；在表字段被声明之后，SQLite 都会根据该字段声明时的类型为其选择一种亲缘类型，当数据插入时，该字段的数据将会优先采用亲缘类型作为该值的存储方式，除非亲缘类型不匹配或无法转换当前数据到该亲缘类型，这样 SQLite 才会考虑其它更适合该值的类型存储该值。

SQLite 3 数据库中支持分配的类型关联：

  • TEXT
  • NUMERIC
  • INTEGER
  • REAL
  • BLOB

决定规则：

  1. 如果声明的类型包含字符串 “INT”，则它分配为 INTEGER 。
  2. 如果声明的类型包含任意字符串 “CHAR”、“CLOB” 或 “TEXT” 的关键字，则被分配为 TEXT 。请注意，VARCHAR 类型包含字符串 “CHAR”，因此被分配为 TEXT 。
  3. 如果声明的类型包含字符串 “BLOB”，或者如果未指定类型，则被分配为 BLOB 。
  4. 如果声明的类型包含任意字符串 “REAL”、“FLOA” 或 “DOUB” 的关键字，则被分配为 REAL 。
  5. 其余情况下，归类为 NUMERIC 。

note：请注意，确定类型亲缘性的规则顺序很重要。声明类型为 “CHARINT” 的字段将同时匹配规则 1 和 2，但第一条规则采用优先权，所以会被分配为 INTEGER 。

示例：

声明的类型类型亲缘性规则优先级
INT <br/>INTEGER <br/>TINYINT <br/>SMALLINT <br/>MEDIUMINT <br/>BIGINT <br/>UNSIGNED <br/>BIG <br/>INT <br/>INT2 <br/>INT8INTEGER1
CHARACTER(20) <br/>VARCHAR(255) <br/>VARYING <br/>CHARACTER(255) <br/>NCHAR(55) <br/>NATIVE <br/>CHARACTER(70) <br/>NVARCHAR(100) <br/>TEXT <br/>CLOBTEXT2
BLOB <br/>no datatype specifiedBLOB3
REAL <br/>DOUBLE <br/>DOUBLE <br/>PRECISION <br/>FLOATREAL4
NUMERIC <br/>DECIMAL(10,5) <br/>BOOLEAN <br/>DATE <br/>DATETIMENUMERIC5

# 数据表查询

1、查看所有表名

SELECT * FROM sqlite_master WHERE type='table' ORDER BY name;

sqlite_master 是一个特殊表，属于系统表；存放在根页中，每一个数据库的 .db 文件都有一个 sqlite_master 表。该表存放了 .db 中所有表的相关信息，并只有只读权限，写操作由系统自动执行，使用者没有写的执行权限；其结构如下：

CREATE TABLE sqlite_master (
    type TEXT, // 记录项目的类型，如：表 (table), 索引 (index), 视图 (view), 触发器 (trigger)
    name TEXT, // 记录项目的名称，如表名、索引名等
    tbl_name TEXT, // 记录所从属的表名，如索引所在的表名。对于表来说，该列就是表名本身
    rootpage INTEGER, // 记录项目在数据库页中存储的编号。对于视图和触发器，该列值为 0 或者 NULL
    sql TEXT // 记录创建该项目的 SQL 语句
);

2、查看表的字段

PRAGMA table_info(table_name);

其中 [table_name] 为待查数据表名。

3、查看整表数据

SELECT * FROM table_name;

其中 [table_name] 为待查数据表名。

note：参数传入，字符串用单引号包括。

# C / C++ 接口函数

# Errors 码

#define SQLITE_OK           0   /* 成功 */
/* 错误码 */
#define SQLITE_ERROR        1   /* sql 错误或丢失的数据库，SQL error or missing database */
#define SQLITE_INTERNAL     2   /* sqlite 内部逻辑错误，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   /* 某次 malloc 函数调用失败，A malloc () failed */
#define SQLITE_READONLY     8   /* 尝试写入一个只读数据库，Attempt to write a readonly database */
#define SQLITE_INTERRUPT    9   /* 操作 sqlite3_interrupt 函数被中断，Operation terminated by sqlite3_interrupt ()*/
#define SQLITE_IOERR       10   /* 发生磁盘 I/O 错误，Some kind of disk I/O error occurred */
#define SQLITE_CORRUPT     11   /* 数据库磁盘映像不正确，The database disk image is malformed */
#define SQLITE_NOTFOUND    12   /* 找不到表或记录，NOT USED. 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   /* 数据库锁定协议错误，NOT USED. Database lock protocol error */
#define SQLITE_EMPTY       16   /* 数据库为空，Database is empty */
#define SQLITE_SCHEMA      17   /* 数据库结构发生改变，The database schema changed */
#define SQLITE_TOOBIG      18   /* 数据大小超限，String or BLOB exceeds size limit */
#define SQLITE_CONSTRAINT  19   /* 约束违反，Abort due to constraint 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_FORMAT      24   /* 附加数据库格式错误，Auxiliary database format error */
#define SQLITE_RANGE       25   /* sqlite3_bind 的第 2 给参数超出范围，2nd parameter to sqlite3_bind out of range */
#define SQLITE_NOTADB      26   /* 不是数据库文件，File opened that is not a database file */
#define SQLITE_NOTICE      27   /* Notifications from sqlite3_log() */
#define SQLITE_WARNING     28   /* Warnings from sqlite3_log() */
#define SQLITE_ROW         100  /* sqlite3_step 产生一个就绪行，sqlite3_step () has another row ready */
#define SQLITE_DONE        101  /* sqlite3_step 执行完成，sqlite3_step () has finished executing */

# API 函数

以下只列举嵌入式环境下常用的 API：

# sqlite3_open

SQLITE_API int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);

该函数用于打开由 filename 参数指定的 SQLite 数据库文件，并返回一个 *ppDb 数据库连接句柄，即使发生错误； filename 为 UTF-8 编码，如果 SQLite 无法分配内存来保存 sqlite3 对象，则会在 *ppDb 中写入一个 NULL，而不是指向 sqlite3 对象的指针。如果数据库成功打开，则返回 SQLITE_OK。否则返回错误代码。sqlite3_errmsg () 可用于获取任何 sqlite3_open () 执行失败后错误的英文描述。

如果 filename 参数是 ':memory:' ，那么 sqlite3_open () 将会在 RAM 中创建一个内存数据库，这只会在数据库连接关闭前持续。

如果 filename 参数是 '' （空字符串），则将创建一个专用的临时磁盘数据库；一旦数据库连接关闭，此专用数据库将被自动删除。

# sqlite3_close

SQLITE_API int sqlite3_close(sqlite3*);

该函数与 sqlite3_open 函数相对应，用于关闭之前调用 sqlite3_open () 打开的数据库连接，此时所有与连接相关的语句都应在连接关闭之前完成。

如果还有查询没有完成，sqlite3_close () 将返回 SQLITE_BUSY 禁止关闭的错误消息；当成功执行，将销毁 sqlite3 对象并释放所有关联资源时返回 SQLITE_OK。

# sqlite3_exec

SQLITE_API int sqlite3_exec(
  sqlite3*,                                  /* An open database */
  const char *sql,                           /* SQL to be evaluated */
  int (*callback)(void*,int,char**,char**),  /* Callback function */
  void *,                                    /* 1st argument to callback */
  char **errmsg                              /* Error msg written here */
);

sqlite3_exec () 提供了一个执行 SQL 命令的快捷方式，SQL 命令由 *sql 参数提供；它是 sqlite3_prepare_v2 () 、sqlite3_step () 和 sqlite3_finalize () 的包装函数，允许应用程序运行多个 SQL 语句，而无需编写大量 C 代码。

sqlite3_exec () 程序解析并执行由 *sql 参数所给的每个命令，直到字符串结束或者遇到错误为止。此参数等同于使用 shell 实现所有的 sqlite 功能命令，所以这个 *sql 就是对应 sqlite 功能命令的 “字符串”。

callback 是执行 *sql 对应的功能命令后，所调取的函数，即回调函数；用于将执行结果进行分析处理。其原型如下：

typedef int (*sqlite3_callback)(void *data, int argc, char **argv, char **azColName);

sqlite3_callback 参数：

  • data：由 sqlite3_exec 传入的参数 void* ，或者说是指针参数。
  • argc：查询到的这一条记录由多少个字段（多少列）。
  • argv：该参数是双指针，查询出来的数据都保存在这里，它是一个一维数组，每一个元素都是一个 char* , 是一个字段内容，所以这个参数就可以不是单字节，而是可以为字符串等不定长度的数值，用字符串表示，以 '\0' 结尾。
  • azColName：该参数是双指针，与 argv 是对应的，表示这个字段的字段名称。

note：除 data 传入参数外，回调函数的其它参数一定是 SQL 命令执行结果的进一步处理；此外，需要特别注意的是：回调函数多数时候不是执行 1 次，而是会循环执行 n 次，例如当我们执行 SELECT 命令时，往往输出的结果会是多行，假设有 n 行，那么就会执行 n 次回调函数。

实例：

#include <stdio.h>
#include <sqlite3.h>
static int callback(void *NotUsed, int argc, char **argv, char **azColName){
  int i;
  for(i=0; i<argc; i++){
    printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
  }
  printf("\n");
  return 0;
}
int main(int argc, char **argv){
  sqlite3 *db;
  char *zErrMsg = 0;
  int rc;
  if( argc!=3 ){
    fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);
    return(1);
  }
  rc = sqlite3_open(argv[1], &db);
  if( rc ){
    fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
    sqlite3_close(db);
    return(1);
  }
  rc = sqlite3_exec(db, argv[2], callback, NULL, &zErrMsg);
  if( rc!=SQLITE_OK ){
    fprintf(stderr, "SQL error: %s\n", zErrMsg);
    sqlite3_free(zErrMsg);
  }
  sqlite3_close(db);
  return 0;
}
# sqlite3_get_table
SQLITE_API int sqlite3_get_table(
  sqlite3 *db,          /* An open database */
  const char *zSql,     /* SQL to be evaluated */
  char ***pazResult,    /* Results of the query */
  int *pnRow,           /* Number of result rows written here */
  int *pnColumn,        /* Number of result columns written here */
  char **pzErrmsg       /* Error msg written here */
);
在最新的文档中，表明这该函数是一个传统的 API，保留该 API 是为了向后兼容；因此，不建议使用此接口。
输入参数：
  • db：打开数据库文件后返回的句柄。
  • zSql：需要执行的 SQL 命令字符串。
  • pazResult：对字符串数组的引用。使用此引用传回查询结果。传回的值必须通过调用 sqlite3_free_table() 。
  • pnRow：查询返回的行数，不包括列名。
  • pnColumn：查询返回的列数。
  • pzErrmsg：对字符串的可选引用。如果发生错误，引用将被设置为一条错误消息。该应用程序负责 释放的消息 sqlite3_free() 。如果没有错误发生，引用将被设置为 NULL。引用可能为 NULL。
和 sqlite3_exec () 这种使用回调函数的方式不同，sqlite3_get_table () 的功能要显得更加简便。它通过控制语句的传入，直接将参数赋值给指针传出。
note：虽然 sqlite3_get_table () 的使用较于 sqlite3_exec () 更为简便，但官方不推荐使用的，在实际当中它更趋于用作测试 API，并且根据官方建议，推荐使用 sqlite3_prepare_v2 ()、sqlite3_column_xxx () 这些 API 组合。
# sqlite3_free_table
SQLITE_API void sqlite3_free_table(char **result);
该函数与 sqlite3_get_table 函数相对应，在应用程序完成 sqlite3_get_table 的结果使用后，它必须将查询结果的指针 pazResult 传递给 sqlite3_free_table () 以释放分配的内存。
# sqlite3_prepare_v2
SQLITE_API 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 */
);
输入参数：
  • db：从先前成功调用 sqlite3_open（）、sqlite3_open v2（）或 sqlite3_open16（）获得的 SQLite 数据库。
  • zSql：要执行的 SQL 语句（可以包含未赋值的变量）
  • nByte：如果 nByte 参数为负，则 zSql 被读取到第一个空结束终止符。如果 nByte 为正，则它是从 zSql 读取的字节数。
  • ppStmt：解析编译出的 SQL 语句实例。
  • pzTail：如果 pzTail 不为空，那么 *pzTail 将指向 zSql 中第一个 SQL 语句结束后的第一个字节。
# sqlite3_bind_xxx
SQLITE_API int sqlite3_bind_blob(      sqlite3_stmt* stmt, int pidx, const void*          val, int            bytes, mem_callback);
SQLITE_API int sqlite3_bind_blob64(    sqlite3_stmt* stmt, int pidx, const void*          val, sqlite3_uint64 bytes, mem_callback);
SQLITE_API int sqlite3_bind_double(    sqlite3_stmt* stmt, int pidx, double               val);
SQLITE_API int sqlite3_bind_int(       sqlite3_stmt* stmt, int pidx, int                  val);
SQLITE_API int sqlite3_bind_int64(     sqlite3_stmt* stmt, int pidx, sqlite3_int64        val);
SQLITE_API int sqlite3_bind_null(      sqlite3_stmt* stmt, int pidx);
SQLITE_API int sqlite3_bind_text(      sqlite3_stmt* stmt, int pidx, const char*          val, int            bytes, mem_callback);
SQLITE_API int sqlite3_bind_text16(    sqlite3_stmt* stmt, int pidx, const void*          val, int            bytes, mem_callback);
SQLITE_API int sqlite3_bind_text64(    sqlite3_stmt* stmt, int pidx, const char*          val, sqlite3_uint64 bytes, mem_callback, unsigned char encoding);
SQLITE_API int sqlite3_bind_value(     sqlite3_stmt* stmt, int pidx, const sqlite3_value* val);
// SQLITE_API int sqlite3_bind_pointer(   sqlite3_stmt*, int, void*, const char*,void(*)(void*));
SQLITE_API int sqlite3_bind_zeroblob(  sqlite3_stmt* stmt, int pidx,                           int            bytes);
SQLITE_API int sqlite3_bind_zeroblob64(sqlite3_stmt* stmt, int pidx,                           sqlite3_uint64 bytes);
void mem_callback( void* ptr );
用于绑定任何参数值。
  • stmt：指向一个被 sqlite3_prepare_v2 () 等接口编译的语句句柄，它可被 sqlite3_step () 执行。最后用 sqlite3_finalize () 来释放它。
  • pidx：SQL 参数（每列元素）的索引值，参数索引值从 1 开始。
  • val：要绑定的数据值。
  • bytes：数据值的大小，以字节（不是字符）为单位。通常，长度不包括任何空结束符。如果 val 是一个以空结束的字符串，把该值设为为负值（-1），则会自动计算长度。
  • mem_callback：一个指向内存释放函数的函数指针。此函数用于在 SQLite 处理完 BLOB 或 string 之后对其进行处理，主要用来释放 val 的内存缓冲区；如果缓冲区是用 sqlite3_malloc () 分配的，则可以直接传递对 sqlite3_free () 的引用。也可以使用特殊标志 SQLITE_STATIC 和 SQLITE_TRANSIENT ； SQLITE_STATIC 说明数据值为常量，位于静态的非托管空间中，不需要释放，而 SQLITE_TRANSIENT 会使得对 val 数据做一份拷贝。
# sqlite3_step
SQLITE_API int sqlite3_step(sqlite3_stmt*);
在调用 sqlite3_prepare_v2 () 等接口编译的语句句柄后，必须调用此函数一次或多次以计算语句。
返回值：
  • SQLITE_BUSY：忙碌，数据库引擎无法锁定数据去完成其工作。可以重试该语句。
  • SQLITE_DONE：意味着 SQL 语句执行完成且成功。一旦执行成功后，sqlite3_step () 就不应该被再次调用执行，除非我们使用 sqlite3_reset () 重置 sqlite3_stmt 数据。
  • SQLITE_ROW：当返回的行数据较多时会出现，代表获得了一条有效数据行，此时可以通过 sqlite3_column_xxx 函数来取得数据；再次调用 sqlite3_step () 可取得下一条查询结果，直至返回 SQLITE_DONE 。
  • SQLITE_ERROR：表示发生了运行时错误（例如约束冲突），此时可以通过 sqlite3_errmmsg () 取得相关的错误信息，并且不应再次调用 sqlite3_step () 。
  • SQLITE_MISUSE：意味着这个例程被不适当地调用。可能是在已完成的准备好的语句上调用的，也可能是在以前返回 SQLITE_ERROR 或 SQLITE_DONE 的语句上调用的。或者可能是同一数据库连接同时被两个或多个线程使用。
# sqlite3_column_xxx
SQLITE_API const void*          sqlite3_column_blob(  sqlite3_stmt* stmt, int cidx);
SQLITE_API double               sqlite3_column_double( sqlite3_stmt* stmt, int cidx);
SQLITE_API int                  sqlite3_column_int(    sqlite3_stmt* stmt, int cidx);
SQLITE_API sqlite3_int64        sqlite3_column_int64(  sqlite3_stmt* stmt, int cidx);
SQLITE_API const unsigned char* sqlite3_column_text(  sqlite3_stmt* stmt, int cidx);
SQLITE_API const void*          sqlite3_column_text16(sqlite3_stmt* stmt, int cidx);
SQLITE_API sqlite3_value*       sqlite3_column_value( sqlite3_stmt* stmt, int cidx);
SQLITE_API int                  sqlite3_column_bytes(  sqlite3_stmt* stmt, int cidx);
SQLITE_API int                  sqlite3_column_bytes16(sqlite3_stmt* stmt, int cidx);
SQLITE_API int                  sqlite3_column_type(   sqlite3_stmt* stmt, int cidx);
用于从当前结果行中提取列值。
  • stmt：指向一个被 sqlite3_prepare_v2 () 等接口编译的语句句柄，它可被 sqlite3_step () 执行。最后用 sqlite3_finalize () 来释放它。
  • cidx：SQL 参数（每列元素）的索引值，参数索引值从 0 开始。
返回结果：
API 函数结果值
sqlite3_column_blob→BLOB
sqlite3_column_double→REAL
sqlite3_column_int→32 位 INTEGER
sqlite3_column_int64→64 位 INTEGER
sqlite3_column_text→UTF-8 TEXT
sqlite3_column_text16→UTF-16 TEXT
sqlite3_column_value→作为 未受保护的 sqlite3_value 对象
sqlite3_column_bytes→BLOB 或 UTF-8 TEXT 的字节大小
sqlite3_column_bytes16→UTF-16 TEXT 的字节大小
sqlite3_column_type→默认数据的类型
# sqlite3_reset
SQLITE_API int sqlite3_reset(sqlite3_stmt *pStmt);
用于重置 sqlite3_stmt 数据，然后准备被重新执行。任何使用 sqlite3_bind_xxx 函数将值绑定到它们的 SQL 语句变量都将保留它们的值。使用 sqlite3_clear_bindings () 重置绑定。
返回值：
  • SQLITE_BUSY
  • SQLITE_DONE
  • SQLITE_ROW
# 操作流程
使用 SQLite 数据库，最典型的函数操作流程（伪代码）：
伪代码
/* create a statement from an SQL string */
sqlite3_stmt *stmt = NULL;
if ( sqlite3_prepare_v2( db, sql_str, sql_str_len, &stmt, NULL ) == SQLITE_OK )
{
    /* use the statement as many times as required */
    while( ... )
    {
        /* bind any parameter values */
        sqlite3_bind_xxx( stmt, param_idx, param_value... );
        ...
        /* execute statement and step over each row of the result set */
        while ( sqlite3_step( stmt ) == SQLITE_ROW )
        {
            /* extract column values from the current result row */
            col_val = sqlite3_column_xxx( stmt, col_index );
            ...
        }
    
        /* reset the statement so it may be used again */
        sqlite3_reset( stmt );
        sqlite3_clear_bindings( stmt );  /* optional */
    }
}
/* destroy and release the statement */
sqlite3_finalize( stmt );
a、sqlite3_prepare 创建一个预处理对象。
b、sqlite3_bind_xxx 绑定参数。
c、循环执行 sqlite3_step 获取数据。
d、sqlite3_column 从数据中取出每一段。
e、释放 sqlite3_finalize 。
1、插入
sqlite3_open -> sqlite3_prepare_v2 -> sqlite3_bind_xxx -> sqlite3_step -> sqlite3_reset -> sqlite3_finalize -> sqlite3_close
2、查询
sqlite3_open -> sqlite3_prepare_v2 -> sqlite3_step -> sqlite3_column_xxx -> sqlite3_finalize -> sqlite3_close
3、例子
略略略
# 常用命令
If you use an auto-increment field, you can easily write this to delete the oldest 100 records:
DELETE FROM mytable WHERE id IN (SELECT id FROM mytable ORDER BY id ASC LIMIT 100)

Or, if no such field is present, use ROWID :
DELETE FROM mytable WHERE ROWID IN (SELECT ROWID FROM mytable ORDER BY ROWID ASC LIMIT 100)

Or, to leave only the latest 1000 records:
DELETE FROM mytable WHERE ROWID IN (SELECT ROWID FROM mytable ORDER BY ROWID DESC LIMIT -1 OFFSET 1000)

# 参考
An Introduction To The SQLite C/C++ Interface
C-language Interface Specification for SQLite
Datatypes In SQLite
Using sqlite3_bind_xxx()
Using sqlite3_column_xxx()
Write Programs That Use SQLite
SQLite 常用 API (C)
Delete oldest records from database
 更新于  阅读次数  次
请我喝[茶]~(￣▽￣)~*
夏沫の浅雨 微信支付
微信支付
夏沫の浅雨 支付宝
支付宝