SQLite 数据库入门：基础教程详解

引言：欢迎来到轻量级数据库的世界

在数据驱动的今天，数据库无处不在。从大型企业级应用到智能手机上的小工具，它们都依赖于数据库来存储、管理和检索信息。当我们谈论数据库时，许多人首先想到的是庞大的客户端-服务器系统，如MySQL、PostgreSQL、Oracle或SQL Server。然而，对于许多应用场景，特别是嵌入式系统、移动应用、桌面软件以及作为文件格式使用时，这些重量级的解决方案可能过于复杂或资源密集。

这时，一个名为 SQLite 的数据库闪耀登场。SQLite是一个自给自足的、无服务器的、零配置的事务性SQL数据库引擎。它是世界上部署最广泛的数据库引擎，其简洁、高效和易用性使其成为无数应用程序的首选。

本教程旨在为完全没有SQLite经验的初学者提供一个全面而详细的入门指南。我们将从SQLite的基本概念开始，逐步深入到如何使用命令行界面进行操作，学习核心的SQL命令，并探讨一些进阶特性。读完本文，你应该能够：

理解SQLite的核心特性及其适用场景。
安装（如果需要）并使用SQLite命令行工具。
理解数据库、表、列、行等基本概念。
使用标准的SQL语句创建、读取、更新和删除数据（CRUD操作）。
掌握一些常用的.命令来管理SQLite环境。
对更高级的概念如约束、索引、连接等有初步了解。

无论你是开发者、数据分析师，还是仅仅对数据存储感兴趣，SQLite都是一个值得学习的强大工具。让我们开始这段探索之旅吧！

第一章：什么是SQLite？核心特性与优势

在深入学习如何使用SQLite之前，了解它到底是什么以及它与传统数据库有何不同至关重要。

SQLite 的定义：

SQLite 是一个关系型数据库管理系统 (RDBMS)，但它与其他常见的RDBMS（如MySQL、PostgreSQL）最大的区别在于其架构：它不是一个独立的服务器进程，而是一个库。它直接嵌入到使用它的应用程序中。整个数据库就是一个单一的文件。

核心特性：

无服务器 (Serverless)： SQLite不需要单独的服务器进程运行。应用程序通过调用SQLite库中的函数来直接访问数据库文件。这意味着没有客户端-服务器协议，没有网络通信开销，安装和部署变得极其简单。
零配置 (Zero-Configuration)： 不需要安装服务器，不需要设置用户账户，不需要配置权限，甚至不需要创建数据库实例（第一次打开一个不存在的文件时就会自动创建）。一切都围绕着一个文件进行。
事务性 (Transactional)： 尽管简单，SQLite完全支持ACID属性（原子性 Atomicity, 一致性 Consistency, 隔离性 Isolation, 持久性 Durability）。这意味着即使在系统崩溃或断电的情况下，数据库也能保持数据完整性。
自给自足 (Self-Contained)： SQLite库本身几乎不依赖外部库。它只需要标准的C库。这使得它非常容易移植到不同的操作系统和平台。
紧凑 (Compact)： SQLite库本身非常小巧，数据库文件通常也比等量数据在其他数据库中的文件要小。
可移植 (Portable)： 数据库就是一个文件，你可以轻松地在不同设备或操作系统之间复制和移动它。
动态类型 (Dynamic Typing)： SQLite 的列可以存储任何类型的数据，尽管建议在创建表时指定数据类型以增强可读性和约束。这与许多其他数据库的静态类型系统不同。
广泛支持的SQL方言： SQLite支持大部分标准的SQL92规范，包括复杂的查询、子查询、视图、触发器等。

为什么选择 SQLite？优势与适用场景：

简单性： 这是其最大的优势。无需安装服务器，无需管理后台进程，维护成本极低。
部署便捷： 只需分发你的应用程序和数据库文件即可。
高性能 (对于其设计目标而言)： 对于单用户或少量并发写入的应用，SQLite的读写速度通常非常快，因为它避免了网络延迟和进程间通信。
资源占用低： 内存和CPU开销相对较小。
可靠性： ACID事务保证了数据的一致性和持久性。
跨平台： 几乎支持所有主流操作系统。

SQLite 的适用场景：

移动应用： Android和iOS都内置了SQLite，是移动应用存储结构化数据的标准方式。
桌面应用程序： 许多桌面软件使用SQLite存储配置、用户数据等（如Firefox浏览器、Skype、Adobe软件等）。
嵌入式系统： 资源受限的设备（如物联网设备）常使用SQLite。
文件格式： 有时作为一种结构化的文件格式使用，替代XML或JSON，因为它支持快速查询。
网站开发： 对于流量较低、读多写少的网站，或者作为开发/测试环境，SQLite也是一个不错的选择。
教育和学习： 易于上手，非常适合学习SQL。

SQLite 的局限性：

并发写入： SQLite对写入操作有独占锁。在高并发写入的场景下性能可能成为瓶颈。虽然有WAL模式可以改善读写并发，但与客户端-服务器数据库的多写能力仍有差距。
安全性： 由于数据库是一个文件，其安全性依赖于文件系统的权限控制。没有内置的用户管理和细粒度的权限系统。
扩展性： 没有内置的集群或复制功能（需要外部工具或应用层实现）。

总结来说，SQLite是一个非常棒的工具，特别适合需要简单、便携、零配置数据库的应用。理解了这些基础，我们就可以开始动手实践了。

第二章：获取和使用SQLite命令行工具

开始学习SQLite最直接的方式就是使用其官方提供的命令行工具 sqlite3（或 sqlite）。这个工具允许你直接与SQLite数据库文件交互，执行SQL命令和特殊的.命令。

获取 SQLite 命令行工具：

在许多操作系统中，sqlite3 命令行工具可能已经预装了：

macOS: 通常自带。打开终端输入 sqlite3 --version 即可检查。
Linux: 大多数发行版可以通过包管理器安装。例如，在Debian/Ubuntu上：sudo apt update && sudo apt install sqlite3；在Fedora/CentOS上：sudo dnf install sqlite3 或 sudo yum install sqlite3。
Windows: Windows系统通常不自带。你需要从SQLite官方网站下载预编译的二进制文件。访问 https://www.sqlite.org/download.html，找到 “Precompiled Binaries for Windows” 部分，下载 sqlite-tools-win64-x64-....zip (或32位版本) 或 sqlite-dll-win64-x64-....zip (只包含dll和exe)。解压后，将包含 sqlite3.exe 的目录添加到系统PATH环境变量中，或者直接在该目录下打开命令提示符/PowerShell执行 sqlite3.exe。

启动 SQLite 命令行工具：

打开你的终端（Windows用户打开命令提示符或PowerShell），然后输入 sqlite3 命令。

连接或创建一个新的数据库：
- 要连接到一个 已存在的 数据库文件（例如 my_database.db），输入：
  bash sqlite3 my_database.db
  如果 my_database.db 文件不存在，SQLite会自动为你创建它。
- 如果不指定文件名直接输入 sqlite3，SQLite会创建一个内存数据库。这个数据库只存在于本次会话中，当你退出 sqlite3 时，其中的数据就会丢失。这对于测试SQL语句非常有用。
  bash sqlite3

当你成功启动并连接到数据库后，你会看到类似以下的提示符：

SQLite version 3.40.1 2022-12-28 14:03:47 Enter ".help" for usage hints. Connected to: my_database.db sqlite>

sqlite> 提示符表示你现在可以输入SQL命令或SQLite的特殊.命令了。

退出 SQLite 命令行工具：

输入 .quit 或 .exit 然后按回车即可退出。

sqlite sqlite> .quit

第三章：SQLite 基本概念与数据类型

在使用数据库之前，理解一些基本概念是必要的。

数据库 (Database)：
在SQLite中，一个数据库就是一个文件（例如 my_database.db）。它包含了所有的数据以及描述数据结构的元数据（如表的定义）。

表 (Table)：
表是关系型数据库中组织数据的基本单位。它由行和列组成，类似于一个电子表格。每个表都代表了某个实体或概念（例如，用户、产品、订单）。

列 (Column) / 字段 (Field)：
列定义了表中存储的数据的种类。每一列都有一个名称（如 id, name, price）和关联的数据类型。一列中的所有数据都应该是同一种类型的。

行 (Row) / 记录 (Record)：
行代表表中的一个独立的数据项或记录。例如，在一个 Products 表中，每一行可能代表一个具体的产品。

主键 (Primary Key – PK)：
主键是一列或一组列，其值能唯一标识表中的每一行。主键值必须是唯一的，且不能为 NULL。它是确保数据完整性的重要约束。通常用一个自增的整数列作为主键（在SQLite中常用 INTEGER PRIMARY KEY AUTOINCREMENT）。

外键 (Foreign Key – FK)：
外键是一列或一组列，其值引用了另一个表的主键。外键用于建立两个表之间的关系。例如，Orders 表可能有一个 customer_id 列，它引用了 Customers 表的 id 主键。

SQLite 的数据类型 (Data Types)：

SQLite使用一种更灵活的动态类型系统。虽然你可以在创建表时指定数据类型（如 INTEGER, TEXT, REAL, BLOB），但SQLite实际上在底层使用存储类 (Storage Class)来存储数据。任何列都可以存储任何存储类的值。

SQLite 支持以下五种原始存储类：

NULL: 值是 NULL。
INTEGER: 值是一个带符号的整数，根据值的大小存储为1、2、3、4、6或8字节。
REAL: 值是一个浮点数，存储为8字节的IEEE浮点标准。
TEXT: 值是一个文本字符串，使用数据库编码（UTF-8, UTF-16BE 或 UTF-16LE）存储。
BLOB: 值是一个二进制大对象（Binary Large Object），按输入原样存储（例如，图片、音频文件）。

当你使用 CREATE TABLE 指定数据类型时（例如 INT, VARCHAR, FLOAT, BOOLEAN, DATE 等），SQLite会使用类型亲和性 (Type Affinity)来决定该列的存储类。例如：

包含 INT 的类型名（如 INTEGER, TINYINT, BIGINT）具有 INTEGER 亲和性。
包含 CHAR, CLOB, TEXT 的类型名具有 TEXT 亲和性。
包含 BLOB 的类型名具有 BLOB 亲和性。
包含 REAL, FLOA, DOUB 的类型名具有 REAL 亲和性。
没有上述任何关键字的类型名具有 NUMERIC 亲和性（可以存储 INTEGER, REAL, TEXT, BLOB, NULL）。

简单来说，虽然你可以写 VARCHAR(255) 或 INT，SQLite不会强制长度或严格类型检查（除了 INTEGER PRIMARY KEY 强制唯一性和非NULL）。它会根据你指定的类型决定最佳的存储类。对于初学者，通常使用 INTEGER, REAL, TEXT, BLOB 已经足够清晰。

第四章：创建你的第一个数据库和表

现在我们来动手创建一个数据库文件并在其中建立一个表。我们将创建一个简单的 Products 表。

首先，启动 sqlite3 并连接到一个新的数据库文件（如果文件不存在，会被创建）。

bash sqlite3 inventory.db

你将看到 sqlite> 提示符。现在，我们可以创建一个表。我们将创建一个名为 products 的表，包含 id, name, price, 和 stock 列。

sql -- 创建 products 表 CREATE TABLE products ( id INTEGER PRIMARY KEY AUTOINCREMENT, -- 商品ID，主键，自动增长 name TEXT NOT NULL UNIQUE, -- 商品名称，文本类型，不能为空，必须唯一 price REAL NOT NULL CHECK (price > 0), -- 商品价格，浮点类型，不能为空，价格必须大于0 stock INTEGER DEFAULT 0 -- 库存数量，整数类型，默认为0 );

输入上面的SQL语句（可以一次性粘贴或一行一行输入），注意SQL语句以分号 ; 结束。按下回车执行。

如果成功，sqlite> 提示符会再次出现，没有任何错误信息。

解释一下 CREATE TABLE 语句：

CREATE TABLE products (...): 创建一个名为 products 的新表。
id INTEGER PRIMARY KEY AUTOINCREMENT: 定义一个名为 id 的列。数据类型建议为 INTEGER。PRIMARY KEY 指定此列是表的主键，用于唯一标识每一行。AUTOINCREMENT 是SQLite的一个特性，它会自动为新插入的行生成一个唯一、递增的整数值。这是创建唯一ID的常用方法。
name TEXT NOT NULL UNIQUE: 定义一个名为 name 的列。数据类型为 TEXT。NOT NULL 约束表示此列的值不能为空。UNIQUE 约束表示此列中的所有值都必须是唯一的（不能有两个商品有完全相同的名称）。
price REAL NOT NULL CHECK (price > 0): 定义一个名为 price 的列。数据类型为 REAL（浮点数）。NOT NULL 约束。CHECK (price > 0) 是一个CHECK约束，它确保插入或更新到此列的值必须满足指定的条件（价格大于0）。
stock INTEGER DEFAULT 0: 定义一个名为 stock 的列。数据类型为 INTEGER。DEFAULT 0 约束表示如果在插入新行时没有为 stock 列指定值，则会自动使用默认值 0。

查看表结构：

你可以使用 .schema 命令来查看刚刚创建的表的定义：

sqlite sqlite> .schema products

输出应该类似于：

sql CREATE TABLE products ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, price REAL NOT NULL CHECK (price > 0), stock INTEGER DEFAULT 0 );

或者使用 .tables 命令查看当前数据库中的所有表：

sqlite sqlite> .tables

输出：

products

这表明我们已经成功创建了 products 表。现在，这个数据库文件 inventory.db 已经包含了这个表的结构定义，尽管它里面还没有任何数据。

第五章：数据操作（CRUD）

CRUD 是创建 (Create)、读取 (Read)、更新 (Update) 和删除 (Delete) 的缩写，是数据库操作中最核心的部分。我们将学习如何使用 INSERT, SELECT, UPDATE, DELETE 这四个基本的SQL命令。

5.1 插入数据 (Create – INSERT)

使用 INSERT INTO 语句向表中添加新行。

方法1：为所有列指定值 (按列定义顺序)：

“`sql
— 插入一条商品记录
INSERT INTO products (name, price, stock) VALUES (‘Laptop’, 999.99, 50);

— 插入另一条记录
INSERT INTO products (name, price, stock) VALUES (‘Mouse’, 25.50, 200);
“`

注意，我们没有为 id 列指定值，因为它设置了 AUTOINCREMENT，SQLite会自动生成。我们也没有为 stock 指定值，它会使用默认值0。

sql -- 插入一条只有名称和价格的记录，stock会使用默认值0 INSERT INTO products (name, price) VALUES ('Keyboard', 75.00);

如果你为表中的所有列都提供值（并且顺序与创建表时一致），可以省略列名列表（尽管不推荐，因为它依赖于列的顺序）：

sql -- 不推荐省略列名，但它是有效的 -- INSERT INTO products VALUES (NULL, 'Monitor', 300.00, 30); -- id用NULL或不写，让AUTOINCREMENT生效

方法2：插入多条记录 (SQLite特有语法)：

SQLite允许在一条 INSERT 语句中插入多行数据，使用逗号分隔 VALUES 后面的值列表：

sql -- 插入多条记录 INSERT INTO products (name, price, stock) VALUES ('Tablet', 250.00, 100), ('Webcam', 50.00, 75), ('Speakers', 150.00, 60);

输入这些 INSERT 语句并执行。

5.2 读取数据 (Read – SELECT)

SELECT 语句用于从表中检索数据。这是最常用的SQL命令。

选择所有列和所有行：

sql -- 选择 products 表中的所有列和所有行 SELECT * FROM products;

输出可能类似（id是自动生成的）：

id|name|price|stock 1|Laptop|999.99|50 2|Mouse|25.5|200 3|Keyboard|75.0|0 4|Tablet|250.0|100 5|Webcam|50.0|75 6|Speakers|150.0|60

默认输出格式可能不太美观。可以使用 .mode column 和 .header on 命令来改进显示：

sqlite sqlite> .mode column sqlite> .header on sqlite> SELECT * FROM products;

现在输出应该更易读：

“`
id name price stock

1 Laptop 999.99 50
2 Mouse 25.5 200
3 Keyboard 75.0 0
4 Tablet 250.0 100
5 Webcam 50.0 75
6 Speakers 150.0 60
“`

你可以随时使用 .mode list 和 .header off 恢复默认设置。

选择特定的列：

如果你只需要某些列的数据，可以在 SELECT 后面列出这些列名（用逗号分隔）：

sql -- 选择 name 和 price 列 SELECT name, price FROM products;

过滤数据 (WHERE 子句)：

WHERE 子句用于根据指定的条件过滤行。只有满足条件的行才会被返回。

“`sql
— 选择价格大于 100 的商品
SELECT * FROM products WHERE price > 100;

— 选择库存为 0 的商品
SELECT name FROM products WHERE stock = 0;

— 选择名称包含 ‘o’ 的商品 (使用 LIKE 操作符和通配符 %)
SELECT * FROM products WHERE name LIKE ‘%o%’; — % 表示任意多个字符

— 选择价格在 50 到 300 之间的商品 (使用 BETWEEN)
SELECT * FROM products WHERE price BETWEEN 50 AND 300;

— 选择库存数量是 50 或 200 的商品 (使用 IN)
SELECT * FROM products WHERE stock IN (50, 200);

— 结合多个条件 (使用 AND, OR)
SELECT * FROM products WHERE price > 100 AND stock > 50;
SELECT * FROM products WHERE price < 50 OR stock < 10;
“`

排序结果 (ORDER BY 子句)：

ORDER BY 子句用于根据一个或多个列对结果集进行排序。默认是升序 (ASC)，可以使用 DESC 指定降序。

“`sql
— 按价格升序排列
SELECT name, price FROM products ORDER BY price ASC; — ASC 可以省略

— 按库存降序排列
SELECT name, stock FROM products ORDER BY stock DESC;

— 先按价格升序，再按名称升序排列 (对于价格相同的商品)
SELECT name, price, stock FROM products ORDER BY price ASC, name ASC;
“`

限制结果数量 (LIMIT 子句)：

LIMIT 子句用于限制 SELECT 语句返回的最大行数。通常与 ORDER BY 一起使用，获取排序后的前几条或某范围内的记录。

“`sql
— 获取价格最高的 3 件商品
SELECT name, price FROM products ORDER BY price DESC LIMIT 3;

— 获取价格从高到低排列的第 2 到第 4 件商品 (OFFSET 1 表示跳过第一行)
SELECT name, price FROM products ORDER BY price DESC LIMIT 3 OFFSET 1; — LIMIT 3, OFFSET 1 等同于 LIMIT 1,3 在某些SQL方言，但SQLite支持 LIMIT count OFFSET skip
“`

5.3 更新数据 (Update – UPDATE)

UPDATE 语句用于修改表中现有的数据。

“`sql
— 将 ID 为 3 的商品价格更新为 80.00
UPDATE products SET price = 80.00 WHERE id = 3;

— 将库存数量小于 10 的商品库存设置为 20
UPDATE products SET stock = 20 WHERE stock < 10;

— 将所有商品的库存数量增加 10 (谨慎操作！没有 WHERE 子句会影响所有行)
— UPDATE products SET stock = stock + 10;

— 更新多列
UPDATE products SET price = 100.00, stock = 15 WHERE name = ‘Keyboard’;
“`

特别注意： UPDATE 语句中的 WHERE 子句非常重要！如果没有 WHERE 子句，表中的所有行都会被更新。在执行 UPDATE 语句之前，务必仔细检查 WHERE 条件。

5.4 删除数据 (Delete – DELETE)

DELETE FROM 语句用于从表中删除一行或多行数据。

“`sql
— 删除 ID 为 6 的商品
DELETE FROM products WHERE id = 6;

— 删除所有库存为 0 的商品
DELETE FROM products WHERE stock = 0;

— 删除 products 表中的所有数据 (谨慎操作！没有 WHERE 子句会删除所有行)
— DELETE FROM products;
“`

特别注意： DELETE FROM 语句中的 WHERE 子句同样非常重要！如果没有 WHERE 子句，表中的所有行都会被删除，使表变为空。

在执行完这些操作后，你可以再次使用 SELECT * FROM products; 来查看数据的变化。

第六章：进阶概念初步

学习了CRUD操作后，我们来看一些有助于构建更强大数据库应用的进阶概念。

6.1 约束 (Constraints)

约束是在列或表上定义的规则，用于限制可以存储在表中的数据。我们在创建表时已经接触了一些：PRIMARY KEY, NOT NULL, UNIQUE, CHECK, DEFAULT。另一个重要的约束是 FOREIGN KEY。

外键 (FOREIGN KEY)：

外键用于在两个表之间建立关联。它确保关联列中的值在引用的表中存在，维护了数据的引用完整性。

假设我们有一个 categories 表：

“`sql
— 创建 categories 表
CREATE TABLE categories (
category_id INTEGER PRIMARY KEY AUTOINCREMENT,
category_name TEXT NOT NULL UNIQUE
);

— 插入一些类别数据
INSERT INTO categories (category_name) VALUES (‘Electronics’), (‘Accessories’), (‘Office’);
“`

现在，我们在 products 表中添加一个 category_id 列，作为外键引用 categories 表的 category_id。我们需要先删除旧的 products 表，然后重新创建它（或者使用 ALTER TABLE 修改表结构，但SQLite的 ALTER TABLE 功能相对有限，重创是常见做法）。

“`sql
— 删除现有 products 表 (如果存在)
DROP TABLE IF EXISTS products;

— 重新创建 products 表，加入外键约束
CREATE TABLE products (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
price REAL NOT NULL CHECK (price > 0),
stock INTEGER DEFAULT 0,
category_id INTEGER, — 外键列
— 定义外键约束
FOREIGN KEY (category_id) REFERENCES categories(category_id)
);
“`

现在，当我们向 products 表插入数据时，category_id 的值必须是 categories 表中实际存在的 category_id 值，或者是 NULL (如果列允许NULL)。

sql -- 插入带有分类的商品 (假设 Electronics 的 category_id 是 1, Accessories 是 2, Office 是 3) -- 你可以通过 SELECT * FROM categories; 查看真实的 category_id INSERT INTO products (name, price, stock, category_id) VALUES ('Laptop', 999.99, 50, 1); INSERT INTO products (name, price, stock, category_id) VALUES ('Mouse', 25.50, 200, 2); INSERT INTO products (name, price, stock, category_id) VALUES ('Keyboard', 75.00, 0, 2); -- 配件 INSERT INTO products (name, price, stock, category_id) VALUES ('Monitor', 300.00, 30, 1); -- 尝试插入一个不存在的 category_id (这会失败，因为外键约束) -- INSERT INTO products (name, price, stock, category_id) VALUES ('Fake Product', 10.00, 5, 99); -- Error: FOREIGN KEY constraint failed

注意： SQLite默认情况下不强制外键约束，需要在连接后手动开启。在命令行工具中，可以使用 .headers on 后面的 .mode column 之前执行：

sqlite sqlite> PRAGMA foreign_keys = ON;

在应用程序中连接数据库后，也需要执行 PRAGMA foreign_keys = ON; 语句来开启外键。这通常是第一个要执行的SQL命令。

6.2 索引 (Indexes)

索引是一种数据库结构，用于加快数据的检索速度。它们的工作原理类似于书的目录——允许数据库系统快速定位到包含特定值或值范围的行，而不必扫描整个表。

为经常用于 WHERE 子句或 JOIN 条件中的列创建索引，可以显著提高查询性能。主键列会自动创建唯一索引。

创建索引：

“`sql
— 为 products 表的 name 列创建索引
CREATE INDEX idx_product_name ON products (name);

— 为 products 表的 price 列创建索引
CREATE INDEX idx_product_price ON products (price);
“`

删除索引：

sql -- 删除索引 DROP INDEX idx_product_name;

注意： 虽然索引能提高读取速度，但它们会稍微降低插入、更新和删除数据的速度，因为每次修改数据时都需要同时更新索引。同时，索引会占用额外的磁盘空间。因此，只为那些频繁用于查询条件的列创建索引即可。

6.3 连接 (JOIN)

连接操作用于根据两个或多个表之间的相关列，将这些表中的行组合起来。这是关系型数据库非常强大的功能。

最常见的连接类型是 INNER JOIN。它返回在两个表中都存在匹配行的所有行。

假设我们想显示商品名称及其对应的分类名称。我们需要连接 products 表和 categories 表，通过 products.category_id 和 categories.category_id 列进行匹配。

sql -- 连接 products 表和 categories 表，显示商品名称和分类名称 SELECT p.name AS product_name, -- 使用别名 p for products, c for categories c.category_name FROM products AS p -- 给 products 表起别名 p INNER JOIN categories AS c -- 给 categories 表起别名 c ON p.category_id = c.category_id; -- 指定连接条件

输出可能类似：

“`
product_name category_name

Laptop Electronics
Mouse Accessories
Keyboard Accessories
Monitor Electronics
“`

其他类型的连接包括 LEFT JOIN (或 LEFT OUTER JOIN)，RIGHT JOIN (SQLite不支持标准的 RIGHT JOIN，但可以通过改写为 LEFT JOIN 实现)，以及 FULL OUTER JOIN (SQLite不支持)。LEFT JOIN 返回左表中的所有行，以及右表中匹配的行。如果右表中没有匹配，则右表列为 NULL。

6.4 聚合函数和分组 (Aggregate Functions and GROUP BY)

聚合函数对一组行的值执行计算，并返回一个单一值。常用的聚合函数有：

COUNT(): 计算行数。
SUM(): 计算数值列的总和。
AVG(): 计算数值列的平均值。
MIN(): 找出列中的最小值。
MAX(): 找出列中的最大值。

GROUP BY 子句通常与聚合函数一起使用，将结果集按照一个或多个列进行分组。聚合函数会对每个组独立计算。

“`sql
— 计算 products 表中总共有多少条记录
SELECT COUNT(*) FROM products;

— 计算所有商品的库存总数
SELECT SUM(stock) FROM products;

— 计算所有商品的平均价格
SELECT AVG(price) FROM products;

— 找出价格最高的商品价格
SELECT MAX(price) FROM products;

— 找出价格最低的商品价格
SELECT MIN(price) FROM products;

— 按分类统计每种分类下的商品数量 (使用 GROUP BY)
SELECT category_id, COUNT(*)
FROM products
GROUP BY category_id;

— 结合 JOIN 和 GROUP BY，按分类名称统计商品数量
SELECT c.category_name, COUNT(p.id) AS product_count
FROM products AS p
INNER JOIN categories AS c ON p.category_id = c.category_id
GROUP BY c.category_name;
“`

第七章：常用的 SQLite 命令行命令 (`.`)

除了标准的SQL语句，sqlite3 命令行工具还提供了一系列以点开头的特殊命令（称为点命令或元命令），用于控制环境、获取信息等。

以下是一些常用的点命令：

.help: 显示所有可用的点命令及其简要说明。
.databases: 列出当前打开的数据库文件。
.tables: 列出当前数据库中的所有表。
.schema [表名]: 显示一个或所有表的 CREATE 语句（表结构定义）。
.indices [表名]: 列出指定表的所有索引。
.mode <模式>: 设置输出格式。常用模式有 list (默认), column (列对齐), html, csv, json 等。
.headers on|off: 开启或关闭输出列的标题行。
.quit 或 .exit: 退出 sqlite3 工具。
.open <文件名>: 关闭当前数据库并打开另一个数据库文件。如果文件不存在则创建。
.save <文件名>: 将当前内存数据库的内容保存到文件中 (只在内存数据库中可用)。
.restore <文件名>: 从文件中加载数据到内存数据库 (只在内存数据库中可用)。
.read <文件名>: 从指定文件中读取并执行SQL或点命令。
.import <文件名> <表名>: 从CSV等格式的文件导入数据到指定表。
.output <文件名>: 将后续SQL语句的输出重定向到指定文件。
.show: 显示当前的点命令设置 (mode, headers等)。

熟练使用这些点命令可以极大地提高你在命令行下操作SQLite的效率。

第八章：备份与恢复

由于SQLite数据库就是一个单一的文件，备份和恢复非常简单：

备份： 只需复制 .db 文件到一个安全的位置即可。
恢复： 用备份文件替换当前的 .db 文件即可。

如果数据库正在被应用程序使用，为了确保备份的数据是一致的，最好在备份前停止使用数据库的应用程序。或者，在 sqlite3 命令行中使用 .backup <文件名> 命令，它可以在数据库在线时执行热备份。

sqlite sqlite> .backup inventory_backup.db

这会创建一个名为 inventory_backup.db 的文件，它是 inventory.db 的一个时间点快照。

恢复通常是简单地复制文件，但如果想在 sqlite3 工具中恢复，可以使用 .restore <文件名> (主要用于内存数据库) 或 .read <SQL文件名> (如果备份是SQL转储文件)。更常见且可靠的方式是直接用备份文件替换原文件。

第九章：在编程语言中使用 SQLite (简述)

尽管命令行工具对于学习和简单的任务很有用，但在实际开发中，你通常会在编程语言中使用SQLite。大多数主流编程语言都提供了与SQLite交互的库或驱动程序。

例如，Python的标准库就包含了 sqlite3 模块，无需额外安装。

以下是一个简单的Python示例：

“`python
import sqlite3

连接到数据库 (如果不存在则创建)

conn = sqlite3.connect(‘inventory_app.db’)
cursor = conn.cursor()

开启外键约束 (重要!)

cursor.execute(“PRAGMA foreign_keys = ON;”)

创建 categories 表 (如果不存在)

cursor.execute(”’
CREATE TABLE IF NOT EXISTS categories (
category_id INTEGER PRIMARY KEY AUTOINCREMENT,
category_name TEXT NOT NULL UNIQUE
)
”’)

创建 products 表 (如果不存在)

cursor.execute(”’
CREATE TABLE IF NOT EXISTS products (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
price REAL NOT NULL CHECK (price > 0),
stock INTEGER DEFAULT 0,
category_id INTEGER,
FOREIGN KEY (category_id) REFERENCES categories(category_id)
)
”’)

插入数据 (如果需要)

try:
cursor.execute(“INSERT INTO categories (category_name) VALUES (?)”, (‘Electronics’,))
cursor.execute(“INSERT INTO categories (category_name) VALUES (?)”, (‘Accessories’,))
conn.commit() # 提交事务
except sqlite3.IntegrityError:
print(“Categories already exist.”)

try:
cursor.execute(“INSERT INTO products (name, price, stock, category_id) VALUES (?, ?, ?, ?)”, (‘Laptop’, 999.99, 50, 1))
cursor.execute(“INSERT INTO products (name, price, stock, category_id) VALUES (?, ?, ?, ?)”, (‘Mouse’, 25.50, 200, 2))
conn.commit() # 提交事务
except sqlite3.IntegrityError:
print(“Products already exist or foreign key constraint failed.”)

查询数据

cursor.execute(“SELECT p.name, c.category_name, p.price FROM products p INNER JOIN categories c ON p.category_id = c.category_id”)
rows = cursor.fetchall()

print(“\nProducts List:”)
for row in rows:
print(f”Name: {row[0]}, Category: {row[1]}, Price: {row[2]}”)

更新数据

cursor.execute(“UPDATE products SET price = ? WHERE name = ?”, (1050.00, ‘Laptop’))
conn.commit()

再次查询验证更新

print(“\nAfter Update:”)
cursor.execute(“SELECT name, price FROM products WHERE name = ‘Laptop'”)
print(cursor.fetchone())

删除数据 (谨慎操作)

cursor.execute(“DELETE FROM products WHERE name = ?”, (‘Mouse’,))

conn.commit()

关闭连接

conn.close()
“`

这段代码展示了连接数据库、创建表、插入数据、查询数据和更新数据的基本流程。在实际应用中，你需要更详细地处理错误、管理连接池等。

第十章：SQLite 使用技巧与最佳实践

开启外键约束： 如前所述，务必在每次连接数据库后执行 PRAGMA foreign_keys = ON; 以确保数据完整性。
使用参数化查询： 在编程语言中，插入或查询带有变量的数据时，使用 ? 或 :name 等占位符和对应的方法（如Python的 cursor.execute(sql, values)），而不是直接拼接字符串。这可以有效防止SQL注入攻击，并能正确处理特殊字符。
理解动态类型： 尽管SQLite允许列存储任何类型，但明确指定类型名称并在应用层进行类型校验仍然是好的实践，可以提高代码的可读性和可靠性。
优化查询： 使用 EXPLAIN QUERY PLAN 命令来查看SQLite如何执行你的SQL查询，帮助你识别性能瓶颈并决定是否需要创建索引。
批量操作： 插入或更新多行数据时，尽量使用单条SQL语句 (INSERT INTO ... VALUES (...), (...), ...;) 或在事务中执行多条语句，这通常比单独执行每个操作效率更高。
事务管理： 对于涉及多个操作的逻辑单元（例如，先扣库存再创建订单），使用事务来确保原子性。如果在事务中的任何一步失败，整个事务可以回滚，避免数据不一致。在编程语言中，通常有 conn.begin(), conn.commit(), conn.rollback() 等方法。
数据库文件管理： 选择合适的 .db 文件存放位置，考虑权限和备份策略。

结论：SQLite 是一个伟大的起点

恭喜你！通过阅读本教程，你已经掌握了SQLite数据库的基础知识和常用操作。从理解其独特的无服务器架构，到使用命令行工具进行CRUD操作，再到初步了解约束、索引和连接，你已经具备了开始使用SQLite构建小型应用或管理个人数据的能力。

SQLite因其简洁、高效和可靠性，在众多领域发挥着关键作用。它是学习关系型数据库概念的绝佳工具，其文件化的特性也使得实践和分享变得异常容易。

下一步：

实践！ 尝试创建自己的数据库和表，设计不同实体之间的关系，并执行更复杂的查询。
学习更多SQL： 探索子查询、视图、触发器、窗口函数等高级SQL特性。
掌握特定编程语言的API： 深入学习你常用的编程语言中与SQLite交互的库。
了解并发控制： 虽然SQLite是单写入锁，但理解WAL (Write-Ahead Logging) 模式如何改善读写并发是重要的。
性能调优： 学习如何使用 EXPLAIN QUERY PLAN 分析查询并优化数据库设计。

数据库是任何复杂应用程序的基石。掌握SQLite，你不仅获得了一个强大的工具，也为学习更大型的数据库系统打下了坚实的基础。继续探索，不断实践，你将在数据管理的道路上越走越远！

SQLite 数据库入门：基础教程详解 – wiki基地

SQLite 数据库入门：基础教程详解

引言：欢迎来到轻量级数据库的世界

第一章：什么是SQLite？核心特性与优势

第二章：获取和使用SQLite命令行工具

第三章：SQLite 基本概念与数据类型

第四章：创建你的第一个数据库和表

第五章：数据操作（CRUD）

5.1 插入数据 (Create – INSERT)

5.2 读取数据 (Read – SELECT)

5.3 更新数据 (Update – UPDATE)

5.4 删除数据 (Delete – DELETE)

第六章：进阶概念初步

6.1 约束 (Constraints)

6.2 索引 (Indexes)

6.3 连接 (JOIN)

6.4 聚合函数和分组 (Aggregate Functions and GROUP BY)

第七章：常用的 SQLite 命令行命令 (`.`)

第八章：备份与恢复

第九章：在编程语言中使用 SQLite (简述)

连接到数据库 (如果不存在则创建)

开启外键约束 (重要!)

创建 categories 表 (如果不存在)

创建 products 表 (如果不存在)

插入数据 (如果需要)

查询数据

更新数据

再次查询验证更新

删除数据 (谨慎操作)

cursor.execute(“DELETE FROM products WHERE name = ?”, (‘Mouse’,))

conn.commit()

关闭连接

第十章：SQLite 使用技巧与最佳实践

结论：SQLite 是一个伟大的起点

发表评论取消回复

SQLite 数据库入门：基础教程详解

引言：欢迎来到轻量级数据库的世界

第一章：什么是SQLite？核心特性与优势

第二章：获取和使用SQLite命令行工具

第三章：SQLite 基本概念与数据类型

第四章：创建你的第一个数据库和表

第五章：数据操作（CRUD）

5.1 插入数据 (Create – INSERT)

5.2 读取数据 (Read – SELECT)

5.3 更新数据 (Update – UPDATE)

5.4 删除数据 (Delete – DELETE)

第六章：进阶概念初步

6.1 约束 (Constraints)

6.2 索引 (Indexes)

6.3 连接 (JOIN)

6.4 聚合函数和分组 (Aggregate Functions and GROUP BY)

第七章：常用的 SQLite 命令行命令 (.)

第八章：备份与恢复

第九章：在编程语言中使用 SQLite (简述)

连接到数据库 (如果不存在则创建)

开启外键约束 (重要!)

创建 categories 表 (如果不存在)

创建 products 表 (如果不存在)

插入数据 (如果需要)

查询数据

更新数据

再次查询验证更新

删除数据 (谨慎操作)

cursor.execute(“DELETE FROM products WHERE name = ?”, (‘Mouse’,))

conn.commit()

关闭连接

第十章：SQLite 使用技巧与最佳实践

结论：SQLite 是一个伟大的起点

发表评论 取消回复

第七章：常用的 SQLite 命令行命令 (`.`)

发表评论取消回复