SQLite数据库管理操作详解

SQLite 数据库基础操作

SQLite 是一款轻型的嵌入式数据库，它不需要独立的服务器进程，直接读写数据库文件。SQLite 广泛应用于移动应用、桌面应用以及一些小型服务器应用中。

创建数据库

在 SQLite 中，创建数据库实际上就是创建一个数据库文件。可以使用 SQLite 的命令行工具或者通过编程语言的 SQLite 接口来完成。

使用命令行工具创建数据库 在命令行中，进入 SQLite 命令行工具，执行以下命令：

sqlite3 mydatabase.db

这会在当前目录下创建一个名为 mydatabase.db 的 SQLite 数据库文件。如果该文件已存在，则会直接打开它。

使用 Python 创建数据库

import sqlite3

# 连接到数据库，如果数据库不存在则创建
conn = sqlite3.connect('mydatabase.db')
conn.close()

在上述 Python 代码中，sqlite3.connect() 方法如果指定的数据库文件不存在，就会创建一个新的数据库文件。

创建表

表是数据库中存储数据的基本结构。在 SQLite 中，创建表使用 CREATE TABLE 语句。

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    email TEXT UNIQUE
);

在这个例子中：

• users 是表名。
• id 列是一个自增的整数类型，作为主键，保证每一行数据的唯一性。
• name 列是文本类型，且不允许为空。
• age 列是整数类型。
• email 列是文本类型，且值必须唯一。

使用 Python 创建表

import sqlite3

conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()

cursor.execute('''
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    email TEXT UNIQUE
)
''')

conn.commit()
conn.close()

在 Python 代码中，通过 cursor.execute() 方法执行 SQL 语句来创建表，conn.commit() 用于提交事务，确保表的创建操作被持久化。

插入数据

向表中插入数据使用 INSERT INTO 语句。有几种常见的插入方式。

插入单条记录

INSERT INTO users (name, age, email)
VALUES ('Alice', 25, 'alice@example.com');

这里明确指定了要插入数据的列 name、age 和 email，并对应给出了值。

插入多条记录

INSERT INTO users (name, age, email)
VALUES 
('Bob', 30, 'bob@example.com'),
('Charlie', 35, 'charlie@example.com');

可以一次性插入多条记录，通过逗号分隔不同的记录值。

使用 Python 插入数据

import sqlite3

conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()

# 插入单条记录
cursor.execute("INSERT INTO users (name, age, email) VALUES ('Alice', 25, 'alice@example.com')")

# 插入多条记录
data = [
    ('Bob', 30, 'bob@example.com'),
    ('Charlie', 35, 'charlie@example.com')
]
cursor.executemany("INSERT INTO users (name, age, email) VALUES (?,?,?)", data)

conn.commit()
conn.close()

在 Python 代码中，cursor.execute() 用于插入单条记录，cursor.executemany() 用于批量插入记录。? 是占位符，用于传递参数，避免 SQL 注入风险。

查询数据

查询数据是数据库操作中最常用的操作之一，使用 SELECT 语句。

查询所有记录

SELECT * FROM users;

这会返回 users 表中的所有列和所有行的数据。

查询指定列

SELECT name, email FROM users;

只返回 name 和 email 列的数据。

条件查询

SELECT * FROM users WHERE age > 30;

这会返回年龄大于 30 岁的用户记录。

排序查询

SELECT * FROM users ORDER BY age DESC;

按照年龄降序排列返回用户记录。如果要升序排列，可以使用 ASC，默认也是升序。

使用 Python 查询数据

import sqlite3

conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()

# 查询所有记录
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

# 查询指定列
cursor.execute("SELECT name, email FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

# 条件查询
cursor.execute("SELECT * FROM users WHERE age > 30")
rows = cursor.fetchall()
for row in rows:
    print(row)

# 排序查询
cursor.execute("SELECT * FROM users ORDER BY age DESC")
rows = cursor.fetchall()
for row in rows:
    print(row)

conn.close()

在 Python 代码中，cursor.execute() 执行查询语句，cursor.fetchall() 获取所有查询结果，然后通过循环遍历打印每一行数据。

SQLite 数据库高级操作

更新数据

更新表中的数据使用 UPDATE 语句。

UPDATE users
SET age = age + 1
WHERE name = 'Alice';

这条语句将名字为 Alice 的用户年龄增加 1。

使用 Python 更新数据

import sqlite3

conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()

cursor.execute("UPDATE users SET age = age + 1 WHERE name = 'Alice'")
conn.commit()
conn.close()

在 Python 代码中，通过 cursor.execute() 执行更新语句，conn.commit() 提交事务使更新生效。

删除数据

删除表中的数据使用 DELETE FROM 语句。

DELETE FROM users WHERE email = 'bob@example.com';

这条语句会删除邮箱为 bob@example.com 的用户记录。

使用 Python 删除数据

import sqlite3

conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()

cursor.execute("DELETE FROM users WHERE email = 'bob@example.com'")
conn.commit()
conn.close()

在 Python 代码中，同样通过 cursor.execute() 执行删除语句，conn.commit() 提交事务完成删除操作。

事务处理

事务是一组数据库操作，这些操作要么全部成功执行，要么全部失败回滚。SQLite 支持事务处理。

使用 SQL 语句处理事务

BEGIN TRANSACTION;
UPDATE users SET age = age + 1 WHERE name = 'Alice';
INSERT INTO users (name, age, email) VALUES ('David', 28, 'david@example.com');
COMMIT;

在这个事务中，先更新了 Alice 的年龄，然后插入了一条新的用户记录。如果在执行过程中出现错误，例如插入的 email 不唯一（违反了唯一性约束），整个事务会回滚，之前的更新操作也不会生效。

使用 Python 处理事务

import sqlite3

conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()

try:
    conn.execute('BEGIN')
    cursor.execute("UPDATE users SET age = age + 1 WHERE name = 'Alice'")
    cursor.execute("INSERT INTO users (name, age, email) VALUES ('David', 28, 'david@example.com')")
    conn.execute('COMMIT')
except sqlite3.Error as e:
    print(f"事务处理错误: {e}")
    conn.execute('ROLLBACK')
finally:
    conn.close()

在 Python 代码中，通过 try - except - finally 块来处理事务。如果执行过程中出现 sqlite3.Error，则回滚事务，确保数据的一致性。

索引管理

索引可以加快数据的查询速度。在 SQLite 中，可以创建不同类型的索引。

创建普通索引

CREATE INDEX idx_email ON users (email);

这条语句在 users 表的 email 列上创建了一个名为 idx_email 的索引。

创建唯一索引

CREATE UNIQUE INDEX idx_unique_email ON users (email);

此语句创建了一个唯一索引，确保 email 列的值不仅在查询时能快速定位，而且保证唯一性。

删除索引

DROP INDEX idx_email;

使用 DROP INDEX 语句可以删除指定的索引。

使用 Python 管理索引

import sqlite3

conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()

# 创建普通索引
cursor.execute("CREATE INDEX idx_email ON users (email)")

# 创建唯一索引
cursor.execute("CREATE UNIQUE INDEX idx_unique_email ON users (email)")

# 删除索引
cursor.execute("DROP INDEX idx_email")

conn.commit()
conn.close()

在 Python 代码中，通过 cursor.execute() 执行创建和删除索引的 SQL 语句，并通过 conn.commit() 提交事务。

SQLite 数据库的连接管理

连接字符串

在不同的编程语言中，连接 SQLite 数据库时可能需要用到连接字符串。在 Python 中，连接字符串就是数据库文件名。

import sqlite3

# 连接到数据库
conn = sqlite3.connect('mydatabase.db')

而在 Java 中，使用 JDBC 连接 SQLite 时，连接字符串格式如下：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class SQLiteConnection {
    public static void main(String[] args) {
        String url = "jdbc:sqlite:mydatabase.db";
        try (Connection conn = DriverManager.getConnection(url)) {
            if (conn != null) {
                System.out.println("已连接到 SQLite 数据库");
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
}

这里的 jdbc:sqlite:mydatabase.db 就是连接字符串，它指定了使用 SQLite 驱动（jdbc:sqlite: 前缀）以及要连接的数据库文件 mydatabase.db。

连接池

在多线程或高并发的应用场景中，频繁地创建和关闭数据库连接会带来性能开销。连接池可以解决这个问题，它预先创建一定数量的数据库连接，并将这些连接保存在池中。当应用需要数据库连接时，从池中获取一个连接，使用完毕后再放回池中。

在 Python 中，可以使用 sqlite3 模块结合 queue 模块来实现简单的连接池。以下是一个简单的示例：

import sqlite3
from queue import Queue
from threading import Thread

class SQLiteConnectionPool:
    def __init__(self, db_path, pool_size):
        self.db_path = db_path
        self.pool = Queue(maxsize = pool_size)
        for _ in range(pool_size):
            self.pool.put(sqlite3.connect(db_path))

    def get_connection(self):
        return self.pool.get()

    def return_connection(self, conn):
        self.pool.put(conn)

# 使用连接池
pool = SQLiteConnectionPool('mydatabase.db', 5)

def worker():
    conn = pool.get_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    rows = cursor.fetchall()
    for row in rows:
        print(row)
    pool.return_connection(conn)

threads = []
for _ in range(10):
    t = Thread(target = worker)
    threads.append(t)
    t.start()

for t in threads:
    t.join()

在上述代码中，SQLiteConnectionPool 类实现了一个简单的连接池。__init__ 方法初始化连接池，创建指定数量的 SQLite 连接并放入队列。get_connection 方法从队列中获取一个连接，return_connection 方法将使用完的连接放回队列。

在 Java 中，可以使用第三方库如 HikariCP 来实现高效的连接池。以下是一个简单的使用示例：

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class SQLiteHikariCPExample {
    private static HikariDataSource dataSource;

    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:sqlite:mydatabase.db");
        config.setMaximumPoolSize(5);
        dataSource = new HikariDataSource(config);
    }

    public static void main(String[] args) {
        try (Connection conn = dataSource.getConnection()) {
            String sql = "SELECT * FROM users";
            try (PreparedStatement pstmt = conn.prepareStatement(sql);
                 ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    System.out.println(rs.getString("name"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

在这个 Java 示例中，使用 HikariCP 库来创建 SQLite 数据库连接池。HikariConfig 配置连接字符串和最大连接数等参数，HikariDataSource 根据配置创建数据源。通过 dataSource.getConnection() 获取连接并执行数据库操作。

SQLite 数据库的备份与恢复

备份数据库

SQLite 本身没有内置的直接备份命令，但可以通过一些方法来实现备份。一种常见的方法是在操作系统层面直接复制数据库文件。例如，在 Linux 系统中，可以使用 cp 命令：

cp mydatabase.db mydatabase_backup.db

这种方法简单直接，但在数据库处于使用状态时可能会出现问题，因为 SQLite 数据库文件可能正在被读写，复制操作可能会导致数据不一致。

另一种方法是使用 SQLite 的 backup 机制。在 SQLite 命令行工具中，可以这样操作：

.open mydatabase.db
.backup mydatabase_backup.db

在这个例子中，先打开源数据库 mydatabase.db，然后使用 .backup 命令将其备份到 mydatabase_backup.db 文件。

使用 Python 备份数据库

import sqlite3

def backup_database(src_db, dst_db):
    source_conn = sqlite3.connect(src_db)
    source_conn.execute('PRAGMA wal_autocheckpoint = 0')
    source_conn.execute('PRAGMA synchronous = OFF')
    dest_conn = sqlite3.connect(dst_db)
    source_conn.backup(dest_conn)
    source_conn.close()
    dest_conn.close()

backup_database('mydatabase.db','mydatabase_backup.db')

在 Python 代码中，通过 sqlite3.connect() 分别连接源数据库和目标数据库，然后使用 source_conn.backup(dest_conn) 方法进行备份。在备份前设置了一些 PRAGMA 参数来提高备份效率，PRAGMA wal_autocheckpoint = 0 关闭 WAL 模式下的自动检查点，PRAGMA synchronous = OFF 降低同步级别，减少 I/O 操作。

恢复数据库

恢复数据库可以简单地将备份文件覆盖原数据库文件。在 Linux 系统中：

cp mydatabase_backup.db mydatabase.db

同样，这种方法在数据库使用时可能会有问题。如果使用 SQLite 的 backup 机制备份的数据库，可以使用类似的方法恢复，即将备份数据库作为源，原数据库作为目标进行反向备份。

使用 Python 恢复数据库

import sqlite3

def restore_database(src_db, dst_db):
    source_conn = sqlite3.connect(src_db)
    source_conn.execute('PRAGMA wal_autocheckpoint = 0')
    source_conn.execute('PRAGMA synchronous = OFF')
    dest_conn = sqlite3.connect(dst_db)
    source_conn.backup(dest_conn)
    source_conn.close()
    dest_conn.close()

restore_database('mydatabase_backup.db','mydatabase.db')

在 Python 代码中，恢复操作和备份操作类似，只是源数据库和目标数据库的角色互换。同样设置了 PRAGMA 参数来提高恢复效率。

SQLite 数据库的性能优化

合理使用索引

索引是提高查询性能的重要手段，但过多或不合理的索引也会带来性能开销。在创建索引时，需要根据实际的查询需求来决定。例如，如果经常根据 age 列进行范围查询，那么在 age 列上创建索引会提高查询速度。

CREATE INDEX idx_age ON users (age);

但如果索引列的数据分布很不均匀，例如某一列 90% 的值都是相同的，那么这个索引对查询性能的提升可能不大，反而会增加插入、更新和删除操作的开销，因为每次数据变动都需要更新索引。

优化查询语句

编写高效的查询语句对性能至关重要。避免使用 SELECT *，尽量只选择需要的列，这样可以减少数据传输和处理的开销。例如：

-- 不好的写法
SELECT * FROM users;

-- 好的写法
SELECT name, age FROM users;

另外，在条件查询中，避免在索引列上使用函数。例如：

-- 不好的写法，函数操作会使索引失效
SELECT * FROM users WHERE UPPER(name) = 'ALICE';

-- 好的写法，将函数操作移到参数上
SELECT * FROM users WHERE name = UPPER('alice');

事务优化

在进行批量插入、更新或删除操作时，使用事务可以大大提高性能。将多个操作放在一个事务中，而不是每个操作都单独提交，这样可以减少磁盘 I/O 操作。例如，在 Python 中：

import sqlite3

conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()

data = [
    ('Eve', 22, 'eve@example.com'),
    ('Frank', 27, 'frank@example.com')
]

try:
    conn.execute('BEGIN')
    for item in data:
        cursor.execute("INSERT INTO users (name, age, email) VALUES (?,?,?)", item)
    conn.execute('COMMIT')
except sqlite3.Error as e:
    print(f"事务处理错误: {e}")
    conn.execute('ROLLBACK')
finally:
    conn.close()

在这个例子中，批量插入操作放在一个事务中，只有当所有插入都成功时才提交事务，减少了多次提交带来的性能开销。

SQLite 的配置参数优化

SQLite 提供了一些配置参数（PRAGMA）可以优化性能。例如，PRAGMA synchronous 可以控制 SQLite 如何同步数据到磁盘。

• PRAGMA synchronous = OFF：最快但不安全的模式，数据写入内存后就返回，不保证数据持久化，可能在系统崩溃时丢失数据。
• PRAGMA synchronous = NORMAL：默认模式，在数据写入磁盘后返回，保证一定的数据安全性。
• PRAGMA synchronous = FULL：最安全但最慢的模式，确保数据完全写入磁盘。

在性能敏感的场景中，可以根据需求设置为 NORMAL 甚至 OFF。例如：

PRAGMA synchronous = NORMAL;

另外，PRAGMA journal_mode 可以设置日志模式，不同的日志模式对性能和数据恢复能力有不同影响。

• PRAGMA journal_mode = DELETE：默认模式，事务完成后删除日志文件。
• PRAGMA journal_mode = TRUNCATE：事务完成后截断日志文件。
• PRAGMA journal_mode = PERSIST：日志文件一直存在，只在需要时覆盖。
• PRAGMA journal_mode = MEMORY：日志存储在内存中，性能较高但系统崩溃时无法恢复未提交的事务。
• PRAGMA journal_mode = WAL：Write - Ahead Logging 模式，允许多个读操作并发执行，适用于读多写少的场景。

例如，在读多写少的场景中，可以设置为 WAL 模式：

PRAGMA journal_mode = WAL;

SQLite 数据库与其他技术的集成

SQLite 与 Web 应用集成

在 Web 应用开发中，SQLite 可以作为后端数据库。例如，在使用 Python 的 Flask 框架开发 Web 应用时，可以集成 SQLite。

from flask import Flask, jsonify
import sqlite3

app = Flask(__name__)

@app.route('/users')
def get_users():
    conn = sqlite3.connect('mydatabase.db')
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    rows = cursor.fetchall()
    users = []
    for row in rows:
        user = {
            'id': row[0],
            'name': row[1],
            'age': row[2],
            'email': row[3]
        }
        users.append(user)
    conn.close()
    return jsonify(users)

if __name__ == '__main__':
    app.run(debug = True)

在这个 Flask 应用中，定义了一个 /users 路由，当访问该路由时，从 SQLite 数据库中查询所有用户数据，并以 JSON 格式返回。

SQLite 与移动应用集成

SQLite 在移动应用开发中广泛应用。例如，在 Android 开发中，使用 SQLiteOpenHelper 类来管理 SQLite 数据库。

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class MyDatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "mydatabase.db";
    private static final int DATABASE_VERSION = 1;

    public MyDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String createTable = "CREATE TABLE users ( " +
                "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "name TEXT NOT NULL, " +
                "age INTEGER, " +
                "email TEXT UNIQUE )";
        db.execSQL(createTable);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS users");
        onCreate(db);
    }
}

在这个 Android 示例中，MyDatabaseHelper 类继承自 SQLiteOpenHelper，用于创建和管理 SQLite 数据库。onCreate 方法在数据库首次创建时执行，用于创建表结构。onUpgrade 方法在数据库版本更新时执行，这里简单地删除旧表并重新创建。

SQLite 与数据分析工具集成

SQLite 也可以与数据分析工具集成。例如，在 Python 中使用 pandas 库可以方便地读取和处理 SQLite 数据库中的数据。

import pandas as pd
import sqlite3

conn = sqlite3.connect('mydatabase.db')
query = "SELECT * FROM users"
df = pd.read_sql(query, conn)
print(df)
conn.close()

在这个例子中，pandas 的 read_sql 方法从 SQLite 数据库中读取数据并创建一个 DataFrame，方便进行数据分析和处理。