SQLite - 常用函数

SQLite - 常用函数

SQLite 提供了一组丰富的内置函数，用于执行计算和数据操作。这些函数不区分大小写。这里我们按类别介绍一些最常用的函数。

在我们的示例中，我们将使用以下 EMPLOYEES 表：

CREATE TABLE EMPLOYEES(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   DEPARTMENT     TEXT    NOT NULL,
   SALARY         REAL,
   JOIN_DATE      TEXT
);

INSERT INTO EMPLOYEES VALUES (1, 'Alice', 32, 'Engineering', 95000.0, '2020-03-15');
INSERT INTO EMPLOYEES VALUES (2, 'Bob', 25, 'Marketing', 68000.0, '2021-07-22');
INSERT INTO EMPLOYEES VALUES (3, 'Charlie', 28, 'Engineering', 105000.0, '2019-01-10');
INSERT INTO EMPLOYEES VALUES (4, 'David', 45, 'Sales', 120000.0, '2017-05-30');
INSERT INTO EMPLOYEES VALUES (5, 'Eve', 25, 'Engineering', 88000.0, '2022-11-01');

聚合函数

聚合函数对一组行执行计算并返回单个汇总值。

函数	描述与示例
COUNT(expr)	计算行数。COUNT(*) 计算所有行；COUNT(column) 计算该列中非 NULL 的值。 SELECT COUNT(*) FROM EMPLOYEES; → 5
MAX(expr)	返回组中的最大值。 SELECT MAX(SALARY) FROM EMPLOYEES; → 120000.0
MIN(expr)	返回组中的最小值。 SELECT MIN(AGE) FROM EMPLOYEES; → 25
AVG(expr)	返回组的平均值。 SELECT AVG(AGE) FROM EMPLOYEES; → 31.0
SUM(expr)	返回组中所有值的总和。 SELECT SUM(SALARY) FROM EMPLOYEES; → 476000.0
GROUP_CONCAT(expr)	将组中非 NULL 值连接成一个字符串，以逗号分隔。 SELECT DEPARTMENT, GROUP_CONCAT(NAME) FROM EMPLOYEES GROUP BY DEPARTMENT;

标量核心与字符串函数

标量函数对单个值进行操作并返回单个值。

函数	描述与示例
LENGTH(str)	返回字符串中的字符数。 SELECT NAME, LENGTH(NAME) FROM EMPLOYEES WHERE ID = 1; → 'Alice', 5
UPPER(str)	将字符串转换为大写。 SELECT UPPER(NAME) FROM EMPLOYEES WHERE ID = 1; → 'ALICE'
LOWER(str)	将字符串转换为小写。 SELECT LOWER(NAME) FROM EMPLOYEES WHERE ID = 1; → 'alice'
ABS(num)	返回数字的绝对值（非负值）。 SELECT ABS(-15.5); → 15.5
RANDOM()	返回一个伪随机的 64 位有符号整数。 SELECT RANDOM(); → (一个大的随机数)
TYPEOF(expr)	返回表达式的数据类型：‘null’、‘integer’、‘real’、‘text’ 或 ‘blob’。 SELECT TYPEOF(SALARY) FROM EMPLOYEES LIMIT 1; → 'real'
SUBSTR(str, start, len)	提取从 start 位置（基于 1）开始的 len 个字符的子字符串。 SELECT SUBSTR('SQLite', 1, 3); → 'SQL'

日期与时间函数

这些函数对于处理日期和时间信息至关重要。

函数	描述与示例
DATE(timestring, ...)	以 YYYY-MM-DD 格式返回日期。 SELECT DATE(JOIN_DATE, '+1 year') FROM EMPLOYEES WHERE ID = 1; → '2021-03-15'
STRFTIME(format, time, ...)	根据 format 字符串格式化日期/时间。非常强大。 SELECT STRFTIME('%Y', JOIN_DATE) FROM EMPLOYEES WHERE ID = 1; → '2020'
JULIANDAY(timestring)	返回儒略日编号 - 对于计算日期之间的差异很有用。 SELECT JULIANDAY('now') - JULIANDAY(JOIN_DATE) FROM EMPLOYEES WHERE ID = 1; → (入职以来的天数)

窗口函数（现代 SQLite）

窗口函数对一组与当前行相关的表行执行计算。与聚合函数不同，它们不会折叠行；它们为每行返回一个值。

示例：按部门内薪资对员工进行排名。

SELECT
  NAME,
  DEPARTMENT,
  SALARY,
  RANK() OVER (PARTITION BY DEPARTMENT ORDER BY SALARY DESC) as rank_in_dept
FROM EMPLOYEES;

这将产生一个结果，显示每个员工在其特定部门内的薪资排名。

系统函数

这些函数提供关于 SQLite 库本身的元数据。

sqlite> SELECT sqlite_version();
-- 返回 SQLite 库的版本号。
-- 示例结果：'3.41.2'