SQL 実習

SQL (しーける) を使用する際、商用を含めたくさんのソフトウェアがあるが、本学では、無償で利用でき、かつ、完全なソースコードも提供している PostgresQL (ぽすとぐれしーける) で SQL の実習を行う。

前回学んだ DDL 文を実際に PostgresQL を使って、ひとつひとつ試してみよう。

実際にデータを使って練習しよう。

データベース名 itdb にアクセスしよう。情報処理基礎論での練習ユーザ名を ipausers とする。

% ssh ta05001@roy
% psql -d itdb -U ipausers;                               [~]
psql (8.4.2)
Type "help" for help.
itdb=>

psql -d itdb -U アカウント名

データベース練習帳

練習用紙

関係表の例：キャラクタと番組
		属性
		フィールド 1「名前」	フィールド 2「番組名」	フィールド 3「性別」	フィールド 4「キャラクタ」
組	レコード 1	ブラサム	パワパフガールズ	女	善
	レコード 2	バブルス	パワパフガールズ	女	善
	レコード 3	バタカップ	パワパフガールズ	女	善
	レコード 4	モジョジョジョ	パワパフガールズ	男	悪
	レコード 5	ミス・キーン	パワパフガールズ	女	善
	レコード 6	メイヨール	パワパフガールズ	男	善
	レコード 7	プロフェッサ	パワパフガールズ	男	善
	レコード 8	ジャック	サムライジャック	男	善
	レコード 9	アク	サムライジャック	男	悪

住んでいる町と趣味・特技
		属性
		フィールド 1「名前」	フィールド 2「住んでいる町」	フィールド 3「趣味・特技」
組	レコード 1	ブラサム	タウンズビル	新しい服
	レコード 2	バブルス	タウンズビル	動物と話す
	レコード 3	バタカップ	タウンズビル	ケンカ
	レコード 4	モジョジョジョ	タウンズビル	発明
	レコード 5	ジャック	未来世界	剣
	レコード 6	デクスタ	ラボ	発明
	レコード 7	ディディ	ラボ	バレエ

番組ストーリー
		属性
		フィールド 1「番組名」	フィールド 2「概要」
組	レコード 1	パワパフガールズ	3 人の女の子たちのお話
	レコード 2	サムライジャック	未来世界の侍の話
	レコード 3	デクスタ研究所	姉と弟の兄弟げんかの話
	レコード 4	快適な生活	電気会社の CM 集
	レコード 5	ウォレスとグルミット	犬と発明家の話

マラソン順位
		属性
		名前	順位
組	レコード 1	カウ	1
	レコード 2	チキン	3
	レコード 3	アル	4
	レコード 4	フレム	2
	レコード 5	レッドガイ	5

100 m 走順位
		属性
		名前	順位
組	レコード 1	ウィゼル	1
	レコード 2	カウ	3
	レコード 3	チキン	2
	レコード 4	レッドガイ	6
	レコード 5	フレム	4
	レコード 6	アル	4

問屋一覧
		属性
		問屋コード	店名	所在地
組	レコード 1	220	ウニクロ	千葉県浦安市
組	レコード 2	221	ハッサン	北海道札幌市

在庫一覧
		属性
		問屋コード	品物	色	在庫数
組	レコード 1	220	シャツ	グリーン	4
	レコード 2	220	タオル	ブラウン	2
	レコード 3	221	ジャケット	ブラック	15

データベースサーバへのログイン

データベースにログインするには、データベースサーバにログインする必要がある。本学のデータベースサーバは roy である。

データベースにアクセスするには

本学では、roy のサーバ上にインストールしてある。よって roy に ssh で接続する。

irsv{ta05001}% ssh roy
ta05001@roy's password:

パスワードを聞かれるので、入力する。

pan{ta05001}% ssh roy                                
Password:
Last login: Thu Dec 25 14:35:20 2008 from pan.e.koeki-u.ac.jp
NetBSD 4.0_STABLE (ROY.MP) #101: Tue Apr 22 20:26:57 JST 2008
roy{ta05001}%

roy の表示が出れば成功。次に、roy のデータベースに接続する。これから練習用に使うデータベース名は itdb である。ゲストアカウントで入ってみよう。接続に使うコマンドは psql である。

% psql -d データベース名 -U アカウント名

データベースにアクセスすると、

roy{ta05001}% psql -d itdb  -U アカウント名                                                     [~]
Welcome to psql 8.1.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
itdb=>

と出る。まずは英語を読み、help と quit を覚えよう。

練習用データベースの中に入っているもの

データベースには、今まで学んだ表

character (キャラクタと番組)
town (住んでいる町と趣味特技)
story (番組ストーリー)
marathon (マラソン順位)
hundred (100 m 走順位)
shop (問屋一覧)
item (在庫一覧)

が含まれている。

データベースからのログアウト

postgresql では、終了は \q である。

ta05001=> \q

データベースサーバからのログアウト

データベースからログアウトしても、まだ pan に接続している。


pan{ta05001}% ssh roy                                                        [~]
Password:
Last login: Thu Dec 25 18:46:27 2008 from pan.e.koeki-u.ac.jp
NetBSD 4.0_STABLE (ROY.MP) #101: Tue Apr 22 20:26:57 JST 2008
roy{ta05001}% exit                                                           [~]
roy への接続が閉じられました。
pan{ta05001}%                                                                [~]

関係データベース

関係データベースとは、データベースを 2 次元表形式にまとたもので、行（record [組]）と列（field [属性]）の表を作る。作られた表は関係表と呼ぶこともある。

関係表の例：キャラクタと番組
		属性
		フィールド 1「名前」	フィールド 2「番組名」	フィールド 3「性別」	フィールド 4「キャラクタ」
組	レコード 1	ブラサム	パワパフガールズ	女	善
	レコード 2	バブルス	パワパフガールズ	女	善
	レコード 3	バタカップ	パワパフガールズ	女	善
	レコード 4	モジョジョジョ	パワパフガールズ	男	悪
	レコード 5	ミス・キーン	パワパフガールズ	女	善
	レコード 6	メイヨール	パワパフガールズ	男	善
	レコード 7	プロフェッサ	パワパフガールズ	男	善
	レコード 8	ジャック	サムライジャック	男	善
	レコード 9	アク	サムライジャック	男	悪

各属性には、「名前」、「番組名」、「性別」、「キャラクタ」が記録されている。属性を鍵 (key) として、必要な情報を取り出していく。

候補キーとは、属性のことで、例えば「名前」フィールドや「性別」フィールドを探すときにこれらを指すために使われる。

演習5-1：候補キーを「キャラクタ」とする。それらのレコードを全て挙げよ。

主キーーとは、その中でも、フィールド内で重複のないものをさす。主キーをもとに、データを探すことになる。ここでは、重なっていない属性は「名前」フィールドのみである。

この他、他の関係表と照しあわせるときに用いるキーを外部キーと呼ぶ。

演習5-2：下の関係表と合せて何か情報を取り出したい(「参照する」という)。下の 2 つの関係表の属性を取り出すとき、上の関係表のどの属性が外部キーとして適切だろうか？

住んでいる町と趣味・特技
		属性
		フィールド 1「名前」	フィールド 2「住んでいる町」	フィールド 3「趣味・特技」
組	レコード 1	ブラサム	タウンズビル	新しい服
	レコード 2	バブルス	タウンズビル	動物と話す
	レコード 3	バタカップ	タウンズビル	ケンカ
	レコード 4	モジョジョジョ	タウンズビル	発明
	レコード 5	ジャック	未来世界	剣
	レコード 6	デクスタ	ラボ	発明
	レコード 7	ディディ	ラボ	バレエ

番組ストーリー
		属性
		フィールド 1「番組名」	フィールド 2「概要」
組	レコード 1	パワーパフガールズ	3 人の女の子たちのお話
	レコード 2	サムライジャック	未来世界の侍の話
	レコード 3	デクスタ研究所	姉と弟の兄弟げんかの話
	レコード 4	快適な生活	電気会社の CM 集
	レコード 5	ウォレスとグルミット	犬と発明家の話

関係表の操作について

何枚にもわたる資料の中から、必要なデータを取り出したり、組み合わせて分析することがある。関係表でも同じような動作を行うことができる。

集合演算

データの集まり (集合) について何か行動を起こす (演算する) ので、データを整理することを集合演算と呼ぶ。和演算、積演算、差演算の 3 つと、直積演算がある。

和演算： 2 つの関係表から、どちらか一方あるいはどちらにも含まれるデータを取り出す

少なくともどちらか一方に存在しているものを取り出すので、表は大きくなる。

数学でいう C = {A ∪ B} の集合のことである。

演習5-3：下の 2 つの関係表に和演算を施すとどうなるか。

マラソン順位
		属性
		名前	順位
組	レコード 1	カウ	1
	レコード 2	チキン	3
	レコード 3	アル	4
	レコード 4	フレム	2
	レコード 5	レッドガイ	5

100 m 走順位
		属性
		名前	順位
組	レコード 1	ウィゼル	1
	レコード 2	カウ	3
	レコード 3	チキン	2
	レコード 4	レッドガイ	6
	レコード 5	フレム	4
	レコード 6	アル	4

実際 100 m 走とマラソンの順位を一緒にしても無意味であるが、演算の練習のため行っている。

積演算(共通演算)： 2 つの関係表から、両方に含まれるデータを取り出す

どちらかに存在しているものを取り出すので、表は小さくなる。

数学でいう C = A ∩ B の集合のことである。

演習5-4：上の「マラソン順位」と「100 m 走順位」に積演算を施すとどうなるか。

差演算： 2 つの関係表から、どちらか一方にしか含まれないデータを取り出す

重なり合いが大きい場合、表は小さくなるが、重なり合いが小さければ、表は大きくなる。また、差演算はどちらの表を引くかで結果が異なる。

演習5-5：「マラソン順位」から「100 m 走順位」の差演算を施したものと、「100 m 走順位」から「マラソン順位」の差演算を施したものと結果を比較せよ。

直積演算

数学でいうC = A ⊗ B である。

具体的には 2 つの表を全て組み合わせたものを作る。以下の 2 つの表

問屋一覧
		属性
		問屋コード	店名	所在地
組	レコード 1	220	ウニクロ	千葉県浦安市
	レコード 2	221	ハッサン	北海道札幌市

と

在庫一覧
		属性
		問屋コード	品物	色	在庫数
組	レコード 1	220	シャツ	グリーン	4
	レコード 2	220	タオル	ブラウン	2
	レコード 3	221	ジャケット	ブラック	15

の直積は

		属性
		問屋コード	店名	所在地	品物	色	在庫数
組	レコード 1	220	ウニクロ	千葉県浦安市	シャツ	グリーン	4
	レコード 2	220	ウニクロ	千葉県浦安市	タオル	ブラウン	2
	レコード 3	221	ハッサン	北海道札幌市	ジャケット	ブラック	15

となる。

関係演算

一つあるいは複数の関係表のデータをフィールドやレコードを取り出して、新たに表を作ることである。射影、選択、結合の三種類がよく使われる。

射影 Projection：表からある属性を取り出す。

演習5-6：

上記の「キャラクタと番組」表から属性「名前」「番組名」を取り出せ。
上記の「キャラクタと番組」表から属性「名前」「性別」を取り出せ。
上記の「住んでいる町と趣味」表から属性「趣味・特技」「住んでいる町」を取り出せ。
上記の「番組ストーリー」表から属性「概要」を取り出せ。

選択 Selection：表からある行を取り出す。条件をつけることが多い。

演習5-7：

上記の「キャラクタと番組」表から属性「性別」が「女」であるレコードを取り出せ。
上記の「住んでいる町と趣味」表から属性「住んでいる町」が「タウンズビル」であるレコードを取り出せ。
上記の「100 m 走順位」表から属性「順位」が「1」「6」であるレコードを取り出せ。
上記の「マラソン順位」表から属性「順位」が「4」より小さいレコードを取り出せ。

結合：複数の表から条件を満たす行を取り出して新しい表を作る。

演習5-8：

上記の「キャラクタと番組」表から属性「名前」及び「性別」が「女」であるレコードを取り出し、上記の「住んでいる町と趣味」表から属性「名前」及び「住んでいる町」が「タウンズビル」であるレコードを取り出し、名前を主キーとして、使用した属性で新たな表を作ると、どのようなものになるか。

解答その 1

キャラクタと番組
		属性
		フィールド 1「名前」	フィールド 2「番組名」	フィールド 3「性別」	フィールド 4「キャラクタ」
組	レコード 1	ブラサム	パワパフガールズ	女	善
	レコード 2	バブルス	パワパフガールズ	女	善
	レコード 3	バタカップ	パワパフガールズ	女	善
	レコード 4	モジョジョジョ	パワパフガールズ	男	悪
	レコード 5	ミス・キーン	パワパフガールズ	女	善
	レコード 6	メイヨール	パワパフガールズ	男	善
	レコード 7	プロフェッサ	パワパフガールズ	男	善
	レコード 8	ジャック	サムライジャック	男	善
	レコード 9	アク	サムライジャック	男	悪

演習5-1：候補キーを「キャラクタ」とする。それらのレコードを全て挙げよ。

答え: "善" と "悪"

演習5-2：下の関係表と合せて何か情報を取り出したい(「参照する」という)。下の 2 つの関係表の属性を取り出すとき、上の関係表のどの属性が外部キーとして適切だろうか？

住んでいる町と趣味・特技
		属性
		フィールド 1「名前」	フィールド 2「住んでいる町」	フィールド 3「趣味・特技」
組	レコード 1	ブラサム	タウンズビル	新しい服
	レコード 2	バブルス	タウンズビル	動物と話す
	レコード 3	バタカップ	タウンズビル	ケンカ
	レコード 4	モジョジョジョ	タウンズビル	発明
	レコード 5	ジャック	未来世界	剣
	レコード 6	デクスタ	ラボ	発明
	レコード 7	ディディ	ラボ	バレエ

「住んでいる町と趣味・特技」表では、「名前」が一意に決まっているので、「名前」を外部キーとして使うとよい。

番組ストーリー
		属性
		フィールド 1「番組名」	フィールド 2「概要」
組	レコード 1	パワーパフガールズ	3 人の女の子たちのお話
	レコード 2	サムライジャック	未来世界の侍の話
	レコード 3	デクスタ研究所	姉と弟の兄弟げんかの話
	レコード 4	快適な生活	電気会社の CM 集
	レコード 5	ウォレスとグルミット	犬と発明家の話

「番組ストーリー」については、「番組名」が一意に決っているので、「番組名」を外部キーとして使うとよい。

演習5-3：下の 2 つの関係表に和演算を施すとどうなるか。

マラソン順位
		属性
		名前	順位
組	レコード 1	カウ	1
	レコード 2	チキン	3
	レコード 3	アル	4
	レコード 4	フレム	2
	レコード 5	レッドガイ	5

100 m 走順位
		属性
		名前	順位
組	レコード 1	ウィゼル	1
	レコード 2	カウ	3
	レコード 3	チキン	2
	レコード 4	レッドガイ	6
	レコード 5	フレム	4
	レコード 6	アル	4

解答は次のようになる:

		属性
		名前	順位
組	レコード 1	カウ	1
	レコード 2	チキン	3
	レコード 3	アル	4
	レコード 4	フレム	2
	レコード 5	レッドガイ	5
	レコード 6	ウィゼル	1
	レコード 7	カウ	3
	レコード 8	チキン	2
	レコード 9	レッドガイ	6
	レコード 10	フレム	4

アルは、両方の競走で、4 等を取ったので、一つのみフィールドが生き残る。

演習5-4：上の「マラソン順位」と「100 m 走順位」に積演算を施すとどうなるか。

		属性
		名前	順位
組	レコード 3	アル	4

両方同じ順位を取ったものだけが生き残る。

演習5-5：「マラソン順位」から「100 m 走順位」の差演算を施したものと、「100 m 走順位」から「マラソン順位」の差演算を施したものと結果を比較せよ。

「マラソン順位」-「100 m 走順位」
		属性
		名前	順位
組
	レコード 1	カウ	1
	レコード 2	チキン	3
	レコード 3	フレム	4
	レコード 4	レッドガイ	5

重なるレコードである「アル」に関する行が削られる。

「100 m 走順位」-「マラソン順位」
		属性
		名前	順位
組
	レコード 1	ウィゼル	1
	レコード 2	カウ	3
	レコード 3	チキン	2
	レコード 4	レッドガイ	6
	レコード 5	フレム	4

こちらも重なるレコードである「アル」に関する行が削られる。が、

引かれる方の食い違いのあるレコードのみ残ることに注意。

演習5-6：上記の「キャラクタと番組」表から属性「名前」「番組名」を取り出せ。

属性

名前番組名

組レコード 1 ブラサムパワパフガールズ

レコード 2 バブルスパワパフガールズ

レコード 3 バタカップパワパフガールズ

レコード 4 モジョジョジョパワパフガールズ

レコード 5 ミス・キーンパワパフガールズ

レコード 6 メイヨールパワパフガールズ

レコード 7 プロフェッサパワパフガールズ

レコード 8 ジャックサムライジャック

レコード 9 アクサムライジャック

上記の「キャラクタと番組」表から属性「名前」「性別」を取り出せ。

		属性
		名前	性別
組	レコード 1	ブラサム	女
	レコード 2	バブルス	女
	レコード 3	バタカップ	女
	レコード 4	モジョジョジョ	男
	レコード 5	ミス・キーン	女
	レコード 6	メイヨール	男
	レコード 7	プロフェッサ	男
	レコード 8	ジャック	男
	レコード 9	アク	男

上記の「住んでいる町と趣味」表から属性「趣味・特技」「住んでいる町」を取り出せ。

		属性
		住んでいる町	趣味・特技
組	レコード 1	タウンズビル	新しい服
	レコード 2	タウンズビル	動物と話す
	レコード 3	タウンズビル	ケンカ
	レコード 4	タウンズビル	発明
	レコード 5	未来世界	剣
	レコード 6	ラボ	発明
	レコード 7	ラボ	バレエ

上記の「番組ストーリー」表から属性「概要」を取り出せ。

		属性
		概要
組	レコード 1	3 人の女の子たちのお話
	レコード 2	未来世界の侍の話
	レコード 3	姉と弟の兄弟げんかの話
	レコード 4	電気会社の CM 集
	レコード 5	犬と発明家の話

演習5-7 上記の「キャラクタと番組」表から属性「性別」が「女」であるものを取り出せ。

		属性
		フィールド 1「名前」	フィールド 2「番組名」	フィールド 3「性別」	フィールド 4「キャラクタ」
組	レコード 1	ブラサム	パワパフガールズ	女	善
	レコード 2	バブルス	パワパフガールズ	女	善
	レコード 3	バタカップ	パワパフガールズ	女	善
	レコード 4	ミス・キーン	パワパフガールズ	女	善

上記の「住んでいる町と趣味」表から属性「住んでいる町」が「タウンズビル」であるものを取り出せ。

		属性
		フィールド 1「名前」	フィールド 2「住んでいる町」	フィールド 3「趣味・特技」
組	レコード 1	ブラサム	タウンズビル	新しい服
	レコード 2	バブルス	タウンズビル	動物と話す
	レコード 3	バタカップ	タウンズビル	ケンカ
	レコード 4	モジョジョジョ	タウンズビル	発明

上記の「100 m 走順位」表から属性「順位」が「1」「6」であるものを取り出せ。

		属性
		名前	順位
組	レコード 1	ウィゼル	1
	レコード 2	レッドガイ	6

上記の「マラソン順位」表から属性「順位」が「4」より小さいものを取り出せ。

		属性
		名前	順位
組	レコード 1	カウ	1
	レコード 2	チキン	3
	レコード 3	フレム	2

「より小さい」場合はその数を含まない。

演習5-8：上記の「キャラクタと番組」表から属性「名前」及び「性別」が「女」であるレコードを取り出し、上記の「住んでいる町と趣味」表から属性「名前」及び「住んでいる町」が「タウンズビル」であるレコードを取り出し、名前を主キーとして、使用した属性で新たな表を作ると、どのようなものになるか。

まずは両方の選択演算を実行し、二つ並べてみる。

		属性
		名前	性別	名前	住んでいる町
組	レコード 1	ブラサム	女	ブラサム	タウンズビル
	レコード 2	バブルス	女	バブルス	タウンズビル
	レコード 3	バタカップ	女	バタカップ	タウンズビル
	レコード 4	ミス・キーン	女

まずは両方にあるレコードのみを取り出す。

これについて重複する属性を取り除くと、完成。

		属性
		名前	性別	住んでいる町
組	レコード 1	ブラサム	女	タウンズビル
	レコード 2	バブルス	女	タウンズビル
	レコード 3	バタカップ	女	タウンズビル

関係データベースとSQL

関係データベースを計算機に入れ、前回行った演算を実行するには専用の言語を必要とする。アプリケーションは PostgreSQL (ぽすとぐれしーける) を使う。言語は SQL, Structured Query Language を用いる。英語では "しーける" と発音するが、日本ではアルファベット読みが通用している。

Query：照会
Query Language：問い合わせ言語

SQL で実行したいこと

そもそも表を作ったり変更したりする許可を与えること
どの表を閲覧できる許可を与えること
その上でレコードを操作すること

である。

SQL は表に対して設定する言語と、レコードに対して動作を行う言語から成り立っている。表に実行を行う許可を与えることは、データ定義言語と呼ばれる DDL, Data Definition Language を使って行い、表の操作はデータ操作言語と呼ばれる DML, Data Manipulation Language を使う。

DML, データ操作言語

関係データベースにおいてデータを操作するのには、射影、選択、結合があった。このような操作は、データを照会しながら行うので、query (照会) と呼ばれる。

Query には SELECT, INSERT, UPDATE, DELETE, をよく使うので覚えておこう。

ワイルドカードの文字は * を使う。ワイルドカードとは、あらゆる文字列の代わりになる文字のこと。

SELECT 文

あるレコードを取り出すときに使う。

SELECT 属性 FROM 表 [WHERE 条件]

「キャラクタと番組」において以下の SELECT 文を実行せよ。

SELECT 「キャラクタ」 FROM 「キャラクタと番組」
SELECT * FROM 「キャラクタと番組」 WHERE 「番組名」=「サムライジャック」

SELECT 文に使う関数

何かを取り出すのに関数を使うことができる。以下のような関数を知っておくと便利である。

関数	目的	使いかた
AVG	平均を出す	AVG(属性)
COUNT	行数を出す	COUNT(*)
MAX	最大値を出す	MAX(属性)
MIN	最小値を出す	MIN(属性)
SUM	合計を出す	SUM(属性)

演習6-2：以下の文を実行した結果を示せ。

SELECT AVG(「順位」) FROM 「100 m 走順位」
SELECT COUNT(*) FROM 「100 m 走順位」
SELECT MAX(「順位」) FROM 「100 m 走順位」
SELECT MIN(「順位」) FROM 「マラソン順位」
SELECT SUM(「順位」) FROM 「マラソン順位」

上の演習問題でも、順位の和や平均は意味がないが、練習のために行っている。

COUNT は、* で全てを数えること以外に、重複しない行数を数える指定ができる。それには重複している情報を含む属性を DISTINCT で指定すればよい。

COUNT (DISTINCT (属性))：重複を排除した行数を数える

演習6-3：以下の文を実行した結果を示せ。表は前回のものを使用する。

SELECT COUNT(DISTINCT(「住んでいる町」)) FROM 「住んでいる町と趣味・特技」
SELECT COUNT(DISTINCT(「順位」)) FROM 「100 m 走順位」

グループ化

GROUP BY を使って、属性に関して重複する行をグループ化することができる。もしそこに条件をつける場合は、HAVING を使う。

GROUP BY 属性：属性に関して重複する行をグループ化。複数の属性を SELECT した場合は、それらを指定する必要がある。 
HAVING 条件式：集計結果に対する問い合わせ条件を指定

グループ化した結果を求めるには

SELECT 属性1, 属性2 ... FROM 表 GROUP BY 属性1, 属性2 ... HAVING 条件式

とする。

演習6-4：以下の文を実行した結果を示せ。

SELECT「名前」,「順位」 FROM 「マラソン順位」 GROUP BY「名前」,「順位」 HAVING 「順位」< 3
SELECT 「名前」,「順位」 FROM 「100 m 走順位」 GROUP BY 「名前」,「順位」 HAVING AVG(「順位」) > 3

同じ属性が複数の表に含まれている場合

表を指定してから属性を指定する必要がある。

表.属性

として、区別する。

複数の条件をつける場合

AND あるいは OR を使う。

WHERE 条件 1 AND 条件 2 [OR 条件 3] ...

同じ属性を含むものを取り出したい場合

表が複数あり、そのなかの属性が重なっているとする。表の名前を A, B とする。どちらの表にも含まれているものだけ取り出す場合は、

A 表.属性 = B 表.属性

を条件に加える。また、その属性を表示したい場合は、どちらか片方だけの属性を取り出すと指定しなければならないため、

SELECT A 表.属性 WHERE 条件 AND A 表.属性 = B 表.属性

などとする必要がある。

GROUP BY ... HAVING の HAVING は、1 つの属性を指定するが、同じフィールドがあるときの重複を防ぐために、GROUP BY で属性を設定する。

たとえば、名前と、町と、話が分かっているときに、全ての表に掲載されている情報を、その部分だけ引き出したいときには、

itdb=> SELECT character.name, town.town, story.abstract FROM character, town, story WHERE character.name=town.name AND story.program=character.program;
      name      |     town     |        abstract        
----------------+--------------+------------------------
 ジャック       | 未来世界     | 未来世界の侍の話
 ブラサム       | タウンズビル | 3 人の女の子たちのお話
 バブルス       | タウンズビル | 3 人の女の子たちのお話
 バタカップ     | タウンズビル | 3 人の女の子たちのお話
 モジョジョジョ | タウンズビル | 3 人の女の子たちのお話
(5 rows)

A=B, C=A ならば A=B=C であることに注意せよ。

PostgresQL の操作

表を見るには

表を見るには、

SELECT 属性 FROM 表

だった。 PostgreSQL では、コマンドの区切りは ; で表す。 character を見ようとする場合、

itdb=> SELECT * FROM character;
      name      |     program      | sex | character
----------------+------------------+-----+--------
 ブラサム       | パワパフガールズ | 女  | 善
 バブルス       | パワパフガールズ | 女  | 善
 バタカップ     | パワパフガールズ | 女  | 善
 モジョジョジョ | パワパフガールズ | 男  | 悪
 ミス・キーン   | パワパフガールズ | 女  | 善
 メイヨール     | パワパフガールズ | 男  | 善
 プロフェッサ   | パワパフガールズ | 男  | 善
 ジャック       | サムライジャック | 男  | 善
 アク           | サムライジャック | 男  | 悪
(9 rows)

とすればよい。

演習7-1: 同様に、その他の表も眺めてみよう。

属性を取り出すには

全ての行を取り出すには属性にワイルドカード * を用いる。

ある属性を取り出すには、属性の中から指定すればよい。例として、character から name を取り出してみよう。

itdb=> SELECT name FROM character;
      name      
----------------
 ブラサム
 バブルス
 バタカップ
 モジョジョジョ
 ミス・キーン
 メイヨール
 プロフェッサ
 ジャック
 アク
(9 rows)

複数の属性を取り出すことも可能である。character から name と character を取り出すと、

itdb=# SELECT name,character FROM character;
      name      | character
----------------+--------
 ブラサム       | 善
 バブルス       | 善
 バタカップ     | 善
 モジョジョジョ | 悪
 ミス・キーン   | 善
 メイヨール     | 善
 プロフェッサ   | 善
 ジャック       | 善
 アク           | 悪
(9 rows)

となる。属性どうしは , でつないでいく。

演習7-2: 5-6 の演習を行なってみよう。

条件をつけて取り出すには

条件は WHERE を用いた。WHERE='要素' で取り出すことが出来る。

character 表から character が「善」であるものを取り出すには、

itdb=> SELECT * FROM character WHERE character='善'

     name     |     program      | sex | character
--------------+------------------+-----+--------
 ブラサム     | パワパフガールズ | 女  | 善
 バブルス     | パワパフガールズ | 女  | 善
 バタカップ   | パワパフガールズ | 女  | 善
 ミス・キーン | パワパフガールズ | 女  | 善
 メイヨール   | パワパフガールズ | 男  | 善
 プロフェッサ | パワパフガールズ | 男  | 善
 ジャック     | サムライジャック | 男  | 善
(7 rows)

条件を繋ぐ場合は、

日本語はおかしな表示になることもあるが、きちんとプログラムは作動するはずである。

解答その 2

演習6-1：「キャラクタと番組」において、以下の SELECT 文を実行せよ。

SELECT 「キャラクタ」 FROM 「キャラクタと番組」

キャラクタと番組
		属性
		「キャラクタ」
組	レコード 1	善
	レコード 2	善
	レコード 3	善
	レコード 4	悪
	レコード 5	善
	レコード 6	善
	レコード 7	善
	レコード 8	善
	レコード 9	悪

SELECT * FROM 「キャラクタと番組」 WHERE 「番組名」=「サムライジャック」

関係表の例：キャラクタと番組
		属性
		フィールド 1「名前」	フィールド 2「番組名」	フィールド 3「性別」	フィールド 4「キャラクタ」
組	レコード 1	ジャック	サムライジャック	男	善
組	レコード 2	アク	サムライジャック	男	悪

SELECT AVG(「順位」) FROM 「100 m 走順位」

1+2+3+6+4+4 = 3.333...

SELECT COUNT(*) FROM 「100 m 走順位」

順位表の行数は 6

SELECT MAX(「順位」) FROM 「100 m 走順位」

最大値は 6

SELECT MIN(「順位」) FROM 「マラソン順位」

最小値は 1

SELECT SUM(「順位」) FROM 「マラソン順位」

1 + 3 + 4 + 2 + 5 = 15

演習6-3：以下の文を実行した結果を示せ。 SELECT COUNT(DISTINCT「住んでいる町」) FROM 「住んでいる町と趣味・特技」

「タウンズビル」「未来世界」「ラボ」の 3 つ

SELECT COUNT(DISTINCT(「順位」) FROM 「100 m 走順位」

1, 3, 2, 6, 4 の 5 種類で 5

演習6-4：以下の文を実行した結果を示せ。

SELECT

FROM

GROUP BY

HAVING

		属性
		名前	順位
組	レコード 1	カウ	1
	レコード 2	フレム	2

SELECT 「名前」,「順位」 FROM 「100 m 走順位」 GROUP BY 「順位」 HAVING AVG(「順位」) > 3

		属性
		名前	順位
組	レコード 1	ウィゼル	1
	レコード 2	チキン	2

解答その 3

itdb=> SELECT name FROM character;
      name      
----------------
 ブラサム
 バブルス
 バタカップ
 モジョジョジョ
 ミス・キーン
 メイヨール
 プロフェッサ
 ジャック
 アク
(9 rows)

itdb=> SELECT sex FROM character;
 sex 
-----
 女
 女
 女
 男
 女
 男
 男
 男
 男
(9 rows)

itdb=> SELECT town FROM town;
     town     
--------------
 タウンズビル
 タウンズビル
 タウンズビル
 タウンズビル
 未来世界
 ラボ
 ラボ
(7 rows)

itdb=> SELECT abstract FROM story;
        abstract        
------------------------
 3 人の女の子たちのお話
 未来世界の侍の話
 姉と弟の兄弟げんかの話
 電気会社の CM 集
 犬と発明家の話
(5 rows)

itdb=> SELECT name,character FROM character;
      name      | character
----------------+--------
 ブラサム       | 善
 バブルス       | 善
 バタカップ     | 善
 モジョジョジョ | 悪
 ミス・キーン   | 善
 メイヨール     | 善
 プロフェッサ   | 善
 ジャック       | 善
 アク           | 悪
(9 rows)

itdb=> SELECT * FROM character where character='善';
     name     |     program      | sex | character
--------------+------------------+-----+--------
 ブラサム     | パワパフガールズ | 女  | 善
 バブルス     | パワパフガールズ | 女  | 善
 バタカップ   | パワパフガールズ | 女  | 善
 ミス・キーン | パワパフガールズ | 女  | 善
 メイヨール   | パワパフガールズ | 男  | 善
 プロフェッサ | パワパフガールズ | 男  | 善
 ジャック     | サムライジャック | 男  | 善
(7 rows)


itdb=> SELECT * FROM marathon where rank < 4;
  name  | rank 
--------+------
 カウ   |    1
 チキン |    3
 フレム |    2
(3 rows)

itdb=> SELECT character.name, town FROM character,town WHERE sex='女' AND town='タウンズビル' AND character.name=town.name;
    name    |     town     
------------+--------------
 ブラサム   | タウンズビル
 バブルス   | タウンズビル
 バタカップ | タウンズビル
(3 rows)

itdb=> SELECT character FROM character;
 character
--------
 善
 善
 善
 悪
 善
 善
 善
 善
 悪
(9 rows)


itdb=> SELECT * FROM character where program='サムライジャック';
   name   |     program      | sex | character 
----------+------------------+-----+--------
 ジャック | サムライジャック | 男  | 善
 アク     | サムライジャック | 男  | 悪
(2 rows)


itdb=> SELECT AVG(rank) FROM hundred ;
        avg         
--------------------
 3.3333333333333333
(1 row)


itdb=> SELECT * FROM hundred ;
    name    | rank 
------------+------
 ウィゼル   |    1
 カウ       |    3
 チキン     |    2
 レッドガイ |    6
 フレム     |    4
 アル       |    4
(6 rows)

itdb=> SELECT COUNT(*) FROM hundred ;
 count 
-------
     6
(1 row)


itdb=> SELECT MAX(rank) FROM hundred ;
 max 
-----
   6
(1 row)

itdb=> SELECT MIN(rank) FROM marathon ;
 min 
-----
   1
(1 row)

itdb=> SELECT COUNT(DISTINCT(town)) FROM town;
 count 
-------
     3
(1 row)


itdb=> SELECT COUNT(DISTINCT(rank)) FROM hundred;
 count 
-------
     5
(1 row)

itdb=> SELECT name,rank FROM marathon GROUP BY name,rank HAVING rank < 3;
  name  | rank 
--------+------
 カウ   |    1
 フレム |    2
(2 rows)

itdb=> SELECT name,rank FROM hundred GROUP BY name,rank HAVING
 AVG(rank) < 3;
   name   | rank 
----------+------
 ウィゼル |    1
 チキン   |    2
(2 rows)

SELECT character.name, town, character FROM town, character WHERE
    character.sex='女' AND town.town='タウンズビル' 
    AND character.name=town.name;
    name    |     town     | character 
------------+--------------+-----------
 ブラサム   | タウンズビル | 善
 バブルス   | タウンズビル | 善
 バタカップ | タウンズビル | 善
(3 rows)

実際は「タウンズビルの女」だけですでに 3 名に絞り込まれている。

属性を日本語に変換して表したい場合

SELECT english AS '日本語' で表すことができる。(Postgres9 以降では未確認)

		属性
		名前	番組名
組	レコード 1	ブラサム	パワパフガールズ
	レコード 2	バブルス	パワパフガールズ
	レコード 3	バタカップ	パワパフガールズ
	レコード 4	モジョジョジョ	パワパフガールズ
	レコード 5	ミス・キーン	パワパフガールズ
	レコード 6	メイヨール	パワパフガールズ
	レコード 7	プロフェッサ	パワパフガールズ
	レコード 8	ジャック	サムライジャック
	レコード 9	アク	サムライジャック

SQL 実習

データベース練習帳

データベースサーバへのログイン

データベースにアクセスするには

練習用データベースの中に入っているもの

データベースからのログアウト

データベースサーバからのログアウト

関係データベース

関係データベース

演習5-1：候補キーを「キャラクタ」とする。それらのレコードを全て挙げよ。

演習5-2：下の関係表と合せて何か情報を取り出したい(「参照する」という)。 下の 2 つの関係表の属性を取り出すとき、上の関係表のどの属性が外部キーとして適切だろうか？

関係表の操作について

集合演算

演習5-3：下の 2 つの関係表に和演算を施すとどうなるか。

演習5-4：上の「マラソン順位」と「100 m 走順位」に積演算を施すとどうなる か。

演習5-5：「マラソン順位」から「100 m 走順位」の差演算を施したものと、 「100 m 走順位」から「マラソン順位」の差演算を施したものと結果を比較せよ。

直積演算

関係演算

演習5-6：

演習5-7：

演習5-8：

解答 その 1

引かれる方の食い違いのあるレコードのみ残ることに注意。

演習5-6：上記の「キャラクタと番組」表から属性「名前」「番組名」を取り出せ。

上記の「キャラクタと番組」表から属性「名前」「性別」を取り出せ。

上記の「住んでいる町と趣味」表から属性「趣味・特技」 「住んでいる町」を取り出せ。

上記の「番組ストーリー」表から属性「概要」を取り出せ。

演習5-7 上記の「キャラクタと番組」表から属性「性別」が「女」であるものを取り出せ。

上記の「住んでいる町と趣味」表から属性「住んでいる町」が「タウンズビル」であるものを取り出せ。

上記の「100 m 走順位」表から属性「順位」が「1」「6」であるものを取り出せ。

上記の「マラソン順位」表から属性「順位」が「4」より小さいものを取り出せ。

「より小さい」場合はその数を含まない。

関係データベースとSQL

DML, データ操作言語

SELECT 文

「キャラクタと番組」 において 以下の SELECT 文を実行せよ。

SELECT 「キャラクタ」 FROM 「キャラクタと番組」 SELECT * FROM 「キャラクタと番組」 WHERE 「番組名」=「サムライジャック」

SELECT 文に使う関数

演習6-2：以下の文を実行した結果を示せ。

演習6-3：以下の文を実行した結果を示せ。表は前回のものを使用する。

グループ化

演習6-4：以下の文を実行した結果を示せ。

同じ属性が複数の表に含まれている場合

複数の条件をつける場合

同じ属性を含むものを取り出したい場合

PostgresQL の操作

表を見るには

演習7-1: 同様に、その他の表も眺めてみよう。

属性を取り出すには

演習7-2: 5-6 の演習を行なってみよう。

条件をつけて取り出すには

解答 その 2

演習6-1：「キャラクタと番組」において、以下の SELECT 文を実行せよ。

SELECT * FROM 「キャラクタと番組」 WHERE 「番組名」=「サムライジャック」

SELECT AVG(「順位」) FROM 「100 m 走順位」

SELECT COUNT(*) FROM 「100 m 走順位」

SELECT MAX(「順位」) FROM 「100 m 走順位」

SELECT MIN(「順位」) FROM 「マラソン順位」

SELECT SUM(「順位」) FROM 「マラソン順位」

演習6-3：以下の文を実行した結果を示せ。 SELECT COUNT(DISTINCT「住んでいる町」) FROM 「住んでいる町と趣味・特技」

SELECT COUNT(DISTINCT(「順位」) FROM 「100 m 走順位」

演習6-4：以下の文を実行した結果を示せ。

SELECT 「名前」,「順位」 FROM 「100 m 走順位」 GROUP BY 「順位」 HAVING AVG(「順位」) > 3

解答 その 3

属性を日本語に変換して表したい場合

演習5-2：下の関係表と合せて何か情報を取り出したい(「参照する」という)。下の 2 つの関係表の属性を取り出すとき、上の関係表のどの属性が外部キーとして適切だろうか？

演習5-4：上の「マラソン順位」と「100 m 走順位」に積演算を施すとどうなるか。

演習5-5：「マラソン順位」から「100 m 走順位」の差演算を施したものと、「100 m 走順位」から「マラソン順位」の差演算を施したものと結果を比較せよ。

解答その 1

上記の「住んでいる町と趣味」表から属性「趣味・特技」「住んでいる町」を取り出せ。

「キャラクタと番組」において以下の SELECT 文を実行せよ。

SELECT 「キャラクタ」 FROM 「キャラクタと番組」
SELECT * FROM 「キャラクタと番組」 WHERE 「番組名」=「サムライジャック」

解答その 2

解答その 3