別のテーブルから新しいテーブルを作成

広告

作成済みのテーブルから新しいテーブルを作成する方法を確認します。書式は次の通りです。 

CREATE TABLE new_tbl 
  SELECT col_name1, col_name2, ... FROM orig_tbl;

元となるテーブルのカラム情報や格納されているデータを使い、新しいテーブルを作成します。いくつかのパターンで実際に試してみてどのようにテーブルが作成されるのかを確認してみます。

なお元となるテーブルは次のようなものを用意しました。 

mysql> create table allproducts(
    -> id int not null primary key, cate int, name varchar(10));

作成したテーブルには次のデータを追加してあります。

p15-1

元のテーブルのカラムだけを使ってテーブルを作成

まずは元となるテーブルのカラムだけを使ってテーブルを作成してみます。 

mysql> create table products_back select * from allproducts;

p15-2

元のテーブルに含まれていた「id」「cate」「name」カラムを持つ新しいテーブルを作成します。また元のテーブルのデータに含まれていたデータが新しいテーブルに格納されます。

では作成したproducts_backテーブルのデータを取得してみます。

p15-3

テーブルが新しく作成されると同時にデータも追加されていることが確認できました。

次に作成したテーブル名と同じ構造のテーブルを作成するのに必要なCREATE TABLE文を表示してみます。

p15-4

カラム名だけでなくカラム毎のデータ型なども引き継いでいることが確認できますが、1点注意することがあります。それはインデックスに関する設定は引き継がれないと言う事です。元のテーブルでは「id」カラムに対してプライマリーキーが設定されていましたが、新しいテーブルでは設定されていません。ユニークキーやインデックスであっても同様です。

*** *** ***

また元のテーブルの特定のカラムだけを指定してテーブルを作成することもできます。 

mysql> create table productname select id, name from allproducts;

p15-5

元のテーブルに含まれていたカラムの中の「id」「name」カラムを持つ新しいテーブルを作成します。また元のテーブルのデータに含まれていたデータが新しいテーブルに格納されます。

*** *** ***

また元のテーブルにWHERE句を指定することでの特定のデータだけを新しいデータに追加することができます。 

mysql> create table drinkproductname
    -> select id, name from allproducts where cate = 1;

p15-6

元のテーブルに格納されていたデータの中で「cate」カラムの値が1のデータだけを新しいテーブルに追加しています。

元のテーブルのカラムに新しいカラムを追加してテーブルを作成

元となるテーブルのカラムに加えて新しいカラムを追加してテーブルを作成してみます。 

mysql> create table pricetable(price int) select * from allproducts;

p15-7

元のテーブルに含まれていた「id」「cate」「name」カラムに加えて追加で「price」カラムを持つ新しいテーブルを作成します。また元のテーブルのデータに含まれていたデータが新しいテーブルに格納されます。

では作成したpricetableテーブルのデータを取得してみます。

p15-8

テーブルが新しく作成されると同時にデータも追加されていることが確認できました。この時、追加されたデータには新しく追加した「price」カラムの値は含まれていませんので、各データには「price」カラムに指定されているデフォルトの値が格納されます。

また新しく作成されたテーブルは、新しく追加したカラムがまず先に並び、その後に元のテーブルに含まれていたカラムが続きます。

元のテーブルのカラムに追加の属性を設定

元のテーブルのカラムに対して、新しい属性を設定したり変更することができます。 

mysql> create table pricetable2(id int primary key, price int)
    -> select * from allproducts;

p15-9

元のテーブルに含まれていた「id」「cate」「name」カラムに加えて追加で「price」カラムを持つ新しいテーブルを作成している点は先ほどと同じですが、今回は元のテーブルにも含まれている「id」カラムに対して新しいテーブルの方でも定義を行っています。この場合、「id」カラムは新しく作られるわけではなく「id」カラムに対して別の属性を設定しています。

今回の場合「id」カラムにプライマリーキー制約を設定しています。最初に記載したとおり、他のテーブルから新しいテーブルを作成する時にはインデックスに関する情報は引き継がれませんが新しいテーブルの方で新しく設定することができます。

では作成したテーブル名と同じ構造のテーブルを作成するのに必要なCREATE TABLE文を表示してみます。

p15-10

「id」カラムに対してプライマリーキーが設定されていることが確認できます。

なお新しく作成されたテーブルのカラムの並びは、新しいテーブル側でのみ記述がある「price」カラムがまず並び、その後で元テーブルに含まれているカラムが並ぶ形になります。

新しく作成されたテーブルのデータを取得してみます。

p15-11

新しい属性を設定したカラムについても値はそのままでデータが追加されています。

今回はプライマリーキーの設定だけでしたが、新しいデータ型を設定したりすることもできます。その場合、元の値を新しいデータ型に変換して格納されるようです。

( Written by Tatsuo Ikura )