C++ SQLite轻量化数据库使用总结

• 官网下载：https://www.sqlite.org/download.html
  
  
• 示例1

#include <iostream>
#include <sqlite3.h>int main() {sqlite3* db;char* zErrMsg = 0;int rc;// 打开数据库连接（如果数据库不存在，则会自动创建）rc = sqlite3_open("test.db", &db);if (rc) {std::cerr << "Can't open database: " << sqlite3_errmsg(db) << std::endl;return(0);} else {std::cout << "Opened database successfully" << std::endl;}// 创建表格const char* sqlCreateTable ="CREATE TABLE STUDENTS("  \"ID INT PRIMARY KEY NOT NULL," \"NAME TEXT NOT NULL," \"AGE INT NOT NULL);";rc = sqlite3_exec(db, sqlCreateTable, 0, 0, &zErrMsg);if (rc != SQLITE_OK) {std::cerr << "SQL error: " << zErrMsg << std::endl;sqlite3_free(zErrMsg);} else {std::cout << "Table created successfully" << std::endl;}// 插入数据const char* sqlInsertData ="INSERT INTO STUDENTS (ID, NAME, AGE) " \"VALUES (1, 'Alice', 20), " \"       (2, 'Bob', 22), " \"       (3, 'Charlie', 23);";rc = sqlite3_exec(db, sqlInsertData, 0, 0, &zErrMsg);if (rc != SQLITE_OK) {std::cerr << "SQL error: " << zErrMsg << std::endl;sqlite3_free(zErrMsg);} else {std::cout << "Data inserted successfully" << std::endl;}// 查询数据sqlite3_stmt* stmt;const char* sqlSelectData = "SELECT * FROM STUDENTS;";rc = sqlite3_prepare_v2(db, sqlSelectData, -1, &stmt, 0);if (rc != SQLITE_OK) {std::cerr << "Failed to select data: " << sqlite3_errmsg(db) << std::endl;} else {while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {int id = sqlite3_column_int(stmt, 0);const unsigned char* name = sqlite3_column_text(stmt, 1);int age = sqlite3_column_int(stmt, 2);std::cout << "ID: " << id << ", Name: " << name << ", Age: " << age << std::endl;}sqlite3_finalize(stmt);}// 更新数据const char* sqlUpdateData ="UPDATE STUDENTS " \"SET AGE = 24 " \"WHERE ID = 2;";rc = sqlite3_exec(db, sqlUpdateData, 0, 0, &zErrMsg);if (rc != SQLITE_OK) {std::cerr << "SQL error: " << zErrMsg << std::endl;sqlite3_free(zErrMsg);} else {std::cout << "Data updated successfully" << std::endl;}// 再次查询数据以验证更新结果rc = sqlite3_prepare_v2(db, sqlSelectData, -1, &stmt, 0);if (rc != SQLITE_OK) {std::cerr << "Failed to select data after update: " << sqlite3_errmsg(db) << std::endl;} else {while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {int id = sqlite3_column_int(stmt, 0);const unsigned char* name = sqlite3_column_text(stmt, 1);int age = sqlite3_column_int(stmt, 2);std::cout << "ID: " << id << ", Name: " << name << ", Age: " << age << std::endl;}sqlite3_finalize(stmt);}// 关闭数据库连接sqlite3_close(db);return 0;
}

关于内容写入及效率优化

• 文章开始列了一个内容插入与查询的简单例子，下边说一下内容写入提速的几点优化，着重参考https://www.cnblogs.com/rainbowzc/p/6444389.html

  • 显示开启事务
  • 写同步
  • 执行字符串准备
  • 高并发模式

• 下边简单示例

#include <iostream>  
#include <string>  
#include <sstream>  
#include <time.h>  
#include "sqlite3.h"  const int nCount = 500000;  int main (int argc,char** argv)  
{  sqlite3* db;  sqlite3_open("testdb.db" ,&db);  sqlite3_exec(db,"PRAGMA synchronous = OFF; ",0,0,0);  sqlite3_exec(db,"drop table if exists t1",0,0,0);  sqlite3_exec(db,"create table t1(id integer,x integer,y integer ,weight real)",0,0,0);  clock_t t1 = clock();  sqlite3_exec(db,"begin;",0,0,0);  sqlite3_stmt *stmt;  const char* sql = "insert into t1 values(?,?,?,?)";  sqlite3_prepare_v2(db,sql,strlen(sql),&stmt,0);  for(int i=0;i<nCount;++i)  {  // std::stringstream ssm;  // ssm<<"insert into t1 values("<<i<<","<<i*2<<","<<i/2<<","<<i*i<<")";  // sqlite3_exec(db,ssm.str().c_str(),0,0,0);  sqlite3_reset(stmt);  sqlite3_bind_int(stmt,1,i);  sqlite3_bind_int(stmt,2,i*2);  sqlite3_bind_int(stmt,3,i/2);  sqlite3_bind_double(stmt,4,i*i);  sqlite3_step(stmt);  }  sqlite3_finalize(stmt);  sqlite3_exec(db,"commit;",0,0,0);  clock_t t2 = clock();  sqlite3_close(db);  std::cout<<"cost tima: "<<(t2-t1)/1000.<<"s"<<std::endl;  return 0;  
}  

使用关键词查询表格

- 0.打开数据库连接：使用sqlite3_open打开数据库文件test.db。
- 1.创建表格：使用sqlite3_exec执行SQL语句来创建表格。
- 2.插入数据：使用sqlite3_exec执行SQL语句来插入数据。
- 3.准备和执行查询：- 使用sqlite3_prepare_v2准备SQL查询语句。- 使用sqlite3_bind_text绑定查询参数（关键词）。- 使用sqlite3_step执行查询并迭代结果集。- 使用sqlite3_column_int和sqlite3_column_text获取查询结果。
- 4.清理资源：使用sqlite3_finalize释放准备好的语句，使用sqlite3_close关闭数据库连接。

#include <iostream>
#include <sqlite3.h>
#include <string>void check_error(int rc, sqlite3* db) {if (rc) {std::cerr << "SQL error: " << sqlite3_errmsg(db) << std::endl;sqlite3_close(db);exit(rc);}
}int main() {sqlite3* db;char* zErrMsg = 0;int rc;// 打开数据库连接rc = sqlite3_open("test.db", &db);if (rc) {std::cerr << "Can't open database: " << sqlite3_errmsg(db) << std::endl;return rc;} else {std::cout << "Opened database successfully" << std::endl;}// 创建表格const char* sql_create_table ="CREATE TABLE IF NOT EXISTS PERSON(""ID INTEGER PRIMARY KEY AUTOINCREMENT,""NAME TEXT NOT NULL,""AGE INTEGER NOT NULL);";rc = sqlite3_exec(db, sql_create_table, 0, 0, &zErrMsg);check_error(rc, db);// 插入数据const char* sql_insert_data ="INSERT INTO PERSON (NAME, AGE) VALUES ('Alice', 30);""INSERT INTO PERSON (NAME, AGE) VALUES ('Bob', 25);""INSERT INTO PERSON (NAME, AGE) VALUES ('Charlie', 35);";rc = sqlite3_exec(db, sql_insert_data, 0, 0, &zErrMsg);check_error(rc, db);// 查询数据std::string keyword = "Alice";const char* sql_query = "SELECT * FROM PERSON WHERE NAME LIKE ?;";sqlite3_stmt* stmt;const char* tail;rc = sqlite3_prepare_v2(db, sql_query, -1, &stmt, &tail);if (rc != SQLITE_OK) {std::cerr << "Failed to prepare statement: " << sqlite3_errmsg(db) << std::endl;sqlite3_close(db);return rc;}// 绑定参数sqlite3_bind_text(stmt, 1, keyword.c_str(), -1, SQLITE_STATIC);// 执行查询while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {int id = sqlite3_column_int(stmt, 0);const unsigned char* name = sqlite3_column_text(stmt, 1);int age = sqlite3_column_int(stmt, 2);std::cout << "ID: " << id << ", Name: " << name << ", Age: " << age << std::endl;}if (rc != SQLITE_DONE) {std::cerr << "Execution failed: " << sqlite3_errmsg(db) << std::endl;}sqlite3_finalize(stmt);sqlite3_close(db);return 0;
}

创建索引

• 创建索引会提高查询速度

#include <iostream>
#include <sqlite3.h>int main() {sqlite3 *db;char *zErrMsg = 0;int rc;// 打开数据库连接rc = sqlite3_open("test.db", &db);if (rc) {std::cerr << "Can't open database: " << sqlite3_errmsg(db) << std::endl;return(0);} else {std::cout << "Opened database successfully" << std::endl;}// 创建表const char *sqlCreateTable = "CREATE TABLE COMPANY("  \"ID INT PRIMARY KEY     NOT NULL," \"NAME           TEXT    NOT NULL," \"AGE            INT     NOT NULL," \"ADDRESS        CHAR(50)," \"SALARY         REAL );";rc = sqlite3_exec(db, sqlCreateTable, 0, 0, &zErrMsg);if (rc != SQLITE_OK) {std::cerr << "SQL error: " << zErrMsg << std::endl;sqlite3_free(zErrMsg);} else {std::cout << "Table created successfully" << std::endl;}// 创建索引const char *sqlCreateIndex = "CREATE INDEX idx_name ON COMPANY(NAME);";rc = sqlite3_exec(db, sqlCreateIndex, 0, 0, &zErrMsg);if (rc != SQLITE_OK) {std::cerr << "SQL error: " << zErrMsg << std::endl;sqlite3_free(zErrMsg);} else {std::cout << "Index created successfully" << std::endl;}// 关闭数据库连接sqlite3_close(db);return 0;
}

参考：

1.https://blog.csdn.net/zearot/article/details/51039593