1. 同望V3平台的多数据库支持策略;
2. 大多数开发人员只熟悉一种数据库;
3. 大多数不同数据库的方言特性具有共通性,可以抽取为公共的语言特性;
4. SQL翻译效率很高;
5. 为V3平台的高级查询设计提供基础引擎。
V-SQL是对同望V3平台实现多数据库支持非常重要的基础引擎。所有与同望V3平台支持的数据库通讯的应用程序都通过向服务器发送V-SQL语句来实现通讯,与应用程序的用户界面无关。 V-SQL抽取了SQL92的部分语法来满足V3平台的最大功能集合,此文档主要介绍V-SQL概要设计方案
2. V-SQL语法规则
1、 vsql里支持直接写查询名称便能引用查询,目前遵从数据库视图的用法,只实现在select查询中的from 、join子句引用,并且查询插件不实现
如 select a.a1 from query1 a left join query2 b on a.id=b.id
其中query1查询 :select a.a1 from a
其中query2查询 :select b.b1 from b
2、 取消表直接连接的方式,必须用inner join替代。
如 select a.field1 from table1 a,table2 b where a.field1=b.field2
改为 select a.field1 from table1 a inner join table2 b on a.field1=b.field2
3、 函数必须用文档里规定的函数。
4、 参数名称需要以字母开头
如 select a.field1 from table1 a,table2 b where a.field1=:1212a
改为 select a.field1 from table1 a,table2 b where a.field1=:a1212
5、 子查询在from或者join后面的必须增加别名。
如 select field1 from (select b.field2 from table2 b)
改为 select field1 from (select b.field2 from table2 b) b
6、 Union all 使用时需要使用子查询。
如 select field1 from a union all select field1 from b
改为 select field1 from (select field1 from a union all select field1 from b) b
如果连接其他表或者查询
如select * from (select field1 from a union all select field1 from b) c inner join d on c. field1=d. field1
改为 select * from (select * from (select field1 from a union all select field1 from b) c) e inner join d on c. field1=d. field1
7、 分页使用limit,使用时如果没有order by则会缺省加上id字段排序,如果该查询没有id字段则必须有order by 子句。
如 select a.field1 from table1 a order by field2 limit 10,1;
指按每页十条记录从第一条开始取。
8、 不要使用关键字或者函数名命名字段。
如 select uuid from table1 a
改为 select id from table1 a
9、 目前between 前面只能是字段、常量、参数、不能是函数或者表达式。
如 select * from yuxg_gz4 where isnull(heji,0) between 1 and 300
改为 select * from yuxg_gz4 where isnull(heji,0) >= 1 and isnull(heji,0)<=300
10、 字符串连接用||代替,不能用+
11、 不等于只能使用<>,不能用!=
12、 in 、not in里的子查询必须加两个括号(())
如 select u from table1 a where a.id in (select id from b)
改为 select u from table1 a where a.id in ((select id from b))
13、 like 表达式中如果有特殊字符如’ %[],请使用参数形式
如 select u from table1 a where a.id like ‘%/[/_’’%’ escape ‘/’
改为 select u from table1 a where a.id like :parm1 escape parm2
‘%/[/_’’%’ 和 ‘/’’ 由参数替代
这里需要注意:
Sqlserver中"/", "%", "_", "[", "]"需要转义;
而db2、oracle中只"%", "_"需要转义;
14、 like 表达式中如果左边是表达式时,请用括号括起
如 select u from table1 a where isnull(a,’’) like :dddd
改为 select u from table1 a where (isnull(a,’’) like :dddd)
15、 sql中的over统计函数使用rownum函数以及统计函数中的参数来体现,详细见
16、 常量查询需要使用dual表来查
如 select 1
改为 select 1 from dual
17、 *号不能和其他字段一起使用,需要加表别名
如 select *,field1 from table1
改为 select a.*,field1 from table1 a
18、 注释功能1.0暂时不能使用
19、 Insert语句只能写 insert into
如 insert table1(field1,field2) values (‘1’,’2’)
改为 insert into table1(field1,field2) values (‘1’,’2’)
20、 Update语句在更新关联查询时遵循sqlserver的写法
如 update a set a.a2=b.b2,a.a3=b.b2 from a a inner join (select * from b ) b on a.a1 = b.b1
21、 delete语句在更新关联查询时使用exist
如 delete from a where exists (select 1 from b where a.a1 is null and a.a1=b.b1)
22、 由于oracle、db2不支持查询语句结果字段中的子查询中的子查询直接引用主查询字段,即只支持一级子查询引用主查询字段,所以查询语句结果字段中存在子查询并且是分页(如取第一条记录)以及存在主查询关联字段需要改写法
如下面的查询price表示查找b表和a表中相同的code的按时间倒序排序的第一条记录的price Select a.code,a.name (select b.price from work b where b.code = a.code order by b.time desc limit 1, 1) price from contact a 改为 Select a.code,a.name, (select b.price from (select rownum("partition by b.code order by b.time desc") num,b.code from work b) b where num =1 and b.code = a.code) ) price from contact a 这里使用了rownum函数先对b表的相同code的按时间倒序排序建立序号,再查出第一条记录 |
23、 由于oracle、db2不支持查询语句结果字段中的子查询中join子句 的on条件直接引用主查询字段,所以请把这些条件写在where子句中
如 select a.a1,(select b.b1 from b inner join c on b.b2=c.c1 and b.b2=a.a2) a3 from a
改成 select a.a1,(select b.b1 from b inner join c on b.b2=c.c1 where b.b2=a.a2) a3 from a
1、 AIX下的Unrecognized JDBC type: 0. ERRORCODE=-4228, SQLSTATE=null错误原因是有参数为null值,类似下面这些sql就会报错
如果是更新语句则是因为更新语句系统不会自动不去掉null值条件,解决方式在开发系统增加isnull函数转换为指定值
2、 AIX下的SQLCODE: -204, SQLSTATE: 42704错误 ,原因是由于db2不支持结果字段为子查询时,子查询里的子查询使用主查询表,详细解决方法参照语法区别22
3、 所有系统的db2中如下语句查不出数据
Select * from (
select ‘1’ type from BA_menu
union all
select ‘cmd’ type from BA_menu) a where a.type=‘1’
原因是因为常量union all后db2会按数据里的最大字符长度填充空格,所以“1”就变成了“1 ”,修改
Select * from (
select ‘1’ type from BA_menu
union all
select ‘cmd’ type from BA_menu) a where trim(a.type)=‘1’
1、 oracle10g中如果to_date方法字段参数中使用了casewhen语法,在外层再使用to_char方法将返回错误信息,这时要使用to_char函数内部先转成字符,涉及的函数主要是datadiff函数,由于vsql未提供to_date、to_char函数,需要用户使用自定义函数在数据库里定义如: vsqlfunc_todate, vsqlfunc_tochar,示例如下:
select TO_CHAR(TO_DATE(a,’yyyy-MM-dd’),’yyyy-MM-dd’)
from (select CASE WHEN NVL(‘2’, ‘1’) != ‘2’ THEN TO_CHAR(SYSDATE, ‘yyyy-MM-dd’)
ELSE ‘2013-08-13’ END a from dual) a
--结果0000-00-00
select TO_CHAR(TO_DATE(a, ‘yyyy-MM-dd’), ‘yyyy-MM-dd’) from
(select to_char(CASE WHEN NVL(‘2’, ‘1’) != ‘2’ THEN SYSDATE
ELSE to_date(‘2013-08-13’, ‘yyyy-MM-dd’) END,’yyyy-MM-dd’) a from dual) a
--改成这样结果对的
VSQL将会自动对sql里的参数值为null值的进行特殊处理,处理逻辑如下:
当条件表达式中的逻辑比较符为>,>=,<,<=,=,<>,like, 或者是in 操作符并且in里面只有一个参数时,VSQL将会删除参数中值为null的表达式(当表达式里有函数除外),即根据and 、or逻辑符替换为指定的表达式(如1=1或者1=2),如:
1、 select a1 from a where a2 = :param1 and a3=2处理成
select a1 from a where 1=1 and a3=2
2、 select a1 from a where a2 like ‘%’||:param1||’%’ or a3=2处理成
select a1 from a where 1 = 2 OR a3 = 2
3、 select a1 from a where a2 in (:param1) 处理成
select a1 from a where 1=1
4、 如果出现在case when中也会替换 如
select case when a1=:param1 and a2=2 then a1 else a2 end from a 处理成
select case when 1=1 and a2=2 then a1 else a2 end from a
注意:如果条件表达式里面参数存在于函数里则不处理,如
select a1 from a where a2 = isnull(:param1,1) and a3=2
VSQL将会自动对sql里的参数值为空字串的进行特殊处理,处理逻辑如下:
当条件表达式中的逻辑比较符为>,>=,<,<=,=,<>,like,VSQL将会替换参数中值为空字串的表达式(当表达式里有函数除外),如:
1、 select a1 from a where a2 = :param1 and a3=2处理成
select a1 from a where (a2 IS NULL OR a2 = :param1)
2、 select a1 from a where a2 like ‘%’||:param1||’%’ 处理成
select a1 from a where (a2 IS NULL OR a2 LIKE ‘%’ || :param1 || ‘%’)
3、 如果出现在case when中也会替换 如
select case when a1=:param1 and a2=2 then a1 else a2 end from a 处理成
select CASE WHEN (a1 IS NULL OR a1 = :param1) AND a2 = 2 THEN a2 ELSE a1 END from a
注意:如果条件表达式里面参数存在于函数里,或者逻辑比较符为in的则不处理,如
select a1 from a where a2 = isnull(:param1,1) and a3=2
select a1 from a where a2 in (:param1)
数据类型 | 长度 | Sql server | Oracle | Db2 |
CHAR | [1,254] | CHAR[(n)] | CHAR[(n)] | CHAR[(n)] |
VARCHAR | [1,4000] | VARCHAR[(n)] | VARCHAR2(n) | VARCHAR(n) |
CLOB | [1,2G] | TEXT | CLOB | CLOB |
INT | [-2^31,2~31-1] | INT | NUMBER(10,0) | INT |
DECIMAL | [-10^31+1,10^31-1] | Numeric(p,s) | NUMBER(p,s) | Numeric(p,s) |
BOOLEAN | 0 表示false,1表示true | Numeric(1,0) | NUMBER(1,0) | Numeric(1,0) |
BLOB | [1,2G] | IMAGE | BLOB | BLOB |
V-SQL支持的数据类型为CHAR , VARCHAR , CLOB,BLOB, INT, DECIMAL, BOOLEAN
字符和二进制字符串大对象
CHAR
VARCHAR
CLOB
BLOB
精确数字
INT
近似数字
DECIMAL
时间数据类型由VARCHAR替代
当两个不同数据类型的表达式用运算符组合后,数据类型的优先顺序规则确定哪种数据类型要向另一种转换。优先顺序低的数据类型向优先顺序高的数据类型转换。如果此转换不是目标数据库所支持的固有转换,则返回错误。当两个操作数表达式有相同的数据类型时,运算的结果就为那种数据类型。
下面是V-SQL数据类型的优先顺序:
l CHAR
l VARCHAR
l CLOB
l BOOLEAN
l INT
l DECIMAL
l BLOB
两个数相加。V-SQL不允许使用’+’进行字符串和日期的运算。
语法
expression + expression
参数
expression
是数值类型中任何数据类型的任何有效V-SQL表达式。
结果类型
返回优先级较高的参数的数据类型
示例
SELECT 1 + ABS( 2.0 ) FROM sample
两个字符串连接。
语法
expression + expression
参数
expression
是数值类型中任何数据类型的任何有效V-SQL表达式。
结果类型
返回优先级较高的参数的数据类型
示例
SELECT 1 + ABS( 2.0 ) FROM sample
一元运算符,返回数字表达式的正值(一元运算符)。
语法
(+ numeric_expression)
参数
numeric_expression
是数值类型中任何数据类型的任何有效V-SQL表达式。
结果类型
返回numeric_expression的类型。
示例
SELECT ( + 1 ) FROM sample
两个数相减。V-SQL不允许使用’-’进行字符串和日期的运算。
语法
expression – expression
参数
expression
是数值类型中任何数据类型的任何有效V-SQL表达式。
结果类型
返回优先级较高的参数的数据类型
示例
SELECT ABS(2.0) – 1 FROM sample
一元运算符,返回数字表达式的负值(一元运算符)。
语法
(-numeric_expression)
参数
numeric_expression
是数值类型中任何数据类型的任何有效V-SQL表达式。
结果类型
返回numeric_expression的类型。
两个表达式相乘(算术乘法运算)。
语法
expression * expression
参数
expression
是数值类型中任何数据类型的任何有效V-SQL表达式。
结果类型
返回优先级较高的参数的数据类型
示例
SELECT price * 1.5 AS NewPrice FROM titles
用一个数除以另一个数(算术除法运算符)。
语法
dividend / divisor
参数
dividend
是被除的数字表达式,dividend可以是数值类型中任何数据类型的有效表达式。
Divisor
除数的数字表达式。divisor可以是数值类型中任何数据类型的有效表达式。
结果类型
返回优先级较高的参数的数据类型。如果用一个整型的 divisor 去除整型的 dividend,其结果是一个整数,小数部分被截断。
注释
由 / 运算符返回的实际值是用第一个表达式除以第二个表达式所得的商。
式例
SELECT ((ytd_sales * price) * royalty)/100 AS ‘Royalty Amout’ FROM titles
WHERE type = ‘business’ ORDER BY title_id
比较两个表达式(比较运算符)。当比较非空表达式时,如果两个操作数相等,则结果为 TRUE;否则结果为FALSE. 如果两个操作数中有一个或者两个都为 NULL,比较结果由目标数据库的环境参数决定。转换取决于数据类型优先级.
语法
expression = expression
参数
expression
可以是数值类型中任何数据类型的有效表达式。两个表达式都必须有可隐式转换的数据类型。转换取决于数据类型优先级。
结果类型
Boolean
比较两个表达式(比较运算符)。当比较非空表达式时,如果左边操作数的值大于右边的操作数,则结果为 TRUE;否则结果为FALSE。如果两个操作数中有一个或者两个都为 NULL, 比较结果由目标数据库的环境参数设置决定。
语法
expression > expression
参数
expression
可以是数值类型中任何数据类型的有效表达式。两个表达式都必须有可隐式转换的数据类型。转换取决于数据类型优先级。
结果类型
Boolean
比较两个表达式(比较运算符)。当比较非空表达式时,如果左边操作数的值小于右边的操作数,则结果为 TRUE;否则结果为FALSE。如果两个操作数中有一个或者两个都为 NULL, 比较结果由目标数据库的环境参数设置决定。
语法
expression < expression
参数
expression
可以是数值类型中任何数据类型的有效表达式。两个表达式都必须有可隐式转换的数据类型。转换取决于数据类型优先级。
结果类型
Boolean
比较两个表达式(比较运算符)。当比较非空表达式时,如果左边操作数的值大于或等于右边的操作数,则结果为 TRUE;否则结果为FALSE。如果两个操作数中有一个或者两个都为 NULL, 比较结果由目标数据库的环境参数设置决定。
语法
expression > = expression
参数
expression
可以是数值类型中任何数据类型的有效表达式。两个表达式都必须有可隐式转换的数据类型。转换取决于数据类型优先级。
结果类型
Boolean
比较两个表达式(比较运算符)。当比较非空表达式时,如果左边操作数的值小于或等于右边的操作数,则结果为 TRUE;否则结果为FALSE。如果两个操作数中有一个或者两个都为 NULL, 比较结果由目标数据库的环境参数设置决定。
语法
expression = < expression
参数
expression
可以是数值类型中任何数据类型的有效表达式。两个表达式都必须有可隐式转换的数据类型。转换取决于数据类型优先级。
结果类型
Boolean
比较两个表达式(比较运算符)。当比较非空表达式时,如果左边操作数的值不等于右边的操作数,则结果为 TRUE;否则结果为FALSE。如果两个操作数中有一个或者两个都为 NULL, 比较结果由目标数据库的环境参数设置决定。
语法
expression < > expression
参数
expression
可以是数值类型中任何数据类型的有效表达式。两个表达式都必须有可隐式转换的数据类型。转换取决于数据类型优先级。
结果类型
Boolean
表示用户提供的文本。可以将注释插入单独行中、嵌套(只限--和//)在 V-SQL 命令行的末端,或者V-SQL语句中。目标数据库不对注释进行评估。--是SQL92标准的注释符号。
语法
-- text_of_comment
参数
text_of_comment
包含注释文本的字符串
注释
将 -- 用于单行或嵌套的注释。用 -- 插入的注释由换行字符分界。注释没有最大长度限制。
示例
SELECT * FROM authors ORDER BY au_id ASC -- We don’t have to specify ASC because that
--is the default.
表示用户提供的文本。可以将注释插入单独行中、嵌套(只限--和//)在 V-SQL 命令行的末端,或者V-SQL语句中。目标数据库不对注释进行评估。//是V-SQL引用了其他高级语言的注释方法。
语法
// text_of_comment
参数
text_of_comment
包含注释文本的字符串
注释
将 // 用于单行或嵌套的注释。用 // 插入的注释由换行字符分界。注释没有最大长度限制。
示例
SELECT * FROM authors ORDER BY au_id ASC // We don’t have to specify ASC because that
//is the default.
表示用户提供的文本。可以将注释插入单独行中、嵌套在 V-SQL 命令行的末端,或者V-SQL语句中。目标数据库不对注释进行评估。
语法
/* text_of_comment*/
参数
text_of_comment
包含注释文本的字符串
注释
将/* */用于单行的和嵌套的注释。由/* */插入的注释不受换行分界符的限制。
示例
SELECT * FROM authors ORDER BY au_id ASC /* We don’t have to specify ASC because that
is the default.*/
匹配指定范围内或者属于方括号所指定的集合中的任意单个字符。
匹配不处于指定范围内或者不属于方括号内指定集合中的任意单个字符。
匹配任意单个字符,并且可以被用作前缀或后缀。
匹配任意单个或多个字符,并且可以被用作前缀或后缀。
V-SQL规定,数据表名 table_name,数据表别名 table_als,字段名 col_nam,字段别名 col_als都属于标识符范围。
以英文字母开头,后跟连续的一个或多个英文字母,下划线或数字。
示例
合法的命名:tTable, t__, t123, tTable__, tTable123, t123__, t__123等。
不合法的命名:_t, _123, 123table, $table, tTable 123等。
包含在双引号内的除双引号以外的连续或不连续字符序列。
示例
合法的命名:”__”, ”123”, ”table 123”等
不合法的命名:””””, “””, “”tTable””等。
以##开头的后跟符号一般表示符命名规则的标识符号,1.0暂不支持临时表。
示例
合法的命名: ##tTable, ##t__等。
不合法的命名: ##1_, ##_12等。
语法
[ N | n ] ‘ [ ^’ ] ‘
参数
N
指示该字符常量为字符串。
n
指示该字符常量为字符串。
注释
包含在’’中的除’以外的任何字符序列。
示例
合法的字符串常量:’’, ‘ ‘, ‘ ‘, ‘ ^ ‘, ‘hello’等。
合法的UNICODE字符串常量:n’’, N’ ‘, N’ ‘, n’ ^ ‘, N’hello’等。
不合法的字符串常量:’’’,’’’’’等。
不合法的字符串常量:N’’’,n’’’’’等。
返回给定数字表达式的绝对值。
语法
ABS ( numeric_expression )
参数
numeric_expression
精确数字或近似数字数据类型类别的表达式
返回类型
返回与 numeric_expression 相同的类型。
示例
SELECT ABS(-1.0), ABS(0.0), ABS(1.0)
返回大于或等于所给数字表达式的最小整数。
语法
CEILING ( numeric_expression )
参数
numeric_expression
是精确数字或近似数字数据类型类别的表达式
返回类型
返回INT类型。
示例
SELECT CEILING(123.45), CEILING(-123.45), CEILING(0.0)
一个数学函数,返回给定表达式中给定角度(以弧度为单位)的三角余弦值。
语法
COS ( float_expression )
参数
float_expression
是DECIMAL类型得expression
返回类型
DECIMAL
示例
SELECT COS(14.78) FROM sample
返回所给的 float 表达式的指数值。
语法
EXP ( float_expression )
参数
float_expression
是 DECIMAL类型的表达式。
返回类型
DECIMAL
示例
SELECT EXP (3.5) FROM sample
返回小于或等于所给数字表达式的最大整数。
语法
FLOOR ( numeric_expression )
参数
numeric_expression
精确数字或近似数字数据类型类别的表达式
返回类型
返回INT类型.
示例
SELECT FLOOR(123.45), FLOOR(-123.45), FLOOR(123.45) FROM sample
返回给定 float 表达式,指定底的自然对数。
语法
LOG ( float_expression, float_base)
参数
float_expression
是DECIMAL数据数据类型的表达式
参数
float_base
是DECIMAL指定底,如10为底
返回类型
DECIMAL
示例
SELECT LOG(5.1753,10) AS fx FROM sample
返回PI的值。
语法
PI( )
参数
DECIMAL
返回类型
DECIMAL
示例
SELECT PI() AS ss FROM sample
返回给定表达式的正 (+1)、零 (0) 或负 (-1) 号。
语法
SIGN ( numeric_expression )
参数
numeric_expression
精确数字或近似数字数据类型类别的表达式
返回类型
DECIMAL
示例
SELECT SIGN( -1),SIGN(10),SIGN(0) FROM sample
以近似数字 (float) 表达式返回给定角度(以弧度为单位)的三角正弦值。
语法
SIN ( float_expression )
参数
float_expression
是DECIMAL类型得表达式。
返回类型
DECIMAL
示例
SELECT SIN(45.17564) sa SN FROM sample
返回给定表达式的平方根。
语法
SQRT ( float_expression )
参数
float_expression
是 DECIMAL类型的表达式。
返回类型
DECIMAL
示例
SELECT SQRT( 1.0 ) FROM sample
返回输入表达式的正切值。
语法
TAN ( float_expression )
参数
float_expression
返回类型
float 或 real 类型的表达式,解释为弧度数。
示例
SELECT TAN(PI( ) /2) FROM sample
返回数字表达式并四舍五入为指定的长度或精度.不同数据库返回的值在小数位数10位以上会有不同
语法
ROUND ( numeric_expression , length )
参数
numeric_expression
精确数字或近似数字数据类型类别的表达式
length
是 numeric_expression 将要四舍五入的精度。length 必须是INT(INTEGER). 当 length 为正数时,numeric_expression 四舍五入为 length 所指定的小数位数。
返回类型
返回与 numeric_expression 相同的类型。
示例
SELECT ROUND(123.9994, 3), ROUND(123.9995, 3) FROM sample
返回数字表达式为指定的长度或精度,不四舍五入.
语法
TRUNC ( numeric_expression , length )
参数
numeric_expression
精确数字或近似数字数据类型类别的表达式
length
是 numeric_expression 将要四舍五入的精度。length 必须是INT(INTEGER). 当 length 为正数时,numeric_expression为 length 所指定截取的小数位数。
返回类型
如果length >0,则返回DECIMAL,否则为整数
示例
SELECT TRUNC (123.9994, 3), TRUNC (123.9995, 3) FROM sample
返回两个整型数值相除得余数。
语法
MOD( int_expression , int_expression )
参数
int_expression
整型数据类型类别得表达式。
返回类型
INT
示例
SELECT MOD(11,2) FROM sample
如果第一个表达式的值为空,则返回第二个表达式的值,否则,返回第一个表达式的值。
语法
ISNULL ( expression , expression )
参数
expression
常量、列名、函数、子查询或算术运算符的任意组合。
返回类型
返回类型与第一个 expression 相同。
如果第一个表达式的值为空,ISNULL 返回第二个 expression 的值。否则,ISNULL 返回第一个 expression 类型的值。
示例
SELECT ISNULL( FMoney , 0 ) AS FMoney FROM sample
是否数字,包括正负实数,是返回1,不是返回0。
语法
ISNUMERIC ( expression )
参数
expression
常量、列名、函数、子查询或算术运算符的任意组合。
返回类型
INT
是返回1,不是返回0。
示例
SELECT ISNUMERIC (FMoney) AS A FROM sample
将 ASCII 代码int转换为字符的字符串函数。
语法
CHAR ( integer_expression )
参数
integer_expression
介于 0 和 255 之间的整数。如果整数表达式不在此范围内,将返回 NULL 值。
返回类型
字符
示例
SELECT CHAR( 65 ) FROM sample 结果为 A
SELECT CHAR( 13 )|| CHAR (10) FROM sample 结果为回车换行
将大写字符数据转换为小写字符数据后返回字符表达式。
语法
LOWER ( character_expression )
参数
character_expression
是字符或二进制数据表达式。
character_expression 可以是常量和列。它必须是可以隐式转换成CHAR和VARCHR的数据类型。否则使用CAST显式转换character_expression。
返回类型
VARCHR
示例
SELECT LOWER ( ‘IT IS NONE OF YOU BUSINESS’) FROM sample
返回从字符串左边开始指定个数的字符。
语法
LEFT ( character_expression , integer_expression )
参数
character_expression
是字符或二进制数据表达式。
character_expression 可以是常量和列。它必须是可以隐式转换成CHAR和VARCHR的数据类型。否则使用CAST显式转换character_expression。
integer_expression
是正整数。
返回类型
VARCHAR
示例
SELECT LEFT (‘Take care, baby’ ,2) FROM sample
返回给定字符串表达式的字符(而不是字节)个数,其中不包含尾随空格
语法
LENGTH ( string_expression )
参数
string_expression
要计算的字符串表达式。
返回类型
INT
示例
SELECT LENGTH( ‘How are you’) FROM sample
语法
参数
返回类型
示例
删除起始空格后返回字符表达式。
语法
LTRIM ( character_expression )
参数
character_expression
是字符或二进制数据表达式。
character_expression 可以是常量和列。它必须是可以隐式转换成CHAR和VARCHR的数据类型。否则使用CAST显式转换character_expression。
返回类型
VARCHAR
示例
SELECT LTRIM ( ‘ Nobody loves you more than I do’ ) FROM sample
返回字符串中从右边开始指定个数的 integer_expression 字符。
语法
RIGHT ( character_expression , integer_expression )
参数
character_expression
是字符或二进制数据表达式。
character_expression 可以是常量和列。它必须是可以隐式转换成CHAR和VARCHR的数据类型。否则使用CAST显式转换character_expression。
integer_expression
是正整数,开始返回字符串的起始位置。如果 integer_expression 为负,则根据目标数据库的环境返回相应的错误信息。
返回类型
VARCHAR
示例
SELECT RIGHT('You are an apple in my eyes' , 2) FROM sample
截断所有尾随空格后返回一个字符串。
语法
RTRIM ( character_expression )
参数
character_expression
由字符数据组成的表达式。character_expression 可以是常量、也可以是字符或二进制数据的列。
返回类型
VARCHAR
示例
SELECT RTRIM ( 'I always laugh off setback ') FROM sample
截断所有起始或尾随空格后返回一个字符串。
语法
TRIM ( character_expression )
参数
character_expression
由字符数据组成的表达式。character_expression 可以是常量、也可以是字符或二进制数据的列。
返回类型
VARCHAR
示例
SELECT TRIM ( ' I always laugh off setback ') FROM sample
返回将小写字符数据转换为大写的字符表达式。
语法
UPPER ( character_expression )
参数
character_expression
由字符数据组成的表达式。character_expression 可以是常量、也可以是字符或二进制数据的列。
返回类型
VARCHAR
示例
SELECT UPPER('Misfortune comes on wings and depart on foot') FROM sample
返回颠倒字符串顺序的字符串。
语法
REVERSE ( character_expression )
参数
character_expression
是字符或二进制数据表达式。
character_expression 可以是常量和列。它必须是可以隐式转换成CHAR和VARCHR的数据类型。否则使用CAST显式转换character_expression。
说明:这函数在DB2、H2下不支持。
返回类型
VARCHAR
示例
SELECT REVERSE ( 'Take') FROM sample 结果ekat
返回字符CHAR,VARCHAR, CLOB,BLOB或具有其类型的结果表达式的一部分。
语法
SUBSTRING ( expression , start , length )
参数
expression
是字符串、字符串大对象, 列或包含列的表达式。不使用包含聚合函数的表达式。
Start
是一个整数,指定子串的开始位置,开始位置必须大于等于1。
Length
是一个整数,指定子串的长度(要返回的字符数或字节数)。
返回类型
返回字符数据。
示例
SELECT SUBSTRING( 'So said,so done', 1 ,10) FROM sample;
返回连接两个字符型表达式的结果。
语法
CONCAT( expression1 , expression2 )
参数
expression1
是字符串、字符串大对象, 列或包含列的表达式。不要使用包含聚合函数的表达式。
expression2
是字符串、字符串大对象, 列或包含列的表达式。不要用包含聚合函数的表达式。
返回类型
字符数据
示例
SELECT CONCAT( 'TO nod politely is one thing ,','and to follow what you say is quite another') FROM sample
返回在指定的字符串中用某一字符串替换特定字符串的结果。
语法
REPLACE( expression1 , expression2, expression3 )
参数
expression1
指定的字符串。
expression2
需要查找替换的字符串。
Expression3
替换为的字符串。
返回类型
字符数据
示例
SELECT REPLACE('abcdefghicde','cde','xxx') FROM sample
注意:replace在MYSQL MSSQL不区分大小写,其他数据库区分
从字符串(source)中的指定开始位置(start),返回字符串(key)的位置。(这里的字符名与函数参数对应)
限制条件:指定开始位置(start),开始位置必须大于等于1,否则报错.
语法
CHARINDEX( key , source,start )
参数
key:字符串(key)的位置
source:字符串(source)
start:指定开始位置(start),开始位置必须大于0,否则不能保证正确性.如果不能确定此值,请用case when处理。如果是常量,在java已验证,并转换为1,如果是字段或参数,则在oracle、db2环境中,start=0返回值永远是“0”
执行后返回类型
整型,最小值是”1”,小于”1”表示没找到
示例
SELECT CHARINDEX(‘key’,’abckeybcd’,1) FROM sample 返回4
返回现在日期。
语法
TODAY( )
参数
返回类型
VARCHAR
示例
SELECT TODAY( ) FROM sample
返回现在时间。
语法
CURTIME( )
参数
返回类型
VARCHAR
示例
SELECT CURTIME( ) FROM sample
返回日期和时间戳月中的第几号。
语法
DAYOFMONTH ( expression )
参数
expression
具有VARCHAR类型的日期常量、列或它们的任意组合运算的结果。
返回类型
INT
示例
SELECT DAYOFMONTH( NOW( ) ) FROM sample
返回日期和时间戳的一周星期几,按国际标准从周日开始为1。
语法
DAYOFWEEK( expression )
参数
expression
具有VARCHAR类型的日期常量、列或它们的任意组合运算的结果。
返回类型
INT
示例
SELECT DAYOFWEEK( NOW( ) ) FROM sample
返回日期和时间戳在一年中的天数位置。
语法
DAYOFYEAR( expression )
参数
expression
具有VARCHAR类型的日期常量、列或它们的任意组合运算的结果。
返回类型
INT
示例
SELECT DAYOFYEAR( NOW( ) ) FROM sample
返回时间或时间戳中的小时部分。
语法
HOUR( expression )
参数
expression
具有VARCHAR类型的日期常量、列或它们的任意组合运算的结果。
返回类型
INT
示例
SELECT HOUR( NOW ( ) ) FROM sample
返回时间或时间戳中的分钟部分。
语法
MINUTE( expression )
参数
expression
具有VARCHAR类型的日期常量、列或它们的任意组合运算的结果。
返回类型
INT
示例
SELECT MINUTE( NOW( ) ) FROM sample
返回日期和时间戳的月份。
语法
MONTH( expression )
参数
expression
具有VARCHAR类型的日期常量、列或它们的任意组合运算的结果。
返回类型
INT
示例
SELECT MONTH( NOW( ) ) FROM sample
返回现在时间(日期加时间)。
语法
NOW( )
参数
返回类型
VARCHAR
示例
SELECT NOW( ) FROM sample
返回日期和时间戳的季度号。
语法
QUARTER( expression )
参数
expression
具有VARCHAR类型的日期常量、列或它们的任意组合运算的结果。
返回类型
INT
示例
SELECT QUARTER( NOW( ) ) FROM sample
返回时间和时间戳的秒部分。
语法
SECOND( expression )
参数
expression
具有VARCHAR类型的日期常量、列或它们的任意组合运算的结果。
返回类型
INT
示例
SELECT SECOND( NOW( ) ) FROM sample
返回日期和时间戳在一年的第几周。
语法
WEEK( expression )
参数
expression
具有VARCHAR类型的日期常量、列或它们的任意组合运算的结果。
返回类型
INT
示例
SELECT WEEK( NOW( ) ) FROM sample
返回日期和时间戳的年部分。
语法
YEAR( expression )
参数
expression
具有VARCHAR类型的日期常量、列或它们的任意组合运算的结果。
返回类型
INT
示例
SELECT YEAR( NOW( ) ) FROM sample
返回时间和时间戳在一周中名字,如星期一、星期二、星期三…。
语法
DAYNAME( espression)
参数
espression
具有VARCHAR类型的日期常量、列或它们的任意组合运算的结果。
返回类型
VARCHAR
示例
SELECT DAYNAME( NOW( ) ) FROM sample
返回日期和时间戳的月份名字。
语法
MONTHNAME( expression [,format])
参数
expression
具有VARCHAR类型的日期常量、列或它们的任意组合运算的结果。
format:以单引号包括
‘en’-返回双位数月份,如01,02,03…11,12,这是默认取值
‘cn’-返回月份,如一、二、三、十一、十二
返回类型
VARCHAR
示例
SELECT MONTHNAME( NOW( ) ) FROM sample
返回特定格式的日期字符串的转换值
语法
FORMATDATE(expression [,format])
参数
expression
时间常量,日期常量字符
Format:以单引号包括
返回字符的格式字符,如yyyy-mm-dd hh24:mi:ss
返回类型
VARCHAR
示例
SELECT FORMATDATE(‘2012-02-01’, ‘yyyy-mm-dd hh24:mi:ss’) FROM sample
暂时只支持以下8格式定义:
DATETIME: ‘yyyy-MM-dd HH24:mi:ss’
DATE: ‘yyyy-MM-dd’
TIME: ‘HH24:mi:ss’
DATETIME_CN:’yyyy年MM月dd日HH24时mi分ss秒’
DATE_CN: ‘yyyy年MM月dd日’
TIME_CN: ‘HH24时mi分ss秒’
YYYYMMDD: ‘yyyyMMdd’
HHMMSS: ‘HH24miss’
返回指定的VARCHAR之后若干秒的另一个VARCHAR类型的数值
语法
DATEADD(date ,addcount ,type)
参数
date
具有VARCHAR类型的日期常量、列或它们的任意组合运算的结果
addcount
整数,表示expression1之后若干(年、月、天、小时、分钟、秒),如果 expression2 为负,则表示之前。
type
取值范围:Year,Month,Day,Hour,Minute,Second,六种,返回值是由此参数决定。例如type= Year,缺省为秒
返回类型
VARCHAR
示例
SELECT DATEADD(NOW(), 1*60*60*24) FROM sample
SELECT DATEADD(NOW(),1,’month’) FROM sample
返回两个指定的VARCHAR之间相隔的时间差
语法
DATEDIFF(startDate ,endDate ,type)
参数
startDate 开始时间
具有VARCHAR类型的日期常量、列或它们的任意组合运算的结果
endDate开始结束
具有VARCHAR类型的日期常量、列或它们的任意组合运算的结果
这里的计算方式是endDate- startDate,返回为两者相隔数,单位由type决定
Type: 可以省略,以单引号包括,默认是day
取值范围:Year,Month,Day,Hour,Minute,Second,六种,返回值是由此参数决定。例如type=Year,则函数计算结果是两个时间相差的年数.
另外特别说明,年、月、日是截断计算的,时、分、秒是取秒后再反算,再取整,算法是有差异的:
a) 年计算:2012-1-1与2011-12-31的计算结果是1年.
b) 月计算:2012-4-1与2012-3-31的计算结果是1月.
c) 日计算:2012-4-1 23:59:59与2012-4-2 02:01:01与的计算结果是-1日.
d) 时计算:2012-4-1 02:00:01与2012-4-1 1:00:09的计算结果不足一小时是0时.
2012-4-1 02:59:59与2012-4-1 1:01:01的计算结果不足两小时是1时.
e) 分计算:2012-4-1 01:02:01与2012-4-1 1:01:59的计算结果不足一分钟是0分.
2012-4-1 1:02:59与2012-4-1 01:01:01的计算结果不足两分钟是1分
f) 秒计算:2012-4-1 01:01:02与2012-4-1 01:01:01的计算结果是1秒.
本函数是参考mssql的DATEDIFF进行处理的,所以计算值也跟它类似,但也有区别,时、分、秒的计算就有区别(其他数据库转换不了,所有就没处理相同)。
返回类型
INT
示例
SELECT DATEDIFF(NOW(), tt) FROM sample
返回在某一集合上对数值表达式求得的和。
语法
SUM (expression [,partitionexp])
参数
expression
数值表达式。
partitionexp : 以双引号包括,内容是partition by xx与order by xx
可选参数,分组累计统计条件。
返回类型
同expression
示例
SELECT SUM (money,“partition by id”) FROM sample
上述对应到原生sql为:SELECT SUM (money) over(partition by id),name FROM sample,指id相同money字段进行累加,注意和group by的区别是查询可以出现非统计字段如上面的name
返回在某一集合上对数值表达式求得的平均值。
语法
AVG (expression [,partitionexp])
参数
expression
数值表达式。
partitionexp : 以双引号包括,内容是partition by xx与order by xx
可选参数,分组累计统计条件。
返回类型
DECIMAL
示例
SELECT AVG (money,“partition by id”) FROM sample
注:AVG除不尽的情况下在H2数据库情况下会返回整数,请使用sum(money)/count(money)
返回在某一集合上对数值表达式求得的总记录数。
语法
COUNT (expression [,partitionexp])
参数
expression
有效表达式。
partitionexp : 以双引号包括,内容是partition by xx与order by xx
可选参数,分组累计统计条件。
示例:
VSQL:select count(id,”order by id”) from example
原生:select COUNT( id) OVER(order by id) from example;
VSQL:select count(id,” PARTITION by id”) from example
原生:select COUNT( id) OVER(PARTITION by id) from example;
VSQL: select count(id,” PARTITION BY ID order by id”) from example
原生:select COUNT( id) OVER(PARTITION BY ID order by id) from example;
VSQL:select count(“distinct”id) from example
原生:select COUNT(distinct id) from example;
不支持的写法:
VSQL:select count(“distinct”,id,”order by id”) from example
原生:select COUNT(distinct id) OVER(order by id) from example;
mssql只支持这种写法select COUNT(id,”PARTITION by id”) from example;
返回类型
INT
示例
SELECT COUNT (distinct name) FROM sample,这里允许distinct关键字出现
SELECT COUNT (name,“partition by id”) FROM sample
返回在某一集合上对数值表达式求得的最小值。
语法
MIN (expression [,partitionexp])
参数
expression
有效表达式。
partitionexp : 以双引号包括,内容是partition by xx与order by xx
可选参数,分组累计统计条件。
返回类型
同expression
示例
SELECT MIN (money,“partition by id”) FROM sample
返回在某一集合上对数值表达式求得的最大值。
语法
MAX (expression [,partitionexp])
参数
expression
有效表达式。
partitionexp : 以双引号包括,内容是partition by xx与order by xx
可选参数,分组累计统计条件。
返回类型
同expression
示例
SELECT MAX (money,“partition by id”) FROM sample
返回按指定排序方式生成的记录序列号,从1开始。
语法
ROWNUM (partitionexp)
参数
partitionexp : 以双引号包括,内容是partition by xx或order by xx分组条件。
返回类型
INT
示例
SELECT ROWNUM(“partition by id order by sam1 asc”) FROM sample
该函数在在H2和 MYSQL下不支持。
将某种数据类型的表达式强制转换为指定的数据类型,此函数慎用。
限制条件:
1、 暂时,所有类型不能转换成CHAR,因为开发平台没有与CHAR对应的控件
2、 CLOB、BLOB不能转换成其他类型,其他类型也不可以转换为CLOB、BLOB。
语法
CAST (expression,datatype,length,precision )
参数
expression
任何有效的数据类型。
datatype : 以单引号包括,内容是Boolean、Integer、Decimal、Char、VarChar中的一种。
Length:数字总长度
Precision:数字小数位数
返回类型
指定的数据类型datatype
示例
SELECT CAST(1 ,’VARCHAR’) FROM sample
SELECT CAST(1 ,’Decimal’,10,2) FROM sample
注:select CAST(12345.678 as int)在mysql下会四舍五入返回12346.
返回数据库自动生成的uuid。
语法
UUID ()
参数
返回类型
VARCHAR
示例
SELECT UUID() FROM sample
返回0和1之间的随机浮点数。注意,由于是返回的是浮点数,如果round()截取前几位时,有可能得到重复的数据。
语法
RAND()
参数
返回类型
DECIMAL
示例
SELECT RAND() FROM sample
中文字段按拼音排序,该函数只针对oracle,其他数据库会直接处理成字段排序。
语法
SORTPY(expression)
参数
Expression
排序字段
返回类型
无
示例
SELECT * FROM sample order by SORTPY (col1)
VSQL支持引用用户在数据库里自定义的函数;注意,函数名称需要加上vsqlfunc_前缀,其它同VSQL函数语法相同;目前sqlserver的函数引用只支持dbo用户建的函数。如
Select vsqlfunc_test(1,:param,’test’) as a1 from table1
VSQL支持引用用户在数据库里自定义的存储过程;注意,存储过程名称需要加上vsqlproc_前缀,其它同VSQL函数语法类似,参数只能是常量或者具名参数。如
call vsqlproc_test(1,:param,’test’)
注:目前存储过程不支持直接返回数据。
约定 | 用于 |
大写 | V-SQL 关键字或文法终结符(在具体语法子句中予以解释) |
小写 | V-SQL的语法子句和文法非终结符(在具体语法子句中予以解释) |
斜体 | V-SQL语法中用户提供的参数。 |
| (竖线) | 分隔括号或大括号内的语法项目。只能选择一个项目。 |
[ ] (方括号) | 可选语法项目。不必键入方括号。 |
{}(大括号) | 必选语法项。不要键入大括号。 |
[ ,...n ] | 表示前面的项可重复 n 次。每一项由逗号分隔。 |
[ ...n ] | 表示前面的项可重复 n 次。每一项由空格分隔。 |
加粗 | 数据库名、表名、列名、索引名、存储过程、实用工具、数据类型名以及必须按所显示的原样键入的文本。 |
<标签> ::= | 语法块的名称。此规则用于对可在语句中的多个位置使用的过长语法或语法单元部分进行分组和标记。适合使用语法块的每个位置由括在尖括号内的标签表示:<标签>。 |
; | V-SQL 语句终止符。 |
从数据库中检索行,允许从一个或多个表中检索一个或多个行或列。
sel_stat ::= SELECT [ mod_stat ] sel_items table_exp
| sel_stat UNION [ALL] sel_stat
sel_items ::= *
| {
[ table_name . ] *
| val_exp [ [ AS ] col_als ]
} [,…n]
mod_stat ::= { DISTINCT }
table_exp ::= FROM table_src [ WHERE sc_cond ]
[ GROUP BY group_exp [ HAVING sc_cond ] ]
[ ORDER BY group_exp ]
[ LIMIT { count } [, start ]]
group_exp ::= { val_exp [ ASC | DESC ] } [,…n]
table_src ::= { table_ref }
table_ref ::= [ table_name ] [ [ AS ] table_als ]
| (sel_stat ) [ AS ] table_als [ col_items ]
col_items ::= col_nam [,…n]
join_table ::= {
{ LEFT | RIGHT | FULL } [ OUTER ] | INNER
}
{
{
{ LEFT | RIGHT | FULL } [ OUTER ] | INNER} JOIN table_ref ON sc_cond
} […n]
sc_cond ::= { bool_exp | (bool_exp ) | sc_cond |(sc_cond ) }
{ { AND | OR } { bool_exp | (bool_exp ) | sc_cond |(sc_cond ) } } […n]
bool_exp ::=
{ = | <> | < | <= | > | >= }
| val_exp BETWEEN val_exp AND val_exp
| val_exp [ NOT ] IN { (sel_stat ) | in_exp }
| val_exp [ NOT ] LIKE strval_exp ESCAPE strval_exp
| IS [ NOT ] NULL
| EXISTS (sel_stat )
in_exp ::= { NULL | val_exp } [,…n]
nval_exp ::= math_op [ { + | - } math_op ] ] […n]
math_op ::= math_subop [ { * | / } math_subop ] […n]
math_subop ::= [ + | - ] num_pri
num_pri ::= UINT
| [ [ table_name .] col_nam ]
| COUNT ( * )
| AVG (nval_exp [,CONSTSTR] )
| MAX (nval_exp [,CONSTSTR] )
| MIN (nval_exp [,CONSTSTR])
| SUM (nval_exp [,CONSTSTR])
| COUNT (val_exp [,CONSTSTR])
| COUNT ( DISTINCT val_exp )
| (sel_stat )
| CASE val_exp case_val_exp [ ELSE [ NULL | val_exp ] ] END
| CASE case_cond_exp [ ELSE [ NULL | val_exp ] ] END
| (val_exp )
| ABS (nval_exp )
| CEILING (nval_exp)
| COS (nval_exp )
| EXP (nval_exp )
| FLOOR (nval_exp )
| LOG (nval_exp )
| PI ( )
| SIGN (nval_exp )
| SIN (nval_exp )
| SQRT (nval_exp )
| LOG (nval_exp, nval_base )
| ROUND (nval_exp [ , UINT ] )
| LENGTH (val_exp )
| CAST ( { INT | INTEGER | DECIMAL [ ( UINT [ , UINT ] ) ] } , nval_exp )
case_val_exp ::= { WHEN val_exp THEN [ NULL | val_exp ] } […n]
case_cond_exp ::= { WHEN sc_cond THEN [ NULL | val_exp ] } […n]
strval_exp ::= CONSTSTR
| CHAR ( UINT )
| LOWER (val_exp )
| UPPER (val_exp )
| LEFT (val_exp )
| RIGHT (val_exp )
| TRIM (val_exp )
| LTRIM (val_exp )
| RTRIM (val_exp )
| SUBSTRING (val_exp )
| CONCAT (val_exp )
| CAST ( { CHAR [ UINT ] | VARCHAR [ UINT ] } , val_exp )
dateval_exp ::= DATESTR
| TIMESTR
| VARCHARSTR
| NOW ( )
| TODAY ( )
| CURTIME ( )
| DAYOFMONTH (val_exp )
| DAYOFWEEK (val_exp )
| DAYOFYEAR (val_exp )
| YEAR (val_exp )
| MONTH (val_exp )
| WEEK (val_exp )
| HOUR (val_exp )
| MINUTE (val_exp )
| SECOND (val_exp )
| MONTHNAME (val_exp )
| DAYNAME (val_exp )
| QUARTER (val_exp )
| DATEADD (UINT , val_exp )
| DATEDIF (UINT , val_exp )
| CAST( VARCHAR , val_exp )
定义Select文法结构。
语法
sel_stat ::= SELECT [mod_stat ] sel_items table_exp
| sel_stat UNION sel_stat
参数
sel_items
select items,指定由查询返回的列。
table_exp
table expression,指定查询基于的表表达式。
Mod_sta
Modifier statement, 修饰符,被用于指定查询返回的行数。
UNION
将两个或更多查询的结果组合为单个结果集,该结果集包含联合查询中的所有查询的全部行。
指定由查询返回的列。
语法
sel_items ::= *
| {
[ table_name . ] *
| val_exp [ [ AS ] col_als ]
} [,…n]
参数
*
返回指定表的所有列的所由行。
说明 不能使用多个*指定多个表的所有列。
示例
SELECT * , * FROM A , B WHERE A. lname = B. lname 是非法的语法。
SELECT A.* , B.* FROM A , B WHERE A. lname = B. lname 是合法的语法。
table_name
table name,查询表名称。它必须遵循一般标识符或引号标识符的命名规则。
val_exp
value expression,作为查询返回列的表达式。
AS
修饰符,用于将查询表达式指定为列名称。
col_als
column alias,查询表达式的列别名。它必须遵循一般标识符或引号标识符的命名规则。
指定查询返回行数的修饰符号
语法
mod_stat ::= { DISTINCT }
参数
DISTINCT
指定在结果集中只能显示唯一行。为了 DISTINCT 关键字的用途,空值被认为相等。
注释
V-SQL语法规定,在modifier statement后只允许接受符合一般标识符和引号标识符命名规则的列定义作为查询返回列。
示例 SELECT DISTINCT pub_id FROM publishers。
指定查询基于的表表达式。
语法
table_exp ::= FROM table_src [ WHERE sc_cond ]
[ GROUP BY group_exp [ HAVING sc_cond ] ]
[ ORDER BY group_exp ]
[ LIMIT { count } [, start ]]
参数
FROM
修饰符,用于指定在 DELETE、SELECT 和 UPDATE 语句中使用的表、视图、派生表和连接表。
table_src
table source,指定查询使用的表资源。它可以是表引用和联接表。
WHERE
修饰符,用于指定查询返回行的条件。
sc_cond
search condition, 指定查询返回行的条件。
[ LIMIT { count } [ , start ]]
用于分页,从指定位置start开始读取指定数目count记录,start 默认1
指定分组(GROUP BY )和排序(ORDER BY)的表达式。
语法
group_exp ::= { val_exp [ ASC | DESC ] } [,…n]
参数
val_exp
value expression,基于V-SQL的任意合法表达式。
ASC
修饰符,用于指定按升序分组(GROUP BY )和排序(ORDER BY)。ASC是默认值。
DESC
修饰符,用于指定按降序分组(GROUP BY )和排序(ORDER BY)。
指定查询使用的表资源。
语法
table_src ::= { table_ref } 注意这里去掉了[,…n],即不允许表连接直接用,号
参数
table_ref
table reference,指定查询引用的表。它可以是数据库表,也可以是由子查询组成的表。
join_table
joined table,指定查询使用的联接表。
注释
不允许在FROM关键字后同时使用引用表和联接表组成表资源。
如:SELECT * FROM A , B LEFT JOIN C ON B.id = C.id……是非法的语句。
指定查询引用的表。
语法
table_ref ::= [ table_name ] [ [ AS ] table_als ]
| (sel_stat ) [ AS ] table_als [col_items ]
参数
table_name
table name, 表名称。它必须遵循一般标识符或引号标识符的命名规则。
AS
修饰符,用于指定标的别名。
table_als
table alias,表的别名。
col_items
column list,指定列的列表。
指定列的列表。
语法
col_items ::= col_nam [,…n]
参数
col_nam
column name,列名,它必须遵循一般标识符或引号标识符的命名规则。
指定查询基于的表联接。
语法
join_table ::= {
{ LEFT | RIGHT | FULL } [ OUTER ] | INNER
} JOIN table_ref ON sc_cond
}
{
{
{ LEFT | RIGHT | FULL } [ OUTER ] | INNER
} JOIN table_ref ON sc_cond
} […n]
参数
table_ref
table reference,表引用,指定查询引用的表。它可以是数据库表,也可以是由子查询组成的表。
LEFT
修饰符,用于指定联接类型为左外联接。
RIGHT
修饰符, 用于指定联接类型为右外联接。
FULL
修饰符, 用于指定联接类型为完全外联接。
OUTER
修饰符,与LEFT,RIGHT,FULL 配合使用。它是可选的。
INNER
修饰符,用于指定联接类型为内联接。
JOIN
联接修饰符。
ON
修饰符,用于指定联接基于的条件。
sc_cond
search condition,表联接条件。
指定用于查询和约束的条件。
语法
sc_cond ::= { bool_exp | (bool_exp ) | sc_cond |(sc_cond ) }
{ { AND | OR } { bool_exp | (bool_exp ) | sc_cond |(sc_cond ) } } […n]
参数
bool_exp
谓词。它是V-SQL中定义的所有返回BOOL值的元关系运算。
NOT
修饰符,求BOOL条件逻辑非。
AND
修饰符,求BOOL条件逻辑与。
OR
修饰符,求BOOL条件逻辑或。
返回BOOL值的谓词。
语法
bool_exp ::=
{ = | <> | < | <= | > | >= }
| val_exp [ NOT ] BETWEEN val_exp AND val_exp
| val_exp [ NOT ] IN { (sel_stat ) | in_exp }
| val_exp [ NOT ] LIKE strval_exp ESCAPE strval_exp
| IS [ NOT ] NULL
| EXISTS (sel_stat )
参数
val_exp
基于V-SQL的任意合法的表达式。
=
关系运算符,等于。
<>
关系运算符,不等于。
<
关系运算符,小于。
>
关系运算符号,大于。
<=
关系运算符号,小于或等于。
>=
关系运算符号,大于或等于。
NOT
修饰符,求逻辑非。
BETWEEN … AND …
修饰符,用于指定测试表达式所在的测试范围。测试表达式必须与测试范围表达式由相同的类型。
IN
修饰符,确定给定的值是否与子查询或列表中的值相匹配。测试表达式必须与IN后的所有表达式具有相同的类型。
LIKE
修饰符, 确定给定的字符串是否与指定的模式匹配。
ESCAPE
修饰符,允许在字符串中搜索通配符而不是将其作为通配符使用,这里需要注意:
Sqlserver中"/", "%", "_", "[", "]"需要转义;
而db2、oracle中只"%", "_"需要转义;
strval_exp
string value expression,字符串表达式。
EXISTS
修饰符,指定一个子查询,检测行的存在。 如有行存在,返回TRUE,否则返回FALSE。
in_exp
用于IN后的表达式列表。
指定用于谓词IN后的表达式列表。
语法
in_exp ::= { NULL | val_exp } [,…n]
参数
NULL
允许在谓词IN后使用NULL.
val_exp
value expression,基于V-SQL的任意合法的表达式。
指定基于V-SQL的任意合法的表达式。
语法
val_exp ::= nval_exp
参数
nval_exp
numeric value expression,数值表达式。
strval_exp
string value expression,数值表达式。
dateval_exp
VARCHAR value expression,时间日期表达式。
指定数值表达式。
语法
nval_exp ::= math_op [ { + | - } math_op ] ] […n]
参数
math_op
数值运算术语。
指定运算术语。
语法
math_op ::= math_subop [ { * | / } math_subop ] […n]
参数
math_subop
数值运算因子。
指定运算因子。
语法
math_subop ::= [ + | - ] num_pri
参数
num_pri
基本数值运算元素。
指定基本数值运算元素。
语法
num_pri ::= UINT
| [ [ table_name. ] col_nam ]
| COUNT ( * )
| AVG (nval_exp [,CONSTSTR] )
| MAX (nval_exp [,CONSTSTR] )
| MIN (nval_exp [,CONSTSTR])
| SUM (nval_exp [,CONSTSTR])
| COUNT (val_exp [,CONSTSTR])
| COUNT ( distinct val_exp )
| (sel_stat )
| CASE val_exp case_val_exp [ ELSE [ NULL | val_exp ] ] END
| CASE case_cond_exp [ ELSE [ NULL | val_exp ] ] END
| ( val_exp )
| ABS ( nval_exp )
| CEILING ( nval_exp )
| COS ( nval_exp )
| EXP ( nval_exp )
| FLOOR ( nval_exp )
| LOG ( nval_exp,nval_base )
| PI ( )
| SIGN ( nval_exp )
| SIN ( nval_exp )
| SQRT ( nval_exp )
| LOG ( nval_exp )
| ROUND ( nval_exp [ , UINT ] )
| MOD ( nval_exp , nval_exp )
| LENGTH ( val_exp )
| CAST ( { INT | INTEGER | DECIMAL [ ( UINT [ , UINT ] ) ] } , nval_exp )
参数
UINT
无符号数。如:10, 1.23, 10 e1, 10.0 E1, 0.5 E10-2, 1.1 E10+4等。
table_name
Table name, 数据表名称。
col_nam
column name,列名。
COUNT
返回 组中项目的数量。
AVG
返回在某一集合上对数值表达式求得的平均值。
MAX
返回表达式的最大值。
MIN
返回表达式的最小值。
SUM
返回在某一集合上对数值表达式求得的和。
定位(简单)的CASE … WHEN …表达式。
语法
case_val_exp ::= { WHEN val_exp THEN [ NULL | val_exp ] } […n]
参数
注释
带搜索的CASE … WHEN …表达式。
语法
case_cond_exp ::= { WHEN sc_cond THEN [ NULL | val_exp ] } […n]
参数
注释
该语法(带条件的CASE … WHEN …)。
指定字符表达式。
语法
strval_exp ::= CONSTSTR
| CHAR ( UINT )
| LOWER ( val_exp )
| UPPER ( val_exp )
| LEFT ( val_exp )
| RIGHT ( val_exp )
| LTRIM ( val_exp )
| RTRIM ( val_exp )
| SUBSTRING ( val_exp )
| CONCAT ( val_exp )
| CAST ( { CHAR [ UINT ] | VARCHAR [ UINT ] } , val_exp )
参数
指定日期时间表达式。
语法
dateval_exp ::= DATESTR
| TIMESTR
| VARCHARSTR
| NOW ( )
| TODAY ( )
| CURTIME ( )
| DAYOFMONTH (val_exp )
| DAYOFWEEK (val_exp )
| DAYOFYEAR (val_exp )
| YEAR (val_exp )
| MONTH (val_exp )
| WEEK (val_exp )
| HOUR (val_exp )
| MINUTE (val_exp )
| SECOND (val_exp )
| MONTHNAME (val_exp)
| DAYNAME (val_exp )
| QUARTER (val_exp )
| DATEADD (UINT , val_exp )
| DATEDIF (UINT , val_exp )
| CAST(VARCHAR , val_exp )
参数
从表中删除行。
语法
del_stat ::= DELETE [ FROM ] [ table_name ] [ WHERE sc_cond ]
参数
table_name
table name, 数据表名称。它必须遵循一般标识符或引号标识符的命名规则。
sc_cond
指定从表中删除行的条件。
将新行添加到表或视图。
语法
ins_stat ::= INSERT INTO [ table_name ] [ (col_items ) ] { VALUES (in_exp ) | sel_stat }
参数
table_name
table name, 数据表名称。它必须遵循一般标识符或引号标识符的命名规则。
col_items
指定要向表中插入目标列列表。
in_exp
指定向表中行插入的表达式列表
upd_stat ::= UPDATE [ table_name ] SET set_items [FROM join_table ] [ WHERE sc_cond ]
set_items ::= [ [ table_name .] col_nam ] = upd_src
upd_src ::= NULL
| val_exp
定义更新表的实现。
语法
upd_stat ::= upd_stat ::= UPDATE [ table_name ] SET set_items [FROM join_table ] [ WHERE sc_cond ]
参数
table_name
table name, 数据表名称。它必须遵循一般标识符或引号标识符的命名规则。
set_items
更新列列表。
sc_cond
更新条件。
关联更新表
示例
UPDATE a set (a.a2,a.a3)=(‘1’, ‘2’) 是非法的语法。
UPDATE a set a.a2=b.b2,a.a3=b.b2 FROM a INNER JOIN (select * from b ) b ON a.a1 = b.b1 where a.a1 is null 是合法的语法
指定更新列列表。
语法
set_items ::= [ [ table_name .] col_nam ] = upd_src
参数
table_name
table name, 数据表名称。它必须遵循一般标识符或引号标识符的命名规则。
col_nam
column name,列名称。它必须遵循一般标识符或引号标识符的命名规则。
upd_src
更新资源。
指定更新资源。
语法
upd_src ::= NULL
| val_exp
参数
NULL
更新为NULL.
val_exp
更新为表达式的值。
3. V-SQL限制及未解决问题
1)select 里 * 和 case when 不能同时使用
2)函数CAST:CHAR,BLOB,CLOB不要转换就行了
3)select 后面,需要 x.xxx [表名.字段名]
4)select 里 *号不能和其他字段一起使用
5)V-sql是对应3个数据库使用的,所以3个数据库的关键字,保留字都不可以写成查询的别名
6)charindex 指定开始位置(start),开始位置必须大于0,否则报错.如果不能确定,请使用casewhen处理。
7)还有in 语句必须要两个括号
正常的写法:select * from t where id in (select id from t2)
VSQL的写法:select * from t where id in ((select id from t2))
8)mysql数据库不支持ROWNUM函数及partition by 语法
9)mysql数据库的唯一性索引如果是utf-8编码,长度不能超过255。在存储引擎为InnoDB的时候,最大的唯一性索引长度为767 bytes,utf-8 一个字符占用三个字节. 256*3=768 > 767
4. 保留字
V-SQL是对应3个数据库使用的,为了保证可以跨数据库使用,所以3个数据库的关键字,保留字都不可以写成查询的别名或者表的字段名。建表时,最好检查下保留字是否有冲突。如果遇到无法处理时,可通过使用别名处理,例如”status”是保留字: select status as fstatus from table
1) 名称中不能出现这些字符:
空格,’!’,’"‘,’#’,’$’,’%’,’&’,’\’’,’(‘,’)’,’*’,’+’,’,’
,’-’,’.’,’/’,’:’,’;’,’<‘,’=‘,’>‘,’?’,’@’
2) ORACLE的保留字:
ALL, ALTER, AND, ANY, AS, ASC, BETWEEN, BY,CHAR, CHECK, CLUSTER, COMPRESS, CONNECT, CREATE,
DATE, DECIMAL, DEFAULT, DELETE,DESC, DISTINCT, DROP, ELSE, EXCLUSIVE, EXISTS, FLOAT, FOR, FROM,
GRANT, GROUP,HAVING, IDENTIFIED, IN, INDEX, INSERT, INTEGER, INTERSECT, INTO, IS, LIKE,
LOCK,LONG, MINUS, MODE, NOCOMPRESS, NOT, NOWAIT, NULL, NUMBER, OF, ON, OPTION, OR,ORDER,
PCTFREE, PRIOR, PUBLIC, RAW, RENAME, RESOURCE, REVOKE, SELECT, SET, SHARE,SIZE, SMALLINT,
START, SYNONYM, TABLE, THEN, TO, TRIGGER, UNION, UNIQUE, UPDATE,VALUES, VARCHAR, VARCHAR2,
VIEW, WHERE, WITH
3) MSSQL的保留字:
ACTION, ADD, AGGREGATE, ALL, ALTER, AFTER, AND, AS,ASC, AVG, AVG_ROW_LENGTH, AUTO_INCREMENT,
BETWEEN, BIGINT, BIT, BINARY, BLOB, BOOL,BOTH, BY, CASCADE, CASE, CHAR, CHARACTER, CHANGE,
CHECKSUM, COLUMN, COLUMNS,COMMENT, CONSTRAINT, CREATE, CROSS, CURRENT_DATE, CURRENT_TIME,
CURRENT_TIMESTAMP, DATA,DATABASE, DATABASES, DATE, DATETIME, DAY, DAY_HOUR, DAY_MINUTE,
DAY_SECOND, DAYOFMONTH,DAYOFWEEK, DAYOFYEAR, DEC, DECIMAL, DEFAULT, DELAYED, DELAY_KEY_WRITE,
DELETE, DESC,DESCRIBE, DISTINCT, DISTINCTROW, DOUBLE, DROP, END, ELSE, ESCAPE, ESCAPED,
ENCLOSED,ENUM, EXPLAIN, EXISTS, FIELDS, FILE, FIRST, FLOAT, FLOAT4, FLOAT8, FLUSH,RELOAD
FOREIGN,FROM, FOR, FULL, FUNCTION, GLOBAL, GRANT, GRANTS, GROUP, HAVING, HEAP,CHECK, REFERENCES
HIGH_PRIORITY,HOUR, HOUR_MINUTE, HOUR_SECOND, HOSTS, IDENTIFIED, IGNORE, IN, INDEX, INFILE,
INNER,INSERT, INSERT_ID, INT, INTEGER, INTERVAL, INT1, INT2, INT3, INT4, INT8, INTO, IF,IS,
ISAM, JOIN, KEY, KEYS, KILL, LAST_INSERT_ID, LEADING, LEFT, LENGTH, LIKE,LINES, LIMIT, LOAD,
LOCAL, LOCK, LOGS, LONG, LONGBLOB, LONGTEXT, LOW_PRIORITY, MAX,MAX_ROWS, MATCH, MEDIUMBLOB,
MEDIUMTEXT, MEDIUMINT, MIDDLEINT, MIN_ROWS, MINUTE,MINUTE_SECOND, MODIFY, MONTH, MONTHNAME,
MYISAM, NATURAL, NUMERIC, NO, NOT, NULL, ON,OPTIMIZE, OPTION, OPTIONALLY, OR, ORDER, OUTER,
PRECISION, PRIMARY, PROCEDURE, PROCESS, PROCESSLIST, PRIVILEGES, READ, REAL,
REGEXP, RENAME, REPLACE, RESTRICT, RETURNS, REVOKE, RLIKE, ROW, ROWS, SECOND,SELECT, SET,
SHUTDOWN, SMALLINT, SONAME, SQL_BIG_TABLES, SQL_BIG_SELECTS,SQL_LOW_PRIORITY_UPDATES,
SQL_LOG_OFF, SQL_LOG_UPDATE, SQL_SELECT_LIMIT, SQL_SMALL_RESULT,SQL_BIG_RESULT, SHOW,
STRAIGHT_JOIN, STARTING, STATUS, STRING, TABLE, TABLES,TEMPORARY, TERMINATED, TEXT, THEN,
TIME, TIMESTAMP, TINYBLOB, TINYTEXT, TINYINT,TRAILING, TO, TYPE, USE, USING, UNIQUE, UNLOCK,
UNSIGNED, UPDATE, USAGE, VALUES,VARCHAR, VARIABLES, VARYING, VARBINARY, WITH, WRITE, WHEN,
WHERE, YEAR, YEAR_MONTH,ZEROFILL,PASSWORD,SQL_WARNINGS,OUTFILE, PACK_KEYS, PARTIAL
4)VSQL中的保留字:
LOG, CONCAT, SUBSTRING, TRIM, CAST, RIGHT, CHARS,TODAY, CURTIME, NOW, DAYNAME, FORMATDATE,
QUARTER, DATEADD, DATEDIFF, TRUNC, WEEK,UUID, MOD, CHARINDEX, CEILING, ISNULL, REPEAT, RAND,
SQRT, EXP, MIN, COUNT, REVERSE, ASCII, PI, FLOOR, ROUND, DAYNAME, DATEADD,
DATEDIFF, LTRIM, RTRIM, CASEWHEN, ROWNUM, TODATE, SUM, UPPER, LOWER, SIGN, POWER,LENGTH, LEFT,
DAYOFMONTH, MONTHNAME, DAYOFWEEK, YEAR, DAYOFYEAR, HOUR, MINUTE, SECOND,MONTH, MAX, REPLACE,
ISNUMERIC, ABS, SIN, COS, TAN,AVG
5) DB2中:暂时没有