SQLite 快速入门

¶什么是 SQLite？

SQLite 是一个进程内的库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎
它是一个零配置的数据库，这意味着与其他数据库不一样，不需要在系统中配置
就像其他数据库，SQLite 引擎不是一个独立的进程，可以按应用程序需求进行静态或动态连接
SQLite 直接访问其存储文件

¶在 Windows 上安装 SQLite

访问 SQLite 下载页面
在 windows 区下载 sqlite-tools-win32-*.zip 和 sqlite-dll-win32-*.zip 压缩文件
创建文件夹，解压压缩文件
有 sqlite3.def sqlite3.dll sqldiff.exe sqlite3.exe sqlite3_analyzer.exe
放在同一级目录下，添加目录到 PATH 环境变量
在 cmd 中使用 sqlite3 命令，检查是否安装成功

¶SQLite 简易笔记

¶SQLite 点命令

SQLite 的点命令，它们不以分号 ; 结束

.exit 退出 SQLite 提示符
.quit 退出 SQLite 提示符
.show 显示各种设置的当前值
.help 显示消息
.mode MODE 设置输出模式，例 .mode column
…

¶SQLite 语法

SQL 注释以两个连续的 “-” 字符（ASCII 0x2d）开始
并扩展至下一个换行符（ASCII 0x0a）或直到输入结束，以先到者为准
也可以使用 C 风格的注释，以 “/*” 开始，并扩展至下一个 “*/” 字符对或直到输入结束，以先到者为准
SQLite的注释可以跨越多行

SQLite 不区分大小写，但有命令大小写敏感，如 GLOB 与 glob

所有的 SQLite 语句可以以任何关键字开始，如 SELECT、INSERT、UPDATE、DELETE、ALTER、DROP 等，所有的语句以分号 ; 结束。

¶SQLite 数据类型

每个存储在 SQLite 数据库中的值都具有以下存储类之一

存储类	描述
NULL	值是一个 NULL 值
INTEGER	值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中
REAL	值是一个浮点值，存储为 8 字节的 IEEE 浮点数字
TEXT	值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储
BLOB	值是一个 blob 数据，完全根据它的输入存储

¶SQLite 常用语句

SQLite 的 CREATE TABLE 语句用于在任何给定的数据库创建一个新表。创建基本表，涉及到命名表、定义列及每一列的数据类型

SQLite 的 INSERT INTO 语句用于向数据库的某个表中添加新的数据行

SQLite 的 SELECT 语句用于从 SQLite 数据库表中获取数据，以结果表的形式返回数据。这些结果表也被称为结果集

SQLite SELECT 语句

SELECT column1, column2…columnN
FROM table_name;

用 SELECT 语句获取表 table_name 中指定的字段 column1, column2…columnN

SQLite的 WHERE 子句用于指定从一个表或多个表中获取数据的条件。

SQLite WHERE 子句

SELECT column1, column2…columnN
FROM table_name
WHERE [CONDITION];

• C++ 数据库查询范式
  • sqlite3_open
  • sqlite3_prepare_v2
  • sqlite3_errmsg
  • sqlite3_bind_text
  • sqlite3_step
  • sqlite3_column_text

sqlite3* db;
sqlite3_stmt* stmt;
int comp = sqlite3_open(" ", &db);

if (comp) {
    std::cerr << "Can't open database: " << sqlite3_errmsg(db) << std::endl;
    return;
} else {
    std::cout << "Opened database successfully" << std::endl;
}

// initialize
std::string bsid = " ";

std::string sql = "SELECT g, c, sat1, sat2, saliency1, saliency2 FROM comp_sat WHERE bsid = ?";

comp = sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, 0);
if (comp != SQLITE_OK) {
    std::cerr << "SQL error: " << sqlite3_errmsg(db) << std::endl;
    return;
}

// 绑定 sql 中的 ? 到 bsid 字符串
sqlite3_bind_text(stmt, 1, bsid.c_str(), -1, SQLITE_STATIC);

while (sqlite3_step(stmt) == SQLITE_ROW) {

    std::string g = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
    std::string c = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
    std::string sat_1 = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
    std::string sat_2 = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 3));
    std::string saliency_1 = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 4));
    std::string saliency_2 = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 5));

    // do something
}

可以使用比较或逻辑运算符指定条件，比如 >、<、=、LIKE、NOT

¶附录：SQLite 库接口 - Tcl

SQLite 库被设计为非常容易从 Tcl 或 Tcl/Tk 脚本中使用，SQLite 最初就是作为 Tcl 的扩展，并且 SQLite 的主要测试套件是用 TCL 编写的，SQLite 可以与任何编程语言一起使用，但它与 TCL 的连接非常紧密

下载 sqlite 后，将解压后的文件 sqlite3.dll 和 sqlite3.def 放到 Tcl 的 lib 目录下

# 引入 sqlite3 库
package require sqlite3

Tcl 中，在与 SQLite 数据库交互时，通常有以下操作：

• 创建、管理和操作 SQLite 数据库连接
• 执行查询、插入、更新和删除等操作
• 通过参数化查询来防止 SQL 注入攻击和更安全地处理数据
• 处理查询结果，将查询结果存储在变量中，然后对其进行迭代和处理
• 事务管理，使用 BEGIN、COMMIT 和 ROLLBACK 等命令来管理事务，确保数据的一致性和完整性
• 错误处理，处理数据库操作可能出现的错误，以确保代码的健壮性和可靠性

¶sqlite3

与 SQLite 库的接口由一个名为 sqlite3 的单个 tcl 命令组成。由于只有这一个命令，所以接口没有放置在单独的命名空间中。

sqlite3 命令主要用以下方式来打开或创建数据库

sqlite3  dbcmd  ?database-name?  ?options?

关于 sqlite3 命令的选项的介绍先略过，需要时返回来再写

一旦打开了 SQLite 数据库，就可以使用 dbcmd 中的方法来进行控制。目前定义了 40 种方法

authorizer backup bind_fallback busy cache changes close collate collation_needed commit_hook complete config copy deserialize

enable_load_extension errorcode eval exists function incrblob interrupt last_insert_rowid nullvalue onecolumn preupdate profile progress restore

rollback_hook serialize status timeout total_changes trace trace_v2 transaction unlock_notify update_hook version wal_hook

¶eval

最有用的 dbcmd 方法是 eval，eval 方法用于在数据库上执行 SQL 语句。eval 方法的语法如下：

dbcmd  eval  ?-withoutnulls?  sql   ?array-name?  ?script?

eval 方法的作用是执行第二个参数中给出的 SQL 语句或语句。例如，要在数据库中创建一个新表，可以执行以下操作：

# 在 Tcl 脚本中创建一个名为 "db1" 的 SQLite 数据库连接，连接到位于当前工作目录下的 "testdb" 数据库文件
sqlite3 db1 ./testdb
# 创建一个名为 t1 的新表，其中包含列 a 和 b
db1 eval {CREATE TABLE t1(a int, b text)}

上述代码创建了一个名为 t1 的新表，其中包含列 a 和 b。还有什么比这更简单的呢？

查询结果以列值的列表形式返回。如果一个查询请求 2 列，并且有 3 行匹配查询，那么返回的列表将包含 6 个元素。例如：

# 使用了 db1 数据库连接对象执行了一个 SQL INSERT 语句，将一行行数据插入到名为 "t1" 的表格中
db1 eval {INSERT INTO t1 VALUES(1,'hello')}
db1 eval {INSERT INTO t1 VALUES(2,'goodbye')}
db1 eval {INSERT INTO t1 VALUES(3,'howdy!')}

# 执行了一个 SELECT 查询，从 "t1" 表格中选取所有的行，并按照列 "a" 进行升序排序
set x [db1 eval {SELECT * FROM t1 ORDER BY a}]
# x 是一个包含多个子列表的列表，每个子列表包含两个元素，分别对应于查询结果的两个列的值
# {{1 hello} {2 goodbye} {3 howdy!}}

上面的代码会将变量 $x 设置为：1 hello 2 goodbye 3 howdy!

您还可以通过在 SQL 代码之后指定数组变量的名称和脚本，逐行处理查询结果。对于查询结果的每一行，所有列的值将被插入到数组变量中，并执行脚本。例如：

# 执行一个数据库查询，将查询结果存储在名为 values 的数组中，然后打印数组的内容，并在每次打印时插入一个空行
# 后续的大括号 {} 代码块将接收来自前一个查询的结果作为参数
db1 eval {SELECT * FROM t1 ORDER BY a} values {
    # parray 是一个 Tcl 命令，用于打印一个数组的所有元素    
    parray values
    # 在输出中插入一个空行
    puts ""
}

上面的代码将输出：

values(*) = a b
values(a) = 1
values(b) = hello

values(*) = a b
values(a) = 2
values(b) = goodbye

values(*) = a b
values(a) = 3
values(b) = howdy!

对于结果的每一行中的每一列，列的名称用作数组中的索引，并且列的值存储在相应的数组条目中。（注意：如果查询结果集中的两个或更多列具有相同的名称，则具有该名称的最后一列将覆盖先前的值，并且具有相同名称的较早列将无法访问。）特殊的数组索引 * 用于按它们的出现顺序存储列名的列表。

通常，使用 nullvalue 设置将 NULL SQL 结果存储在数组中。但是，如果使用 -withoutnulls 选项，则 NULL SQL 值将导致相应的数组元素被取消设置。

如果省略数组变量名称或为空字符串，则每列的值将存储在与列本身相同名称的变量中。例如：

# dbcmd  eval  ?-withoutnulls?  sql   ?array-name?  ?script?
# 即 array-name 省略或为空
db1 eval {SELECT * FROM t1 ORDER BY a} {
    puts "a=$a b=$b"
}

从中我们得到以下输出：

a=1 b=hello
a=2 b=goodbye
a=3 b=howdy!

Tcl 变量名称可以出现在第二个参数的 SQL 语句中的任何位置，只要在其中放置字符串或数字字面值是合法的位置。变量的值将替换为变量名称。如果变量不存在，则使用 NULL 值。例如：

# 这样就可以在 SQL 语句中含有 Tcl 的变量
db1 eval {INSERT INTO t1 VALUES(5,$bigstring)}

请注意，不必对 $bigstring 的值加引号，这会自动处理。如果 $bigstring 是一个大的字符串或二进制对象，则这种技术不仅更容易编写，而且更有效率，因为它避免了对 $bigstring 的内容进行复制。

如果 $bigstring 变量同时具有字符串和 “bytearray” 表示，则 TCL 会将值作为字符串插入。如果它只有一个 “bytearray” 表示，则将该值作为 BLOB 插入。即使值也具有文本表示，也可以使用 “@” 字符来将值强制插入为 BLOB。像这样：

db1 eval {INSERT INTO t1 VALUES(5,@bigstring)}

如果变量没有 bytearray 表示，则 “@” 在所有情况下都与 “$” 一样。请注意，在所有情况下，":" 像 “$” 一样工作，因此以下是表达相同语句的另一种方式：

db1 eval {INSERT INTO t1 VALUES(5,:bigstring)}

在 SQL 文本被双引号 “…” 而不是大括号 {…} 包围时，变量名之前使用 “:” 而不是 “$” 有时可能是有用的。当 SQL 包含在双引号 “…” 内时，TCL 会对 $ 变量进行替换，如果不小心使用，可能会导致 SQL 注入。但是，无论是使用双引号 “…” 还是大括号 {…} 将 SQL 包围起来，TCL 都不会替换 :-变量，因此使用 :-变量增加了一层防范 SQL 注入的措施。

¶close

正如其名称所示，close 方法用于关闭 SQLite 数据库，这会导致（副作用）删除 dbcmd Tcl 命令。以下是打开然后立即关闭数据库的示例：

sqlite3 db1 ./testdb
db1 close

如果直接删除 dbcmd，这与调用 close 方法具有相同的效果。因此，以下代码等同于前面的代码：

sqlite3 db1 ./testdb
# 在 Tcl 中，rename 命令用于重命名一个过程（procedure）或一个命名空间（namespace）
# 下面是删除的用法
rename db1 {}

¶transaction

transaction 方法用于在 SQLite 数据库事务中执行 TCL 脚本

¶cache

如上所述的 eval 方法会为最近评估的 SQL 命令保留一个预编译语句的缓存，而 cache 方法用于控制这个缓存

¶complete

complete 方法接受一个字符串作为其唯一参数。如果字符串是完整的 SQL 语句，则返回 TRUE，如果还有更多内容需要输入，则返回 FALSE，该方法对于创建具有SQL查询功能的交互式应用程序非常有用

¶config

config 方法用于查询或更改数据库连接的某些配置设置，使用 sqlite3_db_config()（C 语言）接口。如果不带参数运行此方法，将获取可用配置设置及其当前值的 TCL 列表：

dbcmd config

上述命令会返回类似以下内容的结果：

defensive 0 dqs_ddl 1 dqs_dml 1 enable_fkey 0 enable_qpsg 0 enable_trigger 1 enable_view 1 fts3_tokenizer 1 legacy_alter_table 0 legacy_file_format 0 load_extension 0 no_ckpt_on_close 0 reset_database 0 trigger_eqp 0 trusted_schema 1 writable_schema 0

要查询特定配置设置的当前值，请将配置设置的名称添加到命令中。还可以选择添加布尔值以更改设置。

以下是四个建议的配置更改，用于最大限度地提高应用程序的安全性。将 trust_schema 设置关闭可以防止在触发器、视图、CHECK 约束、生成的列和表达式索引中使用虚拟表和不安全的 SQL 函数。关闭 dqs_dml 和 dqs_ddl 设置可以防止使用双引号字符串。打开 defensive 可以防止对阴影表进行直接写入。

db config trusted_schema 0
db config defensive 1
db config dqs_dml 0
db config dqs_ddl 0

¶copy

copy 方法用于从文件复制数据到表中。它返回成功处理的文件中的行数。“copy” 方法的语法如下：

dbcmd copy conflict-algorithm table-name file-name ?column-separator? ?null-indicator?

conflict-algorithm 必须是 INSERT 语句的 SQLite 冲突算法之一：rollback（回滚）、abort（中止）、fail（失败）、ignore（忽略）或 replace（替换）。请参阅 SQLite 语言部分的 ON CONFLICT 获取更多信息。冲突算法必须以小写字母指定

table-name 作为表必须已经存在。file-name 必须存在，并且文件中的每一行必须包含与表中定义的列数相同的列。如果文件中的某一行包含的列数多于或少于表中定义的列数，则 copy 方法会回滚任何插入并返回错误。

column-separator 是可选的列分隔符字符串。默认为ASCII制表符字符 \t。

null-indicator 是可选的字符串，表示列值为null。默认为空字符串。请注意，column-separator 和 null-indicator 是可选的位置参数；如果指定了 null-indicator，则必须指定一个 column-separator 参数，并在 null-indicator 参数之前指定它

copy 方法实现了类似于 SQLite shell 命令中的 .import 的功能。这是一个方便的方法，可以将文件中的数据快速加载到数据库表中，而无需手动编写 INSERT 语句

¶timeout

timeout 方法用于控制 SQLite 库在放弃数据库事务之前等待锁释放的时间，其默认超时时间为 0 毫秒（即默认情况下根本不等待）

¶busy

busy 方法，类似于 timeout 方法，仅在数据库被锁定时才起作用，但是 busy 方法给程序员提供了更多控制的选项

¶enable_load_extension

SQLite 的扩展加载机制（通过使用 load_extension() SQL 函数访问）默认是关闭的。这是一项安全预防措施，如果应用程序想要使用load_extension()函数，必须首先使用这个方法来启用该功能

¶exists

exists 方法类似于 onecolumn 和 eval，因为它执行 SQL 语句。不同之处在于，exists 方法始终返回一个布尔值，如果 SQL 语句返回一个或多个行，则返回 TRUE，如果 SQL 返回一个空集，则返回FALSE

exists 方法通常用于测试表中是否存在行，例如：

if {[db exists {SELECT 1 FROM table1 WHERE user=$user}]} {
   # 处理如果 $user 存在
} else {
   # 处理如果 $user 不存在
}

¶last_insert_rowid

last_insert_rowid 方法返回一个整数，该整数是最近插入的数据库行的ROWID（行标识符）

¶function

function 方法用于在 SQLite 引擎中注册新的 SQL 函数。参数包括新 SQL 函数的名称和实现该函数的 TCL 命令。函数的参数会在调用 TCL 命令之前追加到命令中

语法如下：

dbcmd function sql-name ?options? script

¶nullvalue

nullvalue 方法用于更改 eval 方法返回的 NULL 的表示方式

¶onecolumn

onecolumn 方法类似于 eval，因为它会评估作为参数传递的 SQL 查询语句。不同之处在于，onecolumn 返回一个单一元素，该元素是查询结果的第一行的第一列

¶changes

changes 方法返回一个整数，该整数表示由最近的 eval 方法插入、删除和/或修改的数据库中的行数，这个值表示最近的 SQL 操作对数据库的影响

¶authorizer

authorizer 方法提供了对 sqlite3_set_authorizer C/C++ 接口的访问

¶bind_fallback

bind_fallback 方法允许应用程序控制在没有与参数名称匹配的 TCL 变量时如何处理参数绑定

¶progress

该方法注册一个回调函数，在查询处理过程中定期调用，progress 回调可用于显示长时间查询的状态或在长时间查询期间处理 GUI 事件

¶collate

这个方法用于注册新的文本排序序列，它有两个参数：排序序列的名称和实现排序序列的比较函数的 TCL 过程的名称

¶collation_needed

这个方法用于注册一个回调例程，当 SQLite 引擎需要特定的排序序列但尚未注册该排序序列时会被调用。回调函数可以注册所需的排序序列。回调函数被调用时会传递一个参数，即所需排序序列的名称

¶commit_hook

这个方法用于注册一个回调例程，在 SQLite 尝试提交对数据库的更改之前调用。如果回调抛出异常或返回非零结果，那么事务将回滚而不是提交

¶status

status 方法从最近评估的 SQL 语句中返回状态信息，该方法基本上是 sqlite3_stmt_status() C 语言接口的包装器。通过使用这个方法，您可以获取关于 SQL 查询性能的信息，以便识别可能需要优化的查询

¶update_hook

这个方法用于注册一个回调例程，在每次通过 UPDATE、INSERT 或 DELETE 语句修改行之后调用，这个功能可用于在每次数据库发生更改时执行自定义逻辑，以便进行额外的处理或记录更改的情况

¶preupdate

这个方法，要么注册一个回调例程，该回调在每次通过 UPDATE、INSERT 或 DELETE 语句修改行之前调用，要么执行与即将发生的更新相关的某些操作

¶wal_hook

这个方法用于注册一个回调例程，当数据库处于WAL（写入日志）模式时，在事务提交后调用

¶incrblob

这个方法打开了一个 TCL 通道，可以用来读取或写入数据库中已经存在的 BLOB

¶errorcode

这个方法返回了最近的SQLite操作导致的数值错误代码

¶trace

trace 方法注册一个回调函数，该函数在每个 SQL 语句被编译时都会被调用。SQL 语句的文本被附加为一个字符串，然后在调用回调函数之前传递给它。这可以用于（例如）记录应用程序执行的所有 SQL 操作的日志。

¶trace_v2

trace_v2 方法注册一个回调函数，该函数在每个 SQL 语句被编译时都会被调用。其语法如下：

dbcmd  trace_v2  ?callback?  ?mask?

这个命令会在特定条件发生时调用 callback 脚本。这些条件由 mask 参数决定，mask 应该是一个 TCL 列表，包含以下零个或多个关键字：

statement
profile
row
close

¶backup

backup 方法用于创建活动数据库的备份副本。命令语法如下：

dbcmd backup ?source-database? backup-filename

¶restore

restore 方法将内容从一个单独的数据库文件复制到当前的数据库连接中，覆盖任何已存在的内容。命令语法如下：

dbcmd restore ?target-database? source-filename

¶serialize

serialize 方法创建一个 BLOB，它是底层数据库的完全副本。命令语法如下：

dbcmd  serialize  ?database?

¶deserialize

deserialize 方法接受一个包含 SQLite 数据库文件的 TCL 字节数组，并将其添加到数据库连接中。命令语法如下：

dbcmd  deserialize  ?database?  value

¶interrupt

interrupt 方法调用 sqlite3_interrupt() 接口，导致任何挂起的查询停止

¶version

返回当前库的版本号，例如 “3.23.0”

¶profile

这个方法用于对应用程序运行的SQL语句的执行进行性能分析。语法如下：

dbcmd  profile  ?script?

¶unlock_notifys

unlock_notify 方法用于访问 SQLite 核心库的 sqlite3_unlock_notify() 接口，用于测试目的。不建议应用程序使用此方法