潍坊网站制作案例,wordpress 本地访问慢,永久域名网站,wordpress视频网站上传视频写在前面#xff1a;
本文章旨在总结备份、方便以后查询#xff0c;由于是个人总结#xff0c;如有不对#xff0c;欢迎指正#xff1b;另外#xff0c;内容大部分来自网络、书籍、和各类手册#xff0c;如若侵权请告知#xff0c;马上删帖致歉。 目录 SQLite 类型数据…写在前面
本文章旨在总结备份、方便以后查询由于是个人总结如有不对欢迎指正另外内容大部分来自网络、书籍、和各类手册如若侵权请告知马上删帖致歉。 目录 SQLite 类型数据表查询C / C 接口函数Errors 码API 函数sqlite3_opensqlite3_closesqlite3_execsqlite3_get_tablesqlite3_free_tablesqlite3_prepare_v2sqlite3_bind_xxxsqlite3_stepsqlite3_column_xxxsqlite3_reset 操作流程 常用命令参考 首发及后期更新将于 https://arachnid.cc/sqlite-summarize/此处仅作二次备份。 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 表示 true0 表示 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 数据库中支持分配的类型关联
TEXTNUMERICINTEGERREALBLOB
决定规则
如果声明的类型包含字符串 “INT”则它分配为 INTEGER 。如果声明的类型包含任意字符串 “CHAR”、“CLOB” 或 “TEXT” 的关键字则被分配为 TEXT 。请注意VARCHAR 类型包含字符串 “CHAR”因此被分配为 TEXT 。如果声明的类型包含字符串 “BLOB”或者如果未指定类型则被分配为 BLOB 。如果声明的类型包含任意字符串 “REAL”、“FLOA” 或 “DOUB” 的关键字则被分配为 REAL 。其余情况下归类为 NUMERIC 。
note请注意确定类型亲缘性的规则顺序很重要。声明类型为 “CHARINT” 的字段将同时匹配规则 1 和 2但第一条规则采用优先权所以会被分配为 INTEGER 。
示例
声明的类型类型亲缘性规则优先级INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8INTEGER1CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOBTEXT2BLOB no datatype specifiedBLOB3REAL DOUBLE DOUBLE PRECISION FLOATREAL4NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIMENUMERIC5 数据表查询
1、查看所有表名
SELECT * FROM sqlite_master WHERE typetable 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 或者 NULLsql 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.hstatic int callback(void *NotUsed, int argc, char **argv, char **azColName){int i;for(i0; iargc; 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, Cant 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() 来释放它。pidxSQL 参数每列元素的索引值参数索引值从 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() 来释放它。cidxSQL 参数每列元素的索引值参数索引值从 0 开始。
返回结果
API 函数结果值sqlite3_column_blob→BLOBsqlite3_column_double→REALsqlite3_column_int→32 位 INTEGERsqlite3_column_int64→64 位 INTEGERsqlite3_column_text→UTF-8 TEXTsqlite3_column_text16→UTF-16 TEXTsqlite3_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_BUSYSQLITE_DONESQLITE_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©
Delete oldest records from database
