RDBMS

プログラミング論 II
RDBMSとSQL
http://www.ns.kogakuin.ac.jp/~ct13140/Prog.2010/
R-1
概要
• データベース
– 簡単．単に覚えるだけ．
– 情報系技術者の超重要スキル
• リレーション
– 抽象的で理解しづらい．
大まかに分かれば良い．
• SQL
– 易しい．必ず理解すること！
R-2
データベース
RDBMS
R-3
Database
• データベースとは，データの集まり．
– 例えば，実験の測定値，社員表など．
– 多くの場合，数値か文字列．
R-4
DBMS
(Database Management
System)
– データベース管理システム．データベースを管
理運用するためのシステム．
• 多くの場合，ソフトウェアである．
– DBMSには数種類のモデルがある．
• リレーショナルデータモデル，ネットワークデータモ
デル
• 階層型データモデル，オブジェクト指向，XML
– 現在，多くのDBMSがリレーショナルデータモデ
ルのRDBMB (Relational Database
Management System)である．
– 本講義ではRDBMSのみ扱う．
R-5
RDBMS
• RDBMB
– Relational Database Management System
• 日本語で「関係データベース(管理システム)」
– Relational Data Model(関係データモデル)を
採用したDMBS
• 通常，SQLという言語を用いて運用する．
• 現在，ほとんどのDBMSがRDBMS
– Oracle， Microsoft SQL Server, DB2(IBM),
PostgreSQL, MySQL, Firebird, SQLiteなどの実装があ
る．
• 現在，超重要なアプリケーションの一つ．
R-6
単語"Database"の曖昧性
• 「データとDBMS(DB管理システム)」をまとめて
「データベース(DB)」と呼ぶこともある．
• DBMS(DB管理システム)を「データベース」と呼
ぶこともある．
– RDBMSの意味で使うこともある．
R-7
データモデル
• DBMSでは各種モデルによりデータを整理，
管理する．この整理，管理のモデルがデー
タモデル．
• 一般に，以下の3要素から成り立つ
– データやデータ間の関連を表現する構造記述
– データやデータ間に存在する一貫性制約の記述．
– データの検索，更新などのデータ操作言語
R-8
リレーショナルデータモデル
• 数学の集合論に基づく，データモデル．
• 極めて簡潔に説明すると「表型」．
• 全てのデータ，データ間の関連を「リレー
ションのタプル」で表現する．(後述)
– タプルは，値の組み合わせ．
– Codd(コッド)博士が提案．チューリング賞．
– 日本語では「関係データモデル」
R-9
リレーション
R-10
リレーショナルデータモデル
会員表
• データを表形式で管理する．
会員番号
氏名
住所
所属
0
福田康夫
群馬県...
部署M
1
安倍晋三
山口県...
部署M
2
小泉純一郎
神奈川県...
部署M
3
森喜朗
石川県...
部署M
4
小渕恵三
群馬県...
部署O
R-11
リレーショナルデータモデル
表，リレーション，TABLE
会員番号
氏名
住所
所属
0
福田康夫
群馬県...
部署M
1
安倍晋三
山口県...
部署M
2
小泉純一郎
神奈川県...
部署M
3
森喜朗
石川県...
部署M
4
小渕恵三
群馬県...
部署O
R-12
リレーショナルデータモデル
行，ロー，
レコード，
タプル
会員番号
氏名
住所
所属
0
福田康夫
群馬県...
部署M
1
安倍晋三
山口県...
部署M
2
小泉純一郎
神奈川県...
部署M
3
森喜朗
石川県...
部署M
4
小渕恵三
群馬県...
部署O
R-13
リレーショナルデータモデル
列，カラム，
フィールド
会員番号
氏名
住所
所属
0
福田康夫
群馬県...
部署M
1
安倍晋三
山口県...
部署M
2
小泉純一郎
神奈川県...
部署M
3
森喜朗
石川県...
部署M
4
小渕恵三
群馬県...
部署O
R-14
リレーショナルデータモデル
列名，カラム名，
フィールド名
会員番号
氏名
住所
所属
0
福田康夫
群馬県...
部署M
1
安倍晋三
山口県...
部署M
2
小泉純一郎
神奈川県...
部署M
3
森喜朗
石川県...
部署M
4
小渕恵三
群馬県...
部署O
R-15
リレーショナルデータモデル
• 行の値の変更，行の追加，行の削除
などを繰り返して，DBMSを運用してい
く．
会員番号
氏名
住所
• 通常，列の
0
福田康夫
群馬県...
追加/削除は 1
安倍晋三
山口県...
行わない．
2
小泉純一郎神奈川県...
• 行の順は意味 3
森喜朗
石川県...
を持たない
4
小渕恵三
群馬県...
所属
部署M
部署M
部署M
部署M
部署O
R-16
キー key
R-17
候補キー
•表内の行を特定できる列（の組）が候補キー．
–会員番号を定めると，行が一意に定まる．
よって，会員番号は候補キー．
•「会員番号」が「2」である行は，1行しかない．
•当然，会員番号は重複しないことが前提．
–「氏名」では，
同姓同名がいると
破綻する．
会員番号
氏名
住所
所属
0
福田康夫
群馬県...
部署M
1
安倍晋三
山口県...
部署M
2
小泉純一郎神奈川県... 部署M
3
森喜朗
石川県...
部署M
4
小渕恵三
群馬県...
部署O
R-18
候補キー
• キーは，複数の列で構成されていても良い．
「氏名，科目」を定めると行が特定される．
よって，「氏名，科目」の組は候補キー
氏名
科目
得点
英語
A
数学
B
英語
C
安倍晋三
数学
D
小泉純一郎
英語
E
最悪でも，「全列の組」は
福田康夫
候補キーとなる．
(同一の行は存在しては福田康夫
安倍晋三
ならない．)
R-19
候補キー
•候補キーは複数存在する場合もある．
–会員番号を定めると行が特定される．
よって，会員番号は候補キー
–基礎年金番号を定めると行が特定される．
よって，基礎年期番号も候補キー
会員番号
氏名
住所
基礎年金番号
0
福田康夫
群馬県...
234
1
安倍晋三
山口県...
567
2
小泉純一郎神奈川県...
890
3
森喜朗
石川県...
123
4
小渕恵三
群馬県...
456
R-20
主キー
• 候補キーの中から，DBMS管理者が
主観的に選択した主たるキーが「主キー」．
– 理論的価値は低いが，実運用上重要な意味
を持つ
– 下線を引くことが多い．
会員番号
氏名
住所
基礎年金番号
0
福田康夫
群馬県...
234
1
安倍晋三
山口県...
567
2
小泉純一郎神奈川県...
890
3
森喜朗
石川県...
123
4
小渕恵三
群馬県...
456
R-21
正規形
R-22
第一正規形
• 1マスに複数の要素を入れてはならない．
• 1個の要素が複数マスに跨ってはならない．
– そもそも，RDBMSに格納できない．
注意：この説明は過度に簡潔化されています．
そもそも「マス」などという表現は使ってはならない．
R-23
第一正規形
会員
番号
0
趣味
氏名
福田康夫クラシック音楽鑑賞
2
歌舞伎鑑賞
会員
番号
映画鑑賞
0
小泉純一郎
2
会員
番号
0
趣味
氏名
福田康夫クラシック音楽鑑賞
2 小泉純一郎
歌舞伎鑑賞，
映画鑑賞
非正規形
氏名
趣味
福田康夫クラシック音楽鑑賞
2 小泉純一郎
歌舞伎鑑賞
2 小泉純一郎
映画鑑賞
第一正規形
R-24
関数従属
• ある列群の値が決まれば，別のある
列群の値が一意に定まるとき「関数従
{氏名,教科}が決まると，
属(する)」という． {点数，合否}は決まる．
氏名
教科
点数合否
福田康夫
英語
50
否
福田康夫
数学
60
合
安倍晋三
英語
70
合
安倍晋三
数学
80
合
よって，関数従属である．
{氏名，教科}→{点数，合否}
{氏名}を決めても，
{点数}は決まらない．
関数従属でない．
{点数}が決まると，
{合否}は決まる．
関数従属である．
R-25
完全従属
• 列群Aに列群Bが関数従属であり，
列群Aの真部分集合(Aの一部)に
関数従属でないとき，「完全従属」という．
会員
番号
氏名
教科点数合否
0
福田康夫英語 50
否
0
福田康夫数学 60
合
1
安倍晋三英語 70
合
1
安倍晋三数学 80
合
{会員番号,氏名,教科}
→{点数,合否}なので関数従属．
ところが，
{会員番号,教科}
→{点数,合否}でもある．
実は，{氏名}は無くても良い．
よって，{点数,合否}は
{会員番号,氏名,教科}に
完全従属でない．
R-26
第二正規形
• 第一正規形である．
• 候補キーでない列が候補キーに完全従属
している．
R-27
第二正規形でない例
赤枠は主キー
user
name
ID
email
年
表"日記"
月
日
text
0
福田
[email protected]
2007 9
26
首相になった
1
安倍
[email protected]
2007 7
29
選挙を行った
1
安倍
[email protected]
2006 9
26
首相になった
2
小泉
[email protected]
2005 8
8
解散した
{userID,年,月,日}が候補キーかつ主キー．
{userID}→{name,email}で，
nameやemailが候補キーに完全従属でない．
R-28
第二正規化の例
user
name
ID
email
user
ID
年
月
日
text
0
福田
[email protected]
0
2007 9
26
首相になった
1
安倍
[email protected]
1
2007 7
29
選挙を行った
2
小泉
[email protected]
1
2006 9
26
首相になった
表"ユーザ表"
2
2005 8
8
解散した
表"日記"
• 先ほどの表を分解し，
非候補キーが全ての候補キーに完全従属．
これで，第二正規形
R-29
第三正規形
• 第二正規形である．
• 全ての非キー列は，どの候補キーにも推
移的に従属しない．
R-30
第三正規形でない例
user
name
ID
所属
勤務地
0
福田 A事業部
八王子
1
安倍 B事業部
新宿
2
小泉 B事業部
新宿
3
森
A事業部
八王子
• 第二正規形であるが，
所属→勤務地の関数
従属が存在する．
• つまり，勤務地は
• userID→所属
→勤務地と
推移的に決定される．
分解するべき
R-31
第三正規形
user
name 所属
勤務地
ID
0 福田 A事業部八王子
1
安倍 B事業部
新宿
2
小泉 B事業部
新宿
3
森
A事業部八王子
user
name 所属
ID
0 福田 A事業部
1
安倍 B事業部
2
小泉 B事業部
3
森
所属
勤務地
A事業部八王子
B事業部
新宿
A事業部
R-32
SQL
R-33
SQL
• SQLは，RDBMSを操作するのに使用する
言語の一つ．
– 現在，本言語が主流．本稿ではSQLのみ扱う．
– 厳密には，SQLは略語ではない．
• 歴史的背景から"Structured Query
Language"の略とすることもある．
• 構造化問い合わせ言語．
– 言語仕様には，実装依存する箇所が多々ある．
• C言語にも方言はあるが，SQLほど酷くはない．
R-34
SQLの超基本命令
• 表を作成する．
CREATE TABLE 表名 (...);
• 表に行を挿入する．
INSERT INTO 表名 VALUES (...);
• 表の内容を確認する．
SELECT * FROM 表名;
• 表の行の値を変更する.
UPDATE 表名 SET 列名=値[,列名=値]
WHERE ..;
• 表の行を削除す
DELETE FROM 表名 WHERE ...;
R-35
SQLの例
• 以下の様なデータベースを構築することを
考える．
表"user_tbl"
userID
name
email
0
Fukuda Yasuo
[email protected]
1
Abe Shinzo
[email protected]
2
3
Koizumi Junichiro [email protected]
Mori Yoshiro
[email protected]
R-36
SQLの例
userID
name
ユーザID ユーザ名を格納する．
を格納．
最大64文字とする．
INTEGER
型とする．
VARCHAR(64)型
とする．
email
ユーザのE-mailアドレス
を格納する．
最大64文字とする．
VARCHAR(64)型
とする．
• 各行(レコード)の列(フィールド)には「型」が
ある．
R-37
SQL (CREATE TABLE)
CREATE TABLE user_tbl (
userID integer,
name
varchar(64),
email varchar(64)
);
R-38
• 空の表"user_tbl"が作成される．
– 確認方法は後述．
userID
name
表"user_tbl"
email
R-39
SQL (INSERT INTO)
INSERT INTO user_tbl
VALUES (
0,
'Fukuda Yasuo',
'[email protected]'
);
これが，
userIDになる．
これが，
nameになる．
これが，
emailになる．
R-40
• 表"user_tbl"に1行追加される．
– 確認方法は後述．
表"user_tbl"
userID
name
email
0
Fukuda Yasuo
[email protected]
R-41
SQL (INSERT INTO)
INSERT INTO user_tbl VALUES (
1,
'Abe Shinzo',
'[email protected]'
);
INSERT INTO user_tbl VALUES (
2,
'Koizumi Junichiro',
'[email protected]'
);
R-42
• 表"user_tbl"に2行追加される．
– 確認方法は後述．
表"user_tbl"
userID
name
email
0
Fukuda Yasuo
[email protected]
1
Abe Shinzo
[email protected]
2
Koizumi Junichiro [email protected]
R-43
SQL (SELECT/FROM)
SELECT * FROM user_tbl;
結果
userID
name
email
0
Fukuda Yasuo
[email protected]
1
Abe Shinzo
[email protected]
2
Koizumi Junichiro [email protected]
R-44
SQL (SELECT/FROM)
• SELECT name, email FROM
user_tbl;
結果
name
email
Fukuda Yasuo
[email protected]
Abe Shinzo
[email protected]
Koizumi Junichiro [email protected]
R-45
SQL (SELECT/FROM)
SELECT * FROM user_tbl
WHERE userID=2;結果
userID
2
name
email
Koizumi Junichiro [email protected]
SELECT * FROM…;
この部分では，
列の選択を行う．
SELECT * FROM … WHERE userID=2;
この部分では，
行の選択を行う．
R-46
SQL (SELECT/FROM/WHERE)
SELECT * FROM user_tbl WHERE
1<=userID;
結果
userID
name
email
1
Abe Shinzo
[email protected]
2
Koizumi Junichiro [email protected]
R-47
SQL (UPDATE)
UPDATE user_tbl SET email=
'[email protected]' WHERE userID=0;
SELECT * FROM user_tbl;
結果
userID
name
email
0
Fukuda Yasuo
[email protected]
1
Abe Shinzo
[email protected]
2
Koizumi Junichiro [email protected]
R-48
SQL (DELETE)
DELETE FROM user_tbl WHERE
userID=0;
SELECT * FROM user_tbl;
結果
userID
name
email
1
Abe Shinzo
[email protected]
2
Koizumi Junichiro [email protected]
R-49
データ型
整数型
INTEGER型:整数値を格納．
SMALLINT型,NUMERIC型,DECIMAL型
浮動小数点型
REAL型:単精度浮動小数点
DOUBLE PRECISION型:倍精度浮動小
数点
FLOAT型
R-50
データ型
文字列型
CHARACTER(n):n文字以内の文字列
VARCHAR(n):n文字以内の文字列
n文字より短い文字列を格納したとき，
CHARACTER型の場合，不足分は空白で埋められ
n文字分容量が消費される．
VARCHAR型の場合は，埋められない．
VARCHAR型の使用の方が一般的と思われる．
R-51
データ型
日付型
DATE型:日付を表す．
TIME型:時刻
TIMESTAMP:日付と時刻
R-52
データ型
• データ型はRDBMS実装に依存する部分が
大きい．
• 詳細は，実装ごとのマニュアルを読む必要
がある．
R-53
NULL値
• RDBMSのマスを空にすることができる(場
合がある)
• 空を「NULL」と呼ぶ．
• 主キーやNOT NULL制約のついた列のマ
スは，NULLにできない．
• 注意：一般にRDBMSに「マス」という表現は
ない．
R-54
算術演算子
使用例
意味
+
a+b
aとbの和
-
a-b
aとbの差
*
a*b
aとbの積
/
a/b
aをbで割った商
%
a%b
aとbで割った余り
R-55
比較演算子
使用例
意味
=
a=b
aとbは等しい
<
a<b
aはbより小さい
<=
a<=b
aはb以下
>
a>b
aはbより大きい
>=
<>
!=
a>=b
aはb以上
a!=b
aとbは等しくない
R-56
論理演算子
使用例
意味
A and B
A かつ B
10<=a and a <= 20
aが10以上かつ
aが20以下
A or B
A または B
a<=10 or 20<=a
aが10以下または
aが20以上
NOT A
A でない
NOT (a = 100)
Aが100でない
AND
OR
NOT
R-57
SELECTの応用
• 以下の様な表があるとする．
表"user_tbl"
userID
name
email
0
Fukuda Yasuo
[email protected]
1
Abe Shinzo
[email protected]
2
Koizumi Junichiro [email protected]
R-58
SELECT
SELECT name FROM user_tbl;
name
Fukuda Yasuo
Abe Shinzo
Koizumi Junichiro
R-59
SELECT/WHERE
SELECT * FROM user_tbl
WHERE userID = 2;
userID
2
name
email
Koizumi Junichiro [email protected]
R-60
SELECT/WHERE
SELECT * FROM user_tbl
WHERE userID < 2;
userID
name
email
0
Fukuda Yasuo
[email protected]
1
Abe Shinzo
[email protected]
R-61
SELECT/WHERE
SELECT * FROM user_tbl
WHERE name = 'Koizumi
Junichiro';
userID
2
name
email
Koizumi Junichiro [email protected]
R-62
SELECT/WHERE
SELECT * FROM user_tbl
WHERE 0<userID AND userID<2;
userID
name
email
1
Abe Shinzo
[email protected]
R-63
SELECT/WHERE
SELECT * FROM user_tbl
WHERE userID BETWEEN 1 AND 2;
userID
name
email
1
Abe Shinzo
[email protected]
2
Koizumi Junichiro [email protected]
userIDが「1以上2以下」
R-64
SELECT/WHERE
SELECT * FROM user_tbl
WHERE userID IN (0,2);
userID
name
email
0
Fukuda Yasuo
[email protected]
2
Koizumi Junichiro [email protected]
userIDが「0か2」
R-65
SELECT/WHERE
SELECT * FROM user_tbl
WHERE userID NOT IN (0,2);
userID
name
email
1
Abe Shinzo
[email protected]
R-66
SELECT/ORDER BY
表"user_tbl"
userID
name
score
0
Fukuda Yasuo
80
1
Abe Shinzo
70
2
Koizumi Junichiro
90
注意：この順は
保証されていない
SELECT * FROM user_tbl;
userID
name
score
0
Fukuda Yasuo
80
1
Abe Shinzo
70
2
Koizumi Junichiro
90
R-67
SELECT/ORDER BY
userID
name
score
0
Fukuda Yasuo
80
1
Abe Shinzo
70
2
Koizumi Junichiro
90
SELECT * FROM user_tbl ORDER BY score;
userID
name
score
1
Abe Shinzo
70
0
Fukuda Yasuo
80
2
Koizumi Junichiro
90
R-68
SELECT/ORDER BY
userID
name
score
0
Fukuda Yasuo
80
1
Abe Shinzo
70
2
Koizumi Junichiro
90
SELECT * FROM user_tbl ORDER BY score DESC;
userID
name
score
2
Koizumi Junichiro
90
0
Fukuda Yasuo
80
1
Abe Shinzo
70
R-69
SELECT/ORDER BY
userID
name
score
0
Fukuda Yasuo
80
1
Abe Shinzo
70
2
Koizumi Junichiro
90
SELECT * FROM user_tbl ORDER BY score ASC;
userID
name
score
1
Abe Shinzo
70
0
Fukuda Yasuo
80
2
Koizumi Junichiro
90
R-70
SELECT/ORDER BY
SELECT * FROM user_tbl ORDER BY
score, age;
まず，"score"の順に並び替え，
それが同じ値だったら"age"で並び替える．
R-71
LIKE演算子と % と _
LIKE演算子
文字列を比較するのに使用する．
下記の，パターンマッチが使用可能．
% (パーセント)
任意の長さ(0文字以上)の，任意の文字にマッ
チする．
_ (アンダースコア)
1文字の任意の文字にマッチする．
R-72
% と _ の例
ABC, AAC, AAB があるとき，
「A_C」は，
ABC と AAC が該当．
ABC は，「_がB」と考えれば該当．
AAC は，「_がA」と考えれば該当．
R-73
% と _ の例
ABC, AAC, AAB があるとき，
「_A_」は，
AAC と AAB が該当．
AAC は，「_と_が，AとC」と考えれば該当．
AAB は，「_と_が，AとB」と考えれば該当．
R-74
% と _ の例
ABC, AA, A, BAA があるとき，
「A%」は，
ABC, AA, A が該当．
ABC は，「%が，"BC"」と考えれば該当．
AA は，「%が，"A"」と考えれば該当．
A は，「%が，""」と考えれば該当．(0文字でもOK)
BAA は，%をどのようにおいても該当しない．
R-75
% と _ の例
ABC, AA, BAA, B があるとき，
「%A%」は，
ABC, AA, BAA が該当．
ABC は，「%と%が，""と"BC"」と考えれば該当．
AA は，「%と%が，"A"と""」と考えれば該当．
「%と%が，""と"A"」と考えても該当．
BAA は，「%と%が，"B"と"A"」と考えれば該当．
「%と%が，"BA"と""」と考えれば該当．
B は，%をどのようにおいても該当しない．
R-76
文字'%'を検索するとき
'%'をそのまま書くと，特殊な意味になってしまう．
LIKE '100%'には，「100%」ではなく「100から始まる
任意の文字列」が該当してしまう．
LIKE '100\%'とすれば「100%」の意味になる．
LIKE '%\%'は，「～%」がマッチする．(%で終わる任意
の文字列)
R-77
LIKE演算子
userID
name
0
ABC
1
BBC
2
BBCC
userID
name
3
BC
0
ABC
4
BAC
1
BBC
5
B
2
BBCC
表"user_tbl"
SELECT * FROM
user_tbl WHERE name
LIKE '_B%';
R-78
関数
R-79
本講義で頻繁に用いる表の設計
表"usertbl"
userID
name
passwd
email
登録ユーザの表
0
fukuda
yasu [email protected]
1
abe
shin
[email protected]
2
koizumi
jun
[email protected]
3
mori
yoshi [email protected]
表"TODOtbl"
TODOID userID
0
0
1
0
2
2
3
2
4
3
txt
組閣
演説
民営化
解散
選挙
各ユーザが入力した
TODOデータ
R-80
SELECT と関数
SECECT name
FROM user_tbl;
SELECT UPPER(name), name
FROM user_tbl;
name
UPPER(name)
name
Fukuda Yasuo
FUKUDA YASUO
Fukuda Yasuo
Abe Shinzo
ABE SHINZO
Abe Shinzo
Koizumi Junichiro
KOIZUMI JUNICHIRO Koizumi Junichiro
UPPER関数は，文字列を大文字に変換．
LOWER関数は，文字列を小文字に変換．
R-81
SELECT と関数
userID
name
score
0
Fukuda Yasuo
80
1
Abe Shinzo
70
2
Koizumi Junichiro
90
SELECT SUM(score)
FROM user_tbl;
SUM(score)
240
SUM関数は，合計を返す．
R-82
SELECT と関数
userID
name
score
0
Fukuda Yasuo
80
1
Abe Shinzo
70
2
Koizumi Junichiro
90
SELECT AVG(score)
FROM user_tbl;
SUM(score)
80
AVG関数は，平均を返す．
R-83
SELECT と関数
userID
name
score
0
Fukuda Yasuo
80
1
Abe Shinzo
70
2
Koizumi Junichiro
90
SELECT COUNT(*)
FROM user_tbl;
COUNT(*)
3
COUNT関数は，件数を返す．
R-84
SELECT と関数
userID
name
score
0
Fukuda Yasuo
1
1
Abe Shinzo
1
2
Koizumi Junichiro
2
SELECT COUNT(ALL score)
FROM user_tbl;
COUNT(ALL score)
3
COUNT関数は，件数を返す．
列名を指定しても良い．
R-85
SELECT と関数
userID
name
score
0
Fukuda Yasuo
1
1
Abe Shinzo
1
2
Koizumi Junichiro
2
SELECT COUNT(DISTINCT
score)
FROM user_tbl;
COUNT(DISTINCT score) COUNT関数は，件数を返す．
2
DISTINCTを付けると
重複は1件として数える．
R-86
SELECT と関数
userID
name
score
0
Fukuda Yasuo
1
1
Abe Shinzo
1
2
Koizumi Junichiro
2
SELECT COUNT(*)
FROM user_tbl WHERE
score=1;
COUNT(DISTINCT score)
2
score=1の件数を数える．
R-87
SELECT と関数
• 関数MAX()は，最大値を返す．
• 関数MIN()は，最小値を返す．
R-88
練習
studentID
class
score
0
A
10
1
A
20
2
A
30
3
B
40
4
B
50
5
B
60
クラスAの学生(3人)の平均点を出すSQL文は？
R-89
SQLのGROUP BY
R-90
GROUP BY
• 指定した列の値が同じなら，それらをまと
めて1個のGROUPにすることが可能
R-91
GROUP BY の例
userID
0
1
2
3
4
class
M
M
M
M
O
name
fukuda
abe
koizumi
mori
obuchi
表:usrtbl
SELECT * FROM usertbl;
userID
0
1
2
3
4
class
M
M
M
M
O
name
fukuda
abe
koizumi
mori
obuchi
R-92
GROUP BY の例
userID
0
1
2
3
4
class
M
M
M
M
O
name
fukuda
abe
koizumi
mori
obuchi
表:usrtbl
class
M
M
M
M
SELECT class FROM usrtbl;
O
SELECT class FROM usrtbl GROUP BY class;
class
M
O R-93
GROUP BY の例
userID
0
1
2
3
4
class
M
M
M
M
O
name
fukuda
abe
koizumi
mori
obuchi
表:usrtbl
SELECT class, count(class) FROM usrtbl
GROUP BY class;
class
M
O
count
4
1
R-94
GROUP BY の例
userID
0
1
2
3
4
class
M
M
M
M
O
name
fukuda
abe
koizumi
mori
obuchi
表:usrtbl
SELECT * FROM usrtbl GROUP BY class;
これはERROR!
userID
??
??
class
M
O
name
??
??
ここに表示すべき項目が，
定まらない！
fukuda? abe?
koizumi? mori?
R-95
DISTINCT (GROUP BYと類似)
userID
0
1
2
3
4
class
M
M
M
M
O
name
fukuda
abe
koizumi
mori
obuchi
表:usrtbl
class
M
M
M
M
SELECT class FROM usrtbl;
O
SELECT class FROM usrtbl GROUP BY class;
class
M
O
SELECT DISTINCT class
FROM usrtbl;
R-96
DISTINCTとGROUP BY
• DISTINCTは単に重複データを削除する
のみ
• GROUP BYはグループ化し,その後集合
関数を用いるのが一般的．
R-97
DISTINCTとGROUP BY
DISTINCT編
userID
0
1
2
3
4
class
M
M
M
M
O
name
fukuda
abe
koizumi
mori
obuchi
表:usrtbl
class
M
M
M
SELECT class FROM usrtbl;
M
O
重複を削除
class
SELECT DISTINCT class FROM usrtbl;
M
O
R-98
DISTINCTとGROUP BY
GROUP BY編
userID
0
1
2
3
4
class
M
M
M
M
O
name
fukuda
abe
koizumi
mori
obuchi
表:usrtbl
"GROUP BY class"によるグループ化
userID
0
1
2
3
4
class
M
O
name
fukuda
abe
koizumi
mori
obuchi
R-99
DISTINCTとGROUP BY
GROUP BY編
userID
0
1
2
3
4
class
M
O
name
fukuda
abe
koizumi
mori
obuchi
表:usrtblを
GROUP BY classしたもの
SELECT class FROM usrtbl GROUP BY class;
class
M
O
R-100
DISTINCTとGROUP BY
GROUP BY編
userID class
name
表:usrtblを
0
fukuda
GROUP BY classしたもの
1
abe
M
2
koizumi
3
mori
4
O
obuchi
SELECT class, count(class) FROM usrtbl
GROUP BY class;
class
count
M
4
O
1
R-101
GROUP BY
userID
0
1
2
3
4
class
M
M
M
M
O
blood
A
B
A
O
A
表:usrtbl
name
fukuda
abe
koizumi
mori
obuchi
classと
blood
が同じ
SELECT class,blood FROM usrtbl
GROUP BY class, blood;
class
blood
M
A
←ここには fukuda と koizumi がある
M
M
O
B
O
A
←ここには abe がある
←ここには mori がある
←ここには obuchi がある
R-102
GROUP BY
userID
0
1
2
3
4
class
M
M
M
M
O
blood
A
B
A
O
A
name
fukuda
abe
koizumi
mori
obuchi
表:usrtbl
classと
blood
が同じ
SELECT class,blood,count(*)
FROM usrtbl GROUP BY class, blood;
class
blood
count
M
A
2
←fukuda と koizumi
M
M
O
B
O
A
1
1
1
←abe
←mori
←obuchi
R-103
結合 JOIN
R-104
結合
• 2個の表を組み合わせて，別の表を作成
する操作を「結合」という
– 交差結合(CROSS JOIN)
– 内部結合(INNER JOIN)
– 左外部結合(LEFT OUTER JOIN)
– 右外部結合(RIGHT OUTER JOIN)
– 全外部結合(FULL OUTER JOIN)
R-105
交差結合
表"tblA"
aID
0
1
2
表"tblB"
bID
0
1
4
CROSS JOIN
tblA.aID
0
0
0
1
1
1
2
2
2
tblB.bID
0
1
4
0
1
4
0
1
4
R-106
内部結合
表"tblA"
aID
0
1
2
表"tblB"
bID
0
1
4
INNER JOIN
ON
tblA.aID = tblB.bID
tblA.aID
0
1
tblB.bID
0
1
R-107
左外部結合
表"tblA"
aID
0
1
2
表"tblB"
bID
0
1
4
LEFT OUTER JOIN
ON
tblA.aID = tblB.bID
tblA.aID
0
1
2
tblB.bID
0
1
NULL
R-108
左外部結合
表"tblA"
aID
0
1
2
表"tblB"
bID
0
1
4
RIGHT OUTER JOIN
ON
tblA.aID = tblB.bID
tblA.aID
0
1
NULL
tblB.bID
0
1
4
R-109
全外部結合
表"tblA"
aID
0
1
2
表"tblB"
bID
0
1
4
RIGHT OUTER JOIN
ON
tblA.aID = tblB.bID
tblA.aID
0
1
２
NULL
tblB.bID
0
1
NULL
4
R-110
交差結合 CROSS JOIN
表"a"
id
0
1
2
表"b"
name
fukuda
abe
CROSS JOIN
3列の表と，
2列の表を交差結合
すると，
3×2=6列になる．
id
0
0
1
1
2
2
• 両表の1列ずつを
組み合わせて(横
に並べて)新しい表
を作成．
name
• 全組み合わせ存在．
fukuda
abe
fukuda
abe
fukuda
abe
R-111
交差結合 CROSS JOIN
表"a"
id
0
1
2
表"b"
name
fukuda
abe
CROSS JOIN
id
0
0
1
1
2
2
表aと表bの交差結合を得るには，
SELECT * FROM a CROSS JOIN b;
SELECT * FROM a, b;
name
fukuda
abe
fukuda
abe
fukuda
abe
id | name
----+-------0 | fukuda
0 | abe
1 | fukuda
1 | abe
2 | fukuda
2 | abe R-112
交差結合 CROSS JOIN
表"usertbl"
userID
name
0
fukuda
1
abe
2
koizumi
登録ユーザの表
CROSS JOIN
userID=1と
userID=2を
横に並べた行.
あまり意味がない
userID
0
0
0
1
1
1
2
2
2
表"TODOtbl"
TODOID userID
txt
0
0
組閣する
1
0
演説する
2
2
解散する
各ユーザが入力したTODOデータ
name
TODOID userID
fukuda
0
0
fukuda
1
0
fukuda
2
2
abe
0
0
abe
1
0
abe
2
2
koizumi
0
0
koizumi
1
0
koizumi
2
2
txt
組閣する
演説する
解散する
組閣する
演説する
解散する
組閣する
演説する
解散する
R-113
内部結合 INNER JOIN
表"usertbl"
userID
name
0
fukuda
1
abe
2
koizumi
INNER JOIN on
usertbl.userID
= TODOtbl.userID
• 交差結合のうち，条
件にあうもののみを
選択．
TODOID
userID
txt
0
1
2
userID
name
TODOID userID
0
fukuda
0
0
0
fukuda
1
0
2
koizumi
2
2
0
0
2
組閣する
演説する
解散する
表"TODOtbl"
txt
組閣する
演説する
解散する
R-114
内部結合 INNER JOIN
表"usertbl"
userID
name
0
fukuda
1
abe
2
koizumi
INNER JOIN
on
usertbl.userID
=
TODOtbl.useriD
userID
0
0
0
1
1
1
2
2
2
表"TODOtbl"
TODOID userID
0
0
1
0
2
2
name
TODOID userID
fukuda
0
0
fukuda
1
0
fukuda
2
2
abe
0
0
abe
1
0
abe
2
2
koizumi
0
0
koizumi
1
0
koizumi
2
2
txt
組閣する
演説する
解散する
txt
組閣する
演説する
解散する
組閣する
演説する
解散する
組閣する
演説する
解散する
R-115
内部結合 INNER JOIN
表"usertbl"
userID
name
0
fukuda
1
abe
2
koizumi
表"TODOtbl"
TODOID userID
0
0
1
0
2
2
txt
組閣する
演説する
解散する
INNER JOIN
on
usertbl.userID
=
TODOtbl.userID
userID
name
TODOID userID
0
fukuda
0
0
0
fukuda
1
0
2
koizumi
2
2
txt
組閣する
演説する
解散する
R-116
内部結合 INNER JOIN
• SELECT * FROM usertbl INNER JOIN
TODOtbl
on usertbl.userID = TODOtbl.userID;
• SELECT * FROM usertbl, TODOtbl
WHERE usertbl.userID = TODOtbl.userID;
R-117
内部結合 INNER JOIN
•SELECT対象の列を指定するには，表名を明記する．
SELECT
TODOtbl.TODOID, TODOtbl.txt, usertbl.name
FROM TODOtbl INNER JOIN usertbl
ON TODOtbl.userID = usertbl.userID;
TODOID
0
1
2
txt
組閣する
演説する
解散する
name
fukuda
fukuda
koizumi
R-118
内部結合 INNER JOIN
表"usertbl"
userID
name
0
fukuda
1
abe
2
koizumi
表"TODOtbl"
TODOID userID
0
0
1
0
2
2
txt
組閣する
演説する
解散する
SELECT * FROM usertbl INNER JOIN TODOtbl
on usertbl.userid = TODOtbl.userid;
userID | name
| TODOID | userID | txt
--------+---------+--------+--------+-------0 | fukuda |
0 |
0 | 組閣する
0 | fukuda |
1 |
0 | 演説する
2 | koizumi |
2 |
2 | 解散する
R-119
外部結合
• 左外部結合，右外部結合，全外部結合と
ある．
• 交差結合，内部結合と同様だが，
相手の表に対応するものが無い場合も表
示される．
– 対応するものが無い場合は，NULLで表示
R-120
左外部結合 LEFT OUTER JOIN
• 左外部結合は，(例え右の表に対応する
行が無くても)左の表の行は必ず表示され
る．
– 右の表に対応する行が無い場合はNULLを表
示
SELECT 左の表
* FROM tblA LEFT OUTER
JOIN tblB
右の表
on ...;
R-121
左外部結合 LEFT OUTER JOIN
表"usertbl"
userID
name
0
fukuda
1
abe
2
koizumi
表"TODOtbl"
TODOID userID
0
0
1
0
2
2
txt
組閣する
演説する
解散する
SELECT * from usertbl LEFT OUTER JOIN TODOtbl
on usertbl.userID = TODOtbl.userid;
userID
0
name
fukuda
TODOID
0
1
1
abe
該当なし
2
koizumi
2
userID
0
0
2
txt
組閣する
演説する
解散するR-122
左外部結合 LEFT OUTER JOIN
表"usertbl"
userID
name
0
fukuda
1
abe
2
koizumi
表"TODOtbl"
TODOID userID
0
0
1
0
2
2
txt
組閣する
演説する
解散する
SELECT * from usertbl LEFT OUTER JOIN TODOtbl
on usertbl.userID = TODOtbl.userid;
userID
name
TODOID
0
fukuda
0
0
fukuda
1
1
abe
NULL
2
koizumi
2
userID
0
0
NULL
2
txt
組閣する
演説する
NULL
解散する
R-123
左外部結合 LEFT OUTER JOIN
表"TODOtbl"
TODOID userID
0
0
1
0
2
2
txt
組閣する
演説する
解散する
表"usertbl"
userID
name
0
fukuda
1
abe
2
koizumi
SELECT * from TODOtbl LEFT OUTER JOIN usertbl
on TODOtbl.userID = usertbl.userid;
TODOID
0
1
2
userID
0
0
2
txt
組閣する
演説する
解散する
userID
name
0
fukuda
0
fukuda
2
koizumi
R-124
左外部結合 LEFT OUTER JOIN
表"TODOtbl"
TODOID userID
0
0
1
0
2
2
txt
組閣する
演説する
解散する
表"usertbl"
userID
name
0
fukuda
1
abe
2
koizumi
SELECT * FROM TODOtbl LEFT OUTER JOIN usertbl
on TODOtbl.userID = usertbl.userid;
TODOID
0
1
2
userID
0
0
2
txt
組閣する
演説する
解散する
userID
name
0
fukuda
0
fukuda
2
koizumi
R-125
右外部結合 RIGHT OUTER JOIN
• 右外部結合は，(例え左の表に対応する
行が無くても)右の表の行は必ず表示され
る．
– 左の表に対応する行が無い場合はNULLを表
示
SELECT * FROM tblA LEFT OUTER JOIN tblB
左の表
右の表
on ...;
R-126
右外部結合 RIGHT OUTER JOIN
表"usertbl"
userID
name
0
fukuda
1
abe
2
koizumi
表"TODOtbl"
TODOID userID
0
0
1
0
2
2
SELECT * FROM usertbl RIGHT OUTER JOIN
TODOtbl
= TODOtbl.userid;
userIDon usertbl.userID
name
TODOID
userID
txt
組閣する
演説する
解散する
0
fukuda
0
0
txt
組閣する
1
abe
1
0
演説する
2
koizumi
2
2
解散する
R-127
右外部結合 RIGHT OUTER JOIN
表"usertbl"
userID
name
0
fukuda
1
abe
2
koizumi
表"TODOtbl"
TODOID userID
0
0
1
0
2
2
txt
組閣する
演説する
解散する
SELECT * FROM usertbl RIGHT OUTER JOIN
TODOtbl
= TODOtbl.userid;
userIDon usertbl.userID
name
TODOID
userID
txt
0
1
2
fukuda
abe
koizumi
0
1
2
0
0
2
組閣する
演説する
解散する
R-128
右外部結合 RIGHT OUTER JOIN
表"TODOtbl"
TODOID userID
0
0
1
0
2
2
txt
組閣する
演説する
解散する
表"usertbl"
userID
name
0
fukuda
1
abe
2
koizumi
SELECT * FROM TODOtbl RIGHT OUTER JOIN
usertbl
usertbl.userIDtxt
= TODOtbl.userid;
TODOIDon userID
userID
0
1
0
0
組閣する
演説する
該当無し
2
2
解散する
0
name
fukuda
1
abe
2
koizumi
R-129
右外部結合 RIGHT OUTER JOIN
表"TODOtbl"
TODOID userID
0
0
1
0
2
2
表"usertbl"
userID
name
0
fukuda
1
abe
2
koizumi
txt
組閣する
演説する
解散する
SELECT * FROM TODOtbl RIGHT OUTER JOIN
usertbl
usertbl.userIDtxt
= TODOtbl.userid;
TODOIDon userID
userID
name
0
1
NULL
2
0
0
NULL
2
組閣する
演説する
NULL
解散する
0
0
1
2
fukuda
fukuda
abe
koizumi
R-130
全外部結合 FULL OUTER JOIN
• 全外部結合は，左外部結合と右外部結合
の両方の要素が表示される．
– 左の表に対応する行が無い場合は，左の表
の列にNULLを表示
– 右の表に対応する行が無い場合は，右の表
の列にNULLを表示
R-131
全外部結合 FULL OUTER JOIN
表"usertbl"
userID
name
0
fukuda
1
abe
2
koizumi
表"TODOtbl"
TODOID userID
0
0
1
0
2
2
3
4
txt
組閣する
演説する
解散する
公演する
SELECT * FROM usertbl FULL OUTER JOIN TODOtbl
on usertbl.userID = TODOtbl.userid;
userID
name
TODOID
0
fukuda
0
0
fukuda
1
1
abe
NULL
2
koizumi
2
NULL
NULL
3
userID
0
0
NULL
2
4
txt
組閣する
演説する
NULL
解散する
公演する
R-132
関係代数
R-133
和両立
• 2個の表があり，
「両表の次数(列数)が等しい」かつ
「各列の意味が等しい」とき
「和両立」という．
表"連絡表"
名前
連絡先
123-456
福田
789-012
安倍
345-678
小泉
表"ユーザ表"
ユーザ名
電話番号
345-678
小泉
901-234
森
両表は
・列数が等しい(ともに3列)
・各列の意味が等しい
ので，「和両立」である．
R-134
和集合演算
• 和両立な表Rと表Sがあり,「和集合演算」
とは以下の演算．
• それぞれの表の全行をあわせた表を作成
する演算が「和集合演算」．
ただし，重複する行は1行にまとめる．
– 集合論(RDBMS),では「同一の行が2回以上
登場」することは許されない．
– 実際は，RDBMSに全く同じ行を2個以上作成し
てもエラーにならないことが多い．
• 「R∪S」と記述．論理和に近い感覚．
R-135
和集合演算
表"連絡表"
名前
連絡先
123-456
福田
789-012
安倍
345-678
小泉
和集合演算
「小泉,345-678」が2回
登場することはない
表"ユーザ表"
ユーザ名
電話番号
345-678
小泉
901-234
森
名前
福田
安倍
小泉
森
連絡先
123-456
789-012
345-678
901-234
R-136
差集合演算
• 和両立な表Rと表Sがあり,「差集合演算」
とは以下の演算．
• 表Rから，「表Sに登場する行」を削除した
表を作成するのが差集合演算．
– 「Sに登場するが，Rに登場しない行」は(削除
対象が存在せず)削除されない．
• 「R-S」と記述
R-137
差集合演算
表"連絡表"
名前
連絡先
123-456
福田
789-012
安倍
345-678
小泉
表"ユーザ表"
ユーザ名
電話番号
345-678
小泉
901-234
森
名前
福田
安倍
差集合演算
連絡先
123-456
789-012
小泉
345-678
←"連絡表"からこの行が削除された
森
901-234
←
この行は"連絡表"に無いので，
R-138
特に何も起きない．
共通集合演算
• 和両立な表Rと表Sがあり,「共通集合演
算」とは以下の演算．
• 表Rと表Sの両方に登場する行で構成され
る表を作成するのが，共通集合演算．
• 「R∩S」と記述．論理積に近い感覚．
R-139
共通集合演算
表"連絡表"
名前
連絡先
123-456
福田
789-012
安倍
345-678
小泉
共通集合演算
表"ユーザ表"
ユーザ名
電話番号
345-678
小泉
901-234
森
名前
小泉
連絡先
345-678
R-140
直積演算
• 前述の交差結合をおこなうこと．
• 演算対象の表Rと表Sは和両立である必要
はない．
• 「R×S」と記述．
R-141
直積演算
表"連絡表"
名前
連絡先
123-456
福田
789-012
安倍
345-678
小泉
表"商品表"
商品名
牛乳
ジュース
価格
100
120
直積集合演算
名前
福田
福田
安倍
安倍
小泉
小泉
連絡先
123-456
123-456
789-012
789-012
345-678
345-678
商品名
牛乳
ジュース
牛乳
ジュース
牛乳
ジュース
価格
100
120
100
120
100
120
R-142
射影演算
• 表から特定の列を抜き出す演算
• SELECT ?? FROM ...;
ここで
行っている．
R-143
射影演算
表"連絡表"
名前
連絡先
123-456
福田
789-012
安倍
345-678
小泉
射影演算
名前
福田
安倍
小泉
R-144
選択演算
• 表から特定の行を抜き出す演算
• SELECT * FROM WHERE ??;
ここで
行っている．
R-145
射影演算
表"連絡表"
名前
連絡先
123-456
福田
789-012
安倍
345-678
小泉
選択演算
名前
安倍
小泉
連絡先
789-012
345-678
R-146
結合演算
• 「結合演算」とは,
表Rと表SがありR×S(直積)の中から
それぞれの属性値を用いて特定の行を選
択したもの．
• 演算対象の表Rと表Sは和両立である必要
はない．
• 前述のSQLの結合演算．
R-147
商演算
• 表Rと表Sの商演算は，「R÷S」と記述．
Rの列数＞Sの列数であることが必要．
• 説明は，次スライドの例を用いて行う．
• 代数的定義は
R÷S={ t | t∈R ∧ (∀u∈S)((t , u)∈R) }
R-148