みんなの為のMySQLまとめ（１４）

参考
http://homepage2.nifty.com/sak/w_sak3/doc/sysbrd/mysql_17.htm

■テーブルコピー

・問い合わせ結果で、新たにテーブルを作成することができる。
別テーブルへ移動するには、テーブルコピー後にオリジナルを delete、
新テーブルに整合性規則を追加するか、作成済みテーブルに insert で
select 追加すると良い。(下記のレコード追加も参照のこと。)
(表コピー)

create table workw as
select
*
from testm
where key1 like 'a%'
;

select * from workw;

・集合問い合わせ結果で、新たにテーブルを作成することもできる。

create table work2w as
select
*
from testm
where key1 like 'a%'
union all
select
*
from testm
where key1 like 'a%'
;

select * from work2w;

・空のテーブルをコピーしたい場合は、ありえないキーで抽出する。

create table work3w as
select
*
from testm
where key1 = '@';

select * from work3w;

■レコード追加(レコード更新、インサート)

・テーブルにレコードを追加する。

insert into testm (
key1,
data1,
data2,
data3
)
values (
'a001',
100,
200,
300
);

・追加データの並びがテーブル定義と同一であるなら、項目名を省略できる。

nsert into testm values (
'a001',
100,
200,
300
);

・default が指定されていれば、Oracle のようにデータ値として、default
と書くことができる。

create table test4m (
key1 char(8),
data1 int8,
data2 int8,
data3 int8 default 123
);

insert into test4m values (
'a001',
100,
200,
default
);

select * from test4m;

・入力が必須でない項目は、省略して追加も可能である。
default 指定や not null 制約がない場合、NULL が割り当てられる。

create table test5m (
key1 char(8),
data1 int8,
data2 int8,
data3 int8 default 123
) type=InnoDB;

-- この形式は MySQL ではエラーになるので注意 --

insert into test5m values (
'a001',
100
);

-- データを省略する場合は、フィールド名の指定が必要 --

insert into test5m (key1, data2) values (
'a001',
100
);

select * from test5m;

・問い合わせ結果を別テーブルへレコード追加することも可能である。
別テーブルへレコード移動するには、別テーブルへレコード追加後にオリジ
ナルを delete すると良い。

insert into workw
select
key1,
data1,
data2,
data3
from testm where key1 like 'a%';

・特定の項目を固定値にしたりもできる。

insert into workw
select
key1,
data1,
data2,
111
from testm where key1 like 'a%';

・特定の項目だけをセットすることもできる。

insert into workw (key1, data2)
select
key1,
data2
from testm where key1 like 'a%'
;

・NULL を項目にセットすることもできる。
もちろん、not null 制約があれば、NULL をセットすることはできない。

insert into testm (
key1,
data1,
data2,
data3
)
values (
'a001',
100,
200,
NULL
);

・データ中にシングルコーテーション「'」を含める場合は、「''」と 2 個
続けて書くと 1 個の「'」が文字として入る。

insert into testm (
key1,
data1,
data2,
data3
)
values (
'a0''1',
100,
200,
300
);

■レコード修正(レコード更新、アップデート)

・テーブルのレコードを修正する。
where 句には select と同様に複雑な指定が可能である。

update testm set
data1 = 111,
data2 = 222,
data3 = 333
where key1 = 'a001'
;

・MySQL では、insert は default で追加することができるが、
update では指定のしようがなかった。

update test4m set
data1 = 111,
data2 = 222,
data3 = default -- エラーになる
where key1 = 'a001'
;

・一項目だけの修正も可能である。

update testm set
data3 = 333
where key1 = 'a001'
;

・NULL を項目にセットすることもできる。
もちろん、not null 制約があれば、NULL をセットすることはできない。

update testm set
data1 = 111,
data2 = 222,
data3 = NULL
where key1 = 'a001'
;

■レコード削除(レコード更新、デリート)

・テーブルのレコードを削除する。
where 句には select と同様に複雑な指定が可能である。

delete from testm
where key1 = 'a001';

・この例では、testm の key1 の先頭が「a」で始まるレコードを全て削除して
いる。

delete from testm
where key1 like 'a%';

・この例では、testm の全レコードを削除している。
件数が多いとレスポンスに問題がある。
テーブルを空にするのが目的なら、表切り捨て truncate を使う方が高速で
現実的である。

delete from testm;