Ubuntu上使用SQLite3完全指南

目录

• 什么是SQLite3
• SQLite3的作用和特点
• 安装SQLite3
• 基本使用方法
• 数据库操作
• 表操作
• 数据操作
• 查询操作
• 高级功能
• 实际应用案例
• 最佳实践
• 常见问题解决

什么是SQLite3

SQLite3是一个轻量级的嵌入式关系型数据库管理系统，它是SQLite数据库引擎的命令行工具。SQLite是一个C语言库，实现了一个小型、快速、自包含、高可靠性、功能齐全的SQL数据库引擎。

核心特点

• 无服务器架构：不需要单独的服务器进程
• 零配置：无需安装和管理
• 单文件数据库：整个数据库存储在一个磁盘文件中
• 跨平台：支持Windows、Linux、macOS等多个平台
• 轻量级：库文件小于600KB
• 事务性：支持ACID事务
• SQL标准：支持大部分SQL92标准

SQLite3的作用和特点

主要作用

1. 嵌入式应用开发

   • 移动应用（Android、iOS）
   • 桌面应用程序
   • 浏览器数据存储

2. 原型开发和测试

   • 快速搭建数据库原型
   • 单元测试数据存储
   • 开发环境数据库

3. 小型项目数据存储

   • 个人项目
   • 小型网站
   • 配置文件存储

4. 数据分析和处理

   • 临时数据处理
   • 数据转换和清洗
   • 报表生成

优势特点

适用场景

✅ 适合使用的场景：

• 嵌入式应用
• 小到中型网站（<100K点击/天）
• 桌面应用程序
• 原型开发
• 教学和学习
• 数据分析脚本

❌ 不适合使用的场景：

• 高并发写入应用
• 大型企业级应用
• 需要复杂用户权限管理
• 网络数据库需求

安装SQLite3

在Ubuntu上安装

大多数Ubuntu发行版默认已安装SQLite3，可以通过以下命令检查：

# 检查是否已安装
sqlite3 --version

如果未安装，使用以下命令安装：

# 更新包列表
sudo apt update

# 安装SQLite3
sudo apt install sqlite3

# 安装开发库（如果需要编程接口）
sudo apt install libsqlite3-dev

验证安装

# 查看版本信息
sqlite3 --version

# 查看帮助信息
sqlite3 --help

基本使用方法

启动SQLite3

# 创建新数据库或打开已存在的数据库
sqlite3 database_name.db

# 打开内存数据库（临时使用）
sqlite3 :memory:

# 直接执行SQL命令
sqlite3 database_name.db "SELECT * FROM table_name;"

SQLite3命令行界面

进入SQLite3后，你会看到提示符 sqlite>，这表示你已进入SQLite命令行界面。

基本命令

-- 查看所有表
.tables

-- 查看表结构
.schema table_name

-- 查看数据库信息
.dbinfo

-- 显示列标题
.headers on

-- 设置输出模式
.mode column

-- 退出SQLite3
.quit
-- 或
.exit

元命令（以点开头）

数据库操作

创建数据库

# 方法1：通过命令行创建
sqlite3 myapp.db

# 方法2：在SQLite中创建
sqlite3
sqlite> .open myapp.db

连接数据库

# 连接到已存在的数据库
sqlite3 /path/to/database.db

# 连接并执行命令
sqlite3 database.db ".tables"

备份数据库

-- 在SQLite3中备份
.backup backup_database.db

-- 或使用命令行
sqlite3 original.db ".backup backup.db"

恢复数据库

-- 恢复数据库
.restore backup_database.db

表操作

创建表

-- 基本创建表语法
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    age INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 创建带约束的表
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL CHECK(price > 0),
    category_id INTEGER,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

查看表结构

-- 查看表结构
.schema users

-- 查看所有表
.tables

-- 获取表信息
PRAGMA table_info(users);

修改表结构

-- 添加列
ALTER TABLE users ADD COLUMN phone TEXT;

-- 重命名表
ALTER TABLE users RENAME TO customers;

-- 重命名列（SQLite 3.25.0+）
ALTER TABLE users RENAME COLUMN username TO user_name;

删除表

-- 删除表
DROP TABLE IF EXISTS users;

数据操作

插入数据

-- 插入单条记录
INSERT INTO users (username, email, age) 
VALUES ('john_doe', 'john@example.com', 25);

-- 插入多条记录
INSERT INTO users (username, email, age) VALUES 
    ('alice', 'alice@example.com', 30),
    ('bob', 'bob@example.com', 28),
    ('charlie', 'charlie@example.com', 35);

-- 从其他表插入数据
INSERT INTO backup_users SELECT * FROM users WHERE age > 30;

更新数据

-- 更新单个字段
UPDATE users SET age = 26 WHERE username = 'john_doe';

-- 更新多个字段
UPDATE users SET 
    email = 'newemail@example.com',
    age = age + 1
WHERE id = 1;

-- 条件更新
UPDATE users SET age = age + 1 WHERE age < 30;

删除数据

-- 删除特定记录
DELETE FROM users WHERE age < 18;

-- 删除所有记录（保留表结构）
DELETE FROM users;

-- 清空表并重置自增ID
DELETE FROM users;
VACUUM;

查询操作

基本查询

-- 查询所有数据
SELECT * FROM users;

-- 查询特定列
SELECT username, email FROM users;

-- 条件查询
SELECT * FROM users WHERE age > 25;

-- 排序查询
SELECT * FROM users ORDER BY age DESC;

-- 限制结果数量
SELECT * FROM users LIMIT 10;

-- 分页查询
SELECT * FROM users LIMIT 10 OFFSET 20;

聚合查询

-- 计数
SELECT COUNT(*) FROM users;

-- 平均值
SELECT AVG(age) FROM users;

-- 最大值和最小值
SELECT MAX(age), MIN(age) FROM users;

-- 分组统计
SELECT age, COUNT(*) as count 
FROM users 
GROUP BY age 
HAVING count > 1;

连接查询

-- 内连接
SELECT u.username, p.name as product_name
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN products p ON o.product_id = p.id;

-- 左连接
SELECT u.username, o.id as order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

子查询

-- 标量子查询
SELECT username FROM users 
WHERE age = (SELECT MAX(age) FROM users);

-- 存在性查询
SELECT * FROM users u 
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

高级功能

索引优化

-- 创建索引
CREATE INDEX idx_users_email ON users(email);

-- 创建复合索引
CREATE INDEX idx_users_age_email ON users(age, email);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- 查看索引
.indices users

-- 删除索引
DROP INDEX idx_users_email;

视图

-- 创建视图
CREATE VIEW adult_users AS
SELECT * FROM users WHERE age >= 18;

-- 使用视图
SELECT * FROM adult_users;

-- 删除视图
DROP VIEW adult_users;

触发器

-- 创建触发器
CREATE TRIGGER update_timestamp 
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    UPDATE users SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

-- 查看触发器
.schema users

-- 删除触发器
DROP TRIGGER update_timestamp;

事务处理

-- 开始事务
BEGIN TRANSACTION;

-- 执行操作
INSERT INTO users (username, email) VALUES ('test', 'test@example.com');
UPDATE users SET age = 30 WHERE username = 'test';

-- 提交事务
COMMIT;

-- 或回滚事务
-- ROLLBACK;

实际应用案例

案例1：处理Android应用数据库

# 连接到Android应用的SQLite数据库
sqlite3 bible_book.db

# 查看所有表
.tables

# 查看书签数据
SELECT book_name, chapter_number, verse_number, verse_content 
FROM book_table 
WHERE verse_bookmark IS NOT NULL AND verse_bookmark != '' 
LIMIT 5;

# 清除默认书签数据
UPDATE book_table 
SET verse_bookmark = '', book_mark_modify_time = 0 
WHERE verse_bookmark IS NOT NULL AND verse_bookmark != '';

# 验证清除结果
SELECT COUNT(*) FROM book_table 
WHERE verse_bookmark IS NOT NULL AND verse_bookmark != '';

案例2：数据分析和报表

-- 创建销售数据表
CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    product_name TEXT,
    sale_date DATE,
    amount REAL,
    quantity INTEGER
);

-- 插入示例数据
INSERT INTO sales (product_name, sale_date, amount, quantity) VALUES
    ('Product A', '2024-01-01', 100.00, 2),
    ('Product B', '2024-01-01', 150.00, 1),
    ('Product A', '2024-01-02', 200.00, 4);

-- 生成销售报表
SELECT 
    product_name,
    COUNT(*) as total_orders,
    SUM(quantity) as total_quantity,
    SUM(amount) as total_revenue,
    AVG(amount) as avg_order_value
FROM sales 
GROUP BY product_name;

案例3：日志文件处理

# 创建日志数据库
sqlite3 logs.db

# 创建日志表
CREATE TABLE access_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    timestamp DATETIME,
    ip_address TEXT,
    method TEXT,
    url TEXT,
    status_code INTEGER,
    response_size INTEGER
);

# 从CSV文件导入日志数据
.mode csv
.import access_log.csv access_logs

# 分析访问统计
SELECT 
    status_code,
    COUNT(*) as count,
    AVG(response_size) as avg_size
FROM access_logs 
GROUP BY status_code;

最佳实践

1. 性能优化

-- 使用索引优化查询
CREATE INDEX idx_users_email ON users(email);

-- 使用EXPLAIN QUERY PLAN分析查询
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'test@example.com';

-- 定期清理数据库
VACUUM;

-- 分析数据库统计信息
ANALYZE;

2. 数据完整性

-- 使用外键约束
PRAGMA foreign_keys = ON;

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 使用检查约束
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    price REAL CHECK(price > 0),
    stock INTEGER CHECK(stock >= 0)
);

3. 备份策略

# 定期备份数据库
sqlite3 production.db ".backup backup_$(date +%Y%m%d).db"

# 验证备份完整性
sqlite3 backup_20240101.db "PRAGMA integrity_check;"

4. 安全考虑

-- 避免SQL注入，使用参数化查询
-- 在应用程序中使用绑定参数而不是字符串拼接

-- 设置数据库权限
-- chmod 600 database.db  # 只有所有者可读写

常见问题解决

问题1：数据库被锁定

# 错误信息：database is locked
# 解决方案：
# 1. 确保没有其他程序在使用数据库
# 2. 删除锁文件
rm database.db-wal database.db-shm

# 3. 或者设置超时
sqlite3 database.db "PRAGMA busy_timeout = 30000;"

问题2：中文字符显示问题

-- 设置编码
.encoding UTF-8

-- 或在创建数据库时指定
sqlite3 -init "PRAGMA encoding='UTF-8';" database.db

问题3：大文件处理

-- 增加缓存大小
PRAGMA cache_size = 10000;

-- 设置页面大小（创建数据库时）
PRAGMA page_size = 4096;

问题4：性能问题

-- 关闭同步写入（提高写入速度，但降低安全性）
PRAGMA synchronous = OFF;

-- 使用WAL模式（提高并发性能）
PRAGMA journal_mode = WAL;

-- 批量插入优化
BEGIN TRANSACTION;
-- 执行大量INSERT语句
COMMIT;

总结

SQLite3是一个功能强大且易于使用的嵌入式数据库，特别适合：

1. 学习SQL：语法简单，功能完整
2. 快速原型开发：零配置，即用即走
3. 小型项目：轻量级，高性能
4. 数据分析：支持复杂查询和聚合
5. 移动开发：Android和iOS的首选数据库

通过本指南，你应该能够熟练使用SQLite3进行各种数据库操作。记住，实践是掌握SQLite3的最好方法，多动手操作，多尝试不同的功能和命令。