2008年12月11日 RDBMSとSQL(2/3) 関数,GROUP,JOIN,演算 プログラミング論 II 2008年12月11日 RDBMSとSQL(2/3) 関数,GROUP,JOIN,演算 http://www.ns.kogakuin.ac.jp/~ct13140/Prog.2008/

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

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

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関数は，文字列を小文字に変換．

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

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

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

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

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

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

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

課題 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文は？

SQLのGROUP BY

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

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

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

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

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 ??

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;

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

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

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

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

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

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 がある

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

結合

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

交差結合 表"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

内部結合 表"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

左外部結合 表"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

左外部結合 表"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

全外部結合 表"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

交差結合 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

交差結合 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

交差結合 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を 横に並べた行. あまり意味がない

内部結合 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 組閣する 演説する 解散する

内部結合 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 解散する

内部結合 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 解散する

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

内部結合 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

内部結合 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 | 0 | 組閣する 0 | fukuda | 1 | 0 | 演説する 2 | koizumi | 2 | 2 | 解散する

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

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

左外部結合 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 解散する

左外部結合 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 解散する

左外部結合 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

左外部結合 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

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

右外部結合 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 解散する

右外部結合 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 解散する

右外部結合 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

右外部結合 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

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

全外部結合 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 公演する

関係代数

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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