第五章 SQLite数据库:4、SQLite 进阶用法:常见的约束、PRAGMA 配置、数据操作
SQLite PRAGMA
PRAGMA 命令用于查询和设置 SQLite 数据库的环境配置,可以帮助管理数据库的行为和性能。
语法
- 查询 PRAGMA 值:
PRAGMA pragma_name;
- 设置 PRAGMA 值:
PRAGMA pragma_name = value;
常见 PRAGMA 示例
1. auto_vacuum Pragma
控制数据库的自动清理模式,当启用时,SQLite 会自动回收已删除数据占用的空间。
-- 获取当前 auto_vacuum 设置
PRAGMA auto_vacuum;-- 设置为 FULL 模式,启用自动回收
PRAGMA auto_vacuum = FULL;
2. cache_size Pragma
设置数据库的缓存大小,这直接影响查询性能。
-- 获取当前缓存大小(单位:页面数)
PRAGMA cache_size;-- 设置缓存大小为 2000 页
PRAGMA cache_size = 2000;
3. case_sensitive_like Pragma
控制 LIKE 操作符是否区分大小写。
-- 设置 LIKE 匹配大小写敏感
PRAGMA case_sensitive_like = true;
4. journal_mode Pragma
控制事务日志的存储方式,影响数据库的事务处理和恢复性能。
-- 获取当前日志模式
PRAGMA journal_mode;-- 设置为 WAL(Write-Ahead Logging)模式
PRAGMA journal_mode = WAL;
5. max_page_count Pragma
设置数据库允许的最大页面数,控制数据库文件的最大大小。
-- 获取当前最大页面数
PRAGMA max_page_count;-- 设置最大页数为 5000000
PRAGMA max_page_count = 5000000;
SQLite 约束
SQLite 支持多种约束类型,用于确保数据的完整性和一致性。
常见约束类型
1. NOT NULL 约束
确保列的值不能为空。
-- 创建 EMPLOYEES 表,确保每一列不能为空
CREATE TABLE EMPLOYEES (ID INTEGER PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INTEGER NOT NULL,SALARY REAL NOT NULL
);
2. DEFAULT 约束
为列提供默认值,当插入数据时未提供该列的值时,使用默认值。
-- 创建 EMPLOYEES 表,为 SALARY 列设置默认值
CREATE TABLE EMPLOYEES (ID INTEGER PRIMARY KEY,NAME TEXT NOT NULL,AGE INTEGER NOT NULL,SALARY REAL DEFAULT 30000.0 -- 默认工资为 30000
);
3. UNIQUE 约束
确保列中的每个值唯一。
-- 创建 EMPLOYEES 表,确保每个 NAME 值唯一
CREATE TABLE EMPLOYEES (ID INTEGER PRIMARY KEY,NAME TEXT UNIQUE NOT NULL,AGE INTEGER,SALARY REAL
);
4. PRIMARY KEY 约束
唯一标识表中的每一行。一个表只能有一个主键。
-- 创建 EMPLOYEES 表,ID 列为主键
CREATE TABLE EMPLOYEES (ID INTEGER PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INTEGER,SALARY REAL
);
5. CHECK 约束
确保列值满足指定的条件。
-- 创建 EMPLOYEES 表,确保 SALARY 大于 0
CREATE TABLE EMPLOYEES (ID INTEGER PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INTEGER NOT NULL,SALARY REAL CHECK(SALARY > 0)
);
删除约束
SQLite 不支持直接删除列约束。如果需要修改约束,可以通过创建新表并迁移数据来实现。
SQLite 综合使用案例
以下是一个 SQLite 的综合使用案例,展示了如何使用 PRAGMA
、创建表、插入数据、更新数据、查询数据以及应用不同约束:
-- 创建 PRODUCTS 表,包含多种约束
CREATE TABLE PRODUCTS (ID INTEGER PRIMARY KEY, -- 主键NAME TEXT NOT NULL, -- 名称不能为空CATEGORY TEXT NOT NULL, -- 类别不能为空PRICE REAL CHECK(PRICE > 0), -- 确保价格大于零QUANTITY INTEGER DEFAULT 0, -- 默认数量为零CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 默认当前时间
);-- 插入数据
INSERT INTO PRODUCTS (NAME, CATEGORY, PRICE, QUANTITY)
VALUES ('Laptop', 'Electronics', 1200.00, 10),('Smartphone', 'Electronics', 800.00, 25),('Coffee Maker', 'Home Appliances', 150.00, 5);-- 更新产品价格
UPDATE PRODUCTS SET PRICE = 1100.00 WHERE NAME = 'Laptop';-- 查询价格大于 500 的产品
SELECT * FROM PRODUCTS WHERE PRICE > 500;-- 查询库存大于等于 10 的产品
SELECT * FROM PRODUCTS WHERE QUANTITY >= 10;-- 删除库存小于 10 的产品
DELETE FROM PRODUCTS WHERE QUANTITY < 10;-- 查询所有数据
SELECT * FROM PRODUCTS;-- 获取数据库当前页数
PRAGMA page_count;-- 设置数据库缓存大小
PRAGMA cache_size = 1000;-- 获取数据库日志模式
PRAGMA journal_mode;
解释:
- 创建表:
PRODUCTS
表包含了PRIMARY KEY
、CHECK
、DEFAULT
和NOT NULL
等约束。 - 插入数据:插入了三条产品记录,每条记录的价格和库存数量都符合约束条件。
- 更新数据:更新了名为
Laptop
的产品价格。 - 查询数据:查询了价格大于 500 和库存大于等于 10 的产品。
- 删除数据:删除了库存小于 10 的产品记录。
- PRAGMA 使用:获取了当前数据库的页面数量,设置了缓存大小,查询了当前日志模式。