00メニュー > 01はじめてのMySQL > 02MySQLの基礎知識 > 03MySQLを使いこなす > 04MySQL+PHPの基礎 > 05MySQL+PHPの実践 > 06Appendix

09テーブルを編集する（備忘録）→ > 01p > 02p > 03p > 04p > 05p > END

09 テーブルを編集する

  単純にINSERT、SELECT、DELETEするだけでデータベースが運用できるわけではありません。実践では複雑に絡み合った複数のテーブルを使って大量のデータを扱い、処理の効率を考え、そして不慮のデータ消滅も許されません。MySQLの勉強も佳境を迎えています。条件を設定した抽出、編集から、リレーショナルの名にふさわしい複数のテーブルの利用。そして「ビュー」「ストアドプロシージャ」「トランザクション」「ファイル操作」という、より実践的で必要とされる内容を解説します。
☆CHAPTER08⇒いろんな条件で抽出する
★CHAPTER09⇒テーブルを編集する
☆CHAPTER10⇒複数のテーブルを利用する
☆CHAPTER11⇒ビューを使いこなす
☆CHAPTER12⇒ストアドプロシーｼﾞｬを使いこなす
☆CHAPTER13⇒トランザクションを使いこなす
☆CHAPTER14⇒ファイルを使ったやり取り

★CHAPTER09⇒データを編集する(p164)★

  レコードが抽出できるようになったら、次はデータの編集です。カラムごとにデータを修正したり、レコードを削除したり、コピーしたり、といった操作を扱います。最も神経を使う部分です。

（１）《データを修正するｐ１６４》

（１－１）カラムのすべてのデータを一瞬にして修正

まずはレコードの修正方法です。データを修正するには、｢UPDATE｣コマンドを使います。

書式⇒カラムのデータをすべて修正する

UPDATE テーブル名 SET カラム名 = 設定する値;

※特定のカラムを指定した値に変更します。このコマンドを実行すれば、何千件であろうが、何万件であろうが、カラムの全データが一瞬にして置き換わります。
※通常UPDATEコマンドは、次項で扱うように｢WHERE｣で条件を設定して特定のレコードだけを対象として実行します。WHERE等による設定しないで実行するときは、一気にすべてのカラムが置き換わってしまうので、慎重に扱うこと。
※現在、売上情報テーブル｢tb｣には、社員番号の｢bang｣売上｢uria｣、売上月の｢tuki｣という３つのカラムしかありませんので、ここでは｢備考｣の意味で｢VARCHAR(100)｣のデータを持つカラム｢bikou｣を加えます。そしてこのカラム｢bikou｣にUPDATEコマンドで全レコードに｢特記なし｣と文字入力後、全レコードを表示してみましょう。

●実行される内容　ｐ１６５

●操作手順

①次を実行する
ALTER TABLE tb ADD bikou VARCHAR(100);
②次を実行する
UPDATE tb SET bikou='特記なし';
③次を実行する
SELECT * FROM tb;

【実行結果】

mysql> ALTER TABLE tb ADD bikou VARCHAR(100);
Query OK, 10 rows affected (0.79 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> UPDATE tb SET bikou='特記なし';
Query OK, 10 rows affected (0.08 sec)
Rows matched: 10 Changed: 10 Warnings: 0

mysql> SELECT * FROM tb;
+------+------+------+----------+
| bang | uria | tuki | bikou |
+------+------+------+----------+
| A103 | 101 | 4 | 特記なし |
| A102 | 54 | 5 | 特記なし |
| A104 | 181 | 4 | 特記なし |
| A101 | 184 | 4 | 特記なし |
| A103 | 17 | 5 | 特記なし |
| A101 | 300 | 5 | 特記なし |
| A102 | 205 | 6 | 特記なし |
| A104 | 93 | 5 | 特記なし |
| A103 | 12 | 6 | 特記なし |
| A107 | 87 | 6 | 特記なし |
+------+------+------+----------+
10 rows in set (0.00 sec)

mysql>

（１－２）条件に一致するレコードのカラムだけ修正する

｢すべてのカラムを修正する｣のは簡単ですが、実際には、実行する機会はそれほど多くはありません。通常の運用では｢条件に一致したレコードだけ修正｣といったケースが多いでしょう。WHEREで指定した条件に一致するレコードだけを修正してみましょう。次のような構文を使います。

●書式⇒条件に一致したレコードだけ修正

UPDATE テーブル名 SET カラム名 = 設定する値 WHERE 条件;

※売上情報テーブル｢tb｣を見ると、売上が100以上の優秀な成績がいくつかあります。そこで、カラム｢｣が100以上のレコードだけに、その備考蘭に(カラム｢bikou｣)に｢優秀｣と入れてみることにしましょう。

●実行される内容　ｐ１６７

●操作手順

①次を実行する
UPDATE tb SET bikou='優秀' WHERE uria>=100;
②次を実行する
SELECT * FROM tb;

【実行結果】

mysql> UPDATE tb SET bikou='優秀' WHERE uria>=100;
Query OK, 5 rows affected (0.14 sec)
Rows matched: 5 Changed: 5 Warnings: 0

mysql> SELECT * FROM tb;
+------+------+------+----------+
| bang | uria | tuki | bikou |
+------+------+------+----------+
| A103 | 101 | 4 | 優秀 |
| A102 | 54 | 5 | 特記なし |
| A104 | 181 | 4 | 優秀 |
| A101 | 184 | 4 | 優秀 |
| A103 | 17 | 5 | 特記なし |
| A101 | 300 | 5 | 優秀 |
| A102 | 205 | 6 | 優秀 |
| A104 | 93 | 5 | 特記なし |
| A103 | 12 | 6 | 特記なし |
| A107 | 87 | 6 | 特記なし |
+------+------+------+----------+
10 rows in set (0.01 sec)

mysql>

（１－３）売上下位3件だけ｢ガンバレ！｣と修正

  今度は、売上の少ないレコードに対し励ましのメッセージを入れてみることにしましょう。すべての売上を比較して、下位3件にだけ｢ガンバレ！｣をいれます。いっけん面倒そうですが、カラム｢uria｣を｢ORDER BY｣で降順に並べ｢LIMIT 3｣で最初の3件だけ｢SELECT｣し、カラム｢bikou｣に｢ガンバレ！｣を入力する要領で｢UPDATE｣を実行します。

●実行される内容　ｐ１６９

●操作手順

①次を実行する
UPDATE tb
SET bikou='ガンバレ！'
ORDER BY uria
LIMIT 3;

【実行結果】

mysql> UPDATE tb
-> SET bikou='ガンバレ！'
-> ORDER BY uria
-> LIMIT 3;
Query OK, 3 rows affected (0.13 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> SELECT * FROM tb;
+------+------+------+------------+
| bang | uria | tuki | bikou |
+------+------+------+------------+
| A103 | 101 | 4 | 優秀 |
| A102 | 54 | 5 | ガンバレ！ |
| A104 | 181 | 4 | 優秀 |
| A101 | 184 | 4 | 優秀 |
| A103 | 17 | 5 | ガンバレ！ |
| A101 | 300 | 5 | 優秀 |
| A102 | 205 | 6 | 優秀 |
| A104 | 93 | 5 | 特記なし |
| A103 | 12 | 6 | ガンバレ！ |
| A107 | 87 | 6 | 特記なし |
+------+------+------+------------+
10 rows in set (0.13 sec)

mysql>

●操作手順

②次を実行する
SELECT * FROM tb;

※確認のため次を実行して、「uria」の順に並べて表示してみましょう。
SELECT * FROM tb ORDER BY uria;

【実行結果】

mysql> SELECT * FROM tb;
+------+------+------+------------+
| bang | uria | tuki | bikou |
+------+------+------+------------+
| A103 | 101 | 4 | 優秀 |
| A102 | 54 | 5 | ガンバレ！ |
| A104 | 181 | 4 | 優秀 |
| A101 | 184 | 4 | 優秀 |
| A103 | 17 | 5 | ガンバレ！ |
| A101 | 300 | 5 | 優秀 |
| A102 | 205 | 6 | 優秀 |
| A104 | 93 | 5 | 特記なし |
| A103 | 12 | 6 | ガンバレ！ |
| A107 | 87 | 6 | 特記なし |
+------+------+------+------------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM tb ORDER BY uria;
+------+------+------+------------+
| bang | uria | tuki | bikou |
+------+------+------+------------+
| A103 | 12 | 6 | ガンバレ！ |
| A103 | 17 | 5 | ガンバレ！ |
| A102 | 54 | 5 | ガンバレ！ |
| A107 | 87 | 6 | 特記なし |
| A104 | 93 | 5 | 特記なし |
| A103 | 101 | 4 | 優秀 |
| A104 | 181 | 4 | 優秀 |
| A101 | 184 | 4 | 優秀 |
| A102 | 205 | 6 | 優秀 |
| A101 | 300 | 5 | 優秀 |
+------+------+------+------------+
10 rows in set (0.01 sec)

mysql>

※最後に、売上情報テーブル｢tb1｣のカラム｢bikou｣を削除して、元のテーブル｢tb｣に戻しましょう。
ALTER TABLE tb DROP bikou;

mysql> ALTER TABLE tb DROP bikou;
Query OK, 10 rows affected (0.16 sec
Records: 10 Duplicates: 0 Warnings

mysql> SELECT * FROM tb;
+------+------+------+
| bang | uria | tuki |
+------+------+------+
| A103 | 101 | 4 |
| A102 | 54 | 5 |
| A104 | 181 | 4 |
| A101 | 184 | 4 |
| A103 | 17 | 5 |
| A101 | 300 | 5 |
| A102 | 205 | 6 |
| A104 | 93 | 5 |
| A103 | 12 | 6 |
| A107 | 87 | 6 |
+------+------+------+
10 rows in set (0.00 sec)

mysql>

（２）《条件に一致したレコードだけをコピーする。ｐ１７１》

（２－１）特定のレコードだけをコピーする

第７では、すべてのレコードを丸ごとコピーする方法を３種類紹介しました。ここでは、条件に一致するレコードを選んで他のテーブルにコピーする方法を学びます。
※例えば、「成績が８０点以上のレコードだけ」「渡辺さんのレコードだけ」を集めて、別のテーブルを作るといった操作です。条件を設定して「SELECT」したレコードを挿入し、「CREATE TABLE」で新規にテーブルを作成します。つまり、「CREATE TABLE 新規テーブル名 SELECT * FROM 元となるテーブル名」に「WHERE」で条件を設定します。
※売上情報テーブル｢tb｣から、ベテラン社員「A101」さんのレコードだけを抽出して、「A101」さんだけのテーブル「tb_A101」を作成してみよう。
※具体的には、テーブル｢tb｣のカラム構造および「bang」がA101のレコードだけをコピーして、新規のテーブル｢tb_A101｣を作成します。さらに、作成後、テーブル｢tb_A101｣の全レコードを表示してみましょう。

●実行される内容　ｐ１７２

●操作手順

①次を実行する
CREATE TABLE tb_A101
SELECT *
FROM tb
WHERE bang LIKE 'A101';
②次を実行する
SELECT * FROM tb_A101;

【実行結果】

mysql> CREATE TABLE tb_A101
-> SELECT *
-> FROM tb
-> WHERE bang LIKE 'A101';
Query OK, 2 rows affected (0.51 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM tb_A101;
+------+------+------+
| bang | uria | tuki |
+------+------+------+
| A101 | 184 | 4 |
| A101 | 300 | 5 |
+------+------+------+
2 rows in set (0.00 sec)

mysql>
※・・・

※すでに存在するテーブルにレコードを挿入する場合は、次のように「CREATE TABLE」の部分を「INSERT INTO」に変えるだけです。
INSERT INTO 存在するテーブル名 SELECT * FROM tb WHERE bang LIKE A101;

（２－２）順番を並べてコピーするｐ１７３

  最も小さい値を持つ３レコードだけをコピー」「上位５番から１０番までのレコードをコピー」などのように、あるカラムの値の順に基づいてレコードをコピーする方法を学びます。
前項と同様に「CREATE TABLE ～ SELECT～」を実行するときに「ORDER BY」で順に並べ、さらに「LIMIT」や「OFFSET」を使ってコピーするレコードの数や開始位置を指定する。
※具体的には、テーブル｢tb｣のカラム構造及びカラム「uria」の値が上位２番目から５番目までの４レコードをコピーして、新規にテーブル｢tb_2to5｣を作成します。さらに作成後、テーブル｢tb_2to5｣の全レコードを表示してみましょう。

●実行される内容　ｐ１７３

●操作手順

①次を実行する
CREATE TABLE tb_2to5
SELECT *
FROM tb
ORDER BY uria
DESC
LIMIT 4
OFFSET 1;
②次を実行する
SELECT * FROM tb_2to5;

【実行結果】

mysql> CREATE TABLE tb_2to5
-> SELECT *
-> FROM tb
-> ORDER BY uria
-> DESC
-> LIMIT 4
-> OFFSET 1;
Query OK, 4 rows affected (0.16 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM tb_2to5;
+------+------+------+
| bang | uria | tuki |
+------+------+------+
| A102 | 205 | 6 |
| A101 | 184 | 4 |
| A104 | 181 | 4 |
| A103 | 101 | 4 |
+------+------+------+
4 rows in set (0.00 sec)

mysql>

（３）《条件に一致したレコードを削除するｐ１７５》

（３－１）全レコードを削除する（復習）

  今度は、レコードを選択して削除します。なにしろレコードが消えてしまうのですから、「UPDATE」よりさらに慎重に行なうべきコマンドです。 すべてのレコードを削除する方法は、「DELETE FROM テーブル名;」でした。「DELETE」コマンドは「レコードを削除」しましたが、テーブル（テーブルのカラム構造）自体はそのまま残ります。ちなみにテーブル自体を削除するときは「DROP TABLE･･･」です。

（３－２）特定のレコードを削除

  条件に一致したレコードだけを削除する方法を勉強します。ここまで「WHERE」ｄｗ条件を設定した「SELECT」による方法を扱いましたが、これを「DELETE」に置き換えるイメージです。「DELETE FROM～」でレコードを削除するとき、さらに「WHERE」で条件を設定します。

書式⇒条件に一致するレコードを削除する

DELETE FROM テーブル名 WHERE 条件;

さて、社員情報テーブル「tb1」を見ると、年齢は２３～４０となっています。年齢が３０未満のレコードだけを削除してみることにしましょう。
※ここでは、社員情報テーブル「tb1」と同じ内容のテーブル｢tb1k｣があるものとして解説します。
※テーブル｢tb1｣と同じ内容のテーブル｢tb1k｣で、カラム｢tosi｣の値が３０未満の３レコードだけを削除します。さらに削除後、テーブル｢tb1k｣の全レコードを表示してみましょう。

●実行される内容　ｐ１７６

●操作手順

※テーブルをコピー
CREATE TABLE tb1k SELECT * FROM tb1;
①次を実行する
DELETE FROM tb1k WHERE tosi<30;

②次を実行する
SELECT * FROM tb1k;

【実行結果】

mysql> CREATE TABLE tb1k SELECT * FROM tb1;
Query OK, 6 rows affected (0.38 sec)
Records: 6 Duplicates: 0 Warnings: 0

①mysql> DELETE FROM tb1k WHERE tosi<30;
Query OK, 2 rows affected (0.03 sec)

②mysql> SELECT * FROM tb1k;
+------+------+------+
| bang | nama | tosi |
+------+------+------+
| A101 | 佐藤 | 40 |
| A105 | 西沢 | 35 |
+------+------+------+
2 rows in set (0.00 sec)

mysql>

（３－３）順番に並べてから削除

  今度は、「順番に並べてからコピーする」の削除版です。「上位３番目までを削除」というようなケースです。「ORDER BY」で順番に並べ、さらに「LIMIT」を使って削除するレコードの数を指定します。
売上情報テーブル｢tb｣と同じ内容のテーブル｢tb_copy｣に対して、売上が上位４レコードを削除します。
※具体的には、テーブル｢tb｣と同じ内容のテーブル｢tb_copy｣で、カラム｢uria｣の値が大きもの４レコードを削除します。
さらにテーブル｢tb_copy｣の全レコードを表示してみましょう。

●実行される内容　ｐ１７７

●操作手順

※テーブルをコピー
CREATE TABLE tb_copy SELECT * FROM tb;
①次を実行する
DELETE FROM tb_copy
ORDER BY uria
DESC
LIMIT 4;


②次を実行する
SELECT * FROM tb_COPY;

【実行結果】

mysql> CREATE TABLE tb_copy SELECT * FROM tb;
Query OK, 10 rows affected (0.64 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> SHOW TABLES;
+-----------------+
| Tables_in_db1 |
+-----------------+
| hizu_ziko |
| hizuke |
| ima |
| itii |
| nadake |
| renzoku |
| syokiti_renzoku |
| tb |
| tb1 |
| tb1b |
| tb1c |
| tb1g |
| tb1h |
| tb1i |
| tb1j |
| tb1k |
| tb_2to5 |
| tb_a101 |
| tb_copy |
| unig |
+-----------------+
20 rows in set (0.01 sec)

mysql> SELECT * FROM tb_copy;
+------+------+------+
| bang | uria | tuki |
+------+------+------+
| A103 | 101 | 4 |
| A102 | 54 | 5 |
| A104 | 181 | 4 |
| A101 | 184 | 4 |
| A103 | 17 | 5 |
| A101 | 300 | 5 |
| A102 | 205 | 6 |
| A104 | 93 | 5 |
| A103 | 12 | 6 |
| A107 | 87 | 6 |
+------+------+------+
10 rows in set (0.00 sec)

mysql> DELETE FROM tb_copy
-> ORDER BY uria
-> DESC
-> LIMIT 4;
Query OK, 4 rows affected (0.46 sec)

mysql> SELECT * FROM tb_COPY;
+------+------+------+
| bang | uria | tuki |
+------+------+------+
| A103 | 101 | 4 |
| A102 | 54 | 5 |
| A103 | 17 | 5 |
| A104 | 93 | 5 |
| A103 | 12 | 6 |
| A107 | 87 | 6 |
+------+------+------+
6 rows in set (0.00 sec)

mysql>

＜まとめ＞

●指定したカラムの値をすべて更新する方法
●条件に一致したレコードのカラムを更新する方法
●条件に一致したレコードだけを削除する方法
●昇順（降順）に並べたレコードから、件数を指定してカラムの値を更新する方法

＜チェック＞

□UPDATE命令を使ってカラムの値を更新できる
□WHEREを使って、条件に一致したレコードのカラムだけを更新できる
□WHEREを使って、条件に一致したレコードだけをコピーできる
□｢ORDER BY｣と｢LIMIT｣を使って、レコードを限定して更新できる
□WHEREを使って、条件に一致したレコードだけを削除できる

＜練習問題＞

問題１
  テーブル｢tb｣の｢uria｣の合計値の前後に｢合計は｣｢万円です｣の文字列を付けて表示して下さい。 なお、表示する項目名には｢売上｣のエイリアスを付けること。

問題２
  次のようなテーブル｢zaiko｣があります。
○【zaiko】テーブル
カラム名→a→b→c
カラムのデータ型→VARCHAR(10)→INT→DATE
カラムの内容→東支店→ 200→1999－08－08
　　　西支店→ 500→2006－06－15
　　　南支店→ 100→1998－02－23
　　　北支店→ 400→2007－08－08

●実行される内容　ｐ１７９

このテーブル｢zaiko｣のカラム｢c｣の値が、今現在より５年前以前のものを除いたレコードだけを抽出し、テーブル「new_zaiko」を作ってください。ただし、「今現在より５年前」の日時は次の表すものとします。またこの処理は、２００８年に行なったものです。
□今現在より５年前の日時
NOW()- INTERVAL 5 YEAR

《解答1》

問題１
次を実行します
CREATE TABLE new_zaiko
SELECT * FROM zaiko
where c>NOW() - INTERVAL 5 YEAR;

【実行結果】

未処理

※テーブル｢new_zaiko｣の作成と確認を行ないます。

【実行結果】

未処理