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