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?表時默認類型，它們的數據整體保存在內存當中，但是任何改變它們的結構或者內容，它們會被寫到<dbname>.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?<column?name>?RESTART?WITH?<new?value>;
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
<dbname>.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
.<extension>?????????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?<indexname>?RENAME?TO?<newname>;
6.1.2.????ALTER?SEQUENCE[1]
ALTER?SEQUENCE?<sequencename>?RESTART?WITH?<value>;
6.1.3.????ALTER?SCHEMA[1]
ALTER?SCHEMA?<schemaname>?RENAME?TO?<newname>;
6.1.4.????ALTER?TABLE[1]
ALTER?TABLE?<tablename>?ADD?[COLUMN]?<columnname>?Datatype
????[(columnSize[,precision])]?[{DEFAULT?<defaultValue>?|
????GENERATED?BY?DEFAULT?AS?IDENTITY?(START?WITH?<n>[,?INCREMENT?BY?<m>])}]?|
????[[NOT]?NULL]?[IDENTITY]?[PRIMARY?KEY]
????[BEFORE?<existingcolumn>];

ALTER?TABLE?<tablename>?DROP?[COLUMN]?<columnname>;
ALTER?TABLE?<tablename>?ALTER?COLUMN?<columnname>?RENAME?TO?<newname>
ALTER?TABLE?<tablename>?ALTER?COLUMN?<columnname>?SET?DEFAULT?<defaultvalue>};
ALTER?TABLE?<tablename>?ALTER?COLUMN?<columnname>?SET?[NOT]?NULL
ALTER?TABLE?<tablename>?ALTER?COLUMN?<columnDefinition>;
ALTER?TABLE?<tablename>?ALTER?COLUMN?<columnname>
????RESTART?WITH?<new?sequence?value>

ALTER?TABLE?<tablename>?ADD?[CONSTRAINT?<constraintname>]
????CHECK?(<search?condition>);
ALTER?TABLE?<tablename>?ADD?[CONSTRAINT?<constraintname>]?UNIQUE?(<column?list>);
ALTER?TABLE?<tablename>?ADD?[CONSTRAINT?<constraintname>]
????PRIMARY?KEY?(<column?list>);
ALTER?TABLE?<tablename>
????ADD?[CONSTRAINT?<constraintname>]?FOREIGN?KEY?(<column?list>)
????REFERENCES?<exptablename>?(<column?list>)
????[ON?{DELETE?|?UPDATE}?{CASCADE?|?SET?DEFAULT?|?SET?NULL}];

ALTER?TABLE?<tablename>?DROP?CONSTRAINT?<constraintname>;
ALTER?TABLE?<tablename>?RENAME?TO?<newname>;
6.1.5.????ALTER?USER[1]
ALTER?USER?<username>?SET?PASSWORD?<password>;
ALTER?USER?<username>?SET?INITIAL?SCHEMA?<schemaname>;
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?<username>?PASSWORD?<password>;
See?also:?GRANT,?REVOKE.
6.1.10.????CREATE?ALIAS
CREATE?ALIAS?<function>?FOR?<javaFunction>;
See?also:?CALL,?Stored?Procedures?/?Functions.
6.1.11.????CREATE?INDEX
CREATE?[UNIQUE]?INDEX?<index>?ON?<table>?(<column>?[DESC]?[,?...])?[DESC];
6.1.12.????CREATE?ROLE[1]
CREATE?ROLE?<rolename>;
6.1.13.????CREATE?SCHEMA[1]
CREATE?SCHEMA?<schemaname>?AUTHORIZATION?<grantee>
????[<createStatement>?[<grantStatement>]?[...];
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?<sequencename>?[AS?{INTEGER?|?BIGINT}]
????[START?WITH?<startvalue>]?[INCREMENT?BY?<incrementvalue>];
SELECT?[...,]?NEXT?VALUE?FOR?<sequencename>?[,?...]?FROM?<tablename>;

6.1.15.????CREATE?TABLE
CREATE?[MEMORY?|?CACHED?|?[GLOBAL]?TEMPORARY?|?TEMP?[1]?|?TEXT[1]]?TABLE?<name>
????(?<columnDefinition>?[,?...]?[,?<constraintDefinition>...]?)
????[ON?COMMIT?{DELETE?|?PRESERVE}?ROWS];
6.1.16.????CREATE?TRIGGER[1]
CREATE?TRIGGER?<name>?{BEFORE?|?AFTER}?{INSERT?|?UPDATE?|?DELETE}?ON?<table>
????[FOR?EACH?ROW]?[QUEUE?n]?[NOWAIT]?CALL?<TriggerClass>;
6.1.17.????CREATE?USER
CREATE?USER?<username>?PASSWORD?<password>?[ADMIN];
6.1.18.????CREATE?VIEW[1]
CREATE?VIEW?<viewname>[(<viewcolumn>,..)?AS?SELECT?...?FROM?...?[WHERE?Expression]
[ORDER?BY?orderExpression?[,?...]]
[LIMIT?<limit>?[OFFSET?<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?<rolename>;
6.1.23.????DROP?SEQUENCE[1]
DROP?SEQUENCE?<sequencename>?[IF?EXISTS]?[RESTRICT?|?CASCADE];
6.1.24.????DROP?SCHEMA[1]
DROP?SCHEMA?<schemaname>?[RESTRICT?|?CASCADE];
6.1.25.????DROP?TABLE
DROP?TABLE?<table>?[IF?EXISTS]?[RESTRICT?|?CASCADE];
6.1.26.????DROP?TRIGGER
DROP?TRIGGER?<trigger>;
6.1.27.????DROP?USER
DROP?USER?<username>;
6.1.28.????DROP?VIEW[1]
DROP?VIEW?<viewname>?[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?<grantee>;
GRANT?<rolename>?[,...]?TO?<grantee>[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?<grantee>;
REVOKE?<rolename>?[,...]?FROM?<grantee>[1];
6.1.33.????ROLLBACK
ROLLBACK?[TO?SAVEPOINT?<savepoint?name>[1]?|??WORK}];
6.1.34.????SAVEPOINT[1]
SAVEPOINT?<savepoint?name>;
6.1.35.????SCRIPT
SCRIPT?['file'];
6.1.36.????SELECT[1]
SELECT?[{LIMIT?<offset>?<limit>?|?TOP?<limit>}[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?<limit>?[OFFSET?<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?<double?quoted?collation?name>;
6.1.39.????SET?CHECKPOINT?DEFRAG[1]
SET?CHECKPOINT?DEFRAG?<size>;
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?<schemaname>;
6.1.42.????SET?LOGSIZE
SET?LOGSIZE?<size>;
6.1.43.????SET?PASSWORD
SET?PASSWORD?<password>;
6.1.44.????SET?PROPERTY[1]
SET?PROPERTY?<double?quoted?name>?<value>;
6.1.45.????SET?REFERENTIAL?INTEGRITY
SET?REFERENTIAL_INTEGRITY?{?TRUE?|?FALSE?};
6.1.46.????SET?SCHEMA[1]
SET?SCHEMA?<schemaname>;
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?<tablename>?READONLY?{TRUE?|?FALSE};
6.1.50.????SET?TABLE?SOURCE[1]
SET?TABLE?<tablename>?SOURCE?<file?and?options>?[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?=?<encoding?name>?
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=?<numeric?value>?
exponent?to?calculate?rows?of?the?text?file?in?cache.?Default?is?8,?equivalent?to?nearly?800?rows
cache_size_scale?=?<numeric?value>r?
exponent?to?calculate?average?size?of?each?row?in?cache.?Default?is?8,?equivalent?to?256?bytes?per?row.
fs?=?<unquoted?character>?
field?separator
vs?=?<unquoted?character>?
varchar?separator
lvs?=?<unquoted?character>?
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?}?|?<seconds>?|?<milliseconds>?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