RDBMD 2/3

プログラミング論 II
2008年12月11日
RDBMSとSQL(2/3)
関数,GROUP,JOIN,演算
http://www.ns.kogakuin.ac.jp/~ct13140/Prog.2008/
M-1
概要
• RDBMS の SQLについて
– 先週の続き．主にSELECT文
– 簡単
• GROUP
– 簡単
• JOIN(結合)
– おそらく簡単
• 関係代数の演算
– 簡単
M-2
本講義で頻繁に用いる表の設計
表"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データ
M-3
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関数は，文字列を小文字に変換．
M-4
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関数は，合計を返す．
M-5
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関数は，平均を返す．
M-6
SELECT と関数
userID
name
score
0
Fukuda Yasuo
80
1
Abe Shinzo
70
2
Koizumi Junichiro
90
SELECT COUNT(*)
FROM user_tbl;
COUNT(*)
3
COUNT関数は，件数を返す．
M-7
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関数は，件数を返す．
列名を指定しても良い．
M-8
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件として数える．
M-9
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の件数を数える．
M-10
SELECT と関数
• 関数MAX()は，最大値を返す．
• 関数MIN()は，最小値を返す．
M-11
課題
studentID
class
score
0
A
10
1
A
20
2
A
30
3
B
40
4
B
50
5
B
60
クラスAの学生(3人)の平均点を出すSQL文は？
M-12
SQLのGROUP BY
M-13
GROUP BY
• 指定した列の値が同じなら，それらをまと
めて1個のGROUPにすることが可能
M-14
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
M-15
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 M-16
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
M-17
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?
M-18
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;
M-19
DISTINCTとGROUP BY
• DISTINCTは単に重複データを削除する
のみ
• GROUP BYはグループ化し,その後集合
関数を用いるのが一般的．
M-20
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
M-21
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
M-22
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
M-23
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
M-24
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 がある
M-25
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
M-26
結合
M-27
結合
• 2個の表を組み合わせて，別の表を作成
する操作を「結合」という
– 交差結合(CROSS JOIN)
– 内部結合(INNER JOIN)
– 左外部結合(LEFT OUTER JOIN)
– 右外部結合(RIGHT OUTER JOIN)
– 全外部結合(FULL OUTER JOIN)
M-28
交差結合
表"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
M-29
内部結合
表"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
M-30
左外部結合
表"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
M-31
左外部結合
表"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
M-32
全外部結合
表"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
M-33
交差結合 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
M-34
交差結合 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
M-35
交差結合 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
組閣する
演説する
解散する
組閣する
演説する
解散する
組閣する
演説する
解散する
M-36
内部結合 INNER JOIN
表"usertbl"
userID
name
0
fukuda
1
abe
2
koizumi
INNER JOIN on
usertbl.userID
= TODOtbl.userID
• 交差結合のうち，条件
にあうもののみを選択．
TODOID
0
1
2
userID
name
TODOID userID
0
fukuda
0
0
0
fukuda
1
0
2
koizumi
2
2
userID
0
0
2
txt
組閣する
演説する
解散する
表"TODOtbl"
txt
組閣する
演説する
解散する
M-37
内部結合 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
組閣する
演説する
解散する
組閣する
演説する
解散する
組閣する
演説する
解散する
M-38
内部結合 INNER JOIN
表"usertbl"
userID
name
0
fukuda
1
abe
2
koizumi
表"TODOtbl"
memoID 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
組閣する
演説する
解散する
M-39
内部結合 INNER JOIN
• SELECT * FROM usertbl INNER JOIN TODOtbl
on usertbl.userID = TODOtbl.userID;
• SELECT * FROM usertbl, TODOtbl
WHERE usertbl.userID = TODOtbl.userID;
M-40
内部結合 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
M-41
内部結合 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 | 解散する
M-42
外部結合
• 左外部結合，右外部結合，全外部結合と
ある．
• 交差結合，内部結合と同様だが，
相手の表に対応するものが無い場合も表
示される．
– 対応するものが無い場合は，NULLで表示
M-43
左外部結合 LEFT OUTER JOIN
• 左外部結合は，(例え右の表に対応する行が無
くても)左の表の行は必ず表示される．
– 右の表に対応する行が無い場合はNULLを表示
SELECT * FROM tblA LEFT OUTER JOIN tblB on ...;
左の表
右の表
M-44
左外部結合 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
組閣する
演説する
解散するM-45
左外部結合 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
解散する
M-46
左外部結合 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
M-47
左外部結合 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
M-48
右外部結合 RIGHT OUTER JOIN
• 右外部結合は，(例え左の表に対応する行が無
くても)右の表の行は必ず表示される．
– 左の表に対応する行が無い場合はNULLを表示
SELECT * FROM tblA LEFT OUTER JOIN tblB on ...;
左の表
右の表
M-49
右外部結合 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
on usertbl.userID = TODOtbl.userid;
userID
0
name
fukuda
TODOID
0
userID
0
txt
組閣する
1
abe
1
0
演説する
2
koizumi
2
2
解散する
M-50
右外部結合 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
on usertbl.userID = TODOtbl.userid;
userID
name
TODOID
0
fukuda
0
1
abe
1
2
koizumi
2
userID
0
0
2
txt
組閣する
演説する
解散する
M-51
右外部結合 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
on usertbl.userID = TODOtbl.userid;
TODOID
0
1
userID
0
0
txt
組閣する
演説する
該当無し
2
2
解散する
userID
0
name
fukuda
1
abe
2
koizumi
M-52
右外部結合 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
on usertbl.userID = TODOtbl.userid;
TODOID
0
1
NULL
2
userID
0
0
NULL
2
txt
組閣する
演説する
NULL
解散する
userID
name
0
fukuda
0
fukuda
1
abe
2
koizumi
M-53
全外部結合 FULL OUTER JOIN
• 全外部結合は，左外部結合と右外部結合の両
方の要素が表示される．
– 左の表に対応する行が無い場合は，左の表の列に
NULLを表示
– 右の表に対応する行が無い場合は，右の表の列に
NULLを表示
M-54
全外部結合 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
解散する
公演する
M-55
関係代数
M-56
和両立
• 2個の表があり，
「両表の次数(列数)が等しい」かつ
「各列の意味が等しい」とき
「和両立」という．
表"連絡表"
名前
連絡先
123-456
福田
789-012
安倍
345-678
小泉
表"ユーザ表"
ユーザ名
電話番号
345-678
小泉
901-234
森
両表は
・列数が等しい(ともに3列)
・各列の意味が等しい
ので，「和両立」である．
M-57
和集合演算
• 和両立な表Rと表Sがあり,「和集合演算」
とは以下の演算．
• それぞれの表の全行をあわせた表を作成
する演算が「和集合演算」．
ただし，重複する行は1行にまとめる．
– 集合論(RDBMS),では「同一の行が2回以上
登場」することは許されない．
– 実際は，RDBMSに全く同じ行を2個以上作成し
てもエラーにならないことが多い．
• 「R∪S」と記述．論理和に近い感覚．
M-58
和集合演算
表"連絡表"
名前
連絡先
123-456
福田
789-012
安倍
345-678
小泉
和集合演算
「小泉,345-678」が2回
登場することはない
表"ユーザ表"
ユーザ名
電話番号
345-678
小泉
901-234
森
名前
福田
安倍
小泉
森
連絡先
123-456
789-012
345-678
901-234
M-59
差集合演算
• 和両立な表Rと表Sがあり,「差集合演算」
とは以下の演算．
• 表Rから，「表Sに登場する行」を削除した
表を作成するのが差集合演算．
– 「Sに登場するが，Rに登場しない行」は(削除
対象が存在せず)削除されない．
• 「R-S」と記述
M-60
差集合演算
表"連絡表"
名前
連絡先
123-456
福田
789-012
安倍
345-678
小泉
表"ユーザ表"
ユーザ名
電話番号
345-678
小泉
901-234
森
名前
福田
安倍
差集合演算
連絡先
123-456
789-012
小泉
345-678
←"連絡表"からこの行が削除された
森
901-234
←
この行は"連絡表"に無いので，
M-61
特に何も起きない．
共通集合演算
• 和両立な表Rと表Sがあり,「共通集合演
算」とは以下の演算．
• 表Rと表Sの両方に登場する行で構成され
る表を作成するのが，共通集合演算．
• 「R∩S」と記述．論理積に近い感覚．
M-62
共通集合演算
表"連絡表"
名前
連絡先
123-456
福田
789-012
安倍
345-678
小泉
共通集合演算
表"ユーザ表"
ユーザ名
電話番号
345-678
小泉
901-234
森
名前
小泉
連絡先
345-678
M-63
直積演算
• 前述の交差結合をおこなうこと．
• 演算対象の表Rと表Sは和両立である必要
はない．
• 「R×S」と記述．
M-64
直積演算
表"連絡表"
名前
連絡先
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
M-65
射影演算
• 表から特定の列を抜き出す演算
• SELECT ?? FROM ...;
ここで
行っている．
M-66
射影演算
表"連絡表"
名前
連絡先
123-456
福田
789-012
安倍
345-678
小泉
射影演算
名前
福田
安倍
小泉
M-67
選択演算
• 表から特定の行を抜き出す演算
• SELECT * FROM WHERE ??;
ここで
行っている．
M-68
射影演算
表"連絡表"
名前
連絡先
123-456
福田
789-012
安倍
345-678
小泉
選択演算
名前
安倍
小泉
連絡先
789-012
345-678
M-69
結合演算
• 「結合演算」とは,
表Rと表SがありR×S(直積)の中から
それぞれの属性値を用いて特定の行を選
択したもの．
• 演算対象の表Rと表Sは和両立である必要
はない．
• 前述のSQLの結合演算．
M-70
商演算
• 表Rと表Sの商演算は，「R÷S」と記述．
Rの列数＞Sの列数であることが必要．
• 説明は，次スライドの例を用いて行う．
• 代数的定義は
R÷S={ t | t∈R ∧ (∀u∈S)((t , u)∈R) }
M-71
商演算
表"ユーザ趣味表"
ユーザ
趣味
A
福田
B
福田
B
安倍
C
安倍
A
小泉
B
小泉
C
小泉
表"ユーザ趣味表"から，
「趣味A」と「趣味B」を両方持つ
ユーザを捜してくること．
表"趣味表"
趣味
A
B
商演算
名前
福田
小泉
M-72
最後に
1年間，お疲れ様でした！
• 当然ですが，試験勉強頑張ってください．
– 最低でも試験前は勉強しましょう．
– 「試験前のみ勉強」を推奨しているのではない．
• しかし，試験はさておき，「プログラムが書
ける」様になってください．
–情報系技術者なら，「プログラムくら
い書けて当たり前」です．
M-73

Download Report