异常教程

SQLite 常用函数（超详细）

更新时间:

💡一则或许对你有用的小广告

欢迎加入小哈的星球 ，你将获得：专属的项目实战 / 1v1 提问 / Java 学习路线 / 学习打卡 / 每月赠书 / 社群讨论

  • 新项目:《从零手撸：仿小红书（微服务架构）》 正在持续爆肝中，基于 Spring Cloud Alibaba + Spring Boot 3.x + JDK 17...，点击查看项目介绍 ;
  • 《从零手撸：前后端分离博客项目（全栈开发）》 2 期已完结，演示链接： http://116.62.199.48/ ;

截止目前， 星球 内专栏累计输出 82w+ 字，讲解图 3441+ 张，还在持续爆肝中.. 后续还会上新更多项目，目标是将 Java 领域典型的项目都整一波，如秒杀系统, 在线商城, IM 即时通讯，权限管理，Spring Cloud Alibaba 微服务等等，已有 2900+ 小伙伴加入学习 ，欢迎点击围观

前言

在数据库开发中，函数是提升数据处理效率的核心工具。SQLite 作为轻量级、高性能的关系型数据库，提供了丰富的内置函数，能够帮助开发者快速完成数据清洗、计算和转换。无论是处理字符串、数值，还是操作日期时间，SQLite 的函数体系都能提供灵活且高效的解决方案。本文将从基础到进阶，系统性地讲解 SQLite 常用函数，并通过实际案例展示其应用场景，帮助读者快速掌握这些工具的使用方法。

一、字符串函数：数据的“文字编辑器”

字符串函数是处理文本数据的“瑞士军刀”，适用于拼接、截取、替换等操作。想象一下，字符串就像一块布料，而函数就是裁剪、缝纫的工具，能精准完成对字符的加工。

1. substr()：按位置截取子字符串

substr(text, start, length) 函数从指定位置开始截取字符串，常用于提取固定长度的数据。例如，从身份证号中提取出生年份：

SELECT substr('110105199003072315', 7, 4); -- 结果：1990

注意：SQLite 的索引从 1 开始，而部分数据库（如 PostgreSQL）从 0 开始，需特别留意。

2. replace()：批量替换字符

replace(text, old_str, new_str) 可替换字符串中的指定内容。例如，将文本中的英文逗号替换为中文顿号：

SELECT replace('apple,banana,orange', ',', '、'); -- 结果：apple、banana、orange

3. length()：获取字符串长度

length(text) 返回字符数量，适用于验证输入长度。例如，检查用户名是否符合 6-12 位的要求：

SELECT CASE  
    WHEN length('user123') BETWEEN 6 AND 12 THEN '有效'  
    ELSE '无效'  
END; -- 结果：有效

二、数值函数：数学运算的“计算器”

数值函数是 SQLite 的“计算引擎”，能处理加减乘除、取整、随机数生成等需求。它们如同计算器的按键，让开发者快速完成复杂计算。

1. abs()：获取绝对值

abs(number) 返回数值的绝对值。例如，计算温度差的绝对值：

SELECT abs(-5.5); -- 结果：5.5

2. round()：四舍五入

round(number, decimal) 对数值进行四舍五入。例如，保留两位小数：

SELECT round(3.1415926, 2); -- 结果：3.14

3. random()：生成随机数

random() 生成一个随机整数，常用于测试或抽样场景。例如，随机选取 10% 的用户：

SELECT * FROM users WHERE random() % 10 = 0;

三、日期与时间函数：时间管理的“时钟”

日期时间函数是处理时间数据的“万年历”，支持格式化、计算间隔等操作。它们如同时间的转换器，能将日期转换为不同格式或计算两个时间的差值。

1. date()：基础日期操作

date(text, modifier) 可解析并操作日期。例如，获取下个月的第一天：

SELECT date('2023-10-15', '+1 month', 'start of month'); -- 结果：2023-11-01

2. strftime()：灵活的时间格式化

strftime(format, datetime) 根据指定格式解析或输出时间。例如，将 Unix 时间戳转为可读格式：

SELECT strftime('%Y-%m-%d %H:%M', 1717986917); -- 结果：2024-06-11 14:35

3. julianday()：计算时间差

julianday(datetime) 将日期转换为儒略日数，可用于计算两个时间的间隔。例如，计算出生日期与当前的年龄：

SELECT julianday('now') - julianday('1990-03-07'); -- 结果：11,840 天（约 32 年）

四、聚合函数：数据的“统计大师”

聚合函数是数据统计的“仪表盘”，能对多行数据进行汇总计算。它们如同统计员，将大量数据浓缩为关键指标，如总和、平均值或最大值。

1. sum()：计算总和

sum(column) 求某一列的数值总和。例如，统计某月订单的总销售额：

SELECT sum(amount) FROM orders WHERE order_date BETWEEN '2023-10-01' AND '2023-10-31';

2. avg()：计算平均值

avg(column) 计算数值的平均值。例如，计算班级平均分：

SELECT avg(score) FROM students WHERE class = '3A';

3. count()：统计行数

count(*) 统计总行数，而 count(column) 统计非空值的行数。例如，统计用户中未填写手机号的人数：

SELECT count(*) AS total_users, count(phone) AS users_with_phone FROM users;

五、条件函数：逻辑判断的“交通灯”

条件函数是控制数据流向的“开关”，通过判断条件返回不同结果，常见于复杂查询的逻辑分支。

1. CASE 表达式：多条件分支

CASE 类似编程中的 switch 语句，根据条件返回不同值。例如，根据分数划分等级：

SELECT name,  
    CASE  
        WHEN score >= 90 THEN 'A'  
        WHEN score >= 70 THEN 'B'  
        ELSE 'C'  
    END AS level  
FROM students;

2. IFNULL()：处理空值

IFNULL(expr, value) 若表达式为 NULL，则返回替代值。例如，显示默认头像路径：

SELECT IFNULL(avatar_url, '/default.jpg') AS avatar FROM users;

六、进阶函数：高级场景的“工具包”

以下函数适用于复杂场景，如正则表达式匹配或复杂数据转换，适合中级开发者深入掌握。

1. printf()：格式化输出

printf(format, ...) 类似 C 语言的 printf，用于组合字符串。例如，生成用户欢迎语：

SELECT printf('欢迎，%s！您的余额为 %.2f 元。', name, balance) AS message FROM users;

2. regexp()：正则表达式匹配（需扩展）

SQLite 原生不支持正则函数，但可通过扩展（如 sqlite3_routines）实现。例如，筛选邮箱格式：

SELECT * FROM users WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';

结论

SQLite 的 常用函数 构成了数据处理的核心工具集，从基础的字符串操作到复杂的统计分析，都能通过这些函数高效完成。通过本文的案例与代码示例，读者可以快速掌握函数的使用逻辑，并结合实际项目进行实践。建议读者在开发中多尝试组合函数，例如将 CASE 与聚合函数结合，或用 strftime() 格式化时间，逐步提升数据处理能力。掌握这些函数，将使开发者在 SQLite 开发中更加得心应手，应对各种复杂场景。

最新发布

查看更多文章