勉強日記

チラ裏

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 リファレンスマニュアル

dev.mysql.com


正規化されたデータベースを設計する。正規化されたデータベースを作成する

正規化(復習)

wand-ta.hatenablog.com

↑の復習

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
勝海舟 コンピュータアーキテクチャ原理
坂本龍馬 リレーショナルモデル
坂本龍馬 コンピュータアーキテクチャ原理
  • リレーションが表現する事実はこうなる:
    • 桂小五郎は、「ある学科」の、リレーショナルモデルを受講する」
    • 桂小五郎は、「ある学科」の、Javaプログラミングを受講する」
    • 勝海舟は、「ある学科」の、リレーショナルモデルを受講する」
    • 勝海舟は、「ある学科」の、RoRを受講する」
    • 勝海舟は、「ある学科」の、コンピュータアーキテクチャ原理を受講する」
    • 勝海舟は、「ある学科」の、リレーショナルモデルを受講する」
    • 坂本龍馬は、「ある学科」の、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のうち、非キー属性が高々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
      • それ以上分解すると関数従属性が崩れ、無損失分解にならない
  • 常識的に考えて、入荷先によって入荷可能な品物に制限があるはず

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

正規化されたデータベースを作成する

dev.mysql.com

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省略可能

ddl.sql

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のインデックスが効いている
  • ほかが効かないのは件数が少ないから??

データ整合性を強制する表の制約について説明する。このような制約を作成する

dev.mysql.com

無効データの制約

  • 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

dev.mysql.com

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データベース・オブジェクトのメタデータを取得するさまざまな方法を識別し、使用する

dev.mysql.com

INFORMATION_SCHEMAテーブル

データベース横断的なメタデータを格納する

  • 例: information_schema.tables
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)