SQLite数据库C++基础用法

1. 引言笔者最近在做嵌入式Linux应用项目中需要用到SQLite数据库因此本期分享一下其基础知识。SQLite 是一个嵌入式、无服务器的轻量级关系型数据库它以函数库形式直接集成到应用程序中将整个数据库存储为单个普通文件无需安装、配置或维护独立进程。因其极小的内存占用、零管理成本和跨平台特性非常适合移动应用、桌面软件、嵌入式设备或测试原型等单应用本地存储场景。相比 MySQL等传统数据库SQLite 牺牲了高并发写入、网络访问和复杂权限管理换来了极简部署和直接文件访问的高性能。因此当你的应用只需本地数据存储、没有多客户端同时写入时SQLite 是最省力的选择反之需要网络服务、高并发或企业级功能时才选用其他数据库。2. 基础SQL语法2.1 创建表基本语法如下CREATE TABLE database_name.table_name( column1 datatype PRIMARY KEY(one or more columns), column2 datatype, column3 datatype, ..... columnN datatype, );CREATE TABLE代表创建表的关键字。data type代表每一列的数据类型PRIMARY KEY代表的是主键表示唯一标识。实例如下所示以笔者项目为例/* 创建检测记录表的 SQL 语句 */ const char *create_table_sql CREATE TABLE IF NOT EXISTS inspection_records ( id INTEGER PRIMARY KEY AUTOINCREMENT, inspected_at TEXT NOT NULL, product_code TEXT NOT NULL, result_pass INTEGER NOT NULL, failure_reason TEXT NOT NULL, recipe_version TEXT NOT NULL );;上述例子中先创建了一个C风格的字符串指针create_table_sql指向后边的SQL语句文本表名为inspection_records。下边针对SQL语句一行一行的看第一列是id主键、INTEGER代表整数类型PRIMARY KEY AUTOINCREMENT表示这是主键并且每次插入时自动生成自增值。第二列是inspected_at类型是TEXTNOT NULL表示这一列必须要有值该列用于存放时间戳。第三列是product_code类型是TEXTNOT NULL表示这一列必须要有值该列用于存放产品码其他的也是同理就不再多加说明。2.2 插入基本语法有两种如下所示#第一种 column1, column2,...columnN 是要插入数据的表中的列的名称 INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)] VALUES (value1, value2, value3,...valueN); # 第二种要为表中的所有列添加值 #可以不需要在 SQLite 查询中指定列名称 #但要确保值的顺序与列在表中的顺序一致 INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);其中TABLE_NAME为表名VALUES、INSERT INTO为关键字。以笔者项目举例使用方法如下/*插入表代表占位符表示插入前由程序动态提供具体值 *有占位符时需使用绑定函数将值传入到预编译语句中否则会导致 SQL 注入风险 */ const char *insert_sql INSERT INTO inspection_records( inspected_at, product_code, result_pass, failure_reason, recipe_version ) VALUES(?, ?, ?, ?, ?);;2.3 查询在SQLie数据库中使用SELECT语句在数据库表中获取数据以结果表得形式返回数据。基本语法如下# column1, column2...是表的字段他们的值即是要获取的 SELECT column1, column2, columnN FROM table_name; # 如果想获取所有可用的字段那么可以使用下面的语法 SELECT * FROM table_name;实例如下所示/*构建查询SQL语句根据条件动态添加过滤和限制*/ std::string sql SELECT inspected_at, product_code, result_pass, failure_reason, recipe_version FROM inspection_records;2.4 WHERE子句SQLite的WHERE子句用于指定从一个表或多个表中获取数据的条件。如果满足给定的条件即为真true时则从表中返回特定的值。可以使用 WHERE 子句来过滤只获取需要的记录一般可用在 SELECT、 UPDATE、DELETE 语句中。其语法如下SELECT column1, column2, columnN FROM table_name WHERE [condition]2.5 Limit子句SQLite 的LIMIT子句用于限制由 SELECT 语句返回的数据数量基本语法如下# LIMIT [no of rows]这部分用来限制查询结果的行数。 #[no of rows] 是一个数字表示你想要返回的最大行数。 #如LIMIT 10那么查询将只返回最多10行数据 SELECT column1, column2, columnN FROM table_name LIMIT [no of row]3. SQLite的C接口笔者将对自己项目中所用到的核心API进行讲解通常情况下掌握以下几个足以够用。3.1 sqlite3_open函数该函数用于创建或者打开一个数据库文件并返回一个数据库对象参数如下所示int sqlite3_open( const char *filename, /* 数据库文件名UTF-8 */ sqlite3 **ppDb /* 输出数据库连接句柄 */ );以笔者项目为例用法如下所示static int open_database(const std::string db_path, sqlite3 **db) { if (sqlite3_open(db_path.c_str(), db) ! SQLITE_OK) { if (*db ! nullptr) { sqlite3_close(*db); //关闭数据库需要与sqlite3_open连用 *db nullptr; } return -1; } return 0; }3.2 sqlite3_exec函数该函数用于执行一条或多条SQL语句参数如下int sqlite3_exec( sqlite3 *db, /* 数据库连接 */ const char *sql, /* 要执行的 SQL 语句UTF-8 */ int (*callback)(void*, int, char**, char**), /* 结果回调函数 */ void *data, /* 传递给回调的第一个参数 */ char **errmsg /* 输出错误信息需要 sqlite3_free() 释放 */ );但需要注意的是该函数不适合处理大数据量对于大数据量需要使用sqlite3_prepare_v2()sqlite3_step()循环接下来对其进行讲解。3.3 sqlite3_prepare_v2函数该函数代表“准备”将 SQL 文本编译为字节码程序称为“预编译语句”并返回一个sqlite3_stmt句柄。其参数如下int sqlite3_prepare_v2( sqlite3 *db, /* 数据库连接 */ const char *zSql, /* SQL 语句UTF-8 */ int nByte, /* SQL 语句长度字节传 -1 表示自动计算到第一个 \0 */ sqlite3_stmt **ppStmt, /* 输出预编译语句句柄 */ const char **pzTail /* 输出指向未使用的 SQL 部分可传 NULL */ );需要注意的是如果需要执行参数化查询使用?或:name占位符则要通过sqlite3_bind_*()函数绑定值以将参数传递给预编译语句比如说将某个参数填充到SQL语句的占位符处。此外与创建预编译语句函数对应有一个销毁预编译语句函数sqlite3_finalize(sqlite3_stmt *pStmt)一般与sqlite3_prepare_v2函数搭配使用。3.4 sqlite3_step函数该函数代表“执行”表示执行一次预编译语句通常与sqlite3_prepare_v2函数连用参数如下int sqlite3_step(sqlite3_stmt *pStmt);其返回值如下所示SQLITE_ROW成功获取到一行数据可以通过sqlite3_column_*()函数读取列值。SQLITE_DONE语句执行完毕查询无更多行或非查询语句完成。SQLITE_BUSY数据库被锁定通常需要重试。SQLITE_ERROR、SQLITE_MISUSE等错误码。4. 总结上述仅仅是笔者在项目中用到的一些知识更完整的知识建议大家可以去菜鸟编程网站进行学习。5. 参考文献[1] SQLite – C/C | 菜鸟教程