轻量级数据库SQLite

SQLite简介

SQLite 是一个轻量级的、嵌入式的关系型数据库管理系统（RDBMS）。它的设计初衷是为了提供一个简便、快速、可靠的数据库解决方案，不需要独立的服务器进程即可运行，适合嵌入到各种应用程序中。

主要特性

• 自包含：SQLite 是一个自包含的库，所有的功能都打包在一个单独的文件中。它不需要额外的配置或依赖，可以直接在任何支持 C 的环境中编译和运行。
• 无服务器：不同于 MySQL、PostgreSQL 等需要单独的服务器进程，SQLite 不需要服务器。数据库操作直接在本地文件系统上进行，简化了部署和管理。
• 零配置：无需复杂的安装和配置过程。SQLite 数据库文件可以直接在文件系统中创建和访问，适合快速开发和原型设计。
• 跨平台：SQLite 是跨平台的，可以在几乎所有的操作系统上运行，包括 Windows、Linux、macOS、iOS、Android 等。
• 事务支持：SQLite 支持 ACID（原子性、一致性、隔离性、持久性）事务特性，确保数据的完整性和安全性。
• 丰富的数据类型支持：SQLite 支持多种数据类型，包括整数、浮点数、文本、BLOB（大对象）等，尽管它使用动态类型系统。
• 广泛的语言绑定：SQLite 提供多种编程语言的绑定，包括 C/C++、Python、Java、C#、PHP 等，使其易于集成到不同的开发环境中。

典型应用场景

• 嵌入式应用：由于其小巧和无服务器的特性，SQLite 非常适合嵌入式设备和应用，如移动应用、浏览器扩展、嵌入式系统等。
• 本地/客户端存储：SQLite 常用于需要本地存储数据的应用程序中，例如桌面应用和移动应用，以便在无网络连接时也能访问数据。
• 测试和原型设计：因为部署简单，SQLite 是开发和测试环境的理想选择，特别是在需要快速原型设计和迭代的场景中。
• 小型网站和应用：对于流量和数据量不大的小型网站和应用，SQLite 提供了足够的功能和性能。

SQLite 的局限性

• 并发写入限制：SQLite 使用数据库级别的锁机制来处理写操作，因此在高并发写入场景下可能会成为瓶颈。
• 单用户写入：虽然支持多个读取者同时访问，但一次只能有一个写入者进行操作，这在高写入频率的应用中可能不够。
• 功能限制：与更复杂的数据库系统相比，SQLite 缺少一些高级功能，如复杂的权限管理、分布式数据库特性等。

总之，SQLite 是一个功能强大、使用简单的数据库解决方案，特别适合嵌入式系统和需要轻量级数据库支持的应用程序。它的无服务器和零配置特性使其成为开发人员的热门选择。

SQLite的使用

SQLite 支持的数据类型

SQLite 的数据类型系统与传统的关系型数据库有所不同，它采用了一种称为“动态类型”（dynamic typing）的灵活系统。虽然在创建表时可以指定列的数据类型，但 SQLite 并不严格限制存储在列中的数据类型。以下是 SQLite 支持的主要数据类型：

• NULL表示一个空值。可以用于表示数据缺失或未知的情况。
• INTEGER用于存储有符号的整数，大小根据值的大小自动调整，可以是 1、2、3、4、6 或 8 字节。
• REAL用于存储浮点数字，采用 8 字节的 IEEE 浮点数格式。
• TEXT用于存储文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）进行存储。
• BLOB用于存储二进制数据，存储的内容与输入的内容完全一致，不进行任何编码或转换。

类型关联（Type Affinity）

SQLite 使用“类型关联”来决定如何处理不同的数据类型。即使指定了某个数据类型，SQLite 仍然可以存储不同类型的数据。SQLite 的类型关联分为以下几类：

• INTEGER：如果列具有 INTEGER 类型关联，SQLite 优先将数据视为整数。
• REAL：如果列具有 REAL 类型关联，SQLite 优先将数据视为浮点数。
• TEXT：如果列具有 TEXT 类型关联，SQLite 优先将数据视为字符串。
• BLOB：如果列具有 BLOB 类型关联，数据将被存储为它的输入形式。
• NUMERIC：如果列具有 NUMERIC 类型关联，SQLite 会根据输入的格式自动选择 INTEGER、REAL 或 TEXT 类型。

类型推断

SQLite 在插入数据时，会根据输入值的格式自动推断其数据类型。例如：

• 如果插入的值是一个整数，SQLite 将尝试将其存储为 INTEGER。
• 如果插入的值是一个浮点数，SQLite 将尝试将其存储为 REAL。
• 如果插入的值是一个字符串（例如包含非数字字符），SQLite 将尝试将其存储为 TEXT。

SQLite 的数据类型系统提供了灵活性，允许在列中存储不同类型的数据。这种灵活性适合多种应用场景，但在设计数据库架构时需要谨慎，以避免数据不一致的问题。了解 SQLite 的类型系统有助于更好地利用其功能和特性。

SQLite 对 SQL92 标准的支持

SQLite 是一个轻量级的数据库引擎，虽然它支持许多 SQL92 标准的特性，但并不完全支持整个标准。以下是 SQLite 对 SQL92 标准的一些支持情况：

支持的 SQL92 特性

• 基本查询：支持基本的SELECT 查询，包括 WHERE、ORDER BY、GROUP BY、HAVING 等子句。
• 数据操作语言 (DML)：支持INSERT、UPDATE 和 DELETE 语句，用于数据的增删改操作。
• 数据定义语言 (DDL)：支持CREATE TABLE、ALTER TABLE 和 DROP TABLE 等语句，用于定义和修改数据库结构。支持 CREATE INDEX 和 DROP INDEX，用于索引的管理。
• 事务控制：支持BEGIN TRANSACTION、COMMIT 和 ROLLBACK，用于事务的控制和管理。
• 视图：支持CREATE VIEW 和 DROP VIEW，可以定义和删除视图。
• 聚合函数：支持常见的聚合函数，如COUNT、SUM、AVG、MIN、MAX。
• 连接操作：支持INNER JOIN、LEFT JOIN、CROSS JOIN 等连接操作。
• 子查询：支持在SELECT、INSERT、UPDATE 和 DELETE 语句中使用子查询。
• 窗口函数：自 SQLite 3.25.0 版本（2018 年 9 月发布）开始，SQLite 引入了对窗口函数的支持。

不完全支持或不支持的 SQL92 特性

• 外键约束：虽然 SQLite 支持外键约束，但默认情况下是禁用的。需要通过PRAGMA foreign_keys = ON; 启用。
• 复杂数据类型：不支持一些复杂的数据类型，如 ENUM、SET、存储过程和触发器（虽然支持简单的触发器）。
• 权限管理：SQLite 没有用户管理和权限控制功能，因为它是一个嵌入式数据库，通常用于单用户或单应用场景。
• 存储过程：不支持存储过程和函数的定义，这在某些复杂应用中可能是一个限制。
• 某些高级 SQL 特性：不支持某些 SQL92 标准的高级特性，如RIGHT JOIN、FULL OUTER JOIN、CHECK 约束的复杂表达式等。

SQLite 提供了许多符合 SQL92 标准的基本功能，足以满足大多数嵌入式和小型应用的需求。然而，由于其设计目标是轻量级和嵌入式，SQLite 在某些高级功能和特性上有所限制。因此，在选择 SQLite 作为数据库引擎时，需要根据具体应用场景评估其功能是否满足需求。

图形用户界面（GUI）客户端

• DB Browser for SQLite:一个开源的、跨平台的 SQLite 数据库管理工具，提供简单易用的图形界面，可以执行 SQL 查询、查看和编辑表数据、管理数据库结构等。
• SQLiteStudio:一个免费的 SQLite 数据库管理工具，支持跨平台，提供丰富的功能，包括数据库管理、SQL 查询、表格设计等。
• DBeaver:一个通用的数据库管理工具，支持多种数据库，包括 SQLite。它提供了一个强大的图形界面，适合专业用户进行复杂的数据库管理任务。
• DataGrip:由 JetBrains 开发的数据库管理工具，支持多种数据库系统，包括 SQLite。DataGrip 提供智能的 SQL 编辑器和数据库管理功能。
• Navicat for SQLite:款商业数据库管理工具，提供强大的数据库设计和管理功能，支持多平台使用。

SQLite 在Python中的使用

在 Python 中使用 SQLite 非常方便，因为 Python 的标准库中已经内置了一个名为 sqlite3 的模块，可以直接用于与 SQLite 数据库进行交互。

SQLite模块介绍

sqlite3 是 Python 标准库中的一个模块，提供了对 SQLite 数据库的接口。SQLite 是一个轻量级的嵌入式关系数据库管理系统，不需要独立的服务器进程，数据存储在一个文件中。sqlite3 模块允许在 Python 程序中使用 SQLite 数据库，进行数据的持久化存储和管理。

主要功能

• 数据库连接：建立与 SQLite 数据库的连接。
• SQL 执行：执行 SQL 查询和命令。
• 事务管理：支持事务的提交和回滚。
• 数据操作：插入、更新、删除和查询数据。
• 游标操作：遍历查询结果集。

工作机制

• 数据库连接：通过connect() 创建与 SQLite 数据库的连接。
• 游标操作：通过cursor() 创建游标对象，使用游标执行 SQL 语句和获取结果。
• 事务管理：使用commit() 提交事务，使用 Connection.rollback() 撤销事务。
• 数据存储：SQLite 数据库存储在一个文件中，所有数据都保存在该文件内。

主要组件和函数：

sqlite3.connect() 函数

sqlite3.connect() 函数用于创建与 SQLite 数据库的连接。可以连接到一个数据库文件，如果文件不存在，将会创建一个新的数据库文件。

参数：

• database：数据库文件的名称。如果文件不存在，将创建一个新的文件。
• timeout：可选参数，指定连接超时时间（秒）。
• detect_types：可选参数，用于检测类型的标志。
• isolation_level：可选参数，指定事务隔离级别。
• check_same_thread：可选参数，指定是否在同一线程中使用连接。
• factory：可选参数，指定游标工厂。

返回值：返回一个连接对象。

Connection.cursor() 方法

Connection.cursor() 方法用于创建一个游标对象，通过游标可以执行 SQL 语句和获取查询结果。

返回值：返回一个游标对象。

Cursor.execute() 方法

Cursor.execute() 方法用于执行单条 SQL 语句。

参数：

• sql：要执行的 SQL 语句。
• parameters：可选参数，SQL 语句中的参数值。

Cursor.executemany() 方法

Cursor.executemany() 方法用于执行多条 SQL 语句。

参数：

• sql：要执行的 SQL 语句。
• seq_of_parameters：参数序列，每个元素是一个参数元组。

Cursor.fetchall() 方法

Cursor.fetchall() 方法用于获取所有查询结果。

返回值：返回一个列表，其中每个元素是查询结果的一行。

Connection.commit() 方法

Connection.commit() 方法用于提交当前事务，将所有修改保存到数据库。

Connection.rollback() 方法

Connection.rollback() 方法用于回滚当前事务，撤销所有未提交的修改。

Connection.close() 方法

Connection.close() 方法用于关闭与数据库的连接。

SQLite使用示例

连接到数据库

首先，需要导入 sqlite3 模块并连接到一个 SQLite 数据库文件。如果数据库文件不存在，SQLite 会自动创建一个新的数据库文件。

import sqlite3

# 连接到 SQLite 数据库（如果数据库不存在，则会自动创建）
connection = sqlite3.connect('example.db')

创建表

使用 cursor 对象执行 SQL 语句来创建表。

# 创建一个 cursor 对象
cursor = connection.cursor()

# 创建一个表
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER NOT NULL
    )
''')

# 提交事务
connection.commit()

插入数据

可以使用 INSERT 语句向表中插入数据。

# 插入数据
cursor.execute('''
    INSERT INTO users (name, age) VALUES (?, ?)
''', ('Alice', 30))

# 提交事务
connection.commit()

查询数据

使用 SELECT 语句从表中查询数据。

# 查询数据
cursor.execute('SELECT * FROM users')

# 获取所有结果
rows = cursor.fetchall()

# 遍历并打印结果
for row in rows:
    print(row)

更新数据

可以使用 UPDATE 语句来更新表中的数据。

# 更新数据
cursor.execute('''
    UPDATE users SET age = ? WHERE name = ?
''', (31, 'Alice'))

# 提交事务
connection.commit()

删除数据

使用 DELETE 语句从表中删除数据。

# 删除数据
cursor.execute('''
    DELETE FROM users WHERE name = ?
''', ('Alice',))

# 提交事务
connection.commit()

关闭连接

操作完成后，记得关闭数据库连接。

# 关闭 cursor 和连接
cursor.close()
connection.close()

使用上下文管理器

在 Python 中，使用 with 语句可以更优雅地管理数据库连接和资源，确保在块结束时自动关闭连接。

import sqlite3

with sqlite3.connect('example.db') as connection:
    cursor = connection.cursor()
    # 执行数据库操作
    cursor.execute('SELECT * FROM users')
    rows = cursor.fetchall()
    for row in rows:
        print(row)

通过 Python 的 sqlite3 模块，可以轻松地与 SQLite 数据库进行交互。这个模块提供了执行 SQL 语句的基本功能，同时也支持事务处理和参数化查询，以确保应用程序的安全性和性能。