データベース工学および演習 第5章 リレーショナルデータベース言語SQL

Presentation on theme: "データベース工学および演習 第5章 リレーショナルデータベース言語SQL"— Presentation transcript:

1 データベース工学および演習 第5章 リレーショナルデータベース言語SQL
ジョン　ジンヒ 全　眞嬉

2 担当教員 所属 名前 連絡先等 東北大学 大学院情報科学研究科 准教授 全 眞嬉（ジョン ジンヒ）
東北大学　大学院情報科学研究科　准教授 名前 全　眞嬉（ジョン　ジンヒ） 連絡先等 Tel/ Fax

3 ５．1　背景

4 データベース（Database） 特定のテーマに従って収集され，容易に再利用でき るように整理されたデータの群
再利用できるようになっているところが重要 データベースは複数の目的で使用される 再利用する為にはデータは良く整理されている必要 がある 複数の目的で共有できる何らかのテーマがあるのが 一般的 例 音楽データベース，気象情報データベース，図書館データベース，顧 客データベース・・・

5 データベース管理システム データベース管理システム(Database Management System:DBMS)
データベースをコンピュータ上に構築・管理する為の 仕組み データベースシステム(Database Sytem) データベース管理システムとそれによって管理される データの群を合わせてデータベースシステムと呼ぶ

6 SQL SQL(Structured Query Languages) データベースを操作するための言語
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

7 SQL SQL(Structured Query Languages) System Rの開発
世界初のリレーショナルデータベース管理システム （RDBMS） IBMがリレーショナルモデルの有効性を実証する為開 発した実験的データベースシステム システムを操作するに当ってSEQUEL（Structured English Query Language）が採用 SQLSQL(Structured Query Languages)に改名 OracleやSybase, Informixなどの商用DBMSが SQLを実装し，各ベンダーごとに拡張されている E.F. Codd

8 SQL SQLの改良 OracleやSybase, Informixなどの商用DBMSがSQL を実装し，各ベンダーごとに拡張されている
ANSI(American National Standard Institute) 1987年 SQL 87 ISO（International Organization Standardization) JIS(Japanese Industrial Standard)

9 SQL 近年では SQL99 SQL:2003 SQL:2006 オブジェクト指向データベースへの拡張
OLAP(Online Analytical Processing)関連そうあの強化 SQL:2003 マルチメディアデータベース関連機能への拡張 XMLデータ関連のフィーチャが追加 SQL:2006 XQuery

10 2005年に発売されたハリーポッターシリーズの本下さい
SQLって何？ 簡単に言えば リレーショナルデータベース管理システムと対話する 為の言語 リレーショナルデータベース管理システムに対して問 い合わせる際にSQLを使う 2005年に発売されたハリーポッターシリーズの本下さい RDB SQL語 RDBMS

11 データベース言語の標準化のメリット ユーザは一つのデータベース言語を学ぶことで各 種のDBMSを利用できる
標準データベース言語を用いたあるツールはユー ティリティを開発したり利用したりすることが容易 になる 異なるユーザ間でのDBMS利用技術のノウハウ 共有が促進される

12 ５．２基本概念

13 SQLの対象とするデータ構造とRDBのデータ構造との相違点
重複したタプルの存在 リレーショナルデータモデルでは リレーションはタプルの集合として規定 全く同じ属性値の並びからなる重複したタプルは不可 現実のデータ操作において重複した値が自動的 に除去されると都合が悪い場合がある 属性について平均値計算をした場合 属性のみを残すような射影演算の結果中に重複した値がジ除 去されると正しい平均は計算できない SQLでは必要に応じて重複したタプルを許すことが可 能 マルチ集合 重複した要素の存在を許すような集まり SQLは集合ではなくマルチ集合を基礎とした体系

14 SQLの対象とするデータ構造とRDBのデータ構造との相違点
属性やタプルの順序付け リレーショナルデータモデルでは リレーションスキーマにおいては属性の並び順序は意味 がない SQLでは 属性及び属性値は明示的に順序付けられたものとして扱 う 問い合わせ結果を アプリケーションに渡す時点においてタプル同士が並ぶ順 序うぃ明示的に指定できる

15 SQLでの用語 SQLの対象とするデータ構造とRDBのデータ構造 との相違点からSQLでは表，列，行と呼ぶ 表（table）
リレーション 列（column） 属性 行（low） タプル

16 ５．3　データの定義

17 実表（base table） 実表 他の表 SQLでデータの実体を伴う表 ビュー表（viewed table）
ビューを表現する表 導出表(derived table) 問い合わせ結果として一般的に出来る表

18 図3.3 リレーショナルデータベースの例 科目 学生 科目番号 科目名 単位数 001 データベース 2 002 システムプログラム 3
図3.3　リレーショナルデータベースの例 科目 学生 科目番号 科目名 単位数 001 データベース 2 002 システムプログラム 3 学生番号 氏名 専攻 住所 00001 山田一郎 情報工学 東京都XXX 00002 鈴木明 茨城県XXX 00003 佐藤花子 履修 実習課題 科目番号 学籍番号 成績 001 00001 90 00002 80 002 00003 70 科目番号 課題番号 課題名 001 01 データモデリング 02 データベース設計 03 SQL Cプログラミング 002 7システムk－る

19 実表の定義の例 : 科目 各列の列名とそのデータ型 を指定する データ型 文字列、数、ビット列、日時な ど CHAR(3)
3文字の固定長文字列型 NCHAR(12) 12文字の固定長感じ文 字列型 INTEGER 整数型 科目 科目番号 科目名 単位数 001 データベース 2 002 システムプログラム 3 CREATE　TABLE　科目 （科目番号　CHAR（3）　NOT NULL、 　科目名　　NCHAR(12) NOT NULL、 　単位数　　INTEGER、 　PRIMARY　KEY　（科目番号）、 　CHECK（単位数　BETWEEN 1 AND　12））

20 実表の定義の例 : 科目 CHAR(3) 3文字の固定長文字列型 NCHAR(12) 12文字の固定長感じ文字列型 INTEGER 整数型
PRIMARY　KEY　（科目番号） 科目表の主キーが科目番号で あることを示したキー制御の記 述 CHECK（単位数　BETWEEN　1　 AND　12） 単位数の値が1から12までの範 囲でなければならないとの整合 性制御を記述 科目 科目番号 科目名 単位数 001 データベース 2 002 システムプログラム 3 CREATE　TABLE　科目 （科目番号　CHAR（3）　NOT NULL、 　科目名　　NCHAR(12) NOT NULL、 　単位数　　INTEGER、 　PRIMARY　KEY　（科目番号）、 　CHECK（単位数　BETWEEN　1 AND12））

21 定義域（domain） CREAT DOMAIN文で定義された対象を定義域 （DOMAIN）と呼ぶ
実表定義の際のデータ型に変わって用いることも 可能 その場合はCHECKで記述された整合制約が適用さ れる CREATE　DOMAIN 　単位数　INTEGER 　　CHECK（VALUE BETWEEN 1 AND 12）

22 実表の定義の例 : 履修 主キーは科目番号と学 籍番号のペアとして指定 FOREIGN KEY記述 外部キーの指定
参照整合制約の記述に 相当 科目番号 学籍番号 成績 001 00001 90 00002 80 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　１００））

23 ５．4　問合せ 5.4.1　問合せの基本形

24 問合せの基本形 SELECT Ti1.C1,・・・,Tim.Cm FROM T1,・・・,Tn WHERE ψ
SQLにおける典型的な問合せ記述の形式 T1,・・・,Tn 　は表名 CT1,・・・,Cnm はそれぞれT1,・・・,Tn 中の表　 Ti1.,・・・,Tim　の例名 Ψは条件 SELECT Ti1.C1,・・・,Tim.Cm FROM　　 T1,・・・,Tn WHERE ψ

25 Q1: 科目番号005の科目の履修者の学籍番号と成績の一覧
SELECT 履修.学籍番号、履修.成績 FROM 履修 WHERE 履修.科目番号=‘005’ SELECT 学籍番号、成績 FROM 履修 WHERE 科目番号=‘005’

26 Q2: 学籍番号00100のの学生が履修した科目番号，成績の一覧
SELECT 科目.科目番号、科目名、成績 FROM 科目 WHERE 科目.科目番号=履修.科目番号 　　　　　　　　AND 　学籍番号=‘00100’ SELECT 科目番号、科目名、成績 FROM 科目　NATURAL JOIN 履修 WHERE 学籍番号=‘00100’

27 Q3: 情報工学専攻のいずれかの学生が履修した科目の科目番号と科目名の一覧
SELECT 科目.科目番号、科目名 FROM 科目、履修、学生 WHERE 科目.科目番号=履修.科目番号 　　　　　　　AND　履修.学籍番号＝学生.学籍番号　 　　　　　　　AND　専攻＝N’情報工学’ SELECT 科目番号、科目名 FROM 科目 NATURAL JOIN　履修 NATURAL JOIN 学生 WHERE 専攻＝N’情報工学’ ORDER BY　科目番号

28 Q4: 科目番号005の科目に関して学籍番号00100の学生よりも成績の良かった学生の学籍番号一覧
SELECT y.学籍番号 FROM 履修　AS ｘ、履修 AS y WHERE x.科目番号＝‘005’ AND ｘ.学籍番号=‘00100’ 　　　　　　　　　 　　　　　　　AND y.科目番号=‘005’ AND y.成績>x.成績

29 Q5: 全科目の科目名と単位数一覧 SELECT 科目名、単位数 FROM 科目 SELECT DISTINCT 科目名、単位数

30 Q6: 単位数が3単位以上の科目の科目番号，科目名，単位数の一覧
SELECT ＊ FROM 科目 WHERE　　単位数 >= 3

31 ５．4　問合せ 5.4.2　集合関数

32 Q7: 科目番号005の科目の平均点 SELECT AVG(成績) FROM 履修 WHERE 　科目番号 = ‘005’

33 ５．4　問合せ 5.4.3　グループ表

34 Q8: 全科目について科目番号と平均点の一覧
SELECT 科目番号、AVG(成績) FROM 履修 GROUP　BY 　科目番号

35 Q9: 情報工学専攻の学生が履修した科目の科目番号と情報工学専攻の学生に関する平均点の一覧
SELECT 履修.科目番号、AVG(成績) FROM 履修、学生 WHERE 　履修.学籍番号＝学生．学籍番号 　　　　　　　AND 専攻＝N’情報工学’ GROUP BY 履修.科目番号

36 Q10: 履修者が30人以上の科目の科目番号，履修者数，平均点の一覧
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

39 Q12: 実習課題のない科目の科目番号と科目名の一覧
SELECT 科目番号、科目名 FROM 科目 EXCEPT UNION　　　 SELECT　 科目.科目番号、科目名 FROM　　　科目、実習課題 WHERE　　科目.科目番号=実習課題.科目番号

40 ５．4　問合せ 5.4.5　副問合せ

41 Q3: 情報工学専攻のいずれかの学生が履修した科目の科目番号と科目名の一覧
SELECT 科目.科目番号、科目名 FROM 科目、履修、学生 WHERE 科目.科目番号=履修.科目番号 　　　　　　　AND　履修.学籍番号＝学生.学籍番号　 　　　　　　　AND　専攻＝N’情報工学’ SELECT 科目番号、科目名 FROM 科目 WHERE　　科目番号　IN 　(SELECT 科目番号 　　　 FROM 履修、学生 　　　 WHERE 履修.学籍番号=学生.学籍番号　 　　　　　　　　　　　AND 　専攻＝N’情報工学’）

42 Q12: 実習課題のない科目の科目番号と科目名の一覧
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　データの更新

46 INSERT:行を追加 U1 科目番号002の科目の実習問題03として 「シェル作成」を追加
科目番号010の科目履修者として学籍番号が ‘00099’以下の学生を全員登録 INSERT INTO 　実習問題 VALUE (’002’、’03’、N’シェル作成’) INSERT INTO 　履修（科目番号、学籍番号） 　SELECT ‘010’、学籍番号 　FORM 　 学生 　WHERE　学籍番号<=‘00099’　

47 DELETE:行を削除, UPDATE：列の値を更新
科目番号010の科目の単位数を3単位に変更 DELETE FROM 　実習問題 WHERE 科目番号＝‘005’ UPDATE 科目 SET 単位数＝３ WHERE　 科目番号＝‘010’

48 課題 教科書１００ページの演習問題 提出方法 問題５．１ 問題５．５ 問題５．６ A4の紙に作成し提出 次回の講義時に提出
学籍番号，名前右上に記入 表紙は不要 次回の講義時に提出 １１月２７日（土）