HSQLDBを使用したデータベース入門その１　SELECTの基本形

>>この記事には書き直した新しいページがあります。<<

<<前　 [TOP] 　次>>

データベースにサンプルのテーブルを作成します。
以下の内容を２つのテキストファイルにコピーし、それに拡張子「.sql」をつけて保存して下さい。

今回は、SQLファイルの名前を「TestTable.sql」と「TestData.sql」にしました。

【TestTable.sql】

create table books (
    id            int,
    title         varchar(50),
    author        varchar(50),
    publisher     varchar(30),
    constraint pk_books primary key(id)
);

create table keywords (
	id      int,
	keyword varchar(30),
	constraint fk_id foreign key(id) references books(id)
);

【TestData.sql】

insert into books values (1001, '国語の本その１', '国語一郎', '国語出版');
insert into books values (1002, '国語の本その２', '国語次郎', '国語出版');
insert into books values (1003, '国語の本その３', '国語三郎', '国語出版');
insert into books values (1004, '国語の本その４', '国語四郎', '国語出版');
insert into books values (1005, '国語の本その５', '国語五郎', '国語出版');
insert into books values (1006, '国語の本その６', '国語六郎', '国語出版');
insert into books values (1007, '国語の本その７', '国語七郎', '国語出版');
insert into books values (1008, '国語の本その８', '国語八郎', '国語出版');
insert into books values (1009, '国語の本その９', '国語九郎', '国語出版');
insert into books values (1010, '国語の本その１０', '国語十郎', '国語出版');
insert into books values (1011, '算数の本その１', '算数一郎', '算数出版');
insert into books values (1012, '算数の本その２', '算数次郎', '算数出版');
insert into books values (1013, '算数の本その３', '算数三郎', '算数出版');
insert into books values (1014, '算数の本その４', '算数四郎', '算数出版');
insert into books values (1015, '算数の本その５', '算数五郎', '算数出版');
insert into books values (1016, '算数の本その６', '算数六郎', '算数出版');
insert into books values (1017, '算数の本その７', '算数七郎', '算数出版');
insert into books values (1018, '算数の本その８', '算数八郎', '算数出版');
insert into books values (1019, '算数の本その９', '算数九郎', '算数出版');
insert into books values (1020, '算数の本その１０', '算数十郎', '算数出版');
insert into books values (1021, '社会の本その１', '社会一郎', '社会出版');
insert into books values (1022, '社会の本その２', '社会次郎', '社会出版');
insert into books values (1023, '社会の本その３', '社会三郎', '社会出版');
insert into books values (1024, '社会の本その４', '社会四郎', '社会出版');
insert into books values (1025, '社会の本その５', '社会五郎', '社会出版');
insert into books values (1026, '社会の本その６', '社会六郎', '社会出版');
insert into books values (1027, '社会の本その７', '社会七郎', '社会出版');
insert into books values (1028, '社会の本その８', '社会八郎', '社会出版');
insert into books values (1029, '社会の本その９', '社会九郎', '社会出版');
insert into books values (1030, '社会の本その１０', '社会十郎', '社会出版');
insert into books values (1031, '理科の本その１', '理科一郎', '理科出版');
insert into books values (1032, '理科の本その２', '理科次郎', '理科出版');
insert into books values (1033, '理科の本その３', '理科三郎', '理科出版');
insert into books values (1034, '理科の本その４', '理科四郎', '理科出版');
insert into books values (1035, '理科の本その５', '理科五郎', '理科出版');
insert into books values (1036, '理科の本その６', '理科六郎', '理科出版');
insert into books values (1037, '理科の本その７', '理科七郎', '理科出版');
insert into books values (1038, '理科の本その８', '理科八郎', '理科出版');
insert into books values (1039, '理科の本その９', '理科九郎', '理科出版');
insert into books values (1040, '理科の本その１０', '理科十郎', '理科出版');
insert into books values (1041, '英語の本その１', '英語一郎', '英語出版');
insert into books values (1042, '英語の本その２', '英語次郎', '英語出版');
insert into books values (1043, '英語の本その３', '英語三郎', '英語出版');
insert into books values (1044, '英語の本その４', '英語四郎', '英語出版');
insert into books values (1045, '英語の本その５', '英語五郎', '英語出版');
insert into books values (1046, '英語の本その６', '英語六郎', '英語出版');
insert into books values (1047, '英語の本その７', '英語七郎', '英語出版');
insert into books values (1048, '英語の本その８', '英語八郎', '英語出版');
insert into books values (1049, '英語の本その９', '英語九郎', '英語出版');
insert into books values (1050, '英語の本その１０', '英語十郎', '英語出版');
insert into keywords values (1003, '国語三郎');
insert into keywords values (1013, '算数三郎');
insert into keywords values (1023, '社会三郎');
insert into keywords values (1033, '理科三郎');
insert into keywords values (1043, '英語三郎');

この「TestTable.sql」と「TestData.sql」の２つのファイルをDatabaseManagerの「File メニュー」-->「Open Script」で指定します。

まずは「TestTable.sql」ファイルを選択して、右上の「Execute」ボタンを押します。
ファイルに書かれたSQLが実行され、下の余白に結果が表示されます。

次に「TestData.sql」ファイルを選択して、「Execute」ボタンを押します。

これでテスト用のテーブルとデータが揃いました。

まず、一般のデータベース・ユーザーとして、データベース利用の基本であるデータの検索をして見ましょう。
SQLでは、検索には、select文を用いるのですが、その基本形は次のような形をしています。

select　　　　項目リスト
　　　　from　　　　テーブルのリスト
　　　　where　　　　検索条件;

命令文の最後はセミコロン（；）を必ず記述します。

ここでもう一度、リレーショナル・データベースの基本的な構成を振り返ってみます。
1.「データベース」は、「テーブル」から構成されます。
2.「テーブル」は、「行」から構成されます。
3.「行」は、「項目（カラム）」から構成されます。

こうした構成を反映して、リレーショナル・データベースでの検索は、検索すべき項目をfrom句で指定したテーブルの中から選び出すという形をとります。
ここで項目リストに現れる項目は、必ずリスト中のテーブルに含まれねばなりません。

具体的なselectの実例を見てみましょう。

これは「books」テーブルからの検索例です。
この「books」テーブルは、「id」「title」「author」「publisher」という４つの項目から出来ています。

【例１：項目リストの順番】

select id, title, author, publisher from books;

次の例では、「books」テーブルの全ての行から、３つの項目が選ばれて表示されます。
項目名のリストが、コンマ（,）で区切られていることに注意しましょう。
項目名の順序に意味があることは、例１のselect文の出力と比較してみれば分かります。

【例２：項目リストの順番】

select publisher, title, id from books;

select文での行の出力は、元のテーブル内での項目の順番とは独立に、項目リストでの項目名の順番に従います。

このようにselect文での項目リストは、元のテーブルから特定の項目を抜き出す働きをしています。
大事なことは、select文の出力が項目リストで指定された項目からなる行の集まりであることです。
このことはselect文の出力が、再び新しいテーブルを構成していることを意味しています。

次の例では、項目名のリストが省略され、「＊」が指定されています。
この指定の意味は、出力結果を見れば明らかです。
すなわちテーブルの全項目が、元のテーブルの項目順に指定されたのと同じ結果が得られています。

【例３: select】

select * from books;

次は各項目をカンマ（，)で区切ります。
例３と同じ結果を得ることが出来ます。

【例 4: 全項目の指定】

select id, title, author, publisher from books;

これまでの出力を見れば、selectの出力では最初に見出しが表示されていることがわかります。
デフォルトで見出しには項目名が使われるのですが、この見出しを変更することも可能です。

次の例と例１の出力と比べれば、出力の見出しが「id」「title」「author」「publisher」から「番号」「書籍名」「著者」「出版社」と変わっているのがわかります。
見出しを変更するには、select文の項目リストを少し変形して、項目名の後ろに空白文字で区切り、対応する見出し名を置き、この項目名と見出し名のペアをカンマで区切って並べればよいです。

【例５: 見出しの変更】

select id　番号, title 書籍名, author 著者, publisher 出版社 from books;

見出しを変えるだけでなく、select文の出力の行内に文字列を表示したいこともあります。
こうした時には、次のように項目リスト内に文字列を置くことが出来ます。

【例６: 行内への文字列の表示】

select '番号=',id, '書籍名=',title, '著者=',author, '出版社=',publisher from books;

SQLでは、項目リストに、関数や四則演算を含んだ式を置くことができます。
この時、項目は他のプログラム言語での「変数」と同じような働きをする事になります。
「books」テーブルでは数値のフィールドがないので、ここでは別のテーブルを準備して計算例を見てみることにします。

【TestTable2.sql】

create table books2 (
    id            int,
    title         varchar(50),
    price        int,
    qty     	int,
    constraint pk_books2 primary key(id)
);

【TestData2.sql】

insert into books2 values (1001,'国語の本その１',100,15);
insert into books2 values (1002,'国語の本その２',200,30);
insert into books2 values (1003,'国語の本その３',300,45);
insert into books2 values (1004,'国語の本その４',400,90);
insert into books2 values (1005,'国語の本その５',500,20);
insert into books2 values (1006,'国語の本その６',150,40);
insert into books2 values (1007,'国語の本その７',300,60);
insert into books2 values (1008,'国語の本その８',120,75);
insert into books2 values (1009,'国語の本その９',250,12);
insert into books2 values (1010,'国語の本その１０',1000,5);
insert into books2 values (1011,'価格不明の本',null,25);

次の例のselect文の最後「price * qty」に注目してください。
これは同じ行の中の本の定価に、売上部数を掛けたものです。
結果の出力は見出し無しですが、新しい項目が作られていることが分かります。

【例７: 式の利用１】

select id, title, price, qty, price*qty from books2;

この例では「 price * qty」でしたが、次に見るように定数やかっこを含む式を自由にselect文の項目リスト中に書くことが出来ます。

price * ( qty - 1 ) + 100
qty * qty + 2 * qty + 1
price - qty
( price - qty ) * 100 / qty

先の例では見出しはデフォルトの項目名が使われていたため、演算で出来た新しい項目には見出しがありませんでしたが、例５の方法を用いると、計算の結果できた項目に見出しを与えることが出来ます。

【例８: 式の利用２】

select id　番号, title　書籍名, price　価格, qty　部数, price*qty 総売上 from books2;

二つのテーブルの「積」という演算を定義してみましょう。
項目 a1 , a2 からなるテーブルＡと、項目 b1 , b2 , b3 からなるテーブルＢがあったとします。
いま単純化して、テーブルＡとＢには下の図式で表されるようなデータが入っており、それぞれ２行と３行からなっているとします。

テーブルA		テーブルB
a1	a2	b1	b2	b3
10	01	a	1	x
20	02	b	2	y
		c	3	z

こうした時、２つのテーブルの「積」Ａ×Ｂは、次のように作られます。

まず、テーブルＡの第一行を取り出し、その一行と、テーブルＢの全ての行を組み合わせて新しい行を作ります。
次に、テーブルＡの第二行目と、テーブルＢの全ての行を組み合わせて行を作ります。
こうした操作を、テーブルＡの行が無くなるまで繰り返します。
先のテーブルＡ、Ｂの例では、Ａ×Ｂは、次のようになります。