JQuick-SQL

纯 Java 8+ · Apache-2.0 · 零数据库部署

数据在 Java 进程里，用标准 SQL 直接查不装数据库、不写 JDBC——一行 embedded() 注册内存表即查

把内存行、接口返回、文件解析结果注册为表（registerTable），标准 SQL 完成过滤、聚合、JOIN、排序分页，结果以 JQuickDataSet 返回。SQL 经自研语法解析编译为强类型 AST，12 条规则优化后进程内执行；200+ 内置函数开箱即用，还可像 iBatis 一样把 SQL 维护在 XML、以 #{param} 绑定 Java 接口动态代理调用。

快速开始 GitHub Maven Central

从进程内数据到查询结果的完整链路 — JQuick-SQL — 从进程内数据到查询结果的完整链路

0 个需要部署的数据库服务
200+内置函数，SPI 可扩展
12 条基于规则的查询优化
30+AST 节点类型，解析独立可用

8 行创建引擎、注册内存表、执行查询的全部 Java 代码

JQuickSQL sql = JQuickSQL.embedded();
sql.registerTable("users", columns, rows);

JQuickDataSet result = sql.execute(
    "SELECT status, COUNT(*) AS cnt, AVG(age) AS avgAge "
  + "FROM users GROUP BY status ORDER BY cnt DESC");
result.printTable();
sql.shutdown();

嵌入式引擎一行创建，Coordinator-Worker 架构；使用完毕调用 shutdown() 释放 worker 端口资源。

XML同一引擎的 iBatis 风格 XML 动态 SQL

<sqls namespace="com.example.service.UserService">
    <sql name="findUsers" returnClass="java.util.List">
        SELECT * FROM users
        WHERE age > #{minAge}
        AND status = #{status}
        LIMIT #{limit}
    </sql>
</sqls>

SQL 维护在 XML 中，#{param} 绑定接口方法参数，JDK 动态代理生成实现——动态查询不再手拼字符串。

同样的数据加工，手写循环过滤要付出什么

比较的是「表达与维护查询的成本」，不是「能不能实现」。

对比维度	JQuick-SQL	手写循环过滤
查询表达	标准 SQL 一句话表达过滤、聚合、JOIN、分页	for + if 手写循环，逻辑改一次改一遍
部署形态	进程内嵌入，一行 embedded()，无需部署数据库	引入数据库或内存网格，部署运维成本高
动态条件	SQL 字符串或 iBatis 风格 XML，#{param} 绑定接口	条件拼接代码膨胀，易错难测
执行性能	AST → 逻辑计划 → 12 条规则优化 → 物理计划	无优化，数据量一大就逐条遍历
函数复用	200+ 内置函数 + SPI 自定义，SQL 内直接调用	工具方法各自实现，命名与复用混乱
数据来源	registerTable 任意注册：内存行、接口返回、文件解析结果	每种来源各写一套装配逻辑

不是 JDBC 驱动，也不依赖外部数据库：数据在哪个进程，查询就发生在哪个进程。

JQuick-SQL 是自研语法解析驱动的轻量级 SQL 解析与内存查询引擎。它将标准 SQL 文本解析为强类型 AST，经逻辑计划优化后在进程内对你注册的数据集直接执行查询——不连接外部数据库，不是 JDBC 驱动，也不需要部署任何数据库服务。面向 Java 嵌入式动态查询、报表平台计算与各类动态查询场景。

简介

JQuick-SQL 解决的问题很直接：数据已经在 Java 进程里（内存行、接口返回、文件解析结果），想用标准 SQL 直接查询，而不是手写循环过滤。

它不是连接外部数据库的 JDBC 驱动，而是一套内存查询引擎：你把数据注册为表（registerTable），然后用标准 SQL 完成过滤、聚合、排序、分页、多表关联，结果以 JQuickDataSet 返回。典型使用场景：

场景	说明
Java 嵌入式动态查询	应用内组装 SQL，直接查询内存数据集，无需部署数据库
报表平台计算	取数后在进程内完成 GROUP BY / 聚合 / 排序 / 分页等报表计算
动态查询场景	运营、风控、后台检索等条件频繁变化的查询，用 SQL 表达而非硬编码
单元测试	以内存表替代真实数据库，测试 SQL 逻辑
SQL 审计与解析	SQL 文本解析为强类型 AST，按子句遍历检查

工作流程：SQL 文本 → 自研语法解析 → AST → 逻辑计划 → 规则优化 → 物理计划 → 进程内执行 → JQuickDataSet。

核心特性

标准 SQL 支持：SELECT、DISTINCT、WHERE、ORDER BY、LIMIT/OFFSET、GROUP BY/HAVING、聚合函数（COUNT/SUM/AVG/MIN/MAX）、JOIN（INNER/LEFT/RIGHT/FULL/CROSS/NATURAL）、集合运算（UNION/MINUS/INTERSECT）、子查询（EXISTS/IN/标量子查询）、CASE WHEN 条件表达式。
丰富的表达式求值：比较、逻辑（AND/OR/NOT）、算术、LIKE、REGEXP、IN、BETWEEN、IS NULL、布尔列直接作为条件、常量表达式与列别名。
200+ 内置函数：由 jquick-transform-function 提供，覆盖字符串、数学、日期、条件、转换、业务校验、加解密等类别，函数名大小写不敏感。
函数 SPI 扩展：实现 JQuickBaseFunctionFunctionProvider 并通过 ServiceLoader 注册，自定义函数即可像内置函数一样在 SQL 任意子句中使用，支持 @Priority 优先级覆盖。
查询优化：基于规则的优化器，含谓词下推、投影下推、常量折叠、表达式化简、limit 下推、聚合下推、连接重排、子查询转连接等 12 条规则。
嵌入式执行：JQuickSQL.embedded() 一行创建引擎，Coordinator-Worker 架构，支持哈希连接、嵌套循环连接、两阶段聚合与 Top-N。
XML 动态 SQL：iBatis 风格，SQL 维护在 XML 中，通过 #{param} 占位符绑定 Java 接口方法，由 JDK 动态代理生成实现。
AST 解析独立可用：JQuickSQLExecutor 一行代码把 SQL 字符串解析为强类型 AST（30+ 节点类型），用于 SQL 审计、语句拆分等静态分析。

快速开始

创建嵌入式引擎、注册内存表、执行 SQL，三步完成：

// Create embedded SQL engine
JQuickSQL sql = JQuickSQL.embedded();
// Register test data
List<JQuickColumnMeta> columns = Arrays.asList(
    new JQuickColumnMeta("id", Integer.class, "users"),
    new JQuickColumnMeta("name", String.class, "users"),
    new JQuickColumnMeta("age", Integer.class, "users")
);
List<JQuickRow> rows = Arrays.asList(
    createRow("id", 1, "name", "Alice", "age", 25),
    createRow("id", 2, "name", "Bob", "age", 30)
);
sql.registerTable("users", columns, rows);
// Execute SQL
JQuickDataSet result = sql.execute("SELECT * FROM users");
result.printTable();
// Shutdown
sql.shutdown();

<dependency>
    <groupId>io.github.paohaijiao</groupId>
    <artifactId>jquick-sql-runtime</artifactId>
    <version>5.0.0</version>
</dependency>

implementation 'io.github.paohaijiao:jquick-sql-runtime:5.0.0'

jquick-sql-runtime 是入口构件，会传递引入 jquick-sql-query、jquick-sql-plan、jquick-sql-parser 与 jquick-sql-core。

环境要求：JDK 1.8+，Maven 3.x。

语法示例

以下示例均来自官方 demo 的实际执行结果，每条示例包含【输入 SQL】与【输出】两部分。输出为 JQuickDataSet.printTable() 打印的表格日志。

基础查询

本节输入数据（users 表）：

id	name	age	status	enable	addr	birthday
1	Alice	25	active	true	beijing	2020-04-09
2	Bob	30	active	true	shanghai	1991-08-09
3	Charlie	20	pending	false	chengdu	1988-07-12
4	David	35	inactive	true	xian	1955-11-29
5	Eve	28	active	true	chongqing	2003-07-12
6	Martin	30	active	true	guangzhou	1978-06-30

示例 1.1：查询全部行与列

返回 users 表的所有列和所有行，用于查看完整数据集。

输入 SQL

SELECT * FROM users

输出

[2026-07-23 11:19:19.052] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:19:19.052] [INFO] | id | name    | age | status   | enable | addr      | birthday             |
[2026-07-23 11:19:19.052] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:19:19.053] [INFO] | 1  | Alice   | 25  | active   | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 11:19:19.053] [INFO] | 2  | Bob     | 30  | active   | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 11:19:19.053] [INFO] | 3  | Charlie | 20  | pending  | false  | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 11:19:19.053] [INFO] | 4  | David   | 35  | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 11:19:19.053] [INFO] | 5  | Eve     | 28  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 11:19:19.053] [INFO] | 6  | Martin  | 30  | active   | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 11:19:19.053] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+

示例 1.2：查询指定列

只返回 users 表中指定的列（id、name、age、status、enable、addr）。

输入 SQL

SELECT id, name,age, status,enable,addr FROM users

输出

[2026-07-23 11:21:39.973] [INFO] +----+---------+-----+----------+--------+-----------+
[2026-07-23 11:21:39.973] [INFO] | id | name    | age | status   | enable | addr      |
[2026-07-23 11:21:39.973] [INFO] +----+---------+-----+----------+--------+-----------+
[2026-07-23 11:21:39.973] [INFO] | 1  | Alice   | 25  | active   | true   | beijing   |
[2026-07-23 11:21:39.973] [INFO] | 2  | Bob     | 30  | active   | true   | shanghai  |
[2026-07-23 11:21:39.974] [INFO] | 3  | Charlie | 20  | pending  | false  | chengdu   |
[2026-07-23 11:21:39.974] [INFO] | 4  | David   | 35  | inactive | true   | xian      |
[2026-07-23 11:21:39.974] [INFO] | 5  | Eve     | 28  | active   | true   | chongqing |
[2026-07-23 11:21:39.974] [INFO] | 6  | Martin  | 30  | active   | true   | guangzhou |
[2026-07-23 11:21:39.974] [INFO] +----+---------+-----+----------+--------+-----------+

示例 1.3：在 SELECT 中使用内置函数

toUpper() 等内置函数由 jquick-transform-function 提供，并可通过 SPI 扩展。

输入 SQL

SELECT id, toUpper(name) as upperName,age, status,enable,addr,birthday FROM users

输出

[2026-07-23 11:22:48.126] [INFO] +----+-----------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:22:48.126] [INFO] | id | upperName | age | status   | enable | addr      | birthday             |
[2026-07-23 11:22:48.126] [INFO] +----+-----------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:22:48.126] [INFO] | 1  | ALICE     | 25  | active   | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 11:22:48.126] [INFO] | 2  | BOB       | 30  | active   | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 11:22:48.126] [INFO] | 3  | CHARLIE   | 20  | pending  | false  | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 11:22:48.126] [INFO] | 4  | DAVID     | 35  | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 11:22:48.126] [INFO] | 5  | EVE       | 28  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 11:22:48.126] [INFO] | 6  | MARTIN    | 30  | active   | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 11:22:48.126] [INFO] +----+-----------+-----+----------+--------+-----------+----------------------+

示例 1.4：算术表达式与别名

支持嵌套算术表达式，如对 age 列计算 (age + 1) * 3，并为列起别名。

输入 SQL

SELECT id, name as upperName,(age+1)*3 as age, status,enable,addr,birthday FROM users

输出

[2026-07-23 11:23:35.632] [INFO] +----+-----------+-------+----------+--------+-----------+----------------------+
[2026-07-23 11:23:35.632] [INFO] | id | upperName | age   | status   | enable | addr      | birthday             |
[2026-07-23 11:23:35.632] [INFO] +----+-----------+-------+----------+--------+-----------+----------------------+
[2026-07-23 11:23:35.632] [INFO] | 1  | Alice     | 78.0  | active   | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 11:23:35.632] [INFO] | 2  | Bob       | 93.0  | active   | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 11:23:35.633] [INFO] | 3  | Charlie   | 63.0  | pending  | false  | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 11:23:35.633] [INFO] | 4  | David     | 108.0 | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 11:23:35.633] [INFO] | 5  | Eve       | 87.0  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 11:23:35.633] [INFO] | 6  | Martin    | 93.0  | active   | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 11:23:35.633] [INFO] +----+-----------+-------+----------+--------+-----------+----------------------+

示例 1.5：CASE WHEN 条件表达式

使用 CASE WHEN 按年龄分组归类。

输入 SQL

SELECT id, name, age, CASE WHEN age >= 30 THEN '中年'      WHEN age >= 20 THEN '青年'      ELSE '少年' END AS age_group FROM users

输出

[2026-07-23 11:24:12.889] [INFO] +----+---------+-----+-----------+
[2026-07-23 11:24:12.890] [INFO] | id | name    | age | age_group |
[2026-07-23 11:24:12.890] [INFO] +----+---------+-----+-----------+
[2026-07-23 11:24:12.890] [INFO] | 1  | Alice   | 25  | 青年        |
[2026-07-23 11:24:12.890] [INFO] | 2  | Bob     | 30  | 中年        |
[2026-07-23 11:24:12.890] [INFO] | 3  | Charlie | 20  | 青年        |
[2026-07-23 11:24:12.890] [INFO] | 4  | David   | 35  | 中年        |
[2026-07-23 11:24:12.890] [INFO] | 5  | Eve     | 28  | 青年        |
[2026-07-23 11:24:12.890] [INFO] | 6  | Martin  | 30  | 中年        |
[2026-07-23 11:24:12.890] [INFO] +----+---------+-----+-----------+

示例 1.6：DISTINCT 去重

返回去重后的 age 值。

输入 SQL

SELECT distinct age FROM users

输出

[2026-07-23 11:25:44.641] [INFO] +-----+
[2026-07-23 11:25:44.641] [INFO] | age |
[2026-07-23 11:25:44.641] [INFO] +-----+
[2026-07-23 11:25:44.641] [INFO] | 25  |
[2026-07-23 11:25:44.641] [INFO] | 30  |
[2026-07-23 11:25:44.641] [INFO] | 20  |
[2026-07-23 11:25:44.641] [INFO] | 35  |
[2026-07-23 11:25:44.641] [INFO] | 28  |
[2026-07-23 11:25:44.641] [INFO] +-----+
[2026-07-23 11:25:44.641] [INFO] Total: 5 rows

示例 1.7：布尔取反表达式

支持 ! 对布尔列取反（如 !enable）。

输入 SQL

SELECT id, toUpper(name) as upperName,age, status,!enable,addr,birthday FROM users

输出

[2026-07-23 11:26:55.089] [INFO] +----+-----------+-----+----------+------------+-----------+----------------------+
[2026-07-23 11:26:55.089] [INFO] | id | upperName | age | status   | NOT enable | addr      | birthday             |
[2026-07-23 11:26:55.089] [INFO] +----+-----------+-----+----------+------------+-----------+----------------------+
[2026-07-23 11:26:55.089] [INFO] | 1  | ALICE     | 25  | active   | false      | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 11:26:55.089] [INFO] | 2  | BOB       | 30  | active   | false      | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 11:26:55.089] [INFO] | 3  | CHARLIE   | 20  | pending  | true       | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 11:26:55.090] [INFO] | 4  | DAVID     | 35  | inactive | false      | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 11:26:55.090] [INFO] | 5  | EVE       | 28  | active   | false      | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 11:26:55.090] [INFO] | 6  | MARTIN    | 30  | active   | false      | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 11:26:55.090] [INFO] +----+-----------+-----+----------+------------+-----------+----------------------+
[2026-07-23 11:26:55.090] [INFO] Total: 6 rows

示例 1.8：常量表达式作为输出列

支持常量表达式（如 0 as index）作为 SELECT 字段。

输入 SQL

SELECT 0 as index,id, toUpper(name) as upperName,age, status,!enable,addr,birthday FROM users

输出

[2026-07-23 11:27:41.668] [INFO] +-------+----+-----------+-----+----------+------------+-----------+----------------------+
[2026-07-23 11:27:41.668] [INFO] | index | id | upperName | age | status   | NOT enable | addr      | birthday             |
[2026-07-23 11:27:41.669] [INFO] +-------+----+-----------+-----+----------+------------+-----------+----------------------+
[2026-07-23 11:27:41.669] [INFO] | 0.0   | 1  | ALICE     | 25  | active   | false      | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 11:27:41.669] [INFO] | 0.0   | 2  | BOB       | 30  | active   | false      | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 11:27:41.669] [INFO] | 0.0   | 3  | CHARLIE   | 20  | pending  | true       | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 11:27:41.669] [INFO] | 0.0   | 4  | DAVID     | 35  | inactive | false      | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 11:27:41.669] [INFO] | 0.0   | 5  | EVE       | 28  | active   | false      | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 11:27:41.670] [INFO] | 0.0   | 6  | MARTIN    | 30  | active   | false      | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 11:27:41.670] [INFO] +-------+----+-----------+-----+----------+------------+-----------+----------------------+
[2026-07-23 11:27:41.670] [INFO] Total: 6 rows

Where过滤

按指定条件过滤行。支持比较运算符（=、>、>=、<、<=、<>）、逻辑运算符（AND、OR、NOT）、NULL 判断（IS NULL、IS NOT NULL）、范围查询（BETWEEN）、集合判断（IN）、模式匹配（LIKE）、正则表达式（REGEXP）以及子查询（EXISTS）。

本节输入数据（users 表）：

id	name	age	status	enable	addr	birthday
1	Alice	25	active	true	beijing	2020-04-09
2	Bob	30	active	true	shanghai	1991-08-09
3	Charlie	20	pending	false	chengdu	1988-07-12
4	David	35	inactive	true	xian	1955-11-29
5	Eve	28	active	true	chongqing	2003-07-12
6	Martin	30	active	true	guangzhou	1978-06-30
7	Davila	39	active	true	null	1999-06-30

示例 2.1：等值条件

WHERE column = value，按等值条件过滤，例如 status = 'active'。

输入 SQL

SELECT * FROM users WHERE status = 'active'

输出

[2026-07-23 11:32:33.878] [INFO] +----+--------+-----+--------+--------+-----------+----------------------+
[2026-07-23 11:32:33.878] [INFO] | id | name   | age | status | enable | addr      | birthday             |
[2026-07-23 11:32:33.878] [INFO] +----+--------+-----+--------+--------+-----------+----------------------+
[2026-07-23 11:32:33.878] [INFO] | 1  | Alice  | 25  | active | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 11:32:33.878] [INFO] | 2  | Bob    | 30  | active | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 11:32:33.878] [INFO] | 5  | Eve    | 28  | active | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 11:32:33.878] [INFO] | 6  | Martin | 30  | active | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 11:32:33.878] [INFO] | 7  | Davila | 39  | active | true   | null      | 1999-06-29T16:00:00Z |
[2026-07-23 11:32:33.878] [INFO] +----+--------+-----+--------+--------+-----------+----------------------+
[2026-07-23 11:32:33.878] [INFO] Total: 5 rows

示例 2.2：AND 多条件

WHERE condition1 AND condition2，返回年龄大于 25 的活跃用户。

输入 SQL

SELECT * FROM users WHERE age > 25 AND status = 'active'

输出

[2026-07-23 11:33:27.498] [INFO] +----+--------+-----+--------+--------+-----------+----------------------+
[2026-07-23 11:33:27.498] [INFO] | id | name   | age | status | enable | addr      | birthday             |
[2026-07-23 11:33:27.498] [INFO] +----+--------+-----+--------+--------+-----------+----------------------+
[2026-07-23 11:33:27.498] [INFO] | 2  | Bob    | 30  | active | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 11:33:27.498] [INFO] | 5  | Eve    | 28  | active | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 11:33:27.498] [INFO] | 6  | Martin | 30  | active | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 11:33:27.498] [INFO] | 7  | Davila | 39  | active | true   | null      | 1999-06-29T16:00:00Z |
[2026-07-23 11:33:27.498] [INFO] +----+--------+-----+--------+--------+-----------+----------------------+
[2026-07-23 11:33:27.498] [INFO] Total: 4 rows

示例 2.3：OR 条件

WHERE condition1 OR condition2，返回状态为 pending 或已启用的用户。

输入 SQL

SELECT * FROM users WHERE status = 'pending' OR enable = true

输出

[2026-07-23 11:34:40.825] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:34:40.825] [INFO] | id | name    | age | status   | enable | addr      | birthday             |
[2026-07-23 11:34:40.825] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:34:40.825] [INFO] | 1  | Alice   | 25  | active   | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 11:34:40.825] [INFO] | 2  | Bob     | 30  | active   | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 11:34:40.825] [INFO] | 3  | Charlie | 20  | pending  | false  | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 11:34:40.825] [INFO] | 4  | David   | 35  | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 11:34:40.826] [INFO] | 5  | Eve     | 28  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 11:34:40.826] [INFO] | 6  | Martin  | 30  | active   | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 11:34:40.826] [INFO] | 7  | Davila  | 39  | active   | true   | null      | 1999-06-29T16:00:00Z |
[2026-07-23 11:34:40.826] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:34:40.826] [INFO] Total: 7 rows

示例 2.4：括号嵌套逻辑

WHERE condition OR (condition OR condition)，支持括号进行复杂逻辑分组。返回年龄大于 30、或状态为 pending、或居住在 chengdu 的用户。

输入 SQL

SELECT * FROM users WHERE age > 30 OR (status = 'pending' OR addr = 'chengdu')

输出

[2026-07-23 11:35:36.293] [INFO] +----+---------+-----+----------+--------+---------+----------------------+
[2026-07-23 11:35:36.293] [INFO] | id | name    | age | status   | enable | addr    | birthday             |
[2026-07-23 11:35:36.293] [INFO] +----+---------+-----+----------+--------+---------+----------------------+
[2026-07-23 11:35:36.293] [INFO] | 3  | Charlie | 20  | pending  | false  | chengdu | 1988-07-11T15:00:00Z |
[2026-07-23 11:35:36.294] [INFO] | 4  | David   | 35  | inactive | true   | xian    | 1955-11-28T16:00:00Z |
[2026-07-23 11:35:36.294] [INFO] | 7  | Davila  | 39  | active   | true   | null    | 1999-06-29T16:00:00Z |
[2026-07-23 11:35:36.294] [INFO] +----+---------+-----+----------+--------+---------+----------------------+
[2026-07-23 11:35:36.294] [INFO] Total: 3 rows

示例 2.5：常量布尔条件

WHERE true，常量布尔条件，返回全部行。

输入 SQL

SELECT * FROM users WHERE true

输出

[2026-07-23 11:36:17.974] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:36:17.975] [INFO] | id | name    | age | status   | enable | addr      | birthday             |
[2026-07-23 11:36:17.975] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:36:17.975] [INFO] | 1  | Alice   | 25  | active   | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 11:36:17.975] [INFO] | 2  | Bob     | 30  | active   | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 11:36:17.975] [INFO] | 3  | Charlie | 20  | pending  | false  | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 11:36:17.975] [INFO] | 4  | David   | 35  | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 11:36:17.975] [INFO] | 5  | Eve     | 28  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 11:36:17.975] [INFO] | 6  | Martin  | 30  | active   | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 11:36:17.975] [INFO] | 7  | Davila  | 39  | active   | true   | null      | 1999-06-29T16:00:00Z |
[2026-07-23 11:36:17.975] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:36:17.975] [INFO] Total: 7 rows

示例 2.6：布尔列直接作为条件

WHERE column，布尔列求值为 true 的行被保留，返回所有已启用用户。

输入 SQL

SELECT * FROM users WHERE enable

输出

[2026-07-23 11:37:11.555] [INFO] +----+--------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:37:11.555] [INFO] | id | name   | age | status   | enable | addr      | birthday             |
[2026-07-23 11:37:11.555] [INFO] +----+--------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:37:11.555] [INFO] | 1  | Alice  | 25  | active   | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 11:37:11.555] [INFO] | 2  | Bob    | 30  | active   | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 11:37:11.555] [INFO] | 4  | David  | 35  | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 11:37:11.555] [INFO] | 5  | Eve    | 28  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 11:37:11.555] [INFO] | 6  | Martin | 30  | active   | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 11:37:11.556] [INFO] | 7  | Davila | 39  | active   | true   | null      | 1999-06-29T16:00:00Z |
[2026-07-23 11:37:11.556] [INFO] +----+--------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:37:11.556] [INFO] Total: 6 rows

示例 2.7：过滤条件中调用函数

WHERE function(column) = value，支持在过滤条件中调用函数。将 name 转为大写后匹配 'ALICE'。

输入 SQL

SELECT * FROM users WHERE toUpper(name)='ALICE'

输出

[2026-07-23 11:37:58.226] [INFO] +----+-------+-----+--------+--------+---------+----------------------+
[2026-07-23 11:37:58.226] [INFO] | id | name  | age | status | enable | addr    | birthday             |
[2026-07-23 11:37:58.226] [INFO] +----+-------+-----+--------+--------+---------+----------------------+
[2026-07-23 11:37:58.226] [INFO] | 1  | Alice | 25  | active | true   | beijing | 2020-04-08T16:00:00Z |
[2026-07-23 11:37:58.226] [INFO] +----+-------+-----+--------+--------+---------+----------------------+
[2026-07-23 11:37:58.226] [INFO] Total: 1 rows

示例 2.8：IS NULL

WHERE column IS NULL，返回 addr 为空的用户。

输入 SQL

SELECT * FROM users WHERE addr is null

输出

[2026-07-23 11:38:36.946] [INFO] +----+--------+-----+--------+--------+------+----------------------+
[2026-07-23 11:38:36.946] [INFO] | id | name   | age | status | enable | addr | birthday             |
[2026-07-23 11:38:36.946] [INFO] +----+--------+-----+--------+--------+------+----------------------+
[2026-07-23 11:38:36.946] [INFO] | 7  | Davila | 39  | active | true   | null | 1999-06-29T16:00:00Z |
[2026-07-23 11:38:36.946] [INFO] +----+--------+-----+--------+--------+------+----------------------+
[2026-07-23 11:38:36.946] [INFO] Total: 1 rows

示例 2.9：IS NOT NULL

WHERE column IS NOT NULL，返回 addr 有值的用户。

输入 SQL

SELECT * FROM users WHERE addr is not null

输出

[2026-07-23 11:39:15.906] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:39:15.906] [INFO] | id | name    | age | status   | enable | addr      | birthday             |
[2026-07-23 11:39:15.906] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:39:15.906] [INFO] | 1  | Alice   | 25  | active   | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 11:39:15.907] [INFO] | 2  | Bob     | 30  | active   | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 11:39:15.907] [INFO] | 3  | Charlie | 20  | pending  | false  | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 11:39:15.907] [INFO] | 4  | David   | 35  | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 11:39:15.907] [INFO] | 5  | Eve     | 28  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 11:39:15.907] [INFO] | 6  | Martin  | 30  | active   | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 11:39:15.907] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+

示例 2.10：比较运算符

WHERE column > value，支持 >、>=、<、<=、=、<>，返回年龄大于 25 的用户。

输入 SQL

SELECT * FROM users WHERE age >25

输出

[2026-07-23 11:39:59.745] [INFO] +----+--------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:39:59.745] [INFO] | id | name   | age | status   | enable | addr      | birthday             |
[2026-07-23 11:39:59.745] [INFO] +----+--------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:39:59.745] [INFO] | 2  | Bob    | 30  | active   | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 11:39:59.745] [INFO] | 4  | David  | 35  | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 11:39:59.745] [INFO] | 5  | Eve    | 28  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 11:39:59.746] [INFO] | 6  | Martin | 30  | active   | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 11:39:59.746] [INFO] | 7  | Davila | 39  | active   | true   | null      | 1999-06-29T16:00:00Z |
[2026-07-23 11:39:59.746] [INFO] +----+--------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:39:59.746] [INFO] Total: 5 rows

示例 2.11：BETWEEN 范围

WHERE column BETWEEN min AND max，返回年龄在 25 到 30 之间的用户。

输入 SQL

SELECT * FROM users WHERE age  between 25 and 30

输出

[2026-07-26 15:24:44.552] [INFO] +----+--------+-----+--------+--------+-----------+----------------------+
[2026-07-26 15:24:44.552] [INFO] | id | name   | age | status | enable | addr      | birthday             |
[2026-07-26 15:24:44.552] [INFO] +----+--------+-----+--------+--------+-----------+----------------------+
[2026-07-26 15:24:44.552] [INFO] | 1  | Alice  | 25  | active | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-26 15:24:44.553] [INFO] | 2  | Bob    | 30  | active   | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-26 15:24:44.554] [INFO] | 5  | Eve    | 28  | active | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-26 15:24:44.554] [INFO] | 6  | Martin | 30  | active | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-26 15:24:44.554] [INFO] +----+--------+-----+--------+--------+-----------+----------------------+
[2026-07-26 15:24:44.554] [INFO] Total: 4 rows

示例 2.12：IN 集合

WHERE column IN (value1, value2, ...)，返回年龄为 25 或 30 的用户。

输入 SQL

SELECT * FROM users WHERE age  in ( 25 , 30)

输出

[2026-07-23 11:41:31.655] [INFO] +----+--------+-----+--------+--------+-----------+----------------------+
[2026-07-23 11:41:31.655] [INFO] | id | name   | age | status | enable | addr      | birthday             |
[2026-07-23 11:41:31.655] [INFO] +----+--------+-----+--------+--------+-----------+----------------------+
[2026-07-23 11:41:31.655] [INFO] | 1  | Alice  | 25  | active | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 11:41:31.655] [INFO] | 2  | Bob    | 30  | active | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 11:41:31.655] [INFO] | 6  | Martin | 30  | active | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 11:41:31.655] [INFO] +----+--------+-----+--------+--------+-----------+----------------------+
[2026-07-23 11:41:31.655] [INFO] Total: 3 rows

示例 2.13：NOT IN 集合

WHERE column NOT IN (value1, value2, ...)，返回年龄既不是 25 也不是 30 的用户。

输入 SQL

SELECT * FROM users WHERE age not in ( 25 , 30)

输出

[2026-07-23 11:42:21.731] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:42:21.731] [INFO] | id | name    | age | status   | enable | addr      | birthday             |
[2026-07-23 11:42:21.731] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:42:21.731] [INFO] | 3  | Charlie | 20  | pending  | false  | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 11:42:21.731] [INFO] | 4  | David   | 35  | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 11:42:21.731] [INFO] | 5  | Eve     | 28  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 11:42:21.731] [INFO] | 7  | Davila  | 39  | active   | true   | null      | 1999-06-29T16:00:00Z |
[2026-07-23 11:42:21.731] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 11:42:21.731] [INFO] Total: 4 rows

示例 2.14：LIKE 模式匹配

WHERE column LIKE pattern（或 NOT LIKE），% 匹配任意序列、_ 匹配单个字符，返回名字包含 ‘Davi’ 的用户。

输入 SQL

SELECT * FROM users WHERE name like '%Davi%'

输出

[2026-07-23 11:43:10.536] [INFO] +----+--------+-----+----------+--------+------+----------------------+
[2026-07-23 11:43:10.536] [INFO] | id | name   | age | status   | enable | addr | birthday             |
[2026-07-23 11:43:10.536] [INFO] +----+--------+-----+----------+--------+------+----------------------+
[2026-07-23 11:43:10.536] [INFO] | 4  | David  | 35  | inactive | true   | xian | 1955-11-28T16:00:00Z |
[2026-07-23 11:43:10.536] [INFO] | 7  | Davila | 39  | active   | true   | null | 1999-06-29T16:00:00Z |
[2026-07-23 11:43:10.536] [INFO] +----+--------+-----+----------+--------+------+----------------------+
[2026-07-23 11:43:10.536] [INFO] Total: 2 rows

示例 2.15：REGEXP 正则匹配

WHERE column REGEXP pattern（或 NOT REGEXP），返回名字以 ‘A’ 开头的用户。

输入 SQL

SELECT * FROM users WHERE name REGEXP '^A.*'

输出

[2026-07-23 11:43:45.932] [INFO] +----+-------+-----+--------+--------+---------+----------------------+
[2026-07-23 11:43:45.933] [INFO] | id | name  | age | status | enable | addr    | birthday             |
[2026-07-23 11:43:45.933] [INFO] +----+-------+-----+--------+--------+---------+----------------------+
[2026-07-23 11:43:45.933] [INFO] | 1  | Alice | 25  | active | true   | beijing | 2020-04-08T16:00:00Z |
[2026-07-23 11:43:45.933] [INFO] +----+-------+-----+--------+--------+---------+----------------------+
[2026-07-23 11:43:45.933] [INFO] Total: 1 rows

示例 2.16：EXISTS 子查询

WHERE EXISTS (subquery)，按子查询中是否存在匹配记录过滤，返回至少有一笔订单的用户。

输入 SQL

SELECT * FROM users u WHERE EXISTS (   SELECT 1 FROM orders o WHERE o.user_id = u.id)

输出

[2026-07-23 12:13:18.994] [INFO] +----+---------+-----+---------+--------+----------+----------------------+
[2026-07-23 12:13:18.994] [INFO] | id | name    | age | status  | enable | addr     | birthday             |
[2026-07-23 12:13:18.994] [INFO] +----+---------+-----+---------+--------+----------+----------------------+
[2026-07-23 12:13:18.994] [INFO] | 1  | Alice   | 25  | active  | true   | beijing  | 2020-04-08T16:00:00Z |
[2026-07-23 12:13:18.994] [INFO] | 2  | Bob     | 30  | active  | true   | shanghai | 1991-08-08T15:00:00Z |
[2026-07-23 12:13:18.994] [INFO] | 3  | Charlie | 20  | pending | false  | chengdu  | 1988-07-11T15:00:00Z |
[2026-07-23 12:13:18.994] [INFO] +----+---------+-----+---------+--------+----------+----------------------+
[2026-07-23 12:13:18.994] [INFO] Total: 3 rows

GroupBy聚合

GROUP BY 按指定列分组，配合聚合函数（COUNT、SUM、AVG、MIN、MAX）对每组计算，HAVING 对分组结果再过滤。

本节输入数据（users 表）：

id	name	age	status	enable	addr	birthday
1	Alice	25	active	true	beijing	2020-04-09
2	Bob	30	active	true	shanghai	1991-08-09
3	Charlie	20	pending	false	chengdu	1988-07-12
4	David	35	inactive	true	xian	1955-11-29
5	Eve	28	active	true	chongqing	2003-07-12
6	Martin	30	active	true	guangzhou	1978-06-30
7	Davila	39	active	true	null	1999-06-30

示例 5.1：GROUP BY + 聚合 + 排序

GROUP BY column, aggregate_function(column)，按 status 分组统计人数与平均年龄，并按 status 排序。

输入 SQL

SELECT status, COUNT(*) as count, AVG(age) as avg_age FROM users GROUP BY status ORDER BY status

输出

[2026-07-23 17:02:47.779] [INFO] +----------+-------+---------+
[2026-07-23 17:02:47.780] [INFO] | status   | count | avg_age |
[2026-07-23 17:02:47.780] [INFO] +----------+-------+---------+
[2026-07-23 17:02:47.780] [INFO] | active   | 5     | 30.4    |
[2026-07-23 17:02:47.780] [INFO] | inactive | 1     | 35.0    |
[2026-07-23 17:02:47.780] [INFO] | pending  | 1     | 20.0    |
[2026-07-23 17:02:47.780] [INFO] +----------+-------+---------+
[2026-07-23 17:02:47.780] [INFO] Total: 3 rows

示例 5.2：HAVING 过滤分组

GROUP BY ... HAVING condition ORDER BY ...，按 status 分组后仅保留人数大于 1 的组，并按人数降序。

输入 SQL

SELECT status, COUNT(age) as count, AVG(age) as avg_age FROM users GROUP BY status HAVING COUNT(age) >1 ORDER BY count DESC

输出

[2026-07-23 17:04:55.056] [INFO] +--------+-------+---------+
[2026-07-23 17:04:55.056] [INFO] | status | count | avg_age |
[2026-07-23 17:04:55.056] [INFO] +--------+-------+---------+
[2026-07-23 17:04:55.056] [INFO] | active | 5     | 30.4    |
[2026-07-23 17:04:55.056] [INFO] +--------+-------+---------+
[2026-07-23 17:04:55.056] [INFO] Total: 1 rows

以下 5 条示例的输入数据（users 表，含 salary 列）：

id	name	age	status	enable	addr	birthday	salary
1	Alice	25	active	true	beijing	2020-04-09	5000.0
2	Bob	30	active	true	shanghai	1991-08-09	6000.0
3	Charlie	20	pending	false	chengdu	1988-07-12	4500.0
4	David	35	inactive	true	xian	1955-11-29	7000.0
5	Eve	28	active	true	chongqing	2003-07-12	5500.0
6	Martin	30	active	true	guangzhou	1978-06-30	6500.0
7	Davila	39	active	true	null	1999-06-30	8000.0

示例 8.1：COUNT 计数

按 status 分组，返回每组行数。

输入 SQL

SELECT status, COUNT(*) AS count FROM users GROUP BY status

输出

[2026-07-27 07:03:18.201] [INFO] +----------+-------+
[2026-07-27 07:03:18.201] [INFO] | status   | count |
[2026-07-27 07:03:18.201] [INFO] +----------+-------+
[2026-07-27 07:03:18.201] [INFO] | inactive | 1     |
[2026-07-27 07:03:18.201] [INFO] | pending  | 1     |
[2026-07-27 07:03:18.201] [INFO] | active   | 5     |
[2026-07-27 07:03:18.201] [INFO] +----------+-------+
[2026-07-27 07:03:18.201] [INFO] Total: 3 rows

示例 8.2：SUM 求和

按 status 分组，返回每组年龄总和。

输入 SQL

SELECT status, SUM(age) AS age_sum FROM users GROUP BY status

输出

[2026-07-27 07:07:19.828] [INFO] +----------+---------+
[2026-07-27 07:07:19.828] [INFO] | status   | age_sum |
[2026-07-27 07:07:19.830] [INFO] +----------+---------+
[2026-07-27 07:07:19.830] [INFO] | inactive | 35.0    |
[2026-07-27 07:07:19.830] [INFO] | pending  | 20.0    |
[2026-07-27 07:07:19.830] [INFO] | active   | 152.0   |
[2026-07-27 07:07:19.830] [INFO] +----------+---------+
[2026-07-27 07:07:19.830] [INFO] Total: 3 rows

示例 8.3：AVG 平均值

按 status 分组，返回每组平均年龄。

输入 SQL

SELECT status,AVG(age) AS avg_age FROM users GROUP BY status

输出

[2026-07-27 07:10:15.850] [INFO] +----------+---------+
[2026-07-27 07:10:15.850] [INFO] | status   | avg_age |
[2026-07-27 07:10:15.850] [INFO] +----------+---------+
[2026-07-27 07:10:15.850] [INFO] | inactive | 35.0    |
[2026-07-27 07:10:15.850] [INFO] | pending  | 20.0    |
[2026-07-27 07:10:15.850] [INFO] | active   | 30.4    |
[2026-07-27 07:10:15.850] [INFO] +----------+---------+
[2026-07-27 07:10:15.851] [INFO] Total: 3 rows

示例 8.4：MIN 最小值

按 status 分组，返回每组最小年龄。

输入 SQL

SELECT status,MIN(age) AS active_min_age FROM users GROUP BY status

输出

[2026-07-27 07:12:58.509] [INFO] +----------+----------------+
[2026-07-27 07:12:58.509] [INFO] | status   | active_min_age |
[2026-07-27 07:12:58.509] [INFO] +----------+----------------+
[2026-07-27 07:12:58.509] [INFO] | inactive | 35             |
[2026-07-27 07:12:58.511] [INFO] | pending  | 20             |
[2026-07-27 07:12:58.511] [INFO] | active   | 25             |
[2026-07-27 07:12:58.512] [INFO] +----------+----------------+
[2026-07-27 07:12:58.512] [INFO] Total: 3 rows

示例 8.5：MAX 最大值

按 status 分组，返回每组最大年龄。

输入 SQL

SELECT status,Max(age) AS active_max_age FROM users GROUP BY status

输出

[2026-07-27 07:14:15.781] [INFO] +----------+----------------+
[2026-07-27 07:14:15.782] [INFO] | status   | active_max_age |
[2026-07-27 07:14:15.782] [INFO] +----------+----------------+
[2026-07-27 07:14:15.782] [INFO] | inactive | 35             |
[2026-07-27 07:14:15.782] [INFO] | pending  | 20             |
[2026-07-27 07:14:15.782] [INFO] | active   | 39             |
[2026-07-27 07:14:15.782] [INFO] +----------+----------------+
[2026-07-27 07:14:15.782] [INFO] Total: 3 rows

排序与分页

ORDER BY 支持单列/多列、升序（ASC）/降序（DESC）混合排序；LIMIT n 限制返回行数，LIMIT offset, count 先跳过 offset 行再取 count 行，可与 ORDER BY 组合实现分页。

本节输入数据（users 表）：

id	name	age	status	enable	addr	birthday
1	Alice	25	active	true	beijing	2020-04-09
2	Bob	30	active	true	shanghai	1991-08-09
3	Charlie	20	pending	false	chengdu	1988-07-12
4	David	35	inactive	true	xian	1955-11-29
5	Eve	28	active	true	chongqing	2003-07-12
6	Martin	30	active	true	guangzhou	1978-06-30
7	Davila	39	active	true	null	1999-06-30

示例 3.1：单列升序

ORDER BY column ASC，按年龄从小到大排序。

输入 SQL

SELECT * FROM users ORDER BY age ASC

输出

[2026-07-23 16:53:29.684] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 16:53:29.684] [INFO] | id | name    | age | status   | enable | addr      | birthday             |
[2026-07-23 16:53:29.684] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 16:53:29.684] [INFO] | 3  | Charlie | 20  | pending  | false  | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 16:53:29.684] [INFO] | 1  | Alice   | 25  | active   | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 16:53:29.684] [INFO] | 5  | Eve     | 28  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 16:53:29.684] [INFO] | 2  | Bob     | 30  | active   | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 16:53:29.685] [INFO] | 6  | Martin  | 30  | active   | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 16:53:29.685] [INFO] | 4  | David   | 35  | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 16:53:29.685] [INFO] | 7  | Davila  | 39  | active   | true   | null      | 1999-06-29T16:00:00Z |
[2026-07-23 16:53:29.685] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 16:53:29.685] [INFO] Total: 7 rows

示例 3.2：单列降序

ORDER BY column DESC，按年龄从大到小排序。

输入 SQL

SELECT * FROM users ORDER BY age DESC

输出

[2026-07-23 16:54:29.749] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 16:54:29.749] [INFO] | id | name    | age | status   | enable | addr      | birthday             |
[2026-07-23 16:54:29.749] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 16:54:29.749] [INFO] | 7  | Davila  | 39  | active   | true   | null      | 1999-06-29T16:00:00Z |
[2026-07-23 16:54:29.749] [INFO] | 4  | David   | 35  | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 16:54:29.750] [INFO] | 2  | Bob     | 30  | active   | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 16:54:29.750] [INFO] | 6  | Martin  | 30  | active   | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 16:54:29.750] [INFO] | 5  | Eve     | 28  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 16:54:29.750] [INFO] | 1  | Alice   | 25  | active   | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 16:54:29.750] [INFO] | 3  | Charlie | 20  | pending  | false  | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 16:54:29.750] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 16:54:29.750] [INFO] Total: 7 rows

示例 3.3：多列混合排序（ASC + DESC）

ORDER BY column1 ASC, column2 DESC，先按 status 升序，同组内按年龄降序。

输入 SQL

SELECT * FROM users ORDER BY status ASC, age DESC

输出

[2026-07-23 16:55:13.550] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 16:55:13.550] [INFO] | id | name    | age | status   | enable | addr      | birthday             |
[2026-07-23 16:55:13.550] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 16:55:13.550] [INFO] | 7  | Davila  | 39  | active   | true   | null      | 1999-06-29T16:00:00Z |
[2026-07-23 16:55:13.550] [INFO] | 2  | Bob     | 30  | active   | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 16:55:13.550] [INFO] | 6  | Martin  | 30  | active   | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 16:55:13.550] [INFO] | 5  | Eve     | 28  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 16:55:13.550] [INFO] | 1  | Alice   | 25  | active   | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 16:55:13.551] [INFO] | 4  | David   | 35  | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 16:55:13.551] [INFO] | 3  | Charlie | 20  | pending  | false  | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 16:55:13.551] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 16:55:13.551] [INFO] Total: 7 rows

示例 3.4：多列混合排序（DESC + ASC）

ORDER BY column1 DESC, column2 ASC，先按 enable 降序，同组内按年龄升序。

输入 SQL

SELECT * FROM users ORDER BY enable DESC, age ASC

输出

[2026-07-23 16:56:03.295] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 16:56:03.295] [INFO] | id | name    | age | status   | enable | addr      | birthday             |
[2026-07-23 16:56:03.295] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 16:56:03.295] [INFO] | 1  | Alice   | 25  | active   | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 16:56:03.296] [INFO] | 5  | Eve     | 28  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 16:56:03.296] [INFO] | 2  | Bob     | 30  | active   | true   | shanghai  | 1991-08-08T15:00:00Z |
[2026-07-23 16:56:03.296] [INFO] | 6  | Martin  | 30  | active   | true   | guangzhou | 1978-06-29T16:00:00Z |
[2026-07-23 16:56:03.296] [INFO] | 4  | David   | 35  | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-23 16:56:03.296] [INFO] | 7  | Davila  | 39  | active   | true   | null      | 1999-06-29T16:00:00Z |
[2026-07-23 16:56:03.296] [INFO] | 3  | Charlie | 20  | pending  | false  | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 16:56:03.296] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-23 16:56:03.296] [INFO] Total: 7 rows

示例 3.5：LIMIT offset, count

跳过前 2 行（offset），再返回 3 行。

输入 SQL

SELECT * FROM users LIMIT  2, 3

输出

[2026-07-28 16:58:34.179] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-28 16:58:34.179] [INFO] | id | name    | age | status   | enable | addr      | birthday             |
[2026-07-28 16:58:34.179] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-28 16:58:34.179] [INFO] | 3  | Charlie | 20  | pending  | false  | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-28 16:58:34.180] [INFO] | 4  | David   | 35  | inactive | true   | xian      | 1955-11-28T16:00:00Z |
[2026-07-28 16:58:34.180] [INFO] | 5  | Eve     | 28  | active   | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-28 16:58:34.180] [INFO] +----+---------+-----+----------+--------+-----------+----------------------+
[2026-07-28 16:58:34.180] [INFO] Total: 3 rows

示例 4.1：LIMIT n

LIMIT n，限制返回行数，返回前 3 行。

输入 SQL

SELECT * FROM users LIMIT 3

输出

[2026-07-23 16:59:29.806] [INFO] +----+---------+-----+---------+--------+----------+----------------------+
[2026-07-23 16:59:29.806] [INFO] | id | name    | age | status  | enable | addr     | birthday             |
[2026-07-23 16:59:29.807] [INFO] +----+---------+-----+---------+--------+----------+----------------------+
[2026-07-23 16:59:29.807] [INFO] | 1  | Alice   | 25  | active  | true   | beijing  | 2020-04-08T16:00:00Z |
[2026-07-23 16:59:29.807] [INFO] | 2  | Bob     | 30  | active  | true   | shanghai | 1991-08-08T15:00:00Z |
[2026-07-23 16:59:29.807] [INFO] | 3  | Charlie | 20  | pending | false  | chengdu  | 1988-07-11T15:00:00Z |
[2026-07-23 16:59:29.807] [INFO] +----+---------+-----+---------+--------+----------+----------------------+
[2026-07-23 16:59:29.807] [INFO] Total: 3 rows

示例 4.2：LIMIT offset, limit（仅命中剩余行）

跳过前 2 行后返回后续行（本例数据经前置过滤后仅剩 1 行命中）。

输入 SQL

SELECT * FROM users LIMIT  2, 3

输出

[2026-07-23 17:00:04.403] [INFO] +----+------+-----+--------+--------+-----------+----------------------+
[2026-07-23 17:00:04.403] [INFO] | id | name | age | status | enable | addr      | birthday             |
[2026-07-23 17:00:04.403] [INFO] +----+------+-----+--------+--------+-----------+----------------------+
[2026-07-23 17:00:04.403] [INFO] | 5  | Eve  | 28  | active | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 17:00:04.403] [INFO] +----+------+-----+--------+--------+-----------+----------------------+
[2026-07-23 17:00:04.403] [INFO] Total: 1 rows

示例 4.3：ORDER BY + LIMIT 分页

ORDER BY ... LIMIT offset, limit，先排序再分页。按年龄升序并取前 3 行。

输入 SQL

SELECT * FROM users order by age asc LIMIT  0, 3

输出

[2026-07-23 17:00:32.950] [INFO] +----+---------+-----+---------+--------+-----------+----------------------+
[2026-07-23 17:00:32.950] [INFO] | id | name    | age | status  | enable | addr      | birthday             |
[2026-07-23 17:00:32.950] [INFO] +----+---------+-----+---------+--------+-----------+----------------------+
[2026-07-23 17:00:32.951] [INFO] | 3  | Charlie | 20  | pending | false  | chengdu   | 1988-07-11T15:00:00Z |
[2026-07-23 17:00:32.951] [INFO] | 1  | Alice   | 25  | active  | true   | beijing   | 2020-04-08T16:00:00Z |
[2026-07-23 17:00:32.951] [INFO] | 5  | Eve     | 28  | active  | true   | chongqing | 2003-07-11T16:00:00Z |
[2026-07-23 17:00:32.951] [INFO] +----+---------+-----+---------+--------+-----------+----------------------+
[2026-07-23 17:00:32.951] [INFO] Total: 3 rows

多表Join

支持 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN、CROSS JOIN、NATURAL JOIN，并可与 WHERE 组合过滤。

本节输入数据：

users 表

id	name	age	status	enable	addr	birthday
1	Alice	25	active	true	beijing	2020-04-09
2	Bob	30	active	true	shanghai	1991-08-09
3	Charlie	20	pending	false	chengdu	1988-07-12
4	David	35	inactive	true	xian	1955-11-29
5	Eve	28	active	true	chongqing	2003-07-12
6	Martin	30	active	true	guangzhou	1978-06-30
7	Davila	39	active	true	null	1999-06-30

orders 表

id	user_id	amount
101	1	100.0
102	1	200.0
103	2	150.0
104	3	300.0

示例 6.1：INNER JOIN

INNER JOIN table ON condition，只返回两表键匹配的行，返回有订单的用户及其订单明细。

输入 SQL

SELECT u.id, u.name, u.age, o.id as order_id, o.amount FROM users u INNER JOIN orders o ON u.id = o.user_id

输出

[2026-07-23 22:26:39.908] [INFO] +------+---------+-------+----------+----------+
[2026-07-23 22:26:39.909] [INFO] | u.id | u.name  | u.age | order_id | o.amount |
[2026-07-23 22:26:39.909] [INFO] +------+---------+-------+----------+----------+
[2026-07-23 22:26:39.909] [INFO] | 1    | Alice   | 25    | 1        | 100.0    |
[2026-07-23 22:26:39.909] [INFO] | 1    | Alice   | 25    | 1        | 200.0    |
[2026-07-23 22:26:39.909] [INFO] | 2    | Bob     | 30    | 2        | 150.0    |
[2026-07-23 22:26:39.910] [INFO] | 3    | Charlie | 20    | 3        | 300.0    |
[2026-07-23 22:26:39.910] [INFO] +------+---------+-------+----------+----------+
[2026-07-23 22:26:39.910] [INFO] Total: 4 rows

示例 6.2：INNER JOIN + WHERE

关联后再应用额外过滤，返回订单金额不低于 150 的活跃用户。

输入 SQL

SELECT u.name, u.age, u.status, o.id as order_id, o.amount FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE u.status = 'active' AND o.amount >= 150

输出

[2026-07-23 22:27:57.249] [INFO] +--------+-------+----------+----------+----------+
[2026-07-23 22:27:57.249] [INFO] | u.name | u.age | u.status | order_id | o.amount |
[2026-07-23 22:27:57.249] [INFO] +--------+-------+----------+----------+----------+
[2026-07-23 22:27:57.250] [INFO] | Alice  | 25    | active   | 1        | 200.0    |
[2026-07-23 22:27:57.250] [INFO] | Bob    | 30    | active   | 2        | 150.0    |
[2026-07-23 22:27:57.250] [INFO] +--------+-------+----------+----------+----------+
[2026-07-23 22:27:57.251] [INFO] Total: 2 rows

示例 6.3：LEFT JOIN

LEFT JOIN table ON condition，返回左表全部行，右表无匹配时为 NULL，返回所有用户及其订单（如有）。

输入 SQL

SELECT u.id, u.name, u.age, o.id as order_id, o.amount FROM users u LEFT JOIN orders o ON u.id = o.user_id

输出

[2026-07-23 22:28:58.433] [INFO] +------+---------+-------+----------+----------+
[2026-07-23 22:28:58.434] [INFO] | u.id | u.name  | u.age | order_id | o.amount |
[2026-07-23 22:28:58.434] [INFO] +------+---------+-------+----------+----------+
[2026-07-23 22:28:58.434] [INFO] | 1    | Alice   | 25    | 1        | 100.0    |
[2026-07-23 22:28:58.434] [INFO] | 1    | Alice   | 25    | 1        | 200.0    |
[2026-07-23 22:28:58.434] [INFO] | 2    | Bob     | 30    | 2        | 150.0    |
[2026-07-23 22:28:58.435] [INFO] | 3    | Charlie | 20    | 3        | 300.0    |
[2026-07-23 22:28:58.435] [INFO] | 4    | David   | 35    | 4        | null     |
[2026-07-23 22:28:58.435] [INFO] | 5    | Eve     | 28    | 5        | null     |
[2026-07-23 22:28:58.435] [INFO] | 6    | Martin  | 30    | 6        | null     |
[2026-07-23 22:28:58.435] [INFO] | 7    | Davila  | 39    | 7        | null     |
[2026-07-23 22:28:58.435] [INFO] +------+---------+-------+----------+----------+
[2026-07-23 22:28:58.436] [INFO] Total: 8 rows

示例 6.4：RIGHT JOIN

RIGHT JOIN table ON condition，返回右表全部行，左表无匹配时为 NULL，返回所有订单及其用户（如有）。

输入 SQL

SELECT u.id, u.name, u.age, o.id as order_id, o.amount FROM users u RIGHT JOIN orders o ON u.id = o.user_id

输出

[2026-07-23 22:30:04.935] [INFO] +------+---------+-------+----------+----------+
[2026-07-23 22:30:04.935] [INFO] | u.id | u.name  | u.age | order_id | o.amount |
[2026-07-23 22:30:04.935] [INFO] +------+---------+-------+----------+----------+
[2026-07-23 22:30:04.935] [INFO] | 1    | Alice   | 25    | 1        | 100.0    |
[2026-07-23 22:30:04.935] [INFO] | 1    | Alice   | 25    | 1        | 200.0    |
[2026-07-23 22:30:04.935] [INFO] | 2    | Bob     | 30    | 2        | 150.0    |
[2026-07-23 22:30:04.935] [INFO] | 3    | Charlie | 20    | 3        | 300.0    |
[2026-07-23 22:30:04.936] [INFO] +------+---------+-------+----------+----------+
[2026-07-23 22:30:04.936] [INFO] Total: 4 rows

示例 6.5：FULL JOIN

FULL JOIN table ON condition，返回两表全部行，匹配则关联，不匹配的一侧为 NULL。

输入 SQL

SELECT u.id, u.name, u.age, o.id as order_id, o.amount FROM users u FULL JOIN orders o ON u.id = o.user_id

输出

[2026-07-23 22:31:13.677] [INFO] +------+---------+-------+----------+----------+
[2026-07-23 22:31:13.679] [INFO] | u.id | u.name  | u.age | order_id | o.amount |
[2026-07-23 22:31:13.679] [INFO] +------+---------+-------+----------+----------+
[2026-07-23 22:31:13.679] [INFO] | 1    | Alice   | 25    | 1        | 100.0    |
[2026-07-23 22:31:13.680] [INFO] | 1    | Alice   | 25    | 1        | 200.0    |
[2026-07-23 22:31:13.680] [INFO] | 2    | Bob     | 30    | 2        | 150.0    |
[2026-07-23 22:31:13.680] [INFO] | 3    | Charlie | 20    | 3        | 300.0    |
[2026-07-23 22:31:13.681] [INFO] | 4    | David   | 35    | 4        | null     |
[2026-07-23 22:31:13.681] [INFO] | 5    | Eve     | 28    | 5        | null     |
[2026-07-23 22:31:13.681] [INFO] | 6    | Martin  | 30    | 6        | null     |
[2026-07-23 22:31:13.682] [INFO] | 7    | Davila  | 39    | 7        | null     |
[2026-07-23 22:31:13.682] [INFO] +------+---------+-------+----------+----------+
[2026-07-23 22:31:13.682] [INFO] Total: 8 rows

示例 6.6：FULL JOIN（重复执行验证）

同 6.5 的 FULL JOIN 语义，重复执行结果一致。

输入 SQL

SELECT u.id, u.name, u.age, o.id as order_id, o.amount FROM users u FULL JOIN orders o ON u.id = o.user_id

输出

[2026-07-24 08:49:12.518] [INFO] +------+---------+-------+----------+----------+
[2026-07-24 08:49:12.518] [INFO] | u.id | u.name  | u.age | order_id | o.amount |
[2026-07-24 08:49:12.518] [INFO] +------+---------+-------+----------+----------+
[2026-07-24 08:49:12.518] [INFO] | 1    | Alice   | 25    | 1        | 100.0    |
[2026-07-24 08:49:12.519] [INFO] | 1    | Alice   | 25    | 1        | 200.0    |
[2026-07-24 08:49:12.519] [INFO] | 2    | Bob     | 30    | 2        | 150.0    |
[2026-07-24 08:49:12.519] [INFO] | 3    | Charlie | 20    | 3        | 300.0    |
[2026-07-24 08:49:12.519] [INFO] | 4    | David   | 35    | 4        | null     |
[2026-07-24 08:49:12.519] [INFO] | 5    | Eve     | 28    | 5        | null     |
[2026-07-24 08:49:12.519] [INFO] | 6    | Martin  | 30    | 6        | null     |
[2026-07-24 08:49:12.519] [INFO] | 7    | Davila  | 39    | 7        | null     |
[2026-07-24 08:49:12.519] [INFO] +------+---------+-------+----------+----------+
[2026-07-24 08:49:12.519] [INFO] Total: 8 rows

示例 6.7：CROSS JOIN

CROSS JOIN table，返回两表笛卡尔积（所有行组合），将每个用户与每笔订单配对。

输入 SQL

SELECT u.name, u.age, o.id, o.amount FROM users u CROSS JOIN orders o

输出

[2026-07-24 08:53:28.324] [INFO] +---------+-------+------+----------+
[2026-07-24 08:53:28.324] [INFO] | u.name  | u.age | o.id | o.amount |
[2026-07-24 08:53:28.324] [INFO] +---------+-------+------+----------+
[2026-07-24 08:53:28.324] [INFO] | Alice   | 25    | 101  | 100.0    |
[2026-07-24 08:53:28.324] [INFO] | Alice   | 25    | 102  | 200.0    |
[2026-07-24 08:53:28.326] [INFO] | Alice   | 25    | 103  | 150.0    |
[2026-07-24 08:53:28.326] [INFO] | Alice   | 25    | 104  | 300.0    |
[2026-07-24 08:53:28.326] [INFO] | Bob     | 30    | 101  | 100.0    |
[2026-07-24 08:53:28.326] [INFO] | Bob     | 30    | 102  | 200.0    |
[2026-07-24 08:53:28.326] [INFO] | Bob     | 30    | 103  | 150.0    |
[2026-07-24 08:53:28.326] [INFO] | Bob     | 30    | 104  | 300.0    |
[2026-07-24 08:53:28.326] [INFO] | Charlie | 20    | 101  | 100.0    |
[2026-07-24 08:53:28.326] [INFO] | Charlie | 20    | 102  | 200.0    |
[2026-07-24 08:53:28.326] [INFO] | Charlie | 20    | 103  | 150.0    |
[2026-07-24 08:53:28.326] [INFO] | Charlie | 20    | 104  | 300.0    |
[2026-07-24 08:53:28.326] [INFO] | David   | 35    | 101  | 100.0    |
[2026-07-24 08:53:28.326] [INFO] | David   | 35    | 102  | 200.0    |
[2026-07-24 08:53:28.326] [INFO] | David   | 35    | 103  | 150.0    |
[2026-07-24 08:53:28.326] [INFO] | David   | 35    | 104  | 300.0    |
[2026-07-24 08:53:28.326] [INFO] | Eve     | 28    | 101  | 100.0    |
[2026-07-24 08:53:28.326] [INFO] | Eve     | 28    | 102  | 200.0    |
[2026-07-24 08:53:28.326] [INFO] | Eve     | 28    | 103  | 150.0    |
[2026-07-24 08:53:28.326] [INFO] | Eve     | 28    | 104  | 300.0    |
[2026-07-24 08:53:28.326] [INFO] | Martin  | 30    | 101  | 100.0    |
[2026-07-24 08:53:28.326] [INFO] | Martin  | 30    | 102  | 200.0    |
[2026-07-24 08:53:28.326] [INFO] | Martin  | 30    | 103  | 150.0    |
[2026-07-24 08:53:28.326] [INFO] | Martin  | 30    | 104  | 300.0    |
[2026-07-24 08:53:28.328] [INFO] | Davila  | 39    | 101  | 100.0    |
[2026-07-24 08:53:28.328] [INFO] | Davila  | 39    | 102  | 200.0    |
[2026-07-24 08:53:28.328] [INFO] | Davila  | 39    | 103  | 150.0    |
[2026-07-24 08:53:28.328] [INFO] | Davila  | 39    | 104  | 300.0    |
[2026-07-24 08:53:28.328] [INFO] +---------+-------+------+----------+
[2026-07-24 08:53:28.328] [INFO] Total: 28 rows

示例 6.8：NATURAL JOIN

NATURAL JOIN table，自动按同名列关联（如 id），只返回同名列值相等的行。本例两表同名列为 id，无相等值，结果为空。

输入 SQL

SELECT u.name, u.age, o.id, o.amount FROM users u NATURAL JOIN orders o

输出

[2026-07-24 14:02:03.903] [INFO] +--------+-------+------+----------+
[2026-07-24 14:02:03.903] [INFO] | u.name | u.age | o.id | o.amount |
[2026-07-24 14:02:03.903] [INFO] +--------+-------+------+----------+
[2026-07-24 14:02:03.903] [INFO] +--------+-------+------+----------+
[2026-07-24 14:02:03.903] [INFO] Total: 0 rows

内置函数

JQuick-SQL 内置 200+ 函数，由 jquick-transform-function 提供，覆盖字符串、日期、聚合、加解密、类型转换、业务校验等类别；同时支持通过 SPI 扩展自定义函数。注册后的函数可像内置函数一样在 SELECT、WHERE、GROUP BY、HAVING、ORDER BY 等任意子句中直接使用，函数名大小写不敏感。

类别	说明	代表函数
字符串	字符串处理	`toUpper`、`toLower`、`concat`、`substring`、`replace`、`length`、`trim`、`mask`、`md5`、`base64Encode`、`reverse`、`split`
数学	数学与聚合	`abs`、`ceil`、`floor`、`round`、`max`、`min`、`sum`、`avg`、`count`、`sqrt`、`pow`、`log`、`toInt`、`toDouble`、`stdDev`、`median`
日期	日期与时间	`now`、`today`、`addDays`、`formatDate`、`day`、`month`、`year`、`isWeekend`、`daysBetween`、`startOfMonth`、`endOfYear`
条件	条件逻辑	`caseWhen`、`if`、`ifElse`、`coalesce`、`nvl`、`between`、`switch`、`defaultIfNull`
转换	类型转换	`toBoolean`、`toDate`、`toDateTime`、`toShort`
业务	业务校验与脱敏	`bankCardMask`、`bankCardValidate`、`idCardValidate`、`idCardInfo`、`phoneInfo`、`phoneMask`、`emailMask`、`isEmail`
加密	加解密（AES / RSA / ECC）	`aesEncrypt`、`aesDecrypt`、`rsaEncrypt`、`rsaDecrypt`、`eccEncrypt`、`eccDecrypt`、`aesGenerateKey`
集合	集合操作	`isEmpty`、`size`、`join`
位运算	位操作	`bitAnd`、`bitOr`、`bitXor`
几何	几何与组合数学	`areaCircle`、`areaRectangle`、`distance`、`factorial`、`gcd`、`lcm`、`permutation`
随机	随机生成	`randomInt`、`randomDouble`、`randomUUID`、`randomString`、`randomChoice`、`randomSample`
扩展	格式化与强转	`cast`、`formatNumber`、`parseNumber`、`toCurrency`、`toPercentage`、`typeOf`、`toArray`、`toList`
JSON	JSON 处理	`toJson`

所有内置函数可直接在 SQL 中调用：

输入 SQL

SELECT toUpper(name) AS upper_name,
       length(addr) AS addr_len,
       formatDate(birthday, 'yyyy-MM') AS birth_month,
       caseWhen(age >= 30, 'senior', 'junior') AS level,
       mask(phoneInfo(addr), '***') AS masked_addr
FROM users

输出

[INFO] +-----------+----------+-------------+--------+-------------+
[INFO] | upper_name | addr_len | birth_month | level  | masked_addr |
[INFO] +-----------+----------+-------------+--------+-------------+
[INFO] | ALICE     | 7        | 2020-04      | junior | ***         |
[INFO] | BOB       | 8        | 1991-08      | senior | ***         |
[INFO] +-----------+----------+-------------+--------+-------------+
[INFO] Total: 7 rows

Java API 使用

执行引擎 JQuickSQL

方法	说明
`JQuickSQL.embedded(int parallelism)`	创建指定并行度的嵌入式 SQL 引擎
`JQuickSQL.builder()`	创建 Builder 进行自定义配置
`registerTable(String name, List<JQuickColumnMeta> columns, List<JQuickRow> rows)`	注册表
`execute(String sql)`	执行 SQL 查询，返回 `JQuickDataSet`
`getTable(String name)`	获取已注册的表数据
`hasTable(String name)`	判断表是否存在
`getRegisteredTables()`	获取所有已注册表名
`shutdown()`	关闭引擎

Builder 配置

JQuickSQL sql = JQuickSQL.builder()
    .embedded(2)           // Use embedded mode with 2 workers
    .parallelism(2)        // Set parallelism
    .table("products", columns, rows)  // Register table
    .build();

解析 SQL 为 AST

不执行查询、只做解析时，使用 JQuickSQLExecutor 一行得到强类型 AST：

JQuickSQLExecutor executor = new JQuickSQLExecutor();
JQuickQueryNode ast = executor.execute(
        "SELECT dept, COUNT(*) FROM emp WHERE salary > 20000 GROUP BY dept HAVING COUNT(*) > 1"
);
System.out.println(ast);

XML 动态 SQL

iBatis 风格：SQL 维护在 XML 中，接口由 JDK 动态代理实现，#{param} 占位符绑定方法参数。

XML 配置文件（如 classpath 下的 jquick-sql.xml）：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqls PUBLIC "-//PAOHAIJIAO//DTD API JAVA 1.0//EN"
        "classpath:paohaijiao/dtd/Jquick-sql.dtd">
<sqls namespace="com.example.service.UserService">
    <sql name="getUsers" returnClass="java.util.List">
        SELECT * FROM users LIMIT #{limit}
    </sql>
</sqls>

服务接口（方法名与 <sql> 的 name 一致，参数用 @Param 绑定占位符）：

package com.example.service;

import com.github.paohaijiao.statement.JQuickDataSet;
import com.github.paohaijiao.xml.param.Param;

public interface UserService {
    JQuickDataSet getUsers(@Param("limit") Integer limit);
}

注册表数据并创建服务：

// Create table data
JQuickTable table = new JQuickTable("users", getUserColumns(), getUserRows());
JQuickJavaXmlParseFactory handler = new JQuickJavaXmlParseFactory(Arrays.asList(table));

// Create factory with XML configuration
JQuickFactory factory = new JQuickXmlFactory(handler, "jquick-sql.xml");

// Generate service API dynamically
UserService userService = factory.createApi(UserService.class);

// Execute queries
JQuickDataSet dataSet = userService.getUsers(2);
dataSet.printTable();  // Print the result table

XML 元素说明：

XML 元素	属性	说明
`<sqls>`	`namespace`	服务接口的全限定名
`<sql>`	`name`	服务接口中的方法名
`<sql>`	`returnClass`	方法返回类型（如 `java.util.List`、`java.lang.Integer`）

参数绑定使用 #{paramName} 语法：

<sql name="findUsers" returnClass="java.util.List">
    SELECT * FROM users
    WHERE age > #{minAge}
    AND status = #{status}
    LIMIT #{limit}
</sql>

自定义函数（SPI 扩展）

函数管理器 JQuickMethodInvocationManager 是单例，启动时通过 SPI 加载所有函数提供者并按 @Priority 注解排序，启动日志会打印加载数量：

[INFO] Loaded 259 functions via SPI

扩展自定义函数只需三步。下面以实现 jsonPath(json, path) 函数为例。

第 1 步：实现函数提供者

继承 JQuickBaseFunctionFunctionProvider，在 invoke 方法中实现函数逻辑：

package com.github.paohaijiao.provider;

import com.github.paohaijiao.builder.JSONPathQueryBuilder;
import com.github.paohaijiao.exception.JAssert;
import com.github.paohaijiao.function.domain.JQuickBaseFunctionFunctionProvider;
import com.github.paohaijiao.model.JSONObject;
import com.github.paohaijiao.model.JSONPathResult;
import com.github.paohaijiao.spi.anno.Priority;
import com.github.paohaijiao.spi.constants.PriorityConstants;

import java.util.List;

@Priority(PriorityConstants.SYSTEM_HIGH)
public class JQuickJSonPathFunctionFunctionProvider extends JQuickBaseFunctionFunctionProvider {

    public JQuickJSonPathFunctionFunctionProvider() {
        super("jsonPath", "Extract data by JSON path expression - usage: jsonPath(jsonField, pathExpression)");
    }

    @Override
    public Object invoke(List<Object> args) {
        validateArgCount(args, 2);
        Object object = args.get(0);
        JAssert.notNull(object, "para1 require not null");
        String path = (String) args.get(1);
        JSONObject obj;
        if (object instanceof JSONObject) {
            obj = (JSONObject) object;
        } else if (object instanceof java.util.Map) {
            @SuppressWarnings("unchecked")
            java.util.Map<String, Object> map = (java.util.Map<String, Object>) object;
            obj = new JSONObject(map);
        } else {
            throw new IllegalArgumentException(
                    "jsonPath first argument must be a JSON object or row, but was: "
                            + (object == null ? "null" : object.getClass().getName()));
        }
        JSONPathResult result = JSONPathQueryBuilder.from(obj)
                .path(path)
                .execute();
        return result.getRawData();
    }
}

要点：

构造函数 super(methodName, description) 注册函数名与说明，函数名即 SQL 中调用的名字（大小写不敏感）。
JQuickBaseFunctionFunctionProvider 提供参数转换与校验辅助方法：asString、asInt、asLong、asDouble、asBoolean、validateArgCount、validateArgCountRange。
@Priority(PriorityConstants.SYSTEM_HIGH) 声明优先级；同名函数的其他实现（如有）将排在其后。

第 2 步：SPI 注册

在 src/main/resources/META-INF/services/ 下创建名为 com.github.paohaijiao.function.core.JQuickMethodFunctionProvider 的文件，内容为实现类全限定名（每行一个，支持 # 注释）：

com.github.paohaijiao.provider.JQuickJSonPathFunctionFunctionProvider

第 3 步：在 SQL 中使用

注册后即可像内置函数一样在 SQL 中调用：

输入 SQL

SELECT name, age, status, enable, jsonPath(detail, '$.position') FROM users

输出

[INFO] +----+---------+-----+----------+--------+--------------------+
[INFO] | id | name    | age | status   | enable | position            |
[INFO] +----+---------+-----+----------+--------+--------------------+
[INFO] | 1  | Alice   | 25  | active   | true   | Senior Developer   |
[INFO] | 2  | Bob     | 30  | active   | true   | Marketing Manager  |
[INFO] | 3  | Charlie | 20  | pending  | false  | Junior Accountant  |
[INFO] | 4  | David   | 35  | inactive | true   | HR Director        |
[INFO] | 5  | Eve     | 28  | active   | true   | Frontend Developer |
[INFO] | 6  | Martin  | 30  | active   | true   | Architect          |
[INFO] | 7  | Davila  | 39  | active   | true   | Tech Lead          |
[INFO] +----+---------+-----+----------+--------+--------------------+
[INFO] Total: 7 rows

除 SPI 自动发现外，也可以在代码中手动注册（适合运行时需要动态注册的场景）：

JQuickMethodInvocationManager manager = JQuickMethodInvocationManager.getInstance();
manager.registerInvoker(new JQuickJSonPathFunctionFunctionProvider());

// Or register a simple function directly as a lambda
manager.registerInvoker("greet", args -> "Hello, " + args.get(0), "Concatenate a greeting", PriorityConstants.USER_HIGH);

手动注册与 SPI 加载共享同一注册表：同名函数后注册不会覆盖先注册的（如需覆盖使用 registerOrReplaceInvoker）。多个同名函数由 @Priority 决定优先调用哪一个，数值越小优先级越高，未指定时默认为 5000（PriorityConstants.USER_HIGHEST）。

自定义数据源接入

JQuick-SQL 不直接连接外部数据库。外部数据先转换为 JQuickDataSet（JQuickColumnMeta 列元数据 + JQuickRow 行数据），再通过 registerTable / JQuickTable 注册进引擎执行查询。

配合 jquick-connector 可以将关系型数据库、文件、NoSQL、REST API 等异构数据源接入为 JQuickDataSet，再用 JQuick-SQL 做查询、关联与聚合，无需数据搬迁：

<dependency>
    <groupId>io.github.paohaijiao</groupId>
    <artifactId>jquick-connector</artifactId>
    <version>${latest.version}</version>
</dependency>

常见问题 FAQ

Q：JQuick-SQL 是 JDBC 驱动吗？能连接 MySQL / Oracle 吗？

不是。它是内存查询引擎，不提供 JDBC 方言层与数据库驱动。数据由应用注册为数据集（内存行数据，或 jquick-connector 等组件产出的行数据），SQL 在进程内对数据集执行。

Q：数据从哪儿来？

两种方式：代码中直接构造 JQuickColumnMeta + JQuickRow 并 registerTable；或通过 jquick-connector 接入外部数据源转换为 JQuickDataSet。

Q：函数名区分大小写吗？

不区分。toUpper(name)、TOUPPER(name) 等价。

Q：自定义函数写了但没生效？

最常见原因是 SPI 注册文件名不对：文件名必须严格等于接口全限定名 com.github.paohaijiao.function.core.JQuickMethodFunctionProvider，放在 META-INF/services/ 下，内容为函数实现类的全限定名。文件名写错会导致 ServiceLoader 找不到实现。

Q：同名函数有多个实现时调用哪个？

按 @Priority 数值排序，数值小者优先；未标注时默认 5000（PriorityConstants.USER_HIGHEST）。手动注册的同名函数不会覆盖已注册的，需要覆盖时用 registerOrReplaceInvoker。

Q：嵌入式模式使用完要注意什么？

嵌入式 worker 会占用本机 19001 起的端口，使用完毕务必调用 sql.shutdown() 释放。

Q：支持哪些 SQL 语法？

SELECT（ DISTINCT、别名、表达式、CASE WHEN）、WHERE（比较、AND/OR/NOT、BETWEEN、IN、LIKE、REGEXP、IS NULL、EXISTS）、JOIN（INNER/LEFT/RIGHT/FULL/CROSS/NATURAL）、GROUP BY/HAVING、ORDER BY、LIMIT/OFFSET、集合运算（UNION/MINUS/INTERSECT）、子查询、函数调用。

版本说明

项目	说明
当前版本	5.0.0
Maven 坐标	`io.github.paohaijiao:jquick-sql-runtime:5.0.0`（传递引入 `jquick-sql-query`、`jquick-sql-plan`、`jquick-sql-parser`、`jquick-sql-core`）
运行环境	JDK 1.8+，Maven 3.x
执行层依赖	gRPC 1.80.0 + protobuf-java 3.21.12 + grpc-netty-shaded，序列化使用 snappy-java

License & 仓库链接

JQuick-SQL 基于 Apache License 2.0 开源，可商用。

GitHub 仓库：https://github.com/paohaijiao/jquick-sql
Maven Central：io.github.paohaijiao:jquick-sql-runtime

JQuick-SQL

同样的数据加工，手写循环过滤要付出什么

目录

简介

核心特性

快速开始

Maven 依赖引入

语法示例

基础查询

示例 1.1：查询全部行与列

示例 1.2：查询指定列

示例 1.3：在 SELECT 中使用内置函数

示例 1.4：算术表达式与别名

示例 1.5：CASE WHEN 条件表达式

示例 1.6：DISTINCT 去重

示例 1.7：布尔取反表达式

示例 1.8：常量表达式作为输出列

Where过滤

示例 2.1：等值条件

示例 2.2：AND 多条件

示例 2.3：OR 条件

示例 2.4：括号嵌套逻辑

示例 2.5：常量布尔条件

示例 2.6：布尔列直接作为条件

示例 2.7：过滤条件中调用函数

示例 2.8：IS NULL

示例 2.9：IS NOT NULL

示例 2.10：比较运算符

示例 2.11：BETWEEN 范围

示例 2.12：IN 集合

示例 2.13：NOT IN 集合

示例 2.14：LIKE 模式匹配

示例 2.15：REGEXP 正则匹配

示例 2.16：EXISTS 子查询

GroupBy聚合

示例 5.1：GROUP BY + 聚合 + 排序

示例 5.2：HAVING 过滤分组

示例 8.1：COUNT 计数

示例 8.2：SUM 求和

示例 8.3：AVG 平均值

示例 8.4：MIN 最小值

示例 8.5：MAX 最大值

排序与分页

示例 3.1：单列升序

示例 3.2：单列降序

示例 3.3：多列混合排序（ASC + DESC）

示例 3.4：多列混合排序（DESC + ASC）

示例 3.5：LIMIT offset, count

示例 4.1：LIMIT n

示例 4.2：LIMIT offset, limit（仅命中剩余行）

示例 4.3：ORDER BY + LIMIT 分页

多表Join

示例 6.1：INNER JOIN

示例 6.2：INNER JOIN + WHERE

示例 6.3：LEFT JOIN

示例 6.4：RIGHT JOIN

示例 6.5：FULL JOIN

示例 6.6：FULL JOIN（重复执行验证）

示例 6.7：CROSS JOIN

示例 6.8：NATURAL JOIN

内置函数

Java API 使用

执行引擎 JQuickSQL

Builder 配置

解析 SQL 为 AST

XML 动态 SQL

自定义函数（SPI 扩展）

自定义数据源接入

常见问题 FAQ

版本说明

License & 仓库链接