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. PACK 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 Word 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, 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] [LIMIT [offset,] [limit] ] [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] [,...]

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 sz9010;
select *,__children__ from testword.pictures;
select *,__children__ from testdoc.vtable1;
select *,__children__ from testdoc.vtable2;
select *,__children__ from testdoc.celltable1;
select *,__children__ from testdoc.celltable1 where __rowid_=1146756269056;
select *,__children__ from testdoc.table1;
select *,__children__ from testdoc.vtable1;
select *,__children__ from testdoc.htable1;
select *,__children__ from testdoc.celltable1;
select * from testdoc.properties;
select *,__children__,objectNo,objectType from testdoc.tables;
select *,__children__ from testdoc.vtable1;
select *,__children__ from testdoc.htable2;
select *,__children__ from testdoc.celltable3;
select *,__children__ from testdoc;
select *,__children__ from testdoc.paragraphs;
select *,__children__ from testdoc.paragraph1;
select *,__children__ from testdoc.headers;
select *,__children__ from testdoc.footers;
select *,__children__ from testdoc.comments;
select *,__children__ from testdoc.tables;
select *,__children__ from testdoc.pictures;
select *,__children__ from testdoc.hyperlinks;
select * from testdoc.comments;

Pivot Samples

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

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

pivot_clause is complicated to understand, and HXTT Word 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 Word 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 Word 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'}

For instance:

insert into testword.paragraph1 (text) values('A demo line');
insert into testword.paragraph1 (text) values('New demo line for paragraph1'+RAND());
insert into testword.paragraph2 (text) values('demo line for paragraph2');
insert into testword (text) values('demo new paragraph');
insert into he.table1 (__child__1,__child__2) select __children__ as __child__1,'Another demo' as __child__2 from testdoc.tables where objectno=1;'/*Warning: Clone a table into a cell*/
insert into he.table2 (__child__1,__child__2) select __children__ as __child__1,'Another demo' as __child__2 from testdoc.table1';/*Warning: Clone a table row into a cell*/
create table if not exists he.paragraphs select 'Warning: Clone a table into a cell: insert into he.table1 (__child__1,__child__2) select __children__ as __child__1,''A nother demo'' as __child__2 from testdoc.tables where objectno=1;'; insert into he.table1 (__child__1,__child__2) select __children__ as __child__1,'Another demo' as __child__2 from testdoc.tables where objectno=1;' create table if not exists he.paragraphs select 'Warning: Clone a table row into a cell: insert into he.table2 (__child__1,__child__2) select __children__ as __child__1,''A nother demo'' as __child__2 from testdoc.table1'; insert into he.table2 (__child__1,__child__2) select __children__ as __child__1,'Another demo' as __child__2 from testdoc.table1' insert into testdoc.comments (__rowid_,text) select __rowid_, 'A demo comment ' from testword.paragraph2;/* will append a comment for paragraph2 */ insert into testdoc.comments (author,text) select 'Tom Cat' , 'HXTT Word demo comment' ;/* will append a comment after the end */ insert into _memory_.testword.paragraphs (text) values('A demo line');
insert into _memory_.testword.paragraph2 (text) values(' append some demo text for paragraph2');
insert into _memory_.testword.paragraph3 (text) values(' paragraph3');
create table if not exists testdoc.hyperlink1 (url varchar default 'http://www.hxtt.com/',text varchar default 'A demo text');/* will create a hyperlink if there's no hyperlink */ insert into testdoc.hyperlinks (__rowid_,url,text) select __rowid_, 'http://www.hxtt.com/','A demo text' from testword.paragraph2;/* will append a hyperlink for paragraph2 */
insert into testdoc.hyperlinks (url,text)select 'http://www.hxtt.com/word.html' , 'HXTT Word' ;/* will append a hyperlink after the end */

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 testword.paragraphs set __self__->'comment'='A comment' where recno()=2;
update testword.paragraph1 set __self__->'color'='FF00FF';
update _memory_.testword.paragraphs set text=replace(text,'demo','hello') where recno()=2;
update testword.paragraphs set text=replace(text,'demo','hello') where recno()=2;
update testword.paragraphs set text=replace(text,'demo','hello') where recno()=5;
create table if not exists testword.header1 (text varchar default 'A header demo');
update testword.header1 set text ='A header demo2';
create table if not exists testword.footer1 (text varchar );
update testword.footer1 set text ='A footer demo2';
update testdoc.comments set text='A demo comment' where recno()=3;

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

Removes rows in a table according to condition_expression.
DELETE sql is used to set text or MS Word objects(table, paragraph, comment, picture, and so on).

For instance:

delete from testword.paragraph3; /* become a blank paragraph */ delete from testword.vtable1; /* become a blank table but leave the first line */

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 MS Word document, the URL format must be assigned as a directory, for example,"jdbc:word:////usr/data"

create database if not exists testword;
create database if not exists _memory_.testword;

DROP DATABASE [IF EXISTS] compressed-file-name

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.

Table name is using to create MS Word object.

For instance:

Table name is using to create MS Word object.

To create a MS Word document or insert MS Word objects from another MS Word document.
        create table if not exists aDemoFile select __children__ from testdoc;/* copy all objects from testdoc file*/
        create table if not exists he select __children__ from testdoc.comments;/*copy only comments*/
        create table if not exists he select __children__ from testdoc.headers;/* copy header */
        create table if not exists he select __children__ from testdoc.hyperlinks;/* copy all hyperlinks from testdoc file*/

To create a paragraph.
        create table if not exists he.paragraphs select 'That text content will be append as a new paragraph';
        create table if not exists testword.paragraph1 (nothing int);
        create table if not exists _memory_.testword.paragraph1 (text longvarchar) select 'abcefg';
        create table if not exists testword.paragraph1 (size varchar(2) default 'A5' );
        create table if not exists testword.paragraph2 select 'A demo line for create paragraph2' as text;
        create table if not exists testword.paragraph3 (text longvarchar) select 'A demo line for create paragraph3';
        create table if not exists testword.paragraph4 (text longvarchar) select 'A demo line for create paragraph4';

For MS Word table, you can use tables or tableN format to create table or insert table rows. vable means vertical table, htable means horizontal table, and celltable means every cell as a data row.
        create table if not exists he.celltable1 select __children__ from testdoc.celltable1;
        create table if not exists he.htable1 select __children__ from testdoc.htable1;
        create table if not exists he.vtable2 select __children__ from testdoc.vtable1;
        create table if not exists he.celltable3 select __children__ from testdoc.celltable1;
        create table if not exists he.vtable1 select __children__ from he2.vtable1;/*It will create the first table in he.docx file, append all data rows, except the header row, from the first vertical table */
        create table if not exists testword.vtable1 (size varchar(5) default '4x3', alignment varchar default 'center',name varchar(5),phone int,age int);/* table size rows,cols: nxn or n,n *//* table alignment: center,left,right,both *//* table verticalAlignment: center,left,right,both */

        create table if not exists testword.htable2 (size varchar(5) default '3x6', name varchar(5),phone int,age int);
        create table if not exists testword.celltable3 (name varchar(5) default 'Tom Cat',phone int,age int,sex boolean);         create table if not exists testword.table1 (size varchar default '3x5');         create table if not exists testword.table2 (product varchar, amount int);         create table if not exists he.vtables select __children__ from testdoc.vtable1;/* Clone a vertical table*/
        create table if not exists he.celltables select __children__ from testdoc.celltable1;/*'Clone a cell table*/
        create table if not exists he.vtable2 select __children__ from testdoc.vtable1;/*Clone or append text into vertical table2*/
        create table if not exists he.htables select __children__ from testdoc.htable1;/*Clone a horizontal table*/
        create table if not exists he.vtables select __children__ from testdoc.htable1;/*Rotate text to create a vertical table*/
        create table if not exists he.htable3 select __children__ from testdoc.vtable1;/*Clone or append rotated text into a horizontal table 3*/
        create table if not exists he.tables select date() as __child__1,__children__ as __child__2 from testdoc.tables where objectno=1;/*Warning: Clone a table into a cell*/
        create table if not exists he.tables select date() as __child__1,__children__ as __child__2 from testdoc.table1;/*Warning: Clone a table row into a cell*/
        create table if not exists he.tables select date() as __child__1,__children__ as __child__2 from testdoc.table1;/* complicate sqyl: to create a table, which will contain two column, but the second cell will contain another table from testdoc file*/
        create table if not exists testword.celltable1 (size varchar(5) default '3x6', name varchar(5),phone int,age int);/*Create a table 3(row)x6(column), use name,'',phone,'',age,'' to set the first row*/
create table if not exists testword.celltable1 (name varchar(5),phone int,age int);

CREATE TABLe sql can create header, footer, picture, or hyperlink too.
        create table if not exists testword.header1 (text varchar default 'A header demo');
        create table if not exists testword.footer1 (text varchar );
/* picture size: nxn width,height */         create table if not exists testword.picture1 (fileName varchar default 'icon.gif', size varchar(5) default '128x128');
        create table if not exists testdoc.hyperlink1 (url varchar default 'http://www.hxtt.com/',text varchar default 'A demo text');/* will create a hyperlink if there's no hyperlink */

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.

It can remove all kinds of MS Word object.

For instance:

drop table if exists testword.paragraph4;

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

For instance:

alter table testword modify size varchar default 'A4'; alter table _memory_.testword.paragraph3 modify alignment varchar default 'CENTER'; alter table testword.paragraph3 modify size varchar default 'A4';

TRUNCATE TABLE [IF EXISTS] table_name

Remove all table rows.

For instance:

truncate table _memory_.testword.paragraph2;/* become a blank paragraph */ truncate table _memory_.testword.paragraphs;/*clear all paragraphs*/

PACK TABLE [IF EXISTS] table_name

LOCK TABLE table_name

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

For instance:

lock table table2;

UNLOCK TABLE table_name

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

For instance:

unlock table table2;

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

You can use tables(__child__1, __child__2,... as column name), vtables(vertical table, first row as column names), htable(horizontal table, first column as column names),celltable( cell table, every cell become a data row). "select *,text from testpdf.vtable1;" will return all text content information in the first table file.
You can use
update testpdf.vtable1 set __child__1=?,__child__2= where __child__1=? and recno()=?; to set any content
or
create table if not exist testdoc.vtable1 select __children__ from anotherfile.vtable1;
to a table from another document.

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

text: used to get the main text content of Word object
__children__: used to quote all __children__ of a Word 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 Word object.
__self__: used to quote the MS Word object.
__rowId__: used to quote a speicial row in an object table.
objectNo: the object number in an object table, which can be same for different object type.
objectType: the object type of a MS Word object
columNo, rowNo: is useful only for celltable object.
url: is for hyperlink object
author: is for comment object
fileName: is for picture object
For instance, update testword.paragraph1 set __self__->'color'='FF00FF';
select __rowid_,text,array_ndims(__children__),__children__,__children__[1]->'color',__children__[1]#>'text' from testword.paragraph1 where array_ndims(__children__)>0;

__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).

CURRENT_Page is a pseudo variable which used to set page size.
Size: A0, A1, A2, A3, A4, A5, A6, Legal, Letter, nxn (for instance, 8.5x1.4 means 8.5" x 14")
Rotate integer (Optional; inheritable) The number of degrees by which the page shall be rotated clockwise when displayed or printed. The value shall be a multiple of 90. Default value: 0.
head,foot,leftMargin,rightMargin: use to define page margin

select CURRENT_Page;
DECLARE _Page_Demo '{"size":"A4"}'; select _Page_Demo,_Page_Demo->'size'; set _Page_Demo->'size'='A2'; select _Page_Demo; set CURRENT_Page=_Page_Demo; select CURRENT_Page; /* head,foot,leftMargin,rightMargin */ set CURRENT_Page->'header'=32; DECLARE CURRENT_Page '{"size":"A4","rotate":0}'; /*size: A0, A1, A2, A3, A4, A5, A6, Legal, Letter, nxn (for instance, 8.5x1.4 means 8.5" x 14")*/ /* Rotate integer (Optional; inheritable) The number of degrees by which the paragraph shall be rotated clockwise when displayed or printed. The value shall be a multiple of 90. Default value: 0.*/ select CURRENT_Page; set CURRENT_Page->'size'='A6'; select CURRENT_Page;

CURRENT_Font is a pseudo variable which used to set font information for current text operation
Name: Courier, Courier-Bold, Courier-BoldOblique, Courier-Oblique, Helvetica, Helvetica-Bold, Helvetica-BoldOblique, Helvetica-Oblique,Times-Roman, Times-Bold, Times-BoldItalic, Times-Italic, and so on

DECLARE CURRENT_Font '{"name":"Courier"}';
DECLARE _Font_Demo '{"name":"Times-Roman"}';
set CURRENT_Font=_Font_Demo;

CURRENT_TextFormat is a pseudo variable which used to set text format information for current text operation

DECLARE CURRENT_TextFormat '{"fontSize":12,"color":"BED4F1","italic":false,"bold":false,"shadow":false,"strike":false,"underline":"dash"}';

CURRENT_Paragraph is a pseudo variable which used to set text format information for current parapgrah operation

set CURRENT_Paragraph->'verticalAlignment'='center';/* table verticalAlignment: center,left,right,both */
set CURRENT_Paragraph->'alignment'='left';/*alignment: BOTH, CENTER, DISTRIBUTE, HIGH_KASHIDA, LEFT, LOW_KASHIDA, MEDIUM_KASHIDA, NUM_TAB, RIGHT, THAI_DISTRIBUTE */ set CURRENT_Paragraph->'wordwrap'=true;
set CURRENT_Paragraph->'indentationFirstLine'=480;/* indentationFirstLine: uset to indent the First Line of Every Paragraph*/
/* color: in the hex form "RRGGBB" */
/*underline: DASH, DASH_DOT_DOT_HEAVY, DASH_DOT_HEAVY, DASH_LONG, DASH_LONG_HEAVY, DASHED_HEAVY, DOT_DASH, DOT_DOT_DASH, DOTTED, DOTTED_HEAVY, DOUBLE, NONE, SINGLE, THICK, WAVE, WAVY_DOUBLE, WAVY_HEAVY, WORDS */

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