SQLite 数据库基本操作指南

63 views Posted on 2025-09-02 by 林坤源

SQLite是一个轻量级、零配置的关系型数据库管理系统，广泛应用于嵌入式设备和小型应用中。下面将介绍SQLite的基本操作，包括权限管理、数据操作和高级功能。

1. 创建角色和分配权限

在SQLite中，没有像企业级数据库那样完整的角色权限系统，但我们可以通过一些方式模拟类似功能：

-- 创建用户表（模拟角色系统）
CREATE TABLE IF NOT EXISTS roles (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    permissions TEXT NOT NULL
);

-- 创建用户表
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    role_id INTEGER,
    FOREIGN KEY (role_id) REFERENCES roles (id)
);

-- 插入角色数据
INSERT INTO roles (name, permissions) VALUES 
('admin', 'select,insert,update,delete,create,drop'),
('user', 'select,insert,update'),
('guest', 'select');

2. 创建用户并分配角色

-- 创建用户并分配角色
INSERT INTO users (username, password, role_id) VALUES
('john_doe', 'password123', (SELECT id FROM roles WHERE name = 'admin')),
('jane_smith', 'securepass', (SELECT id FROM roles WHERE name = 'user')),
('bob_wilson', 'guestpass', (SELECT id FROM roles WHERE name = 'guest'));

3. 回收角色/用户权限

由于SQLite没有内置的权限回收机制，我们需要通过更新操作来模拟：

-- 回收角色的某些权限
UPDATE roles SET permissions = 'select,insert' WHERE name = 'user';

-- 更改用户的角色（降权）
UPDATE users SET role_id = (SELECT id FROM roles WHERE name = 'guest') 
WHERE username = 'bob_wilson';

-- 删除用户（彻底回收所有权限）
DELETE FROM users WHERE username = 'bob_wilson';

4. 基本增删改查操作

首先创建一个示例表：

-- 创建员工表
CREATE TABLE IF NOT EXISTS employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department TEXT NOT NULL,
    salary REAL,
    hire_date TEXT
);

增加数据

INSERT INTO employees (name, department, salary, hire_date) VALUES
('张三', '技术部', 8500.00, '2022-03-15'),
('李四', '市场部', 7500.00, '2021-08-22'),
('王五', '技术部', 9200.00, '2020-05-10');

查询数据

-- 查询所有员工
SELECT * FROM employees;

-- 条件查询
SELECT name, salary FROM employees WHERE department = '技术部';

-- 排序查询
SELECT * FROM employees ORDER BY salary DESC;

-- 聚合查询
SELECT department, AVG(salary) as avg_salary 
FROM employees 
GROUP BY department;

更新数据

-- 给技术部员工加薪10%
UPDATE employees 
SET salary = salary * 1.1 
WHERE department = '技术部';

-- 修改特定员工信息
UPDATE employees 
SET department = '人事部', salary = 8000.00 
WHERE name = '李四';

删除数据

-- 删除特定员工
DELETE FROM employees WHERE name = '王五';

-- 清空表
DELETE FROM employees;

5. 创建视图

视图是基于SQL语句的虚拟表，可以简化复杂查询：

-- 创建高薪员工视图
CREATE VIEW high_salary_employees AS
SELECT id, name, department, salary 
FROM employees 
WHERE salary > 8000;

-- 使用视图查询
SELECT * FROM high_salary_employees ORDER BY salary DESC;

-- 部门薪资统计视图
CREATE VIEW department_stats AS
SELECT 
    department, 
    COUNT(*) as employee_count,
    AVG(salary) as average_salary,
    MAX(salary) as max_salary,
    MIN(salary) as min_salary
FROM employees 
GROUP BY department;

6. 创建存储过程（SQLite中的等价物）

SQLite不支持传统存储过程，但可以使用事务和自定义函数来模拟类似功能：

-- 开始一个事务（模拟存储过程）
BEGIN TRANSACTION;

-- 执行多个操作
INSERT INTO employees (name, department, salary, hire_date) 
VALUES ('赵六', '财务部', 7800.00, date('now'));

UPDATE employees SET salary = salary * 1.05 WHERE department = '财务部';

-- 提交事务
COMMIT;

-- 创建自定义函数（需要在应用程序层面实现）
-- 例如在Python中使用SQLite时：
-- import sqlite3
-- conn = sqlite3.connect('company.db')
-- conn.create_function('calculate_bonus', 1, lambda salary: salary * 0.1)

7. 模式、实例等区别

在数据库领域中，这些概念有特定含义：

模式(Schema)

  • 模式是数据库的结构蓝图，定义了表、视图、索引等数据库对象
  • 在SQLite中，模式信息存储在sqlite_master系统表中
  • 查看模式信息：SELECT * FROM sqlite_master WHERE type='table';

实例(Instance)

  • 实例是运行中的数据库管理系统及其内存结构
  • 在SQLite中，每个打开的数据库连接可以视为一个实例
  • SQLite是服务器less数据库，实例与应用程序进程紧密相关

数据库(Database)

  • 数据库是存储数据的容器，在SQLite中通常是一个单一文件
  • 一个SQLite实例可以同时访问多个数据库文件

表(Table)

  • 表是数据的结构化表示，由行和列组成
  • 是模式的主要组成部分

区别总结

  • 模式是设计时概念，定义数据结构
  • 实例是运行时概念，表示数据库的运行状态
  • 数据库是物理存储概念，是数据的持久化容器
  • 表是数据组织概念，是模式的具体组成部分

在SQLite中，这些概念相对简化，因为SQLite设计目标是轻量级和嵌入式使用，不像企业级数据库那样有严格的分离。

总结

SQLite虽然是一个轻量级数据库，但仍然提供了丰富的功能来处理大多数常见的数据管理任务。通过本文介绍的基本操作，您可以开始使用SQLite进行数据存储和管理。需要注意的是，SQLite在某些高级功能（如完整的权限管理系统）上可能不如企业级数据库强大，但对于许多应用场景来说已经足够。

对于更复杂的需求，可以考虑在应用程序层面实现额外的逻辑，或者考虑使用更强大的数据库系统如PostgreSQL或MySQL。

This entry was posted in 应用. Bookmark the permalink.