SQLite数据库教程

1. 核心概念

SQLite是一种开源的轻量级嵌入式关系型数据库管理系统，由D. Richard Hipp开发。它的设计理念是简单、轻量、自包含，不需要独立的服务器进程，而是作为应用程序的一部分运行。

1.1 主要特点

  • 轻量级：SQLite的核心库非常小，只有几百KB
  • 无服务器：不需要独立的服务器进程，直接嵌入到应用程序中
  • 自包含：包含所有必要的组件，不需要外部依赖
  • 零配置：不需要复杂的配置过程
  • 事务支持：支持ACID事务
  • 跨平台：支持多种操作系统，包括Linux、Windows、macOS等
  • 广泛兼容：支持标准SQL语法
  • 开源免费：使用BSD许可证，可自由使用和修改

1.2 核心组件

  • SQLite Core：核心库，包含SQL解析器、查询引擎等
  • SQLite Command Line Tool：命令行工具，用于交互式操作数据库
  • SQLite API：提供C语言接口，供其他编程语言调用
  • SQLite ODBC/JDBC Drivers：提供ODBC和JDBC驱动，支持更多编程语言

1.3 数据存储

SQLite将整个数据库存储在单个文件中：

  • 数据库文件：包含所有表、索引、触发器等数据
  • 事务日志：用于确保事务的ACID特性
  • 临时文件：用于处理大型查询和事务

1.4 适用场景

SQLite特别适合以下场景：

  • 嵌入式应用：如移动应用、桌面应用等
  • 小型网站：流量不大的个人网站或小型企业网站
  • 原型开发：快速开发和测试
  • 数据缓存：作为应用程序的缓存存储
  • 教育和学习：学习SQL和数据库概念的理想工具

2. 安装配置

2.1 安装SQLite

Windows系统

  1. 从SQLite官方网站下载预编译的二进制文件
  2. 将下载的文件解压到合适的目录
  3. 将SQLite的bin目录添加到系统环境变量PATH中

Linux系统

使用包管理器安装：

# Ubuntu/Debian
sudo apt update
sudo apt install sqlite3

# CentOS/RHEL
sudo yum install sqlite

macOS系统

macOS默认已安装SQLite，也可以使用Homebrew更新：

brew install sqlite3

2.2 验证安装

# 查看SQLite版本
sqlite3 --version

# 启动SQLite命令行工具
sqlite3

2.3 基本配置

SQLite几乎不需要配置，主要通过命令行参数或环境变量进行调整：

  • SQLITE_HOME：SQLite安装目录
  • SQLITE_TMPDIR：临时文件目录
  • SQLITE_DEBUG：调试模式

3. 基本使用

3.1 命令行操作

# 创建或打开数据库
sqlite3 mydatabase.db

# 执行SQL语句
sqlite3 mydatabase.db "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);"

# 导入SQL文件
sqlite3 mydatabase.db < schema.sql

# 导出数据库
sqlite3 mydatabase.db ".dump" > backup.sql

3.2 数据库操作

-- 创建数据库（SQLite会自动创建文件）
-- 打开数据库时自动创建

-- 查看所有表
.tables

-- 查看表结构
.schema users

-- 删除数据库（直接删除文件）
-- rm mydatabase.db

3.3 表操作

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

-- 修改表结构
ALTER TABLE users ADD COLUMN phone TEXT;
ALTER TABLE users RENAME COLUMN phone TO telephone;

-- 删除表
DROP TABLE users;

3.4 数据操作

-- 插入数据
INSERT INTO users (name, email, age) VALUES ('张三', 'zhangsan@example.com', 25);
INSERT INTO users (name, email, age) VALUES ('李四', 'lisi@example.com', 30);

-- 查询数据
SELECT * FROM users;
SELECT name, email FROM users WHERE age > 20;
SELECT * FROM users ORDER BY age DESC;
SELECT * FROM users LIMIT 10 OFFSET 5;

-- 更新数据
UPDATE users SET age = 26 WHERE id = 1;

-- 删除数据
DELETE FROM users WHERE id = 1;

3.5 索引操作

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

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 删除索引
DROP INDEX idx_email;

4. 高级功能

4.1 事务管理

-- 开始事务
BEGIN TRANSACTION;

-- 执行操作
INSERT INTO users (name, email) VALUES ('王五', 'wangwu@example.com');
UPDATE users SET age = 35 WHERE name = '王五';

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

4.2 视图

-- 创建视图
CREATE VIEW user_info AS
SELECT id, name, email FROM users;

-- 使用视图
SELECT * FROM user_info;

-- 删除视图
DROP VIEW user_info;

4.3 触发器

-- 创建触发器
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
BEGIN
    UPDATE users SET created_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

-- 删除触发器
DROP TRIGGER before_user_insert;

4.4 存储过程和函数

SQLite支持有限的存储过程功能，主要通过用户定义函数实现：

-- 创建用户定义函数（通过API实现）
-- 在SQLite命令行中无法直接创建，需要通过编程语言的API

-- 使用内置函数
SELECT COUNT(*) FROM users;
SELECT MAX(age) FROM users;
SELECT MIN(age) FROM users;
SELECT AVG(age) FROM users;
SELECT SUM(age) FROM users;

4.5 备份和恢复

-- 备份数据库
VACUUM INTO 'backup.db';

-- 从备份恢复
-- 直接替换原数据库文件

-- 在线备份
BACKUP DATABASE TO 'backup.db';

5. 最佳实践

5.1 数据库设计

  • 规范化设计：遵循数据库规范化原则，减少数据冗余
  • 合理使用数据类型：根据实际需求选择合适的数据类型
  • 设置适当的索引：为常用查询字段创建索引，提高查询性能
  • 使用主键：为每个表设置主键
  • 合理设计表结构：避免过于复杂的表结构

5.2 查询优化

  • 使用EXPLAIN分析查询：了解查询执行计划
  • **避免SELECT ***：只选择需要的字段
  • 使用LIMIT限制结果集：避免返回过多数据
  • 优化WHERE子句：使用索引字段作为查询条件
  • 合理使用JOIN：避免复杂的多表连接

5.3 性能调优

  • 使用事务：将多个操作组合成一个事务，减少磁盘I/O
  • 批量插入：使用BEGIN和COMMIT批量处理插入操作
  • 使用PRAGMA语句：调整SQLite的行为
  • 定期VACUUM：使用VACUUM命令优化数据库文件
  • 合理设置缓存大小：通过PRAGMA cache_size调整

5.4 安全措施

  • 参数化查询：避免SQL注入攻击
  • 加密数据库：使用SQLCipher等工具加密数据库
  • 备份数据库：定期备份数据库文件
  • 权限控制：限制对数据库文件的访问权限
  • 输入验证：验证用户输入，防止恶意数据

6. 实际应用

6.1 命令行操作

示例：基本数据库操作

# 创建数据库并创建表
sqlite3 company.db "CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, department TEXT, salary REAL);"

# 插入数据
sqlite3 company.db "INSERT INTO employees (name, department, salary) VALUES ('张三', '技术部', 8000);"
sqlite3 company.db "INSERT INTO employees (name, department, salary) VALUES ('李四', '市场部', 6000);"
sqlite3 company.db "INSERT INTO employees (name, department, salary) VALUES ('王五', '财务部', 7000);"

# 查询数据
sqlite3 company.db "SELECT * FROM employees;"
sqlite3 company.db "SELECT name, salary FROM employees WHERE department = '技术部';"
sqlite3 company.db "SELECT department, AVG(salary) FROM employees GROUP BY department;"

# 更新数据
sqlite3 company.db "UPDATE employees SET salary = 8500 WHERE name = '张三';"

# 删除数据
sqlite3 company.db "DELETE FROM employees WHERE name = '王五';"

6.2 Node.js应用

示例：使用Node.js操作SQLite

// 使用sqlite3库
const sqlite3 = require('sqlite3').verbose();

// 打开数据库连接
const db = new sqlite3.Database('mydatabase.db', (err) => {
  if (err) {
    console.error('数据库连接失败:', err.message);
  } else {
    console.log('成功连接到SQLite数据库');
  }
});

// 创建表
db.run(`CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    age INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)`, (err) => {
  if (err) {
    console.error('创建表失败:', err.message);
  } else {
    console.log('成功创建users表');
  }
});

// 插入数据
const addUser = (name, email, age) => {
  return new Promise((resolve, reject) => {
    db.run(
      'INSERT INTO users (name, email, age) VALUES (?, ?, ?)',
      [name, email, age],
      function(err) {
        if (err) {
          reject(err);
        } else {
          resolve(this.lastID);
        }
      }
    );
  });
};

// 查询数据
const getUsers = () => {
  return new Promise((resolve, reject) => {
    db.all('SELECT * FROM users', [], (err, rows) => {
      if (err) {
        reject(err);
      } else {
        resolve(rows);
      }
    });
  });
};

// 更新数据
const updateUser = (id, name, email, age) => {
  return new Promise((resolve, reject) => {
    db.run(
      'UPDATE users SET name = ?, email = ?, age = ? WHERE id = ?',
      [name, email, age, id],
      function(err) {
        if (err) {
          reject(err);
        } else {
          resolve(this.changes);
        }
      }
    );
  });
};

// 删除数据
const deleteUser = (id) => {
  return new Promise((resolve, reject) => {
    db.run('DELETE FROM users WHERE id = ?', [id], function(err) {
      if (err) {
        reject(err);
      } else {
        resolve(this.changes);
      }
    });
  });
};

// 示例使用
async function main() {
  try {
    // 插入用户
    const userId = await addUser('张三', 'zhangsan@example.com', 25);
    console.log('创建用户ID:', userId);
    
    // 查询所有用户
    const users = await getUsers();
    console.log('所有用户:', users);
    
    // 更新用户
    const updateCount = await updateUser(userId, '张三', 'zhangsan@example.com', 26);
    console.log('更新用户数量:', updateCount);
    
    // 删除用户
    const deleteCount = await deleteUser(userId);
    console.log('删除用户数量:', deleteCount);
  } catch (error) {
    console.error('操作失败:', error.message);
  } finally {
    // 关闭数据库连接
    db.close((err) => {
      if (err) {
        console.error('关闭数据库连接失败:', err.message);
      } else {
        console.log('成功关闭数据库连接');
      }
    });
  }
}

main();

6.3 Python应用

示例：使用Python操作SQLite

import sqlite3

# 连接到SQLite数据库
# 如果数据库不存在，会自动创建
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', 'AAPL', 100, 150.0)")
cursor.execute("INSERT INTO stocks VALUES ('2023-01-02', 'BUY', 'MSFT', 50, 300.0)")
cursor.execute("INSERT INTO stocks VALUES ('2023-01-03', 'SELL', 'AAPL', 50, 160.0)")

# 提交事务
conn.commit()

# 查询数据
cursor.execute('SELECT * FROM stocks')
rows = cursor.fetchall()
for row in rows:
    print(row)

# 更新数据
cursor.execute("UPDATE stocks SET price = 155.0 WHERE symbol = 'AAPL' AND trans = 'BUY'")
conn.commit()
print("更新行数:", cursor.rowcount)

# 删除数据
cursor.execute("DELETE FROM stocks WHERE symbol = 'MSFT'")
conn.commit()
print("删除行数:", cursor.rowcount)

# 关闭游标和连接
cursor.close()
conn.close()

6.4 移动应用

SQLite在移动应用开发中非常流行，如Android和iOS应用：

Android示例

// 在Android中使用SQLite
public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "mydatabase.db";
    private static final int DATABASE_VERSION = 1;
    
    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    
    @Override
    public void onCreate(SQLiteDatabase db) {
        // 创建表
        db.execSQL("CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT)");
    }
    
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 升级数据库
        db.execSQL("DROP TABLE IF EXISTS users");
        onCreate(db);
    }
    
    // 插入数据
    public long insertUser(String name, String email) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name", name);
        values.put("email", email);
        return db.insert("users", null, values);
    }
    
    // 查询数据
    public List<User> getAllUsers() {
        List<User> users = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM users", null);
        
        if (cursor.moveToFirst()) {
            do {
                User user = new User();
                user.setId(cursor.getInt(0));
                user.setName(cursor.getString(1));
                user.setEmail(cursor.getString(2));
                users.add(user);
            } while (cursor.moveToNext());
        }
        
        cursor.close();
        return users;
    }
}

7. 总结

SQLite是一种轻量级、自包含、零配置的嵌入式关系型数据库管理系统，特别适合嵌入式应用、移动应用、小型网站等场景。它的设计理念是简单、高效、可靠，提供了完整的SQL支持和事务处理能力。

通过本教程的学习，读者应该能够：

  1. 理解SQLite的核心概念和特点
  2. 掌握SQLite的安装和基本配置方法
  3. 熟练使用SQLite进行数据库操作和查询
  4. 了解SQLite的高级功能和应用场景
  5. 掌握SQLite的性能调优和安全措施
  6. 能够在实际项目中应用SQLite解决数据存储问题

SQLite作为一种成熟的嵌入式数据库，具有广泛的应用前景和丰富的生态系统。它的简单性和可靠性使其成为许多应用程序的理想选择，特别是在资源受限的环境中。随着移动应用和嵌入式设备的普及，SQLite的重要性将会继续增长。

« 上一篇 MySQL数据库教程 下一篇 » Neo4j图数据库教程
↑