リレーショナルデータベースでは、SQLというデータベース問い合わせ言語を用いてテーブルにアクセスします。ここでは、このSQLについて解説します。
テーブルを作成するには、create table 文を使います。テーブルには、項目名(column name)とデータ型(data type)のペアを、複数指定します。ここではデータの入れ物を作成するだけで、データそのものは登録されません。データの登録は、後で解説するinsert文で行います。
create table テーブル名 (
項目名 データ型,
項目名 データ型,
.....
.....
);
例
create table books (
ndc varchar(10),
tyosya_hyouji varchar(2),
id int,
title varchar(50),
author varchar(50),
publisher varchar(30),
constraint pk_books primary key(id)
);
このテーブルは、表[テーブル books に含まれる項目]のような項目を持っています。
| 項目名 | 意味 |
|---|---|
| ndc | 図書の分類番号 |
| tyosya_hyouji | 著者表示 |
| id | 登録番号(1冊ごとに異なる) |
| title | タイトル |
| author | 著者 |
| publisher | 出版社 |
このcreate table文の最後の方に"constraint pk_books primary key(id)"という文字列があります。この意味については、後で解説します。
SQLでは、さまざまなデータ型が定義されています。表[代表的なデータ型]に、代表的なデータ型を示しましょう。
| char(n) | 固定長の文字列(nは文字列の長さ) |
| varchar(n) | 可変長の文字列(nは最大文字数) |
| int | 符号付き整数 |
| float(p) | 浮動小数点(pは精度) |
| date | 日付 |
| time | 時刻 |
リレーショナルデータベースでは、上に挙げたようなデータ型に加えて、製品ごとに独自のデータ型が定義されています。例えばPostgreSQLでは、最大文字数を指定する必要がない"text"という文字列データ型が定義されています。
表[テーブル books に含まれる項目とデータ型]は、先に作成したテーブルの項目名とデータ型の対応です。
| 項目名 | データ型 |
|---|---|
| ndc | varchar |
| tyosya_hyouji | varchar |
| id | int |
| title | varchar |
| author | varchar |
| publisher | varchar |
テーブルを作成するときに、項目にさまざまな条件をつけられます。例えば、「この項目には、正の整数しか入らない」といった条件を指定できます。こうした条件を「制約」と呼びます。制約には、ひとつの列に制約を加える「列制約」と、複数の列に同時に制約を加える「テーブル制約」があります。
主キーを指定するには、テーブルを作成するときに、項目に"primary key"というキーワードを付けます。これを主キー制約と言います。
create table books (
ndc varchar(10),
tyosya_hyouji varchar(2),
id int primary key,
title varchar(50),
author varchar(50),
publisher varchar(30)
);
テーブル制約も使えます。主キーが複数あるときに便利です。
create table books (
ndc varchar(10),
tyosya_hyouji varchar(2),
id int,
title varchar(50),
author varchar(50),
publisher varchar(30),
primary key (id)
);
not null 制約は、nullを許さない制約です。nullとは、項目にデータが入らない状態のことを言います。not null制約が指定された項目には、何らかのデータが入らなければいけません。
create table books (
.....
title varchar(50) not null,
.....
);
unique 制約は、データの重複を許さない制約です。
create table students (
.....
email varchar(50) unique,
.....
);
この例では、unique制約により、studentsテーブルのe-mailという項目は、すべて違ったデータでなければいけません。
check 制約では、指定した論理式についてチェックします。
create table books (
.....
id int check (0 <= id and id < 100000),
.....
);
この例では、idは0以上100000未満の値でなければいけません。
制約には「制約名」をつけられます。制約名をつけることで、エラー処理がわかりやすくなります。制約名をつけるには、constraint句を指定します。
次の例では、項目idの主キー制約に、pk_booksという制約名をつけています。
create table books (
ndc varchar(10),
tyosya_hyouji varchar(2),
id int constraint pk_books primary key,
title varchar(50),
author varchar(50),
publisher varchar(30)
);
テーブル制約に制約名をつけることもできます。
create table books (
ndc varchar(10),
tyosya_hyouji varchar(2),
id int,
title varchar(50),
author varchar(50),
publisher varchar(30),
constraint pk_books primary key (id),
constraint check_id check (0 <= id and id < 100000)
);
外部キーを設定するには、テーブル制約を利用するのが一般的です。
create table テーブル名 (
項目名 データ型
.....
constraint 制約名 foreign key(項目名)
references 参照テーブル名(項目名)
)
例
create table books (
id int constraint pk_books primary key,
.....
.....
);
create table keywords (
id int,
keyword varchar(30),
constraint fk_id foreign key(id) references books(id)
);
また、列制約も利用できます。
create table テーブル名 (
項目名 データ型 references 参照テーブル名(項目名),
.....
)
例
create table books (
id int constraint pk_books primary key,
.....
.....
);
create table keywords (
id int references books(id),
keyword varchar(30)
);
テーブルを削除するには、drop table 文を使います。
drop table テーブル名
例 drop table books;
検索をするには、select 文を使います。select文では、from句で指定したテーブルの中から、必要な項目を選び出します。検索条件が必要なときには、where句を指定します。
select 項目のリスト from テーブルのリスト where 検索条件
最初の例は、テーブルbooks中のすべてのデータから、titleという項目を取り出すものです。
select title from books;
(出力例) TITLE -------------------------------------------------- 情報メディア論 リアリティの社会学 社会理論のリアリティ 裸になったサラリーマン 伽藍とバザール (以下略)
次に、テーブルbooks中のすべてのデータから、idとtitleという２つの項目を取り出す例です。項目名のリストは、コンマ(,)で区切ります。
select id, title from books;
(出力例) ID |TITLE ------------------------------------------------------------------ 41439 |情報メディア論 43148 |リアリティの社会学 41984 |社会理論のリアリティ 42047 |裸になったサラリーマン 42377 |伽藍とバザール (以下略)
最後に、テーブルbooks中のすべてのデータから、すべての項目を取り出す例です。*は、すべての項目を意味します。
select * from books;
(出力例)
NDC |TYO&|ID |TITLE
|AUTHOR |PUBLISHER
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----
361 |M |41439 |情報メディア論
|丸山不二夫 編著 |八千代出版
304 |H |43148 |リアリティの社会学
|張江洋直 他著 |八千代出版
(以下略)
これまでのselect文の使い方では、項目を選び出すことはできても、必要な行だけを残すことはできませんでした。
必要な行だけを残すためには、where句を使って検索条件を指定します。
検索条件を指定するために、次のような比較演算子が利用できます。
| 比較演算子 | 意味 |
|---|---|
| = | 等しい |
| > | 大きい |
| < | 小さい |
| >= | 大きいか、あるいは、等しい |
| <= | 小さいか、あるいは、等しい |
| != | 等しくない |
| <> | 等しくない |
最初の例は、テーブルbooks中のすべてのデータから、登録番号(id)が30000未満であるものを検索して、idとtitleを出力するものです。
select id, title from books where id < 30000;
(実行例) ID |TITLE ------------------------------------------------------------------ 25955 |Javaプログラミング実践講座・アプレット作成編
次に、テーブルbooks中のすべてのデータから、出版社が「八千代出版」であるものを検索して、titleとpublisherを出力するものです。文字列は、シングルクォート(')か、ダブルクォート(")で囲みます。
select title, publisher from books where publisher = '八千代出版';
(実行例) TITLE |PUBLISHER -------------------------------------------------------------------- 情報メディア論 |八千代出版 リアリティの社会学 |八千代出版 社会理論のリアリティ |八千代出版
論理演算子を使うと、複数の条件を組み合わせることができます。
| 論理演算子 | 意味 |
|---|---|
| and | いずれも真ならば真 |
| or | いずれかが真ならば真 |
最初の例は、テーブルbooks中のすべてのデータから、登録番号(id)が42000以上で、かつ43000未満であるものを検索して、idとtitleを出力するものです。「idが42000以上である」ことと「idが43000未満である」という２つの条件をともに満たしているものが検索されます。
select id, title from books where 42000 <= id and id < 43000;
(実行例) ID |TITLE ------------------------------------------------------------------ 42047 |裸になったサラリーマン 42377 |伽藍とバザール 42872 |Java言語で学ぶデザインパターン入門 (以下略)
上の例は、次のようにも書けます。
select id, title from books where between 42000 and 43000;
次の例は、テーブルbooks中のすべてのデータから、出版社が「群像社」か「八千代出版」のいずれかであるものを検索して、titleとauthor、publisherを出力するものです。
select title, author, publisher from books where publisher = '群像社' or publisher = '八千代出版';
(出力例)
TITLE |AUTHOR
|PUBLISHER
------------------------------------------------------------------------------------------------------------------------------------
情報メディア論 |丸山不二夫 編著
|八千代出版
リアリティの社会学 |張江洋直 他著
|八千代出版
社会理論のリアリティ |張江洋直 著
|八千代出版
言葉の建築術 マンデリシュターム研究1 |鈴木正美
著 |群像社
先の例で、publisherが「八千代出版」であるものを検索してみました。この場合、publisherのデータが「八千代出版」に完全に一致するものだけが検索されます。もし「八千代出版会」というデータがあっても、そのデータは検索されません。
ただ、検索をする場合には、「タイトルに"Java"という文字を含む本」といった指定をすることもあります。SQLでこうした処理を行うには、like 演算子とともに、「ワイルドカード」を用います。
| ワイルドカード | 意味 |
|---|---|
| % | 任意の長さ（ゼロを含む）の文字列 |
| _ | 任意の１文字 |
実例をみてみましょう。
最初の例は、テーブルbooks中のすべてのデータから、タイトルが「Java」という言葉ではじまっているデータを検索して、titleを出力するものです。この例では、「Java」の後に「任意の長さの文字列」が続くので、「Java」ではじまる文字列を検索できます。
select title from books where title like 'Java%';
(出力例) TITLE -------------------------------------------------- Java言語で学ぶデザインパターン入門 Javaプログラミング実践講座・アプレット作成編 Javaサーブレットプログラミング 第2版 Java&XML 第2版 JavaRMI Javaによるパーサ構築技法 JavaScriptプログラミング入門 Javaネットワークプログラミング 第2版 Javaプログラミングクイックリファレンス 第2版 Javaメッセージサービス Javaセキリュティ
次の例は、テーブルbooks中のすべてのデータから、タイトルに「Java」という言葉を含むデータを検索して、titleを出力するものです。この例では、「Java」の前後に「任意の長さの文字列」があるので、「Java」という文字がtitle中のどの位置にあっても良いのです。
select title from books where title like '%Java%';
(出力例) TITLE -------------------------------------------------- Java言語で学ぶデザインパターン入門 Javaプログラミング実践講座・アプレット作成編 Javaサーブレットプログラミング 第2版 Java&XML 第2版 JavaRMI まるごと図解 最新サーバーサイドJavaがわかる Javaによるパーサ構築技法 MIDP Javaゲームプログラミング (以下略)
最後の例は、テーブルbooks中のすべてのデータから、タイトルに「Java」という言葉か、あるいは「Perl」という言葉を含むデータを検索して、titleを出力するものです。
select title from books where title like '%Java%' or title like '%Perl%';
(出力例) TITLE -------------------------------------------------- Java言語で学ぶデザインパターン入門 Javaプログラミング実践講座・アプレット作成編 Javaサーブレットプログラミング 第2版 Java&XML 第2版 JavaRMI MySQL&PerlWebアプリケーション開発 Perlデバッグ シェル&Perl入門 (以下略)
前回説明したような表[「図書」を示すテーブル]と表[「図書のキーワード」を示すテーブル]のようなテーブルがあったとします。
| number | title | author | publisher |
|---|---|---|---|
| 42377 | 伽藍とバザール | 山形浩生 訳 | 光芒社 |
| 42872 | Java言語で学ぶデザインパターン入門 | 結城浩 著 | ソフトパンク |
| 25955 | Javaプログラミング実践講座・アプレット作成編 | 植田龍男 著 | アスキー出版局 |
| number | keyword |
|---|---|
| 42377 | オープンソース |
| 42872 | Java |
| 42872 | デザインパターン |
| 25955 | Java |
| 25955 | Javaアプレット |
本のタイトルとキーワードを組み合わせて表示させるとしましょう。そのためには、この２つのテーブルを組み合わせる必要があります。これを「結合」と言います。
まず、複数のテーブルを使う場合、項目名の指定は次のような形になります。
テーブル名.項目名
例えば、booksというテーブルのidという項目は、次のように指定します。
books.id
この２つのテーブルは、それぞれidという項目を持っています。この２つのidが一致しているもの同士を組み合わせることによって、「42377というidを持つ『伽藍とバザール』という本は、『オープンソース』というキーワードを持っている」ことがわかります。
SQLでは、次のように記述します。
select books.title, keywords.keyword from books, keywords where books.id = keywords.id
(実行例) TITLE KEYWORD -------------------------------------------- --------- Javaプログラミング実践講座・アプレット作成編 Java Javaプログラミング実践講座・アプレット作成編 Javaアプレット 伽藍とバザール オープンソース Java言語で学ぶデザインパターン入門 Java Java言語で学ぶデザインパターン入門 デザインパターン
それぞれのテーブルのidが一致しているものを選んでいることがわかります。
データを登録するには、insert 文を使います。デーブルに、１行分のデータを登録します。
insert into テーブル名 values (値1, 値2, ...)
例 insert into books values ( '361', 'M', '41439', '情報メディア論', '丸山不二夫 編著', '八千代出版' );
values句には7つの項目が与えられています。これらの項目は、create table文での項目の順序通りに指定します。
テーブル上のデータを更新するには、update 文を使います。where句以降を省略すると、そのテーブルのすべての行が更新されます。
update テーブル名 set 項目名 = 式 where 更新条件
例 update books set author = '丸山不二夫 編著 / 植田龍男 ほか著' where id = '41439';
この例では、idが'41439'である本のauthorという項目を、「丸山不二夫 編著/植田龍男 ほか著」に変更しています。
テーブル上のデータを削除するには、delete 文を使います。where句以降を省略すると、そのテーブルのすべての行が削除されます。
delete from テーブル名 where 削除条件
例 delete from books where id = '41439';
この例では、idが'41439'である本のデータを削除しています。