2008年12月11日 RDBMSとSQL(2/3) 関数,GROUP,JOIN,演算

Presentation on theme: "2008年12月11日 RDBMSとSQL(2/3) 関数,GROUP,JOIN,演算"— Presentation transcript:

1 2008年12月11日 RDBMSとSQL(2/3) 関数,GROUP,JOIN,演算
プログラミング論 II 2008年12月11日 RDBMSとSQL(2/3) 関数,GROUP,JOIN,演算

2 概要 RDBMS の SQLについて 先週の続き．主にSELECT文 簡単 GROUP JOIN(結合) おそらく簡単 関係代数の演算

3 本講義で頻繁に用いる表の設計 表"usertbl" userID name passwd email 登録ユーザの表 fukuda yasu
fukuda yasu 1 abe shin 2 koizumi jun 3 mori yoshi 表"TODOtbl" TODOID userID txt 組閣 各ユーザが入力した TODOデータ 1 演説 2 2 民営化 3 2 解散 4 3 選挙

4 SELECT と 関数 SECECT name FROM user_tbl; SELECT UPPER(name), name
Fukuda Yasuo FUKUDA YASUO Fukuda Yasuo Abe Shinzo ABE SHINZO Abe Shinzo Koizumi Junichiro KOIZUMI JUNICHIRO Koizumi Junichiro UPPER関数は，文字列を大文字に変換． LOWER関数は，文字列を小文字に変換．

5 SELECT と 関数 SELECT SUM(score) FROM user_tbl; SUM関数は，合計を返す． userID name
Fukuda Yasuo 80 1 Abe Shinzo 70 2 Koizumi Junichiro 90 SELECT SUM(score) FROM user_tbl; SUM(score) SUM関数は，合計を返す． 240

6 SELECT と 関数 SELECT AVG(score) FROM user_tbl; AVG関数は，平均を返す． userID name
Fukuda Yasuo 80 1 Abe Shinzo 70 2 Koizumi Junichiro 90 SELECT AVG(score) FROM user_tbl; SUM(score) AVG関数は，平均を返す． 80

7 SELECT と 関数 SELECT COUNT(*) FROM user_tbl; COUNT関数は，件数を返す． userID name
score Fukuda Yasuo 80 1 Abe Shinzo 70 2 Koizumi Junichiro 90 SELECT COUNT(*) FROM user_tbl; COUNT(*) COUNT関数は，件数を返す． 3

8 SELECT COUNT(ALL score)
userID name score Fukuda Yasuo 1 1 Abe Shinzo 1 2 Koizumi Junichiro 2 SELECT COUNT(ALL score) FROM user_tbl; COUNT(ALL score) COUNT関数は，件数を返す． 列名を指定しても良い． 3

9 SELECT と 関数 SELECT COUNT(DISTINCT score) FROM user_tbl;
userID name score Fukuda Yasuo 1 1 Abe Shinzo 1 2 Koizumi Junichiro 2 SELECT COUNT(DISTINCT score) FROM user_tbl; COUNT(DISTINCT score) COUNT関数は，件数を返す． DISTINCTを付けると 重複は1件として数える． 2

10 SELECT と 関数 SELECT COUNT(*) FROM user_tbl WHERE score=1;
userID name score Fukuda Yasuo 1 1 Abe Shinzo 1 2 Koizumi Junichiro 2 SELECT COUNT(*) FROM user_tbl WHERE score=1; COUNT(DISTINCT score) score=1の件数を数える． 2

11 SELECT と 関数 関数MAX()は，最大値を返す． 関数MIN()は，最小値を返す．

12 課題 studentID class score A 10 1 A 20 2 A 30 3 B 40 4 B 50 5 B 60
A 10 1 A 20 2 A 30 3 B 40 4 B 50 5 B 60 クラスAの学生(3人)の平均点を出すSQL文は？

13 SQLのGROUP BY

14 GROUP BY 指定した列の値が同じなら，それらをまとめて1個のGROUPにすることが可能

15 GROUP BY の例 userID class name M fukuda 表:usrtbl 1 M abe 2 M koizumi 3
M fukuda 表:usrtbl 1 M abe 2 M koizumi 3 M mori 4 O obuchi SELECT * FROM usertbl; userID class name M fukuda 1 M abe 2 M koizumi 3 M mori 4 O obuchi

16 GROUP BY の例 userID class name 表:usrtbl M fukuda 1 M abe 2 M koizumi 3
M fukuda 1 M abe 2 M koizumi 3 M mori class 4 O obuchi M M M SELECT class FROM usrtbl; M O SELECT class FROM usrtbl GROUP BY class; class M O

17 GROUP BY の例 userID class name M fukuda 表:usrtbl 1 M abe 2 M koizumi 3
M fukuda 表:usrtbl 1 M abe 2 M koizumi 3 M mori 4 O obuchi SELECT class, count(class) FROM usrtbl GROUP BY class; class count M 4 O 1

18 GROUP BY の例 userID class name M fukuda 表:usrtbl 1 M abe 2 M koizumi 3
M fukuda 表:usrtbl 1 M abe 2 M koizumi 3 M mori 4 O obuchi SELECT * FROM usrtbl GROUP BY class; ここに表示すべき項目が， 定まらない！ fukuda? abe? koizumi? mori? これはERROR! userID class name ?? M ?? ?? O ??

19 DISTINCT (GROUP BYと類似)
userID class name 表:usrtbl M fukuda 1 M abe class 2 M koizumi M 3 M mori M 4 O obuchi M M SELECT class FROM usrtbl; O SELECT class FROM usrtbl GROUP BY class; class M O SELECT DISTINCT class FROM usrtbl;

20 DISTINCTとGROUP BY DISTINCTは単に重複データを削除するのみ

21 DISTINCTとGROUP BY DISTINCT編
userID class name 表:usrtbl M fukuda 1 M abe 2 M koizumi class 3 M mori M 4 O obuchi M M SELECT class FROM usrtbl; M O 重複を削除 class SELECT DISTINCT class FROM usrtbl; M O

22 DISTINCTとGROUP BY GROUP BY編
userID class name 表:usrtbl M fukuda 1 M abe 2 M koizumi 3 M mori 4 O obuchi "GROUP BY class"によるグループ化 userID class name M fukuda 1 abe 2 koizumi 3 mori 4 O obuchi

23 DISTINCTとGROUP BY GROUP BY編
userID class name 表:usrtblを GROUP BY classしたもの M fukuda 1 abe 2 koizumi 3 mori 4 O obuchi SELECT class FROM usrtbl GROUP BY class; class M O

24 DISTINCTとGROUP BY GROUP BY編
userID class name 表:usrtblを GROUP BY classしたもの M fukuda 1 abe 2 koizumi 3 mori 4 O obuchi SELECT class, count(class) FROM usrtbl GROUP BY class; class count M 4 O 1

25 GROUP BY 表:usrtbl userID class blood name M A fukuda classと blood が同じ
M A fukuda classと blood が同じ 1 M B abe 2 M A koizumi 3 M O mori 4 O A obuchi SELECT class,blood FROM usrtbl GROUP BY class, blood; class blood M A ←ここには fukuda と koizumi がある M B ←ここには abe がある M O ←ここには mori がある O A ←ここには obuchi がある

26 GROUP BY 表:usrtbl userID class blood name M A fukuda classと blood が同じ
M A fukuda classと blood が同じ 1 M B abe 2 M A koizumi 3 M O mori 4 O A obuchi SELECT class,blood,count(*) FROM usrtbl GROUP BY class, blood; class blood count M A 2 ←fukuda と koizumi M B 1 ←abe M O 1 ←mori O A 1 ←obuchi

27 結合

28 結合 2個の表を組み合わせて，別の表を作成する操作を「結合」という 交差結合(CROSS JOIN) 内部結合(INNER JOIN)
左外部結合(LEFT OUTER JOIN) 右外部結合(RIGHT OUTER JOIN) 全外部結合(FULL OUTER JOIN)

29 交差結合 表"tblA" 表"tblB" aID bID 1 1 2 4 tblA.aID tblB.bID 1 4 1 1 1
1 1 2 4 tblA.aID tblB.bID 1 4 1 1 1 CROSS JOIN 1 4 2 2 1 2 4

30 内部結合 表"tblA" 表"tblB" aID bID 1 1 2 4 tblA.aID tblB.bID INNER JOIN ON
1 1 2 4 tblA.aID tblB.bID INNER JOIN ON tblA.aID = tblB.bID 1 1

31 左外部結合 表"tblA" 表"tblB" aID bID 1 1 2 4 tblA.aID tblB.bID
1 1 2 4 tblA.aID tblB.bID LEFT OUTER JOIN ON tblA.aID = tblB.bID 1 1 2 NULL

32 左外部結合 表"tblA" 表"tblB" aID bID 1 1 2 4 tblA.aID tblB.bID
1 1 2 4 tblA.aID tblB.bID RIGHT OUTER JOIN ON tblA.aID = tblB.bID 1 1 NULL 4

33 全外部結合 表"tblA" 表"tblB" aID bID 1 1 2 4 tblA.aID tblB.bID
1 1 2 4 tblA.aID tblB.bID RIGHT OUTER JOIN ON tblA.aID = tblB.bID 1 1 ２ NULL NULL 4

34 交差結合 CROSS JOIN 両表の1列ずつを組み合わせて(横に並べて)新しい表を作成． 全組み合わせ存在． 表"a" 表"b" id
name fukuda 1 abe 2 id name fukuda CROSS JOIN abe 1 fukuda 3列の表と， 2列の表を交差結合 すると， 3×2=6列になる． 1 abe 2 fukuda 2 abe

35 交差結合 CROSS JOIN 表aと表bの交差結合を得るには， SELECT * FROM a CROSS JOIN b;
id name id name fukuda fukuda abe 1 abe 1 fukuda 2 1 abe 2 fukuda CROSS JOIN 2 abe 表aと表bの交差結合を得るには， SELECT * FROM a CROSS JOIN b; SELECT * FROM a, b; id | name 0 | fukuda 0 | abe 1 | fukuda 1 | abe 2 | fukuda 2 | abe

36 交差結合 CROSS JOIN 表"usertbl" 表"TODOtbl" userID name TODOID userID txt
fukuda 組閣する 1 abe 1 演説する 2 koizumi 2 2 解散する 登録ユーザの表 各ユーザが入力したTODOデータ userID 1 2 name fukuda abe koizumi TODOID txt 組閣する 演説する 解散する CROSS JOIN userID=1と userID=2を 横に並べた行. あまり意味がない

37 内部結合 INNER JOIN 交差結合のうち，条件にあうもののみを選択． 表"usertbl" userID name fukuda 1
fukuda 1 abe 2 koizumi TODOID userID txt 組閣する 1 演説する INNER JOIN on usertbl.userID = TODOtbl.userID 2 2 解散する 表"TODOtbl" userID 2 name fukuda koizumi TODOID 1 txt 組閣する 演説する 解散する

38 内部結合 INNER JOIN 表"usertbl" 表"TODOtbl" userID name TODOID userID txt
fukuda 組閣する 1 abe 1 演説する 2 koizumi 2 2 解散する userID name TODOID userID txt fukuda 組閣する fukuda 1 演説する INNER JOIN on usertbl.userID = TODOtbl.useriD fukuda 2 2 解散する 1 abe 組閣する 1 abe 1 演説する 1 abe 2 2 解散する 2 koizumi 組閣する 2 koizumi 1 演説する 2 koizumi 2 2 解散する

39 内部結合 INNER JOIN 表"usertbl" 表"TODOtbl" userID name memoID userID txt
fukuda 組閣する 1 abe 1 演説する 2 koizumi 2 2 解散する INNER JOIN on usertbl.userID = TODOtbl.userID userID name TODOID userID txt fukuda 組閣する fukuda 1 演説する 2 koizumi 2 2 解散する

40 内部結合 INNER JOIN SELECT * FROM usertbl INNER JOIN TODOtbl
on usertbl.userID = TODOtbl.userID; SELECT * FROM usertbl, TODOtbl WHERE usertbl.userID = TODOtbl.userID;

41 内部結合 INNER JOIN SELECT対象の列を指定するには，表名を明記する． SELECT
TODOtbl.TODOID, TODOtbl.txt, usertbl.name FROM TODOtbl INNER JOIN usertbl ON TODOtbl.userID = usertbl.userID; TODOID txt name 組閣する fukuda 1 演説する fukuda 2 解散する koizumi

42 内部結合 INNER JOIN 表"usertbl" 表"TODOtbl" userID name TODOID userID txt
fukuda 組閣する 1 abe 1 演説する 2 koizumi 2 2 解散する SELECT * FROM usertbl INNER JOIN TODOtbl on usertbl.userid = TODOtbl.userid; userID | name | TODOID | userID | txt 0 | fukuda | | | 組閣する 0 | fukuda | | | 演説する 2 | koizumi | | | 解散する

43 外部結合 左外部結合，右外部結合，全外部結合とある． 交差結合，内部結合と同様だが， 相手の表に対応するものが無い場合も表示される．
対応するものが無い場合は，NULLで表示

44 左外部結合 LEFT OUTER JOIN 左外部結合は，(例え右の表に対応する行が無くても)左の表の行は必ず表示される．
右の表に対応する行が無い場合はNULLを表示 SELECT * FROM tblA LEFT OUTER JOIN tblB on ...; 左の表 右の表

45 左外部結合 LEFT OUTER JOIN 表"usertbl" 表"TODOtbl" userID name TODOID userID
txt fukuda 組閣する 1 abe 1 演説する 2 koizumi 2 2 解散する SELECT * from usertbl LEFT OUTER JOIN TODOtbl on usertbl.userID = TODOtbl.userid; userID name TODOID userID txt fukuda 組閣する 1 演説する 1 abe 該当なし 2 koizumi 2 2 解散する

46 左外部結合 LEFT OUTER JOIN 表"usertbl" 表"TODOtbl" userID name TODOID userID
txt fukuda 組閣する 1 abe 1 演説する 2 koizumi 2 2 解散する SELECT * from usertbl LEFT OUTER JOIN TODOtbl on usertbl.userID = TODOtbl.userid; userID name TODOID userID txt fukuda 組閣する fukuda 1 演説する 1 abe NULL NULL NULL 2 koizumi 2 2 解散する

47 左外部結合 LEFT OUTER JOIN 表"TODOtbl" 表"usertbl" TODOID userID txt userID
name 組閣する fukuda 1 演説する 1 abe 2 2 解散する 2 koizumi SELECT * from TODOtbl LEFT OUTER JOIN usertbl on TODOtbl.userID = usertbl.userid; TODOID userID txt userID name 組閣する fukuda 1 演説する fukuda 2 2 解散する 2 koizumi

48 左外部結合 LEFT OUTER JOIN 表"TODOtbl" 表"usertbl" TODOID userID txt userID
name 組閣する fukuda 1 演説する 1 abe 2 2 解散する 2 koizumi SELECT * FROM TODOtbl LEFT OUTER JOIN usertbl on TODOtbl.userID = usertbl.userid; TODOID userID txt userID name 組閣する fukuda 1 演説する fukuda 2 2 解散する 2 koizumi

49 右外部結合 RIGHT OUTER JOIN 右外部結合は，(例え左の表に対応する行が無くても)右の表の行は必ず表示される．
左の表に対応する行が無い場合はNULLを表示 SELECT * FROM tblA LEFT OUTER JOIN tblB on ...; 左の表 右の表

50 右外部結合 RIGHT OUTER JOIN 表"usertbl" 表"TODOtbl" userID name TODOID userID
txt fukuda 組閣する 1 abe 1 演説する 2 koizumi 2 2 解散する SELECT * FROM usertbl RIGHT OUTER JOIN TODOtbl on usertbl.userID = TODOtbl.userid; userID name TODOID userID txt fukuda 組閣する 1 abe 1 演説する 2 koizumi 2 2 解散する

51 右外部結合 RIGHT OUTER JOIN 表"usertbl" 表"TODOtbl" userID name TODOID userID
txt fukuda 組閣する 1 abe 1 演説する 2 koizumi 2 2 解散する SELECT * FROM usertbl RIGHT OUTER JOIN TODOtbl on usertbl.userID = TODOtbl.userid; userID name TODOID userID txt fukuda 組閣する 1 abe 1 演説する 2 koizumi 2 2 解散する

52 右外部結合 RIGHT OUTER JOIN 表"TODOtbl" 表"usertbl" TODOID userID txt userID
name 組閣する fukuda 1 演説する 1 abe 2 2 解散する 2 koizumi SELECT * FROM TODOtbl RIGHT OUTER JOIN usertbl on usertbl.userID = TODOtbl.userid; TODOID userID txt userID name 組閣する fukuda 1 演説する 該当無し 1 abe 2 2 解散する 2 koizumi

53 右外部結合 RIGHT OUTER JOIN 表"TODOtbl" 表"usertbl" TODOID userID txt userID
name 組閣する fukuda 1 演説する 1 abe 2 2 解散する 2 koizumi SELECT * FROM TODOtbl RIGHT OUTER JOIN usertbl on usertbl.userID = TODOtbl.userid; TODOID userID txt userID name 組閣する fukuda 1 演説する fukuda NULL NULL NULL 1 abe 2 2 解散する 2 koizumi

54 全外部結合 FULL OUTER JOIN 全外部結合は，左外部結合と右外部結合の両方の要素が表示される．
左の表に対応する行が無い場合は，左の表の列にNULLを表示 右の表に対応する行が無い場合は，右の表の列にNULLを表示

55 全外部結合 FULL OUTER JOIN 表"usertbl" 表"TODOtbl" userID name TODOID userID
txt fukuda 組閣する 1 abe 1 演説する 2 koizumi 2 2 解散する 3 4 公演する SELECT * FROM usertbl FULL OUTER JOIN TODOtbl on usertbl.userID = TODOtbl.userid; userID name TODOID userID txt fukuda 組閣する fukuda 1 演説する 1 abe NULL NULL NULL 2 koizumi 2 2 解散する NULL NULL 3 4 公演する

56 関係代数

57 和両立 2個の表があり， 「両表の次数(列数)が等しい」かつ 「各列の意味が等しい」とき 「和両立」という． 表"連絡表" 表"ユーザ表"
名前 連絡先 ユーザ名 電話番号 福田 小泉 安倍 森 小泉 両表は ・列数が等しい(ともに3列) ・各列の意味が等しい ので，「和両立」である．

58 和集合演算 和両立な表Rと表Sがあり,「和集合演算」とは以下の演算． それぞれの表の全行をあわせた表を作成する演算が「和集合演算」．
ただし，重複する行は1行にまとめる． 集合論(RDBMS),では「同一の行が2回以上登場」することは許されない． 実際は，RDBMSに全く同じ行を2個以上作成してもエラーにならないことが多い． 「R∪S」と記述．論理和に近い感覚．

59 和集合演算 和集合演算 表"連絡表" 表"ユーザ表" 名前 連絡先 ユーザ名 電話番号 福田 123-456 小泉 345-678 安倍
森 小泉 和集合演算 名前 連絡先 福田 安倍 小泉 「小泉, 」が2回 登場することはない 森

60 差集合演算 和両立な表Rと表Sがあり,「差集合演算」とは以下の演算． 表Rから，「表Sに登場する行」を削除した表を作成するのが差集合演算．

61 差集合演算 差集合演算 表"連絡表" 表"ユーザ表" 名前 連絡先 ユーザ名 電話番号 福田 123-456 小泉 345-678 安倍
森 小泉 名前 連絡先 福田 差集合演算 安倍 小泉 ←"連絡表"からこの行が削除された 森 ← この行は"連絡表"に無いので， 特に何も起きない．

62 共通集合演算 和両立な表Rと表Sがあり,「共通集合演算」とは以下の演算．

63 共通集合演算 共通集合演算 表"連絡表" 表"ユーザ表" 名前 連絡先 ユーザ名 電話番号 福田 123-456 小泉 345-678 安倍
森 小泉 共通集合演算 名前 連絡先 小泉

64 直積演算 前述の交差結合をおこなうこと． 演算対象の表Rと表Sは和両立である必要はない． 「R×S」と記述．

65 直積演算 直積集合演算 表"連絡表" 表"商品表" 名前 連絡先 商品名 価格 福田 123-456 牛乳 100 安倍 789-012
ジュース 120 小泉 直積集合演算 名前 連絡先 商品名 価格 福田 牛乳 100 福田 ジュース 120 安倍 牛乳 100 安倍 ジュース 120 小泉 牛乳 100 小泉 ジュース 120

66 射影演算 表から特定の列を抜き出す演算 SELECT ?? FROM ...; ここで 行っている．

67 射影演算 表"連絡表" 名前 連絡先 福田 安倍 小泉 射影演算 名前 福田 安倍 小泉

68 選択演算 表から特定の行を抜き出す演算 SELECT * FROM WHERE ??; ここで 行っている．

69 射影演算 選択演算 表"連絡表" 名前 連絡先 福田 123-456 安倍 789-012 小泉 345-678 名前 連絡先 安倍

70 結合演算 「結合演算」とは, 表Rと表SがありR×S(直積)の中から それぞれの属性値を用いて特定の行を選択したもの．
前述のSQLの結合演算．

71 商演算 表Rと表Sの商演算は，「R÷S」と記述． Rの列数＞Sの列数であることが必要． 説明は，次スライドの例を用いて行う． 代数的定義は
R÷S={ t | t∈R ∧ (∀u∈S)((t , u)∈R) }

72 商演算 商演算 表"ユーザ趣味表" 表"趣味表" ユーザ 趣味 趣味 福田 A A 福田 B B 安倍 B 安倍 C 小泉 A 小泉 B
表"ユーザ趣味表"から， 「趣味A」と「趣味B」を両方持つ ユーザを捜してくること． 名前 福田 小泉

73 1年間，お疲れ様でした！ 最後に 情報系技術者なら，「プログラムくらい書けて当たり前」です． 当然ですが，試験勉強頑張ってください．
最低でも試験前は勉強しましょう． 「試験前のみ勉強」を推奨しているのではない． しかし，試験はさておき，「プログラムが書ける」様になってください． 情報系技術者なら，「プログラムくらい書けて当たり前」です．