印刻数据库

外观

SQLite 内置函数参考 ​

概述 ​

SQLite 提供了丰富的内置函数，用于处理字符串、数值、日期时间等数据类型。本文档详细介绍了 SQLite 的内置函数，包括函数语法、参数说明、返回值和使用示例，帮助开发人员更高效地使用 SQLite 进行数据处理和查询。

字符串函数 ​

基本字符串操作 ​

LENGTH(X) ​

返回字符串 X 的长度（字符数）。

语法：LENGTH(X)

参数：

  • X：要计算长度的字符串

返回值：整数，表示字符串的长度

示例：

sql
SELECT LENGTH('SQLite'); -- 返回 6
SELECT LENGTH(NULL); -- 返回 NULL

SUBSTR(X, Y, Z) ​

从字符串 X 中返回从位置 Y 开始、长度为 Z 的子字符串。

语法：SUBSTR(X, Y, Z)

参数：

  • X：源字符串
  • Y：起始位置（1-based），负数表示从末尾开始计数
  • Z：可选，子字符串长度，默认到字符串末尾

返回值：字符串，指定位置的子字符串

示例：

sql
SELECT SUBSTR('SQLite', 2, 3); -- 返回 'QLi'
SELECT SUBSTR('SQLite', -3); -- 返回 'ite'

UPPER(X) ​

将字符串 X 转换为大写。

语法：UPPER(X)

参数：

  • X：要转换的字符串

返回值：大写字符串

示例：

sql
SELECT UPPER('SQLite'); -- 返回 'SQLITE'

LOWER(X) ​

将字符串 X 转换为小写。

语法：LOWER(X)

参数：

  • X：要转换的字符串

返回值：小写字符串

示例：

sql
SELECT LOWER('SQLite'); -- 返回 'sqlite'

TRIM(X, Y) ​

去除字符串 X 两端的指定字符 Y，默认去除空格。

语法：TRIM(X, Y)

参数：

  • X：要处理的字符串
  • Y：可选，要去除的字符集，默认空格

返回值：去除指定字符后的字符串

示例：

sql
SELECT TRIM('  SQLite  '); -- 返回 'SQLite'
SELECT TRIM('xxSQLitexx', 'x'); -- 返回 'SQLite'

字符串连接与替换 ​

CONCAT(X, Y, ...) ​

连接多个字符串。

语法：CONCAT(X, Y, ...)

参数：

  • X, Y, ...：要连接的字符串

返回值：连接后的字符串

示例：

sql
SELECT CONCAT('SQL', 'ite'); -- 返回 'SQLite'
SELECT CONCAT('Hello', ' ', 'World'); -- 返回 'Hello World'

REPLACE(X, Y, Z) ​

将字符串 X 中的所有 Y 替换为 Z。

语法：REPLACE(X, Y, Z)

参数：

  • X：源字符串
  • Y：要替换的子字符串
  • Z：替换后的子字符串

返回值：替换后的字符串

示例：

sql
SELECT REPLACE('SQLite', 'ite', 'Database'); -- 返回 'SQLDatabase'

数值函数 ​

基本数值运算 ​

ABS(X) ​

返回 X 的绝对值。

语法：ABS(X)

参数：

  • X：数值表达式

返回值：X 的绝对值

示例：

sql
SELECT ABS(-123); -- 返回 123
SELECT ABS(45.6); -- 返回 45.6

ROUND(X, Y) ​

将 X 四舍五入到 Y 位小数，默认 Y=0。

语法：ROUND(X, Y)

参数：

  • X：要四舍五入的数值
  • Y：可选，小数位数，默认 0

返回值：四舍五入后的数值

示例：

sql
SELECT ROUND(3.14159); -- 返回 3
SELECT ROUND(3.14159, 2); -- 返回 3.14

CEIL(X) ​

返回大于或等于 X 的最小整数。

语法：CEIL(X)

参数：

  • X：数值表达式

返回值：大于或等于 X 的最小整数

示例：

sql
SELECT CEIL(3.14); -- 返回 4
SELECT CEIL(-3.14); -- 返回 -3

FLOOR(X) ​

返回小于或等于 X 的最大整数。

语法：FLOOR(X)

参数：

  • X：数值表达式

返回值：小于或等于 X 的最大整数

示例：

sql
SELECT FLOOR(3.14); -- 返回 3
SELECT FLOOR(-3.14); -- 返回 -4

数学函数 ​

RANDOM() ​

返回一个随机整数。

语法：RANDOM()

返回值：随机整数

示例：

sql
SELECT RANDOM(); -- 返回一个随机整数
SELECT ABS(RANDOM()) % 100; -- 返回 0-99 之间的随机数

POWER(X, Y) ​

返回 X 的 Y 次方。

语法：POWER(X, Y)

参数：

  • X：底数
  • Y：指数

返回值：X 的 Y 次方

示例：

sql
SELECT POWER(2, 3); -- 返回 8
SELECT POWER(10, -2); -- 返回 0.01

日期时间函数 ​

基本日期时间操作 ​

DATE(TIMESTAMP, MODIFIERS) ​

返回日期部分（YYYY-MM-DD）。

语法：DATE(TIMESTAMP, MODIFIERS)

参数：

  • TIMESTAMP：日期时间字符串或表达式
  • MODIFIERS：可选，日期修改器，如 '+1 day', '-1 month' 等

返回值：日期字符串（YYYY-MM-DD）

示例：

sql
SELECT DATE('2023-10-15 14:30:45'); -- 返回 '2023-10-15'
SELECT DATE('now'); -- 返回当前日期
SELECT DATE('2023-10-15', '+1 day'); -- 返回 '2023-10-16'

TIME(TIMESTAMP, MODIFIERS) ​

返回时间部分（HH:MM:SS）。

语法：TIME(TIMESTAMP, MODIFIERS)

参数：

  • TIMESTAMP：日期时间字符串或表达式
  • MODIFIERS：可选，时间修改器

返回值：时间字符串（HH:MM:SS）

示例：

sql
SELECT TIME('2023-10-15 14:30:45'); -- 返回 '14:30:45'
SELECT TIME('now'); -- 返回当前时间

DATETIME(TIMESTAMP, MODIFIERS) ​

返回完整日期时间（YYYY-MM-DD HH:MM:SS）。

语法：DATETIME(TIMESTAMP, MODIFIERS)

参数：

  • TIMESTAMP：日期时间字符串或表达式
  • MODIFIERS：可选，日期时间修改器

返回值：日期时间字符串（YYYY-MM-DD HH:MM:SS）

示例：

sql
SELECT DATETIME('now'); -- 返回当前日期时间
SELECT DATETIME('2023-10-15', '+1 month', '-2 days'); -- 返回 '2023-11-13 00:00:00'

STRFTIME(FORMAT, TIMESTAMP, MODIFIERS) ​

按照指定格式返回日期时间字符串。

语法：STRFTIME(FORMAT, TIMESTAMP, MODIFIERS)

参数：

  • FORMAT：格式字符串，如 '%Y-%m-%d', '%H:%M:%S' 等
  • TIMESTAMP：日期时间字符串或表达式
  • MODIFIERS：可选，日期时间修改器

返回值：格式化后的日期时间字符串

示例：

sql
SELECT STRFTIME('%Y-%m-%d %H:%M:%S', 'now'); -- 返回当前日期时间
SELECT STRFTIME('%W', '2023-10-15'); -- 返回 '41'（一年中的第几周）

聚合函数 ​

基本聚合函数 ​

COUNT(X) ​

返回非 NULL 值的数量。

语法：COUNT(X) 或 COUNT(*)

参数：

  • X：表达式，* 表示所有行

返回值：整数，表示非 NULL 值的数量

示例：

sql
SELECT COUNT(*) FROM users; -- 返回用户总数
SELECT COUNT(email) FROM users; -- 返回有邮箱的用户数量

SUM(X) ​

返回所有非 NULL 值的和。

语法：SUM(X)

参数：

  • X：数值表达式

返回值：所有非 NULL 值的和

示例：

sql
SELECT SUM(amount) FROM orders; -- 返回订单总金额

AVG(X) ​

返回所有非 NULL 值的平均值。

语法：AVG(X)

参数：

  • X：数值表达式

返回值：所有非 NULL 值的平均值

示例：

sql
SELECT AVG(score) FROM students; -- 返回学生平均分数

MAX(X) ​

返回所有非 NULL 值中的最大值。

语法：MAX(X)

参数：

  • X：表达式

返回值：所有非 NULL 值中的最大值

示例：

sql
SELECT MAX(age) FROM users; -- 返回最大年龄

MIN(X) ​

返回所有非 NULL 值中的最小值。

语法：MIN(X)

参数：

  • X：表达式

返回值：所有非 NULL 值中的最小值

示例：

sql
SELECT MIN(price) FROM products; -- 返回最低价格

高级聚合函数 ​

GROUP_CONCAT(X, SEP) ​

将分组中的值连接为一个字符串，使用 SEP 作为分隔符（默认逗号）。

语法：GROUP_CONCAT(X, SEP)

参数：

  • X：要连接的表达式
  • SEP：可选，分隔符，默认逗号

返回值：连接后的字符串

示例：

sql
SELECT category, GROUP_CONCAT(product_name, '; ') FROM products GROUP BY category;

窗口函数 ​

SQLite 3.25.0 及以上版本支持窗口函数，用于在一组行上执行计算。

ROW_NUMBER() ​

为结果集中的每一行分配一个唯一的序号。

语法：ROW_NUMBER() OVER (PARTITION BY expr ORDER BY expr)

示例：

sql
SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS rank FROM students;

RANK() ​

为结果集中的每一行分配一个排名，相同值的行有相同的排名，后续行排名跳过。

语法：RANK() OVER (PARTITION BY expr ORDER BY expr)

示例：

sql
SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM students;

DENSE_RANK() ​

为结果集中的每一行分配一个排名，相同值的行有相同的排名，后续行排名不跳过。

语法：DENSE_RANK() OVER (PARTITION BY expr ORDER BY expr)

示例：

sql
SELECT name, score, DENSE_RANK() OVER (ORDER BY score DESC) AS rank FROM students;

版本差异 ​

不同 SQLite 版本之间内置函数有一些变化，以下是主要版本差异：

SQLite 3.45.0（2024-03-12） ​

  • 添加了 JSON_ARRAY_LENGTH() 函数
  • 增强了 JSON_EXTRACT() 函数的功能

SQLite 3.40.0（2022-11-16） ​

  • 添加了 GENERATE_SERIES() 表值函数
  • 增强了 STRFTIME() 函数，支持更多格式说明符

SQLite 3.35.0（2021-03-12） ​

  • 添加了 JSON_TABLE() 函数
  • 增强了 JSON_GROUP_ARRAY() 和 JSON_GROUP_OBJECT() 函数

SQLite 3.25.0（2018-09-15） ​

  • 首次支持窗口函数（ROW_NUMBER(), RANK(), DENSE_RANK() 等）

SQLite 3.8.0（2013-08-26） ​

  • 添加了 JSON1 扩展，支持 JSON 相关函数

最佳实践 ​

  1. 避免在索引列上使用函数：在索引列上使用函数会导致索引失效，影响查询性能
  2. 使用合适的聚合函数：根据需求选择合适的聚合函数，如 COUNT(*) 用于计算行数，COUNT(column) 用于计算非 NULL 值数量
  3. 注意 NULL 值处理：大多数函数对 NULL 值返回 NULL，需要注意处理
  4. 使用日期时间函数的标准格式：尽量使用 ISO 8601 格式（YYYY-MM-DD HH:MM:SS）处理日期时间
  5. 合理使用窗口函数：窗口函数功能强大，但性能开销较大，避免在大数据集上过度使用

常见问题（FAQ） ​

1. 为什么某些函数在我的 SQLite 版本中不可用？ ​

不同 SQLite 版本支持不同的函数集，特别是一些高级函数（如窗口函数、JSON 函数）是在较新版本中添加的。请检查您的 SQLite 版本，并考虑升级到较新版本。

2. 如何检查 SQLite 版本？ ​

sql
SELECT sqlite_version();

3. 如何处理函数返回的 NULL 值？ ​

可以使用 COALESCE() 函数将 NULL 值替换为默认值：

sql
SELECT COALESCE(LENGTH(NULL), 0); -- 返回 0

4. 为什么在索引列上使用函数会影响性能？ ​

当在索引列上使用函数时，SQLite 需要对每一行计算函数结果，无法直接使用索引进行查询，导致全表扫描，性能下降。

5. 如何扩展 SQLite 函数？ ​

可以使用 SQLite 的扩展机制添加自定义函数，具体方法取决于您使用的编程语言和 SQLite 驱动。

总结 ​

SQLite 提供了丰富的内置函数，用于处理各种数据类型和查询需求。本文档详细介绍了 SQLite 的内置函数，包括字符串函数、数值函数、日期时间函数、聚合函数和窗口函数，帮助开发人员更高效地使用 SQLite 进行数据处理和查询。建议开发人员根据实际需求选择合适的函数，并遵循最佳实践，以提高查询性能和代码可读性。