Powered by SmartDoc

SQL

リレーショナルデータベースでは、SQLというデータベース問い合わせ言語を用いてテーブルにアクセスします。ここでは、このSQLについて解説します。

SQLには、いくつかのバージョンがあります。ここでは、SQL92というバージョンのSQLを中心に解説します。ほかには、SQL89, SQL99といったバージョンがあります。

テーブルの作成 (1) 基本パターン

テーブルを作成するには、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)
);

このテーブルは、表B.1[テーブル books に含まれる項目]のような項目を持っています。

テーブル books に含まれる項目
項目名 意味
ndc 図書の分類番号
tyosya_hyouji 著者表示
id 登録番号(1冊ごとに異なる)
title タイトル
author 著者
publisher 出版社

このcreate table文の最後の方に"constraint pk_books primary key(id)"という文字列があります。この意味については、後で解説します。

テーブルの作成 (2) データ型

SQLでは、さまざまなデータ型が定義されています。表B.2[代表的なデータ型]に、代表的なデータ型を示しましょう。

代表的なデータ型
char(n) 固定長の文字列(nは文字列の長さ)
varchar(n) 可変長の文字列(nは最大文字数)
int 符号付き整数
float(p) 浮動小数点(pは精度)
date 日付
time 時刻

リレーショナルデータベースでは、上に挙げたようなデータ型に加えて、製品ごとに独自のデータ型が定義されています。例えばPostgreSQLでは、最大文字数を指定する必要がない"text"という文字列データ型が定義されています。

表B.3[テーブル books に含まれる項目とデータ型]は、先に作成したテーブルの項目名とデータ型の対応です。

テーブル books に含まれる項目とデータ型
項目名 データ型
ndc varchar
tyosya_hyouji varchar
id int
title varchar
author varchar
publisher varchar

テーブルの作成 (3) 制約

テーブルを作成するときに、項目にさまざまな条件をつけられます。例えば、「この項目には、正の整数しか入らない」といった条件を指定できます。こうした条件を「制約」と呼びます。制約には、ひとつの列に制約を加える「列制約」と、複数の列に同時に制約を加える「テーブル制約」があります。

主キー制約

主キーを指定するには、テーブルを作成するときに、項目に"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 制約

not null 制約は、nullを許さない制約です。nullとは、項目にデータが入らない状態のことを言います。not null制約が指定された項目には、何らかのデータが入らなければいけません。

create table books (
    .....
    title  varchar(50) not null,
    .....
);

unique 制約

unique 制約は、データの重複を許さない制約です。

create table students (
    .....
    email  varchar(50) unique,
    .....
);

この例では、unique制約により、studentsテーブルのe-mailという項目は、すべて違ったデータでなければいけません。

check 制約

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;

データの検索 (1) 基本パターン

検索をするには、select 文を使います。select文では、from句で指定したテーブルの中から、必要な項目を選び出します。検索条件が必要なときには、where句を指定します。

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

データの検索 (2) 項目の選択

最初の例は、テーブルbooks中のすべてのデータから、titleという項目を取り出すものです。

select title from books;
(出力例)
TITLE
--------------------------------------------------
情報メディア論
リアリティの社会学
社会理論のリアリティ
裸になったサラリーマン
伽藍とバザール
(以下略)

次に、テーブルbooks中のすべてのデータから、idとtitleという2つの項目を取り出す例です。項目名のリストは、コンマ(,)で区切ります。

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          |リアリティの社会学
           |張江洋直 他著                                           |八千代出版
(以下略)

データの検索 (3) 検索条件の指定

これまでの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未満である」という2つの条件をともに満たしているものが検索されます。

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                               |鈴木正美 
著                                            |群像社

like とワイルドカード

先の例で、publisherが「八千代出版」であるものを検索してみました。この場合、publisherのデータが「八千代出版」に完全に一致するものだけが検索されます。もし「八千代出版会」というデータがあっても、そのデータは検索されません。

ただ、検索をする場合には、「タイトルに"Java"という文字を含む本」といった指定をすることもあります。SQLでこうした処理を行うには、like 演算子とともに、「ワイルドカード」を用います。

ワイルドカード
ワイルドカード 意味
% 任意の長さ(ゼロを含む)の文字列
_ 任意の1文字

実例をみてみましょう。

最初の例は、テーブル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入門
(以下略)

データの検索 (4) テーブルの結合

前回説明したような表B.7[「図書」を示すテーブル]表B.8[「図書のキーワード」を示すテーブル]のようなテーブルがあったとします。

「図書」を示すテーブル
number title author publisher
42377 伽藍とバザール 山形浩生 訳 光芒社
42872 Java言語で学ぶデザインパターン入門 結城浩 著 ソフトパンク
25955 Javaプログラミング実践講座・アプレット作成編 植田龍男 著 アスキー出版局
「図書のキーワード」を示すテーブル
number keyword
42377 オープンソース
42872 Java
42872 デザインパターン
25955 Java
25955 Javaアプレット

本のタイトルとキーワードを組み合わせて表示させるとしましょう。そのためには、この2つのテーブルを組み合わせる必要があります。これを「結合(JOIN)」と言います。

まず、複数のテーブルを使う場合、項目名の指定は次のような形になります。

テーブル名.項目名

例えば、booksというテーブルのidという項目は、次のように指定します。

books.id

この2つのテーブルは、それぞれidという項目を持っています。この2つの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が一致しているものを選んでいることがわかります。

SQL92からのSQLでは、次のようにも書けます。

select books.title, keywords.keyword
from books join keywords
on books.id = keywords.id;

この例では、booksテーブルのid項目とkeywordsテーブルのid項目が一致しているものを選んでいます。それぞれの項目名はidです。このように、2つのテーブルで項目名が一致している場合、次のようにも書けます。ただし、この例はHSQLDBでは動きません。

select books.title, keywords.keyword
from books join keywords
using(id);

データの登録

データを登録するには、insert 文を使います。デーブルに、1行分のデータを登録します。

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'である本のデータを削除しています。

参考文献・URL

UNIXデータベース入門
はじめてでもわかるSQLとデータ設計