SQLite 简介

SQLite 是一个轻量级、嵌入式的关系型数据库，它不是一个独立的客户端/服务器应用程序，而是以库的形式存在，直接链接到你的程序中，这使得它非常小巧、快速且易于使用，非常适合桌面应用、移动应用、小型网站和物联网设备。

核心命令

所有 SQLite 命令都以点（）开头，它们是用于与 SQLite 交互的特殊指令，而不是 SQL 语句。

数据库与表操作

数据库操作

SQLite 可以同时打开多个数据库文件。

-- 附加一个数据库文件
-- ATTACH [DATABASE] 'File_Name' AS [Database_Name];
ATTACH DATABASE 'my_other_db.db' AS other_db;
-- 分离一个数据库
DETACH DATABASE other_db;
-- 查看所有附加的数据库
.databases

表操作

创建表

CREATE TABLE table_name (
    column1 data_type PRIMARY KEY,
    column2 data_type NOT NULL,
    column3 data_type DEFAULT 'default_value',
    column4 data_type UNIQUE
);

示例：

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,  -- 主键，自动递增
    name TEXT NOT NULL,                    -- 文本类型，不能为空
    email TEXT UNIQUE,                     -- 文本类型，值必须唯一
    age INTEGER DEFAULT 18,                -- 整数类型，默认值为 18
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP -- 创建时间，默认为当前时间
);

查看表结构

-- 方法一：使用 .schema 命令
.schema users
-- 方法二：使用 SQL 查询
PRAGMA table_info(users);

PRAGMA table_info(table_name) 会返回一个包含列名、数据类型、是否为空、默认值等信息的表格。

修改表结构

SQLite 对修改表结构的支持有限,但可以通过以下方式实现。

-- 添加新列
ALTER TABLE users ADD COLUMN country TEXT;
-- 重命名表
ALTER TABLE users RENAME TO customers;
-- 删除列 (SQLite 3.35.0+ 版本支持)
ALTER TABLE customers DROP COLUMN country;

删除表

DROP TABLE customers;

数据操作语言

插入数据

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

示例：

INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@example.com', 25);
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com'); -- age 会使用默认值 18

查询数据

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column1 [ASC|DESC]
LIMIT number OFFSET number;

示例：

-- 查询所有用户的所有信息
SELECT * FROM users;
-- 查询用户名和邮箱
SELECT name, email FROM users;
-- 查询年龄大于 30 的用户
SELECT * FROM users WHERE age > 30;
-- 查询名字以 'A' 开头的用户
SELECT * FROM users WHERE name LIKE 'A%';
-- 查询邮箱包含 'example' 的用户
SELECT * FROM users WHERE email LIKE '%example%';
-- 查询年龄在 20 到 30 之间的用户
SELECT * FROM users WHERE age BETWEEN 20 AND 30;
-- 查询用户，并按年龄降序排列，只取前 5 条
SELECT * FROM users ORDER BY age DESC LIMIT 5;
-- 查询用户，跳过前 2 条，取接下来的 3 条
SELECT * FROM users LIMIT 3 OFFSET 2;

更新数据

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

⚠️ 警告： WHERE 子句至关重要！如果省略它，表中所有行的数据都会被更新。 示例：

-- 将 ID 为 1 的用户的年龄更新为 26
UPDATE users SET age = 26 WHERE id = 1;
-- 将所有用户的年龄增加 1
UPDATE users SET age = age + 1;

删除数据

DELETE FROM table_name
WHERE condition;

⚠️ 警告： WHERE 子句至关重要！如果省略它，表中所有行都会被删除。 示例：

-- 删除 ID 为 1 的用户
DELETE FROM users WHERE id = 1;
-- 删除所有年龄小于 18 的用户
DELETE FROM users WHERE age < 18;

事务控制

事务确保一组操作要么全部成功，要么全部失败,保证数据的一致性。

-- 开始一个事务
BEGIN TRANSACTION;
-- 执行一系列操作
INSERT INTO users (name, email) VALUES ('Charlie', 'charlie@example.com');
UPDATE users SET age = 30 WHERE name = 'Alice';
-- 如果所有操作都成功，则提交事务，更改将永久保存
COMMIT;
-- 如果发生错误，可以回滚事务，撤销所有未提交的更改
-- ROLLBACK;

自动提交模式： 在 SQLite 中，默认情况下，每个 DML 语句（INSERT, UPDATE, DELETE）都是一个独立的事务，会自动提交，要手动控制事务，需要使用 BEGIN TRANSACTION。

高级功能

索引

索引可以大大加快查询速度,但会降低写入速度并占用额外存储空间。

-- 创建索引
CREATE INDEX index_name ON table_name (column_name);
-- 示例：为 users 表的 email 列创建索引
CREATE INDEX idx_users_email ON users (email);
-- 删除索引
DROP INDEX index_name;

聚合函数

用于对一组值进行计算，通常与 GROUP BY 一起使用。

示例：

-- 计算用户总数
SELECT COUNT(*) FROM users;
-- 计算平均年龄
SELECT AVG(age) FROM users;
-- 找出最年轻的用户年龄
SELECT MIN(age) FROM users;
-- 按年龄分组，统计每个年龄的用户数量
SELECT age, COUNT(*) FROM users GROUP BY age;

连接

用于从两个或多个表中查询数据。

-- 假设有另一个表 'orders'
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    product TEXT,
    amount REAL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);
INSERT INTO orders (user_id, product, amount) VALUES (1, 'Laptop', 999.99);
INSERT INTO orders (user_id, product, amount) VALUES (2, 'Mouse', 25.50);

示例：

-- 内连接：查询所有用户及其订单（只包含有订单的用户）
SELECT users.name, orders.product, orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;
-- 左连接：查询所有用户及其订单（即使没有订单也会显示）
SELECT users.name, orders.product, orders.amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

常用 PRAGMA 命令

PRAGMA 是 SQLite 的特殊命令,用于查询或设置数据库内部参数和状态。

实践示例：完整流程

1. 启动 SQLite

   sqlite3 my_app.db

2. 创建表

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

3. 插入数据

   INSERT INTO products (name, price, stock) VALUES ('Keyboard', 75.0, 100);
   INSERT INTO products (name, price) VALUES ('Mouse', 25.5); -- stock 默认为 0

4. 查询数据

   .mode column
   .headers on
   SELECT * FROM products;

5. 更新数据

   UPDATE products SET stock = stock - 10 WHERE name = 'Keyboard';

6. 使用事务

   BEGIN TRANSACTION;
   UPDATE products SET stock = stock - 1 WHERE id = 2;
   UPDATE products SET stock = stock + 1 WHERE id = 1;
   COMMIT;

7. 创建索引

   CREATE INDEX idx_products_name ON products (name);

8. 退出

   .quit