Sqlite数据库表的操作有哪些

发布时间：2021-10-09 17:20:01 作者：iii
来源：亿速云阅读：151

# SQLite数据库表的操作有哪些

SQLite作为一款轻量级的关系型数据库，因其零配置、无服务器、单文件存储等特性被广泛应用于移动端和嵌入式场景。本文将全面介绍SQLite中表操作的各类方法，包含基础操作、高级特性及最佳实践。

## 目录
1. [表的基本操作](#表的基本操作)
   - 创建表
   - 删除表
   - 修改表结构
2. [数据操作语言(DML)](#数据操作语言dml)
   - 插入数据
   - 查询数据
   - 更新数据
   - 删除数据
3. [表约束与索引](#表约束与索引)
   - 主键约束
   - 唯一约束
   - 外键约束
   - 创建索引
4. [高级表操作](#高级表操作)
   - 事务处理
   - 视图创建
   - 触发器使用
5. [性能优化建议](#性能优化建议)
6. [常见问题解决方案](#常见问题解决方案)

---

## 表的基本操作

### 创建表
```sql
CREATE TABLE [IF NOT EXISTS] 表名 (
    列1 数据类型 [约束条件],
    列2 数据类型 [约束条件],
    ...
    [表级约束]
);

示例：

CREATE TABLE employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER CHECK(age >= 18),
    department_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

关键参数说明： - IF NOT EXISTS：避免重复创建报错 - 数据类型：INTEGER, TEXT, REAL, BLOB, NUMERIC等 - 约束条件：PRIMARY KEY, NOT NULL, UNIQUE等

删除表

DROP TABLE [IF EXISTS] 表名;

注意： - 删除表会同时删除所有关联的索引和触发器 - 建议先备份重要数据

修改表结构

SQLite的ALTER TABLE功能有限，仅支持：

-- 重命名表
ALTER TABLE 旧表名 RENAME TO 新表名;

-- 添加列（SQLite 3.25.0+支持DROP COLUMN）
ALTER TABLE 表名 ADD COLUMN 列名 数据类型;

复杂修改方案： 1. 创建新表 2. 迁移数据 3. 删除旧表 4. 重命名新表

数据操作语言(DML)

插入数据

-- 完整插入
INSERT INTO 表名 VALUES (值1, 值2,...);

-- 指定列插入
INSERT INTO 表名 (列1, 列2) VALUES (值1, 值2);

-- 批量插入
INSERT INTO 表名 (列1, 列2) 
VALUES 
    (值1, 值2),
    (值3, 值4);

查询数据

-- 基础查询
SELECT 列1, 列2 FROM 表名 WHERE 条件;

-- 排序分组
SELECT department, COUNT(*) 
FROM employees 
GROUP BY department 
ORDER BY COUNT(*) DESC;

-- 多表连接
SELECT e.name, d.department_name 
FROM employees e 
JOIN departments d ON e.department_id = d.id;

更新数据

UPDATE 表名 
SET 列1 = 新值1, 列2 = 新值2 
WHERE 条件;

删除数据

DELETE FROM 表名 WHERE 条件;

表约束与索引

主键约束

-- 单列主键
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT
);

-- 复合主键
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    PRIMARY KEY (order_id, product_id)
);

唯一约束

CREATE TABLE users (
    email TEXT UNIQUE,
    username TEXT UNIQUE
);

外键约束

需先启用外键支持：

PRAGMA foreign_keys = ON;

示例：

CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    department_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

创建索引

-- 单列索引
CREATE INDEX idx_employee_name ON employees(name);

-- 复合索引
CREATE INDEX idx_employee_dept ON employees(department_id, hire_date);

-- 唯一索引
CREATE UNIQUE INDEX idx_unique_email ON users(email);

索引使用建议： 1. 为频繁查询的列创建索引 2. 为JOIN操作的关联列创建索引 3. 避免过度索引（影响写入性能）

高级表操作

事务处理

BEGIN TRANSACTION;
-- 执行SQL操作
COMMIT;  -- 或 ROLLBACK;

自动提交模式： SQLite默认每条SQL语句都在独立事务中执行

视图创建

CREATE VIEW active_employees AS
SELECT * FROM employees WHERE status = 'active';

触发器使用

CREATE TRIGGER update_timestamp
AFTER UPDATE ON employees
BEGIN
    UPDATE employees SET last_updated = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

性能优化建议

合理使用事务：批量操作时显式使用事务
索引策略：分析查询模式后建立合适索引
预编译语句：重复查询使用参数化语句
数据类型选择：严格匹配数据特征
分页查询：大数据集使用LIMIT/OFFSET

-- 分页示例
SELECT * FROM large_table LIMIT 10 OFFSET 20;

常见问题解决方案

1. 表锁问题

错误：database is locked
解决方案：
- 减少长事务
- 优化查询性能
- 使用WAL模式

2. 数据类型转换

SQLite使用动态类型系统，建议： - 显式指定数据类型 - 使用CAST函数转换类型

3. 性能瓶颈排查

-- 启用执行时间统计
.timer ON

-- 查看查询计划
EXPLN QUERY PLAN SELECT * FROM table;

通过掌握这些表操作技术，您可以高效地管理和优化SQLite数据库。建议结合具体业务场景选择合适的操作方式，并定期进行数据库维护以保证最佳性能。 “`

注：本文实际约3000字，完整4000字版本可扩展以下内容： 1. 更多实战案例（含完整SQL示例） 2. 各操作的性能对比数据 3. 与其它数据库的语法差异说明 4. 特定语言（Python/Java等）的操作示例 5. 数据库设计规范建议