データベース - MySQL - レコードの挿入・抽出

[fr-sato]

■目的

SQLを用いてレコードの挿入、抽出などについて学びます。

■課題

テーブル作成 > 課題1で作成したテーブルに対し以下レコード操作を行って下さい。
発行したコマンドはIssueに記述して下さい。

• レコードを1件挿入
• レコードを複数挿入(SQLは1文のみ)
• レコードを全件抽出
• IDが2のレコードを抽出
• created_atを降順に並び替えてレコードを全件取得
• IDを降順に並び替えてレコードを2件取得
• レコードの総数を抽出
• 課題4で作成したテーブルを内部結合して全件取得
• IDが1のレコードのeditorをsatoに更新
• editorがsatoのレコードを削除

[fr-itaya]

本課題は工数見積5時間で行います。 開始時刻・中断は追ってコメント致します。

[fr-itaya]

6/4は15：30に開始します。

[fr-itaya]

レコードを1件挿入

• 使用コマンド：

INSERT INTO table_name(column_name1, column_name2,...) VALUES (val1, val2,...);

• 出力結果：

mysql> INSERT INTO test02(id, title, editor, created_at) VALUES(1, 'Book1', 'hoge', 19970901000000);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM test02;
+----+----------+-----------+---------------------+
| id | title    | editor    | created_at          |
+----+----------+-----------+---------------------+
|  1 | Book1    | hoge      | 1997-09-01 00:00:00 |
+----+----------+-----------+---------------------+
1 row in set (0.00 sec)

レコードを複数挿入(SQLは1文のみ)

• 使用コマンド：

INSERT INTO table_name (colomn_name1[,column_name2, ... ]) VALUES (val1, val2, ... ), (val1, val2, ...), (val1, val2, ...)...;

• 複数行の挿入は、括弧をカンマ区切りで繋げることで実現できる
• 出力結果：

mysql> INSERT INTO test02(id, title, editor, created_at) VALUES(2, 'Permiculation City', 'Greg Egan', 19940101000000), (3, 'Nighteen Eighty-Four', 'George Orwell', 19721220000000);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test02;                                                                          
+----+----------------------+---------------+---------------------+
| id | title                | editor        | created_at          |
+----+----------------------+---------------+---------------------+
|  1 | Book1                | hoge          | 1997-09-01 00:00:00 |
|  2 | Permiculation City   | Greg Egan     | 1994-01-01 00:00:00 |
|  3 | Nighteen Eighty-Four | George Orwell | 1972-12-20 00:00:00 |
+----+----------------------+---------------+---------------------+
3 rows in set (0.00 sec)

レコードを全件抽出

• 使用コマンド：SELECT * FROM table_name WHERE (condition);
• 出力結果：

mysql> SELECT * FROM test02;
+----+----------------------+---------------+---------------------+
| id | title                | editor        | created_at          |
+----+----------------------+---------------+---------------------+
|  1 | Book1                | hoge          | 1997-09-01 00:00:00 |
|  2 | Permiculation City   | Greg Egan     | 1994-01-01 00:00:00 |
|  3 | Nighteen Eighty-Four | George Orwell | 1972-12-20 00:00:00 |
+----+----------------------+---------------+---------------------+
3 rows in set (0.00 sec)

IDが2のレコードを抽出

• 使用コマンド：SELECT * FROM table_name WHERE id = 2;
• 出力結果：

mysql> SELECT * FROM test02 WHERE id=2;
+----+--------------------+-----------+---------------------+
| id | title              | editor    | created_at          |
+----+--------------------+-----------+---------------------+
|  2 | Permiculation City | Greg Egan | 1994-01-01 00:00:00 |
+----+--------------------+-----------+---------------------+
1 row in set (0.00 sec)

created_atを降順に並び替えてレコードを全件取得

• 使用コマンド：SELECT * FROM table_name ORDER BY column_name DESC;
• 出力結果：

mysql> SELECT * FROM test02 ORDER BY created_at DESC;
+----+----------------------+---------------+---------------------+
| id | title                | editor        | created_at          |
+----+----------------------+---------------+---------------------+
|  1 | Book1                | hoge          | 1997-09-01 00:00:00 |
|  2 | Permiculation City   | Greg Egan     | 1994-01-01 00:00:00 |
|  3 | Nighteen Eighty-Four | George Orwell | 1972-12-20 00:00:00 |
+----+----------------------+---------------+---------------------+
3 rows in set (0.00 sec)

並びが変わらなかったので昇順でも試してみる

mysql> SELECT * FROM test02 ORDER BY created_at ASC;
+----+----------------------+---------------+---------------------+
| id | title                | editor        | created_at          |
+----+----------------------+---------------+---------------------+
|  3 | Nighteen Eighty-Four | George Orwell | 1972-12-20 00:00:00 |
|  2 | Permiculation City   | Greg Egan     | 1994-01-01 00:00:00 |
|  1 | Book1                | hoge          | 1997-09-01 00:00:00 |
+----+----------------------+---------------+---------------------+
3 rows in set (0.00 sec)

IDを降順に並び替えてレコードを2件取得

• 使用コマンド：SELECT * FROM table_name ORDER BY column_name DESC LIMIT num;
• 出力結果：

mysql> SELECT * FROM test02 ORDER BY id DESC LIMIT 2;
+----+----------------------+---------------+---------------------+
| id | title                | editor        | created_at          |
+----+----------------------+---------------+---------------------+
|  3 | Nighteen Eighty-Four | George Orwell | 1972-12-20 00:00:00 |
|  2 | Permiculation City   | Greg Egan     | 1994-01-01 00:00:00 |
+----+----------------------+---------------+---------------------+
2 rows in set (0.00 sec)

レコードの総数を抽出

• 使用コマンド：SELECT COUNT(*) FROM table_name;
• 出力結果：

mysql> SELECT COUNT(*) FROM test02;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

課題4で作成したテーブルを内部結合して全件取得

• 使用コマンド：

SELECT * FROM table_nameL INNER JOIN table_nameR ON table_nameL.column_name = table_nameR.column_name;

• 内部結合を行うには、2つのテーブルで結合の対象となるカラムを指定する
  • 出力結果：

mysql> CREATE TABLE test03
    -> (
    -> id INT NOT NULL,
    -> detail TEXT,
    -> FOREIGN KEY(id)
    -> REFERENCES test02(id)
    -> )
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.17 sec)

• ここで間違えて外部キーを設定してしまったことにより、最後の操作(レコードの削除)でエラーが出る。
• 今回やむなくテーブルの外部キー設定を外す処理を行った。コマンドと出力結果は後述

mysql> SHOW TABLE STATUS
    -> ;
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| test02 | InnoDB |      10 | Compact    |    3 |           5461 |       16384 |               0 |            0 |         0 |              4 | 2014-06-04 03:33:47 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| test03 | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |         0 |           NULL | 2014-06-04 07:55:05 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
2 rows in set (0.00 sec)

mysql> DESCRIBE test03;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id     | int(11) | NO   | MUL | NULL    |       |
| detail | text    | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> INSERT INTO test03(id, detail) VALUES(1,'Someones diary'), (2, 'SF novel featured in artificial life and simulated reality'), (3, 'This novel portrays a society omnipresent government surveillance and public manipulation');                                                                            Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test03;
+----+-------------------------------------------------------------------------------------------+
| id | detail                                                                                    |
+----+-------------------------------------------------------------------------------------------+
|  1 | Someones diary                                                                            |
|  2 | SF novel featured in artificial life and simulated reality                                |
|  3 | This novel portrays a society omnipresent government surveillance and public manipulation |
+----+-------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM test02 INNER JOIN test03 ON test02.id = test03.id;                               
+----+----------------------+---------------+---------------------+----+-------------------------------------------------------------------------------------------+
| id | title                | editor        | created_at          | id | detail                                                                                    |
+----+----------------------+---------------+---------------------+----+-------------------------------------------------------------------------------------------+
|  1 | Book1                | hoge          | 1997-09-01 00:00:00 |  1 | Someones diary                                                                            |
|  2 | Permiculation City   | Greg Egan     | 1994-01-01 00:00:00 |  2 | SF novel featured in artificial life and simulated reality                                |
|  3 | Nighteen Eighty-Four | George Orwell | 1972-12-20 00:00:00 |  3 | This novel portrays a society omnipresent government surveillance and public manipulation |
+----+----------------------+---------------+---------------------+----+-------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

IDが1のレコードのeditorをsatoに更新

• 使用コマンド：

UPDATE table_name SET column_name=`val`[, column_name=`val`, ... ] WHERE conditional_formula;

• 出力結果：

mysql> UPDATE test02 SET editor = 'sato' WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM test02 WHERE id=1;
+----+-------+--------+---------------------+
| id | title | editor | created_at          |
+----+-------+--------+---------------------+
|  1 | Book1 | sato   | 1997-09-01 00:00:00 |
+----+-------+--------+---------------------+
1 row in set (0.01 sec)

editorがsatoのレコードを削除

• 使用コマンド：DELETE FROM table_name [WHERE conditional _formula];
• 出力結果：

mysql> SHOW CREATE TABLE test03;
+--------+------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                 |
+--------+------------------------------------------------------------------------------------------------------------------------------+
| test03 | CREATE TABLE `test03` (
  `id` int(11) DEFAULT NULL,
  `detail` text,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> DELETE FROM test02 WHERE editor='sato';
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM test02;
+----+----------------------+---------------+---------------------+
| id | title                | editor        | created_at          |
+----+----------------------+---------------+---------------------+
|  2 | Permiculation City   | Greg Egan     | 1994-01-01 00:00:00 |
|  3 | Nighteen Eighty-Four | George Orwell | 1972-12-20 00:00:00 |
+----+----------------------+---------------+---------------------+
2 rows in set (0.00 sec)

[fr-itaya]

補足：外部キー設定を削除する

外部キーの参照先になっているため、親テーブル(test02)のレコードを削除しようとするとエラーが出る。 (この後の記述の整合性を保つため、ALTER TABLE table_name MODIFYで誤って子テーブルのNOT NULLを外してしまったことも併せて記載しておきます)

mysql> DELETE FROM test02 WHERE editor='sato';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mysql_test`.`test03`, CONSTRAINT `test03_ibfk_1` FOREIGN KEY (`id`) REFERENCES `test02` (`id`))
mysql> ALTER TABLE test03 MODIFY COLUMN id INT;
Query OK, 0 rows affected (0.44 sec)
Records: 0  Duplicates: 0  Warnings: 0

まずテーブル内部に設定された外部キーIDを確認 外部キーIDはSHOW CREATE TABLEのCONSTRAINT句に記載

mysql> SHOW CREATE TABLE test03;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                             |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test03 | CREATE TABLE `test03` (
  `id` int(11) DEFAULT NULL,
  `detail` text,
  KEY `id` (`id`),
  CONSTRAINT `test03_ibfk_1` FOREIGN KEY (`id`) REFERENCES `test02` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

ALTER TABLE table_name DROP FOREIGN KEY [foreign_key_id];で外部キー設定を外す

mysql> ALTER TABLE test03 DROP FOREIGN KEY test03_ibfk_1;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

テーブル情報確認

mysql> SHOW CREATE TABLE test03;
+--------+------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                 |
+--------+------------------------------------------------------------------------------------------------------------------------------+
| test03 | CREATE TABLE `test03` (
  `id` int(11) DEFAULT NULL,
  `detail` text,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

これで削除できるようになった

mysql> DELETE FROM test02 WHERE editor='sato';
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM test02;
+----+----------------------+---------------+---------------------+
| id | title                | editor        | created_at          |
+----+----------------------+---------------+---------------------+
|  2 | Permiculation City   | Greg Egan     | 1994-01-01 00:00:00 |
|  3 | Nighteen Eighty-Four | George Orwell | 1972-12-20 00:00:00 |
+----+----------------------+---------------+---------------------+
2 rows in set (0.00 sec)

[fr-sato]

確認しました、基本的にはOKです。

親テーブルのあるデータを削除する場合は外部キー削除ではなく、紐づく子テーブルのデータを削除後に親テーブルのデータを削除するようにしましょう。

[fr-itaya]

補足2：正しい親テーブル・子テーブルのレコード削除

mysql> SELECT * FROM test02;
+----+----------------------+---------------+---------------------+
| id | title                | editor        | created_at          |
+----+----------------------+---------------+---------------------+
|  2 | Permiculation City   | Greg Egan     | 1994-01-01 00:00:00 |
|  3 | Nighteen Eighty-Four | George Orwell | 1972-12-20 00:00:00 |
+----+----------------------+---------------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test03;
+------+-------------------------------------------------------------------------------------------+
| id   | detail                                                                                    |
+------+-------------------------------------------------------------------------------------------+
|    1 | Someones diary                                                                            |
|    2 | SF novel featured in artificial life and simulated reality                                |
|    3 | This novel portrays a society omnipresent government surveillance and public manipulation |
+------+-------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

最後の操作をする前に戻す

mysql> INSERT INTO test02 (id, title, editor, created_at) VALUES(1, 'Book1', 'hoge', 201301010000);
ERROR 1292 (22007): Incorrect datetime value: '201301010000' for column 'created_at' at row 1
mysql> INSERT INTO test02 (id, title, editor, created_at) VALUES(1, 'Book1', 'hoge', 20130101000000);
Query OK, 1 row affected (0.01 sec)

子テーブルの情報確認

mysql> SHOW CREATE TABLE test03;
+--------+------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                 |
+--------+------------------------------------------------------------------------------------------------------------------------------+
| test03 | CREATE TABLE `test03` (
  `id` int(11) DEFAULT NULL,
  `detail` text,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

後から外部キー制約追加

mysql> ALTER TABLE test03 ADD FOREIGN KEY(id) REFERENCES test02(id);
Query OK, 3 rows affected (34.58 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE test03;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                             |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test03 | CREATE TABLE `test03` (
  `id` int(11) DEFAULT NULL,
  `detail` text,
  KEY `id` (`id`),
  CONSTRAINT `test03_ibfk_1` FOREIGN KEY (`id`) REFERENCES `test02` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

本題。まず子テーブルから、外部キー参照元のレコードを削除

mysql> DELETE FROM test03 WHERE id=1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    35
Current database: mysql_test

Query OK, 1 row affected (0.05 sec)

つづいて親テーブルから参照先のレコードを削除

mysql> DELETE FROM test02 WHERE editor='hoge';
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM test02;
+----+----------------------+---------------+---------------------+
| id | title                | editor        | created_at          |
+----+----------------------+---------------+---------------------+
|  2 | Permiculation City   | Greg Egan     | 1994-01-01 00:00:00 |
|  3 | Nighteen Eighty-Four | George Orwell | 1972-12-20 00:00:00 |
+----+----------------------+---------------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test03;
+------+-------------------------------------------------------------------------------------------+
| id   | detail                                                                                    |
+------+-------------------------------------------------------------------------------------------+
|    2 | SF novel featured in artificial life and simulated reality                                |
|    3 | This novel portrays a society omnipresent government surveillance and public manipulation |
+------+-------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)