Transcript １１月１３日

1
データベース工学および演
習
第5章
リレーショナルデータベース言語SQL
ジョン ジンヒ
全 眞嬉
担当教員
2

所属
 東北大学

名前
全

大学院情報科学研究科 准教授
眞嬉（ジョン ジンヒ）
連絡先等
 [email protected]
 Tel/
Fax 022-795-4747
 http://www.dais.is.tohku.ac.jp/~jinhee/
3
５．1 背景
データベース（Database）
4


特定のテーマに従って収集され，容易に再利用でき
るように整理されたデータの群
再利用できるようになっているところが重要
 データベースは複数の目的で使用される
 再利用する為にはデータは良く整理されている必要
がある
 複数の目的で共有できる何らかのテーマがあるのが
一般的

例

音楽データベース，気象情報データベース，図書館データベース，顧
客データベース・・・
データベース管理システム
5

データベース管理システム(Database
Management System:DBMS)
 データベースをコンピュータ上に構築・管理する為の
仕組み

データベースシステム(Database Sytem)
 データベース管理システムとそれによって管理される
データの群を合わせてデータベースシステムと呼ぶ
SQL
6
E.F. Codd

SQL(Structured Query Languages)
 データベースを操作するための言語
 1970年代IBMのE.F.
Codd氏のよって
リレーショナルデータベース概念が提唱
 E.F.Codd
, “A Relational Model of Data
for Large Shared Data
Banks”,Communications of the ACM Vol.13,
No.6, pp.377–387,1970
SQL
7

SQL(Structured Query Languages)
 System
E.F. Codd
Rの開発
 世界初のリレーショナルデータベース管理システム
（RDBMS）
 IBMがリレーショナルモデルの有効性を実証する為開
発した実験的データベースシステム
 システムを操作するに当ってSEQUEL（Structured
English Query Language）が採用
 SQLSQL(Structured
 OracleやSybase,
Query Languages)に改名
Informixなどの商用DBMSが
SQLを実装し，各ベンダーごとに拡張されている
SQL
8

SQLの改良
 OracleやSybase,
Informixなどの商用DBMSがSQL
を実装し，各ベンダーごとに拡張されている
 改良と共にSQLの標準化
 1986年

ANSI(American National Standard Institute)
 1987年


SQL86
SQL 87
ISO（International Organization Standardization)
JIS(Japanese Industrial Standard)
SQL
9

近年では
 SQL99
 オブジェクト指向データベースへの拡張
 OLAP(Online
Analytical Processing)関連そうあの強化
 SQL:2003
 マルチメディアデータベース関連機能への拡張
 XMLデータ関連のフィーチャが追加
 SQL:2006
 XQuery
SQLって何？
10

簡単に言えば
 リレーショナルデータベース管理システムと対話する
為の言語
 リレーショナルデータベース管理システムに対して問
い合わせる際にSQLを使う
RDB
2005年に発
売されたハ
リーポッター
シリーズの本
下さい
SQL語
RDBMS
データベース言語の標準化のメ
リット
11




ユーザは一つのデータベース言語を学ぶことで各
種のDBMSを利用できる
異なるDBMS巻のアプリケーションプログラムの
移植や連携が容易
標準データベース言語を用いたあるツールはユー
ティリティを開発したり利用したりすることが容易
になる
異なるユーザ間でのDBMS利用技術のノウハウ
共有が促進される
12
５．２基本概念
SQLの対象とするデータ構造とRDBの
データ構造との相違点
13

重複したタプルの存在
 リレーショナルデータモデルでは
 リレーションはタプルの集合として規定
 全く同じ属性値の並びからなる重複したタプルは不可
 現実のデータ操作において重複した値が自動的
に除去されると都合が悪い場合がある
 属性について平均値計算をした場合

属性のみを残すような射影演算の結果中に重複した値がジ除
去されると正しい平均は計算できない
 SQLでは必要に応じて重複したタプルを許すことが可
能
 マルチ集合
 重複した要素の存在を許すような集まり
 SQLは集合ではなくマルチ集合を基礎とした体系
SQLの対象とするデータ構造とRDBの
データ構造との相違点
14

属性やタプルの順序付け
 リレーショナルデータモデルでは
 リレーションスキーマにおいては属性の並び順序は意味
がない
 SQLでは
 属性及び属性値は明示的に順序付けられたものとして扱
う
 問い合わせ結果を
 アプリケーションに渡す時点においてタプル同士が並ぶ順
序うぃ明示的に指定できる
SQLでの用語
15

SQLの対象とするデータ構造とRDBのデータ構造
との相違点からSQLでは表，列，行と呼ぶ
 表（table）
 リレーション
 列（column）
 属性
 行（low）
 タプル
16
５．3 データの定義
実表（base table）
17

実表
 SQLでデータの実体を伴う表

他の表
 ビュー表（viewed
table）
 ビューを表現する表
 導出表(derived
table)
 問い合わせ結果として一般的に出来る表
図3.3 リレーショナルデータベースの
例
18
科目
学生
科目番号
科目名
単位数
学生番号
氏名
専攻
住所
001
データベース
2
00001
山田一郎
情報工学
東京都XXX
002
システムプログラム
3
00002
鈴木明
情報工学
茨城県XXX
00003
佐藤花子
情報工学
東京都XXX
履修
実習課題
科目番号
学籍番号
成績
001
00001
90
001
00002
80
002
00001
90
002
00003
70
科目番号
課題番号
課題名
001
01
データモデリング
001
02
データベース設計
001
03
SQL
001
01
Cプログラミング
002
02
7システムk－る
実表の定義の例 : 科目
19
科目

科目番号
科目名
単位数
001
データベース
2
002
システムプログラム
3
各列の列名とそのデータ型
を指定する
 データ型
 文字列、数、ビット列、日時な
ど

CREATE TABLE 科目
（科目番号 CHAR（3） NOT NULL、
科目名 NCHAR(12) NOT NULL、
単位数 INTEGER、
PRIMARY KEY （科目番号）、
CHECK（単位数 BETWEEN 1 AND 12））


CHAR(3)
 3文字の固定長文字列型
NCHAR(12)
 12文字の固定長感じ文
字列型
INTEGER
 整数型
実表の定義の例 : 科目
20

科目
科目番号
科目名
単位数
001
データベース
2
002
システムプログラム
3
CREATE TABLE 科目
（科目番号 CHAR（3） NOT NULL、
科目名 NCHAR(12) NOT NULL、
単位数 INTEGER、
PRIMARY KEY （科目番号）、
CHECK（単位数 BETWEEN 1 AND12））




CHAR(3)
 3文字の固定長文字列型
NCHAR(12)
 12文字の固定長感じ文字列型
INTEGER
 整数型
PRIMARY KEY （科目番号）
 科目表の主キーが科目番号で
あることを示したキー制御の記
述
CHECK（単位数 BETWEEN 1
AND 12）
 単位数の値が1から12までの範
囲でなければならないとの整合
性制御を記述
定義域（domain）
21

CREAT DOMAIN文で定義された対象を定義域
（DOMAIN）と呼ぶ
CREATE DOMAIN 単位数 INTEGER
CHECK（VALUE BETWEEN 1 AND 12）

実表定義の際のデータ型に変わって用いることも
可能
 その場合はCHECKで記述された整合制約が適用さ
れる
実表の定義の例 : 履修
22
履修
科目番号
学籍番号
成績
001
00001
90
001
00002
80
002
00001
90
002
00003
70


CREATE TABLE 履修
（ 学生番号 CHAR（3） NOT NULL、
科目番号 CHAR（5） NOT NULL、
成績
INTEGER、
PRIMARY KEY （科目番号、学籍番号）、
FOREIGN KEY （科目番号）
REFERENCES 科目（科目番号）、
FOREIGN KEY （学籍番号）
REFERENCES 学生（学籍番号）、
CHECK（成績 BETWEEN ０ AND １００））
主キーは科目番号と学
籍番号のペアとして指定
FOREIGN KEY記述
 外部キーの指定
 参照整合制約の記述に
相当
23
５．4 問合せ
5.4.1 問合せの基本形
問合せの基本形
24

SQLにおける典型的な問合せ記述の形式
SELECT Ti .C1,・・・,Tim.Cm
FROM
T1,・・・,Tn
WHERE ψ
1


T1,・・・,Tn は表名
CT1,・・・,Cnm はそれぞれT1,・・・,Tn 中の表
Ti .,・・・,Tim の例名
Ψは条件
1

Q1: 科目番号005の科目の履修者の学籍番号
と成績の一覧
25
SELECT 履修.学籍番号、履修.成績
FROM
履修
WHERE 履修.科目番号=‘005’
SELECT 学籍番号、成績
FROM
履修
WHERE 科目番号=‘005’
Q2: 学籍番号00100のの学生が履修した科目
番号，成績の一覧
26
SELECT 科目.科目番号、科目名、成績
FROM
科目
WHERE 科目.科目番号=履修.科目番号
AND 学籍番号=‘00100’
SELECT 科目番号、科目名、成績
FROM
科目 NATURAL JOIN 履修
WHERE 学籍番号=‘00100’
Q3: 情報工学専攻のいずれかの学生が履修
した科目の科目番号と科目名の一覧
27
SELECT 科目.科目番号、科目名
FROM
科目、履修、学生
WHERE 科目.科目番号=履修.科目番号
AND 履修.学籍番号＝学生.学籍番号
AND 専攻＝N’情報工学’
SELECT 科目番号、科目名
FROM
科目 NATURAL JOIN 履修 NATURAL JOIN 学
生
WHERE 専攻＝N’情報工学’
ORDER BY 科目番号
Q4: 科目番号005の科目に関して学籍番号00100の学生
よりも成績の良かった学生の学籍番号一覧
28
SELECT y.学籍番号
FROM
履修 AS ｘ、履修 AS y
WHERE x.科目番号＝‘005’ AND ｘ.学籍番号=‘00100’
AND y.科目番号=‘005’ AND y.成績>x.成績
Q5: 全科目の科目名と単位数一覧
29
SELECT 科目名、単位数
FROM
科目
SELECT DISTINCT 科目名、単位数
FROM
科目
Q6: 単位数が3単位以上の科目の科目番号，
科目名，単位数の一覧
30
SELECT ＊
FROM
科目
WHERE 単位数 >= 3
31
５．4 問合せ
5.4.2 集合関数
Q7: 科目番号005の科目の平均点
32
SELECT AVG(成績)
FROM
履修
WHERE 科目番号 = ‘005’
33
５．4 問合せ
5.4.3 グループ表
Q8: 全科目について科目番号と平均点の一
覧
34
SELECT 科目番号、AVG(成績)
FROM
履修
GROUP BY 科目番号
Q9: 情報工学専攻の学生が履修した科目の科目番号
と情報工学専攻の学生に関する平均点の一覧
35
SELECT 履修.科目番号、AVG(成績)
FROM
履修、学生
WHERE 履修.学籍番号＝学生．学籍番号
AND 専攻＝N’情報工学’
GROUP BY 履修.科目番号
Q10: 履修者が30人以上の科目の科目番号，
履修者数，平均点の一覧
36
SELECT 科目番号、COUNT(＊)、AVG(成績)
FROM
履修
GROUP BY 科目番号
HAVING COUNT(＊)>=30
37
５．4 問合せ
5.4.4 集合演算
38
Q11: 実習課題があるか，あるいは単位数が5単
位以上の科目の科目番号，科目名，科目の一
覧
SELECT 科目.＊
FROM
科目、実習課題
WHERE 科目.科目番号＝実習課題.科目番号
UNION
SELECT ＊
FROM
科目
WHERE 単位数>=5
Q12: 実習課題のない科目の科目番号と科目名
の一覧
39
SELECT 科目番号、科目名
FROM
科目
EXCEPT
UNION
SELECT 科目.科目番号、科目名
FROM
科目、実習課題
WHERE 科目.科目番号=実習課題.科目番号
40
５．4 問合せ
5.4.5 副問合せ
Q3: 情報工学専攻のいずれかの学生が履修し
た科目の科目番号と科目名の一覧
41
Q3
SELECT 科目.科目番号、科目名
FROM
科目、履修、学生
WHERE 科目.科目番号=履修.科目番号
AND 履修.学籍番号＝学生.学籍
番号
AND 専攻＝N’情報工学’
SELECT 科目番号、科目名
FROM
科目
WHERE 科目番号 IN
(SELECT 科目番号
FROM
履修、学生
WHERE 履修.学籍番号=学生.学籍番号
AND 専攻＝N’情報工学’）
Q12: 実習課題のない科目の科目番号と科目
名の一覧
42
Q5
SELECT 科目番号、科目名
FROM
科目
EXCEPT
UNION
SELECT 科目.科目番号、科目名
FROM
科目、実習課題
WHERE 科目.科目番号=実習課題.科目番号
SELECT 科目番号、科目名
FROM
科目
WHERE NOT EXISTS
(SELECT ＊
FROM
実習問題
WHERE 実習問題.科目番号＝科目.科目番号）
43
５．4 問合せ
5.4.6 ビュー
実習科目
44
CREAT VIEW 実習科目（科目番号、科目名、単位数） AS
SELECT 科目.＊
FROM
科目、実習課題
WHERE 科目.科目番号＝実習課題.科目番号
45
５．5 データの更新
INSERT:行を追加
46

U1 科目番号002の科目の実習問題03として
「シェル作成」を追加
INSERT INTO 実習問題
VALUE (’002’、’03’、N’シェル作成’)

科目番号010の科目履修者として学籍番号が
‘00099’以下の学生を全員登録
INSERT INTO 履修（科目番号、学籍番号）
SELECT ‘010’、学籍番号
FORM 学生
WHERE 学籍番号<=‘00099’
DELETE:行を削除,
UPDATE：列の値を更新
47

U３ 科目番号005の科目の実習課題をすべて削除
DELETE FROM 実習問題
WHERE 科目番号＝‘005’

科目番号010の科目の単位数を3単位に変更
UPDATE 科目
SET
単位数＝３
WHERE 科目番号＝‘010’
課題

教科書１００ページの演習問題
 問題５．１
 問題５．５
 問題５．６

提出方法
 A4の紙に作成し提出
 学籍番号，名前右上に記入
 表紙は不要
 次回の講義時に提出
 １１月２７日（土）