SAK 図書館
SAK 図書館
SQL 基礎実地編 (その九) テーブルコピー、レコード追加、修正、削除
基本的に Oracle、SQL-Server、MDB で共通です。
■テーブルコピー
・問い合わせ結果で、新たにテーブルを作成することができる。
この例では、テストm のキーの先頭が「a」で始まるものだけを抽出して、
ワークw テーブルを自動生成している。
この場合、プライマリキーなどの整合性規則はコピーされない。
テーブル構造とレコードだけがコピーされる。
整合性規則がコピーされないのは、ワークテーブルの創成に都合が良い。
primary key の指定が必要な時は、後で、alter table で追加する。
別テーブルへ移動するには、テーブルコピー後にオリジナルを delete、
新テーブルに整合性規則を追加するか、作成済みテーブルに insert で
select 追加すると良い。(下記のレコード追加も参照のこと。)
(表コピー)
create table sak.ワークw as
select
*
from sak.テストm
where キー like 'a%'
;
・集合問い合わせ結果で、新たにテーブルを作成することもできる。
この例では、テスト1m とテスト2m のキーの先頭が「a」で始まるものだけ
を抽出して、ワークw テーブルを自動生成している。
create table sak.ワークw as
select
*
from sak.テスト1m
where キー like 'a%'
union all
select
*
from sak.テスト2m
where キー like 'a%'
;
・空のテーブルをコピーしたい場合は、ありえないキーで抽出する。
この例では、テストm のキーに「@」は存在しないとして、ゼロ件の抽出を
行い、結果として空のワークw テーブルを自動生成している。
create table sak.ワークw as
select
*
from sak.テストm
where キー = '@'
;
■レコード追加(レコード更新、インサート)
・テーブルやビューにレコードを追加する。
ビューについては、基本的に単純ビューでない一切の更新はできない。
この例では、テストm に 1 レコード追加している。
キーはユニーク指定してあるので、「a001」が既に存在するとエラーになる。
insert into sak.テストm (
キー,
データ1,
データ2,
データ3
)
values (
'a001',
100,
200,
300
);
・追加データの並びがテーブル定義と同一であるなら、項目名を省略できる。
insert into sak.テストm values (
'a001',
100,
200,
300
);
・データ2 の default が指定されていれば、default で追加することができ
る。(update でも default 指定が可能。)
insert into sak.テストm values (
'a001',
100,
default,
300
);
・入力が必須でない項目は、省略して追加も可能である。
この例では、データ1 とデータ2 のセットがない。
データ1 とデータ2 には NULL が割り当てられる。
insert into sak.テストm (
キー,
データ3
)
values (
'a001',
300
);
・問い合わせ結果を別テーブルへレコード追加することも可能である。
別テーブルへレコード移動するには、別テーブルへレコード追加後にオリジ
ナルを delete すると良い。(上記のテーブルコピーも参照のこと。)
この例では、テスト2m のキーが「a」で始まるレコードがテストm に追加
される。(副問い合わせ、レコードコピー)
insert into sak.テストm
select
キー,
データ1,
データ2,
データ3
from sak.テスト2m where キー like 'a%'
;
・特定の項目を固定値にしたりもできる。
insert into sak.テストm
select
キー,
データ1,
データ2,
111
from sak.テスト2m where キー like 'a%'
;
insert into sak.テストm
select
'a001',
10,
20,
30
from dual
;
・特定の項目だけをセットすることもできる。
insert into sak.テストm (キー, データ1)
select
'b002',
10
from dual
;
・NULL を項目にセットすることもできる。
もちろん、not null 制約があれば、NULL をセットすることはできない。
insert into sak.テストm (
キー,
データ1,
データ2,
データ3
)
values (
'a001',
100,
200,
NULL
);
・データ中にシングルコーテーション「'」を含める場合は、「''」と 2 個
続けて書くと 1 個の「'」が文字として入る。
insert into sak.テストm (
キー,
データ1,
データ2,
データ3
)
values (
'a0''1',
100,
200,
NULL
);
■レコード修正(レコード更新、アップデート)
・テーブルやビューのレコードを修正する。
ビューについては、基本的に単純ビューでない一切の更新はできない。
この例では、テストm のキーが「a001」のレコードを修正している。
where 句には select と同様に複雑な指定が可能である。
update sak.テストm set
データ1 = 111,
データ2 = 222,
データ3 = 333
where キー = 'a001'
;
・insert と同様に default として更新することができる。
update sak.テストm set
データ1 = 111,
データ2 = default,
データ3 = 333
where キー = 'a001'
;
・一項目だけの修正も可能である。
この例では、テストm のキーが「a001」のレコード中のデータ3 だけを修正
している。
update sak.テストm set
データ3 = 333
where キー = 'a001'
;
・この例では、テストm のキーの先頭が「a」で始まるレコード中のデータ3
を全て 333 に修正している。
update sak.テストm set
データ3 = 333
where キー like 'a%'
;
・この例では、テストm の全レコードのデータ3 を全て 333 に修正している。
update sak.テストm set
データ3 = 333
;
・副問い合わせの結果を項目にセットすることもできる。
この例では、あいうえおm の番号がキーと等しいデータをデータ2 にセット
している。(Oracle では使用できるが、mdb では使用できないので注意。)
update sak.テストm set
データ1 = 111,
データ2 = select データ from あいうえおm where 番号 = キー,
データ3 = 333
where キー like 'a%'
;
・副問い合わせの結果を複数項目にセットすることもできる。
select 文の組み合わせで、複雑な更新が可能である。
update sak.テストm set
(データ1, データ2, データ3) =
(
select データ1, データ2, データ3
from テスト2m
where テスト2m.キー = テストm.キー
)
where キー like 'a%'
;
・NULL を項目にセットすることもできる。
もちろん、not null 制約があれば、NULL をセットすることはできない。
update sak.テストm set
データ1 = 111,
データ2 = 222,
データ3 = NULL
where キー = 'a001'
;
■レコード削除(レコード更新、デリート)
・テーブルやビューのレコードを削除する。
ビューについては、基本的に単純ビューでない一切の更新はできない。
この例では、テストm のキーが「a001」のレコードを削除している。
where 句には select と同様に複雑な指定が可能である。
delete from sak.テストm
where キー = 'a001'
;
・この例では、テストm のキーの先頭が「a」で始まるレコードを全て削除し
ている。
delete from sak.テストm
where キー like 'a%'
;
・この例では、テストm の全レコードを削除している。
件数が多いとレスポンスに問題がある。
テーブルを空にするのが目的なら、表切り捨て truncate を使う方が高速で
現実的である。
delete from sak.テストm;
■トランザクション
・レコード追加、修正、削除では、トランザクション機能が働く。
トランザクションとは、複数のテーブルの複数の更新を一時的に行って、
まとめて、コミット(有効) にするか、ロールバック(無効) にするかできる。
Oracle の場合、コネクト直後、または、commit、roolback 直後から、
トランザクションが自動的に開始されている。
この例では、テストm とテスト2m ふたつのテーブルに複数の更新をかけて、
全て正常に更新できたので、トランザクションコミット(commit) して実際
の更新を有効にしている。
insert into sak.テストm values ('a001', 101, 201, 301);
insert into sak.テストm values ('b002', 102, 202, 302);
insert into sak.テストm values ('c003', 103, 203, 303);
insert into sak.テスト2m values ('d004', 111, 222, 333);
update sak.テスト2m set データ3 = 999 where キー = 'e005';
delete from sak.テスト2m where キー = 'f006';
commit;
・更新の途中でエラーが発生したり、間違った更新をしてしまった場合に、
全ての更新をなかった事にできる。
この例では、テストm とテスト2m ふたつのテーブルに複数の更新をかけて、
最後にトランザクションキャンセル(rollback) して実際の更新を無効にし
ている。トランザクションロールバックすると、それまでの更新命令は全て
取り消され、元の状態に戻る。
insert into sak.テストm values ('a001', 101, 201, 301);
insert into sak.テストm values ('b002', 102, 202, 302);
insert into sak.テストm values ('c003', 103, 203, 303);
insert into sak.テスト2m values ('d004', 111, 222, 333);
update sak.テスト2m set データ3 = 999 where キー = 'e005';
delete from sak.テスト2m where キー = 'f006';
rollback;
■排他制御(レコードロック)
・レコード追加、修正、削除では、レコードレベルの排他制御が働く。
デッドロック回避の考え方は、他のファイルシンテムと同様である。
テーブルロック(表ロック) として lock table もある。
・問い合わせで行ロックする方法として、for update 句がある。
for update nowait は、ロック解除を待たずにエラーを返す。
次の例は、ロックされていたら待って問い合わせる。
select * from sak.受注v1 where 受注番号 = '012345-001'
for update
;
・次の例は、ロックされていたらエラーを返す。
select * from sak.受注v1 where 受注番号 = '012345-001'
for update nowait
;
■表切り捨て
・truncate は、表を切り捨ててクリアする。(Access では使用不可)
(テーブル切り捨て、テーブルクリア、表クリア、テーブル内データ消去)
truncate table sak.受注m;
■SQL 基礎実地編資料
■SQL 基礎編資料
■SQL チューニング編資料
■Oracle PL/SQL 編資料
■PostgreSQL 編、JAVA Servlet、JSP 編資料
■MySQL 編資料
Copyright (C) 1999-2018 Y.SAK