MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」
[https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882:embed:cite]
MySQL 5.6 リファレンスマニュアル
[https://dev.mysql.com/doc/refman/5.6/ja/:embed:cite]
----------------------------------bibliography: https://dev.mysql.com/doc/refman/5.6/ja/sql-syntax-data-manipulation.html
INSERT文について説明する。INSERT文を実行する
[https://dev.mysql.com/doc/refman/5.6/ja/insert.html:embed:cite]
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文を実行する
[https://dev.mysql.com/doc/refman/5.6/ja/replace.html:embed:cite]
REPLACE は、INSERT とまったく同じように機能します。ただし、テーブル内の古い行に、PRIMARY KEY または UNIQUE インデックスに関して新しい行と同じ値が含まれている場合、その古い行は新しい行が挿入される前に削除されます。
-
挿入 or (削除 + 挿入)
- cf. 挿入 or 更新は
INSERT ... ON DUPLICATE KEY UPDATE
- cf. 挿入 or 更新は
- 候補キー的なものがない限り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文を実行する
[https://dev.mysql.com/doc/refman/5.6/ja/truncate-table.html:embed:cite]
-
DELETEとの違い
- はやい
- DDLである
- 速さのためにDMLのもろもろをバイパスする
- ロールバックできない(暗黙的なcommit)
ON DELETE
トリガーが起動しない- ので、FK制約の親などにはTRUNCATEできない
-
AUTO_INCREMENT
は開始値にリセット- 【補】PostgreSQLの
SERIAL
とは異なる挙動
- 【補】PostgreSQLの
- 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
コマンド相当
- 【補】PostgreSQLのpsqlの
-
LOCALなし: サーバー側のファイルを読み込むの意
- 【補】PostgreSQLの
COPY
ステートメント相当
- 【補】PostgreSQLの
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 機能を使用して、ほかのテーブルがそれに応じて変更されるようにしてください。