1 / 48

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

データベース工学および演習 第 5 章 リレーショナルデータベース言語 SQL. ジョン　ジンヒ 全　眞嬉. 担当教員. 所属 東北大学　大学院情報科学研究科　准教授 名前 全　眞嬉（ジョン　ジンヒ） 連絡先等 jinhee@dais.is.tohoku.ac.jp Tel/ Fax 022-795-4747 http://www.dais.is.tohku.ac.jp/~jinhee/. ５． 1 　背景. データベース（ Database ）. 特定のテーマに従って収集され，容易に再利用できるように整理されたデータの群

yeo-sexton
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  2. 担当教員 • 所属 • 東北大学　大学院情報科学研究科　准教授 • 名前 • 全　眞嬉（ジョン　ジンヒ） • 連絡先等 • jinhee@dais.is.tohoku.ac.jp • Tel/ Fax 022-795-4747 • http://www.dais.is.tohku.ac.jp/~jinhee/

  3. ５．1　背景

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

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

  6. SQL • 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 E.F. Codd

  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を実装し，各ベンダーごとに拡張されている • 改良と共にSQLの標準化 • 1986年　SQL86 • ANSI(American National Standard Institute) • 1987年 SQL 87 • ISO（International Organization Standardization) • JIS(Japanese Industrial Standard)

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

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

  11. データベース言語の標準化のメリット • ユーザは一つのデータベース言語を学ぶことで各種のDBMSを利用できる • 異なるDBMS巻のアプリケーションプログラムの移植や連携が容易 • 標準データベース言語を用いたあるツールはユーティリティを開発したり利用したりすることが容易になる • 異なるユーザ間でのDBMS利用技術のノウハウ共有が促進される

  12. ５．２基本概念

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

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

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

  16. ５．3　データの定義

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

  18. 図3.3　リレーショナルデータベースの例 科目 学生 履修 実習課題

  19. 実表の定義の例 : 科目 • 各列の列名とそのデータ型を指定する • データ型 • 文字列、数、ビット列、日時など • CHAR(3) • 3文字の固定長文字列型 • NCHAR(12) • 12文字の固定長感じ文字列型 • INTEGER • 整数型 科目 CREATETABLE　科目 （科目番号　CHAR（3）　NOT NULL、 　科目名　　NCHAR(12) NOT NULL、 　単位数　　INTEGER、 PRIMARYKEY　（科目番号）、 CHECK（単位数　BETWEEN 1 AND12））

  20. 実表の定義の例 : 科目 • CHAR(3) • 3文字の固定長文字列型 • NCHAR(12) • 12文字の固定長感じ文字列型 • INTEGER • 整数型 • PRIMARYKEY　（科目番号） • 科目表の主キーが科目番号であることを示したキー制御の記述 • CHECK（単位数　BETWEEN1AND12） • 単位数の値が1から12までの範囲でなければならないとの整合性制御を記述 科目 CREATETABLE　科目 （科目番号　CHAR（3）　NOT NULL、 　科目名　　NCHAR(12) NOT NULL、 　単位数　　INTEGER、 PRIMARYKEY　（科目番号）、 CHECK（単位数　BETWEEN1 AND12））

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

  22. 実表の定義の例 : 履修 履修 • 主キーは科目番号と学籍番号のペアとして指定 • FOREIGNKEY記述 • 外部キーの指定 • 参照整合制約の記述に相当 CREATETABLE　履修 （ 学生番号　CHAR（3）　NOTNULL、 　科目番号　CHAR（5）　NOTNULL、 　成績　　　　INTEGER、 PRIMARYKEY　（科目番号、学籍番号）、 FOREIGNKEY　（科目番号） REFERENCES　科目（科目番号）、　 FOREIGNKEY　（学籍番号） REFERENCES　学生（学籍番号）、 CHECK（成績　BETWEEN　０　AND　１００））

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

  24. 問合せの基本形 • 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 科目名、単位数 FROM 科目

  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 履修 GROUPBY 　科目番号

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

  36. Q10: 履修者が30人以上の科目の科目番号，履修者数，平均点の一覧 SELECT 科目番号、COUNT(＊)、AVG(成績) FROM 履修 GROUPBY 　科目番号 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: 情報工学専攻のいずれかの学生が履修した科目の科目番号と科目名の一覧 Q3 SELECT 科目.科目番号、科目名 FROM 科目、履修、学生 WHERE 科目.科目番号=履修.科目番号 AND　履修.学籍番号＝学生.学籍番号　 AND　専攻＝N’情報工学’ SELECT 科目番号、科目名 FROM 科目 WHERE　　科目番号　IN (SELECT 科目番号 FROM 履修、学生 WHERE 履修.学籍番号=学生.学籍番号　 AND 　専攻＝N’情報工学’）

  42. Q12: 実習課題のない科目の科目番号と科目名の一覧 Q5 SELECT 科目番号、科目名 FROM 科目 EXCEPT UNION SELECT　 科目.科目番号、科目名 FROM　　　科目、実習課題 WHERE　　科目.科目番号=実習課題.科目番号 SELECT 科目番号、科目名 FROM 科目 WHERENOT 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：列の値を更新 • U３　科目番号005の科目の実習課題をすべて削除 • 科目番号010の科目の単位数を3単位に変更 DELETE FROM 　実習問題 WHERE 科目番号＝‘005’ UPDATE 科目 SET 単位数＝３ WHERE　 科目番号＝‘010’

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

More Related