SQLite3数据库的介绍和使用（面向业务编程-数据库）
SQLite3数据库的介绍和使用（面向业务编程-数据库）
SQLite3介绍
SQLite是一种用C语言实现的的SQL数据库
它的特点有：轻量级、快速、独立、高可靠性、跨平台
它广泛应用在全世界范围内的手机电脑应用的内建数据库
官网地址：https://www.sqlite.org/index.html
SQLite因为其采用文件存储，且容易移植。在嵌入式中应用非常广泛，可以说是嵌入式业务开发的必学库
这次先讲一下怎么获取源码和使用
获取SQLite3源码
SQLite3的官网是https://www.sqlite.org/download.html
在官网找到sqlite-autoconf-3410200.tar.gz文件下载，如下
wget https://www.sqlite.org/2023/sqlite-autoconf-3410200.tar.gz
tar -zxvf sqlite-autoconf-3410200
下载后解压，会发现里面很多其他的文件。其中，tea目录是（Tcl Extension Architecture）可以不用管
主要看里面的c文件和h文件，所以我们把源代码放到另一个目录
在工程目录创建一个目录lib/sqlite3，然后删除解压后的源码目录
cp sqlite-autoconf-3410200/*.c lib/sqlite3/
cp sqlite-autoconf-3410200/*.h lib/sqlite3/
rm -r sqlite-autoconf-3410200
将需要的头文件和源文件拷贝进去
有点强的是sqlite3.c文件的大小居然有8.3M
完成后目录树大概应该是这个样子的
├── build
├── CMakeLists.txt
├── main.cpp
└── lib
└── sqlite3
├── shell.c
├── sqlite3.c
├── sqlite3ext.h
├── sqlite3.h
└── sqlite3rc.h
编写CMake工程
其中shell.c是对应的命令行文件，我们可以不用添加。仔细研读官网的文档
https://www.sqlite.org/howtocompile.html
所以CMakeLists.txt我们可以这么写
cmake_minimum_required(VERSION 3.0 FATAL_ERROR)
project(useSQLite LANGUAGES C CXX)
set(CMAKE_CXX_FLAGS "${CMAKE_CXX_FLAGS} -std=c++11 -pthread")
add_executable(useSQLite main.cpp)
# lib sqlite3
include_directories(lib/sqlite3)
add_library(sqlite3
STATIC
lib/sqlite3/sqlite3.c)
target_link_libraries(sqlite3 dl)
#
target_link_libraries(useSQLite sqlite3)
# sqlite3-cli
add_executable(SQLite3-cli lib/sqlite3/shell.c)
target_link_libraries(SQLite3-cli sqlite3)
其中关于添加-ldl选项的步骤参考回答：https://stackoverflow.com/questions/20131138/cmake-add-ldl-at-end-of-link-stage-of-add-library
main.cpp
添加main.cpp文件如下
#include <iostream>
#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, 0, &zErrMsg);
if (rc!=SQLITE_OK) {
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
sqlite3_close(db);
return 0;
}
简单讲一下这个源码，其实就是打开一个数据库文件执行一条指令
看上面的Usage: %s DATABASE SQL-STATEMENT\n就知道，参数是带引号的SQL语句
里面调用了三个库函数sqlite3_open()、sqlite3_exec()和sqlite3_close()
分别是打开SQLite数据库文件，执行第二个参数给的命令，关闭SQLite数据库文件
因为SQLite是本地文件存储和读写的，所以使用起来还是比较简单的，不用考虑太多网络的问题。
编译
mkdir build && cd build
cmake .. && make
编译出来有两个可执行文件，一个是useSQLite一个是SQLite3-cli。
useSQLite就是我们上面说的main.cpp的内容。
SQLite3-cli是官方给的一个命令行执行SQL的程序，可以用它去查一些数据什么的
但是不是很建议用这个，建议用Ubuntu安装的SQLite3去做查表操作，回退（删除）比这个方便些（下面一节介绍怎么安装）
使用SQL
本文为作者原创文章，转载请注明出处：https://www.cnblogs.com/nbtech/p/use_sqlite_library.html
那么都叫SQLite了，肯定是支持SQL语句的，所以使用SQLite，肯定要懂一些SQL。
这里需要注意的是SQLite中的SQL和其他数据库的SQL有所区别，使用的时候需要小心。本文只讲SQLite的SQL
不过也并不用太担心，差异不是很大
那么这里简单讲两个SQL语句
因为只是简单的介绍文，所以不会说的很详细
安装一个SQLite3
我们为了方便练习，可以直接在Ubuntu下安装一个SQLite3程序
sudo apt install sqlite3
那么简单创建一个数据库可以这样
sqlite3 mydatabase.db
mydatabase.db是数据库的名字，这样打开之后，就可以执行后面的SQL语句了
不过也可以拿到shell.c编译出来的SQLite3-cli（这个比较难用，不过也够用）
建表
首先，数据库，数据库有关系型数据库（SQL）和非关系型（NoSQL），那么SQLite是属于 关系型的数据库
关系型的数据库呢，一个特点就是它比较的结构化。结构化存储，就是里面存放东西都是相同结构的，那么相同结构的东西呢就放在一个表里面。
类似于书架上的书籍，整理的时候会将同一类型的书籍放到一个书架上面
那么先建立一个书架，咳咳，建表
CREATE TABLE IF NOT EXISTS mytable (id integer primary key,name text);
上面语句可以建表，建表前会判断表是不是已经存在，也可以不去判断，如下
CREATE TABLE yourtable (id integer primary key, name text, age integer);
primary key表示将该字段设置成主键
注意：语句结束需要由;（分号）进行结尾，表示这一句SQL结束了，SQLite可以执行了
那么新建的这张表有两个字段，一个是id，一个是name。id字段的类型是integer整形，就是整数类型，就是0、1、2这些
name字段是text类型，就是文本类型，例如，"zhangsan"或者 "张三"就是文字类型
除了设置主键约束，还可以设置唯一约束。SQLite里面设置唯一只需要在字段后面加上一个unique即可
例如在上面name text 改成name text unique
插入行
类比我们往书架上放一本书
数据库，就是存放数据的一个地方。数据仓库。
现在库已经有了（已经建好表了），那么就可以存入结构化的数据了。
在一张表中插入一行数据的操作很简单，只需要
INSERT INTO mytable (id,name) values (1000, "zhangsan");
注意上面这个zhangsan要用引号括起来，表示这是一个字符串（前面的1000因为是数字所以不用括号）
INSERT INTO表示插入到，mytable指定对应的表，所以上面语句表示向mytable这张表插入一行数据，数据内容就是
id是1000，name是"zhangsan"的一行数据
非常容易理解
重复值：如果我们想尝试往里面插入相同的id的行，就是报错，因为id是主键，主键是不允许重复的。但是插入相同的非主键的值是被允许的。
INSERT INTO mytable (id, name) values (1000, "lisi");
Runtime error: UNIQUE constraint failed: mytable.id (19)
查找数据
可以类比我们从书架上挑选感兴趣的书，例如找两本比较厚的书
如果我们想查找一张表里面的所有数据，如下
SELECT * FROM mytable;
*表示匹配所有项，FROM表示从mytable中，SELECT表示选择
所以就是：从mytable表中选择所有项
过滤结果
但是如果我们想从表中获取特定的项呢，我们可以搭配WHERE，例如，我们想提取id大于1000的数据，可以这么写
SELECT * FROM mytable WHERE id > 1000;
例如我们的表数据内容如下
1000|zhangsan
1001|lisi
1002|wangwu
可以获取这样的结果
1001|lisi
1002|wangwu
删除一项数据
可以类比我们从书架上拿下一本书，这本书不存放到这个书架了
DELETE就是删除，那么删除一条数据就是要指定是哪个表的那条数据，可以这么写
DELETE FROM mytable WHERE id=1001;
就可以删除id为1001的数据了，WHERE就是用来指定条件的，一般我们DELETE都是需要搭配WHERE使用，因为通常是要删除一条或几条数据。
那么如果不加WHERE，就是删除表上的所有数据（注意，只是删除表里面的所有记录，表还是在的）
DELETE FROM mytable;
多条件
有时候我们需要删除符合多个条件的数据，我们可以用AND将两个语句连接起来
例如说，需要删除age字段大于35的并且id字段小于1000的，可以这么写
DELETE FROM yourtable WHERE id<1000 AND age>35;
如果我们想修改一项数据，例如想将id为1000的"zhangsan"修改成"zhansang"
我们可以删除id为1000的数据然后插入id为1000但是name字段为"zhansang"的数据，但是我们可以不必这么做，我们可以更新值
更新字段
这个不好类比书架了，可以类比于拿下一本书换了一本上去吧
更新字段的值，首先要知道是那个字段，所以一定有WHERE语句，然后更新是UPDATE，所以更新字段的语句就是
UPDATE mytable SET name="zhansang" WHERE name="zhangsan";
当然条件的字段和SET的字段不一定要同一个字段，例如条件可以是id<1000，SET后面可以name="zhansang"类似这样，就可以将所有id小于1000的name都更新成"zhansang"了
SQL总结
SQL数据库的应用非常广泛，包括数据采集、数据分析、单纯的存取数据。
SQL是非常好用的数据库查询语言，并且它不复杂，比较容易懂。而且专业做SQL的人工资也不低（前提是就是靠这个吃饭的哈）。
学好SQL无论是对实用性还是经济性来说都是非常好的，写SQL有点像搭积木，想要什么就搭建成什么样。
C语言编程
在前面编译CMake工程中其实就有编译出使用SQLite进行C语言编程的源码
里面main.cpp里面主要用到3个Sqlite3里面的函数，分别是sqlite3_open、sqlite3_exec和sqlite3_close
非常简单，三个函数分别是
sqlite3_open表示打开一个数据库文件，一般为xxx.db
sqlite3_exec表示执行一条SQL语句
sqlite3_close表示关闭数据库文件
打开和关闭一个数据库文件
打开一个数据库文件
首先，先编写如下代码
int opendatabase(sqlite3** db, const char* dbfilename) {
char *zErrMsg = 0;
int rc;
rc = sqlite3_open(dbfilename, db);
if(SQLITE_OK != rc) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(*db));
sqlite3_close(*db);
return -1;
}
return 0;
}
上面打开一个名为mydatabase.db的数据库，db为该数据库的句柄
然后判断有没有错误（一般在指定文件不是一个数据库文件会报错），如果没有指定的数据库文件会自动创建。
关闭一个数据库句柄
关闭一个数据库文件可以直接调用sqlite3_close(sqlite3* db);也可以自己封装一个函数，如下
int clsoedb(sqlite3** db) {
if(NULL != *db) {
sqlite3_close(*db);
return 0;
}
return -1;
}
执行SQLite语句
本文为作者原创文章，转载请注明出处：https://www.cnblogs.com/nbtech/p/use_sqlite_library.html
因为可以执行的语句特别多，而且上面章节也讲过有关SQLite语句的一些介绍，所以这里主要讲sqlite3_exec这个C语言接口
先看一下源码实现
SQLITE_API int sqlite3_exec(
sqlite3 *db, /* The database on which the SQL executes */
const char *zSql, /* The SQL to be executed */
sqlite3_callback xCallback, /* Invoke this callback routine */
void *pArg, /* First argument to xCallback() */
char **pzErrMsg /* Write error messages here */
){
第一个参数是数据库的句柄，用于操作数据库用的。
第二个参数是传递给SQLite执行的SQL语句
第三个参数是执行后的回调函数
第四个参数是回调函数的第一个参
第五个参数是如果执行语句错误，用于返回错误信息的字符串指针
那么第三个参数和第四个参数可以根据需要置为空或者给一个函数指针和一片内存（通常第四个参数用来返回执行后的结果）
不带回调
不需要回调的示例，参考如下代码
//创建表
int create_table(sqlite3 *pdb) {
char *sql = NULL;
char *errmsg = NULL;
int ret;
sql = (char*)"create table if not exists mytable (id integer primary key,name text);";
ret = sqlite3_exec(pdb, sql, NULL, NULL, &errmsg);
if(SQLITE_OK != ret) {
printf("create table error! %s\n", errmsg);
return -1;
} else {
return 0;
}
}
上面的sql的值就是建表的一个操作，可以参考上面SQL语句的相关介绍。因为建表操作我们只需要知道建表成功了还是失败了，所以我们不需要填充xCallback和pArg参数，自然填NULL的时候不会调用到回调
带回调
需要调用回调的示例，参考如下代码
// callback
int show_row(void *return_, int column, char* result[], char** column_name) {
for(int i = 0; i < column; i++) {
printf("%s\t", result[i]);
}
printf("\n");
return 0;
}
// 查询和显示所有mytable的数据
int query_all_and_show(sqlite3* pdb) {
char sql[24] = {0};
char *errmsg = NULL;
int ret;
strncpy(sql, "select * from mytable;", 22);
// 数据库，语句字符串，回调函数，用户输入的参数，最终传给回调函数使用，错误信息
ret = sqlite3_exec(pdb, sql, show_row, NULL, &errmsg);
if(SQLITE_OK != ret) {
printf("select exec error: %s\n", errmsg);
return -1;
}
return 0;
}
上述代码查询了当前mytable表里面的所有行的数据，上面的会调函数当有多行的时候会调用多次。每一次都可以将一行的数据打印出来
如果需要将表返回到主函数，可以在pArg参数那里填充一个结构体链表指针（或者vector），然后每次创建一项就可以返回整张表的内容。
sqlite3_get_table
除了sqlite3_exec()函数，SQLite3还提供了一个函数可以执行语句，并且可以在同一个函数中处理返回的数据，像上述的查表的操作其实用这个函数会更好一点
SQLITE_API int sqlite3_get_table(
sqlite3 *db, /* The database on which the SQL executes */
const char *zSql, /* The SQL to be executed */
char ***pazResult, /* Write the result table here */
int *pnRow, /* Write the number of rows in the result here */
int *pnColumn, /* Write the number of columns of result here */
char **pzErrMsg /* Write error messages here */
){
同样的，我们看下参数，第一个参数是数据库句柄，第二个参数是需要执行的SQL语句
第三个参数是返回的结果的表
第四个参数是一共有多少行
第五个参数是一共有多少列
第六个参数是产生错误时的错误信息返回
这里不讲具体的写法了，输出行列值可以参考以下写法
for(int i = 0; i < Col; i++) {
for(int j = 0; j < Row; j++) {
printf("%s\t", azResult[i*Row+j]);
}
printf("\n");
}
SQLitecpp
SQLiteC++是一个简洁易用的C++封装库
正常我们用C语言去编程，可以像上面章节说的，自己去封装相关的操作。如果你的项目用上了C++，那么我推荐用C++封装的库去写，会比较方便一点。
源文件
上面C语言编程的章节，介绍了相关操作数据库的流程，这里不再重复介绍
我们看下使用SQLiteC++最简单的示例是怎么样的，首先SQLiteC++的源码在：https://github.com/SRombauts/SQLiteCpp
我们可以创建一个工程，然后获取SQLiteC++的源码
mkdir useSQLiteCpp && cd useSQLiteCpp
git clone https://github.com/SRombauts/SQLiteCpp.git
cd SQLiteCpp
git submodule init
git submodule update
在examples/example2/src路径下有一个示例的main.cpp，我们可以通过观察这个文件学习SQLiteC++库的相关操作，在useSQLiteCpp目录创建main.cpp，内容如下
#include <iostream>
#include "SQLiteCpp/SQLiteCpp.h"
// https://www.cnblogs.com/nbtech/p/use_sqlite_library.html
int main() {
try
{
// Open a database file in create/write mode(用写模式打开一个数据库文件)
SQLite::Database db("test.db3", SQLite::OPEN_READWRITE|SQLite::OPEN_CREATE);
std::cout << "SQLite database file '" << db.getFilename().c_str() << "' opened successfully\n";
// Create a new table with an explicit "id" column aliasing the underlying rowid（创建一个表，id设置为主键）
db.exec("DROP TABLE IF EXISTS test");
db.exec("CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)");
// first row（插入一行，id是NULL就是不指定，不指定会从1开始分配，value是test）
int nb = db.exec("INSERT INTO test VALUES (NULL, \"test\")");
std::cout << "INSERT INTO test VALUES (NULL, \"test\")\", returned " << nb << std::endl;
// second row（插入第二行，id是2，根据上一条记录加一，value是second）
nb = db.exec("INSERT INTO test VALUES (NULL, \"second\")");
std::cout << "INSERT INTO test VALUES (NULL, \"second\")\", returned " << nb << std::endl;
// update the second row（将id为2的行的value值更新为second-updated）
nb = db.exec("UPDATE test SET value=\"second-updated\" WHERE id='2'");
std::cout << "UPDATE test SET value=\"second-updated\" WHERE id='2', returned " << nb << std::endl;
// Check the results : expect two row of result（读取结果，应该会有两行数据。其实就是查表）
SQLite::Statement query(db, "SELECT * FROM test");
std::cout << "SELECT * FROM test :\n";
while (query.executeStep())
{
std::cout << "row (" << query.getColumn(0) << ", \"" << query.getColumn(1) << "\")\n";
}
db.exec("DROP TABLE test"); // 删除test这个表
}
catch (std::exception& e)
{ // 异常处理
std::cout << "SQLite exception: " << e.what() << std::endl;
return EXIT_FAILURE; // unexpected error : exit the example program
}
// remove("test.db3"); // 删除文件
return 0;
}
上面的几个操作总结就是：
1、声明一个db文件，以什么形式打开SQLite::Database db("test.db3", SQLite::OPEN_READWRITE|SQLite::OPEN_CREATE);
2、通过exec方法执行各种SQL语句，db.exec(const char* );
3、定义查询语句，并获取结果SQLite::Statement query(db, "SELECT * FROM test");
4、异常处理
根据上面的注释内容可以知道这个main.cpp做了哪些事情
编写CMake工程
编写CMake工程也比较简单，SQLiteC++是通过CMake管理的，所以添加为子项目即可
cmake_minimum_required(VERSION 3.0 FATAL_ERROR)
project(useSQLiteCpp LANGUAGES CXX)
add_executable(useSQLiteCpp main.cpp)
# SQLiteCpp
include_directories(SQLiteCpp/include)
option(SQLITECPP_RUN_CPPLINT "Not Run cpplint.py tool for Google C++ StyleGuide." OFF)
add_subdirectory(SQLiteCpp lib)
target_link_libraries(useSQLiteCpp SQLiteCpp)
编译就不描述了
总结
数据库在业务开发中是非常常见的，而SQLite3被广泛应用在各个领域。并且由于它的小型无服务器结构并且依靠文件存储，也被广泛应用在各种嵌入式系统中
所以说，了解SQLite3几乎是从事嵌入式业务开发的必选项
本文从SQLite3的库的获取、工程管理、SQL语句介绍、C语言编程四个角度阐述了SQLite3数据库的实际应用。希望对你的数据收集、数据管理有一定的启蒙作用。
but, not yet
相信这对你只是一个开始，当前时代是信息的时代，我们需要的数据越来越庞大，大数据在生活的各个角落起着越来越重要的作用。
在各种高并发、大流量的场景下，SQLite3还是不够用。我们不能停下脚步，我们的目标是星辰大海。