MySQL 5.6 Developer試験公式「試験内容チェックリスト」

https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882education.oracle.com

MySQL 5.6 リファレンスマニュアル

INSERT文について説明する。INSERT文を実行する
REPLACE文について説明する。REPLACE文を実行する
UPDATE文について説明する。UPDATE文を実行する
TRUNCATE文について説明する。TRUNCATE文を実行する
LOAD DATA文について説明する。LOAD DATA文を実行する
DELETE文について説明する。DELETE文を実行する

dev.mysql.com

INSERT文について説明する。INSERT文を実行する

dev.mysql.com

INSERT ... SELECT

連番を突っ込むのとかに使ってる
カラム名とかは関係ないので注意

mysql> CREATE TABLE tbl(col int, col2 int);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO tbl(col, col2)
  SELECT num * num AS col2,
         num AS col
    FROM seq;
Query OK, 1000 rows affected (0.01 sec)
Records: 1000  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tbl ORDER BY col LIMIT 10;
+------+------+
| col  | col2 |
+------+------+
|    0 |    0 |
|    1 |    1 |
|    4 |    2 |
|    9 |    3 |
|   16 |    4 |
|   25 |    5 |
|   36 |    6 |
|   49 |    7 |
|   64 |    8 |
|   81 |    9 |
+------+------+
10 rows in set (0.00 sec)

単なるVALUESの糖衣と考えたほうがよさそう
【所感】select listに順序を要求するのがリレーショナルモデル的に気持ち悪い…

INSERT DELAYED

5.6時点でdeprecated
- 8.0で消滅
複数セッションから同時に大量行を挿入するようなケースで高速化が期待できる
- 逆にそのようなケースでなければ普通のINSERTのほうが速い
MyISAM等で利用可能
- InnoDB不可

mysql> CREATE TABLE tbl(col INT) ENGINE=InnoDB;
CREATE TABLE tbl(col INT) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT DELAYED INTO tbl VALUES (1);
INSERT DELAYED INTO tbl VALUES (1);
ERROR 1616 (HY000): DELAYED option not supported for table 'tbl'

mysql> CREATE TABLE tbl(col INT) ENGINE=MyISAM;
CREATE TABLE tbl(col INT) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT DELAYED INTO tbl VALUES (1);
INSERT DELAYED INTO tbl VALUES (1);
Query OK, 1 row affected, 1 warning (0.00 sec)

INSERT ... ON DUPLICATE KEY UPDATE

後述のREPLACEと異なり、こちらは更新なのでAUTO_INCREMENTが増加しない

CREATE TABLE tbl(
  id INT PRIMARY KEY AUTO_INCREMENT,
  code CHAR(4) UNIQUE,
  name VARCHAR(32)
);

mysql> INSERT INTO tbl(code, name) VALUES ('xxxx', 'old') 
  ON DUPLICATE KEY UPDATE
    name = VALUES(name);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tbl;
+----+------+------+
| id | code | name |
+----+------+------+
|  1 | xxxx | old  |
+----+------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO tbl(code, name) VALUES ('xxxx', 'new') 
  ON DUPLICATE KEY UPDATE
    name = VALUES(name);
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM tbl;
+----+------+------+
| id | code | name |
+----+------+------+
|  1 | xxxx | new  |
+----+------+------+
1 row in set (0.00 sec)

REPLACE文について説明する。REPLACE文を実行する

dev.mysql.com

REPLACE は、INSERT とまったく同じように機能します。ただし、テーブル内の古い行に、PRIMARY KEY または UNIQUE インデックスに関して新しい行と同じ値が含まれている場合、その古い行は新しい行が挿入される前に削除されます。

挿入 or (削除 + 挿入)
- cf. 挿入 or 更新はINSERT ... ON DUPLICATE KEY UPDATE
候補キー的なものがない限りINSERTと全く同じ

CREATE TABLE test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

CREATE TABLE test2 (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id, ts)
);

mysql> REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.00 sec)

mysql> REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');
REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 2 rows affected (0.01 sec)

mysql> SELECT * FROM test; 
SELECT * FROM test; 
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
1 row in set (0.00 sec)

mysql> REPLACE INTO test2 VALUES (1, 'Old', '2014-08-20 18:47:00');
REPLACE INTO test2 VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.01 sec)

mysql> REPLACE INTO test2 VALUES (1, 'New', '2014-08-20 18:47:42');
REPLACE INTO test2 VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test2; 
SELECT * FROM test2; 
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | Old  | 2014-08-20 18:47:00 |
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
2 rows in set (0.00 sec)

削除+挿入なので、自動インクリメントは増加することに注意する

CREATE TABLE tbl(
  id INT PRIMARY KEY AUTO_INCREMENT,
  code CHAR(4) UNIQUE,
  name VARCHAR(32)
);

mysql> REPLACE INTO tbl(code, name) VALUES ('xxxx', 'old');
REPLACE INTO tbl(code, name) VALUES ('xxxx', 'old');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM tbl;
SELECT * FROM tbl;
+----+------+------+
| id | code | name |
+----+------+------+
|  1 | xxxx | old  |
+----+------+------+
1 row in set (0.00 sec)

mysql> REPLACE INTO tbl(code, name) VALUES ('xxxx', 'new');
REPLACE INTO tbl(code, name) VALUES ('xxxx', 'new');
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM tbl;
SELECT * FROM tbl;
+----+------+------+
| id | code | name |
+----+------+------+
|  2 | xxxx | new  |
+----+------+------+
1 row in set (0.00 sec)

UPDATE文について説明する。UPDATE文を実行する

複数テーブルの更新ができる

CREATE TABLE tbl(id int PRIMARY KEY AUTO_INCREMENT);
CREATE TABLE tbl2 (
  parent_id INT,
  num INT,
  FOREIGN KEY (parent_id) REFERENCES tbl(id)
);


INSERT INTO tbl VALUES (1),(2),(3);
INSERT INTO tbl2 VALUES (1, 10),(1, 20),(2, 30),(2, 40),(NULL, 50);

SELECT * FROM tbl
  INNER JOIN tbl2
  ON tbl.id = tbl2.parent_id;

+----+-----------+------+
| id | parent_id | num  |
+----+-----------+------+
|  1 |         1 |   10 |
|  1 |         1 |   20 |
|  2 |         2 |   30 |
|  2 |         2 |   40 |
+----+-----------+------+
4 rows in set (0.00 sec)

更新

UPDATE tbl
  INNER JOIN tbl2 ON tbl.id=tbl2.parent_id
  SET
    tbl2.num = tbl2.num - 5;

Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> SELECT * FROM tbl
  INNER JOIN tbl2
  ON tbl.id = tbl2.parent_id;

+----+-----------+------+
| id | parent_id | num  |
+----+-----------+------+
|  1 |         1 |    5 |
|  1 |         1 |   15 |
|  2 |         2 |   25 |
|  2 |         2 |   35 |
+----+-----------+------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM tbl2;
+-----------+------+
| parent_id | num  |
+-----------+------+
|         1 |    5 |
|         1 |   15 |
|         2 |   25 |
|         2 |   35 |
|      NULL |   50 |
+-----------+------+
5 rows in set (0.00 sec)

TRUNCATE文について説明する。TRUNCATE文を実行する

dev.mysql.com

DELETEとの違い
- はやい
- DDLである
- 速さのためにDMLのもろもろをバイパスする
  - ロールバックできない(暗黙的なcommit)
  - ON DELETEトリガーが起動しない
  - ので、FK制約の親などにはTRUNCATEできない
  - AUTO_INCREMENTは開始値にリセット
    - 【補】PostgreSQLのSERIALとは異なる挙動
- DROP権限を要する
  - 動作もDROP TABLE + CREATE TABLEに似ている
FKの親テーブルのTRUNCATEは怒られる
- 参照されている行が無くても

mysql> CREATE TABLE tbl(id int PRIMARY KEY AUTO_INCREMENT);
CREATE TABLE tbl(id int PRIMARY KEY AUTO_INCREMENT);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE tbl2(parent_id int, FOREIGN KEY (parent_id) REFERENCES tbl(id));
CREATE TABLE tbl2(parent_id int, FOREIGN KEY (parent_id) REFERENCES tbl(id));
Query OK, 0 rows affected (0.01 sec)


mysql> TRUNCATE tbl;
TRUNCATE tbl;
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`mysql`.`tbl2`, CONSTRAINT `tbl2_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `mysql`.`tbl` (`id`))

【補】FK制約チェックを外せば可能

mysql> SET foreign_key_checks='OFF';
SET foreign_key_checks='OFF';
Query OK, 0 rows affected (0.00 sec)

mysql> TRUNCATE tbl;
TRUNCATE tbl;
Query OK, 0 rows affected (0.02 sec)

LOAD DATA文について説明する。LOAD DATA文を実行する

INSERTよりはやいやつ
LOCAL: クライアント側のファイルを読み込むの意
- 【補】PostgreSQLのpsqlの\copyコマンド相当
LOCALなし: サーバー側のファイルを読み込むの意
- 【補】PostgreSQLのCOPYステートメント相当

DELETE文について説明する。DELETE文を実行する

JOIN結果をDELETEできる
- どのテーブルの行をDELETEするかを指定する必要がある

CREATE TABLE tbl(id int PRIMARY KEY AUTO_INCREMENT);
CREATE TABLE tbl2 (
  id INT,
  parent_id INT,
  FOREIGN KEY (parent_id) REFERENCES tbl(id)
);


INSERT INTO tbl VALUES (1),(2),(3);
INSERT INTO tbl2 VALUES (1, 1),(2, 1),(3, 2),(4, 2),(5, NULL);

SELECT * FROM tbl
  INNER JOIN tbl2
  ON tbl.id = tbl2.parent_id;

+----+------+-----------+
| id | id   | parent_id |
+----+------+-----------+
|  1 |    1 |         1 |
|  1 |    2 |         1 |
|  2 |    1 |         2 |
|  2 |    2 |         2 |
+----+------+-----------+
4 rows in set (0.00 sec)

親に紐付いた子の行のみ削除される

DELETE tbl2
  FROM tbl
  INNER JOIN tbl2 
  ON tbl.id = tbl2.parent_id;

mysql> SELECT * FROM tbl
  INNER JOIN tbl2
  ON tbl.id = tbl2.parent_id;

SELECT * FROM tbl
    ->   INNER JOIN tbl2
    ->   ON tbl.id = tbl2.parent_id;
Empty set (0.00 sec)


mysql> SELECT * FROM tbl;
SELECT * FROM tbl;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

mysql> SELECT * FROM tbl2;
SELECT * FROM tbl2;
+------+-----------+
| id   | parent_id |
+------+-----------+
|    5 |      NULL |
+------+-----------+
1 row in set (0.00 sec)

なお、親の行は削除できない(ことがある？)

TRUNCATE TABLE tbl2;
INSERT INTO tbl2 VALUES (1, 1),(2, 1),(3, 2),(4, 2),(5, NULL);
DELETE tbl, tbl2
  FROM tbl
  INNER JOIN tbl2 
  ON tbl.id = tbl2.parent_id;

外部キー制約が存在する InnoDB テーブルを含む、複数テーブルの DELETE ステートメントを使用した場合は、MySQL オプティマイザが、それらの親子関係の順序とは異なる順序でテーブルを処理する可能性があります。
この場合、このステートメントは失敗し、ロールバックされます。
代わりに、1 つのテーブルから削除したあと、InnoDB が提供する ON DELETE 機能を使用して、ほかのテーブルがそれに応じて変更されるようにしてください。

勉強日記

チラ裏

MySQL 5.6 Developer試験対策 8 データの修正

INSERT文について説明する。INSERT文を実行する

INSERT ... SELECT

INSERT DELAYED

INSERT ... ON DUPLICATE KEY UPDATE

REPLACE文について説明する。REPLACE文を実行する

UPDATE文について説明する。UPDATE文を実行する

TRUNCATE文について説明する。TRUNCATE文を実行する

LOAD DATA文について説明する。LOAD DATA文を実行する

DELETE文について説明する。DELETE文を実行する