結合演算 ( 復習 ) データベース論 (9) R 社員番号 046 064 011 011 氏名麻生太郎安部晋三与謝野馨森喜朗 部門総務課 S 部門総務課 電話 45 4567 問合せ言語と SQL(2) R S 社員番号 046 064 011 011 氏名麻生太郎安部晋三与謝野馨森喜朗 部門総務課 電話 45 4567 DB-9 4 結合演算 結合演算 ( 例題演習 ) R 社員番号 046 064 011 011 氏名 麻生太郎 安部晋三 与謝野馨 森喜朗 部門 総務課 S R S 部門 総務課 電話 45 4567 社員番号 046 064 011 011 氏名 麻生太郎 安部晋三 与謝野馨 森喜朗 SELECT 社員番号, 氏名, 部門, 電話 FROM R,S WHERE R. 部門 = S. 部門 R の部門 = S の部門 部門総務課 電話 45 4567 下記の社員と部署の表を結合する問合せは何か 部署 部コード 営業 営業 社員 DB-9 5 DB-9 6
結合演算 自己結合 この自己結合と同じことを 社員 (X と呼ぶ ) 社員 (Y と呼ぶ ) 社員 (X と呼ぶ ) 社員 (Y と呼ぶ ) 33 35 33 35 33 35 33 35 SELECT * FROM 社員 X, 社員 Y WHERE X. =Y. AND X. >Y. DB-9 7 SELECT * FROM 社員 X WHERE X. >(SELECT Y. FROM 社員 Y WHERE X. =Y. ) DB-9 8 JOIN による結合 (SQL-92) 92 で外部結合を導入 (OUTER JOIN) 外部結合 : 元々 結合は相手がなければ取り込まないが 外部結合は相手がないとき NULL として取り込む 基本的な結合は JOIN がなくてもできる ( 前述 ) JOIN による結合 (SQL-92) 92 で外部結合を導入 (OUTER JOIN) 102 外部結合 : 元々 結合は相手がなければ取り込まないが 外部結合は相手がないとき NULL として取り込む 山田俊夫 井上正一 結合 通常の結合 (INNER JOIN) 102 山田俊夫 外部結合 (OUTER JOIN) 山田俊夫 井上正一 NULL DB-9 9 DB-9 10
元のデータ JOIN による結合 ( やってみた 1) mytest1 mytest2 id hobby 00r950 skating 00r951 reading books 00r952 jogging 00r953 travelling 元のデータ JOIN による結合 ( やってみた 2) mytest1 mytest2 id hobby 00r950 skating 00r951 reading books 00r952 jogging 00r953 travelling 前に述べた書き方で結合 select * from mytest1, mytest2 where mytest1.id=mytest2.id; --------+---------------+ id hobby --------+---------------+ 00r950 skating 00r951 reading books 00r952 jogging --------+---------------+ 左外部結合 LEFT OUTER JOIN select * from mytest1 left outer join mytest2 using (id); --------+---------------+ id hobby --------+---------------+ 00r950 skating 00r951 reading books 00r952 jogging --------+---------------+ 左 (mytest1) を基準にして結合 INNER JOIN の書き方で結合 select * from mytest1 inner join mytest2 using (id); --------+---------------+ id hobby --------+---------------+ 00r950 skating 00r951 reading books 00r952 jogging --------+---------------+ 上の書き方と意味は全く同じ DB-9 11 select * from mytest1 right outer join mytest2 using (id); --------+---------------+ id hobby --------+---------------+ 右外部結合 RIGHT OUTER JOIN 00r950 skating 00r951 reading books 00r952 jogging NULL NULL NULL 00r953 travelling --------+---------------+ 右 (mytest2) を基準にして結合 DB-9 12 USING ではなくて ON 条件式を使う 条件式を書かず NATURAL を指定 JOIN による結合 ( やってみた 3) select * from mytest1 left outer join mytest2 on (mytest1.id=mytest2.id); --------+---------------+ id hobby --------+---------------+ 00r950 skating 00r951 reading books 00r952 jogging --------+---------------+ select * from mytest1 natural left outer join mytest2; --------+---------------+ id hobby --------+---------------+ 00r950 skating 00r951 reading books 00r952 jogging --------+---------------+ select * from mytest1 natural right outer join mytest2; --------+---------------+ id hobby 同上 但し右結合 --------+---------------+ 00r950 skating 00r951 reading books 00r952 jogging NULL NULL NULL 00r953 travelling --------+---------------+ 左 (mytest1) を基準にして結合 右 (mytest2) を基準にして結合 DB-9 13 JOIN による結合 ( ついでに ) select * from mytest1 cross join mytest2; --------+---------------+ id hobby --------+---------------+ CROSS JOINは 00r950 skating 00r950 skating 2つの表の ONやUSINGを 00r950 skating 直積使用しないと 00r951 reading books 00r951 reading books 直積を返す 00r951 reading books 00r952 jogging 00r952 jogging 00r952 jogging 00r953 travelling 00r953 travelling 00r953 travelling --------+---------------+ select * from mytest1, mytest2; --------+---------------+ id hobby 直積の基本的 --------+---------------+ 2つの表のな求め方 00r950 skating 00r950 skating 直積 00r950 skating 00r951 reading books 00r951 reading books 00r951 reading books 00r952 jogging 00r952 jogging 00r952 jogging 00r953 travelling 00r953 travelling DB-9 00r953 travelling --------+---------------+ 14
JOIN による結合例題演習 下記の社員と部署の表を結合する問合せは何か JOIN を使って書いてみよ 部署 部コード 営業 営業 社員 DB-9 15 データの更新 : 行の挿入 削除 更新 INSERT 文列を挿入 ( 追加 ) INSERT INTO 表名 ( 列名,, 列名 ) VALUES ( 列の値,, 列の値 ) insert into mytest1 (name) values ('sato kenji'); sato kenji NULL NULL 列 name しか挿入しなかったので 残りの列は値 NULL DB-9 具合が悪いことが多いので 全部の列を挿入する 16 全部の列を挿入する insert into mytest1 (name,id,age) values ('sato kenji','00r953',20); DELETE 文 列を削除 DELETE FROM 表名 WHERE < 条件 > < 条件 > で消したい列を ( うまく ) 指定する 行の順番に意味がない ( 集合!) ので 何か条件が必要 キー とか 主キー とか 列の指定の順序を変えてみた insert into mytest1 (age,id,name) values (20,'00r953','sato kenji'); 値の順序も変えなければならない delete from mytest1 where id='00r953'; DB-9 17 DB-9 18
delete from mytest1 ; Empty set WHERE で条件を書かないとすべてが削除の対象になり表は空になった UPDATE 文 列を変更 UPDATE 表名 SET 列 = 値,, 列 = 値 WHERE < 条件 > < 条件 > で指定された行に対して 列の値を 値 に置き換える delete from mytest1 where age=20; 複数の行が条件に適合し消された例 DB-9 19 update mytest1 set name='suzuki kiyoshi' where id='00r953'; +----------------+--------+------+ +----------------+--------+------+ suzuki kiyoshi 00r953 20 +----------------+--------+------+ DB-9 20 update mytest1 set name='sasaki hajime',id='00r999',age= where id='00r953'; sasaki hajime 00r999 SET 句に複数の変更を書いた例 name, id, age とも変更している ( 主キーとなる id も変えているので次のアクセス時には要注意 ) update mytest1 set name='sato kenji'; +------------+--------+------+ +------------+--------+------+ sato kenji 00r950 19 sato kenji 00r951 20 sato kenji 00r952 19 +------------+--------+------+ WHERE を書かないのですべての行が変更の対象になった DB-9 21 INSERT,DELETE,UPDATE 例題演習 下記の社員の表に < :8 名前 : 伊藤博文 : :> を追加する SQL 文を書け を削除する SQL 文を書け のを に変更する SQL 文を書け 社員表 DB-9 22
更新処理 COMMIT,ROLLBACK トランザクション型の使い方で役立つ COMMIT から COMMIT の間の更新は一時的 COMMIT した時点で確定する ROLLBACK すると前に COMMIT した時点に戻る AUTO COMMIT 更新のコマンドを実行するたびに COMMIT される 実習環境では試せないので注意 表の作成 CREATE TABLE CREATE TABLE 文 表を作成 CREATE TABLE 表名 ( 列名データ型や他の指定,, 列名データ型や他の指定 ) ( 列名データ型その他,, 列名データ型その他 ) に指定された列をもつ表を作成する create table mytest (name char(20), id char(6), age int); insert into mytest (name, id, age) values ('yamada ichiro', '00r599', 22); select * from mytest; yamada ichiro 00r599 22 DB-9 DB-9 24 表の作成 CREATE TABLE データ型 教科書 p137 その他の指定 PRIMARY KEY 主キーであることを指定 NOT NULL 値がNULLでないことを指定 FOREIGN KEY 外部キーであることを指定などなど create table mytest (name char(20) not null, id char(6) not null, age int, primary key (id)); Query OK, 0 rows affected (0.04 sec) insert into mytest (name, id, age) values ('yamada ichiro', '00r599', 22); Query OK, 1 row affected (0.00 sec) insert into mytest3 (name, id, age) values ('tanaka taro', '00r599', 24); ERROR 1062: Duplicate entry '00r599' for key 1 DB-9 表の変更 ALTER TABLE ALTER TABLE 文 表を変更 ALTER TABLE 表名変更の内容 ALTER TABLE 表名 ADD 列名データ型その他指定 ALTER TABLE 表名 MODIFY 列名データ型その他 alter table mytes1 drop hobby; alter table mytest1 add hobby char(20); alter table mytest1 modify name char(7); -------+ hobby +---------+--------+------+ -------+ NULL +---------+--------+------+ NULL tanaka 00r950 19 -------+ yamada 00r951 20 DB-9 +---------+--------+------+
ビューの作成 CREATE VIEW 実表 vs ビュー 実表 : 物理的に存在する表 CREATE TABLE ビュー : 仮想の表 CREATE VIEW ねらい : 不要な情報を隠す セキュリティ的な意味もある 実表の ( 参照しない列の ) 情報を知らなくても済む create view myview as select name,id from mytest1; select * from myview; name id tanaka ichiro 00r950 yamada taro 00r951 toho hanako 00r952 DB-9 27 ビューの作成 CREATE VIEW ビューに対する更新は 元の表も更新する ( 条件付 ) update myview set name= sasaki hajime where id='00r951'; ビュー myview を更新した create view myview as select name,id from mytest1; select * from myview; name id tanaka ichiro 00r950 yamada taro 00r951 toho hanako 00r952 ビュー myview を作った select * from myview; 実表 mytest1 name id も更新 tanaka ichiro 00r950 された sasaki hajime 00r951 sasaki hajime 00r951 20 toho hanako 00r952 DB-9 脱線表のコピー CREATE TABLE 表名 AS CREATE TABLE 新表 AS SELECT * from 旧表 create table mytest3 as select * from mytest3; 但し NOT NULL 以外の制約 たとえば主キーなどの構造が コピーされないので 手で移す 必要がある DB-9 29 CREATE VIEW 例題演習 下記の社員の表から < 名前 > からなるビューを作成する SQL 文を書け 社員表 DB-9
整合性制約定義 (PRIMARY KEY) 主キー (PRIMARY KEY) 制約 列 (or 列の集合 ) を主キーに指定 重複不可 NOT NULLであることが必要 nameだけ create table mytest6 主キー (name char(20) not null primary key, id char(6) not null, age int not null); insert into mytest6 (name, id, age) values ('tanaka ichiro', '00r950', 19); select * from mytest6; -----+ -----+ -----+ name,id,age の 3 つ組が主キー create table mytest7 (name char(20) not null, id char(6) not null, age int not null, primary key (name,id,age)); insert into mytest7 (name, id, age) values ('tanaka ichiro', '00r950', 19); select * from mytest7; ( 左と同じなので略 ) insert into mytest7 (name, id, age) values ('tanaka ichiro', '00r951', 20); 成功し その結果は select * from mytest7; -----+ -----+ tanaka ichiro 00r951 20 insert into mytest6 (name, id, age) values ('tanaka ichiro', '00r951', 20); ERROR 1062: Duplicate entry 'tanaka ichiro for key 1 ここの値は -----+ DB-9 上とは異なる 31 整合性制約定義 (UNIQUE) 複数列の組合せが UNIQUE であるように制約 複数列の組合せで候補キーとなるとき (?) create table mytest5 (name char(20), id char(6), age int, unique (name,id,age)) insert into mytest5 (name, id, age) values ('tanaka ichiro', '00r950', 19); select * from mytest5; insert into mytest1 (name, id, age) values ('tanaka ichiro', '00r950', 19); insert into mytest5 (name, id, age) values ('tanaka ichiro', '00r950', 19); ERROR 1062: Duplicate entry 'tanaka ichiro-00r950-19' for key 1 DB-9 整合性制約定義 (FOREIGN KEY) 外部参照時に 参照先の ( キーの ) 更新 削除に関して制約 ( 参照先がなくなると困るから ) 参照先の表の定義の中に書く 社員表 102 山田俊夫 佐藤健二 22 実習環境では試せないので注意 場所表 DB-9 33 場所 千葉 東京 場所表からを削除するとの [ 場所 が決まらなくなる 場所表の定義時に 列 が社員表から参照されていることを教えておき 削除 更新するときに社員表を確認して 参照しているタプルがないことをチェックする 整合性制約定義 (CHECK, DOMAIN) 検査制約 (CHECK) ( 任意の ) 条件をユーザ ( プログラマ ) が指定する 表の作成時や下記の定義域作成時に指定 たとえば CHECK ( 性別 IN ( 男, 女 )) 定義域制約 (DOMAIN) ( 任意の ) 定義域をユーザ ( プログラマ ) が作成する CREATE DOMAIN 定義域名 AS データ型 DEFAULT デフォルト値 CHECK ( 条件指定 ) 条件指定はたとえば VALUE IN ( 値,, 値 ) 実習環境では試せないので注意 DB-9 34
プログラムと SQL プログラム言語から SQL を使う場合 ( 授業はほとんど省略 ) 静的 SQL( 組込み SQL) と動的 SQL 静的 : コンパイル時に SQL 確定 動的 : 実行時に生成 SQL= 集合演算 結果は集合 プログラム言語で扱いにくい 細工として カーソル機能 カーソル処理機能 問合せ結果が複数行からなるとき 結果の一行を指す カーソル を定義し それを一行ずつずらしながら 行を受け取る プログラム言語 ( アプリ ) との連携のほか ストアドプロ シージャ ( 後述 ) でも用いる DB-9 36 DB-9 35 スドアドプロシージャ (Stored Procedure) DB 側に手続き ( プロシージャ ) を置く機能 プログラムの一部 (DB アクセス部分 ) を括り出す 整合性制約を実現する部分を DB 側に取り込む効果 プログラム DB 間の通信量軽減 コンパイルすることによる処理速度の向上 ロジックの共用化 呼出し vs トリガー (DB 更新後に条件によって起動 ) アプリケーション アクセス処理 データベース アプリケーション データベース アクセス処理