7. 在Debian使用SQLite¶
平台：野火imx6ull开发板
系统：Debian
SQLite版本：SQLite3
注意：此处的操作是在野火imx6ull开发板上（运行的是Debian系统），而非虚拟机。
7.1. SQLite 简介¶
SQLite是一款轻量级数据库，是一个关系型数据库（RDBMS）管理系统，它包含在一个相对小的C库中，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix/Android/IOS等等主流的操作系统，同时能够跟很多程序语言相结合，比如 C#、PHP、Java等，更重要的是 SQLite 文件格式稳定，跨平台且向后兼容，开发人员保证至少在2050年之前保持这种格式。而且速度极快，目前是在世界上最广泛部署的 SQL 数据库，据官网统计活跃使用的SQLite数据库超过1万亿，更重要的是SQLite 源代码不受版权限制，任何人都可以免费使用于任何目的。
大家可以参考官网的介绍： https://www.sqlite.org/index.html
7.2. 开发板安装SQLite¶
我们要在Debian开发板上安装SQLite，使用的版本是SQLite3。
SQLite 的一个重要的特性是零配置的，这意味着不需要复杂的安装或管理，直接一条命令安装即可。
为了确保我们的版本是最新版本，让我们使用apt命令更新本地apt包索引和升级系统：
sudo apt-get update
sudo apt-get -y upgrade
-y 标志将确认我们同意所有要安装的项目。
sudo apt-get -y install sqlite3
因为SQLite非常小，很快就安装完了，可以看到安装完成的最后输出了SQLite的版本信息，所以安装的版本是 3.27.2-3 ：
···
Preparing to unpack .../sqlite3_3.27.2-3_armhf.deb ...
Unpacking sqlite3 (3.27.2-3) ...
Setting up sqlite3 (3.27.2-3) ...
7.3. 交叉编译SQLite¶
上面在开发板上apt安装了的sqlite的环境。 但是很多时候操作sqlite的程序是在PC机上写的所以，我们还需要搭建一个sqlite3的开发环境，也就是需要交叉编译sqlite。
野火直接提供了一键安装脚本，build-sqlite3.sh。 执行脚本之前，请先确认是否安装 arm-gcc 交叉编译工具链
#!/bin/sh
HOST=arm-linux-gnueabihf
SCRIPT_PATH=$(pwd)
#修改源码包解压后的名称
MAJOR_NAME=sqlite-autoconf
#修改需要下载的源码前缀和后缀
OPENSRC_VER_PREFIX=335
OPENSRC_VER_SUFFIX=0500
PACKAGE_NAME=${MAJOR_NAME}-${OPENSRC_VER_PREFIX}${OPENSRC_VER_SUFFIX}
echo $PACKAGE_NAME
#定义压缩包名称
COMPRESS_PACKAGE=${PACKAGE_NAME}.tar.gz
#定义编译后安装--生成的文件,文件夹位置路径
INSTALL_PATH=/opt/${MAJOR_NAME}
#添加交叉编译工具链路径
# CROSS_CHAIN_PREFIX=/opt/arm-gcc/bin/arm-linux-gnueabihf
CROSS_CHAIN_PREFIX=/opt/gcc-arm-linux-gnueabihf-8.3.0/bin/arm-linux-gnueabihf
#无需修改--下载地址
#https://www.sqlite.org/2021/sqlite-autoconf-3350500.tar.gz
#https://www.sqlite.org/2021/sqlite-autoconf-3350500.tar.bz2
DOWNLOAD_LINK=https://www.sqlite.org/2021/${COMPRESS_PACKAGE}
echo ${DOWNLOAD_LINK}
#下载源码包
do_download_src () {
echo "\033[1;33mstart download ${PACKAGE_NAME}...\033[0m"
if [ ! -f "${COMPRESS_PACKAGE}" ];then
if [ ! -d "${PACKAGE_NAME}" ];then
wget -c ${DOWNLOAD_LINK}
fi
fi
echo "\033[1;33mdone...\033[0m"
}
#解压源码包
do_tar_package () {
echo "\033[1;33mstart unpacking the ${PACKAGE_NAME} package ...\033[0m"
if [ ! -d "${PACKAGE_NAME}" ];then
tar -xf ${COMPRESS_PACKAGE}
fi
echo "\033[1;33mdone...\033[0m"
cd ${PACKAGE_NAME}
}
#配置选项
do_configure () {
echo "\033[1;33mstart configure ${PACKAGE_NAME}...\033[0m"
mkdir -p ${INSTALL_PATH}/config
mkdir -p ${INSTALL_PATH}/plugin
export CC=${CROSS_CHAIN_PREFIX}-gcc
./configure \
--prefix=${INSTALL_PATH} \
--host=${HOST}
echo "\033[1;33mdone...\033[0m"
}
#编译并且安装
do_make_install () {
echo "\033[1;33mstart make and install ${PACKAGE_NAME} ...\033[0m"
make && make install
echo "\033[1;33mdone...\033[0m"
}
#删除下载的文件
do_delete_file () {
cd ${SCRIPT_PATH}
if [ -f "${PACKAGE_NAME}" ];then
sudo rm -f ${PACKAGE_NAME}
fi
}
do_download_src
do_tar_package
do_configure
do_make_install
# do_delete_file
exit $?
整个脚本的核心就是使用wget命令将sqlite源码下载到本地，脚本中使用的源码版本为sqlite-autoconf-3350500.tar.gz。 最新源码版本 参考 ,修改脚本中的*OPENSRC_VER_PREFIX*和*OPENSRC_VER_SUFFIX* 就可以更改源码版本。
源码下载成功之后通过tar解压到指定目录，脚本自动在该目录中执行 ./config 和 make install， 最终将sqlite安装到目录（/opt/${PACKAGE_NAME}， 实际上就是/opt/sqlite-autoconf目录下）。
脚本成功执行之后，我们在/opt/sqlite-autoconf下就能找到对应的lib和头文件。
环境搭建成功，接着我们来编写测试程序验证sqlite开发环境。
新建文件 sqlite.c ，加入下面的代码。
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
int main(int argc,char *argv[])
{
sqlite3 *db;
char *ErrMsg = 0;
int rc;
rc = sqlite3_open("./test.db",&db);
if(rc){
fprintf(stderr, "无法打开数据库:%s\n", sqlite3_errmsg(db));
exit(0);
}
else
{
fprintf(stderr, "成功打开数据库!\n");
}
sqlite3_close(db);
}
这段代码仅仅是在打开一个sqlite数据库，如果数据不存在则创建数据库，存在则打开，然后关闭该数据库。程序比较简单主要用于测试sqlite环境。
编译程序如下：
arm-linux-gnueabihf-gcc sqlite_test.c -o sqlite_test -I /opt/sqlite-autoconf/include -L /opt/sqlite-autoconf/lib -lsqlite3
编译的时候编译工具会去找头文件和链接sqlite的库，最终生成程序sqlite_test，将该程序拷贝到开发板就能运行，运行的时候会在同级目录下创建一个test.db数据库。
7.4. SQLite命令¶
我们简单讲解 SQLite 编程所使用的简单却有用的命令，这些命令被称为 SQLite 的点命令。
在终端运行 sqlite3 命令，可以发现进入了 SQLite
命令终端，并且显示了SQLite版本相关的信息
在 SQLite 命令提示符下，我们可以使用各种 SQLite
的点命令，比如我们输入一个 .help 命令，这是一个帮助命令，可以帮助开发者查看
SQLite
支持所有的点命令，注意，这个help命令前有一个 点 的，这也是为什么这些命令被称之为点命令的原因。
当输入 .help 命令后，可以看到非常多的点命令：
➜ ~ sqlite3
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILE name" to reopen on a persistent database.
sqlite> .help
.archive ... Manage SQL archives
.auth ON|OFF Show authorizer callbacks
.backup ?DB? FILE Backup DB (default "main") to FILE
.bail on|off Stop after hitting an error. Default OFF
.binary on|off Turn binary output on or off. Default OFF
.cd DIRECTORY Change the working directory to DIRECTORY
.changes on|off Show number of rows changed by SQL
.check GLOB Fail if output since .testcase does not match
.clone NEWDB Clone data into NEWDB from the existing database
.databases List names and files of attached databases
.dbconfig ?op? ?val? List or change sqlite3_db_config() options
.dbinfo ?DB? Show status information about the database
.dump ?TABLE? ... Render all database content as SQL
.echo on|off Turn command echo on or off
···
···
其实这里的命令非常多，我只截取了一部分，以下是部分点命令的功能介绍，其实没必要全部记住，在需要的时候通过 .help 命令查询一下即可：
命令 |
功能 |
|---|---|
.archive … |
管理SQL存档 |
.auth ON | OFF |
显示授权者回调 |
.backup ?DB? FILE |
备份 DB 数据库（默认是 “main”）到 FILE 文件 |
.restore ?DB? FILE |
从FILE文件恢复DB的内容（默认为“ main”） |
.databases |
列出数据库的名称及其所依附的文件 |
.dump ?TABLE? |
以 SQL 文本格式转储数据库。如果指定了 TABLE 表，则只转储匹配 LIKE 模式的 TABLE 表。 |
.echo ON|OFF |
开启或关闭 echo 命令。 |
.import FILE TABLE |
导入来自 FILE 文件的数据到 TABLE 表中。 |
.indices ?TABLE? |
显示所有索引的名称。如果指定了 TABLE 表，则只显示匹配 LIKE 模式的 TABLE 表的索引。 |
.log FILE|off |
开启或关闭日志。FILE 文件可以是 stderr（标准错误）/stdout（标准输出）。 |
.mode MODE ?TABLE? |
设置输出模式，可选泽的模式：csv 、column 、html、insert、line 、list、list 、tabs 、tcl |
.open ?OPTIONS? ?FILE? |
关闭现有的数据库并重新打开文件 |
.output FILE name |
发送输出到 FILE name 文件。 |
.output stdout |
发送输出到屏幕。 |
.print STRING… |
逐字地输出 STRING 字符串。 |
.quit |
退出 SQLite 提示符终端。 |
.read FILE name |
从FILE读取输入 |
.schema ?PATTERN? |
显示与PATTERN匹配的CREATE语句 |
.show |
显示各种设置的当前值 |
.stats ON|OFF |
开启或关闭统计。 |
.tables ?TABLE? |
列出与LIKE模式TABLE匹配的表的名称 |
7.5. 创建数据库¶
首先我们创建一个数据库，SQLite 的 sqlite3 命令被用来创建新的 SQLite 数据库，我们不需要任何特殊的权限即可创建一个数据，如果路径下存在相同名字的数据库则直接打开该数据库而不是重新创建。
创建test.db数据库
➜ ~ sqlite3 test.db
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite>
7.6. 创建表¶
SQLite 的 CREATE TABLE
语句用于在任何给定的数据库创建一个新表。创建基本表，涉及到命名表、定义列及每一列的数据类型。
CREATE TABLE 语句的基本语法如下：
CREATE TABLE database_name.table_name(
column1 datatype PRIMARY KEY(one or more columns),
column2 datatype,
column3 datatype,
.....
columnN datatype,
);
database_name.table_name在数据库中是唯一的。column1, column2, ... columnN：表示的是数据库中的列。datatype：每列的数据类型，可选INT、TEXT、CHAR、REAL等类型。PRIMARY：表示第1列作为主键。KEY：关键字，一个或者多个。
比如下面的语句就创建一个班级，班级中有学生的id，名字、年龄、总分数等列，id
作为主键， NOT NULL 的约束表示在表中创建纪录时这些字段不能为
NULL，此处使用小写字母表示用户可以修改的内容。
CREATE TABLE class(
id INT PRIMARY KEY NOT NULL,
name TEXT NOT NULL,
age INT NOT NULL,
score INT
);
这样子创建的数据库就类似一个Excel表格，差不多是这样子的类型：
id name age score
---------- ---------- ---------- ----------
在创建完成后，可以使用 SQLIte 命令中的 .tables
命令来验证表是否已成功创建，该命令用于列出附加数据库中的所有表。
sqlite> .tables
class
7.7. 插入数据¶
SQLite 的 INSERT INTO 语句用于向数据库的某个表中添加新的数据行。
INSERT INTO 语句有两种基本语法。
为表中部分列添加对应的值，可以通过以下方法：
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);
TABLE_NAME：表示表的名字，是数据库中唯一的。column1, column2, ... columnN：表示要插入数据的表中的列的 名称 ，注意这里是列的名称。value1, value2, value3, ... valueN：表示要插入数据的表中的列的值。
如果要为表中的所有列添加值，我们也可以不需要在 SQLite 查询中指定列名称，但要确保值的顺序与列在表中的顺序一致，SQLite 的 INSERT INTO 语法如下：
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
TABLE_NAME：表示表的名字，是数据库中唯一的。
value1, value2, value3,…valueN：表示要插入数据的表中的列的值。
注意，插入值的类型要与表中列指定的类型要匹配的。
我们来给class表添加对应的值：
语法1格式：
INSERT INTO class (id, name, age, score)
VALUES (1, 'liuyi', 22, 610);
INSERT INTO class (id, name, age, score)
VALUES (2, 'chener', 19, 621);
INSERT INTO class (id, name, age, score)
VALUES (3, 'zhangsan', 23, 601);
INSERT INTO class (id, name, age, score)
VALUES (4, 'lisi', 21, 666);
INSERT INTO class (id, name, age, score)
VALUES (5, 'wangwu', 20, 629);
INSERT INTO class (id, name, age, score)
VALUES (6, 'zhaoliu', 22, 621);
INSERT INTO class (id, name, age, score)
VALUES (7, 'sunqi', 20, 611);
INSERT INTO class (id, name, age, score)
VALUES (8, 'zhouba', 22, 591);
INSERT INTO class (id, name, age, score)
VALUES (9, 'wujiu', 23, 625);
INSERT INTO class (id, name, age, score)
VALUES (10, 'zhengshi', 21, 621);
语法2格式：
INSERT INTO class VALUES (1, 'liuyi', 22, 610);
INSERT INTO class VALUES (2, 'chener', 19, 621);
INSERT INTO class VALUES (3, 'zhangsan', 23, 601);
INSERT INTO class VALUES (4, 'lisi', 21, 666);
INSERT INTO class VALUES (5, 'wangwu', 20, 629);
INSERT INTO class VALUES (6, 'zhaoliu', 22, 621);
INSERT INTO class VALUES (7, 'sunqi', 20, 611);
INSERT INTO class VALUES (8, 'zhouba', 22, 591);
INSERT INTO class VALUES (9, 'wujiu', 23, 625);
INSERT INTO class VALUES (10, 'zhengshi', 21, 621);
这两个语法得出的结果是一样的，随便选择一个即可。
7.8. 查找数据¶
SQLite 的 SELECT 语句用于从 SQLite
数据库表中获取数据，并且以结果表的形式返回数据。
SQLite 的 SELECT 语句的基本语法如下：
SELECT column1, column2, ... columnN FROM table_name;
column1, column2, ... columnN：表示要查找数据的表中的列的 名称 ，注意这里是列的名称，当然可以使用*符号表示要查找所有的列。table_name：表示要查找数据库中的表名称，它在数据库中是唯一的。
查找刚刚创建的class表操作如下：
sqlite> SELECT * FROM class;
1|liuyi|22|610
2|chener|19|621
3|zhangsan|23|601
4|lisi|21|666
5|wangwu|20|629
6|zhaoliu|22|621
7|sunqi|20|611
8|zhouba|22|591
9|wujiu|23|625
10|zhengshi|21|621
你会发现这些表的格式是很乱，不直观，那么你可以通过 .header 命令显示表头，通过 .mode 设置显示的模式：
.header on
.mode column
然后再次查找所有的内容：
sqlite> SELECT * FROM class;
id name age score
---------- ---------- ---------- ----------
1 liuyi 22 610
2 chener 19 621
3 zhangsan 23 601
4 lisi 21 666
5 wangwu 20 629
6 zhaoliu 22 621
7 sunqi 20 611
8 zhouba 22 591
9 wujiu 23 625
10 zhengshi 21 621
这一次就好看多了，当然你还可以进行排序。
7.9. 数据排序¶
SQLite 的 ORDER BY
语句是用来基于一个或多个列按升序或降序顺序排列数据。
ORDER BY 语句的基本语法如下：
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
比如按照某一列进行排序，此处选择 score 列，ASC表示升序，DESC表示降序：
sqlite> SELECT * FROM class ORDER BY score ASC;
id name age score
---------- ---------- ---------- ----------
8 zhouba 22 591
3 zhangsan 23 601
1 liuyi 22 610
7 sunqi 20 611
2 chener 19 621
6 zhaoliu 22 621
10 zhengshi 21 621
9 wujiu 23 625
5 wangwu 20 629
4 lisi 21 666
sqlite> SELECT * FROM class ORDER BY score DESC;
id name age score
---------- ---------- ---------- ----------
4 lisi 21 666
5 wangwu 20 629
9 wujiu 23 625
2 chener 19 621
6 zhaoliu 22 621
10 zhengshi 21 621
7 sunqi 20 611
1 liuyi 22 610
3 zhangsan 23 601
8 zhouba 22 591
7.10. 导出数据库为SQL脚本¶
我们可以通过 .dump 命令导出数据库为一个sql脚本，也可以通过这个脚本还原一个数据库，具体操作如下：
在shell终端中（注意不是在sqlite提示符终端）通过以下命令即可导出数据库为sql脚本：
sqlite3 test.db .dump > test.sql
# 当前路径下存在test.db数据库
➜ ~ ls
bin mountnfs qt-app test.db
# 导出数据库为sql脚本
➜ ~ sqlite3 test.db .dump > test.sql
# 生成test.sql脚本
➜ ~ ls
bin mountnfs qt-app test.db test.sql
# 查看脚本的内容
➜ ~ cat test.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE class(
id INT PRIMARY KEY NOT NULL,
name TEXT NOT NULL,
age INT NOT NULL,
score INT
);
INSERT INTO class VALUES(1,'liuyi',22,610);
INSERT INTO class VALUES(2,'chener',19,621);
INSERT INTO class VALUES(3,'zhangsan',23,601);
INSERT INTO class VALUES(4,'lisi',21,666);
INSERT INTO class VALUES(5,'wangwu',20,629);
INSERT INTO class VALUES(6,'zhaoliu',22,621);
INSERT INTO class VALUES(7,'sunqi',20,611);
INSERT INTO class VALUES(8,'zhouba',22,591);
INSERT INTO class VALUES(9,'wujiu',23,625);
INSERT INTO class VALUES(10,'zhengshi',21,621);
COMMIT;
7.11. 从SQL脚本导入数据库¶
这一步的操作与上一步操作是相反的，可以通过以下命令导入SQL脚本生成一个新的数据库：
sqlite3 new.db < test.sql
注意，这里的new.db 是通过test.sql生成的，它里面的内容与test.db是完全一致的。
# 从SQL脚本导入数据库
➜ ~ sqlite3 new.db < test.sql
# 生成新的数据库new.db
➜ ~ ls
bin mountnfs new.db qt-app test.db test.sql
# 进入新的数据库中并查询数据表
➜ ~ sqlite3 new.db
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> .header on
sqlite> .mode column
sqlite> SELECT * FROM class;
id name age score
---------- ---------- ---------- ----------
1 liuyi 22 610
2 chener 19 621
3 zhangsan 23 601
4 lisi 21 666
5 wangwu 20 629
6 zhaoliu 22 621
7 sunqi 20 611
8 zhouba 22 591
9 wujiu 23 625
10 zhengshi 21 621
至此，数据库的讲解完成，更多知识大家可以自行学习，此处仅告诉大家野火开发板上可以使用数据库。