semovy

1. hsql 學習
1.1. 學習目的
本文檔是針對hSQL 數據庫方面的基礎學習，為了使項目組成員能夠達到使用hSQL 數據庫的目的。
1.2. 培訓對象
開發人員
1.3. 常用詞及符號說明
常用詞：
hsql：一種免費的跨平臺的數據庫系統
E:\hsqldb：表示是在dos 命令窗口下面
1.4. 參考信息
doc\guide\guide.pdf

2. HSQL
2.1. HSQL 運行工具
java -cp ../lib/hsqldb.jar org.hsqldb.util.DatabaseManager
注意hsqldb.jar 文件的文件路徑,最好能放到classpath 里面,或者放到當前路徑下.
java -cp hsqldb.jar org.hsqldb.util.DatabaseManager

2.2. 運行數據庫
啟動方式: Server Modes and
In-Process Mode (also called Standalone Mode).

一個test 數據庫會包含如下文件:
? test.properties
? test.script
? test.log
? test.data
? test.backup
test.properties 文件包含關于數據庫的一般設置.
test.script 文件包含表和其它數據庫,插入沒有緩存表的數據.
test.log 文件包含當前數據庫的變更.
test.data 文件包含緩存表的數據
test.backup 文件是最近持久化狀態的表的數據文件的壓縮備份文件
所有以上這個文件都是必要的,不能被刪除.如果數據庫沒有緩存表,test.data 和test.backup 文件將不會存在.另外,除了以上文件HSQLDB 數據庫可以鏈接到任何文本文件,比如cvs 文件.

當操作test 數據庫的時候, test.log 用于保存數據的變更. 當正常SHUTDOWN,這個文件將被刪除. 否則(不是正常shutdown),這個文件將用于再次啟動的時候,重做這些變更.test.lck 文件也用于記錄打開的數據庫的事實, 正常SHUTDOWN,文件也被刪除.在一些情況下,test.data.old 文件會被創建,并刪除以前的.






2.3. Server Mode
java -cp ../lib/hsqldb.jar org.hsqldb.Server -database.0 file:mydb -dbname.0 xdb

命令行方式:


啟動數據,數據庫文件mydb,數據庫名稱xdb

也可以在 server.properties 文件中定義啟動的數據庫,最多10個
例如: server.properties:
server.database.0=file:E:/hsqldb/data/mydb
server.dbname.0=xdb

server.database.1=file:E:/hsqldb/data/testdb
server.dbname.1=testdb

server.database.2=mem:adatabase
server.dbname.2=quickdb
啟動命令: java -cp ../lib/hsqldb.jar org.hsqldb.Server
運行結果如下



java 測試程序:
package test;
import junit.framework.TestCase;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestConnect extends TestCase {
Connection connection;
protected void setUp()
{
try {
Class.forName("org.hsqldb.jdbcDriver" );
connection = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/xdb","sa","");


} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void testselect()
{
Statement stmt=null;
ResultSet rs=null;
try {
stmt = connection.createStatement();
String sql ="select * from test";
rs=stmt.executeQuery( sql);
while(rs.next() )
{
System.out.println("id="+rs.getString("id"));
System.out.println("name="+rs.getString("name"));
}

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally
{
try {
rs.close() ;
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

}
protected void tearDown()
{
try {
connection.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

}
以上在eclipse 中測試通過.

2.4.In-Process (Standalone) Mode
不需要啟動server
connection = DriverManager.getConnection("jdbc:hsqldb:file:E:/hsqldb/data/mydb","sa","");
這樣就可以連接數據庫。
只能在一個jvm 中使用，不能在多個jvm 中使用。
這種模式是在相同的jvm 下作為你的應用程序的一部分，運行數據庫引擎。對大多數應用程序，這種模式運行會相當快，作為數據，不需要轉換和網絡傳輸。

主要的缺點就是不可能從外面的應用程序訪問到默認數據庫，因此當你的應用運行時候，你不能通過別的工具檢查數據庫內容。在1.8.0 版本中,你可以在相同jvm 中的線程中運行數據庫初始化，并提供外面訪問你的進程內數據庫。
推薦在開發應用中使用這種方式。
連接串：
Windows: DriverManager.getConnection("jdbc:hsqldb:file:E:/hsqldb/data/mydb","sa","");
Unix: DriverManager.getConnection("jdbc:hsqldb:file:/opt/db/testdb","sa","");

2.5.Memory-Only Databases
當隨即訪問內存，數據庫不固定時，可以采用內存的方式運行數據庫，由于沒有數據寫到硬盤上，這種方式使用在應用數據和applets 和特殊應用的內部進程中使用，URL：

Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:aname", "sa", "");
2.6.Using Multiple Databases in One JVM
2.7.Different Types of Tables
HSQLDB 支持 TEMP 表和三種類型的持久表（MEMORY 表, CACHED 表，TEXT表）

當使用 CREATE TABLE 命令時，Memory 表時默認類型，它們的數據整體保存在內存當中，但是任何改變它們的結構或者內容，它們會被寫到.script 文件中。這個腳本文件在數據庫下一次打開的時候被對出，內存表重新被創建內容，根temp 表不同，內存表時持久化的。

CACHED 表通過CREATE CACHED TABLE 命令建立. 只有部分的它們的數據或者索引被保存在內存中，允許大表占用幾百兆的內存空間。例外一個優點，在數據庫引擎中，啟動大量數據的緩存表需要花費少量的時間，缺點是減慢了運行和使用Hsqldb 的速度。表相對小的時候，不要使用cache 表，在小表中使用內存數據庫。

從版本 1.7.0 以后，支持text 表，使用 CSV (Comma Separated Value) 或者其它分隔符文本文件作為它們的數據源。你可以特殊指定一個存在的CSV 文件，例如從其它的數據或者程序中導出文件，作為TXT 表的數據源。 同時,你可以指定一個空文件，通過數據庫引擎填充數據。TEXT 表將比cache 表更加效率高。Text 表可以指向不同的數據文件。

* memory-only databases 數據庫只支持memory 表和cache 表，不支持text 表。
2.8. 約束和索引
HSQLDB 支持 PRIMARY KEY, NOT NULL, UNIQUE, CHECK and FOREIGN KEY 約束.





3. sql 命令
3.1. sql 支持
select top 1 * from test;
select limit 0 2 * from test;
DROP TABLE test IF EXISTS;
3.2. Constraints and Indexes
主健約束：PRIMARY KEY
唯一約束：
唯一索引：
外健：
CREATE TABLE child(c1 INTEGER, c2 VARCHAR, FOREIGN KEY (c1, c2) REFERENCES parent(p1, p2));

3.3. 索引和查詢速度
索引提高查詢速度，比提高排序速度。
主健和唯一所列自動創建索引，否則需要自己創建CREATE INDEX command。
索引： 唯一索引和非唯一索引
多列的索引，如果只是使用后面的，不使用第一個，將不會條查詢速度。

(TB is a very large table with only a few rows where TB.COL3 = 4)
SELECT * FROM TA JOIN TB ON TA.COL1 = TB.COL2 AND TB.COL3 = 4;
SELECT * FROM TB JOIN TA ON TA.COL1 = TB.COL2 AND TB.COL3 = 4;(faster)

原因是 TB.COL3 可以被快速的估計,如果TB 表放到前面(index on TB.COL3):
一般規則是把縮小條件的列的表放在前面

3.4. 使用where 還是join
使用 WHERE 條件鏈接表可能會降低運行速度.
下面的例子將會比較慢,即使使用了索引:
SELECT ... FROM TA, TB, TC WHERE TC.COL3 = TA.COL1 AND TC.COL3=TB.COL2 AND TC.COL4 = 1
這個查詢隱含TA.COL1 = TB.COL2 ,但是沒有直接設定這個條件.如果 TA 和 TB 每個表都包含100 條記錄,10000 組合將和 TC 關聯,用于TC這個列的條件,盡管有索引在這個列上.使用JOIN 關鍵字, 在組合TC 之前,TA.COL1 = TB.COL2 條件直接并縮小組合 TA 和 TB 的行數, 在運行大數據量的表的結果是,將會很快:
SELECT ... FROM TA JOIN TB ON TA.COL1 = TB.COL2 JOIN TC ON TB.COL2 = TC.COL3 WHERE TC.COL4 = 1
這個查詢可以提高一大步,如果改變表的順序, 所以 TC.COL1 = 1 將最先使用,這樣更小的集合將組合在一起:
SELECT ... FROM TC JOIN TB ON TC.COL3 = TB.COL2 JOIN TA ON TC.COL3 = TA.COL1 WHERE TC.COL4 = 1
以上例子,數據引擎自動應用于TC.COL4 = 1 組合小的集合于其它表關聯. Indexes TC.COL4, TB.COL2 TA.COL1 都將使用索引,提高查詢速度.
3.5. Subqueries and Joins
使用join 和調整表的順序提高效率.
例如:, 第二個查詢的速度將更快一些(TA.COL1 和TB.COL3都有索引):
Example 2.2. Query comparison
SELECT ... FROM TA WHERE TA.COL1 = (SELECT MAX(TB.COL2) FROM TB WHERE TB.COL3 = 4)

SELECT ... FROM (SELECT MAX(TB.COL2) C1 FROM TB WHERE TB.COL3 = 4) T2 JOIN TA ON TA.COL1 = T2.C1
第二個查詢將 MAX(TB.COL2) 與一個單記錄表相關聯. 并使用TA.COL1索引,這將變得非常快. 第一個查詢是將 TA 表中的每一條記錄不斷地與MAX(TB.COL2)匹配.
3.6. 數據類型
TINYINT, SMALLINT, INTEGER, BIGINT, NUMERIC and DECIMAL (without a decimal point) are supported integral types and map to byte, short, int, long and BigDecimal in Java.

Integral Types:
TINYINT, SMALLINT, INTEGER, BIGINT, NUMERIC and DECIMAL
Other Numeric Types:
REAL, FLOAT or DOUBLE
Bit and Boolean Types:
BOOLEAN: UNDEFINED,TRUE,FALSE
NULL values are treated as undefined.
Storage and Handling of Java Objects
Sequences and Identity

Identity Auto-Increment Columns:
The next IDENTITY value to be used can be set with the
ALTER TABLE ALTER COLUMN RESTART WITH ;
Sequences:
SELECT NEXT VALUE FOR mysequence, col1, col2 FROM mytable WHERE ...

3.7. 事務問題:
SET PROPERTY "sql.tx_no_multi_rewrite" TRUE

4. Connections
通用驅動jdbc:hsqldb: 下列協議標識(mem: file: res: hsql: http: hsqls: https:)
Table 4.1. Hsqldb URL Components
Driver and Protocol Host and Port Database
jdbc:hsqldb:mem:
not available accounts

jdbc:hsqldb:mem:.
jdbc:hsqldb:file:
not available mydb
/opt/db/accounts
C:/data/mydb

數據庫路徑.
jdbc:hsqldb:res:
not available /adirectory/dbname

jars files are accessed in Java programs. The /adirectory above stands for a directory in one of the jars.
jdbc:hsqldb:hsql:
jdbc:hsqldb:hsqls:
jdbc:hsqldb:http:
jdbc:hsqldb:https:
//localhost
//192.0.0.10:9500
//dbserver.somedomain.com
/an_alias
/enrollments
/quickdb

別名在server.properties or webserver.properties文件中指定
database.0=file:/opt/db/accounts
dbname.0=an_alias

database.1=file:/opt/db/mydb
dbname.1=enrollments

database.2=mem:adatabase
dbname.2=quickdb
In the example below, the database files lists.* in the /home/dbmaster/ directory are associated with the empty alias:
database.3=/home/dbmaster/lists
dbname.3=
4.1.Connection properties
Connection properties are specified either by establishing the connection via the:
DriverManager.getConnection (String url, Properties info);
method call, or the property can be appended to the full Connection URL.
Table 4.2. Connection Properties
get_column_name true column name in ResultSet
This property is used for compatibility with other JDBC driver implementations. When true (the default), ResultSet.getColumnName(int c) returns the underlying column name
When false, the above method returns the same value as ResultSet.getColumnLabel(int column) Example below:
jdbc:hsqldb:hsql://localhost/enrollments;get_column_name=false

When a ResultSet is used inside a user-defined stored procedure, the default, true, is always used for this property.
ifexists false connect only if database already exists
Has an effect only with mem: and file: database. When true, will not create a new database if one does not already exist for the URL.
When false (the default), a new mem: or file: database will be created if it does not exist.
Setting the property to true is useful when troubleshooting as no database is created if the URL is malformed. Example below:
jdbc:hsqldb:file:enrollments;ifexists=true
shutdown false shut down the database when the last connection is closed
This mimics the behaviour of 1.7.1 and older versions. When the last connection to a database is closed, the database is automatically shut down. The property takes effect only when the first connection is made to the database. This means the connection that opens the database. It has no effect if used with subsequent, simultaneous connections.
This command has two uses. One is for test suites, where connections to the database are made from one JVM context, immediately followed by another context. The other use is for applications where it is not easy to configure the environment to shutdown the database. Examples reported by users include web application servers, where the closing of the last connection conisides with the web app being shut down.


4.2. Properties Files
大小寫敏感 (e.g. server.silent=FALSE will have no effect, but server.silent=false will work).
屬性文件和設定存儲如下 :
Table 4.3. Hsqldb Server Properties Files
File Name Location Function
server.properties the directory where the command to run the Server class is issued settings for running HSQLDB as a database server communicating with the HSQL protocol
webserver.properties the directory where the command to run the WebServer class is issued settings for running HSQLDB as a database server communicating with the HTTP protocol
.properties the directory where all the files for a database are located settings for each particular database
Properties files for running the servers are not created automatically. You should create your own files that contain server.property=value pairs for each property.
4.2.1. Server and Web Server Properties
server.properties and webserver.properties 文件支持如下設定:
Table 4.4. Property File Properties
Value Default Description
server.database.0 test the path and file name of the first database file to use
server.dbname.0 "" lowercase server alias for the first database file
server.urlid.0 NONE SqlTool urlid used by UNIX init script. (This property is not used if your are running Server/Webserver on a platform other than UNIX, or of you are not using our UNIX init script).
server.silent true no extensive messages displayed on console
server.trace false JDBC trace messages displayed on console
In 1.8.0, 每個服務器支持同時啟動10個不同的數據庫. The server.database.0 property defines the filename / path whereas the server.dbname.0 defines the lowercase alias used by clients to connect to that database. The digit 0 is incremented for the second database and so on. Values for the server.database.{0-9} property can use the mem:, file: or res: prefixes and properties as discussed above under CONNECTIONS. For example,
database.0=mem:temp;sql.enforce_strict_size=true;
Values specific to server.properties are:
Table 4.5. Server Property File Properties
Value Default Description
server.port 9001 TCP/IP port used for talking to clients. All databases are served on the same port.
server.no_system_exit true no System.exit() call when the database is closed
Values specific to webserver.properties are:
Table 4.6. WebServer Property File Properties
Value Default Description
server.port 80 TCP/IP port used for talking to clients
server.default_page index.html the default web page for server
server.root ./ the location of served pages
. ? multiple entries such as .html=text/html define the mime types of the static files served by the web server. See the source for WebServer.java for a list.
All the above values can be specified on the command line to start the server by omitting the server. prefix.
5. SqlTool
Mem 數據庫:
E:\hsqldb>java -jar ./lib/hsqldb.jar mem
Hsql Server:
(前提是xdb server 已經啟動):
(java -cp ../lib/hsqldb.jar org.hsqldb.Server -database.0 file:mydb -dbname.0 xdb)
java -jar ./hsqldb.jar xdb



執行sql 語句:
1)
Mydb.sql :
CREATE MEMORY TABLE TEST(ID INTEGER,NAME VARCHAR(20));
INSERT INTO TEST VALUES(1,'aaa');
INSERT INTO TEST VALUES(2,'bbb');
E:\hsqldb>java -jar ./lib/hsqldb.jar mem mydb.sql
1 row updated
1 row updated
2)
testuser.sql:
CREATE MEMORY TABLE userTEST(ID INTEGER,NAME VARCHAR(20));
INSERT INTO userTEST VALUES(1,'aaa');
INSERT INTO userTEST VALUES(2,'bbb');
commit; //這樣才能提交到數據庫
E:\hsqldb>java -jar ./hsqldb.jar xdb testuser.sql > file.txt 2>&1
輸出結果到file.txt 文件中.


連接oracle:

E:\hsqldb>java -classpath ./classes12.zip;./hsqldb.jar org.hsqldb.util.SqlTool --driver oracle.jdbc.driver.OracleDriver
orcl

sqltool.rc 文件:
urlid orcl
url jdbc:oracle:thin:@localhost:1521:livedoor
username scott
password tiger
driver oracle.jdbc.OracleDriver
以上driver 在一處寫出即可,也可以連接別的數據庫

連接mysql
E:\hsqldb>java -classpath ./lib/mysql-connector-java-3.1.10-bin.jar;./hsqldb.jar org.hsqldb.util.SqlTool mysqltest

.rc 文件內容:
urlid mysqltest
url jdbc:mysql://localhost:3306/test
username root
password root
driver com.mysql.jdbc.Driver
6. sql 語法

6.1.1. ALTER INDEX[1]
ALTER INDEX RENAME TO ;
6.1.2. ALTER SEQUENCE[1]
ALTER SEQUENCE RESTART WITH ;
6.1.3. ALTER SCHEMA[1]
ALTER SCHEMA RENAME TO ;
6.1.4. ALTER TABLE[1]
ALTER TABLE ADD [COLUMN] Datatype
[(columnSize[,precision])] [{DEFAULT |
GENERATED BY DEFAULT AS IDENTITY (START WITH [, INCREMENT BY ])}] |
[[NOT] NULL] [IDENTITY] [PRIMARY KEY]
[BEFORE ];

ALTER TABLE DROP [COLUMN] ;
ALTER TABLE ALTER COLUMN RENAME TO
ALTER TABLE ALTER COLUMN SET DEFAULT };
ALTER TABLE ALTER COLUMN SET [NOT] NULL
ALTER TABLE ALTER COLUMN ;
ALTER TABLE ALTER COLUMN
RESTART WITH

ALTER TABLE ADD [CONSTRAINT ]
CHECK ();
ALTER TABLE ADD [CONSTRAINT ] UNIQUE ();
ALTER TABLE ADD [CONSTRAINT ]
PRIMARY KEY ();
ALTER TABLE
ADD [CONSTRAINT ] FOREIGN KEY ()
REFERENCES ()
[ON {DELETE | UPDATE} {CASCADE | SET DEFAULT | SET NULL}];

ALTER TABLE DROP CONSTRAINT ;
ALTER TABLE RENAME TO ;
6.1.5. ALTER USER[1]
ALTER USER SET PASSWORD ;
ALTER USER SET INITIAL SCHEMA ;
6.1.6. CALL
CALL Expression;
See also: Stored Procedures / Functions, SQL Expression.
6.1.7. CHECKPOINT
CHECKPOINT [DEFRAG[1]];
See also: SHUTDOWN, SET LOGSIZE.
6.1.8. COMMIT
COMMIT [WORK];
See also: ROLLBACK, SET AUTOCOMMIT, SET LOGSIZE.
6.1.9. CONNECT
CONNECT USER PASSWORD ;
See also: GRANT, REVOKE.
6.1.10. CREATE ALIAS
CREATE ALIAS FOR ;
See also: CALL, Stored Procedures / Functions.
6.1.11. CREATE INDEX
CREATE [UNIQUE] INDEX ON ( [DESC] [, ...]) [DESC];
6.1.12. CREATE ROLE[1]
CREATE ROLE ;
6.1.13. CREATE SCHEMA[1]
CREATE SCHEMA AUTHORIZATION
[ [] [...];
CREATE SCHEMA ACCOUNTS AUTHORIZATION DBA
CREATE TABLE AB(A INTEGER, ...)
CREATE TABLE CD(C CHAHR, ...)
CREATE VIEW VI AS SELECT ...
GRANT SELECT TO PUBLIC ON AB
GRANT SELECT TO JOE ON CD;
6.1.14. CREATE SEQUENCE[1]
CREATE SEQUENCE [AS {INTEGER | BIGINT}]
[START WITH ] [INCREMENT BY ];
SELECT [...,] NEXT VALUE FOR [, ...] FROM ;

6.1.15. CREATE TABLE
CREATE [MEMORY | CACHED | [GLOBAL] TEMPORARY | TEMP [1] | TEXT[1]] TABLE
( [, ...] [, ...] )
[ON COMMIT {DELETE | PRESERVE} ROWS];
6.1.16. CREATE TRIGGER[1]
CREATE TRIGGER {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON
[FOR EACH ROW] [QUEUE n] [NOWAIT] CALL ;
6.1.17. CREATE USER
CREATE USER PASSWORD [ADMIN];
6.1.18. CREATE VIEW[1]
CREATE VIEW [(,..) AS SELECT ... FROM ... [WHERE Expression]
[ORDER BY orderExpression [, ...]]
[LIMIT [OFFSET ]];
CREATE VIEW mealsjv AS
SELECT m.mid mid, m.name name, t.mealtype mt, a.aid aid,
a.gname + ' ' + a.sname author, m.description description,
m.asof asof
FROM meals m, mealtypes t, authors a
WHERE m.mealtype = t.mealtype
AND m.aid = a.aid;

CREATE VIEW mealswebv AS SELECT name, author FROM mealsjv;

CREATE VIEW aview (new_name, new_author) AS
SELECT name, author
FROM mealsjv
6.1.19. DELETE
DELETE FROM table [WHERE Expression];
6.1.20. DISCONNECT
DISCONNECT;
6.1.21. DROP INDEX
DROP INDEX index [IF EXISTS];
6.1.22. DROP ROLE[1]
DROP ROLE ;
6.1.23. DROP SEQUENCE[1]
DROP SEQUENCE [IF EXISTS] [RESTRICT | CASCADE];
6.1.24. DROP SCHEMA[1]
DROP SCHEMA [RESTRICT | CASCADE];
6.1.25. DROP TABLE
DROP TABLE [IF EXISTS] [RESTRICT | CASCADE];
6.1.26. DROP TRIGGER
DROP TRIGGER ;
6.1.27. DROP USER
DROP USER ;
6.1.28. DROP VIEW[1]
DROP VIEW [IF EXISTS] [RESTRICT | CASCADE];
6.1.29. EXPLAIN PLAN
EXPLAIN PLAN FOR { SELECT ... | DELETE ... | INSERT ... | UPDATE ..};
6.1.30. GRANT
GRANT { SELECT | DELETE | INSERT | UPDATE | ALL } [,...]
ON { table | CLASS "package.class" } TO ;
GRANT [,...] TO [1];
GRANT SELECT ON Test TO GUEST;
GRANT ALL ON CLASS "java.lang.Math.abs" TO PUBLIC;

6.1.31. INSERT
INSERT INTO table [( column [,...] )]
{ VALUES(Expression [,...]) | SelectStatement};
6.1.32. REVOKE
REVOKE { SELECT | DELETE | INSERT | UPDATE | ALL } [,...]
ON { table | CLASS "package.class" } FROM ;
REVOKE [,...] FROM [1];
6.1.33. ROLLBACK
ROLLBACK [TO SAVEPOINT [1] | WORK}];
6.1.34. SAVEPOINT[1]
SAVEPOINT ;
6.1.35. SCRIPT
SCRIPT ['file'];
6.1.36. SELECT[1]
SELECT [{LIMIT | TOP }[1]][ALL | DISTINCT]
{ selectExpression | table.* | * } [, ...]
[INTO [CACHED | TEMP | TEXT][1] newTable]
FROM tableList
[WHERE Expression]
[GROUP BY Expression [, ...]]
[HAVING Expression]
[{ UNION [ALL | DISTINCT] | {MINUS [DISTINCT] | EXCEPT [DISTINCT] } |
INTERSECT [DISTINCT] } selectStatement]
[ORDER BY orderExpression [, ...]]
[LIMIT [OFFSET ]];
tableList
table [{CROSS | INNER | LEFT OUTER | RIGHT OUTER}
JOIN table ON Expression] [, ...]
table
{ (selectStatement) [AS] label | tableName}
selectExpression
{ Expression | COUNT(*) | {
COUNT | MIN | MAX | SUM | AVG | SOME | EVERY |
VAR_POP | VAR_SAMP | STDDEV_POP | STDDEV_SAMP
} ([ALL | DISTINCT][1]] Expression) } [[AS] label]
If CROSS JOIN is specified no ON expression is allowed for the join.
orderExpression
{ columnNr | columnAlias | selectExpression }
[ASC | DESC]
LIMIT n m
LIMIT n m

LIMIT m OFFSET n

TOP m

UNION and other set operations

6.1.37. SET AUTOCOMMIT
SET AUTOCOMMIT { TRUE | FALSE };
6.1.38. SET DATABASE COLLATION[1]
SET DATABASE COLLATION ;
6.1.39. SET CHECKPOINT DEFRAG[1]
SET CHECKPOINT DEFRAG ;
6.1.40. SET IGNORECASE
SET IGNORECASE { TRUE | FALSE };
6.1.41. SET INITIAL SCHEMA [1]
Users may change their base default schema name with the comand
SET INITIAL SCHEMA ;
6.1.42. SET LOGSIZE
SET LOGSIZE ;
6.1.43. SET PASSWORD
SET PASSWORD ;
6.1.44. SET PROPERTY[1]
SET PROPERTY ;
6.1.45. SET REFERENTIAL INTEGRITY
SET REFERENTIAL_INTEGRITY { TRUE | FALSE };
6.1.46. SET SCHEMA[1]
SET SCHEMA ;
6.1.47. SET SCRIPTFORMAT[1]
SET SCRIPTFORMAT {TEXT | BINARY | COMPRESSED};
6.1.48. SET TABLE INDEX
SET TABLE tableName INDEX 'index1rootPos index2rootPos ... ';
6.1.49. SET TABLE READONLY[1]
SET TABLE READONLY {TRUE | FALSE};
6.1.50. SET TABLE SOURCE[1]
SET TABLE SOURCE [DESC];
SET TABLE mytable SOURCE "myfile;fs=|;vs=.;lvs=~"
Supported Properties
quoted = { true | false }
default is true. If false, treats double quotes as normal characters
all_quoted = { true | false }
default is false. If true, adds double quotes around all fields.
encoding =
character encoding for text and character fields, for example, encoding=UTF-8
ignore_first = { true | false }
default is false. If true ignores the first line of the file
cache_scale=
exponent to calculate rows of the text file in cache. Default is 8, equivalent to nearly 800 rows
cache_size_scale = r
exponent to calculate average size of each row in cache. Default is 8, equivalent to 256 bytes per row.
fs =
field separator
vs =
varchar separator
lvs =
long varchar separator
Special indicators for Hsqldb Text Table separators
\semi
semicolon
\quote
quote
\space
space character
\apos
apostrophe
\n
newline - Used as an end anchor (like $ in regular expressions)
\r
carriage return
\t
tab
\\
backslash
\u####
a Unicode character specified in hexadecimal
Only an administrator may do this.

6.1.51. SET WRITE DELAY[1]
SET WRITE_DELAY {{ TRUE | FALSE } | | MILLIS};
6.1.52. SHUTDOWN
SHUTDOWN [IMMEDIATELY | COMPACT | SCRIPT[1]];
6.2. Data Types
Table 9.1. Data Types. The types on the same line are equivalent.
Name Range Java Type
INTEGER | INT as Java type int | java.lang.Integer
DOUBLE [PRECISION] | FLOAT as Java type double | java.lang.Double
VARCHAR as Integer.MAXVALUE java.lang.String
VARCHAR_IGNORECASE as Integer.MAXVALUE java.lang.String
CHAR | CHARACTER as Integer.MAXVALUE java.lang.String
LONGVARCHAR as Integer.MAXVALUE java.lang.String
DATE as Java type java.sql.Date
TIME as Java type java.sql.Time
TIMESTAMP | DATETIME as Java type java.sql.Timestamp
DECIMAL No limit java.math.BigDecimal
NUMERIC No limit java.math.BigDecimal
BOOLEAN | BIT as Java type boolean | java.lang.Boolean
TINYINT as Java type byte | java.lang.Byte
SMALLINT as Java type short | java.lang.Short
BIGINT as Java type long | java.lang.Long
REAL as Java type double | java.lang.Double[1]

BINARY as Integer.MAXVALUE byte[]
VARBINARY as Integer.MAXVALUE byte[]
LONGVARBINARY as Integer.MAXVALUE byte[]
OTHER | OBJECT as Integer.MAXVALUE java.lang.Object
The uppercase names are the data types names defined by the SQL standard or commonly used by RDMS's. The data types in quotes are the Ja

6.2.1. 自動增長:
create table user(id IDENTITY,name varchar(20));
sql> create table dept(id int GENERATED BY DEFAULT AS IDENTITY(start with 10,increment by 5) not null PRIMARY KEY,name v
archar(20));
sql> insert into dept(name) values('asc');
1 row updated
sql> insert into dept(name) values('security');
1 row updated
sql> select * from dept;
ID NAME
-- --------
10 asc
15 security

2 rows

6.3. SQL Comments
-- SQL style line comment
// Java style line comment
/* C style line comment */
7. Hsqldb Test Utility
拷貝 junit.jar 到/lib 目錄下
運行: ant hsqldbtest
生成 hsqldbtest.jar
運行: \hsqldb\testrun\hsqldb>runtest TestSelf