2010年度講義資料 DBMS講義 07 SQL

(1)

データベース

第 6 回、第 7 回 SQL

鈴木幸市

(2)

前回の「トランザクション」の理解

度調査

 かなりきちんと理解していることがわかりまし

た。

 もっとも簡潔で的を得た回答

 複数の処理を必要とする一連の作業を全体として一

つの処理として管理するため

 トランザクションの原子性、一貫性に着目した

回答多数

 複数トランザクションの並列実行に着目した回

答も多数

 身の回りで「トランザクション」になりそうな

ものを考えてみると理解が深まると思います。

(3)

演習講評

 _ER _図以前に

 日本語での説明不足、用語不適切

 最初から 1 件しかあり得ないデータをモデ

ル化している

 同じ性質をもったデータを「複数」集めてモ

デル化したのが「実体」

 _M:N なのに N:1 と誤解している例多数

(4)

説明不足の例

ユーザ

メールアド

レス

名前

サーバー

ユーザ

メールアド

レス

名前

1 ¹

おそらく「電子メール」のことだと思われる

すると、どちらかが送信者でどちらかが受信者

受信者は複数あるのが普通なのでは？　 ---> 1:N?

(5)

最初から 1 件しかないデータをモデ

ル化

上野動物園

住所

建物

サーバー

上野動物園にい

る動物

名前

種類

1 ^N

上野動物園は最初から一つしかない

「同じ性質のデータを複数集めた」ものが「実体」なので

、たとえば、これは「動物園」というふうにすれば実体に

なる。

(6)

前のページの例を 1:N にしてみる

動物園

名前

住所建物

所有

檻

名前

広さ場所

1 ^N

飼育

動物

名前

種類特徴

N ¹

ただし、一つの檻で飼育する動物は一種類のみとする

。

これが、モデル化を

(7)

M:N なのに１： N と誤解

学生

学籍番号

名前

受講

講義

講義番号

講義名

1 ^N

講義 1

講義 2

講義 3

講義 4

学生 A

学生 B

学生 C

学生 D

学生 E

実は M:N でした

(8)

前の例を 1:N で作ってみる

学生

学籍番号

名前

受講

履修簿

講義番号

学籍番号

1 ^N _履修

講義

講義番号

講義名

N ¹

(9)

今日の内容

 _SQL _の背景

 _SQL _以前

 _CODASYL 型の手続き型インタフェース

 _QUEL _{などの言語}

 _SQL _の歴史

 _SQL _の特徴

 _SQL _の分類

 DDL, DML, DCL

 各 SQL 文の働きと例

(10)

SQL _の背景

 _SQL 以前はデータベースへの読み書きの手順をプロ

グラムとして記述していた

 _CODASYL _など

 データベースの内部構造の変更についていけない

 データ量や特性が時間と共に変わると最適な読み書

き手順も変えないと困る

 非手続き型のデータベース操作言語が必要となった

 _QUEL などの実験的な言語も提案された

(11)

SQL _の歴史

 ₁₉₇₀ _年代

 _IBMの SanJose 研究所の System R: SEQUEL 言語の提案

 ₁₉₇₈年バージョンアップして SQL 言語に

 多くのデータベースシステムで採用されはじめた

 ₁₉₈₀ _年代

 _{ISO, JIS}による標準化 (SQL87, SQL89)

 ₁₉₉₀ _年代

 _{ISO, JIS}標準化 (SQL92, SQL99)

 マルチメディア、サーバプログラミング言語などへの拡張

 ₂₀₀₀ _年代

 さらに標準化の努力継続

 大規模な言語になってきた

 C 言語よりも規模が大きいかもしれない

 標準化されているが ...

 すべての標準を実装している DBMS はない

 標準以外の独自の機能はほとんど全部の DBMS が実装している

(12)

SQL _の特徴

 _{プログラムである}

 _{非手続き型である}

 データの読み書き手順をいちいち記述しない

 ほしい結果だけを指定する→あとは言語処理系が面倒見てく

れる

 通常のプログラム言語に埋め込んで使う

 Embedded SQL

 _JDBC/ODBC

 その他多くのプログラム言語で利用できる

 端末から入力して同じことを実行できる

 _{一種のコマンド}

 _SQL のキーワードは大文字でも小文字でも OK

 ここでは、キーワードは大文字で表示する

 一つ一つの SQL を「文」と呼ぶ

(13)

SQL _の分類

 _SQL は次のように分類できる

 _DDL （ Data Definition Language: データ定義言語 )

 テーブルを作る（ CREATE TABLE)

 テーブル定義を変更する (ALTER TABLE)

 テーブルを消去する (DROP TABLE)

 インデックスを作る /変更する /消去する

 CREATE/ALTER/DROP INDEX

 ビューを作る /変更する /消去する

 CREATE/ALTER/DROP VIEW

 _等々

 DML (Data Manipulation Language ：データ操作言語 )

 タプルを追加する (INSERT)

 タプルを変更する (UPDATE)

 タプルを消去する (DELETE)

 タプルを読む (SELECT)

 DCL (Data Control Language: データ操作言語 )

 トランザクションの完了あるいは破棄の指示 (COMMIT/ABORT)

 _{テーブルのロック}

(14)

SQL _{の構文の詳しい解説は}

 _Oracle _{のリファレンス}

http://otndnld.oracle.co.jp/document/products/oracle11g/111/doc_dvd/s erver.111/E05750-03/toc.htm

 _PostgreSQL _{のリファレンス}

http://www.postgresql.jp/document/8.4/html/

(15)

データベースを使ってみたい場合

 学内の Access を使う

 トランザクション機能なし

 _SQL _{はサブセット}

 フリーな DBMS を使う

 _PostgreSQL

 http://www.postgresql.jp/

 _MySQL

 http://www.mysql.gr.jp/

 _Firebird

 http://tech.firebird.gr.jp/firebird/

(16)

細かなことはともかく、、、

 _SQL を使うと、これまでの講義で説明して

きた

 リレーショナルデータベース

でできることがすべてできる

 今日の講義ではその例を示していく

 前回までの講義内容との関連を確認してほし

い

(17)

例題のテーブル構造 (1)

 _{「従業員」テーブル}

 社員 ID 、所属 ID 、職位 ID 、姓、姓ふりがな

、名、名ふりがな、生年月日、採用日付、給

与

 _{「部署」テーブル}

 部署 ID 、部署名、責任者 ID

 _{「職位」テーブル}

 _{職位 ID 、職位名}

(18)

例題のテーブル構造 (2)

 図で書くと次のようになる

社員 ID

所属 ID

職位 ID

姓

姓ふりがな

名

名ふりがな

生年月日

採用日付

給与

部署 ID

部署名

責任者 ID

職位 ID

職位名

従業員 ^部署

職位

主キー

外部キー

(1) 外部キー

(2)

外部キー

(3)

(19)

テーブルを作る

CREATE TABLE 従業員 ( 社員 ID NUMERIC(8) PRIMARY KEY,

　　所属 ID NUMERIC(4) NOT NULL,

職位 ID NUMERIC(2) NOT NULL,

姓 VARCHAR(10) NOT NULL,

姓ふりがな VARCHAR(12) NOT NULL,

名 VARCHAR(10) NOT NULL,

名ふりがな VARCHAR(12) NOT NULL,

生年月日 DATE NOT NULL,

採用日付 DATE NOT NULL,

給与 NUMERIC(10) NOT NULL);

CREATE TABLE 部署 ( 部署 ID NUMERIC(4) PRIMARY KEY,

部署名 VARCHAR(12) NOT NULL,

責任者 ID NUMERIC(8) REFERENCES 従業員 ( 社員 ID));

CREATE TABLE 職位 ( 職位 ID NUMERIC(2) PRIMARY KEY,

職位名 VARCHAR(12) NOT NULL);

(20)

テーブルを作る解説

CREATE TABLE 部署 ( 部署 ID NUMERIC(4) PRIMARY KEY,

部署名 VARCHAR(12) NOT NULL,

責任者 ID NUMERIC(8) REFERENCES 従業員 ( 社員 ID));

テーブルを

作る SQL

文を示す

テーブル名 ^カラム ^名 ^カラムのデータ型

カラムの属性を並

べる：主キー

カラムの属性を並べ

る： NOT NULL 制

約

(21)

SQL _の分類

 _SQL は次のように分類できる

 _等々

 行を追加する (INSERT)

 行を変更する (UPDATE)

 行を消去する (DELETE)

 行を読む (SELECT)

(22)

まずは簡単な例：単純な SELECT 文

 _基本形

 _SLECT _{読むカラム}

FROM 読むテーブル

WHERE どのタプルを読むかの条件 ;

 _SELECT 句、 FROM 句、 WHERE 句などと呼ぶ

 _例

SELECT * FROM _{従業員 ;}

 読むカラム：＊は、すべてのカラムを読む。読む順

序はデータベースまかせ

 読むテーブル：「従業員」テーブル

 条件：書いていないので全部の行を読む

 _SELECT 文がすべての SQL の基本

 「クエリ」 (query) と呼ぶこともある

(23)

Select 文の構造

SELECT カラム [, カラム , … , カラム ]

FROM テーブル名 [, テーブル名 , …]

WHERE 条件 ;

SELECT 文

であること

を示す

読むカラム名を並べる。

複数のカラムはカンマで区切る。

すべてのカラムを読む場合は * を指定

する

FROM _句

であること

を示す

読むテーブル名を並べる。

複数のテーブル名はカンマで区切る。

複数テーブルを指定すると「直積」を

作る。

WHERE 句と組み合わせて結合を指定

WHERE 句する。

であること

を示す読む行が満足すべき条件を指定する

(24)

SELECT, FROM, WHERE _の考え方

 _SELECT _{の後のカラム名の並び}

 読むカラムの名前を示す



「射影」演算に相当する

 カラム名があいまいなとき (FROM にあるテーブルで同じ

名前のカラムがあるとき )



テーブル名 .カラム名のように、テーブル名とカラム名を「ピ

リオド」でつないで区別する

 _FROM _{の後のテーブルの並び}

 複数のテーブルの場合は、指定したテーブルで「直積」を作

り、その結果のテーブルから読む。

 _WHERE _{の後の条件}

 条件に合致した行だけを読む



「選択」演算に相当する

 _FROM に複数テーブルが指定してある場合

(25)

SELECT 文で読み出すカラムを指定する

 _例

　　 SELECT 名 , 社員 ID

FROM _{従業員 ;}

 読むカラム：名前と社員 ID だけを読む。

 読むテーブル：「従業員」テーブル

 条件：書いていないので全部の行を読む

(26)

Where で読み出す行を制限する

 _例

　　 SELECT 名 , 従業員 ID

FROM _従業員

WHERE 姓ふりがな = ' すずき ';

 読むカラム：名前と社員 ID だけを読む。

 読むテーブル：「従業員」テーブル

 条件：「姓ふりがな」カラムの値が「すずき」であ

る行だけを読む

(27)

もう少し複雑な条件にしてみる

 _例

SELECT 名 , 従業員 ID

FROM 従業員

WHERE 姓ふりがな = ' すずき '

AND 給与 > 300000;

 読むカラム：名前と社員 ID だけを読む。

 読むテーブル：「従業員」テーブル

 条件：「姓ふりがな」カラムの値がが「すずき」で、

かつ、給与が 300000 以上の行だけを読む

 条件は、 AND, OR, かっこなどを使っていくらで

も複雑にできる。

(28)

複数のテーブルから結果を合成する：結

 合社員と所属部署名を出力する例

　　

　　 SELECT 名前 , 部署名

FROM 従業員 , 部署

WHERE 所属 ID = 部署 ID ；



読むカラム：部署名と従業員名。



読むテーブル：「従業員」テーブルと「部署」テーブル⇒直

積！



条件：「従業員」の「所属 ID 」と「部署」の「部署 ID 」が同

じ行

 これが「結合」になっていることに注意！！

社員 ID 所属 ID 職位 ID 姓姓ふりがな名名ふりがな生年月日採用日付給与

部署 ID 部署名責任者 ID

職位 ID 職位名

従業員部署

職位

(29)

文字列の部分一致： LIKE

 _例

　　 SLECT 名前 , 従業員 ID

FROM _従業員

WHERE 姓ふりがな LIKE ' す %';

 読むカラム：名前と社員 ID だけを読む。

 読むテーブル：「従業員」テーブル

 条件：「姓ふりがな」が「す」ではじまる行だけを読む

 _LIKE _の書き方

 _% _{任意の長さの文字列}

 _{_} _{任意の 1 文字}



日本語の場合、「 1 文字」を「 2 バイト (2 文字 )」と扱うデー

タベースもあるので、注意。

(30)

Where 句で使う比較演算子の例

 _{値同士を比較する}

 _A=B ： A と B が等しい

 (例 ) 姓ふりがな = ' 鈴木 '

 A <> B ： A と B が等しくない

 A > B, A>=B, A<B, A<=B

 複数行と比較する：サブクエリ

 A IN (SELECT …) ： (SELECT …) で選択した中に A がある

 (例 ) WHERE A IN (SELECT 姓ふりがな FROM 従業員 WHERE

…₎

 取り出した「姓ふりがな」のどれかに「 A 」が一致するような行だけを選ぶ

 _NULL _{かどうかを調べる}

 _{IS NULL}

 (例 ) WHERE 住所 IS NULL

 住所の登録がない行だけを選ぶ

 IS NOT NULL

 (例 ) WHERE 電話番号 IS NOT NULL

(31)

結果を並べ替える : ORDER BY

 _例

SELECT 名前 , 従業員 ID

FROM 従業員

WHERE 姓ふりがな LIKE ' す %'

ORDER BY 姓ふりがな , 名ふりがな ;

 読むカラム：名前と社員 ID だけを読む。

 読むテーブル：「従業員」テーブル

 条件：「姓ふりがな」が「す」ではじまる行だけを読む

 並べ替え：姓のふりがなの順。同じ姓のふりがなの場合に

は、さらに名前のふりがなで並べ替える。

 この場合は、名前の読み順で並べかえる

 _{ORDER BY} _の書き方

 並べるのに使うカラム名を書く。

 複数の場合にはカンマで区切る。

 先に書いたカラムをつかってまず並べ替えを行う。値が同

じなら次のカラムを使って並べ替える。

(32)

集約演算 : 行数を数える

 _{集約演算とは}



複数の行の中の値を合計したり数えたりして一定の値を導き出す

 _例



SELECT COUNT(*)

FROM _従業員

WHERE 姓ふりがな LIKE ' す %';

 読むカラム：見つかった行数を読む。

 読むテーブル：「従業員」テーブル

 条件：「姓ふりがな」が「す」で始まる行だけを数える



_COUNT(*)

 _{行を数える関数。}

 _SELECTに関数を書ことができる

 複数の行の集計を行う関数を「集約関数」という。この演算が「集約演算」



代わりに COUNT( 姓ふりがな )を使うと：値が何種類あるかを調べ

る

(33)

集約演算：合計を数える、 SUM 関数

 _例

　　 SELECT SUM( 給与 )

FROM _従業員

WHERE 姓ふりがな = ' す

ずき ';

 読むカラム：給与の合計を求める。

 読むテーブル：「従業員」テーブル

 条件：「姓ふりがな」が「すずき」の行だけの

合計を求める

(34)

集約演算： GROUP BY ⇒ 集約の仕方の

指定 _ _例

　　 SELECT SUM( 給与 )

FROM 従業員

WHERE 姓ふりがな = ' すずき '

GROUP BY 所属 ID;

 読むカラム：給与の合計を求める。

 読むテーブル：「従業員」テーブル

 条件：「姓ふりがな」が「すずき」の行だけの合計を

求める

 集約の方法：所属ごとの合計を求める

 集約演算の単位を指定している！！

(35)

集約演算： ORDER BY とのあわせ技

 _例

　　 SELECT SUM( 給与 )

FROM 従業員

WHERE 姓ふりがな = ' すずき '

GROUP BY _{所属 ID}

ORDER BY _{所属 ID;}

 読むカラム：給与の合計を求める。

 読むテーブル：「従業員」テーブル

 条件：「姓ふりがな」が「すずき」の行だけの合計を

求める

 集約の方法：所属ごとの合計を求める

 並べ替え：所属の ID 順に並べ替える

(36)

SELECT, FROM, WHERE _の考え方

 _SELECT _{の後のカラム名の並び}

 読むカラムの名前を示す



「射影」演算に相当する

 カラム名があいまいなとき (FROM にあるテーブルで同じ

名前のカラムがあるとき )



テーブル名 .カラム名のように、テーブル名とカラム名を「ピ

リオド」でつないで区別する

 _FROM _{の後のテーブルの並び}

 複数のテーブルの場合は、指定したテーブルで「直積」を作

り、その結果のテーブルから読む。

 _WHERE _{の後の条件}

 条件に合致した行だけを読む



「選択」演算に相当する

 _FROM に複数テーブルが指定してある場合

(37)

複数の SELECT の結果を合成することも

 できる _{UNION :} 前後の SQL の結果を足す

 INTERSECT : 前の SQL の結果と後ろの

SQL の結果の共通部分を求める

 _EXCEPT ：前後の SQL の結果の差を求め

る

(38)

SQL _の分類

 _SQL は次のように分類できる

 _等々

(39)

テーブルを作る： CREATE TABLE 文

 _一般形

　　　 CREATE TABLE テーブル名

(カラム名型 , ....);

 _例

 CREATE TABLE 部署

(部署名 VARCHAR(20),

部署 ID NUMERIC,

責任者 ID NUMERIC);

 注意：標準では NUMERIC, Oracle の場合は NUMBR

 _SQL では、テーブルだけでなく、いろいろなものを

CREATE で作る

 _例

 CREATE INDEX, CREATE SCHEMA, 等々

(40)

型のいろいろ

 _数値

 INTEGER, NUMERIC など：固定小数点



_Oracle では NUMERIC の代わりに NUMBER を使う

 REAL, FLOAT, DOUBLE など：浮動小数点

 _文字列

 _CHAR(n)



固定の長さ n の文字列

 VARCHAR(n), CHARACTER VARYING(n)



最大 n の長さの可変長文字列

 _日付

 _DATE



_年月日

 _TIMESTAMP



_{年月日時分秒 ...}

(41)

制約をつける (1) ： UNIQUE と NOT

 NULL _例

　　 CREATE TABLE 部署

(部署名 VARCHAR(20) 　 UNIQUE 　 NOT NULL,

部署 ID NUMERIC 　　 UNIQUE 　 NOT NULL,

責任者 ID 　 NUMERIC 　 NOT NULL);

 部署名への制約： UNIQUE と NOT NULL

 _{同じ名前の部署はない}

 _{名前のない部署はない}

 部署 ID への制約： UNIQUE と NOT NULL

 すべての部署は ID で区別する

 _ID_{のない部署はない}

 責任者 ID への制約： NOT NULL のみ

 責任者のない部署はない

 一人の人が複数の部署の責任者を兼ねている場合がある

 だから、 UNIQUE 制約はつかない

(42)

制約をつける (2) ： REFERENCES

 _例

　　 CREATE TABLE 従業員

( ... ,

所属 ID 　 NUMERIC REFERENCES 部

署 ( 部署 ID),

...);

 「所属 ID 」は「部署」の「部署 ID 」カラムを参照し

ている

 「所属 ID 」カラムの値は、同じ値が「部署」テーブルの

「部署 ID 」カラムのどこかの行になければならない。

(43)

SQL _の分類

 _SQL は次のように分類できる

 _等々

(44)

テーブルを削除する : DROP TABLE

 CREATE TABLE の逆

 _例

　　 DROP TABLE 部署 ;

 _SQL では、 DROP でテーブル以外のもの

も削除できる。

 _例

 DROP INDEX, DROP SCHEMA _等々

(45)

SQL _の分類

 _SQL は次のように分類できる

 _等々

(46)

テーブル定義の変更： ALTER TABLE

 テーブルに次のような変更を加える

 カラムを追加 /削除 /名前変更

 _{制約を追加 /削除}

 _例

 ALTER TABLE _従業員

ADD COLUMN 住所

VARCHAR(50);

 ALTER TABLE 従業員

ALTER COLUMN 住所

ADD NOT NULL;

 ALTER TABLE _部署

DROP COLUMN 所在地 ;

 ALTER TABLE 部署

(47)

SQL _の分類

 _SQL は次のように分類できる

 _等々

(48)

インデックスを作る： CREATE INDEX

 _基本形

CREATE INDEX インデックス名

ON テーブル名 (カラム名 )；

 インデックス名：作るインデックスにつける名前

 テーブル名：どのテーブルにインデックを作るか

 カラム名：どのカラムの値を使ってインデックスを作るか

 _WHERE 句にインデックスを作ったカラム名が出てくると

、動作が早くなることが多い

 複数のカラムを合成してインデックスを作ることもできる

 _例

 CREATE INDEX 従業員 IDidx

ON 従業員 (従業員 ID) ；

(49)

テーブル同様 DROP コマンドでインデッ

クスを削除

 _例

 DROP INDEX _{従業員 IDidx;}

(50)

SQL _の分類

 _SQL は次のように分類できる

 _等々

(51)

テーブルに行を追加する： INSERT

 _基本形

　　 INSERT INTO テーブル名 [( カラム名 , ....)]

VALUES ( 値 , ....) [, ( 値 , …) …] ;

 テーブル名：行を追加するテーブル名

 (カラム名 , ...): 値を入力する先のカラム名の並び

 省略可能。この場合はシステムで定義されたカラムの順に値を指定していく

 SELECT * ....; で表示されるカラムの順序

 _{VALUES (} 値 , ...) ：各カラムの値。

 _例

 INSERT INTO 部署

VALUES (' 社長室 ', 1, 10);

 INSERT INTO 部署（部署名 , 部署 ID, 責任者 ID)

VALUES (' 社長室 ', 1, 10), (' 企画部 ', 2, 11);

 複数の行の値を一度に入力できる

(52)

SQL _の分類

 _SQL は次のように分類できる

 _等々

(53)

行の消去： DELETE

 _基本形

DELETE FROM テーブル名

WHERE 条件；

 テーブル名：削除する行が入っているテーブル名

 条件：この条件に一致した行が削除される

 _SELECT _と同じ

 _WHERE 句がないと、すべての行が削除される

 それでもテーブルは残っている

 データがないファイルがあるのと同じ

 削除する行を読み込まなくともよい

 _DROP _{と混同しないこと}

(54)

SQL _の分類

 _SQL は次のように分類できる

 _DDL （ Data Definition Language: データ定義言語 )

 _等々

 DML (Data Manipulation Language ：データ操作言語 )

 DCL (Data Control Language: データ操作言語 )

 トランザクションの完了あるいは破棄の指示

(55)

行の変更： UPDATE

 _基本形

　　 UPDATE テーブル名

SET カラム名 = 値 /式 [, カラム名 = 値 /式 … ]

WEHERE 条件 ;



テーブル名：変更する行が入っているテーブル名



カラム名 = 値 /式：値を変更するカラム名と設定する値

 _{値は式でもいい}

 給与 = 給与 *2 とすると、その行の給与の値が 2 倍になる



条件：条件を満たす行だけを変更する。 SELECT と同じ。



更新する行を読む必要がない

 _例

 _UPDATE 従業員 SET 給与 = 給与 *1.1;



全従業員の給与を 10% アップ

 _UPDATE 従業員 SET 住所 ='xxxx' WHERE 社員 ID=200;

社員番号 200 の社員の住所を変更

(56)

SQL _の分類

 _SQL は次のように分類できる

 _DDL （ Data Definition Language: データ定義言語 )

 _等々

 DML (Data Manipulation Language ：データ操作言語 )

 DCL (Data Control Language: データ操作言語 )

(57)

トランザクションの完了 /失敗

 _基本形

 _COMMIT;

 トランザクションが成功したことをデータベースに教える

 これで変更が確定される

 _ABORT;

 _ROLLBACK; _でもよい

 トランザクションが失敗したことをデータベースに教える

 トランザクションで行った変更は破棄され、トランザク

ションの実行前の状態に戻る

 トランザクションは自動的に開始される

 データベースによっては、 BEGIN をデータベースに送ら

ないと SQL 文ごとに内部で自動的に COMMIT されるもの

もある

 テーブルの作成や削除、変更もトランザクションとして扱

うことができるデータベースもある

 CREATE TABLE などが ABORT で無効にできる

(58)

SQL _の分類

 _SQL は次のように分類できる

 _DDL （ Data Definition Language: データ定義言語 )

 _等々

 DML (Data Manipulation Language ：データ操作言語 )

 DCL (Data Control Language: データ操作言語 )

(59)

ビューの定義：ビューとは？

 アプリケーションはテーブルの一部しか使わない

 最初からテーブルの一部だけを見せる

 カラム名などもアプリケーション専用にしてしまう

 アプリケーションの追加変更などでテーブルの形が変

わってもアプリケーションは変更せずに済む

 このような「仮想的な」表のことをビューと呼ぶ

 テーブルと同じように扱うことができる

 行の「変更」や「追加」「削除」には制限がつくこともある

(60)

ビューの定義：基本形と例

 _基本形

CREATE VIEW ビュー名 ( カラム名 , ...)

AS select 文；

 _例

 CREATE VIEW 社長室 ( 部署名 , 部署 ID, 責任者 )

AS SELECT 部署名 , 部署 ID, 責任者 ID

FROM 部署

WHERE 部署 ID=1;

 _効果

 _SELECT 責任者 ID FROM 部署 WHERE 部署 ID=1;

(61)

少し複雑な SEL: サブクエリ

 _WHERE 句の中に SELECT がある例

SELECT 従業員名 FROM 従業員

WHERE 所属 ID IN

(SELECT 部署 ID FROM 部署

WHERE 部署名 LIKE

'% 製造 %');

 _{上記の意味：}

 _{読むカラム：従業員名}

 _{読むテーブル：従業員}

 条件：従業員の所属する部署の名前に「製造」が入ってい

ること

(62)

プログラム内部から SQL を使う

 _{2 つのやり方がある}

 _{埋め込み SQL}

 プログラム言語を SQL で拡張する

 _API _を使う

 _SQL を文字列としてデータベースに与える

 _{JDBC, ODBC} などいくつかの方法がある

(63)

埋め込み SQL

 "EXEC SQL" で始まる文で拡張部を示す

 拡張部では、 : で始まる単語で元のプログ

ラムの変数を参照できる

 「カーソル」を使ってプログラム言語との

橋渡しをする

 _SQL では一度に複数の行を読むことができる

 プログラム言語では一度に一行しか処理でき

ない

 カーソルに SQL の結果をためて一行ずつプロ

グラム言語で処理する

(64)

埋め込み SQL の例

void foobar() { int i;

EXEC SQL BEGIN DECLARE SECTION; char empname[100];

char lastname[50]; char firstname[50];

EXEC SQL ND DECLARE SECTION;

EXEC SQL DECLARE execCursor CURSOR FOR SELECT 従業員名 , 姓ふりがな , 名ふりがな FROM 従業員

ODER BY 姓ふりがな , 名ふりがな； EXEC SQL OPEN execCursor;

while (1) {

EXEC SQL FETCH FROM execCursor

INTO :empname, :lastname, :firstname;

printf(" 従業員名ふりがな： %s%s, 従業員名 %s\n", lastname, firstname, empname);

2010年度講義資料 DBMS講義 07 SQL

データベース

第 6 回 、第 7 回 SQL

鈴木幸市

前回の「トランザクション」の理解

度調査

 かなりきちんと理解していることがわかりまし

た。

 もっとも簡潔で的を得た回答

 複数の処理を必要とする一連の作業を全体として一

つの処理として管理するため

 トランザクションの原子性、一貫性に着目した

回答多数

 複数トランザクションの並列実行に着目した回

答も多数

 身の回りで「トランザクション」になりそうな

ものを考えてみると理解が深まると思います。

演習講評

 ER 図以前に

 日本語での説明不足、用語不適切

 最初から 1 件しかあり得ないデータをモデ

ル化している

 同じ性質をもったデータを「複数」集めてモ

デル化したのが「実体」

 M:N なのに N:1 と誤解している例多数

説明不足の例

ユーザ

メールアド

レス

名前

サー バー

ユーザ

メールアド

レス

名前

1 1

おそらく「電子メール」のことだと思われる

すると、どちらかが送信者でどちらかが受信者

受信者は複数あるのが普通なのでは？ ---> 1:N?

最初から 1 件しかないデータをモデ

ル化

上野動物園

住所

建物

サー バー

上野動物園にい

る動物

名前

種類

1 N

上野動物園は最初から一つしかない

「同じ性質のデータを複数集めた」ものが「実体」なので

、たとえば、これは「動物園」というふうにすれば実体に

なる。

前のページの例を 1:N にしてみる

動物園

名前

住所 建物

所有

檻

名前

広さ 場所

1 N

飼育

動物

名前

種類 特徴

N 1

ただし、一つの檻で飼育する動物は一種類のみとする

。

これが、モデル化を

M:N なのに１： N と誤解

学生

学籍番 号

名前

受講

講義

講義番 号

講義名

1 N

第 6 回、第 7 回 SQL

 _ER _図以前に

 _M:N なのに N:1 と誤解している例多数

サーバー

1 ¹

受信者は複数あるのが普通なのでは？　 ---> 1:N?

サーバー

1 ^N

住所建物

広さ場所

1 ^N

種類特徴

N ¹

学籍番号

講義番号

1 ^N

学籍番号

講義番号

学籍番号

1 ^N _履修

講義番号

N ¹

 _SQL _の背景

 _SQL _以前

 _CODASYL 型の手続き型インタフェース

 _QUEL _{などの言語}

 _SQL _の歴史

 _SQL _の特徴

 _SQL _の分類

SQL _の背景

 _SQL 以前はデータベースへの読み書きの手順をプロ

 _CODASYL _など

 _QUEL などの実験的な言語も提案された

SQL _の歴史

SQL _の特徴

 _{プログラムである}

 _{非手続き型である}

 _JDBC/ODBC

 _{一種のコマンド}

 _SQL のキーワードは大文字でも小文字でも OK

SQL _の分類

 _SQL は次のように分類できる

SQL _{の構文の詳しい解説は}

 _Oracle _{のリファレンス}

 _PostgreSQL _{のリファレンス}

 _SQL _{はサブセット}

 _PostgreSQL