学生向けプログラミング入門

学生向けにプログラミングを解説。Java、C++、Ruby、PHP、データベース

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

<<前  [TOP]  次>>


データベースにサンプルのテーブルを作成します。
以下の内容を2つのテキストファイルにコピーし、それに拡張子「.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, '国語の本その1', '国語一郎', '国語出版');
insert into books values (1002, '国語の本その2', '国語次郎', '国語出版');
insert into books values (1003, '国語の本その3', '国語三郎', '国語出版');
insert into books values (1004, '国語の本その4', '国語四郎', '国語出版');
insert into books values (1005, '国語の本その5', '国語五郎', '国語出版');
insert into books values (1006, '国語の本その6', '国語六郎', '国語出版');
insert into books values (1007, '国語の本その7', '国語七郎', '国語出版');
insert into books values (1008, '国語の本その8', '国語八郎', '国語出版');
insert into books values (1009, '国語の本その9', '国語九郎', '国語出版');
insert into books values (1010, '国語の本その10', '国語十郎', '国語出版');
insert into books values (1011, '算数の本その1', '算数一郎', '算数出版');
insert into books values (1012, '算数の本その2', '算数次郎', '算数出版');
insert into books values (1013, '算数の本その3', '算数三郎', '算数出版');
insert into books values (1014, '算数の本その4', '算数四郎', '算数出版');
insert into books values (1015, '算数の本その5', '算数五郎', '算数出版');
insert into books values (1016, '算数の本その6', '算数六郎', '算数出版');
insert into books values (1017, '算数の本その7', '算数七郎', '算数出版');
insert into books values (1018, '算数の本その8', '算数八郎', '算数出版');
insert into books values (1019, '算数の本その9', '算数九郎', '算数出版');
insert into books values (1020, '算数の本その10', '算数十郎', '算数出版');
insert into books values (1021, '社会の本その1', '社会一郎', '社会出版');
insert into books values (1022, '社会の本その2', '社会次郎', '社会出版');
insert into books values (1023, '社会の本その3', '社会三郎', '社会出版');
insert into books values (1024, '社会の本その4', '社会四郎', '社会出版');
insert into books values (1025, '社会の本その5', '社会五郎', '社会出版');
insert into books values (1026, '社会の本その6', '社会六郎', '社会出版');
insert into books values (1027, '社会の本その7', '社会七郎', '社会出版');
insert into books values (1028, '社会の本その8', '社会八郎', '社会出版');
insert into books values (1029, '社会の本その9', '社会九郎', '社会出版');
insert into books values (1030, '社会の本その10', '社会十郎', '社会出版');
insert into books values (1031, '理科の本その1', '理科一郎', '理科出版');
insert into books values (1032, '理科の本その2', '理科次郎', '理科出版');
insert into books values (1033, '理科の本その3', '理科三郎', '理科出版');
insert into books values (1034, '理科の本その4', '理科四郎', '理科出版');
insert into books values (1035, '理科の本その5', '理科五郎', '理科出版');
insert into books values (1036, '理科の本その6', '理科六郎', '理科出版');
insert into books values (1037, '理科の本その7', '理科七郎', '理科出版');
insert into books values (1038, '理科の本その8', '理科八郎', '理科出版');
insert into books values (1039, '理科の本その9', '理科九郎', '理科出版');
insert into books values (1040, '理科の本その10', '理科十郎', '理科出版');
insert into books values (1041, '英語の本その1', '英語一郎', '英語出版');
insert into books values (1042, '英語の本その2', '英語次郎', '英語出版');
insert into books values (1043, '英語の本その3', '英語三郎', '英語出版');
insert into books values (1044, '英語の本その4', '英語四郎', '英語出版');
insert into books values (1045, '英語の本その5', '英語五郎', '英語出版');
insert into books values (1046, '英語の本その6', '英語六郎', '英語出版');
insert into books values (1047, '英語の本その7', '英語七郎', '英語出版');
insert into books values (1048, '英語の本その8', '英語八郎', '英語出版');
insert into books values (1049, '英語の本その9', '英語九郎', '英語出版');
insert into books values (1050, '英語の本その10', '英語十郎', '英語出版');
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」の2つのファイルを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」という4つの項目から出来ています。


【例1:項目リストの順番】

select id, title, author, publisher from books;




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


【例2:項目リストの順番】

select publisher, title, id from books;




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


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


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


【例3: select】

select * from books;




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


【例 4: 全項目の指定】

select id, title, author, publisher from books;




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


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


【例5: 見出しの変更】

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




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


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

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,'国語の本その1',100,15);
insert into books2 values (1002,'国語の本その2',200,30);
insert into books2 values (1003,'国語の本その3',300,45);
insert into books2 values (1004,'国語の本その4',400,90);
insert into books2 values (1005,'国語の本その5',500,20);
insert into books2 values (1006,'国語の本その6',150,40);
insert into books2 values (1007,'国語の本その7',300,60);
insert into books2 values (1008,'国語の本その8',120,75);
insert into books2 values (1009,'国語の本その9',250,12);
insert into books2 values (1010,'国語の本その10',1000,5);
insert into books2 values (1011,'価格不明の本',null,25);



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


【例7: 式の利用1】

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


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


【例8: 式の利用2】

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




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

テーブルAテーブルB
a1a2b1b2b3
1001a1x
2002b2y
c3z
こうした時、2つのテーブルの「積」A×Bは、次のように作られます。


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


明らかにA×Bは、新しいテーブルです。
一般にテーブルAがa個の行からなり、テーブルBがb個の行からなる時、テーブルA×Bはa×b個の行からなります。


select を使えば、テーブルAの内容は

select * from A; 



で見ることが出来るし、テーブルBの内容は、

select * from B;



で表示されます。
この時、テーブルA×Bの内容は、次のselect文で得られます。

select * from A , B;



これまでは、from句には、単一のテーブルしか現れてきませんでしたが、この例のように、コンマ(,)で区切って複数のテーブル名を指定することが出来ます。

select * from books, books2;




<<前  [TOP]  次>>