3.SAP HANA SQL Reference
3.1注释
/*1.注释*/
--单行注释
/*多行注释*/
3.2标志符
/*2.标识符*/
/*
用来定义table name,column name,index name,function name,procedure name等
1.使用双引号括起来的标志符可以是任何字符
2.非使用双引号括起来的标志符，只能有字母开头，数字和下划线组成
注意：非双引号括起来的标志符，sql解析时，服务器会转换为大写
*/
3.3用户名
/*3.用户名*/
/*
用户名可以包含下划线和连接符，不使用双引号括起来；
密码最小长度为8；
*/
3.4引号
/*4.引号*/
/*
单引号表示分隔字符串；
单引号本身使用''两个单引号表示；
双引号表示标志符；
双引号本身使用""两个双引号表示；
*/
3.5文件语句语法表示符号
/*5.语句、语法表示符号*/
/*
<> :sql语法成分；
::= :运算符，右边表示左边元素；
[] : 表示可选部分；
{} :表示组元素，可以重复0次或多次；
| ： 表示公式两部份，任选一个；
[……]:表示前面元素可选重复；
!! :表示普通文本
<digit> ::= 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 0
<letter> :: = a | b | …… | z | A | …… | Z
*/
3.6数据类型
/*6.数据类型*/
/*
Datetime types DATE, TIME, SECONDDATE, TIMESTAMP
Numeric types TINYINT, SMALLINT, INTEGER, BIGINT, SMALLDECIMAL, DECIMAL, REAL, DOUBLE
Boolean type BOOLEAN
Character string types VARCHAR, NVARCHAR, ALPHANUM, SHORTTEXT
Binary types VARBINARY
Large Object types BLOB, CLOB, NCLOB, TEXT
Multi-valued types ARRAY
Spatial types ST_GEOMETRY, ST_POINT
常数，固定值,使用单引号包起来，‘100’;
Unicode字符串，前面加大写N表示,例如：N'abc';
二进制类型使用varbinary类型存储；
当size小于等于8192可以使用(N)VARCHAR保存
varbinary(<n>)存储以字节为单位，<n>表示最大长度1-5000,默认为1；
布尔数据类型保存布尔类型数据；
数据值：TRUE,FALSE,UNKNOWN(同义NULL)
当系统不支持boolean类型，TRUE = 1,FALSE = 0;
字符串数据类型
varchar:7位ASCII字符串；
nvarchar:用于存储unicode字符串；
字符串不支持排序规则表达式，字符串比较是使用对应二进制比较；
定义
varchar(<n>):<n>指定字符串长度1-5000，不指定默认1；
nvarchar(<n>):
alphanum:包含字母数字的可变长字符串，字符串长度1-127，可以进行排序
对于纯数字，可以看做前导0的字母；
shorttext:可变长字符串，支持文本搜索，字符串搜索；
支持column table,不支持row table;
3.6.1数据类型转换
SAP HANA支持显式或隐式数据类型转换；
function：
● CAST function
● TO_ALPHANUM function
● TO_BIGINT function
● TO_VARBINARY function
● TO_BLOB function
● TO_CLOB function
● TO_DATE function
● TO_DATS function
● TO_DECIMAL function
● TO_DOUBLE function
● TO_INTEGER function
● TO_INT function
● TO_NCLOB function
● TO_NVARCHAR function
● TO_REAL function
● TO_SECONDDATE function
● TO_SMALLINT function
● TO_TINYINT function
● TO_TIME function
● TO_TIMESTAMP function
● TO_VARCHAR function
time -> timestamp 显式转换to_timestamp or cast function;
3.6.2数据类型优先级
优先级低的类型数据比较计算时，会隐式转换成较高优先级数据；
● TIMESTAMP
● SECONDDATE
● DATE
● TIME
● DOUBLE
● REAL
● DECIMAL
● SMALLDECIMAL
● DECIMAL(<precision>, <scale>)
● BIGINT
● INTEGER
● SMALLINT
● TINYINT
● NCLOB
● NVARCHAR
● CLOB
● VARCHAR
● BLOB
● VARBINARY
数据转换报错
1.DECEMAL TO BIGINT
DECEMAL 111.324
BIGINT 111
报错：Overflow error
2.VARCHAR TO BIGINT
VARCHAR ‘12.3332’
BIGINT 12
报错：invalid number
VARCHAR TO TINYINT
VARCHAR ‘256’
TINYINT 256
报错：Overflow error
*/
示例：
-- string
select '100' "str1", 'hello' "str2",N'aba' "unicode str3" from dummy;
--number
select 1 "int1", 1.234 "decimals1", 1.23e2 "decimals2", 0xabcdef "Hex Decimals" from dummy;
--binary string
select X'2112' "binary string", x'abdd' "binary string2" from dummy;
--date,time,timestamp
select date'2011-11-01' "日期", time'12:22:33.22' "时间" , timestamp'2011-02-22 22:22:33' "时间戳" from dummy;
--test boolean
--创建行存储方式table
CREATE ROW TABLE TEST (A BOOLEAN);
--查询显示True 1,False 0
INSERT INTO TEST VALUES (TRUE);
INSERT INTO TEST VALUES (FALSE);
--查询语句显示？？
INSERT INTO TEST VALUES (UNKNOWN);
INSERT INTO TEST VALUES (NULL);
SELECT A "boolean值" FROM TEST;
SELECT A "boolean值" FROM TEST WHERE A = TRUE;
--错误语法
--SELECT * FROM DUMMY WHERE ( 'A'>'B' ) = ( 'C'>'D' );
--同样实现
SELECT * FROM DUMMY WHERE
CASE WHEN ( 'A'>'B' ) THEN TRUE WHEN NOT ( 'A'>'B' ) THEN FALSE ELSE NULL END
= CASE WHEN ( 'C'>'D' ) THEN TRUE WHEN NOT ( 'C'>'D' ) THEN FALSE ELSE NULL END;
--满足条件
SELECT * FROM dummy WHERE TRUE = TRUE ;
--不满足条件
SELECT * FROM dummy WHERE TRUE = FALSE ;
--创建表
CREATE ROW TABLE INT_NUM(
number TINYINT,
number1 SMALLINT,
number2 INTEGER,
number3 BIGINT,
number4 DECIMAL,
number5 SMALLDECIMAL,
number6 REAL,
number7 DOUBLE);
--插入数据
INSERT INTO INT_NUM VALUES (1,1,1,1,1.31333,1.32,1.33,1.34);
SELECT * from INT_NUM;
3.6.3日期数据类型
/*
–日期数据类型
DATE：默认格式YYYY-MM-DD,范围0001-01-01 ~ 9999-12-31
TO_DATE()方法
YYYY/MM/DD
YYYY/MM-DD
YYYY-MM/DD
YYYYMMDD
MON:月份英文简写，示例：JAN.
MONTH:月份英文，示例：JANUARY.
RM:罗马数字表示月份，I-XII,示例：一月：I;
DDD:一年的第几天1-366,
TIME:默认HH24-MI-SS,HH24:小时0-23；MI：分钟0-59；SS：秒0-59；
TO_TIME()方法
HH:MI[:SS][AM][PM]:
HH12:MI[:SS][AM][PM]:12小时进制
HH24:MI[:SS]:
SSSSS:秒，0-86399；
SECONDDATE:默认格式YYYY-MM-DD HH24-MI-SS;
TIMESTAMP:默认格式YYYY-MM-DD HH24-MI-SS.FF7.FF<n>：代表n小数字数
TO_TIMESTAMP()方法
–将字符串VARCHAR转换为日期,对应format格式
TO_VARCHAR()方法
D:周天，1-7；
DAY:周天，英文,示例:星期一，MONDAY;
DY:周天，英文简写,示例：星期一，MON;
MON:月份英文简写,示例：JAN；
MONTH:月份英文全称;
RM:罗马数字符号表示月;
Q:季度1-4；
W：一个月第几周1-6;
WW:一年的第几个月1-54;
*/
--设置时间格式，日期格式
CREATE TABLE TEST_DATE(date1 DATE,date2 TIME,date3 TIMESTAMP,date4 SECONDDATE);
--> OK
INSERT INTO TEST_DATE VALUES ('2020-02-03','02:33:33','2018/01/02 10:00:00','2020-02-23 22:33:33');
--> ERROR,时间戳格式错误
--INSERT INTO TEST_DATE VALUES ('2020-02-03','02:33:33','02/01/2018 10:00:00','2020-02-03 22:33:33');
-->查询显示,时间戳转换为varchar
SELECT TO_VARCHAR(date3) FROM TEST_DATE;
-->设置format格式
SELECT TO_VARCHAR(date3,'月:MON-MONTH;天:DAY-D-DY;第Q季;第WW月;第W周') FROM TEST_DATE;
-->设置默认DATE格式
--SET 'DATE_FORMAT' = 'YYYY/MM/DD';
-->设置默认TIME格式
--SET 'TIME_FORMAT' = 'HH:MI:SS';
-->设置SECONDDATE格式
--SET 'SECONDDATE_FORMAT' = 'DD/MM/YYYY HH:MI:SS';
-->删除TABLE语句
DROP TABLE TEST_DATE1;
CREATE TABLE TEST_DATE1(date1 DATE,date2 TIME,date3 TIMESTAMP,date4 SECONDDATE);
-->设置默认时间戳格式
SET 'TIMESTAMP_FORMAT' = 'DD/MM/YYYY HH:MI:SS';
--> ERROR,时间戳格式错误
--INSERT INTO TEST_DATE VALUES ('2020-02-03','02:33:33','2018/01/02 10:00:00','2020-02-03 22:33:33');
--> OK,需要按照事件戳格式设置值 (并不OK，报错？)
INSERT INTO TEST_DATE1 VALUES ('2020-02-03','02:33:33','02/01/2018 10:00:00','2020-02-03 22:33:33');
--> 02/01/2018 10:00:00
SELECT TO_VARCHAR(date3) FROM TEST_DATE1;
/*DATE空类型
设置值为'','0000-00-00',NULL,查询显示为'？';
*/
CREATE ROW TABLE TEST_DATE2 (A INT, B DATE, C DATE);
INSERT INTO TEST_DATE2 VALUES (1, '', '0001-01-01');
INSERT INTO TEST_DATE2 VALUES (2, '0000-00-00', '0001-01-01');
INSERT INTO TEST_DATE2 VALUES (3, '0000-00-00', '0001-01-01');
INSERT INTO TEST_DATE2 VALUES (4, '0001-01-01', '0001-01-01');
INSERT INTO TEST_DATE2 VALUES (5, NULL, '0001-01-01');
INSERT INTO TEST_DATE2 VALUES (6, '0001-01-01', '0001-02-01');
--查询到1，2，3条数据，不包含NULL
SELECT * FROM TEST_DATE2 WHERE B = '00000000';
--查询到1，2，3条数据，不包含NULL
SELECT * FROM TEST_DATE2 WHERE B = '';
--查询到不为NULL的所有记录
SELECT * FROM TEST_DATE2 WHERE B IS NOT NULL;
--所有记录
SELECT * FROM TEST_DATE2;
--DAYS_BETWEEN()函数两个日期之间天数
SELECT DAYS_BETWEEN(B, C) FROM TEST_DATE2;
--时间格式设置DATE
INSERT INTO TEST_DATE VALUES (
'2020-02-03','','','');
INSERT INTO TEST_DATE VALUES (
'2020/02/03','','','');
INSERT INTO TEST_DATE VALUES (
'2020-02/03','','','');
INSERT INTO TEST_DATE VALUES (
'2020/02-03','','','');
INSERT INTO TEST_DATE VALUES (
'20200203','','','');
INSERT INTO TEST_DATE VALUES (
TO_DATE('2020-Jan-03','YYYY-MON-DD'),'','','');
INSERT INTO TEST_DATE VALUES (
TO_DATE('2020-January-03','YYYY-MONTH-DD'),'','','');
INSERT INTO TEST_DATE VALUES (
TO_DATE('2020-I-03','YYYY-RM-DD'),'','','');
--默认年份0001
INSERT INTO TEST_DATE VALUES (
TO_DATE('I-03','RM-DD'),'','','');
INSERT INTO TEST_DATE VALUES (
TO_DATE('2012-365','YYYY-DDD'),'','','');
--默认年份0001
INSERT INTO TEST_DATE VALUES (
TO_DATE('365','DDD'),'','','');
--时间格式TIME
--默认24小时制
INSERT INTO TEST_DATE VALUES (
'','12:22:02','','');
INSERT INTO TEST_DATE VALUES (
'','12:22:02 AM','','');
INSERT INTO TEST_DATE VALUES (
'','12:22:02 PM','','');
INSERT INTO TEST_DATE VALUES (
'','12:22','','');
INSERT INTO TEST_DATE VALUES (
'',TO_TIME('12:22:22','HH12:MI:SS'),'','');
INSERT INTO TEST_DATE VALUES (
'',TO_TIME('12345','SSSSS'),'','');
--时间格式TIMESTAMP
INSERT INTO TEST_DATE VALUES (
'','',TO_TIMESTAMP('2020-02-02 22:23:13.99','YYYY-MM-DD HH24:MI:SS.FF'),'');
3.6.4LOB数据类型
/*
Large Object(LOB)数据类型
大量数据类型用来保存长文本,图片等数据；
BLOB:用来保存大量的二进制数据，可以转换为varbinary类型；
CLOB:用来保存大量7位ASCII字符数据，可以转换为varchar类型；
NCLOB:用来保存大量Unicode字符数据，可以转换为nvarchar类型；
TEXT:文本类型，可以使用文本搜索功能，只能定义在column table；
TEXT不是标准的SQL数据类型，选择TEXT列会转换为NCLOB列；
BINTEXT：和文本类型类似，支持文本搜索功能，可以保存二进制数据
LOB数据支持操作：
1.Length()支持clob/nclob/blob数据类型，返回字节长度；
2.Substr()支持clob/nclob数据类型；
3.Coalesce();
4.like,contains条件支持clob/nclob数据类型；
5.null条件支持clob/nclob/blob数据类型；
LOB数据限制：
1.order by/group by语句不能使用lob数据列；
2.lob数据列不能作为from连接条件；
3.lob数据列不能作为where条件，除了使用like；
4.lob数据列不能作为聚合函数的参数；
5.select distinct不能使用lob数据列；
6.LOB列不能在集合操作(如EXCEPT)中使用。UNION ALL是个例外。
7.LOB列不能作为主键；
8.LOB列不能创建index;
9.LOB列不能在统计信息更新语句中使用;
*/
/*
Multi-valued数据类型
存储相同数据结构的数据集；
SAP HANA支持所有基础类型放入数据集；
不支持TEXT,LOB类型和Spatial types;
不能再数据集中包含数据集;
Array：数组用来保存相同数据类型数据集；
Array是不可变的，不能增加，删除，修改元素；
支持的操作：
1.<ARRAY> || <ARRAY> (SQL Script)连接；
2.<ARRAY>[<index>],(SQL Script)获取元素；
3.CARDINALITY FUNCTION;
4.MENBER_AT FUNCTION;
5.[NOT] MENBER OF FUNCTION;
6.SUBARRAY FUNCTION;
7.TRIM_ARRAY FUNCTION;
8.UNNEST(SQL Script);
*/
3.6.5Numeric数据类型
/*
Numeric数据类型
浮点数在系统存储为二进制数,使用浮点数获得结果是不准确的；
TINYINT:8位无符号整数,0-255;
SMALLINT:16位有符号整数,-32768~32767;
INTEGER:32位有符号整数,-2147483648~-2147483647;
BIGINT:64位有符号整数, -9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807;
DECIMAL[(precision, scale)] or DEC[(p,s)] :定点小数，p表示总位数，s表示小数点位数，p从1~38取值，s从0-<p>取值；
如果未指定p,s,DECIMAL变成浮点十进制数,p,s由存储的数据决定,p为1~34,s为-6111~6176；
例如：0.0002234(2234E-7),p为2234，s为7
SMALLDECIMAL:SMALLDECIMAL是浮点十进制数,p,s由存储数据决定，p为1-16，s为-369~368，只能在column table中使用；
REAL:単精度32位浮点数；
DOUBLE:双精度64位浮点数，-1.7976931348623157E308 ~ 1.7976931348623157E308
FLOAT(<n>):32位或64位实数，<n>小于25,32位REAL类型数据，大于等于25，或未指定时，64位DOUBLE类型数据；
*/
SELECT TO_DOUBLE(0.1) + TO_DOUBLE(4.6) AS DOUBLE_SUM FROM DUMMY;
3.6.6Spatial数据类型
/*
Spatial数据类型
空间数据类型用于存储包含空间数据的值，例如点、线或多边形。
ST_Point,ST_Geometry列类型只能使用在column table;
ST_Point类型列,只支持二维空间类型ST_Point;
ST_Geometry,支持以下空间类型：
ST_CircularString
ST_GeometryCollection
ST_LineString
ST_MultiLineString
ST_MultiPoint
ST_MultiPolygon
ST_Point
ST_Polygon
更多介绍：SAP HANA Spatial Reference
*/
3.7保留字
/*7.保留字*/
/*
保留字HANA SQL Parser解析具有特殊意义，不能定义变量和其同名；
*/
--查看保留字
SELECT * FROM RESERVED_KEYWORDS;
3.8操作符
/*8.操作符*/
/*
一元&二进制操作符
一元+
一元-
一元not
二进制操作符,二元运算符
op1 operator op2
加法运算符(+，-)
乘法运算符(*,/)
比较运算符(=,!=,<,>,<=,>=)
逻辑运算符(or,and)
运算符优先级
(从高到低)
() parentheses
+, – unary positive and negative operation
*, / multiplication, division
+, – addition, subtraction
|| concatenation
=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN comparison
NOT logical negation
AND conjunction
OR disjunction
算术运算符
使用算术运算符进行加、减、乘、除运算；
-<expression>:取负,如果为null,结果为null;
<expression> + <expression>:加;
<expression> – <expression>:减;
<expression> * <expression>:乘;
<expression> / <expression>:除;
如果结果大于Decimal<p,s>能表示最大值，将转换为浮点类型Decimal;
Decimal类型e1(p1,s1),e2(p2,s2)
e1+e2 p位：max(s1,s2)+max(p1-s1,p2-s2)+1 s位：max(s1,s2)
e1-e2 p位:max(s1,s2)+max(p1-s1,p2-s2)+1 s位：max(s1,s2)
e1*e2 p位：p1+p2+1 s位：s1+s2
e1/e2 p位：p1-s1+s2+max(6,s1+p2+1) s位：max(6,s1+p2+1)
连接运算符
<expression>||<expression> :将两个字符串，常量，表达式连接起来
varchar,nvarchar类型数据连接运算时，保留前置、后置空格，最大长度5000个字符;
集合运算符
可以将多个查询语句结果运算返回单个结果集；
UNION:将多个查询select结果集合并去重返回；
UNION ALL:将多个查询select结果合并，不去重返回；
INTERSECT：将多个查询select结果合并，返回公共行；
EXCEPT：将查询1的内容除去与查询2相同内容返回；
*/
3.9表达式和谓词
/*9.Expressions*/
/*
条件表达式
语法：CASE <expression>
WHEN <expression> THEN <expression>
[WHEN <expression> THEN <expression> ……]
[ELSE <expression>]
END
Function表达式
语法：<function_name>([<expression>,……] )
Aggregate表达式
语句规则：
<aggregate_expression> ::=
COUNT(*)
| COUNT ( DISTINCT <expression_list> )
| <agg_name> ( [ ALL | DISTINCT ] <expression> )
| STRING_AGG ( <expression> [, <delimiter>] [<aggregate_order_by_clause>])
<agg_name> ::= CORR | CORR_SPEARMAN | COUNT | MIN | MEDIAN | MAX | SUM | AVG | STDDEV | VAR | STDDEV_POP | VAR_POP | STDDEV_SAMP | VAR_SAMP
<delimiter> ::= <string_constant>
<aggregate_order_by_clause> ::= ORDER BY <expression> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST]
可以对汇总进行排序，ASC升序，DESC降序；升序NULLS在开头，也可以通过NULLS FIRST,NULLS LAST重新设定；
汇总时，使用DISTINCT，先去重，再汇总计算。 STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP不支持DISTINCT;
CORR函数： Computes the Pearson product momentum correlation coefficient between two columns.
CORR_SPEARMAN函数：Returns the Spearman’s rank correlation coefficient of the values found in the corresponding rows of two columns
COUNT函数：COUNT(*):返回行数；
COUNT(<expressions>):返回非空值数量；
COUNT(DISTINCT <expression_list>):返回非重复值表达式数量，除了所有字段为null的表达式；
MIN函数
MEDIAN函数
MAX函数
SUM函数
AVG函数
STDDEV函数: Returns the standard deviation of the given expression as the square root of the VAR function.
STDDEV_POP函数：Returns the standard deviation of the given expression as the square root of the VAR_POP function.
STDDEV_SAMP函数： Returns the standard deviation of the given expression as the square root of the VAR_SAMP function.
VAR函数： Returns the variance of the given expression as the square of the standard deviation.
VAR_POP函数： Returns the population variance of expression as the sum of squares of the difference of <expression> from the mean of <expression>, divided by the number of rows remaining.
VAR_SAMP函数： Returns the sample variance of expression as the sum of squares of the difference of <expression> from the mean of <expression>, divided by the number of rows remaining minus 1 (one).This function is similar to VAR, the only difference is that it returns NULL when the number of rows is 1.
STRING_AGG函数： Returns the concatenated string.
JSON Object表达式
JSON Object表达式只能在JSON collection table;
<key>:键，使用双引号引用；
<value>:可以是任何表达式，包括{JSON Object},[Array]
示例：
{“key1″:’helo’,”key2″:23,”key3”:{‘key31′:’kdjf’,’key32′:3}}
*/
--count(distinct),count结果3,
CREATE ROW TABLE TEST_COUNT (A INT, B INT);
INSERT INTO TEST_COUNT VALUES (NULL, NULL);
INSERT INTO TEST_COUNT VALUES (1, NULL);
INSERT INTO TEST_COUNT VALUES (1, NULL);
INSERT INTO TEST_COUNT VALUES (NULL, 1);
INSERT INTO TEST_COUNT VALUES (1, 1);
INSERT INTO TEST_COUNT VALUES (1, 1);
--获取count
-- 6,返回行数，
SELECT COUNT (*) AS DISTINCT_A_B FROM TEST_COUNT; 、
-- 1,只计算非空值行，去重；
SELECT COUNT (DISTINCT A) AS DISTINCT_A_B FROM TEST_COUNT; 、
-- 3，非空值行，不去重；
SELECT COUNT (B) AS DISTINCT_A_B FROM TEST_COUNT; --3
--3,A,B两列都不相同,当两列为空不计数
SELECT COUNT (DISTINCT A, B) AS DISTINCT_A_B FROM TEST_COUNT;
/*9.Predicates*/
/*
1.compare
<comparison_predicate> ::=
<expression> { = | != | <> | > | < | >= | <= } [ ANY | SOME | ALL ] ( { <expression_list> | <subquery> } )
<expression_list> ::= <expression> [{, <expression>}…]
2.between
<between_predicate> ::= <expression> [NOT] BETWEEN <lower_expression> AND <upper_expression>
3.contains
<contains_predicate> ::= CONTAINS (
<contains_columns>,
<search_string>
[, <search_specifier>] )
参数1：columns列名
<contains_columns> ::=
*
| <column_name>
| ( <column_list> )
<column_list> ::=
( <column_name> [,<..>] )
参数2：字符串，
<search_string> ::= <string_const>
参数3：
<search_specifier> ::=
[<search_type>] [<opt_search_specifier2_list>]
| <search_specifier2_list>
<opt_search_specifier2_list> ::=
(empty, nothing specified)
| <search_specifier2_list>
<search_type> ::=
<exact_search>
| <fuzzy_search>
| <linguistic_search>
<search_specifier2_list> ::=
<search_specifier2>
| <search_specifier2_list> , <search_specifier2>
<search_specifier2> ::=
<weights> =
| <language>
| <fulltext>
<fulltext> ::= <FULLTEXT>
<(ON>)
| <(OFF>)
| (<AUTOMATIC>)
<exact_search> ::=
EXACT
| EXACT ( <additional_params> )
<fuzzy_search> ::=
FUZZY
| FUZZY ( <fuzzy_params> )
| FUZZY ( <fuzzy_params_list> )
<fuzzy_params_list> ::= ( <fuzzy_params> ) , <fuzzy_params_list2>
<fuzzy_params_list2> ::=
( <fuzzy_params> )
| <fuzzy_params_list2> , ( <fuzzy_params> )
<fuzzy_params> ::=
<float_const>
| <float_const> , <additional_params>
| NULL , <additional_params>
<linguistic_search> ::=
LINGUISTIC | LINGUISTIC ( <additional_params> )
<weights> ::= WEIGHT ( <float_const_list> )
指定每列所占比重
<language> ::= LANGUAGE ( <string_const> )
<additional_params> ::= <string_const>
contains谓词只能做column table,attribute view中使用
4.exists
<exists_predicate> ::= [NOT] EXISTS ( <subquery> )
当<subquery>返回result set为空，返回false;
当<subquery>返回result set有值,返回true;
5.in
<in_predicate> ::= <search_for_expression> [ NOT ] IN { <search_in_expression_list> | <subquery> }
子查询
6.like
<like_predicate> ::= <source_expression> [ NOT ] LIKE <pattern_expression>
[ ESCAPE <escape_expression> ]
7.like_regexpr
正则表达式匹配
<regex_subject_string> LIKE_REGEXPR <pattern> [ FLAG <flag> ]
“匹配的字符串
<regex_subject_string> ::= <string>
“正则表达式
<pattern> ::= !!Perl Compatible Regular Expression
“表达式模式
<flag> ::= i | m | s | x
i Enables case-insensitive matching，不区分大小写
m Enables multiline mode, where the <subject_string> will be treated as multiple lines and the expression ^ and $ match just after or just before, respectively, a line terminator or the end of the input sequence
s Enables the expression <.> as a wildcard to match any character, including a line terminator
x Permits whitespace and comments in the pattern 允许空格和注释
8.member of
whether a value is a member of an array
<expression> [NOT] MEMBER OF <array_value_expression>
9.NULL
<null_predicate> ::= <expression> IS [NOT] NULL
*/
CREATE ROW TABLE TEST_PRE(name VARCHAR(20), age INT, address NVARCHAR(40), salary DECIMAL(10,2));
INSERT INTO TEST_PRE VALUES('TOM',13,'Landom',2233.33);
INSERT INTO TEST_PRE VALUES('LINDA',33,'Sinapo',21333.33);
INSERT INTO TEST_PRE VALUES('TOMMAS',13,'Landom',2321);
INSERT INTO TEST_PRE VALUES('MATIN',13,'Lang',4233.33);
INSERT INTO TEST_PRE VALUES('SINA',13,'Gudom',5333.33);
--ANY,SOME当使用等号相当于IN
SELECT * FROM TEST_PRE WHERE NAME = SOME('SINA','TOMMAS');
SELECT * FROM TEST_PRE WHERE NAME = ANY('SINA','TOMMAS');
--ALL，所有比较都为true返回
SELECT * FROM TEST_PRE WHERE SALARY > ALL(4000,5000);
--BETWEEN
SELECT * FROM TEST_PRE WHERE SALARY BETWEEN 3000 AND 5000;
--CONTAINS,包含
CREATE COLUMN TABLE SEARCH_TEXT(
"Content" TEXT FAST PREPROCESS OFF,
"Descrip" TEXT FAST PREPROCESS OFF,
"Comment" TEXT FAST PREPROCESS OFF );
INSERT INTO SEARCH_TEXT VALUES( 'Blue baseball cap', 'Vintage', 'Out of stock');
INSERT INTO SEARCH_TEXT VALUES( 'Red car', 'Vintage', 'Taking orders' );
INSERT INTO SEARCH_TEXT VALUES( 'Bluish sky', 'Retro', 'Discontinued' );
INSERT INTO SEARCH_TEXT VALUES( 'cap', 'Vintage111', 'Taking orders111' );
--Contains查询
--EXACT精确查询
--FUZZY模糊查询，FUZZY(0.0~1.0),指定模糊程度，0.0非常模糊，1.0精确查询
--默认精确查询
SELECT SCORE() AS SCORE,* FROM SEARCH_TEXT
WHERE CONTAINS("Content",'cap')
ORDER BY SCORE DESC;
SELECT SCORE() AS SCORE,* FROM SEARCH_TEXT
WHERE CONTAINS("Content",'cap',EXACT)
ORDER BY SCORE DESC;
--SCORE()指数表示匹配程度
SELECT SCORE() AS SCORE,* FROM SEARCH_TEXT
WHERE CONTAINS("Content",'cap',FUZZY(0.0))
ORDER BY SCORE DESC;
--FUZZY()指定模糊程度,0.9less fuzzy,more exact
SELECT SCORE() AS SCORE,* FROM SEARCH_TEXT
WHERE CONTAINS("Content",'cap',FUZZY(0.9))
ORDER BY SCORE DESC;
--包含'cap' or 'sky'
SELECT SCORE() AS SCORE,* FROM SEARCH_TEXT
WHERE CONTAINS("Content",'cap OR sky')
ORDER BY SCORE DESC;
--精确搜索字符串"Blue"
SELECT SCORE() AS SCORE,* FROM SEARCH_TEXT
WHERE CONTAINS("Content",'"Blue"')
ORDER BY SCORE DESC;
--LINGUISTIC语言学上的匹配，take可以匹配到takes,taking等词；
SELECT * FROM SEARCH_TEXT WHERE CONTAINS (*, 'take', LINGUISTIC);
--多列值匹配
SELECT * FROM SEARCH_TEXT WHERE CONTAINS (( "Content", "Descrip" ), 'vintage');
SELECT * FROM SEARCH_TEXT WHERE CONTAINS (*, 'vintage');
--exists
SELECT * FROM SEARCH_TEXT WHERE EXISTS( SELECT 1 FROM SEARCH_TEXT WHERE CONTAINS(*,'vintage') );
--in
CREATE COLUMN TABLE "TEST_TAB_IN" ( order_nr NVARCHAR(10), item_nr INTEGER, some_text NVARCHAR(100),
PRIMARY KEY (order_nr, item_nr) );
INSERT INTO "TEST_TAB_IN" (order_nr, item_nr, some_text) VALUES ('A000000001', 1, 'A1 First Item');
INSERT INTO "TEST_TAB_IN" (order_nr, item_nr, some_text) VALUES ('A000000001', 2, 'A1 Second Item');
INSERT INTO "TEST_TAB_IN" (order_nr, item_nr, some_text) VALUES ('B000000001', 1, 'B1 First Item');
INSERT INTO "TEST_TAB_IN" (order_nr, item_nr, some_text) VALUES ('A000000002', 1, 'A2 First Item');
INSERT INTO "TEST_TAB_IN" (order_nr, item_nr, some_text) VALUES ('A000000002', 2, 'A2 Second Item');
--查询可以tuple元组方式匹配,=，<>,!=只支持这三种符号
SELECT * FROM "TEST_TAB_IN" WHERE (order_nr, item_nr) = ('A000000001', 2);
SELECT * FROM "TEST_TAB_IN" WHERE (order_nr, item_nr) <> ('A000000001', 2);
SELECT * FROM "TEST_TAB_IN" WHERE (order_nr, item_nr) != ('A000000001', 2);
-- works only when sub-select returns zero or one row，只支持子查询返回单行数据
SELECT * FROM "TEST_TAB_IN" WHERE (order_nr, item_nr) != (select order_nr, item_nr from "TEST_TAB_IN" where some_text like 'B1%');
-- error: feature not supported: only '=' and '<>'/'!=' operators are allowed here
--SELECT * FROM "TEST_TAB_IN" WHERE (order_nr, item_nr) > ('A000000001', 2);
-- quantified comparison with SOME/ANY/ALL and sub-select is also supported
SELECT * FROM "TEST_TAB_IN" WHERE (order_nr, item_nr) <> ALL (select order_nr, item_nr from "TEST_TAB_IN" where some_text like 'B1%');
-- IN predicate is equivalent to quantified comparison = ANY/SOME
SELECT * FROM "TEST_TAB_IN" WHERE (order_nr, item_nr) IN (('A000000001', 2), ('B000000001', 1));
SELECT * FROM "TEST_TAB_IN" WHERE (order_nr, item_nr) = ANY (('A000000001', 2), ('B000000001', 1));
--LIKE
--A后面一个或多个字符串
SELECT * FROM "TEST_TAB_IN" WHERE some_text LIKE 'A_%';
--ESCAPE，将_转义,匹配'A_'开头的字符
SELECT * FROM "TEST_TAB_IN" WHERE some_text LIKE 'A_%' ESCAPE '_';
--LIKE_REGEXPR
--匹配A开头的字符串
SELECT * FROM "TEST_TAB_IN" WHERE some_text LIKE_REGEXPR '^A' FLAG 's';
--大小写不敏感
SELECT * FROM "TEST_TAB_IN" WHERE some_text LIKE_REGEXPR '^a' FLAG 'i';
--MEMBER OF,判断一个值是否在数组中
CREATE COLUMN TABLE TEST_ARRAY (IDX INT, VAL INT ARRAY);
INSERT INTO TEST_ARRAY VALUES (1, ARRAY(1, 2, 3));
INSERT INTO TEST_ARRAY VALUES (2, ARRAY(10, 20, 30, 40));
INSERT INTO TEST_ARRAY VALUES (3, ARRAY(10, 20, 30, 40));
INSERT INTO TEST_ARRAY VALUES (4, ARRAY(80, 90, 100, 110));
SELECT * FROM TEST_ARRAY WHERE 10 MEMBER OF VAL;
3.10Session参数
/*10.Session Variables*/
/*
APPLICATION NVARCHAR(256) Specifies the application name.
APPLICATIONVERSION NVARCHAR(256) Specifies the application version information. Applications can use their “own” version naming, no format is predefined
APPLICATIONUSER NVARCHAR(256) Specifies the application-defined user name.
APPLICATIONACTION NVARCHAR(256) 指定当前application执行动作
APPLICATIONCOMPONENT NVARCHAR(64) Specifies the name of the application component
APPLICATIONCOMPONENTTYPE VARCHAR(32) Specifies the type of application component. For example, UPD.
APPLICATIONSOURCE NVARCHAR(256)
CASE_SENSITIVE VARCHAR(5) CASE_SENSITIVE is only supported for VARCHAR, NVARCHAR and SHORTTEXT values and only for these operations: ● WHERE (including LIKE) ● JOIN ● HAVING ● CASE ● GROUP BY ● ORDER BY ● Window functions ● UNION
DATE_FORMAT – Specifies the default DATE format to apply for the session.
DEBUG_TOKEN VARCHAR(32) Specifies that with DEBUG_TOKEN you can set a session variable, used by the debugger, to attach criterion.
PROTOCOL_VERSION Specifies the protocol version of the client interface libraries
SECONDDATE_FORMAT Specifies the default SECONDDATE format to apply for the session.
TEMPORAL_SYSTEM_TIME_AS_OF
TEMPORAL_APPLICATION_TIME_AS_OF
TIME_FORMAT Specifies the default TIME format to apply for the session.
TIMESTAMP_FORMAT – Specifies the default TIMESTAMP format to apply for the session.
TOTAL_ROWCOUNT Integer
only accessible from: SELECT session_context(‘TOTAL_ROWCOUNT’) FROM DUMMY.
Specifies the total row count.
Specifying SELECT … LIMIT x returns up to x rows.
With the optional TOTAL ROWCOUNT the (estimated) total number of rows is set in TOTAL_ROWCOUNT variable.
This variable is not deleted/reset by non TOTAL ROWCOUNT statements.
The similar SELECT TOP x … does not support this extension.
This feature is only supported for column tables and column views.
It can return exact or estimated row count or only the given LIMIT.
The concrete return value depends on the statement, used tables and views and internally chosen SQL optimizer strategy.
Usage is recommended only for interactive paged searches to show information like “result 11 to 20 of estimated 50000”.
TRACEPROFILE
Specifies the name of the trace profile.
This is used for manual activation of trace profiles defined in inifiles as [traceprofile_<name>]
The value can contain one trace profile name or a comma separated list of trace profile names.
*/