- 官网下载: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;
}