Chapter 6. SQL Syntax

Index:

1. Select
2. ? Expression
3. Insert
4. Update
5. Delete
6. CREATE SCHEMA
7. CREATE TABLE
8. DROP TABLE
9. ALTER TABLE
10. TRUNCATE TABLE
11. PACK TABLE
12. RENAME TABLE
13. CREATE INDEX
14. DROP INDEX
15. REINDEX
16. CREATE SEQUENCE
17. DROP SEQUENCE
18. ALTER SEQUENCE
19. SET TRANSACTION
20. START TRANSACTION
21. COMMIT
22. ROLLBACK
23. SAVEPOINT
24. RELEASE SAVEPOINT
25. Comment Syntax
26. SQL States

Use ";" to separate multi sql statements. For instance, "insert into test (int1) values(1);insert into test (int1) values(2);". "reserved word", [reserved word] or {v 'reserved word'} is used to quote a column with reserved word name in SQL statement, for instance, 'select {v 'RIGHT'},'other' from states where {v 'RIGHT'}=32. The HXTT DBF supports using DATE, TIME, TIMESTAMP, GROUP, ORDER, KEY, DESC, SEQUENCE, INCREMENT, MINVALUE, MAXVALUE, CACHE, CYCLE, OTHER, SET, and UPDATE directly in SQL, although they're reserved words too.

SELECT [ALL | DISTINCT [ ON ( expression [, ...] ) ] ] | DISTINCTROW [TOP n [PERCENT]] select-list FROM table-reference-list [WHERE condition-expression] [group-by-clause] [HAVING condition-expression] [union-clause] [order-by-clause] [FOR UPDATE]

select-list: { expression [ [AS] columnAlias] | table.* | * } [,...]

table-reference-list: {table_reference | table-join} [,...]

table_reference : { { table-name | subquery | (table-join) } [ [AS] tableAlias] }

table-name: {[schemas.] tableName [@catalog] } | {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.

group-by-clause: GROUP BY expression [,...]

union-clause: { UNION | INTERSECT | EXCEPT | MINUS } [ ALL ] select-statement [ union-clause ...]

order-by-clause: ORDER BY expression [ASC|DESC] [,...]

_rowid_, is a virtual column as primary key.

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, and _rowid_. For instance, "select distinct 'First Name',name,age from users". 'First Name' will be ignored since it's a constant.

For instance:

SELECT top 8 percent * FROM sz9010@data;
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 user@brian 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

? Expression

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), condition operator(>,>=,=,==,<=,<,!=,<>), [NOT] LIKE value {escape 'escape-character'},[NOT] ILIKE value {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 180), 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.

If you query "? Expression", you will get a calculated value through a one-row ResultSet. Column can be used in all sql except for "? Expression". Parameter(?) can only be used in PreparedStatement.

For instance:

?val('123.222')
?CONVERT('123',SQL_INTEGER)
?TTOC({d '1999-10-10'},1)
?32434+656.2
?{d '2999-11-21'}={ts '2999-11-21 23:22:20.3335'}
?{d '2999-11-21'}>{ts '2999-11-21 23:22:20.333'}
?{t '23:22:20'}={ts '1999-01-01 23:22:20.333'}
?POSITION('$', 'ddsds')
?LOCATE('$','SF$SF$S',3)
?CTOD(DTOC(TTOD(NOW()+HOUR(NOW()))))+3
?trim(both 'a' from 'aaaa ddfdd aaa')
?trim(LEADING 'a' from 'aaaa ddfdd aaa')
?IFNULL(1,33)
?truncate(353.32542,-2)
?123 in(456,123,789,'abc')
? DATE_ADD('1997-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND)
?SUBTIME('1997-12-31 23:59:59.999999', '1 1:1:1.000002')
?EXTRACT(DECADE FROM '2001-02-16 20:38:40');
?'88'+IIF(3<6,'1','0')
?encode('adsdfsdf');
?decode(encode('adsdfsdf'))+'';

INSERT INTO table-name [ ( column-identifier [,...] ) ] { 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 DBF 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 ecode@brian 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 SET column-identifier = expression [,...] [WHERE condition-expression]

"update tableName set _DeletedFlag=false[, column-identifier = {expression | NULL}]... [WHERE condition-expression]" will unmarks records marked for deletion in the selected table. For instance, you can use "update tableName set _DeletedFlag=false" to restore all deleted roecords. "update tableName set _DeletedFlag=false where recno() between 50 and 100" will restore part deleted records.

For instance:

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;

DELETE FROM table-name [WHERE condition-expression]

Removes rows in a table according to condition-expression. The HXTT DBF will zap the table if you use "delete from table-name where true". The HXTT DBF will pack the table if you use "delete from table-name where deleted()".

For instance:

delete from test where SequenceID=4;

CREATE SCHEMA [IF NOT EXISTS] schemaName

Create a subdirectory to contain database files.

For instance:

create schema if not exists data222;

CREATE TABLE [IF NOT EXISTS] table-name [(column-identifier data-type [constraint] [,...]) [, PRIMARY KEY ( column [,...] )] ] [ [AS] SELECT query | ? ]

data-type: CHAR(n) | CHARACTER(n) | VARCHAR(n) | BINARY (n) | VARBINARY (n) | NUMERIC(n1[,n2]) | DEC[IMAL](n1[,n2]) | INT[EGER] | 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])

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

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

The HXTT DBF will ignore DEFAULT expression and (dateFormat) for Xbase's compatibility. Column can store null values in FoxPro,FoxBase or VFP, and constraint NULL is ignored for other Xbase. UNIQUE and PRIMARY KEY will be ignored, and you should use CREATE INDEX for that purpose.

For instance:

create table if not exists test (int1 int, float1 float,double1 numeric(12,3),boolean1 boolean, currency1 OTHER (Currency),dec1 DECIMAL(10,5), mem1 clob,char1 varchar(25),date1 date, time1 timestamp);
create table if not exists product1 (MODEL_NO varchar(20), MODEL_NM varchar(30), DIGIT8_CD int, COUNTRY_CD varchar(10), CATE_CD varchar(10), GENRE_CD varchar(20), FACTORY_CD varchar(20), UOM_CD varchar(20), RELEASE_DT date, JAPAN_SRP int, APPROVE_NO varchar(20), BAN boolean, DISCONT boolean, BEST boolean, PKG_REMARK varchar(20), REMARK1 varchar(30), REMARK2 varchar(30), INTERFACE boolean, currency1 OTHER (Currency),mem1 blob);

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.

For instance:

drop table if exists states;

ALTER TABLE table-name alter-specification [,...]

alter-specification: {{ADD|MODIFY} column-identifier data-type [constraint]}| DROP column-identifier | RENAME column-identifier1 TO column-identifier2 | RENAME TO table-name2

When some alter operations are in one ALTER sql, the HXTT DBF 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;

PACK TABLE [IF EXISTS] table-name

pack the table.

For instance:

package table test;

RENAME TABLE table-name TO table-name2

Rename the table.

For instance:

RENAME table test to test1;

CREATE [UNIQUE] INDEX [IF NOT EXISTS] indexName[(keylength)][,...] [OF indexFileName] ON tableName (expression [UNIQUE] [PRIMARY KEY] [ASC|DESC] [FOR expression][,...])

Create an index file which can contains one or more index expressions for a table. The HXTT DBF will utilize index when condition-expression contains indexed expression.

For instance:

create index ttt on test (int1 ASC);
create index ttt of user.cdx on user@brian (user ASC);
create index ttt on test1@BRIAN (COMPANY+LEFT(CITY,2) ASC);
create index int1,char1 of test.cdx on test (int1 unique ,char1+int1 for not empty(char1));
create index if not exists ttt,bbb of product1.cdx on product1 (currency1,mem1 PRIMARY KEY ASC);
create index if not exists passwd,passwdb,encode,encrypt of testencrypt.cdx on testencrypt (passwd,passwdb,encode,encrypt);

DROP INDEX [IF EXISTS] {ALL | indexName[,...]} [of indexFileName] ON table-name

Removes the specified index from the database.

For instance:

drop index all on test;

REINDEX {ALL | indexFileName[,...]} ON table-name

Rebuild the specified index.

For instance:

reindex all on test;

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: [schemas.] sequenceName [@catalog]

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;

Comment Syntax

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

For instance:

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

SQL States