SAK 図書館

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

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

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



Copyright (C) 1999-2017 Y.SAK