SQLite 常用函数
天天向上
发布： 2025-03-04 20:57:24
原创
532
人浏览过
SQLite 提供了多种内置函数，帮助你在数据库操作中进行各种常见的计算、转换、字符串操作、日期时间处理等任务。以下是 SQLite 中一些常用的函数，分为不同类别进行说明。
1. 字符串函数
这些函数帮助你处理和操作字符串数据。
LENGTH(str): 返回字符串的长度（以字符为单位）。
SELECT LENGTH('Hello'); -- 返回 5UPPER(str): 将字符串转换为大写字母。
SELECT UPPER('hello'); -- 返回 'HELLO'LOWER(str): 将字符串转换为小写字母。
SELECT LOWER('HELLO'); -- 返回 'hello'SUBSTR(str, start, length): 从字符串的指定位置提取子字符串。
SELECT SUBSTR('Hello, World', 1, 5); -- 返回 'Hello'TRIM(str): 去除字符串两端的空格。
SELECT TRIM(' Hello '); -- 返回 'Hello'REPLACE(str, old, new): 替换字符串中的指定子串。
SELECT REPLACE('Hello, World', 'World', 'SQLite'); -- 返回 'Hello, SQLite'CONCAT(str1, str2): 将多个字符串连接在一起。
SELECT CONCAT('Hello', ' ', 'World'); -- 返回 'Hello World'2. 聚合函数
聚合函数用于对数据进行聚合计算，通常与 GROUP BY 子句一起使用。
COUNT(expression): 返回某个表达式的行数，常用于统计行数。
SELECT COUNT(*) FROM users; -- 返回用户表中的行数SUM(expression): 返回某列的总和。
SELECT SUM(amount) FROM orders; -- 计算订单金额的总和AVG(expression): 返回某列的平均值。
SELECT AVG(amount) FROM orders; -- 计算订单金额的平均值MAX(expression): 返回某列的最大值。
SELECT MAX(age) FROM users; -- 返回用户表中的最大年龄MIN(expression): 返回某列的最小值。
SELECT MIN(age) FROM users; -- 返回用户表中的最小年龄GROUP_CONCAT(expression, separator): 将组内的多个值连接为一个字符串，可以指定分隔符。
SELECT GROUP_CONCAT(name, ', ') FROM users; -- 返回所有用户名，用逗号分隔3. 数学函数
SQLite 提供了多种数学函数来进行数值计算。
ABS(x): 返回x的绝对值。
SELECT ABS(-10); -- 返回 10ROUND(x, d): 对数字x四舍五入到d位小数。
SELECT ROUND(123.4567, 2); -- 返回 123.46CEIL(x): 返回大于等于x的最小整数。
SELECT CEIL(3.14); -- 返回 4FLOOR(x): 返回小于等于x的最大整数。
SELECT FLOOR(3.14); -- 返回 3RANDOM(): 返回一个随机的整数值。
SELECT RANDOM(); -- 返回一个随机整数PI(): 返回 π (圆周率) 的值。
SELECT PI(); -- 返回 3.141592653589794. 日期和时间函数
SQLite 提供了一些日期和时间处理函数，能够对日期、时间进行计算和格式化。
DATE('now'): 返回当前的日期。
SELECT DATE('now'); -- 返回当前日期（YYYY-MM-DD）TIME('now'): 返回当前的时间。
SELECT TIME('now'); -- 返回当前时间（HH:MM:SS）DATETIME('now'): 返回当前的日期和时间。
SELECT DATETIME('now'); -- 返回当前的日期和时间（YYYY-MM-DD HH:MM:SS）JULIANDAY('now'): 返回自公元前4713年1月1日以来的儒略日数。
SELECT JULIANDAY('now'); -- 返回当前时间的儒略日数STRFTIME(format, time): 格式化日期和时间。
SELECT STRFTIME('%Y-%m-%d %H:%M:%S', 'now'); -- 返回格式化后的当前日期和时间CURRENT_TIMESTAMP: 返回当前的时间戳。
SELECT CURRENT_TIMESTAMP; -- 返回当前的日期和时间（YYYY-MM-DD HH:MM:SS）5. 条件函数
SQLite 也有一些条件函数，允许你根据特定条件执行操作。
IFNULL(expression, default): 如果表达式为NULL，则返回默认值，否则返回表达式的值。
SELECT IFNULL(NULL, 'Default Value'); -- 返回 'Default Value'COALESCE(expression1, expression2, ...): 返回第一个非NULL的值。
SELECT COALESCE(NULL, NULL, 'First non-null value'); -- 返回 'First non-null value'CASE WHEN condition THEN result ELSE default END: 进行条件判断，相当于if-else语句。
SELECT CASE WHEN age >= 18 THEN 'Adult' ELSE 'Minor' END FROM users;6. 其他常用函数
NULLIF(x, y): 如果x和y相等，返回NULL，否则返回x。
SELECT NULLIF(5, 5); -- 返回 NULL
SELECT NULLIF(5, 6); -- 返回 5TOTAL(expression): 返回所有行中expression的总和（类似SUM，但处理NULL值时会忽略它们）。
SELECT TOTAL(amount) FROM orders;RANDOMBLOB(n): 返回一个随机的字节串，长度为n字节。
SELECT RANDOMBLOB(10); -- 返回 10 字节的随机字节串7. 小结
SQLite 提供了多种内置函数，涵盖了字符串处理、数学计算、日期时间、聚合计算、条件判断等常见任务。通过这些函数，你可以方便地进行数据转换、计算、格式化以及条件操作，从而提高数据库操作的效率和灵活性。
SQLite除了我前面提到的常用函数，还有一些其他有用的函数（如下），在不同场景中进行更多的数据处理和查询操作。
8. 排序和限制函数
LIMIT n: 用于限制查询结果的返回行数。
SELECT * FROM users LIMIT 5; -- 返回前 5 行数据OFFSET n: 用于设置查询返回结果的偏移量。通常与LIMIT一起使用来实现分页。
SELECT * FROM users LIMIT 5 OFFSET 10; -- 从第 11 行开始返回 5 行数据ORDER BY column: 按照指定的列对查询结果进行排序。
SELECT * FROM users ORDER BY age DESC; -- 按年龄降序排序9. NULL 相关函数
IS NULL: 检查某个表达式是否为NULL。
SELECT * FROM users WHERE age IS NULL; -- 查找年龄为 NULL 的记录IS NOT NULL: 检查某个表达式是否不是NULL。
SELECT * FROM users WHERE age IS NOT NULL; -- 查找年龄不为 NULL 的记录IFNULL(expression, default): 如果expression为NULL，则返回default，否则返回expression。
SELECT IFNULL(age, 0) FROM users; -- 如果 age 是 NULL，返回 0COALESCE(expr1, expr2, ...): 返回第一个非NULL的表达式值。如果所有表达式都为NULL，则返回NULL。
SELECT COALESCE(age, 30) FROM users; -- 如果 age 为 NULL，则返回 3010. 数学函数
EXP(x): 返回e的x次幂。
SELECT EXP(2); -- 返回 e^2LN(x): 返回x的自然对数。
SELECT LN(10); -- 返回 10 的自然对数LOG(x): 返回以 10 为底的对数。
SELECT LOG(10); -- 返回 10 的以 10 为底的对数PI(): 返回圆周率 π 的近似值。
SELECT PI(); -- 返回 3.1415926535897911. 日期和时间函数
DATE(timestring, modifier, modifier, ...): 修改时间字符串并返回新的日期。
SELECT DATE('now', '+1 day'); -- 返回明天的日期TIME(timestring, modifier, modifier, ...): 修改时间字符串并返回新的时间。
SELECT TIME('now', '+1 hour'); -- 返回当前时间加 1 小时DATETIME(timestring, modifier, modifier, ...): 修改时间字符串并返回新的日期和时间。
SELECT DATETIME('now', '+1 day', '+1 hour'); -- 返回当前日期和时间加 1 天、1 小时STRFTIME(format, time): 按指定格式返回日期和时间。
SELECT STRFTIME('%Y-%m-%d %H:%M:%S', 'now'); -- 返回当前的日期和时间（YYYY-MM-DD HH:MM:SS）12. 聚合函数
GROUP_CONCAT(expr, separator): 将组内的多个字符串值合并为一个字符串，使用指定的分隔符分隔。
SELECT GROUP_CONCAT(name, ', ') FROM users; -- 返回所有用户名，用逗号分隔COUNT(DISTINCT expression): 统计某个列不同值的数量。
SELECT COUNT(DISTINCT age) FROM users; -- 返回不同年龄的数量SUM(DISTINCT expression): 计算某个列不同值的和。
SELECT SUM(DISTINCT amount) FROM orders; -- 返回订单金额的总和，排除重复的金额MAX(DISTINCT expression): 返回某个列不同值的最大值。
SELECT MAX(DISTINCT amount) FROM orders; -- 返回订单金额的最大值，排除重复的金额MIN(DISTINCT expression): 返回某个列不同值的最小值。
SELECT MIN(DISTINCT amount) FROM orders; -- 返回订单金额的最小值，排除重复的金额13. JSON 函数
SQLite 也支持处理 JSON 数据类型，以下是一些常用的 JSON 函数。
JSON_OBJECT(key, value, ...): 创建一个 JSON 对象。
SELECT JSON_OBJECT('name', 'Alice', 'age', 30);
-- 返回 '{"name": "Alice", "age": 30}'JSON_ARRAY(value, ...): 创建一个 JSON 数组。
SELECT JSON_ARRAY('apple', 'banana', 'cherry');
-- 返回 '["apple", "banana", "cherry"]'JSON_EXTRACT(json, path): 从 JSON 对象中提取指定路径的数据。
SELECT JSON_EXTRACT('{"name": "Alice", "age": 30}', '$.name');
-- 返回 'Alice'JSON_SET(json, path, value): 在 JSON 对象中设置指定路径的值。
SELECT JSON_SET('{"name": "Alice", "age": 30}', '$.age', 31);
-- 返回 '{"name": "Alice", "age": 31}'JSON_ARRAY_LENGTH(json): 返回 JSON 数组的长度。
SELECT JSON_ARRAY_LENGTH('["apple", "banana", "cherry"]');
-- 返回 3JSON_TYPE(json): 返回 JSON 值的数据类型（例如：object,array,string,number）。
SELECT JSON_TYPE('{"name": "Alice"}');
-- 返回 'object'14. 其他常用函数
RANDOMBLOB(n): 返回一个随机的二进制大对象，长度为n字节。
SELECT RANDOMBLOB(10); -- 返回 10 字节的随机字节串LAST_INSERT_ROWID(): 返回最后一次插入的行的 ID（仅在插入后有效）。
INSERT INTO users(name, age) VALUES('Alice', 30);
SELECT LAST_INSERT_ROWID(); -- 返回刚插入记录的行 ID15. 总结
SQLite 提供了广泛的内置函数，涵盖了字符串操作、数学计算、日期时间处理、聚合计算、JSON 处理等多种功能。通过灵活地使用这些函数，可以更高效地处理数据库中的数据。你可以根据具体的需求选择合适的函数来提升 SQL 查询的效率和可读性。更多详细内容请关注其他相关文章。