SAK 図書館
SAK 図書館
MySQL 編17 - テーブルコピー、レコード追加、修正、削除、表切り捨て
■テーブルコピー
・問い合わせ結果で、新たにテーブルを作成することができる。
別テーブルへ移動するには、テーブルコピー後にオリジナルを delete、
新テーブルに整合性規則を追加するか、作成済みテーブルに insert で
select 追加すると良い。(下記のレコード追加も参照のこと。)
(表コピー)
create table workw as
select
*
from testm
where key1 like 'a%'
;
alter table workw type=InnoDB;
select * from workw;
+------+-------+-------+-------+
| key1 | data1 | data2 | data3 |
+------+-------+-------+-------+
| a001 | 1 | 2 | 3 |
| a011 | 1 | 2 | 3 |
+------+-------+-------+-------+
・集合問い合わせ結果で、新たにテーブルを作成することもできる。
create table work2w as
select
*
from testm
where key1 like 'a%'
union all
select
*
from testm
where key1 like 'a%'
;
alter table work2w type=InnoDB;
select * from work2w;
+------+-------+-------+-------+
| key1 | data1 | data2 | data3 |
+------+-------+-------+-------+
| a001 | 1 | 2 | 3 |
| a011 | 1 | 2 | 3 |
| a001 | 1 | 2 | 3 |
| a011 | 1 | 2 | 3 |
+------+-------+-------+-------+
・空のテーブルをコピーしたい場合は、ありえないキーで抽出する。
create table work3w as
select
*
from testm
where key1 = '@'
;
alter table work2w type=InnoDB;
select * from work3w;
Empty set (0.00 sec)
■レコード追加(レコード更新、インサート)
・テーブルにレコードを追加する。
insert into testm (
key1,
data1,
data2,
data3
)
values (
'a001',
100,
200,
300
);
・追加データの並びがテーブル定義と同一であるなら、項目名を省略できる。
insert into testm values (
'a001',
100,
200,
300
);
・default が指定されていれば、Oracle のようにデータ値として、default
と書くことができる。
create table test4m (
key1 char(8),
data1 int8,
data2 int8,
data3 int8 default 123
) type=InnoDB;
insert into test4m values (
'a001',
100,
200,
default
);
select * from test4m;
+------+-------+-------+-------+
| key1 | data1 | data2 | data3 |
+------+-------+-------+-------+
| a001 | 100 | 200 | 123 |
+------+-------+-------+-------+
・入力が必須でない項目は、省略して追加も可能である。
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;
+------+-------+-------+-------+
| key1 | data1 | data2 | data3 |
+------+-------+-------+-------+
| a001 | NULL | 100 | 123 |
+------+-------+-------+-------+
・問い合わせ結果を別テーブルへレコード追加することも可能である。
別テーブルへレコード移動するには、別テーブルへレコード追加後にオリジ
ナルを 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;
■トランザクション
・MySQL のトランザクションは、標準は自動コミットとなっている。
ADO - ODBC では、cn.BeginTrans で手動コミットになる。
JDBC では、cn.setAutoCommit(false); でオートコミットを解除して、
手動コミットにする。
・mysql での対話入力では、次のように begin;、commit; でトランザクショ
ン処理をする。ロールバックは、rollback; である。
begin;
insert into testm values ('a001', 100, 200, 300);
insert into testm values ('a002', 102, 200, 300);
insert into testm values ('a003', 103, 200, 300);
commit;
begin;
insert into testm values ('z001', 100, 200, 300);
insert into testm values ('z002', 102, 200, 300);
insert into testm values ('z003', 103, 200, 300);
rollback;
■排他制御(レコードロック)
・レコード追加、修正、削除では、レコードレベルの排他制御が働く。
デッドロック回避の考え方は、他のファイルシンテムと同様である。
テーブルロック(表ロック) として lock table もある。
・問い合わせで行ロックする方法として、for update 句がある。
for update nowait は、ロック解除を待たずにエラーを返す。
次の例は、ロックされていたら待って問い合わせる。
select * from testm where key1 = 'a001'
for update
;
・nowait はエラー構文となった。
select * from testm where key1 = 'a001'
for update nowait
;
■表切り捨て
・truncate は、表を切り捨ててクリアする。
(テーブル切り捨て、テーブルクリア、表クリア、テーブル内データ消去)
truncate table testm;
■ストアドファンクション
・create function はあるが、C 言語か何かで関数を用意する必要があるよう
に読める。Oracle の PL/SQL や PostgreSQL の PL/pgSQL のようなスクリ
プト言語は持っていない。
たぶん、トリガーも使えないと思われる。
■MySQL 編資料
■PostgreSQL 編、JAVA Servlet、JSP 編資料
■SQL 基礎編資料
■SQL 基礎実地編資料
■SQL チューニング編資料
■Oracle PL/SQL 編資料
Copyright (C) 1999-2018 Y.SAK