![cover](https://qiniucloud.tsio.top/blogfile/SQLite-qabymhzf.png)
SQLite简介与安装环境配置分享(附多方案连接测试)
SQLite 是一个内置库,实现了一个自包含、无服务器、零配置的事务性 SQL 数据库引擎。SQLite 的代码属于公共领域,因此可以自由用于任何目的,无论是商业用途还是私人用途。
SQLite基础知识
正如官方文档所说,SQLite的对手是fopen()
,SQLite不能与MySQL、Oracle之类的数据库相比,因为它的构造和要解决的任务与这些数据库完全不一样。
SQLite的轻量特性让它在处理嵌入式、本地应用和一些低配场景服务时得心应手。此外,SQLite无需依赖多余文件,产生的数据库文件也只有一个.db
或一个.zip
压缩包,这让数据共享和同步变得非常简单。将SQLite引擎内置到程序中也毫无压力,由此在大家没有感知的过程中,SQLite已经成为了世界上应用最广泛的数据库,存在于嵌入式设备、移动应用等等场景中。
数据类型
基本数据类型(存储类)
大多数 SQL 数据库引擎(据我们所知,除了 SQLite 之外的每个 SQL 数据库引擎)都使用静态的、严格的类型。使用静态类型时,值的数据类型由其容器(存储值的特定列)决定。SQLite 使用更通用的动态类型系统。
—— sqlite.readdevdocs.com
在SQLite中具有如下数据类型(在这里也叫存储类):
数据类型 | 描述 |
---|---|
NULL | 表示一个空值。 |
INTEGER | 带符号的整型,根据值的大小自动选择1、2、3、4、6或8字节进行存储。 |
REAL | 浮点数,存储为8字节的IEEE浮点数。 |
TEXT | 文本字符串,使用数据库编码(UTF-8、UTF-16BE或UTF-16LE)存储。 |
BLOB | 二进制大对象,是一个任意长度的数据块,以输入的格式存储(即不进行编码转换)。 |
布尔值与日期时间
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中的五种类型亲和归纳如下:
类型亲和性 | 描述 |
---|---|
TEXT | 列中的值倾向于被存储为文本字符串。 |
NUMERIC | 列中的值倾向于被存储为数值。如果不能转换为数值,则作为TEXT存储。 |
INTEGER | 类似于NUMERIC,但会优先存储为整数。 |
REAL | 列中的值倾向于被存储为浮点数。 |
BLOB | 值将以其输入的形式存储,不进行任何转换。 |
因为我也是刚刚学习使用SQLite,实话实说文档上关于亲和类型的描述给我绕得够呛,这玩意只能到实践中去理解了,此外我认为其他编程语言中的数据类型兼容机制也有助于理解SQLite的亲和性设计。
SQLite环境配置
环境介绍
- 操作系统:Windows 11 23H2
- 目标环境:SQLite 3.46.0.
操作步骤
- 前往SQLite官网下载sqlite-dll-win-x64-3460000.zip和sqlite-tools-win-x64-3460000.zip;
- 创建一个sqlite目录并将两个压缩包解压至该目录下;
- 将上述目录添加到PATH系统环境变量,并确定;
- 新开终端,输入
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;
- 将sqlite-dll-win-x64-3460000.zip解压缩到一个目录下;
- 管理员身份启动"Developer Command Prompt for VS 2022"(可以直接在开始菜单中搜索);
- 切换到刚刚解压的目录下并执行下述命令:
我的目录在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()
正常连接并输出结果。