SAK 図書館

SAK Streets メインストリート 図書館・カフェ 一覧 SAK Streets 検索
| VB | SQL | Perl | PHP | ASP | JSP | C | PC | HP | FAQ | フリーソフト |

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 編資料
| VB | SQL | Perl | PHP | ASP | JSP | C | PC | HP | FAQ | フリーソフト |

SAK Streets メインストリート 図書館・カフェ 一覧 SAK Streets 検索



Copyright (C) 1999-2017 Y.SAK