SQLite数据库的使用

海的船

已于 2024-03-13 15:53:29 修改

阅读量1k

点赞数 17

分类专栏：数据库文章标签：数据库 sqlite

于 2024-02-26 17:22:22 首次发布

本文链接：https://blog.csdn.net/qq_40762280/article/details/136304060

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、简介

SQLite 是一个流行的嵌入式关系数据库管理系统，广泛应用于各种程序和系统中，如移动应用、桌面应用和一些服务器应用中。使用 SQLite 不需要一个独立的服务器进程，因为它是无服务器的。SQLite 通过少量的磁盘空间提供一个轻量级但功能丰富的数据库解决方案。

SQLite 数据库的每个表可以有的列数量是有限制的。根据 SQLite 的官方文档，这个限制是由编译时参数 SQLITE_MAX_COLUMN 决定的。默认的最大列数是 2000 列。这意味着在不重新编译 SQLite 并改变这个参数的情况下，任何单个表夹在默认情况下最多只能有 2000 个列。

如果你需要的列数超过这个限制，你必须在编译 SQLite 之前改变 SQLITE_MAX_COLUMN 的值。然而，增加列数可能会影响数据库的性能，并且推荐的做法是重新考虑数据库的设计，以适应这个列数限制，因为一般来说很少有应用场景需要如此多的列。

这是一个考虑性能和可管理性的取舍。例如，在设计时，可以考虑将一些列规范化为单独的表，以减少单个表中的列数。

数据库中表的结构图通常被称为"实体-关系图"(Entity-Relationship Diagram, ERD)或者"数据库模式"(Database Schema Diagram)。这是一种图形化显示数据库表及其间关系的方法。这个结构图包含了多个重要的组成部分：

实体（Entities）：在ERD中，实体通常是指数据库中的表，它代表存储数据的地方。每个实体对应数据库中的一个表。
属性（Attributes）：属性是表中的列（或称字段），它们定义了实体的数据类型。属性决定了数据库表可以存储哪些数据。
主键（Primary Key）：每个实体（表）通常有一个主键，它是唯一地标识实体中各个记录的字段。一个实体中的每个记录必须有一个独一无二的主键值。
外键（Foreign Key）：当表之间存在关系时，一个表中的字段可能会引用另外一个表的主键，这种字段被称为外键。它们是关系数据库中用于关联数据的关键元素。
关系（Relationships）：表之间的关系说明了数据是如何相关的。常见的关系类型包括一对一(1:1)、一对多(1:M)和多对多(M:N)。

二、使用说明

1、数据类型

SQLite 是一个遵循ACID原则的轻量级数据库管理系统，它不需要一个单独的服务器进程或是运行在客户端，而是成为程序的一部分。SQLite支持的存储类型比较简单明了，共有以下几种数据类型：

NULL: 这个类型用于存放NULL值，表示该处数据不存在。
INTEGER: 这个类型是用来存放整数的，它的大小可以是1、2、3、4、6或8字节，具体大小取决于数值的范围。
REAL: 实数类型，用于存放浮点数，具体来说是8字节的IEEE浮点数。
TEXT: 文本数据类型，用于存放字符串。SQLite使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）来存储文本。
BLOB: “Binary Large Object”的缩写，用来存储二进制数据。BLOB 类型的数据完全按照输入存放，不会进行任何转换。

除了上述的内建数据类型，SQLite通过类型亲和性（type affinity）概念来模拟更多的数据类型。每个表的列都有一个推荐的数据类型，被称为该列的类型亲和性。SQLite支持以下五种类型亲和性：

TEXT affinity: 列被推荐用于存储文本数据。如果表的创建语句中列的类型含有 “CHAR”, “CLOB”, 或 “TEXT” 等字符，那么这一列的类型亲和性就是 TEXT。尝试在 TEXT 列中存入非文本数据时，数据将尝试被转换为文本。
NUMERIC affinity: 列被推荐用于存储数字数据。如果列声明（column declaration）中含有"INT"，那么它的类型亲和性将被判断为 INTEGER，但是如果含有 “NUMERIC”, “REAL”, “FLOAT”, “DECIMAL”, “BOOLEAN”, “DATE”, “DATETIME” 等，列的类型亲和性是 NUMERIC。在NUMERIC列中输入的数据首先被尝试转换成整数或REAL数，如果转换失败，则按TEXT类型存储。
INTEGER affinity: 这个亲和性类似于 NUMERIC，但若列的声明有 “INTEGER” 关键字，则该列具有 INTEGER 亲和性，其特点是当输入的数据转换为INTEGER时，即使原始数字是浮点型，它也优先存储为INTEGER。
REAL affinity: 若列声明包含 “REAL”, “DOUBLE”, “DOUBLE PRECISION”, “FLOAT” 等，则亲和性为 REAL。REAL列优先存储浮点数。
NONE affinity: 若列声明不匹配上述任何一种，则亲和性为 NONE，通常用于BLOB类型的列。在这类列上没有尝试转换类型，数据以输入的形式存储。

SQLite 是类型宽松（type-affine）的数据库系统，这意味着即使你指定了 DATETIME，SQLite 对于存储在该列的数据类型并不会强制限制。SQLite 实际上并没有一个内建的存储类直接对应于 DATETIME，而是允许存储为文本（ISO8601 字符串）、实数（Julian day numbers）或整数（Unix Time，从1970-01-01 00:00:00 UTC 到当前的秒数）。然而，当你在应用程序中使用 DATETIME 类型时，你应该坚持使用标准的日期和时间格式，这样数据的一致性和查询的准确性才能够得到保证。

2、创建数据库

使用命令行工具: 你可以通过 SQLite 的命令行接口 sqlite3 创建数据库。例如，要创建一个名为 mydatabase.db 的新数据库，可以使用以下命令：

sqlite3 mydatabase.db

3、定义数据结构

创建表: 在 SQLite 数据库中，数据以表格的形式存储。你可以使用 CREATE TABLE 语句来创建一个新表。例如：

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

这个例子创建了一个名为 users 的表，其中包含 id, username, email, 和 created_at 四个字段。
数据类型: 定义表结构时，应选择合适的数据类型以存储相应的数据。SQLite 主要支持 NULL, INTEGER, REAL, TEXT, BLOB 类型，具体存储类型还应参照之前提及的类型亲和性规则来确定。

4、数据操纵

插入: 使用 INSERT 语句向表中添加数据。

INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com');

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

SELECT * FROM users;
SELECT username FROM users WHERE id = 1;

更新: 使用 UPDATE 语句更新表中的数据。

UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

删除: 使用 DELETE 语句从表中删除数据。
```
DELETE FROM users WHERE id = 1;
```

5、事务控制

SQLite 支持事务控制，您可以使用 BEGIN TRANSACTION, COMMIT, 和 ROLLBACK 语句来控制事务。

6、数据库维护

备份和恢复: SQLite 数据库被存储在单个文件中，因此可以简单地通过复制数据库文件来备份数据库，或通过复制备份文件来恢复数据库。
优化: 通过执行 VACUUM 命令可以重组数据库文件的存储结构，并优化数据库性能。

7、与编程语言集成

SQLite 可以容易地与多种编程语言集成，例如 Python, PHP, C, C++, Java等，很多语言都有相应的库来操作 SQLite 数据库。这样就可以在程序代码中直接进行数据库操作。

三、注意点

在SQLite中，列名可以包含.（点）、[（左方括号）或]（右方括号）等特殊字符，但这种使用方式需要小心处理，以避免引起混淆或错误。

列名中包含特殊字符：当列名包含如.、[或]等特殊字符时，你需要在引用这些列名的时候使用双引号、方括号或反引号（取决于你使用的SQL工具或库）来确保SQL语句被正确解析。例如，如果你有一个名为data.value的列，你需要在查询中将其写为"data.value"（使用双引号），[data.value]（使用方括号）或data.value（使用反引号，主要在MySQL中使用，SQLite推荐使用双引号）。
TEXT数据中的特殊字符：如果你的问题是关于TEXT数据类型的值中出现特殊字符（如.、[或]），则这些字符不会对SQL查询或数据库操作产生直接影响。文本值是按照它们存储的准确内容来处理的，所以除非你的应用程序逻辑特意赋予这些字符特殊意义，否则它们仅仅被视为字符串的一部分。

重要的是要注意，虽然SQLite允许在列名和TEXT数据中使用特殊字符，但这样做可能会使得SQL查询更加复杂和难以理解。特别是当使用.时，因为在SQL中.通常用于分隔表名和列名（例如，在带有JOIN操作的查询中）。因此，最好的做法是尽量避免在列名中使用可能引起混淆的特殊字符，特别是在设计数据库模式时。

总的来说，虽然可以使用这些特殊字符，但为了保持代码的清晰性和减少潜在的错误，建议在列名和数据设计中尽可能避免使用它们。如果你必须使用特殊字符，确保在SQL查询中正确地引用列名，并清楚地理解这些字符在你的数据中的含义。

在SQLite中，$和_字符在列名中的使用并不算是特殊字符的使用。这两个字符是被普遍接受的，并且可以在不需要特殊引用的情况下直接用于列名。这意味着你可以在列名中包含这些字符而不会引起解析查询时的问题或需要额外的引号。

$字符：通常用于某些编程环境中表示变量，但在SQLite列名中使用时，它就是列名的一部分，不具有特殊意义。
_字符：在SQL中是一个广泛接受的字符，用于列名、表名等标识符中，增强可读性（例如，user_name或order_id）。它也没有特殊的含义，不会改变SQL语句的解析方式。

尽管$和_可以自由使用，但值得注意的是，在SQL的LIKE语句中，_字符代表单个字符的通配符。这意味着当你在使用LIKE语句进行模糊匹配时，如果你的列名或文本数据中包含_，你可能需要使用转义字符来指定你是在寻找字面上的_字符，而不是使用它作为通配符。除此之外，在列名和数据中使用这两个字符通常不会引起问题或混淆。