CloudNote

Appearance

11 语法兼容 ​

11.1 DDL ​

11.2.1 TABLE ​

  • CREATE TABLE Syntax
sql
CREATE TABLE tblname (createdefinition,...) 
//createdefinition: colname column_definition

示例

sql
create table x_organization(
  orgNO  varchar(20) not null,
  orgName  varchar(20) ,
  orgType  smallint default 2,
  openDate date not null,
  legalPerson varchar(20),
  primary key (orgNO)
);
  • ALTER TABLE Syntax
sql
ALTER TABLE tblname alterspecification ...
alterspecification: | ADD [COLUMN] colname columndefinition  | ADD [COLUMN] (colname columndefinition,...) | MODIFY [COLUMN] colname columndefinition | DROP [COLUMN] colname | ADD [INDEX|KEY] [indexname] (indexcolname,...) | DROP {INDEX|KEY} indexname | ADD PRIMARY KEY (indexcolname,...)

示例

sql
Alter Table x_organization add PRIMARY KEY (ORGNO);
Alter Table x_organization drop PRIMARY KEY;
Alter Table x_organization add column8 char(10);
Alter Table x_organization add ( 
	colum8 char(10),  
	column9 numeric(5,1) 
)；
Alter Table x_organization modify column8 numeric(3,1);
Alter Table x_organization modify (
 	column8 numeric(3,1), 
	column9 char(11) 
);
Alter Table x_organization drop column column8;

已知限制：

不支持rename语法，如：

Alter Table x_organization rename column column8 to column18;
Alter table x_organization rename to organization8;
  • DROP TABLE Syntax
sql
DROP TABLE tbl_name;
  • TRUNCATE TABLE Syntax
sql
TRUNCATE TABLE tbl_name;

11.2.2 INDEX ​

  • CREATE INDEX Syntax
sql
CREATE [UNIQUE] INDEX indexname  ON tbl_name (indexcolname,...)；

示例

sql
create index index_orgname_orgtype on x_organization(orgname,orgtype)；
create index index_opendate on x_organization(opendate)；
  • DROP INDEX Syntax
sql
DROP INDEX index_name ON tbl_name

  • ALTER INDEX Syntax

    ​ 不支持索引修改，可尝试先删除再重建。

11.2 DML ​

11.2.1 INSERT ​

11.2.1.1 语法 ​

sql
INSERT INTO tbl_name
(col_name,...)
{VALUES | VALUE} ({expr },...)

11.2.1.2 示例 ​

sql
insert into test (col1,col3) values(1,'cust1'); 
insert into x_organization (orgNO, opendate) values('1', to_date('2018-11-08','yyyy-mm-dd'));

11.2.1.3 限制 ​

  1. 在插入ER关系的子表时，每个语句只允许插入一个ROW ；
  2. INSERT INTO ... SELECT ...FROM...使用限制 仅支持分片规则相同的表 并且拆分字段相同，如：x_organization2和fromx_organization表分片规则相同并且拆分字段都是orgNo。
sql
insert into x_organization2(orgNO, opendate) select orgNO ,opendate fromx_organization;

​ 出于性能考虑，不建议使用insert into ...select... from ... 这种语法，由于在单库情况下，这条语句直接交给数据库去处理损耗比较小；通过LIBRA则需要拆解SQL、合并结果集等多步才能完成上述工作，性能损耗比较大。【友情提示--->建议从业务角度去规避类似SQL】

  1. 不支持不包含拆分字段的INSERT语句除全局表外；

11.2.2 DELETE ​

11.2.2.1 语法 ​

sql
DELETE 
FROM tblname [WHERE wherecondition]

11.2.2.2 示例 ​

sql
delete from x_teller
delete from x_teller where tellerNO='1';
delete from x_teller where tellerNO<>'2';
delete from x_teller where tellerLevel>6;
delete from x_teller where tellerLevel>=6;
delete from x_teller where tellerLevel<>6;
delete from x_teller where tellerLevel!=6;
delete from x_teller where tellerLevel < 6;
delete from x_teller where tellerLevel <= 9;
delete from x_teller where tellerLevel <= ( 3+1-2 )
delete from x_teller where tellerLevel <= ( 2*2-6/2+1 )
delete from x_teller where tellerLevel between 3 and 9
delete from x_teller where tellerName like 'te%';
delete from x_teller where tellerNO in ('1','2','3');
delete from x_teller where tellerNO not in ('1','2','3');
delete from x_teller where not( tellerNO in ('1','2','3') );
delete from x_teller where not( tellerNO = '1' );
delete from x_teller where tellerNO='1' and status='D';
delete from x_teller where tellerNO='1' or status='D';
delete from x_teller where tellerLevel*2 >6;

11.2.2.3 限制 ​

  1. 不支持DELETE使用子查询 如：
sql
delete from x_teller where tellerNO in ( select tellerNO from x_trailbox trailbox wheretrailbox.boxNO='11');
delete from x_teller where exists ( select * from x_organization);
delete from x_teller where not exists ( select * fromx_organization);
  1. DELETE语句中的where_condition部分只允许出现简单的条件，不能支持计算表达式；

11.2.2 UPDATE ​

11.2.2.1 语法 ​

sql
UPDATE table_reference
SET colname1={expr1} [, colname2={expr2}] ...
[WHERE where_condition]

11.2.2.2 示例 ​

sql
update x_organization set orgName='updateName'
update x_organization set opendate= to_date('2018-12-22','yyyy-mm-dd');
update x_organization set orgName='updateName2', orgType=3;
update x_organization set orgName='updateName4', orgType=4 
where orgNO='1';"

11.2.2.3 限制 ​

  1. 不支持update中使用子查询 如：
sql
update x_teller set tellerLevel=8where orgNO in (  select orgNo from x_ organization whereorgType=4);

  1. UPDATE语句中的where_condition部分只允许出现简单的条件，不能支持计算表达式；

  2. 不支持多表Join 的UPDATE；

  3. 不支持UPDATE拆分字段的值；

11.2.4 SELECT ​

11.2.4.1 语法 ​

sql
SELECT 
[ALL | DISTINCT | DISTINCTROW ]
select_expr
[, select_expr ...]
[FROM table_references [WHERE where_condition]
[GROUP BY {col_name | expr | position} [ASC | DESC], ...]
HAVING where_condition, ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

11.2.4.2 基础示例 ​

sql
select id,col1,col3 from test where id=3;
select distinct col1,col3 from test where id>=3;
select count(*),max(id),col1 from test group by col1 desc having(count(*)>1) order by col1 desc;
select id,col1,col3 from test order by id limit 2 offset 2;
select id,col1,col3 from test order by id limit 2,2;
select 1+1,'test',id,col1*1.1,now() from test limit 3;
select current_date,current_timestamp;

11.2.4.3 JOIN 示例 ​

  1. 支持语法

    table_references:

    sql
    table_reference [, table_reference] ...

    table_reference:

    table_factor | join_table

    table_factor:

    tbl_name [[AS] alias] 
| table_subquery [AS] alias
| ( table_references )

    join_table:

    table_reference [INNER | CROSS] JOIN table_factor [join_condition]
| table_reference STRAIGHT_JOIN table_factor
| table_reference STRAIGHT_JOIN table_factor ON conditional_expr
| table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor

    join_condition:

    ON conditional_expr
| USING (column_list)

  2. 示例

    sql
    select * from x_teller teller, x_trailbox box where teller.tellerNO=box.tellerNO;
select * from x_teller natural join x_trailbox;
select * from x_teller teller inner join x_trailbox box on teller.tellerNO=box.tellerNO;
select * from (x_teller t inner join x_organization o on o.orgNO=t.orgNO) 
		inner join x_trailbox b on t.tellerNO=b.tellerNO;
select * from x_teller teller left join x_trailbox box on teller.tellerNO=box.tellerNO;
select * from (x_teller t left join x_organization o on o.orgNO=t.orgNO) 
		left join x_trailbox b on t.tellerNO=b.tellerNO;
select * from x_teller teller right join x_trailbox box on teller.tellerNO=box.tellerNO;
select * from (x_teller t right join x_organization o on o.orgNO=t.orgNO) 
		right join x_trailbox b on t.tellerNO=b.tellerNO;
select * from x_teller teller full join x_trailbox box on teller.tellerNO=box.tellerNO;
select * from (x_teller t full join x_organization o on o.orgNO=t.orgNO) 
		full join x_trailbox b on t.tellerNO=b.tellerNO;

11.2.4.4 排序示例 ​

sql
select * from x_teller order by tellerLevel;
select * from x_teller where tellerLevel > 2 order by tellerLevel desc, tellerNO asc;

11.2.4.5 分页示例 ​

适用于不同数据库语法

  • mysql
select id,col1,col3 from test limit 2 offset 2;
  • oracle
SQL
select * from ( 
  select rownum F, t.*  from ( 
    select * from x_teller where tellerLevel>2 
  ) t where rownum<=5
) where F>2;
  • db2
SQL
SELECT *
  FROM (SELECT B.*, ROWNUMBER () OVER () AS TN
          FROM (SELECT * FROM x_trailbox) AS B
    ) AS A
WHERE A.TN BETWEEN 1 and 10;

11.2.4.6 UNION 示例: ​

  1. 支持语法

    SELECT ...
    UNION [ALL | DISTINCT] SELECT ...
    [UNION [ALL | DISTINCT] SELECT ...]

    ​

  2. 示例

    SQL
    select orgNO xNO, orgName xName from x_organization
union
select tellerNo xNO, tellerName xName from x_teller;

select tellerNO, tellerName from x_teller where tellerLevel>3
union all
select tellerNO, tellerName from x_teller where tellerLevel>6;

11.2.4.7 已验证组合 ​

  1. Join&Sorting

    SQL
    select * from x_teller teller left join x_trailbox box on teller.tellerNO=box.tellerNO order by tellerLevel;

  2. Group&Sorting

    SQL
    select count(*), orgNo from x_teller teller group by orgNO order by orgNo;

  3. Paging&Join

    • mysql
    SQL
    select * from x_teller teller
    left join x_trailbox box
    on teller.tellerNO = box.tellerNO
    limit 2 offset 5

    • oracle

      SQL
      SELECT *
  FROM (SELECT ROWNUM F, t.*
          FROM (SELECT *
                  FROM x_teller teller
                       LEFT JOIN x_trailbox box
                          ON teller.tellerNO = box.tellerNO) t
         WHERE ROWNUM <= 5)
 WHERE F > 2;

    • db2

      SQL
      SELECT *
  FROM (SELECT B.*, ROWNUMBER () OVER () AS TN
          FROM (SELECT *
                  FROM x_teller teller
                       LEFT JOIN x_trailbox box
                          ON teller.tellerNO = box.tellerNO) AS B
 ) AS A
WHERE A.TN BETWEEN 1 and 2;

  4. Paging&Sorting

    • mysql

      SQL
      select * from x_teller
    where tellerLevel > 2
    order by tellerLevel 
    limit 2 offset 5

    • oracle

      SQL
      SELECT *
  FROM (SELECT ROWNUM rownum_, t.*
          FROM (  SELECT *
                    FROM x_teller
                   WHERE tellerLevel > 2
                ORDER BY tellerLevel) t
         WHERE ROWNUM <= 5)
 WHERE rownum_ > 2;

    • db2

      SQL
      SELECT *
  FROM (SELECT B.*, ROWNUMBER () OVER () AS TN
          FROM ( SELECT *
                    FROM x_trailbox
                ORDER BY boxno DESC) AS B
) AS A
WHERE A.TN BETWEEN 1 and 2;

11.2.5 SUB QUERY ​

11.2.5.1 Comparisons Using Subqueries ​

  1. 语法

    The most common use of a subquery is in the form:

    non_subquery_operand comparison_operator (subquery)

    Where comparison_operator is one of these operators:

    =    >    <    >=    <=    <>    !=    <=>

    And this construct:

    non_subquery_operand LIKE (subquery)

  2. 示例

    SQL
    SELECT * FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2);

11.2.5.2 Subqueries with ANY, IN, or SOME ​

  1. 语法

    operand comparison_operator ANY (subquery)
operand IN (subquery)
operand comparison_operator SOME (subquery)

    Where comparison_operator is one of these operators:

    SQL
    =    >    <    >=    <=    <>    !=

  2. 示例

    SQL
     SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
 SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);
 SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);

11.2.5.3 Subqueries with ALL ​

  1. 语法

    operand comparison_operator ALL (subquery)

  2. 示例

    SQL
    SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);

11.2.5.4 Subqueries with EXISTS or NOT EXISTS ​

示例

SQL
SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

11.2.5.5 Derived Tables (Subqueries in the FROM Clause) ​

  1. 语法

    SQL
    SELECT ... FROM (subquery) [AS] tbl_name ...

  2. 示例

    SQL
    SELECT sb1,sb2,sb3
  FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb
  WHERE sb1 > 1;

11.2.5.6 Optimizing Subqueries ​

示例

SQL
SELECT * FROM t1 WHERE t1.column1 IN (SELECT column1 FROM t2 ORDER BY column1);
SELECT * FROM t1 WHERE t1.column1 IN (SELECT DISTINCT column1 FROM t2);
SELECT * FROM t1
  WHERE EXISTS (SELECT a FROM t2 WHERE b = some_const);

11.2.5.7 已知限制 ​

不支持关联子查询

​ 不支持写法：

SQL
1.select * from x_teller r where tellerno in (select boxno from x_trailbox x where r.tellername = x.boxno);
2.SELECT tellerNo, tellerName, tellerLevel,
       (SELECT boxNO FROM x_trailbox box WHERE box.tellerNo = teller.tellerNO) AS boxNO
  FROM x_teller teller WHERE tellerLevel > 3;

​ 建议改写：

SQL
1.select * from x_teller r, x_trailbox x where r.tellerno = x.boxno;
2.SELECT tellerNo, tellerName, tellerLevel, box.boxNO 
	from x_teller teller left join x_trailbox box on box.tellerNo = teller.tellerNO

11.2.6 函数支持情况 ​

  • LIBRA支持大部分字符、数字、日期及格式相关的函数，如下函数已经验证过。
函数类型支持情况示例
CONCAT支持select concat('_', tellerNO) xNO, 'Y'||tellerName as xName, tellerLevel from x_teller where tellerLevel > 3;
INSTR支持select INSTR(tellername,'e',-1,1) from x_teller;
LPAD支持select LPAD(tellername,3,'*') from x_teller;
LTRIM支持select LPAD(tellername,12,'*') from x_teller;
REPLACE支持select LTRIM(tellername,'tel') from x_teller;
RPAD支持select replace(tellername,'e','g') from x_teller;
ABS支持select RPAD(tellername,12,'*') from x_teller;
FLOOR支持select RPAD(tellername,3,'*') from x_teller;
LEAST支持select abs(tellerno) from x_teller;
MOD支持select FLOOR(tellerno) from x_teller;
POWER支持select least(-1,2,23,2) from x_teller;
ROUND支持Select mod(tellerno,tellerlevel),x.tellerno,x.tellerlevel from x _teller x;
SIGN支持select sign(tellerno) from x_teller;
SORT支持select SQRT(tellerlevel),x.tellerlevel from x_teller x;
EXTRACT支持SELECT EXTRACT(YEAR FROM createdate) FROM x_trailbox;
LAST_DAY支持select LAST_DAY(createdate),createdate FROM x_trailbox;
CURRENT_TIMESTAMP支持select CURRENT_TIMESTAMP(9) FROM x_trailbox;

  • 已知限制

    • 如果是自定义函数，理论上如果不涉及多条记录的计算且在查询列中都支持；

    • 不支持在WHERE条件中将函数使用在 分片键字段 或者 分片键的值 上面。例如：

      sql
      select * FROM x_trailbox where to_char(createdate,'yyyyMMdd') = '20080921';
select * FROM x_trailbox where createdate = to_date('20080921'，'yyyyMMdd');

11.2.7 存储过程 ​

  • 创建存储过程

    通过LIBRA暂时不支持在oracle、db2数据库上创建存储过程。需要用户人工到真实数据节点创建存储过程。

  • 调用存储过程

    通过hint指定datanode路由。示例如下：

    SQL
    /*!LIBRA:{‘datanode’:‘db1’ */call proc_arc(1);

  • 删除存储过程

    通过hint指定ldatanode，去删除在某个节点上已经创建的存储过程。

    sql
    /*!LIBRA:{‘datanode’:'dn1'} */drop procedure if exists proc_arc;

  • 已知限制

    仅支持存储过程通过HINT模式到指定节点执行，并且其内部不支持跨节点运算。

11.3 其他已知限制 ​

  • 暂不支持BEGIN...END、LOOP...END LOOP、WHILE..DO..END WHILE、IF...END IF等复合语句，该类语句一般只在存储过程内部使用，一般不会在联机系统的应用端使用。
  • 由于触发器只在数据库内部运行，并不在LIBRA的范围，其触发器内部逻辑不支持跨节点处理。
  • 暂不支持临时表。