sql 语法

本指导提供了AppCube对象查询语言SOQL、对象搜索语言SOSL使用指南

功能清单

分类	语句	支撑程度
DDL	新建表	不支持
	删除表	不支持
	修改表	不支持
	新增索引	不支持
	查看索引	支持
	重建索引	支持
DML	新增数据	不支持
	删除数据	不支持
	更新数据	不支持
DCL	用户赋权限	不支持
	取消用户权限	不支持
DQL	查询系统中表清单	部分支持
	查询表结构	支持
	查询表数据	支持

对象数据查询

功能描述

AppCube 提供了对象查询语言 SOQL, 可以在 script、flow 中进行调用

SOQL 语法

SOQL 查询:

SELECT
   [/*+OSQL_NATIVE | +OSQL_NOSYSFIELD*/]
   [ALL | DISTINCT ]
   select_expr [, select_expr] ...
   {FROM table_references [FORCE INDEX(index_name)]}
   [WHERE where_condition]
   [GROUP BY {col_name | expr}]
   [HAVING where_condition]
   [ORDER BY {col_name | expr }
     [ASC | DESC]]
   [LIMIT {[offset,] row_count}]
   [FOR {UPDATE }

SELECT
   [/*+OSQL_NATIVE | +OSQL_NOSYSFIELD*/]
   [ALL | DISTINCT ]
   select_expr [, select_expr] ...
   {FROM table_references [FORCE INDEX(index_name)]}
   [WHERE where_condition]
   [GROUP BY {col_name | expr}]
   [HAVING where_condition]
   [ORDER BY {col_name | expr }
     [ASC | DESC]]
   [LIMIT {[offset,] row_count}]
   [FOR {UPDATE }

SOQL 搜索:

SEARCH
   [ALL ]
   select_expr [, select_expr] ...
   {FROM table_name}
   [WHERE where_condition]
   [LIMIT {[offset,] row_count}]

SEARCH
   [ALL ]
   select_expr [, select_expr] ...
   {FROM table_name}
   [WHERE where_condition]
   [LIMIT {[offset,] row_count}]

###　查询语法

####　 DISTINCT

功能说明：

对象的记录进行排重处理。

对象的记录进行排重处理。

使用样例：

sql

SELECT DISTINCT f1 FROM t1

SELECT DISTINCT f1 FROM t1

####　通配符%

功能说明：

对象属性模糊查询。

对象属性模糊查询。

使用样例：

sql

SELECT f1 FROM t1 WHERE f1 like 'abc%'

SELECT f1 FROM t1 WHERE f1 like 'abc%'

约束限制：

支持%通配符，数据量大的时候慎用,对性能有一定影响。

支持%通配符，数据量大的时候慎用,对性能有一定影响。

GROUP BY 子句

功能说明：

GROUP BY语句与聚合函数一起使用(参考聚合函数列表)，根据一个或多个列对结果集进行分组。

GROUP BY语句与聚合函数一起使用(参考聚合函数列表)，根据一个或多个列对结果集进行分组。

使用样例：

sql

SELECT f1, count(1) FROM t1 WHERE f1=1 GROUP BY f1

SELECT f1, count(1) FROM t1 WHERE f1=1 GROUP BY f1

ORDER BY 子句

功能说明：

ORDER BY语句用于对指定的列结果集进行排序。ORDER BY 语句默认按照升序（ASC）,降序(DESC)进行排序。

ORDER BY语句用于对指定的列结果集进行排序。ORDER BY 语句默认按照升序（ASC）,降序(DESC)进行排序。

使用样例：

sql

SELECT f1, f2 FROM t1  WHERE f1 = 1  ORDER BY f1 DESC

SELECT f1, f2 FROM t1  WHERE f1 = 1  ORDER BY f1 DESC

HAVING 子句

功能说明：

在OSQL 聚合函数中使用, HAVING函数不能使用别名，可以对聚合后的结果再次过滤。

在OSQL 聚合函数中使用, HAVING函数不能使用别名，可以对聚合后的结果再次过滤。

使用样例：

sql

select f1, count(f2) cnt from t1 where f1=1 group by f2 having count(f2) >10

select f1, count(f2) cnt from t1 where f1=1 group by f2 having count(f2) >10

LIMIT 操作符

功能说明：

limit 子句用于规定要返回的记录的数目，有两种形式：
1. 当只有一个参数 `limit M` 时表示获取前 M 个记录；
2. 当有两个参数 `limit M,N` 时，表示的是从第 M 条数据开始取（程序的索引都是从0开始），最多获取 N 个记录。

limit 子句用于规定要返回的记录的数目，有两种形式：
1. 当只有一个参数 `limit M` 时表示获取前 M 个记录；
2. 当有两个参数 `limit M,N` 时，表示的是从第 M 条数据开始取（程序的索引都是从0开始），最多获取 N 个记录。

使用样例：

sql

SELECT f1 as f1, f2 as f2 FROM t1 where f1 >1 limit 0, 100

SELECT f1 as f1, f2 as f2 FROM t1 where f1 >1 limit 0, 100

IN 操作符

功能说明：

IN 操作符允许我们在 WHERE 子句中规定多个值。

IN 操作符允许我们在 WHERE 子句中规定多个值。

使用样例：

sql

SELECT f1 as f1 FROM t1 where f1 in(1,2,3,4)

SELECT f1 as f1 FROM t1 where f1 in(1,2,3,4)

INNER JOIN 关键字

功能说明：

在表中存在至少一个匹配时，INNER JOIN 关键字返回行。

在表中存在至少一个匹配时，INNER JOIN 关键字返回行。

使用样例：

sql

SELECT t1.f1 as f1 FROM t1 INNER JOIN t2 ON t1.f1=t2.f1

SELECT t1.f1 as f1 FROM t1 INNER JOIN t2 ON t1.f1=t2.f1

约束限制：

建议表的关联数不要超过4个，可能会影响查询性能。

建议表的关联数不要超过4个，可能会影响查询性能。

LEFT JOIN 关键字

功能说明：

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

使用样例：

sql

SELECT t1.f1 as f1 FROM t1 LEFT JOIN t2  ON t1.f1=t2.f2

SELECT t1.f1 as f1 FROM t1 LEFT JOIN t2  ON t1.f1=t2.f2

约束限制：

可能会影响查询性能。

可能会影响查询性能。

UNION查询

功能说明：

语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

使用样例

sql

select name, id from cmc__unique__CST  union select name, id from cmc__v__CST

select name, id from cmc__unique__CST  union select name, id from cmc__v__CST

子查询

in子查询

功能说明：

支持select子查询。

支持select子查询。

支持select子查询。

使用样例：

sql

1. In子查询
select b.f1 from t1 b where b.f1 not in (select f1 from t2 where f1 <8 and f2>10);
select b.f1 from t1 b where b.f1 in (select f1 from t2 where f1 <8 and f2>10);
2. Join In子查询
select a.f1, a.f2 from t1 b inner join (select f1 ,f2 from t2 where f1 <8 and f2>10)a on a.f1=b.f1 where b.f2 >1;
3. Cross Info子查询
select a.f1, a.f2,b.NS__f1__CST from t1 b,(select f1,f2 from t2 where f1 <8 and f2>10 group by f1)a where a.f1=b.NS__NO__CST ;
4. from子查询
select a.NS__f1__CST from (select NS__f1__CST from t1) a where a.NS__f1__CST = 'a';

1. In子查询
select b.f1 from t1 b where b.f1 not in (select f1 from t2 where f1 <8 and f2>10);
select b.f1 from t1 b where b.f1 in (select f1 from t2 where f1 <8 and f2>10);
2. Join In子查询
select a.f1, a.f2 from t1 b inner join (select f1 ,f2 from t2 where f1 <8 and f2>10)a on a.f1=b.f1 where b.f2 >1;
3. Cross Info子查询
select a.f1, a.f2,b.NS__f1__CST from t1 b,(select f1,f2 from t2 where f1 <8 and f2>10 group by f1)a where a.f1=b.NS__NO__CST ;
4. from子查询
select a.NS__f1__CST from (select NS__f1__CST from t1) a where a.NS__f1__CST = 'a';

exists子查询

功能说明

支持EXISTS子查询，EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False，EXISTS 指定一个子查询，检测行的存在。

使用示例

sql

select id, name from cmc__unique__CST where exists (select id from cmc__v__CST)

select id, name from cmc__unique__CST where exists (select id from cmc__v__CST)

hint 语法

功能说明：

通过hint语法，能够在特定场景下提升性能。

sql

1. 通过hint指定索引("force index")
   select t1.name,t1.f2, b.id from t1 left join t2 force index(t2_f2_UniqueIndex) on t1.id = t2.f1;
2. 通过hint指定是否需要返回系统属性(OSQL_NOSYSFIELD)
   select /*+OSQL_NOSYSFIELD*/ from t1 where f1=1
3. 通过hint指定Lookup属性是否需要返回Name(OSQL_NATIVE)
   select /*+OSQL_NATIVE*/ from t1 where f1=1

1. 通过hint指定索引("force index")
   select t1.name,t1.f2, b.id from t1 left join t2 force index(t2_f2_UniqueIndex) on t1.id = t2.f1;
2. 通过hint指定是否需要返回系统属性(OSQL_NOSYSFIELD)
   select /*+OSQL_NOSYSFIELD*/ from t1 where f1=1
3. 通过hint指定Lookup属性是否需要返回Name(OSQL_NATIVE)
   select /*+OSQL_NATIVE*/ from t1 where f1=1

函数

聚合函数

函数	描述
AVG(column)	返回某列的平均值
COUNT(column)	返回某列的行数（不包括 NULL 值）
COUNT(*)	返回某列的行数（不包括 NULL 值）
MAX(column)	返回某列的最高值
MIN(column)	返回某列的最低值
SUM(column)	返回某列的总和

字符串处理函数

函数	描述
CONCAT(str1,str2,…)	字符串拼接函数
CONCAT_WS(delimiter,str1,str2,…)	指定分隔符的字符串拼接函数
SUBSTR(str, start, length)	截取字符串,str 为字符串；start 为起始位置；length 为长度
SUBSTRING_INDEX(str, delimiter, number)	在指定数量的分隔符出现之前返回字符串的子字符串
UPPER(str)	返回字符串 str 根据当前字符集映射所有字符为大写
LOWER(str)	返回字符串 str 根据当前字符集映射转为小写字母

时间处理函数

函数	描述
NOW()	返回当前的日期和时间
CURDATE()	当前的日期
CURTIME()	当前的时间
DATE(date)	提取日期或日期/时间表达式的日期部分
QUARTER(date)	函数返回给定日期值（1 到 4 之间的数字）的一年中的季度
DAYOFWEEK(date)	返回一个整数，范围从 1 到 7，表示星期日到星期六
DAYOFMONTH(date)	返回日期的月中的天，范围为 0〜31
DAYOFYEAR(date)	返回年份为日期的天，范围为 1 至 366
DAYNAME(date)	获取其工作日名称的日期
MONTHNAME(date)	日期 date 对应月份的英文全名
MINUTE(date)	返回时间的分钟，范围为 0 至 59
HOUR(date)	提取时间
WEEK(date)	返回周序号
DAY(date)	等同于 DAYOFMONTH()
MONTH(date)	返回传入日期所对应的月序数
YEAR(date)	返回年份
DATE_FORMAT(date,format)	按指定方式格式化日期，例如 DATE_FORMAT(date,'%y-%m-%d')
DATE_ADD(date,'INTERVAL expr type')	给日期添加指定的时间间隔，例如 DATE_ADD(OrderDate,'INTERVAL 2 DAY')
DATE_SUB(date,'INTERVAL expr type')	从日期减去指定的时间间隔，例如 DATE_SUB(OrderDate,'INTERVAL 2 DAY')

OSQL不支持下面关键字

RIGHT JOIN 、FULL JOIN、BETWEEN、SELECT INTO、USING 操作符。

RIGHT JOIN 、FULL JOIN、BETWEEN、SELECT INTO、USING 操作符。

SOQL

search语句可以理解为sql语句的子集，大部分常用的sql查询语句都支持，只需要把开头的select关键词改为search即可
  下面是search语句的一些限制和特点：

  1. search语句当前对聚合、分组、通配符、distinct等功能暂未支持，将在后续迭代中完善并开放
  2. search语句必须带有where从句，否则报错
  3. 字符串类型默认都转为es中text类型，因此可以实现分词的倒排索引。由于默认未设置 Fielddata=on（会很耗性能），所以字符串类型无法排序
  4. 不支持search语句where从句中有非可搜字段，如不支持search * from myobject where t1 = 'abc' （此处t1为非可搜字段）
  5. 同sql语句一样，search语句也大小写不敏感
  6. search语句目前只可进行单表搜索
  7. 无法匹配null值和空串，因空值不会创建倒排索引所以无法匹配
  8. 英语查询分词以空格分词，查询中文以字符分词。例如文档内容为"Business Application平台"，那么查 询'平'可以匹配，'Business'也可以匹配，但是'Bus'无法匹配
  9. text类型采用了英语分词器，因此大小写单复数不敏感，'movie'可匹配'Movies'

search语句可以理解为sql语句的子集，大部分常用的sql查询语句都支持，只需要把开头的select关键词改为search即可
  下面是search语句的一些限制和特点：

  1. search语句当前对聚合、分组、通配符、distinct等功能暂未支持，将在后续迭代中完善并开放
  2. search语句必须带有where从句，否则报错
  3. 字符串类型默认都转为es中text类型，因此可以实现分词的倒排索引。由于默认未设置 Fielddata=on（会很耗性能），所以字符串类型无法排序
  4. 不支持search语句where从句中有非可搜字段，如不支持search * from myobject where t1 = 'abc' （此处t1为非可搜字段）
  5. 同sql语句一样，search语句也大小写不敏感
  6. search语句目前只可进行单表搜索
  7. 无法匹配null值和空串，因空值不会创建倒排索引所以无法匹配
  8. 英语查询分词以空格分词，查询中文以字符分词。例如文档内容为"Business Application平台"，那么查 询'平'可以匹配，'Business'也可以匹配，但是'Bus'无法匹配
  9. text类型采用了英语分词器，因此大小写单复数不敏感，'movie'可匹配'Movies'

索引

功能描述

控制台提供了查看表索引信息，重建索引的功能。但对具体字段加索引并没有该功能。重建索引是为了减小数据碎片化存储，提高数据查询效率。

语法

查看表索引信息:

show index <tableName>

show index <tableName>

重建索引:

rebuild index <tableName>

rebuild index <tableName>

搜索引擎

功能描述

加速全文搜索和字段值匹配的效率在数据量较大时，相比于使用 sql 的 select 关键词查询会有很大的性能提升。

控制台提供了显示、创建、删除、重建搜索引擎索引等功能，对具体字段加搜索引擎索引，需要在字段 IsSearchable 属性上设置 true。只有表中的字段加了搜索引擎索引，以下语法才可以正常运行。

语法

创建搜索引擎索引:

searchindex create <tableName>

searchindex create <tableName>

删除搜索引擎索引:

searchindex delete <tableName>

searchindex delete <tableName>

将数据从数据库刷新到搜索引擎索引:

searchindex rebuild <tableName>

searchindex rebuild <tableName>

显示重建搜索引擎索引信息:

searchindex show

searchindex show

sql 语法 ​

功能清单 ​

对象数据查询 ​

功能描述 ​

SOQL 语法 ​

GROUP BY 子句 ​

ORDER BY 子句 ​

HAVING 子句 ​

LIMIT 操作符 ​

IN 操作符 ​

INNER JOIN 关键字 ​

LEFT JOIN 关键字 ​

UNION查询 ​

子查询 ​

in子查询 ​

支持select子查询。 ​

exists子查询 ​

hint 语法 ​

函数 ​

OSQL不支持下面关键字 ​

SOQL ​

索引 ​

功能描述 ​

语法 ​

搜索引擎 ​

功能描述 ​

语法 ​

sql 语法

功能清单

对象数据查询

功能描述

SOQL 语法

GROUP BY 子句

ORDER BY 子句

HAVING 子句

LIMIT 操作符

IN 操作符

INNER JOIN 关键字

LEFT JOIN 关键字

UNION查询

子查询

in子查询

支持select子查询。

exists子查询

hint 语法

函数

OSQL不支持下面关键字

SOQL

索引

功能描述

语法

搜索引擎

功能描述

语法