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。