データベース06 - SQL（VIEW, ALTER, GRANTなど）
34
データベース 第6回 SQL（VIEW, ALTER, GRANTなど） 1 2015年5⽉14⽇（⽊） 7・8時限 担当：奥 健太
Transcript of データベース06 - SQL（VIEW, ALTER, GRANTなど）
データベース第6回
SQL（VIEW, ALTER, GRANTなど）
1
2015年5⽉14⽇（⽊） 7・8時限担当：奥 健太
SQL編
回 ⽇付 テーマ2 4/16 SQL概要3 4/23 SQL（CREATE, INSERT, DELETE, UPDATEなど）4 4/30 SQL（SELECT：基本）5 5/7 SQL（SELECT：結合，副問合せ）6 5/14 SQL（VIEW, ALTER, GRANTなど）7 5/21 SQL復習，中間試験（SQL）
2
SQLの主なコマンド⼀覧（1/2）
3
種類 コマンド 説明
DDL
CREATE DATABASE データベースを作成CREATE TABLE データベース内にテーブルを定義CREATE VIEW データベースにビュー（仮想テー
ブル）を定義DROP TABLE データベースからテーブルを削除DROP VIEW データベースからビューを削除ALTER TABLE テーブル構成を変更
SQLの主なコマンド⼀覧（2/2）
4
種類 コマンド 説明
DML
INSERT テーブルにデータを登録DELETE テーブルからデータを削除UPDATE テーブル内のデータを更新SELECT テーブル内からデータを検索して
取り出す
DCL
COMMIT 正常終了を通知ROLLBACK 異常終了を通知GRANT 権限を与えるREVOKE 権限を取り下げる
本⽇の講義で学ぶこと
ビューアクセス権限
テーブル構成の変更スクリプトファイルバックアップと復元
⼩説家テーブル
6
writerwriter_id name age
1東野圭吾 572宮部みゆき 543村上春樹 66
id name1001新潮社1002講談社1003⽂藝春秋1004集英社1005岩波書店
publisher
novel_id title writer_id pub_id year101秘密 1 1003 2001102⽩夜⾏ 1 1004 2002103容疑者Xの献⾝ 1 1003 2008104模倣犯 2 1001 2005105⽕⾞ 2 1001 19981061Q84 3 1001 2009107⾵の歌を聴け 3 1002 2004108海辺のカフカ 3 1001 2002109ノルウェイの森 3 1002 1987110使命と魂のリミット 1 1006 2010
novel
*manaba+R上の"writer.sql"内のSQL⽂を実⾏することで復元できる
ビュー
7
CREATE VIEW titles ASSELECTwriter.name AS writer,novel.title,publisher.name AS publisher
FROM writer, novel, publisherWHEREnovel.writer_id = writer.writer_idAND novel.pub_id = publisher.id;
ビュー（仮想テーブル）の作成CREATE VIEW
8
実際に存在するテーブルから抽出したデータを保持 アプリケーションで必要な項⽬のみを抽出
複雑な問合せを単純な問合せとして実⾏できる ビューへのアクセス権限設定によるセキュリティ強化
ビューのメリット
ビューの例
9
writerwriter_id
name age
1東野圭吾 572宮部みゆき 543村上春樹 66
id name1001新潮社1002講談社1003⽂藝春秋1004集英社1005岩波書店
publishernovel_id
title writer_id
pub_id
year
101秘密 1 1003 2001102⽩夜⾏ 1 1004 2002103容疑者Xの献⾝ 1 1003 2008104模倣犯 2 1001 2005
...... ... ... ...
novel
CREATE VIEW titles AS SELECT ...
writer title publisher東野圭吾 秘密 1 東野圭吾 ⽩夜⾏ 1 東野圭吾 容疑者Xの献⾝ 1 宮部みゆき 模倣犯 2 ... ... ...
titles
アクセス権限
10
ユーザへのアクセス権限の付与GRANT
11
GRANT 【権限】
ON 【データベース名】.【テーブル名】
TO '【ユーザ名】'@'【ホスト名】'IDENTIFIED BY '【パスワード】';
GRANT SELECT, INSERTON dbclass.writerTO 'oku'@'localhost'IDENTIFIED BY 'uko';
例
主な権限
12
権限 説明CREATE データベースとテーブルの作成DROP データベースとテーブルの削除INSERT テーブルへのデータの登録DELETE テーブルからのデータの削除UPDATE データの更新SELECT データの検索ALL すべての権限
ユーザからのアクセス権限の剥奪REVOKE
13
REVOKE 【権限】
ON 【データベース名】.【テーブル名】
FROM '【ユーザ名】'@'【ホスト名】';
REVOKE INSERTON dbclass.writerTO 'oku'@'localhost';
例
登録ユーザの確認と削除DROP USER
14
SELECT user, host FROM mysql.user;登録ユーザの確認
DROP USER '【ユーザ名】'@'【ホスト名】';ユーザの削除
DROP USER 'oku'@'localhost';例
テーブル構成の変更
15
カラムの追加ALTER TABLE ... ADD
16
ALTER TABLE 【テーブル名】
ADD 【カラム名】 【データ型】;
ALTER TABLE writerADD home CHAR(3);
例
カラムのデータ型の変更ALTER TABLE ... MODIFY
17
ALTER TABLE 【テーブル名】
MODIFY 【カラム名】 【新データ型】;
ALTER TABLE writerMODIFY name CHAR(16);
例
カラム名とデータ型の変更ALTER TABLE ... CHANGE
18
ALTER TABLE 【テーブル名】
CHANGE 【旧カラム名】 【新カラム名】 【新データ型】;
ALTER TABLE writerCHANGE writer_id wid CHAR(8);
例
カラムの削除ALTER TABLE ... DROP
19
ALTER TABLE 【テーブル名】
DROP 【カラム名】;
ALTER TABLE writerDROP home;
例
テーブル名の変更ALTER TABLE ... RENAME AS
20
ALTER TABLE 【旧テーブル名】
RENAME AS 【新テーブル名】;
ALTER TABLE writerRENAME AS novelist;
例
スクリプトファイル
21
スクリプトファイル
22
# dbclassデータベースがなければ作成する．CREATE DATABASE IF NOT EXISTS dbclass DEFAULT CHARACTER SET utf8;
# mountainテーブルを作成する．CREATE TABLE mountain(no INT PRIMARY KEY, name CHAR(5) NOT NULL, kana TEXT, altitude INT, system TEXT, prefecture TEXT);
# mountainテーブルにデータを登録する．INSERT INTO mountain VALUES(1, '利尻岳', 'りしりだけ', 1721,'利尻島', '北海道');INSERT INTO mountain VALUES(2, '羅臼岳', 'らうすだけ', 1660,'知床半島', '北海道');INSERT INTO mountain VALUES(3, '斜里岳', 'しゃりだけ', 1547,'知床半島', '北海道');...
スクリプトファイルの例
複数のSQL⽂を記述したファイル スクリプトファイルから直接MySQLに読み込ませるこ
とができる
スクリプトファイルの実⾏（MySQLコンソールから）
23
mysql>¥. 【スクリプトファイル名】
mysql>¥. c:¥mountain.sql例
スクリプトファイルの実⾏（Windowsのコマンドプロンプトから）
24
c:¥>mysql -u 【ユーザ名】 -p < 【スクリプトファイル名】
Enter password:【パスワード】
c:¥>mysql -u root < mountain.sql例
バックアップと復元
25
データベースのバックアップ
26
c:¥>mysqldump -u 【ユーザ名】 -p 【データベース名】 > 【ファイル名】
Enter password:【パスワード】
c:¥>mysqldump -u root dbclass > dbclass.sql
例
ダンプファイル
27
DROP TABLE IF EXISTS `author`;...CREATE TABLE `author` (
`aid` char(8) NOT NULL DEFAULT '',`name` char(16) DEFAULT NULL,`age` int(11) DEFAULT NULL,PRIMARY KEY (`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;...INSERT INTO `author` VALUES ('1','東野圭吾',57),('2','宮部みゆき',54),('3','村上春樹',66),('4','aaa',33);...
ダンプファイルの例
データベースを復元するための⼀連のSQL⽂で構成されている
データベースのバージョンに依存しない
データベースの復元
28
c:¥>mysql -u 【ユーザ名】 -p 【データベース名】 < 【ファイル名】
Enter password:【パスワード】
c:¥>mysql -u root dbclass < dbclass.sql
例
事前にデータベースは⽤意しておく必要がある
まとめ
ビューアクセス権限
テーブル構成の変更スクリプトファイルバックアップと復元
まとめ
ALTER TABLEADD MODIFY CHANGE DROP RENAME AS
GRANT REVOKE DROP USER
CREATE VIEW
スクリプトファイル
ダンプファイル
本⽇学習したキーワード〜SQL編〜
31
MySQL スクリプトファイルNOT-NULL制約 静的SQL
SQL 制約⼀意性制約 対話的DB操作外部キー制約 ダンプファイル外部キー データ制御⾔語（DCL）仮想テーブル データ操作⾔語（DML）カラム（列） データ定義⾔語（DDL）関係代数 データベース関係データベース管理システム（RDBMS） テーブル（表）関係データモデル テーブル副問合せ完全外部結合 問合せ（クエリ）交差結合 等結合参照整合性制約 動的SQL⾃⼰結合 内部結合⾃然結合 左外部結合集約関数 ビュー主キー 副問合せ主キー制約 右外部結合スカラ副問合せ ロウ（⾏）
これまでに学習したキーワード〜SQL編〜
32
MySQL スクリプトファイルNOT-NULL制約 静的SQL
SQL 制約⼀意性制約 対話的DB操作外部キー制約 ダンプファイル外部キー データ制御⾔語（DCL）仮想テーブル データ操作⾔語（DML）カラム（列） データ定義⾔語（DDL）関係代数 データベース関係データベース管理システム（RDBMS） テーブル（表）関係データモデル テーブル副問合せ完全外部結合 問合せ（クエリ）交差結合 等結合参照整合性制約 動的SQL⾃⼰結合 内部結合⾃然結合 左外部結合集約関数 ビュー主キー 副問合せ主キー制約 右外部結合スカラ副問合せ ロウ（⾏）
本⽇学習したSQLキーワード
33
ALL DROP TABLE ORDER BYALTER TABLE ... ADD DROP USER PRIMARY KEYALTER TABLE ... CHANGE EXISTS REFERENCESALTER TABLE ... DROP FOREIGN KEY REVOKEALTER TABLE ... MODIFY FULL OUTER JOIN RIGHT OUTER JOINALTER TABLE ... RENAME AS GRANT SELECTANY GROUP BY SHOW COLUMNS FROMAS HAVING SHOW DATABASESASC IN SHOW TABLESavg INNER JOIN SOMEBETWEEN INSERT INTO sumcount JOIN UNIQUECREATE DATABASE LEFT OUTER JOIN UPDATECREATE TABLE LIKE USECREATE VIEW LIMIT WHERECROSS JOIN maxDELETE FROM minDESC NATURAL JOINDISTINCT NOT EXISTSDROP DATABASE NOT NULL
これまでに学習したSQLキーワード
34
ALL DROP TABLE ORDER BYALTER TABLE ... ADD DROP USER PRIMARY KEYALTER TABLE ... CHANGE EXISTS REFERENCESALTER TABLE ... DROP FOREIGN KEY REVOKEALTER TABLE ... MODIFY FULL OUTER JOIN RIGHT OUTER JOINALTER TABLE ... RENAME AS GRANT SELECTANY GROUP BY SHOW COLUMNS FROMAS HAVING SHOW DATABASESASC IN SHOW TABLESavg INNER JOIN SOMEBETWEEN INSERT INTO sumcount JOIN UNIQUECREATE DATABASE LEFT OUTER JOIN UPDATECREATE TABLE LIKE USECREATE VIEW LIMIT WHERECROSS JOIN maxDELETE FROM minDESC NATURAL JOINDISTINCT NOT EXISTSDROP DATABASE NOT NULL
