MySQLの忘備録

MySQLで毎回調べているコマンドをまとめています。逐次更新します。

セットアップ

Docker の MySQL を使います。docker-compose.yml ファイルを用意し、MySQL を起動させてください。

docker-compose.yml

version: "3.7"
services:
  my-db:
    image: mysql:8.0.20
    environment:
      MYSQL_ROOT_PASSWORD: password
      MYSQL_DATABASE: my-database
    command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    ports:
      - 3306:3306

MySQL を起動

$ docker-compose up -d

MySQL にログイン。

$ mysql --host 127.0.0.1 --port 3306 -u root -p    # password: password

データベースを選択

use my-database;

テーブルの作成

テーブルを作成します。

create table pokemon(
    pokemon_id int,
    name varchar(15)
);

insert into pokemon (pokemon_id, name) values (null, 'フシギダネ');
insert into pokemon (pokemon_id, name) values (2, 'フシギソウ');

not null

not null にすることで null が入らなくなる。

create table pokemon(
    pokemon_id int not null,
    name varchar(15)
);

insert into pokemon (pokemon_id, name) values (null, 'フシギダネ');

ERROR 1048 (23000): Column 'pokemon_id' cannot be null

unique

unique にすることでカラムがユニークになる

create table pokemon(
    pokemon_id int unique,
    name varchar(15)
);

insert into pokemon (pokemon_id, name) values (1, 'フシギダネ');
insert into pokemon (pokemon_id, name) values (1, 'フシギダネ');

ERROR 1062 (23000): Duplicate entry '1' for key 'pokemon.pokemon_id'

unique にしても null は複数入る

insert into pokemon (pokemon_id, name) values (null, 'フシギダネ');
insert into pokemon (pokemon_id, name) values (null, 'フシギダネ');

select * from pokemon;

+------------+-----------------+
| pokemon_id | name            |
+------------+-----------------+
|       NULL | フシギダネ      |
|       NULL | フシギダネ      |
+------------+-----------------+

primary key

プライマリーキーを設定する。

create table pokemon(
    pokemon_id int,
    name varchar(15),
    primary key(pokemon_id)
);

insert into pokemon (pokemon_id, name) values (1, 'フシギダネ');
insert into pokemon (pokemon_id, name) values (2, 'フシギソウ');
insert into pokemon (pokemon_id, name) values (3, 'フシギバナ');

primary key にすると null は入らない

insert into pokemon (pokemon_id, name) values (null, 'ヒトカゲ');

ERROR 1048 (23000): Column 'pokemon_id' cannot be null

テーブルのインサートにはデフォルト値が必要

insert into pokemon (name) values ('ゼニノコ');

ERROR 1364 (HY000): Field 'pokemon_id' doesn't have a default value

デフォルト値

カラムのデフォルト値を設定する。

create table pokemon(
    pokemon_id int default 0,
    name varchar(15)
);

id が insert文に無い場合はデフォルト値が入る

insert into pokemon (name) values ('フシギダネ');
insert into pokemon (name) values ('フシギソウ');
insert into pokemon (name) values ('フシギバナ');

auto_increment

プライマリーキーをインクリメントする。

create table pokemon(
    pokemon_id int auto_increment,
    name varchar(15),
    primary key(pokemon_id)
);

デフォルト値がインクリメントされる。

insert into pokemon (name) values ('フシギダネ');
insert into pokemon (name) values ('フシギソウ');
insert into pokemon (name) values ('フシギバナ');

select * from pokemon;

+------------+-----------------+
| pokemon_id | name            |
+------------+-----------------+
|          1 | フシギダネ      |
|          2 | フシギソウ      |
|          3 | フシギバナ      |
+------------+-----------------+

int

int のとりうる値

create table pokemon(
    pokemon_id int,
    name varchar(15)
);

-2147483648 から 2147483647 まで

insert into pokemon (pokemon_id, name) values (-2147483648, 'ヒトカゲ');
insert into pokemon (pokemon_id, name) values (2147483647, 'ヒトカゲ');

この範囲から超えるとインサートできなくなる。

insert into pokemon (pokemon_id, name) values (-2147483649, 'ヒトカゲ');
insert into pokemon (pokemon_id, name) values (2147483648, 'ヒトカゲ');

ERROR 1264 (22003): Out of range value for column 'pokemon_id' at row 1

int unsigned

int unsigned のとりうる値。

create table pokemon(
    pokemon_id int unsigned,
    name varchar(15)
);

0 から 4294967295 まで

insert into pokemon (pokemon_id, name) values (0, 'ヒトカゲ');
insert into pokemon (pokemon_id, name) values (4294967295, 'ヒトカゲ');

マイナスは入らない。

insert into pokemon (pokemon_id, name) values (-1, 'ヒトカゲ');
insert into pokemon (pokemon_id, name) values (4294967296, 'ヒトカゲ');

ERROR 1264 (22003): Out of range value for column 'pokemon_id' at row 1

複合ユニークキー

複合ユニークキー

create table pokemon(
    pokemon_id int,
    name varchar(15),
    unique key pokemon_id_name (
      pokemon_id,
      name
    )
);

以下のインサート文は実行できる。

insert into pokemon (pokemon_id, name) values (0, 'ヒトカゲ');
insert into pokemon (pokemon_id, name) values (1, 'ヒトカゲ');

以下は通らない。

insert into pokemon (pokemon_id, name) values (0, 'ヒトカゲ');
insert into pokemon (pokemon_id, name) values (0, 'ヒトカゲ');

ERROR 1062 (23000): Duplicate entry '0-ヒトカゲ' for key 'pokemon.pokemon_id_name'

foreign key

外部キー

create table pokemon_type(
    type varchar(15) unique
);

create table pokemon(
    name varchar(15),
    type varchar(15),
    constraint pokemon_pokemon_type_fk foreign key (type) references pokemon_type (type) on update RESTRICT on delete RESTRICT
);

insert into pokemon_type (type) values ('ノーマル');
insert into pokemon_type (type) values ('炎');

insert into pokemon (name, type) values ('カビゴン', 'ノーマル');
insert into pokemon (name, type) values ('ヒトカゲ', '炎');

pokemon_type テーブルには ワクチン はないので、以下のインサート分は通らない。

insert into pokemon (name, type) values ('アグモン', 'ワクチン');

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`my-database`.`pokemon`, CONSTRAINT `pokemon_pokemon_type_fk` FOREIGN KEY (`type`) REFERENCES `pokemon_type` (`type`) ON DELETE RESTRICT ON UPDATE RESTRICT)

ただ、pokemon_type.type が null許容になっていると以下のインサート文が通ってしまうので注意

insert into pokemon (name, type) values ('みがわり', null);

drop table

テーブルを削除する

create table pokemon(
    name varchar(15)
);

drop table pokemon;

テーブルのカラム名の取得

テーブルの絡む名を取得する

create table pokemon(
    name varchar(15)
);

show columns from pokemon;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(15) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

alter table

作成済みのテーブルに対して変更を加えることができる。

テーブル名を変更する

pokemon -> digimon にテーブル名を変更している。

create table pokemon(
    name varchar(15)
);

alter table pokemon rename to digimon;

カラム名を変更する

create table pokemon(
    name varchar(15)
);

alter table pokemon rename column name to namae;

show columns from pokemon;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| namae | varchar(15) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

テーブルやカラムにコメントを加える

create table pokemon(
    name varchar(15) comment 'pokemon name'
) comment='pokemon table';

show create table pokemon;

+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                            |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| pokemon | CREATE TABLE `pokemon` (
  `name` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'pokemon name'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='pokemon table' |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

データベースを書き出す(mysqldump)

書き出す。

create table pokemon(
    name varchar(15)
);

create table pokemon_type(
    type varchar(15) unique
);

insert into pokemon (name) values ('カビゴン');
insert into pokemon (name) values ('ヤドン');
insert into pokemon (name) values ('ピカチュウ');

insert into pokemon_type (type) values ('炎');
insert into pokemon_type (type) values ('水');
insert into pokemon_type (type) values ('草');

mysqldump --host 127.0.0.1 --port 3306 -u root -p my-database > export.sql    # password: mysql-pass

CSVを読み込む

create table pokemon(
    pokemon_id int,
    name varchar(15)
);

101,マルマイン
52,ニャース
54,コダック
195,ヌオー
130,ギャラドス

set global local_infile=1;
load data local infile "pokemons.csv" into table pokemon fields terminated by ',';

select * from pokemon;

+------------+-----------------+
| pokemon_id | name            |
+------------+-----------------+
|        101 | マルマイン       　|
|         52 | ニャース          |
|         54 | コダック         　|
|        195 | ヌオー          　|
|        130 | ギャラドス         |
+------------+-----------------+

Header 付きの CSV を読み取るときはこんな感じです。

pokemonId,name
101,マルマイン
52,ニャース
54,コダック
195,ヌオー
130,ギャラドス

set global local_infile=1;
load data local infile "pokemons.csv" into table pokemon fields terminated by ',' ignore 1 lines;

Join

create table type(
    type_id int,
    type varchar(15),
    primary key(type_id)
);

create table pokemon(
    pokemon_id int,
    name varchar(15),
    type_id int,
    primary key(pokemon_id)
);

insert into type (type_id, type) values (1, 'ノーマル');
insert into type (type_id, type) values (2, 'かくとう');
insert into type (type_id, type) values (3, 'ほのお');
insert into type (type_id, type) values (4, 'みず');

insert into pokemon (pokemon_id, name, type_id) values (1, 'カビゴン', 1);
insert into pokemon (pokemon_id, name, type_id) values (2, 'ワンリキー', 2);
insert into pokemon (pokemon_id, name, type_id) values (3, 'ヒトカゲ', 3);
insert into pokemon (pokemon_id, name, type_id) values (4, 'イーブイ', 1);
insert into pokemon (pokemon_id, name, type_id) values (5, 'ミュウ', 0);

select * from pokemon 
join type
on pokemon.type_id = type.type_id;

+------------+-----------------+---------+---------+--------------+
| pokemon_id | name            | type_id | type_id | type         |
+------------+-----------------+---------+---------+--------------+
|          1 | カビゴン        |       1 |       1 | ノーマル     |
|          2 | ワンリキー      |       2 |       2 | かくとう     |
|          3 | ヒトカゲ        |       3 |       3 | ほのお       |
|          4 | イーブイ        |       1 |       1 | ノーマル     |
+------------+-----------------+---------+---------+--------------+

select * from pokemon 
left outer join type
on pokemon.type_id = type.type_id;

+------------+-----------------+---------+---------+--------------+
| pokemon_id | name            | type_id | type_id | type         |
+------------+-----------------+---------+---------+--------------+
|          1 | カビゴン        |       1 |       1 | ノーマル     |
|          2 | ワンリキー      |       2 |       2 | かくとう     |
|          3 | ヒトカゲ        |       3 |       3 | ほのお       |
|          4 | イーブイ        |       1 |       1 | ノーマル     |
|          5 | ミュウ          |       0 |    NULL | NULL         |
+------------+-----------------+---------+---------+--------------+

select * from pokemon 
right outer join type
on pokemon.type_id = type.type_id;

+------------+-----------------+---------+---------+--------------+
| pokemon_id | name            | type_id | type_id | type         |
+------------+-----------------+---------+---------+--------------+
|          4 | イーブイ        |       1 |       1 | ノーマル     |
|          1 | カビゴン        |       1 |       1 | ノーマル     |
|          2 | ワンリキー      |       2 |       2 | かくとう     |
|          3 | ヒトカゲ        |       3 |       3 | ほのお       |
|       NULL | NULL            |    NULL |       4 | みず         |
+------------+-----------------+---------+---------+--------------+