SQLite教程：从零开始学习SQLite数据库（2024最新版）
前言：为何选择SQLite？
在数据库的世界里，我们常常听到MySQL、PostgreSQL、Oracle等重量级选手的名字。它们是强大的、基于客户端-服务器（Client-Server）架构的数据库管理系统。然而，在许多场景下，我们并不需要如此复杂的解决方案。这时，一个轻量级、高效、易于集成的数据库——SQLite，便脱颖而出。
SQLite不是一个传统的客户端-服务器数据库，而是一个嵌入式的、C语言库。它将整个数据库（包括定义、表、索引和数据本身）都存储在一个单一的、跨平台的磁盘文件中。这种设计带来了无与伦比的简便性：
- 零配置：无需安装、无需配置、无需启动数据库服务。
- 无服务器：应用程序直接与数据库文件交互，没有中间的服务器进程。
- 自包含：整个库只有一个C文件，极易集成到任何应用程序中。
- 事务性：完全支持ACID（原子性、一致性、隔离性、持久性），确保数据安全可靠。
- 跨平台：一个数据库文件可以无缝地在Windows, macOS, Linux, iOS, Android等系统之间迁移。
因为这些特性，SQLite成为了世界上部署最广泛的数据库引擎，被用于数十亿个设备和应用程序中，包括几乎所有的智能手机、主流的操作系统和网页浏览器。
本教程（2024最新版）将带你从零开始，系统地学习SQLite。无论你是Web开发者、移动应用开发者、数据分析师，还是仅仅对数据库充满好奇的学生，本教程都将为你打下坚实的基础。
第一章：环境搭建与初次接触
在开始学习SQL语法之前，我们首先需要在自己的电脑上准备好SQLite的运行环境。
1.1 安装SQLite
好消息是，在很多操作系统中，SQLite已经预装好了。
-
macOS / Linux：打开你的终端（Terminal），输入以下命令并回车：
bash
sqlite3 --version
如果你看到类似于3.45.1 2024-01-30...这样的版本号信息，恭喜你，SQLite已经可以使用了。如果没有，可以通过包管理器安装：- Ubuntu/Debian:
sudo apt-get update && sudo apt-get install sqlite3 - macOS (使用Homebrew):
brew install sqlite
- Ubuntu/Debian:
-
Windows：Windows默认不带SQLite。你需要前往SQLite官方网站的下载页面。在 “Precompiled Binaries for Windows” 部分，下载
sqlite-tools-win-x64-*.zip(64位系统) 或sqlite-tools-win-x86-*.zip(32位系统)。解压后，你会得到sqlite3.exe文件。为了方便在任何目录下都能使用它，建议将该文件所在的目录添加到系统的环境变量Path中。
1.2 启动SQLite命令行工具
SQLite最直接的交互方式就是通过其命令行工具（Command-Line Interface, CLI）。
打开你的终端或命令提示符，进入一个你希望存放数据库文件的工作目录，然后输入：
bash
sqlite3 my_first_database.db
这条命令做了两件事：
1. 启动了SQLite的命令行工具。
2. 如果 my_first_database.db 这个文件不存在，它会创建一个新的、空的数据库文件。如果文件已存在，它会打开这个数据库。
执行后，你会看到类似下面的提示符，表示你已经进入了SQLite的交互环境：
SQLite version 3.45.1 2024-01-30 15:00:43
Enter ".help" for usage hints.
sqlite>
1.3 常用“点命令”
在 sqlite> 提示符下，除了可以执行标准的SQL语句，还可以使用一些以点（.）开头的特殊命令，这些命令是SQLite CLI的辅助工具，非常实用。
.help: 显示所有可用的点命令及其说明。.databases: 列出当前连接的数据库文件。.tables: 显示当前数据库中所有的表。.schema [表名]: 显示指定表的创建语句（或所有表的创建语句，如果不指定表名）。这是查看表结构的最佳方式。.quit或.exit: 退出SQLite命令行工具。
现在，你可以尝试输入 .tables，由于是新数据库，应该什么都不会显示。然后输入 .quit 退出。你会发现在当前目录下，已经多了一个 my_first_database.db 文件。
第二章：核心SQL语法 – 数据定义语言 (DDL)
DDL (Data Definition Language) 用于定义和管理数据库的结构，比如创建、修改和删除表。
2.1 创建表 (CREATE TABLE)
数据库的核心是表（Table）。表由行（Row）和列（Column）组成。创建表时，我们需要定义表的名称以及每一列的名称、数据类型和约束。
让我们创建一个 users 表，用来存储用户信息。
sql
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
age INTEGER,
registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
我们来逐行解析这个语句：
CREATE TABLE users (...): 定义了一个名为users的新表。id INTEGER PRIMARY KEY AUTOINCREMENT:id: 列名。INTEGER: 数据类型。在SQLite中，INTEGER PRIMARY KEY类型的列有特殊的含义，它会自动成为行ID的别名，性能极高。PRIMARY KEY: 约束。表示id是主键，唯一标识表中的每一行，不能为空。AUTOINCREMENT: 约束。确保新插入的行id会自动递增，并且保证不会复用已删除的ID。
name TEXT NOT NULL:name: 列名。TEXT: 数据类型，用于存储文本字符串。NOT NULL: 约束。表示此列的值不能为空。
email TEXT UNIQUE NOT NULL:UNIQUE: 约束。表示此列的值在整个表中必须是唯一的，用于防止重复注册。
age INTEGER: 一个普通的整数列，可以为空（因为没有NOT NULL）。registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP:TIMESTAMP: 通常用于存储日期和时间。DEFAULT CURRENT_TIMESTAMP: 约束。如果在插入新行时没有为该列提供值，它将自动使用当前的日期和时间作为默认值。
SQLite的数据类型（Type Affinity）
SQLite使用动态类型系统，但它推荐使用以下五种类型“亲和性”：
* TEXT: 文本字符串。
* INTEGER: 整数。
* REAL: 浮点数。
* BLOB: 二进制大对象，用于存储文件、图片等。
* NULL: 空值。
2.2 修改表 (ALTER TABLE)
如果表结构需要调整，可以使用 ALTER TABLE。SQLite的 ALTER TABLE 功能相对有限，但最常用的操作是支持的。
- 重命名表
sql
ALTER TABLE users RENAME TO employees; - 添加新列
sql
ALTER TABLE employees ADD COLUMN department TEXT DEFAULT 'Unassigned';
这会给employees表增加一个名为department的文本列，并设置了默认值。
注意：SQLite不支持直接删除列（DROP COLUMN）或修改列的类型。通常需要通过创建新表、复制数据、删除旧表、重命名新表的方式来间接实现。
2.3 删除表 (DROP TABLE)
删除一个不再需要的表非常简单，但操作要谨慎，因为表中的所有数据都会被永久删除。
sql
DROP TABLE employees;
第三章：核心SQL语法 – 数据操作语言 (DML)
DML (Data Manipulation Language) 用于操作表中的数据，包括插入、查询、更新和删除。
让我们重新创建 users 表，并用它来实践DML操作。
sql
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
age INTEGER,
registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
3.1 插入数据 (INSERT)
使用 INSERT INTO 语句向表中添加新行。
-
插入单行数据
sql
INSERT INTO users (name, email, age) VALUES ('Alice', '[email protected]', 30);
这里我们指定了要插入的列和对应的值。id和registered_at会自动生成。 -
插入另一行
sql
INSERT INTO users (name, email) VALUES ('Bob', '[email protected]');
age列没有提供值，它将默认为NULL。 -
一次插入多行
sql
INSERT INTO users (name, email, age) VALUES
('Charlie', '[email protected]', 25),
('David', '[email protected]', 42);
3.2 查询数据 (SELECT)
SELECT 是SQL中最强大、最常用的命令，用于从数据库中检索数据。
-
查询所有列
使用星号（*）代表所有列。
sql
SELECT * FROM users; -
查询指定列
sql
SELECT name, email FROM users; -
使用
WHERE子句进行条件过滤
WHERE用于筛选出满足特定条件的行。
“`sql
— 查询年龄大于30的用户
SELECT * FROM users WHERE age > 30;— 查询名字是Alice的用户
SELECT * FROM users WHERE name = ‘Alice’;— 查询邮箱以 .com 结尾的用户 (LIKE 和 % 通配符)
SELECT * FROM users WHERE email LIKE ‘%.com’;— 查询年龄在20到35之间的用户
SELECT * FROM users WHERE age BETWEEN 20 AND 35;
“` -
使用
ORDER BY排序
ASC为升序（默认），DESC为降序。
“`sql
— 按年龄降序排列
SELECT * FROM users ORDER BY age DESC;— 先按年龄升序，年龄相同时按名字字母顺序排列
SELECT * FROM users ORDER BY age ASC, name ASC;
“` -
使用
LIMIT和OFFSET进行分页
LIMIT指定最多返回多少行，OFFSET指定从第几行开始（跳过前面的行）。
“`sql
— 获取前2条记录
SELECT * FROM users LIMIT 2;— 跳过第1条，获取接下来的2条记录（常用于分页查询的第2页）
SELECT * FROM users LIMIT 2 OFFSET 1;
“`
3.3 更新数据 (UPDATE)
使用 UPDATE 命令修改表中已存在的数据。
极其重要：UPDATE 语句一定要带 WHERE 子句，否则会更新表中的所有行！
“`sql
— 将id为1的用户的年龄更新为31
UPDATE users SET age = 31 WHERE id = 1;
— 将所有年龄为空的用户设置默认年龄为18
UPDATE users SET age = 18 WHERE age IS NULL;
“`
3.4 删除数据 (DELETE)
使用 DELETE FROM 命令从表中删除行。
同样极其重要：DELETE 语句也要带 WHERE 子句，否则会删除表中的所有行！
sql
-- 删除名为'David'的用户
DELETE FROM users WHERE name = 'David';
第四章：SQL进阶查询
掌握了基本的CRUD（Create, Read, Update, Delete）之后，我们来学习一些更高级的查询技巧。
4.1 聚合函数
聚合函数对一组值进行计算，并返回单个值。
COUNT(): 计算行数。SUM(): 计算数值列的总和。AVG(): 计算数值列的平均值。MAX():找出列中的最大值。MIN(): 找出列中的最小值。
“`sql
— 计算用户总数
SELECT COUNT(*) FROM users;
— 计算所有用户的平均年龄
SELECT AVG(age) FROM users;
— 找出年龄最大的用户
SELECT MAX(age) FROM users;
“`
4.2 分组 (GROUP BY)
GROUP BY 子句通常与聚合函数一起使用，将具有相同值的行分组到汇总行中。
假设我们有一个 products 表：
“`sql
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT,
category TEXT,
price REAL
);
INSERT INTO products VALUES
(1, ‘Laptop’, ‘Electronics’, 1200),
(2, ‘Mouse’, ‘Electronics’, 25),
(3, ‘T-Shirt’, ‘Apparel’, 20),
(4, ‘Jeans’, ‘Apparel’, 60),
(5, ‘Keyboard’, ‘Electronics’, 75);
“`
现在，我们想知道每个商品类别的平均价格：
sql
SELECT category, AVG(price) FROM products GROUP BY category;
这条语句会按 category 列的值（’Electronics’, ‘Apparel’）进行分组，然后对每个组分别计算价格的平均值。
4.3 使用 HAVING 过滤分组
WHERE 用于过滤行，而 HAVING 用于过滤分组。它在 GROUP BY 之后执行。
sql
-- 找出商品种类超过1个，并且平均价格高于50的类别
SELECT
category,
COUNT(*) as product_count,
AVG(price) as avg_price
FROM products
GROUP BY category
HAVING product_count > 1 AND avg_price > 50;
4.4 连接 (JOIN)
JOIN 用于根据两个或多个表中的相关列之间的关系，来合并这些表的行。
我们再创建一个 orders 表：
“`sql
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
user_id INTEGER,
product_name TEXT,
amount REAL,
FOREIGN KEY(user_id) REFERENCES users(id)
);
INSERT INTO orders VALUES
(101, 1, ‘Book’, 15.99),
(102, 3, ‘Pen’, 1.50),
(103, 1, ‘Notebook’, 4.99);
``FOREIGN KEY约束确保orders表中的user_id必须是users表中存在的id`。
-
INNER JOIN: 返回两个表中连接字段相匹配的行。
sql
-- 查询下过订单的用户的名字和他们的订单信息
SELECT
users.name,
orders.product_name,
orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id; -
LEFT JOIN: 返回左表（
FROM后面的第一个表）的所有行，即使右表中没有匹配的行。如果右表没有匹配，则结果为NULL。
sql
-- 查询所有用户及其订单信息，即使某个用户没有下过单
SELECT
users.name,
orders.product_name
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
你会看到 Bob (id=2) 也会出现在结果中，但他的product_name是NULL。
第五章：SQLite高级特性
5.1 事务 (Transactions)
事务是一组必须作为一个整体全部成功执行或全部失败的操作。这保证了数据的完整性。SQLite默认情况下，每条SQL语句都在其自己的事务中执行。
要手动控制事务，可以使用：
* BEGIN TRANSACTION;: 开始一个事务。
* COMMIT;: 提交事务，将所有更改永久保存到数据库。
* ROLLBACK;: 回滚事务，撤销自 BEGIN 以来的所有更改。
“`sql
BEGIN TRANSACTION;
— 假设这是一个银行转账操作
UPDATE accounts SET balance = balance – 100 WHERE name = ‘Alice’;
UPDATE accounts SET balance = balance + 100 WHERE name = ‘Bob’;
— 如果中间发生任何错误，可以执行 ROLLBACK
— 如果一切顺利，执行 COMMIT
COMMIT;
“`
5.2 索引 (Indexes)
当表中的数据量增大时，查询速度会变慢。索引是一种特殊的数据结构，可以极大地提高查询速度，特别是 WHERE 子句和 JOIN 操作。它就像书的目录。
sql
-- 为 users 表的 email 列创建一个索引
CREATE INDEX idx_users_email ON users(email);
此后，WHERE email = '...' 这样的查询会变得非常快。主键和唯一约束的列会自动创建索引。
5.3 视图 (Views)
视图是一个虚拟表，其内容由查询定义。你可以像查询普通表一样查询视图。
好处：
1. 简化复杂查询。
2. 提供一个安全层，只暴露部分数据给用户。
“`sql
— 创建一个视图，只显示活跃用户的基本信息
CREATE VIEW active_users_view AS
SELECT name, email, registered_at
FROM users
WHERE age >= 18;
— 查询视图
SELECT * FROM active_users_view;
“`
5.4 JSON1 扩展
现代SQLite（自3.9.0版本起）内置了强大的JSON支持，这在处理API数据或非结构化数据时非常有用。
“`sql
CREATE TABLE logs (
id INTEGER PRIMARY KEY,
event_data TEXT — 存储JSON字符串
);
INSERT INTO logs (event_data) VALUES (‘{“user_id”: 1, “action”: “login”, “ip”: “192.168.1.1”}’);
INSERT INTO logs (event_data) VALUES (‘{“user_id”: 2, “action”: “logout”}’);
— 使用 json_extract 函数查询JSON内部的数据
SELECT json_extract(event_data, ‘$.user_id’), json_extract(event_data, ‘$.action’) FROM logs;
— 在WHERE子句中使用
SELECT * FROM logs WHERE json_extract(event_data, ‘$.action’) = ‘login’;
“`
第六章：在编程语言中使用SQLite
虽然命令行工具很方便，但SQLite的真正威力在于它能被轻松集成到应用程序中。几乎所有主流编程语言都有SQLite的库或驱动。
以 Python 为例，其标准库中就内置了 sqlite3 模块。
“`python
import sqlite3
连接到数据库（如果不存在则创建）
conn = sqlite3.connect(‘my_app.db’)
创建一个Cursor对象
cursor = conn.cursor()
执行SQL语句
cursor.execute(”’
CREATE TABLE IF NOT EXISTS stocks
(date text, trans text, symbol text, qty real, price real)
”’)
插入一条记录
cursor.execute(“INSERT INTO stocks VALUES (‘2024-05-20′,’BUY’,’MSFT’,100,313.50)”)
保存（提交）更改
conn.commit()
查询数据
for row in cursor.execute(‘SELECT * FROM stocks ORDER BY price’):
print(row)
关闭连接
conn.close()
``sqlite3
其他语言如Node.js (,better-sqlite3)、Java (JDBC Driver)、Go (database/sql`) 等也都有成熟的库来操作SQLite。
总结与展望
恭喜你！通过本教程的学习，你已经从一个SQLite的门外汉，成长为一名能够熟练运用其核心功能和高级特性的开发者。我们一起走过了从环境搭建到DDL、DML，再到进阶查询、事务、索引和JSON支持的完整旅程。
SQLite的哲学是“小而美，简而强”。它可能不是处理海量并发请求的互联网巨头的首选，但对于移动应用、桌面软件、物联网设备、数据分析原型、中小型网站以及无数需要一个可靠、轻便、无需管理的数据库的场景来说，它都是一个完美的选择。
数据库技术日新月异，但SQLite凭借其独特的定位和坚如磐石的稳定性，在未来很长一段时间内，仍将是软件工具箱中不可或缺的一员。希望本教程能成为你探索数据世界的坚实起点。继续实践，不断探索，祝你在数据的海洋中乘风破浪！