www.ns.kogakuin.ac.jp

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