SQLite简介与安装环境配置分享（附多方案连接测试）

SQLite 是一个内置库，实现了一个自包含、无服务器、零配置的事务性 SQL 数据库引擎。SQLite 的代码属于公共领域，因此可以自由用于任何目的，无论是商业用途还是私人用途。

SQLite基础知识

正如官方文档所说，SQLite的对手是fopen()，SQLite不能与MySQL、Oracle之类的数据库相比，因为它的构造和要解决的任务与这些数据库完全不一样。

SQLite的轻量特性让它在处理嵌入式、本地应用和一些低配场景服务时得心应手。此外，SQLite无需依赖多余文件，产生的数据库文件也只有一个.db或一个.zip压缩包，这让数据共享和同步变得非常简单。将SQLite引擎内置到程序中也毫无压力，由此在大家没有感知的过程中，SQLite已经成为了世界上应用最广泛的数据库，存在于嵌入式设备、移动应用等等场景中。

数据类型

基本数据类型（存储类）

大多数 SQL 数据库引擎（据我们所知，除了 SQLite 之外的每个 SQL 数据库引擎）都使用静态的、严格的类型。使用静态类型时，值的数据类型由其容器（存储值的特定列）决定。SQLite 使用更通用的动态类型系统。
—— sqlite.readdevdocs.com

在SQLite中具有如下数据类型（在这里也叫存储类）：

布尔值与日期时间

SQLite没有为布尔值和日期时间预留单独的数据类型，而采用了更通用的方式对这些值进行表示。

• 布尔值
  • 从版本 3.23.0 (2018-04-02) 开始，SQLite 识别关键字“TRUE”和“FALSE”，但这些关键字实际上只是整数文字 1 和 0 的替代拼写。
• 日期和时间（应用程序可以选择以这些格式中的任何一种存储日期和时间，并使用内置的 日期和时间函数在格式之间自由转换。）
  • TEXT作为 ISO8601 字符串（“YYYY-MM-DD HH:MM:SS.SSS”）。
  • REAL作为 Julian 天数，根据 proleptic Gregorian calendar，从公元前 4714 年 11 月 24 日格林威治中午开始的天数。
  • INTEGER作为 Unix 时间，自 1970-01-01 00:00:00 UTC 以来的秒数。

数据亲和性

我这样理解SQLite中的类型亲和性（Type Affinity）概念，即在建表时所规定的列数据类型只是对数据库引擎的建议，对其存储何类数据不构成强制限制，数据库引擎会根据实际数据选取合适的数据类型。

将SQLite中的五种类型亲和归纳如下：

因为我也是刚刚学习使用SQLite，实话实说文档上关于亲和类型的描述给我绕得够呛，这玩意只能到实践中去理解了，此外我认为其他编程语言中的数据类型兼容机制也有助于理解SQLite的亲和性设计。

SQLite环境配置

环境介绍

• 操作系统：Windows 11 23H2
• 目标环境：SQLite 3.46.0.

操作步骤

1. 前往SQLite官网下载sqlite-dll-win-x64-3460000.zip和sqlite-tools-win-x64-3460000.zip；
2. 创建一个sqlite目录并将两个压缩包解压至该目录下；
3. 将上述目录添加到PATH系统环境变量，并确定；
4. 新开终端，输入sqlite3，输出如下结果，完毕。

Connected to a transient in-memory database:这一输出表示我们在使用sqlite3时没有指定数据库文件，SQLite为我们自动创建了一个临时的数据库。

这个安装过程就体现出了SQLite的绝佳优势：无需配置，作为一个无需配置的轻量级数据库，在Linux下的安装也同样简洁。

SQLite 命令和语句

• 启动SQLite：sqlite3 <database_name.db>

基础命令

.help -- 获取帮助
.exit -- 退出SQLite

.open database_name.db -- 打开或创建数据库
.close -- 关闭当前数据库

.tables -- 显示所有表
.schema table_name -- 查看表结构

.headers on/off -- 开/关列名显示
.dbinfo -- 查看当前数据库大小

SQL语句

SQLite的基本查询操作和一般SQL一致，也有一些独特之处和扩展功能。

• 自增主键

CREATE TABLE table_name (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    column1 TEXT
);

• UPSET语法，用于冲突时更新记录

INSERT INTO users (id, name) VALUES (1, 'Alice')
ON CONFLICT(id) DO UPDATE SET name = excluded.name;

• WITHOUT ROWID创建不带隐藏ID的表
  默认情况下，SQLite中的每个表都有一个隐含的ROWID列，它是一个唯一的整数，用于标识表中的每一行。WITHOUT ROWID允许你创建一个没有ROWID列的表，依赖于用户定义的主键来唯一标识每一行以节省内存空间。

CREATE TABLE example (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
) WITHOUT ROWID;

Navicat SQLite连接测试

Navicat连接也是非常的简单顺利，选择数据库路径即可。

C++ SQLite连接测试

测试环境

• Windows11 23H2 64位
• Visual Studio 2022

编译获取库文件

这篇文章中提到了更丰富的自定义编译方法，有需求请参阅原文：windows平台sqlite3的编译及简单使用 | Ivanzz (ivanzz1001.github.io)

• 在官网下载sqlite-amalgamation-3460000.zip，这是SQLite的源码文件，当中包含sqlite.h头文件；
• 基于预编译好的动态链接库来编译得到sqlite.lib；
  1. 将sqlite-dll-win-x64-3460000.zip解压缩到一个目录下；
  2. 管理员身份启动"Developer Command Prompt for VS 2022"（可以直接在开始菜单中搜索）；
  3. 切换到刚刚解压的目录下并执行下述命令：

我的目录在D盘下，故需要先切换盘符，再切换到对应的解压目录
> D:
> cd D:\Real_Path\sqlite-dll-win-x64-3460000
> lib /def:sqlite3.def /machine:x64

执行完毕后，目录中会出现sqlite.lib和sqlite.exp文件，命令行界面示意如下：

在VS项目中添加对应文件路径

• 创建一个新的C++ CLI项目；
• 右键项目名称后选择 Properties，即项目属性；
• 在 Configuration Properties -> C/C++ -> General 中，找到 Additional Include Directories，添加sqlite3.h头文件所在目录；
• 在 Configuration Properties -> Linker -> General 中，找到 Additional Library Directories，添加sqlite3.lib所在目录。
• 在Configuration Properties -> Linker -> Input 中，找到 找到 Additional Library Directories，添加sqlite3.lib。

编码测试

给出demo如下，生成编译代码并运行：

#include <iostream>
#include "sqlite3.h"

// 回调函数，用于处理查询结果
static int callback(void* NotUsed, int argc, char** argv, char** azColName) {
    for (int i = 0; i < argc; i++) {
        std::cout << azColName[i] << ": " << (argv[i] ? argv[i] : "NULL") << std::endl;
    }
    std::cout << std::endl;
    return 0;
}

int main() {
    sqlite3* db;
    char* zErrMsg = nullptr;
    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* sqlCreateTable = "CREATE TABLE IF NOT EXISTS 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, callback, 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 COMPANY (ID, NAME, AGE, ADDRESS, SALARY) "
        "VALUES (1, 'Paul', 32, 'California', 20000.00); "
        "INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) "
        "VALUES (2, 'Allen', 25, 'Texas', 15000.00); "
        "INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) "
        "VALUES (3, 'Teddy', 23, 'Norway', 20000.00); "
        "INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) "
        "VALUES (4, 'Mark', 25, 'Rich-Mond', 65000.00);";

    rc = sqlite3_exec(db, sqlInsertData, callback, 0, &zErrMsg);
    if (rc != SQLITE_OK) {
        std::cerr << "SQL error: " << zErrMsg << std::endl;
        sqlite3_free(zErrMsg);
    }
    else {
        std::cout << "Records inserted successfully" << std::endl;
    }

    // 查询数据
    const char* sqlSelectData = "SELECT * FROM COMPANY;";
    rc = sqlite3_exec(db, sqlSelectData, callback, 0, &zErrMsg);
    if (rc != SQLITE_OK) {
        std::cerr << "SQL error: " << zErrMsg << std::endl;
        sqlite3_free(zErrMsg);
    }
    else {
        std::cout << "Operation done successfully" << std::endl;
    }

    // 关闭数据库连接
    sqlite3_close(db);
    return 0;
}

成功运行并输出如下结果：

Python SQLite连接测试

sqlite3库内置于Python标准库中。
The module was written by Gerhard Häring. It provides an SQL interface compliant with the DB-API 2.0 specification described by PEP 249, and requires SQLite 3.7.15 or newer.

相对来说，在Python下连接SQLite就要简单的多了。

测试环境

• Python 3.11.0
• Pycharm 2023.1.4

如果您是首次在Pycharm中连接SQLite数据库，那么跟随Pycharm提示简单配置并安装相关驱动即可。

import sqlite3  
  
# 连接到SQLite数据库（如果数据库文件不存在，会自动创建一个）  
conn = sqlite3.connect('example.db')  
  
# 创建一个游标对象，用于执行SQL语句  
cursor = conn.cursor()  
  
# 创建表  
cursor.execute('''  
CREATE TABLE IF NOT EXISTS users (  
id INTEGER PRIMARY KEY,  
name TEXT NOT NULL,  
age INTEGER NOT NULL  
)  
''')  
  
# 插入数据  
cursor.execute('''  
INSERT INTO users (name, age)  
VALUES ('Alice', 30)  
''')  
  
cursor.execute('''  
INSERT INTO users (name, age)  
VALUES ('Bob', 25)  
''')  
  
# 提交事务  
conn.commit()  
  
# 查询数据  
cursor.execute('SELECT * FROM users')  
rows = cursor.fetchall()  
  
# 打印查询结果  
for row in rows:  
print(row)  
  
# 关闭游标和连接  
cursor.close()  
conn.close()

正常连接并输出结果。

参考阅读

• SQLite Home Page
• SQlite中文网
• SQLite 中的数据类型 | SQLite中文网 (readdevdocs.com)
• SQLite 教程 | 菜鸟教程 (runoob.com)
• windows平台sqlite3的编译及简单使用 | Ivanzz (ivanzz1001.github.io)
• sqlite3 --- SQLite 数据库的 DB-API 2.0 接口 — Python 3.12.4 文档