SQLite 详细教程
SQLite 是一个轻量级的嵌入式关系型数据库管理系统，它不需要单独的服务器进程，直接将数据库存储在普通磁盘文件中。以下是 SQLite 的详细教程：
1. SQLite 简介
SQLite 的特点：
- 零配置：无需安装或管理
- 服务器端：无服务器架构
- 单一磁盘文件：整个数据库存储在一个文件中
- 跨平台：支持多种操作系统
- 事务性：支持 ACID 事务
- 轻量级：整个库小于 1MB
2. 安装 SQLite
Windows
- 访问 SQLite 官网
- 下载预编译的二进制文件包
- 解压后得到 sqlite3.exe
Linux
sudo apt-get install sqlite3
Mac
brew install sqlite
3. 基本命令
启动 SQLite
sqlite3 数据库名.db
常用命令
.help- 显示帮助信息.databases- 列出数据库名称和文件.tables- 显示所有表.schema 表名- 显示表结构.quit- 退出 SQLite.mode- 设置输出模式 (csv, column, html, insert, line, list, tabs, tcl).headers on/off- 显示/隐藏列名.output 文件名- 将输出重定向到文件
4. SQL 基本操作
创建表
CREATE TABLE 表名 (
列1 数据类型 [约束],
列2 数据类型 [约束],
...
);
示例：
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
age INTEGER,
email TEXT UNIQUE
);
数据类型
- NULL - 空值
- INTEGER - 整数
- REAL - 浮点数
- TEXT - 文本字符串
- BLOB - 二进制数据
插入数据
INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...);
示例：
INSERT INTO users (name, age, email) VALUES ('张三', 25, 'zhangsan@example.com');
查询数据
SELECT 列1, 列2, ... FROM 表名 [WHERE 条件] [ORDER BY 列 [ASC|DESC]] [LIMIT 数量];
示例：
SELECT * FROM users;
SELECT name, email FROM users WHERE age > 20;
SELECT * FROM users ORDER BY age DESC LIMIT 5;
更新数据
UPDATE 表名 SET 列1=值1, 列2=值2, ... WHERE 条件;
示例：
UPDATE users SET age=26 WHERE name='张三';
删除数据
DELETE FROM 表名 WHERE 条件;
示例：
DELETE FROM users WHERE id=1;
5. 高级 SQL 操作
条件查询
SELECT * FROM users WHERE age BETWEEN 20 AND 30;
SELECT * FROM users WHERE name LIKE '张%';
SELECT * FROM users WHERE email IS NOT NULL;
聚合函数
SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users;
SELECT MAX(age), MIN(age) FROM users;
SELECT name, SUM(score) FROM scores GROUP BY name;
连接查询
-- 内连接
SELECT u.name, o.order_id
FROM users u
JOIN orders o ON u.id = o.user_id;
-- 左外连接
SELECT u.name, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
子查询
SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);
视图
CREATE VIEW 视图名 AS SELECT 语句;
示例：
CREATE VIEW adult_users AS SELECT * FROM users WHERE age >= 18;
索引
CREATE INDEX 索引名 ON 表名 (列名);
示例：
CREATE INDEX idx_email ON users (email);
6. 事务处理
BEGIN TRANSACTION;
-- SQL 语句
COMMIT;
-- 或
ROLLBACK;
示例：
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
7. 在编程语言中使用 SQLite
Python 示例
import sqlite3
# 连接数据库
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# 创建表
cursor.execute('''CREATE TABLE IF NOT EXISTS stocks
(date text, trans text, symbol text, qty real, price real)''')
# 插入数据
cursor.execute("INSERT INTO stocks VALUES ('2023-01-01','BUY','RHAT',100,35.14)")
# 提交事务
conn.commit()
# 查询数据
for row in cursor.execute("SELECT * FROM stocks"):
print(row)
# 关闭连接
conn.close()
Java 示例
import java.sql.*;
public class SQLiteExample {
public static void main(String[] args) {
Connection conn = null;
try {
// 加载驱动
Class.forName("org.sqlite.JDBC");
// 连接数据库
conn = DriverManager.getConnection("jdbc:sqlite:test.db");
Statement stmt = conn.createStatement();
// 创建表
stmt.executeUpdate("CREATE TABLE IF NOT EXISTS users " +
"(id INTEGER PRIMARY KEY, name TEXT, age INTEGER)");
// 插入数据
stmt.executeUpdate("INSERT INTO users (name, age) VALUES ('张三', 25)");
// 查询数据
ResultSet rs = stmt.executeQuery("SELECT * FROM users");
while (rs.next()) {
System.out.println(rs.getString("name") + " " + rs.getInt("age"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
8. SQLite 优化技巧
- 合理使用索引
- 使用事务批量操作
- 避免在循环中执行 SQL
- 使用预编译语句
- 定期执行
VACUUM命令整理数据库 - 合理设置页面大小 (
PRAGMA page_size) - 关闭同步以提高写入速度 (
PRAGMA synchronous=OFF)
9. 实用命令
-- 查看 SQLite 版本
SELECT sqlite_version();
-- 设置外键约束
PRAGMA foreign_keys = ON;
-- 查看编译选项
PRAGMA compile_options;
-- 整理数据库
VACUUM;
-- 备份数据库
.backup 备份文件名.db
10. 常见问题
- 并发访问：SQLite 支持并发读，但写操作是独占的
- 数据库大小限制：默认最大 140TB
- 数据类型灵活性：SQLite 使用动态类型系统
- 性能考虑：对于高并发写入应用，考虑使用客户端-服务器数据库
SQLite 是一个功能强大且易于使用的数据库系统，适合嵌入式应用、移动应用和小型项目。通过本教程，您应该能够开始使用 SQLite 进行数据库开发。