MySQL 5.6 Developer試験対策 2 MySQLのスキーマ・オブジェクトの設計と作成
MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」
https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882education.oracle.com
MySQL 5.6 リファレンスマニュアル
- 正規化されたデータベースを設計する。正規化されたデータベースを作成する
- 適切なデータ型と索引を使用して表を作成する。適切なデータ型と索引を使って表を修正する
- データ整合性を強制する表の制約について説明する。このような制約を作成する
- ビューの作成と修正
- MySQLデータベース・オブジェクトのメタデータを取得するさまざまな方法を識別し、使用する
正規化されたデータベースを設計する。正規化されたデータベースを作成する
正規化(復習)
↑の復習
1NF
- 表をリレーションとして使えること
2NF
- 部分関数従属がない
3NF
- 推移的関数従属がない
BCNF
- 非キー属性からキー属性(真部分集合)への関数従属がない
2NF ~ BCNFまとめ
fromからtoへの関数従属をなくす作業
- 語彙
- キー属性: 着目している候補キーの真部分集合
- 非キー属性: 着目している候補キーに含まれない属性
from \ to | キー属性 | 非キー属性 |
---|---|---|
キー属性 | 候補キーの既約化 | 2N |
非キー属性 | BCN | 3N |
- キー属性から別のキー属性への関数従属がある場合
- その候補キーは既約でない (スーパーキー)
- 候補キーを選び直そう
- キー属性から非キー属性への関数従属がある場合
- = 部分関数従属
- これを無くすのが第2正規化
- 非キー属性から非キー属性への関数従属がある場合
- = 推移的関数従属
- これを無くすのが第3正規化
- 非キー属性からキー属性への関数従属がある場合
- 候補キーを選び直してボイス・コッド正規化する
- 分割のしかたをしくじると無損失分解にならないので注意する
- 候補キーを選び直してボイス・コッド正規化する
結合従属性
A, B, ..., CをリレーションRの見出しの部分集合であるとする。 もしA, B, ..., Cの射影に対応するリレーションを結合した結果と、 Rが同じ場合かつその場合に限り、Rは次の結合従属性を満たすこととする ☆{A, B, ..., C}
- 要するに、「結合して元に戻ること」
- 言い換えると、「無損失分解が可能である」こと
- 自明な結合従属性
- A,B, ..., Cのいずれかの射影がR自身
- リレーションRと、その任意の射影との結合はRになる
- 暗黙的な結合従属性
- A,B, ..., CがすべてRのスーパーキー
4NF
- 多値従属のない形
- 多値従属とは、結合従属の特殊なもの
A, B, CをリレーションRの見出しの部分集合であるとする。 A, B, Cが次の結合従属性を満たす場合かつその場合に限り、 BおよびCはAに多値従属すると言う。 ☆{AB, AC} 次のような記号を用いて表現する。 A →→ B A →→ C
例
氏名(RK) | 学科(RK) | 授業(RK) |
---|---|---|
桂小五郎 | コンピュータアーキテクチャ | リレーショナルモデル |
桂小五郎 | コンピュータアーキテクチャ | Javaプログラミング |
勝海舟 | コンパイラ | リレーショナルモデル |
勝海舟 | コンパイラ | RoR |
勝海舟 | コンパイラ | コンピュータアーキテクチャ原理 |
坂本龍馬 | データベース | リレーショナルモデル |
坂本龍馬 | データベース | コンピュータアーキテクチャ原理 |
坂本龍馬 | コンパイラ | リレーショナルモデル |
坂本龍馬 | コンパイラ | コンピュータアーキテクチャ原理 |
- 分解する
学科所属
氏名(RK) | 学科(RK) |
---|---|
桂小五郎 | コンピュータアーキテクチャ |
勝海舟 | コンパイラ |
坂本龍馬 | データベース |
坂本龍馬 | コンパイラ |
- リレーションが表現する事実はこうなる:
授業所属
氏名(RK) | 授業(RK) |
---|---|
桂小五郎 | リレーショナルモデル |
桂小五郎 | Javaプログラミング |
勝海舟 | リレーショナルモデル |
勝海舟 | RoR |
勝海舟 | コンピュータアーキテクチャ原理 |
坂本龍馬 | リレーショナルモデル |
坂本龍馬 | コンピュータアーキテクチャ原理 |
- リレーションが表現する事実はこうなる:
- 分解の仕方によっては、結合しても元に戻らないことに注意する
- 今回の分解は大丈夫
5NF
- 暗黙的でないすべての結合従属性が取り除かれた形
氏名(RK) | 学科(RK) | 授業(RK) |
---|---|---|
勝海舟 | コンパイラ | C++ |
勝海舟 | コンパイラ | Java |
坂本龍馬 | コンパイラ | C++ |
坂本龍馬 | コンパイラ | RoR |
坂本龍馬 | データベース | RoR |
坂本龍馬 | データベース | リレーショナルモデル |
桂小五郎 | コンパイラ | Java |
桂小五郎 | コンピュータアーキテクチャ | Java |
桂小五郎 | コンピュータアーキテクチャ | OS |
桂小五郎 | コンピュータアーキテクチャ | コンピュータアーキテクチャ原理 |
- 分解
氏名(RK) | 学科(RK) |
---|---|
勝海舟 | コンパイラ |
坂本龍馬 | コンパイラ |
坂本龍馬 | データベース |
桂小五郎 | コンパイラ |
桂小五郎 | コンピュータアーキテクチャ |
氏名(RK) | 授業(RK) |
---|---|
勝海舟 | C++ |
勝海舟 | Java |
坂本龍馬 | C++ |
坂本龍馬 | RoR |
坂本龍馬 | リレーショナルモデル |
桂小五郎 | Java |
桂小五郎 | OS |
桂小五郎 | コンピュータアーキテクチャ原理 |
学科(RK) | 授業(RK) |
---|---|
コンパイラ | C++ |
コンパイラ | Java |
コンパイラ | RoR |
データベース | RoR |
データベース | リレーショナルモデル |
コンピュータアーキテクチャ | Java |
コンピュータアーキテクチャ | OS |
コンピュータアーキテクチャ | コンピュータアーキテクチャ原理 |
- 学科授業リレーションにより、学科ごとの受講可能な授業が制限される
- これにがないと、結合後に不当なタプルが増えてしまう
- 桂小五郎氏に関わるタプルのみ見てみる
元
氏名(RK) | 学科(RK) | 授業(RK) |
---|---|---|
桂小五郎 | コンパイラ | Java |
桂小五郎 | コンピュータアーキテクチャ | Java |
桂小五郎 | コンピュータアーキテクチャ | OS |
桂小五郎 | コンピュータアーキテクチャ | コンピュータアーキテクチャ原理 |
分割
氏名(RK) | 学科(RK) |
---|---|
桂小五郎 | コンパイラ |
桂小五郎 | コンピュータアーキテクチャ |
氏名(RK) | 授業(RK) |
---|---|
桂小五郎 | Java |
桂小五郎 | OS |
桂小五郎 | コンピュータアーキテクチャ原理 |
- ここまでだけだと無損失分解にならない
- 結合すると余計なタプルが現れる
氏名(RK) | 学科(RK) | 授業(RK) |
---|---|---|
桂小五郎 | コンパイラ | Java |
桂小五郎 | コンパイラ | OS |
桂小五郎 | コンパイラ | コンピュータアーキテクチャ原理 |
桂小五郎 | コンピュータアーキテクチャ | Java |
桂小五郎 | コンピュータアーキテクチャ | OS |
桂小五郎 | コンピュータアーキテクチャ | コンピュータアーキテクチャ原理 |
- 学科授業リレーションによる制限を加え、結合することで、余計なタプルが偽になり結合結果に含まれなくなる
- = 無損失分解になる
- このような、結果セットの行を絞り込むためだけの結合を準結合(semi-join)という
学科(RK) | 授業(RK) |
---|---|
コンパイラ | C++ |
コンパイラ | Java |
コンパイラ | RoR |
コンピュータアーキテクチャ | Java |
コンピュータアーキテクチャ | OS |
コンピュータアーキテクチャ | コンピュータアーキテクチャ原理 |
6NF
- 暗黙的な結合従属制含め、自明な結合従属性以外をすべて排除したもの
- 例
- 5NFなリレーションRがあり、見出しが
A,B,C,D
だとする - Aが候補キー。
A -> B,C,D
- このとき、暗黙的な結合従属性
☆{AB,AC,AD}
が成り立つ
- 5NFなリレーションRがあり、見出しが
- 要するに、5NFのうち、非キー属性が高々1つになるものが6NF
正規化サンプル
仕入先 | 住所 | 代表者名 | 代表先連絡先 | 商品名 | 1個あたりの金額 | 入荷日 | 入荷数 | 備考 |
---|---|---|---|---|---|---|---|---|
扱いづらいので英名にする
supplier | address | representative | representative_tel | item_name | unit_price | stock_quantity | supplied_at | supplied_quantity | memo |
---|---|---|---|---|---|---|---|---|---|
津軽ファーム | 青森県弘前市 | 陸奥太郎 | 111-1111 | りんご | 150 | 10 | 2012-07-01 | 10 | 青森産 |
!! | !! | !! | !! | ぶどう | 300 | 10 | 2012-07-01 | 10 | |
!! | !! | !! | !! | りんご | 150 | 30 | 2012-07-03 | 20 | 青森産 |
みかん園 | 愛媛県松山市 | 松山花子 | 222-2222 | みかん | 80 | 30 | 2012-07-01 | 30 |
(!!
は上のセルと結合していることを表す)
1NF
supplier | address | representative | representative_tel | item_name | unit_price | supplied_at | supplied_quantity | memo |
---|---|---|---|---|---|---|---|---|
津軽ファーム | 青森県弘前市 | 陸奥太郎 | 111-1111 | りんご | 150 | 2012-07-01 | 10 | 青森産 |
津軽ファーム | 青森県弘前市 | 陸奥太郎 | 111-1111 | ぶどう | 300 | 2012-07-01 | 10 | |
津軽ファーム | 青森県弘前市 | 陸奥太郎 | 111-1111 | りんご | 150 | 2012-07-03 | 20 | 青森産 |
みかん園 | 愛媛県松山市 | 松山花子 | 222-2222 | みかん | 80 | 2012-07-01 | 30 |
- 本当はNULLを含むと1NFじゃないけど誰もそんなこと気にしないので一旦置いておく
- 導出可能な属性を廃する
- 在庫 = 入荷 - 出荷
2NF
- 第2正規化以上を行うには、候補キーを考える必要がある
- 候補キー:
supplier,item_name,supplied_at
- 他にもあるかもしれないけれど、とりあえずこれ
- 部分関数従属を見つける
supplier -> address, representative, representative_tel
item_name
->unit_price, memo
supplier, item_name, suppplied_at -> supplied_quantity
suppliers
supplier | address | representative | representative_tel |
---|---|---|---|
津軽ファーム | 青森県弘前市 | 陸奥太郎 | 111-1111 |
みかん園 | 愛媛県松山市 | 松山花子 | 222-2222 |
items
item_name | unit_price | item_memo |
---|---|---|
りんご | 150 | 青森産 |
ぶどう | 300 | |
みかん | 80 |
supply_quantities
supplier | item_name | supplied_at | supplied_quantity |
---|---|---|---|
津軽ファーム | りんご | 2012-07-01 | 10 |
津軽ファーム | ぶどう | 2012-07-01 | 10 |
津軽ファーム | りんご | 2012-07-03 | 20 |
みかん園 | みかん | 2012-07-01 | 30 |
NULLを排除して、より「正しい」正規化を推し進めるにはこうする:
items
item_name | unit_price |
---|---|
りんご | 150 |
ぶどう | 300 |
みかん | 80 |
item_memos
item_name | item_memo |
---|---|
りんご | 青森産 |
結合して元に戻すにはLEFT OUTER JOIN
を使う
3NF
- (代表者指名の同姓同名は置いておいて)代表者から代表者連絡先を導出可能なはず
- 推移的関数従属
supplier -> representative -> representative_tel
suppliers
supplier | address | representative |
---|---|---|
津軽ファーム | 青森県弘前市 | 陸奥太郎 |
みかん園 | 愛媛県松山市 | 松山花子 |
representatives
representative | representative_tel |
---|---|
陸奥太郎 | 111-1111 |
松山花子 | 222-2222 |
BCNF
- 非キー属性からキー属性への関数従属は既に無さそう
4,5NF
supplies
supplier | item_name | supplied_at |
---|---|---|
津軽ファーム | りんご | 2012-07-01 |
津軽ファーム | ぶどう | 2012-07-01 |
津軽ファーム | りんご | 2012-07-03 |
みかん園 | みかん | 2012-07-01 |
- こういうテーブルをまず考える必要がある
- 非キー属性がある3NFはその時点で5NF
- それ以上分解すると関数従属性が崩れ、無損失分解にならない
- 非キー属性がある3NFはその時点で5NF
- 常識的に考えて、入荷先によって入荷可能な品物に制限があるはず
supplier_items
supplier | item_name |
---|---|
津軽ファーム | りんご |
津軽ファーム | ぶどう |
みかん園 | みかん |
- この表へのFK制約を付けることで、「津軽ファームからみかんを入荷する」といったanomalyを未然に防げる
- 【所感】多対多を連関エンティティでほぐすのは第4/5正規化だったんだな…って
6NF
suppliers
supplier | address | representative |
---|---|---|
津軽ファーム | 青森県弘前市 | 陸奥太郎 |
みかん園 | 愛媛県松山市 | 松山花子 |
- これの暗黙的な結合従属性をすべて排する
supplier_addresses
supplier | address |
---|---|
津軽ファーム | 青森県弘前市 |
みかん園 | 愛媛県松山市 |
supplier_representatives
supplier | representative |
---|---|
津軽ファーム | 陸奥太郎 |
みかん園 | 松山花子 |
普通ここまでやらない
5NFまでまとめ
representatives
representative | representative_tel |
---|---|
陸奥太郎 | 111-1111 |
松山花子 | 222-2222 |
suppliers
supplier | address | representative |
---|---|---|
津軽ファーム | 青森県弘前市 | 陸奥太郎 |
みかん園 | 愛媛県松山市 | 松山花子 |
items
item_name | unit_price |
---|---|
りんご | 150 |
ぶどう | 300 |
みかん | 80 |
item_memos
item_name | item_memo |
---|---|
りんご | 青森産 |
supplier_items
supplier | item_name |
---|---|
津軽ファーム | りんご |
津軽ファーム | ぶどう |
みかん園 | みかん |
supply_quantities
supplier | item_name | supplied_at | supplied_quantity |
---|---|---|---|
津軽ファーム | りんご | 2012-07-01 | 10 |
津軽ファーム | ぶどう | 2012-07-01 | 10 |
津軽ファーム | りんご | 2012-07-03 | 20 |
みかん園 | みかん | 2012-07-01 | 30 |
正規化されたデータベースを作成する
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [partition_options] CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [partition_options] select_statement ...
CREATE TABLE tbl_name select_statement
... create_definition
省略可能
DROP DATABASE sample; CREATE DATABASE sample; USE sample; CREATE TABLE representatives ( representative varchar(20) PRIMARY KEY, representative_tel varchar(15) NOT NULL ); CREATE TABLE suppliers ( supplier varchar(20) PRIMARY KEY, address varchar(191) NOT NULL, representative varchar(20) NOT NULL, CONSTRAINT fk_srrr FOREIGN KEY (representative) REFERENCES representatives (representative) ); CREATE TABLE items ( item_name varchar(20) PRIMARY KEY, unit_price int NOT NULL ); CREATE TABLE item_memos ( item_name varchar(20) PRIMARY KEY, item_memo varchar(191) NOT NULL, CONSTRAINT fk_iminiin FOREIGN KEY (item_name) REFERENCES items (item_name) ); CREATE TABLE supplier_items ( supplier varchar(20), item_name varchar(20), PRIMARY KEY (supplier, item_name), CONSTRAINT fk_sisss FOREIGN KEY (supplier) REFERENCES suppliers (supplier), CONSTRAINT fk_siiniin FOREIGN KEY (item_name) REFERENCES items (item_name) ); CREATE TABLE supply_quantities ( supplier varchar(20), item_name varchar(20), supplied_at date, supplied_quantity int NOT NULL, PRIMARY KEY (item_name, supplier, supplied_at), CONSTRAINT fk_mqsss FOREIGN KEY (supplier) REFERENCES suppliers (supplier), CONSTRAINT fk_mqiniin FOREIGN KEY (item_name) REFERENCES items (item_name), CONSTRAINT fk_mqsinssiin FOREIGN KEY (supplier,item_name) REFERENCES supplier_items (supplier,item_name) ); SHOW TABLES;
Tables_in_sample item_memos items representatives supplier_items suppliers supply_quantities
データ投入
USE sample; INSERT INTO representatives (representative,representative_tel) VALUES ("陸奥太郎", "111-1111"), ("松山花子","222-2222"); INSERT INTO suppliers (supplier,address,representative) VALUES ("津軽ファーム","青森県弘前市","陸奥太郎"), ("みかん園","愛媛県松山市","松山花子"); INSERT INTO items (item_name,unit_price) VALUES ("りんご",150), ("ぶどう",300), ("みかん",80); INSERT INTO item_memos (item_name,item_memo) VALUES ("りんご","青森産"); INSERT INTO supplier_items (supplier,item_name) VALUES ("津軽ファーム","りんご"), ("津軽ファーム","ぶどう"), ("みかん園","みかん"); INSERT INTO supply_quantities (supplier,item_name,supplied_at,supplied_quantity) VALUES ("津軽ファーム","りんご","2012-07-01",10), ("津軽ファーム","ぶどう","2012-07-01",10), ("津軽ファーム","りんご","2012-07-03",20), ("みかん園","みかん","2012-07-01",30);
結合
SELECT supplier, address, representative, representative_tel, item_name, unit_price, supplied_at, supplied_quantity, item_memo as memo FROM supply_quantities INNER JOIN supplier_items USING (supplier,item_name) INNER JOIN suppliers USING (supplier) INNER JOIN items USING (item_name) INNER JOIN representatives USING (representative) LEFT OUTER JOIN item_memos using (item_name);
+--------------------+--------------------+----------------+--------------------+-----------+------------+-------------+-------------------+-----------+ | supplier | address | representative | representative_tel | item_name | unit_price | supplied_at | supplied_quantity | memo | +--------------------+--------------------+----------------+--------------------+-----------+------------+-------------+-------------------+-----------+ | 津軽ファーム | 青森県弘前市 | 陸奥太郎 | 111-1111 | りんご | 150 | 2012-07-01 | 10 | 青森産 | | 津軽ファーム | 青森県弘前市 | 陸奥太郎 | 111-1111 | りんご | 150 | 2012-07-03 | 20 | 青森産 | | 津軽ファーム | 青森県弘前市 | 陸奥太郎 | 111-1111 | ぶどう | 300 | 2012-07-01 | 10 | NULL | | みかん園 | 愛媛県松山市 | 松山花子 | 222-2222 | みかん | 80 | 2012-07-01 | 30 | NULL | +--------------------+--------------------+----------------+--------------------+-----------+------------+-------------+-------------------+-----------+ 4 rows in set (0.00 sec)
適切なデータ型と索引を使用して表を作成する。適切なデータ型と索引を使って表を修正する
EXPLAIN
+----+-------------+-------------------+--------+--------------------+---------+---------+-----------------------------------------------------------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------------+--------+--------------------+---------+---------+-----------------------------------------------------------+------+----------------------------------------------------+ | 1 | SIMPLE | suppliers | ALL | PRIMARY,fk_srrr | NULL | NULL | NULL | 2 | NULL | | 1 | SIMPLE | supplier_items | ref | PRIMARY,fk_siiniin | PRIMARY | 22 | sample.suppliers.supplier | 1 | Using index | | 1 | SIMPLE | supply_quantities | ref | PRIMARY,fk_mqsss | PRIMARY | 44 | sample.supplier_items.item_name,sample.suppliers.supplier | 1 | NULL | | 1 | SIMPLE | item_memos | ALL | PRIMARY | NULL | NULL | NULL | 1 | Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | representatives | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | items | eq_ref | PRIMARY | PRIMARY | 22 | sample.supplier_items.item_name | 1 | NULL | +----+-------------+-------------------+--------+--------------------+---------+---------+-----------------------------------------------------------+------+----------------------------------------------------+ 6 rows in set (0.00 sec)
| 1 | SIMPLE | supplier_items | ref | PRIMARY,fk_siiniin | PRIMARY | 22 | sample.suppliers.supplier | 1 | Using index |
- PKとFKのインデックスが効いている
- ほかが効かないのは件数が少ないから??
データ整合性を強制する表の制約について説明する。このような制約を作成する
無効データの制約
sql_mode
の設定でゼロ日付などを弾ける
ENUMおよびSETの制約
Strict SQL Modeを設定するが吉
SET sql_mode="STRICT_ALL_TABLES";
ENUM
CREATE TABLE tbl (col ENUM('one','two')); Query OK, 0 rows affected (0.02 sec)
- 正常データ投入
INSERT INTO tbl VALUES('one'),('two'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0
- 不正データ投入
INSERT INTO tbl VALUES('three'); Query OK, 1 row affected, 1 warning (0.01 sec)
OKじゃねぇ
SELECT * FROM tbl; +------+ | col | +------+ | one | | two | | | +------+ 3 rows in set (0.00 sec)
- Strict SQL Modeだとちゃんと弾かれる
INSERT INTO tbl VALUES('three'); ERROR 1265 (01000): Data truncated for column 'col' at row 1
SET
CREATE TABLE tbl (col SET('one','two')); Query OK, 0 rows affected (0.01 sec)
- 正常なデータ投入
INSERT INTO tbl VALUES (''),('one'),('two'),('one,two'),('two,one'); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0
カラム自体が集合になる
- 不正なデータ投入
INSERT INTO tbl VALUES ('hoge'); Query OK, 1 row affected, 1 warning (0.00 sec)
OKじゃねぇ
SELECT * FROM tbl; +---------+ | col | +---------+ | | | one | | two | | one,two | | one,two | | | +---------+ 6 rows in set (0.00 sec)
- Strict SQL Modeだとちゃんと弾かれる
INSERT INTO tbl VALUES ('hoge'); ERROR 1265 (01000): Data truncated for column 'col' at row 1
ビューの作成と修正
USE sample; CREATE VIEW report AS SELECT supplier, address, representative, representative_tel, item_name, unit_price, supplied_at, supplied_quantity, item_memo as memo FROM supply_quantities INNER JOIN supplier_items USING (supplier,item_name) INNER JOIN suppliers USING (supplier) INNER JOIN items USING (item_name) INNER JOIN representatives USING (representative) LEFT OUTER JOIN item_memos using (item_name); SELECT * FROM report;
SELECT * FROM report ; +--------------------+--------------------+----------------+--------------------+-----------+------------+-------------+-------------------+-----------+ | supplier | address | representative | representative_tel | item_name | unit_price | supplied_at | supplied_quantity | memo | +--------------------+--------------------+----------------+--------------------+-----------+------------+-------------+-------------------+-----------+ | 津軽ファーム | 青森県弘前市 | 陸奥太郎 | 111-1111 | りんご | 150 | 2012-07-01 | 10 | 青森産 | | 津軽ファーム | 青森県弘前市 | 陸奥太郎 | 111-1111 | りんご | 150 | 2012-07-03 | 20 | 青森産 | | 津軽ファーム | 青森県弘前市 | 陸奥太郎 | 111-1111 | ぶどう | 300 | 2012-07-01 | 10 | NULL | | みかん園 | 愛媛県松山市 | 松山花子 | 222-2222 | みかん | 80 | 2012-07-01 | 30 | NULL | +--------------------+--------------------+----------------+--------------------+-----------+------------+-------------+-------------------+-----------+ 4 rows in set (0.00 sec)
- 修正:
ALTER VIEW
またはCREATE VIEW OR REPLACE
- 実データが格納されていないので同じこと
\h ALTER VIEW Name: 'ALTER VIEW' Description: Syntax: ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = user] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] This statement changes the definition of a view, which must exist. The syntax is similar to that for CREATE VIEW see [HELP CREATE VIEW]). This statement requires the CREATE VIEW and DROP privileges for the view, and some privilege for each column referred to in the SELECT statement. ALTER VIEW is permitted only to the definer or users with the SUPER privilege. URL: https://dev.mysql.com/doc/refman/5.6/en/alter-view.html
requires the CREATE VIEW and DROP privileges
- cf.
ALTER TABLE
は実データが入っているのでCREATE, INSERT、リネーム時はDROP権限が必要
o To use ALTER TABLE, you need ALTER, CREATE, and INSERT privileges for the table. Renaming a table requires ALTER and DROP on the old table, ALTER, CREATE, and INSERT on the new table.
- ALTER VIEWにRENAMEはない
MySQLデータベース・オブジェクトのメタデータを取得するさまざまな方法を識別し、使用する
INFORMATION_SCHEMA
テーブル
データベース横断的なメタデータを格納する
- 例:
information_schema.tables
- 全データベース(全Schema)中の全table・viewのメタデータ
DESC information_schema.tables; +-----------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+---------------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | NO | | | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | TABLE_TYPE | varchar(64) | NO | | | | | ENGINE | varchar(64) | YES | | NULL | | | VERSION | bigint(21) unsigned | YES | | NULL | | | ROW_FORMAT | varchar(10) | YES | | NULL | | | TABLE_ROWS | bigint(21) unsigned | YES | | NULL | | | AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | | | DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | | | DATA_FREE | bigint(21) unsigned | YES | | NULL | | | AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | | | CREATE_TIME | datetime | YES | | NULL | | | UPDATE_TIME | datetime | YES | | NULL | | | CHECK_TIME | datetime | YES | | NULL | | | TABLE_COLLATION | varchar(32) | YES | | NULL | | | CHECKSUM | bigint(21) unsigned | YES | | NULL | | | CREATE_OPTIONS | varchar(255) | YES | | NULL | | | TABLE_COMMENT | varchar(2048) | NO | | | | +-----------------+---------------------+------+-----+---------+-------+ 21 rows in set (0.00 sec)
SELECT table_name, table_type, engine FROM information_schema.tables WHERE table_schema = 'sample' ORDER BY table_name;
+-------------------+------------+--------+ | table_name | table_type | engine | +-------------------+------------+--------+ | items | BASE TABLE | InnoDB | | item_memos | BASE TABLE | InnoDB | | report | VIEW | NULL | | representatives | BASE TABLE | InnoDB | | suppliers | BASE TABLE | InnoDB | | supplier_items | BASE TABLE | InnoDB | | supply_quantities | BASE TABLE | InnoDB | +-------------------+------------+--------+ 7 rows in set (0.00 sec)