プログラミング論 II RDBMSとSQL http://www.ns.kogakuin.ac.jp/~ct13140/Prog.2010/

概要 データベース リレーション SQL 簡単．単に覚えるだけ． 情報系技術者の超重要スキル 抽象的で理解しづらい． 大まかに分かれば良い． 易しい．必ず理解すること！

データベース RDBMS

Database データベースとは，データの集まり． 例えば，実験の測定値，社員表など． 多くの場合，数値か文字列．

DBMS (Database Management System) データベース管理システム．データベースを管理運用するためのシステム． 多くの場合，ソフトウェアである． DBMSには数種類のモデルがある． リレーショナルデータモデル，ネットワークデータモデル 階層型データモデル，オブジェクト指向，XML 現在，多くのDBMSがリレーショナルデータモデルのRDBMB (Relational Database Management System)である． 本講義ではRDBMSのみ扱う．

RDBMS RDBMB 通常，SQLという言語を用いて運用する． 現在，ほとんどのDBMSがRDBMS Relational Database Management System 日本語で「関係データベース(管理システム)」 Relational Data Model(関係データモデル)を採用したDMBS 通常，SQLという言語を用いて運用する． 現在，ほとんどのDBMSがRDBMS Oracle， Microsoft SQL Server, DB2(IBM), PostgreSQL, MySQL, Firebird, SQLiteなどの実装がある． 現在，超重要なアプリケーションの一つ．

単語"Database"の曖昧性 「データとDBMS(DB管理システム)」をまとめて「データベース(DB)」と呼ぶこともある． RDBMSの意味で使うこともある．

データモデル DBMSでは各種モデルによりデータを整理，管理する．この整理，管理のモデルがデータモデル． 一般に，以下の3要素から成り立つ データやデータ間の関連を表現する構造記述 データやデータ間に存在する一貫性制約の記述． データの検索，更新などのデータ操作言語

リレーショナルデータモデル 数学の集合論に基づく，データモデル． 極めて簡潔に説明すると「表型」． 全てのデータ，データ間の関連を「リレーションのタプル」で表現する．(後述) タプルは，値の組み合わせ． Codd(コッド)博士が提案．チューリング賞． 日本語では「関係データモデル」

リレーション

リレーショナルデータモデル 会員表 データを表形式で管理する． 会員番号 氏名 住所 所属 福田康夫 群馬県... 部署M 1 安倍晋三 福田康夫 群馬県... 部署M 1 安倍晋三 山口県... 部署M 2 小泉純一郎 神奈川県... 部署M 3 森喜朗 石川県... 部署M 4 小渕恵三 群馬県... 部署O

リレーショナルデータモデル 表，リレーション，TABLE 会員番号 氏名 住所 所属 福田康夫 群馬県... 部署M 1 安倍晋三 福田康夫 群馬県... 部署M 1 安倍晋三 山口県... 部署M 2 小泉純一郎 神奈川県... 部署M 3 森喜朗 石川県... 部署M 4 小渕恵三 群馬県... 部署O

リレーショナルデータモデル 会員番号 氏名 住所 所属 福田康夫 群馬県... 部署M 1 安倍晋三 山口県... 部署M 行，ロー， 福田康夫 群馬県... 部署M 1 安倍晋三 山口県... 部署M 行，ロー， レコード， タプル 2 小泉純一郎 神奈川県... 部署M 3 森喜朗 石川県... 部署M 4 小渕恵三 群馬県... 部署O

リレーショナルデータモデル 列，カラム， フィールド 会員番号 氏名 住所 所属 福田康夫 群馬県... 部署M 1 安倍晋三 山口県... 福田康夫 群馬県... 部署M 1 安倍晋三 山口県... 部署M 2 小泉純一郎 神奈川県... 部署M 3 森喜朗 石川県... 部署M 4 小渕恵三 群馬県... 部署O

リレーショナルデータモデル 列名，カラム名， フィールド名 会員番号 氏名 住所 所属 福田康夫 群馬県... 部署M 1 安倍晋三 福田康夫 群馬県... 部署M 1 安倍晋三 山口県... 部署M 2 小泉純一郎 神奈川県... 部署M 3 森喜朗 石川県... 部署M 4 小渕恵三 群馬県... 部署O

リレーショナルデータモデル 行の値の変更，行の追加，行の削除などを繰り返して，DBMSを運用していく． 通常，列の 追加/削除は 行わない． 行の順は意味 を持たない 会員番号 氏名 住所 所属 福田康夫 群馬県... 部署M 1 安倍晋三 山口県... 2 小泉純一郎 神奈川県... 3 森喜朗 石川県... 4 小渕恵三 部署O

キー key

候補キー 表内の行を特定できる列（の組）が候補キー． 会員番号を定めると，行が一意に定まる． よって，会員番号は候補キー． 「氏名」では， 　よって，会員番号は候補キー． 「会員番号」が「2」である行は，1行しかない． 当然，会員番号は重複しないことが前提． 「氏名」では， 　同姓同名がいると 　破綻する． 会員番号 氏名 住所 所属 福田康夫 群馬県... 部署M 1 安倍晋三 山口県... 2 小泉純一郎 神奈川県... 3 森喜朗 石川県... 4 小渕恵三 部署O

候補キー キーは，複数の列で構成されていても良い． 「氏名，科目」を定めると行が特定される． よって，「氏名，科目」の組は候補キー 福田康夫 英語 数学 安倍晋三 小泉純一郎 得点 A B C D E 最悪でも，「全列の組」は 候補キーとなる． (同一の行は存在しては ならない．)

候補キー 候補キーは複数存在する場合もある． 会員番号を定めると行が特定される． よって，会員番号は候補キー 　よって，会員番号は候補キー 基礎年金番号を定めると行が特定される． 　よって，基礎年期番号も候補キー 会員番号 氏名 住所 基礎年金番号 福田康夫 群馬県... 234 1 安倍晋三 山口県... 567 2 小泉純一郎 神奈川県... 890 3 森喜朗 石川県... 123 4 小渕恵三 群馬県... 456

主キー 候補キーの中から，DBMS管理者が 主観的に選択した主たるキーが「主キー」． 理論的価値は低いが，実運用上重要な意味を持つ 下線を引くことが多い． 会員番号 氏名 住所 福田康夫 群馬県... 1 安倍晋三 山口県... 2 小泉純一郎 神奈川県... 3 森喜朗 石川県... 4 小渕恵三 基礎年金番号 234 567 890 123 456

正規形

第一正規形 1マスに複数の要素を入れてはならない． 1個の要素が複数マスに跨ってはならない． そもそも，RDBMSに格納できない． 注意：この説明は過度に簡潔化されています． そもそも「マス」などという表現は使ってはならない．

第一正規形 第一正規形 非正規形 会員 番号 氏名 趣味 福田康夫 クラシック音楽鑑賞 2 小泉純一郎 歌舞伎鑑賞 会員 番号 氏名 趣味 福田康夫 クラシック音楽鑑賞 2 小泉純一郎 歌舞伎鑑賞 会員 番号 氏名 趣味 2 映画鑑賞 福田康夫 クラシック音楽鑑賞 2 小泉純一郎 歌舞伎鑑賞 会員 番号 氏名 趣味 2 小泉純一郎 映画鑑賞 福田康夫 クラシック音楽鑑賞 第一正規形 2 小泉純一郎 歌舞伎鑑賞， 映画鑑賞 非正規形

関数従属 ある列群の値が決まれば，別のある列群の値が一意に定まるとき「関数従属(する)」という． {氏名,教科}が決まると， {点数，合否}は決まる． よって，関数従属である． {氏名，教科}→{点数，合否} {氏名}を決めても， {点数}は決まらない． 関数従属でない． {点数}が決まると， {合否}は決まる． 関数従属である． 氏名 教科 点数 合否 福田康夫 英語 50 否 福田康夫 数学 60 合 安倍晋三 英語 70 合 安倍晋三 数学 80 合

完全従属 列群Aに列群Bが関数従属であり， 列群Aの真部分集合(Aの一部)に 関数従属でないとき，「完全従属」という． 会員 番号 氏名 教科 点数 合否 {会員番号,氏名,教科} →{点数,合否}なので関数従属． ところが， {会員番号,教科} →{点数,合否}でもある． 実は，{氏名}は無くても良い． よって，{点数,合否}は {会員番号,氏名,教科}に 完全従属でない． 福田康夫 英語 50 否 福田康夫 数学 60 合 1 安倍晋三 英語 70 合 1 安倍晋三 数学 80 合

第二正規形 第一正規形である． 候補キーでない列が候補キーに完全従属している．

第二正規形でない例 {userID,年,月,日}が候補キーかつ主キー． {userID}→{name,email}で， 赤枠は主キー 表"日記" user ID name email 年 月 日 text 福田 fuku@k.go.jp 2007 9 26 首相になった 1 安倍 abe@k.go.jp 2007 7 29 選挙を行った 1 安倍 abe@k.go.jp 2006 9 26 首相になった 2 小泉 koi@k.go.jp 2005 8 8 解散した {userID,年,月,日}が候補キーかつ主キー． {userID}→{name,email}で， nameやemailが候補キーに完全従属でない．

第二正規化の例 先ほどの表を分解し， 非候補キーが全ての候補キーに完全従属． これで，第二正規形 表"ユーザ表" 表"日記" user ID name email user ID 年 月 日 text 福田 fuku@k.go.jp 2007 9 26 首相になった 1 安倍 abe@k.go.jp 1 2007 7 29 選挙を行った 2 小泉 koi@k.go.jp 1 2006 9 26 首相になった 2 2005 8 8 解散した 表"ユーザ表" 表"日記" 先ほどの表を分解し， 非候補キーが全ての候補キーに完全従属． これで，第二正規形

第三正規形 第二正規形である． 全ての非キー列は，どの候補キーにも推移的に従属しない．

第三正規形でない例 第二正規形であるが， 所属→勤務地の関数従属が存在する． つまり，勤務地は userID→所属 →勤務地 と name 所属 勤務地 第二正規形であるが， 所属→勤務地の関数従属が存在する． つまり，勤務地は userID→所属 →勤務地 と 推移的に決定される． 福田 A事業部 八王子 1 安倍 B事業部 新宿 2 小泉 B事業部 新宿 3 森 A事業部 八王子 分解するべき

第三正規形 user ID name 所属 勤務地 福田 A事業部 八王子 1 安倍 B事業部 新宿 2 小泉 3 森 user ID 福田 A事業部 八王子 1 安倍 B事業部 新宿 2 小泉 3 森 user ID name 所属 所属 勤務地 福田 A事業部 A事業部 八王子 1 安倍 B事業部 B事業部 新宿 2 小泉 B事業部 3 森 A事業部

SQL

SQL SQLは，RDBMSを操作するのに使用する言語の一つ． 現在，本言語が主流．本稿ではSQLのみ扱う． 歴史的背景から"Structured Query Language"の略とすることもある． 構造化問い合わせ言語． 言語仕様には，実装依存する箇所が多々ある． C言語にも方言はあるが，SQLほど酷くはない．

SQLの超基本命令 表を作成する． 表に行を挿入する． 表の内容を確認する． 表の行の値を変更する. 表の行を削除す CREATE TABLE 表名 (...); 表に行を挿入する． INSERT INTO 表名 VALUES (...); 表の内容を確認する． SELECT * FROM 表名; 表の行の値を変更する. UPDATE 表名 SET 列名=値[,列名=値] WHERE ..; 表の行を削除す DELETE FROM 表名 WHERE ...;

SQLの例 以下の様なデータベースを構築することを考える． 表"user_tbl" userID name email Fukuda Yasuo fukuda@kantei.go.jp 1 Abe Shinzo abe@kantie.go.jp 2 Koizumi Junichiro koizumi@kantie.go.jp 3 Mori Yoshiro mori@kantie.go.jp

SQLの例 各行(レコード)の列(フィールド)には「型」がある． userID name email ユーザID を格納． INTEGER 型とする． ユーザ名を格納する． 最大64文字とする． VARCHAR(64)型 とする． ユーザのE-mailアドレス を格納する． 最大64文字とする． VARCHAR(64)型 とする． 各行(レコード)の列(フィールド)には「型」がある．

SQL (CREATE TABLE) CREATE TABLE user_tbl ( userID integer, name varchar(64), email varchar(64) );

空の表"user_tbl"が作成される． 確認方法は後述． 表"user_tbl" userID name email

SQL (INSERT INTO) INSERT INTO user_tbl VALUES ( 0, 'Fukuda Yasuo', 'fukuda@kantei.go.jp' ); これが， userIDになる． これが， nameになる． これが， emailになる．

表"user_tbl"に1行追加される． 確認方法は後述． 表"user_tbl" userID name email Fukuda Yasuo fukuda@kantei.go.jp

SQL (INSERT INTO) INSERT INTO user_tbl VALUES ( 1, 'Abe Shinzo', 'abe@kantie.go.jp' ); 2, 'Koizumi Junichiro', 'koizumi@kantie.go.jp'

表"user_tbl"に2行追加される． 確認方法は後述． 表"user_tbl" userID name email Fukuda Yasuo fukuda@kantei.go.jp 1 Abe Shinzo abe@kantie.go.jp 2 Koizumi Junichiro koizumi@kantie.go.jp

SQL (SELECT/FROM) SELECT * FROM user_tbl; 結果 userID name email Fukuda Yasuo fukuda@kantei.go.jp 1 Abe Shinzo abe@kantie.go.jp 2 Koizumi Junichiro koizumi@kantie.go.jp

SQL (SELECT/FROM) SELECT name, email FROM user_tbl; 結果 name email Fukuda Yasuo fukuda@kantei.go.jp Abe Shinzo abe@kantie.go.jp Koizumi Junichiro koizumi@kantie.go.jp

SELECT * FROM … WHERE userID=2; SQL (SELECT/FROM) SELECT * FROM user_tbl WHERE userID=2; 結果 userID name email 2 Koizumi Junichiro koizumi@kantie.go.jp SELECT * FROM…; SELECT * FROM … WHERE userID=2; この部分では， 列の選択を行う． この部分では， 行の選択を行う．

SQL (SELECT/FROM/WHERE) SELECT * FROM user_tbl WHERE 1<=userID; 結果 userID name email 1 Abe Shinzo abe@kantie.go.jp 2 Koizumi Junichiro koizumi@kantie.go.jp

SQL (UPDATE) UPDATE user_tbl SET email= 'root@fukuda.org' WHERE userID=0; SELECT * FROM user_tbl; 結果 userID name email Fukuda Yasuo root@fukuda.org 1 Abe Shinzo abe@kantie.go.jp 2 Koizumi Junichiro koizumi@kantie.go.jp

SQL (DELETE) DELETE FROM user_tbl WHERE userID=0; SELECT * FROM user_tbl; 結果 userID name email 1 Abe Shinzo abe@kantie.go.jp 2 Koizumi Junichiro koizumi@kantie.go.jp

データ型 整数型 INTEGER型:整数値を格納． SMALLINT型,NUMERIC型,DECIMAL型 浮動小数点型 REAL型:単精度浮動小数点 DOUBLE PRECISION型:倍精度浮動小数点 FLOAT型

データ型 文字列型 CHARACTER(n):n文字以内の文字列 VARCHAR(n):n文字以内の文字列

データ型 日付型 DATE型:日付を表す． TIME型:時刻 TIMESTAMP:日付と時刻

データ型 データ型はRDBMS実装に依存する部分が大きい． 詳細は，実装ごとのマニュアルを読む必要がある．

NULL値 RDBMSのマスを空にすることができる(場合がある) 空を「NULL」と呼ぶ． 主キーやNOT NULL制約のついた列のマスは，NULLにできない． 注意：一般にRDBMSに「マス」という表現はない．

算術演算子 使用例 意味 + a+b aとbの和 - a-b aとbの差 * a*b aとbの積 / a/b aをbで割った商 % a%b

比較演算子 使用例 意味 = a=b aとbは等しい < a<b aはbより小さい <= a<=b aはb以下 > a>b aはbより大きい >= a>=b aはb以上 <> != a!=b aとbは等しくない

論理演算子 使用例 意味 AND A and B A かつ B OR A or B A または B NOT NOT A A でない 10<=a and a <= 20 aが10以上 かつ aが20以下 OR A or B A または B a<=10 or 20<=a aが10以下 または aが20以上 NOT NOT A A でない NOT (a = 100) Aが100でない

SELECTの応用 以下の様な表があるとする． 表"user_tbl" userID name email Fukuda Yasuo Fukuda Yasuo fukuda@kantei.go.jp 1 Abe Shinzo abe@kantie.go.jp 2 Koizumi Junichiro koizumi@kantie.go.jp

SELECT SELECT name FROM user_tbl; name Fukuda Yasuo Abe Shinzo Koizumi Junichiro

SELECT/WHERE SELECT * FROM user_tbl WHERE userID = 2; userID name email 2 Koizumi Junichiro koizumi@kantie.go.jp

SELECT/WHERE SELECT * FROM user_tbl WHERE userID < 2; userID name email Fukuda Yasuo fukuda@kantei.go.jp 1 Abe Shinzo abe@kantie.go.jp

SELECT/WHERE SELECT * FROM user_tbl WHERE name = 'Koizumi Junichiro'; userID name email 2 Koizumi Junichiro koizumi@kantie.go.jp

SELECT/WHERE SELECT * FROM user_tbl WHERE 0<userID AND userID<2; name email 1 Abe Shinzo abe@kantie.go.jp

SELECT/WHERE SELECT * FROM user_tbl WHERE userID BETWEEN 1 AND 2; name email 1 Abe Shinzo abe@kantie.go.jp 2 Koizumi Junichiro koizumi@kantie.go.jp userIDが「1以上2以下」

SELECT/WHERE SELECT * FROM user_tbl WHERE userID IN (0,2); userID name email Fukuda Yasuo fukuda@kantei.go.jp 2 Koizumi Junichiro koizumi@kantie.go.jp userIDが「0か2」

SELECT/WHERE SELECT * FROM user_tbl WHERE userID NOT IN (0,2); userID name email 1 Abe Shinzo abe@kantie.go.jp

SELECT/ORDER BY 表"user_tbl" userID name score Fukuda Yasuo 80 1 Fukuda Yasuo 80 1 Abe Shinzo 70 2 Koizumi Junichiro 90 注意：この順は 保証されていない SELECT * FROM user_tbl; userID name score Fukuda Yasuo 80 1 Abe Shinzo 70 2 Koizumi Junichiro 90

SELECT/ORDER BY userID name score Fukuda Yasuo 80 1 Abe Shinzo 70 2 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 Fukuda Yasuo 80 2 Koizumi Junichiro 90

SELECT/ORDER BY userID name score Fukuda Yasuo 80 1 Abe Shinzo 70 2 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 Fukuda Yasuo 80 1 Abe Shinzo 70

SELECT/ORDER BY userID name score Fukuda Yasuo 80 1 Abe Shinzo 70 2 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 Fukuda Yasuo 80 2 Koizumi Junichiro 90

SELECT/ORDER BY SELECT * FROM user_tbl ORDER BY score, age;

LIKE演算子 と % と _ LIKE演算子 文字列を比較するのに使用する． 下記の，パターンマッチが使用可能． % (パーセント) 任意の長さ(0文字以上)の，任意の文字にマッチする． _ (アンダースコア) 1文字の任意の文字にマッチする．

% と _ の例 ABC, AAC, AAB があるとき， 「A_C」は， ABC と AAC が該当． ABC は，「_がB」と考えれば該当． AAC は，「_がA」と考えれば該当．

% と _ の例 ABC, AAC, AAB があるとき， 「_A_」は， AAC と AAB が該当． AAC は，「_と_が，AとC」と考えれば該当． AAB は，「_と_が，AとB」と考えれば該当．

% と _ の例 ABC, AA, A, BAA があるとき， 「A%」は， ABC, AA, A が該当． ABC は，「%が，"BC"」と考えれば該当． AA は，「%が，"A"」と考えれば該当． A は，「%が，""」と考えれば該当．(0文字でもOK) BAA は，%をどのようにおいても該当しない．

% と _ の例 ABC, AA, BAA, B があるとき， 「%A%」は， ABC, AA, BAA が該当． ABC は，「%と%が，""と"BC"」と考えれば該当． AA は，「%と%が，"A"と""」と考えれば該当． 「%と%が，""と"A"」と考えても該当． BAA は，「%と%が，"B"と"A"」と考えれば該当． 「%と%が，"BA"と""」と考えれば該当． B は，%をどのようにおいても該当しない．

文字'%'を検索するとき '%'をそのまま書くと，特殊な意味になってしまう． LIKE '100%'には，「100%」ではなく「100から始まる任意の文字列」が該当してしまう． LIKE '100\%'とすれば「100%」の意味になる． LIKE '%\%'は，「～%」がマッチする．(%で終わる任意の文字列)

LIKE演算子 SELECT * FROM user_tbl WHERE name LIKE '_B%'; userID name ABC ABC 1 BBC 2 BBCC userID name 3 BC ABC 4 BAC 1 BBC 5 B 2 BBCC 表"user_tbl"

関数

本講義で頻繁に用いる表の設計 表"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

結合 JOIN

結合 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 両表の1列ずつを組み合わせて(横に並べて)新しい表を作成． 全組み合わせ存在． 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 TODOID 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) }