目次 1. データモデルと SQL( 復習 ) 2.SQL の特徴 3.SQL の文法 4. データ定義 ( 基本編 ) 5.DB 管理 ( 基本編 ) 6. データ操作 ( 基本編 ) 7. レポート課題 8. 参考書ほか

Size: px

Start display at page:

Download "目次 1. データモデルと SQL( 復習 ) 2.SQL の特徴 3.SQL の文法 4. データ定義 ( 基本編 ) 5.DB 管理 ( 基本編 ) 6. データ操作 ( 基本編 ) 7. レポート課題 8. 参考書ほか"

つかさうづき
5 years ago
Views:

1 SQL (1) データベース論 Ⅰ 第 6 回 URL 作成者末次文雄 C

2 目次 1. データモデルと SQL( 復習 ) 2.SQL の特徴 3.SQL の文法 4. データ定義 ( 基本編 ) 5.DB 管理 ( 基本編 ) 6. データ操作 ( 基本編 ) 7. レポート課題 8. 参考書ほか

3 1. データモデルと SQL ( 復習 ) 1.1 関連と関連実体 1.2 ER モデルの表記法層アーキテクチャと SQL

4 1.1 関連と関連実体エンティティ間の関連を関連と関連実体に分ける言い方がある運転手 1:1 1:1 保有免許 ( 単に関連がある ) 1 1 ( 関連 ) 学生 N 1:N M:1 受講 ( 関連実体 ) 科目 M ( 関連を正規化テーブルとして独立させる場合 )

5 1.2 ER モデルの表記法 1 基数の表記法部門 1:N 社員 ( 基数明示法 ) 部門社員 ( 矢印法 ) 部門社員 ( 鳥脚法 )

6 1.2 ( 続き ) ER モデルの表記法 1 関連の表記法学生受講科目 ( オリジナル ) 学生受講科目 ( 改善 )

7 1.3 3 層アーキテクチャと SQL 3 層アーキテクチャーデータとプログラムの独立データモデリングデータ正規化と ER モデル技法により論理データモデルを作成する SQL 論理データモデルを実装するための言語

8 1.3 ( 補足 ) スキーマと技法 SQL の対応関係の集合演算データ正規化 ER モデル技法外部フロクラム A フロクラム B スキーマ外部スキーマ外部概念スキーマ ( 論理 DB モデル ) 内部スキマ物理 DB フロクラム C スキーマ SQL( データ操作 ) SQL( データ定義 ) 関係の集合演算

9 2.SQL の特徴 2.1 SQLの由来 2.2 SQLの特長 2.3 関係モデルと SQL の相違点 2.4 SQLの利用形態 2.5 SQLの機能

10 2.1 SQL の由来 1970 年代中頃 IBM が SEQUEL 開発したデータベース用の非手続き型の問合せ言語 (Structured English Query Language) 1987 年 ISO ANSI の関係 DB の標準規格 1989 年 ISO ANSI の SQL89 規格現在大半の RDBMS 製品がこれに準拠 1992 年 ISO ANSI の SQL92 規格初級 (=89) 中級上級 1999 年 ISO ANSI の SQL99 規格ストアドプロシジャーオブジェクト指向 Java リンク ( 注 ) 標準の対象はデータ操作部分でありデータ定義は対象外

11 2.1 補足 (RDBMS 製品例 ) 主な関係モデル用の RDBMS 製品大型システム向け :IBM DB2 SQL/DS Infomix : 富士通 AIM/RDB :NEC RIQS : 日立 RDB1 : オラクル社 Oracle 中小システム向け : マイクロソフト SQL-server ACCESS : Sybase 社 Sybase :MySQL AB 社 MySQL : オープン開発 ( 本部カナダ ) PostgreSQL

12 2.2 SQL の特長 1 関係データモデルに準拠 2データ操作は公的機関により標準化されている 3 大半のRDBMS 製品が採用している規格 4データアクセスだけでなくデータ管理機能も併せて装備している 5データをまとめて処理する方法 ( セット処理 ) 一件づつ処理する方法を共にサポートする

13 2.3 関係モデルと SQL の相違点 1 重複するデータ組 ( 行 ) の存在を許す行 UNIQUE の指定しない限り射影演算結果で作成される重複行を削除しない 2 列の並びを指定できる ( 注 ) 列とは別名属性データ項目のこと

14 2.4 SQL の利用形態独立言語方式ホスト言語方式 QBE コマンド方式 SQL とは別の問合せ専用の言語を使う用意されたスケルトン ( 骨組み ) に問合せ条件を入力する (Query By Example) SQL に付属するコマンドプロセッサーを使って SQL コマンドを入力 ( エントユーサー向き ) 通常のプログラミング言語からデータアクセスが出来る埋込み方式プログラム内に SQL 命令を書く静的 SQL 動的 SQL 実行時 SQL 命令が不変実行時 SQL 命令を変えるモシュール方式プログラムからサブプログラムを呼び出す ( ストアドプロシジャー関数方式ともいう )

15 ( 補足 ) 利用形態の標準化 ODBCは 1991 年マイクロソフト社が提唱したクライアント-DBMS 間のAPI これにより種々のクライアントから異なるDBMSの利用が可能となった (ODBC=Open Databese Connectivity) プログラム A DBMS ドライバ 1 SQL Server プログラム B プログラム C ODBC-API ODBC ドライバマネージャー DBMS ドライバ 2 ORACLE DBMS ドライバ 3 その他各 DBMS 用のコマンドプロセッサープログラム独自インターフェース

16 2.5 SQL の機能データ定義管理スキーマ定義権限の管理表 ( テーブル ) VIEW INDEX 作成 (CREATE ALTER DROP) データ利用の権限付与取消し (GRANT REVOKE) データ操作処理の制御トランスアクション管理カーソル操作 (COMMIT ROLLBACK CURSOR) データ変更データ検索データの追加削除更新 (INSERT DELETE UPDATE) データの取り出し ( 検索 ) (SELECT)

17 3 SQL の文法 3.1 SQL 命令の構造キーワード句文の三つからなる句 = キーワードの後ろに単語を付けたもの文 = 幾つかの句 (Phrase) を集めたものキーワードキーワード文の終わり SELECT * FROM 学生名簿 TBL ; 句句文 (STATEMENT)

18 3.2 文字の扱い 1 大文字小文字の区別が無い ( データ値は別 ) 2 空白文字の個数は関係ない空白が幾つ連続しても 1 つとみなされる空白文字とは半角スヘースタブ改行全角スペースは空白文字では無い 3 文字列常数はシングルクォートで囲む文字列にを含む場合はと重ねる SUETSUGU S = SUETSUGU S マイクロソフト社の ACCESS ではを使用

19 3.2 ( 続き ) 文字の扱い 4 日本語の扱いデータ項目には当然日本語対応 RDBMS では取扱いが可能である表名列名では RDBMS により異なることに注意 5 常数の取扱い数値常数はで囲む必要は無い ( e23) 文字常数はで囲むこと (a 商品学生 ) 6 コメントの記入マイナス記号二つでその行の終わりまでコメントに出来る例 :SELECT * -- 全ての列の検索 /* */ で囲んだ範囲をコメントに出来る

20 3.3 列名の記述通常は列名を使うが表の間でダブル時は. で結ぶ商品名商品 TBL. 商品名列名の後にスペースを置いて別名を定義できる商品商品名称列名別名計算式の結果を別名で表すことが出来る SELECT ( 支給総額 - 控除額 ) 手取額計算式別名

21 3.4 ワイルドカード比較演算ではあいまいな文字列を指定することができる (ACCESS では *? になる ) % は複数の文字列にマッチさせる場合 _ は一文字の文字列 ( アンタースコア記号 ) 例 :SELECT * FROM 商品 TBL WHERE 商品名 LIKE %AAA% またはまたはまたは A%A A- -A

22 3.5 予約語予約語は表名列名には使用できないただしで囲めば使用可能例 : SELECT INSERT FROM SELECT DBMS により多少異なる場合がある ALL CROSS FOREIGN JOIN ORDER SUM ALTER AND ANY AS AVG BETWEEN CURSER CECLARE DELETE DESC DISTINCT DROP FROM FULL GRANT GROUP HAVING IN KEY LEFT LIKE LOWER MAX MIN OUTER PRIMARY REFERENCES REVOKE ROLLBACK SCHEMA TABLE TRUE UNION UNIQUE UNKNOWN UPDATE BY COMMIT EXCEPT EXEC INNER INSERT NOT NULL SELECT SET UPPER USER COUNT CREATE EXISTS FALSE INTERSECT INTO ON OR SOME SUBSTRING WHERE

23 4. データ定義 ( 基本編 ) 4.1 データ定義文の構造 CREATE 文で概念スキーマ ( 表 ) 外部スキーマ( ビュー ) を定義 GRANT 文でデータアクセスの権限を付与するまたは SCHEMA CREATE DATABASE ~ CREATE TABLE ~ CREATE VIEW ~ GRANT ~

24 4.2 データ定義方法 CREATE DATABASE 住所録 CREATE TABLE 名刺帳名刺帳社名 30 漢字 ( 社名 NCHAR(30), 所属 30 漢字所属 NCHAR(30), 氏名 20 漢字氏名 NCHAR(20), 電話 12 数字電話 INT ) GRANT ALL TO 末次住所録 DB

25 4.3 テーブル定義方法テーブルのデータ構造を定義するもの CREATE TABLE テーブル名 ( データ項目名データ型 ( 長さ ) NULLまたは NOT NULL UNIQUE DEFAULT デフォルト値データ項目名 )

26 4.3 ( 補足 ) データ型の種類データ型の種類データ型の表記法文字列固定長 character (char) 可変長 character varying (varchar) ハイナリー固定長 bit 可変長 bit varying 数値数字 numeric ( 長さ, 小数点長さ ) 数字 decimal ( 長さ, 小数点長さ ) 数字 dec ( 長さ, 小数点長さ ) 整数 integer (int) 科学 float または real 日付日 date 時刻 time 2ハイト文字固定長 nchar 可変長 nvarchar

27 4.4 VIEW 定義方法 ( 仮想表 ) 複数テーブルから作成することが出来る定義時に仮想のテーブルが作成される分けでは無い外部スキーマのデータ構造を定義するもの CREATE VIEW ビュー名 ( データ項目名, データ項目名, ) AS SELECT 計算式 FROM 表名 WHERE 条件式ビューの効用 : 開発効率面セキュリティ面問合せ文 ( 検索文 ) と同形式

28 4.5 DB 生成方法 1 ユーザープログラムで DB 生成するケース INSERT 句でデータを挿入するロード用ファイルデータの GET INSERT INTO 学生名簿 TBL VALUE (100, システム科, 末次, 文雄,3 年次 ) 学生名簿 TBL 2DBMS のユーティリティ使用ケース DB 生成用のユーティリティを用意している DBMS が多い生成用のファイル作成ロード用ファイル DB ロードのユーティリティテーブル

29 5.DB 管理 ( 基本編 ) 5.1 権限付与の定義アクセス権限を規定する GRANT CREATE DATABASE, CREATE TABLE, GRANT SELECT, CREATE VIEW TO さん INSERT,UPDATE,DELETE TO さん REVOKE システム権限オブジェクト権限アクセス権を取り消す注 : 実際には個人をグルーピングして権限を管理する

30 5.2 処理の制御データ一貫性保持のために処理の制御を行う例示 : 振替送金 : 普通預金口座 A から 100 万円を口座 B に送金する更新開始 *1 口座 A の残高チェック異常? *1 begin work とか begin transaction 口座 A から 100 万円差し引く異常? 更新終了口座 Bに100 万円を加える異常? 方法 1 COMMIT(DBに反映する ) 方法 2 ROLLBACK( 全て元に戻す )

31 6. データ操作 ( 基本編 ) 6.1 単純な DB 検索商品価格 TBL SELECT 商品名, 価格, 仕入先 FROM 商品価格 TBL 商品コード商品名価格仕入先仕入単価 WHERE 価格 <10000 ORDER BY 商品コード ; SELECT 文の中の説明 SELECT 計算式 FROM 表名 WHERE 条件式 ORDER BY 行の順序

32 6.2 単純な DB 更新 INSERT INTO 商品価格 TBL ( 商品コート商品名価格仕入先単価 ) VALUES (10030, 商品名 BB, 1500, UPDATE 商品価格 TBL Y 商事,1200) ; SET 商品名 = 商品名称 AAA WHERE 商品コード = ; DELETE FROM 商品価格 TBL 追加 WHERE 商品コード = ; 上書き削除商品コード商品価格 TBL 商品名価格仕入先仕入単価

33 7. レポート課題 1SQL の主要な特長と関係モデルとの相違点は何か 2 次ページの事例についてテーブル定義をして下さい 3 同事例でデータ検索のための SELECT 文の作成 1 レポートの内容レベルは A4x1 枚程度 2 次回の授業開始時に提出して下さい ( ただしそれ以前に提出する場合はメールで願いますアドレス : fwhy6454@mb.infoweb.ne.jp )

34 7. の課題用の事例 2 課題のテーブル学科 TBL 学生 TBL 学科番号学科名称主任教授学籍番号年次入学年度氏名所属 TBL 3 データ検索の事例学科コード年度学生コード ( 注 ) 学科番号学科コードの間及び学籍番号学生コードの間のカーディナリティは同じ前提 2002 年度の学科を受ける 1999 年入学の学生の一覧表を作成する一覧表に必要な列は学科名称学籍番号氏名である

35 8. 参考書ほか大木幹雄テータヘース設計の基礎 ( 日本理工出版会 ) 林衛 ER モデルによるデータベース設計技法 (SRC 社 ) 小野哲ほかまるごと図解 SQL がわかる ( 技術評論社 ) 宮坂雅輝 SQL ハンドブック ( ソフトバンク社 ) 村上毅ほか MySQL 活用ガイド ( 秀和システム ) ライアンほか SQL フロクラミンク入門 ( ソフトハンク )

36 課題 6 の解答例テーブル定義 CREATE DATABASE 学科 ; CREATE TABLE 学科 TBL ( 学科番号 INT(7) NOT NULL UNIQUE, 学科名称 NCHAR(10), 主任 NCHAR(10) ); CREATE TABLE 学生 TBL ( 学籍番号 CHAR(7) NOT NULL UNIQUE, 年次 INT(1), 入学年度 INT(4), 氏名 NCHAR(10) ); CREATE TABLE 所属 TBL ( 学科コード INT(7) NOT NULL, 年度 INT(4), 学生コード CHAR(7), );

37 課題 6 の解答例 SELECT 文 SELECT 学科 TBL. 学科名称, 学生 TBL. 学籍番号, 学生 TBL. 氏名 FROM 学科 TBL, 学生 TBL, 所属 TBL 結合 (JOIN) WHERE 学生 TBL. 入学年度 =1999 AND 所属 TBL. 年度 =2002 AND 学科 TBL. 学科番号 = 所属 TBL. 学科コード AND 学生 TBL. 学籍番号 = 所属 TBL. 学生コード ORDER BY 所属 TBL. 学科コード, 所属 TBL. 学生コード ; 学科名称学籍番号氏名電子工学 A Aさん電子工学 A Bさん情報工学 A Cさん ~ ~ ~

Microsoft PowerPoint - db03-5.ppt

Microsoft PowerPoint - db03-5.ppt データベース言語 SQL リレーショナルデータモデルにおけるデータ操作言語 : リレーショナル代数少なくともリレーショナル代数と同等のデータ検索能力をもつときリレーショナル完備という. リレーショナル代数はユーザフレンドリではない. 自然な英文による質問の表現が必要になる. リレーショナルデータベース言語 SQL 英文による簡単な構文リレーショナル代数でできない, 合計, 平均, 最大などの計算機能の組み込み.