JavaDB SQLメモ

JavaDB（Apache Derby）のSQL（文法）のメモ。

CREATE TABLE

何はともあれ、テーブルを作成しないと。

create table テーブル名 (
 項目名 属性,
 …,
 constraint プライマリキー名 primary key(項目名, …)
);

• DerbyリファレンスマニュアルのCREATE TABLE 文
• Derbyリファレンスマニュアルのデータ型
• DerbyリファレンスマニュアルのCONSTRAINT 節 …制約

テーブルの存在は、SYS.SYSTABLESテーブルで管理されている。

select * from sys.systables where tablename='テーブル名';

テーブルの項目は、SYS.SYSCOLUMNSテーブルで管理されている。

select c.* from sys.systables t, sys.syscolumns c
where t.tablename = 'テーブル名'
  and c.referenceid = t.tableid
order by columnnumber
;

テーブルのキー名はSYS.SYSCONSTRAINTSで分かる。

select c.* from sys.systables t, sys.sysconstraints c
where t.tablename = 'テーブル名'
  and c.tableid = t.tableid
  and c.type = 'P' --プライマリキー
;

識別子列属性

INSERTする度に自動的に増加していく番号。Oracleのシーケンスに当たるもの。
Oracleでは「シーケンス」というオブジェクトだが、JavaDBでは項目の属性（いわば「default 値」と同じ）として扱われている。
ただし、１つのテーブルには１つしか設定できない。

項目の属性なので、create table文で項目に指定する。

create table TEST_SEQ (
 KEY1  integer generated always as identity (start with 1, increment by 1),
 DATA1 varchar(10),
 constraint PK_TEST primary key (KEY1)
);

「always」を指定すると、insertする度に自動的に採番された値が使用される。（insert時に自分では値を指定できない）
alwaysの代わりに「by default」を指定すると、insert文で明示的に値を指定しない場合だけ自動採番される。

startやincrement、また現在値（次の採番で使用される値）を確認するには、SYS.SYSCOLUMNSテーブルを参照する。

select COLUMNNAME, AUTOINCREMENTSTART, AUTOINCREMENTINC, AUTOINCREMENTVALUE
from sys.systables t, sys.syscolumns c
where t.tablename='TEST_SEQ'
  and c.referenceid=t.tableid
  and c.columnname='KEY1'
;

現在値をクリア・変更（採番を再スタート）したい場合は、ALTER TABLE文を使用する。コミットも必要。

alter table TEST_SEQ alter column KEY1 restart with 1;
commit;

• Derbyリファレンスマニュアルの発番列仕様
• DerbyリファレンスマニュアルのALTER TABLE文（列定義変更）

INSERT

テーブルを作ったら、データを入れないと意味が無い。

基本形

項目と値を列挙する。

insert into テーブル名 (項目, …) values (値, …);

insert into テーブル名 (項目, …) values (値, …), (値, …), 〜(…);	←2レコード以上いっぺんに挿入する

insert into テーブル名 (項目) values 値;			←1項目しか指定しない場合

insert into テーブル名 (項目) values 値, 値, …;	←1項目しか指定せず、2レコード以上挿入する

テーブルに存在しているのにinsert文で指定しなかった項目については、テーブル作成時に指定されたdefault値（あるいは自動採番値）が入る。
default値が指定されていなかった場合はnullになる。この場合、項目にNOT NULL制約があるなら、エラーになる。

項目名全部を省略する書き方

insert into テーブル values (値, …);

• DerbyリファレンスマニュアルのINSERT文

値には「default」というキーワードを指定することが出来る。
これは、テーブル作成時に指定されたdefault値（あるいは自動採番値）を意味する。

insert into TEST_SEQ (KEY1, DATA1) values (default, 'zzz');

insert into TEST_SEQ (      DATA1) values (          'zzz');	--これと同じ

• DerbyリファレンスマニュアルのVALUES式

→採番された値をJDBCで取得する方法

ダミーテーブル

1項目しか定義されておらず1レコードしか入っていないテーブルとして、SYSIBM.SYSDUMMY1というテーブルがある。[2009-04-10]
（Apache Derbyの前身はIBMが作っていたらしいので、このテーブル名はその名残なのかも）
OracleのDUALテーブルに相当。

ij> select * from SYSIBM.SYSDUMMY1;
IBM&
----
Y

1 行が選択されました