1.????hsql?學(xué)習(xí)
1.1.????學(xué)習(xí)目的
本文檔是針對hSQL?數(shù)據(jù)庫方面的基礎(chǔ)學(xué)習(xí)，為了使項目組成員能夠達到使用hSQL?數(shù)據(jù)庫的目的。
1.2.????培訓(xùn)對象
開發(fā)人員
1.3.????常用詞及符號說明
常用詞：
hsql：一種免費的跨平臺的數(shù)據(jù)庫系統(tǒng)
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?里面,或者放到當(dāng)前路徑下.
java?-cp?hsqldb.jar?org.hsqldb.util.DatabaseManager
2.2.????運行數(shù)據(jù)庫
啟動方式:?Server?Modes?and
In-Process?Mode?(also?called?Standalone?Mode).
一個test?數(shù)據(jù)庫會包含如下文件:
??test.properties
??test.script
??test.log
??test.data
??test.backup
test.properties?文件包含關(guān)于數(shù)據(jù)庫的一般設(shè)置.
test.script??文件包含表和其它數(shù)據(jù)庫,插入沒有緩存表的數(shù)據(jù).
test.log?文件包含當(dāng)前數(shù)據(jù)庫的變更.
test.data?文件包含緩存表的數(shù)據(jù)
test.backup?文件是最近持久化狀態(tài)的表的數(shù)據(jù)文件的壓縮備份文件
所有以上這個文件都是必要的,不能被刪除.如果數(shù)據(jù)庫沒有緩存表,test.data?和test.backup?文件將不會存在.另外,除了以上文件HSQLDB?數(shù)據(jù)庫可以鏈接到任何文本文件,比如cvs?文件.
當(dāng)操作test?數(shù)據(jù)庫的時候,?test.log?用于保存數(shù)據(jù)的變更.?當(dāng)正常SHUTDOWN,這個文件將被刪除.?否則(不是正常shutdown),這個文件將用于再次啟動的時候,重做這些變更.test.lck?文件也用于記錄打開的數(shù)據(jù)庫的事實,?正常SHUTDOWN,文件也被刪除.在一些情況下,test.data.old?文件會被創(chuàng)建,并刪除以前的.
2.3.????Server?Mode
java?-cp?../lib/hsqldb.jar?org.hsqldb.Server?-database.0?file:mydb?-dbname.0?xdb
命令行方式:
?
啟動數(shù)據(jù),數(shù)據(jù)庫文件mydb,數(shù)據(jù)庫名稱xdb
也可以在?server.properties?文件中定義啟動的數(shù)據(jù)庫,最多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
運行結(jié)果如下
?
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","");
這樣就可以連接數(shù)據(jù)庫。
只能在一個jvm?中使用，不能在多個jvm?中使用。
這種模式是在相同的jvm?下作為你的應(yīng)用程序的一部分，運行數(shù)據(jù)庫引擎。對大多數(shù)應(yīng)用程序，這種模式運行會相當(dāng)快，作為數(shù)據(jù)，不需要轉(zhuǎn)換和網(wǎng)絡(luò)傳輸。
主要的缺點就是不可能從外面的應(yīng)用程序訪問到默認數(shù)據(jù)庫，因此當(dāng)你的應(yīng)用運行時候，你不能通過別的工具檢查數(shù)據(jù)庫內(nèi)容。在1.8.0?版本中,你可以在相同jvm?中的線程中運行數(shù)據(jù)庫初始化，并提供外面訪問你的進程內(nèi)數(shù)據(jù)庫。
????推薦在開發(fā)應(yīng)用中使用這種方式。
連接串：
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
當(dāng)隨即訪問內(nèi)存，數(shù)據(jù)庫不固定時，可以采用內(nèi)存的方式運行數(shù)據(jù)庫，由于沒有數(shù)據(jù)寫到硬盤上，這種方式使用在應(yīng)用數(shù)據(jù)和applets?和特殊應(yīng)用的內(nèi)部進程中使用，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表）
當(dāng)使用?CREATE?TABLE??命令時，Memory?表時默認類型，它們的數(shù)據(jù)整體保存在內(nèi)存當(dāng)中，但是任何改變它們的結(jié)構(gòu)或者內(nèi)容，它們會被寫到<dbname>.script?文件中。這個腳本文件在數(shù)據(jù)庫下一次打開的時候被對出，內(nèi)存表重新被創(chuàng)建內(nèi)容，根temp?表不同，內(nèi)存表時持久化的。
CACHED?表通過CREATE?CACHED?TABLE?命令建立.?只有部分的它們的數(shù)據(jù)或者索引被保存在內(nèi)存中，允許大表占用幾百兆的內(nèi)存空間。例外一個優(yōu)點，在數(shù)據(jù)庫引擎中，啟動大量數(shù)據(jù)的緩存表需要花費少量的時間，缺點是減慢了運行和使用Hsqldb?的速度。表相對小的時候，不要使用cache?表，在小表中使用內(nèi)存數(shù)據(jù)庫。
從版本?1.7.0?以后，支持text?表，使用?CSV?(Comma?Separated?Value)??或者其它分隔符文本文件作為它們的數(shù)據(jù)源。你可以特殊指定一個存在的CSV?文件，例如從其它的數(shù)據(jù)或者程序中導(dǎo)出文件，作為TXT?表的數(shù)據(jù)源。?同時,你可以指定一個空文件，通過數(shù)據(jù)庫引擎填充數(shù)據(jù)。TEXT?表將比cache?表更加效率高。Text?表可以指向不同的數(shù)據(jù)文件。
*?memory-only?databases?數(shù)據(jù)庫只支持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.????索引和查詢速度
索引提高查詢速度，比提高排序速度。
主健和唯一所列自動創(chuàng)建索引，否則需要自己創(chuàng)建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):
一般規(guī)則是把縮小條件的列的表放在前面
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?,但是沒有直接設(shè)定這個條件.如果?TA?和?TB?每個表都包含100?條記錄,10000?組合將和?TC?關(guān)聯(lián),用于TC這個列的條件,盡管有索引在這個列上.使用JOIN?關(guān)鍵字,?在組合TC?之前,TA.COL1?=?TB.COL2?條件直接并縮小組合?TA?和?TB?的行數(shù),?在運行大數(shù)據(jù)量的表的結(jié)果是,將會很快:
????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
以上例子,數(shù)據(jù)引擎自動應(yīng)用于TC.COL4?=?1?組合小的集合于其它表關(guān)聯(lián).?Indexes?TC.COL4,?TB.COL2??TA.COL1?都將使用索引,提高查詢速度.
3.5.????Subqueries?and?Joins
使用join?和調(diào)整表的順序提高效率.
例如:,?第二個查詢的速度將更快一些(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)?與一個單記錄表相關(guān)聯(lián).?并使用TA.COL1索引,這將變得非常快.?第一個查詢是將?TA?表中的每一條記錄不斷地與MAX(TB.COL2)匹配.
3.6.????數(shù)據(jù)類型
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.????事務(wù)問題:
SET?PROPERTY?"sql.tx_no_multi_rewrite"?TRUE
4.????Connections
通用驅(qū)動jdbc:hsqldb:??下列協(xié)議標(biāo)識(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?
數(shù)據(jù)庫路徑.?
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).
屬性文件和設(shè)定存儲如下?:
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?文件支持如下設(shè)定:
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,?每個服務(wù)器支持同時啟動10個不同的數(shù)據(jù)庫.?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?數(shù)據(jù)庫:
E:\hsqldb>java?-jar?./lib/hsqldb.jar?mem
Hsql?Server:
(前提是xdb?server?已經(jīng)啟動):
(java?-cp?../lib/hsqldb.jar?org.hsqldb.Server?-database.0?file:mydb?-dbname.0?xdb)
java?-jar?./hsqldb.jar?xdb
執(zhí)行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;?//這樣才能提交到數(shù)據(jù)庫
E:\hsqldb>java?-jar?./hsqldb.jar?xdb?testuser.sql?>?file.txt?2>&1
輸出結(jié)果到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?在一處寫出即可,也可以連接別的數(shù)據(jù)庫
連接mysql
E:\hsqldb>java?-classpath?./lib/mysql-connector-java-3.1.10-bin.jar;./hsqldb.jar?org.hsqldb.util.SqlTool?mysqltest
.rc?文件內(nèi)容:
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