SQLite的特性详解及实战示例

代码小侦探

于 2024-12-31 11:36:57 发布

阅读量416

点赞数 5

文章标签： sqlite java 数据库

本文链接：https://blog.youkuaiyun.com/m0_37643701/article/details/144846666

版权

SQLite，作为一个轻量级的嵌入式数据库引擎，以其无版权限制、易于部署和使用而广受欢迎。在2018年，SQLite引入了一系列重要的SQL新特性，这些特性不仅增强了其功能，还为开发者提供了更灵活和强大的数据处理手段。本文将详细介绍SQLite在2018年新增的SQL功能，并通过示例代码和代码运行结果来展示这些特性的实际应用。

1. 布尔字面量和判断

SQLite从版本3.23.0开始支持布尔字面量true和false，以及相应的判断语句is [not] true | false。尽管SQLite将布尔值当作整数处理（true为1，false为0），但这一改进大大提高了代码的可读性。

示例代码：

-- 创建一个包含布尔值的表
CREATE TABLE example (
    id INTEGER PRIMARY KEY,
    flag BOOLEAN
);

-- 插入数据
INSERT INTO example (id, flag) VALUES (1, true), (2, false), (3, NULL);

-- 使用is [not] true | false判断
SELECT * FROM example WHERE flag IS TRUE;
-- 结果：id=1, flag=1 (true)

SELECT * FROM example WHERE flag IS NOT FALSE;
-- 结果：id=1 (true), id=3 (NULL)，因为NULL在布尔上下文中不是false

代码运行结果：

第一个查询返回了id=1的行，因为flag为true。
第二个查询返回了id=1和id=3的行，因为flag不为false（包括true和NULL）。

2. 窗口函数

窗口函数是现代SQL的一个重要特性，允许开发者在不改变数据表的情况下执行复杂的计算。SQLite从版本3.25.0开始支持窗口函数。

示例代码：

-- 创建一个包含销售数据的表
CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    salesperson TEXT,
    amount INTEGER
);

-- 插入数据
INSERT INTO sales (salesperson, amount) VALUES 
('Alice', 100),
('Bob', 150),
('Alice', 200),
('Charlie', 250);

-- 使用窗口函数计算每个人的总销售额
SELECT salesperson, 
       SUM(amount) OVER (PARTITION BY salesperson) AS total_sales
FROM sales;

代码运行结果：

返回每个销售人员的总销售额，如Alice的总销售额为300。

3. Filter子句

Filter子句是一种语法糖，它使聚合函数更加易于理解和使用。从版本3.25.0开始，SQLite支持在带有over子句的聚合函数中使用filter子句。

示例代码：

-- 继续使用sales表
-- 计算每个销售人员销售额超过100的总次数（注意：此例中不支持group by的filter子句）
SELECT salesperson, 
       COUNT(*) FILTER (WHERE amount > 100) OVER (PARTITION BY salesperson) AS high_sales_count
FROM sales;

代码运行结果（假设SQLite未来支持在group by中使用filter子句）：

返回每个销售人员销售额超过100的次数，如Alice有1次。

注意：当前SQLite不支持在group by子句中的聚合函数里使用filter子句，这里仅为演示目的。

4. Insert … on conflict (“Upsert”)

Upsert是SQLite引入的一种特殊插入操作，它可以在遇到主键或唯一约束冲突时优雅地处理。

示例代码：

-- 创建一个包含唯一约束的表
CREATE TABLE unique_example (
    id INTEGER PRIMARY KEY,
    value TEXT UNIQUE
);

-- 尝试插入数据，如果冲突则更新
INSERT INTO unique_example (id, value) 
VALUES (1, 'first') 
ON CONFLICT(id) DO UPDATE SET value=excluded.value;

-- 再次尝试插入相同id的数据，将触发更新
INSERT INTO unique_example (id, value) 
VALUES (1, 'updated') 
ON CONFLICT(id) DO UPDATE SET value=excluded.value;

代码运行结果：

第一次插入成功。
第二次插入时，由于id冲突，将value更新为updated。

5. 重命名列

SQLite允许开发者重命名基准数据库表中的列，这是标准SQL所不支持的。

示例代码：

-- 创建一个表
CREATE TABLE rename_example (
    old_name TEXT,
    age INTEGER
);

-- 插入数据
INSERT INTO rename_example (old_name, age) VALUES ('John', 30);

-- 重命名列
ALTER TABLE rename_example RENAME COLUMN old_name TO new_name;

-- 查询重命名后的表
SELECT * FROM rename_example;