データベースと応用システム：SQL(3)その他の命令

Transcript

1. (c)長岡技術科学大学 電気系 1 データベースと応用システム ＳＱＬ(3) ～その他の命令～ 山本和英 長岡技術科学大学 電気系
2. (c)長岡技術科学大学 電気系 2 データの操作
3. (c)長岡技術科学大学 電気系 3 データの直接追加 INSERT INTO 社員 (社員番号, 所属, 氏名, 年齢) VALUES (C2, 'Ｃ部', 近藤, 24) 社員 番号 所属 氏名 年齢 C1 Ｃ部 佐藤 40 A1 Ａ部 小林 35 B1 Ｂ部 田中 38 B2 Ｂ部 池田 23 B3 Ｂ部 鈴木 31 【社員】 社員 番号 所属 氏名 年齢 C1 Ｃ部 佐藤 40 A1 Ａ部 小林 35 B1 Ｂ部 田中 38 B2 Ｂ部 池田 23 B3 Ｂ部 鈴木 31 C2 Ｃ部 近藤 24
4. (c)長岡技術科学大学 電気系 4 他の表からデータの追加 INSERT INTO 社員 (社員番号, 所属, 氏名, 年齢) SELECT 社員番号, 所属, 氏名, 年齢 FROM 子会社社員 WHERE 状態 = '出向' 【社員】 （略：前ページ） 社員 番号 所属 氏名 年齢 状態 C14 Ｃ部 三上 33 B11 Ｂ部 松田 42 出向 A13 Ａ部 伊藤 25 出向 C12 Ｃ部 宮崎 27 B13 Ｂ部 山口 38 【子会社社員】 社員 番号 所属 氏名 年齢 C1 Ｃ部 佐藤 40 A1 Ａ部 小林 35 B1 Ｂ部 田中 38 B2 Ｂ部 池田 23 B3 Ｂ部 鈴木 31 B11 Ｂ部 松田 42 A13 Ａ部 伊藤 25
5. (c)長岡技術科学大学 電気系 5 データの変更 UPDATE 社員 　　SET 氏名 = '中村' WHERE 社員番号 = 'B2' 社員 番号 所属 氏名 年齢 C1 Ｃ部 佐藤 40 A1 Ａ部 小林 35 B1 Ｂ部 田中 38 B2 Ｂ部 池田 23 B3 Ｂ部 鈴木 31 【社員】 社員 番号 所属 氏名 年齢 C1 Ｃ部 佐藤 40 A1 Ａ部 小林 35 B1 Ｂ部 田中 38 B2 Ｂ部 中村 23 B3 Ｂ部 鈴木 31
6. (c)長岡技術科学大学 電気系 6 データの削除 DELETE FROM 社員 WHERE 社員番号 = 'C1' 社員 番号 所属 氏名 年齢 C1 Ｃ部 佐藤 40 A1 Ａ部 小林 35 B1 Ｂ部 田中 38 B2 Ｂ部 池田 23 B3 Ｂ部 鈴木 31 【社員】 社員 番号 所属 氏名 年齢 A1 Ａ部 小林 35 B1 Ｂ部 田中 38 B2 Ｂ部 池田 23 B3 Ｂ部 鈴木 31
7. (c)長岡技術科学大学 電気系 7 権限の付与
8. (c)長岡技術科学大学 電気系 8 権限の付与 GRANT 権限+ ON テーブル TO ユーザ+ • 権限 – SELECT, INSERT, DELETE, UPDATE – UPDATE(列)とすると指定した列のみ更新可能 – ALL PRIVILEGES　 全権限 • ユーザ – ユーザを指定する。 – PUBLIC　 全ユーザ
9. (c)長岡技術科学大学 電気系 9 GRANT 使用例 GRANT SELECT ON 社員 TO tanabe tanabe に【社員】の参照権限を与える。 GRANT SELECT, UPDATE ON 社員 　TO Ｃ課長, Ｊ課長 Ｃ課長とＪ課長に【社員】の参照と更新権限を与える。 GRANT UPDATE(電話番号) ON 得意先 TO PUBLIC 全員に【得意先】の電話番号の更新権限を与える。
10. (c)長岡技術科学大学 電気系 10 GRANT のオプション WITH GRANT OPTION 他のユーザに与えられた権限を与えることができる。
11. (c)長岡技術科学大学 電気系 11 権限の取り消し REVOKE 権限+ ON テーブル FROM ユーザ+ • 権限 – SELECT, INSERT, DELETE, UPDATE – UPDATE(列)とすると指定した列のみ更新可能 – ALL PRIVILEGES　 全権限 • ユーザ – ユーザを指定する。 – PUBLIC　 全ユーザ GRANTとほぼ同じだがTOとFROMが違うので注意。
12. (c)長岡技術科学大学 電気系 12 REVOKE 使用例 REVOKE SELECT ON 社員 FROM tanabe tanabe から【社員】の参照権限を取り消す。 REVOKE SELECT, UPDATE ON 社員 　FROM Ｃ課長, Ｊ課長 Ｃ課長とＪ課長から【社員】の参照と更新権限を取り消す。 REVOKE UPDATE(電話番号) ON 得意先 FROM PUBLIC 全員から【得意先】の電話番号の更新権限を取り消す。
13. (c)長岡技術科学大学 電気系 13 ビュー表の定義
14. (c)長岡技術科学大学 電気系 14 ビュー表とは データベース中に実際に存在する表（実表）ではなく、 閲覧のために実表から仮想的に生成した表のこと。 CREATE VIEW によって定義する。 （ビュー表の利点） • セキュリティ：見せたくない項目がある • 一覧性：複数の表を照合しながら見るのは大変 • 処理の簡素化：カーソル処理（後述）する際に楽
15. (c)長岡技術科学大学 電気系 15 CREATE VIEWの定義例 CREATE VIEW 社名一覧 AS SELECT 会社ID, 社名 FROM 得意先 CREATE VIEW 県内企業 AS SELECT * FROM 得意先 WHERE 住所 LIKE '新潟県%' CREATE VIEW 受注(受注ID,日付,社名) AS SELECT X.受注ID, X.日付, Y.得意先 FROM 伝票 X, 得意先 Y WHERE X.得意先ID = Y.得意先ID
16. (c)長岡技術科学大学 電気系 16 更新可能なビュー表 • ビューに対してもINSERT, UPDATE, DELETEによる 更新は可能である。 • ただし、以下の場合は不可： – 集合関数(AVG, MAX)を使っている – GROUP BY, HAVING を使っている – 複数の表を使っている – 同一の表で副問い合わせをしている – 重複削除(DISTINCT)を使っている
17. (c)長岡技術科学大学 電気系 17 CREATE VIEWのオプション CREATE VIEW 県内企業 AS SELECT * FROM 得意先 WHERE 住所 LIKE '新潟県%' WITH CHECK OPTION 新潟県以外のデータを追加するとエラーになる CREATE VIEW 県内企業 AS SELECT * FROM 得意先 WHERE 住所 LIKE '新潟県%' WITH READ ONLY 読み取り専用
18. (c)長岡技術科学大学 電気系 18 ビューと権限 （作成時） • 元表のSELECT権限が必要。WITH GRANT OPTION があれば他のユーザに権限を付与することも可能。 （使用時） • 元表の所有者は元表の権限に従う。 • 所有者以外は、ビュー表の権限に従う。 • 追加、削除、更新はビュー表が更新可能であること が大前提。
19. (c)長岡技術科学大学 電気系 19 体現ビュー • 体現ビュー(materialized view)とは、実表のよう に内容が保存されているビュー表のこと。 • ビューの高速化が可能。 • 元表の内容が変更された時には体現ビューも変更 する。この操作をリフレッシュと呼ぶ。
20. (c)長岡技術科学大学 電気系 20 カーソル
21. (c)長岡技術科学大学 電気系 21 カーソル • 本来は、データ中で現在処理している位置を示す マークのこと。 • ＳＱＬでは、SELECTなどの結果が一般に複数行に なるため、１行ずつ処理する必要がある。このため にカーソルという概念を用いて処理を行う。 • このように、各種プログラム・アプリケーション中に ＳＱＬを埋め込んでアクセスする機能を埋め込みＳ ＱＬとも呼ぶ。
22. (c)長岡技術科学大学 電気系 22 カーソルの定義 EXEC SQL DECLARE カーソル名 CURSOR FOR SELECT … FROM … WHERE … END-EXEC • EXEC SQL … END-EXEC はSQL文であることを示 すためにSQL文の前後に入れる。次ページのOPEN, FETCH等も同様。 （この部分は 普通のSELECT文）
23. (c)長岡技術科学大学 電気系 23 カーソル使用例 OPEN カーソル 　　カーソルの使用開始を宣言 FETCH カーソル INTO :A, :B, :C 　　データを取り出して変数に格納 CLOSE カーソル 　　カーソルの使用終了を宣言 UPDATE テーブル SET … WHERE CURRENT OF カーソル 　　カーソルが指す行の更新 DELETE FROM テーブル WHERE CURRENT OF カーソル 　　カーソルが指す行の削除