Chapter 6. SQL Syntax

Index:

1. Select
2. Insert
3. Update
4. Delete
5. CREATE CATALOG
6. CREATE DATABASE
7. DROP DATABASE
8. FLUSH DATABASE
9. COPY DATABASE
10. CLONE DATABASE
11. CREATE TABLE
12. DROP TABLE
13. ALTER TABLE
14. TRUNCATE TABLE
15. RENAME TABLE
16. LOCK TABLE
17. UNLOCK TABLE
18. CREATE VIEW
19. DROP VIEW
20. CREATE SEQUENCE
21. DROP SEQUENCE
22. ALTER SEQUENCE
23. SET TRANSACTION
24. START TRANSACTION
25. COMMIT
26. ROLLBACK
27. SAVEPOINT
28. RELEASE SAVEPOINT
29. Call Procedure
30. EXPLAIN
31. Pseudo Tables
32. Pseudo Columns
33. Pseudo Variables
34. Split Multivalue Column Into Rows
35. Declare Variable
36. SET Variable
37. Common Table Expression
38. Comment Syntax
39. SQL States

Use ";" to separate multi sql statements. "reserved word" , `reserverd word`, [reserverd word] or {v 'reserved word'} is used to quote a column with reserved word name in SQL statement. The HXTT XML supports using DATE, TIME, TIMESTAMP, GROUP, ORDER, KEY, DESC, SEQUENCE, INCREMENT, MINVALUE, MAXVALUE, CACHE, CHECK, CYCLE, OTHER, SET, INT, UNIQUE, LEVEL, RELEASE, INDEX, TOP, PACK, CALL, CONNECT, START, PRIMARY, COMMENT, TRANSACTION, REFERENCES, OFFSET, LIMIT, and UPDATE directly in SQL, although they're reserved words too.

SELECT [ALL | DISTINCT [ ON ( expression [, ...] ) ] ] | DISTINCTROW [TOP n [PERCENT]] select_list [INTO variable [, ...] | INTO OUTFILE 'outfileName' ] FROM table_reference_list [WHERE condition_expression] [[START WITH initial-condition] CONNECT BY [NOCYCLE] recurse-condition] [group_by_clause] [HAVING condition_expression] [union_clause] [order_by_clause] [fetch_clause] [FOR UPDATE]

select_list: { expression [ [AS] columnAlias] | table.* | * } [,...]

table_reference_list: {table_reference | table_join} [,...]

table_reference: { { table_name | subquery | json_table(jsonContent,xpath) | xml_table(xmlContent,xpath) | (table_join) | (VALUES expression[, ...] ) AS tableName(columnName[,...])} [ [AS] tableAlias] } [pivot_clause] [unpivot_clause]

table_name: { [catalog.]tableName} | {UNC path}

table_join: table_reference join_clause [join_clause,...]

join_clause: [NATURAL] { INNER | { [ LEFT | RIGHT | FULL] [OUTER] } } JOIN table_reference [ ON condition_expression | USING(column1,column2,...) ]

condition_expression: an expression which should return a boolean value.

pivot_clause: PIVOT ( aggregate_function(value_column) FOR pivot_expr IN (columnValue_as_columnName_list) ) [ [AS] tableAlias ] . Pivot Samples, which includes dynamical column, ILIKE pattern, expression calculation.

unpivot_clause: UNPIVOT ( value_column FOR pivot_column IN (columnName_as_columnValue_list) ) [ [AS] tableAlias ] . Unpivot Samples, which includes dynamical column, ILIKE pattern, expression calculation.

group_by_clause: GROUP BY expression [,...] [WITH ROLLUP | CUBE]

union_clause: { UNION | INTERSECT | EXCEPT | MINUS } [ ALL ] select_statement [ union_clause ...]

order_by_clause: ORDER BY expression [ASC|DESC] [,...]

fetch_clause: [LIMIT [skipped_rows,] rows_count ] | [ [ OFFSET skipped_rows {ROW | ROWS} ] [ FETCH { FIRST | NEXT } rows_count { ROW | ROWS } ONLY ] ]

DISTINCT specifies that duplicate rows are discarded. A duplicate row is when each corresponding select_list column has the same value. DISTINCT has no effect on constant value, and pseudo columns(__rowid__).

expression: a complicated expression which can include parentheses, logical operator(NOT, AND, OR), positives/minus sign(+, -), arithmetical operator(+,-,*,/,%), string operator(|| (left string concat right string), +(left string concat right string), -(trim left string then concat rightstring), $(check whether left string is contained in right string), comparison operator(>, >=, =, ==, <=, <, !=, <>), bitwise logical operator(&, |, ~, ^, <<, >>), [NOT] LIKE pattern {escape 'escape_character'},[NOT] ILIKE pattern {escape 'escape_character'}, IS [NOT] NULL, BETWEEN ... AND ..., [NOT] IN, [NOT] EXISTS, [ALL|ANY|SOME] (subquery), [NOT] CASE WHEN expr THEN result [WHEN expr THEN result ...] [ELSE expr] END, CASE expr WHEN compare_expr THEN result [WHEN compare_expr THEN result ...] [ELSE result] END, SQL Escape Syntax({d 'yyyy-mm-dd'}, {t 'hh:mm:ss'}, {ts 'yyyy-mm-dd hh:mm:ss.f...'},{v 'reserved_word'}, {fn functionExpression}, {escape 'oneEcapeCharacter'}, {"varbinary" 'string'}), function(more than 200), aggregate function(MAX, MIN, AVG, COUNT, SUM, STD, STDDEV), constant(null, true, false, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, date, time, timestamp, number, string), column, parameter(?), subquery(single-row subquery, multirow subquery, multiple-column subquery, inline views, correlated subquery) and so on.

"SELECT select_list" can be used to get some calculated values through an one-row ResultSet. Column can be used in all sql except for "SELECT select_list". Parameter(?) can only be used in PreparedStatement.

For instance:

select val('123.222')
SELECT top 8 percent * FROM data.sz9010;
select distinct top 10 * from test where not deleted() order by int1,char1 desc;
select int1,float1 from test where int1>0 group by int1,double1;
select distinct on (int1) int1,double1 from test;
select sum(int1),max(dec1),min(double1) from test;
SELECT SUM(apmast.fnamount), SUM(glcshi.fnadjamt),SUM(glcshi.fncashamt),SUM(glcshi.fndiscount) FROM apmast, glcshi WHERE apmast.fcinvoice +apmast.fvendno = glcshi.fcinvoice + glcshi.fcnameid AND apmast.fduedate between {d '1999-01-01'} AND {d '1999-11-30'} AND apmast.finvdate <= {d '1999-11-30'};
SELECT cellID, columnID, reference, function, parameter FROM repLayout WHERE reportID = '1' AND __rowid_ = 0 ORDER BY columnID;
select distinct int1,double1 from test group by int1,double1,float1;
select distinct * from test where int1>0
select distinct int1,count(*),sum(int1) from brain.user group by int1
SELECT date1,time1,int1 FROM test where TIMESTAMPdIFF(SQL_TSI_YEAR,time1,{ts '3999-03-24 00:59:23.22222'})<-2000 and date1>{d '1900-01-01'} and date1>{d '1960-01-01'} and date1<{d '2000-01-02'}+20;
select int1 as a,c+23 as b,a+b as c from test where a=1;
SELECT INT1,FLOAT1,A.* FROM TEST A WHERE {fn abs(-TEST.INT1)}>0 or a.float1<0 order by int1 asc,currency1,double1*5+int1 desc;
select int1,count(*),sum(int1+count(*)),sum(int1)+int1 from test group by int1 having int1>10;
SELECT SCHOOLNUM, STULINK,CHGNUMBER, {v 'ABSEN$0101'}, {v 'ABSEN$0102'}, USERSTAMP, DATESTAMP, {v 'TIMESTAMP'},SEQUENCE FROM AATD2019 where {v 'ABSEN$0101'}='1234' ORDER BY SCHOOLNUM, STULINK, SEQUENCE;
select char1,char1 like 'Z%',char1 in('ZZAA','Z'),char1 between 'A' and 'ZZZ',char1 in('ZZAA','Z') or char1 between 'A' and 'Z',* from test where char1='Z';
select int1 from test where int1=(select distinct top 1 int1 from test where int1>0);
select int1 from test where int1 in(select int1 from test where not deleted());
select recno(),int1 from test where (recno(),int1) in(select top 2 recno(),int1 from test where int1>0);
select subquery.int1,recno('subquery') from (select top 2 recno(),int1 from test where int1>0) as subquery;
select subquery.int1,recno('test'),test.int1,recno('subquery') from (select top 2 recno(),int1 from test where int1>0) as subquery, test where test.int1=subquery.int1;
ELECT INT1 FROM test as a WHERE EXISTS(SELECT 1 FROM test WHERE int1 >0);
SELECT INT1 FROM test as a WHERE int1>=all(SELECT int1 FROM test);
SELECT INT1 FROM test as a WHERE int1>=any(SELECT int1 FROM test);
SELECT INT1 FROM test as a WHERE int1>=some(SELECT int1 FROM test);
select int1,recno() from test where (int1,recno())>(3,5);
select int1,recno() from test where (recno(),int1)=(6,222);
SELECT * FROM (SELECT * FROM test WHERE int1 = 222 ) as a WHERE EXISTS(SELECT 1 FROM test WHERE int1 >0);
select recno('a'),recno('b'),a.int1,a.char1,b.int1,b.char1 from test a, test as b where recno('a')=recno('b');
select a.int1,a.char1,b.int1,b.char1 from test a inner join test as b on a.int1=b.int1;
select a.int1,a.char1,b.int1,b.char1 from test a NATURAL inner join test as b on a.int1=b.int1;
select recno('a'),recno('b'),a.int1,a.char1,b.int1,b.char1 from test a left join test as b on a.int1=b.int1;
select a.int1,a.char1,b.int1,b.char1 from test a right join test as b on a.int1=b.int1;
select a.int1,a.char1,b.int1,b.char1 from test a full join test as b on a.int1=b.int1;
select recno('a'),recno('b'),a.int1,a.char1,b.int1,b.char1 from test a full join test as b on a.int1==b.int1 and recno('a')!=recno('b');
SELECT * FROM test a LEFT JOIN (test b JOIN test c ON (b.int1 = c.int1)) as d ON (a.int1 = d.int1);
SELECT * FROM test a,test b,test c WHERE a.int1 = b.int1 AND b.int1 = c.int1;
SELECT * FROM test a NATURAL CROSS JOIN test b CROSS JOIN test c WHERE a.int1 = b.int1 AND b.int1 = c.int1;
SELECT * FROM test a LEFT JOIN (test b JOIN test c ON (b.int1 = c.int1)) on recno('a')=recno(2);
SELECT int1 FROM test where int1>0 UNION ALL select int1 from test where int1>3000 order by int1 desc
SELECT int1,* FROM test where int1>0 UNION select int1,* from test where int1>3000 order by int1
ELECT int1,* FROM test where int1>0 INTERSECT all select int1,* from test where int1>3000 order by int1;
SELECT int1,* FROM test where int1>0 EXCEPT select int1,* from test where int1>3000 order by int1 descl
SELECT int1,* FROM test where int1>0 MINUS select int1,* from test where int1>3000 order by int1,double1 desc;
select double1,sum(double1),int1 from test where int1>0 group by int1 having sum(double1)>0 and double1>0;
select distinct 1,a.int1,sum(a.int1) from test as a,test as b group by a.int1,B.int1
select a.int1,a.char1,b.int1,b.char1 from test a NATURAL inner join test as b
select * from (select 'ab5' as a) where a like '%[a-c][^ac][12345]'
select lpad(' ',2*(level-1)) || str(child) s from test_connect_by start with parent=0 connect by prior child = parent;

Pivot Samples

pivot_clause: PIVOT ( aggregate_function(value_column) FOR pivot_expr IN (columnValue_as_columnName_list) ) [ [AS] tableAlias ]

HXTT XML PIVOT supports dynamical column, ILIKE pattern, and expression calculation

pivot_clause is complicated to understand, and HXTT XML will try to fix your sql so that you can get expected rersult. The following sql has different format but can get the similar result

LIKE pattern "%[a-c][^ac][12345]" can be used in column_list, and __value__, is a pseudo column as pivot value, which means column value.
With LIKE pattern and __value__, it's possible to produce any dynamic column name . For instance, quarter in ('%' as ’Q'+ __value__) . HXTT XML allows to pivot multi values into a column, which means that you can get an ARRAY column. For instance, quarter in ('%' as 'Q') .

create table if not exists pivotdemo2(id int,name varchar(20),quarter int,profile int);
insert into pivotdemo2 values(1,'John',1,1000);insert into pivotdemo2 values(1,'John',2,2000);insert into pivotdemo2 values(1,'John',3,4000);insert into pivotdemo2 values(1,'John',4,5000);insert into pivotdemo2 values(1,'John',4,5000);insert into pivotdemo2 values(2,'Jack',1,3000);insert into pivotdemo2 values(2,'Jack',2,3500);insert into pivotdemo2 values(2,'Jack',3,4200);insert into pivotdemo2 values(2,'Jack',4,5500);
select id,name, * from pivotdemo2 pivot (sum(profile)for quarter in (1,2,3,4));
select id,name, * from pivotdemo2 pivot (sum(profile)for quarter in ('%' as ’Q'+ __value__)) as pvt;
select id,name, [1] as "quarter1", [2] as "quarter2" from pivotdemo2 pivot (sum(profile)for quarter in (1,2)) as pvt;
select id,name, [1] as "quarter1", [2] as "quarter2" from pivotdemo2 pivot (sum(profile)for quarter in (1,2,3,4)) as pvt;
select id,name, [1] as "quarter1", [2] as "quarter2",[3] as "quarter3",[4] as "quarter4" from pivotdemo2 pivot (sum(profile)for quarter in (1,2,3,4)) as pvt;
select id,name, [1] as "quarter1", [2] as "quarter2",[3] as "quarter3",[4] as "quarter4" from pivotdemo2 pivot (sum(profile)for quarter in ([1],[2],[3],[4])) as pvt;
select id,name, * from pivotdemo2 pivot (sum(profile)for quarter in ('%' as 'Q')) as pvt;
select id,name, * from pivotdemo2 pivot (sum(profile)for quarter in ('%' as 'Q'+ __value__)) as pvt;
select id,name, * from pivotdemo2 pivot (sum(profile)for quarter in (1 as spring, '%' as 'Q'+ __value__)) as pvt;

CREATE TABLE if not exists PIVOT_TEST( EMP_NAME VARCHAR(10), RATE DECIMAL(8, 2) , HOURS DECIMAL(8, 2));
INSERT INTO PIVOT_TEST VALUES('Tom', 1.00, 1.5);INSERT INTO PIVOT_TEST VALUES('Tom', 1.33, 8);INSERT INTO PIVOT_TEST VALUES('Tom', 1.33, 1);INSERT INTO PIVOT_TEST VALUES('Jerry', 1.33, 8);INSERT INTO PIVOT_TEST VALUES('John', 1.00, 2);INSERT INTO PIVOT_TEST VALUES('John', 1.33, 1);INSERT INTO PIVOT_TEST VALUES('John', 1.33, 1.5);INSERT INTO PIVOT_TEST VALUES('John', 1.66, 4);
SELECT * FROM PIVOT_TEST PIVOT ( SUM(HOURS) FOR RATE IN ([1.00], [1.33], [1.66]) ) AS PVT;
SELECT EMP_NAME,* FROM PIVOT_TEST PIVOT ( SUM(HOURS) FOR RATE IN ([1.00], [1.33], [1.66]) ) AS PVT;
SELECT * FROM PIVOT_TEST PIVOT ( SUM(HOURS) FOR RATE IN ([1.00], [1.33], [1.66]) ) AS PVT GROUP BY EMP_NAME ORDER BY EMP_NAME;
SELECT EMP_NAME FROM PIVOT_TEST PIVOT ( SUM(HOURS) FOR RATE IN ([1.00], [1.33], [1.66]) ) AS PVT GROUP BY EMP_NAME ORDER BY EMP_NAME;
SELECT * FROM (SELECT EMP_NAME, 'Rate ' + RTRIM(RATE) AS RATE_NAME, SUM(HOURS ) AS SUM_HOURS FROM PIVOT_TEST GROUP BY EMP_NAME, 'Rate ' + RTRIM(RATE) ) AS X PIVOT ( SUM(SUM_HOURS) FOR RATE_NAME IN ([Rate 1.00], [Rate 1.33], [Rate 1.66]) ) AS PVT;

SELECT * FROM (SELECT EMP_NAME, 'Rate ' + RTRIM(RATE) AS RATE_NAME, SUM(HOURS ) AS SUM_HOURS FROM PIVOT_TEST GROUP BY EMP_NAME, 'Rate ' + RTRIM(RATE) ) AS X PIVOT ( SUM(SUM_HOURS) FOR RATE_NAME IN ([Rate 1.00], [Rate 1.33], [Rate 1.66]) ) AS PVT ORDER BY EMP_NAME;
SELECT EMP_NAME, 'Rate ' + RTRIM(RATE) AS RATE_NAME, SUM(HOURS ) AS SUM_HOURS FROM PIVOT_TEST GROUP BY EMP_NAME, 'Rate ' + RTRIM(RATE);
SELECT * FROM (SELECT EMP_NAME, 'Rate ' + RTRIM(RATE) AS RATE_NAME, SUM(HOURS ) AS SUM_HOURS FROM PIVOT_TEST GROUP BY EMP_NAME, 'Rate ' + RTRIM(RATE) ) AS X PIVOT ( SUM(SUM_HOURS) FOR RATE_NAME IN ([Rate 1.00], [Rate 1.33], [Rate 1.66]) ) AS PVT;

create table if not exists emp_phone(name varchar(50), type tinyint, phone varchar(50));
insert into emp_phone values('Tom', '1', '1234-5678');insert into emp_phone values('Tom', '2', '3219-6066');insert into emp_phone values('Tom', '3', '5365-9583');insert into emp_phone values('Jerry', '1', '6837-2745');insert into emp_phone values('Jerry', '3', '2649-5820');insert into emp_phone values('Mike', '1', '5838-9002');insert into emp_phone values('Mike', '2', '2749-5580');insert into emp_phone values('John', '2', '9876-3453');
select * from emp_phone pivot(max(phone) for type in (1 as home, 2 as office, 3 as mobile));
select * from emp_phone1 unpivot(phone for type in (home as 1, office as 2, mobile as 3));

Unpivot Samples

unpivot_clause: UNPIVOT ( value_column FOR pivot_column IN (columnName_as_columnValue_list) ) [ [AS] tableAlias ]

HXTT XML UNPIVOT supports dynamical column, ILIKE pattern, and expression calculation

LIKE pattern "%[a-c][^ac][12345]" can be used in column_list, and __name__, is a pseudo column as unpivot value, which means column name.
With LIKE pattern and __name__, it's possible to produce any dynamic value for any column name. For instance, "off%" as substring(__name__,2) .

create table if not exists emp_phonePVT select name,* from emp_phone pivot(max(phone) for type in (1 as home, 2 as office, 3 as mobile)) pvt; select * from emp_phonePVT; select name,phone,type from emp_phonePVT unpivot(phone for type in (home , office , mobile)); select name,phone,type from emp_phonePVT unpivot(phone for type in (home as 1, office as 2, mobile as 3)) pvt3; select * from emp_phonePVT unpivot(phone for type in (home , office , mobile)) pvt3; select * from emp_phonePVT unpivot(phone for type in (home as 1, office as 2, mobile as 3)) pvt3; select * from emp_phonePVT unpivot(phone for type in (home , "off%" , mobile)) pvt3; select * from emp_phonePVT unpivot(phone for type in (home , "off%" as substring(__name__,2), mobile)) pvt3;

INSERT INTO table_name [ [AS] tableAlias] [ ( column_identifier [,...] ) ] { VALUES ( expression [, ...] ) | VALUES expression [, ...] | VALUES ( expression [, ...] ),... | SELECT query | ? }

column_identifier = columnName | "reserved_word" | {v 'reserved_word'}

Adds one or more new rows of data into a table. SQL does't permit that table1 is the same table as table2 when INSERT INTO table1 select * from table2, but the HXTT XML support such an unadvisable operation, for example, INSERT INTO test (INT1,DATE1) select distinct int1,date1 from test.

For instance:

INSERT INTO test (INT1,dec1,time1) VALUES(-1999,-222.33333,{ts '1333-11-30 22:22:22.999999999'});
INSERT INTO test ("INT1","DATE1") VALUES(1999.0111,{d '1996-10-21'});
INSERT INTO test ("INT1","DATE1") VALUES(1999.0111,{d '1996-10-21'}),(333,{d '2006-10-21'});
INSERT INTO test ("INT1") VALUES 1999.0111,333;
insert into ecode values('Maciej', 'Kowalski');
insert into test values (reccount()+1,'abc',date(),{ts '2003-12-18 19:42:17.88'});
INSERT INTO AATD2019 ({v 'ABSEN$0101'}) values('1234');
insert into test select * from test order by int1 asc;
insert into test select * from test order by int1 asc;

UPDATE table_name [ [AS] tableAlias] SET [column_identifier | (column_identifier,...) ] = expression [,...] [WHERE condition_expression]
UPDATE table_reference_list SET [column_identifier | (column_identifier,...) ] = expression [,...] [WHERE condition_expression]

For instance:

update order set buyer=(SELECT name FROM user WHERE user.id = order.buyerid);
update test set int1=null where SequenceID=26;
update test set INT1=323232,DEC1=-DEC1 where FLOAT1=3.00 and INT1=222 and DEC1=3.00 and DOUBLE1=34.0 and TIME1 is NULL and CHAR1='ZZAA' and CURRENCY1=0 and BOOLEAN1 is NULL
update AATD2019 set {v 'ABSEN$0101'}='1234' where SequenceID=1;
update test set int1=3333555 where exists(SELECT 1 FROM test WHERE int1 = 222 ) and SequenceID=3;
UPDATE "UIP_Target" SET ("key_string","key_int","data_string","data_int","data_decimal" ) = (SELECT COALESCE(DP3475."key_string", "UIP_Target"."key_string"),COALESCE(DP3475."key_int", "UIP_Target"."key_int"),COALESCE(DP3475."data_string", "UIP_Target"."data_string"),COALESCE(DP3475."data_int", "UIP_Target"."data_int"),COALESCE(DP3475."data_decimal", "UIP_Target"."data_decimal") FROM DP3475 WHERE "UIP_Target"."key_string"=DP3475."KEY208" AND "UIP_Target"."key_int"=DP3475."KEY572");
UPDATE "UIP_Target" SET ("key_string","key_int","data_string","data_int","data_decimal" ) = (SELECT COALESCE(DP3475."key_string", "UIP_Target"."key_string"),COALESCE(DP3475."key_int", "UIP_Target"."key_int"),COALESCE(DP3475."data_string", "UIP_Target"."data_string"),COALESCE(DP3475."data_int", "UIP_Target"."data_int"),COALESCE(DP3475."data_decimal", "UIP_Target"."data_decimal") FROM DP3475 WHERE "UIP_Target"."key_string"=DP3475."KEY208" AND "UIP_Target"."key_int"=DP3475."KEY572") WHERE EXISTS (SELECT 'X' FROM DP3475 WHERE "UIP_Target"."key_string"=DP3475."KEY208" AND "UIP_Target"."key_int"=DP3475."KEY572")
UPDATE "UIP_Target" SET ("data_string","data_int","data_decimal" ) = (date(),789,44.5) WHERE EXISTS (SELECT 'X' FROM DP3475 WHERE "UIP_Target"."key_string"=DP3475."KEY208" AND "UIP_Target"."key_int"=DP3475."KEY572")

DELETE FROM table_name [ [AS] tableAlias] [WHERE condition_expression]

Removes rows in a table according to condition_expression.

For instance:

delete from test where SequenceID=4;

CREATE CATALOG [IF NOT EXISTS] catalogName

Create a subdirectory to contain database files.

For instance:

create catalog if not exists data222;

CREATE DATABASE [IF NOT EXISTS] databaseName

Create a new XML document, the URL format must be assigned as a directory, for example,"jdbc:xml:////usr/data"

create database if not exists testxml;

DROP DATABASE [IF EXISTS] databaseName

The DROP DATABASE statement is used to delete a database. IF that database doesn't exist without using IF EXIST, an SQLException will be thrown.

FLUSH DATABASE [IF EXISTS] databaseName

Flush all cached modications to database file.

For instance:

flush DATABASE db1;

COPY DATABASE [IF EXISTS] sourceDatabase TO destinationDatabase

Create a destination database, and copy all tables from source database.

For instance:

copy DATABASE db1 to db2;

CLONE DATABASE [IF NOT EXISTS] destinationDatabase FROM sourceDatabase

Create a destination database, and copy all tables from source database.

For instance:

clone DATABASE db4 from db3;

CREATE TABLE [IF NOT EXISTS] table_name [(column_identifier data_type [constraint] [,...] [, constraint_clause [,...] ] )] [ [AS] SELECT query | ? ]

data_type: CHAR(n) | CHARACTER(n) | VARCHAR[(n)] | BINARY (n) | VARBINARY (n) | NUMERIC(n1[,n2]) | DEC[IMAL](n1[,n2]) | INT[EGER] [ AUTO_INCREMENT] | SMALLINT | FLOAT [(n)] | REAL | DOUBLE | BIT | BOOLEAN | DATE [(dateFormat)] | TIME [(dateFormat)] | TIMESTAMP [(dateFormat)] | LONGVARCHAR [(n)] | LONGVARBINARY [(n)] | JAVA_OBJECT [(n)] | CLOB | BLOB| OTHER(type_name [,n]) | NUMBER[(n1[,n2])]

n, n1,n2: positive integer, n2 can be 0

constraint: [NULL| NOT NULL] [UNIQUE] [DEFAULT expression] [PRIMARY KEY] [COMMENT 'string']

constraint_clause: [ CONSTRAINT constraint_name ] PRIMARY KEY (column1, column2, . column_n) | CONSTRAINT constraint_name FOREIGN KEY (column1, column2, ... column_n) REFERENCES parent_table (column1, column2, ... column_n) [ON DELETE CASCADE] [ON UPDATE CASCADE] | [ CONSTRAINT constraint_name ] UNIQUE (column1, column2, . column_n) | CONSTRAINT constraint_name CHECK (column_name condition)

Temporary table is only visible in the current connection.

For instance:

create table testxml.abc (efg varchar(10));

DROP TABLE [IF EXISTS] table_name

Removes a table, and its indexes from the database. IF that table doesn't exist without using IF EXIST, an SQLException will be thrown.

If the root element have been dropped, then that XML file will be removed.

For instance:

drop table if exists states;

ALTER TABLE [IF EXISTS] table_name alter_specification [,...]

alter_specification: {{ADD|MODIFY} column_identifier data_type [constraint]}| DROP column_identifier | RENAME column_identifier 1 TO column_identifier 2 | RENAME TO table_name2 | ADD constraint_clause

When some alter operations are in one ALTER sql, the HXTT XML will complete all RENAME column operations first, then do all ADD, MODIFY, AND DROP column operations at one time, and RENAME table is the last operation.

For instance:

alter table test rename int11 to int1;
alter table test rename int1 to int2, rename to test22;
alter table test22 rename to test;
alter table test add column1 int DEFAULT 3 NULL, drop clob1, modify double1 int;

TRUNCATE TABLE [IF EXISTS] table_name

Remove all table rows.

For instance:

truncate table test;

RENAME TABLE table_name TO table_name2

Rename the table.

For instance:

RENAME table test to test1;

LOCK TABLE table_name

lock the table. Returns 1 if sucess, 0 if failed to lock a table.

For instance:

lock table test;

UNLOCK TABLE table_name

unlock the table. Returns 1 if sucess, 0 if failed to unlock a table.

For instance:

unlock table test;

CREATE [OR REPLACE] [ TEMP | TEMPORARY ] VIEW table [ ( column_identifier [,...] ) ] [AS ] SELECT query

Creates a virtual table whose contents (columns and rows) are defined by a query. Temporary view is only visible in the current connection.

For instance:

create or replace temporary view abcv (aaa) select FEC_ANAL FROM ANALISIS;

DROP VIEW [IF EXISTS] table

Drop a virtual table.

For instance:

drop view if exists abcv;

CREATE SEQUENCE [IF NOT EXISTS] sequence_name [AS {INT|SMALLINT|TINYINT|BIGINT}] [START [WITH] n] [INCREMENT [BY] n] [MINVALUE n | NO MINVALUE] [ MAXVALUE n | NO MAXVALUE ] [ CACHE n | NO CACHE] [ [ NO ] CYCLE ]

sequence_name: [catalog.]sequenceName

The optional clause START WITH n allows the sequence to begin anywhere. The default starting value is minvalue for ascending sequences and maxvalue for descending ones. The optional clause INCREMENT BY n specifies which value is added to the current sequence value to create a new value. A positive value will make an ascending sequence, a negative one a descending sequence. The default value is 1. The optional clause MINVALUE n determines the minimum value a sequence can generate. If this clause is not supplied or NO MINVALUE is specified, then defaults will be used. The defaults are 1 and -128(-32768,0x80000000,0x8000000000000000L) for ascending and descending sequences, respectively. The optional clause MAXVALUE n determines the maximum value for the sequence. If this clause is not supplied or NO MAXVALUE is specified, then default values will be used. The defaults are 127(32767,0x7fffffff,0x7fffffffffffffffL)and -1 for ascending and descending sequences, respectively. The optional clause CACHE cache specifies how many sequence numbers are to be preallocated and stored in memory for faster access. The minimum value is 1 (only one value can be generated at a time, i.e., no cache), and this is also the default. The maximum value for cache is 65535. The CYCLE option allows the sequence to wrap around when the maxvalue or minvalue has been reached by an ascending or descending sequence respectively. If the limit is reached, the next number generated will be the minvalue or maxvalue, respectively. If NO CYCLE is specified, any calls to nextval after the sequence has reached its maximum value will throw an exception. If neither CYCLE or NO CYCLE are specified, NO CYCLE is the default.

For instance:

create sequence if not exists userID start WITH 100 increment by 2 maxvalue 2000 cache 5 cycle;

DROP SEQUENCE [IF EXISTS] sequence_name

Removes a sequence from the database. IF that sequence doesn't exist without using IF EXIST, an SQLException will be thrown.

For instance:

drop sequence if exists userID;

ALTER SEQUENCE sequence_name [AS {INT|SMALLINT|TINYINT|BIGINT}] [RESTART [WITH] n] [INCREMENT [BY] n] [MINVALUE n | NO MINVALUE] [ MAXVALUE n | NO MAXVALUE ] [ CACHE n | NO CACHE] [ [ NO ] CYCLE ]

ALTER SEQUENCE changes the parameters of an existing sequence generator. Any parameter not specifically set in the ALTER SEQUENCE command retains its prior setting.

For instance:

alter sequence userID restart WITH 100 increment by 1 maxvalue 5000;

SET TRANSACTION transaction_mode [, ...]

transaction_mode: { ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } | { READ WRITE | READ ONLY }

Sets the transaction characteristics of the current transaction. It effects any subsequent transactions in the same connection. java.sql.Connection.setTransactionIsolation(int level) and java.sql.Connection.setReadOnly(boolean readOnly) can do the same task.

For instance:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION [ transaction_mode [, ...] ]

Begins a new transaction block. java.sql.Connection.setAutoCommit(false), java.sql.Connection.setTransactionIsolation(int level), and java.sql.Connection.setReadOnly(boolean readOnly) can do the same task.

For instance:

START TRANSACTION;

COMMIT [WORK]

Terminates the current transaction and makes all changes under the transaction persistent. It commits the changes to the database. java.sql.Connection.commit() can do the same task.

For instance:

commit;

ROLLBACK [WORK] [ TO [ SAVEPOINT ] savepoint_name]

Without savepoint_name, terminates the current transaction and rescinds all changes made under the transaction. It rolls back the changes to the database. With savepoint_name, rolls back all commands that were executed after the savepoint was established. java.sql.Connection.rollback() can do the same task of ROLLBACK [WORK] sql.

For instance:

rollback;

SAVEPOINT savepoint_name

SAVEPOINT establishes a new savepoint within the current transaction. java.sql.Connection.setSavepoint(String name) and java.sql.Connection.setSavepoint() can do the same task.

For instance:

savepoint t1;

RELEASE SAVEPOINT savepoint_name

Destroys a savepoint previously defined in the current transaction. java.sql.Connection.releaseSavepoint(Savepoint savepoint) can do the same task.

For instance:

release savepoint t1;

{ [ ? = ] call procedure_name [ ( ? [, ? [ , ... ]] ) ] }

java.sql.CallableStatement can be used to call stored procedure.

For instance:

{call myview(?)};

EXPLAIN [SQL] anySQL

EXPLAIN SQL anySQL provides SQL syntax analysis feature like INFORMATION_PARSER.sql table

For instance:

explain sql select RECNO(),vrrdnr,Vrrdvnr,STR(vrrdnr),STR(Vrrdvnr,2),STR(vrrdnr)+STR(Vrrdvnr,2),* from Voorraad where STR(vrrdnr)+STR(Vrrdvnr,2)='4567812';

EXPLAIN anySQL shows how the tables involved in the statement will be scanned by index scanned or sequential scan.

For instance:

explain select RECNO(),vrrdnr,Vrrdvnr,STR(vrrdnr),STR(Vrrdvnr,2),STR(vrrdnr)+STR(Vrrdvnr,2),* from Voorraad where STR(vrrdnr)+STR(Vrrdvnr,2)=' 4567812';

Pseudo Tables

The INFORMATION_SCHEMA implementation include SCHEMATA, TABLES, COLUMNS, and DATABASES. For instance,

SELECT TABLE_SCHEM FROM INFORMATION_SCHEMA.SCHEMATA;
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='TABLE';
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='TABLE' AND TABLE_CAT='aDatabaseFileName';;
SELECT column_name,data_type,column_default,is_nullable FROM information_schema.tables AS t JOIN information_schema.columns AS c ON t.table_catalog=c.table_catalog AND t.table_schem=c.table_schem AND t.table_name=c.table_name WHERE t.table_name='TABLE-NAME';
SELECT column_name, data_type, character_maximum_length, numeric_precision, column_default, is_nullable FROM information_schema.tables as t JOIN information_schema.columns AS c ON t.table_catalog=c.table_catalog AND t.table_schem=c.table_schem AND t.table_name=c.table_name WHERE c.table_schema='TABLE-SCHEMA' AND c.table_name='TABLE-NAME';

The INFORMATION_PARSER.sql provides SQL syntax analysis feature. For instance,

            sql =  "SELECT * FROM INFORMATION_PARSER.sql WHERE SQL_COMMAND = ? and OBJECT_TYPE='TABLE'";
            sql =  "SELECT SQL_TYPE,OBJECT_TYPE,OBJECT_NAME FROM  INFORMATION_PARSER.SQL WHERE SQL_COMMAND= ?";


            pstmt = con.prepareStatement(sql);

             pstmt.setString(1, "select * from a.test out; SELECT efg,3+2 as dd FROM ABC;update a set a=3;");
      //      pstmt.setString(1, "select EFG from a.test, test2 where test.b=test2.c");
//            pstmt.setString(1, "select EFG from (select * from abc);select EFG from (select * from abc) as o;");
    //        pstmt.setString(1, "select EFG from a.test left join test2 where test.b=test2.c; SELECT A FROM B UNION SELECT C FROM D;");
            ResultSet rs = pstmt.executeQuery();

Pseudo Columns

__children__: used to quote all __children__ of a XML object for import/export
__child__N(n is an int object number from 1 to n): is a pseudo column name to access a child of a XML object.
__self__: used to quote the XML object.
__type__: used to quote the name of XML object. It'll be automatically invisible if there's no name.
__value__: used to quote the value of XML object. It'll be automatically invisible if XML found sub elements.
For instance, select array_ndims(__children__),__children__,__children__[1],__child__1 from books."/bookstore/book[price>30]/title";

__rowid__, is a pseudo column as primary key. A __rowid__ identifies a row in a table.

__values__, is a pseudo read-only column for all values in a data row.

level is a pseudo column that can be used in hierarchical queries (start with .. connect by). For records that appear in the root, level is 1, for their (direct) __children__, level is 2 and so on.
select *,level from hierarchy START WITH id=1 CONNECT BY prior id=parentId having level<=3 order by parentId;

Pseudo Variable

CURRENT_DATE is a pseudo variable which returns the current date.

CURRENT_TIME is a pseudo variable which returns the current time.

CURRENT_TIMESTAMP is a pseudo variable which returns the current timestamp.

_CURRENT_ is a pseudo catalog(or schema name) which returns the current catalog (or schema).

Split Multivalue Column Into Rows

If each row has multiple multi value columns, a special subquery table can be used in special join sql. For instance,

select User,Role from aTable,(select split(aTable.Roles,',') as Role) AS bTable;

select User,Role,Year from aTable,(select split(aTable.Roles,',') as Role,split(aTable.Years,',') as Year) AS bTable;

DECLARE variable_name[,...] type [DEFAULT expression]
DECLARE var_name[,...] 'jsonText'
DECLARE table_variable_name TABLE [(column_identifier data_type [constraint] [,...] [, constraint_clause [,...] ] )]
DECLARE TABLE table_variable_name [(column_identifier data_type [constraint] [,...] [, constraint_clause [,...] ] )]

Variable is visiable only in the same connection. @local_variable is a preferable format to avoid name conflict.
A table variable object is in memory for currection connection, and can be dropped by DROP TABLE.

For instance:

DECLARE abc CHAR(20) DEFAULT 'Hello';
DECLARE x, y INT;
DECLARE _Page_Demo '{"size":"A4","rotate":0}';
select _Page_Demo; select _Page_Demo->'size';

SET variable_name[.propertyName] = expression [,...]

expression can be a complicated expresion. BTW, INTO variable[,...] clause of SELECT syntax can set selected columns directly into variables.

For instance:

SET x = 1+int(55.5),y=2;
SELECT name,id INTO x,y FROM table1 WHERE id=33;
SELECT date(),pi() INTO x,y;
SELECT id,data INTO x,y FROM test.t1 LIMIT 1;
set _Page_Demo->'size'='A2';

Common Table Expression

WITH <common_table_expression> [ ,...n ] ]   
<common_table_expression>::=  
    expression_name [ ( column_name [ ,...n ] ) ]  
    AS  
    ( CTE_query_definition )      

Common table expression (CTE) is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or CREATE VIEW statement. A common table expression can include references to itself. This is referred to as a recursive common table expression, which is similar to CONNECT BY clause. Having clause with Level (a pseudo column) can be used to limit recursive depth.

For instance:

    
DECLARE table hierarchy(ID  int not null ,ParentID int not null,name varchar(100) not null);
insert into hierarchy values(1,0,'a1'),(2,1,'b2'),(3,2,'c3'),(4,3,'d4'),(12,3,'d12'),(13,3,'d13'),(5,1,'b5'),(6,5,'c6'),(7,6,'d7'),(15,1,'b15'),(16,15,'c16'),(17,1,'b17'),(18,17,'c18');
select recno(),* from hierarchy ;

select * from hierarchy order by ParentID;

select * from hierarchy where ParentID=1;

select *,level from hierarchy START WITH id=1 CONNECT BY prior id=parentId having level<=3  order by parentId;

select * from hierarchy START WITH id=1 CONNECT BY prior id=parentId;

select * from hierarchy START WITH id=1 CONNECT BY prior id=parentId;

select *,level from hierarchy START WITH id=1 CONNECT BY prior id=parentId having level<=3  order by parentId;

with cte(Id,ParentID,Name) as
(
select * 
from hierarchy 
where id=1

union all
select h.* 
from hierarchy h
inner join cte c on h.ParentID=c.id 
)
select *
from cte
order by ParentID;

with cte(Id,ParentID,Name) as(select * from hierarchy where id=1 union all select h.* from hierarchy h inner join cte c on h.ParentID=c.id) select * from cte order by ParentID;

with cte(Id,ParentID,Name) as(select *,level from hierarchy where id=1 union all select h.* from hierarchy h inner join cte c on h.ParentID=c.id having level<=3 ) select * from cte order by ParentID;


DECLARE @t1 TABLE (itmID INT, itmIDComp INT);  
INSERT INTO @t1 VALUES (1,10), (2,10);   
SELECT * FROM @T1;

select * FROM (SELECT 1 as itmID UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t ;


DECLARE @t2 TABLE (itmID INT, itmIDComp INT);   
INSERT INTO @t2 VALUES (3,10), (4,10);   

WITH vw AS   (      SELECT itmIDComp, itmID      FROM @t1      UNION ALL      SELECT itmIDComp, itmID      FROM @t2  )  SELECT * FROM vw;  

WITH vw (a,b) AS   (      SELECT itmIDComp, itmID      FROM @t1      UNION ALL      SELECT itmIDComp, itmID      FROM @t2  )  SELECT a,b FROM vw;  

WITH r AS   (      SELECT t.itmID AS itmIDComp             , NULL AS itmID            ,0 AS N             ,1 AS Lvl      FROM (SELECT 1 as itmID UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t  )   SELECT itmIDComp,Lvl, N FROM r;  

WITH vw AS   (      SELECT itmIDComp, itmID      FROM @t1      UNION ALL      SELECT itmIDComp, itmID      FROM @t2  )   ,r AS   (      SELECT t.itmID AS itmIDComp             , NULL AS itmID            ,0 AS N             ,1 AS Lvl      FROM (SELECT 1 as itmID UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t  )   SELECT Lvl, N FROM r;  


declare table example (user_id int, application varchar(1),content longvarchar);
insert into example values (1,'A','abc'),(2,'B','efg'), (1,'B','CCC');

with
alias1 as (select * from example where application = 'A'),
alias2 as (select * from example where application = 'B')
select *
from alias1, alias2
where alias1.user_id = alias2.user_id;

with
alias1 as (select * from example where application = 'A'),
alias2 as (select * from example where application = 'B')
select *
from alias1 join alias2 on alias1.user_id = alias2.user_id;



with
alias1 as (select * from example where application = 'A'),
alias2 as (select * from example where application = 'B')
select *
from alias1 as a1, alias2 as a2
where a1.user_id = a2.user_id;

Comment Syntax

#one-line comment
--one-line comment
/*multiline comment*/

For instance:

select * /* column list */ from test;#This is a select sql.

SQL States